Site Sections
Posts
33
Comments
186
Trackbacks
0
SQL Server 2000/2005
Tips & Tricks and solution to some common problems faced while coding in SQL server 2000/2005.Repository of smart ways to do stuffs in SQL server
MS SQL Server - 'sa' user locked out
If you come across a scenario where 'sa' user is locked out and you do not have any other ways to reset or unlock the user. Use the below MSDN reference to get SQL server back and running.https://msdn.micros... ......
Posted On Tuesday, January 26, 2016 6:33 PM | Comments (44)
Find out all the changes / alterations to SQL database objects
This script when run under user who has access to 'sys.traces' will be able to sql tables, stored procedures which has been modified.DECLARE @filename VARCHAR(255) SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1; print @FileNameSELECT gt.HostName, gt.ApplicationName, gt.NTUserName, gt.NTDomainName, gt.LoginName, gt.SPID, gt.EventClass, te.Name AS EventName, gt.EventSubClass, gt.TEXTData, gt.StartTime, gt.EndTime, gt.ObjectName, ......
Posted On Thursday, November 5, 2015 4:22 PM | Comments (1)
Remove all characters words after certain character word in SQL
If you want to get rid of all the data after certain word in SQL column, you can use the combination of LEFT and CHARINDEX function to achieve the desired result.For example, if column consists of following data - Column1-----------tempdata1 tempdata2;tempdata3and if you want to display only uptill the semicolon in result - then use the following query:SELECT LEFT(Column1, CHARINDEX(';', Column1)-1)RESULTtempdata1 tempdata2 ......
Posted On Friday, August 14, 2015 12:24 PM | Comments (2)
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) ......
Posted On Tuesday, March 12, 2013 3:45 PM | Comments (2)
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 tableSELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update, *FROM sys.dm_db_index_usage_stats... database_id = DB_ID( 'DB_Name')AND OBJECT_ID=OBJECT_ID('table_... ......
Posted On Friday, March 1, 2013 3:30 PM | Comments (5)
SQL Server Management Studio crashed - recover unsaved sql query
If for some reasons SQL server instance dies or it crashes and if you had an unsaved query - you could still recover it from the backup files that sql server creates whenever it crashes unexpectedly.The path for the backup .sql files is something like the following -C:\Users\USER_PROFI\Docume... Server Management Studio\Backup Files\Solution1 ......
Posted On Thursday, February 28, 2013 5:31 PM | Comments (8)
DMV to figure out unused and missing Indexes on table in SQL server
MS SQL Server 2005 onwards provides quite a lot of views and functions which can be used to keep a tab on the database engine and also gives you statistics which helps you in making informed decision about the database schemas. This are collectively called as DMV (Dynamic management views).For example - the following DMV can help you show the state of indexes in the database.It shows you the table name, the number of indexes on that table, how many of those indexes have been unused since either the ......
Posted On Thursday, February 28, 2013 3:49 PM | Comments (0)
Update Query in SQL with SELECT statement
UPDATE Table SET Table.col1 = other_table.col1, Table.col2 = other_table.col2 FROM Table INNER JOIN other_table ON Table.id = other_table.id ......
Posted On Tuesday, March 13, 2012 4:16 PM | Comments (2)
Date/Time formats in SQL Server
In SQL server a column with datatype of DateTime stores the time to the millisecond, but many times you just like to show the date and time only upto the second. Although there are many ways to achieve this, I'm just going to show the couple of possible ways - 1.) SELECT CONVERT(VARCHAR,GETDATE(),120) This would yield the current date time in YYYY-MM-DD HH:MM:SS (ofcourse depends on your regional setting) 2.) You can get the millisecond part from the getdate() function and subtract it with the getdate() ......
Posted On Thursday, June 9, 2011 12:21 PM | Comments (2)
Catching Schema Errors in TRY..CATCH block in SQL Server
Recently I was writing a stored procedure in SQL Server 2005. The stored procedure was pretty straight forward with some couple of SQL DML statements inside TRY block and exception being handled and raised in CATCH block. However, while testing the exception handling of the stored procedure, it didn't seem to go to CATCH block. All I was trying was - Insert some data into some 'non-existent' table and catch the error in the CATCH block. I was expecting stored procedure to report 'Invalid object name' ......
Posted On Wednesday, June 8, 2011 3:15 PM | Comments (1)
SET DATEFORMAT in SQL
Some times, you may want the database to interpret the date value as per the format you have provided rather than using the default database level format (which is mostly set to mdy as default US date format). SQL server SET DATEFORMAT syntax comes to rescue in this situations - you can set the dateformat to your convenience and then can run the insert or update with your format and SQL will not crib about it. Please note, this is applicable only for the session and it does not set the date format ......
Posted On Thursday, February 28, 2013 3:40 PM | Comments (0)
Dynamic table creation using SELECT INTO Clause in SQL
While working with SQL, you come across a scenario when you need to dump the resultset of a SELECT statement into either a temporary table or a placeholder table. I can imagine how tiresome it is to create a schema for the table beforehand and keep that updated with any additions/deletions to the SELECT statement. To cut the long story short, SQL makes ones life easy with the help of a syntax "SELECT... INTO..." E.g. SELECT column1, column2, column3 INTO NewDynamicTable FROM TableName This would ......
Posted On Tuesday, June 7, 2011 12:07 PM | Comments (3)
Find the occurrence of word/character in SQL column with wildcard character - PATINDEX
CharIndex and PatIndex both can be used to determine the presence of character or string within sql column data. Both returns the starting position of the first occurrence of the character/word within expression. However, one major difference between CharIndex and PatIndex is that later allows the use of wild card characters while searching for character or word within column data. Also, Patindex is useful for searching within Text datatype. Allowed wild card characters are % and _ . " % " - use ......
Posted On Thursday, March 25, 2010 5:35 PM | Comments (5)
How to find the occurrence of particular character in string - CHARINDEX
Many times while writing SQL, we need to find if particular character is present in the column data. SQL server possesses an in-built function to do this job - Syntax CHARINDEX(character_to_search, string [, starting_position]) Returns the position of the first occurrence of the character in the string. NOTE - index starts with 1. So, if character is at the starting position, this function would return 1. Returns 0 if character is not found. Returns 0 if 'string' is empty. Returns NULL if string ......
Posted On Tuesday, March 23, 2010 3:48 PM | Comments (4)
Various string manipulation functions in SQL Server 2000 / 2005
SQL Server provides a range of string manipulation functions. I was aware of most of those in back of the mind, but when I needed to use one, I had to dig it out either from SQL server help file or from google. So, I thought I will list some of the functions which performs some common operations in SQL server. Hope it will be helpful to you all. Len (' String_Expression' ) - returns the length of input String_Expression. Example - Select Len('Vipin') Output - 5 Left ( 'String_Expression', int_characters ......
Posted On Friday, March 26, 2010 3:07 PM | Comments (2)
Archives
Post Categories