Pad Numbers By Using CASE, LEFT And CONVERT

Let's say you have a table with integer values below 100000 and you want them in the same format with leading zeros. For example 500 has to be 000500 and 1 has to be 000001
How do you do this in SQL server?
Below are two ways to accomplish this.

CREATE TABLE #ValueTable (value INT)
INSERT INTO #ValueTable
SELECT 1
UNION ALL
SELECT 500
UNION ALL
SELECT 4000
UNION ALL
SELECT 50000

--Use CASE, LEN and CONVERT to pad the values
SELECT value,CASE LEN(value)
WHEN 1 THEN '00000' + CONVERT(VARCHAR,value)
WHEN 2 THEN '0000' + CONVERT(VARCHAR,value)
WHEN 3 THEN '000' + CONVERT(VARCHAR,value)
WHEN 4 THEN '00' + CONVERT(VARCHAR,value)
WHEN 5 THEN '0' + CONVERT(VARCHAR,value)
ELSE CONVERT(VARCHAR,value)
END AS Formattedvalue
FROM #ValueTable


--Use LEFT, LEN and CONVERT to pad the values
SELECT value,LEFT('000000',(6 -LEN(value )))
+ CONVERT(VARCHAR,value) AS Formattedvalue
FROM #ValueTable

I have received a comment from Rob Farley who was so kind to point out to me that I should have used RIGHT instead of left
Here is the code he supplied

--Use RIGHT to pad the value
SELECT value, RIGHT('000000' + CONVERT(VARCHAR,value),6) AS FormattedValue
FROM #ValueTable

SQL Server 2005 launch Webcast

Today at 9AM Pacific Standard Time Steve Ballmer launches Microsoft SQL Server 2005, the next generation data management and analysis software. You can follow the launch, including a Webcast of the launch from this Web site:
http://www.microsoft.com/windowsserversystem/applicationplatform/launch2005/default.mspx

SQL Server product site: http://microsoft.com/sql
SQL Server Developer Center: http://msdn.microsoft.com/sql
SQL Server TechCenter: http://technet.microsoft.com/sql/
Data Access and Storage Developer Center: http://msdn.microsoft.com/data

Formatting Data By Using CHARINDEX And SUBSTRING

Let's say you have names stored in the format [Klein, Barbara] but would like it to be [Barbara Klein]
How do you accomplish that in SQL?
SQL provides 2 useful functions (CHARINDEX And SUBSTRING)
Run the code below to see how they work

CREATE TABLE Names (ID INT identity not null,NameField VARCHAR(50), ProperNameField VARCHAR(50))

INSERT INTO Names
SELECT 'Klein, Barbara',NULL
UNION ALL
SELECT 'Smith, John',NULL
UNION ALL
SELECT 'Jackson, Michael',NULL
UNION ALL
SELECT 'Gates, Bill',NULL


UPDATE Names
SET ProperNameField =SUBSTRING(NameField,CHARINDEX(',',NameField) + 2,
LEN(NameField) - CHARINDEX(',',NameField))
+ ' ' + LEFT(NameField,CHARINDEX(',',NameField)-1)


SELECT * FROM Names

Finding ASCII, Numbers and Alphabet Characters In SQL Server Tables

SQL Server has limited support for regular expressions,
You can run the code below to see what some of the results are by searching for data while using regular expressions

CREATE TABLE AsciiTest (id INT identity, TextField VARCHAR(50))
INSERT INTO AsciiTest
SELECT '%&&&123%#$#$'
UNION ALL
SELECT 'safdsfsdrfsdfse'
UNION ALL
SELECT '12121212'
UNION ALL
SELECT '1212asasas'
UNION ALL
SELECT '%&&&%#$#$'
UNION ALL
SELECT '4564gg565656'
UNION ALL
SELECT '12'

--No Alphabet Characters
SELECT * FROM AsciiTest
WHERE TextField NOT LIKE '%[a-Z]%'

--Starting with a number
SELECT * FROM AsciiTest
WHERE TextField LIKE '[0-9]%'

--Ending with a number
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[0-9]'

--Numbers and characters
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[0-Z]%'

--Only non alphabet/numbers characters
SELECT * FROM AsciiTest
WHERE TextField NOT LIKE '%[0-Z]%'


--some non alphabet/number characters
SELECT * FROM AsciiTest
WHERE TextField LIKE '%[^0-Z]%'

-- ASCII Code 38 (&)
SELECT * FROM AsciiTest
WHERE TextField LIKE '%' + char(38) + '%'

Top Google Searches On SQL Server Code

These are the top SQL Searches on this site for the month of October.
I have left out searches that have nothing to do with SQL Server or programming
Here are the results...

non-ANSI outer join
HOW TO COMMENT CODE IN sql SERVER
veritas 10 sql module
backup log files
CHAINTECH 6BTM0
Free SQL Code help
query analiser


An interesting list, lets say what next month will bring

SQL Server/Math Puzzle

Use the numbers 3,4,5,6 and the operators +, -, / and * to get the number 28
You can use each number only one time and also each operator one time
You don't have to use all operators, as far as I know the only way is to use all the numbers
Create a SQL SELECT statement You will need parentheses to make it work, good luck

