This tip applies to both Microsoft SQL Server and to Oracle, and probably several other databases.

When you use the asterisk (aka star) in a select statement it requires all columns of data from the result set to be accessed.

Why not SELECT *?

If you are querying a small table with 3 or 4 columns, then the following two statements won’t be very different:

SELECT * FROM …

Or

SELECT column1, column2, column3, column4 FROM …

But when your query gets more complicated, and you then join in two more tables, which combined with the original table have a total of 40 columns, then all 40 columns are returned in your record set.

SELECT * FROM Table1, Table2, Table3 WHERE …   

will return all 40 columns

SELECT column1, column2, column3, column4
FROM Table1, Table2, Table3 WHERE …   

will only return 4 columns.

Recently I was optimizing a query using the SELECT * syntax which returned about 25 columns, one of which was a VARBINARY(MAX) column.  This query was taking about 7 seconds to run.  I then substituted the 6 column names that I needed for the select *, and with no other changes, that same query ran in about .1 seconds.

Additionally assume that you had the following query:

SELECT column1, column2, column3, column4 FROM Table1

And you had an index that contained column1, column2, column3, and column4.  This would be called a covered index, and when your query was run, it would never hit any of the data pages, and instead pull all of your results from the index.  This can much quicker than having to access the data pages.

So my recommendation is to never use the SELECT * syntax, specify what you want in your result sets.  Even if your table is small, specify what you want, because over time your table may grow to have more columns.

Why not COUNT(*)

Instead of COUNT(*), you should use COUNT(1).   Either way, you will get the same results. 

The COUNT(*) counts the number of rows in your result set by bringing all of the columns into memory, but using COUNT(1) doesn't require any of these columns to come into memory.  This also applies when your query includes a GROUP BY with COUNT(1).

My recommendation is to NEVER use COUNT(*), and instead use COUNT(1).  They will both give you the same number of rows in your result set, but there is far less work required to get the count when using COUNT(1), and COUNT(*) is just wasteful.

CREATE TABLE #tmp

   ( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), value BIGINT );

INSERT INTO #tmp EXEC ( 'DBCC MEMORYSTATUS' );

SELECT top 1 value / 1024 'KBUsed'

  FROM #tmp

 WHERE RTRIM(LTRIM(name)) = 'Working Set';

// get the total memory used by the SQL Server

SQLStatement = @"CREATE TABLE #tmp

   ( row_id INT IDENTITY PRIMARY KEY, name VARCHAR(100), value BIGINT );

INSERT INTO #tmp EXEC ( 'DBCC MEMORYSTATUS' );

SELECT top 1 value / 1024 'KBUsed'

  FROM #tmp

 WHERE RTRIM(LTRIM(name)) = 'Working Set';

DROP TABLE #tmp;";

SQLDataAdapter = new SqlDataAdapter(SQLStatement, conn);

dtResult = new DataTable();

SQLDataAdapter.Fill(dtResult);

foreach (DataRow drRow in dtResult.Rows)

{

    tbServerMemory.Text = drRow["KBUsed"].ToString();

}

For days I have been battling with MSDTC (Microsoft Distributed Transaction Server) and transaction that have the status of indoubt.  We are using the .NET System.Transactions.TransactionScope class to wrap many SQL Server calls together in a single transaction, which requires MSDTC to manage the transaction.  The application would run for 30 minutes then hang, timing out, and the table on SQL Server would end up being row locked -- hanging all selects, inserts, updates and deletes.  It took me a while to realize that the Kerbose error in the application log on SQL Server was the reason the MSDTC communication was failing -- by comparing the crash time with the error log.  Then doing some research I realized that all those virtual machines running our web servers that we copied around on our virtual servers had the same SID.  Even though we had renamed them and readded them to the domain, that didn't generate a new SID.  So I used Microsoft's NewSID utility to give them different SIDs and I am back in business.

NewId Application:

http://technet.microsoft.com/en-us/sysinternals/bb897418.aspx

Kerbose Error:

Event Type: Error
Event Source: Kerberos
Event Category: None
Event ID: 4
Date:  9/12/2008
Time:  12:10:29 PM
User:  N/A
Computer: LE79TEST
Description:
The kerberos client received a KRB_AP_ERR_MODIFIED error from the server
XXXXX.  The target name used was
XXXX. This indicates that the
password used to encrypt the kerberos service ticket is different than that
on the target server. Commonly, this is due to identically named  machine
accounts in the target realm (XXXX), and the client realm.
Please contact your system administrator.

