1
vote
1answer
65 views

Performance hit with column alias

I have a database in SQL Server 2008, with the final data table having about 2 million rows. The development team is implementing a new application and they have suggested changing the column name to ...
2
votes
1answer
40 views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...
2
votes
0answers
20 views

Setting up triggers based on exposed columns in a view's source table

I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view CREATE VIEW [dbo].[iv_EmployeesView] AS SELECT EMPLOYEE_GUID AS EmployeeGuid, ...
1
vote
2answers
69 views

Fixed database paths in stored procedures/views - is there a smoother way of doing this?

Our developers love to create lots of different stored procedures/views, which extracts data from lots of different databases on different servers. Every time we migrate to a new database server, ...
5
votes
2answers
498 views

Frequency distributions segmented by a field

Some background: I have a sample population data file. Each record in the data file has a frequency weight (FIELD NAME: wgt) indicating how many times the records needs to be replicated to get the ...
3
votes
2answers
3k views

SQL SERVER - Linked Server and query performance

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =). I have a database K with 3 linked servers ...
4
votes
2answers
77 views

Partitioned Views - Storage of Indexes

I have a single table that contains 4 billions rows that I plan on splitting up in to separate tables for each Quarter of the year. Then I want to create a partitioned view across all tables. At the ...
2
votes
2answers
404 views

View column has an 8000 character limit?

In SQL Server 2008 R2 we are using an updateable view to update a varchar(max) column. When we update the column with text and then check the number of characters, it is showing a length of 8000 ...
1
vote
1answer
103 views

Does the SQL Server Backup Database Command also backup views from a Database?

I have a backup job (SQL Server Agent job) which will iterate through all of our SQL Server databases in SSMS, invoke the BACKUP DATABASE command to create a .bak file on a different server. This ...
2
votes
2answers
78 views

View Disc Usage

Is this statement correct? Behind the scenes SQL Server actually stores the data in the view as a physical table which must be updated if any of the data behind the view changes I did some ...
2
votes
1answer
273 views

Indexed view not being used - SQL Server [duplicate]

I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
4
votes
1answer
551 views

Join To a @Table Variable is running ineficiently

Alright so I have a report sproc that was running incredibly slow. Customers were complaining that the report would not run so I started investigating exactly where in the sproc the problem was and I ...
1
vote
1answer
88 views

Is it a best practice to contrast table and dependent view metadata to target which views to refresh?

We have non-indexed views created on vendor tables. When they change table structure or column definitions we run sp_refreshview as needed. What is the viability of a script to contrast the table ...
4
votes
1answer
235 views

Adding indices to views

I have a table that contains XML data in a varchar type field. This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table. ...
2
votes
2answers
115 views

Migrating SQL Server 2000 to SQL Server 2008

I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause? Thanks.
4
votes
1answer
495 views

Determining whether column in view can only contain unique values

I have a SQL Server 2008 database with several views. I'm looking for a way to definitively determine whether a particular view has a column that can only contain unique values (i.e. can be used as a ...
13
votes
1answer
1k views

Execution plan shows expensive CONVERT_IMPLICIT operation. Can I fix this with indexing or do I need to change the table?

I have a really important, really slow view which includes some really ugly conditions like this in its where clause. I am also aware that the joins are gross and slow joins on varchar(13) instead of ...
2
votes
1answer
2k views

Grant select to dbo view without select on non dbo tables

SQL Server 2008 R2 We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs - it has Public access only to the database. We then granted it SELECT on a dbo ...
2
votes
2answers
5k views

SQL query to return a column per date in date range?

Let me explain what I've got and what I'm trying to achieve. Let's say I have Table A: BookingsPerPerson Person_Id ArrivalDate DepartureDate 123456 2012-01-01 2012-01-04 213415 ...
2
votes
2answers
723 views

IN Clause causes Execution plan to change from Nested Loops to Hash Match

I'm tuning a query and have discovered some behaviour I'm not clear about. If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes. There only 7 rows returned in the ...
4
votes
2answers
1k views

SQL Server Linked Server to Progress is slow with an openquery view

We have a SQL Server database setup with a Linked Server setup connecting to a Progress OpenEdge database. We created a SQL Server view (for using with SSRS) of some of the OpenEdge tables using code ...
5
votes
2answers
710 views

Create index view with self join

I use SQL Server 2008 R2 and know that in Indexed View cant have self join. I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between ...
1
vote
1answer
158 views

What is a good way to query data stored in multiple identical tables split by date/time

I have a 3rd party piece of software which records data samples from industrial equipment. This data logging software stores data in sample data tables with approximately one day's worth of samples ...
3
votes
2answers
9k views

How to add a user with access to a single view?

I'm working with MSSQL Server Management Studio 2008 and I need to expose a view to a third party for their data reconciliation. I have created the appropriate view but I'm having trouble creating a ...
5
votes
2answers
1k views

how does SQL Server know to lock view objects?

