Search

Advertisements

Showing posts with label t-sql. Show all posts
Showing posts with label t-sql. Show all posts

SQL Server access Previous / Next rows and first / last row from partitioned resultset

From SQL Server 2012, new built-in analytic functions are introduced, using that we can easily access subsequent/previous row in the same result set without the use of a self-join and first/last value in an ordered set of values.

In this post, let us see some examples for LEAD(), LAG(), FIRST_VALUE() and LAST_VALUE()

SQL Server 2016 - Drop Objects IF EXISTS

In SQL Server 2016 many T-SQL enhancements were introduced. In this article, I am going to share one among them.

In SQL Server 2016 there is a new way to drop objects with simplified syntax.

In previous versions syntax will be as shown below: (this will also work in SQL Server 2016)

IF OBJECT_ID (N'dbo.Test_tbl', N'U') IS NOT NULL
DROP TABLE dbo.Test_tbl;
GO

Below are some examples:

SQL Server - Split CSV into separate row and other splitting techniques using OPENJSON

In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

In this article, let us see some examples on splitting comma separated string into separate rows using OPENJSON

 --Split comma separated numeric
SELECT value FROM OPENJSON('[1,2,3]')

-- Split comma separated alphabets
SELECT value FROM OPENJSON('["A","B","C"]')

--Ignore null while splitting

SQL Server - Convert relational data to JSON data


In SQL Server 2016, Microsoft provides built-in support for importing, exporting, parsing, and querying JSON documents or fragments.

In this article, let us see examples on how to convert relational data (data in rows & columns format) to JSON format

SQL Server services information

Though there is an option in Windows to get SQL Server services information by typing Services.msc in Start - > Search Programs

From SQL Server 2008R2, DMV is available to get SQL Server services information.

SQL Server - Subquery - Be careful in passing column names in the subqueries

If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error.
 
SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.
 
Examples:

T-SQL - Storing and retrieving multi lingual data

In SQL Server, to store and retrieve multi lingual data :

*) We should define the column data type as NVARCHAR

*) and while inserting multi lingual data into column, column value should be prefixed with N'

For example:
 

SQL Server - Calender details

DECLARE @StartDate DATE = '2014-01-01', @EndDate DATE = '2014-12-31'
;With CTE
AS
(
SELECT @StartDate dt
UNION ALL
SELECT DATEADD(DAY, 1, dt) FROM Cte
WHERE dt < @EndDate
)
SELECT
FORMAT ( dt, 'D', 'en-US' ) Calender
,DATENAME(dayofyear, dt) DayNumber_Year
,DATEPART(day, dt) DayNumber_Month
,DATEPART(weekday, dt) DayNumber_Week
,DATENAME(weekday, dt) DayName
,DATEPART(WEEK,dt) WeekNumber
,IIF ( DATEPART(weekday, dt) IN (1,7) , 'Weekend', 'Weekday' ) Day
,DATENAME(month, dt) MonthName
,DATEPART(month, dt) MonthNumber
,DATENAME(year, dt) Year
,EOMONTH (dt) MonthLastDate
FROM cte
OPTION (MAXRECURSION 366);


 

TSQL Script to Generate Create ColumnStore Index Script

 This article is about the TSQL script which can be used to generate create ColumnStore Index script from the database .

SET CONCAT_NULL_YIELDS_NULL OFF;

SQL Server - FOR XML clause can be used within CTE

In SQL Server  - FOR XML clause can be used within CTE . But it is stated in MSDN BOL as

The following clauses cannot be used in the CTE_query_definition:
  • ORDER BY (except when a TOP clause is specified)
  • INTO
  • OPTION clause with query hints
  • FOR XML
  • FOR BROWSE

To make it more clear , I raised a question on MSDN T-SQL forum - http://social.technet.microsoft.com/Forums/en-US/1b5d3c1a-0083-4f47-b6b0-35f86cdc2291/for-xml-clause-with-cte?forum=transactsql

Here is the Connect case [Feedback]  for change in MSDN BOL documentation .




T-SQL - INSERT Statement with Columnslist inside Stored procedure

This article shares the information about what happens when INSERT Statement is used  inside Stored procedure with & without Columns list .


--Create Sample tableCREATE TABLE Test (Id INT , Name VARCHAR(20))
GO


--Scenario 1 - INSERT statement Without Columnslist inside Stored procedure


SQL Server ORDER BY using Alias name of the column mentioned in the SELECT statement

This article shares information about  sorting resultset of T-SQL query using ORDER BY clause with alias name of the column mentioned in the SELECT statement .

Example :


DECLARE @Tmp TABLE (Id INT)
INSERT @Tmp SELECT 3
INSERT @Tmp SELECT 2
INSERT @Tmp SELECT 1

