DELETE BY JOINING TABLES

Join in DELETE CLAUSE :  

There are situation where we need to delete records based on one or other condition and that in turn depends on joining of tables.

We start wondering whether DELETE statement hold joins??

Yes you can use join with delete statement, let’s have a small practical.

I am having two table ‘PARENT_TAB’ and ‘CHILD_TAB’ where ‘first_id’ is the Primary key in PARENT_TAB and same is the Foreign Key in CHILD_TAB.

The data in the table are as follows

select * from parent_tab

first_id    name

----------- --------------------------------

2           nimesh

3           pratik

(2 row(s) affected)

select * from child_tab

second_id   first_id    dept

----------- ----------- ----------------

3           2           DEVLOPMENT

5           2           DESIGNING

(2 row(s) affected)

Q Now the question is how to delete record using join in delete statement from CHILD_TAB where the candidate name is ‘nimesh’.

delete parent_tab

from parent_tab p, child_tab c

where p.first_id=c.first_id and p.name='nimesh'

It is simple as writing select statement, add FROM clause give the table name you wanna join with alias and then in WHERE clause join the table and put the condition.

After executing above DELETE statement, execute the following statement

select * from child_tab

second_id   first_id    dept

----------- ----------- --------------------------------------------------

(0 row(s) affected)

Since both the record belongs to candidate ‘nimesh’ both get deleted.

 Post Reference: Vikram Aristocratic Elfin Share

CASCADE IT (UPDATE / DELETE)

Many a time we need to clean up all the table data. For this we have first traverse through each child table and delete data gradually from bottom to top.

This is time consuming, instead if we alter table to add ON DELETE CASCADE clause to the reference key field then by deleting master table record the corresponding child record will get deleted.

ALTER TABLE [dbo].[CHILD_TAB] ADD  CONSTRAINT [FK_CHILD_TAB_PARENT_TAB] FOREIGN KEY([first_id])

REFERENCES [dbo].[PARENT_TAB] ([first_id])

ON UPDATE CASCADE

ON DELETE CASCADE

Same way if we want modifying master record modify the related child record then we can alter table to have ON UPDATE CLAUSE to reference key field.

EXAMPLE: Here in this example code we are having Child table as ‘CHILD_TAB’ and Parent table as ‘PARENT_TAB’ and we are making first_id of ‘CHILD_TAB’  as foreign key field to the Primary Key field first_id of ‘PARENT_TAB’ with ON UPDATE and ON DELETE CASCADE.

update parent_tab

set first_id = 1 where first_id=5

Now when we update first_id of ‘PARENT_TAB’ automatically all the child record will get updated.

delete parent_tab where first_id=1

Similirly when we delete ‘PARENT_TAB’ where first_id=1, it automatically delete all record in child table ‘CHILD_TAB’ with first_id=1

Post Reference: Vikram Aristocratic Elfin Share

Function Returning Table in SQL Server

Give the return type as Table in Function signature then you can return the result of any select statement as a table to calling function.

Example: Here we create a function say 'fun_returning_tab' which is taking a parameter @pnr_no and returning table to the calling function.

Create function fun_returning_tab( @pnr_no varchar(7)) return table
as
Begin
return (select * from reservation where pnr_no = @pnr_no)
End

How to call this function:
select * from   fun_returning_tab(8161237)

enjoy coding...:)

Post Reference: Vikram Aristocratic Elfin Share

Thinking about using rownum while using it in TOP N Query with ORDER BY Clause: BEAWARE Series-II

First :

SELECT ename, sal FROM
(SELECT ename, sal FROM emp ORDER BY sal DESC)
WHERE rownum <=3;


ENAME SAL
---------- ---------
KING 5000
SCOTT 3000
FORD 3000

3 rows selected

 Second:

SELECT ename, sal FROM emp
WHERE rownum <=3
ORDER BY sal DESC




ENAME SAL
---------- ----------------------
ALLEN 1600
WARD 1250
SMITH 800

The first will return desired result however, second does not give us the result we want
The reason is described below:

