accent search in sql server

Hello All,

Generally in our business application, we need to implement search functionality on database column like title, firstname, lastname, city etc. It will create problem in search result when data contains accent characters and the database collation is accent-sensitive. so we will get wrong result.

Let’s take an example, you need to search the database for movies having a director that contains “Shohei”. So we will write query like below:

select movieTitle from movies where director like '%Shohei%'

Ideally, the result set should include below:
Shohei
Imamura, Shohei
Youssef Chahine,Shôhei Imamura
Amos Gitai,Shohei
Shôhei Imamura,Claude Lelouch

If your database collation is accent-sensitive, the result set includes only:
Shohei
Imamura, Shohei
Amos Gitai,Shohei

Note that directors containing accent characters are excluded from the result set. This is because accent characters are excluded from the query. Unfortunately, this is not the desired result.

Now I will show you how can we search accent characters in sql query. For this, first we need to understand collection in sql.

What is collation in SQL?
A collation in SQL Server is a defined set of rules for sorting and comparing data. This includes rules for case-sensitivity and accent-sensitivity. When you select a collation (for your server, database, column, or expression), you assign specific characteristics to your data that affect the results of database operations.

When we installed SQL Server 2008 R2/SQL Server 2012, the default collation was SQL_Latin1_General_CP1_CI_AS. The collation name is broken into these parts:

  • SQL = indicates a SQL Server collation name
  • Latin1_General = identifies the alphabet whose sorting rules are applied
  • CP1 = identifies Code Page 1252, which is a character encoding of the Latin alphabet
  • CI = defines case-insensitive comparison rules
  • AS = defines accent-sensitive comparison rules

I have already write one blog for the case sensitive search in sql server where we have use COLLATE Latin1_General_CS_AS = ‘Case sensitive Data’

How can we implement accent search in sql server. There are some solution for it.
1. Configure the database so that it is not accent-sensitive; change the collation to SQL_Latin1_General_CP1_CI_AI.

2. Remove and replace accented characters in the query.

select movieTitle from movies where replace(director, 'ô', 'o')like '%Shohei%'

3. Specify an accent-insensitive collation in the query

select movieTitle from movies where director like '%Shohei%' collate SQL_Latin1_General_CP1_CI_AI

Option 1 is simplest from a programmer’s perspective, because it is a permanent solution, server-wide and database-wide, and it requires no programming changes to applications. When you are installing a new SQL Server instance then this is easy to do.
Generally we are hosting application on hosting server so we cannot manage this option because they have already installed database with default option accent-sensitivity.

Options 2 and 3 are simplest from a database administrator’s perspective, because they require no changes to the database. This is useful for shared hosting environment.

Hope this will help.

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

Hello All,

Recently i am facing one common error in store procedure. “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements” comes when you write begin transaction statement in store procedure. When you try to set variable value and exit without commit then it will gives error.

For example

CREATE PROCEDURE [dbo].[StatusDelete]
(
	@StatusID SMALLINT,
	@outResult BIT OUTPUT
)
AS BEGIN
	
	DECLARE @count INT
	
	SET @count = 0

	BEGIN TRANSACTION
		

		SELECT
			@count = COUNT(*)
		FROM
			Types
		WHERE
			Types.StatusID = @StatusID
		
		IF @count > 0
		BEGIN
			SET @outResult = CAST(0 AS BIT)
			RETURN
		END

		DELETE FROM
			Status
		WHERE
			StatusID = @StatusID
	
		SET @outResult = CAST(1 AS BIT)
	COMMIT TRANSACTION
END

Above example will gives you error to solve that problem just add following line

.....
IF @count > 0
BEGIN
     SET @outResult = CAST(0 AS BIT)
     COMMIT TRANSACTION -- Add this line
     RETURN
END
..........

This will solve your issue.

Using Pivot in Sql Server

Hi All,

First of all we need to know about pivot in sql server.

pivot rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

The following is the syntax for Pivot in sql.

SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
.............................
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column], .............................)
) AS <alias for the pivot table>
<optional ORDER BY clause>;

Let’s take a basic example so we can understand it easily.

