Introduction to Microsoft SQL Server Databases |
|
Introduction to the Structured Query Language
Overview of SQL
After establishing a connection, you can take actions, such as creating a database and/or manipulating data. The Structured Query Language, known as SQL, is a universal language used on various computer systems to create and manage databases.
SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in “A SQL statement” instead of "An SQL statement". Also, we will regularly write, “The SQL” instead of “The SQL language, as the L already represents Language. |
Like other non-platform specific languages such as C/C++, Pascal, or Java, the SQL you learn can be applied to various database systems. To adapt the SQL to Microsoft SQL Server, the company developed Transact-SQL as Microsoft's implementation of SQL. Transact-SQL is the language used internally by Microsoft SQL Server and MSDE. Although SQL Server highly adheres to the SQL standards, it has some internal details that may not be applied to other database systems like MySQL, Oracle, or even Microsoft Access, etc; although they too fairly conform to the standard.
The SQL we will learn and use here is Transact-SQL. In other words, we will assume that you are using Microsoft SQL Server as your platform for learning about databases. This means that, unless specified otherwise, most of the time, on this site, the word SQL refers to Transact-SQL or the way the language is implemented in Microsoft SQL Server.
The SQL Interpreter |
As a computer language, the SQL is used to give instructions to an internal program called an interpreter. As we will learn in various sections, you must make sure you give precise instructions. SQL is not case-sensitive. This means that CREATE, create, and Create mean the same thing. It is a tradition to write SQL's own words in uppercase. This helps to distinguish SQL instructions with the words you use for your database.
As we will learn in this and the other remaining lessons, you use SQL by writing statements. In a Windows Forms Application, you can write the code and pass it to a SqlCommand object you would have created as we saw in the previous lesson. This would be done as follows:
void InitializeComponent() { SqlConnection connection = new SqlConnection("Data Source=(local);Integrated Security=yes"); SqlCommand command = new SqlCommand(SQL Code, connection); }
In this example, the SQL Code factor represents a SQL statement you would write and pass it as a string.
In the next sections and lessons, we will learn various techniques of creating SQL statements with code. If there is no error in the code, what happens when you execute a statement depends on the code and the type of statement. In a Windows Forms Application, after passing the SQL code to a command, to execute it, as we saw in the previous lesson, you can call the ExecuteNonQuery() method of your SqlCommand object. This would be done as follows:
void InitializeComponent() { SqlConnection connection = new SqlConnection("Data Source=(local);Integrated Security=yes"); SqlCommand command = new SqlCommand(SQL Code, connection); connection.Open(); command.ExecuteNonQuery(); connection.Close(); }
Introduction |
Before using a database, you must have one. A database is primarily a group of computer files that each has a name and a location. Just as there are different ways to connect to a server, there are also different ways to create a database. You can create a new database in Microsoft Visual Studio or with code (there are many other options, such as the Command Prompt).
To visually create a database, in the Server Explorer, right-click Data Connections and click Create New SQL Server Database... You will be presented with the Create New SQL Server Database dialog box where you must first specify the name of the server. If the computer you are using is the same that has Microsoft SQL Server, you can specify the server name as (local):
To programmatically create a database, pass the necessary SQL code as the command text of the SqlCommand object:
private void btnDatabase_Click(object sender, EventArgs e) { SqlConnection connection = new SqlConnection("Data Source=(local);Integrated Security=yes"); SqlCommand command = new SqlCommand(Database Creation Code, connection); connection.Open(); command.ExecuteNonQuery(); connection.Close(); }
The Name of a Database |
The primary piece of information about a database is its name. There are rules you must follow and suggestion you should observe:
Because of the flexibility of SQL, it can be difficult to maintain names in a database. Based on this, there are conventions we will use for our objects. In fact, we will adopt the rules used in C/C++, C#, Pascal, Java, and Visual Basic, etc. In our databases:
After creating an object whose name includes space, whenever you use that object, include its name between [ and ]. Examples are [Countries Statistics], [Global Survey], or [Date of Birth]. Even if you had created an object with a name that doesn't include space, when using that name, you can still include it in square brackets. Examples are [UnitedStations], [FullName], [DriversLicenseNumber], and [Country].
To assist you with creating and managing databases, including their objects, you use a set of language tools referred to as the Data Definition Language (DDL). This language is made of commands. For example, the primary command to create a database uses the following formula:
CREATE DATABASE DatabaseName
The CREATE DATABASE (remember that SQL is not case-sensitive) expression is required. The DatabaseName is the name that the new database will have. Although SQL is not case-sensitive, you should make it a habit to be aware of the cases you use to name your objects. Every statement in SQL can be terminated with a semi-colon. Although this is a requirement in many implementations of SQL, in Microsoft SQL Server, you can omit the semi-colon. Otherwise, the above formula would be:
CREATE DATABASE DatabaseName;
Here is an example:
CREATE DATABASE NationalCensus;
This formula is used if you do not want to provide any option. A database has one or more files saved in a default folder.
The Location of a Database |
As you should be aware already from your experience on using computers, every computer file must have a path. The path is where the file is located in one of the drives of the computer. This allows the operating system to know where the file is so that when you or another application calls it, the operating system would not be confused.
By default, when you create a new database, Microsoft SQL Server assumes that it would be located at Drive:C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data folder. When creating a new database, if you want, you can specify the location of the files of your database. To specify where the primary file of the database will be located, you can use the following formula:
CREATE DATABASE DatabaseName ON PRIMARY ( NAME = LogicalName, FILENAME = Path )
The only three factors whose values need to be changed from this formula are the database name, the logical name, and the path name. The logical name can be any one-word name but should be different from the database name. The path is the directory location of the file. This path ends with a name for the file with the extension .mdf. The path should be complete and included in single-quotes. Here is an example:
CREATE DATABASE NationalCensus ON PRIMARY ( NAME = DataRepository, FILENAME = 'C:\Exercises\NationalCensus.mdf') GO
Besides the primary file, you may want to create and store a log file. To specify where the log file of the database would be located, you can use the following formula:
CREATE DATABASE DatabaseName ON PRIMARY ( NAME = LogicalName, FILENAME = Path.mdf ) LOG ON ( NAME = LogicalName, FILENAME = Path.ldf )
The new factor in this formula is the path of the log file. Like the primary file, the log file must be named (with a logical name). The path ends with a file name whose extension is .ldf. Here is an example:
CREATE DATABASE NationalCensus ON PRIMARY ( NAME = DataRepository, FILENAME = 'C:\Exercises\NationalCensus.mdf') LOG ON ( NAME = DataLog, FILENAME = 'C:\Exercises\NationalCensus.ldf') GO
The Primary Size of a Database |
When originally creating a database, you may or may not know how many lists, files, or objects the project would have. Still, as a user of computer memory, the database must use a certain portion, at least in the beginning. The amount of space that a database is using is referred to as its size. When you create a new database, the SQL interpreter automatically gives it a default size. This is enough for a starting database. Of course, you can either change this default later on or you can increase it when necessary.
Database Maintenance |
Connecting to a Database |
Once a database exists on the server, to use it, as we saw in the previous lesson, you must first establish a connection to it. We saw that, to programmatically connection to a Microsoft SQL Server database, you could use a SqlConnection variable. In the connection string, to specify the database, assign its name to the Database attribute. Here is an example:
void CreateConnection() { SqlConnection connection = new SqlConnection("Data Source=(local);Database='Exercise';Integrated Security=yes;"); }
Once you have established a connection, you can then open it and perform the desired actions:
using System; using System.Data; using System.Drawing; using System.Windows.Forms; using System.Data.SqlClient; public class Exercise : Form { public Exercise() { InitializeComponent(); } void InitializeComponent() { SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise';" + "Integrated Security=yes;"); SqlCommand command = new SqlCommand(SQL Code, connection); connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } public class Program { static int Main() { System.Windows.Forms.Application.Run(new Exercise()); return 0; } }
Renaming a Database |
Database maintenance consists of renaming one database or removing another. To change the name of a database, Transact-SQL provides sp_renamedb. (Notice that the name starts with sp_. This is called a stored procedure. We will learn how to create them. For now, trust that they work and do what they are supposed to do). The formula used would be:
EXEC sp_renamedb 'ExistingName', 'NewName'
The EXEC sp_renamedb expression is required. The ExistingName factor is the name of the database that you want to rename. The NewName factor is the name you want the database to have after renaming it.
Here is an example of renaming a database:
EXEC sp_renamedb 'RentalCars', 'BethesdaCarRental GO
To rename a table in a C# code, pass the EXEC sp_renamedb code as string to a SqlCommand object and call the SqlCommand.ExecuteNonQuery() method.
Deleting a Database |
If you have created a database but don't need it anymore, you can delete it. It is important to know, regardless of how you create a database, whether using Microsoft SQL Server Management Studio, the Command Prompt, or Microsoft Visual Studio, every database can be accessed by any of these tools and you can delete any of the databases using any of these tools.
As done with creating a database, every tool provides its own means. To delete a database in SQL, use the DROP DATABASE instruction followed by the name of the database. The formula used is:
DROP DATABASE DatabaseName
Before deleting a database in SQL, you must make sure the database is not being used or accessed by someone else or by another object.
Microsoft SQL Server Primary Settings |
The System Databases |
When you install Microsoft SQL Server, it also installs 5 databases named master, model, msdb, and tempdb. These databases will be for internal use. This means that you should avoid directly using them, unless you know exactly what you are doing.
One of the databases installed with Microsoft SQL Server is named master. This database holds all the information about the server on which your Microsoft SQL Server is installed. For example, we saw earlier that, to perform any operation on the server, you must login. The master database identifies any person, called a user, who accesses the database, about when and how.
Besides identifying who accesses the system, the master database also keeps track of everything you do on the server, including creating and managing databases.
You should not play with the master database; otherwise you may corrupt the system. For example, if the master database is not functioning right, the system would not work.
A Namespace |
As you should know from your learning C#, a namespace is a group of items that each has a unique name. For example, if you start creating many databases, there is a possibility that you may risk having various databases with the same name. If using a namespace, you can isolate the databases in various namespaces. In reality, to manage many other aspects of your database server, you use namespaces and you put objects, other than databases, within those namespaces. Therefore, a namespace and its content can be illustrated as follows:
Notice that there are various types of objects within a namespace.
Introduction to Schemas |
Within a namespace, you can create objects as you wish. To further control and manage the objects inside of a namespace, you can put them in sub-groups called schemas. A schema is a group of objects within a namespace. This also means that, within a namespace, you can have as many schemas as you want:
To manage the schemas in a namespace, you need a way to identify each schema. Based on this, each schema must have a name. In our illustration, one schema is named Schema1. Another schema is named Schema2. Yet another schema is named Schema_n.
The Database Owner |
There are two types of schemas you can use, those built-in and those you create. When Microsoft SQL Server is installed, it also creates a few schemas. One of the schemas is called sys.
The sys schema contains a list of some of the objects that exist in your system. One of these objects is called databases (actually, it's a view). When you create a database, its name is entered in the databases object using the same name you gave it.
In the previous lesson, we saw that, before using a database, you must establish a connection with the server. You do this using a user account that can use the server. Once the connection exists, you can create a database. In Microsoft SQL Server, the user who creates a database is referred to as the database owner. To identify this user, when Microsoft SQL Server is installed, it also creates a special user account named dbo. This account is automatically granted various permissions on the databases of the server.
Because the dbo account has default access to all databases, to refer to an object of a database, you can qualify it by typing dbo, followed by the period operator, followed by the name of the object.
Creating a Schema |
A schema is an object that contains other objects. To access the schemas of a database, in the Object Explorer, expand the Databases node, expand the database that will hold or own the schema, and expand the Security node.
To programmatically create a schema, the syntax to follow is:
CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ] <schema_name_clause> ::= { schema_name | AUTHORIZATION owner_name | schema_name AUTHORIZATION owner_name } <schema_element> ::= { table_definition | view_definition | grant_statement | revoke_statement | deny_statement }
The most important part is:
CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]
In this case, start with the CREATE SCHEMA expression and add a name to it. Here is an example:
1> CREATE SCHEMA PrivateListing; 2> GO 1>
The other parts deal with issues we have not studied yet.
Accessing an Object From a Schema |
Inside of a schema, two objects cannot have the same name, but an object in one schema can have the same name as an object in another schema. Based on this, if you are accessing an object within its schema, you can simply use its name, since that name would be unique. On the other hand, because of the implied possibility of dealing with objects with similar names in your server, when accessing an object outside of its schema, you must qualify it. To do this, you would type the name of the schema that contains the object you want to use, followed by the period operator, followed by the name of the object you want to use. From our illustration, to access the Something1 object that belongs to Schema1, you would type:
Schema1.Something1
As mentioned already, when Microsoft SQL Server is installed, it creates a schema named dbo. This is probably the most common schema you will use. In fact, if you don't create a schema in a database, the dbo schema is the default and you can apply it to any object in your database.
SQL Selection
Introduction to SQL Operators and Operands
An operation is an action performed on one or more values either to modify the value held by one or both of the variables or to produce a new value by combining values. Therefore, an operation is performed by using at least one symbol and one value. The symbol used in an operation is called an operator. A value involved in an operation is called an operand.
SELECT Something |
The SELECT operator can be used, among other things, to display a value. In this case, the SELECT keyword uses the following syntax:
SELECT What
Based on this, to use it, where it is needed, type SELECT followed by a number, a word, a string, or an expression. Here is an example:
SELECT 226.75;
Based on this definition, instead of just being a value, the thing on the right side of SELECT must be able to produce a value. SELECT can be used with more than one value. The values must be separated by commas. Here is an example:
SELECT 'Hourly Salary', 24.85
Nesting a SELECT Statement |
Because we mentioned that the thing on the right side must produce a result, you can as well use another SELECT statement that itself evaluates to a result. To distinguish the SELECT sections, the second one should be included in parentheses. Here is an example:
SELECT (SELECT 448.25); GO
When one SELECT statement is created after another, the second is referred to as nested.
Just as you can nest one SELECT statement inside of another, you can also nest one statement in another statement that itself is nested. Here is an example:
SELECT (SELECT (SELECT 1350.75)); GO
SELECT This AS That |
One of the characteristics of SELECT is that it can segment its result in different sections. SELECT presents each value in a section called a column. Each column is represented with text called a caption. By default, the caption is "(No column name)". If you want to use your own caption, on the right side of an expression, type the AS keyword followed by the desired caption. The item on the right side of the AS keyword must be considered as one word. Here is an example:
SELECT 24.85 AS HourlySalary;
You can also include the item on the right side of AS in single-quotes. Here is an example:
SELECT 24.85 AS 'HourlySalary';
If the item on the right side of AS is in different words, you should include it in single-quotes or put them in inside of an opening square bracket "[" and a closing square bracket "]". Here is an example:
SELECT 24.85 AS 'Hourly Salary';
If you create different sections, separated by commas, you can follow each with AS and a caption. Here is an example:
SELECT 'James Knight' As FullName, 20.48 AS Salary;
An alternative is:
SELECT 'James Knight' As [Full Name], 20.48 AS [Hourly Salary];
Transact-SQL Data Types and Variables |
Introduction to Variables |
Like C#, Transact-SQL supports the ability to declare and use variables. These are values that are stored in the computer memory.
To declare a variable, use the DECLARE keyword in the following formula:
DECLARE Options
The DECLARE keyword is followed by a name for the variable. In Transact-SQL, the name of a variable starts with the @ sign. The name of a variable allows you to identify the area of memory where the value of the variable is stored. Transact-SQL is extremely flexible with names. There are rules and suggestions you will use for the names:
A name cannot be one of the following words reserved for Transact-SQL internal keywords:
aggregate | alter | and | any | application |
as | assembly | backup | begin | between |
bigint | binary | bit | break | broker |
by | case | catalog | catch | certificate |
char | check | checkpoint | close | commit |
compute | contains | continue | contract | create |
credential | cursor | database | date | datetime |
datetime2 | datetimeoffset | deallocate | decimal | declare |
default | delete | deny | disable | drop |
else | enable | end | endpoint | event |
exec | execute | false | fetch | float |
foreign | from | full | fulltext | function |
go | goto | grant | group | having |
hierarchyid | if | image | in | index |
insert | int | into | is | kill |
like | login | master | merge | message |
money | move | nchar | next | not |
ntext | null | numeric | nvarchar | on |
order | output | partition | proc | |
procedure | queue | real | receive | remote |
resource | return | revert | revoke | role |
rollback | rowversion | rule | save | schema |
select | send | set | setuser | shutdown |
smalldatetime | smallint | smallmoney | sql_variant | status |
table | text | then | time | timestamp |
tinyint | tran | transaction | trigger | true |
try | type | union | unique | uniqueidentifier |
update | use | values | varbinary | varchar |
view | when | while | with | xml |
To declare a variable, use the following formula:
DECLARE @VariableName DataType;
You can also declare more than one variable. To do that, separate them with a comma. The formula would be:
DECLARE @Variable1 DataType1, @Variable2 DataType2, @Variable_n DataType_n;
The name of each variable must be followed by its own data type.
After declaring a variable, to initialize it, in the necessary section, type the SELECT or the SET keyword followed by the name of the variable, followed by the assignment operator "=", followed by an appropriate value. The formula used is:
SELECT @VariableName = DesiredValue
or
SET @VariableName = DesiredValue
Once a variable has been initialized, you can use its value.
Like C#, the SQL supports Boolean values. To declare a Boolean variable, you use the BIT or bit keyword. Here is an example:
DECLARE @IsOrganDonor bit;
After declaring a Boolean variable, you can initialize it with 0 or another integral value. If the variable is initialized with 0, it receives the Boolean value of False. If it is initialized with any other number, it receives a True value. Here is an example:
private void btnOperate_Click(object sender, EventArgs e) { SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;"); SqlCommand command = new SqlCommand("DECLARE @IsMarried bit; " + "SET @IsMarried = 1; " + "SELECT @IsMarried; ", connection); connection.Open(); SqlDataReader rdr = command.ExecuteReader(); while (rdr.Read()) MessageBox.Show("Is Married? " + rdr[0].ToString()); rdr.Close(); connection.Close(); }
Natural Numbers |
Like C#, the SQL supports integers. If a variable would hold natural numbers in the range of -2,147,483,648 to 2,147,483,647, you can declare it with the int keyword as data type. The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.
Remember that you can also declare and use more than one variable. Here is an example:
DECLARE @IsMarried bit, @EmplStatus int; SET @IsMarried = 1; SET @EmplStatus = 2; SELECT @IsMarried AS [Is Married?], @EmplStatus AS [Employment Status]; GO
Here is an example that declares and uses a variable
private void btnOperate_Click(object sender, EventArgs e) { SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;"); SqlCommand command = new SqlCommand("DECLARE @IsMarried bit; " + "SET @IsMarried = 1; " + "SELECT @IsMarried AS [Married?]; ", connection); connection.Open(); SqlDataReader rdr = command.ExecuteReader(); while (rdr.Read()) MessageBox.Show("Is Married? " + rdr[0].ToString()); rdr.Close(); command = new SqlCommand("DECLARE @EmplStatus int; " + "SET @EmplStatus = 2; " + "SELECT @EmplStatus; ", connection); rdr = command.ExecuteReader(); while (rdr.Read()) { MessageBox.Show("Employment Status: " + rdr[0].ToString()); cbxEmploymentsStatus.SelectedIndex = int.Parse(rdr[0].ToString()); } rdr.Close(); connection.Close(); }
If you want to use very small numbers such as student's ages, or the number of pages of a brochure or newspaper, apply the tinyint data type to such a field. A variable with the tinyint data type can hold positive numbers that range from 0 to 255.
The smallint data type follows the same rules and principles as the C#'s short data type to store numbers that range between -32,768 and 32,767. Here is an example that uses a smallint:
using System; using System.Drawing; using System.Windows.Forms; using System.Data.SqlClient; public class Exercise : System.Windows.Forms.Form { Button btnDatabase; public Exercise() { InitializeComponent(); } void InitializeComponent() { btnDatabase = new Button(); btnDatabase.Text = "Database"; btnDatabase.Location = new Point(12, 12); btnDatabase.Click += new EventHandler(btnDatabaseClick); Controls.Add(btnDatabase); } void btnDatabaseClick(object sender, EventArgs e) { using (SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;")) { SqlCommand command = new SqlCommand("DECLARE @NumberOfPages smallint; " + "SET @NumberOfPages = 748; " + "SELECT @NumberOfPages; ", connection); connection.Open(); SqlDataReader rdr = command.ExecuteReader(); while (rdr.Read()) MessageBox.Show("Number of Pages: " + rdr[0].ToString(), "Database Application", MessageBoxButtons.OK, MessageBoxIcon.Information); } } public static int Main() { System.Windows.Forms.Application.Run(new Exercise()); return 0; } }
The bigint data type follows the same rules and principles as the C#'s long data type and can hold numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
The binary data type is used for a variable that would hold hexadecimal numbers. Examples of hexadecimal numbers are 0x7238, 0xFA36, or 0xAA48D. Use the binary data type if all values of the variable would have the exact same length (or quantity).
If you anticipate that some entries would be different than others, then use the alternative varbinary data type. The varbinary type also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals.
Decimal Numbers |
Introduction |
Like C#, Transact-SQL suports decimal number with single or double-precistion. If you want to use a variable whose values will be decimal, specify its data type as numeric or decimal. Here is an example:
void btnDatabaseClick(object sender, EventArgs e) { using (SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;")) { SqlCommand command = new SqlCommand("DECLARE @Distance decimal; " + "SET @Distance = 648.16; " + "SELECT @Distance; ", connection); connection.Open(); SqlDataReader rdr = command.ExecuteReader(); while (rdr.Read()) MessageBox.Show("Distance: " + rdr[0].ToString(), "Database Application", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
Transact-SQL supports decimal numbers whose precision is not particularly important. To declare such a variable, use the float or the real keyword. Here is an example:
void btnDatabaseClick(object sender, EventArgs e) { using (SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;")) { SqlCommand command = new SqlCommand("DECLARE @Radius float; " + "SET @Radius = 52.49; " + "SELECT @Radius; ", connection); connection.Open(); SqlDataReader rdr = command.ExecuteReader(); while( rdr.Read() ) MessageBox.Show("Radius: " + rdr[0].ToString(), "Database Application", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
The Precision of a Decimal Number |
A precision is the number of digits used to represent a numeric value. For example, the number 42005 has a precision of 5, while 226 has a precision value of 3. If the data type is specified as an integer (the int and its variants) or a floating-point number (float and real), the precision is fixed by the database and you can just accept the value set by the Microsoft SQL Server interpreter. For a decimal number (decimal or numeric data types), Microsoft SQL Server allows you to specify the amount of precision you want. The value must be an integer between 1 and 38.
The scale of a number if the number of digits on the right side of the period (or the character set as the separator for decimal numbers for your language, as specified in Control Panel). The scale is used only for numbers that have a decimal part, which includes currency (money and smallmoney) and decimals (numeric and decimal). If a variable is declared with the decimal or numeric data type, you can specify the amount of scale you want. The value must be an integer between 0 and 18. Here is an example:
DECLARE @WeeklyTime Decimal(6,2);SET @WeeklyTime = 36.50; SELECT @WeeklyTime AS [Time Worked]; GO
Here is an example that declares and uses a decimal
variable:
private void btnOperate_Click(object sender, EventArgs e) { SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;"); SqlCommand command = new SqlCommand("DECLARE @EmplStatus int; " + "SET @EmplStatus = 2; " + "SELECT @EmplStatus; ", connection); connection.Open(); SqlDataReader rdr = command.ExecuteReader(); while (rdr.Read()) { cbxEmploymentsStatus.SelectedIndex = int.Parse(rdr[0].ToString()); } rdr.Close(); command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2); " + "SET @WeeklyHours = 36.50; " + "SELECT @WeeklyHours; ", connection); rdr = command.ExecuteReader(); while (rdr.Read()) { MessageBox.Show("Weekly Hours: " + rdr[0].ToString()); txtWeeklyTime.Text = rdr[0].ToString(); } rdr.Close(); connection.Close(); }
Currency Values |
If a variable would hold monetary values, you can declare it with the money data type. A variable with a money data type can hold positive or negative values from -922,337,203,685,477.5808 to +922,337,203,685,477.5807. While the money data type can be used for a variable that would hold large quantities of currency values, the smallmoney data type can be applied for a variable whose value cannot be lower than -214,748.3648 nor higher than 214,748.3647.
The precision and scale of a money or smallmoney variable are fixed by Microsoft SQL Server. The scale is fixed to 4.
Here is an example that uses currency variables:
private void btnOperate_Click(object sender, EventArgs e) { SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;"); SqlCommand command = new SqlCommand("DECLARE @EmplStatus int; " + "SET @EmplStatus = 2; " + "SELECT @EmplStatus; ", connection); connection.Open(); SqlDataReader rdr = command.ExecuteReader(); while (rdr.Read()) { cbxEmploymentsStatus.SelectedIndex = int.Parse(rdr[0].ToString()); } rdr.Close(); command = new SqlCommand("DECLARE @WeeklyHours Decimal(6,2); " + "SET @WeeklyHours = 36.50; " + "SELECT @WeeklyHours; ", connection); rdr = command.ExecuteReader(); while (rdr.Read()) { txtWeeklyTime.Text = rdr[0].ToString(); } rdr.Close(); command = new SqlCommand("DECLARE @HourlySalary SmallMoney; " + "SET @HourlySalary = 15.72; " + "SELECT @HourlySalary; ", connection); rdr = command.ExecuteReader(); while (rdr.Read()) { MessageBox.Show("Hourly Salary: " + rdr[0].ToString()); txtHourlySalary.Text = rdr[0].ToString(); } rdr.Close(); command = new SqlCommand("DECLARE @WeeklyHours Decimal(6, 2), " + " @HourlySalary SmallMoney, " + " @WeeklySalary SmallMoney; " + "SET @WeeklyHours = 36.50; " + "SET @HourlySalary = 15.72; " + "SET @WeeklySalary = " + " @WeeklyHours * @HourlySalary; " + "SELECT @WeeklySalary; ", connection); rdr = command.ExecuteReader(); while (rdr.Read()) { MessageBox.Show("Weekly Salary: " + rdr[0].ToString()); txtWeeklySalary.Text = rdr[0].ToString(); ; } rdr.Close(); connection.Close(); }
Characters and Strings |
A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you want a variable to hold a fixed number of characters, such as the book shelf numbers of a library, declare it with the char data type. Here is an example:
DECLARE @Gender char;
By default, the char data type can be applied to a variable that would hold one character at a time. After declaring the variable, when initializing it, include its value in single-quotes. Here is an example:
DECLARE @Gender char; SET @GENDER = 'M'; SELECT @Gender AS Gender; GO
By default, when you initialize a character variable, the interpreter reserves 8 bits of memory for the variable. This could be a problem if you want to store characters other than those used in US English. The alternative is to ask the interpreter to reserve 16 bits of space and follow Unicode rules. To do this, when initializing the variable, precede its value with N.
Strings |
A string is a character or a combination of characters. If a variable will hold strings of different lengths, declare it with the varchar data type. The maximum length of text that a field of varchar type can hold is equivalent to 8 kilobytes. In some circumstances, you will need to change or specify the number of characters used in a string variable.
To specify the maximum number of characters that can be stored in a string variable, on the right side of char or varchar, type an opening and a closing parentheses. Inside of the parentheses, type the desired number.
The text data type can be used on a variable whose data would consist of ASCII characters. As opposed to a varchar type of field, a text type of field can hold text that is longer than 8 kilobytes.
The nchar, nvarchar, and ntext types follow the same rules as the char, varchar, and text respectively, except that they can be applied to variables that would hold international characters, that is, characters of languages other than US English. This is done following the rules of Unicode formats.
When initializing the variable, to follow Unicode rules, precede its value with N follow by single-quotes:
1> DECLARE @FirstName nchar(20), 2> @LastName nchar(20); 3> SET @FirstName = N'Philomène'; 4> SET @LastName = N'Açore'; 5> SELECT @FirstName As "Prénom", 6> @LastName As "Nom de Famille"; 7> GO Prénom Nom de Famille -------------------- -------------------- Philomène Açore (1 rows affected) 1>
In Powershell, if you are not using Unicode rules, the string must be included in double-quotes and if you are using Unicode, the string must be included in single-quotes.
User-Defined Types |
Introduction |
If you have programmed in languages like C/C++, Pascal, or ADA, etc, you are probably familiar with the ability to give a friendly name to a known data type. Transact-SQL also gives you this option. A user-defined data type (UDT) is a technique of creating a data type based on an existing Transact-SQL data type.
Before creating a user-defined data type, you must be familiar with the existing types. If you want, you can create an alias name for one of these. To create a UDT in Transact-SQL, the basic formula to follow is:
CREATE TYPE AliasName FROM BaseType
You start with the CREATE TYPE expression, followed by the desired name for the new type. After the FROM keyword, type an existing Transact-SQL data type. Here is an example:
using System; using System.Drawing; using System.Windows.Forms; using System.Data.SqlClient; public class Exercise : System.Windows.Forms.Form { Button btnDatabase; public Exercise() { InitializeComponent(); } void InitializeComponent() { btnDatabase = new Button(); btnDatabase.Text = "Database"; btnDatabase.Location = new Point(12, 12); btnDatabase.Click += new EventHandler(btnDatabaseClick); Controls.Add(btnDatabase); } void btnDatabaseClick(object sender, EventArgs e) { using (SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;")) { SqlCommand command = new SqlCommand("CREATE TYPE NaturalNumber FROM int; ", connection); connection.Open(); command.ExecuteNonQuery(); MessageBox.Show("A user-defined data type has been created", "Database Application", MessageBoxButtons.OK, MessageBoxIcon.Information); } } public static int Main() { System.Windows.Forms.Application.Run(new Exercise()); return 0; } }
In the same way, you can create as many aliases of known data types as you want. You must also be aware of rules that govern each data type. Here are examples:
void btnDatabaseClick(object sender, EventArgs e) { using (SqlConnection connection = new SqlConnection("Data Source=(local);" + "Database='Exercise1';" + "Integrated Security=yes;")) { SqlCommand command = new SqlCommand("CREATE TYPE ShortString FROM nvarchar(20); " + "CREATE TYPE ItemCode FROM nchar(10); " + "CREATE TYPE LongString FROM nvarchar(80); " + "CREATE TYPE Salary FROM decimal(8, 2); " + "CREATE TYPE Boolean FROM bit;", connection); connection.Open(); command.ExecuteNonQuery(); MessageBox.Show("User-defined types have been created", "Database Application", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
Using a User-Defined Type |
After creating a UDT, you can use it as you see fit. For example, you can declare a variable for it. Then, before using it, you must initialize it with the appropriate value. Here are examples:
DECLARE @EmployeeID NaturalNumber, @EmployeeNumber ItemCode, @FirstName ShortString, @LastName ShortString, @Address LongString, @HourlySalary Salary, @IsMarried Boolean; SET @EmployeeID = 1; SET @EmployeeNumber = N'28-380'; SET @FirstName = N'Gertrude'; SET @LastName = N'Monay'; SET @Address = N'1044 Alicot Drive'; SET @HourlySalary = 26.75; SET @IsMarried = 1; SELECT @EmployeeID AS [Empl ID], @EmployeeNumber AS [Empl #], @FirstName AS [First Name], @LastName AS [Last Name], @Address, @HourlySalary AS [Hourly Salary], @IsMarried AS [Is Married ?]; GO
You can mix Transact-SQL data types and your own defined type in your code.