Index "INDEX" on table "TABLE" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

When I try to execute a simple SELECT statement, It's really slow and I found that the table performed TABLE SCAN.

But, The table also have a Nonclustered Index on it.

So, I try to force query optimizer to use the Index.

Select * from Tb_Sample with (Index =NCI_Id)
Go


oh. I got an error...

Msg 315, Level 16, State 1, Line 1
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.


The reason behind are..
1. The Index (NCI_Id) may be DISABLED.
2. The Filegroup in OFFLINE(Where the actual Index resides).

1. The Index (NCI_Id) may be DISABLED(Scenario)

/*Disabling Index*/
Alter Index NCI_Id on Tb_Sample DISABLE
Go


/*Forcing Index*/
Select * from Tb_Sample with (Index =NCI_Id)
Go


It througs an error...
Msg 315, Level 16, State 1, Line 2
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.


2. The Filegroup in OFFLINE(Where the actual Index resides)(Scenario)

/*Creating Index on different filegroup: IndexFilegroup*/
Create NonClustered Index NCI_Id on Tb_Sample(Id) on [IndexFilegroup]
Go

- File(Indexes) belongs to [IndexFilegroup] filegroup

/*Setting OFFLINE a particular file, which belongs to [IndexFilegroup] filegroup*/
Alter Database DatabaseName Modify File (Name=[Indexes],OFFLINE)
Go


Caution: It makes all the objects in the filegroup inaccessible, Only use when you have valid backup, Proper understanding/guidance

/*Forcing Index*/
Select * from Tb_Sample with (Index =NCI_Id)
Go


Now also, It througs an error...
Msg 315, Level 16, State 1, Line 2
Index "NCI_Id" on table "Tb_Sample" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.


So, Index can not be forced the query optimizer when the index in DISABLED or The filegroup is OFFLINE where the Index resides.

/*To Enable the DISABLED Index*/
Alter Index NCI_Id on Tb_Sample Rebuild
Go


/*To set ONLINE the Filegroup again*/
A file set to OFFLINE can only be set ONLINE by restoring the file from the backup..

The IDENTITY function can only be used when the SELECT statement has an INTO clause.

When we try to generate sequence number for existing records in a table, we have lot of ways...

But, can we use IDENTITY function for that ?

Yes. but, it can be used only when creating new table and copying records from an existing table using SELECT...INTO. otherwise, The following error will occur..

Scenario:
/*Creating a new table*/
If OBJECT_ID('Tb_Sample') Is Null
Create Table Tb_Sample
(
Sensitivity Varchar(25)
)
Go


/*Inserting some sample data*/
Insert Tb_Sample Values('Case')
Insert Tb_Sample Values('Accent')
Insert Tb_Sample Values('Kana')
Insert Tb_Sample Values('Width')
Go


/*Using IDENTITY function*/
Select Identity(Int,1,1) [Sequence],Sensitivity from Tb_Sample
Go


oh. It throws an error...!.

Msg 177, Level 15, State 1, Line 1
The IDENTITY function can only be used when the SELECT statement has an INTO clause.


The IDENTITY function shoule be used as follows instead.

Select Identity(Int,1,1) [Sequence],Sensitivity Into Tb_Sensitivity from Tb_Sample
Go


Select * from Tb_Sensitivity
Go


So, IDENTIFY function can be used with SELECT...INTO statement

Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

When we pass a parameter value using the name(called Named parameter), should be little focus on that.

/*Creating a stored procedure*/
Create Proc Usp_Sample
(@Para1 Int,
@Para2 Int,
@Para3 Int
)As
Begin
Set Nocount on
Select @Para1 + @Para2 + @Para3
End
Go


/*#1. Executing the procedure*/
Exec Usp_Sample @Para1=1, 2, 3
Go


It'll throw an error...
Msg 119, Level 15, State 1, Line 1
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.


/*#2. Executing the procedure*/
Exec Usp_Sample 1, @Para2=2, 3
Go


It'll also throw an error...
Msg 119, Level 15, State 1, Line 1
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.


Reason is...
- Whenever the parameter value is passed using Named Parameter(ie: @Name=Value), rest of the all parameters next to the named parameter also should be Named parameter.

i:e
1. If a stored procedure have 3 parameters(IN).
2. If the First parametere used with parameter name(named parameter) then, all the remaining (Second, Third) parameters also should be named parameter.

Exec Usp_Sample @Para1=1, @Para2=2, @Para3=3
Go


3. If the Second parametere used with parameter name(named parameter) then, the Third parameters also should be named parameter.But, the First parameter no need to be a Named parameter.

Exec Usp_Sample 1, @Para2=2, @Para3=3
Go


4. If the Third parametere used with parameter name(named parameter) then, the First, Second parameters no need to be a Named parameter.

Exec Usp_Sample 1, 2, @Para3=3
Go

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Subquery should have only one expression when comparing with main query using normal operators(=,>=,>,<=.<,<>, In, Not In).

/*Creating a table*/
If OBJECT_ID('Tb_Sample1') Is Null
Create Table Tb_Sample1
(
Id Int Identity(1,1),
Col1 Varchar(10),
Col2 DateTime
)
Go


