Should I Create Indexes On Views Or Tables

May 22, 2007

I have a pretty large database that has tables that will contain millions of rows of records. I will predominantly be using Views just to select the data. (I will not be performing any updates or inserts). I propose creating indexes on the views. My question is - if I create indexes on my views, do I have to create them on the tables as well? Is it good practice to create indexes on tables by default even if I am not going to be performing select statements directly on my tables but via my indexed views? Any advice is appreciated.

Thanks
Ran

View 4 Replies


ADVERTISEMENT

How To List Tables,indexes,views Etc...

Jun 16, 2003

Hi,

I want to list the table names in a database "mydev_db".What would be the query ?.

I want to run a similar query to find out the indexes,views,stored_procs etc.



Regards,
Copernicus

View 3 Replies View Related

DB Engine :: How To Find List Of Indexes On Tables On Which Views Has Been Created

Aug 28, 2015

The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.

query to find list of indexes on the tables on which the views has been created.

View 4 Replies View Related

Create Views Not Tables

Jun 9, 2004

I want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??

Thanks, John :eek:

View 1 Replies View Related

Can Not Create Views, Tables Etc * URGENT*

Jul 20, 2005

Hi,I am not getting option as 'new view' and 'new table' when I rightclick onviews and tables option in VisualStudio.net IDE server explorersqlservers database to create new objects.Looks like some setup issue in my database.Thanks for your help in advance.RgdsCV

View 1 Replies View Related

Unable To Create/edit Views, Tables.. From VS .NET

Mar 21, 2005

Hi,
I'm working with few examples from a 70-315 exam prep book.
I'm trying to view the Table design... edit/ create views from VS. NET environment.
But when i right click on the View node under Data Connection tree i get only "Refresh" & "Properties". According to this book.. i should be able to create new views from here.
I'm working with a SQL Server 2000 local installation. VS .NET is connected to the DB, as i tried other examples and i'm able to read from the DB.
My SQL server installation is a 120 days eval installation... has this got anything to do with the behavior ??
I also reied to establish a connection with SQL server 'sa' login... i could connect, but again i wasnt able to create 'views' access tables design.
Any help would be appreciated.

Thanks.

View 1 Replies View Related

DB Design :: Create Views From 2 Tables One Of Which Is Lookup Table That Will Give Column Names

Aug 5, 2015

I am trying a create views that would join 2 tables:

Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape

I want to create a view that looks like

ID, Color, Size, Flavor, Shape

View 4 Replies View Related

Removal Of Selected Indexes / Script Index Create For List Of Indexes

Jul 1, 2014

I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).

Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.

I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.

As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?

View 5 Replies View Related

EAV Performance And Clustered Indexes On Views

Jan 25, 2008

OK so I have this EAV system on a server that is old enough for kindergarten. Insanely enough, this company that makes more money than any of your gods can not buy me a new box.

Before you say "redesign", I need funding allocated for that. See my first statement.

Anywho, I have this page that touches the dreaded Value table and does a clustered index seek on it. Can't search faster than that, right? Well I am getting some funding for "performance tuning". I am wondering if maybe incorporating some clustered index views involving the value table and producing a smaller clustered index for it to seek may alleviate some of this. Any thoughts?

View 10 Replies View Related

SQL Server 2014 :: Indexes And Views - Manage Date Filtering Within A Query?

May 21, 2014

I have a dynamic SQL query that uses various indexes and views.

When a user wants to filter records based on last one day, last one week, last 30 days ...etc.. i use the following code:

@date is an integer.

begindate is datetime format.

begindate > cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20))

The above code slows my query performance by at least 400%!

Would it be faster to add a computed column to my table, using the suggested method in the link below?

[URL] ....

Then i could add an indexed view to improve performance, or can this be done another way?

View 9 Replies View Related

Create Indexes In SQL 7.0

Mar 13, 2000

When creating the indexes on the table, SQL 7.0 also created some type of system indexes ( ex. _WA_Sys_curr_int_rate_2C88998B nonclustered, statistics, auto create located on PRIMARY, etc. ). I want to know how can I clear up these system indexes.

View 1 Replies View Related

Create Indexes

Mar 3, 2006

Slayer writes "Probably a really lame question but in the absence of our DBA I have been asked to create a couple of indexes on a table. No problem with that, just need to know if this is a safe operation whilst users are using the database.

