How to Concatenate Column Values in SQL Server 2005

At times, we need to concatenate the values of columns and present it to the user. Let us see how to Concatenate Column Values in SQL Server 2005, espcially when one column is a varchar and the other is an integer.

-- SCRIPT To Create Table

CREATE TABLE #Customers (id integer, cname varchar(20), pincode int)

-- INSERT sample rows

INSERT INTO #Customers VALUES (1, 'Jack',45454 )
INSERT INTO #Customers VALUES (2, 'Jill', 43453)
INSERT INTO #Customers VALUES (3, 'Tom', 43453)
INSERT INTO #Customers VALUES (4, 'Kathy', 34544)
INSERT INTO #Customers VALUES (5, 'David', 65443)
INSERT INTO #Customers VALUES (6, 'Kathy', 65445)
INSERT INTO #Customers VALUES (7, 'Kim', 65443)

-- Concatenate Values

SELECT ID, 'Employee ' + cname + ' has a pincode ' + CAST(pincode as varchar(8)) as Info
FROM #Customers

How To Return Random Records From A Table

Let us see how to return random 'n' records from a table

-- SAMPLE SCRIPT

CREATE TABLE #Customers (id integer, cname varchar(20), pincode int)

-- INSERT SAMPLE RECORDS

INSERT INTO #Customers VALUES (1, 'Jack',45454 )
INSERT INTO #Customers VALUES (2, 'Jill', 43453)
INSERT INTO #Customers VALUES (3, 'Tom', 43453)
INSERT INTO #Customers VALUES (4, 'Kathy', 223434)
INSERT INTO #Customers VALUES (5, 'David', 65443)
INSERT INTO #Customers VALUES (6, 'Kathy', 456556)
INSERT INTO #Customers VALUES (7, 'Kim', 65443)

-- Return 3 random records from the #Customers table

SELECT TOP 3 cname,pincode FROM #Customers ORDER BY NEWID()

So what happens over here is that the NEWID() is used to generate a unique value of type uniqueidentifier. So ordering the results by this ID every time gives us random rows. Cool!!

Understanding Authentication and Authentication Mode in Sql Server 2005

There is a difference between 'Authentication' and 'Authentication mode' in SQL Server 2005.

Authentication (2 types) - Windows and SQL Server Authentication.

Authentication mode (2 types) - Windows Authentication mode and Mixed Mode.

When using 'Windows authentication mode' you can only use Windows authentication to connect to SQL Server. When using 'Mixed mode' you can use either 'Windows authentication' or 'SQL Server authentication' to connect to SQL Server 2005

When to use what?

'Windows Authentication Mode' is much more secure than Mixed Mode. Windows Authentication utilizes Kerberos security protocol. Remember that in a typical installation, Windows Authentication is the default security mode. So when a user having a Windows user account connects to SQL Server, the server validates the account credentials using information in the Windows operating system.

SQL Server Authentication is provided for backward compatibility only. Whenever possible, use Windows Authentication.

If all the users users accessing the database are Microsoft Windows users, use 'Windows authentication mode' . If your environment consists of Windows users and Non-Windows users use 'Mixed mode'.

Copy a table from one database to another in SQL Server 2005

If you have a table in a database and you would like to copy the table to another database, use this query:

SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers

Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.

If you want to transfer all the objects from one database to another, open Sql Server Management Studio > Right click on your database > All Tasks > Generate SQL Scripts. Then run these scripts against the new database.

Transfer both schema and data

To copy both data and schema, use the Microsoft SQL Server Database Publishing Wizard 1.1. This tool works for both SQL 2000 and SQL 2005 and generates a single SQL script file which can be used to recreate a database (both schema and data).

How to quickly analyze a slow running query using SHOWPLAN_TEXT

To quickly analyze a slow-running query, examine the query execution plan to determine what is causing the problem.

SET SHOWPLAN_TEXT causes SQL Server to return detailed information about how the statements are executed.

Eg:

USE Northwind;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT *
FROM Customers
WHERE CustomerID = 'ALFKI';
GO
SET SHOWPLAN_TEXT OFF;
GO

Displays how indexes are used:
--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), SEEK:([Northwind].[dbo].[Customers].[CustomerID]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)

