SQL Snippets

From SpenchWiki

Jump to: navigation, search

Contents

Snippets

  • Update index statistics: 
EXEC SP_UPDATESTATS
  • Server version: 
SELECT @@VERSION
  • Case-sensitive string comparison: 
WHERE	(ComponentName.Name = @name COLLATE SQL_Latin1_General_CP1_CS_AS)
  • Finding a sproc in the meta-database 
SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_NAME = N'uspFindComponent'
     AND SPECIFIC_SCHEMA = N'<Schema_Name, sysname, Schema_Name>'
  • Declaring a table variable 
DECLARE @TableVar TABLE (Cola INT PRIMARY KEY, Colb CHAR(3))
 
INSERT INTO @TableVar VALUES (1, 'abc')
INSERT INTO @TableVar VALUES (2, 'def')
 
SELECT * FROM @TableVar
GO
  • WHERE EXISTS condition 
SELECT Version FROM LanguageVersion WHERE EXISTS
	(
		SELECT * FROM LanguageVersion
	)
  • Selecting results into a table variable & CTE using WITH 
DECLARE @i INT
DECLARE @t TABLE (L BIGINT, ID BIGINT, V NVARCHAR(64))
INSERT @t
EXEC @i = uspFindLanguageVersion 'My Language'
SELECT V FROM @t
SELECT @i AS 'Return'
GO
 
WITH DirReps(ManagerID, DirectReports) AS
(
    SELECT Version, LANGUAGE
    FROM LanguageVersion AS e
)
SELECT ManagerID, DirectReports
FROM DirReps
ORDER BY ManagerID;
GO
  • TRANSACTION 
BEGIN TRANSACTION;
 
BEGIN TRY
 
-- Statements
 
END TRY
BEGIN CATCH
 SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
 
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
	RETURN(1);
END CATCH;
 
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
	RETURN(0);

Notes

  • Cannot have ORDER BY clause in a view's definition
  • Performing replication on sprocs with GO not commented out using standard SQL -- comment will result in replication failure: 
-- Drop stored procedure if it already exists
/*IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
	WHERE SPECIFIC_NAME = N'uspFindComponent'
)
   DROP PROCEDURE uspFindComponent
--GO
 
CREATE PROCEDURE uspFindComponent
*/ALTER PROCEDURE [dbo].[uspFindComponent]

MSDN links

More snippets

  • Derived query 
SELECT     ComponentName.Name, derivedtbl_1.ID
FROM         ComponentName CROSS JOIN
                          (SELECT     ID
                            FROM          Component) AS derivedtbl_1
	WHERE derivedtbl_1.ID = ComponentName.Component
  • INNER JOIN 
SELECT     ComponentReference.Reference, ComponentReference.Component, Reference.Location, ComponentReference.ID
FROM         ComponentReference INNER JOIN
                      Reference ON ComponentReference.Reference = Reference.ID
WHERE     (ComponentReference.Component = 983)
  • Generating a GUID 
DECLARE @id UNIQUEIDENTIFIER
SET @id = NEWID()
DECLARE @txt NVARCHAR(36)
SET @txt = CONVERT(NVARCHAR(36), @id)
SELECT @id, @txt, LEN(@txt)
  • Procedure for converting a hexadecimal string into the original binary data 
CREATE PROCEDURE dbo.uspHexToBinary
	@s NVARCHAR(MAX),
	@b VARBINARY(MAX) OUT
AS
	DECLARE @SQL NVARCHAR(MAX)
	SET @SQL = 'SELECT @b = ' + @s
	EXEC SP_EXECUTESQL @SQL, N'@b VARBINARY(MAX) OUTPUT', @b OUTPUT
GO
  • Clever EXISTS ( SELECT 1 ) JOIN trick 
SELECT  r2.*
FROM    (
        SELECT  TOP 50 CustomerID
        FROM    (
                SELECT  MovieID
                FROM    Ratings
                WHERE   CustomerID = 915
                ) q
        JOIN    Ratings r
        ON      r.MovieID = q.MovieID
                AND CustomerID <> 915
                AND EXISTS
                (
                SELECT  1
                FROM    Ratings re
                WHERE   re.MovieID = 1
                        AND re.CustomerID = r.CustomerID
                )
        GROUP BY
                CustomerID
        ORDER BY
                COUNT(*) DESC
        ) ro
JOIN    Ratings r2
ON      r2.MovieID = 1
        AND r2.CustomerID = ro.CustomerID
  • Spatial index statistics (useful for debugging slow query or plan warnings)

If query is slow (especially when using a JOIN or limiting row count using TOP or ROW_NUMBER), force plan to use spatial index with (no quotes): 

WITH (INDEX(<spatial INDEX name>))

This will avoid the case where a plan is generated that suggests it's faster to seek over all rows and filter by a spatial predicate, instead of actually using the spatial index.

Plan utilising spatial index might show a warning on the "Clustered Index Seek (Spatial)" saying "Columns with no statistics" (Cell_Attributes, SRID, pk0). This may have something to do with the sub-optimal plan being generated when the query has certain conditions (e.g. TOP 690 is slow, while TOP 691 and above is fast). 

DECLARE c CURSOR STATIC FOR
SELECT OBJECT_ID, name, index_id FROM sys.indexes WHERE type_desc = 'SPATIAL'
OPEN c
      DECLARE @obj INT
      DECLARE @statsname VARCHAR(MAX)
      DECLARE @indid INT
      DECLARE @tname VARCHAR(MAX)
FETCH NEXT FROM c INTO @obj, @statsname, @indid
WHILE (@@FETCH_STATUS = 0)
BEGIN
      DECLARE @internaltablename VARCHAR(MAX)
      DECLARE @stmt VARCHAR(MAX)
      FETCH NEXT FROM c INTO @obj, @statsname, @indid
      SELECT TOP 1 @tname = name FROM sys.objects WHERE OBJECT_ID = @obj
      SET @internaltablename = 'sys.extended_index_' + CAST(@obj AS VARCHAR(MAX)) + '_' + CAST(@indid AS VARCHAR(MAX))
    SELECT 'Showing statistics for ', @tname
      SELECT @stmt = 'dbcc show_statistics (''' + @internaltablename + ''', ''' + @statsname + ''')'
      EXEC (@stmt)
END
CLOSE c
DEALLOCATE c

Spatial index statistics missing

Retrieved from "http://wiki.spench.net/wiki/SQL_Snippets"
Personal tools
Navigation
Latest Content
Blog
Gallery