|
|
-
|
|
While you can use sys.dm_db_file_space_usage to get the free space available in tempdb. you can use sys.dm_db_session_space_usage to get the used space by each session.
sys.dm_db_session_space_usage contains following columns:
1. session_id – session_id
2. database_id – will always be......
|
|
-
|
|
You can use the sys.dm_db_file_space_usage dynamic management view to monitor the spaced used by tempdb for storing user/internal objects and version store.
sys.dm_db_file_space_usage has following columns:
1. database_id – will always be 2, database id of tempdb.
2. unallocated_extent_page......
|
|
-
|
|
COALESCE() can be used to return first non-null value from the specified arguments. A typical scenario to use COALESCE() is when you need to retrieve value from another column when the primary column you need is NULL. for example, you need to retrieve all values from ColumnA, but in cases where Colu......
|
|
-
|
|
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......
|
|