SQL Server

Using Inner join or Function(Case)

2010-12-14T16:53:00.014+08:00

One of the developer using function in query to return the result that he needed. The query and write using inner join without function
What is the different ?

Example :
In adventurework :

create table HumanResources.Gender
(id char(1),
Name varchar(20))

insert into HumanResources.Gender
values ('M','This is Male')
insert into HumanResources.Gender
values ('F','This is Female')

create FUNCTION dbo.GetGender (@what char(1))
RETURNS varchar(20)
AS
BEGIN
Declare @whatGender varchar(20)
SELECT @whatGender=Name from HumanResources.Gender where id=@what
RETURN @whatGender
End

Run Display Estimated Execution plan
together for the above 2 query

What Estimated Execution plan above show?
First query Query cost is 69% compare to second query Cost 31% (25%+6%)
Is that mean second query better that first query ?

Let set statistics time on, click on
and actual run the query
set statistics time on
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id

SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
set statistics time off

Actual execution plan

From the actual exection plan
First query still have high relative cost compare to second query

From the statistics
First query CPU time = 0 ms, elapsed time = 83 ms.
compare to Second query CPU time = 15 ms, elapsed time = 91 ms.
Second query elapsed time more slower compare to first query and use more CPU resource.

Let turn on Profiler to see what is running when we execute the query

Open SQL Profiler and select only TSQL:SQL:Batch Complete and click run
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id

Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 15, Read 19, Write 0, Duration 85

In Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 31, Read 893, Write 0, Duration 85

It show First query use less resource and run more faster compare to Second query.

Go back to SQL Server Profiler, pause the trace
From File->Properties->Event Selection->Check on Show all events-> Go to TSQL Category and check on SP:StmtCompleted (SQL statement within a stored procedure has completed).
Press run to run SQL profiler again.

From Menu bar, click on "Clear Trace Window" to clear the early trace

In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id

Go back to SQL Profiler and check the result
That is only 1 line in the SQL Profiler, SQL:BatchCompleted with CPU 31, Read 893, Write 0, Duration 85

Let clear Trace Window in SQL Proiler to clear to trace result and go back to Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a

Go back to SQL Profiler and check the result
SP:stmtCompleted with
- SELECT @whatGender=Name from HumanResources.Gender where id=@what
- RETURN @whatGender

Last result is SQL:BatchCompleted with query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a

When we execute Second query, SQL need to get Name value from HumanResources.Gender and return the value to GetGender function.

If we have 291 result return, SQL will execute (291*2)+1 time in SQL Server compare to first query which only execute one

From the result above, we can conclude first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id

is better than second query

SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a

SQL Change column name and type

2010-12-14T14:11:00.006+08:00

I want change existing table column name and data type

The faster way is drop and create
If table already have data inside and we don't wanna recreate table, we can do this :

Original table structure
create table HumanResources.Gender2
(id char(1),
lang varchar(20),
Name varchar(20))

Execute below query to change column name
sp_RENAME 'HumanResources.Gender2','lang', 'lang_id' , 'COLUMN'
Caution: Changing any part of an object name could break scripts and stored procedures.

Identify store procedure which reference to Gender2.lang table

Example :
create procedure GetGender2
@id int
as
select lang,name from Gender2 where
id =@id

To identify table, trigger, view reference to Gender2.lang table, execute this
sp_depends 'Gender2'-- Gender2 is the table name

In the current database, the specified object is referenced by the following:
name type
dbo.GetGender2 stored procedure

After change the column name and excute the above store procedure, SQL will give this error message:
Msg 207, Level 16, State 1, Procedure GetGender2, Line 4
Invalid column name 'lang'.

We should change all store procedure which reference to this table

In this case, we need change Store Procedure GetGender2, change lang to lang_id

alter procedure GetGender2
@id int
as
select lang_id,name from Gender2 where
id =@id

I execute the second query to change the data type
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
Data type change successful

If original table already have data and data as below:
insert into Gender2
values ('English','This is Male')
insert into Gender2
values ('English','This is Female')

When we run
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
SQL will give error :
- Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'English' to data type int.
The statement has been terminated.

We fail to change the data type because original data is character, we can't change the data type to integer.

Reference :
SQL SERVER – How to Rename a Column Name or
Table Name

Listing SQL Server Object Dependencies

SQL 2008 Free Learning Resource Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability

2010-12-14T11:28:00.003+08:00

Read Kimberly L. Tripp Blog, and found this

"When SQL Server 2008 was in beta, Paul and I worked on a project to create content that would "jumpstart" folks in learning and working with SQL Server 2008. We delivered these "new features" courses at various locations and finally, we recorded the content. All of this content is now available FOR FREE from Microsoft through their eLearning outlet"

Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability.

You need login into Microsoft Learning to start the Clinic. The good news is this is free and valid for one year!

You must use the Role Management Tool to install or configure Microsoft.NET Framework 3.5SP1

2010-12-09T14:22:00.002+08:00

I want install SQL Express Version in Windows 2008 R2
When I Install SQL Server Express 2008, installation fail and give this error message
"need install Microsoft .NET Framework 3.5 SP1 Setup"
I Download and install Microsoft .NET Framework 3.5 SP1 Setup but fail
"You must use the Role Management Tool to install or configure Microsoft.NET Framework 3.5SP1."

From Server-> Start->Administrator tools->Server management ->Features -> Add Features->.NET Framework 3.5.1 Features

After installation, SQL intallation can contineous.

SQL 2005 Self-Paced Lab from PSS

2010-11-26T07:50:00.003+08:00

If you still using SQL 2005, or want to learn the below topic, go to download PSS Service Center Lab 2005

Blocking and Deadlocking
In these labs you will use SQLDiag, SQL Profiler, and various DMVs to solve blocking and deadlocking problems.

Performance
In these labs you will use SQLDiag, SQL Profiler, showplan, and ReadTrace to solve various query performance problems.

Debugging
In these labs you will learn more about debugging tools used by the PSS team such as SQLDumper and Windows Debugging Tools to solve problems like a crash of SQL Server.

Server and Data Recovery
In these labs you will learn to how to solve various server and database offline problems.

Netvault - Failed to Open file exclusions file

2010-11-12T09:17:00.003+08:00

I using Netvault Backup to backup my database.
The Netvault backup has an option where we can exclude file with certain extention

I add a file with extention list want to exclude in backup

When Netvault start backup, it give this message :
Backup completed with warnings

I check Netvault log and found this
A problem occurred initializing items to exclude from the backup: "Failed to open file exclusions file 'c:\file\exclude.txt"

A problem occurred initializing items to exclude from the backup: "File exclusions file 'c:\file\exclude.txt" is not a valid Unicode file

Solve with :
Open and save exclude.txt as unicode file and backup successful without warning

Query running in SQL Server

2010-10-22T09:34:00.002+08:00

I use this sript to check what query is running in server

select r.blocking_session_id,r.session_id ,h.text,substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)
else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text , r.wait_type, r.wait_time , r.last_wait_type , r.wait_resource ,
r.command , r.database_id , r.granted_query_memory, r.reads , r.writes , r.row_count , s.[host_name] , s.program_name , s.login_name
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r on s.session_id =r.session_id and s.last_request_start_time=r.start_time
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h where is_user_process = 1 order by 2 desc

SQL 2005 SSIS FAIL TO SAVE PACKAGE file error 0x80040155 "interface not registered"

2010-10-21T23:06:00.002+08:00

When I create new project in SSIS, it fail and give this message :

"FAIL TO SAVE PACKAGE file error 0x80040155 "interface not registered"

Google and get this answer:

regsvr32 msxml6.dll
regsvr32 msxml3.dll

I run the above in the command prompt and It succesful create Project in SSIS with no error.

Objects in SQL File Group

2010-07-02T10:57:00.001+08:00

I want know which object sit in SQL filegroup.
Do some google and found this script to list objects per filegroup

SQL Server 2005 Patch Fails to install with an error "Unable to install Windows Installer MSP file"

2010-05-18T10:50:00.003+08:00

I try to install SQL 2005 Service Patch 3, but it fail to install
After some Google, find this solution useful. My SQL 2005 SP3 successful install

SQL Server 2005 Patch Fails to install with an error "Unable to install Windows Installer MSP file"

Reference:
Converting a Regular GUID to a Compressed GUID

SSIS - Exception from HRESULT: 0x8007007E

2010-05-18T10:43:00.006+08:00

I try to add SSIS to SQL jobs, but it gives me this erorr:
The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)
and the SSIS UI no show

The step can be repeat with:
Open Microsoft SQL Server Management Studio ->SQLServerName-> SQL Server Agent-> Job- >right click "new job" -> In General ->keying Job name- > In steps -> Press "New"-> In Type -> Choose "SQL Server Integration Services Package" and the error message will pop out

solution
1. My computer- > right click
2. Advance -> environment Variables
3. System variables- > path

** Please change with careful **
let say you have this in path
C:\WINDOWS\system32;C:\WINDOWS
append this line at behind
C:\WINDOWS\system32;C:\WINDOWS;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\

c:\program files\Microsoft SQL Server\90\DTS\Binn\ is the location of DTS\Binn in your server
please replace with the correct path

4. close the UI
5. Close Mircosoft SQL Server Management Studio and reopen it
6. Repeat the step, and no more error.

The above solution work for my server and problem solve.

Reference
http://www.sqlservercentral.com/Forums/Topic278644-148-1.aspx
http://narfle.com/blog/2008/02/ssis-subsystem-failed-to-load.asp

SQL Server Build List

2010-05-12T11:45:00.004+08:00

SQL Server 2005 Build list maintain by By Steve Jone in sqlservercentral
http://www.sqlservercentral.com/articles/Administration/2960/

SQL Server 2008 Build list maintain by By Steve Jone in sqlservercentral
http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/

Another Source from sqlsecurity
http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

Microsoft Security -> Select SQL 2005
http://www.microsoft.com/technet/security/current.aspx

What SQL job in SQL Server

2010-03-23T11:35:00.003+08:00

My x colleague ask me how to query SQL job in SQL Server
Normally I use the below script

use msdb
select name from sysjobs
where enabled=1

Media - For DBA

2010-03-04T01:40:00.002+08:00

From Microsoft TechNet
example of Title :
LINQ and Entity Framework, for the DBA
SQL Services - What does it mean for the DBA

and lot of Real World DBA Episode ...

Find all media here from Microsoft Technet

SQL Server 2005 ranking functions - RANK(), DENSE_RANK(), NTILE()

2010-02-18T11:09:00.003+08:00

http://msdn.microsoft.com/en-us/library/ms189798.aspx
Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

Good explanation of ranking function

Good example of ranking function

SQL Free monitor tools

2010-02-12T15:12:00.002+08:00

SQL HeartBeat, Monitor your sql server from SQLSolutions
http://www.sqlsolutions.com/products/Heartbeat/index.html

SQL Check, Monitor your sql server from Idera
http://www.idera.com/Products/Free-Tools/SQL-check/

Login is locked out when enforce password policy is on

2010-01-25T16:25:00.004+08:00

MY SQL 2005 login is set with “Enforce password policy” turn on

Check what mean by Enforce password policy:
In Start->Administrative tools->Local Security Settings
Security Settings-> Account Policies -> Account Lockout Policy

Account Lockout threshold - 5 invalid login attempts
If user keying 5 invalid login attempts, account will be lock.

Account lockout duration - 30 minutes
account will be lock for 30 min before automatically becoming unlocked

Reset account lockout counter after 30 minutes
This security setting determines the number of minutes that must elapse after a failed logon attempt before the failed logon attempt counter is reset to 0 bad logon attempts

I use test as login id and keying 5 invalid password.
I try next login with correct password, I can’t login into this SQL 2005 server.

I check from Microsoft SQL Server Management Studio Server name->Security-> test
Double click on test (login name), select Status->Under
Status -> SQL Server authentication ->
"Login is locked" out is checked.

It mean my login account been locked.

I unchecked "account been locked" and press "OK".
I login with the correct password, the login still fail.
I double click the SQL login again, "account been locked" is still "checked"

I unchecked Enforce password policy, unchecked Login is locked out" and press OK.
I login with correct password, but login still fail.

Solution:
1. Change password with TSQL
-ALTER LOGIN test WITH PASSWORD = 'complexpassword',CHECK_POLICY = ON;
-I login with complexpassword, login successful

2. Change password from Microsoft SQL Server Management Studio
-Microsoft SQL Server Management Studio -> Server name->Security-> test
-Double click on test (login name)->General -> Keying new password in Password and Confirm Password and press "OK"
-Double click on test (login name)->Status ->SQL Server authentication: -> Login is locked out
-I login with complexpassword, login successful

Reference
Account lockout duration

Reset account lockout counter after

MSOLAP Provider used in connection string

2010-01-15T16:34:00.004+08:00

We move asp page from live server to new setup server.
When asp page connect to our olap server, it show the below message:

Microsoft® OLE DB Provider for OLAP Services
error '80004005' OLAP server error: The operation requested failed due to security problems (the user could not be authenticated).

Our connection string as below and same with live server
provider=msolap;data source=servername;initial catalog=customer;user id=user;password=xxx;sspi=anonymous

After some Google and found this article by my colleague
How to check the MSOLAP Provider used in connection string

Some extraction of article as below:
MSOLAP ---Default Provider
MSOLAP.1 ---OLAP 7.0 Provider (msolap.dll)
MSOLAP.1 ---Analysis Service 2000 Provider (msolap80.dll)
MSOLAP.1 ---SSAS provider (msolap90.dll)

HKCR\MSOLAP ---is the default and used when MSOLAP is selected in the connection string, the file msolap.dll, msolap80.dll or msolap90.dll that is latest registered will be the default.
HKCR\MSOLAP.1 ---is the registry key for OLAP version 7, file msolap.dll
HKCR\MSOLAP.2 ---is the registry key for AS2000, file msolap80.dll
HKCR\MSOLAP.3 ---is the registry key for AS2005, file msolap90.dl

We change the connection string to
provider=msolap.3;data source=servername;initial catalog=customer;user id=user;password=xxx;sspi=anonymous
and it work

SQL process, percentage of complete

2010-01-15T15:11:00.004+08:00

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

2009-12-01T15:26:00.003+08:00

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

2009-11-03T14:41:00.002+08:00

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

refer to this site

SQL Data Type - Bit

2009-11-02T17:07:00.004+08:00

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

2009-10-28T17:02:00.003+08:00

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

2009-10-26T23:53:00.007+08:00

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"

2009-10-22T17:15:00.003+08:00

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