About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Sunday, June 24, 2012

Multiple Value Inserts Using a Single INSERT Statement



With the introduction of row constructor in SQL 2008, you can now insert multiple values using a single INSERT statement.

With the row constructor, you can now insert multiple values using a single INSERT statement. In SQL Server 2000 or SQL Server 2005, to insert multiple rows to a table, you would have need of one INSERT statement for each record.   To exemplify, to insert the multiple records to EmpTest Table, you would do something like the following

insert into EmpTest values('sakshi',2)
insert into EmpTest values('Aishwarya',3)
insert into EmpTest values('Arti',2)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

The individual insert into... values statement can now be cut down with the row constructor.  The script above will now look as follows:

insert into EmpTest values('Vibha',1),
                        ('Sandhya',3),
                        ('Aurpita',1)
(3 row(s) affected)

SELECT with VALUES Clauses in the INSERT Statement Row Constructor

It allow a combination of specifying a list of values using the VALUES clause and getting data from another table using the SELECT statement

Note: Subqueries are allowed as long as the subquery returns only a single column and a single row

insert into EmpTest values('Paavai',2),
                          ('Edwina',3),
((select o.name from OneRowTable o where o.id =1),
                          (select '3'))

select * from EmpTest

emp_id      emp_name   dept_id
----------- ---------- -----------
1           sakshi     2
2           Aishwarya  3
3           Arti       2
4           Vibha      1
5           Sandhya    3
6           Aurpita    1
7           Paavai     2
8           Edwina     3
9           vikram     3

(9 row(s) affected)


Enclose grand code


Post Reference: Vikram Aristocratic Elfin Share

Thursday, June 21, 2012

Ensure there is one row in a Table

How to ensure there is only one row in a table

There are situation where you want your table to contain only one row, generally such tables are used to store some configuration information, and you want :
  • No one even accidently can delete the rows in it
  • No one can enter new record to that table

If such is your requisite then we can achieve your requirement by implementing “Instead Of Triggers” on Table, let see how we are implementing our solution:

We will simulate the whole problem by taking example; we create a table named OneRowTable then insert single record in it:

create table OneRowTable
(id int identity(1,1),
name varchar(10) )

Command(s) completed successfully.

insert into OneRowTable values('Siddhi')
(1 row(s) affected)

Now we try to create Insert Preventive Trigger on OneRowTable, so that when anyone try to insert new record, it will not allow to insert and prompt a message.

create trigger  tr_insert_prevent
ON OneRowTable INSTEAD OF INSERT
AS
begin
declare @cnt int
      select @cnt=count(*) from OneRowTable
      if @cnt > 0
      begin
            raiserror('The table must always have only one row',1,1)                  
      end
      else
      begin
            insert into OneRowTable
            select i.name from inserted i
      end  
end
GO

Command(s) completed successfully.

Insertion after Trigger

Now since we have created this trigger which prevent user to enter new row to the table, it should give error message while someone tries to insert new record. Let’s check out
   
insert into OneRowTable values('Kyasti')

The table must always have only one row.
Msg 50000, Level 1, State 1

(1 row(s) affected)

We can see as we go for inserting new row, trigger cam into action and prevent the inserting of record.

Deletion after Trigger

Since we have Trigger in place, it will restrict the user from deleting rows from OneRowTable, let’s have a look to it

delete OneRowTable

The table must always have only one row.
Msg 50000, Level 1, State 1

(1 row(s) affected)

So you can see it prevent me from deleting record.

Coding is what coders know “BLISS”


Post Reference: Vikram Aristocratic Elfin Share

Friday, June 15, 2012

Varchar(N) VS Varchar(Max) from deeper view... Series-I

Prior to SQL 2005, Varchar and NVarchar holds only 8000 bytes, so those days sql programmers used TEXT datatype when their requirement ask to store more than 8000 byte of data.
The problem with this approach was that most of the string function does not work with text datatype. But after the introduction of Varchar(MAX) / NVarchar(MAX) life of programmers became easy to handle long string.
Now the question is how much Varchar(Max) datatype holds, what is the limitation for MA X? The answer is 2GB…
Oh! That nice space J
So can I declare a string variable of length 9000 byte like this Varchar(9000)?
Let’s find out:
create table #tempo
(Id int identity(1,1),
 EmpName varchar(20),
 EmpProfile varchar(9000)
)
Msg 131, Level 15, State 2, Line 5
The size (9000) given to the column 'EmpProfile' exceeds the maximum allowed for any data type (8000).

Answer we found is No; we can’t declare a column with varchar size greater than 8000. You can either declare a variable as VARCHAR(8000) or VARCHAR(MAX). But none in between.

This leads to the question:
If my requirement says to declare field with varchar(9000) size how could I do this?

How do we restrict the length of the field then?

