Home > Uncategorized > Indexed views SQL Server 2005

Indexed views SQL Server 2005

In an attempt to speed up queries coming from my 11 million row table "Companies", I decided to dabble in a bit of indexed views, which are now available in sql 2005.

Here’s a transscript of how I got on (warts and all)

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:Documents and SettingsAdministrator>sqlcmd
1>
Sqlcmd: Warning: Last operation was terminated by the user by pressing Ctrl-C.

C:Documents and SettingsAdministrator>sqlcmd -S.SQLEXPRESS -E
1> use dinfo
2> create view VCompanies with schemabinding as
3> select * from companies
4> go
Msg 111, Level 15, State 1, Server OPENMERCHANTACCSQLEXPRESS, Line 2
‘CREATE VIEW’ must be the first statement in a query batch.
Msg 156, Level 15, State 1, Server OPENMERCHANTACCSQLEXPRESS, Line 3
Incorrect syntax near the keyword ‘select’.
Sqlcmd: Error : Microsoft OLE DB Provider for SQL Server : One or more errors oc
curred during processing of command..
1> use dinfo
2> go
Changed database context to ‘dinfo’.
1> create view VCompanies with schemabinding as
2> select * from companies
3> go
Msg 1054, Level 15, State 6, Server OPENMERCHANTACCSQLEXPRESS, Procedure VCompa
nies, Line 2
Syntax ‘*’ is not allowed in schema-bound objects.
1> create view VCompanies with schemabinding as
2> select id,name,address,city,PostCode,Telephone from companies
3> go
Msg 4512, Level 16, State 3, Server OPENMERCHANTACCSQLEXPRESS, Procedure VCompa
nies, Line 2
Cannot schema bind view ‘VCompanies’ because name ‘companies’ is invalid for sch
ema binding. Names must be in two-part format and an object cannot reference its
elf.
1> select id,name,address,city,PostCode,Telephone from dinfo..companies
2> create view VCompanies with schemabinding as
3> select id,name,address,city,PostCode,Telephone from dinfo..companies
4> go
Msg 111, Level 15, State 1, Server OPENMERCHANTACCSQLEXPRESS, Line 2
‘CREATE VIEW’ must be the first statement in a query batch.
Msg 156, Level 15, State 1, Server OPENMERCHANTACCSQLEXPRESS, Line 3
Incorrect syntax near the keyword ‘select’.
1> create view VCompanies with schemabinding as
2> select id,name,address,city,PostCode,Telephone from dinfo..companies
3> go
Msg 4512, Level 16, State 3, Server OPENMERCHANTACCSQLEXPRESS, Procedure VCompa
nies, Line 2
Cannot schema bind view ‘VCompanies’ because name ‘dinfo..companies’ is invalid
for schema binding. Names must be in two-part format and an object cannot refere
nce itself.
1> create view VCompanies with schemabinding as
2> select id,name,address,city,PostCode,Telephone from dbo.companies
3> go
1> CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)
2> go
Msg 1088, Level 16, State 12, Server OPENMERCHANTACCSQLEXPRESS, Line 1
Cannot find the object ‘Vdiscount1’, because it does not exist or you do not hav
e permission.
1> create unique clustered index idxVcompanies on VCompanies (name,address,city,
postcode,telephone)
2> go
Msg 1935, Level 16, State 1, Server OPENMERCHANTACCSQLEXPRESS, Line 1
Cannot create index. Object ‘VCompanies’ was created with the following SET opti
ons off: ‘QUOTED_IDENTIFIER’.
Sqlcmd: Error : Microsoft OLE DB Provider for SQL Server : One or more errors oc
curred during processing of command..
1> SET ANSI_NULLS ON
2> SET ANSI_PADDING ON
3> SET ANSI_WARNINGS ON
4> SET CONCAT_NULL_YIELDS_NULL ON
5> SET NUMERIC_ROUNDABORT OFF
6> SET QUOTED_IDENTIFIER ON
7> SET ARITHABORT ON
8>
9> go
1> create unique clustered index idxVcompanies on VCompanies (name,address,city,
postcode,telephone)
2> go
Msg 1935, Level 16, State 1, Server OPENMERCHANTACCSQLEXPRESS, Line 1
Cannot create index. Object ‘VCompanies’ was created with the following SET opti
ons off: ‘QUOTED_IDENTIFIER’.
1> drop view vcompanies
2> go
1> create view VCompanies with schemabinding as
2> select id,name,address,city,PostCode,Telephone from dbo.companies
3> go
1> create unique clustered index idxVcompanies on VCompanies (name,address,city,
postcode,telephone)
2> go..

Warning! The maximum key length is 900 bytes. The index ‘idxVcompanies’ has maxi
mum length of 1275 bytes. For some combination of large values, the insert/updat
e operation will fail.
Msg 1105, Level 17, State 2, Server OPENMERCHANTACCSQLEXPRESS, Line 1
Could not allocate space for object ‘SORT temporary run storage:  49609231545139
2′ in database ‘dinfo’ because the ‘PRIMARY’ filegroup is full. Create disk spac
e by deleting unneeded files, dropping objects in the filegroup, adding addition
al files to the filegroup, or setting autogrowth on for existing files in the fi
legroup.
The statement has been terminated.
Sqlcmd: Error : Microsoft OLE DB Provider for SQL Server : One or more errors oc
curred during processing of command..

alter database dinfo
ADD FILE
(
 NAME = dinfot2,
 FILENAME = ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadinfo2.ndf’,
 SIZE = 5MB,
 MAXSIZE = UNLIMITED,
 FILEGROWTH = 5MB
)
GO

… and re-ran the Create index statement..

 

 

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: