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

Count of SQL Server connections by IP address

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

Pages

Subscribe to SQLhint RSS