SQL function to extract number from string
A common question among forum is a SQL function to extract number from string. Let’s see how this can be achieved. One way to achieve this is with while loop as …Read More »
charindex vs patindex performance comparison
CHARINDEX and PATINDEX are used to get starting position of a pattern. The functional difference is that the PATINDEX can use wild characters in the pattern being searched whereas CHARINDEX can’t. … Read More »
DBCC RESEED table identity value
An identity column in a table is used to auto generate new values based on current seed and increment value. Seed is the first value or the value to start with … Read More »
IDENT_CURRENT vs SCOPE_IDENTITY vs @@IDENTITY vs. IDENTITY in SQL Server
These are the most common functions to work with identity values in SQL Server. This blog looks at IDENT_CURRENT vs. SCOPE_IDENTITY vs. @@IDENTITY vs. IDENTITY comparison. Let’s look at the definition … Read More »
T SQL clear stored procedure cache
SQL Server stores the compiled plans of procedures being executed so as to increase procedure performance. A plan is compiled and saved first time a procedure is executed and is used … Read More »
QUOTED IDENTIFIER in SQL Server
THE QUOTED_IDENTIFIER setting allows SQL Server to follow ISO rules regarding quotation mark delimiting identifiers and literal settings or in plain English it specifies how SQL Server treats data with in … Read More »
T SQL paging stored procedure
Paging is a process of splitting large result set into subset of pages. So, instead of displaying 5000 records resulting from a search, first 20 records are shown and then next … Read More »
Move tempdb files in SQL Server
Tempdb is a SQL Server system database popularly known as SQL Servers garbage bin mainly because of its usage. The Tempdb database is created at the same location as other system … Read More »
Index seek vs Index scan in SQL Server
An index is a way to speed up performance of a query in SQL Server. An index is a B-Tree structure on a table column or set of columns referred as … Read More »
Error 8101 SQL Server
The error 8101 SQL Server is given below The above query tries to inserts an explicit value for the identity column without specifying the column name in insert statement and when … Read More »
SQL Server error message table
SQL Server error message table or catalog view has the error messages which are being displayed by SQL Server when an error occurs. The error message catalog views are sys.sysmessages and … Read More »
SQL Server change collation of database
A SQL Server collation setting governs the code page to store non Unicode data in SQL Server and the rules to sort and compare non Unicode characters. The SQL Server installation … Read More »
Unique index vs unique constraint in SQL Server
To start with there is no practical difference between Unique Index and unique constraint; they both accomplish the task of providing uniqueness to a column. A unique constraint is enforced by … Read More »
Enable xp_cmdshell in SQL Server
Xp_cmdshell is an extended stored procedure which takes a dos command as a string and returns output in table format. It is disabled by default and can be enabled as shown … Read More »
SQL Server error 1222 lock request time out period exceeded
As the error says error 1222 lock request time out period exceeded, it occurs when a query waits longer than the lock timeout setting. The lock timeout setting is the time … Read More »
Script to find SQL Server version
One of the first things we do when inheriting a new SQL Server environment or when troubleshooting issues, knowing SQL Server version. There are different ways to get SQL Server information. … Read More »
T-SQL script delete column from a table
It’s a common task that needs to be done on development/test environment. Though it seems that deleting a column from a table is just running an ALTER TABLE statement, however it’s … Read More »
Script to find SQL Server service account
SQL Server service account information can be fetched from registry or from sys.dm_server_services for versions starting from SQL Server 2008 R2 SP1 and above. This blog provides script to find SQL … Read More »
T-SQL script to rename table
Renaming a table is a simple task, however one thing most people miss is to find all table dependencies and replace the table in stored procedures/functions/views using that particular table. A … Read More »
Restore transaction log with standby in SQL Server
Restore transaction log with standby option leaves the database read only mode. The uncommitted transaction are undone and saved in a file so that recover effects can be reversed when database … Read More »
One Comment on “SQL Server Accidental DBA”