SQL process, percentage of complete

I restore one database and want know how many percent the SQL process will be complete?

Step
1. Restore database test from disk='c:\test.mdf'
2. We can check which session belong to this process with running the below command
sp_who2 'active'
3. Check on command column, and look for "Restore database"
My SPID for this "Restore database" = 57
4. Run the below command and look for session_id=57

Select session_id,percent_complete,estimated_completion_time,total_elapsed_time
From sys.dm_exec_requests
Where session_id=57

session_id percent_complete estimated_completion_time total_elapsed_time
---------- ---------------- ------------------------- ------------------
57 26.47547 5800366 2090266

The above result show the SQL process already complete 26.47%, estimated_completion_time=5800366 and total_elapsed_time=2090266

This above example is use to show how to use sys.dm_exec_requests to show how many percent_complete of SQL process.
We can also run the below command to show percentage complete of restore process.

Restore database test from disk='c:\test.mdf' with stats
10 percent processed.
20 percent processed.
30 percent processed.
....

Disable SQL Guest user

For SQL 2005
1.
Microsoft SQL Server Management Studio -> SQLServer name-> database -> database name-> right click -> property - >Permission

User/roles -> select guest -> un-check "connect"

2.
USE
GO
REVOKE CONNECT FROM GUEST
GO

Reference
SQL Server Database Guest User Account

SQL connection string

All type of sql connection string for ODBC, OLE DB , SQLConnection.net

refer to this site

SQL Data Type - Bit

We have SQL 2000 and SQL 2005 server
we run this in SQL 2000 server and SQL 2005 Server

DECLARE @bit bit
SET @bit = 'TRUE'
IF @bit = 1
PRINT 'Yes, this is true!'
ELSE
PRINT 'No, this is FALSE!'


SQL 2000 result :
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'TRUE' to a column of data type bit.

SQL 2005 result :
Yes, this is true!


SQL 2000 bit data type won't accept 'True'!

thanks for my colleague for this finding.


SQL 2000 - Bit
Integer data type 1, 0, or NULL.

SQL 2005 - Bit
An integer data type that can take a value of 1, 0, or NULL.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Loop through all database and do something

I code this :

declare @database varchar(50)
declare @strSwitchDB nvarchar(100)
declare @count int
declare @maxRow int
SET @count = 1
SELECT @maxRow=max(database_id) FROM sys.databases
WHILE (@count <= @maxRow)
BEGIN
SELECT @database = name FROM sys.databases WHERE database_id = @count
set @strSwitchDB='use '+@database
print 'do something here, processing'
EXEC sp_executesql @strSwitchDB
set @count=@count+1
END

I expected the process will run in all databases in SQL server
example :
use master
do something
use tempdb
do something
use msdb
do something


but it actually no return expected result. The process will run in same database instead run in all database.

example
use master
do something
do something
do something

I actually find another way to do this
if we want to loop through all database and do something,
we actually can write in one line

EXECUTE sp_msforeachdb 'SELECT ''[?]'', count(1) from [?].dbo.sysobjects'

Result
It actually loop through all the database and count number of row in sysobjects table
-------- -----------
[master] 3594
-------- -----------
[tempdb] 67
------- -----------
[model] 47
------ -----------
[msdb] 754

JFYI,The above is for example and not the the actually action I want to perform

Trigger - After, Instead of

Trigger - After
An UPDATE trigger is used to perform an action after an update is made on a table

Trigger - INSTEAD OF
An Instead of trigger is used to replace the incoming statement with statemement declare in trigger.

Reference
Introduce to Trigger

Making use of INSTEAD-OF triggers in SQL Server 2005
This article explain why we use Instead-of triggers, and give example of Instead-of Triggers.

Using INSTEAD OF In Place of Triggers In SQL Server 2000
This article gives good example of "Instead of trigger". In the example, instead of trigger is used when we want replace insert statement with customize message.

Notes:
- We cannot create an AFTER trigger on views.
- AFTER triggers gets executed automatically after the PK and FK constraints.
- INSTEAD OF triggers can be created on views
- you cannot create two INSTEAD OF triggers on the view/ table for the same event.
- INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on tables that have corresponding ON DELETE or ON UPDATE cascading referential integrity defined.

reference : Implementing Triggers in SQL 2000

Order by "Alias Name"

We have this query:

create table testme11
(id int identity(1,1),
source_name varchar(20),
source_description varchar(20))

insert into testme11 values ('A',NULL)
insert into testme11 values (NULL,'Q')
insert into testme11 values ('E',NULL)
insert into testme11 values (NULL,'C')

select * from testme11
id source_name source_description
----------- -------------------- --------------------
1 A NULL
2 NULL Q
3 E NULL
4 NULL C

(4 row(s) affected)


Are this three queries will return the same result?
A.
SELECT id,ISNULL(source_name,source_description) AS source_name
FROM testme11 src
order by src.source_name

B.
SELECT id,ISNULL(source_name,source_description) AS source_name
FROM testme11 src
order by source_name

C.
SELECT id,ISNULL(source_name,source_description) AS source_name
FROM testme11 src
order by ISNULL(source_name,source_description)

result A:
id source_name
----------- --------------------
2 Q
4 C
1 A
3 E
(4 row(s) affected)

result B:
id source_name
----------- --------------------
1 A
4 C
3 E
2 Q

(4 row(s) affected)


Result C:
id source_name
----------- --------------------
1 A
4 C
3 E
2 Q

(4 row(s) affected)

Query B & C will return same result, while Query A won't return expected result

Check Schedule Job Status

In SQL Job, we can know which schedule job is running from the below step:
Microsoft SQL Server Management Studio -> Server name-> SQL Server Agent-> Job Activity Monitor->Right click -> View Job Activity

You can view which job is running with check on "Status" column

