Tagged Questions
5
votes
2answers
94 views
Does an index with multiple columns make a similar index redundant?
Let's say I have a table that looks like this:
CREATE TABLE Activity (
ActivityID int primary key identity(1,1) ,
ActivityName nvarchar(10),
InactiveFlag bit
)
with an index that looks ...
-2
votes
0answers
43 views
Unexpected index scan against partitioned table [closed]
I have a set of partitioned tables with CIX on (ID, DATE) and partitioned on DATE.
There is another identical set of non-partitioned tables with CIX on ID.
Case A: T1 and T2 are non-partitioned ...
3
votes
0answers
23 views
custom population of full text index
Is there any way to populate a full text search index without storing the backing information in the database?
I don't mean using remote-blob-storage or FILESTREAM types. The backing information is a ...
6
votes
1answer
63 views
Dropping Hypothetical Indexes
In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.
I have a database that is ...
4
votes
2answers
72 views
Should I use a composite or single-column index?
I have the following columns in my database table (Medicines).
ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.
I am making a stored ...
7
votes
4answers
335 views
Index Seek vs Index Scan
Looking at an execution plan of a slow running query and I noticed that some of the nodes are index seek and some of them are index scan.
What is the difference between and index seek and an index ...
0
votes
1answer
49 views
Can I use the database and query it while full-text indexing?
I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
1
vote
2answers
84 views
SQL Server clustered index high fragmentation
I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index.
Inserts are working perfectly fine. After inserting 2000 rows the fragmentation ...
5
votes
2answers
146 views
Why disabling a clustered index makes the table inaccessible?
When an index is disabled, the definition remains in the system catalog but is no longer
used.
SQL Server does not maintain the index (as data in the table changes), and the index
cannot be used to ...
0
votes
2answers
86 views
Sync Indexes between two tables
I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.
I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
4
votes
1answer
134 views
Indexes file damaged, was on ramdisk
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
2
votes
0answers
93 views
Why does CREATE INDEX … WITH ONLINE=ON block access to the table over a period of minutes?
I have an existing table:
CREATE TABLE dbo.ProofDetails
(
ProofDetailsID int NOT NULL
CONSTRAINT PK_ProofDetails
PRIMARY KEY CLUSTERED IDENTITY(1,1),
ProofID int NULL,
...
2
votes
1answer
38 views
Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds
We have a number of tables (~1M records) that have a column on them defined as: [GlobalID] [uniqueidentifier] NOT NULL that gets auto-populated with newid(). We use this ID for synchronizing data ...
1
vote
2answers
121 views
Clustered vs Nonclustered Index
My database currently has a primary Key/Clustered index on the ID column for each table. However, the application that connects to the database is always looking at views defined as:
SELECT * FROM ...
3
votes
2answers
89 views
Reducing Log Impact During Re-Indexing
We use Ola's maintenance solution and its great.
Regardless of method for re-indexing a major friction point with IT is the amount of log generated during the weekly re-indexing process. For a 1TB ...
2
votes
1answer
61 views
Validate Primary Key and Index Selection
I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this ...
2
votes
2answers
83 views
Suspended in index rebuild
In SQL Server 2012, an Index Rebuild job is taking a very long time (up to 8 hours). However, not even one Index rebuild completed, so I stopped the index job.
In monitoring SQL task:
state = ...
5
votes
1answer
63 views
When REBUILDing indexes on SQL Server, what bearing does tempdb & LOG disk speed have?
Say I have the a data disk that is 50x faster than the LOG and tempdb (measured by Random Write speed) disk. (Don't ask why that's something we'll be fixing if needed)
I have a table that's got 19 ...
3
votes
1answer
75 views
How can I alter the fill factor of an index outside of an ALTER INDEX command?
I am using a COTS package to perform index rebuilds across my SQL Server portfolio. The package unfortunately doesn't have an option to set index fill factors globally for an individual instance or DB ...
3
votes
1answer
103 views
Why is a hash match operator in this very basic query
I'm beginning to learn some about looking at execution plans and making queries more efficient
Consider these two basic queries
select distinct pat_id, drug_class, drug_name from rx
select pat_id, ...
2
votes
3answers
85 views
Programmatically find indexes that cannot be rebuilt online
I am automating rebuild and reorganise indexes using T-SQL. I run into problems with indexes that cannot be rebuilt online. Primarily this happens because ntext/nvarchar columns are included.
Is ...
2
votes
2answers
74 views
Differences Between Two Different Create Index Commands
Are there differences between these two scripts? Or would all of the extra tokens/attributes (ie: NONCLUSTERED, WITH..., etc...) for the 1st script be defaults in SQL Server 2008 for the 2nd script?
...
4
votes
2answers
190 views
What are the differences between leaf and non-leaf pages?
I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page ...
2
votes
1answer
92 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
430 views
SQL Server 2008 query planner failing after index drop & recreate
Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
1
vote
1answer
82 views
SQL Azure: Drop Constraint and Index
This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake.
Here is my question:
When dropping Constraints, are their indexes dropped as well?
...
1
vote
1answer
223 views
Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan
I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts ...
3
votes
0answers
80 views
Key Lookup and Full-text index
I have the following query:
SELECT T2.Title
FROM TitleTable T1
INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1
AND T2.FKID2 = @ID_PARAM2
AND ...
2
votes
1answer
138 views
Will a primary key be added as a clustered index?
I've inherited a database where no primary keys were defined on the tables. There are also no clustered indexes assigned to the tables.
If I perform an alter table to assign a primary key will this ...
1
vote
1answer
74 views
Consolidating indexes
I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
-7
votes
2answers
75 views
I have table with 17 Indexes if i add 1 more index for search in two columns [closed]
I have table with 17 Indexes if i add 1 more index for search in two columns insert , update and delete queries will be slow.I dont want them to slow...what to do
4
votes
1answer
201 views
Indexing strategy when using the between operator SQL Server 2008
I have a large table ~25 million rows with the structure
CREATE TABLE [dbo].[rx](
[pat_id] [int] NOT NULL,
[fill_Date] [date] NOT NULL,
[script_End_Date] AS ...
6
votes
3answers
265 views
Clustered index in SQL Server vs index organized tables in Oracle
I am making the transition as a database developer from SQL Server to Oracle and found some fantastic resources here already (How to make a transition from SQL Server DBA to Oracle? and As a DBA, how ...
5
votes
1answer
77 views
Efficiently Filter Large Set With Disjunctions
Let's say I have a single table
CREATE TABLE Ticket (
TicketId int NOT NULL,
InsertDateTime datetime NOT NULL,
SiteId int NOT NULL,
StatusId tinyint NOT NULL,
AssignedId int NULL,
...
3
votes
1answer
127 views
Index Key Column Order
I have created a joining table for many-to-many relationship.
The table only has 2 cols in it, ticketid and groupid
typical data would be
groupid ticketid
20 56
20 87
20 ...
2
votes
0answers
70 views
What is the effect of replacing indexes with filtered (non-null value) indexes?
Our project runs a very large, very complicated database. So about a month ago, we noticed that the space used by indexed columns containing null values was getting too large. As a response to that, I ...
2
votes
1answer
241 views
SQL Server manage indexes
Is it possible to automate the managing of indexes on my database? I'm looking to see if there are any scripts or programs that can automate this process. Would it be possible to use stats generated ...
7
votes
2answers
495 views
At What Point Does Having an Index Become Efficient
I've found a lot of resources that mention that adding an index to a table makes searches faster and inserts slower, but only if the table is large. This creates a tradeoff, which is a design ...
1
vote
1answer
53 views
Is it a good idea to have an index when the column value changes several times over record lifetime?
I have a table where one of the columns represents an object state - let it be State of type int. The State value starts with 1 and changes to 7 with values in between. Over a row lifetime there're ...
1
vote
0answers
89 views
Best Practice to update columnstore indexed tables
I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
0
votes
1answer
143 views
Find fulltext indexes in SQL Server 2008
How do I find a list of tables that have a FULLTEXT INDEX applied to them on a given database?
For example, you can enable and disable a FULLTEXT INDEX like this:
ALTER FULLTEXT INDEX ON ...
3
votes
2answers
130 views
Proper Index for WHERE X <> 1 AND Y <> 1 ORDER BY Z
I have a relatively large table that is accessed in exactly one way, by exactly one query (on the read side).
The query filters the table by two columns (both bit), and sorts by a third column (an ...
-1
votes
1answer
83 views
Covering Index isn't being used and I don't know why
We have this complicated query that I'm trying to make "better" until we move it to pull from a data warehouse. I need a solution that's "good enough" for now and I think I'm about 2-3 indexes away ...
2
votes
1answer
144 views
Non-clustered Index Update not displayed in execution plan [duplicate]
Possible Duplicate:
Nonclustered Index Insert
I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution ...
3
votes
1answer
618 views
Should I rebuild index after truncate/large insert?
I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.)
Basic outline is very simple:
...
3
votes
2answers
96 views
Why is this key lookup required?
I have an admittedly-complex query that I'm optimizing and I'm stuck on the next step. I have pinpointed the bulk of my problem to one part of my query that's doing the following Key Lookup but I'm ...
3
votes
1answer
58 views
Large inserts breaking indexed queries
Very similar to Large insert/update in table causes timeout while SQL Server re-arrange indexes only I am not using SSIS with only occassional large (predominently annual) inserts from another staging ...
1
vote
1answer
322 views
SQL Server Full-Text Indexer with stoplists/stopwords
As I'm working on a graduate project (Textmining with SQL Server 2012 Semantic Search) I run into a situation where I need to post a question on this website, hoping someone can help me.
This ...
7
votes
1answer
185 views
SQL Server Index vs Statistic
What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each?
4
votes
2answers
87 views
We are running database maintenace with indexing the system, indexing fails due to locking?
We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be ...