This can be achieved by adding a CHECK constraint.

create table Students (
    student_id int,   
    notes VARCHAR(MAX))
GO

-- then alter the table to add the check constraint
-- which will restrict the the size of notes column

alter table Students
add constraint StudentNoteLength
check (DATALENGTH(notes) <= 9000)

Command(s) completed successfully.

Storage of NVARCHAR(MAX)/VARCHAR (MAX) values
The datatype Varchar(MAX), Nvarchar(MAX), Varbinary(MAX)  and XML data types are known as Large Value Types. The maximum size of a data row is 8060 bytes for storing user data. When you store a Large Value Type to a column, if the total size of the row (including your Large Value Type) value is less than 8060 bytes, the value is stored "in row". That means the value is stored in the same data row where other values of the same record are stored. If the Large Value Type is larger, it is stored "out of row" which means that the data is stored in another location and a pointer to the stored location will be added in the data row. Reading or writing values "out of the row" will need some additional processing and hence is not as fast as "in row" operations.
Coding; Joy what it gives

Post Reference: Vikram Aristocratic Elfin Share

Thursday, June 14, 2012

Misbehaving Subqueries!!! What I Do?

Misbehaving Subqueries!!!  Then why it is not seize up by compiler??? Then what should I do?
There are many tricky programming error involving subqueries  which even compiler parse it correctly. We will be describing those bugs then make the recommendation, what we can do to avoid such bugs.
Let’s take an example to see where we are making mistakes in subquery.
Here in this example, I have taken two table Department and Employee.
create table Department
(dept_id int identity(1,1) primary key,
dept_name varchar(10))

create table Employee
(emp_id int identity(1,1),
emp_name varchar(10),
designation varchar(10),
d_id int references Department(dept_id) )
The dept_id in Department table is primary key, and d_id in Employee table is corresponding reference key to the dept_id of Department table.
Now I am inserting few data to both the tables
insert into Department values('HR')
insert into Department values('Operation')
insert into Department values('Marketing')
insert into Department values('Software')
insert into Employee values('Abhilash','HR Executive',1)
insert into Employee values('Vidisha ','Marketing Manager',2)
insert into Employee values('Vibhuti  ','Operation Incharge',3)
Here you can see there is no employee belonging to the Software Department.
Now suppose you are asked to return the Department name where there are no employee belonging to it, you will write a subquery to return the desired result like this below:
select dept_id, dept_name from Department
where dept_id not in (select dept_id from Employee)
The above query is supposed to return the desired result that is Software department. But to your surprise, this query returns an empty set. Can you tell why? Can you identify bug in the code?
Well, the matter is, the reference column in Employee table referencing to dept_id of Department table is not dept_id instead it is defined as d_id.
Realizing this you did probably expect the query to have failed because of invalid column name, sure enough if you run that part of subquery it will fail like this:
select dept_id from Employee
Msg 207, Level 16, State 1, Line 1
Invalid column name 'dept_id'.
However in the context of outer query, apparently the subquery is valid, though we don’t get desired result!!!
select dept_id, dept_name from Department
where dept_id not in (select dept_id from Employee)
dept_id     dept_name
----------- ----------

(0 row(s) affected)
Why So?? The name resolution process works from inner nesting level outward. The query processor first looked for dept_id in Employee Table, not having found such column name, it looked for one in Department table ( the outer level) and found it. It is interpreted as
select dept_id, dept_name from Department d
where dept_id not in (select d.dept_id from Employee e)
Logically, the query doesn’t make any sense.
To fix the problem, of course, you need to use the correct column name from Employee table.
select dept_id, dept_name from Department
where dept_id not in (select d_id from Employee)
dept_id     dept_name
----------- ----------
4           Software

(1 row(s) affected)
Recommendation!!! To avoid such bugs in future, it is good practice to always include the table name or table alias for all attribute in subquery. Like this
select d.dept_id, d.dept_name from Department d
where d.dept_id not in (select e.d_id from Employee e)
Get pleasure from coding

Post Reference: Vikram Aristocratic Elfin Share

Wednesday, June 13, 2012

User defined stored procedures should not be prefixed with 'SP_'. IS IT SO?


Microsoft best practice says, User defined stored procedures should not be prefixed with 'SP_'. IS IT SO?
I have seen many blogs saying it is best practice not to use SP_ prefix to the user defined stored procedure. ???

The question of the hour is what it makes difference from system procedure if we create user defined stored procedure with SP_ prefix, and then we decide whether to stop writing stored procedure with SP_ prefix.

We will go into the depth to find out how it works when we declare stored procedure with SP_ prefix by taking enough of examples.