Because Oracle assigns the ROWNUM values to the rows before it does the sort.
In this example, Oracle will retrieve three rows from the table, any three rows, and sort only these three rows. We really need Oracle to sort all the rows and then return the first three. The inline view will ensure that this will happen


Post Reference: Vikram Aristocratic Elfin Share

The Help21X-Men: First Class (+Digital Copy) [Blu-ray]A Time to Heal (Quilts of Lancaster County)

Thinking about using rownum in where clause: BEAWARE Series-I

First :

SELECT rnum, table_name FROM
(SELECT rownum rnum, table_name FROM user_tables)
WHERE rnum > 2;

Second:

SELECT table_name FROM user_tables
WHERE rownum > 2;

The first will return result whereas the second will zero rows.


The reason is described below:
However, this query will always return zero rows, regardless of the number of rows in the table.

To explain this behavior, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.
The bottom line is that conditions such as the following will work as expected.
WHERE rownum = 1;
WHERE rownum <= 10;
WHERE rownum <=3;
While queries with these conditions will always return zero rows.

WHERE rownum = 2;
WHERE rownum > 10;



Post Reference: Vikram Aristocratic Elfin Share

The simplest way to export data from dataset to Excel

Step 1. Create a dynamic datagrid.
Step 2: Set the datasource of datagrid to dataset.
Step 3: Render the datagrid to Excel sheet.

This Button click event will establish connection with oracle and fill the dataset with the data from the acc_system_moniter table.

protected void btnExport_Click(object sender, EventArgs e)
{
OracleConnection con = new OracleConnection();
string strCon = "Data Source=CONF_INS;
User Id=INS;Password=INS555;Integrated Security=no;";
con.ConnectionString = strCon;

OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "select * from acc_system_monitor";
cmd.CommandType = CommandType.Text;

OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
vikramWB(ds);
}


This function will create a dynamic dataGrid and bind the datagrid with the datset which was passed as an argument to this function. Then it render the dataGrid to the HtmlTextWriter

public static void vikramWB(DataSet ds)
{
System.Web.UI.WebControls.DataGrid grid =
new System.Web.UI.WebControls.DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.DataSource = ds;
grid.DataMember = ds.Tables[0].TableName;

grid.DataBind();

//StreamWriter will write the excel file
using (StreamWriter sw = new StreamWriter("c:\\test.xls"))
{
//HtmlTextWriter constructer will
//take StringWriter as an argument.
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
//The Grid RenderControl will render
// the grid to the HtmlTextWriter
grid.RenderControl(hw);
}
}
}

Post Reference: Vikram Aristocratic Elfin Share

A tribute to the bosses!!!

Chai k Liye jaise Toast hota hai,
Waise har ek BOSS zaruri hota hai.

Koi friday evening review par bulaye
Koi saturday ko office bulaye

Ek teri idea ko apna bataye,
Aur Ek tera target har month badhaye
Koi nature se gentle,
koi bura hota hai,
Par har ek boss zaruri hota hai.

Ek ghadi ghadi review kare par kabhi kabhi advice de
Ek kabhi kabhi review kare aur ghadi ghadi advice de

Koi Gyan ka ghoomta phirta satellite,
Koi din raat rakhe team ko tight;
Koi welcomed hai, koi forced hota hai
Par har ek boss zaruri hota hai

Koi bossy boss,
koi friendly boss
Koi Data crazy excel boss.
Moody boss, koi gloomy boss
Early morning office aane wala Boss,
Koi late night jaane wala Boss

Koi promote na kare aur appraisal me tarsaye
Koi good suggestion ko bhi thukhraye
Koi best friend aur, koi aloof hota hai
Par har ek boss Zaruri hota hai !!

Post Reference: Vikram Aristocratic Elfin Share

Lock up your transaction...

Lock types

There are three main types of locks that SQL Server 7.0/2000 uses:

·  Shared locks

·  Update locks

·  Exclusive locks

Shared locks are used for operations that do not change or update data, such as a SELECT statement.

Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.

Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

Shared locks are compatible with other Shared locks or Update locks.

Update locks are compatible with Shared locks only.

