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

Rename a local and remote branch in git

If you have named a branch incorrectly AND pushed this to the remote repository follow these steps before any other developers get a chance to jump on you and give you shit for not correctly following naming conventions.

1. Rename your local branch.
If you are on the branch you want to rename:

1
git branch -m new-name

If you are on a different branch:

1
git branch -m old-name new-name

2. Delete the old-name remote branch and push the new-name local branch.

1
git push origin :old-name new-name

3. Reset the upstream branch for the new-name local branch.
Switch to the branch and then:

1
git push origin -u new-name

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))

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