Ms Sql Server Technology Discussions

[deals2buy]

[b] Introduction to Partitioning[/b]

Partitioning is the database process or method where very large tables and indexes are divided in multiple smaller and manageable parts. SQL Server 2005 allows to partition tables using defined ranges and also provides management features and tools to keep partition tables in optimal performance.

Tables are partition based on column which will be used for partitioning and the ranges associated to each partition. Example of this column will be incremental identity column, which can be partitioned in different ranges. Different ranges can be on different partitions, different partition can be on different filegroups, and different partition can be on different hard drive disk to improve performance.

Partitions can be set up very easily using schemes and functions as well can manage very easily humongous tables separate indexes for each partition. This will lead to high performance gain. Partitioning can increase performance when hard disk speed is performance bottleneck, if CPU or RAM is bottleneck partitioning will not help much.

[deals2buy]

[b] Database Table Partitioning Tutorial – How to Horizontal Partition Database Table[/b]


Here is simple tutorial which explains how a table can be partitioned.

[b]Step 1 : Create New Test Database with two different filegroups[/b]
I have written tutorial using my C: Drive, however to take advantage of partition it is recommended that different file groups are created on separate hard disk to get maximum performance advantage of partitioning. Before running following script, make sure C: drive contains two folders – Primary and Secondary as following example has used those two folder to store different filegroups.

[CODE]
USE Master;
GO
--- Step 1 : Create New Test Database with two different filegroups.
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1',
FILENAME=
'C:\Data\Primary\TestDB_Part1.mdf',
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2',
FILENAME =
'C:\Data\Secondary\TestDB_Part2.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 );
GO
[/CODE]

[img]http://www.pinaldave.com/bimg/partition1.gif[/img]

[b]Step 2 : Create Partition Range Function[/b]
Partition Function defines the range of values to be stored on different partition. For our example let us assume that first 10 records are stored in one filegroup and rest are stored in different filegroup. Following function will create partition function with range specified.

[CODE]
USE TestDB;
GO
--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION TestDB_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (10);
GO
[/CODE]

[url="http://www.pinaldave.com/bimg/partition2.gif"][img]http://www.pinaldave.com/bimg/partition2.gif[/img][/url] Click on image to see larger image

[b]Step 3 : Attach Partition Scheme to FileGroups[/b]
Partition function has to be attached with filegroups to be used in table partitioning. In following example partition is created on primary and secondary filegroup.

[CODE]
USE TestDB;
GO
--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2);
GO
[/CODE]

[b]Step 4 : Create Table with Partition Key and Partition Scheme[/b]
The table which is to be partitioned has to be created specifying column name to be used with partition scheme to partition tables in different filegroups. Following example demonstrates ID column as the Partition Key.

[CODE]
USE TestDB;
GO
--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (ID);
GO
[/CODE]

[b]Step 5 : (Optional/Recommended) Create Index on Partitioned Table[/b]
This step is optional but highly recommended. Following example demonstrates the creation of table aligned index. Here index is created using same Partition Scheme and Partition Key as Partitioned Table.

[CODE]
USE TestDB;
GO
--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable
ON TestTable(ID)
ON TestDB_PartitionScheme (ID);
GO
[/CODE]

[b]Step 6 : Insert Data in Partitioned Table[/b]
Insert data in the partition table. Here we are inserting total of 3 records. We have decided that in table partition 1 Partition Key ID will contain records from 1 to 10 and partition 2 will contain reset of the records. In following example record with ID equals to 1 will be inserted in partition 1 and rest will be inserted in partition 2.

[CODE]
USE TestDB;
GO
--- Step 6 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
GO
[/CODE]

[b]Step 7 : Test Data from TestTable[/b]
Query TestTable and see the values inserted in TestTable.

[CODE]
USE TestDB;
GO
--- Step 7 : Test Data from TestTable
SELECT *
FROM TestTable;
GO
[/CODE]

[b]Step 8 : Verify Rows Inserted in Partitions[/b]
We can query sys.partitions view and verify that TestTable contains two partitions and as per Step 6 one record is inserted in partition 1 and two records are inserted in partition 2.

