The default port that SQL Server listens on is 1433. This port however is not the same port in all cases as we will come to find. SQL Server has three different ways to communicate. These include: TCP Ports Named Pipes Shared Memory VIA (Virtual Interface Adapater) – Deprecated When communicating, SQL Server can actually [...]
Archive of entries posted on June 2010
Transform Each Row Into XML
This neat and simple little trick will help to transform each row in a table or query into an XML row. The simplest way is to use the FOR XML clause. However if you want to return the XML as a separate column in the table, you need to perform a self-join on the table [...]
Using XQuery
XQuery in SQL Server was introduced in version 2005, along with so many major advancements. The ability to store and query XML has changed the game of SQL Server and has made it what it more of what it should be, a better workflow / storage engine. There are some things to consider prior to [...]
Using sp_configure
sp_configure is a system stored procedure designed to show and change server-level configuration settings on SQL Server. When executed without parameters, this procedure will enumerate the basic configuration settings on the SQL Server. EXEC sp_configure These results show us 16 configuration options, however these are only the basic configuration options. There are also advanced options [...]
Using sp_addlinkedserver
The system stored procedure sp_addlinkedserver is used in order to link a server from the server in which you are executing the command. In order to connect to a remote SQL Server, you basically only need 3 parameters. EXEC sp_addlinkedserver @server=N’HOGEN-PC’, — Remote Computer Name @srvproduct=N”, — Not Needed @provider=N’SQLNCLI’, — SQL Server Driver @datasrc=N’HOGEN-PC\SQLEXPRESS’; [...]
Using sp_who
sp_who is a system stored procedure designed to return back information regarding the current sessions in the database. These sessions are commonly referred to as SPIDS (Server Process ID’s). While sp_who is sometimes used, it’s sister procedure sp_who2 is used far more often. This is because sp_who2 returns back more information than sp_who. Let’s look [...]
Using sp_help
Sp_help is a system stored procedure that is similar to a ‘help’ command that is issued at a command prompt. This procedure is extremely useful in the database because almost any discreet object can be passed as a parameter in order to return back detailed information regarding the object. It should be noted that it [...]
Using sp_spaceused
sp_spaceused is a system stored procedure that reveals the amount of space used. This procedure can take many different parameters as input in order to show the amount of space on disk that is used. If your system can afford it, it is best to update the usage stats before finding the space used. This [...]
Using sp_rename
sp_rename is a system stored procedure that can rename objects. This procedure should be used with caution however, because when renaming an object that is referenced by other objects, will not update the name in the object reference. It is also a better practice to drop and recreate objects rather than using this procedure. That [...]
Using Coalesce
The coalesce function is used to find the first non-null value. The function takes limitless number of parameters in order to evaluate the first non null. If all the parameters are null, then COALESCE will also return a NULL value. — hard coded example SELECT MyValue = COALESCE(NULL, NULL, ‘abc’, 123) The example above returns [...]
