Archive for March, 2012

WITH SCHEMABINDING Option with CREATE VIEW Statement in SQL Server 2008

Let’s discuss the WITH SCHEMABINDING option with CREATE VIEW statement today.

In order to appreciate this option, let’s consider a scenario. Think that we are going to design a view with very simple SELECT statement:

We execute above view’s definition and our view is created. Wow! It works perfectly fine. We come to the office day after tomorrow and try to run our front-end application, which is using this view in one of the SELECT statements in order to pull the data out.

Oops! We get a run-time error saying – 

Invalid object name Person.Contact.

Could not use view or function ‘vw_GetEmployeeList’ becuase of binding errors.

We are surprised as we know that we designed this view and it was absolutely working fine. So, what went wrong now? Read more…



Before we discuss the WITH ENCRYPTION option with CREATE VIEW or ALTER VIEW statement, we need to understand the need for it.

Let’s take a scenario up where the database development team has completed development of Tables, Views, Stored Procedures, UDFs, etc. and ready to deploy it to the production environment along with the front-end application. The dev team runs the DB script in production environment and entire production database is ready and up.

But, what if someone, who is having access to the production server, logs in to SQL Server Management Studio, gets into desired database and right click you view and opens up the definition – that is, ALTER VIEW… window. He/she makes modification, which is incorrect and gives false data, and then executes that script. Now, this view is going to give us wrong information, just because someone has tampered with it. So, how to overcome this problem? What’s the remedy for it?

Here comes WITH ENCRYPTION option in CREATE or ALTER VIEW statement. Using WITH ENCRYPTION option, we can encrypt the definition of a view. Once encrypted, no body can retrieve the definition and see it, not even the person who created it or SQL Server Administrator himself. Once encrypted, it is encrypted for everyone and forever.

Here, I would like to mention about the sp_helptext system stored procedure. This is a system stored procedure, which gives us object’s definition passed as parameter to it. Even this stored procedure cannot pull the view’s definition out if it is encrypted. So, there is no way out to see view’s definition once we encrypt it.

Therefore, we need to keep the VIEW’s definition script file with us for use in future. If we lose that definition script, we’ll have to write entire view definition script all over again.

Hence, we need to be little cautious while using WITH ENCRYPTION option with the view’s definition.

What is AdventureWorks Sample Database?

Yeah! This is very common question among professionals, especially those are learning the SQL Server. And, I often come across one more question, which is relevant to AdventureWorks database – that is, Why do we have so many  different types of AdventureWorks sample databases such as AdventureWorks, AdventureWorks2008, AdventureWorksLT, etc.? Frankly speaking, I got no correct answer from anyone so far. Hence, I decided to write about it today.

So, the story starts since very beginning. Microsoft provided ‘pubs‘ sample database with SQL Server 6.5. This is very basic OLTP database in order to try SQL Server features and learn SQL Server. This smaple database remains there until now. We can run the pubs database script and restore it on SQL Server 2008 as well. In fact, I have got it on my system. But, there were a lot of shortcomings in this database as it was not designed properly. When SQL Server 2000 was released, it came up with new sample database, named Northwind. I’m sure a lot of professionals, especially trainers, do use this sample database till date.

The Northwind was again a sample OLTP database but with little design issues. Still, it was great for learning and trying out SQL Server new features. We can restore this database on SQL Server 2008 as well.                                                                               Read more…

Types of Operators in SQL Server 2008

Here, I’m going to discuss types of operators that SQL Server 2008 uses. But, before I go ahead I would like to mention what an Operator is.

An operator is nothing but a sign or symbol that specifies an action on one or more expressions called operands.

We can organize SQL Server operators into 8 major categories:

1. Arithmetic Operators: Perform mathematical operations on one or more than one expression of numetic data type. Following are the arithmetic operators offerred by SQL Server 2008:

  • (+) Add: Addition
  • (-) Subtract: Subtraction
  • (*) Multiply: Multiplication
  • (/) Divide: Division
  • (%) Modulo: Remainder of a division

2. Assignment Operators: (=) Equal sign is the assignment operator that SQL Serve 2008 supports. It is used not only to perform equality but also to give an alias name to a column.                                                                                                                                             Read more…

Miscellaneous Data Types in SQL Server 2008

Here, I’m going to discuss miscellaneous data types in SQL Server 2008. We can organize the miscellaneous data types in the following 7 types:

Cursor: Used to create a recordset by SQL Server, which is primarily used as OUTPUT parameter to a stored procedure.

HierarchyID: It is system data type and variable-length. It is primarily used to represent position in a hierarchy. This is very useful data type when pulling the data out for hierarchical analysis.

Sql_Variant: This stores values of various SQL Server supported data types. This data type has been introduced to cater advanced and complex database design issues.

Table: It is used to stored a result set or rows and manipulate later on. This has been introduced to counter cursor data type, which, in most cases, degrades performance.

Timestamp: Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime2 data type.

Uniqueidentifier: It is a 16-byte GUID. There are two ways to initialize a variable of this data type. It is always unique across SQL Server instance.

XML: It stores XML type of data.



Character, Unicode Character and Binary Strings Data Type in SQL Server 2008

Character Strings: We can organize Character Strings data type into 3 categories in SQL Server:

  • Char: Contains fixed-length, non-unicode data. It accepts a parameter stating how many characters can be saved. And, that decides how many bytes it is going to consume. It can have maximum 8000 characters. It means we can define a variable with maximum 8000 characters with this data type.
  • Varchar: Cotains variable-length, non-unicode data. It accepts a parameter stating how many characters can be saved. It can contain characters from 1 through 8000. However, we can specify max as well. In that case, the maximum storage capacity is 2^31 – 1 bytes. The storage size is actual length of data entered + 2 more bytes.
  • Text: Variable-length, non-unicode data with a maximum length of 2^31-1 characters.

Read more…