Order by based on the parameter passed in SQL server:

Order by based on the parameter passed in SQL server:
Dynamically ordering a result set based on the parameter passed is a powerful technique for ordering your data. The below example shows that and see the issues which we faces.


Initial setup:
create table dept
(
deptid int,
deptname varchar(10)
)

insert into dept values (1,'Sales')
insert into dept values (4,'Production')
insert into dept values (3,'fiance')
insert into dept values (2,'HR')
The following SQL script shows how you might try to use a CASE expression to dynamically order a result set:

DECLARE @OrderByOption int
SET @OrderByOption = 1 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE
WHEN @OrderByOption = 1 THEN deptid
WHEN @OrderByOption = 2 THEN deptname
END

deptid deptname
----------- ----------
1 Sales
2 HR
3 fiance
4 Production

(4 row(s) affected)


The script works well if the value of @OrderByOption is set to 1. We change the value as 2 and try.

DECLARE @OrderByOption int
SET @OrderByOption = 2 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE
WHEN @OrderByOption = 1 THEN deptid
WHEN @OrderByOption = 2 THEN deptname
END
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'Sales' to data type int.
Conceptually, the query offers the ability to order by either the deptid column or the deptname column based on the current value of @OrderByOption. The above statement attempts to order by deptname, but produces the error, " Msg 245, Level 16, State 1, Line 2 Conversion failed when converting the varchar value 'Sales' to data type int "


Why the query works when the value for @OrderByOption is set to 1 but doesn't work when the value is set to 2, we need to recognize that the two THEN conditions of the CASE statement reference expressions of different data types. In this case, SQL Server implicitly converts the data type for the entire CASE expression to the data type in the THEN clause that has the highest order of data-type precedence.

In this example, the CASE statement has two possible values that follow a THEN clause: deptid, which is an integer data type, and deptname, which is a varchar data type. The integer data type has a higher precedence than the varchar data type, so SQL Server attempts to cast the deptname expression as an integer if you try to order by that column. Such a conversion isn't allowed, so SQL Server generates the above error.
We can work around this problem by using multiple CASE statements, as the following example shows:

DECLARE @OrderByOption int
SET @OrderByOption = 2 SELECT deptid
,deptname
FROM dept
ORDER BY
CASE WHEN @OrderByOption = 1 THEN deptid END,
CASE WHEN @OrderByOption = 2 THEN deptname END
deptid deptname
----------- ----------
3 fiance
2 HR
4 Production
1 Sales

Read More

Schema Binding in SQL server

Schema Binding :
Creating a view with the SCHEMABINDING option locks the tables being referred by the view and prevents any changes that may change the table schema.

Notice two important points while creating a view with SCHEMABINDING OPTION:
1. The objects should be referred to by their owner names (two part name - [ownername.objectname] ).
2. SELECT * is not permitted.

We see that using below scripts.
create table schema_binding_table
(
id int,
msg varchar(10)
)

insert into schema_binding_table values
(1,'msg1'),
(2,'msg2'),
(3,'msg3')

select * from schema_binding_table

id msg
----------- ----------
1 msg1
2 msg2
3 msg3

(3 row(s) affected)

Chance for erros while creating view with schema binding:
The below error messgae clearly mention that SELECT * is not permitted whille creating the view with schema binding.
CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT * FROM schema_binding_table

Msg 1054, Level 15, State 6, Procedure schema_binding_VIEW, Line 2
Syntax '*' is not allowed in schema-bound objects.

Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view.

CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT id,msg FROM schema_binding_table

Msg 4512, Level 16, State 3, Procedure schema_binding_VIEW, Line 2
Cannot schema bind view 'schema_binding_VIEW' because name 'schema_binding_table' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Successful syntax:
CREATE VIEW schema_binding_VIEW WITH SCHEMABINDING AS
SELECT id,msg FROM dbo.schema_binding_table

Command(s) completed successfully.
We know that without schema binding there was no issue to alter or drop the base table without any type of warning or error. Now if we try to alter the table structure or drop the table, we are going to get this error message.

1. Drop table:

drop table schema_binding_table
Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'schema_binding_table' because it is being referenced by object 'schema_binding_VIEW'.
2. Alter table:alter table schema_binding_table alter column msg varchar(20)
Msg 5074, Level 16, State 1, Line 1
The object 'schema_binding_VIEW' is dependent on column 'msg'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN msg failed because one or more objects access this column.

Read More

Simulating ORACLE ROWNUM in SQL Server

Simulating ORACLE ROWNUM in SQL Server:
Here is the approach you can use to generate a pseudo row number in SQL Server.

The table format and data:

select * from A

c1 c2
-----
101 0
102 0
103 0

(3 row(s) affected)

Approach 1:
select rank=count(*),a.c1,a.c2 from A a ,A b
where a.c1+a.c2 > = b.c1+b.c2
group by a.c1,a.c2


rank c1 c2
-----------
1 101 0
2 102 0
3 103 0

(3 row(s) affected)

Approach 2 :
select rank() OVER (ORDER BY a.c1,a.c2) as rank,a.c1,a.c2
from A a
order by rank

rank c1 c2
--------------------
1 101 0
2 102 0
3 103 0

(3 row(s) affected)