The solution is below highlight to see, but don't give up so fast

solution below (highlight to see)
select 4*(5+6/3)
solution above (highlight to see)

Percentage Of NULLS And Values In A SQL Server Table

Sometimes you want to know what the percentage is of null values in a table for a field
Or you might want to know what the percentage of all values in a field is grouped by value
You can get these answers by running the code below

CREATE TABLE #perc ( Field1 INT,Field2 INT,Field3 INT)
INSERT INTO #perc
SELECT NULL,1,1
UNION ALL
SELECT 1,1,1
UNION ALL
SELECT NULL,NULL,1
UNION ALL
SELECT NULL,1,NULL
UNION ALL
SELECT NULL,1,1
UNION ALL
SELECT 1,1,NULL
UNION ALL
SELECT NULL,1,1
UNION ALL
SELECT 2,1,2
UNION ALL
SELECT 3,1,1


--Get the percentage of nulls in all the fields in my table
SELECT 100.0 * SUM(CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field1Percent,
100.0 * SUM(CASE WHEN Field2 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field2Percent,
100.0 * SUM(CASE WHEN Field3 IS NULL THEN 1 ELSE 0 END) / COUNT(*) AS Field3Percent
FROM #perc


--Get the values and the percentage of all values in a field
SELECT Field3 AS Value,COUNT(Field3) AS ValueCount,
100.0 * COUNT(coalesce(Field3,0))/(SELECT COUNT(*) FROM #perc ) AS Percentage
FROM #perc
GROUP BY Field3
ORDER BY Percentage DESC



DROP TABLE #perc

Microsoft SQL Server Developer Edition 2005 CD/DVD For Sale On Amazon

For all of you people who don't have a MSDN subscription, Amazon is selling the developer edition of SQL Server 2005 for $59.99 with free shipping

Microsoft SQL Server Developer Edition 2005 CD/DVD

SQL Server 2005 and Visual Studio 2005 are released!!!

SQL Server 2005 and Visual Studio 2005 are available for download at the MSDN site
For people who have a subscription go here
http://www.msdn.microsoft.com/subscriptions/

Enjoy

SQL Server 2005 Certification Links

Below are the links for the SQL Server Certification exams

Microsoft Certified Technology Specialist: SQL Server 2005

Microsoft Certified IT Professional: Database Administrator

Microsoft Certified IT Professional: Database Developer

Microsoft Certified IT Professional: Business Intelligence Developer

Microsoft Certified Database Administrator

My Top 3 SQL Server Books

This is my top 3 SQL server books.
They are not in any order. I use all 3 of them frequently

The Guru's Guide to Transact-SQL
by Ken Henderson
This is a fantastic book, Ken has done such a great job. I have bought this book in December 2001 after reading some reviews on Amazon'
I must tell you that it is much better than I would ever have expected.
There are so many cool things that I didn't know you could do in SQL Server, the chapter about undocumented things is a gem.
Ken will show you many ways to accomplish something and of course every example is better than the one before it
If you are a SQL Server Programmer then this book belongs in your library, it doesn't matter if you are a beginner or a MVP you will definitely learn something from this book
Another bonus is that all the source code is on a CD


Inside Microsoft SQL Server 2000
by Kalen Delaney
If you need to know about locking/blocking/deadlocks/performance then this is the book for you It goes into such detail and with great examples on how to find out what causes deadlocks and how to prevent them. For example using sp_lock2
The SQL server architecture part is a must read as well the chapter about cursors and large objects


Microsoft SQL Server 2000 Unleashed (2nd Edition)
by Ray Rankins, Paul Jensen, Paul Bertucci
This is a very good reference book about SQL Server it covers everything from programming to administration to OLAP. I keep it on my desk when I need to look up things (with the help of post-it notes)

SQL Server 2005 Books On Line on MSDN

For those of you who don't want to download the books on line for SQL Server 2005 but would like to see it online here is the link (http://msdn2.microsoft.com/en-us/library/ms130214(en-US,SQL.90).aspx)

Here are the links for the main categories

Database Engine(link)
The Database Engine is the core service for storing, processing and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise. The Database Engine also provides rich support for sustaining high availability.

Analysis Services(link)
Analysis Services delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by allowing you to design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services enables you to design, create, and visualize data mining models. These mining models can be constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

Integration Services(link)
Integration Services is a platform for building high performance data integration solutions, including packages that provide extract, transform, and load (ETL) processing for data warehousing.

Replication(link)
Replication is a set of technologies for copying and distributing data and database objects from one database to another, and then synchronizing between databases to maintain consistency. By using replication, you can distribute data to different locations and to remote or mobile users by means of local and wide area networks, dial-up connections, wireless connections, and the Internet.

Reporting Services(link)
Reporting Services delivers enterprise, Web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.

Notification Services(link)
Notification Services is an environment for developing and deploying applications that generate and send notifications. You can use Notification Services to generate and send timely, personalized messages to thousands or millions of subscribers, and can deliver the messages to a variety of devices.

Service Broker(link)
Service Broker helps developers build scalable, secure database applications. This new Database Engine technology provides a message-based communication platform that enables independent application components to perform as a functioning whole. Service Broker includes infrastructure for asynchronous programming that can be used for applications within a single database or a single instance, and also for distributed applications.

Full-Text Search(link)
Full-Text Search contains the functionality you can use to issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.

Some Undocumented DBCC Commands

DBCC MEMORYSTATUS
This DBCC command provides detailed info about SQL Server memory usage

DBCC TRACEON(3604)
DBCC Resource
DBCC TRACEOFF(3604)
This DBCC command list SQL Server resource utilization

DBCC DBREINDEXALL('pubs')
This DBCC command will rebuild all the indexes for a user Database, the DB has to exclusively locked or you will get the error message “The database could not be exclusively locked to perform the operation.”

DBCC FLUSHPROCINDB
This will force a recompile of all the stored procedures in a database

DECLARE @ID INT
SET @id =DB_ID('pubs')
DBCC FLUSHPROCINDB(@ID)

Find Out How Many Occurrences Of A Substring Are In A String

If you want to know how many occurrences of a substring you have in a string play around with the code below. The trick here is to take the total length of the string, subtract the same string minus the occurrences and divide by the length of the substring

For example
String is ’ABABABABZZZZZ’, length is 13
Substring is ’AB’ length is 2
Length of string minus all occurrences of substring is 5
So (13 -5) /2 =4 occurrences

DECLARE @chvString VARCHAR(500)
SELECT @chvString ='ababababajdfgrhgjrhgierghierabababaaaaaaaabbbbbbbaaaa'
DECLARE @chvSearchString VARCHAR(50)
SELECT @chvSearchString = 'ab'
SELECT LEN(@chvString) AS StringLength,
LEN(@chvSearchString) AS SearchForStringLength,(LEN(@chvString)-
(LEN(REPLACE(@chvString,@chvSearchString,''))))/LEN(@chvSearchString) AS HowManyOccurances

Do Not Drop And Create Indexes On Your Tables

When you do this the nonclustered indexes are dropped and recreated twice, once when you drop the clustered index and then again when you create the clustered index.

Use the DROP_EXISTING clause of the CREATE INDEX statement, this recreates the clustered indexes in one atomic step, avoiding recreating the nonclustered indexes since the clustered index key values used by the row locators remain the same.

Here is an example:

CREATE UNIQUE CLUSTERED INDEX pkmyIndex ON MyTable(MyColumn)
WITH DROP_EXISTING

Which Service Pack Is Installed On My SQL Server

To get the answer to this question run the code below

SELECT SERVERPROPERTY('ProductVersion') AS SqlServerVersion,
SERVERPROPERTY( 'ProductLevel') AS ServicePack

There are 3 possible values for ProductLevel

'RTM' = shipping version.
'SPn' = service pack version
'Bn' = beta version.

List All The Indexes In My Database

The query below will list all the indexes on all user tables in the database

SELECT OBJECT_NAME ( si.id ) AS TableName ,
CASE indid WHEN 1 THEN 'Clustered'
ELSE 'NonClustered'
END TypeOfIndex,
si.[name] AS IndexName
FROM sysindexes si
JOIN sysobjects so ON si.id =so.id
WHERE xtype ='U'
AND indid < 255
ORDER BY TableName,indid

Concatenate Data Into One Comma Delimited String

To concatenate data from several rows you can use the code below
I have declared the variable as varchar 1000, adjust the size if you think the size doesn't fit your needs

USE pubs

DECLARE @chvCommaDelimitedString VARCHAR(1000)

SELECT @chvCommaDelimitedString = COALESCE(@chvCommaDelimitedString + ',', '') + REPLACE(au_lname,'''','''''')
FROM dbo.authors

SELECT @chvCommaDelimitedString

Find Out Server Roles For a SQL Server Login

This is a quick way to find out what roles a user has on the SQL Server
Just change the value of @chvLoginName to the login you want

DECLARE @chvLoginName VARCHAR(50)
SELECT @chvLoginName='BUILTIN\Administrators'

SELECT [name],sysadmin,securityadmin,serveradmin,
setupadmin,processadmin,diskadmin,
dbcreator,bulkadmin,loginname
FROM master..syslogins
WHERE loginname =@chvLoginName

-------------------------------------------------------------------------------
The query below returns all logins whose role is a certain role.
The available roles are:

sysadmin
Can perform any activity in SQL Server.

serveradmin
Can set serverwide configuration options, shut down the server.

setupadmin
Can manage linked servers and startup procedures.

securityadmin
Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords.

processadmin
Can manage processes running in SQL Server.

dbcreator
Can create, alter, and drop databases.

diskadmin
Can manage disk files.

bulkadmin
Can execute BULK INSERT statements.

SELECT [name],sysadmin,bulkadmin
FROM master..syslogins
WHERE sysadmin =1 or bulkadmin =1

How to find all the tables and views in a database

Use the code below

USE PUBS
GO

--Get All Tables
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO

--Get All Views
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
GO

That's all, plain and simple