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"