- 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
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
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
Getting Datatype of an SQL Column
DECLARE @sDataType NVARCHAR(60) SELECT @sDataType = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName'
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)
'Is valid JSON'
CREATE TABLE Person ( ID INT IDENTITY PRIMARY KEY, json NVARCHAR(MAX) CONSTRAINT [Content should be formatted as JSON] CHECK ( ISJSON(json )>0 ) )
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.
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.
An index seek is much more efficient then an index scan when there is an index on the column
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
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))