Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Resources The SQL Server Programming Environment
Print E-mail

Client server database applications (of which Microsoft SQL Server is a prime example) are multifaceted programs which involve not only that involve the efforts of various roles in an organization to model the solution of a business need. The actual mechanics of the database solution typically involve:

  • The design and creation of the database itself
  • Writing a set of instructions for processing information in that database
  • Establishing relationships and limits among the tables themselves
  • Controlling security at multiple levels for users, their roles, and permissions for each of the database objects
  • Maintenance procedures including backup and recovery
  • Creation and selection of a database interface for the users.

The conduit for transmitting calls or requests from the user passes through the language of the User Interface or UI (VB.NET, for instance), through the call level interface or CLI (ODBC, for example), and passes to the language of the database itself. In the case of Microsoft SQL Server, this language is called Transact-SQL, or T-SQL.

SQL Server itself can be best understood as a platform of Windows programs that are used to manage data in a multi-user environment. It exists as of this writing only on the Windows platform. And, although theoretically could be used as a desktop super tool, in practice it is almost always run on a server responding to the multiple requests of other desktop or server machines in the enterprise, or on the internet. Internet.

The major component of SQL Server is the query processor, and it is that component which gives the product a distinct competitive advantage. The query processor parses a T-SQL request and normalizes it, creating a plan for executing it in the most efficient manner. This accounts for much of the speed of the product in processing queries. Among the factors affecting the plan are the construction and relationships between the tables in the query, the existence and types of indices used, and the clauses required by the query.

Database Objects

There are 9 database object types in a SQL Server database.

Tables

The tables in SQL Server is where all of the data for the program is stored. contain all the data of the programs. Tables are composed of columns containing data of various types. Each record in a table s is stored in a separate row, which can contain some 8060 bytes of data (excluding text and image fields).

Views

A view is a defined SQL statement which that can be named and referred to later by that name. It has application for securitMay 23, 2006y. Instead, one can refuse permission to view or change a table, but allow those operations on a view. Views are analogous to the Access object called a dynaset. They are virtual tables within a database.

Indexes

Indexes are containers of pointers which that speed up the retrieval of data from a table. They may be unique, which impose a constraint on the table of requiring that each record in a table have a unique value for the field that is indexes, or non-unique. They may also be clustered, which imposes a physical ordering of records in a table, or non-clustered, which impose only a logical ordering of records. Indexes are a collection of pointers. Although indexes vastly improve the speed of retrieval of data from a table, they also impose an overhead because they themselves require updating.

Stored Procedures

A stored procedure is a collection of at least one Transact-SQL statement needed to manipulate and/or retrieve data from the database tables. They can accept multiple arguments, and are complied at the time they are created to maximize their efficiency. SQL Server comes with a bunch of useful system stored procedure already written which can to productive things like email and schedule activities.

Triggers

Triggers are user defined stored procedure which procedures that are triggered when changes are made to a table. They can be set to run when data in a certain field meets your requirements, of when a new record is added to a table, or when data in a field changes, etc.

Users

Users are the way SQL Server assigns permissions to access the data objects. Depending on the validation model, users can be specific, or generic

Roles

Roles are a method for managing security and permissions if SQL Server. They are similar to the groups which that are used in network security models.

Rules

Rule s are used to validate data being entered into a column. It can be bound to multiple columns, but each column can have only one rule associated to it. There is some question regarding the future compatibility of Rules, so check constraints are probably a better method of accomplishing the same functionality.

User-defined Data Type

User defined data types are defined using the system datatypes. This helps to create consistent data fields across the database, with all the same lengths and datatypes, rules and constraints. It makes for better programming and ease of update

User-defined Function

A new enhancement in SQL 2000 is the ability to create user-defined functions. These act much like the functions in VB or C++ languages, and are so useful that it makes me wonder why it took them so long to implement this ability.

Resources

•  Tutorial: SQL 7 Database Files

This is an insightful tutorial on SQL 7 database files.

•  Tutorial: Introduction to Structured Query Language

This is an excellent tutorial that discusses the concept of SQL in detail.

Metro NY / NJ SQL Server Consultants

We specialize is custom database software. Call us for a free consultation (973) 635 0080

 

Computer Consultants: Database Development
Computer Programming: Web Site Services
Computer Programming: Custom Software
Computer Consulting: IT Consulting
Paladin Consultants, LLC Home Page
Computer Consulting: Contact Us

 

 
Bookmark and Share