|
|
-
|
|
When an identity column is created, we need to specify the initial value and an increment value for the identity column.
You can retrieve the seed and increment values using IDENT_SEED and IDENT_INCR respectively.
-- © 2011 – Vishal (http://SqlAndMe.com)
USE AdventureWorks2008R2
......
|
|
-
|
|
While LEN() is used to return the number of characters in a String, DATALENGTH() can be used to return the number of bytes used by any expression.
LEN() and DATALENGTH() functions are useful for variable length data types.
To demonstrate these functions let us create a table with different data ty......
|
|
-
|
|
IDENTITY() function can be used to create a new Identity column for a table, You can only use this function in a SELECT statement with INTO clause.
Syntax:
IDENTITY ( data type, seed, increment ) AS Column Name
for example,
I have the following table with two columns – Department Name and ......
|
|
-
|
|
You can use the HOST_NAME() function to get the client workstation name from which the query is executed.
You can use it as:
-- © 2011 – Vishal (http://SqlAndMe.com)
SELECT @@SERVERNAME, HOST_NAME()
Result Set:
——————- ———&md......
|
|
-
|
|
You can use NEWID() and NEWSEQUENTIALID() to create a unique value of type UNIQUEIDENTIFIER.
You can use the NEWID() as any other TSQL function as below:
-- © 2011 – Vishal (http://SqlAndMe.com)
SELECT NEWID() AS 'Unique ID
Result Set:
Unique ID
————&mdash......
|
|
-
|
|
You can user the built-in configuration function @@SERVERNAME to get the server name using TSQL.
It can be used as below:
SELECT @@SERVERNAME AS 'Server Name'
Result Set:
Server Name
————————————————......
|
|
-
|
|
you can use @PARTITION TSQL function to check which partition will be mapped to a specified column value.
For example if you have a partition function with four partitions as below:
-- © 2011 – Vishal (http://SqlAndMe.com)
CREATE PARTITION FUNCTION PartFunc01 (INT)
AS RANGE FOR VAL......
|
|
-
|
|
To calculate the difference between two dates, you can use DATEDIFF() function. The DATEDIFF() function returns the number of days/month/years and time between two dates.
Syntax:
DATEDIFF (date part, start date, end date)
For example, to calculate time left till Independence Day, you can use:
--......
|
|
-
|
|
To add or subtract days from a date, you can simply use:
-- © 2011 – Vishal (http://SqlAndMe.com)
SELECT GETDATE() AS 'Today',
GETDATE() + 10 AS '10 Days Later',
GETDATE() - 10 AS '10 Days Earlier'
Result Set:
Today 10 Days Later ......
|
|
-
|
|
You can do this using two different ways. First is to us DAY(), MONTH() an YEAR() TSQL functions. These functions return an integer representing a day/month or year respectively.
These can be used as:
-- © 2011 – Vishal (http://SqlAndMe.com)
SELECT DAY ( GETDATE() ) AS 'Day',
......
|
|