Feeling pain in importing data to index table; here it is dub way out

Disable Index and Insert!!! Is this the solution???

In order to speed the import of our large amount of data we want to disable all non-clustered indexes.  The easiest way of doing this is to disable the clustered index since whenever we disable a clustered index; it disables all the non-clustered indexes on the table as well. 

NOTE: An interesting side-effect that saves us the time of disabling each non-clustered index individually but presents us with a serious problem: once a clustered index is disabled, users can not access the underlying table data.

Let me say that again: once a clustered index is disabled, users can not access the underlying table data. 

So, we want the ease of disabling all indexes at once but need to mitigate the rather pesky side-effect of losing access to the data that occurs when one disables a clustered index.  The way to do that is to disable the clustered index, then re-enable it, leaving the non-clustered indexes disabled.

Lets fuel up our discussion by stimulate the same scenario, we are create table  InsertNdisableIndexDemo and defining cluster index on primary key and non cluster index on fName, sName and education

create table InsertNdisableIndexDemo

(id int identity(1,1) primary key clustered,

fName varchar(10),

sName varchar(10),

age int,

education varchar(10))

command(s) completed successfully.

create nonclustered index nonClusterd_fName ON InsertNdisableIndexDemo (fName)

create nonclustered index nonClusterd_sName ON InsertNdisableIndexDemo (sName)

create nonclustered index nonClusterd_education ON InsertNdisableIndexDemo (education)

Command(s) completed successfully.

Now we have indexes on place for InsertNdisableIndexDemo table, lets query the sys.indexs table to see all the indexes and the status of each index.

select name 'IndexName', type_desc, is_disabled from sys.indexes

where object_id = (select object_id from sys.objects where name = 'InsertNdisableIndexDemo')

IndexName                       type_desc     is_disabled

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

PK__InsertNd__3213E83F403A8C7D  CLUSTERED     0

nonClusterd_fName               NONCLUSTERED  0

nonClusterd_sName               NONCLUSTERED  0

nonClusterd_education           NONCLUSTERED  0

(4 row(s) affected)

We can see from the result of query that all indexes are in place and are in enable status. Now our task is to diable the indexes so as to ease the work of importing data to sql server, for this we will be taking the below approach, let find out each step

Follow this procedure:

1. Disable all indexes on a table by disabling the clustered index
2. Rebuild only the clustered index (since a rebuild is the method for re-enabling an index), leaving the non-clustered indexes disabled
3. Check disabled indexes
4. Check to make sure no clustered indexes are disabled in the database
5. Import the data
6. Rebuild all indexes (since a rebuild is the method for re-enabling an index) on a table

Disable all indexes on a table by disabling the clustered index

We are now disabling our cluster index, which will ultimately all non cluster index will get disable.

  

alter index PK__InsertNd__3213E83F403A8C7D on InsertNdisableIndexDemo disable

Warning: Index 'nonClusterd_fName' on table 'InsertNdisableIndexDemo' was disabled as a result of disabling the clustered index on the table.

Warning: Index 'nonClusterd_sName' on table 'InsertNdisableIndexDemo' was disabled as a result of disabling the clustered index on the table.

Warning: Index 'nonClusterd_education' on table 'InsertNdisableIndexDemo' was disabled as a result of disabling the clustered index on the table.

We can check out the indexes status by querying the sys.indexes table

select name 'IndexName', type_desc, is_disabled from sys.indexes

where object_id = (select object_id from sys.objects where name = 'InsertNdisableIndexDemo')

IndexName                       type_desc     is_disabled

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

PK__InsertNd__3213E83F403A8C7D  CLUSTERED     1

nonClusterd_fName               NONCLUSTERED  1

nonClusterd_sName               NONCLUSTERED  1

nonClusterd_education           NONCLUSTERED  1

(4 row(s) affected)

As to our discussion all the non cluster indexes are disabled by disabling the cluster index.  

Now if we try to insert data to the table, it will give error

insert into InsertNdisableIndexDemo

select 'Akansha','Patnaik',24,'BE'

Msg 8655, Level 16, State 1, Line 1

The query processor is unable to produce a plan because the index 'PK__InsertNd__3213E83F403A8C7D' on table or view 'InsertNdisableIndexDemo' is disabled.

Reason: Cluster index is disable

Rebuild clustered index

alter index PK__InsertNd__3213E83F403A8C7D on InsertNdisableIndexDemo rebuild

Command(s) completed successfully.

This will rebuild only the clustered index, re-enabling access to the data. Now lets check the disable index

select name 'IndexName', type_desc, is_disabled from sys.indexes

where object_id = (select object_id from sys.objects where name = 'InsertNdisableIndexDemo')

IndexName                       type_desc     is_disabled

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

PK__InsertNd__3213E83F403A8C7D  CLUSTERED     0

nonClusterd_fName               NONCLUSTERED  1

nonClusterd_sName               NONCLUSTERED  1

nonClusterd_education           NONCLUSTERED  1

(4 row(s) affected)

Now, checking the disabled indexes on InsertNdisableIndexDemo shows that only the non-clustered indexes are disabled.

 Once all non cluster index gets disabled, you can go ahead and perform your import/insert of data to your table.

insert into InsertNdisableIndexDemo

select 'Akansha','Patnaik',24,'BE'

union all

select 'Pratiksha','Sharma',24,'Mtec'

union all

select 'Aadhya','Mohapatra',24,'BE'

union all

