How to find space used by each column in a table

SELECT 'SELECT SUM(DATALENGTH(' + name + '))/1024.0/1024.0 As ' + name + '_MB FROM dbo.MyTable'
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.MyTable')

Number of pages in a table

How to find data file pages for a table:

SELECT
        T.NAME AS table_name,
        P.rows AS no_rows,
        SUM(AU.total_pages) AS total_pages,
        SUM(AU.used_pages) AS used_pages,
        (SUM(AU.total_pages) - SUM(AU.used_pages)) AS unused_pages
FROM
        sys.tables T
        INNER JOIN sys.indexes I ON T.OBJECT_ID = I.object_id
        INNER JOIN sys.partitions P ON I.object_id = P.OBJECT_ID AND I.index_id = P.index_id
        INNER JOIN sys.allocation_units AU ON P.partition_id = AU.container_id
GROUP BY
        T.name, P.rows
ORDER BY total_pages DESC

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:
1/ Drop the constraints
2/ Trunc the table
3/ 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

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

Comparing table structure with TSQL

Compare table structures using sys.columns:

DECLARE
        @table1 as NVARCHAR(255) = N'Reports',
        @table2 as NVARCHAR(255) = N'Reports_Archives'

;WITH cteColumns
AS
(
        SELECT C.object_id, C.name as column_name, T.name as type_name, C.max_length, C.precision
        FROM
                sys.columns C
                INNER JOIN sys.types T ON T.system_type_id = C.system_type_id
)
SELECT column_name, type_name, max_length, precision, 'In ' + @table1
FROM cteColumns
WHERE object_id = OBJECT_ID(@table1)
EXCEPT
SELECT column_name, type_name, max_length, precision, 'In ' + @table1
FROM cteColumns
WHERE object_id = OBJECT_ID(@table2)
 
UNION ALL
 
SELECT column_name, type_name, max_length, precision, 'In ' + @table2
FROM cteColumns
WHERE object_id = OBJECT_ID(@table2)
EXCEPT
SELECT column_name, type_name, max_length, precision, 'In ' + @table2
FROM cteColumns
WHERE object_id = OBJECT_ID(@table1)

When statistics were last updated in SQL Server?

T-SQL showing when statistics were last updated using STATS_DATE function in SQL Server:

SELECT
        OBJECT_NAME(S.object_id) as object_name,  S.name as stat_name,
        S.auto_created, S.user_created, S.no_recompute,
        STATS_DATE(S.object_id, stats_id) as last_updated
FROM
        sys.stats S
        INNER JOIN sys.objects O ON O.object_id = S.object_id AND O.is_ms_shipped = 0
ORDER BY object_name

How to (manually) update the statistics?

  • Force stats to update for a specific table (including clause WITH FULLSCAN)
    UPDATE STATISTICS <tablename>;
  • Full sampling:
    UPDATE STATISTICS <tableName> (<columnName>) WITH FULLSCAN;
  • Force stats to update for the entire database:
    EXEC sp_updatestats;

Identify blocking in SQL Server

Run this T-SQL statement to identify current blockings in the current database:

SELECT *
FROM sys.sysprocesses
WHERE
        dbid = DB_ID()
        AND blocked > 0

Too see T-SQL that blocks, just take the value of spid column returned above and run the following command:

DBCC INPUTBUFFER(<spid>);

In order to see the blocked T-SQL command, take the value of column returned above and run:

DBCC INPUTBUFFER(<blocked>);

Find missing index warnings in cached query plans

Find missing index warnings for cached plans in the current database:

SELECT TOP 20
        OBJECT_NAME(objectid) as object_name,
        CP.objtype, CP.usecounts,
        QP.query_plan  
FROM
        sys.dm_exec_cached_plans CP
        CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
        CAST(QP.query_plan as NVARCHAR(MAX)) LIKE N'%MissingIndex%'
        AND dbid = DB_ID()
ORDER BY CP.usecounts DESC

Pages

Subscribe to SQLhint RSS