>Spot the difference – SQL Server 2008 TSQL enhancements

>If you’ve had your hands on T-SQL for quite some time, it wouldn’t be hard for you to identify the new enhancements available in SQL Server 2008. Now just for fun, try to identify what’s unusual in this script, from a SQL Server 2000/2005 point-of-view:

DECLARE

@variable int = 1;
SET
@variable += 1
;

CREATE TABLE

table1 (col1 int);

INSERT
table1
VALUES (@variable),(@variable+1
);

If you try to execute this script in SQL Server 2005, you’ll get an error similar to the one below

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable “@variable”.
Now you start thinking, what could be wring with the script? The answer: T-SQL enhancements in SQL Server 2008. First, The DECLARE statement in the first line now has the capability to accommodate initialization of variables unlike before where it only does variable declaration and you need either a SET or SELECT statement to assign values to variables. This is something similar to what you do in .NET, particularly VB.NET (the language enhancement part, in this case) where you can declare and assign values to variables in a single line. You can still use the SET statement to assign values to variables. Next is the += operator (-= is also included). I’ve first encountered this in C++ (Java, C# and VB.NET also have these) where the expression is used to increment (or decrement) variable values. In this case, the value for the variable @variable is set to 2 after the second line because of the += operator. The third item is the existence of multiple row predicates in the VALUES clause. This also works for an UPDATE statement. This means you can now make your INSERT or UPDATE statements shorter with fewer codes. Not that you couldn’t do these in previous versions but this simply means writing less code and getting more work done (although you might be in trouble as your boss might give you more work to do)
Check out my SQL Server 2008 videos at BlogCastRepository.com

>Speed up your SQL Server 2005 Books Online

>Microsoft did a very literal translation of the term Books Online with SQL Server 2005. Books Online happens to be the help file for SQL Server. In SQL Server 2005 (and now with SQL Server “Katmai”), Books Online has the capability to go “online” and look for related searches on the Internet, MSDN Online, community websites like CodeZone, etc. A lot of users find it annoying that it takes ages to load a search result in Books Online. This is because the default configuration is that it will search for the results in the local version, then, it will go online and grab the results from the online sources configured. The make Books Online respond a bit faster to searches, configure it not to go online at all (Google still offers faster search results, after all). Open SQL Server Books Online, go to the Tools menu then select Options. When the Options window appears, select the Online option under the Help node. On the When Loading Help content section, select the Try local only, not online option. This will return search results from the local copy of Books Online (and make the searches a bit faster). Try it out and see what happens

>The pitfalls of ROBOCOPY

>I have been using ROBOCOPY for a lot of reasons – Home folder replication for disaster recovery purposes, file-copy utility for custom transaction log shipping for SQL Server, etc. It has been a very useful tool for me, especially when you talk about disaster recovery. But what a lot of people do not realize is that this will become a very big headache if not used properly. One scenario I have seen is a database log file being replicated over to another server. Database log files are being locked by the service or application responsible for running the database engine. In this case, depending on how it is configured, ROBOCOPY will try execute the copy command until such time that it can do so. Now what will happen if it cannot copy the file due to a lock caused by the service? It will enter an infinite loop. And if you’re like me who likes to log every activity done by any utility or script, you will configure ROBOCOPY to do so as well. When this happens, the log file generated will become so huge that it would be very difficult to open with Notepad. Just a word of caution when using ROBOCOPY – DO NOT use the utility on files that are being locked or used by services or applications.

>The Pains of Collation Settings

>Collation settings define the physical storage of character strings in SQL Server. It specifies the bit patterns that represent each character and the rules by which characters are sorted and compared. Collation settings have a direct impact on the databases if you configure them to use a different collation other than the default. When you configure a different collation setting during the installation, the rest of the databases (system and user-defined) will have this setting was their default when you create them unless you specifically defined something different from the default. One case I encountered was that the tempdb database had a different collation setting compared to the user databases. This is because SQL Server was reinstalled and the master and msdb databases were restored from backup together with the user-defined databases. This made the collation settings of the entire database server back to their original configuration – well, almost. Since the model database was not restored (and obviously, the tempdb database does not require a backup), collation settings for the both would be the settings defined during re-installation. A script executed in one of the user-databases would require the use of the tempdb and since they do not have the same collation settings, queries will fail unless the queries themselves take into account collation setting differences. The next best bet is to rebuild the master database and change the collation settings and go through all the grueling task of restoring the system databases, provided that the user-defined databases are still intact. Well, another approach would be to simply restore the model database if you have a backup (or simply look for the old MDF and LDF files if they are still working fine). This is because tempdb database takes the settings of the model database everytime SQL Server service starts. This will resolve collation problems should you encounter a similar case

>What? I lost my port on a named instance?Dynamic port detection?

>One thing I realized is that SQL Server port numbers will not be the same for I don’t know what reason. I found this out while rebuilding a SQL Server named instance. We had standard settings in setting up SQL Server. But since I am rebuilding my test environment, I just used the default port number settings (which is 1433) during the process. To my surprise, it was using port number 3080 when the service started. Apparently, SQL Server dynamically determines the port it listens on. It will try to listen on the port it used previously. If for some reason it cannot bind to that port, the named instance may bind to a different port – which is obviously the case for my setup. Now this would cause a lot of problems if your SQL Server is interacting with a lot of servers, services and applications as you need to either change the port number to it’s original value (which I think would be more appropriate) or change the clients to use the new port number. I have yet to dig up more details on this as to why SQL Server behaves that way. At least I know another place to look at when troubleshooting connectivity problems

I was doing more research on SQL Server port usage when I chanced upon this Microsoft KB. SQL Server clients use DBNETLIB to perform port detection. This is either thru ODBC or SQLOLEDB componentes which loads DBNETLIB. SQL Server clients can use the Client Network Utility for dynamic port detection. This is to make sure that in case SQL Server uses a different port number during service startup or is configured to user dynamic port assignments, the client can map to the port number that a specific instance uses. Now, what if the client is a third party application running on a non-Microsoft platform(like a JSP application running on top of Apache on a Linux box?) How does this port resolution work?