select 'Prajna','Das',24,'Btec'

union all

select 'Nandani','Ray',24,'MBA'

union all

select 'Apali','Mohanty',24,'CA'

union all

select 'Niharika','Patnaik',24,'BE'

(7 row(s) affected)

Once the insert completes we can rebuild all indexes which will both re-enable the indexes and update them with the new data.

alter index all on InsertNdisableIndexDemo rebuild

select name 'IndexName', type_desc, is_disabled from sys.indexes

where object_id = (select object_id from sys.objects where name = 'InsertNdisableIndexDemo')

IndexName                       type_desc     is_disabled

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

PK__InsertNd__3213E83F403A8C7D  CLUSTERED     0

nonClusterd_fName               NONCLUSTERED  0

nonClusterd_sName               NONCLUSTERED  0

nonClusterd_education           NONCLUSTERED  0

(4 row(s) affected)

Just code,have fun. Enjoy the game

TOP 100 PERCENT and ORDER BY obsolete from SQL Server 2005

In SQL 2000 it was an attempt to return all records of the view in the correct order. In SQL 2005 and up you can not simulate ORDER BY using this trick anymore, so SELECT TOP (100) PERCENT has no meaning.

Lets stimulate the scenerio. We create a table testTop100 for our explanation with field id and name

create table testTop100

(id int identity(1,1),

name varchar(10))

Command(s) completed successfully.

Lets insert few records to testTop100

insert into testTop100 values('Abhaya')

insert into testTop100 values('Nitya')

insert into testTop100 values('Ananya')

insert into testTop100 values('Roma')

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Now we will be creating view vwTestTop100 with top 100 Percent and Order by clause

create view vwTestTop100

as

select top 100 percent * from testTop100 order by id desc

go

Command(s) completed successfully.

Lets see the result with simple select query

select * from testTop100

id          name

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

1           Abhaya

2           Nitya

3           Ananya

4           Roma

(4 row(s) affected)

Now lets see the result of View which is using Top 100 Percent and order by on id desc

select * from vwTestTop100

id          name

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

1           Abhaya

2           Nitya

3           Ananya

4           Roma

(4 row(s) affected)

As we can see SQL Server neither throws error nor it acknowledge the presence of order by clause, it simply ignore the order by clause in View defination.

Now lets try the following

select * from vwTestTop100 order by id desc

id          name

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

4           Roma

3           Ananya

2           Nitya

1           Abhaya

(4 row(s) affected)

Now from the output we can see, desired result found

Conclusion :  It is pointless to add ORDER BY clause to the view definition and expect records to come in that ORDER. If you need records ordered, don't put ORDER BY in the view, but rather

select * from myView ORDER BY OrderFields.

This way the correct order will be guaranteed.

Abide by code semantic, u miss all coding fun..Walk off beyond   

Post Reference: Vikram Aristocratic Elfin Share

Sorting table separately while using UNION All, How to??

There are times when developer want top and bottom query of the UNION ALL resultset sorted independently, like this

select Columns from table1 order by Columns

union all

select Columns from tabbe2 order by Columns

However the above query will fail and give the output as

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'union'.

NOTE :It is not possible to have two different order by in the UNION statement because UNION returns single resultant.

However if your requirement is saying to sort top and bottom query independently then how to do?

Let’s create a scenario, by taking two tables TABLE1 and TABLE2

create table table1

(id int,

name varchar(15)

)

Command(s) completed successfully.

create table table2

(id int,

name varchar(15)

)

Command(s) completed successfully.

insert into table1(id,name)

select 1,'Ananya_Tab1'

union all

select 2,'Abhiroop_Tab1'

union all

select 3,'Gunjal_Tab1'

(3 row(s) affected)

insert into table2(id,name)

select 3,'Bikshapati_Tab2'

union all

select 2,'Sanjana_Tab2'

union all

select 1,'Akshit_Tab3'

(3 row(s) affected)

Now our tables are in place let’s do proceed to stimulate our requirement, we want like this  

select id,name, 'tab1' as odrCol from table1

order by id

union all

select id,name, 'tab2' as odr1 from table2

order by id

But firing the above script on ssms, gives error                          

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'union'.

And if we execute the select UNION without order by

select id,name from table1

union all

select id,name from table2

id          name

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

1           Ananya_Tab1

2           Abhiroop_Tab1

3           Gunjal_Tab1

3           Bikshapati_Tab2

2           Sanjana_Tab2

1           Akshit_Tab3

(6 row(s) affected)

However our requirement is like this

id          name          

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

1           Ananya_Tab1    

2           Abhiroop_Tab1  

3           Gunjal_Tab1    

1           Akshit_Tab3    

2           Sanjana_Tab2   

3           Bikshapati_Tab2

So to get the output as required, lets add a additional column ordCol and use it in order by clause

select id,name, 'tab1' as odrCol from table1

union all

select id,name, 'tab2' as odrCol from table2

order by odrCol,id

id          name            odrCol

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

1           Ananya_Tab1     tab1

2           Abhiroop_Tab1   tab1

3           Gunjal_Tab1     tab1

1           Akshit_Tab3     tab2

2           Sanjana_Tab2    tab2

3           Bikshapati_Tab2 tab2

(6 row(s) affected)

Now we can see we achieved our desired requirement of independent sorting of select query in UNION ALL.

Code exercises your creative instincts; do code n enjy J   

Post Reference: Vikram Aristocratic Elfin Share