/*Creating a table*/
If OBJECT_ID('Tb_Sample2') Is Null
Create Table Tb_Sample2
(
Id Int ,
Col1 Varchar(10),
)
Go


/*Inserting record*/
Insert Tb_Sample1(Col1, Col2) Values('SQL Server',CURRENT_TIMESTAMP)
Go
Insert Tb_Sample2(Id,Col1) Values(1, 'DBA')
Go


/*Using Sub-query with multiple expressions*/
Select * from Tb_Sample1 where Id in (Select Id, Col1 from Tb_Sample2)
Go


It'll throug an error..
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


Sub-query has two expressions Id, Col1. It should be one when using Sub-query and normal operators

The query can be changed to..
Select a.* from Tb_Sample1 a Join Tb_Sample2 b On (a.Id = b.Id)
Go

or
Select a.* from Tb_Sample1 a where exists (Select 1 from Tb_Sample2 b where a.Id = b.Id)
Go

or
Select a.* from Tb_Sample1 a where exists (Select 1 from Tb_Sample2 b where a.Id = b.Id)
Go

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations

Data assigning and Data retrival can not be used in a same statement.

/*Creating a table*/
If OBJECT_ID('Tb_Sample1') Is Null
Create Table Tb_Sample1
(
Id Int Identity(1,1),
Col1 Varchar(10),
Col2 DateTime
)
Go


/*Inserting one record*/
Insert Tb_Sample1(Col1, Col2) Values('SQL Server',CURRENT_TIMESTAMP)
Go


/*Using Data assigning and Data retrival in same statement*/
Declare @Column1 Varchar(10)
select Id, Col2,@Column1 = Col1 from Tb_Sample1
Select @Column1


Id, Col2 - is the retrival part
@Column1 = Col1 - is the assigning data to variable, These both operations can not be used in a single statement

It'll throw an error...
Msg 141, Level 15, State 1, Line 2
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

INSERT into an identity column not allowed on table variables.

Normally, Inserting data explicitly in IDENTITY column in Table variable is not allowed.

Declare @Table1 Table(ID Int Identity(1,1), Column1 Varchar(10))
Insert @Table1(Id, Column1) Values(1,'Sql Server')
Go


It'll throws an error
Msg 1077, Level 16, State 1, Line 2
INSERT into an identity column not allowed on table variables.

DEFAULT or NULL are not allowed as explicit identity values.

Normally, an IDENTITY column can not be a Nullable.

When we try to insert some explicit data into an IDENTITY column the following error occurred.

Create Table Tb_Table1
(
Id Int Identity(1,1),
Column1 int
)
Go

Set identity_insert Tb_Table1 on
Insert Tb_Table1 (Id,Column1) values(Null,1)
Set identity_insert Tb_Table1 off


It'll throw an error
Msg 339, Level 16, State 1, Line 2
DEFAULT or NULL are not allowed as explicit identity values.

UNION operator is not eliminating duplicate data. When ? and Why ?


Normally, UNION operator will elimate the duplicate data from the result sets.

But, In some situation, It'll not eliminate the duplicate data.

When ?
Incase, If we have used any Case-Sensitive collations on columns, database, server.

Why ?
The UNION operation depend on the collation what we have used in columns, Database, Server.

Scenario...
/*One table created, column with case-sensitive(CS) collation*/
If OBJECT_ID('Tb_Sample1') Is Null
Create Table Tb_Sample1
(
Id Int Identity(1,1),
Column1 Varchar(10) Collate SQL_Latin1_General_CP1_CS_AS,
Column2 Varchar(10)
)
Go


/*Inserting Duplicate data, But the case differs*/
Insert Tb_Sample1(Column1, Column2) Values('SQL','Server')
Insert Tb_Sample1(Column1, Column2) Values('MS','Access')
Insert Tb_Sample1(Column1, Column2) Values('Adobe','Reader')
Insert Tb_Sample1(Column1, Column2) Values('sql','Server')
Go


/*Fetching data using UNION operator*/
Select Column1 , Column2 From Tb_Sample1 where Column1 in('SQL', 'MS','sql')
Union
Select Column1 , Column2 From Tb_Sample1 where Column1 in('Adobe')


/*Expected result was...*/
Column1Column2
AdobeReader
MSAccess
sqlServer


/*But, The actual result is...*/
Column1Column2
AdobeReader
MSAccess
SQLServer
sqlServer

The UNION operator compares the data depend on Collation what we have used in Columns, Database, Server. So, It could not be considered as a duplicate and eliminate it.

How to fix it ?
- The collation has to be changed to Case-Insensitive as given below
Alter Table Tb_Sample1 Alter Column Column1 Varchar(10) Collate SQL_Latin1_General_CP1_CI_AS
Go


or

- Use COLLATE statement and force the case-insensitive collation when using the UNION operator as given below
Select Column1 Collate SQL_Latin1_General_CP1_CI_AS as Column1, Column2 From Tb_Sample1 where Column1 in('SQL', 'MS','sql')
UNION
Select Column1 , Column2 From Tb_Sample1 where Column1 in('Adobe')


Important:
- UNION operator automatically applies DISTINCT SORT operator internally.
- So, The duplicate data will be eliminated and SORTED from the final result set. But, UNION ALL will NOT work depend on Collation and not do any data Sorting.