Exclusive locks are not compatible with other lock types.

Let me to describe it on the real example. There are four processes, which attempt to lock the same page of the same table. These processes start one after another, so Process1 is the first process, Process2 is the second process and so on.

Process1 : SELECT
Process2 : SELECT
Process3 : UPDATE
Process4 : SELECT

Process1 sets the Shared lock on the page, because there are no another locks on this page.
Process2 sets the Shared lock on the page, because Shared locks are compatible with other Shared locks.
Process3 wants to modify data and wants to set Exclusive lock, but it cannot make it before Process1 and Process2 will be finished, because Exclusive lock is not compatible with other lock types. So, Process3 sets Update lock.
Process4 cannot set Shared lock on the page before Process3 will be finished. So, there is no Lock starvation. Lock starvation occurs when read transactions can monopolize a table or page, forcing a write transaction to wait indefinitely. So, Process4 waits before Process3 will be finished.
After Process1 and Process2 were finished, Process3 transfer Update lock into Exclusive lock to modify data. After Process3 was finished, Process4 sets the Shared lock on the page to select data.

Fun with CURSOR, print all your SP

Use cursor to print all stored procedure in your database as a text. Use the code below to get all your SP in current database.

USE MyDatabase
GO
DECLARE @proc_Name VARCHAR(100)

DECLARE @mySP_cursor CURSOR FOR
SELECT s.name FROM sysobjects s WHERE type = 'P'

OPEN @mySP_cursor 
FETCH NEXT FROM @mySP_cursor  INTO @proc_Name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_HelpText @procName
    FETCH NEXT FROM @mySP_cursor  INTO @proc_Name
END
CLOSE @mySP_cursor 
DEALLOCATE @mySP_cursor 
GO 

Happy coding... :)

Post Reference: Vikram Aristocratic Elfin Share 

Surrogate Key, Candidate Key, Primary Key, Alternate Key and Composite Key

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

A key formed by combining at least two or more columns is called composite key.

Surrogate Key : Artificial key generated internally that has no real meaning outside the Db (e.g. a Unique Identifier or Int with Identity property set etc.). Implemented in SQL Sevrver by Primary Key Constraints on a column(s).

Post Reference: Vikram Aristocratic Elfin Share

Update Employee Table with MERGE

Once very good friend of mine asked me how to update an employee master table having fields
emp_id,
emp_name,
department_id,  (refer to dept_id of Department_Master table) 
department_name

where department_name fields is null and he want to update department_name with the correspondent department_id.

The solution is very simple i told him to do it by cursor or using temporary table and loop through the temp table.

But today when i was toying with MERGE i found one more solution to same question, i have yet not executed it but i m pretty confident it will work and it is the best solution to the above question

MERGE Employee_Master AS ed
USING (SELECT dept_id,dept_name FROM Department_Master) AS dm
ON dm.dept_id = em.dept_id
WHEN MATCHED THEN UPDATE SET em.dept_name = dm.dept_name;

Just try it n see the result.
Happy coding :) 

Post Reference: Vikram Aristocratic Elfin Share

Fetch Excel Records using ADO.Net

This code snap will fetch each record from your excel sheet 1 located at D:\ drive and return records as datatable.