Thanks for your help"

View 2 Replies View Related

Does 7.0 Create Permanent Indexes On Its Own??

Oct 29, 1999

Hello,

A few months ago, the company I work for switched to SQL 7.0 from 6.5. All is running fine but recently I noticed that we have a ton of indexes on each table that start like this: _WA_SYS_....0B679CE2 (here are some specific examples: _WA_SYS_PRODUCT_LINE_0B679CE2, _WA_SYS_RMA_DISPOSITION_0B679CE2)

Did SQL 7.0 do this? If so why? What is it trying to do? Why doesn't it use current indexes (clustered and non-clustered)? Why don't they get Fragmented like User defined indexes? I have a lot of questions associated with this if someone can explain it.

Tech Net, MSDN, BOL and other manual resources don't mention anything about it. At least I haven't found anything on it and I have spent some time looking.

Please help if you can.
Troy

View 2 Replies View Related

SQL 2012 :: Create New Indexes

May 24, 2014

1)When we create Indexes, key columns are the columns that use in where clause and included columns are the columns that can be used in the select list and on join clause column.

2) I am thinking that we have to create new Index, only if we found at least 50 msec time save.

View 9 Replies View Related

Create Indexes Within SSMS

Jun 15, 2008

I found out how to create clustered index using TSQL, but how can you create clustered index and manage indexes using SQL Server Management Studio?

Thanks
Goldmember

View 2 Replies View Related

Quicker Way To Create Indexes

Jul 18, 2006

Hi,I have a new job. It needs to drop and re-create (by insert) a tableevery night. The table contains approximately 3,000,000 (and growing)records. The insert is fine, runs in 2 minutes. The problem is thatwhen I create the indexes on the table, it is taking 15-20 minutes.There is one clustered index and 11 non-clustered. This is a lookuptable that takes many different paremeters, so it really needs theindexes for the user interface to run efficiently. However, thedatabase owners aren't keen on a job taking 20 minutes to run everynight.Any ideas?

View 5 Replies View Related

Re-Create All Indexes In Database

Jun 17, 2006

First, I'd like some help stablishing if it's a good idea or not what I'm thinking, and second is what would be the easyist way to achive the result.

Background. We have a multicompany ERP system (200GB, 200+ tables). All data tables have a companyid, counter1id, counter2id, fields. Each table has a primary clusterd index on these fields in that order. And also has multiple other indexes that usually include companyid filed first. ( Note we have 10 companies, ids are 1..10, companies 2 and 5 are the most active, inserts, selects ). MY GUESS IS THAT THIS IS VERY BAD HAVING COMPANYID FIRST. AM I CORRECT? LEADS TO AL LOT OF FRAGMENTATION? VERY BAD TO FIND DATA ON THE INDEXES?


I want to re-create all my indexes and have them counter2id, companyid, counter2id. ( counter1id is a counter for each company, and counter2id is a global counter like identity ) GOOD OR BAD IDEA?

Since I have hundreds of indexes what is the correct procedure? I was thinking on scripting the database, then dropping all indexes, then recreate clustered indexes, then recreate other indexes. Do I have to take care of anything else foreing relationships, etc.

View 6 Replies View Related

Using EM To Create Views

Jun 6, 2000

When I create a view using Enterprise Manager, I can click on the 'Add Table' button to select a table. Is there any way I can tell EM that I want to include a table from another server?

Thanks,

Randy

View 1 Replies View Related

Auto Create Statistics / Indexes

Sep 1, 2000

Hi everyone,

