Thursday, July 26, 2012

Common Table Expression (CTE) is MS SQL Server


Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE can be self-referencing and can be referenced multiple times in the same query unlike a derived table.
In Common Table Expression we can create a recursive query. Reference the resulting table multiple times in the same statement. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.



EXAMPLE : 1
UPDATE or SELECT single row from a table which has many realted duplicates row
WITH CTE (config_element, rowNum)
AS
(
SELECT config_element,
ROW_NUMBER() OVER(PARTITION BY config_element ORDER BY config_element) AS rowNum
FROM [myDb].[dbo].[temp_configuration]
)
--UPDATE CTE SET config_element='factoryShortKeys' WHERE rowNum=2
SELECT * from CTE WHERE rowNum = 2
GO

EXAMPLE : 2
Using multiple CTE in single select query
WITH CTE_test1 AS (SELECT 100 AS Col1),
CTE_test2 AS (SELECT COL1+1 AS Col2 FROM CTE_test1)
SELECT CTE_test1.Col1, CTE_test2.Col2 FROM CTE_test1 
CROSS JOIN CTE_test2
GO

EXAMPLE : 3
Using multiple CTE in single select query
WITH CTE_test1  AS (SELECT 100 AS Col1),
CTE_test2 AS (SELECT COL1+1 AS Col2 FROM CTE_test1)
SELECT CTE_test1.Col1, CTE_test2.Col2 FROM CTE_test1 
CROSS JOIN CTE_test2
GO


For further detail see link:

Monday, July 23, 2012

Searching or matching text in string in MS SQL Server


Funcation CONTAINS match pattern which pass in second parameter.
Function PATINDEX works very similar to function CHARINDEX. We can use searching patter in PATINDIEX unlike CHARINDEX.

Syntax and example given below.

 --Pattern matching  

SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'rk')
SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'"rk*"')

SELECT PATINDEX('%rk%', [name]) as idx FROM tb1      --It return integer index

--like indexOf() (programming language function)
SELECT CHARINDEX('rk', [name])  as idx FROM tb1      --It return integer index

--like lastIndexOf() (programming language function)
SELECT CHARINDEX('rk', REVERSE([name])) FROM tb1     --It return integer index

Thursday, July 19, 2012

Difference between TRUNCATE and DELETE command in MS SQL Server

Sr. No. DLETE Command TRUNCATE Command
1. DELETE is a DML (Data Manipulation Language) command. TRUNCATE is a DDL (Data Definition Language) command.
2. DELETE command allows where clause. TRUNCATE does not allow WHERE clause.
3. We can ROLLBACK data in DELETE command. We cannot ROLLBACK data in TRUNCATE command. It removes all data permanently.
4. DELETE works on all table. TRUNCATE doesn’t work on that table which is referenced with another table.
5. DELETE Command is slower than TRUNCATE Command. TRUNCATE command is much faster than DELETE Command.
6. DELETE command doesn't resets any identity counter if there is any identity column present in the table . TRUNCATE resets identity counter if there is any identity column present in the table.
7. Triggers get fired in DELETE command if applied. A trigger doesn’t get fired in case of TRUNCATE Command.
8. DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically delete. TRUNCATE is also a logged operation but in a different way. TRUNCATE logs the de-allocation of the data pages in which the data exists. The de-allocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.

Monday, July 16, 2012

Convert Date in various Format Example



Example : Convert Date in various Format


SELECT GETDATE()               -- 2012-07-16 13:00:38.280
--DATE FORMAT
SELECT CONVERT(VARCHAR, GETDATE(), 1)           --Result will be   07/16/12
SELECT CONVERT(VARCHAR, GETDATE(), 2)           --Result will be   12.07.16
SELECT CONVERT(VARCHAR, GETDATE(), 3)           --Result will be   16/07/12
SELECT CONVERT(VARCHAR, GETDATE(), 4)           --Result will be   16.07.12
SELECT CONVERT(VARCHAR, GETDATE(), 5)           --Result will be   16-07-12
SELECT CONVERT(VARCHAR, GETDATE(), 6)           --Result will be   16 Jul 12
SELECT CONVERT(VARCHAR, GETDATE(), 7)           --Result will be   Jul 16, 12
SELECT CONVERT(VARCHAR, GETDATE(), 10)          --Result will be   07-16-12
SELECT CONVERT(VARCHAR, GETDATE(), 11)          --Result will be   12/07/16
SELECT CONVERT(VARCHAR, GETDATE(), 101)         --Result will be   07/16/2012
SELECT CONVERT(VARCHAR, GETDATE(), 102)         --Result will be   2012.07.16
SELECT CONVERT(VARCHAR, GETDATE(), 103)         --Result will be   16/07/2012
SELECT CONVERT(VARCHAR, GETDATE(), 104)         --Result will be   16.07.2012
SELECT CONVERT(VARCHAR, GETDATE(), 105)         --Result will be   16-07-2012
SELECT CONVERT(VARCHAR, GETDATE(), 106)         --Result will be   16 Jul 2012
SELECT CONVERT(VARCHAR, GETDATE(), 107)         --Result will be   Jul 16, 2012
SELECT CONVERT(VARCHAR, GETDATE(), 110)         --Result will be   07-16-2012
SELECT CONVERT(VARCHAR, GETDATE(), 111)         --Result will be   2012/07/16

