SQL Server Journey with SQL Authority

I have previously written about how to get random rows from SQL Server.

• SQL SERVER – Generate A Single Random Number for Range of Rows of Any Table – Very interesting Question from Reader
• SQL SERVER – Random Number Generator Script – SQL Query

However, I have not blogged about following trick before. Let me share the trick here as well. You can generate random scripts using following methods as well.

USE AdventureWorks2012
GO
-- Method 1
SELECT TOP 100 *
FROM Sales.SalesOrderDetail
ORDER BY NEWID()
GO
-- Method 2
SELECT TOP 100 *
FROM Sales.SalesOrderDetail
ORDER BY CHECKSUM(NEWID())
GO

You will notice that using NEWID() in the ORDER BY will return random rows in the result set. How many of you knew this trick? You can run above script multiple times and it will give random rows every single time.

Watch a 60 second video on this subject

Note: This method can be very resource intensive for large resultsets.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »

Today let us start today’s blog post with a simple start question which I was asked by reader of my latest book SQL Server Interview Questions and Answers. Indeed a good question warrants a good answer with a script associated with the same.

Question: What is the difference between ORIGINAL_LOGIN() and SUSER_SNAME() and when will I use it?

Function ORIGINAL_LOGIN() returns the name of the original or very first login that connected to the instance of SQL Server and it is used to identity of the original login in sessions. If there is an application or database where context switching is happening quite often this can be a very useful feature for auditing. Let us see a quick T-SQL example where we initially retrieve current user and original login together you will notice that they are same. Afterwards we will create a new user and we will set the context of the execution to the new user. Later we will run the same command and you will notice that the current user name has been changed but the original login remains the same.

USE AdventureWorks2012;
GO
-- Get Login Details
SELECT ORIGINAL_LOGIN() OriginalLogin, SUSER_SNAME() CurrentLogin
GO
--Create a temporary login and user.
CREATE LOGIN TestLogin WITH PASSWORD = 'Complex@1';
CREATE USER TestUser FOR LOGIN TestLogin;
GO
-- Execute as another user
EXECUTE AS LOGIN = 'TestLogin'
GO
SELECT ORIGINAL_LOGIN() OriginalLogin, SUSER_SNAME() CurrentLogin
GO
-- Revert Execution as another user
REVERT
GO
-- Get Login Details
SELECT ORIGINAL_LOGIN() OriginalLogin, SUSER_SNAME() CurrentLogin
GO
-- Clean up
DROP LOGIN TestLogin;
DROP USER TestUser;
GO

This feature can be very useful in the auditing as well figuring out who is the original user to the session. Here I have a quick question – are you using this feature in your application? What is the real use case scenario for the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Read Full Post »