Sql server updating data in a view sedating dogs on

Posted by / 03-May-2016 02:55

Sql server updating data in a view

Find out when statistics were last updated and changes since the last update.Use SQL Update Statistics’ point and click interface to initiate table and index statistics updates quickly and easily.In my script, I currently have it limited to pull only the tables that have nchar types, assuming that the views that are fed by the tables will know to update their values. TABLE_NAME) where cols.data_type ='nchar' and tabs. TABLE_TYPE = 'BASE TABLE' open c fetch next from c into @tn, @cn, @ln while @@FETCH_STATUS = 0 begin set @sql = 'alter table ' @tn ' alter column ' @cn ' nvarchar(' convert(nvarchar(50), @ln) ')' exec sp_executesql @sql set @sql = 'update ' @tn ' set ' @cn ' = LTRIM(RTRIM(' @cn '))' exec sp_executesql @sql fetch next from c into @tn, @cn, @ln end close c deallocate c CREATE TABLE dbo.x(a INT, b NCHAR(4)); GO CREATE VIEW AS SELECT a, b FROM dbo.x; GO ALTER TABLE dbo.x ALTER COLUMN a TINYINT; ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4); GO SELECT a,b INTO #blat FROM dbo.vx; GO EXEC sp_columns N'#blat'; GO DROP VIEW dbo.vx; DROP TABLE dbo.x, #blat; so that I can't alter a table without knowing about the views and other objects it affects - this can make cowboy development/deployment painful, but I guess that's kind of the point).Is this assumption correct, or should I be including views in this list as well? You can build the script to refresh all referenced views dynamically like this (this is for a single table; you'll need to incorporate this into your existing script to make it dynamic for all affected tables): DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql = N'EXEC sp_refreshview ''' QUOTENAME(s.name) '.' QUOTENAME(v.name) ''';' FROM sys.sql_expression_dependencies AS d INNER JOIN sys.views AS v ON d.referencing_id = v.[object_id] INNER JOIN sys.schemas AS s ON v.[schema_id] = s.[schema_id] WHERE d.referenced_id = OBJECT_ID('dbo.whatever') GROUP BY s.name, v.name; EXEC sp_executesql @sql; CREATE TABLE dbo.x(a INT, b NCHAR(4)); GO CREATE VIEW AS SELECT * FROM dbo.x; GO SELECT * INTO #b1 FROM dbo.vx; GO ALTER TABLE dbo.x ALTER COLUMN a TINYINT; ALTER TABLE dbo.x ALTER COLUMN b NVARCHAR(4); GO SELECT * INTO #b2 FROM dbo.vx; GO ALTER TABLE dbo.x ADD d INT; GO SELECT * INTO #b3 FROM dbo.vx; GO EXEC sp_rename N'dbo.x.b', N'c', N'COLUMN'; EXEC sp_rename N'dbo.x.d', N'b', N'COLUMN'; GO SELECT * INTO #b4 FROM dbo.vx; GO EXEC sp_columns N'#b1'; EXEC sp_columns N'#b2'; EXEC sp_columns N'#b3'; EXEC sp_columns N'#b4'; GO DROP TABLE #b1, #b2, #b3, #b4; GO -- initial: #b1____ a int 10 #b1____ b nchar 4 -- correct: #b2____ a tinyint 3 #b2____ b nvarchar 4 -- missing new column d: #b3____ a tinyint 3 #b3____ b nvarchar 4 -- missing column c, b still points at "old" b: #b4____ a tinyint 3 #b4____ b nvarchar 4 Great answer in several ways: made it understandable, gave an executable example, and provided two other tips I hadn't heard of before. I'll be putting all of those (including testing with test tables, etc.) to use in the future.Keep in mind, however, that you might want to avoid indexing a view if the underlying data is updated frequently because the index itself will have to be constantly updated, which can impact the performance of other operations.

The updatability of views may be affected by the value of the CREATE TABLE t1 (x INTEGER); CREATE TABLE t2 (c INTEGER); CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1; CREATE VIEW vup AS SELECT * FROM t2; CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c; Additional discussion and examples follow.

You can also use views as a security mechanism to restrict the data available to end users.

Views can also aggregate data (particularly useful if you can take advantage of indexed views), and help partition data.

The fields in the view’s virtual table are the fields of one or more real tables in the database.

You can use views to join two tables in your database and present the underlying data as if the data were coming from a single table, thus simplifying the schema of your database for users performing ad-hoc reporting.

sql server updating data in a view-59sql server updating data in a view-72sql server updating data in a view-22

Select database properties and table statistics to update, as well as, when to update statistics and sample size.

One thought on “sql server updating data in a view”