Posts
33
Comments
153
Trackbacks
0
March 2013 Entries
SQL Server–Find Nth Largest/smallest entry in a table

We could find the maximum and minimum value in a table by using the SQL inbuilt aggregate functions MAX() and MIN() respectively. But, many times there comes a scenario when we need to find the second or third largest/smallest entry in table. There is no built-in SQL function to get this information but we can use Row_Number() function to our advantage to find Nth Largest or smallest value in table.

For example – usually, to find maximum value we normally use the following

select MAX(column_name) as column_name from table_name 

This is equivalent to the following

SELECT * FROM
(
SELECT ROW_NUMBER() OVER ( ORDER BY column_name DESC) ROW_NUM, column_name FROM table_name
)  T 
WHERE row_num = 1
However, this can then be used to get any largest value in the table, all you have to do is just change the row_num value in the where clause.
So, to get the second largest value set 
WHERE row_num = 2

Similarly to find the second smallest entry, just sort the column by ascending and set the where clause to 2.

Hope this helps!

Posted On Tuesday, March 12, 2013 3:45 PM | Comments (2)
Reseed Identity column in SQL Server
Use the below SQL command to reset the seed on the identity column -

DBCC CHECKIDENT('TABLE_NAME', RESEED, 'RESEED_VALUE')

The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value, that is, the new current increment value defined for the column value plus 1.

DBCC CHECKIDENT ("Person.AddressType", RESEED, 10);


Posted On Tuesday, March 5, 2013 5:25 PM | Comments (3)
SQL Server - get last executed SQL statement
We all know the usage of sp_who2 command in SQL server, its life-saver when you need to know what's causing your query take longer time to execute or in other words who's blocking you r query.

But, SQL server has many in-built functions which lets you sneak a peek inside the sql server and we can use them to our advantage. One of them is 

DBCC INPUTBUFFER(SP_ID)

Using this function, we can see the last executed sql statement in that process id.

There are several ways to find out what is the latest run query from system table sys.sysprocesses.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext sql_handle
FROM sys.sysprocesses
WHERE spid 61
SELECT TEXT
FROM 
sys.dm_exec_sql_text(@sqltext)
GO



Posted On Monday, March 4, 2013 3:32 PM | Comments (8)
SQL server - get list of active connections to each database
Many times, we want list of all users who are connected to the SQL server - to determine it  use the below query. It returns the Database, Number of open connections and logged-in user credentials.

SELECT DB_NAME(dbid) as Database, COUNT(dbid) as Number Of Open Connections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

Hope this helps.
Posted On Monday, March 4, 2013 3:24 PM | Comments (5)
Find out last modification to the SQL table
If you need to find when was the table last updated for insert/delete/update .. use the below query to find it. It will show you the last updated datetime for the table

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, 
last_user_update, *

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'DB_Name')
AND OBJECT_ID=OBJECT_ID('table_Name')
Posted On Friday, March 1, 2013 3:30 PM | Comments (5)