Not sure whether this is a more or less appropriate place to ask this question, originally posed at Stack Overflow. In SQL Server 2008 I have a view V over tables A and B that looks roughly like ...
5
votes
1answer
2k views

How to make a union view execute more efficiently?

I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process ...
2
votes
1answer
256 views

Speeding Up Querying Views

We've got a complicated stored procedure. The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries. I can't add indexes to it. So, I want to cheat. By ...
12
votes
3answers
2k views

Are Views optimized when I add a WHERE clause to them?

Does it make a difference if you filter a View inside or outside the View? For example, is there any difference between these two queries? SELECT Id FROM MyTable WHERE SomeColumn = 1 Or SELECT Id ...
2
votes
1answer
183 views

Views - Indexes/Keys/Execution Plans

I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server. There is a ...
-1
votes
1answer
2k views

sql server - could not allocate space for object

I am trying to retrieve the first 1000 records of my view and am getting this error message: Msg 1105, Level 17, State 2, Line 2 Could not allocate space for object 'dbo.SORT temporary run ...
4
votes
2answers
2k views

sql 2008, select from a view as a different user, like owner

In sql server 2008, the system views sys.database_role_members and sys.database_principals, only returns the users and group that the current account is allowed to view. I need to check id a certain ...
0
votes
1answer
425 views

Using View filters for tenants in SQL-Server 2008

I'm currently creating a Multi-tenant data architecture in a SaaS BI-product. The product are using shared database and shared schema, but with tenant view filters and tenant data encryption ...
14
votes
3answers
3k views

Determining how a schema change occurred?

Something bad happened yesterday. A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this ...
1
vote
1answer
143 views

How should row-specific metadata be created handled for an outer join view?

I have two tables, a large (millions) table of Tickets for orders, and a small (hundreds of thousands) table for Invoices metadata on a subset of those tickets. The metadata is information about when ...
2
votes
1answer
89 views

Is it possible to create a database called C that is a view into all tables in database A + B?

Using SQL 2005 or 2008, is it possible to create a database called C that is a view into all tables in database A + B ? Databases A, B, and C are on the same local server. I am wanting to be able ...
1
vote
2answers
1k views

Allow db_owner to add/view existing logins to database

On our development server (SQL 2008 R2) we want local dbo's to be able to add existing logins on the server to the database. The problem is they can't view them, they can only see themselves in the ...
2
votes
2answers
390 views

Creating a view on a non-existing view

I have a whole bunch of views that I need to create. Many of these views depend on other views. If I create a view that depends on a view not yet created, I get an Invalid Object error. Rather than ...
5
votes
1answer
303 views

Does sql server calculate views every time when someone query the views?

I wonder whether sql server calculate views every time when someone query the views. It is because everytime I query the views, it take such a long time. And when I tried to shorten the time by ...
3
votes
3answers
2k views

How to create a view that refresh automatically

I was triying to create a View by the simplest way like this: Use SoccerDB; GO CREATE VIEW ExampleDBaseII AS SELECT ID, Cast(Name AS Varchar) as Name,Cast(City AS Varchar) as City, FROM Team GO ...
1
vote
2answers
668 views

SQL Server 2000: Can't DROP VIEW?

I have SQL Server 2000. I want to clean it by dropping obsolete and unused objects. One of the views causes me an error. I try to give more information but I still have no clue what the cause is. ...
0
votes
2answers
2k views

Hide column if any value is null or zero via proc sql or view [closed]

Given either of the two following datasets Kağıt Cinsi Sarı Yeşil Mavi Kalın 4 0 5 İnce null 0 3 or Kağıt Cinsi ...
2
votes
2answers
1k views

Set permissions on a view

I'm creating a view on a SQL Server 2005 database via SQL Server Management Studio 2008, the view reads from two tables. The view is accessed from a web application and I want users to be able to ...
12
votes
3answers
4k views

Is nested view a good database design?

I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
3
votes
2answers
1k views

sys.database_files / sys.filegroups VS sys.sysfiles / sys.sysfilegroups

These catalog views seem to show the exact same data. What is the point of having multiple system views that seem to show the same thing? Here is how I proved it is the same data: select ...
8
votes
3answers
1k views

What are the alternatives for an ORDER BY clause in a View?

This question just had to be in this site :) ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view. I know that there are ...
2
votes
2answers
264 views

Search on part of a column in a SQL Server cross-database view

I'm currently writing an application that interfaces with a 3rd party vendor's SQL Server 2008 database that I only have read access to. We have a separate database that contains cross database views ...
5
votes
2answers
6k views

How do I switch off SCHEMABINDING for a view without recreating it?

How do I switch off SCHEMABINDING for a view without recreating it?
9
votes
5answers
2k views

How do I query this 20 million record view faster?

For a search functionality I am using a view that has the records from all the tables within which I need to search for. The view has almost 20 million records. Searches against this view are taking ...