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'?)
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.
select first_name from [users]
order by first_name COLLATE SQL_Latin1_General_CP1_CS_AS
Let's go through some collation examples below.
SQL has no idea what code page to associate your inserted data to and questions you.
The unicode data type is a double byte standard which SQL knows how to display.
SQL has an idea of what code page to associate your inserted data to and no longer questions you.
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.
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.
Cursor example for looping through items in a temp table.
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:
- Click Start, click Run, type gpedit.msc, and then click OK.
Note The Group Policy dialog box appears.
- Expand Computer Configuration, and then expand Windows Settings.
- Expand Security Settings, and then expand Local Policies.
- Click User Rights Assignment, and then double-click Lock pages in memory.
- In the Local Security Policy Setting dialog box, click Add User or Group.
- In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.
- Close the Group Policy dialog box.
- Restart the SQL Server service.