Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – SPACE Function Example

July 19, 2007

A month ago, I wrote about SQL SERVER – TRIM() Function – UDF TRIM() . I was asked in comment if SQL Server has space function? Yes. SELECT SPACE(100) will generate 100 space characters. The use of SPACE() function is demonstrated in BOL very fine. Example from BOL: USE AdventureWorks;…

SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

July 18, 2007

Pinal Dave

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

SQL Backup and Restore, SQL Download, SQL Scripts

The questions I received in last two weeks: “I do not have backup, is it possible to restore database to previous state?” “How can restore the database without using backup file?” “I accidentally deleted tables in my database, how can I revert back?” “How to revert the changes, I have…

SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable

July 17, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts

This article is as per request from Application Development Team Leader of my company. His team encountered code where application was preparing string for ORDER BY clause of SELECT statement. Application was passing this string as variable to Stored Procedure (SP) and SP was using EXEC to execute the SQL…

SQL SERVER – Microsoft White Papers – Analysis Services Query Best Practices – Partial Database Availability

July 16, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

Best Practices, SQL Download, SQL White Papers

Microsoft TechNet frequently releases White Papers on SQL Server Technology. I have read the following two white papers recently. The summary of its content is here. Analysis Services Query Performance Top 10 Best Practices Optimize cube and measure group design Define effective aggregations Use partitions Write efficient MDX Use the…

SQL SERVER – SQL Joke, SQL Humor, SQL Laugh – 15 Signs to Identify Bad DBA

July 15, 2007

Pinal Dave

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

Database, SQL Download, SQL Humor, SQL Scripts

15 Signs to Identify Bad DBA They think it is bug in SQL Server when two NULL values compared with each other but SQL Server does not say they equal to each other. They do not rename the trigger name thinking it will not work after it is rename. They…

SQL SERVER – 2005 Collation Explanation and Translation – Part 2

July 14, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Collation, SQL Scripts, SQL String

Following function return all the available collation of SQL Server 2005. My previous article about the SQL SERVER – 2005 Collation Explanation and Translation. SELECT * FROM sys.fn_HelpCollations() Result Set: (only few of 1011 records) Name Description Latin1_General_BIN Latin1-General, binary sort Latin1_General_BIN2 Latin1-General, binary code point comparison sort Latin1_General_CI_AI Latin1-General,…

SQL SERVER – 2005 – Use ALTER DATABASE MODIFY NAME Instead of sp_renameDB to rename

July 13, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Scripts, SQL Stored Procedure

To rename database it is very common to use for SQL Server 2000 user : EXEC sp_renameDB 'oldDB','newDB' sp_renameDB syntax will be deprecated in the future version of SQL Server. It is supported in SQL Server 2005 for backwards compatibility only. It is recommended to use ALTER DATABASE MODIFY NAME…