I know that statistics called _WA_... are created on tables when auto create statistics is set on a database. Is this an indication that queries against the table would perform better if indexes were created on the columns in question? (The tables I'm interested in optimising are used equally for transactional querying and reporting)

Thanks for any replies!

Les

View 1 Replies View Related

Using Alter Statements To Create Indexes?

Apr 19, 2006

I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.

Any help is appreciated.

View 3 Replies View Related

Create An Indexes/Keys Property With T-SQL

May 19, 2004

is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property

thanx

View 5 Replies View Related

Create Indexes - Own File Group

Mar 12, 2007

With help of others on this group, I've been learning and researchingabout indexes; an area I neglected.I see I can specify which filegroup I wish to create an index, whichthe default is Primary.I have more than one drive in my SQL server where I put data and logson their own logical raid groups.My databases are SIMPLE, so they dont use much, if any logs (none as Iunderstand).I was thinking of adding an additional file to my database and use itsolely for the indexes.Any thoughts?SQL Server 2005 Enterprise x64 SP28 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.Thanks,Rob

View 2 Replies View Related

How To Create Parameterised Views?

Jun 15, 2007

I am using SqlServer2005 and asp.net 2005. I have a large database for which I have to provide reports for it. I need a report that user will specify a date, and the report will be run, from this month & year and I will calculate the first day of the month and the last day of the month and these will serve as the input parameters for a long SELECT query.For my previous reports i've used views . But for this functionality i need  a parameterized view, which i cant create.Please help me how to create a parameterized view.... Thanks in Advance... 

View 3 Replies View Related

Create Views With Union

Sep 17, 2007

Hey

I am very new to database and have a question about views, that I hope someone can help me with, i am sure its simple:

I have to tables for storing different users, I want(for a log in function),to make a view that combine these to tables.

so all names stored in table1 under column customer_name, and all names stored in table2 under column name contact_name will in the view be stored under column username.

What shall a do?

Thanks for all help

View 10 Replies View Related

Using Variables To Create Views

May 8, 2007

I am stuck on creating views using the variables. I keep on getting the 'CREATE VIEW' must be the first statement in a query batch error. I understand that views need to be the first statement, but I have a lot of views that need to reference specific variables - is there any way to do this?

The code I am using is as follows:

DECLARE @view varchar(MAX)
DECLARE @database varchar(30)
SET @database = 'KateTEST3'

--Insert views
SET @view = 'USE ['+@database+']
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON;

CREATE view [dbo].[userssupplier]
as
SELECT *
FROM dbo.Users
WHERE (User_Type = 4.0) OR
(User_Type = 5.2)'
EXEC(@view)





Thanks

View 11 Replies View Related

Need To Create Case Sensitive Uniqiue Indexes

Jul 20, 2005

Hi,How Can I create a case sensitive unique index so that A1 and a1 are treatedas different ?I dont mind if I have to make a global DB change.Steve

View 1 Replies View Related

Transact SQL :: Create Table Indexes In SSMS2012

May 26, 2015

I use Indexes Fundamentals of Microsoft SQL Server - Lesson 30: Indexes in the website of URL... to learn the basic things of Indexes. In my SQL Server 2012 Management Studio (SSMS2012), I executed the following code..

-- scFTX_CreateTableEmployees.sql
-- saved in C:/Documents/SQLServerIndexes_downloadCode
-- 26 May 2015 10:52 AM
USE ScottChangDB;
GO
CREATE TABLE Employees

[code]....

Where the SCHEMA and the index 'IX_Employees are located in the Object Explorer of the database "ScottChangDB" of my SSMS2012. 

View 3 Replies View Related

TABLES AND INDEXES

Jun 7, 2001

How do I move a table (test1) to a physical file group, and the table(test1) indexes to another file group. One clustered index and 3 nonclustered indexes. Thanks!!!!

View 4 Replies View Related

Indexes On Tables

Oct 9, 2001

Hello List,

I've a customer table and It has two indexes only. But when I view from the query analyser it list ten indexes with the following name:

"_WA_Sys_status_01EAB64E"

I do not know, what does the above index does and i remember, I did not created that. When trying to drop that, It says, Its not an index. I dont know, what to do. Would somebody please advice me on this.

Thanks,

View 3 Replies View Related

Indexes On Tables

Mar 17, 2006

Hi,

Can someone point me to where I can find out if an SQL server table has an index ? I was in enterprise manager and brought up properties for any given table but was unable to find the information.

Thanks,
Jeff

View 7 Replies View Related

Create Views In The Order Of Their Dependency

Feb 5, 2007

In SQL 2K...i need to write a script that writes a script that creates all views in the order that they are nested and I am feeling lazy. Does anyone have anything handy?For example if View_BOB says...CREATE VIEW VIEW_BOBASSELECT COL_1 FROM VIEW_JOHNI need the script to generate a script that creates View_JOHN before View_BOB.

View 1 Replies View Related

How To Create Identity Column In Views

Mar 21, 2006

Hi there,

I'm new to SQL Server please help me, i want to create identity column(AutoNumber) when creating views.

View 6 Replies View Related

Create Views In Stored Procedures?

Sep 25, 2007

Can you create views within a stored procedure?

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved