Tips for SQL Server


Showing the SQL Statistics in XML

To show the query plan

SET STATISTICS XML { ON | OFF }

To Show the execution times for a query, wrap the query using the below

SET STATISTICS TIME ON;

SELECT * FROM USERS;

SET STATISTICS TIME OFF;

Sample Query with CTE

WITH URT_CTE (AssignedTo, RoleId, AssignedBy,UserId)
AS
(
SELECT distinct urt.userroletenant_stratifiedtenantid AS AssignedTo,ur.userrole_roleid AS RoleId, urt.userroletenant_roleassigningtenantid AS AssignedBy, Ud.User_UserId as UserId
FROM userroletenant AS urt
INNER JOIN userroles AS ur ON ur.userrole_id = urt.userroletenant_userroleid
INNER JOIN userdetails AS ud ON ud.user_userid = ur.userrole_userid AND ud.user_userid=@userId
WHERE urt.userroletenant_roleassigningtenantid=@tenantid
)

Select AssignedTo From URT_CTE

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s