SQL Server 2008

Microsoft SQL Server 2008 is at the heart of a comprehensive data platform that enables you to access and manipulate business-critical data from a variety of diverse devices, platforms, and data services across the enterprise.

Top New Features

Map data structures to business entities by using the new ADO.NET Entity Framework

Use consistent syntax to query diverse data through .NET Language Integrated Query (LINQ) extensions to Microsoft Visual C# and Microsoft Visual Basic.NET

Create occasionally connected solutions using SQL Server 2008 Compact Edition and Microsoft Synchronization Services

Consolidate data storage through the SQL Server 2008 support for relational, XML, Filestream, and geo-location-based data

Get your hands on
http://www.microsoft.com/sqlserver/2008/en/us/app-dev.aspx

What is a join and explain different types of joins

Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.
OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

Postback from Javascript

Postback is a concept introduced in ASP.NET and is a very handy method. Postback is built into the ASP.NET and most of the web controls support it without writing any code.

There may be some scenario where you may want to explicitly postback to the server using some clientside javascript. It is pretty simple to do this.

ASP.NET already creates a client side javascript method as shown below to support Postbacks for the web controls:

Reference: Calling postback event from Javascript

SQL Query Basics

i have read one good blog which explains basics of query process. Here is the order in which query operators are evaluated. There are 11 levels.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH{CUBE | ROLLUP}
7. HAVING
8. SELECT
9. DISTINCT
10. HAVING
11. TOP

First the query processor reads all the rows from the FROM the left table and apply the ON condition with the right table provided in JOIN.

If there are more JOINs the same is done for all the JOINs.

Then the WHERE clause is applied to filter rows.

Then GROUP BY is done

There WITH clause get evaluated followed by HAVING.

Then the columns are selected. (This is the reason why you cannot use a column alias in WHERE or GROUP BY)

Then DISTINCT clause applied

Then ORDER BY is processed…(This is the reason why you CAN use a column alias in the ORDER BY clause)

Then TOP clause get evaluated.

Reference:
https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-IN&EventID=1032345689&CountryCode=IN

ON UPDATE CASCADE

ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables.

ON DELETE CASCADE

ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.

What is a concatenated primary key

Each table has one and only one primary key, which can consist of one or many columns. A concatenated primary key comprises two or more columns. In a single table, you might find several columns, or groups of columns, that might serve as a primary key and are called candidate keys. A table can have more than one candidate key, but only one candidate key can become the primary key for that table

differnce between @@sqlstatus and @@fetch_status

The @@sqlstatus global variable holds status information (warning exceptions) resulting from the execution of a fetch statement. Its value reflects the last cursor fetched.

The @@fetch_status global variable provides information about whether fetch is executed successfully in a scrollable cursor.

Delete two tables data same at a time

How to delete the data from two tables at the same time if they have common column?

There is NO direct method to do this. If the tables are having Parent-Child relationship then you need to delete the Child table first and delete the parent table.

If not you can define Trigger on Parent table and inside the trigger body write your code to delete the data from child table.

Or You can use CASCADE DELETE option at the KEY level to do this.

What is Referential Integrity

Building and maintaining logical relationships between tables are fundamental parts of working with relational databases. Most databases must maintain certain relationships or the data will be logically corrupt. When such relationships exist, we say that the data has referential integrity. One table is the referenced table and the other is the referencing table; values in the referencing table must match values in the referenced table. (Some people call these tables parent and child tables, but that terminology implies a hierarchy, which the relational model avoids.) SQL Server can automatically enforce referential integrity through foreign key constraints that you define. This feature is called Declarative Referential Integrity (DRI) because it's part of the table definition. You can also use other features, such as triggers, to enforce relationships; this use is procedural referential integrity.

Count No Of Records in a Table

What is the fastest way to check whether Table has any records or not???

Most of the people use count(*) or count(1) or TOP keywords to answer the above question. But you know what if you use above methods, it will take long time if the table has millions of records.

Instead what you can do is, use the below query to see get the answer.

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('EMP') AND indid < 2

Add Column between two other columns

At least there will be one question in my mail box about how to add a column in between two existing columns in a table???

There is no direct query/script to achieve this becoz in general, there is no significance for the order of the columns in the table.

If you really need this then you can try the below apporach

Lets assume that you want to add ENAME column to the EMP table in between EMP_ID and SALARY.

1. Add the the new column to the existing table say EMP
now the order will be EMP_ID,SALARY,ENAME

2. Create a new table EMP_NEW with the desired table structure and column order

3. Insert values from your existing EMP to this EMP_NEW

4. Drop the existing table EMP and rename EMP_NEW to EMP.


You can do the above steps only when no one is performing any DML operations on the table to avoid any missing rows.

HAVING clause

The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

Happy New Year........2009

This is my first post in this year....at the very start of my day...
I am very much with my over-all performance in 2008 where i have got somany awards and prizes from various technology forums....
I am looking forward to get some more in 2009 as well.......



The biggest thing now is..i am planning to write a Book on SQL...and as i am new comer in this area lookng for some one to guide me on this....



wish me all the best....

Keep watching....