Archive

Posts Tagged ‘system function’

Using SQL DMVs – sys.dm_db_task_space_usage

08.06.2011 No comments

sys.dm_db_task_space_usage contains details for allocation/de-allocation pages for activity tasks for database.

It returns below columns:

1. session_id – session id

2. request_id – request id within the session, i.e. execution batch within a session

3. database_id – will always be 2, id of tempdb

4. user_objects_alloc_page_count – number pages allocated for the request

5. user_objects_dealloc_page_count – number of pages de-allocated for the request.

for other columns returned, refer BOL…

 

sys.dm_db_task_space_usage returns only active tasks:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      session_id, request_id,

            user_objects_alloc_page_count,

            user_objects_dealloc_page_count

FROM        sys.dm_db_task_space_usage

Partial Result Set:

session_id request_id  user_objects_alloc_page_count user_objects_dealloc_page_count

———- ———– —————————– ——————————-

1          0           0                             0

2          0           0                             0

3          0           0                             0

4          0           0                             0

5          0           0                             0

6          0           0                             0

7          0           1                             0

8          0           0                             0

 

When a query is executed which requires storage in tempdb, this DMV will return the number of pages allocated for the request.

For example, below query will require tempdb storage:

— Session ID = 56

 

USE AdventureWorks2008R2

 

SELECT      A.*

INTO        #TempTable

FROM        HumanResources.Employee A

CROSS APPLY HumanResources.Employee B

CROSS APPLY HumanResources.Employee C

You can check sys.dm_db_task_space_usage while the above query is running to check the pages allocated to the request.

SELECT      session_id, request_id,

            user_objects_alloc_page_count,

            user_objects_dealloc_page_count

FROM        sys.dm_db_task_space_usage

WHERE       session_id = 56

Result Set:

session_id request_id  user_objects_alloc_page_count user_objects_dealloc_page_count

———- ———– —————————– ——————————-

56         0           8193                          0

 

(1 row(s) affected)

Once the request is completed this row will no longer be available.

To check pages allocated to a session you can use sys.dm_db_session_space_usage.

Hope This Helps!

Vishal

Using Built-in system functions – COALESCE()

03.06.2011 1 comment

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 ColumnA is NULL, you need values from ColumnB.

I have below table which contains NULL values for some rows:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      Title, FirstName, LastName

FROM        dbo.Contacts

Result Set:

Title    FirstName        LastName

——– ————————————————– ————————————————–

Ms.      Gail        Erickson

Mr.      NULL        Goldberg

Ms.      NULL        Galvin

Ms.      Jill        NULL

Mr.      Hung-Fu     NULL

 

(5 row(s) affected)

Now, if I need retrieve single name, I can achieve that by using COALESCE().

SELECT      Title, COALESCE(FirstName,LastName) AS Name

FROM        dbo.Contacts

Result Set:

Title    Name

——– ————————————————–

Ms.      Gail

Mr.      Goldberg

Ms.      Galvin

Ms.      Jill

Mr.      Hung-Fu

 

(5 row(s) affected)

The same output can be achieved by using a CASE expression as well:

SELECT      Title,

            CASE

                  WHEN FirstName IS NOT NULL THEN FirstName

                  WHEN LastName  IS NOT NULL THEN LastName

            END AS Name

FROM        dbo.Contacts

Result Set:

Title    Name

——– ————————————————–

Ms.      Gail

Mr.      Goldberg

Ms.      Galvin

Ms.      Jill

Mr.      Hung-Fu

 

(5 row(s) affected)

Hope This Helps!

Vishal

Using Built-in system functions – UPDATE()

02.06.2011 2 comments

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 SqlAndMe

 

CREATE TABLE dbo.Salary

(

      ID          INT,

      Name        VARCHAR(50),

      Salary      MONEY

)

And I have also created on the table for an update as below:

CREATE TRIGGER Updated_Salary

ON dbo.Salary

AFTER UPDATE

AS

IF ( UPDATE (Salary) )

BEGIN