There is a table “Employee” with below columns,
ID : autogenerate int NOT NULL
name : nvarchar(50) NOT NULL
address : nvarchar(250) NOT NULL
contactNo : nvarchar(50) NOT NULL
leaveType : nvarchar(2) NOT NULL

You can create it by using below query

GO
CREATE TABLE [dbo].[Employee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[address] [nvarchar](250) NOT NULL,
	[contactNo] [nvarchar](50) NOT NULL,
	[leaveType] [nvarchar](2) NOT NULL
) ON [PRIMARY]
GO

Now insert some records in table using below query

INSERT INTO Employee(name, address, contactNo, leaveType)VALUES
('sachin', 'aaa', '111111111', 'PL'),  
('sehwag', 'bbb', '222222222', 'CL'),
('dravid', 'ccc', '333333333', 'SL'),  
('sehwag', 'bbb', '222222222', 'CL'),
('sachin', 'aaa', '111111111', 'SL'),
('Yuvraj', 'ddd', '444444444', 'PL'),
('Virat', 'eee', '555555555', 'SL'),
('Yuvraj', 'ddd', '444444444', 'CL')

now we need to count leave of all employee by leaveType and need to display it as below
name   address   contactNo   CL   PL   SL

see below query which use pivot feature and get the above result:

SELECT name, address, contactNo, CL, PL, SL
FROM (
SELECT name, address, contactNo, leaveType
FROM Employee) srcTable
PIVOT (COUNT(leaveType) FOR leaveType IN (CL, PL,SL)) AS pvtTable
ORDER BY name

see the below result
piv_result
Hope this will clear the concept of pivot feature.

Saving changes is not permitted in sql server

Hello,

When we install new version of sql server then sometimes we are getting this error “Saving changes is not permitted in sql server” on adding new column or changing column in table.

tblsave_error

If we check this in other PC then it is working fine. This is not a hosting problem.
we need to uncheck prevent saving changes option from Tools >> Options >> Designers >> Table and Database Designer. see below screenshot. so you can get better idea.

tblsave_error1

After unchecking the option, you can add or modify the tables.

Hope this will help you.

T-SQL Enhancements in SQL Server 2008 phase-5.1

Hello,

we have seen some development features of sql server 2008 in my previous articles like Table-Valued Parameters, Grouping Sets, cube and rollup, grouping_ID, merge statement, compound assignment operators, declare and initialize variables, Row constructors etc. Now today i am going to describe Datatype Enhancements.

Datatype Enhancements :

  • Temporal Data
  • Spatial Data
  • Hierarchical Data

Temporal Data :

  • New Datatypes
  • New and Enhanced Functions

New Datatypes :

Datatype Storage (bytes) Date Range Accuracy Recommended Entry Format and Example
DATE 3 January 1st, 0001 through December 31st, 9999 (in Gregorian calendar) 1 day ‘YYYY-MM-DD’ ’2009-02-12′
TIME 3 to 5 100 nanoseconds ‘hh:mm:ss.nnnnnnn’ ’12:30:15.1234567′
DATETIME2 6 to 8 January 1st, 0001 through December 31st, 9999 100 nanoseconds ‘YYYY-MM-DD hh:mm:ss.nnnnnnn’ ’2009-02-12 12:30:15.1234567′
DATETIMEOFFSET 8 to 10 January 1st, 0001 through December 31st, 9999 100 nanoseconds ‘YYYY-MM-DD hh:mm:ss.nnnnnnn [+|-]hh:mm’ ’2009-02-12 12:30:15.1234567 +02:00′

New Functions :

1. Return DATETIME2:

  • SYSDATETIME
  • SYSDATETIMEOFFSET
  • SYSUTCDATETIME

2. Return DATETIMEOFFSET:

  • SWITCHOFFSET (input DATETIMEOFFSET)
  • TODATETIMEOFFSET – returns input value with specified time zone

3. New date and time parts:

  • microsecond, nanosecond, Tzoffset, ISO_WEEK

Example :

DECLARE @d DATE
SET @d = '20110103'
SELECT  @d

DECLARE @t TIME
SET @t = GETDATE()
SELECT @t

DECLARE @decimalprecision TIME(3)
SET @decimalprecision = GETDATE()
SELECT @decimalprecision

