Sql server DBA Inter view questions

What are the steps to take to improve performance of a poor performing query?
Maximum use of indexes, stored procures should be done.
Avoid excessive use of complicated joins and cursors.
Avoid using conditional operators using columns of different tables.
Make use of computed columns and rewriting the query.

Download more questions

Remove special charcters in a column

Sql server provides replace function. By using this we can remove any character and replace with any char.

Example:

my phone number is like 578(45-89564_)

If i want to remove special char's from the above we will do the following.

Replace -,\,),( with blanks

update business set phone1=replace(replace(replace(replace(phone1,'-',''),'',''),')',''),'(','')

Update data fromTWO tables

Update data from TWO tables:

UPDATE Business
SET Profile_City = Temp_Zipcodes.City,
Profile_State = Temp_Zipcodes.State
FROM Business, Temp_Zipcodes
WHERE
Business.Profile_Zipcode = Temp_Zipcodes.Zipcode

here business and Temp_Zipcodes are my tables.
I want to update city and state names if both the zipcodes are matched from my both tables.

SQL Server Error Handling

SQL Server Error Handling :

/*
Error handling in SQL Server breaks down into two very distinct
situations: you're handling errors because you're in SQL Server 2005
or you're not handling errors because you're in SQL Server 2000.
What's worse, not all errors in SQL Server, either version, can be
handled. I'll specify where these types of errors come up in each
version.

The different types of error handling will be addressed in two
different sections. 'll be using two different databases for the
scripts as well, [pubs] for SQL Server 2000 and [AdventureWorks]
for SQL Server 2005.

I've broken down the scripts and descriptions into sections. Here is
a Table of Contents to allow you to quickly move to the piece of code
you're interested in. Each piece of code will lead with the server
version on which it is being run. In this way you can find the section
and the code you want quickly and easily.
As always, the intent is that you load this workbench into Query Analyser
or Management Studio and try it out for yourself! The workbench script
is available from the CODE DOWNLOAD link above.

SQL Server 2005 - GENERATING AN ERROR */
---------------------------------------
USE AdventureWorks;
GO
UPDATE HumanResources.Employee
SET MaritalStatus = 'H'
WHERE EmployeeID = 100;

/* This generates a familiar error:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint
"CK_Employee_MaritalStatus".
The conflict occurred in database "AdventureWorks",
table "HumanResources.Employee", column 'MaritalStatus'.
The statement has been terminated.

how to Set DATA BASE recovery mode full ?

Set data base recovery mode is full?
Run folliing sql in to your querry analizer.
USE master;
--Make sure the database is using the full recovery model.
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO
Change AdventureWorks to your data base name.

How do we know which files and file groups are ONLINE?

How do we know which files and file groups are ONLINE?
The following SQL shows files status.
select fg.name as FilegroupName, fg.type_desc as FileGroupType,
df.name as FileName, df.physical_name, df.state_desc
from sys.database_files df left outer join sys.filegroups fg on df.data_space_id
= fg.data_space_id

It shows all the files status which are in Online/ Offline.

Shrink data base/ Reduce mdf file size

Shrink data base size:
Follow the follwoing

Empty log file size:

Empty log file size:

It is used to trunctae/Reduce size of log file in sqlserver 2005.
This is outdated for sqlserver 2008.

Example:

Our data base size 30GB. log file size 20GB, and mdf file size 10GB.

Then the requiremnt comes like reduce log file size. Then we will go with follwing mechanizam, If we dont need log file tractions.


backup log [databasename] with truncate_only
go
DBCC SHRINKDATABASE ([databasename], 10, TRUNCATEONLY)
go

After exucuting above statement let check your log file size. Absolutly it size reduces.

Delete duplicate records from large data table.

Delete duplicate records from table:

For completing this task we need to do three steps:
Step 1: Identify what are all the duplicate records for our criteria
Step 2: Move all those duplicate records in to some temporary table.
Step 3: Let write some sql script for delete.

Step 1:

Identify what are all the duplicate records for our criteria:

Following query gives duplicate records list and it will move to
some temporary table

-- Script by suresh
select First_name, Last_name, city, state, count(*) as duplicate_count
into #dupes -- dupes is duplicate table name step 2
from dup_authors
group by First_name, Last_name, city, state
having count(*) > 1
order by count(*) desc, First_name, Last_name

In the first step we found duplicate records like

First_name Last_name city state duplicate_count
seep jubly Idaho IH 10

By observing this we found 10 resords for the above criteria. Then the question arises how can we delete 9 records by keeping single record in original table.

Step 3:

Write a simple cursor for deletion by using 'top' clause in deletion

SQL is:
CREATE Procedure [dbo].[CUR_DeleteDuplicateRecords_Final]

AS
BEGIN
DECLARE @Profile_name varchar(MAX)
DECLARE @Profile_Phone1 varchar(100)
DECLARE @Profile_StreetAddress1 varchar(MAX)
DECLARE @Profile_State varchar(300)
DECLARE @Duplicate_Rows_Count INT

DECLARE DuplicateRecords_Cursor CURSOR FAST_FORWARD FOR
SELECT Profile_Name, Profile_Phone1, Profile_Streetaddress1 ,Profile_State, Duplicate_Rows_Count
FROM
dups

OPEN DuplicateRecords_Cursor

FETCH NEXT FROM DuplicateRecords_Cursor INTO
Profile_name, @Profile_Phone1, @Profile_StreetAddress1,
@Profile_State,@Duplicate_Rows_Count

WHILE @@FETCH_STATUS = 0
BEGIN

--Start Delete Querry

DELETE TOP (@Duplicate_Rows_Count-1)
FROM dbo.T_Business_Profiles
WHERE Profile_name=@Profile_name and
Profile_phone1=@Profile_Phone1 and
Profile_StreetAddress1=@Profile_StreetAddress1 and
Profile_State= @Profile_State

--End Delete Querry

FETCH NEXT FROM DuplicateRecords_Cursor INTO @Profile_name, @Profile_Phone1, @Profile_StreetAddress1,
@Profile_State,@Duplicate_Rows_Count
END

CLOSE DuplicateRecords_Cursor
DEALLOCATE DuplicateRecords_Cursor

END

Identify Duplicate records in SQL Server 2005

We are having large data base and it's having vast records for example we have 10 millins of records in a data abse then we will find what are all the duplicates records based some sriteria. following is the example query.

Query:
SELECT au_lname, au_fname, city, state, RecCount=COUNT(*), PktoKeep = MAX(NewPK)
INTO #DUPLICATES
FROM dup_authors
GROUP BY au_lname, au_fname, city, state
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC, au_lname, au_fname

'INTO' is a clause it inserts the selection data in to 'DUPLICATES' temporary table.

Convert Text to Integer

Convert Text to Integer in sql server 2005:

declare @site as varchar(2000)
set @site = ‘123,234,34567,45678,567890,6789012,701,80,9′

declare @t1 table
(
t1 int
)

while charindex(‘,’,@site) > 0
begin
insert into @t1 select substring(@site,1,(charindex(‘,’,@site)-1))
SET @site = substring(@site,charindex(‘,’,@site)+1,len(@site))
end
insert into @t1
select @site

Select * from @t1

In htis exmle we declare t1 as a table variable it can create automatically, and disposes when we are complete this task. No need to worry about table variable 't1'.

It uses for converting stirng values like '101,23456,475639' -- this is string.
we will convert it as integers and pass this for querry for out put.
example:

select * from T_Employes where Employee_ID in (select * from t1)

t1 conatins all the employee id's. before all the ids are in single column separated by comma.

Rebuilding All Indexes on a Data base:

Rebuilding All Indexes on a Data base:

Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance. It will also update column statistics.

If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent (see below).

The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease:

--Script to automatically reindex all tables in a database

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 90%. You can substitute any number appropriate for the fill factor in the above script.

When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.

For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don't need access to the tables being reorganized.

Replace Some text in single column of same table:

UPDATE dbo.Tablename SET Column_name= REPLACE(column_name, 'Existing text', 'Replace text')
Example:
I have one table called L_Newsletter_Templates , with column name Newsletter_Imagepath I want to replace 'suresh' with 'localhost' then i will write my query as follows

UPDATE dbo.L_Newsletter_TemplatesSET Newsletter_Imagepath = REPLACE(Newsletter_Imagepath, 'suresh', 'localhost')

Replace is a sqlserver builtin function which can takes 3 arguments
1.Column name
2.what text we want to replace
3. with text

By using this we are simply update same thing in all columns.