Types of Functions in SQL Server 2008

Today, I’m going to talk about types of functions in the SQL Server 2008 over here. At first, I would like to mention that we may categorize functions into various different types of categories based upon parameters, which we are considering for categorization. However, in general we can categorize SQL Server functions into two broad categories:

1. Built-In Functions: As it says, these functions are ready-made. SQL Server has offerred us set of functions, which we can just call and make use of it. However, we can further sub-categorize these functions into four sub-categories:

• Aggregate Functions: I’m sure everyone is aware of this category. These fucntions accept group of values and return a single, summarized value. We usually use these functions either into SELECT or GROUP BY clause. Example, Avg, Min, Max, Count, etc.
• Ranking Functions: This category is unique one as it gives a row number to a row in a group or partition of rows. These functions are very useful and handy when we are doing some sort of statistical analysis with data. Example, RANK, DENSE_RANK, NTILE and ROW_NUMBER.
• Rowset Functions: Return an object that we can use in place of a table reference i.e WHERE clause or in a subquery. Example, FREETEXTTABLE, OPENDATASOURCE, OPENQUERY, OPENXML, etc.
• Scalar Functions:The word ‘Scalar’ says it all. It return a single value and, of course, it operates on a single value only. Here, we can again sub-categorize the Scalar functions into ten subcategories:
  • String Functions: Operate on char or varchar input types and return a single value of either string or numetic data type.
  • Mathematical Functions: Operate on numetic data type input values and return a numeric value.
  • Data & Time Functions: Operate on date and time data type and return either string or numetic or date and time data type value.
  • System Functions: Though, these are built-in function but we may keep them over here based upon their return type. Return values, objects or setting in an instance of SQL Server.
  • Cursor Functions: Return information about a cursor.
  • Security Functions: Return information about roles or users in SQL Server.
  • System Statistical Functions: Return statistical information about the system. Example, @@CPU_BUSY, @@IDLE, etc.
  • Text & Image Functions: These functions would be dropped off in coming version of SQL Server. Operate on text and image data type and return information about value.
  • Metadata Functions: Return information about database and its objects.
  • Configuration Functions: Returns information about the current configuration of an instance of SQL Server.

2. User-Defined Functions: SQL Server offers a feature where users can create their own functions and make use of it inside T-SQL statements. There are three types of user-defined functions:

• Scalar Functions: Here again, we’ve got scalar category but user-defined scalar functions, and not built-in ones. It returns a single value and, of course, it operates upon a single input value.
• Inline Table-Valued Functions: Return a set of rows as TABLE data type. It can have only single SELECT statement. A user doesn’t have liberty to define the table structure. Whatever SELECT clause returns, returned table takes that structure.
• Multistatement Table-Valued Functions: Returns a set of rows as TABLE data type. Unlike Inline Table-Valued function, it can have multiple SELECT statements and user can define the structure of the table being returned.

We may categorize functions as Deterministic and Non-Deterministic as well but, as I mention in the beginning, it requires different set of parameters for categorization. I’ll definitely discuss about Deterministic and Non-Deterministic types but in a separate thread, in another blog. So, follow my blogs to catch it…

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.

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.                                                                                                                                            

3. Bitwise Operators: Perform bit manipulations between two expressions of integer data type. There are threee bitwise operators in SQL Server 2008:

• (&) Bitwise AND:
• (|) Bitwise OR
• (^) Bitwise Exclusive OR

4. Comparison Operators: Checks whether two expressions are the same or not. Always returns True or False. Following are the comparison operators supported by SQL Server 2008:

• = (Equals to)
• > (Greater than)
• < (Less than)
• >= (Greater than or equal to)
• <= (Less than or equal to)
• <>, != (Not equal to)
• !< (Not less than)
• !> (Not greater than)

5. Logical Operators: Checks for truth of some condition. Return either True or False or Unknown value. Following is the list of logical operators:

• ALL
• AND
• ANY
• BETWEEN
• EXISTS
• IN
• LIKE
• NOT
• OR
• SOME

6. Scope Resolution Operators: Scope resolution operator :: provides access to static members of a compound data type. I know you wish to know about compound data type now. Will write about it later on.

7. String Concatenation Operators: The plus sign (+) is the string concatenation operator used to concatenate two or more strings of String data type i.e. char, varchar, etc.

8. Unary Operators: Unary operators perform an operation on only one expression of any one of the data types of the numeric data type category. Follwoing is the list of unary operators in SQL Server 2008:

• (+) Positive
• (-) Negative
• (~) Bitwise NOT

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.                                                                                                                                      
6. AFTER triggers cannot be associated with a view. However, we may associate INSTEAD OF trigger with a view.
7. Rules and Defaults cannot be associated with a view.
8. Full-Text index cannot be defined on a view.
9. A view can have maximum of 1,024 column in its SELECT list.
10. A view can have maximum of 32 levels of view nesting i.e. called nested views. 
11. All columns names must be explicitly specified when using WITH SCHEMABINDING option with a view’s definition. Even, when we are trying to pull all the columns out, we can’t use (*) sign. Instead, we need to write all column names explicitly.

This is all what I can compile that my memory could recall so far. If you have something to add/remove, which I missed out over here, please do put it in Leave a comment/Leave a Reply section below. I would appreciate your valuable contribution to the community.

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 surprised 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

 (ii) Database Level. 

Identifiers can be logically categorized into two categories:

 (i)  Regular Identifier

(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.

How to Prevent Conflict j query?

  <script type="text/javascript">
        var $j = jQuery.noConflict();
        $j(function () { $j(".slideshow").cycle({ fx: "fadeout", timeout: 4000 }); $j('#slider').slider({}); });
     </script>

New Feature in 2013 VS

1:Create a new Azure website from within VS
2:Open the Resolve menu when typing
3:Live debugging in Azure
4:Live tracing from Azure
5:64 bit Edit and Continue
6:Return value inspection
7:Just my code
8:Peek a definition