Information Schema

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'
Advertisements

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)

Clearing Caches in SQL Server

The following are a few useful commands for Database Performance testing:

  • DBCC FREEPROCCACHE – Clears the procedure cache and forces SQL Server to recompile procedure plans the next time a respective procedure is run
  • DBCC DROPCLEANBUFFERS – Clears the SQL Server data cache i.e any data that exists in the buffer cache from previous execution of queries is removed

Bulk Inserting Data into a Database

Bulk inserting data into a database table can be a very useful operation for the following reasons:

  • You may have data stored in a file that you need to insert all at once
  • You may also want to execute a table TRIGGER once, if a cursor inputted data then a Trigger would be executed on each insert which would make the operation much less performant. Bulk insertions ensure that a TRIGGER is executed only once regardless of the amount of data inserted.

 

Methods of Bulk Insertion

1. Bulk Inserting Data from a file

Consider the scenario where we have customer details stored in a csv file and want to insert these records into a customer table in our database.

BULK INSERT Customers FROM 'C:\Customers.csv'
 WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
 GO

In relation to executing triggers, the BULK INSERT command actually has a mechanism for executing a TRIGGER on each INSERT if required. If we wanted to execute a trigger for an insert of each row from the Customers.csv file our BULK INSERT statement would look like the following:

BULK INSERT Customers FROM 'C:\Customers.csv'
 WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRETRIGGERS)
 GO

Omitting FIRETRIGGERS means that no TRIGGERs will be fired.

 

2. Bulk Inserting Data using a temporary table

Using the same example of the Customers table like above, except this time we will be inserting data row by row into a Temp table, and then insert all the contents of this Temp table into the Customers table.

The main requirement here is that the Customers table and the CustomersTemp table must have the same definition:

DECLARE @Customers AS TABLE 
	([CustomerID] [int] NOT NULL, [Age] [int] NULL, [Data] [nvarchar](max) NULL)

DECLARE @CustomersTemp AS TABLE 
	([CustomerID] [int] NOT NULL, [Age] [int] NULL, [Data] [nvarchar](max) NULL)

For example, if we had a script that created bulk Customers, we could have a cursor that inserts each record into the @CustomersTemp temporary table. Once all the data has been inserted to this temp table, we insert the contents of the temp table into the actual table like follows:

INSERT INTO @Customers (CustomerID, Age, Data)
SELECT
CustomerID, Age, Data FROM @CustomersTemp

 

SQL Cursors

Cursors are very useful for allowing a user to step through a result set of a query and performing an operation/task on every row.

For example – stepping through the result set of a Customers table and appending two columns like FirstName and Surname to a variable. The result of these operations is one row of comma separated values.

Basic Synthax

2015-05-14_17h59_44

  • The DECLARE CURSOR statement defines the SELECT statement that forms the basis for the cursor. READ_ONLY is important here as it dramatically improves the performance of the Cursor.
  • The OPEN statement executes the SELECT and populates the result.
  • The FETCH statement returns a row from the result set into the variable
  • FETCH_STATUS is used to determine if there are any more rows. It will contain the value of 0 as long as there are more rows. The WHILE part of this statement moves through each row of the result set,
  • In this WHILE loop we just PRINT the contents of @AuthorID, but in fact you can execute any type of SQL you wish here. For example you could use a Stored procedure here and create a new unique Identifier for all authors based on their @AuthorID. For updates, you will need to remove the READ_ONLY clause from the cursor declararion
  • CLOSE releases the row set and DEALLOCATE release the resources associated with a cursor