Helpful topics on SQL Server

SQL Server - FAQ

This page contains a collection of the freqently asked questions about SQL Server in the various SQL Server newsgroups interlaced with some, from my own experience with the product over the years.

SQL Server - FAQ

Return a list of executable files in the current directory ?.

Is there a way to find duplicate records in a table by: Firstname, Lastname, Email?

How to count trailing spaces?

EXTRACT Numeric characters from a string

Function to capitalize the first letter

How to use the MIN() and MAX() Functions with the OR Keyword to Find Target Rows

How to add text string to a text data type

More String Examples

Pad leading zereos to a string?

Select partial words

Remove spaces from a string

How to convert a string in d/m/y format to datetime

How to calculate last day of the month

Get the name of the month from an Int value

Use TOP to select the records from row 8 to 13

Use SET method to select the nth records from a table

Sort a column that contains email addresses by "domain"

Order Resultset by Nulls

Give temp tables alias names

How to quickly create a table using existing table

How to create a select statement that would return where a certain player ranks in this list? For example, if you want to find out the ranking, according to points, of the player with the playerid of 1. In the example below, that would be 3.

How to schedule the scripting of database objects?

Error Handling in SQL Server

Different ways to load data from text files to SQL Server(DTS, bcp,Bulk Insert, ODBC)

We often add columns to replicated tables. How can we add a column without having to reinitialize the entire publication?

How many instances of SQL Server can be created?

Return a list of executable files in the current directory ?.

EXEC master..xp_cmdshell 'dir *.exe'

Is there a way to find duplicate records in a table by: Firstname, Lastname, Email?

Select Firstname , Lastname ,Email, count (*)

group by

Firstname , Lastname ,Email

having

count (*) > 1

select *

from table t

where 1 < (select count(*) from table t1 where t1.field1 = t.field1 and

t1.field2 = t.field2 etc)

How to count trailing spaces?

Select len('123' + ' ') returns 3? But it should have returned 8 (3 + 5 spaces). So how to count the last 5 spaces?

We need to use DATALENGTH() instead, because LEN ignores trailing spaces.

SELECT DATALENGTH('123' + ' ')

EXTRACT Numeric characters from a string

declare @str varchar(30)

select @str = 'MyString19796Character'

select substring(@str, patindex('%[1-9]%', @str), 5)

Function to capitalize the first letter

select master.dbo.INITCAP(lastname+ ', ' + firstname) from

northwind.dbo.employees

This will be the code for the function:

========================

create function INITCAP(@par varchar(8000))

returns varchar(8000)

BEGIN

declare @counter as int

declare @lastchar as varchar(1)

set @counter=1

set @lastchar=' '

while @counter <= len(@par)

begin

if @lastchar=' '

begin

set @par= STUFF(@par, @counter, 1, upper(substring(@par,@counter,1)))

set @lastchar=substring(@par,@counter,1)

set @counter=@counter+1

end

else

begin

select @par= STUFF(@par, @counter, 1, lower(substring(@par,@counter,1)))

set @lastchar=substring(@par,@counter,1)

set @counter=@counter+1

end

return @par

END

How to use the MIN() and MAX() Functions with the OR Keyword to Find Target Rows

DECLARE @TargetOrder int

SET @TargetOrder = 10330

SELECT

OrderId

FROM

Orders

WHERE

OrderId = @TargetOrder

OR OrderId = (SELECT MAX(OrderId) FROM orders

WHERE OrderId < @TargetOrder)

OR OrderId = (SELECT MIN(OrderId) FROM orders

WHERE OrderId > @TargetOrder)

-----SECOND METHOD----------

SELECT

TOP 3

FROM

orders

WHERE

OrderId >= (SELECT MAX(OrderId) FROM orders WHERE OrderId < @TargetOrder)

ORDER BY

OrderId

How to convert a string in d/m/y format to datetime

If you want to convert 28/01/03 to a datetime value. then use SET DATEFORMAT dmy

SET DATEFORMAT dmy
go
declare @zzzdate as datetime
set @zzzdate = '28/01/03'
select convert(datetime, @zzzdate, 103)

Use TOP to select the records from row 8 to 13

select top 5 * from #dups a

where nameid not in (select top 7 nameid from #dups b)

Use SET method to select the nth records from a table


To find the 10th row:
------------------------------------------------------------
SELECT *
FROM pubs..Titles a
WHERE 10 = (SELECT count(*) from pubs..Titles b Where 
b.title_id <= a.title_id)
ORDER BY title_id

OR

SELECT TOP 1 * FROM pubs..Titles a WHERE
 title_id NOT IN (SELECT TOP 9  title_id FROM pubs..Titles b order by title_id )
order by title_id 

OR

DECLARE @row_number INTEGER
SET @row_number = 10

SELECT *
 FROM pubs..Titles AS T
 WHERE
  (SELECT COUNT(*)
   FROM pubs..Titles
   WHERE title_id <= T.title_id)
  = @row_number

Sort a column that contains email addresses by "domain"

CREATE TABLE #Test (

Email char(256) NOT NULL

)

