Archive for April, 2012

Object Naming Rules in SQL Server

Here goes a list of all rules in order to name an object in SQL Server. The below list depends upon compatibility level of 100 i.e. SQL Server 2008. However, most of the rules do satisfy SQL Server 2000 as well as SQL Server 2005.

1. A name should have minimun 1 and a maximum of 128 characters. However, there is an exception in case of local temporary tables. A temporary table can have a maximum of 116 characters. Rest 12 characters are system-generated when temporary table is going to be created. In fact, a local temporary table is also having 128 characters internally.

2. First character should be either a through z, from A through Z, also letter characters from other languages, underscore ( _ ), number sign ( # ), or at sign ( @ ). Objects having number or st sign have different significance in SQL Server, which we’re not discussing over here.

3. Subsequent characters can have underscorer ( _ ), at sign ( @), dollar sign ( $ ) , decimal numbers from either Basic Latin or other national scripts.                                                                                                                                                              Read more…

What is ‘Identifier’ in SQL Server?

I’ve come across this question many times in life – What is “Identifier” in SQL Server? So, I thought to write about it today. Here it goes.

In SQL Server we create objects like database, login, tables, views, stored procedures, columns, indexes, etc. We give a name to every object, which is used to refer to it later on. This name is known as “IDENTIFIER” in SQL Server. But, one must be surprized to know that there are objects in SQL Server, which don’t require identifier i.e. a name. For example, Constraints.

Identifiers are created at two levels: (i) SQL Serve Instance Level, and (ii) Database Level. 

Identifiers can be logically categorized into two categories: (i)  Regular Identifier, and (ii) Delimited Identifier.

Regular identifier is one which conforms to the object naming rules in SQL Server while Delimited identifier is one which does not conform to object naming rules for SQL Server. Delimited identifiers are enclosed with either ( ‘  ‘ ) single quote or ( [  ] ) square bracket.

Types of Views in SQL Server 2008

Though, title of this blog is ‘Types of Views in SQL Server 2008’, however I’m goint to discuss the ‘Types of User-Defined Views in SQL Server 2008’ over here. I’m sure you are aware that we have System-Defined and User-Defined views in SQL Server.

I opine that the basis to categorize the Views into various categories could be: how and where the view is getting data from. However, there might be n number of reasons/parameters to have this categorization by the Microsoft.

SQL Server 2008 organizes user-defined views into three major categories:

  1. Standard Views: Most of the time, we create this kind of view where we use single or more that one tables, use JOINs and other clauses and pull the data out. The tables are saved on a single physical file, most of the time on primary data file – that is .mdf file. All tables don’t have any kind of partitions – I’ll discuss partitions in Partitions Views section below. So, most of views that we write come under this category.                                                                                                                                Read more…

Restrictions with View Definition in SQL Server 2008

We do have a few restrictions with the view’s definition in SQL Server. We cannot use each and every T-SQL statement inside a view’s definition. And, of course, this is one of the most commonly asked questions in technical interviews as well.

Here goes a list of restrictions with view’s definition:

  1. Every column, basically derived columns must have a column name.
  2. The INTO keyword cannot be used inside the SELECT statement.
  3. The COMPUTE BY clause cannot be used with the SELECT statement.
  4. Normally, the ORDER BY clause cannot be used with the SELECT statement. However, if we wish to use ORDER BY clause, we must use TOP(n) with the SELECT statement.
  5. A local or global temporary table (tables with # and ## sign) cannot be used inside a view.                                                                                                                                       Read more…