“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
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…
Read MoreSQL SERVER – 2005 – Version Information and Additional Information – Extended Stored Procedure xp_msver
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…
Read MoreSQL SERVER – 2005 – Multiple Language Support
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…
Read MoreSQL SERVER – FIX : ERROR : 3260 An internal buffer has become full
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…
Read MoreSQL SERVER – Rename Database to New Name Using Stored Procedure by Changing to Single User Mode
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…
Read MoreSQLAuthority News – Scale-Out Querying with Analysis Services Using SAN Snapshots
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…
Read MoreSQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function
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…
Read MoreSQLAuthority News – NASDAQ Uses SQL Server 2005 – Reducing Costs through Better Data Management
I just came across PDF published by Microsoft to promote SQL Server 2005. I find few things very interesting. I will list them here.
NASDAQ
SQL SERVER – Difference Between UPDATE and UPDATE()
What is the difference between UPDATE and UPDATE()? UPDATE is syntax used to update the database tables or database views. USE AdventureWorks ; GO UPDATE Production.Product SET ListPrice = ListPrice * 2; GO UPDATE() is used in triggers to check update/insert to the database tables or database views. Returns a…
Read More