A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Collation Basics 浚

 
 
 

Collation is used by SQL Server to dictate how language is interpreted.

 
 

Collation settings control the following:
Case sensitivity ('A'='a'? or 'A' = 'A'?)
Accent sensitivity
('AIMÉE' = 'AIMEE'?)
Kana sensitivity
Width sensitivity

 

Example :

 

Latin1_General_CI_AI = Latin code page, Case Insensitive, Accent Insensitive

 

Collation can be set at the server, database, and column level.

Collation can also be explicitly forced in joins and sorting.

 
 
 

example:

      select first_name from [users]

      order by first_name COLLATE SQL_Latin1_General_CP1_CS_AS

 
 
 
 
 Let's go through some collation examples below.
 
 
No collation, No Unicode Datatype

--No Collation or Unicode Datatype.

create table #no_collo (value varchar(64))

insert #no_collo (value) select N''

select * from #no_collo

 Result:
 
 SQL has no idea what code page to associate your inserted data to and questions you.
 
 
 
 
No Collation, Includes Unicode Datatype

--No collation specified with unicode datatype
create
table #unicode (value Nvarchar(64))
      insert #unicode (value) select N'
'
select
* from #unicode

 
 Result:
 
 The unicode data type is a double byte standard which SQL knows how to display.
 
 
 
 
 
No Unicode Datatype, Includes Column Level Collation

-- No unicode datatype with collation used
-- add the collation to the column definition

create
table #collation (value varchar(64) COLLATE Chinese_Taiwan_Stroke_BIN)
      insert #collation (value)
      select N'
'
select
* from #collation

  Result:
 
  SQL has an idea of what code page to associate your inserted data to and no longer questions you.
 
 
 
 
 
Joining Unicode and Collocated Column 

---Selecting data from two tables created earlier...
select
* from #unicode u
join
 #collation c on c.value = u.value

 
  Result:    
   Msg 468, Level 16, State 9, Line 2

      Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"
      and "Chinese_Taiwan_Stroke_BIN" in the equal to operation.

 
 
 
 
 
Select with Collation in statement fixes the above error 

---Selecting data based on collation.
select
* from #unicode u
join
 #collation c on c.value = u.value COLLATE Chinese_Taiwan_Stroke_BIN

 
 Result:
 
 You would assume joining a unicode datatype against a specific collation would work,but it doesn't.
You'll need to specify the collation used in the join because the collation for the two sets of data are different.
 
 

SQL Server Cursor Example

 
Cursor example for looping through items in a temp table.
 
 
Cursor Example

create table #sandbox

(id int identity(1,1), val varchar(128))

insert into #sandbox (val)

select 'SQL is Fun'

insert into #sandbox (val)

select 'T-SQL is the Funnest'

insert into #sandbox (val)

select 'Yea, but cursors are dangerous'

GO

 

declare @data varchar(128)

 

Declare sandy_cursor cursor

      FOR Select val from #sandbox

 

      open sandy_cursor

 

      fetch next from sandy_cursor into @data

 

      while @@fetch_status = 0

      begin

            print @data

            fetch next from sandy_cursor into @data

      end

 

close sandy_cursor

deallocate sandy_cursor

 
 

Lock Pages In Memory

It's necessary to keep SQL from paging to disk to reduce physical I/O. One way is to keep the SQL working set in Memory. The steps below will set you up for this. Before performing this, ensure that your SQL Server has enough ram to handle your current production load. You wouldn't want your primary indexes dropping out of memory to replace 1 off Jobs.
 
 
I generally deploy new servers with Lock Pages in Memory set for the SQL Server Service start-up account .
 

To assign the Lock pages in memory user right, follow these steps:

  1. Click Start, click Run, type gpedit.msc, and then click OK.

    Note The Group Policy dialog box appears.
  2. Expand Computer Configuration, and then expand Windows Settings.
  3. Expand Security Settings, and then expand Local Policies.
  4. Click User Rights Assignment, and then double-click Lock pages in memory.
  5. In the Local Security Policy Setting dialog box, click Add User or Group.
  6. In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.
  7. Close the Group Policy dialog box.
  8. Restart the SQL Server service.