ROLLBACK

END

 

INSERT INTO dbo.Salary VALUES (1, 'Vishal', 1000)

Now if someone tries to update the Salary column, a message will be displayed as below:

UPDATE      dbo.Salary SET Salary = 2000

WHERE       ID = 1

Result Set:

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

However, other columns can be updated without any issues.

UPDATE      dbo.Salary SET Name = 'Gajjar'

WHERE       ID = 1

Result Set:

 

(1 row(s) affected)

Hope This Helps!

Vishal

Using Built-in system functions – IDENT_CURRENT(), IDENT_INCR(), IDENT_SEED()

31.05.2011 No comments

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

 

SELECT      IDENT_SEED('Production.Product') AS 'Seed',

            IDENT_INCR('Production.Product') AS 'Increment'

Result Set:

Seed                                    Increment

————————————— —————————————

1                                       1

 

(1 row(s) affected)

IDENT_SEED function returns the 'original' seed value even if it has been changed using DBCC CHECKIDENT.

DBCC  CHECKIDENT ('Production.Product' , RESEED, 1001)

SELECT      IDENT_SEED('Production.Product') AS 'Seed',

            IDENT_INCR('Production.Product') AS 'Increment'

Result Set:

Checking identity information: current identity value ‘1001’, current column value ‘1001’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Seed                                    Increment

————————————— —————————————

1                                       1

 

(1 row(s) affected)

To retrieve the current identity value that was used we can use IDENT_CURRENT function.

SELECT      IDENT_CURRENT('Production.Product') AS 'Current Value'

Result Set:

Current Value

—————————————

1001

 

(1 row(s) affected)

Hope This Helps!

Vishal

Using Built-in system functions – LEN() and DATALENGTH()

30.05.2011 No comments

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 type columns:

