Unleashing MS SQL Server 2012
Microsoft recently launched a new member to the family of SQL Server i.e. MS SQL Server 2012.Presently, many of you might be working on MS SQL Server 2005 tool or MS SQL Server 2008 tool. So, are you excited to know more about the new features of MS SQL Server 2012? Congratulations!!! You are at the right door as this article will aim to unleash the new features of MS SQL Server 2012.Microsoft Code-named the MS SQL Server 2012 as “Denali”.
Denali (MS SQL Server 2012) includes several types of groundbreaking new features such as Always-on, Column store Index, Data Quality Services, Power View and Cloud Connectivity. All these great features help to provide better performance and reduce the work of the developers at the time of writing queries. This article will mainly focus on the new things added to MS SQL Server 2012. In future articles, we will be learning these features into depth practically. Also, we will uncover MS SQL Server 2014 features. Let’s gather some brief information on the above mentioned terms.
Always on – An improved version of Database mirroring known as Availability groups is added. It helps to simplify high availability environment. It helps in Multi-site clustering also referred as Multi-Subnet clustering.
Column Store Index – It is a new type of index in data warehouse to improve query performance.How columnstore index can enhance the query performance? The funda behind this is that columnstore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored.
So, what is the use of this and why we should go with it? Because it is the more efficient way as thequery only requests a few columns from the table. Microsoft claims that with this feature, query performance can improve from 6X to 100X in cases where the data can be cached in RAM, and thousand-fold improvements where the working set does not fit in RAM.
NOTE :- For Technology giants, you can follow the below link for an exhaustive guide to what’s new in SQL Server 2012, Microsoft Press have released a free 288 page book covering all the new features; available in PDF form here :- http://download.microsoft.com/download/F/F/6/FF62CAE0-CE38-4228-9025-FBF729312698/Microsoft_Press_eBook_Introducing_Microsoft_SQL_Server_2012_PDF.pdf
The following are the other new features added in MS SQL Server 2012 – Denali :-
- Sequences.
- Data Paging.
- Analytic Window Functions.
- Conversion Functions.
- Logical Functions.
- Date/Time Functions.
- String Functions.
- Error handling.
Feature# Sequences :-
- It’s almost similar to Identity property.
- New construct to generate numeric sequences.
- We can set min and max values.
- Cycle will set the current value to the min value when the max value is reached.
- Future values can be cached minimizing disk IO.
Feature# Data Paging :-
OFFSET and FETCH :-
- It allows for server side paging.
- Not data pages (8K) but paging like on a web page.
- OFFSET AND FETCH is the argument of the ORDER BY clause.
- OFFSET – Number of rows to skip.
- FETCH – Number of rows to return.
Feature# Analytic Window Function:-
We can use OVER clause with the Analytic functions. The OVER clause determines the partitions and order of a row set before the window function is applied. Before going to Analytic function we must know about OVER clause.
OVER clause –
- PARTITION BY – Similar to GROUP BY but only applies to the window function and not the entire query.
- ORDER BY – Specifies the order of the rows in the partition.
Now, Let’s learn about Analytic functions.
- LAG
- LEAD
- FIRST_VALUE
- LAST_VALUE
- PERCENT_RANK
- CUME_DIST
- PERCENTILE_CONT
- PERCENTILE_DISC
LAG and LEAD :- No longer need to use a self-join or CTE. Many developers use to find previous rows and future rows by using SELF join and CTE, But now it’s pretty much simple to find those by using LAG() and LEAD() functions.
- LAG : Access data from previous rows in the result set.
- LEAD : Access data from future rows in the result set.
FIRST_VALUE and LAST_VALUE :-
- FIRST_VALUE : Retrieves the first value in a partition.
- LAST_VALUE : Retrieves the last value in a partition.
CUME_DIST :-
- Calculates the percentage of values less than or equal to the current value in the group.
- COUNT (*) OVER (ORDER BY Col1) / Total Count.
PERCENTILE_CONT :-
- Calculates a percentile value.
- Will interpolate the appropriate value.
- Can use to find the median.
- PERCENTILE_CONT(0.5).
- CONT stands for continuous.
PERCENTILE_DISC :-
- Calculates a percentile value.
- Like PERCENTILE_CONT but will select a value that exists in the set.
- Can use to find the median.
- PERCENTILE_CONT(0.5).
- DISC stands for discrete distribution.
Feature# Conversion functions:-
- PARSE :-
- Can only convert to a number or datetime.
- TRY_PARSE :-
- Like PARSE but if an error occurs returns a NULL.
- TRY_CONVERT :-
- Attempts to cast a value into a specified data type. Returns NULL if CONVERT fails.
Feature# Logical functions:-
- IIF :-
- Takes a Boolean expression and returns one of two values.
- Has the same limitations as CASE.
- Can only be nested to 10 levels.
- CHOOSE :-
- Returns a value from a list based on a specified index.
- If the specified index is not in the list NULL is returned.
- Returns the data type based on data type precedence.
Feature# DATE/TIME functions :-
- EO_MONTH :-
- Returns last date of a specified month.
- Can specify a month_to_add argument to increment or decrement result.
- FROMPARTS :-
- DATEFROMPARTS ( year, month, day)
- DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
- DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
- DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
- SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
- TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
Feature# STRING FUNCTIONS :-
- CONCAT :-
- Does what it says…concatenates strings together.
- NULLs are automatically converted to empty strings.
- Can pass other data types for concatenation.
- FORMAT :-
- Simplifies the string formatting of dates and other data types.
- No more memorizing numeric predefined format values.
- Returns a string.
Feature# Error handling :-
- THROW :-
- Reduces the need to use RAISERROR in TRY/CATCH blocks.
- Can provide custom error messages.
- Always uses severity level 16.
Conclusion :- Microsoft SQL Server 2012 code name Denali is launched with features such as Always on, Column store Index, Data Quality Services, Power View and Cloud Connectivity will benefit in the following way.
- Greater availability.
- Blazing-fast performance.
- Rapid data exploration.
- Consistent data.
- Optimized productivity.
Free e-books and Pdf’s :-
Please Subscribe or Like PhpRinG Tutorials for free e-books and pdf’s.
- Launching Very Soon free e-books and pdf’s on MS SQL Server.
These all makes MS SQL Server 2012 – “Next generation Data warehouse”. I am sure new version of this tool (SQL Server 2012) will be a boom in the market. Please share your views in the form of comments. Stay tune to PhpRinG Tutorials for more Information!!!
String functions perform an operation on an input string and return a string or numeric value:
1) ASCII (character_expression): Returns the integer value, as defined by the ASCII standard, for the first character of the input expression.
EX: SELECT 'V' AS CHARACTER , ASCII('V') AS ASCIIValue
O/p: Character ASCIIValue
----------------------
D 86
2) CHAR ( integer_expression):It takes ASCII Value and returns ASCII character for that value
EX: SELECT 86 AS ASCIIValue ,Char(86) AS CHARACTER
O/p: ASCIIValue Character
-----------------------------
86 V
3) NCHAR ( integer_expression ):It takes UNICODE Value and returns UNICODE character for that value
EX: SELECT 248 AS UniCodeValue ,NChar(248) AS UniCodeCharacter
O/p: UnicodeValue UnicodeCharacter
------------------------------------------------------
248 ø
4) CHARINDEX (expression1, expression2 [ , start_location ] ): It searches expression2 for expression1 and returns its starting position if found. The search starts at start_location.
EX: SELECT CHARINDEX ('Tide', 'Time and Tide wait for none') AS Location
O/p: Location
------------------
10
5) SOUNDEX (character_expression): Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
EX: SELECT SOUNDEX ('Jeanie') AS COLUMN1, SOUNDEX ('Jeany') AS COLUMN2
O/p : COLUMN1 COLUMN2
-------------------------------------------------
S500 S500
6) DIFFERENCE (character_expression , character_expression ): Returns an integer value (0
to 4) that indicates the difference between the SOUNDEX values of two character expressions 0
means less similarity. 4 mean strong similarity.
to 4) that indicates the difference between the SOUNDEX values of two character expressions 0
means less similarity. 4 mean strong similarity.
EX: SELECT DIFFERENCE('Green','Greene') AS COLUMN1;
O/p: COLUMN1
------------------
4
7) LEFT (character_expression , integer_expression ): Returns the left part of a character string with the specified number of characters.
EX: SELECT LEFT('Anubhi',3)
O/p: NoColumnValue
------------------
Anu
8) RIGHT (character_expression , integer_expression ): Returns the right part of a character string with the specified number of characters.
EX: SELECT RIGHT('SQLServer',6)
O/p: NoColumnValue
------------------
Server
9) LEN (string_expression ): Returns the number of characters of the specified string expression, excluding trailing blanks.
EX: SELECT LEN(' Vijaya Lakshmi') AS Length
O/p : Length
------------------
15
10) LOWER (character_expression ): Returns a character expression after converting uppercase character data to lowercase.
EX: SELECT LOWER('ViShAl JhArWaDe') AS LowerCase
O/p : LowerCase
---------------
vishal jharwade
11) UPPER (character_expression ): Returns a character expression with lowercase character data converted to uppercase.
EX: SELECT UPPER(' ViShAl JhArWaDe') AS UpperCase
O/p : UpperCase
------------------------
VISHAL JHARWADE
12) PATINDEX ( '%pattern%' , expression ): Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. ‘%pattern%’ Is a literal string. Wildcard characters can be used; however, the % character must come before and follow patternis an expression of the character string data type category.
EX: SELECT PATINDEX ('%like%',' I like reading history books but do not like Geography
books') AS COLUMN1
books') AS COLUMN1
O/p: Column1
-----------
3
SELECT PATINDEX ('%like%', 'I love reading history books but do not like Geography
books') AS COLUMN1
books') AS COLUMN1
O/p: Column1
-----------
41
SELECT PATINDEX ('%hate%', 'I love reading history books but do not like Geography
books') AS COLUMN1
books') AS COLUMN1
O/p: Column1
---------
0
13) QUOTENAME (‘character_string' [ , 'quote_character' ] ): Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier.
EX: SELECT QUOTENAME('abc[]def') AS COLUMN1
O/p : COLUMN1
------------------------
[abc[]]defg]
14) REPLACE (string_expression, string_pattern , string_replacement ): Replaces all occurrences of a specified string value with another string value.
EX: SELECT REPLACE ('Money and Tide wait for none.','Money','Time') AS COLUMN1;
O/p: COLUMN1
----------
Time and Tide wait for none.
15) REPLICATE (string_expression, integer_expression): Repeats a string value a specified number of times.
EX: SELECT REPLICATE ('*', 8) + 'End of the Document' + REPLICATE ('*', 8) AS COLUMN1
O/p : COLUMN1
--------------------------
********End of the Document********
16) REVERSE ( string_expression ): Returns the reverse of a string value.
EX: SELECT REVERSE(12345678910) AS Reversed
O/p : Reversed
------------------
01987654321
EX: SELECT REVERSE('NITIN') AS Reversed
O/p : Reversed
------------
NITIN
EX: SELECT REVERSE('VISHAL') AS Reversed
O/p : Reversed
-----------------
LAHSIV
17) LTRIM (character_expression): Returns a character expression after it removes leading blanks.
EX: SELECT LTRIM(' SQLServer') AS COLUMN1
O/p: COLUMN1
---------------
SQLServer
18) RTRIM ( character_expression ): Returns a character string after truncating all trailing blanks.
EX: SELECT RTRIM('SQLServer ') AS COLUMN1
O/p : COLUMN1
-------------
SQLServer
19) SPACE (integer_expression ): Returns a string of repeated spaces.
EX: SELECT 'SQL' + SPACE(5) + 'Server' AS COLUMN1
O/p : COLUMN1
------------
SQL Server
20) STR ( float expression [ , length [ , decimal ] ] ): Returns character data converted from numeric data.
EX: SELECT STR(123.45, 6, 1)
O/p : NoColumnValue
-------
123.5
21) STUFF ( character_expression , start , length ,character_expression ): The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
EX: SELECT STUFF ('SQL Server is a File database', 17,4, 'Relational') AS COLUMN1
O/p : AS COLUMN1
-----------------
SQL Server is a Relational database
22) SUBSTRING (value_expression ,start_expression , length_expression ): Returns part of a character, binary, text, or image expression.
EX: SELECT SUBSTRING('Data Mining is knowledge discovery in Databases',1, 11) AS StringOutput
O/p : StringOutput
---------------------
Data Mining
23) UNICODE ( 'ncharacter_expression' ): Returns the integer value, as defined
by the Unicode standard, for the first character of the input expression.
EX: SELECT UNICODE('a') AS COLUMN0,UNICODE('A') AS COLUMN1,UNICODE('0')
O/p:
COLUMN0 COLUMN1 COLUMN2
97 65 48
24) FORMAT (value, format [, culture ] ): Returns a value formatted with the specified format. Culture is optional in this function.
DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/mm/yyyy') AS 'DateTime Result'
,FORMAT(123456789,'###-##-####') AS 'Custom Number Result'
,FORMAT(@d,'d') AS Date1
,FORMAT(@d,'D') AS Date2;
DateTime Result Custom Number Result Date1 Date2
29/25/2012 123-45-6789 10/29/2012 Monday, October 29, 2012
25) CONCAT (string_value1, string_value2 [, string_valueN ] ) : Returns a string that is the result of concatenating two or more string values.
EX: SELECT CONCAT ( 'SQL ', 'Server ', 2012, ' is the latest version') AS Result;
O/p: Result
SQL Server 2012 is the latest version
TOP 5 Exciting New Features of SQL Server 2012
Microsoft understands the importance of sql server and is continually striving to make powerful yet easy-to-use tool for Sqlserver developers and end users.
In this Part I will discuss exciting new features related to developer and SQL Server enhancement.
#1 Column Store Indexes
SQL Server 2012 introduced a new feature Column store indexes that can be used to improve query performance from 10x to 100x times. The xVelocity technology makes the columnstore index more efficient than a traditional index.
In a regular index, all indexed data from each row is kept together on a single page, and the data in each column is spread across all pages in an index. In a columnstore index, the data from each column is kept together so that each data page contains data only from a single column. In addition, the indexed data for each column is compressed (means many columns contains repetitive values).The compression ration can be very high and It also reduces the number of pages in memory.
Building a columnstore index is simple. You use the same index creation syntax and specify the keyword COLUMNSTORE. But once you add a columnstore index to a table, the table becomes read-only, so inserts, updates, and deletes are not allowed. If you need to insert or update rows, you can disable the index, make the modifications, and rebuild the columnstore index.
It is greatly reducing I/O and memory utilization on large queries and creating aggregate tables.
Syntax:
CREATE NONCLUSTERED COLUMNSTORE INDEX ON (Col1,Col2........Coln)
Example:
CREATE NONCLUSTERED INDEX INDEX_1 ON EmployeeDetail(ID, Name)
#2 Sequence Objects
Example:
CREATE NONCLUSTERED INDEX INDEX_1 ON EmployeeDetail(ID, Name)
#2 Sequence Objects
A Sequence is just an object that is a counter same as similar functionality of an identity column, there is an interesting option to utilize called Sequence. Sequence is a user-defined object that creates a sequence number. A good example of its use would be to increment values in a table, based a trigger.
Syntax:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH
[ INCREMENT BY
[ { MINVALUE [
[ { MAXVALUE [
[ CYCLE | { NO CYCLE } ]
[ { CACHE [
[ ; ]
• START WITH: Starting number in the sequence
• INCREMENT BY: The incrementing value of the sequence
• MINVALUE: The minimum value the sequence can produce.
• MAXVALUE: The maximum value the sequence can produce.
• CYCLE: If the MAXVALUE is set with the CYCLE, when the MAXVALUE is reached the sequence will cycle to the MINVALUE and start again.
• CACHE: If a CACHE argument is provided, SQL Server will cache (store in memory) the amount of values specified.
#3 Error Handling
If you have programming in languages like C# or other similar languages then you are probably know how to handle errors by using try, catch and throw statements. Transact-SQL also gives you this option to find an exception using a try/catch block.
In SQL Server 2005/2008, RAISERROR has remained the only mechanism for generating your own errors. In SQL Server 2012, a new THROW statement is used to raise exceptions in your T-SQL code instead of RAISERROR.
Example:
We divide a number by zero:
In SQL Server 2005/2008, if you want to re-throw an error in a catch block of TRY CATCH statement, you need to use RAISERROR with ERROR_MESSAGE() AND ERROR_SEVERITY().
BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 12/ 0
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(4000), @ErrorSeverity int
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY()
RAISERROR ( @ErrorMessage, @ErrorSeverity, 1 )
END CATCH
But in SQL Server 2012 you can use only a THROW statement to re-throw an error in a catch block of a TRY CATCH statement.
BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 12/ 0
END TRY
BEGIN CATCH
throw
END CATCH
#4 New T-SQL Functions
There are many new functions added to SQL Server 2012 as shown below
Logical Functions
• CHOOSE (Transact-SQL)
• IIF (Transact-SQL) ....................................More>>
Conversion Functions
• PARSE (Transact-SQL)
• TRY_PARSE (Transact-SQL)
• TRY_CONVERT (Transact-SQL) ..........................More>>
Date and time Functions
• DATEFROMPARTS Function
• TIMEFROMPARTS Function
• DATETIMEFROMPARTS Function
• EMONTH Function
....... and so on .............................More>>
String Functions
• FORMAT (Transact-SQL)
• CONCAT (Transact-SQL) ......
Analytic Functions
• First_Value Function
• Last_Value Function
#5 Pagination
In the earlier versions of SQL Server, if you use a Gridview then you set its pagination property. However if you want to do this from the server side then you need to use the row_number() function and supply the specific range of rows and also retrieve the current page data from the database using a temporary table. The ORDER BY OFFSET & FETCH NEXT ONLY keywords are one of the major features introduced in SQL Server 2012.
Briefly explain as follows
Creating a Table in SQL Server
create table Userdetails
(id int identity (1,1),
firstname varchar(30),
lastname varchar(30))
insert into Userdetails values('John','K')
insert into Userdetails values('Crown','P')
insert into Userdetails values('Lee','B')
insert into Userdetails values('Khan','S')
insert into Userdetails values('Amith','N')
insert into Userdetails values('Nelson','M')
insert into Userdetails values('Maro','T')
insert into Userdetails values('Alijibit','R')
insert into Userdetails values('Francko','S')
select * from Userdetails
The table looks like as follows
To get rows in between 4 to 8
In SQL Server 2008, we have been doing this data paging by writting a complex query as follows.
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id) AS number, *
FROM Userdetails) AS TempTable
WHERE number > 4 and number <= 8
First_Value and Last_Value
Overview
SQL Server 2012 introduces following analytical functions
- CUM_DIST & Percentile_DIST
- FIRST_Value & Last_Value
- LAG & LEAD
- Percent_Count & Percent_Range
First_Value & Last_Value functions allow you to get the same value for the first and last row for all records in a result set, it will also be useful in a scenario where you want to compare particular thing with max and minimum value of the same thing.
Syntax
FIRST_VALUE ( [scalar_expression ] )
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
Scenarios & Example
- Find out highest and lowest value for particular resultset
Let's say we have a table called "Marks" which contain the marks of each student, each subject. and we want to compare marks of particular subject for each student with highest and lowest marks then this is how we can get it,
SELECT StudentName, Marks, FIRST_VALUE (Marks) OVER (ORDER BY Marks)LowestMarks
, LAST_VALUE (Marks) OVER (ORDER BY Marks) HighestMarksSoFar
, LAST_VALUE (Marks) OVER (ORDER BY Marks ROWS BETWEEN UNBOUNDEDPRECEDING AND UNBOUNDED FOLLOWING) HighestMarks
FROM Marks
WHERE SubjectName = 'Maths'
StudentName
|
Marks
|
LowestMarks
|
HighestMarksSoFar
|
HighestMarks
|
Rob
|
67
|
67
|
67
|
100
|
Ryan
|
97
|
67
|
97
|
100
|
Bob
|
98
|
67
|
98
|
100
|
John
|
100
|
67
|
100
|
100
|
We can see that, when we use "Rows Between Unbound Preceding and Unbound Following" in LAST_Value function, then we are getting actual Highest value from that result-set otherwise it gives the highest value whatever we have got so far. This is NOT the case for First_Value function
- Find out highest and lowest value using Partition By clause
Let's say we want to get above result for all the subjects then in this case, we need to use Partition By clause.
SELECT SubjectName, StudentName, Marks, FIRST_VALUE (Marks) OVER (PARTITION BYSubjectName ORDER BY Marks) LowestMarks
, LAST_VALUE (Marks) OVER (PARTITION BY SubjectName ORDER BY Marks ROWSBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HighestMarks
FROM Marks
order by 1,3
SubjectName
|
StudentName
|
Marks
|
LowestMarks
|
HighestMarks
|
English
|
Ryan
|
89
|
89
|
99
|
English
|
Bob
|
93
|
89
|
99
|
English
|
John
|
94
|
89
|
99
|
English
|
Rob
|
99
|
89
|
99
|
Maths
|
Rob
|
67
|
67
|
100
|
Maths
|
Ryan
|
97
|
67
|
100
|
Maths
|
Bob
|
98
|
67
|
100
|
Maths
|
John
|
100
|
67
|
100
|
Science
|
Rob
|
87
|
87
|
98
|
Science
|
Ryan
|
94
|
87
|
98
|
Science
|
John
|
98
|
87
|
98
|
Science
|
Bob
|
98
|
87
|
98
|
Lag and Lead
Overview
SQL Server 2012 introduces following analytical functions
- CUM_DIST & Percentile_DIST
- FIRST_Value & Last_Value
- LAG & LEAD
- Percent_Count & Percent_Rank
Out of these, LAG & LEAD is very useful in almost all applications, where we need to find previous and next value based on a specific result-set and we need to do the self-join, LAG & LEAD completely gets rid off this self join and makes it very easy.
Syntax
LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )
LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
Scenarios & Examples
- Compare values between years
Let's say we have TotalSales per year for each product and now I want to compare with previous year sales and next year sales. look at the below query and we can know how easy it is to derive using these functions, otherwise we need to do self-joins and it is always painful.
SELECT ProductID, SaleYear, TotalSales, LAG(TotalSales,1,0) OVER (Order by SaleYear)PrevYearSales,
LEAD(TotalSales,1,0) OVER (Order by SaleYear) NextYearSales
FROM ProductSales
WHERE ProductID = 2
ProductID
|
SaleYear
|
TotalSales
|
PrevYearSales
|
NextYearSales
|
2
|
2008
|
9000
|
0
|
96570
|
2
|
2009
|
96570
|
9000
|
67800
|
2
|
2010
|
67800
|
96570
|
78700
|
2
|
2011
|
78700
|
67800
|
90565
|
2
|
2012
|
90565
|
78700
|
0
|
0 is the default value, we will get 0 if function cannot derive anything.
- Use LAG, LEAD along with GROUP By
Let's say, we want to find out aggregated value of TotalSales per year and we want to compare with previous and next year, then here is the query
SELECT SaleYear, SUM(TotalSales) TotalSales, LAG(SUM(TotalSales),1,0) OVER (Orderby SaleYear) PrevYearSales,
LEAD(SUM(TotalSales),1,0) OVER (Order by SaleYear) NextYearSales
FROM ProductSales
group by SaleYear
SaleYear
|
TotalSales
|
PrevYearSales
|
NextYearSales
|
2008
|
28000
|
0
|
232070
|
2009
|
232070
|
28000
|
142600
|
2010
|
142600
|
232070
|
136500
|
2011
|
136500
|
142600
|
174465
|
2012
|
174465
|
136500
|
0
|
- Use LAG, LEAD within Partition
Let's say, we want to compare totalsales with previous and next year for all the products, then we can achieve this by integrating Partition by clause
SELECT ProductID, SaleYear, TotalSales, LAG(TotalSales,1,0) OVER (Partition ByProductID Order by SaleYear) PrevYearSales,
LEAD(TotalSales,1,0) OVER (Partition By ProductID Order by SaleYear) NextYearSales
FROM ProductSales
ProductID
|
SaleYear
|
TotalSales
|
PrevYearSales
|
NextYearSales
|
1
|
2008
|
10000
|
0
|
10400
|
1
|
2009
|
10400
|
10000
|
47000
|
1
|
2010
|
47000
|
10400
|
14500
|
1
|
2011
|
14500
|
47000
|
78000
|
1
|
2012
|
78000
|
14500
|
0
|
2
|
2008
|
9000
|
0
|
96570
|
2
|
2009
|
96570
|
9000
|
67800
|
2
|
2010
|
67800
|
96570
|
78700
|
2
|
2011
|
78700
|
67800
|
90565
|
2
|
2012
|
90565
|
78700
|
0
|
3
|
2008
|
8000
|
0
|
67800
|
3
|
2009
|
67800
|
8000
|
4500
|
3
|
2010
|
4500
|
67800
|
9000
|
3
|
2011
|
9000
|
4500
|
4500
|
3
|
2012
|
4500
|
9000
|
0
|
4
|
2008
|
1000
|
0
|
57300
|
4
|
2009
|
57300
|
1000
|
23300
|
4
|
2010
|
23300
|
57300
|
34300
|
4
|
2011
|
34300
|
23300
|
1400
|
4
|
2012
|
1400
|
34300
|
0
|
Sequence
Overview
Introduced in SQL Server 2012
User-defined object which generates sequence of numbers according to the property (Ascending/Descending/Cycle, etc..)
It can be defined as any Integer datatype, if not specified, default is BigInt
Sequence vs Identity
Identity
|
Sequence
| |
Association
|
With table
|
with database, can be used for multiple tables
|
Generation
|
generated only when we insert the data in table
|
can be generated even before inserting data using NEXT VALUE FOR
|
Cycling
|
not feasible
|
feasible, will restart the number once specified number is reached
|
Reserving number
|
not feasible, if we insert data from multiple places, it will take the number whatever is next
|
feasible, we can reserver 5 sequential numbers before inserting the data and later we can use them
|
Gaps
|
Generally no unless we have deliberately changed the Identity number, otherwise records will be in a sequence without gaps
|
One of the purpose of sequence is for multiple tables, gaps are usual
|
Usage
- Creating a sequence
CREATE SEQUENCE Schema.SequenceName
AS int
START WITH 1
INCREMENT BY 1 ;
- Using sequence while inserting data
--Create the Test schema
CREATE SCHEMA Test ;
GO
-- Create a table
CREATE TABLE Test.Orders
(OrderID int PRIMARY KEY,
Name varchar(20) NOT NULL,
Qty int NOT NULL);
GO
-- Create a sequence
CREATE SEQUENCE Test.CountBy1
START WITH 1
INCREMENT BY 1 ;
GO
-- Insert three records
INSERT Test.Orders (OrderID, Name, Qty)
VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;
INSERT test.Orders (OrderID, Name, Qty)
VALUES (NEXT VALUE FOR Test.CountBy1, 'Seat', 1) ;
INSERT test.Orders (OrderID, Name, Qty)
VALUES (NEXT VALUE FOR Test.CountBy1, 'Brake', 1) ;
GO
- Calling NEXT VALUE FOR before inserting the data
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Test.CountBy1;
-- Some work happens
INSERT Test.Orders (OrderID, Name, Qty)
VALUES (@NextID, 'Rim', 2) ;
- Using Sequence in multiple tables
CREATE SCHEMA Audit ;
GO
CREATE SEQUENCE Audit.EventCounter
AS int
START WITH 1
INCREMENT BY 1 ;
GO
CREATE TABLE Audit.ProcessEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EventCode nvarchar(5) NOT NULL,
Description nvarchar(300) NULL
) ;
GO
CREATE TABLE Audit.ErrorEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EquipmentID int NULL,
ErrorNumber int NOT NULL,
EventDesc nvarchar(256) NULL
) ;
GO
CREATE TABLE Audit.StartStopEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EquipmentID int NOT NULL,
StartOrStop bit NOT NULL
) ;
GO
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES (248, 0) ;
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES (72, 0) ;
INSERT Audit.ProcessEvents (EventCode, Description)
VALUES (2735,
'Clean room temperature 18 degrees C.') ;
INSERT Audit.ProcessEvents (EventCode, Description)
VALUES (18, 'Spin rate threashold exceeded.') ;
INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)
VALUES (248, 82, 'Feeder jam') ;
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES (248, 1) ;
INSERT Audit.ProcessEvents (EventCode, Description)
VALUES (1841, 'Central feed in bypass mode.') ;
-- The following statement combines all events, though not all fields.
SELECT EventID, EventTime, Description FROM Audit.ProcessEvents
UNION SELECT EventID, EventTime, EventDesc FROM Audit.ErrorEvents
UNION SELECT EventID, EventTime,
CASE StartOrStop
WHEN 0 THEN 'Start'
ELSE 'Stop'
END
FROM Audit.StartStopEvents
ORDER BY EventID ;
EventID
|
EventTime
|
Description
|
1
|
2013-07-28 06:10:36.673
|
Start
|
2
|
2013-07-28 06:10:36.677
|
Start
|
3
|
2013-07-28 06:10:36.677
|
Clean room temperature 18 degrees C.
|
4
|
2013-07-28 06:10:36.680
|
Spin rate threashold exceeded.
|
5
|
2013-07-28 06:10:36.680
|
Feeder jam
|
6
|
2013-07-28 06:10:36.680
|
Stop
|
7
|
2013-07-28 06:10:36.680
|
Central feed in bypass mode.
|
We can also see that we can define NEXT VALUE FOR as a default constraint, so that way we don't need to use it while inserting the data
- Repeating Sequence
CREATE SEQUENCE CountBy5
AS tinyint
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE ;
GO
SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROMsys.objects ;
GO
SurveyGroup
|
Name
|
1
|
sysrscols
|
2
|
sysrowsets
|
3
|
sysclones
|
4
|
sysallocunits
|
5
|
sysfiles1
|
1
|
sysseobjvalues
|
2
|
syspriorities
|
- Generating Sequence using OVER clause
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) ASNutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
- Resetting the sequence
ALTER SEQUENCE Samples.IDLabel
RESTART WITH 1 ;
Offset & Fetch
Overview
This is the new feature introduced in SQL 2012.
OFFSET Specifies the number of rows to skip
FETCH Specifies the number of rows to return
Basically by using OFFSET & FETCH, we can get selected result in a particular order while sorting the data. So these are used along with ORDER By clause.
Usage
Here is the basic query.
SELECT TOP 5 * FROM Students ORDER BY StudentName
StudentID
|
StudentName
|
City
|
5
|
Dipa
|
Hyderabad
|
6
|
Kamlesh
|
Mumbai
|
8
|
Lalu
|
Hyderabad
|
9
|
Mahesh
|
Ahmedabad
|
4
|
Maulik
|
Redmond
|
Now, I want the result starting from 3rd row and I want to get total 4 rows starting from 3rd row, basically while sorting, I want to ignore first 2 rows, get only 4 rows and also want to ignore rest of the rows, then this is how I can achieve this
SELECT * FROM Students ORDER BY StudentName OFFSET 2 ROWS FETCHNEXT 4 ROWS ONLY
StudentID
|
StudentName
|
City
|
8
|
Lalu
|
Hyderabad
|
9
|
Mahesh
|
Ahmedabad
|
4
|
Maulik
|
Redmond
|
2
|
Megha
|
Redmond
|
I can also use variables instead of static value in OFFSET and FETCH
DECLARE @OFFSET INT = 2
DECLARE @FETCH INT = 3
SELECT * FROM Students ORDER BY StudentName OFFSET @OFFSET ROWSFETCH NEXT @FETCH + 1 ROWS ONLY
It will give me same result what we got earlier.
Limitation
- ORDER BY is mandatory
- OFFSET is mandatory with FETCH.. You cannot use only 1
- TOP cannot be combined with OFFSET & FETCH
OFFSET/FETCH vs ROW_NUMBER()
- First of all query is much simpler while using OFFSET/FETCH than ROW_Number, here is the comparison. Below query will give the same result with the query what we used earlier.
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER(ORDER BY StudentName)RowNumber, *
FROM Students
)
SELECT *
FROM CTE
WHERE RowNumber BETWEEN 3 AND 6
- And if we look at the execution plan, then we can see, there is lot more going on while using ROW_Number than Offset/Fetch
EXECUTE in MS SQL 2012
Introduction
In MS SQL server 2008 and earlier version we are all familiar with Execute statement (EXEC). The EXEC is used to execute a dynamic query or a Stored Procedure (SP). But in MS SQL 2012 it has some improvement. In this article we try to explore it.
Why we Use EXEC
Here I am using EXEC to execute a stored procedure.
Here is my base table
IF OBJECT_ID(N'dbo.tbl_STUDENT', N'U')IS NOT NULL
BEGIN
DROP TABLE dbo.tbl_STUDENT;
END
GO
CREATE TABLE dbo.tbl_STUDENT
(STDROLLNO INT NOT NULL IDENTITY PRIMARY KEY,
STDNAME VARCHAR(50) NOT NULL);
GO
INSERT INTO dbo.tbl_STUDENT
(STDNAME)
VALUES ('Joydeep Das'),('Archita Dutta'), ('Ranajit Dhar');
GO
Here the Table
SELECT * FROM dbo.tbl_STUDENT;
STDROLLNO STDNAME
1 Joydeep Das
2 Archita Dutta
3 Ranajit Dhar
Now we create the stored procedure
IF OBJECT_ID(N'dbo.sproc_GETSTUDENT', N'P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sproc_GETSTUDENT;
END
GO
--Creating SP
CREATE PROCEDURE dbo.sproc_GETSTUDENT
AS
BEGIN
SELECT STDROLLNO, STDNAME FROM dbo.tbl_STUDENT;
END
GO
Here we Execute the Stored procedure
EXEC dbo.sproc_GETSTUDENT
STDROLLNO STDNAME
1 Joydeep Das
2 Archita Dutta
3 Ranajit Dhar
Look the output of the EXEC statement when we execute the stored procedure. The columns names are same that we used in the stored procedure.
If we want to change the column name we must alter the stored procedure by providing the alias name in the columns.
So what's New in MS SQL 2012
In MS SQL 2012 we can change the execution columns name which is executed by EXECUTE statement.
MS SQL Server 2012, improved version of EXECUTE keyword allows to modify the result set with changing the column name and data type without modifying the stored procedure.
Here is the Example
EXEC sproc_GETSTUDENT
WITH RESULT SETS
(
(
STUDENTROLLNO INT,
NAMEOFSTUDENT VARCHAR(50)
)
);
STUDENTROLLNO NAMEOFSTUDENT
1 Joydeep Das
2 Archita Dutta
3 Ranajit Dhar
Hope you like it.
PARSE () in SQL 2012
Introduction
PARSE () function is Introduced at MS SQL Server 2012. Here in this article we are trying to illustrate the PARSE () function.
Why the PARSE ()
The PARSE () function is used to convert staring value to Numeric or Date/Time format. Please remember that PARSE () function can convert only string value. If the string value cannot converted to Numeric or Date/Time format it will provide an Error.
What it needs
PARSE () function needs common language run time (CLR) to convert sting value to Numeric or Date/Time format. If the CLR is not installed the PARSE () returns an Error.
To install CLR use
Syntax of PARSE ()
PARSE ( string_value As data_type [Using culture])
String_value - The expression which needs to be parsed.
Data_type - To which data type we are converting to.
CULTUre - To which culture, i.e., language such as gb-en, us-en. This is an optional parameter.
Culture part of the function is optional. Language like English, Japanese, Spanish, Danish, French etc. to be used by MS SQL Server to interpret data. If the culture is not specified the culture of the current session is used. The culture can be any of the .NET supported cultures.
Example of PARSE ()
SELECT PARSE('08-04-2013' AS DATETIME USING 'en-US') AS Date
Date
2013-08-04 00:00:000
SELECT PARSE('200.000' AS INT) AS ValueInt
ValueInt
200
SELECT PARSE('July 30, 2013' AS DATETIME)AS ValueDT
ValueDT
2013-07-30 00:00:00.000.
Error Handling with THROW clause at SQL 2012
Introduction
Before MS SQL 2005 developer knows how hard to trap an error by using @@ERROR variable value. But from MS SQL 2005 we use TRY… CATCH blog to trap an error. It is so easy that all the SQL developer is just flying on the sky.
So what's new in MS SQL 2012
As a simple question is that what's new at MS SQL 2012 for Error handling as we are very happy with MS from MS SQL 2005 onward.
Yes MS is now trying us to send at Moon surface with SQL 2012. Just a recall of TRY …CATCH block of Error handling.
BEGIN
DECLARE @v_NO INT;
DECLARE @v_ErrSen INT,
@v_ErrMsg VARCHAR(max),
@v_ErrState INT;
BEGIN TRY
SET @v_NO = 1000;
SET @v_NO = @v_NO / 0;
END TRY
BEGIN CATCH
SET @v_ErrSen = ERROR_SEVERITY();
SET @v_ErrMsg = ERROR_MESSAGE();
SET @v_ErrState = ERROR_STATE();
RAISERROR(@v_ErrMsg, @v_ErrSen, @v_ErrState);
END CATCH
END
Please look at the BEGIN CATCH… END CATCH section. Here we use RAISERROR() which takes three parameters.
IN MS SQL 2012 There is no need to understand RAISERROR() just Use THROW only
Code sample is given bellow
BEGIN
BEGIN TRY
SET @v_NO = 1000;
SET @v_NO = @v_NO / 0;
END TRY
BEGIN CATCH
THROW;
END CATCH
END
Is it not easier then the complex RAISERROR(). A new developer can set it easily at SQL 2012.
Thanks to MS for that.
SEQUENCE a new Feature of MS SQL 2012
Introduction
A long time developer's demands for an object that can generate sequential number like ORACLE have. After long journey with MS SQL server MS finally think about it and provide SEQUENCE objects in MS SQL Server 2012.
In this article I am trying to understand the feature of SEQUENCE objects of MS SQL 2012.
What About IDENTITY
As the developers knows INDENTITY columns very well (it starts from MS SQL 2000) but there are lot of difference with SEQUENCE.
First we have to understand although the both IDENTITY and SEQUENCE generate sequential number but there are certain differences between them.
SEQUENCE is a database object but IDENTITY columns are tied to table. That means we can use the SEQUENCE objects in entire database. A single SEQUENCE object can be used with multiple Table objects to insert sequential values.
We can use SEQUENCE objects with T-SQL statement also.
How we can Create and use the SEQUENCE object
To use the SEQUENCE objects we must first create it
CREATE SEQUENCE [dbo].[MySequence] AS INT
START WITH 1
INCREMENT BY 1
GO
CREATE TABLE dbo.Tbl_Employee(ID INT, EmpNameVARCHAR(50))
GO
INSERT INTO dbo. Tbl_Employee
VALUES (NEXT VALUE FOR dbo.MySequence,'Joydeep Das'),
(NEXT VALUE FOR dbo.MySequence,'Archita Dutta'),
(NEXT VALUE FOR dbo.MySequence,'Rajesh Das')
GO
SELECT * FROM dbo.Tbl_Employee WITH(NOLOCK)
GO
ID Name
———– —————————–
1 Joydeep Das
2 Archita Dutta
3 Rajesh Das
Regenerate SEQUENCE Number
ALTER SEQUENCE dbo.MySequence
RESTART WITH 1 ;
GO
SELECT (NEXT VALUE FOR dbo.MySequence)
AS SequenceValue
GO
SequenceValue
———–
1
———–
1
Get the Current value of SEQUENCE
SELECT Current_Value
FROM SYS.Sequences
WHERE name='MySequence'
GO
Current_Value
——————
1
——————
1
OFFSET and FETCH in SQL 2012
Introduction
Hello friends after long time, I am trying to post something. It's related to MS SQL Server 2012 new T-SQL features called OFFSET and FETCH.
As it is introduce new at SQL 2012 but logic behind is OLD as we can do it by previous version of MS SQL. But before it is Littlemore tedious jobs. Here in MS SQL 2012 we can do in on the fly.
First Understand What is OFFSET and FETCH
Suppose we have a table objects called tbl_student
STUDROLL
|
STUDNAME
|
1
|
Joydeep
|
2
|
Tanay
|
3
|
Archita
|
4
|
Sudeshna
|
5
|
Tapash
|
6
|
Palash
|
Now we understand what OFFSET done. OFFSET provides a starting row from which to display the result set. FETCH instructs the query to display the number of rows you want in your result set from the OFFSET point.
We must remember that OFFSET and FETCH only worked with ORDER BY clause only.
Take a Simple Example to understand it
SELECT STUDROLL, STUDNAME
FROM tbl_student
ORDER BY STUDROLL
OFFSET 0 ROWS
FETCH NEXT 2 ROWS ONLY
So what it display as result
STUDROLL
|
STUDNAME
|
1
|
Joydeep
|
2
|
Tanay
|
Now
SELECT STUDROLL, STUDNAME
FROM tbl_student
ORDER BY STUDROLL
OFFSET 2 ROWS
FETCH NEXT 2 ROWS ONLY
So what it display as result
STUDROLL
|
STUDNAME
|
3
|
Archita
|
4
|
Sudeshna
|
Hope now we understand it.
How it worked with previous Version
SELECT a.STUDROLL, a.STUDNAME
FROM (SELECT ROW_NUMBER() OVER (ORDER BY STUDROLL)AS RNUM,
STUDROLL, STUDNAME
FROM tbl_student)AS a
WHERE a.RNUM > 2 AND a.RNUM < 5
Hope you like it.
SQL 2012 FORMAT for Date formatting
In SQL Server 2008 and earlier version used the CONVERT() function to handle the date formatting. As we notice that the function is not very user friendly.
In SQL Server 2012, a new function named FORMAT() has been introduced which is much easier to use.
First of all we have look about CONVERT() function and then we are going to new SQL Server 2012 FORMAT() function.
Some example of CONVERT() function is mentioned bellow
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM) -- Oct 2 2010 11:01AM
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) -- hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
-- Oct 2 2010 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
-- yyyymmdd - ISO date format - international standard - works with any language setting
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
-- 02 Oct 2010 11:02:07:577
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm - ISO8601
-- 2010-10-02T10:52:47.513
SELECT convert(varchar, getdate(), 127) -- yyyy-mm-ddThh:mm:ss.mmmZ - with time zone
SELECT convert(nvarchar(64), getdate(), 130) -- Arabic Hijri date, 15 جمادى الثانية 1433 2:40:09:210PM
SELECT convert(nvarchar, getdate(), 131) -- Arabic Hijri date-Islamic calendar, 15/06/1433 2:40:39:240PM
-- Without century (YY) date / datetime conversion - there are exceptions!
SELECT convert(varchar, getdate(), 0) -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(varchar, getdate(), 1) -- mm/dd/yy
SELECT convert(varchar, getdate(), 2) -- yy.mm.dd
SELECT convert(varchar, getdate(), 3) -- dd/mm/yy
SELECT convert(varchar, getdate(), 4) -- dd.mm.yy
SELECT convert(varchar, getdate(), 5) -- dd-mm-yy
SELECT convert(varchar, getdate(), 6) -- dd mon yy
SELECT convert(varchar, getdate(), 7) -- mon dd, yy
SELECT convert(varchar, getdate(), 8) -- hh:mm:ss
SELECT convert(varchar, getdate(), 9) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 10) -- mm-dd-yy
SELECT convert(varchar, getdate(), 11) -- yy/mm/dd
SELECT convert(varchar, getdate(), 12) -- yymmdd
SELECT convert(varchar, getdate(), 13) -- dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 14) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 20) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 21) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 22) -- mm/dd/yy hh:mm:ss AM (or PM)
SELECT convert(varchar, getdate(), 23) -- yyyy-mm-dd
SELECT convert(varchar, getdate(), 24) -- hh:mm:ss
SELECT convert(varchar, getdate(), 25) -- yyyy-mm-dd hh:mm:ss.mmm
-- SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd
SELECT convert(varchar(7), getdate(), 126) -- yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8) -- mon yyyy
SELECT substring(convert(varchar, getdate(), 120),6, 11) -- mm-dd hh:mm
In SQL Server 2012 the FORMAT function
In SQL Server 2012 introduced a new function named FORMAT to handle formatting dates. It is similar to ORACLE to_date() function.
The syntax of the FORMAT() function is mentioned bellow.
FORMAT(value , format [,culture])
Example of FORMAT()
SELECT FORMAT (getdate(), 'dd-MM-yy') as date -- 05-06-12
SELECT FORMAT (getdate(), 'hh:mm:ss') as time -- 2:48:42
With Culture Option
Culture is the another option of the FORMAT() function. By this we can obtain the regional formatting.
Example of USA format
SELECT FORMAT (getdate(), 'd', 'en-us') as date -- 6/5/2012
Example of Spanish culture in Bolivia
SELECT FORMAT (getdate(), 'd', 'es-bo') as date -- 5/6/2012
More Examples
Query
|
Sample output
|
select FORMAT (getdate(), 'dd/MM/yyyy ') as date
|
06/05/2012
|
select FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date
|
06/05/2012, 11:33:13
|
select FORMAT (getdate(), 'dddd, MMMM, yyyy') as date
|
Wednesday, May, 2012
|
select FORMAT (getdate(), 'MMM dd yyyy') as date
|
May 06 2012
|
select FORMAT (getdate(), 'MM.dd.yy') as date
|
05.06.12
|
select FORMAT (getdate(), 'MM-dd-yy') as date
|
05-06-12
|
select FORMAT (getdate(), 'hh:mm:ss tt') as date
|
11:36:14 AM
|
select FORMAT (getdate(), 'd','af') as date
|
2012/05/06
|
Hope you like it.
SEQUENCE in SQL 2012
SQL Server 2012 code name "Denali" introduced a new feature of T-SQL to make task easier. It is called Sequence. It generates a sequence of number.
In previous version we can specify identity fields in a table. But if we want to have database wide sequential number, then we must derive something by our self before SQL server 2012.
It was a long request by SQL community and Microsoft release it with version of SQL Server 2012.
The syntax is mentioned bellow:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
A partial list of the output demonstrates the default values.
start_value
|
-9223372036854775808
|
increment
|
1
|
mimimum_value
|
-9223372036854775808
|
maximum_value
|
9223372036854775807
|
is_cycling
|
0
|
is_cached
|
1
|
current_value
|
-9223372036854775808
|
Example to create Sequence is mentioned bellow.
CREATE SEQUENCE mysequence
START WITH 1
INCREMENT BY 1;
GO
BEGIN TRAN
SELECT NEXT VALUE FOR dbo.mysequence
ROLLBACK TRAN
Another example with Table objects are mentioned bellow.
----Create Sequence Object
CREATE SEQUENCE my_seq
START WITH 1
INCREMENT BY 1;
----Create Temp Table
DECLARE @Customer_dtl TABLE
(
ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL
);
----Insert Some Data
INSERT @Customer_dtl (ID, FullName)
VALUES (NEXT VALUE FOR my_seq, 'Joydeep Das'),
(NEXT VALUE FOR my_seq, 'Sudip Das'),
(NEXT VALUE FOR my_seq, 'Subarata Kar');
----Show the Data
SELECT * FROM @Customer_dtl;
The "NEXT VALUE FOR" T-SQL key words is used to get the next sequential number from sequence
Hope you like it.
SPACE() Function in SQL
Some of my friends has a lot of confusions about the functions SPACE() and it's implementations.
The SPACE function takes Integer as parameters and Returns a specified number of spaces as string.
The number of space it returns is what number you provide in the parameters.
The syntax is
SPACE(expression int)RETURN varchar
EXAMPLE
SELECT fname +','+SPACE(2)+lname 'Student Name'
FROM tbl_student
RETURN VALUE
Student Name
RAJA, KUMAR
DIA, MIRZA
The Spce came in between , and Last name.
