Cannot create index on view [ViewName]. It does not have a unique clustered index

If you are using View, you might have come across error message something like this:

Msg 1940, Level 16, State 1, Line 3

Cannot create index on view 'dbo.ViewName'. It does not have a unique clustered index.

This is a very common error while you are trying to create non clustered index on view. If you have view and you want to create Index on that view, that is fine but there is one prerequisite, you have to have one Unique Clustered Index in that view and then you will be able to create NonClustered Index on that view. You may face this error in any SQL Server version after SQL Server 2000.

Let us see it by example.

--create tbl_Employee table

create table tbl_Employee

(

EmployeeID int,

name varchar(50),

dept varchar(50),

company varchar(50)

)

GO

--create view on that table

create view dbo.vwEmployee

as

select name,dept,company from dbo.tbl_Employee

--check the view whether it is working

select * from vwEmployee

--create index on view

CREATE nonCLUSTERED INDEX id_View_NCLvwEmployee

ON dbo.vwEmployee (name,dept)

--as soon as you will try to create above index,

--you will get the below error.

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'vwEmployee' because the view is not schema bound.

--reason of above error is, before creating index on view, View should be with schemabindings.

--let us drop view and recreate it.

drop view dbo.vwEmployee

go

create view dbo.vwEmployee

WITH SCHEMABINDING

as

select name,dept,company from dbo.tbl_Employee

GO

--now let us again create index.

CREATE nonCLUSTERED INDEX id_View_NCLvwEmployee

ON dbo.vwEmployee (name,dept)

go

--this time you will again getting different error (as below).

Msg 1940, Level 16, State 1, Line 2

Cannot create index on view 'dbo.vwEmployee'. It does not have a unique clustered index.

-- if you create unique clustered index first and then you will be able to --- create nonclustered index on view

CREATE unique CLUSTERED INDEX id_View_CLvwEmployee

ON dbo.vwEmployee (name,dept)

GO

--now create nonclustered index

CREATE nonCLUSTERED INDEX id_View_NCLvwEmployee

ON dbo.vwEmployee (company)

--select your view

Select * from vwEmployee

Different Type of Joins

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.

1 INNER JOIN

The INNER JOIN keyword return rows when there is at least one match in both tables.

2 OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
The LEFT OUTER JOIN keyword returns all rows from the left table (table1), even if there are no matches in the right table (table2). If there are no columns

matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
The RIGHT OUTER JOIN keyword returns all the rows from the right table (table2), even if there are no matches in the left table (table1). If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

3 CROSS JOIN

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The following example shows a Transact-SQL cross join.

SELECT p.ID, p.value, t.ID, t.value

FROM table1 p

CROSS JOIN table2 t

ORDER BY p.ID;

Cross-join is the same thing as performing a join where the condition is always true.

For Example

SELECT t1.*, t2.*
FROM t1
INNER JOIN t2
ON ( 1 = 1 )

“SQL Server 2005 Express tools" failed while installing SQL Server 2008 Express Edition.

Today morning when I am going to install SQL Server 2008 R2 Express edition, I am getting below error.
Rule "SQL Server 2005 Express tools" failed.

The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools.

Solution:

I am just trying to solve this issue By following the steps described. I want to keep SQL Server 2005 and SQL Server 2008 both on my PC. I am going to explain one trick through which you can keep and work on both SQL server 2005 and 2008 and installed on the machine at the same time. The trick is simple, just change a registry directory name of the SQL server 2005 studio with registry editor. Follow the below step.

1. Go to Windows > Run, enter “regedit” and click ok.
2. Browse to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90.
3. Rename “90″ to something else, like “old”

After the installation, make sure you have to change it back.

You can now rerun the process in the SQL Server 2008 Express installation wizard. After installation completed, you will notice that the old SQL Server 2005 doesn’t run when you try to launch it. No worry, to fix it you can change the newly created directory “90″ to “70″. Then change “old” that you changed from “90″ before back to “90″. It should now work for both SQL Server 2005 and 2008 Studio Express.

Enable CLR in SQL Server

The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

You can disable CLR integration by setting the clr enabled option to 0. When you disable CLR integration, SQL Server stops executing all CLR routines and unloads all application domains.

Note: To enable CLR integration, you must have ALTER SETTINGS server level permission, which is implicitly held by members of the sysadmin and serveradmin fixed server roles.

What is NULL?

Most of them always confused that NULL is false, absent of value. There are few properties of the NULL are universal but the knowledge of the same is not universal.

Let us run following very simple query. Run following T-SQL script.

SELECT SUM(data)FROM (SELECT NULL AS data) t

It will return following error:

Msg 8117, Level 16, State 1, Line 1
Operand data type NULL is invalid for sum operator.

Now error itself explain, here NULL is not the type of Integer by default. We must have to convert it in Integer by casting.

SELECT SUM(data)FROM (SELECT CAST(NULL AS INT) AS data) t

Now when we run this above query we will get NULL value as below.

Most the the SQL Developer convert NULL to 0 by using ISNULL function. But It will produce the different result.

SELECT SUM(data)FROM (SELECT ISNULL(NULL,0) AS data) t

Output would be as below.




Here I just want to explain that NULL can be converted in any type like int, varchar, decimal etc. and perform your action according the datatype query.

CAST(NULL AS INT)

CAST(NULL AS VARCHAR)

CAST(NULL AS DECIMAL)

One more interesting point regarding NULL is that when you add or concatenate anything with NULL, output would be NULL.

SELECT 5 + NULL

SELECT 5 + CAST(NULL AS INT)

OR

SELECT 'Anjum' + NULL

SELECT 'Anjum' + CAST(NULL AS varchar)

Output would be NULL.

I am confident that after reading the post you will have no confusion regarding NULL in future.