{6230289B-5BEE-409e-932A-2F01FA407A92}

In honor of William Vaugh showing up at our local .NET user group, I am posting a SSRS article about how to add maps to your SQL Server Reporting Service reports.  Google Map has static mapping functionality in their API set.  Static maps are the ability to call a URL on the Google server and get back a single image of the map.  This is different then using the Ajax objects in your web page, since those objects need the javascript engine to excute.  With SSRS you don't get a javascript engine and you can't be guareenteed that it will be a web page, the export might be a PDF.  Google Maps API for static maps takes a Query string like this example:

http://maps.google.com/staticmap?center=40.714728,-73.998672&zoom=14&size=512x512&maptype=mobile\
&markers=40.702147,-74.015794,blues%7C40.711614,-74.012318,greeng%7C40.718217,-73.998284,redc\
&key=MAPS_API_KEY

With good eyes you can see that it sets the center, zoom, and some markets on the map.  First thing to do is drop the zoom and center, the map will auto center with the markers.  Here are the steps:

1) Create a new Report in SSRS, try to avoid the wizard, however if you can't use some Random T-SQL to get past it.

2) Delete the table or matrix the wizard created for you.

3) Create a Image, using a Web image, and add the URL above.

4) Create a new Data Set, Here is the T-SQL I used to create my markers:

DECLARE @Markers varchar(max)
DECLARE @latitude float
DECLARE @longitude float

SET @Markers  = 'markers='

DECLARE _cursor CURSOR FOR 
SELECT DISTINCT  TOP 50 Latitude, Longitude
FROM MapTable
WHERE (NOT Latitude IS NULL) AND (NOT Longitude IS NULL)

OPEN _cursor 

FETCH NEXT FROM _cursor 
INTO @latitude, @longitude

WHILE @@FETCH_STATUS = 0
BEGIN

IF ((NOT (@latitude IS NULL)) AND (NOT (@longitude IS NULL)))
SET @Markers = @Markers + CONVERT(varchar(max),@latitude) + ',' +
   CONVERT(varchar(max),@longitude) + ',tinyred%7C'

FETCH NEXT FROM _cursor 
INTO @latitude, @longitude

END 
CLOSE _cursor 
DEALLOCATE _cursor 

SELECT @Markers AS Marker

5) Click on the Image in your preview window and open the properties.  Change the Value from the static URL to this dynamic URL:

="http://maps.google.com/staticmap?size=640x640&maptype=mobile&" + Fields!Marker.Value + "&key=YourKey"

In Sql server query window, execute query that selects all stored procedures with the prefix you want (such as procedures that start with 'cust'):

SELECT 'DROP PROCEDURE ' + Name FROM sys.objects

WHERE type in (N'P', N'PC')

and name like 'cust_%'

 

The one newsletter that arrives in my inbox that is bound to perk me up anytime is from http://www.sqlservercentral.com/. It is sort of an older style newsletter but the contents are great. The editorials (yes, there are editorials) are also great. The main articles might not be right for me at the time but then there is the question and the community-submitted help code as a featured script.

Let's say you forgot a WHERE clause and want to restore your backup database to recover the lost data.  However, you don't want to override the data that has accumulated since the last back-up.  What you need to do is restore the backup along side the newer database.  Here is how to do it:

RESTORE DATABASE backup
   FROM 'C:\temp\lastbackup.bak'
   WITH MOVE 'mydb_Data' TO 'C:\MySQLServer\backupdb.mdf',
   MOVE 'mydb_Log' TO 'C:\MySQLServer\backupdb.ldf';

This statement tells the SQL server to create a database called "backup", from the lastbackup.bak file and not to use the files stored in the back-up, but instead use: backupdb.mdf and backupdb.ldf.  Unless you remap your backup files, the RESTORE will try to override the old files that are in use by the newer database -- which will cause a SQL Server error (not data loss).

{6230289B-5BEE-409e-932A-2F01FA407A92}

Detaching a database in SQL Server doesn't delete the files (.ldf, .mdf, .ndf) where the data is stored.  Deleting a database is different then detaching a database.  Detaching allows you to move the files and reattach them to a different SQL Server.  However, once you detach a database it is not available to query.

{6230289B-5BEE-409e-932A-2F01FA407A92}