Microsoft SQL Server 2000 And T-SQL - Designing A Logical Database
(Page 2 of 2 )
Let us take a look at some of the
classic architecture used in the past and present. Single Tier (Host) System: This old mainframe and mini-computer model. It mostly sent information about data display to the terminal. It was very reliable, deployment was easy, and requires very little bandwidth on network. Disadvantages were very expensive hardware, proprietary in nature and limited software packages.
2-Tier Architecture (Client-Server): This was based on the fact that you’re going to harness more power when you distribute the computing requirement as much as possible. The server performs only data service part. The business and UI side was performed at the client thus ensuring load balance. Advantages were distributed workload, speedy processes while the negatives are terrible network clogs, and installation is time consuming and licensing for each separate client.
3-Tier:The most hyped architecture of today. This model takes the approach of breaking up all the three services into completely separate logical models. Clients are responsible for UI issues. The business and data services are logically separated for each other. This adds a significant level of stability and scalability since everything is “component” based.
N-Tier: It looks a lot like 3-tier model, but instead breaks the component into their smallest logical units of work. If the data service layer is done properly then this too can be spread cross multiple servers and moved as needed. The only impact will be on data service component that provides access to moved data. So deciding on which one to use will depend on the task at hand and resources available, plus what the client might think will suit their business.
About .Net: Pronounced, as “dot net” is a new development pushed by Microsoft. The latest updates of Microsoft’s server products including SQL Server 2000 is now being called as .Net Servers. The strength of .Net lies in the fact of increasing supports to XML.
An Overview Of Database Objects: An RDBMS such as SQL Server contains many objects.
• The
Database itself is effectively the highest-level object, is typically a collection of a set of tables and other objects such as stored procedure and views.
•
Transaction Log: while data is read in from the database, any changes you make are not directly sent to database. Instead written to the transaction log and updated later when checkpoint is issued by the database.
•
Tables: This is the more central to the make-up of the database, and is made of what is called the domain data (columns) and entity data (rows). The nature of data in each record or row is described and restricted by the data type of the column.
•
Indexes: Is an object that exists within the framework of a table or view. There are two types clustered index you can have only one per table and non-clustered you can have many of these, they basically provide faster retrieval of data.
•
Triggers: This object exists only within the frame work of the table, triggers are logically pieces of code executed automatically when certain things like insert, update or delete happen on a table.
•
Constraints: This too exists with the table to ensure data integrity.
•
Filegroups: By default tables and all other database object were stored in primary filegroup. But SQL Server 2000 allows 32,000 & more secondary files to be added to the primary group.
•
Views: Is something like a virtual table, new with SQL 2000 is indexed views.
•
Stored Procedure: Is an ordered series of TSQL statements bundled up in a single logical unit. Since they are a pre-complied saves time and encapsulates.
•
User Defined Functions: This is new to SQL 2000, they are similar to procedure except that they return a value.
•
Users and Roles: Users are pretty many equivalents to logins and in turn belong to one or more Roles.
•
Rules: It restricts what information has to go into a table.
•
Default: While inserting values in a column which is define as default then it get the value inserted automatically as define by default.
•
User-Define Data Types: these are an extension of system define data types.
•
Full-Text Catalog: Has been greatly enhanced in SQL Server 2000, it helps in mapping of data and speed up search. We will be taking a closer look at each of these objects in subsequent articles.
Getting Connected To SQL Server: Having Installed SQL Server next question is, How to start SQL Server 2000, click on start button select Microsoft SQL Server 2000. This in turn will open the Query Analyzer (QA) which is considered the base tool for development and trouble shooting. A login screen appears, you could choose local which means a default instance of the SQL Server is on this machine regardless of its name (the name is the same as your machine is named on the network). Getting connected use SQL Server authentication and login name SA plus whatever password SA was set at time of installation. When you click OK Query Analyzer will open. In the main window type:
Select * from Information_Schema.tables
The main window will automatically divide into
two the top is the query and below is the result pane listing all the tables. Today we will be taking a look at the first of the SQL Server 2000 object: the database itself in all is colors. An RDBMS such as SQL Server may have multiple user databases on one server or have only just one. And an added feature of SQL Server 2000 is one can have many instances of the server with separate logins and management rights. When we first load SQL Server 2000 you have six database installed. The
Master Database this holds a special set of tables called system tables.
Model Database forms the template for future new databases.
MSDB Database, is where SQL Agent process, stores any system task.
Tempdb Database when we build complex query SQL needs a place to build interim tables it does so in the tempdb.
Pubs Databaseis mostly training articles.
Northwind Database is also a training database with sample tables for practice. {mospagebreak title=Implementing A Database Design Using MS SQL Server 2000}
A database is a collection of tables and objects such as view, indexes, stored procedure and triggers. The data storage in a database may be related to a process such as inventory or payroll. SQL Server can support many databases. The data stored in one database may or may not be related to data stored in other databases. The data and objects in a database are stored as a set of operating system files.
There are three types of files: Primary, Secondary and Transaction Log. The primary file consists of database objects. The transaction log records all modification that occurred in the database. Very large databases may need multiple secondary files to store user data. All databases have a primary file and one or more transaction log. Logical file names are used in T-SQL statements. The primary file has
.mdf extension while secondary file .ndf extension and log file .ldf extensions. Now we will see
filegroups it is collection of files. A database comprises of
primary filegroup and user-define filegroup. Filegroups can be marked as ReadOnly or ReadWrite.
Creating Databases: Let’s get down and dirty, we are now going to see how to create our own database. The basic syntax for creating any object is as follows; you have to type it out in Query Analyzer.
FONT face=verdana size=1>
Create <Object type > <Object Name>
The most basic of
create database statement
looks like below:
Create database
This will yield a database that looks exactly like the
“model database”. In real world application development it takes a more complex approach, so a more full syntax listing:
Create database
[On [Primary]
([Name = ‘logical file name’ >,]
Filename = < ‘File Name’ >
[, Size = ]
[, Maxsize = ]
[, Filegrowth = ]
[ Log On
([Name = ‘logical file name’ >, ]
Filename = < ‘File Name’ >
[, Size = ]
[, Maxsize = ]
[, Filegrowth = ])]
[Collate ]
[For load | For Attach]
There is lot there so let us break it down
part by part. First the word: On is used in two places to define the location of the file where the data is to be stored. The Primary keyword means that main filegroup where to physically store data. Name is a name for the file you are defining, but only a logical name used by SQL Server to internal reference. FileName is the physical name on the disk of the actual operating system file in which data and log is stored. The data file named by default with .mdf and log file .ldf. Size is no mystery by default it is MB but you can use KB also. Maxsize SQL Server allows your database to allocate memory and grow automatically when extra disk space is needed. Maxsize is the maximum size to which it can grow. Filegrowth while, Size sets the initial size & Maxsize determines how large it can grow, Filegrowth justifies how fast this can be done. Logon allows you to establish where you want your log to go to a specific set of file and where those files are to be located. Collate this is totally new to SQL Server 2000 and deals with issues like sorting order, case sensitivity. For Load this is mainly for backward compatibility. Finally For Attach is used to attach an existing set of database file to current server. Now Let us take a simple example to illustrate the above syntax, Say you want to create a database to keep track of the customers visiting your shop “Maya”. Let us name the database as “Customers” itself. We are ready to do go with flying colors……
Create database Customers
On
(Name = ‘Customers’,
// Says you save it in C drive, under main Program file and MSQL directory & Data
// sub directory, then type in as follows other give your full path name.
Filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\Customerdata.mdf’,
Size = 15 MB,
Maxsize = 30 MB,
Filegrowth =5 MB)
Log On
(Name = ‘CustomerLog’ , ]
Filename = ‘C:\Program Files\Microsoft SQL Server\MSSQL\data\Customelog.ldf’,
Size = 5 MB,
Maxsize = 20 MB,
Filegrowth =5 MB)
GO
Line by line Explanation, we have
created a database named “Customers”, stored at the given path under C drive. With initial size of database 15 MB, maximum size 30 MB and file growth size set to 5 MB. Which means after the database reaches 15 MB and you want to add more tables or other objects, it can grow up to 30 MB automatically at a rate of 5 MB at each expansion. Similarly a log will also be established with the name “Customerlog” and initial size of 5 MB, growing at a rate of 5 MB as and when needed and up to 20 MB. And this info will be stored as a log file under the given file path.
Next Comes The Million-Dollar Question: How To View A Database. You can try running a command called
sp_helpdb which takes one parameter- the database name. So type this to execute the above code.
Exec sp_help ‘Customers’
This yields two results, first is based on the combine
(data & log) information about your database. The second provides various files that make up the database. Congratulate yourself for having learnt how to create your own database, this is what most database -administrators and programmers do. In the next part we will see how to add tables to Customer database. Plus how to alter or modify tables when need arises.
Hope you have got an in-depth look into how databases came into existence, what architecture to use, database Object in SQL Server 2000 and the most talked about .NET plus how SQL Server 2000 blends in with the .NET Technology.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 26 
