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
Delete
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
=====================================================================
Delete
From Anonymous 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
Delete
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 .Delete
====================================================================
From Anonymous Ahmed Bouzamondo
Hi, I found your scripts about index usages very useful. Good workDelete
====================================================================
FromAnonymous 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 testDelete
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 :-)



2 comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts