In SQL FULLTEXT, we can discards commonly occurring string from build into FULLTEXT INDEX.
Example : is, an, i, we. Thease discarded string s are called stopwords
In SQL 2005 and below, stoplist is called noise word and define in noise file under fulltext database.
Noise word is applied through SQL Instance
In SQL 2008, we can define STOPLIST in database instance
There is 3 option to select when we create STOPLIST
Create an empty stoplist
Create from the system stoplist
Create from an exisiting full-text stop list
System stoplist
select * from sys.fulltext_system_stopwords
Current database stoplist
select * from sys.fulltext_stoplists
I try create stoplist in adventurework
but sql return with error
CREATE FULLTEXT STOPLIST myStoplist;
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'STOPLIST'."
I just notice in "Stopwords and Stoplists", it did mention :
"Important CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes."
I change the database compatibility level from 90 to level 100 and execute the query again
CREATE FULLTEXT STOPLIST myStoplist;
Stoplist create successful
Reference :
Stopwords and Stoplists
http://msdn.microsoft.com/en-us/library/ms142551.aspx
Wednesday, December 22, 2010
Tuesday, December 21, 2010
SQL 2008 express : The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]
Install SQL Express Edition with Advanced Service (64-bit)
After install successful, SQL Server start but $SQL Server Agent not start
Check from SQL Server Configuration Manager -> SQL Server Agent (MSSQLSERVER)
State -> Stopped
Right click ->Property->Service->Start Mode change from "Disable" to "Automatic" and press "Apply"
SQL Server give the below message :
"The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]"
Google and found this from Microsoft Site.
SQL Server Features that Are Not Supported in SQL Server Express
One of the component is "SQL Server Agent and SQL Server Agent Service"
That is nothing wrong with installation but SQL Server Agent and SQL Server Agent Service by design is not supported in SQL Server Express 2008
After install successful, SQL Server start but $SQL Server Agent not start
Check from SQL Server Configuration Manager -> SQL Server Agent (MSSQLSERVER)
State -> Stopped
Right click ->Property->Service->Start Mode change from "Disable" to "Automatic" and press "Apply"
SQL Server give the below message :
"The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]"
Google and found this from Microsoft Site.
SQL Server Features that Are Not Supported in SQL Server Express
One of the component is "SQL Server Agent and SQL Server Agent Service"
That is nothing wrong with installation but SQL Server Agent and SQL Server Agent Service by design is not supported in SQL Server Express 2008
Monday, December 20, 2010
SQL 2008 Table data type
My developer ask me question about table data type.
Google and found this new features sql server 2008-user defined table type and table valued parameters and this Passing table valued parameters in SQL Server 2008
We have the same scenario but with additional step
When we execute the store procedure with pass in parameter, we want
1. Check whether the data already exists in table.
2. If exists, update the data with new value
3. If not exists, insert the data
In this store procedure, we have this :
CREATE procedure insertintocustomer(@Cust_ID int, @Cust_Name varchar(50),@Cust_Surname varchar(50),@Cust_Email varchar(50))
as
begin
If exists (select 1 from customers where cust_id=@Cust_ID)
Begin
update customers
set cust_surname=@Cust_Surname, Cust_Email=@Cust_Email
where cust_id=@Cust_id
END
else
BEGIN
insert into customers
values(
@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)
END
End
So we do this
alter procedure newcustomer(@Customer_details customertype READONLY)
as
begin
update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id
insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)
end
Google and found this new features sql server 2008-user defined table type and table valued parameters and this Passing table valued parameters in SQL Server 2008
We have the same scenario but with additional step
When we execute the store procedure with pass in parameter, we want
1. Check whether the data already exists in table.
2. If exists, update the data with new value
3. If not exists, insert the data
In this store procedure, we have this :
CREATE procedure insertintocustomer(@Cust_ID int, @Cust_Name varchar(50),@Cust_Surname varchar(50),@Cust_Email varchar(50))
as
begin
If exists (select 1 from customers where cust_id=@Cust_ID)
Begin
update customers
set cust_surname=@Cust_Surname, Cust_Email=@Cust_Email
where cust_id=@Cust_id
END
else
BEGIN
insert into customers
values(
@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)
END
End
So we do this
alter procedure newcustomer(@Customer_details customertype READONLY)
as
begin
update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id
insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)
end
Tuesday, December 14, 2010
Using Inner join or Function(Case)
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
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 queryset 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
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
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
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!
"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!
Thursday, December 09, 2010
You must use the Role Management Tool to install or configure Microsoft.NET Framework 3.5SP1
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.
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.
Subscribe to:
Posts (Atom)