--Without ORDER BY
SELECT * FROM @Tmp

--With ORDER BY AliasName
SELECT Id AliasName
FROM  @Tmp
ORDER BY AliasName




The reason that ORDER BY clause works with alias name of the column mentioned in the SELECT statement where as WHERE , GROUP BY & HAVING clause cannot is  ORDER BY comes after SELECT statement in the logical query processing phase .

SQL Server - ORDER BY with CASE statement - Example

In this article , let us see an example for ORDER BY with CASE statement 

Consider below sample data :

DECLARE @Tmp TABLE (Id VARCHAR(5),Name VARCHAR(5))
INSERT @Tmp SELECT 1,'C'
INSERT @Tmp SELECT 2,'B'
INSERT @Tmp SELECT 3,'A'


If we are required to sort based on column and if the name of the column is passed as input , then we can make use of  ORDER BY with CASE statement 

--Sort Input
DECLARE @Sortby VARCHAR(20) = 'Name'

SELECT * FROM @Tmp
ORDER BY CASE WHEN @Sortby = 'Name' THEN Name
              ELSE Id END

--Sort Input
SET @Sortby = 'Id'

SELECT * FROM @Tmp
ORDER BY CASE WHEN @Sortby = 'Name' THEN Name
              ELSE Id END  





Reference links :

http://technet.microsoft.com/en-us/library/ms188385.aspx
http://technet.microsoft.com/en-us/library/ms188723%28v=sql.105%29.aspx 

SQL Server - Concatenating string / integer with NULL

In SQL Server , SET statement - CONCAT_NULL_YIELDS_NULL -
Controls whether concatenation results are treated as null or empty string values.

Concatenation of string with NULL (Example) :

DECLARE @a VARCHAR(5) = 100
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT @a + NULL;
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT @a + NULL; 




Concatenation of integer with NULL (Example) :

In case of  integer , we cannot make use of CONCAT_NULL_YIELDS_NULL ,
CONCAT_NULL_YIELDS_NULL works only with string types .

SET CONCAT_NULL_YIELDS_NULL OFF;
DECLARE @b INT = 100
SELECT @b + NULL ;
SELECT @b + ISNULL(NULL,0) ; 




In SQL Server 2012 , we have CONCAT( ) function to concatenate string with NULL .

For example :

SELECT CONCAT(100,NULL)
--returns 100


Reference - http://technet.microsoft.com/en-us/library/ms176056.aspx

SQL Server - ISNUMERIC

ISNUMERIC - Determines whether an expression is a valid numeric type .
Returns 1 if it is a valid numeric type else 0 .

--Valid Scenarios (ISNUMERIC returns 1)

--valid numbers
SELECT ISNUMERIC(12345)

--valid numbers inside quotes
SELECT ISNUMERIC('12345')

--valid numbers with comma separated
SELECT ISNUMERIC('1,23,456')

--decimal type
SELECT ISNUMERIC(12345.)

--decimal type
SELECT ISNUMERIC(123.45)

--decimal type
SELECT ISNUMERIC(.12345)

--decimal type
SELECT ISNUMERIC('.')

--plus and minus symbols are allowed in the beginning
SELECT ISNUMERIC('+')

--plus and minus symbols are allowed in the beginning
SELECT ISNUMERIC(+12345)

--plus and minus symbols are allowed in the beginning
SELECT ISNUMERIC(-12345)

--money symbols are allowed in the beginning
SELECT ISNUMERIC('$')

--money symbols are allowed in the beginning
SELECT ISNUMERIC($12345)

--Exponential function
SELECT ISNUMERIC(123E2)

--Exponential function
SELECT ISNUMERIC(123e-2)




--Invalid Scenarios (ISNUMERIC returns 0)


--dots are allowed to represent decimal & float type(multiple dots are not allowed)
SELECT ISNUMERIC('12.3.45')

--symbols are allowed only in the beginning
SELECT ISNUMERIC('12345$')

--symbols are allowed only in the beginning
SELECT ISNUMERIC('12345+')

-- Other symbols are not allowed except +,- and money symbols
SELECT ISNUMERIC('*12345')

--Invalid exponential function
SELECT ISNUMERIC('1232E')

--Invalid exponential function
SELECT ISNUMERIC('12E3E2')

--Invalid exponential function
SELECT ISNUMERIC('E1232')

--Obviously alphabets are not allowed
SELECT ISNUMERIC('A1232')


Reference links :
http://technet.microsoft.com/en-us/library/ms186272.aspx
http://technet.microsoft.com/en-us/library/ms179882.aspx

Advertisements