How To Use Different Indexes In View
Aug 18, 2005
I have a "employee" table. This table has a primary key "emp_id" this by default becomes the cluster index of the table.
There is a view "empl" defined with the index on "emp_id" field as "Emp_ID". This becomes the cluster index for the view. There are other two non clustered indexes "Fname" and "Lname".
I want to use Both indexes without using "Order By" Clause. Can I get the order of the "Fname" or "Lname" without using "Order By" Clause? How?
View 3 Replies
ADVERTISEMENT
Nov 9, 2000
Hi
I want to know if we can create indexes on Views in SQL 7.0. If yes, then how
Thanks
alsi
View 1 Replies
View Related
Nov 23, 2005
I am having a problem with indexes on specific tables. For some reasona query that runs against a view is not selecting the correct index ona table. I run the same query against the table directly and it looksfine. Can anyone give me some insight? Thanks.PRODUCTION1:CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2CHAR(10))CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)ARCHIVE1:CREATE TABLE MyTest1 (ID INT IDENTITY(1,1), COLUMN1 CHAR(10), COLUMN2CHAR(10))CREATE CLUSTERED INDEX IDX_MyTest1 ON MyTest1 ON (ID)CREATE NONCLUSTERED INDEX IDX_MyTest2 ON MyTest1 ON (COLUMN2, ID)REPORTDB:CREATE VIEW MyTest1 ASSELECT ID, COLUMN1, COLUMN2 FROM PRODUCTION1..MyTest1UNION ALLSELECT ID, COLUMN1, COLUMN2 FROM ARCHIVE1..MyTest1While in PRODUCTION1:SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'--> Clustered index seek PRODUCTION1..IDX_MyTest2--> Results returnedWhile in ARCHIVE1:SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'--> Clustered index seek ARCHIVE1..IDX_MyTest2--> Results returnedWhile in REPORTDB:SELECT ID, COLUMN2 FROM MyTest1 WHERE COLUMN2 = 'Testing'--> Index seek PRODUCTION1..IDX_MyTest2--> Bookmark lookup PRODUCTION1..IDX_MyTest1--> Index seek ARCHIVE1..IDX_MyTest2--> Bookmark lookup ARCHIVE1..IDX_MyTest1--> Concatenate data and results returned
View 3 Replies
View Related
Oct 4, 2015
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string basedÂ
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
View 4 Replies
View Related
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
Sep 18, 2007
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
View 4 Replies
View Related
Jun 25, 2015
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
Sep 17, 2006
What is the difference please?
View 1 Replies
View Related
Jul 23, 2005
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies
View Related
Jul 24, 2012
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
View 2 Replies
View Related
Aug 24, 2007
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
View 3 Replies
View Related
Mar 9, 2006
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies
View Related
Aug 14, 2000
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View 1 Replies
View Related
Feb 17, 2006
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
View 4 Replies
View Related
Jul 31, 2002
Sir,
1. How do I call the indexes created in SQL server for a table from Front end VB programming ?
2. How can I use SEEK command with ADODB control ?
Please give me some tips & samples
Sundar Raman
View 1 Replies
View Related
Jul 23, 2001
Does anyone have the idea why the Indexes with _WA.....(like _WA_Sys_au_fname_07020F21) gets created.I don't how this index got created. I did not create this Index.
My Question is does the system creates these indexes or something else does this.
Thanks
Chak
View 1 Replies
View Related
Jul 27, 2000
Is there a way to tell how many indexes exist for an entire database, all I'm looking is for a count or generating a report list.
any help would be appreciated, thank you
View 2 Replies
View Related
Sep 13, 2000
At present I have been assigned to create indexes to retrieve the information fast, from the table. The existing table doesn’t have primary key, foreign key and unique constraints but I found to many default indexes already created by the system. I would like to know how this happened? Please inform how to delete these default indexes. Further, inform me other possible ways for the faster retrieval in SQL sever 7.0, if there are any.
I would appreciate if you send me a step by step explanations for the above problems.
Thanks a lot
View 1 Replies
View Related
Nov 20, 2000
I have run into a snag on my development server. Queries that are selecting data based on indexed fields in a where clause are using the wrong indexes. They are arbitrarily using the clustered index which isn't in the select at all and causing big performance problems. I can run the same statements on my production server and it runs based on the proper indexes. I used query execution plans to determine that this was infact the case.
I run DBCC Checkdb everynight and it comes back with no errors. I also rebuild the indexes. We also don't receive any other errors inputting or updating data. This sounds like corruption to me but if it's something else I don't want to spend the night restoring from production if there is another reason.
Has anyone encountered this before? Any ideas?
Appreciate it, K.
View 1 Replies
View Related
Jan 10, 2001
Is there any way for me to find out when last indexes have been used so that the one I don't need can be dropped.And also the one's that are of no use at all.
I need this as i am trying to dump all duplicated indexe . i know i can do this in ver 7
thanks
View 2 Replies
View Related
Feb 5, 2000
when executed sp_help tablename, I get lot of statistics and indexes like the following. Can anyone please tell me how it is generated automatically. as far i know statistics are generated only for primary keys. Can you please tell me what is clustered , hypothetical and the indexes starting with _WA supposed to be. Also there are lot of duplicate stats. Is it Ok to deletes those.
_WA_Sys_is_platinum_0A9D95DB
_WA_Sys_active_0A9D95DB nonclustered, statistics, auto create located on PRIMARY Active
hind_c_33_15 nonclustered, statistics located on
hind_c_37_1 clustered, hypothetical located
Thanks
Raj
View 2 Replies
View Related
Mar 2, 2001
Is there a way T-SQL script can find out all indexes built on a set of tables, drop them and periodically ( quarterly as an example ) re-build them ?
Thanks in advance for help.
Ivan
View 1 Replies
View Related
Mar 5, 2001
How do you find out indexes ( with column names info ) on a table ?
Thanks in advance.
Ivan
View 3 Replies
View Related
Jun 20, 2001
I am on SQL 6.5.
I have a question about speed and indexes. I have a static table (no updates except once a year). I want to be able to search data quickly on one column or many columns. I have created nonclustered indexes on each of the columns I search by. Is there anything else I can do to speed up my queries? Unfortunately all the searches involve using the like operator. I have even broken my table down into 2 smaller tables (Table A ~ 3 million rows, Table B 8 million rows).
All suggestions will be appreciated.
Thanks,
Eric
View 2 Replies
View Related
Nov 12, 1998
We have to interduce a new naming convention for the indexes currently available in the user databases.
We also have to drop all the old indexes available in about 250 tables and recreate them all acording to the naming convention we are coming up with.
Can any body suggest any idea.
I thank you guys in advance for your considaration.
View 2 Replies
View Related
Mar 10, 2006
hi.
there 2 different queries
1
[MYSQL].... where Cat=@CatID and Date=@Date[/MYSQL]
2
[MYSQL]... where Cat=@CatID and Date=@Date and Salesman=@SID[/MYSQL]
these queries are often used.
my question is about indexes.
should I use two different indexes?
index 1 : Cat,Date
index 2 : Cat,Date,Salesman
or only index 2 is enough ?
View 8 Replies
View Related
Jul 11, 2006
I need to alphabetize PART of a union all query, and was told that this may be accomplished with an index. I tried joining two views, but it does not work! I have NO experience with indexes and need some help.
Here is my code:
Select id, country from countries where id = 6 union all Select id, country from countries where id <> 6
I want the country with id 6 (USA) to be the default on a drop down list, then all the other countries listed after it in alphabetical order. Can you do this with an index? If so, how do I proceed?
Thanks!
Ronna
View 6 Replies
View Related
Nov 15, 2004
Hi all,
I want to know all indexes in database.
What do I do to get them?
Thanks in advanced,
Thi Nguyen
View 4 Replies
View Related
Feb 6, 2006
We recently updated some of the databases from MS Access 2000 to SQL Server 2000. In Access we had columns which were set to Indexed No Duplicates, these were not Primary key fields. Is this possible to set some indexes to prevent duplicates in SQL Server without creating performance issues?
Miranda
View 2 Replies
View Related
Feb 16, 2006
Do you have to take the DB offline to create or run indexes in SQL Server 2000??
View 2 Replies
View Related
May 30, 2007
Q1
The table i have stores max 2000 records. Not sure if i neeed a clustered index for this few records. I guess not.
Q2
For the same table as above. How do you create primary keys if there is not unique records.
Do i create a new column? like newColumnID identity? and create a primary key on this new column? Ok even if i create this newcolumn. I don't think it will improve the retrieving speed cause i'm not selecting this column.
Opinions, ideas?
View 2 Replies
View Related
Aug 24, 2007
i am running a stored procedure which has got clustered indexes created after creating table and data is inserted into it after creating clustered indexes. The tables which are meant in this sp is temporary tables and how to gain the performance of a query
View 2 Replies
View Related
Nov 16, 2007
I've created indexes for the queries below running select getdate() before and after the query to determine the time. I'm curioius as to how sql server determines and decides how and when to use the indexes for the queries and how the run times of these queries compare. I'm also curious to know what kind of difference there would be on the last two queries as the only difference is the values.
For the last two queries I don't think there would be a difference but hoping that someone would know.
Below are my results:
select count(distinct CustomerID)
from Orders
where Status = 5
SELECT getdate()
go
Create Index Orders_Index On Orders(customerID)
go
SELECT getdate()
select sum(TotalDue)
from Orders
where CustomerID = 11212
select getdate()
go
Create Index Orders_Index On Orders(customerID)
go
select getdate()
select count(distinct AccountNumber)
from Orders
where SalesPersonID = 288
select getdate()
go
Create Index Orders_Index On Orders(salespersonID)
go
select getdate()
select count(distinct AccountNumber)
from Orders
where SalesPersonID = 276
select getdate()
go
Create Index Orders_Index On Orders(salespersonID)
go
select getdate()
View 16 Replies
View Related
