Inserting data into a table that has just one column which is identity type???

If you have a table with one column, which is promoted with an identity column and you were told to insert rows into the table, how you will be performing the command?

Let’s stimulate the case by taking a table OneColumnTable with only one column id

create table OneColumnTable

(id int identity(1,1))

Now try to insert record in this table using insert statement

insert into OneColumnTable values(1)

//OUTPUT

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'OneColumnTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So it is not allowing to insert record in the table, but then you got a task to insert data into this table, how to do the insertion, lets fire the below query

insert into OneColumnTable default values

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

(1 row(s) affected

Note: If you want to continue inserting identity values, then you can make use of default value clause

And, If you want to insert explicit values then you can write

set identity_insert oneColumnTable on

insert into OneColumnTable(id) values(5);

set identity_insert oneColumnTable on

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

5

(2 row(s) affected)

That’s how you can insert values in identity column. J

Put out of your mind, it’s coding; very straightforward J   

Post Reference: Vikram Aristocratic Elfin Share

How to convert 0 to 1 and 1 to 0 in table object

Say I am having a table ZeroOneZero with following data

select * from ZeroOneZero

name       Active

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

Prakriti   0

Aaliya     1

Falak      0

Snehal     1

(4 row(s) affected)

Now what I want is to convert 0 values in Active column to 1 and vice versa. It seems very easy to do, yes absolutely it is, but you need make sure you don’t have to use where clause in Update statement else you will end up getting something unexpected….

Let’s check out how to get done with this problem, the Case clause is the solution:

update ZeroOneZero

set Active =

      (case when Active = 0 then 1

              when Active = 1 then 0

      end)

(4 row(s) affected)

/*Now lets fire select query to see the result*/

select * from ZeroOneZero

name       Active

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

Prakriti   1

Aaliya     0

Falak      1

Snehal     0

(4 row(s) affected)

Yes we are through.

Coding a Psyche Game

Post Reference: Vikram Aristocratic Elfin Share

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

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

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

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