— © 2011 – Vishal (http://SqlAndMe.com)

 

CREATE TABLE dbo.TempTable

(

      NameNVarchar      NVARCHAR(64),

      NameVarchar       VARCHAR(64),

      NameChar          CHAR(64)

)

INSERT INTO dbo.TempTable VALUES

('Lincoln Burrows', 'Lincoln Burrows', 'Lincoln Burrows')

 

INSERT INTO dbo.TempTable VALUES

('Michael Scofield', 'Michael Scofield', 'Michael Scofield')

In above example, the first columns will store only 32 characters as it will be stored using Unicode. Also, the first two columns are of variable-length. And, the third columns will required fixed 64-bytes.

Now, to find out the string length we can use LEN():

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      LEN(NameVarchar)  AS 'LEN(VARCHAR)',

            LEN(NameNVarchar) AS 'LEN(NVARCHAR)',

            LEN(NameChar)     AS 'LEN(CHAR)'

FROM        dbo.TempTable

Result Set:

LEN(VARCHAR) LEN(NVARCHAR) LEN(CHAR)

———— ————- ———–

15           15            15

16           16            16

 

(2 row(s) affected)

But, the storage size will be different from string length due to different data types, which can be retrieved using DATALENGTH():

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      DATALENGTH(NameVarchar)  AS 'DATALENGTH(VARCHAR)',

            DATALENGTH(NameNVarchar) AS 'DATALENGTH(NVARCHAR)',

            DATALENGTH(NameChar)     AS 'DATALENGTH(CHAR)'

FROM        dbo.TempTable

Result Set:

DATALENGTH(VARCHAR) DATALENGTH(NVARCHAR) DATALENGTH(CHAR)

——————- ——————– —————-

15                  30                   64

16                  32                   64

 

(2 row(s) affected)

Hope This Helps!

Vishal

Using Built-in system function – IDENTITY()

27.05.2011 No comments

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 Manager ID:

SELECT      *

FROM        dbo.Departments

Result Set:

deptname                  deptmgrid

————————- ———–

HR                        2

Marketing                 7

Finance                   8

R&D                       9

Training                  4

Gardening                 NULL

 

(6 row(s) affected)

Now, if I need to add a new identity column to this table, I can do it using IDENTITY function.

An identity column can be added to above data using:

SELECT      IDENTITY(INT, 100, 2) AS deptid,

            deptname,

            deptmgrid

INTO        dbo.NewDepartments

FROM        dbo.Departments

Result Set:

(6 row(s) affected)

A new table dbo.NewDepartments is now created with an identity column – deptid.

SELECT      *

FROM        dbo.NewDepartments

Result Set:

deptid      deptname                  deptmgrid

———– ————————- ———–

100         HR                        2

102         Marketing                 7

104         Finance                   8

106         R&D                       9

108         Training                  4

110         Gardening                 NULL

 

(6 row(s) affected)

Hope This Helps!

Vishal

Using Built-in System Function – HOST_NAME()

26.05.2011 No comments

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:

——————- ——————-

VGAJJAR             VGAJJAR

 

(1 row(s) affected)

Same query executed from different machine gives the result:

——————- ——————-

VGAJJAR             VGAJJAR2

 

(1 row(s) affected)

However, HOST_NAME() function can not be used as a security feature, because client application can set the workstation name in a connection string, using WSID keyword.

For example,

image

Now, check the result again:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT @@SERVERNAME, HOST_NAME()

Result Set:

——————- ——————-

VGAJJAR             SERVER01

 

(1 row(s) affected)

Another useful function to know is HOST_ID() – which can be used to get the client process ID.

Hope This Helps!

Vishal

Using Built-in System Functions – NEWID() and NEWSEQUENTIALID()

25.05.2011 No comments

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

————————————

86E49A76-B4CE-4798-9091-B7E494C05705

 

(1 row(s) affected)

 

NEWSEQUENTIALID() generates a new GUID which is greater than previously generated GUID using NEWSEQUENTIALID(). NEWSEQUENTIALID() can only be used in a DEFAULT constraint for a table.

— © 2011 – Vishal (http://SqlAndMe.com)

 

CREATE TABLE TestTable

(

      INT_ID INT,

      Sequential_ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),

      Unique_ID     UNIQUEIDENTIFIER DEFAULT NEWID()

)

We can test this by adding a few rows:

INSERT INTO TestTable (INT_ID) VALUES (1), (2), (3), (4), (5)

SELECT * FROM TestTable

Result Set:

INT_ID     Sequential_ID                        Unique_ID

——— ———————————— ————————————

1         E326E023-B286-E011-8FC2-F4CE4697A63A EA44BE6E-A0EE-4A52-85BC-C24F6ADC4DD8

2         E426E023-B286-E011-8FC2-F4CE4697A63A D534CB28-922D-49C9-A51A-1CB8185CD662

3         E526E023-B286-E011-8FC2-F4CE4697A63A FFF87554-CFD2-46C9-B351-0A63F7552CCE

4         E626E023-B286-E011-8FC2-F4CE4697A63A 55219CF9-5D41-407D-95E5-BE9C4A013806

5         E726E023-B286-E011-8FC2-F4CE4697A63A B3C74453-05E7-42A3-8DD0-8FAA70F780A6

 

(5 row(s) affected)

 

Hope This Helps!

Vishal

Using Built-in System Function – $PARTITION

23.05.2011 No comments

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 VALUES (10, 20, 50)

Now, when you insert data to a table which uses a partition scheme which is based on this partition function, the data will be inserted according to partition number returned by the partition function.

If you need to check which partition does a column value belongs to, you can use $PARTITION function as below:

SELECT      $PARTITION.PartFunc01 (9),

            $PARTITION.PartFunc01 (11),

            $PARTITION.PartFunc01 (21),

            $PARTITION.PartFunc01 (51)

Result Set:

———– ———– ———– ———–

1           2           3           4

 

(1 row(s) affected)

It can also be used in a WHERE clause:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      ID, Name

FROM  dbo.FirstNames

WHERE $PARTITION.PartFunc01(ID) = 2

— Where 2 is the partition number

 

Hope This Helps!

Vishal