Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – Introduction and Example for DATEFORMAT Command

September 28, 2007

While doing surprise code review of Jr. DBA I found interesting syntax DATEFORMAT. This keywords is very less used as CONVERT and CAST can do much more than this command. It is still interesting to learn about learn about this new syntax. Sets the order of the dateparts (month/day/year) for…

SQL SERVER – FIX : Error 3154: The backup set holds a backup of a database other than the existing database

September 27, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Backup and Restore, SQL Data Storage, SQL Error Messages, SQL Scripts

Our Jr. DBA ran to me with this error just a few days ago while restoring the database. Error 3154: The backup set holds a backup of a database other than the existing database. Solution is very simple and not as difficult as he was thinking. He was trying to…

SQLAuthority News – Book Review – Programming SQL Server 2005 [ILLUSTRATED]

September 26, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks, SQLAuthority News

SQLAuthority Book Review

Programming SQL Server 2005 [ILLUSTRATED] (Paperback) by Bill Hamilton (Author) Link to Amazon User does not have to be experience SQL Server 2005 programmer to use this book; as it is designed for users of all levels. This book also suggests that user does not have to be experienced with…

SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

September 25, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Transactions

Few days ago, one of the Jr. Developer asked me this question (What will be the Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT?) while I was rushing to an important meeting. I was getting late so I asked him to talk with his Application Tech…

SQL SERVER – Order of Result Set of SELECT Statement on Clustered Indexed Table When ORDER BY is Not Used

September 24, 2007

Pinal Dave

SQL Performance

SQL Index, SQL Order By, SQL Server

“What will be the order of the result set of a SELECT statement on clustered indexed table when the ORDER BY clause is not used?”

SQL SERVER – Stored Procedure to Know Database Access Permission to Current User

September 23, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Cursor, SQL Scripts, SQL Stored Procedure, undocumented SQL

Jr. DBA in my company only have access to the database which they need to use. Often they try to access database and if they do not have permission they face error. Jr. DBAs always check which database they have access using following system stored procedure. It is very reliable…

SQL SERVER – 2005 – Version Information and Additional Information – Extended Stored Procedure xp_msver

September 22, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts

I was glad when I discovered this Extended Stored Procedure myself. I always used different syntax to retrieve server information. Many of information I was looking up using system information of the windows operating system. Syntax: EXEC xp_msver ResultSet: Index Name Internal_Value Character_Value —— ——————————– ————– ————————————- 1 ProductName NULL…