Tag Archives: T-SQL Fundamentals

Schemas and Naming in SQL Server

Whenever you execute a data selection query in SQL Server, you’ll be accessing one or more database objects, so it is important that you understand how things are named. In SQL Server, database object names use a convention that can contain four parts, any of which can be blank, except the object name:

  • The server_name specifies linked server name or remote server name. A blank implies the current server.
  • The database_name specifies the database name. A blank implies the current database context.
  • The schema_name specifies the name of the schema that contains the object. A blank implies the default schema for the current user or the dbo schema if no other default schema is assigned to the current user.
  • The object_name specifies the name of the object.

In most situations, it is not necessary to use all four parts. However, the recommendation is to use the schema name with the object name, as shown in the following two examples. The first example for the Northwind database will work with or without dbo, because the server uses dbo when no schema is explicitly defined and no default schema is explicitly assigned to the current user. The second query for the AdventureWorks2012 database will fail if the
schema name Sales is omitted, unless the user has Sales set as her default schema. In this case, the Store table was created in the Sales schema. This query will work only for a user with Sales as the default schema.

It might be tempting to deal with schemas by keeping all database objects assigned to dbo and avoid creating or assigning any other schemas. However, schemas can be a useful way of creating multiple namespaces in a database, just as namespaces make it easier for .NET programmers to keep track of classes. The AdventureWorks2012 database provides a good example of using schemas as namespaces. You can also assign permissions on a schema that grant the permission to all objects within it, which makes schemas a powerful security tool for protecting data access. 

NOTE

In versions before SQL Server 2005, a schema was created automatically for each database user. When a user created an object, the object was automatically created in that user’s
schema—unless the user was a database owner, in which case the object was created in the dbo schema. In more recent versions of SQL Server, each user does not automatically have a schema. Schemas are created independently of users; users must explicitly be assigned rights to a schema and can be assigned a default schema. Many users can have rights to use any schema and many users can have the same schema as their default.

 

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Select, Where and Clauses course written by expert Don Kiely. Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

 

Overview of Transact-SQL (T-SQL)

Transact-SQL is the SQL Server implementation of SQL, a standard codified
by the American National Standards Institute (ANSI) and also adopted by the
International Organization for Standardization (ISO). No single vendor has
fully implemented every part of the ANSI/ISO standard, and each vendor has
added its own proprietary extensions to the language, so you’ll find plenty of
things in Transact-SQL (T-SQL) that you won’t find in other database
products. The standard is updated roughly every three or four years, and so is a
moving target that no SQL product ever fully implements.

The SQL language came about as a result of the work that Dr. E. F. Codd did
in the 1960s on his Relational Database Model. The first version of the
language was known as SEQUEL. It was then completely rewritten in the
seventies, and eventually became known as SQL for Structured Query
Language, because it turned out that the acronym SEQUEL had already been
trademarked. The original “sequel” pronunciation has stuck to this day–SQL
Server is still widely referred to as sequel server, although some purists insist
that the language name should be pronounced ess-que-ell. However you
pronounce it, the SQL standard has been relatively well received and is the
most widely supported standard today.

Transact-SQL (T-SQL)

Transact-SQL is Microsoft’s implementation of the SQL language in SQL
Server. The language implements a significant subset of the features of
standard SQL, as well as some very useful extensions to the ANSI standard
that add procedural capabilities, which makes Transact-SQL more like a
programming language. There are control-of-flow features, such as IF…ELSE
syntax and WHILE loops, as well as support for variables, parameters, and
user-defined functions. Like other programming languages, Transact-SQL
supports built-in functions for manipulating strings, numbers, and date/time
information, and for returning system information.

Although Transact-SQL has programming language features, you’d never want
to use Transact-SQL to replace a general purpose programming language. It

has no user interface and its programming constructs are very limited. The
main advantage to programming in Transact-SQL is that your routines execute
on the server and are highly efficient for set-based data operations. Transact-
SQL provides the building blocks for all your views, stored procedures, userdefined
functions, and triggers. When you perform as much processing as
possible in Transact-SQL, performance improves because less data has to
traverse the network for processing on the client.

T-SQL has two broad sets of statement types: data definition language (DDL)
and data manipulation language (DML) statements. DDL statements let you
create and update database and server objects, while DML statements let you
work with the data itself to create, retrieve, update, and delete rows of data.
These four operations are so common in relational databases that they are often
collectively referred to as CRUD operations.

Microsoft adds a number of new T-SQL features in every new version of SQL
Server, some of which are proprietary to that product, while others are
implementations of the features in the SQL standard. This chapter focuses on
the fundamentals of the T-SQL language.

NOTE

SQL Server hosts the .NET Common Language Runtime (CLR),
which allows you to write code in any supported .NET language,
such as C# or Visual Basic. You can use Visual Studio to write
programs that are then compiled into assemblies and loaded into
SQL Server. CLR procedures are designed for code that is
processor-intensive, such as complex mathematical or string
manipulation. The CLR is not designed to replace Transact-SQL,
which is always the best choice for data access and for set-based
operations. Any time a CLR procedure performs data access, it
uses Transact-SQL “under the covers.”

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.