1、Introduction to SQL Server By Samuel Relational databases have been around for 30 years, but they were not the original kind ofdatabase, nor are they the newest kind of database. XML and object-oriented data structures haveevolved in recent years. But relational databases are still by far the most p
2、opular kind of database available and will be for some time to come. SQL is the abbreviation of Structured Query Language and it is for relational databases, as the title indicates this is only for fresher who has just started the carrier or who is waiting to open up the carrier in the application p
3、rogramming side. But that does not mean this article is a tutorial for a fresher who does not know anything about SQL.This article is meant for who already have a little knowledge in SQL and want toimprove it. What Does SQL Do? First, SQL is the premier tool for viewing information from a relational
4、 database. It doesnt just give you a data dump. SQL gives you sophisticated tools to summarize, consolidate, and calculate from the data. Using table relationships, data can be combined from multiple tables in a number of ways. With a properly designed database, SQL can answer practically any questi
5、on about the data. Second, SQL provides commands to manipulate the data in a relational database. Records can be updated and added to or deleted from a table. Here is SQL as a database language really shines. Procedural programming languages, such as BASIC, might require several lines of code to upd
6、ate a record in a database table. In addition, procedural programming languages would have to use some sort of looping structure to repeat this process on every record. SQL operates on an entire set of records all at the same time. SQL is like haiku for programmers； often a dozen words or fewer can
7、delete or change thousands of records. Finally, SQL is a complete data definition language (DDL). The database itself can be created along with all tables, fields, primary keys, and relationships. Add to that the record insert commands, and you can have a complete database and all its data expressed
8、 in programming code. This greatly enhances a database programmers ability to work remotely or to port data enhancements among various installations. The prerequisite for learning SQL is knowledge in Discrete Mathematics (Set Theory,Relations and Functions). Although it is not necessary to learn all
9、 the theorems and proof for the theorems in the Discrete Mathematics, you should have learned the basic concepts of the Sets, Relations and Functions. This will help you to learn SQL queries and fundamentals easily. If you want to explore a RDBMS more deeply you should learn Graph Theory too. Althou
10、gh I tried to avoid SQL Server specific topics in this article, I am sure that some topics are pure to SQL server such as SQL Enterprise manager. Representation of Data by DBMS DBMS should represent the data stored by them in some form and it is most common to represent them as Column, Row, Tables a
11、nd Databases. As you know SQL you know what are columns, rows, tables and database. Primary Key A column or set of columns used to uniquely identify the records in a table. Primary Keys dont allow NULL values. You can relate a table with other table if you have defined primary on it. (So defining UN
12、IQUE and NOT NULL constraint is not equivalent to primary key). SQL server will create a Clustered index while you create a primary key. Primary keys should be carefully defined since based on it other relations are defined. Poorly designed primary keys will affect insert, update and delete operatio
13、ns. Primary keys are different from which are used for paperwork. Foreign Key The primary key of one of the tables is almost always involved in the relationship. The field in the other table on the other end of that relationship is called the foreign key. The term simply refers to the fact that this
14、 field is key to relating to a foreign (or other) table. In the Lyric Music database there is a relationship between artists and titles. The ArtistID field is the primary key in the Artists table. Therefore, the ArtistID field in the Titles table is a foreign key. It relates the Titles table to the
15、primary key in the Artists table. Most table relationships can be described as one-to-many. In a one-to-many relationship, a single record in the first table can be related to many records in the second table. However, each record in the second table relates to only one record in the first table. In
16、 addition to one-to-many relationship, tables can have one-to-one relationships. But these are much less common. DDL (Data Definition Language) What is data type? Classification of data into similar groups. Names, Money,Date, Time, etc are examples for data type. What is the use of classification or
17、 data type? It increases the performance, reduces the space needed to store the data. DML (Data Manipulation Language) Data Manipulation Languages contains command used to query, change, and add data from/to the database. This includes the following commands - SELECT, INSERT, DELETE. All these comma
18、nds are deals with set of data. So I want to refresh the Set theory concepts before going further. Transactions A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolatio
19、n, and Durability) properties, to qualify as a transaction. DTS (Data Transformation Services) Data Transformation Services are used to transfer and transform data from one datasource to another datasoure in the same server or another server.Some applications of DTS 1) Copying data from one database
20、 to another database 2) Copying data structure from one database to another database 3) Migrating data from other datasources such as Flat File, Excel, Access, Oracle to SQL Server. 4) Migrating data from SQL server to other datasources. Since the DTS topic is huge you cannot get to know what it is
21、until you do actual task that is related to DTS. So try the following things and you will get to know something about DTS. Create an Excel file with the following columns EmployeeName, Address, DateOfJoin, PhoneNumber, MaritialStatus, and Department. Fill this excel sheet with some meaningful inform
22、ation and then try to transfer the contents from Excel to SQL using DTS Import/Export Wizard. You can also create DTS packages which can be scheduled to run at future. DTS package programming allows mapping source fields to different destination fields and also provides error control. SQL Profiler S
23、QL Profiler is a handy tool used to analyze what is happening inside and outside of a SQL Server. Simply it is a tool which extracts the log information from SQL server.These logs will help you debug applications, optimize queries, redesign database structure. Running SQL Profiler is very easy, Clic
24、k SQL profiler from the SQL Server group menu. Then select New Trace from the file menu. It will open the connection dialog box. Type your server name, user name and password. Now you have to set options for your profile. The options include what events you want to trace, what columns you want in the output, filters if any.