--TIME FORMAT
SELECT CONVERT(VARCHAR, GETDATE(), 8)           --Result will be   13:19:27
SELECT CONVERT(VARCHAR, GETDATE(), 9)           --Result will be   Jul 16 2012  1:19:35:720PM
SELECT CONVERT(VARCHAR, GETDATE(), 14)          --Result will be   13:19:42:610


Sunday, July 15, 2012

SQL Query Quick Examples

***  Various Usefull Examples ***

------RE-Seed Auto-number in Relational Table (if TRUNCATE not allowed on table)---
--PRIMARY TABLE
CREATE TABLE masterTbl (
id INT IDENTITY(1,1) CONSTRAINT pk_id PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description VARCHAR(max))

--FORIGN TABLE
CREATE TABLE detailTbl (
id INT IDENTITY(1,1) PRIMARY KEY,
m_id INT NOT NULL CONSTRAINT fk_mid FOREIGN KEY REFERENCES masterTbl(id) ON DELETE CASCADE,
details VARCHAR(MAX))
GO
----THIS CODE WILL RE-SEED AND AUTONUBER BEGIN TO ONE AS LIKE TRUNCATE---
DELETE FROM [dbo].[masterTbl]
DBCC CHECKIDENT ('[dbo].[masterTbl]', RESEED, 1)
GO
-------------------------END------------------------------


IF NOT EXISTS (SELECT 1 FROM [tb1] WHERE [id]=101)
PRINT '101 Not Exist'
ELSE
PRINT '101 Exist'
GO


----------- CTE (Common Table Expressions)----------------------- 
WITH CTE (config_element, rowNum)
AS
(
SELECT config_element,
ROW_NUMBER() OVER(PARTITION BY config_element ORDER BY config_element) AS rowNum
FROM [myDb].[dbo].[temp_configuration]
)
--update cte set config_element='factoryShortKeys' WHERE rowNum=2
SELECT * from CTE
GO


SELECT MY_NAME = [name] FROM [myDb]..[myTable] --alias
SELECT  CAST([id] as FLOAT) FROM [myDb]..[myTable]
SELECT  CONVERT (FLOAT, [id]) FROM [myDb]..[myTable]
SELECT CONVERT(VARCHAR, [date], 101) as MY_Date FROM [myDb]..[myTable] ---four digit yr US format
SELECT CONVERT(VARCHAR, [date], 1) as MY_Date FROM [myDb]..[myTable]   ---two digit yr US format
SELECT CONVERT(VARCHAR, [date], 103) as MY_Date FROM [myDb]..[myTable] ---four digit yr UK format
SELECT CONVERT(VARCHAR, [date], 3) as MY_Date FROM [myDb]..[myTable]   ---two digit yr UK format
SELECT [date], DATEDIFF(YY, [date], GETDATE()) AS Age FROM [myDb]..[myTable] -------DATE DIFF() GETDATE()
SELECT CASE WHEN [name] = 'Ajay' THEN 'AJAY GARG' ELSE [name] END as [my name] FROM [myDb]..[myTable]
SELECT CASE WHEN [name] = 'Ajay' THEN 'AJAY GARG'  WHEN [name]='Vijay' THEN 'VEDA GARG' ELSE [name] END as [my name] FROM [myDb]..[myTable]

SELECT [name] FROM myTable1 WHERE name <> 'Ajay'
SELECT [name] FROM myTable1 WHERE (name<>'Vijay' and name <>'Ajay') or [salary] is null
SELECT [name] FROM myTable1 WHERE Left([name],2)='Aj'

