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

Read file names from the folder

Stored procedure to read the filenames: create procedure spReadFolder @path sysnameasbeginset nocount ondeclare @dirfile table(id int identity(1,1),FileName sysname NULL )declare @cmd nvarchar(512)set @cmd = 'DIR /b ' + @pathinsert into @dirfileexec master..xp_cmdshell @cmdselect * from @dirfileendgoExecute stored procedure: declare @path sysnameset @path = 'D:\SQL_folder'exec spReadFolder @pat...

Read More