Skip to content

Latest commit

 

History

History
98 lines (79 loc) · 7.6 KB

SqlServer.md

File metadata and controls

98 lines (79 loc) · 7.6 KB
Raw
Difference between CTE,Temp Table and Table variable? Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression. 
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
  1. A table variable is created in the memory whereas a temporary table is created in the TempDB. But if there is memory pressure, the pages belonging to a table variable may be pushed out to tempdb.
  2. Table variables cannot be involved in transactions, logging or locking. This makes the table variable faster than a temporary table.
  3. We can pass the table variable as a parameter to functions and stored procedures, whereas we cannot do the same with a temporary table.
  4. The temporary table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variable can be faster, but if there are a lot of records, or there is a need to search the temporary table based on a clustered index, then a Temporary Table would be better. If we have less than 100 rows generally use a table variable. Otherwise, use a temporary table. This is because SQL Server won’t create statistics on table variables.
Improve sql performance https://www.dotnettricks.com/learn/sqlserver/tips-to-improve-sql-server-database-design-and-performance
  1. Select columns instead of *
  2. Avoid select distinct - it works by grouping all fields
  3. Create ANSI joins instead of Non ANSI
  4. Using where is more efficient than having clause
  5. Use wildcards at the end of LIKE search
  6. Archiving old data
  7. Missing indexes
  8. Query hints - nolock, recompile(update statistics)
  9. Use exists() instead of count()
Cascading referential integrity constraint Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a user attempts to delete or update a primary key to which an existing foreign keys point.
  1. SET NULL: If a delete or update statement affects rows in a foreign key table, those values will be set to NULL when the primary key record is deleted or updated. The foreign key columns affected must allow NULL values.
  2. CASCADE: If a delete statement affects one or more rows in a foreign key table, those rows will be deleted when the primary key record is deleted. If an update statement affects rows in the foreign key table, those rows will be updated with the value from the primary key record after it has been updated.
  3. SET DEFAULT: If a delete or update statement affects rows in a foreign key table, then all rows containing those foreign keys are set to the default value. All foreign key columns in the related table must have default constraints defined on them.
  4. NO ACTION: This is the default action. This specifies that if an update or deletes statement affects rows in foreign key tables, then the action will be denied and rolled back. An error message will be raised.
Databases - master, resource, msdb, model, tempdb

Master

Records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.

Resource

Read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

MSDB

Stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

Tempdb

Holds temporary objects such as global and local temporary tables and stored procedures.

Model

Template database used in the creation of any new user database created in the instance.

Can a foreign key reference a primary key? Yes, a foreign key can actually reference a key that is not the primary key of a table. But a foreign key must reference a unique key.
Disadvantages of an index
  1. Increased disk space
  2. DML statements could be slow
Advantages of using a stored procedure
  1. Execution Plan Retention and Reusability As there is no unnecessary compilation of queries this will reduces the burden on database (when we send a query to a SQL Server three things happen in order, 1st it checks the syntax of that query, 2nd it compiles that query, 3rd it generates an execution plan) as response User will get a quick response. The Stored Procedures are pre-compiled and their execution plan is cached and used again when the same stored procedure is executed again. Although ad-hoc queries also create and reuse plan, the plan is reused only when the query is the textual match and the datatypes are matching with the previous call. Any changes in the datatype or you have an extra space in the query then, a new plan is created.
  2. Reduces the Network Traffic The Stored Procedure reduces network traffic. When we execute a stored procedure we need to send the procedure name and parameters so only these things are passed on the network but if we are not using the stored procedure then we need to write the ad-hoc queries and we need to execute them which may contain many numbers of lines. So the stored procedure reduces the network traffic as a result performance of the application increase.
  3. Code Reusability and Better Maintainability Multiple applications can use the same stored procedure. The different applications which want similar kind of data then they can use the same stored procedure. The advantage is that if we want to change the stored procedure then we need to change it in one place that will affect to all the application that uses it whereas if it is inline SQL query and if we have to use it in multiple applications, then we end up with multiple copies of the same inline SQL query, and if the logic has to change, then we have to change the logic at all the places, which makes it harder maintaining inline SQL. So, the stored procedure provides code reusability and maintainability.
  4. Better Security By granting permission to the underlying database the user can do everything. He can view all the records as well as he can also modify the records. But if we want to restrict the user only to view the records then we need to grant only for that stored procedure which will display the records. In that way, we achieve better security with a stored procedure.