[CODE]
USE TestDB;
GO
--- Step 8 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO
[/CODE]

[img]http://www.pinaldave.com/bimg/partition3.gif[/img]

Partitioning table is very simple and very efficient when used with different filegroups in different tables.

[deals2buy]

[b] Rules for Optimizining Any Query – Best Practices for Query Optimization[/b]

This subject is very deep subject but today we will see it very quickly and most important points.

In this article I am not focusing on in depth analysis of database but simple tricks which DBA can apply to gain immediate performance gain.[list]
[*]Table should have primary key
[*]Table should have minimum of one clustered index
[*]Table should have appropriate amount of non-clustered index
[*]Non-clustered index should be created on columns of table based on query which is running
[*]Following priority order should be followed when any index is created a) WHERE clause, JOIN clause, c) ORDER BY clause, d) SELECT clause
[*]Do not to use Views or replace views with original source table
[*]Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
[*]Remove any adhoc queries and use Stored Procedure instead
[*]Check if there is atleast 30% HHD is empty – it improves the performance a bit
[*]If possible move the logic of UDF to SP as well
[*]Remove * from SELECT and use columns which are only necessary in code
[*]Remove any unnecessary joins from table
[*]If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)
[/list]
There are few hardware upgrades can be considered as well like separating index on different disk drive or moving tempdb to another drive. However, I am not suggesting them here as they are not quick way to improve the performance of query.

[deals2buy]

[b] SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL[/b]

The reason I started to use this script to find out longest running query as if query is changed a bit it will display it as new row and new log start time. This way when I am improving T-SQL query or Stored Procedure I can check their progress in the query and does not have to get bothered with previous data.
I always run following DBCC command before I started to use my query. Following DBCC commands clears the cache of the server and starts fresh logging of the query running time.

[CODE]
DBCC FREEPROCCACHE
[/CODE]

Run following query to find longest running query using T-SQL.

[CODE]
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO
[/CODE]

You can also add WHERE clause to above T-SQL query and filter additionally.

[deals2buy]

[b] SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2[/b]


[CODE]
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO
[/CODE]

[deals2buy]

[b] Shrinking NDF and MDF Files – A Safe Operation[/b]

[i][b]Can we shrink NDF and MDF[/b] [b]files?? If you do so is there any data loss?[/b][/i]


[b]Answer:[/b]
[b]Shrinking MDF and NDF file is possible and there is no chance of data loss.[/b]

It is not always advisable to shrink those file as those files are usually growing. There are cases when one database is separated in multiple database of any large table is dropped from database MDF and NDF can contain large empty space. This is the time they should be shrank. Shrinking database can be many hours process but it there are very slim chances of data lose.
Following is the script to shrink whole database.

[CODE]
DBCC SHRINKDATABASE (dbName)
[/CODE]

Following is the script to shrink single file.

[CODE]
DBCC SHRINKFILE (logicalLogFileName)
[/CODE]

To find logicalLogFileName following command has to be ran.

[CODE]
USE dbName
EXEC sp_helpfile
[/CODE]

Let us understand this using database AdventureWorks.

[CODE]
/* Shrink Whole AdventureWorks Database */
DBCC SHRINKDATABASE (AdventureWorks)
GO
/* Get the Logical File Name */
USE AdventureWorks
EXEC sp_helpfile
GO
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
GO
[/CODE]

Following image of the same process show when whole process is done there will be resultset with information about the new states of the database files.

[img]http://www.pinaldave.com/bimg/shrinkdb.gif[/img]

[deals2buy]

[b] Execution Plan – Estimated I/O Cost – Estimated CPU Cost – No Unit[/b]


[b][i]Q: What is the estimated I/O cost?
Q: What is the estimated CPU cost?
Q: Why there is no unit of measurement for estimated costs?[/i][/b]

There are several other questions. However, let me try to answer the above questions today.

[img]http://www.pinaldave.com/bimg/qexe.jpg[/img]

