|
|
-
|
|
UPDATE() function can be used to determine whether an INSERT or UPDATE was made on the specified column.
You can use it inside a trigger to test if a column has been updated by INSERT/UPDATE statement.
for example, I have below table:
-- © 2011 – Vishal (http://SqlAndMe.com)
USE Sq......
|
|
-
|
|
@@IDENTITY is a system function that can be used to retrieve last inserted identity value. @@IDENTITY is not limited to scope.
for example, I have a Table which contains an identity column as below:
-- © 2011 – Vishal (http://SqlAndMe.com)
USE [SqlAndMe]
CREATE TABLE [dbo].[Temp......
|
|
-
|
|
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:
--......
|
|