Example 1
I am creating a stored procedure in tempDatabase, then I am creating a user defined stored procedure with the name sp_tables.
create database tempDatabase
Command(s) completed successfully.
Database is ready.
use tempDatabase
go
CREATE PROCEDURE sp_tables
AS
BEGIN
      select * from EmployeeList
END
GO
Now we have created procedure which returns record set of all employee from EmployeeList table
Now its time to execute the stored procedure.
Expected output
Records from Employee List tables. Correct

Auctual Output
use tempDatabase
exec sp_tables

tempDatabase
dbo
EmployeeList

TABLE
NULL
tempDatabase
INFORMATION_SCHEMA
CHECK_CONSTRAINTS

VIEW
NULL
tempDatabase
INFORMATION_SCHEMA
COLUMN_DOMAIN_USAGE

VIEW
NULL

The output is something different from what we have expected, so where we go wrong???
The finding is, if user defined stored procedure name is same as system defined stored procedure, then the DB engine search the SP from Master database not from the user defined database.

So, the procedure will be executed from "MASTER" database... Not from user database. In our case the stored procedure is System stored procedure name, So, its searched and executed from MASTER database itself.

Example 2
Lets create a stored procedure in TempDatabase with the name sp_Tables1, which is not a system defined stored procedure name.
use tempDatabase
go
CREATE PROCEDURE sp_tables1
AS
BEGIN
      select * from EmployeeList
END
GO

Now its time to execute the stored procedure.
Expected output
Records from Employee List tables. Correct

Auctual Output
use tempDatabase
exec sp_tables1

id          empName
----------- ----------
1           Aakhya
2           Kruthika
3           Aanandita
4           Chandanika
5           Ceitra
6           Arasi
7           Siddhiksha
8           Sakshi

(8 row(s) affected)
It results as the same, what we expected... How and why?, The stored procedure name was prefixed as 'Sp_' , Then it should be searched in "MASTER" database. correct?
NO The reason is, This is not a "SYSTEM STORED PROCEDURE", So, It will be searched in Local database first and then "MASTER" database, In our case, The procedure will be there in user database (tempDatabase) itself, So, Its searched and executed from User database itself...
Example 3
I am creating one stored procedure in "MASTER" database with name Sp_Tables1. Before that we first need to drop the procedure sp_table1 from user database (tempDatabase) which we had created in previous example.
use tempDatabase
drop procedure sp_tables1

use master
go
CREATE PROCEDURE sp_tables1
AS
BEGIN
      select * from EmployeeList
END
GO

Now its time to execute the stored procedure. We are executing this procedure from user database (tempDatabase)
use tempDatabase
exec sp_tables1

Expected output
Don’t Know, because no procedure with sp_tables1 is present in user database(tempDatabase).
Auctual Output
use tempDatabase
exec sp_tables1

id          empName
----------- ----------
1           Aakhya
2           Kruthika
3           Aanandita
4           Chandanika
5           Ceitra
6           Arasi
7           Siddhiksha
8           Sakshi

(8 row(s) affected)
The reason is, The procedure is NOT A SYSTEM STORED PROCEDURE..., So, The it searches the Stored procedure in Local database first. If it is not there in local then only it searches in MASTER database.

In our case, the procedure will be there in MASTER database. So, It is searched in use database first, But the procedure will not be there in user database, So, Its searched and executed from MASTER database.

Example 4
Now lets create one stored procedure in both MASTER and User database (tempDatabase) named Sp_Tables1.

IN MASTER DATABASE

use master
go
CREATE PROCEDURE sp_tables1
AS
BEGIN
      print 'from master'
END
GO

Command(s) completed successfully.


IN USER DATABASE

use tempDatabase
go
CREATE PROCEDURE sp_tables1
AS
BEGIN
      select * from EmployeeList
END
GO
Command(s) completed successfully.

Now its time to execute the stored procedure. We will executing this procedure from user database (tempDatabase)
use tempDatabase
exec sp_tables1
id          empName
----------- ----------
1           Aakhya
2           Kruthika
3           Aanandita
4           Chandanika
5           Ceitra
6           Arasi
7           Siddhiksha
8           Sakshi

(8 row(s) affected)
The reason is, the procedure is NOT A SYSTEM STORED PROCEDURE..., So, the search operation is performed in user database (TempDatabase) first and then MASTER database.

If Procedure is there in user database then, the procedure will be executed from user database itself, and it will not be search in MASTER database.
Conclusion:

The 'SP_' prefixed stored procedure will not be searched the stored procedure in MASTER database always. It will be searched based on the various scenario described above.

When Object name is SYSTEM DEFINED NAME then only it searches the procedure in MASTER database first and then user current database.

Now it’s on u whether to use SP_ prefix to user defined stored procedure or not…
But DB practice says, not to use SP_ prefix to user procedure.

Coding gives immense pleasure J


Post Reference: Vikram Aristocratic Elfin Share