SELECT [name] FROM myTable1 WHERE [name] like '[A-K]%'
SELECT [name] FROM myTable1 WHERE [name] like '[^A-K]%'
SELECT CHARINDEX('i','ANIAC',1)
SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'rk')
SELECT ID FROM [RKDB].[dbo].[tb1] WHERE CONTAINS([name],'"rk*"')
SELECT TOP 1000 [id],[name],[dob] FROM [RKDB].[dbo].[tb1] ORDER BY 1 DESC
SELECT TOP 1 WITH TIES [name],[salary] FROM [RKDB].[dbo].[tb1] ORDER BY salary DESC --salary is highest
SELECT TOP 50 PERCENT [id],[name],[dob],[salary] FROM [RKDB].[dbo].[tb1]

SELECT COUNT(*) FROM [myDb].[dbo].[customer]
SELECT COUNT_BIG(*) FROM [myDb].[dbo].[customer]
SELECT COUNT(distinct [customerId]) FROM [myDb].[dbo].[customer]
SELECT MIN([customerId]) FROM  [myDb].[dbo].[customer]
SELECT MAX([customerId]) FROM  [myDb].[dbo].[customer]
SELECT SUM([customerId]) FROM  [myDb].[dbo].[customer]
SELECT '$' + CAST ( CAST(SUM([customerId]) AS DECIMAL(17,2)) AS VARCHAR) FROM  [myDb].[dbo].[customer]


SELECT AVG([customerId]) FROM  [myDb].[dbo].[customer]
SELECT STDEV([customerId]) FROM  [myDb].[dbo].[customer]
SELECT STDEV(ALL [customerId]) FROM  [myDb].[dbo].[customer]
SELECT STDEV(DISTINCT [customerId]) FROM  [myDb].[dbo].[customer]
SELECT COUNT([customerId]) AS COUNT,[customerId] into #NewDimProduct FROM  [myDb].[dbo].[customer]  GROUP BY [customerId]


//------BOTH are similar query---
SELECT DISTINCT userId FROM TB1 WHERE exists (SELECT projectId FROM TB2 T2 WHERE T2.userId='121')
SELECT DISTINCT userId FROM TB1 WHERE projectId IN (SELECT projectId FROM TB2 T2 WHERE T2.userId='121')
//-------------------


SELECT  * FROM TB1  //--UNION TABLE WITH NO REPEAT ROW
UNION
SELECT  * FROM TB2


SELECT  * FROM TB1  //--UNION TABLE WITH REPEAT ROW
UNION ALL
SELECT  * FROM TB2

SELECT  * FROM TB1 //--SELECT FROM TB1 WHICH IS NOT IN TB2
EXCEPT
SELECT  * FROM TB2

SELECT  * FROM TB1 //--SELECT ONLY COMMON ROW
INTERSECT
SELECT  * FROM TB2

//------------------------------GUID as ID by default-------------------
CREATE TABLE [tb1] (guidCol UNIQUEIDENTIFIER DEFAULT newsequentialid() ROWGUIDCOL, name VARCHAR(60))
INSERT INTO [tb1] (name) SELECT 'abc'
INSERT INTO [tb1] (name) SELECT 'xyz'
SELECT $rowguid FROM [tb1]
SELECT guidCol FROM [tb1]



SELECT CURRENT_TIMESTAMP
SELECT DAY(CURRENT_TIMESTAMP)
SELECT MONTH(GETDATE())
SELECT YEAR('2012-04-24')

---DATE DIFFERENCES---
SELECT DATEDIFF(SECOND, GETUTCDATE(), GETDATE()) AS SEC_DIFF      --SECOND DIFFERENCE   **RESULT 19800
SELECT DATEDIFF(MI, GETUTCDATE(), GETDATE()) AS MIN_DIFF          --MIN DIFFERENCE   **RESULT 330
SELECT DATEDIFF(YEAR, GETUTCDATE(), GETDATE()) AS YEAR_DIFF       --YEAR DIFFERENCE   **RESULT 0
SELECT DATEDIFF(YEAR,'2011-04-15', '2012-05-09') AS YEAR_DIFF     --YEAR DIFFERENCE   **RESULT 1


SELECT  DATEADD (MINUTE , 50 , GETDATE()) ADD MINUTE IN DATE

SELECT SIGN(3-7)  --RESULT  -1  
SELECT SIGN(3-1)  --RESULT  +1
SELECT SIGN(3-3)  --RESULT  0


SELECT STUFF ('ABCDEF',2,0,'xyz') --syntax: STUFF( char_expression, start, length, replaceWith_expression)
SELECT COALESCE([salary],12000) FROM [tb1] -- If salary then salary else 12000


--CONCATENATE VALUES OF A COLUMN
DECLARE @name nvarchar(max)
SELECT @name = coalesce(@NAME+', ', '')+[name] FROM [tb1]
SELECT @name

