SQL server basics/job interview questions Part 3

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

From rezwan_marshall

Hi Amish- have questions.When upgrade to sql server sp3 1) Do I need to disconnect applications prior to sp3 installation and connect them back after? 2) What generic tests or (cursory patching test) can be done on sql server and the applications to verify that the upgrade was successful ? Thanks in advance -Marshall

6:21 PM

Answer

Yes, You need downtime for this. Always take downtime before doing any major upgradation in SQL Server To get version of SQL Server select SERVERPROPERTY (‘productlevel’) This will give you current version of SQL Server.Use it to check version of SQL Server after upgradation Amish Shah

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

From rezwan_marshall

Hi. I need to implement a trigger to email me when someone creates a database on a server without letting me know.I dont know how to write the trigger.Can you help please? Thanks Rezwan

6:24 PM

Answer

you have to use ddl trigger which controls different activity on the server Here is example for trigger on create database statement CREATE TRIGGER MONITOR_DATABASE ON ALL SERVER FOR CREATE_DATABASE AS BEGIN DECLARE @CREATE_DB_STATEMENT VARCHAR(MAX) SELECT @CREATE_DB_STATEMENT = EVENTDATA().value(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’) SELECT @CREATE_DB_STATEMENT /* use sp_send_dbmail statement here */ END you have to use sp_send_dbmail in trigger to send mail and use @CREATE_DB_STATEMENT as message for mail .

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

From Ahmed Bouzamondo

Hi, I found your scripts about index usages very useful. Good work

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

From Prakash

I am running following query on adventureworks but its giving me error Msg 319, Level 15, State 1, Line 4 Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. declare @id int set @id = 109 with test as (select *, row_number() over (order by loginid ) as rownum from HumanResources.Employee where managerid = @id) select * from test

Answer

You have to use CTE in BEGIN END clause. DECLARE @id INT SET @id = 109 BEGIN WITH test AS (SELECT *, Row_number() OVER(ORDER BY loginid) AS rownum FROM humanresources.employee WHERE managerid = @id) SELECT * FROM test END

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

From David

Hi Amish
I want to to edition of my SQL Server. How can I do it.

Answer

serverproperty function will give you this details

select serverproperty(‘edition’)

select SERVERPROPERTY(‘productlevel’)

Look at serverproperty in BOL for more details.

Cheers
Amish Shah

===================================================================
From Anirabn
I have restored a database on other server , the server has same login but I can connect to database. Can you guide me?

Answer
You have map login to the user in database.
Look at sp_change_users_login in BOL

exec sp_change_users_login ‘update_one’,,

Look at BOL for more options.
===================================================================
Question:- Is there any performance difference between “select count(*) from table1” or “select count(1) from table1”

Answer:- No there is no difference between this. If you look at execution plan both are same.
Optimizer is smart to enough to get idea what to do 🙂

How to get backup history details

Many times we need to get backup details.
Below procedure which I am using to get backup info. It might be helpful to you guys also.

CREATE PROC Get_backup_info @db   VARCHAR(256),

                            @type VARCHAR(1) = NULL

AS

    BEGIN

        SELECTbs.database_name,

               database_creation_date,

               backup_finish_date,

               expiration_date,

               position                                 backup_postion_in_file,

               CASEtype

                 WHEN‘D’ THEN ‘Database’

                 WHEN‘I’ THEN ‘Differential database’

                 WHEN‘L’ THEN ‘Log’

                 WHEN‘F’ THEN ‘File or filegroup’

                 WHEN‘G’ THEN ‘Differential file’

                 WHEN‘P’ THEN ‘Partial’

                 WHEN‘Q’ THEN ‘Differential partial’

               END                                      AS backup_type,

               backup_size / ( 1024 * 1024 )            AS backup_size_mb,

               compressed_backup_size / ( 1024 * 1024 ) AScompressed_backup_size_mb,

               bf.physical_device_name,

               bks.is_password_protected,

               bks.is_compressed,

               bs.recovery_model,

               bs.is_snapshot,

               bs.is_readonly,

               bs.is_single_user,

               bs.has_backup_checksums,

               bs.is_damaged,

               bs.is_force_offline,

               bs.is_copy_only

        FROM   msdb..backupset bs

               INNERJOIN msdb..backupmediafamily bf

                   ONbs.media_set_id =bf.media_set_id

               INNERJOIN msdb..backupmediaset bks

                   ONbs.media_set_id =bks.media_set_id

        WHERE  bs.database_name = @db

               ANDtype = Isnull(@type, type)

        ORDER  BYbackup_finish_date

    END

Example
To get backup details of database
EXEC Get_backup_info ‘testdb’

To get log backupdetails
EXEC Get_backup_info ‘testdb’ ,’L’

SQL Server 2008:- Insert Script with DATA

If we want to generate Insert Script with data values, it was not possible before SQL Server 2008.
Now SQL Server 2008 support insert script with data also.

Here are steps for it

1. Right click on database, select task -> generate scripts

2.Follow the wizard , select DATABASE

3.In the choose script options, select Table View Options -> script data

Follow remaining options.

The script generated by wizard will have insert script with data.

How to do running sum using CTE – SQL Server

Many time we need to do runing sum of value.
In SQL 2oo5 we can do it with use of CTE

We have table which have monthwise data for 2 years. We want running sum monthwise , which needs to reset on start of year

create table #t

(month int ,

year int,

value int)

insert into #T values (1,2001,10)

insert into #T values (2,2001,20)

insert into #T values (3,2001,30)

insert into #T values (4,2001,40)

insert into #T values (5,2001,50)

insert into #T values (6,2001,60)

insert into #T values (7,2001,70)

insert into #T values (8,2001,80)

insert into #T values (9,2001,90)

insert into #T values (10,2001,100)

insert into #T values (11,2001,110)

insert into #T values (12,2001,120)

insert into #T values (1,2002,5)

insert into #T values (2,2002,15)

insert into #T values (3,2002,25)

insert into #T values (4,2002,35)

insert into #T values (5,2002,45)

insert into #T values (6,2002,55)

insert into #T values (7,2002,65)

insert into #T values (8,2002,75)

insert into #T values (9,2002,85)

insert into #T values (10,2002,95)

insert into #T values (11,2002,105)

insert into #T values (12,2002,115)

go

with test

as

(select * , value as running_sum from#t where month= 1

union all

select t.*, t.value + t1.running_sum from #t t inner join test t1

on t.month = t1.month+1 and t.year = t1.year where t.month > 1

)

select * from test

order by year,month

option (maxrecursion 0)

Here we can see it do running sum monthwise , and it resets as start of year.

Now if we want running sum for all data instead of reset it at start of year
we need to generate rownumber for all rows in the table.

WITH rowtest

     AS (SELECT *,

                Row_number() OVER (ORDER BY year, month) rownum

         FROM   #t),

     test

     AS (SELECT *,

                value ASrunning_sum

         FROM   rowtest

         WHERE  rownum = 1

         UNION ALL

         SELECTt.*,

                t.value + t1.running_sum

         FROM   rowtest t

                INNERJOIN test t1

                    ONt.rownum = t1.rownum + 1

         WHERE  t.rownum &> 1)

SELECT *

FROM   test

ORDER  BY rownum

OPTION (maxrecursion 0)