What Are Character Sets And Sort Orders?

Okay, see you again in SQL black book, I just read the book and tell what I see, so I didn't teach you, never :)

 

 

Another preinstallation issue is choosing a character set and sort order. A character set is the basic text and symbols that are loaded in your system. Regardless of the character set you choose, the first 128 characters are the same. The extended characters, including language-specific characters, reside in the remaining half of the character set. Your decision depends on whether you are doing business overseas or in other languages and need to store text and special characters. In most cases, the default is fine and should provide you with what you need to function.

You should make this determination prior to installation. Changing character sets can be a daunting task with many system ramifications. If your company is concerned about character sets, chances are you are experienced in these issues and this feature should be nothing new to you.

Another interesting issue concerns sort orders. Sort orders determine the way the data is organized when stored by Microsoft SQL Server. The default sort order for Microsoft SQL Server is dictionary order and case-insensitive. This is fine and probably the best default setting. It is not, however, the fastest setting you can use on your system.

Note: Microsoft is not trying to slow you down. Most programmers are not as careful as they could be and do not always exercise consistent case sensitivity when they write code. The default for Microsoft SQL Server should be used if you have legacy systems that might contain this kind of SQL code.

 

The fastest sort order is binary. The use of this setting has some impact on how you perform certain tasks down the road, so choose it carefully. It will change all of your SQL scripts, stored procedures, and client pass-through code to be case-sensitive. If you type a statement and use a different case than was specified when the table was created, you will get an error message. Say, for instance, you have a table called MyTable on your system. To access it, you type “mytable”. An “Object Not Found” error is returned.

Binary sort order poses a few issues in developing client software, and great care should be taken when using it. Your ad hoc queries might not return what you expect back from the server, either. A capital “F” does not equal a lowercase “f”. Reports are not inherently smart enough to tell the difference, and your code needs to allow for this.

If you store, access, and check for case sensitivity on your entire system, binary is the way to go. I have configured two identical machines installed from scratch with the same data sets stored in different sort orders. My tests have proven that binary is faster for a lot of common operations. If you are putting third-party applications on your server, make sure they run as expected in this sort order. If in doubt, call the vendor or technical support for the product in question.

I often use binary sort orders as an example of a setting that restricts programmers in a way they might find difficult. Because of the case-sensitive nature, programmers must write code with more care than they would otherwise. The end result is faster, but getting there might be more difficult.

Users of the system should also be considered when selecting binary sort orders. If a system allows for ad hoc reports or queries and a user does not know that the data is stored with case sensitivity, he or she might not get the expected results. This can be dangerous when converting legacy systems. Make the decision to use a binary sort order only after carefully weighing the impact on your entire organization.

Note: Under “Performance Comparisons” in the Microsoft SQL Server Books Online, select Topic 5 from the SQL Server 6.0 Setup Guide for more information.

 

 

 

 

Another consideration in choosing a character set and a sort order is whether you are setting up a distributed server environment. If you are, you must use compatible character sets and sort orders among your servers. If you are going to share, replicate, or distribute data, use a common character set and sort order throughout your enterprise. Do not forget that in business today we must occasionally share data with other companies. If your system interacts with another company’s system, again make sure the character sets and sort orders are compatible.

 

References : Microsoft SQL Server Black Book

What is a Database SQL?

What is A Database?

Databases are also considered containers. They hold the objects that make up your server’s purpose in life. Tables, views, indexes, and stored procedures are all objects that reside in your database. You can, and often will, have multiple user-defined databases residing on your server. These databases are where the production information and code reside. Other databases are installed on your server to give it the intelligence it needs to function; I will cover these databases in a few different areas throughout the book. However, our focus will be on setting up a production system, not on the inner workings of Microsoft SQL Server.

One of the most common mistakes new users make is to confuse the device and the database. You place your databases within your devices. To understand this, think of a database as a division within your company. For instance, Human Resources deal with very specific kinds of information, so you would logically put all of that type of information in a container for centralized management and access control. Accounting is an area that often requires more security than others, and the information generated from this area would justly be placed in a separate container for security reasons. You would not scatter information for the Human Resources department throughout all the offices; instead, you would put all those functions and resources in one place. The same applies to databases and good database design.

An interesting point for all PC-based database programmers is that Microsoft SQL Server does not store the information or data in the database. Remember, the database is a container. Instead, the server stores your data in a table. The index you create for fast access to data is not stored in the table with the raw data; it is stored as another object within the database. A database is a collection of objects. This concept is not hard to follow, but it is different enough from the organization of other database programs that it is sometimes a stumbling block for the small-system programmer. An MIS department accustomed to dBASE or Microsoft FoxPro databases will struggle with this at first. Since this structure is common to most large database systems today, you should become familiar with it.

In addition, you should focus on the database level when administrating your system’s security. Your users will be granted a logon ID for connecting to the server, but this does not allow them to get to the data they need. This is done by adding users and groups to each database individually on a need-to-know basis. This method of security keeps unwanted users from browsing where they should not while allowing others to do their jobs.

Returning to the office analogy, let’s compare a database to the Accounting department in your company. This department might have a door you must pass through, and once you pass through that door, you would see all the cubicles and desks where the actual work is done. This door might be locked in the evening or even require a passkey to enter during the day.

The same idea can be applied to a database. The records and files are not strewn around the office; they reside in filing cabinets and in folders or ledgers for ease of access. These organizational tools can be related to Microsoft SQL Server objects. You use tables, stored procedures, and indexes to find what you need when you need it.

The security model that Microsoft SQL Server uses is also similar to the passkey entry requirement. No one gets access without a valid key or password. I will not try to recommend a security method here because of the diverse requirements in the market today. However, I will say that Microsoft SQL Server will accommodate a strict security model very well and still allow for the simple, trusting models required by smaller companies growing into Microsoft SQL Server.

During installation of Microsoft SQL Server, you will not be concerned with these divisions or security, but you should make a few assumptions on the amount of disk space you will need to accommodate these areas and how you will accommodate these needs.