Estimated I/O Cost and CPU Cost are just cost estimations as the names suggest. SQL Server Query Optimizer uses the cost to make the decision on selecting the most efficient execution plan. When any query is executed, the SQL Server Query Optimizer prepares several alternative execution plans to execute the query. Each alternative plans each operation and assign some type of cost on the basis of the heuristic data. This estimated number just implies the amount of work CPU or I/O has to do to complete the task. Due to this reason, there is no unit assigned for these estimations. These estimates should be used by us in the same way by which the SQL Server uses it – The estimate should be used to compare different queries with each other.

[deals2buy]

[b] Find Statistics Update Date – Update Statistics[/b]

Statistics are one of the most important factors of a database as it contains information about how data is distributed in the database objects (tables, indexes etc). It is quite common to listen people talking about not optimal plan and expired statistics. Quite often I have heard the suggestion to update the statistics if query is not optimal. Please note that there are many other factors for query to not perform well; expired statistics are one of them for sure.

If you want to know when your statistics was last updated, you can run the following query.

[CODE]
USE AdventureWorks
GO
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('HumanResources.Department')
GO
[/CODE]

[img]http://www.pinaldave.com/bimg/statsupdate.jpg[/img]

If due to any reason you think that your statistics outdated and you want to update them, you can run following statement. In following statement, I have specified an additional option of FULLSCAN, which implies that the complete table is scanned to update the statistics.

[CODE]
USE AdventureWorks;
GO
UPDATE STATISTICS HumanResources.Department
WITH FULLSCAN
GO
[/CODE]

Please note that you should only run update statistics if you think they will benefit your query and when your server is not very busy. If you have auto update “usually” on, the SQL Server takes care of updating stats when necessary. Here, is a quick example where you can see the updating of older statistics using fullscan.

[img]http://www.pinaldave.com/bimg/statsupdate1.jpg[/img]


Statistics is very deep subject, and a lot of things can be discussed over it.

[deals2buy]

[b] SQL SERVER – 2012 – Multi-Monitor SSMS Windows[/b]


Recently when I was working with SQL Server 2011 ‘Denali’ CTP1, I dragged one of the windows by accident, and suddenly it magically appeared out of its ‘Shell’ of SSMS and was appearing on a separate monitor. I played around a bit and figured out that SSMS now supports multi-monitor (or multi screen) support with single SSMS instance. We can now drag out and drag in any window and resize them at any size. Fantastic!

[img]http://www.pinaldave.com/bimg/multiserver.jpg[/img]

If you are multi-monitor user, I am sure you will like this feature.

[deals2buy]

[b] SQL SERVER – 2012 – Clipboard Ring – CTRL+SHIFT+V[/b]

I found out that there is one more similar feature which existed in Visual Studio is also now part of SQL Server 2012. The feature is called clipboard ring feature.

This is how it works.

Select Multiple object one by one using regular CTRL + X.

Now instead of pasting using CTRL+V use CTRL+SHIFT+V. Well, you will see that that pasted value is rotating based on what you have earlier selected in CTRL+V. I was really happy as I think this is one of the feature of VS, I really wanted SSMS to have.

[deals2buy]

[b] Basic Explanation of SET LOCK_TIMEOUT – How to Not Wait on Locked Query[/b]

When any connection starts the value of the SET LOCK_TIMEOUT is -1, which means that the query has to wait for infinite time for the lock to be released on another query. If you want to simulate the scenario of SET LOCK_TIMEOUT to match NOWAIT query hint, it should be set to value 0. Let us see a similar example where we demonstrate how SET LOCK_TIMEOUT works.

First Let us create a table:

[CODE]
USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID, Col1)
VALUES (1, 'First')
GO
[/CODE]

Now let us open two different connections.

Run following command in the First Connection:

[CODE]
BEGIN TRAN
DELETE FROM First
WHERE ID = 1
[/CODE]

Run following command in the Second Connection:

[CODE]
SET LOCK_TIMEOUT 2000
BEGIN TRAN
SELECT ID, Col1
FROM First
WHERE ID = 1
[/CODE]

[img]http://www.pinaldave.com/bimg/set-lock-timeout.jpg[/img]

In this case, I have set the value of the SET LOCK_TIMEOUT to 2000 milliseconds. This query will wait for 2 seconds to another query to release the lock. If another query does not release the lock in 2 seconds, it will display the following error:

[color=#ff0000]Msg 1222, Level 16, State 45, Line 3[/color]
[color=#ff0000]Lock request time out period exceeded.[/color]


Here are a couple of very interesting differences between SET LOCK_TIMEOUT and NOWAIT query hint.[list]
[*]User can configure the time to wait before throwing error in SET LOCK_TIMEOUT, whereas in case of NOWAIT it is always zero (o).
[*]The scope of the SET LOCK_TIMEOUT is wholly connected however the scope of NOWAIT is the table where it is applied.
[/list]

[deals2buy]

[b] Update statistics in parallel[/b]


To update statistics on a whole database, there is the T-SQL command, [url="http://msdn.microsoft.com/en-us/library/ms173804.aspx"]sp_updatestats[/url]. This T-SQL statement works serially on one table at a time, so it is not very fast. Sometimes it would be nice to update statistics on an entire database using not just one, but several threads. The solution is the SSIS package UPDATE_STATS_PARALLEL.dtsx, which creates dynamically and launches another SSIS package. The second package contains as many independent Execute SQL Tasks as needed to update statistics in parallel.

The UPDATE_STATS_PARALLEL.dtsx has several variables that are used as input parameters to control the behaviour of the generated SSIS package. These are:

[b]INSTANCE_NAME[/b]: name of the SqlServer Instance

[b]DEST_DB[/b]: name of the database to perform update statistics on , and of the dynamically generated SSIS package.

[b]NUM_OF_THREADS[/b]: maximum number of threads of the generated dtsx

[b]SOURCE_SELECT[/b]: The T-SQL statement that generates the list of tables to update statistics on. This must return two columns: [tname] and [comando]. These are the name of the table and the command to be run on the table, respectively. Here is one example :

[CODE]
/*
Update statistics on tables not empty and with statistics older than 3 days
*/
select
t.name as [tname]
, ' update statistics ['+t.name+'] ' [comando]
from sys.tables t
left join sys.stats s
on t.object_id = s.object_id
join ( select OBJECT_ID
, sum(rows) rows
from sys.partitions
group by object_id
having SUM(rows) >0
) pa
on t.object_id = pa.object_id
where ( STATS_DATE(t.object_id,stats_id) is null
or datediff(DAY, STATS_DATE(t.object_id,stats_id), GETDATE() ) >=3 )
group by t.name
order by 1
[/CODE]

The criteria in the where clause can be modified as needed.

Here is picture of the UPDATE_STATS_PARALLEL.dtsx package

[img]http://www.sqlservercentral.com/Images/17224.jpg[/img]

The most important part of UPDATE_STATS_PARALLEL.dtsx is the VB script named "Create DTSX". This script creates an SSIS package named ValueOf(DEST_DB).dtsx.

The code used is shown below.
[CODE]
Public Sub Main()

Dim packageName As String = Dts.Variables("DEST_DB").Value.ToString

app = New Application()

Dts.Events.FireInformation(0, "Update Stats Package", packageName, "", 0, True)

Dim destDbConnectionString As String = Dts.Variables("DEST_CONNECT").Value.ToString

'Create package
dataTransferPackage = New Package()
dataTransferPackage.LocaleID = 1029


'Create DestConection
Dim destConn As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = dataTransferPackage.Connections.Add("OLEDB")
destConn.Name = "DestDB"
destConn.ConnectionString = destDbConnectionString

workDestConn = New ServerConnection(Dts.Variables("INSTANCE_NAME").Value.ToString)
workDestConn.DatabaseName = Dts.Variables("DEST_DB").Value.ToString


Dim tablesDS As DataSet = workDestConn.ExecuteWithResults(Dts.Variables("SOURCE_SELECT").Value.ToString)
Dim tablesDT As DataTable = tablesDS.Tables(0)

' For each row in the dataset, add an ExecuteSQLTask that performs update statistics
For Each tablesR As DataRow In tablesDT.Rows
Dim sqlcomandosource As String = tablesR.Item("comando").ToString
'Dts.Events.FireInformation(0, "Creating components for table", "[" + schemaName + "].[" + tableName + "]", "", 0, True)
Dim UpdStatsTask As Executable = dataTransferPackage.Executables.Add("STOCK:SQLTask")
Dim thError As Microsoft.SqlServer.Dts.Runtime.TaskHost = CType(UpdStatsTask, Microsoft.SqlServer.Dts.Runtime.TaskHost)
thError.Name = "Stats " + tablesR.Item("tname").ToString
thError.Properties("Connection").SetValue(thError, dataTransferPackage.Connections("DestDB").Name)
thError.Properties("SqlStatementSourceType").SetValue(thError, 1)
thError.Properties("SqlStatementSource").SetValue(thError, sqlcomandosource)
thError.Properties("DelayValidation").SetValue(thError, True)
' nnn thError.Properties("TimeOut").SetValue(thError, CUInt(50))
'Dts.Events.FireInformation(0, "Error task created for table", "[" + schemaName + "].[" + tableName + "]", "", 0, True)

Next


dataTransferPackage.MaxConcurrentExecutables = Dts.Variables("NUM_OF_THREADS").Value.ToString

dataTransferPackage.MaximumErrorCount = 100

dataTransferPackage.DelayValidation = True


app.SaveToXml(packageName + ".dtsx", dataTransferPackage, Nothing)

Dts.TaskResult = ScriptResults.Success
End Sub
[/CODE]
Here is a picture of how the dynamically generated SSIS package might look. The package as many independent Execute SQL Tasks as the rows in the result set from the variable SOURCE_SELECT.

[img]http://www.sqlservercentral.com/Images/17225.jpg[/img]

If this generated package runs, it will execute simultaneously up to [b]NUM_OF_THREADS [/b]of these tasks. It will be much faster, and also more resource intensive, than the built-in T-SQL command, sp_updatestats.

After creating this package, the master package, UPDATE_STATS_PARALLEL.dtsx, executes the package using an SSIS ExecutePackageTask. The master package then deletes the child package using an SSIS FileSystemTask.

In the attachment there is also a T-SQL stored procedure that can be used to launch UPDATE_STATS_PARALLEL.dtsx using a T-SQL command. This is an optional part of the proposed solution .

[b] Resources:[/b]

[url="http://www.sqlservercentral.com/Files/sp_Update_Statistics_Parallel_byDTSX.sql/17182.sql"]sp_Update_Statistics_Parallel_byDTSX.sql[/url] | [url="http://www.sqlservercentral.com/Files/UPDATE_STATS_PARALLEL.dtsx/17183.dtsx"]UPDATE_STATS_PARALLEL.dtsx[/url]

[deals2buy]

[b] SQL Server 2012 Build List[/b]

This is a list of the builds for SQL Server 2012. There are [url="http://www.sqlservercentral.com/articles/Build+List/71065/"]other build lists available here[/url].

All builds are listed in reverse order, so the newest are at the top and the earliest at the bottom. You can find your build number with:

[CODE]
select @@Version
[/CODE]

in a query window. This gives you the current version you are running and it should match up with one of the builds listed below.

Note that Service Packs include all builds below that Service Pack level. So any builds listed below Service Pack 1 are included in Service Pack 1. Those below Service Pack 2 (including Service Pack 1), are included in SP2, etc. Hotfixes may or may not include updates below them because there are two trees of code. The cumulative updates do include all hotfixes before them.

You can also download a CSV of the builds attached to this article.

[b] Resources:[/b]

[url="http://www.sqlservercentral.com/Files/BuildList_SQLServer2012.xls/17412.xls"]BuildList_SQLServer2012.xls[/url]

[deals2buy]

What is the difference between COALESCE and ISNULL? (choose 2)

[b]Answer: [/b][list]
[*]Coalesce can take more than two parameters, and IsNULL takes exactly two parameters
[*]The result returned by coalesce depends on data type precedence
[/list]
[b]Explanation: [/b]COALESCE can use a variable number of paramters, where ISNULL takes just two parameters. The return type of COALESCE does depend on the data type precedence rules and only COALESCE is an ANDI standard function.

Ref: [url="http://www.sqlservercentral.com/links/1427054/291013"]http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx[/url]

[deals2buy]

[b] My view isn’t reflecting changes I’ve made to the underlying tables[/b]

Problem: You’ve added columns to the base table of one of your views, but the view isn’t reflecting the change.

Over the years I’ve seen lot’s of views created similar to this one.

[CODE]
CREATE VIEW vw_TableView AS
SELECT * FROM TableName
[/CODE]

Generally the argument is that if I put “SELECT *” rather than an explicit field list, then when my table changes so will my view. Unfortunately it doesn’t work that way.

Let’s try an example.

Create a test table and populate it with some data.

[CODE]
CREATE TABLE TableName (Column1 varchar(10))
GO
INSERT INTO TableName VALUES ('abcdefg')
INSERT INTO TableName VALUES ('hij')
INSERT INTO TableName VALUES ('klmnop')
INSERT INTO TableName VALUES ('qrstuvwxy')
INSERT INTO TableName VALUES ('zabcde')
INSERT INTO TableName VALUES ('123456')
GO
[/CODE]

Create a test view.

[CODE]
CREATE VIEW vw_TableView AS
SELECT * FROM TableName
GO
[/CODE]

Test the view to make sure we are getting the data we expect.
[CODE]
SELECT * FROM vw_TableView
GO
[/CODE]
So far so good. The output is exactly what we expected. Now let’s add a column to the table and populate it.
[CODE]
ALTER TABLE TableName ADD Column2 INT
GO
UPDATE TableName SET Column2 = 3
GO
[/CODE]
And try out the view again.
[CODE]
SELECT * FROM vw_TableView
GO
[/CODE] [b]Column1[/b] abcdefg hij klmnop qrstuvwxy zabcde 123456

Now wait just a minute. The output I’m getting looks exactly like it did before I added Column2. All I’m seeing is Column1. Now the first thing I do when debugging something like this is make sure the view should in fact be pulling the new column. So:

[CODE]
EXEC sp_helptext vw_TableView
Text
---------------------------------------------------------------
CREATE VIEW vw_TableView AS
SELECT * FROM TableName
[/CODE]

Ok, so the code still looks correct. So why aren’t we pulling all of the columns even though we are using a *? From what I understand the metadata for the view is not automatically updated when the tables are modified.

The fix is to either drop and re-create or alter the view or to use the sp_refreshview stored procedure. Sp_refreshview has the combined benefit of being the simplest method and not messing up any explicit permissions on the view caused by dropping it.
[CODE]
EXEC sp_RefreshView vw_TableView
GO
[/CODE]
And test the view again.
[CODE]
SELECT * FROM vw_TableView
GO
[/CODE] [b]Column1[/b] [b]Column2[/b] abcdefg 3 hij 3 klmnop 3 qrstuvwxy 3 zabcde 3 123456 3

And now we have the correct number of columns for our view.

Next let’s try going the other way. We remove a column from the table.
[CODE]
ALTER TABLE TableName DROP Column2
GO
[/CODE]
And we try querying the view again. (I’m hoping no one expects it to work correctly.)
[CODE]
SELECT * FROM vw_TableView
GO
[/CODE]
This time we get an error.
[color=#ff0000] Msg 4502, Level 16, State 1, Line 1
View or function 'vw_TableView' has more column names specified than columns defined.[/color]
If we again run sp_refreshview then the view will once again show the expected data.
[CODE]
EXEC sp_RefreshView vw_TableView
GO
SELECT * FROM vw_TableView
GO
[/CODE] [b]Column1[/b] abcdefg hij klmnop qrstuvwxy zabcde 123456

And last but not least some cleanup code.
[CODE]
DROP VIEW vw_TableView
DROP TABLE TableName
GO
[/CODE]