sql-server

Solving the N+1 selects problem

This problems occurs when you have a parent object loading its child data by issuing a separate SQL statement for each child objects that needs to be loaded. So n+1 queries are executed against the database.

Records are being loaded individually because they are being lazy loaded.

What we can do instead if to use eager loading.

Adhoc queries vs Parameterized queries vs Stored procedures

Parameterized queries vs adhoc (dynamic) queries:
- Protects against SQL injection attacks.
- Improves application performance and scalability as the same cached execution plan is reused for each query execution, compared with adhoc queries where an execution plan is generated every time.
- Plan Cache memory is kept low.

Parameterized queries vs stored procedures:
- Similar performance.
- Stored procedures offers advantages when we talk about security or restriction to data.

SQL Server Operations in Query Execution Plan

Clustered Index Scan: Reads all the rows stored in a table stored as a clustered index.
Table Scan: Reads all the rows in table that is stored as a heap structure.
Clustered Index Seek: Traverses the tree structure of table stored as a clustered index to find the needed row(s).
Index Scan: Reads all of the key values of an index to find the matching data.
Index Seek: Traverses the tree structure of an index to find the matching index keys.

Nested Loops Join: For each value in the first data set, SQL Server loops through the second data set looking for matches.
Merge Join: Used to join two data sets that are already sorted using the same key. A row from each source is obtained. If the rows match, they are joined. If the rows don't match, the lower value row is discarded and a new row is obtained from that source.
Hash Match: A hashtable of the smaller data set is created, then SQL Server loops through the larger data set probing the hashtable for matching values. Used when two large datasets must be joined.

SQL Server: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

You cannot truncate a table which has an FK constraint on it. As workaround, you could:
- Drop the constraints.
- Trunc the table.
- Recreate the constraints.

Here it is the associated T-SQL script, supposing you have 2 tables called MyTable and MyReferencedTable:

-- Remove constraint
IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_MyReferencedTable_MyTable')
BEGIN
        ALTER TABLE dbo.MyReferencedTable
        DROP CONSTRAINT FK_MyReferencedTable_MyTable
END


-- Truncate table
TRUNCATE TABLE dbo.MyTable


-- Re-Add constraint
IF NOT EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_MyReferencedTable_MyTable')
BEGIN
        ALTER TABLE dbo.MyReferencedTable
        WITH CHECK ADD CONSTRAINT [FK_MyReferencedTable_MyTable] FOREIGN KEY(ListingKey)
        REFERENCES dbo.MyTable (ListingKey)
END

Find all permissions for all users in a database

You need to run the following query to list permissions for all users in a database.

WITH cteUserPermissions
AS
(
-- Permissions provisioned to a sql user
-- or windows user/group directly
SELECT  
        [UserName] = CASE DP.[type]
                                        WHEN 'S' THEN DP.[name]
                                        WHEN 'U' THEN L.[name]
                                 END,
        [UserType] = CASE DP.[type]
                                        WHEN 'S' THEN 'SQL User'
                                        WHEN 'U' THEN 'Windows User'
                                 END,  
        [DatabaseUserName] = DP.[name],      
        [Role] = null,      
        [PermissionState] = P.[state_desc],
        [PermissionName] = P.permission_name,
        [ObjectName] = OBJECT_NAME(P.major_id),
        [ObjectType] = O.type_desc    
FROM        
        sys.database_principals DP
        LEFT JOIN sys.login_token L ON DP.[sid] = L.[sid]
        LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
        LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]
WHERE DP.[type] in ('S','U')  

UNION

-- Permissions provisioned to a sql user or windows user/group
-- through a database or application role
SELECT  
        [UserName] = CASE M.[type]
                                        WHEN 'S' THEN M.[name]
                                        WHEN 'U' THEN L.[name]
                                 END,
        [UserType] = CASE M.[type]
                                        WHEN 'S' THEN 'SQL User'
                                        WHEN 'U' THEN 'Windows User'
                                 END,
        [DatabaseUserName] = M.[name],  
        [Role] = R.[name],      
        [PermissionState] = P.[state_desc],
        [PermissionName] = P.permission_name,
        [ObjectName] = OBJECT_NAME(P.major_id),
        [ObjectType] = O.type_desc
   