DECLARE @dt2 DATETIME2
SET @dt2 = '20110103 23:59:59.9999999'
SELECT @dt2

DECLARE @decimalprecision2 DATETIME2(3)
SET @decimalprecision2 = '20110103 23:59:59.9999999'
SELECT @decimalprecision2

DECLARE @dto DATETIMEOFFSET
SET @dto = '20110103 23:59:59.9999 +09:00'
SELECT @dto

DECLARE @decimalprecision3 DATETIMEOFFSET(3)
SET @decimalprecision3 = '20110103 23:59:59.9999 +09:00'
SELECT @decimalprecision3


SELECT GETDATE()
SELECT SYSDATETIME()
SELECT SYSUTCDATETIME()
SELECT SYSDATETIMEOFFSET()
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-01:00')
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-01:00')

SELECT DATEPART(ISO_WEEK, SYSDATETIMEOFFSET())
SELECT DATEPART(ISO_WEEK, '2011-05-10')
SELECT DATEPART(TZoffset, '2011-05-10 00:00:01.1234567 +05:10')
SELECT DATEPART(TZoffset, SYSDATETIMEOFFSET())

That’s it.
we will see Spatial Data in next article

T-SQL Enhancements in SQL Server 2008 phase-4.2

Hello,

In my previous post, we have seen table value parameters, how to define it in sql server and asp.net, How to use Table Valued Parameters, Advantages of Table Valued Parameters, Limitations to passing Table Parameters etc.

Today we will see the example of Table Valued Parameters

I have used two tables Orders and OrderDetails. You can see the User Defined Table Type with the same structure.

I have created procedure “InsertOrders”. You need to pass the Table Type Variable as a parameter from asp.net

Example:

CREATE TABLE [Orders]
(
	OrderId UNIQUEIDENTIFIER NOT NULL,
	CustomerId INT NOT NULL,
	OrderedDate DATE NOT NULL,
	CreatedDate DATETIME2(0) NOT NULL
)
GO

CREATE TABLE [OrderDetails]
(
	OrderId UNIQUEIDENTIFIER NOT NULL,
	ProductId INT NOT NULL,
	Quantity INT NOT NULL,
	Price MONEY NOT NULL,
	CreatedDate DATETIME2(0) NOT NULL
)
GO

CREATE TYPE OrderUdt AS TABLE
(
	OrderId UNIQUEIDENTIFIER,
	CustomerId INT,
	OrderedDate DATE
)
GO

CREATE TYPE OrderDetailUdt AS TABLE
(
	OrderId UNIQUEIDENTIFIER,
	ProductId INT,
	Quantity INT,
	Price MONEY
)
GO

CREATE PROCEDURE InsertOrders
(
	@OrderHeaders AS OrderUdt READONLY,
	@OrderDetails AS OrderDetailUdt READONLY
)
AS
BEGIN

 -- Bulk insert order header rows from TVP
 INSERT INTO [Orders]
 SELECT *, SYSDATETIME() FROM @OrderHeaders

 -- Bulk insert order detail rows from TVP
 INSERT INTO [OrderDetails]
 SELECT *, SYSDATETIME() FROM @OrderDetails

 END
GO

SELECT name, system_type_id, user_type_id  
FROM sys.types
WHERE is_table_type = 1

/*DROP TYPE OrderDetailUdt
DROP TYPE OrderUdt*/

SELECT * FROM Orders
SELECT * FROM OrderDetails

T-SQL Enhancements in SQL Server 2008 phase-4.1

Hello,

we have seen some features of sql server 2008 in my previous article like Grouping Sets, cube and rollup, grouping_ID, merge statement, compund assignment operators, declare and initialize variables, Row constructors etc. Now today i am going to describe Table-Valued Parameters.

Table-Valued Parameters

  • Helps address the need to pass “array” of elements to stored procedure / function
  • Solutions pre-2008
    1. Dynamic SQL : Suffers from security and performance problems:
    a. Security: SQL Injection
    b. Performance: inefficient reuse of execution plans
    2. UDF that splits array to individual elements
    a. Overcomes security and performance issues
    b. Complicated

