July 18, 2014

SQL Server - PATINDEX() function

PATINDEX() function helps to return the starting position of a pattern in a string. It takes two arguments, the pattern to be searched and the expression (string), and is not case sensitive. This will return zero if it doesn't find the pattern.

Syntax:

PATINDEX('%pattern%, expression')

This function supports same as LIKE operator which includes % wildcard for one or more characters and the _ wildcard for any single character.

The string that is enclosed inside % character is the pattern. The pattern can also be used as a pattern using character such as "_", "[]" and [^].

% helps to match one or more characters.
_  helps to match any single character.
[] helps to match any character within the []
[^] helps to match all the character that is not in [^]

It would be more clear how PATINDEX works in the SQL Server looking at the example below:

DECLARE @var VARCHAR(255)= 'PATINDEX() will return zero if it does not find the pattern.'
SELECT
            PATINDEX('%n_t%', @var) AS 'not',
            PATINDEX('%pat%', @var) AS 'PATINDEX',
            PATINDEX('%pat[^i]%', @var) AS 'pattern',
            PATINDEX('%w[a-i]%', @var) AS 'will'


July 16, 2014

Current Time - SQL Server

You can get the current datetime from SQL Server in three different ways:

1) SELECT CURRENT_TIMESTAMP

2) SELECT {FN NOW()}

3) SELECT GETDATE()

July 11, 2014

Difference between Temp Table, Table Variable and Common Table Expression (CTE)

Temp Table in SQL Server are temporarily created table in TempDB database. TempDB can be found on System Databases. It is stored as an object and created with the help of single pound sign (#).


Temp table acts just like normal table, it can have primary keys, constraints, indexes and statistics. It can be referenced in different places within the procedures but are limited to the SQL Server session or connection. Its better to use Temp table when there is lot more data to be handled with the help of indexes and statistics.

Example:

CREATE TABLE #TEMP
(
        ID INT,
        FIRSTNAME VARCHAR(50),
        LASTNAME VARCHAR(50)
)

GO

INSERT INTO #TEMP VALUESA (1, 'Avesh', 'Dhakal')

GO
SELECT * FROM #TEMP

Table Variable is like a temp table and are created in TempDB database. The main difference is primary key can be created in Table Variable but there cannot be indexes and statistics.  Primary key and unique constraint in the table declaration will automatically create an index. It acts much like variables in their scoping variables. They are created when they are declared and gets dropped once it comes out of the batch. If there isn't much more data to be handled and does not require indexes and statistics then Table variable can be used.

DECLARE @TABLE TABLE
(
        ID INT,
        FIRSTNAME VARCHAR(50),
        LASTNAME VARCHAR(50)
)

GO

INSERT INTO @TABLE VALUESA (1, 'Avesh', 'Dhakal')

GO
SELECT * FROM @TABLE

GO  -- table variable is not valid after this GO ends the batch
SELECT * FROM @TABLE -- This will throw an error because

Common Table Expression (CTE) also act like a temp table/view, but are not created in TempDB. It is created in memory.