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 Ac1 c2-----101 0102 0103 0(3 row(s) affected)Approach 1:select rank=count(*),a.c1,a.c2 from A a ,A bwhere a.c1+a.c2 > = b.c1+b.c2group by a.c1,a.c2rank c1 c2-----------1 101 02 102 03 103 0(3 row(s) affected)Approach 2 :select rank() OVER (ORDER BY a.c1,a.c2) as rank,a.c1,a.c2from A aorder by rankrank c1 c2--------------------1 101 02 102 03 103 0(3 row(s) affected)Finally very very simple...

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 -...

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)...

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...

Read More

Autotrace from SYS - Some things appear to work but don't really

Autotrace from SYS - Some things appear to work but don't really We use autotrace to get the Execution Plan and Statistics. It appear to work but don't really from SYS user. We see that.SQL> create table t ( num number(2), name varchar2(10));Table created.SQL> insert into t values(1,'A');1 row created.SQL> insert into t values(2,'A');1 row created.SQL> select * from t;NUM NAME---------- ----------1 A2 ASQL> set autotrace on;SQL> select * from t;NUM NAME---------- ----------1 A2 AExecution Plan----------------------------------------------------------0...

Read More

CHAR Vs VARCHAR

CHAR Vs VARCHAR: We see the information about the CHAR and VARCHAR in this Blog.Create table t ( X varchar2(30) , Y char(30));Insert into t values ('a','a');The above table is having the two fields X & Y and corresponding data types are varchar2 and char. The CHAR is nothing more than a VARCHAR2 that is blank padded out to the maximum length. That is difference between the column X and Y.The field X consumes 3 bytes – ( NULL indicator, leading byte length , 1 byte for ‘a’).The field Y consumes 32 bytes–(NULL indicator, leading byte length,...

Read More

Performance Improvement – Index scan with NULL condition

Performance Improvement – Index scan with NULL conditionWhat will be our first step when we start tuning the queries? The first step is creating the index. But simply creating the index does not improve the performance in all scenarios.The below scripts are proves that.SQL> create table t2 as3 select object_name name, a.*4 from all_objects a;Table created.SQL> alter table t modify name null;Table altered.SQL> create index t_idx on t(name);Index created.SQL> exec dbms_stats.gather_table_stats( user, 'T' );PL/SQL procedure successfully...

Read More

Output clause

Output clause:OUTPUT clause has accesses to inserted and deleted tables just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.Let us understand OUTPUT clause using example.Initial setup: create table #t(id int,p_name char(10))insert into #t values ( 1,'AAAAA')insert into #t values ( 2,'BBBBB')insert into #t...

Read More

Performance Improvement - Part 3: Correlated Update tuning

Performance Improvement - Part 3: Correlated Update tuning:We have seen the small difference in correlated update statement execution in the previous Blog. Please refer below link if need to check it. http://karthikeyanbaskaran.blogspot.com/2010/06/difference-in-correlated-update.html The scenario is, need to update the “Address” field in table”Main” from the “Sub” table using the “id” field. The -1 needs to update in all other columns. The below update method solve it in single statement.update main mset address = nvl((select address from sub...

Read More

Performance Improvement - Part 2: Hard parse is not bad

Performance Improvement - Part 2: Hard parse is not bad In previous post, we have seen the soft parsing is doing less operation comparing with hard parsing.Please have a look on below post before read this one.http://karthikeyanbaskaran.blogspot.com/2010/07/performance-improvement-part-1-parsing.htmlThe Hard parse includes the parse, optimize, generation of the plan for the query and soft parse skips optimize and generation of the plan. So we decided...

Read More

Performance Improvement - Part 1: Parsing

Performance Improvement - Part 1: Parsing In this article, we see how to improve the performance of the SQL queries. Before that we should know about the parsing and types of that.Parsing:This is the first step in the processing of any statement in Oracle. Parsing is the process of breaking the submitted statement down into its component parts. Determining what type of statement it is (whether Query, DML, DDL) and performing various checks on it.The...

Read More

Reusable scripts - Part1

Find the letter occurrences in the statement: The below query is finding the number of occurrence letter “a” in the string.select length('Find a letter in a string') - length(replace('Find a letter in a string', 'a','')) as "Number of occurrence" from dual ;Number of occurrence--------------------2Remove the alphabetic letters in the given string:declarel_start number;len number;i number;p_str varchar2(1000) := 'aa123yksjds45';p_str1 varchar2(1000);res varchar2(1000);begini:=0;len :=length(p_str);WHILE i <= len loop len := ASCII(substr(p_str,i,1));...

Read More

creating the composite index with more than 900 bytes

Creating the composite index with more than 900 bytes:Sql server is having some constraints while creating the index. The index entry will not allow if the data size is more than 900 bytes. It gives the error while inserting the data.There is a way to insert the more than 900 bytes into composite index. we see that in this article.Intial setup:Creating the table with two columns and total size is more than 900 bytes.use tempdbcreate table...

Read More

Good to Know in SQL server 2008

Good to Know in SQL server 20081. Insert values:Many times we use “INSERT INTO tab_name VALUES (….) “way for inserting the data into table. There is a simple way in the SQL server 2008 to avoid multiple INSERT clauses for more than one record. It is possible in single statement. use tempdbCreate table Dept(Id int, DName varchar(100))Insert into Deptvalues(1,'A'),(2,'B'),(3,'C'),(4,'D')select * from Dept 2.Declare and initiate in same lines:Old style...

Read More

Pivot in SQL server

PIVOT: PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. CREATE TABLE Sales (Make varchar(8), Year int, Sales int)GOInsert into Sales values ('Honda',2005,20000)Insert into Sales values ('Honda',2005,60000)Insert into Sales values ('Mahindra',2005,40000)Insert...

Read More

Difference in Correlated Update

Difference in Correlated Update : I came across the Correlated update statement with below format. It updates the address filed in the “Main” table and takes it from the “sub” table. The NVL function has given outside the sub query.Outer NVL Query: update main mset address = nvl((select address from sub s where m.id=s.id),-1); We can give the NVL function with the sub query (specifically for that column). Then what is the difference between...

Read More

Shell script to find the latest file

The below shell script will help to find the latest file and name starting with “sales”.Example the below files are available in UNIX box.sales_2007_02_23_0010.datsales_2007_02_24_0024.datsales_2007_02_24_0714.datsales_2007_02_25_0010.datsales_2007_02_25_0525.datsales_2007_02_25_0835.datThere are 3 files on 25th with different time stamp. The aim is identify the latest file. So the script check the latest file based on the time when that file has created in the UNIX box.Script : latest_file.sh#!/usr/bin/kshrm -f /home/hb/ms/hb/karthi/sales.datval=`ls...

Read More

Cumulative Sum

Ways to get a Cumulative Sum:Initial setup:Creating the table and populating the table. CREATE TABLE #Sales (DayCount smallint, Sales money, RunningTotal money)INSERT INTO #Sales (DayCount, Sales, RunningTotal)SELECT 1, 10, 0union allSELECT 2, 7, 0union allSELECT 3, 2, 0union allSELECT 4, 6, 0GOSELECT * FROM #Sales ORDER BY DayCountGO Method 1:DECLARE @RunningTotal moneySET @RunningTotal = 0UPDATE #SalesSET @RunningTotal = RunningTotal = @RunningTotal...

Read More

Partitioning in SQL server

Partitioning in SQL server with small sample: Summary: Table partitioning can make very large tables and indexes easier to manage, and improve the performance of appropriately filtered queries. Table Partitioning Components: The partition function, partition scheme, and partitioned table or index form a dependency tree, with the partition function at the top, the partition scheme depending on the partition function, and then the partitioned...

Read More

WITH TIES

WITH TIES in SQL server Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified. create table sales( customer_name varchar(10), sales_amount int)insert into sales values('A',6000),('B',6000),('C',10000),('D',12000),('E',13000),('F',15000),('G',5000),('H',4000) Top 5:The “Top 5” gives first 5 records based on the condition in query. select...

Read More

Comparing the Contents of Two Tables in SQL Server

Comparing the Contents of Two Tables: create table a(c1 int,c2 varchar(3),c3 varchar(3))create table b(c1 int,c2 varchar(3),c3 varchar(3))insert into a values(1,'x','y')insert into a values(2,'xx','y')insert into a values(3,'x','y')insert into b values(1,'x','y')insert into b values(2,'x','y')insert into b values(3,'x','yy')select * from aC1 C2 C3----- -- --1 x y2 xx y3 x yselect * from bC1 C2 C3----- -- --1 x y2 x y3 x yy The first rows are the same, but the second and third rows differ. This is how we can find them: select i.c1,i.c2, i.c3,count(i.src1)...

Read More

Spool operation in SQL server

SQL Server doesn't have a SPOOL command like Oracle does for writing to files,But there are other ways of doing what we want.1. For reading, use xp_cmdshell and the ECHO command. Use the > or >> redirection symbols to either create or append to a file. xp_cmdshell "@ECHO test message >> C:\file.fil"2. Put the information you want into a table (note this can't be an ordinary temporary table, but it can be a global temporary table) and then bcp it out to a file via xp_cmdshell. xp_cmdshell "bcp .. out c:\file.fil -Usa -P -c"3. Run...

Read More

Enable xp_cmdshell on a MS SQL Server Instance

Enable xp_cmdshell on a MS SQL Server Instance : xp_cmdshell has the power to make modifications on the outer world of SQL Server. So this power has to be controlled in the security concepts and be manageable. In the early versions of Microsoft SQL Server the xp_cmdshell extended procedure was enabled default. This caused some security gaps for SQL Server owners. Although some administrators do not use xp_cmdshell functionality, it was out of control and can be used in an unsecure way by a sql server developer. Microsoft now enables SQL Server...

Read More

Sysname

About Sysname: The sysname data type is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers. Therefore, it can vary between instances of SQL Server. sysname is functionally the same as nvarchar(128) except that, by default, sysname is NOT NULL. In earlier versions of SQL Server, sysname is defined as varchar(30).Example:declare @path sysna...

Read More