Finally very very simple method:
drop table #T
Select IDENTITY(int, 1,1) id, * INTO #T from A
Select * from #T


id c1 c2
-----------
1 101 0
2 102 0
3 103 0

(3 row(s) affected)

Read More

EXECUTE AS and REVERT:

EXECUTE AS and REVERT:
The EXECUTE AS and the REVERT are used to change the execution logins. We see the example to change the connection details and coming back to old one.


The client has connected using the “Test” user and “EXECUTE AS” statement changes the Login connection as “Demo” user. The second result set shows that.

The REVERT command switches the execution context back to the Test user.

Explanation about the keywords:
EXECUTE AS Clause - helps us to define the execution context.

REVERT - Switches the execution context back to the caller of the last EXECUTE AS statement

SUSER_NAME() - Returns the login identification name of the user.

USER_NAME() - Returns a database user name.

Read More

Error message is: The directory name is invalid in SQL Server

Error Message: in SQL server
An error occurred while executing batch. Error message is: The directory name is invalid.


Reason:
This error will come when your disk space is very low or if the directory is protected.

Solution:check your disk space or try connecting the SQL server from some other machine.

Read More

Careful with SQL server Update

Careful with SQL server Update:
The Blog is sharing my experience with the update statement in SQL server. We need to careful when write a update statement in SQL server like a scenarios below.
Initial setup:

create table A
( c1 int,
c2 int
)

create table B
( c1 int,
c2 int
)

insert into A values (101,0)
insert into A values (102,0)
insert into A values (103,0)

insert into B values (101,7)
insert into B values (102,5)
insert into B values (103,4)
insert into B values (103,6)

Tables with value:

select * from A


select * from B




The field in c2 is having Zero for all the fields. We need to update the field c2 in table A from the table B. But we have multiple values in table B for the value 103.


Based on the above scenario, the update statement has to fail. We mostly use the Inner join method of updates in SQL server Like a below statement.

begin tran
update A
set A.c2=B.c2
from A,B
where A.c1=B.c1
The above statement is updating the records. The above method is taking care of the multiple records.





The below method is giving the error message due to multiple records.

begin tran
update A set A.c2
=(select B.c2 from B
where A.c1=B.c1
)
We need to careful when we write a update statements.

Read More

Character of DUAL

Character of DUAL:
DUAL is owned by SYS. SYS owns the data dictionary, therefore DUAL is part of the data dictionary. You are not to modify the data dictionary via SQL ever.
Dual table and its purpose:
Dual is just a convenience table. You don't need to use it, you can use anything you want. The advantage to dual is the optimizer understands dual is a special one row, one column table -- when you use it in queries, it uses this knowledge when developing the plan.
The Dual table structure, field and record details are below.
SQL> desc dual;
Name Null? Type
-------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL> select * from dual;
D
-
X
SQL> select count(*) from dual;
COUNT(*)
----------
1DUAL is owned by SYS:

The below scripts connect to DB as SYSTEM user and try to modify the DUAL table. The insert statement is not able to modify the table which is owned by SYS user.

SQL> conn system/password@oraprc
Connected.

SQL> INSERT INTO DUAL VALUES ('X');
INSERT INTO DUAL VALUES ('X')
*
ERROR at line 1:
ORA-01031: insufficient privileges
We change the connection to SYS user and try the same and it is able to insert the data.
SQL> conn sys/password@oraprc as sysdba
Connected.

SQL> INSERT INTO DUAL VALUES ('X');

1 row created.

SQL> select count(*) from dual;
COUNT(*)
----------
2
Special one row:
The definition has mentioned that DUAL is special one row, one column table. But we can insert the records in that table. So the count got changed in that.

SQL> INSERT INTO DUAL VALUES ('X');
1 row created.

SQL> select count(*) from dual;
COUNT(*)
---------
3

SQL> select * from dual;
D
-
X
The reason is that, the optimizer understands dual is a magic, special 1 row table. It stopped on the select * because there is to be one row in there. It’s just the way it works.

Delete operation on DUAL:
SQL> select count(*) from dual;
COUNT(*)
----------
3
SQL> delete from dual;
1 row deleted.

SQL> select count(*) from dual;
COUNT(*)
----------
2

SQL> delete from dual;
1 row deleted.

SQL> select count(*) from dual;
COUNT(*)
----------
1
The delete statement is deleting single record each time. The reason is that, the DUAL is special one row table.

Checking with FUCTION:

We create the function which return the value “1” and check the same function with DUAL and user created table.

SQL> select count(*) from dual;

COUNT(*)
----------
2

SQL> create or replace function foo return number
2 as
3 x number;
4 begin
5 x:=1;
6 return 1;
7 end;
8 /
Function created.

SQL> create table emp
2 ( num int);

Table created.

SQL> insert into emp values(1);

1 row created.

SQL> insert into emp values(2);

1 row created.

SQL> insert into emp values(3);

1 row created.

SQL> insert into emp values(4);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

NUM
----------
1
2
3
4
SQL> select foo from dual;

FOO
----------
1
SQL> select foo from emp;
FOO
----------
1
1
1
1
The EMP table is having the 4 records and it is returning the 4 records when do the select on that table. We have checked the same thing with the DUAL table. The count of the DUAL is 2 and it is returning one record.

Read More