SELECT STUFF ((SELECT N', ' + name FROM tb1 FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'')
SELECT STUFF ((SELECT','+ [name] FROM [tb2] FOR XML PATH('')),1,1,'') AS [name_csv_col]



BULK INSERT [tb3] FROM 'c:\a\data.txt' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n') --IMPORT DATA FROM CSV FILE


--GET EXT NAME OF GIVEN FILE NAME
DECLARE @TEXT VARCHAR(50) ='MY_FILE.JPG'
SELECT SUBSTRING(@TEXT, CHARINDEX('.',@TEXT)+,LEN(@TEXT))


--GET LENTH OF DATA IN BYTE
SELECT DATALENGTH(dob) FROM [tb2]



--** CREATE FUNCTION UDF TO EXTRACT INTEGER FROM STRING **---
CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
      RETURNS VARCHAR(1000)
AS
BEGIN
      DECLARE @Count INT
      DECLARE @IntNumbers VARCHAR(1000)
      SET @Count = 0
      SET @IntNumbers = ''

      WHILE @Count <= LEN(@String)
      BEGIN
            IF SUBSTRING(@String,@Count,1) >= '0'
            AND SUBSTRING(@String,@Count,1) <= '9'
            BEGIN
                  SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
            END
            SET @Count = @Count + 1
      END
      RETURN @IntNumbers
END
GO

SELECT dbo.ExtractInteger('ASDFASDFASDF-AS-DF-ASD-F43QW25-432-534-345-ASDF')
GO
-------**END FUNCTION**----------



--------TABLE VARIABLE DOES NOT KNOW ROLLBACK UNLIKE TEMP TABLE ----
CREATE TABLE #MY_TEMP (col1 VARCHAR(100)) --CREATE TEMP TABLE
INSERT INTO #MY_TEMP (col1)                           --INSERT ROW IN TEMP TABLE
VALUES ('TEMP TABLE ----- OUT SIDE TRANSACTION')

DECLARE @TBL_VAR TABLE (col1 VARCHAR(100))      --DECLARE TABLE VARIABLE
INSERT INTO @TBL_VAR (col1)                           --INSERT INTO TABLE VARIABLE
VALUES('TABLE VARIABLE-------OUT SIDE TRANSACTION')

SELECT [col1] AS tempTable_beforeTrans            --CHECK VALUE IN TEMP TABLE
FROM #MY_TEMP

SELECT [col1] as tempVariable_beforeTrans         --CHECK VALUE IN TABLE VARIABLE
FROM @TBL_VAR

BEGIN TRAN                                                  --BEGIN TRANSCTION
INSERT INTO #MY_TEMP (col1)
VALUES ('TEMP TABLE --------AFTER TRANS') --INSERT INTO TEMP IN TRANS

INSERT INTO @TBL_VAR (col1)
VALUES ('TABLE VARIABLE -------- AFTER TRANS')-- INSERT INTO TABLE VARIABLE IN TRANS
ROLLBACK

SELECT [col1] AS tempTable_afterTrans                   --NOW CHECK VALUE
FROM #MY_TEMP

SELECT [col1] as tempVariable_afterTrans
FROM @TBL_VAR
GO
-----------END DIFF TABLE VARIABLE AND TEMP TABLE--------



SELECT [create_date] FROM sys.server_principals WHERE sid = 0x010100000000000512000000 --TO GET SQL-SERVER INSTALLATION DATE




---------------------------BEGIN-------------------
--CASE SENSITIVE COLLECTION ON RESULT SET --Latin1_General_CI_AS (CASE INSENSITIVE), Latin1_General_CS_AS (CASE SENSITIVE)
CREATE TABLE [MyTable] (
Col1 VARCHAR(15) COLLATE Latin1_General_CI_AS,
Col2 VARCHAR(14) COLLATE Latin1_General_CS_AS) ;
INSERT [MyTable](Col1, Col2)
VALUES ('Hyna','Hyna'),
('hyna','hyna'),
('starfish','starfish'),
('Starfish','Starfish');
GO
-- Retrieve SORTED Data will retrieve case in-sensitve order
SELECT * FROM [MyTable] ORDER BY Col1
GO
-- Retrieve SORTED Data will retrieve case sensitve order
SELECT * FROM [MyTable] ORDER BY Col2
GO
//----------------END--------------------------



----Short Key to jump Parenthesis---
SELECT *
FROM (SELECT *
FROM (SELECT *
FROM (SELECT *
FROM myTable)x)y)z
--CTRL + SHIFT + ]  --Shortcut to Select Code Between Two Parenthesis
--CTRL + ]              --Shortcut to Switch Between Two Parenthesis