INSERT INTO #Test SELECT 'A@abc.com'

INSERT INTO #Test SELECT 'Ad@george.com'

INSERT INTO #Test SELECT 'b@george.com'

INSERT INTO #Test SELECT 'c@bc.com'

INSERT INTO #Test SELECT 'd@abc.com'

To achieve the following result:

A@abc.com

d@abc.com

c@bc.com

Ad@george.com

b@george.com

Use two functions:

select * from #test

order by substring(email, charindex('@', email),len(email) )

SELECT *

FROM #Test

ORDER BY STUFF(Email, 1, PATINDEX('%@%', Email), SPACE(0))

Order Resultset by Nulls

SELECT Prodid, ProdName, Type

FROM YourTable

ORDER BY ISNULL(Type, (SELECT MAX(Type) + 1 FROM YourTable))

Give temp tables alias names

SELECT * FROM #fooBarsAreGoodChocolateBars fb, #someOtherRidiculousTableName so
    WHERE fb.something = so.something
    AND fb.id = 5
    AND so.category = 'bears'

How to quickly create a table using existing table

Use SELECT * INTO T2 FROM T1

The SELECT INTO statement creates a table with the same schema and copies the data into it.

Constraints and indexes are not copied, though. IDENTITY property is. The above example will also copy all of the records from the old table into the new table... If you merely wish the table and no data , use a false where clause ie where 0 = 1 or use top 0 *

How to calculate last day of the month

SELECT

CONVERT

(

CHAR(8),

DATEADD(MONTH, 1, GETDATE()-DAY(GETDATE())+1)-1,

112

)

How to create a select statement that would return where a certain player ranks in this list? For example, if you want to find out the ranking, according to points, of the player with the playerid of 1. In the example below, that would be 3.

create table #James

(

playerid int NOT NULL primary key,

points int NOT NULL

)

insert into #James values (1, 10)

insert into #James values (5, 5)

insert into #James values (6, 12)

insert into #James values (8, 7)

insert into #James values (9, 11)

select * from #james

select j1.playerid,

(select count(*)

from #James as j2

where j2.points <= j1.points) as rank

from #James as j1

order by rank

How to schedule the scripting of database objects?

We can use the script method of SQL-DMO...here is a sample....

Dim oSS As SQLDMO.SQLServer

Dim oDb As SQLDMO.Database

Dim oT As SQLDMO.Transfer

Dim sS As String

Sub Script()

Set oSS = New SQLDMO.SQLServer

Set oT = New SQLDMO.Transfer

oSS.Connect "server", "user", "password" 'Connect to the server

Set oDb = oSS.Databases("pubs") 'Use a DB

oT.CopyAllTables = True

oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"

End Sub

More information about SQL-DMO at: Sql-Dmo

If its a SQL2000 ,we can also use SCPTXFR.exe utility in the upgrade folder to script things out.

The usage is explained in:

DTS Does Not Copy Identity, Indexes, Primary Key or Other Constraints (Q220163)

Different ways to load data from text files to SQL Server?

SQL Server provides several solutions for importing and exporting text files. Data transformation services (DTS) supports text as a data source or destination; the bcp and osql utilities can import and export text files; and T-SQL provides BULK INSERT for importing text. Another solution is less widely known: the Jet text file driver can connect SQL Server directly to text files via a linked server. This ODBC driver handles some tasks better than any of the other solutions, and it provides pure T-SQL solutions to many problems. For more click here

Another way is to use OPENROWSET like exec master..xp_cmdshell 'echo "this is a test" >> c:\temp\test.txt & echo
"this is a test2" >> c:\temp\test.txt'
go
create view v_
as
select *
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=c:\temp\;HDR=NO',
'select * from test.txt')
go
select * from v_
go
drop view v_

We often add columns to replicated tables. How can we add a column without having to reinitialize the entire publication?

A. In SQL Server 2000, you can use the sp_repladdcolumn stored procedure to add a column to a replicated table without reinitializing the entire publication because the stored procedure automatically adds the column at the subscriber. For example, if the authors table in the Pubs database has already been published, you can add the newcol integer column to that table by executing the following stored procedure:

sp_repladdcolumn @source_object = 'authors'

, @column = 'newcol'

, @typetext = 'INT'

, @publication_to_add = '<name of publication authors is

included in>'

Note that you can use the stored procedure sp_repladdcolumn to add only new columns to a replicated table; you can't use it to manage a table's existing columns. To drop existing columns from a published table, you can use the sp_repldropcolumn stored procedure.

How many instances of SQL Server can be created?

Instances per server                          16
Databases per Instance                    32,767
FileGroups per database                  256
Files per database                            32,767
Database size                                    1,048,516 TB
Data File size                                     32 TB
Log File size                                       32 TB
Total Database objects                     2,147,483,647
Columns per table                              1,024

Top

Amazon Honor System