Few days ago, I have received email from users asking question :How to run 64 bit SQL SERVER 2005 on 32 bit operating system?
SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String
Following SQL User Defined Function will extract/parse numbers from the string. CREATE FUNCTION ExtractInteger(@String VARCHAR(2000)) RETURNS VARCHAR(1000) AS BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) SET @Count = 0 SET @IntNumbers = '' WHILE @Count <= LEN(@String) BEGIN IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9' BEGIN SET @IntNumbers =…
Read MoreSQL SERVER – 2005 – Explanation of TRY…CATCH and ERROR Handling
SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which are virtually impossible to handle at the database level in SQL Server 2000, can now be handled with ease. By taking advantage of these new features, you can…
Read MoreSQL SERVER – 2005 – Silent Installation – Unattended Installation
Silent SQL Server 2005 Installation is possible in two steps. 1) Creating an .ini file The SQL Server CD contains a template file called template.ini . Based on that create another required .ini file which includes a single [Options] section containing multiple parameters, each relating to a different feature or…
Read MoreSQL SERVER – SP Performance Improvement without changing T-SQL
There are two ways, which can be used to improve the performance of Stored Procedure (SP) without making T-SQL changes in SP. Do not prefix your Stored Procedure with sp_. In SQL Server, all system SPs are prefixed with sp_. When any SP is called which begins sp_ it is…
Read MoreSQL SERVER – 2005 Reserved Keywords
Microsoft SQL Server 2005 uses reserved keywords for defining, manipulating, and accessing databases. Reserved keywords are part of the grammar of the Transact-SQL language that is used by SQL Server to parse and understand Transact-SQL statements and batches. It is not legal to include the reserved keywords in a Transact-SQL…
Read MoreSQL SERVER – Search Text Field – CHARINDEX vs PATINDEX
We can use either CHARINDEX or PATINDEX to search in TEXT field in SQL SERVER. The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. Both functions take two arguments. With PATINDEX, you must include percent signs before and after the pattern, unless you are looking…
Read MoreSQL SERVER – DBCC Commands Introduced in SQL Server 2005
SQL Server 2005 has introduced following two documented and five undocumented DBCC Commands. I was able to find documentation for only first one online. If you find any documentation of any other DBCC Commands please add comments. It will be helpful to all of us. Documented: freesessioncache () — no…
Read MoreSQL SERVER – Fix: Server: Msg 7391, Level 16, State 1, Line 1
I have received this error many times on different servers in my careers. There is no single fix for this Error. Server: Msg 7391, Level 16, State 1, Line 1 can happen due to many reasons. I have used various of this reasons with few of my servers. Please refer…
Read MoreSQL SERVER – Performance Optimization of SQL Query and FileGroups
It is suggested to place transaction logs on separate physical hard drives. In this manner, data can be recovered up to the second in the event of a media failure. In SQL 2005 When database is created without specifying a transaction log size, the transaction log will be re-sized to…
Read More