Some Important Points :

1. SET SHOWPLAN_TEXT cannot be specified when using a stored procedure

2. You need to have the SHOWPLAN permission while running SET SHOWPLAN_TEXT

Read more about it over here

How to set up your database for Distributed queries

SQL Server 2005 provides you with the sp_addlinkedserver procedure. This proc creates a linked server to fire distributed queries against OLE DB data sources.

Let us see how to create a linked server against different data sources

Link to SQL Server 2005

EXEC sp_addlinkedserver
@server='FirstLS',
@srvproduct='SQL Server',
@provider='SQLNCLI',
@datasrc='servername\instance'

where @server is the name of the linked server; @srvproduct is the OLEDB Data Source to add (can be kept blank for SQL Server); @provider is the OLE DB provider that corresponds to the data source; @datasrc is the data source

Link to Access

EXEC sp_addlinkedserver
@server = 'SecondLS',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'Access 2003',
@datasrc = 'C:\Data\MyData.mdb

Link to Excel

EXEC sp_addlinkedserver 'ThirdLS', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'c:\Data\MySheet.xls', NULL, 'Excel 5.0

Link to Oracle

EXEC sp_addlinkedserver
@server = 'FourthLS',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'Server1'

Check if a user has access to a database in Sql Server 2005

HAS_DBACCESS returns information about whether the user has access to the specified database (BOL).

Example:

SELECT HAS_DBACCESS('Northwind');

returns

1 if the user has access to the database
0 if the user does not have access to the database
NULL if the database does not exist

Find all databases that the current user has access to

SELECT [Name] as DatabaseName from master.dbo.sysdatabases
WHERE ISNULL(HAS_DBACCESS ([Name]),0)=1
ORDER BY [Name]

This query was written by a guy named safigi in sqlteamforums.

Passing parameter to the TOP clause

The TOP clause in SQL Server 2005 has been enhanced. You can now specify an expression as the number definition in the TOP clause. This makes your TOP clause dynamic as you can pass the number value in a variable and use that variable in the TOP clause of your T-Sql query

Sample Usage:

DECLARE @TopVar AS int
SET @TopVar = 20

SELECT TOP(@TopVar)
CustomerID,CompanyName, ContactName
FROM Northwind.dbo.Customers

Execute a T-SQL statement at a given time

The 'WAITFOR' command is used to delay the execution of a batch, stored procedure or transaction till a specified time duration or till an actual time. Let me demonstrate this:

Create a DELAY for a specific amount of time

USE NORTHWIND
WAITFOR DELAY '00:01:00'
BEGIN
SELECT CustomerID, CompanyName, ContactName FROM CUSTOMERS
END

Delays the execution of the T-Sql statement by 1 minute. To delay by an hour, you would use '01:00:00'. You can specify a maximum of 24 hours.

Execute at the given time (actual time)

USE NORTHWIND
WAITFOR TIME '11:23:00'
BEGIN
SELECT CustomerID, CompanyName, ContactName FROM CUSTOMERS
END

Delays the execution of the T-Sql statement till the time '11:23 A.M'. You cannot specify a date, only time is allowed.

Using CASE function to evaluate one or more conditions

As mentioned in the BOL 'CASE Function evaluates a list of conditions and returns one of multiple possible result expressions'. In short CASE function returns a value by evaluating the expression given to it.

Let us see how we can add another column called 'Continent' to the Customers table of the Northwind database. We will be using the CASE function to evaluate the 'Continent' by giving the Country list as the expression.

USE NORTHWIND
SELECT CustomerID, CompanyName,Country, Continent =
CASE
WHEN COUNTRY IN ('Austria','Belgium','Denmark','Finland','France','Germany',
'Ireland','Italy','Norway','Poland','Portugal','Spain','Sweden','Switzerland','UK') THEN 'Europe'
WHEN COUNTRY IN ('Argentina','Brazil','Venezuela') THEN 'SouthAmerica'
WHEN COUNTRY IN ('Canada','Belgium','Mexico','USA') THEN 'NorthAmerica'
ELSE 'UNKNOWN'
END
FROM CUSTOMERS

Once you run this query, you will find that the continent column gets displayed based on the WHEN expression using the CASE function