Advantages of Table Valued Parameters

  • It is easier to maintain and provides better performance than the use of temporary tables
  • Reduces multiple round trips between client and the server
  • It is much easier to program and use Table Valued Parameters
  • It doesn’t cause the statements to recompile
  • It enables you to include complex business logic in a single routine
  • They have a well defined scope at the end of which they are automatically cleared

How to use Table Valued Parameters

  • Create a User Defined Table Type with the same structure as that of the table, which can be passed as a Table Valued Parameter to a SP or a Function
  • Create a stored procedure which accepts table type as a parameter
  • Declare a variable as Table Type and refer the Table Type which you have created earlier
  • Populate the Table Type Variable by using an insert statement
  • Once the Table Type Variable is created and populated then the next step will be to pass the Table Type Variable as a parameter to the stored procedure or a function

Limitations to passing Table Parameters

  • You must use the READONLY clause when passing in the table valued variable into the procedure.
  • Data in the table variable cannot be modified. you can use the data in the table for any other operation.
  • Also, you cannot use table variables as OUTPUT parameters. you can only use table variables as input parameters.

That’s it. I will explain example in my next article.

T-SQL Enhancements in SQL Server 2008 phase-3

Hello,

we have seen some features of sql server 2008 in my previous article like merge statement, compund assignment operators, declare and initialize variables, Row constructors etc. Now today i am going to describe grouping sets.

Describe Grouping Sets :

  • Grouping set: a set of group by columns
  • Helps supporting dynamic analysis of aggregates
  • SQL Server versions earlier than 2008 had limited support for grouping sets
  • SQL Server 2008 introduces several important enhancements supporting grouping sets

Grouping Sets Enhancements in 2008 :

  • New ISO-compliant subclauses in GROUP BY clause:
    • GROUPING SETS
    • CUBE
    • ROLLUP
  • New GROUPING_ID function identifies grouping set

GROUPING SETS Subclause :

  • Define multiple grouping sets in a single query
  • NULLs used as placeholders

CUBE and ROLLUP Subclauses :

  • CUBE: abbreviation for a power set of grouping sets constructed from input elements (2^n grouping sets for n elements)
  • ROLLUP: abbreviation for the subset of grouping sets that is relevant in a hierarchy scenario (n+1 grouping sets for n elements)

GROUPING_ID :

  • Accepts a list of elements as input
  • Produces an integer bitmap that identifies the grouping set
  • Each bit represents a different element
  • Bit off (0) – element participated in current grouping set
  • Bit on (1) – element did not participate in current grouping set

Example :

CREATE TABLE [dbo].[employee]
(
	[Employee_Number] [int] NOT NULL PRIMARY KEY,
	[Employee_Name] [varchar](30) NULL,
	[Salary] [float] NULL,
	[Department_Number] [int] NULL,
[Region] [varchar](30) NULL
) ON [PRIMARY]

INSERT INTO employee
VALUES
(1,'Sachin',5000,1,'Ahmedabad'),
(2,'Rahul',9000,1,'Baroda'),
(3,'Saurav',4000,2,'Ahmedabad'),
(4,'Yuvraj',8000,1,'Delhi'),
(5,'Zahir',8000,2,'Delhi')

SELECT Region, Department_number, AVG(salary) Average_Salary
FROM Employee
GROUP BY
GROUPING SETS
(
	(Region, Department_number),
	(Region),
	(Department_number) ,
	()                            
)

--UNION results of above query
SELECT Region, Department_number, avg(salary) Average_Salary
from Employee
Group BY
Region, Department_number
UNION
SELECT Region, NULL, avg(salary) Average_Salary
from Employee
Group BY
Region
UNION
SELECT NULL, Department_number, avg(salary) Average_Salary
from Employee
Group BY
Department_number
UNION
SELECT NULL, NULL, avg(salary) Average_Salary
from Employee

--CUBE  REPLACEMENT:
SELECT Region, Department_Number, avg(salary) Average_Salary
from Employee
Group BY
CUBE (Region, Department_Number)  

--ROLLUP  REPLACEMENT:
SELECT Region, Department_Number, avg(salary) Average_Salary
from Employee
Group BY
ROLLUP (Region, Department_Number) 
--(Region, Department_Number),(Region)()

