Vipin Agarwal
Tips & tricks on .Net, SQL & Javascript
Site Sections
Home
Contact
Syndication
Login
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... ......
Share This Post:
Short Url:
http://wblo.gs/ij0
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, ......
Share This Post:
Short Url:
http://wblo.gs/hpE
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 ......
Share This Post:
Short Url:
http://wblo.gs/hFW
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) ......
Share This Post:
Short Url:
http://wblo.gs/ddr
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_... ......
Share This Post:
Short Url:
http://wblo.gs/dc1
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 ......
Share This Post:
Short Url:
http://wblo.gs/dbx
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 ......
Share This Post:
Short Url:
http://wblo.gs/dbs
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 ......
Share This Post:
Short Url:
http://wblo.gs/cl0
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() ......
Share This Post:
Short Url:
http://wblo.gs/bvS
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' ......
Share This Post:
Short Url:
http://wblo.gs/bvC
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 ......
Share This Post:
Short Url:
http://wblo.gs/dbr
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 ......
Share This Post:
Short Url:
http://wblo.gs/bur
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 ......
Share This Post:
Short Url:
http://wblo.gs/a8X
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 ......
Share This Post:
Short Url:
http://wblo.gs/a5o
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 ......
Share This Post:
Short Url:
http://wblo.gs/a91
Posted On
Friday, March 26, 2010 3:07 PM
|
Comments (2)
Archives
January 2016 (2)
November 2015 (1)
August 2015 (1)
June 2015 (2)
July 2014 (1)
March 2014 (1)
July 2013 (1)
June 2013 (1)
March 2013 (5)
February 2013 (3)
March 2012 (5)
June 2011 (5)
October 2010 (1)
September 2010 (1)
March 2010 (3)
Post Categories
Tips & Insights
The Dev Life
Tech Topics
Dev Culture
BuildBetter
Copyright © 2005 Vipin
This work is licensed under a
Creative Commons License