qr code generator c# Creating the Table in Visual C#

Creation QR Code in Visual C# Creating the Table

Now, let s look at creating a simple table. Consider this example:

CREATE TABLE HR.Employees ( EmployeeId INT NOT NULL ,FirstName NVARCHAR(50) NOT NULL ,LastName NVARCHAR(50) NOT NULL ,PhoneNumber VARCHAR(15) NULL ,BirthDate DATE NOT NULL );

This code creates a table named Employees containing five columns. The CREATE TABLE statement starts by defining which schema the table should reside in (in this case, HR), the table s name (Employees) and the table s columns. The columns are defined using three basic properties: column name, data type, and nullability (whether or not the column allows NULL values).

When choosing the name of tables and columns, it is important to follow the organization or project s naming guidelines. A few typical naming guidelines are provided here:

Use PascalCasing (also known as upper camel casing). Avoid abbreviations. A long name that users understand is preferred over a short name that users might not understand.

The data type used for each column is also very important. We have already covered most of the data types available in SQL Server 2008, but this section discusses some guidelines that you should try to follow when deciding which data type to use. They are as follows:

Always use the data type that requires the least amount of disk space while still providing the functionality that you require. It can be very costly (both in development time and server resources) to change a column s data type later on. Do not use a data type if there is a chance that it will not cover your application s future needs. In most cases use a variable-length data type, such as nvarchar, rather than a fixed-length data type, such as nchar. One of the few cases where a fixed-length data type is preferred over a variable-length data type is if the column s value changes frequently. If the column s value is updated frequently, the cost of moving the row to a new position where the new value fits may outweigh the cost of the additional storage required by a fixed data type. Avoid using the datetime and smalldatetime because they use more disk space and provide less precision than the new date, time, and datetime2 data types. Use the varchar(max), nvarchar(max), and varbinary(max) data types instead of the text, ntext, and image data types, which might not be available in future releases of SQL Server. Use the rowversion data type instead of the timestamp data type because the timestamp data type may not be available in future releases of SQL Server. Only use the varchar(max), nvarchar(max), varbinary(max), and xml data types if a data type with a specified size cannot be used. This is because using the data types prevents you from being able to rebuild indexes online and because these data types cannot be used in the key of an index.

Deciding on whether to allow NULLs in a column can be a problem. Many people have very strong opinions about NULLs they either accept them or they are strongly against them. The decision whether to allow NULLs is actually easy to make: In general, never allow them because it is the simplest way to design the table. Allowing NULLs where you don t need to do so greatly increases the potential for problems when querying your tables.

If the value for the column is optional (that is, not all rows have a value), the column must allow NULLs. You should never use another value instead of NULL (such as 1 for integers), which might cause you lots of problems in your queries. This is because 1 means minus one and not unknown, which is the definition of NULL. For example, if you use the AVG function, it includes -1 values in the calculation, but AVG would omit the NULL values. Alternatively, add a new table with a one-to-one relationship to the table you are designing and store the potentially unknown value in the other table. If a row shouldn t have a value, you simply don t insert a row into the other table. Consider this example:

CREATE TABLE HR.Employees ( EmployeeId INT NOT NULL ,FirstName NVARCHAR(50) NOT NULL ,LastName NVARCHAR(50) NOT NULL ,BirthDate DATE NOT NULL ); CREATE TABLE HR.EmployeePhoneNumbers ( EmployeeId INT NOT NULL ,PhoneNumber VARCHAR(15) NOT NULL ); -- Employee with phone number: INSERT HR.Employees (EmployeeId, FirstName, LastName, BirthDate) VALUES (1, N'John', N'Kane', '1970-02-20');

INSERT HR.EmployeePhoneNumbers (EmployeeId, PhoneNumber) VALUES (1, N'+1-425-555-1234');

-- Employee without phone number: INSERT HR.Employees (EmployeeId, FirstName, LastName, BirthDate) VALUES (2, N'Jane', N'Dow', '1965-05-30');

This implementation is not used very often because it increases the need for queries with OUTER JOINS or subqueries to retrieve the nullable columns from the other table. This, in turn, increases the risk for performance problems and also adds more complexity to queries than just allowing NULL values in the original table.