All Articles - SQL Authority with Pinal Dave

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

September 26, 2007

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

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…

SQL SERVER – 2005 – Multiple Language Support

September 21, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts

SQL Server supports multiple languages. Information about all the languages are stored in sys.syslanguages system view. You can run following script in Query Editor and see all the information about each language. Information about Months and Days varies for each language. Syntax: SELECT Alias, * FROM sys.syslanguages ResultSet: (* results…

SQL SERVER – FIX : ERROR : 3260 An internal buffer has become full

September 20, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages

ERROR : 3260 An internal buffer has become full The reason I have picked to write about this error is because we have encountered this error many times in one of our older server. Fix/WorkAround/Solution: We were not able to absolutely reduce this error but following changes helped. 1) Rebooted…

SQL SERVER – Rename Database to New Name Using Stored Procedure by Changing to Single User Mode

September 19, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Database, SQL Scripts, SQL Stored Procedure

In my organization we rename the database on development server when are refreshing the development server with live data. We save the old database with new name and restore the database from live with same name. If developer/Jr. DBA have not saved the SQL Script from development server, he/she can…

SQLAuthority News – Scale-Out Querying with Analysis Services Using SAN Snapshots

September 18, 2007

Pinal Dave

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

Data Warehousing, Database, SQL White Papers

White paper describes the use of virtual copy Storage Area Network (SAN) snapshots in a load-balanced scalable querying environment for SQL Server 2005 Analysis Services. This architecture provides the following improvements Improves the utilization of disk resources Optimizes cube processing operations Supports dedicated snapshots for specific users at different points…

SQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function

September 18, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts

Few days ago I wrote SQL SERVER – UDF – Validate Integer Function. It was very interesting to write this and developers at my company started to use it. One Jr. DBA modified this function to validate only positive integers. I will share this with everybody who are interested in…