Another way to do this is run this query in Microsoft SQL Server Management Studio->New query

msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

This script will show which job is running now.

Check Last Backup

Recently, T-Mobile and Danger, the microsoft-owned subsidiary that makes the SideKick, has just announced that they've likely lost all user data that was being stored on Microsoft's Servers due to a server failure.
Source : T-MObile Sidekick Disaster: Danger's Servers Crashed, And They Don't Have A Backup

Database Server with important data without backup will kill your company. How you know you already backup your entire important database?

I not remember where I get this script, but basically this script will tell us when is our last full backup of sql database

SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as
LastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
WHERE T2.TYPE='D'
GROUP BY T1.Name
ORDER BY T1.Name

if you want know more then full backup, you can run the below script

SELECT
T1.Name as DatabaseName,
backuptype=case type
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'
else 'NA'
end,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as
LastBackUpTaken
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name,T2.Type
ORDER BY T1.Name

reference :
BackupSet
T-MObile Sidekick Disaster: Danger's Servers Crashed, And They Don't Have A Backup

SQL 2005 - Outer apply, Cross apply

SQL - using apply : Outer apply, Cross apply and example

There are two forms of APPLY: CROSS APPLY and OUTER APPLY.

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function.

OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.


Example: We have EmployeeMaster and sales table we want know which through they year the sales person achieve the highest sales and which month

Create Table EmployeeMaster
(
EmpId int,
EmpName varchar(50),
Age int,
Grade varchar(5)
)


Create Table Sales
(
SalesId int identity(1,1),
EmpId int,
SalesAmount int,
Month varchar(3)
)
Go


Insert into Sales values(1,32000,'Jan')
Insert into Sales values(1,12000,'Feb')
Insert into Sales values(1,42000,'Mar')
Insert into Sales values(1,32000,'Apr')
Insert into Sales values(1,52000,'May')
Insert into Sales values(1,32000,'Jun')
Insert into Sales values(2,2000,'Jan')
Insert into Sales values(2,7000,'Feb')
Insert into Sales values(2,15600,'Mar')
Insert into Sales values(2,9000,'Apr')
Insert into Sales values(2,7500,'May')
Insert into Sales values(2,560,'Jun')

set statistics io on

Normally we will code as

Select E.EmpName,
(select top 1 SalesAmount from Sales L
where L.EmpID=E.EmpId
order by L.SalesAmount desc) as SalesAmount,
(select top 1 month from Sales L
where L.EmpID=E.EmpId
order by L.SalesAmount desc) as month1
from EmployeeMaster E

Table 'Sales'. Scan count 6, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EmployeeMaster'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


In SQL 2005, we can use OUTER Apply

Select E.EmpName, A.SalesAmount,[Month] from EmployeeMaster E
OUTER APPLY
(
Select top 1 SalesAmount,[Month] from Sales L
where L.EmpID=E.EmpId
order by L.SalesAmount desc
) A

Table 'Sales'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'EmployeeMaster'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Result

EmpName SalesAmount Month
-------------------------------------------------- ----------- -----
James 52000 May
May 15600 Mar
Henry NULL NULL


set statistics io off

Refer to above,

1. The query cost for first statement (66%) is double then second statement (34%)
which using OUTER APPLY.
(Highlight the above 2 query in query analyzer and click on Display Estimation Execution Plan)

2. Page Read for Sales (Using set statistics io on) , the first statement is 6 and second
statement is 3. Page read for first statement is double that second statement



Notes:

To use APPLY, the database compatibility level must be at least 90.

Reference:

Using APPLY

Cross Apply and Outer Apply in SQL Server 2005

Isolation level in SQL2005?

If you don't know what is the isolation level in SQL 2005

you can run this command


DBCC useroptions

and exam the row labeled "Isolation Level".

my setting is
"isolation level read committed"

Compare delimiter data from 2 table

Table 1
ID | Country
---------------
1 | French， China，Japan
2 | Malaysia, Singapore

Table 2
ID | Country1
---------------
1 | Japan， China


Result i want :
ID | Country
---------------
1 | French, Malaysia, Singapore


create table test1
(id int,
country varchar(50))
create table test2
(id int,
country varchar(50))
insert into test1 values (1,'French, China,Japan')
insert into test1 values (2,'Malaysia, Singapore')
insert into test2 values (1,'Japan, China')


WITH extract1 (country_Attributes) AS
(
SELECT
CONVERT(XML,''
+ REPLACE(country,',', '')
+ '') AS country_Attributes
FROM test1
union all
SELECT
CONVERT(XML,''
+ REPLACE(country,',', '')
+ '') AS country_Attributes
FROM test2
)
SELECT
country_Attributes.value('/country[1]/Attribute[1]','varchar(25)') AS a,
country_Attributes.value('/country[1]/Attribute[2]','varchar(25)') AS b,
country_Attributes.value('/country[1]/Attribute[3]','varchar(25)') AS c,
country_Attributes.value('/country[1]/Attribute[4]','varchar(25)') AS d
into #temp
FROM extract1;

select country into #temp2 from
(SELECT rtrim(ltrim(colval)) as country
FROM
(SELECT a, b,c,d
FROM #temp) p
UNPIVOT
(ColVal FOR Col IN
(a, b, c,d)
)AS unpvt) tmp
group by country
having count(1)<2

declare @retstr varchar(8000)
select @retstr = COALESCE(@retstr + ',','') + country
from #temp2
print @retstr

drop table #temp
drop table #temp2

SQL 2005 execute as

I need to login as user when i want to test their permission against the database object and most of time I forgot/ never record user password and need request password from them

with SQL 2005, I don't need relogin, I can do this as below

login as sa
execute as user='user'
--select from login table with "user" permission
select * from login

revert
--revert back to sa