Local and global temporary tables in SQL Server

  • Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.
  • Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.
  • Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
  • Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted
Advertisements

Optional Parameters in UDF and SP

Optional Parameters in Functions and Procedures in SQL Server work quite differently. An optional parameter in a function still needs to be called with the DEFAULT keyword in the place of the optional value, while in Stored Procedures they can simply be left out. See the two examples below

You can declare option parameters in a Functions like as follows:

CREATE FUNCTION [dbo].[RS_GetVehiclePartsInspectionHistory2]
( 
@FromTime datetime,
@ToTime datetime,
@nVehicleID INT = NULL
)
RETURNS TABLE 
AS
RETURN

SELECT .....

But it still needs to be called as follows:

You can declare option parameters in a UDF using the DEFAULT keyword like as follows:

SELECT *
FROM dbo.RS_GetVehiclePartsInspectionHistory2(@FromTime, @ToTime, DEFAULT)

For Stored Procedures, you can declare optional parameters like so:

CREATE PROCEDURE [dbo].[Core__SetConfigItemString](@configKey varchar(256), @value nvarchar(2000), @timestamp datetime, @CurrentUser nvarchar(200) = NULL)
AS

........

And then you do not need to pass the @CurrentUser variable into the Proc at all

Using CASE Statements in WHERE and ORDER BY

You can use CASE statements in SQL WHERE clauses to change your WHERE Statement depending on a value.

For example, consider the scenario where I want to SELECT everything from the Vehicles table if the Vehicle ID passed into the function is NULL, but use the VehicleID passed in to filter if it is infact an Integer ID:

CREATE FUNCTION [dbo].[RS_GetVehicles]
( 
@nVehicleID INT = NULL
)
RETURNS TABLE 
AS
RETURN

SELECT * FROM Vehicles WHERE IsDeleted = 0 AND VehicleID = CASE WHEN @nVehicleID IS NOT NULL THEN @nVehicleID ELSE VehicleID END
GO

 

You can use CASE statements in SQL Order BY clauses to change your ORDER BY Statement depending on a value.

For example, consider the scenario where I want to SELECT everything from the Vehicles table ordered by VehicleName DESC if the VehicleName is less NOT NULL, and ordered by VehicleID DESC otherwise:

SELECT * FROM Vehicles WHERE IsDeleted = 0
ORDER BY (CASE WHEN VehicleName IS NULL THEN VehicleID ELSE VehicleName END) DESC

 

For example, consider the scenario where I want to SELECT everything from the Vehicles table ordered by Vehicle ID ASC if the IsDeleted column is 0, and ordered by VehicleID DESC otherwise:

SELECT * FROM Vehicles
ORDER BY (CASE WHEN IsDeleted = 0 THEN VehicleID*-1 ELSE VehicleID END) DESC

Validating JSON in SQL Server 2016

SQL Server 2016 and upwards has a useful feature where JSON data can be validated through SQL Code.

There are two ways of doing this:

Inline in a SQL Function/Procedure

IF (ISJSON(@json) = 1)
BEGIN
   PRINT 'Is valid JSON'
END
As part of a check constraint on a table
CREATE TABLE Person 
 ( ID INT IDENTITY PRIMARY KEY,
 json NVARCHAR(MAX)
 CONSTRAINT [Content should be formatted as JSON]
 CHECK ( ISJSON(json )>0 )
 )

Why Functions in WHERE clauses should be avoided

Consider we have the following query:

SELECT ModifiedDate FROM Contacts 
WHERE DATEDIFF(MINUTE, ModifiedDate, GETDATE()) > 0

This query uses the DATEDIFF function, and when we look at the query plan we can see a Index scan is being performed.

2017-12-18_12h31_43

The issue with an index scan is that all rows are reviewed before returning a result

And now consider this query:

SELECT ModifiedDate FROM Contacts 
WHERE GETDATE() > ModifiedDate

Since we are not using a function in the WHERE clause, this query uses an Index seek.

2018-01-10_17h13_47.png

An index seek is much more efficient then an index scan when there is an index on the column

Useful SQL Snippets

Returns first day of a week (Most recent Monday)

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)

Returns Date of Previous Sunday

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1)

To get midnight of a timestamp

DECLARE @dtTimestamp DATETIME = '2017-10-12 15:26:00.000'
SET @dtTimestamp = DATEADD(DAY, DATEDIFF(DAY, 0, @dtTimestamp + 1), 0)
PRINT @dtTimeStamp

To concatenate an INT and Varchar

SET ConcatString = ('Name' + CAST(ID AS VARCHAR)

To get number of records by day

SELECT    DATEPART(YEAR, Timestamp) AS 'Year',
          DATEPART(MONTH, Timestamp) AS 'Month',
          DATEPART(DAY, Timestamp) AS 'Day',
          COUNT(*) AS 'NumberOfInspections'
FROM      InspectionsV2
GROUP BY  DATEPART(DAY, Timestamp),
          DATEPART(MONTH, Timestamp),
          DATEPART(YEAR, Timestamp)
ORDER BY  'Year',
          'Month',
          'Day'

To set first day of week to be a Monday (default in TSQL is Sunday)

SET DATEFIRST 1

2019-07-10_03h50_40

To get non distinct enteries:

SELECT ID FROM Customers GROUP BY ID HAVING COUNT(ID) > 1

To get random int between range (between 1 and 500 here):

ABS(CHECKSUM(NEWID()) % (500 - 1 + 1)) + 1

To get middle time in a span:

DATEADD(ms, DATEDIFF(ms,CAST(@FromTime AS datetime), CAST(@ToTime AS datetime))/2, CAST(@FromTime AS datetime))