Pinal Dave - SQL Server Performance Tuning Expert

SQL SERVER – Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery

May 16, 2007

SQL Error Messages, SQL Function, SQL Scripts, SQL Sub Query

Fix: Error 130: Cannot perform an aggregate function on an expression containing an aggregate or a subquery Following statement will give the following error: “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.” MS SQL Server doesn’t support it. USE PUBS GO SELECT AVG(COUNT(royalty)) RoyaltyAvg…

SQL SERVER – Binary Sequence Generator – Truth Table Generator

May 15, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL Utility

Run following script in query editor to generate truth table with its decimal value and binary sequence. The truth table is 512 rows long. This can be extended or reduced by adding or removing cross joins respectively. Script: USE AdventureWorks; DECLARE @Binary TABLE ( Digit bit) INSERT @Binary VALUES (0)…

SQL SERVER – DBCC commands List – documented and undocumented

May 15, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Server DBCC, undocumented SQL

Database Consistency Checker (DBCC) commands can gives valuable insight into what’s going on inside SQL Server system. DBCC commands have powerful documented functions and many undocumented capabilities. Current DBCC commands are most useful for performance and troubleshooting exercises. To learn about all the DBCC commands run following script in query…

SQL SERVER – SQL Joke, SQL Humor, SQL Laugh – Photo

May 14, 2007

Pinal Dave

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

SQL Humor

Pay attention to the last line of the ingredients. I found this entry at Worse Than Failure. I found it humorous. Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL SERVER – MS TechNet : Storage Top 10 Best Practices

May 14, 2007

Pinal Dave

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

Best Practices, SQL Data Storage, SQL Download, SQL Scripts

This one of the very interesting article I read regarding SQL Server 2005 Storage. Please refer original article at MS TechNet here. Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application. More / faster spindles are better for performance. Try not to…

SQL SERVER – Query to Find First and Last Day of Current Month – Date Function

May 13, 2007

Pinal Dave

SQL Tips and Tricks

SQL DateTime, SQL Function, SQL Scripts, SQL Server, SQL Utility

Following query will run respective on today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month. Let us see how we can do this with the help of Date Function in SQL Server.

SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

May 13, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL String

Following function keeps only Alphanumeric characters in string and removes all the other character from the string. This is very handy function when working with Alphanumeric String only. I have used this many times. CREATE FUNCTION dbo.UDF_ParseAlphaChars ( @string VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @IncorrectCharLoc SMALLINT SET @IncorrectCharLoc…