FROM        
        sys.database_role_members DRM
        JOIN sys.database_principals R ON R.[principal_id] = DRM.[role_principal_id]
        JOIN sys.database_principals M ON M.[principal_id] = DRM.[member_principal_id]
        LEFT JOIN sys.login_token L ON M.[sid] = L.[sid]
        LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = R.[principal_id]
        LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]

UNION

-- Permissions provisioned to the public role,
-- which everyone gets by default
SELECT  
        [UserName] = '{All Users}',
        [UserType] = '{All Users}',
        [DatabaseUserName] = '{All Users}',      
        [Role] = DP.[name],      
        [PermissionState] = P.[state_desc],
        [PermissionName] = P.permission_name,
        [ObjectName] = OBJECT_NAME(P.major_id),
        [ObjectType] = O.type_desc
FROM
        sys.database_principals DP
        LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
        JOIN sys.objects O ON O.[object_id] = P.[major_id]
WHERE
        DP.[type] = 'R'                         -- only roles
        AND DP.[name] = 'public'        -- only public role
        AND O.is_ms_shipped = 0
)
SELECT *
FROM cteUserPermissions
WHERE UserName in ('{All Users}', 'myusername')
ORDER BY UserName, ObjectName, PermissionState, PermissionName

Upsert and Merge with SSIS

Question
Check if data exists in the destination table then update it, otherwise insert new record.

Solution using Lookup
- on match you execute an UPDATE statement via **OLE DB command**.
- on error you add row in destination via **OLE DB destination task**.

More details: here.

upsert with sql server integration services

Solution using Merge Join Transformation and Conditional Split
More details: here.
upsert with sql server integration services

Count of SQL Server connections by IP address

Count of connections by IPs.

SELECT
        EC.client_net_address, ES.host_name, ES.program_name, ES.login_name,
        COUNT (ec.session_id) as NoConnections
FROM
        sys.dm_exec_sessions ES
        INNER JOIN sys.dm_exec_connections EC ON ES.session_id = EC.session_id
GROUP BY EC.client_net_address, ES.program_name, ES.host_name, ES.login_name
ORDER BY NoConnections DESC

Creating readonly views in Sql Server

According to MSDN, views composed of simple selects automatically allow you to use insert/update/delete statements on the table.

There are several different options for avoiding this and in this article I will present various ways to make a view read only in a SQL Server database:

1. Permissions
You have the option to remove UPDATE/DELETE/INSERT permissions on the view.

2. UsingINSTEAD OFtrigger

CREATE TRIGGER dbo.MySampleView_Trigger_OnInsertOrUpdateOrDelete]
ON dbo.MySampleView
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
        RAISERROR ('You are not allow to update this view!', 16, 1)    
END

3. UsingUNIONoperator
You could specify an UNION operator in order to make SQL Server fail during the INSERT/UPDATE/DELETE operation. See the below example:

ALTER VIEW dbo.MySampleView
as
SELECT col1, col2 FROM dbo.MySampleTable
UNION
SELECT NULL, NULL WHERE 1 =0

Islands problem - identifying consecutive ranges

I will present a T-SQL query that uses ROW_NUMBER for identifying consecutive ranges of existing values (also known as islands problem).
Given the below script, the expected ranges are: [1,2], [11-14], [25], [32-33].

SET NOCOUNT ON;
IF OBJECT_ID('dbo.MyTable') IS NOT NULL
        DROP TABLE dbo.MyTable;
GO

CREATE TABLE dbo.MyTable
(
col1 INT NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
);

INSERT INTO dbo.MyTable(col1)
VALUES (1),(2),(11),(12),(13),(14),(25),(31),(32),(33);
GO

Step 1: use the ROW_NUMBER function to calculate row numbers based on col1 ordering

SELECT col1, ROW_NUMBER() OVER (ORDER BY col1) as row_num
FROM dbo.MyTable

Step 2: identify islands
The difference between two columns is constant and unique for each island as shown in this query:

SELECT col1, col1 - ROW_NUMBER() OVER (ORDER BY col1) as grp
FROM dbo.MyTable

Step 3: complete solution

;WITH cte
AS
(
        SELECT col1, col1 - ROW_NUMBER() OVER (ORDER BY col1) as grp
        FROM dbo.MyTable
)
SELECT MIN(col1) as start_range, MAX(col1) as end_range
FROM cte
GROUP BY grp

Pages

Subscribe to RSS - sql-server