SELECT Region, Department_Number, avg(salary) Average_Salary
from Employee
Group BY
ROLLUP (Department_Number, Region) 
--(Department_Number, Region),(Department_Number)()

SELECT GROUPING_ID
(
	Region,						--2
	Department_Number			--1
)AS grp_ID, Region, Department_Number FROM Employee
Group BY
CUBE (Region, Department_Number)

SELECT GROUPING_ID
(
	Region,						--4
	Department_Number,			--2
	salary						--1
)AS grp_ID, Region, salary Department_Number FROM Employee
Group BY
GROUPING SETS(salary, 
CUBE (Region, Department_Number))

Hope you enjoyed this; Now I will explain phase-4 in coming days.

sql server 2012 management studio not responding

Hello All,

Recently I have installed sql server Management Studio 2012 Express edition in my windows 7 64 bit machine.

It is working fine in my local PC but when I connect remote host(hosting server) then it says not responding.

Actually i am connecting sql server 2008 from my sql server 2012 management studio. Not all hosting server gives me an error.

I have found some solutions for above problem
1. re-install mssql server 2012 express edition
2. Object Explorer Details window is opened. So, to resolve that, make sure that before trying to connect, Object Explorer Details windows (F7) is closed.
3. Install mssql server 2012 sp1
4. Generate new work profile for sql server 2012
In Explorer, open “%AppData%\Microsoft\SQL Server Management Studio”
Rename the “11.0″ folder to something else, like “11.0.old”

T-SQL Enhancements in SQL Server 2008 phase-2

Hello,

I have described some features of sql server 2008 in my previous article. Now today i am going to describe merge statement.

Describe Merge :

  • Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
  • Done as a set-based operation; more efficient than multiple separate operations
  • MERGE is defined by ANSI SQL; you will find it in other database platforms as well
  • Useful in both OLTP(Online Transaction Processing) and Data Warehouse environments
    • OLTP: merging recent info from external source
    • DW: incremental updates of fact, slowly changing dimensions

Basic Syntax :

MERGE [INTO] <target table>
USING <source table or table expression>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>

OUTPUT Clause and $action Function :

  • Use OUTPUT clause to have the MERGE statement return effected rows
  • Use $action to return a string representing the action that modified the row (‘INSERT’, ‘UPDATE’, ‘DELERE’)

MERGE …
OUTPUT $action, deleted.*, inserted.*;

Example :

--Create a target table
CREATE TABLE Products
(
	ProductID INT PRIMARY KEY,
	ProductName VARCHAR(100),
	Rate MONEY
) 
GO

--Insert records into target table
INSERT INTO Products
VALUES
(1, 'Alu Tikki', 10.00),
(2, 'Cold Coffee', 20.00),
(3, 'Burger', 30.00),
(4, 'Pasta', 40.00)
GO

--Create source table
CREATE TABLE UpdatedProducts
(
	ProductID INT PRIMARY KEY,
	ProductName VARCHAR(100),
	Rate MONEY
) 
GO

--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, 'Alu Tikki', 10.00),
(2, 'Cold Coffee', 25.00),
(3, 'Burger', 35.00),
(5, 'Pizza', 60.00)
GO

SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO

MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
--When records are matched, update 
--the records if there is any change
WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName 
OR TARGET.Rate <> SOURCE.Rate THEN 
UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
TARGET.Rate = SOURCE.Rate 
--When no records are matched, insert
--the incoming records from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN 
INSERT (ProductID, ProductName, Rate) 
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same record does not exist in source table
--then delete this record from target table
WHEN NOT MATCHED BY SOURCE THEN 
DELETE
--$action specifies a column of type nvarchar(10) 
--in the OUTPUT clause that returns one of three 
--values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
--according to the action that was performed on that row
OUTPUT $action, 
DELETED.ProductID AS TargetProductID, 
DELETED.ProductName AS TargetProductName, 
DELETED.Rate AS TargetRate, 
INSERTED.ProductID AS SourceProductID, 
INSERTED.ProductName AS SourceProductName, 
INSERTED.Rate AS SourceRate; 
SELECT @@ROWCOUNT;
GO

SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO

Hope you enjoyed this; Now I will explain phase-3 in coming days.