MS SQL Server

When an index is defragmented to a small percentage, like < 30%, you can use the "ALTER INDEX ... REORGANIZE" statement to defragment the index. Here is a tutorial exercise on defragmenting indexes:

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (

DB_ID(N'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_links_indexed'),

DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i

WHERE s.object_id = i.object_id

AND s.index_id = i.index_id;

GO

0 NULL 0.574712643678161

2 ggl_links_url 84.053862508859

3 ggl_links_counts 0.448430493273543

ALTER INDEX ggl_links_url ON ggl_links_indexed REORGANIZE;

GO

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (

DB_ID(N'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_links_indexed'),

DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i

WHERE s.object_id = i.object_id

AND s.index_id = i.index_id;

GO

0 NULL 0.574712643678161

2 ggl_links_url 1.87590187590188

3 ggl_links_counts 0.448430493273543

The fragmentation level has been reduced from 84.05% to 1.88%.

When an index is defragmented to a large percentage, like > 30%, you can use the "ALTER INDEX ... REBUILD" statement to rebuild the index. Here is a tutorial exercise on rebuilding indexes:

UPDATE ggl_links_indexed SET url = REVERSE(url)

WHERE id <=50000;

GO

(50000 row(s) affected)

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (

DB_ID(N'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_links_indexed'),

DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i

WHERE s.object_id = i.object_id

AND s.index_id = i.index_id;

GO

0 NULL 0.574712643678161

2 ggl_links_url 85.0142045454545

3 ggl_links_counts 0.448430493273543

ALTER INDEX ggl_links_url ON ggl_links_indexed REBUILD;

GO

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (

DB_ID(N'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_links_indexed'),

DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i

WHERE s.object_id = i.object_id

AND s.index_id = i.index_id;

GO

0 NULL 0.574712643678161

2 ggl_links_url 0

3 ggl_links_counts 0.448430493273543

If you have several indexes on a single table and want to rebuild all of them, you may use the "ALTER INDEX ALL ON table_name REBUILD" statement as shown in the tutorial exercise below:

UPDATE ggl_links_indexed

SET url = REVERSE(url), counts = -counts

WHERE id <=50000;

GO

(50000 row(s) affected)

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (

DB_ID(N'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_links_indexed'),

DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i

WHERE s.object_id = i.object_id

AND s.index_id = i.index_id;

GO

0 NULL 0.574712643678161

2 ggl_links_url 85.750315258512

3 ggl_links_counts 84.040404040404

ALTER INDEX ALL ON ggl_links_indexed REBUILD;

GO

SELECT i.index_id, i.name, s.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats (

DB_ID(N'GlobalGuideLineDatabase'),

OBJECT_ID(N'ggl_links_indexed'),

DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i

WHERE s.object_id = i.object_id

AND s.index_id = i.index_id;

GO

0  NULL   0.574712643678161

2  ggl_links_url 0.12987012987013

3  ggl_links_counts 0.44843

If you want to change the definition of an existing index, you can use the "DROP INDEX" statement to drop the index first. Then use the "CREATE INDEX" statement to create it again with the new definition.

But you can also combine those two statements into one:

CREATE INDEX ... WITH (DROP_EXISTING = ON)

The tutorial exercise below recreates ggl_links_url with a change to index columns:

USE GlobalGuideLineDatabase;

GO

CREATE INDEX ggl_links_url ON ggl_links_indexed (url, counts)

WITH (DROP_EXISTING = ON);

GO

SP_HELP ggl_links_indexed;

GO

index_name  index_description   index_keys 

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

ggl_links_counts nonclustered located on PRIMARY counts

ggl_links_url  nonclustered located on PRIMARY url, counts

If you want to a view on an existing table, you can use the CREATE VIEW statement in a simple syntax:

CREATE VIEW view_name AS SELECT ...

The tutorial exercise below shows you how to create a view to represent sub set of data stored in ggl_links table:

USE GlobalGuideLineDatabase;

GO

CREATE VIEW ggl_links_top AS

SELECT id, counts, url FROM ggl_links

WHERE counts > 100;

GO

SELECT TOP 10 * FROM ggl_link_top;

GO

id  counts url

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

7600 237946 eyfndw jdt lee ztejeyx l q jdh k 

19437 222337 eypx u x

55924 1877 eyq ntohxe i rtnlu riwaskzp cucoa dva c 

63742 121330 ezdaeh mmgmo vaai meytbjjv f jixfsdjw pw

92455 945262 ezlmyenrw dyeb

36391 41386 f 

87433 977726 f

7180 559314 f kqbqlej s xixuurcgg lh r dqqvqsstxw

2252 702033 f bkh jy sqrkttuoarxmfp idqyhyy tme d

1228 146283 f m asukh

If you don't need a specific view any more, you can use the DROP VIEW statement to delete it from the database. The following tutorial exercise shows you how to delete the view, ggl_links_view:

USE GlobalGuideLineDatabase;

GO

SELECT * FROM sys.views;

GO

name object_id schema_id type type_desc

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

ggl_links_view 1189579276 1 V VIEW

ggl_links_top 1205579333 1 V VIEW

(2 row(s) affected)

DROP VIEW ggl_links_view;

GO

SELECT * FROM sys.views;

GO

name  object_id schema_id type type_desc 

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

ggl_links_top 1205579333 1  V VIEW 

(1 row(s) affected)

If you have an existing view, but you don't remember what are the columns defined in the view, you can use the "sys.columns" system view to get a list of all columns of all views in the current database.

In order to a list of columns of a single view, you need to join sys.columns and sys.views as shown in the tutorial example below:

SELECT * FROM sys.columns c, sys.views v

WHERE c.object_id = v.object_id

AND t.name = 'ggl_links_top'

GO

object_id name column_id user_type_id max_length

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

1205579333 id 1  56   4  

1205579333 counts 2  56   4  

1205579333 url  3  167  80

You can see the column names easily from the sys.columns view. But you can only see the column type IDs. This requires another join to get the column type names. You may try the "sp_columns" stored procedure to get a better list of columns.

Another way to get a list of columns from a view is to use the "sp_help" stored procedure. "sp_help" returns more than just a list of columns. It returns: the view information, the column information, the identity column, the row GUID column. The tutorial exercise belwo shows you what you will get with sp_help:

EXEC SP_HELP ggl_links_top;

GO

Name   Owner Type Created_datetime

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

ggl_links_top dbo view 2007-05-19 13:43:46.983

Column_name Type  Computed Length Prec Scale Nullable

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

id   int no  4  10 0  yes  

counts  int no  4  10 0  yes  

url  varchar no  80   no 

Identity    

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

No identity column defined.

RowGuidCol

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

No rowguidcol column defined.

If you want get the definition of an existing view back from the SQL Server, you can use the system view called sys.sql_modules, which stores definitions of views and procedures.

The sys.sql_modules holds view definitions identifiable by the object id of each view. The tutorial exercise below shows you how to retrieve the definition of view, "ggl_link_view" by joining sys.sql_modules and sys.views:

USE GlobalGuideLineDatabase;

GO

SELECT m.definition 

 FROM sys.sql_modules m, sys.views v

 WHERE m.object_id = v.object_id

 AND v.name = 'ggl_links_top'; 

GO

definition

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

CREATE VIEW ggl_links_top (LinkText) AS

 SELECT CONVERT(VARCHAR(20),id) 

 + ' - ' + CONVERT(VARCHAR(20),counts) 

 + ' - ' + url 

 FROM ggl_links WHERE counts > 1000

(1 row(s) affected)

If you want to know how an existing view was created, you can use SQL Server Management Studio to automatically generate a "CREATE VIEW" script The following tutorial shows you how to do this:

1. Run SQL Server Management Studio and connect to SQL server.

2. On the Object Explorer window, follow the object tree: Databases > GlobalGuideLineDatabasee > Views > dbo.ggl_links_top.

3. Click right mouse button on dbo.ggl_links_top. The context menu shows up.

4. Select "Script Table as" > "CREATE to" > "New Query Editor Window". The following script will be displayed:

USE [GlobalGuideLineDatabasee]

GO

/****** Object: View [dbo].[ggl_links_top]

 Script Date: 05/19/2007 15:07:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[ggl_links_top] AS

 SELECT id, counts, url FROM ggl_links 

 WHERE counts > 100;

Can You Create a View with Data from Multiple Tables? The answer is yes. A view can be created with a SELECT statement to join data from multiple tables.

It is a common practice to normalize data into multiple tables. Then using a view to de-normalize them into a single output.

The tutorial exercise below shows you how to create a view to normalize data from two tables SalesOrderHeader and Customer in the sample database AdventureWorksLT.

USE AdventureWorksLT;

GO

CREATE VIEW SalesOrderView AS

SELECT o.SalesOrderNumber, o.OrderDate, o.TotalDue,

c.FirstName, c.LastName, c.CompanyName

FROM SalesLT.SalesOrderHeader o, SalesLT.Customer c

WHERE o.CustomerID = c.CustomerID

GO

Can You Create a View with Data from Another View? The answer is yes. A view can be used as a table to build other views. The tutorial exercise below shows you how to create a view using data from another view:

USE AdventureWorksLT;

GO

CREATE VIEW SalesOrderTop AS

 SELECT SalesOrderNumber, TotalDue, CompanyName

 FROM SalesOrderView 

 WHERE TotalDue > 10000.0

GO

SELECT TOP 10 * FROM SalesOrderTop;

GO

SalesOrderNumber TotalDue CompanyName

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

SO71780  42452.6519 Nearby Cycle Shop

SO71782  43962.7901 Professional Sales and Service

SO71783  92663.5609 Eastside Department Store

SO71784  119960.824 Action Bicycle Specialists

SO71796  63686.2708 Extreme Riding Supplies

SO71797  86222.8072 Riding Cycles

SO71832  39531.6085 Closest Bicycle Store

SO71845  45992.3665 Trailblazing Sports

SO71858  15275.1977 Thrilling Bike Tours

SO71897  14017.9083 Paints and Solvents Company

Assuming that you have a table which is used by a view, and you try to delete that table. SQL Server will let you delete the table without any trouble.

But that view will become invalid. The tutorial exercise below shows you what happens to the view, when the underlying table is deleted:

USE GlobalGuideLineDatabase;

GO

SELECT * INTO ggl_links_copy

FROM ggl_links WHERE counts > 0;

GO

CREATE VIEW ggl_links_view AS

SELECT * FROM ggl_links_copy;

GO

SELECT COUNT(*) FROM ggl_links_view;

GO

50015

DROP TABLE ggl_links_copy;

GO

SELECT COUNT(*) FROM ggl_links_view;

GO

Msg 208, Level 16, State 1, Line 1

Invalid object name 'ggl_links_copy'.

Msg 4413, Level 16, State 1, Line 1

Could not use view or function 'ggl_links_view'

because of binding errors.

Sometimes you want the data in a view to be sorted and try to use the ORDER BY clause in the SELECT statement to define the view.

But SQL Server will not allow you to use ORDER BY to define a view without the TOP clause. The tutorial exercise below shows you what error you will get when using ORDER BY in a CREATE VIEW statement:

USE GlobalGuideLineDatabase;

GO

CREATE VIEW ggl_links_top AS

SELECT id, counts, url FROM ggl_links

WHERE counts > 100

ORDER BY counts DESC;

GO

Msg 1033, Level 15, State 1, Procedure ggl_links_top, Line 4

The ORDER BY clause is invalid in views, inline functions,

derived tables, subqueries, and common table expressions,

unless TOP or FOR XML is also specified.

CREATE VIEW ggl_links_top AS

SELECT TOP 100 id, counts, url FROM ggl_links

WHERE counts > 100

ORDER BY counts DESC;

GO

SELECT TOP 3 * FROM ggl_links_top;

GO

id  counts url

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

36470 999966 dgqnvmy onfb

12292 999953  qebmw v qkxqns jnb 

6192 999943 p o qik od

The view seems to be sorted correctly.

The answer is no.

But if the question is "Can you update data in the underlying table through view?" The answer is then yes. SQL Server will allow you to update data in the underlying table through a view. The tutorial exercise below is a good example:

UPDATE ggl_links_top SET url = REVERSE(url) 

 WHERE id = 100001;

GO

SELECT * FROM ggl_links_top;

GO

id  counts url

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

100001 1000001 rendc.org

36470 999966 dgqnvmy pyjqd toqcoupuxortasdtzvcae jonfb

12292 999953  qebmw v qqmywe q kza wskxqns jnb 

SELECT TOP 1 * FROM ggl_links ORDER BY counts DESC;

GO

id  url    notes counts created

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

100001 rendc.org NULL 1000001  2007-05-19

The answer is no.

But if the question is "Can you insert data into the underlying table through view?" The answer is then yes. SQL Server will allow you to insert data into the underlying table through a view with a condition:

* The insert columns must be limited to columns of a single underlying table.

The tutorial exercise below shows you how to insert data into a underlying table through a view:

USE GlobalGuideLineDatabase;

GO

ALTER VIEW ggl_links_top AS

 SELECT TOP 3 id, counts, url FROM ggl_links 

 WHERE counts > 100

 ORDER BY counts DESC;

GO

INSERT INTO ggl_links_top 

 VALUES(100001, 1000001, 'rendc.org');

GO

SELECT * FROM ggl_links_top;

GO

id  counts url

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

100001 1000001 rendc.org

36470 999966 dgqtzvcae jonfb

12292 999953 ggl wskxqns job 

SELECT TOP 1 * FROM ggl_links ORDER BY counts DESC;

GO

id  url    notes counts created

------ ------------------- ----- ----------- ----------
100001 rendc.org NULL 1000001  2007-05-19