Precalculated SQL Views data

Question: I have a SQL View returning data .The SQL Server View is not schema bound. Normally users do a SELECT col1,col2,col3 FROM MYVIEW . The data sets have grown and the users want to start filtering data , such as SELECT col1,col2,col3 FROM MYVIEW WHERE COL1 > xxxx. If the users filter data on the SQL data , will all the data be returned via the VIEW and then filtered or will just the filtered data be returned? Answer: A View is a pre-defined query. The Microsoft documentation defines a View as “virtual table whose contents are defined by... Read more →

Msg 229 Level 14 State 5 - SQL VIEW permissions and underlying tables

Question:Can a user return a recordset from a VIEW , if the underlying tables have not being given explicit read rights? I’ve received this error message when attempting to access a VIEW Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'musicians_VIEW', database 'MY_DB', schema 'dbo'. Answer:A user does not require explicit SELECT permissions on table to view data through a VIEW, as long as the user has SELECT permissions on the VIEW. Granting the user SELECT permissions on the VIEW will stop this error message appearing. In a situation of granting permissions... Read more →

Use SQL Materialized view to eliminate DISTINCT and speed up response time

Question: Users are complaining this query is slow and is creating a bottleneck. The query includes a subquery which relies on the DISTINCT keyword. The Execution Plan indicates the highest cost is related to the DISTINCT . How can I speed up the query response time? --SQL Server parse and compile time: -- CPU time = 31 ms, elapsed time = 116 ms. --SQL Server Execution Times: --CPU time = 29500 ms, elapsed time = 36388 ms. --SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SELECT NumbersCodeId, sStatsDescription, sStatsDescriptionCode, NumbersRoutineId, NumbersTypeId FROM... Read more →

Select all SQL Server Views

Question: How can I list all all SQL Views in a SQL Server database? Answer: Use the sys.objects system SQL View and the type column to choose just the Views. Try not to use the sys.sysobjects sy as it’s a deprecated view , to be decommissioned at some point in the future USE Database_name GO --Method 1 select [name] from sys.objects where type = 'V' order by [name] --Method 2 select * from sys.views --Method 3 select * from information_schema.tables where table_type = 'view' Read More SQL Server Materialized Views SQL Server – refresh all Views Read more →