public static  DataTable  GetItemsFromExcel1()
{
    DataTable dt = new DataTable();
    oleDbConnection excelConnection =
                        new  OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;"
                    + @"Data Source=D:\YourFile.xls;"
                    + @"Extended Properties=""Excel
                        8.0;HDR=Yes;IMEX=1;""");

    excelConnection.Open();
    try
    {
       OleDbDataAdapter dbAdapter =
       new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelConnection);
              
       dbAdapter.Fill(dt);
    }
    finally
    {
       excelConnection.Close();
    }

    return dt;
}


Post Reference: Vikram Aristocratic Elfin Share

Stuff function in SQL SERVER

The STUFF function inserts a set of characters into a given string at a given position

STUFF(character_expression1, start, length, character_expression2)

Character_Expression1 represents the string in which the stuff is to be applied. start indicates the starting position of the character in character_expression1, length is the length of characters which need to be replaced. character_expression2 is the string that will be replaced to the start position

1) SELECT STUFF('Fun', 1, 0, '*')
    the output will be : *Fun.

2) SELECT STUFF('SQL SERVER is USEFUL',5,6,'DATABASE')
    the output will be : SQL DATABASE is USEFUL



Post Reference: Vikram Aristocratic Elfin Share

How to use ORDER BY when Defining a View?

If you use Order By clause while creating SQL View, SQL Server throw you such kind of error

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Sloution: Use Top with Percent keyword while creating view. For example-

Create View  

As (Select Top 100 Percent col1, col2, col3 from Table11 order by col1,col2,col3)

Post Reference: Vikram Aristocratic Elfin Share

Can We Insert Data into a View?

We can not insert data into view instead we can insert data into the underline table of view. SQL Server will

allow you to insert data into the underlying table through a view with a condition:

*The insert columns must be limited to columns of a single underlying table:- This means if a view is composed of 2 or more tables then insert should be made to column of one table at time.

For example

CREATE VIEW vTest AS
    SELECT      tb1.tab1_id, tb2.tab2_id, tb1.value tab1_value, tb2.value
    tab2_value
    FROM        table1 tb1
    INNER JOIN  table2 tb2
    Where tb1.tab1_id=tb2.tab1_id


This is a view "vTest" made from two table table1 and table2 inner joined on
tab1_id field

Now if you insert data from view to underlying table of table1 and Table2 you
need to do this in this way:

INSERT INTO vTest (tab1_id, tab1_value) VALUES (3, 30);
INSERT INTO vTest (tab2_id, tab1_id, tab2_value) VALUES (1,3, 30); 

Post Reference: Vikram Aristocratic Elfin Share

Get Ping with every DML statement on your table.....TRIGGERS

A trigger is a database object that is Bind to a table. In many aspects it is similar to a stored procedure and are often referred to as a "special kind of stored procedure."  

Modifications to the table are made using INSERT,UPDATE,OR DELETE statements.Triggers are used to enforce data integrity and business rules such as automatically updating summary data. It allows to perform cascading delete or update operations. If constraints exist on the trigger table,they are checked prior to the trigger execution. If constraints are violated statement will not be executed and trigger will not run.Triggers are associated with tables and they are automatic . Triggers are automatically invoked by SQL SERVER. Triggers prevent incorrect , unauthorized,or inconsistent changes to data.
 
 SQL Server  has many types of triggers:

1. After Trigger
2. Multiple After Triggers
3. Instead Of Triggers
4. Mixing Triggers Type

Triggers that run after an update, insert, or delete can be used in several ways:

• Triggers can update, insert, or delete data in the same or other tables. This is useful to maintain relationships between data or to keep audit trail information.
• Triggers can check data against values of data in the rest of the table or in other tables. This is useful when you cannot use RI constraints or check constraints because of references to data from other rows from this or other tables.
• Triggers can use user-defined functions to activate non-database operations. This is useful, for example, for issuing alerts or updating information outside the database.
• Note: An AFTER trigger can be created only on tables, not on views.

Exercising with After Triggers

1)Working with INSERT Triggers  

CREATE TRIGGER invoiceUpdate ON [Orders]
FOR INSERT
AS
Begin
UPDATE p SET p.instock=[p.instock – i.qty]
FROM products p JOIN inserted I ON p.prodid = i.prodid
End
 
You created an INSERT trigger that referenced the logical inserted table. Whenever you insert a new  record in the orders table now, the corresponding record in the products table will be updated to subtract the quantity of the order from the quantity on hand in the instack coloumn of the products table.

2)Working with DELETE Triggers
DELETE triggers are used for restricting the data that your users can remove from a database. For example

CREATE TRIGGER VikramDelete ON [Customers]
FOR DELETE
AS
IF (SELECT name FROM deleted) = ‘Vikram’
BEGIN
PRINT ‘Danger...Can not remove customers with the name vikram’
PRINT ‘Transaction has been canceled’
ROOLBACK
END

DELETE trigger used the logical deleted table to make certain that you were not trying to delete a customer with a great name “Vikram” – if you did try to delete such a customer, you would be met with Anaconda in the form of an error message (which was generated by the PRINT statement that you entered in the trigger code).


3)Working with UPDATE Triggers
UPDATE triggers are used to restrict UPDATE statement issued by your users, or to back your previous data.

CREATE TRIGGER CheckStock ON [Products]
FOR UPDATE
AS
IF (SELECT InStock FROM inserted) < 0
BEGIN
PRINT ‘Cannot oversell Products’
PRINT ‘Transaction has been cancelled’
ROLLBACK
END
You created an UPDATE trigger that references the inserted table to verify that you are not trying to insert a value that is less than zero. You need to check only the inserted table because SQL Server performs any necessary mathematical functions before inserting your data.
 
Since I am running out of time, in next post i will try to explain INSTEAD OF TRIGGER.


Post Reference: Vikram Aristocratic Elfin Share 
BlueTangledKindle Wireless Reading Device, Wi-Fi, Graphite, 6" Display with New E Ink Pearl Technology 

How SQL Azure different from my SQL Server ??.. SQL Azure vs SQL Server

 Similarities and Differences

Similar to an instance of SQL Server on your premises, SQL Azure exposes a tabular data stream (TDS) interface for Transact-SQL-based database access. This allows your database applications to use SQL Azure in the same way that they use SQL Server. Since SQL Azure is a service, administration in SQL Azure is slightly different.
Unlike administration for an on-premise instance of SQL Server, SQL Azure abstracts the logical administration from the physical administration; you continue to administer databases, logins, users, and roles, but Microsoft administers and configures the physical hardware such as hard drives, servers, and storage. This approach helps SQL Azure provide a large-scale multi-tenant database service that offers enterprise-class availability, scalability, security, and self-healing.
Since Microsoft handles all of the physical administration, there are some differences between SQL Azure and an on-premise instance of SQL Server in terms of administration, provisioning, Transact-SQL support, programming model, and features. For more information, see Guidelines and Limitations (SQL Azure Database).

 

Logical Administration vs. Physical Administration

Although SQL Azure plays an active role in managing the physical resources of the database, the DBA plays a very important role in administering SQL Azure-based database applications. Using SQL Azure, DBAs manage schema creation, statistics management, index tuning, query optimization, and security administration (logins, users, roles, etc.). For more information about security administration in SQL Azure, see Managing Logins and Users in SQL Azure.
Database administration in SQL Azure differs most from SQL Server in terms of physical administration. SQL Azure automatically replicates all data to provide high availability. SQL Azure also manages load balancing and, in case of a server failure, transparent fail-over to a healthy machine hosting one of the backup copies of your database.
To provide this level of physical administration, you cannot control the physical resources of SQL Azure. For example, you cannot specify the physical hard drive or file group where a database or index will reside. Because the computer file system is not accessible and all data is automatically replicated, SQL Server backup and restore commands are not applicable to SQL Azure. The SQL Azure service still backs up all databases; however they are not accessible to regular users. This is a feature that may be offered in future.


Starting with SQL Azure Service Update 4, SQL Azure allows you to back up your database by copying it to a new database in SQL Azure. For more information, see Copying Databases in SQL Azure.
For more information on the available options to transfer data to SQL Azure, see Migrating Databases to SQL Azure.

 

Provisioning

When preparing an on-premises SQL Server deployment, it may be the role of the DBA or IT department to prepare and configure the required hardware and software. When using SQL Azure, these tasks are performed by the SQL Azure provisioning process.
You can begin provisioning your SQL Azure databases after you create a Windows Azure Platform account. This account allows you to access all the services, such as Windows Azure, AppFabric, and SQL Azure, and is used to set up and manage your subscriptions.
Each SQL Azure subscription is bound to one SQL Azure server within one of the Microsoft data centers. Your SQL Azure server is an abstraction that defines a grouping of databases. To enable load-balancing and high availability, databases associated with your SQL Azure server may reside on separate physical computers within the Microsoft data center.

For more information about provisioning, see SQL Azure Provisioning Model.

 

 

Transact-SQL Support

Transact-SQL is a language that contains commands used to administer instances of SQL Server including creating and managing all objects in an instance of SQL Server, and inserting, retrieving, modifying, and deleting all data in tables. Applications can communicate with an instance of SQL Server by sending Transact-SQL statements to the server. Microsoft SQL Azure Database supports a subset of Transact-SQL for SQL Server. For more information about Transact-SQL support, see Transact-SQL Support (SQL Azure Database).

 

Features and Types

SQL Azure does not support all of the features and data types found in SQL Server. Analysis Services, Replication, and Service Broker are not currently provided as services on the SQL Azure. You can connect from on-premises Analysis Server to SQL Azure and SQL Azure can be used either as a data source or destination. When this article is being updated, the Customer Technology Preview of SQL Azure Reporting is also available. Microsoft SQL Azure Reporting  is a cloud-based reporting service built on SQL Azure, SQL Server, and SQL Server Reporting Services technologies. You can publish, view, and manage reports that display data from SQL Azure data sources. 
Because SQL Azure performs the physical administration, any statements and options that attempt to directly manipulate physical resources will be blocked, such as Resource Governor, file group references, and some physical server DDL statements. It is also not possible to set server options and SQL trace flags or use the SQL Server Profiler or the Database Tuning Advisor utilities.

 

Key Benefits of the Service

The benefits of using SQL Azure include manageability, high availability, scalability, a familiar development model, and a relational data model.

 

Self-Managing

SQL Azure offers the scale and functionality of an enterprise data center without the administrative overhead that is associated with on-premise instances of SQL Server. This self-managing capability enables organizations to provision data services for applications throughout the enterprise without adding to the support burden of the central IT department or distracting technology-savvy employees from their core tasks in order to maintain a departmental database application.
With SQL Azure, you can provision your data storage in minutes. This reduces the initial costs of data services by enabling you to provision only what you need. When your needs change, you can easily extend your cloud-based data storage to meet those needs.

 

High Availability

SQL Azure is built on proven Windows Server and SQL Server technologies, and is flexible enough to cope with any variations in usage and load. The service replicates multiple redundant copies of your data to multiple physical servers to maintain data availability and business continuity. In the case of a hardware failure, SQL Azure provides automatic failover to ensure availability for your application.

 

Scalability

A key advantage of SQL Azure is the ease with which you can scale your solution. As data grows, databases need to either scale up or scale out. Scale up always has a ceiling whereas scale out has no virtual limits. A common scale out technique is data-partitioning. After partitioning your data, the service scales as your data grows. A pay-as-you-grow pricing model makes sure that you only pay for the storage that you use, so that you can also scale down the service when you do not need it.

 

Familiar Development Model

When developers create on-premise applications that use SQL Server, they use client libraries like ADO.NET, ODBC that use the tabular data stream (TDS) protocol to communicate between client and server. SQL Azure provides the same TDS interface as SQL Server so that you can use the same tools and libraries to build client applications for data that is stored in SQL Azure. For more about TDS, see Network Protocols and TDS Endpoints.

 

Relational Data Model

SQL Azure will seem very familiar to developers and administrators because data is stored in SQL Azure just like it is stored in SQL Server, by using using Transact-SQL. Conceptually similar to an on-premise instance of SQL Server, a SQL Azure server is logical group of databases that acts as an authorization boundary.
Within each SQL Azure server, you can create multiple databases that have tables, views, stored procedures, indices, and other familiar database objects. This data model makes good use of your existing relational database design and Transact-SQL programming skills, and simplifies the process of migrating existing on-premise database applications to SQL Azure. For more about Transact-SQL and its relationship to SQL Azure, see Transact-SQL Support (SQL Azure Database).
SQL Azure servers and databases are virtual objects that do not correspond to physical servers and databases. By insulating you from the physical implementation, SQL Azure enables you to spend time on your database design and adding additional value to the business.
The following table provides a high-level comparison between SQL Azure and SQL Server. For the most up-to-date and detailed information, see the following topics on MSDN:

• Accounts and Billing in SQL Azure
• Guidelines and Limitations in SQL Azure
• Tools and Utilities Support in SQL Azure
• Transact-SQL Reference Support in SQL Azure
• SQL Server Feature Limitations (SQL Azure Database)

Taken from : http://social.technet.microsoft.com/wiki/contents/articles/comparing-sql-server-with-sql-azure.aspx

Post Reference: Vikram Aristocratic Elfin Share 

BurlesqueKindle 3G Wireless Reading Device, Free 3G + Wi-Fi, 3G Works Globally, Graphite, 6" Display with New E Ink Pearl Technology 

What SQL Azure Database ??

Microsoft SQL Azure Database is a database service provided as part of the Windows Azure Platform. It is based on Microsoft SQL Server, and provides many of the same features. Unlike SQL Server, which is provided as a software package that you install and maintain, SQL Azure Database is provided as a service that is hosted on hardware in Microsoft datacenters. SQL Azure takes care of maintaining the hardware and software layer, freeing you to concentrate on database design.
Since SQL Azure maintains the hardware and software for your database, provisioning a new database is very straightforward and easy; you request a new database and Microsoft provisions one automatically for you. Data stored in SQL Azure Database is also highly available, as each database hosted in the Microsoft data center has three copies: one primary and two secondary replicas. All reads and writes go through the primary, and any changes are replicated to the secondary replicas asynchronously.

Kindle Wireless Reading Device, Wi-Fi, Graphite, 6" Display with New E Ink Pearl Technology

Post Reference: Vikram Aristocratic Elfin Share

When to TRUNCATE and when to DELETE ??

Difference between Truncate and Delete in SQL

TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.

You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE .

You cann't rollback data in TRUNCATE but in DELETE you can rollback data.TRUNCATE removes(delete) the record permanently.

A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.

TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.

Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.

TRUNCATE is faster than DELETE.

Post Reference: Vikram Aristocratic Elfin Share

How SQL Server evaluate Null Value in Check Condition

Understanding NULL value with check constraint

           

As I said earlier when the condition of check constraint evaluates to false, it will not allow inserting /updating record  in the table.

In the given below example I am creating a check constraint which is allowing only three word in the country field.

            Alter table MyCountry

Add check (country_name in (‘india’, ’usa’, ’uk ’))

Now I am trying to insert these three record using insert statement

           

            insert into MyCountry(country_name) values(‘india’)

            insert into MyCountry(country_name) values(NULL)

            insert into MyCountry(country_name) values(‘uss’)

Now what will happen, when I will try to execute these three insert statement:

a)      Only first insert statement insert record.

b)      First and second insert record.

c)      First and third insert record successfully.

The correct solution is (b).

The reason the second INSERT statement worked is that the check constraint did not equate to FALSE when the second INSERT statement was run. Because of this, the database engine inserted the record. So why did this happen? This happened because when NULL is used in a comparison operation it equates to UNKNOWN. Since UNKNOWN is not FALSE there is no violation of the check constraint. Therefore, you need to be careful when you write your check constraints where you want to reject values that contain NULLS. Another way to code the above constraint is

           

            Alter table MyCountry

Add check (country_name in (‘india’, ’usa’, ’uk ’) and country_name is not NULL)

Post Reference: Vikram Aristocratic Elfin Share

Predefined value for a column in SQL Server Table object

How to set predefined value for a column in SQL Server Table object

Check constraints contain an expression the database will evaluate when you modify or insert a row. You can use any of the operator (<, >, <=, >=, IN, Like, Between, AND, OR) to build your check constraint expression. Only when the expression in check constraint evaluates to true, you will be allowed to insert or update record in the table whereas if check constraint expression evaluate to false you will not allowed enter/update record in the table.

You can add check constraint for your column in 3 ways: column level, table level or using alter table statement.

1) Column level

            Create table ActivityMaster

            (

                        activity_id  int,

                        activity_name  check (activity_name in (‘cricket’, ’tt’, ’swimming ’))

            }

2)Table Level

            Create table ActivityMaster

            (

                        activity_id  int,

                        activity_name,

constraint  chkActivityName  check (activity_name in (‘cricket’, ’tt’, ’swimming ’))

            }

3)In Alter Statement

            Alter table ActivityMaster

Add check (activity_name in (‘cricket’, ’tt’, ’swimming ’))

Here in the above example we are having a table called ActivityMaster which contain two column activity_id and activity_name then we are adding check constraint with activity_name which says, the activity_name column cannot have other than  (‘cricket’, ’tt’, ’swimming ’).

Some more use of Check

We can have multiple use of check constraint say salary should be greater than 3000

            Alter table EmployeeSalary

Add check (emp_salary > 3000)

           

You can also use ‘AND’ and ‘OR’ operator with check constraint.

            Alter table EmployeeSalary

Add check (emp_salary  >=  3000 AND emp_salary  <= 90000 )

Two or more column in single Check

You can have multiple columns in a single check constraint like the one below

            Alter table EmployeeSalary

            Add check (emp_salary > 10000 and emp_post like ‘manager’)

                                

Post Reference: Vikram Aristocratic Elfin Share

What is Common Table Expression(CTE) in SQL Server?

There are often times when we may need to operate over a set of data that doesn't inherently exist within the system. For example say InvoiceDatabase where we may have tables like Order, OrderDtail, InvoiceMaster, InvoiceDetail but we may need to run report against aggregate data across these tables.

Typically, views are used to break down complex queries into desired set of data but again views are parmanent database objects with will occupy the database space. Then you are left with #temp table option, here also you need to write the whole create table then you need to insert record to the temporary table...

The CTE is a form of the derived table that can be declared once and referenced multiple times in a query.

A Simple Common Table Expression Example
  TempCTE(StreamName,IsActive)as(

select top 5 sm.StreamName, ssm.SubStreamName from StreamMaster sm, SubStreamMaster ssmwhere sm.StreamId=ssm.StreamId )

select  * from Temp CTE

StreamName  SubStreamName
12th Science  B
12th Science  A
12th Science  AB
12th General  General
12th General  Home Science


Common Table Expression Syntax
A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

NOTE: The query using the CTE must be the first query appearing after the CTE. That you can not do like the below:

with abc(StreamName,IsActive)as(

select top 5 sm.StreamName, ssm.SubStreamName from StreamMaster sm, SubStreamMaster ssmwhere sm.StreamId=ssm.StreamId )select * from StreamMasterselect * from abc

Post Reference: Vikram Aristocratic Elfin Share
This query creates a CTE named  TempCTE that returns top 5 StreamName with there sub-stream name. Now you can query the TempCTE to get the resultant data.
with

Difference between varchar and nvarchar?

Storing Unicode Data

varchar: Variable-length, non-Unicode character data. varchar stores unicode data.
nvarchar: Variable-length Unicode character data. nvarchar stores unicode.

If you are using vchar datatype then each time you store data you first need the data to be converted into varchar then only you can store, this is not apply to nvarchar.

So if you are using nvarchar lot of your conversion task will be reduced. But nvarchar takes twice as much space as varchar, so before using this datatype considered this point.

With cheap memory nowadays I prefer nvarchar.
Post Reference: Vikram Aristocratic Elfin Share

Export Data form Sql Server to Excel Datasheet

Do The Following Step to Export Data from Sql Server to Excel

1)Create an Excel Datasheet save it with name testingExcel in D:/testingExcel.xls
2)Save Type of excel sheet should be Excel 97-2003 Worbook.
3)Then enable the 'ad hoc distributed queries' in surface area configuration using  sp_configure

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

4)Then with the help of openrowset function you can insert the data of sql server table to worksheet. Yet not finished you have to create column in excel sheet with the name StreamName and IsActive

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\testingExcel.xls;','SELECT StreamName,IsActive FROM [Sheet1$]') select StreamName,IsActive from StreamMaster
go


5)Be sure you have closed your excel sheet befor executing the above query.
6) Thats all you need to do to export data to excel sheet.

Post Reference: Vikram Aristocratic Elfin Share