TOP, WITH TIES and ORDER BY? What is WITH TIES clause in SQL Server?

WITH TIES can be used only when TOP and ORDER BY clauses are present in SELECT statement, both the clauses are required in order to use WITH TIES.

Let understand this by taking a small example: Here we are having a table parent_tab with following records:

select * from parent_tab

first_id    name

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

1           sneha

3           pratik

3           pratik

1           sneha

4           chitrangada

5           chitrangada

(6 row(s) affected)

Now I am firing a select query with Top clause to retrieve the topmost record from the table [parent_tab] where the name is equal to ‘chitrangada’.

select top 1  * from parent_tab where name like 'chitrangada'

first_id    name

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

4           chitrangada

(1 row(s) affected)

Here we can see the top most record has been arrived by the above query.

Now If I want to retrieve the top most record and all the records in the table where the name is equal to ‘chitrangada’?

If  such is my requirement then ‘WITH TIES’ will help you out, let see how:

select top 1 with ties  * from parent_tab where name like 'chitrangada' order by name

first_id    name

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

4           chitrangada

5           chitrangada

(2 row(s) affected)

What it does it when you use TOP 1 rows, it will return you only 1 rows, but when used TOP 1 WITH TIES, it will return you all the rows that have same value as that of the last record of TOP 1.

The expected result is based on the column that is specified in ORDER BY. That is it will look for the column used in the ORDER BY to compare its equivalent in rest of the table.

NOTE: WITH TIES Clause can be used only with TOP and ORDER BY, both the clauses are required.

Post Reference: Vikram Aristocratic Elfin Share

How to find Distinct record using GROUP BY?

How to find distinct records using group by? Then what is the difference between group by and Distinct?

Taking a scenario where we are having a table with duplicate records like the one mentioned below:

select first_id,name from vw_parent_tab

first_id    name

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

1           sneha

3           pratik

3           pratik

1           sneha

(4 row(s) affected)

Now if we want to find distinct record we can easily do it with the help of distinct clause

select distinct first_id,name from vw_parent_tab

first_id    name

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

1           sneha

3           pratik

(2 row(s) affected)

But the question is how to find the same distinct result using GROUP BY Clause; Answer is simple add a group by clause and put all the field you want to display using select query with the Group By.

select first_id,name from vw_parent_tab group by first_id,name

first_id    name

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

1           sneha

3           pratik

(2 row(s) affected)

If you are using a group by without any aggregate function then internally it will be treated as Distinct so in this case there is no difference between group by and Distinct...
But when you are provided with Distinct Clause better to use it for finding your unique records because objective of group by is to achieve aggregation not distinct.

Use of Group by:

select first_id,name,count(*) as 'duplicate_count' from vw_parent_tab group by first_id,name

first_id    name                       duplicate_count

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

3           pratik                     2

1           sneha                      2

(2 row(s) affected)

A hammer can work to drive in a screw sometimes, but if you have got a screwdriver handy, why bother?

Conclusion:  Use Distinct when you want distinct records whereas use group by when you want your records to be aggregated.

Post Reference: Vikram Aristocratic Elfin Share

How DATETIME2 differs with DATETIME?

If you are having a table with two different columns of datetime datatype as FirstModified and LastModified. And you have datatype for FirstModified as Datetime and LastModified as Datetime2.

create table testDemoForDateDatatype

(

firstName varchar(50),

firstModified datetime,

lastModified datetime2,

)

Now when you are populating each of them with SYSDATETIME, you assume that the value inserted in the table will be the same, you have done on insert operation on it, no update operation has yet done on the table.

insert into testDemoForDateDatatype values('abc', sysdatetime() ,sysdatetime ())

insert into testDemoForDateDatatype values('xyz', sysdatetime (),sysdatetime ())

insert into testDemoForDateDatatype values('def', sysdatetime (),sysdatetime ())

But when you querying the table with distinct clause on both these columns and you will be surprised by the result because your perception will be like both the column will have the same data, but in fact, they had very different data.

The date value in the DATETIME field gets rounded up whereas the value didn’t round up in the field having DATETIME2 datatype.

*The best way is to use GETDATE () if you are using DATETIME datatype.

*And SYSDATETIME () if you are using DATETIME2

 select getdate() -- 2011-12-28 11:23:39.727

select sysdatetime () --2011-12-28 11:23:39.9062500

NOTE: DATETIME2 and Sysdatetime() was introduced in SQL Server 2008. So try this in 2008 SQL Server.

Post Reference: Vikram Aristocratic Elfin Share

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