First of all my apologies if you have seen this mail already but I am
re-sending as there were some initial problems.
This query is related to defining indexes to be unique or not and
consequences thereof.
Some documented facts that I am aware of include
1. Defining uniqueness allows optimiser to create optimal plans eg
select based on keys in such an index allows the optimiser to determine
at most only one row will be returned
2. Defining uniqueness ensures that rule (business/Primary key) is
enforced, regradless of how the data is entered.
We have many cases where non unique indexes are defined. The approach to
date has been that even though we are aware of some of the benefits
offered by defining uniqueness , we have chosen not to add keys to non
unique indexes such that they become unique. The primary reason for this
was that we did not want to make the keys comprising the indexes
unnecessarily large and therefore ensuing consequences when DML
statements are performed.
However, I have concerns that having highly duplicate indexes can have
performance impacts , including deadlocking. I am also aware Sybase used
to store duplicate values in overflow pages and therefore there were
performance consequences. Could SQL 2000 have the same behaviour ?
Thanking you in advance
Puvendran
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I have a situation where I'll need to get results from tables based on totally arbitrary filters. The user can select the field to compare against, the value, the comparison operator, and the boolean operator, so each bit in brackets would be configurable:[field] [>] [value] [and]The user can specify an arbitrary number of these, including zero of them. I like the coalesce function for situations that are a little more structured, but I think I'm stuck generating a dynamic query for this -- please correct e if I'm wrong!
1. I am looking data base solution for highly critical application. what type of HA/DR solution will suite for this.(always on, transactional , log shipping or mirroring). make sure all secondary nodes should be identical to production .
2. for any need to upgrade /patching /maintenance work, Â production should not be able to down. No downtime 100%.
I'm working on the viability of using SSIS to process data files that are (currently) sent by email or FTP and processed by hand. They are .csv or Excel and have some columns that are required, some that are included but the data is discarded, usually have some anomaly (like columns out of place, or columns missing) from source to source, and if they have headers the actual headers are inconsistent from source to source (and sometimes from submission to sumission ). Some sources send only one file, others might send a dozen. Due to the nature of the sources - many are not very technically inclined and sometimes there are other factors involved in how the data is exported - there's not a lot I can do to leverage them into all using the exact same format. Creating an SSIS package for each source (over 100 in all) is not a viable solution.
The compromise that I've come up with is I would like to come up with a universal SSIS package that can:
Take a .csv file with headers (I think we can force the sources to include the right headers if nothing else) as input , with or without quote delimiters. Not be sensitive to the order of the columns. Not be sensitive to the number of columns. Loop through multiple files from one source. Place the ouput in a staging table for further processing. Reading a .csv file, looping and the staging table is not a problem. What I'm struggling with is the middle portion. I've tried to make the column headers dynamic, but then I lose the ability to have quote delimiters. My next instinct was to the source file(s) and create one interim file with a different delimiter (such as pipe or ## or something) but that doesn't seem to work quite the way I want it - since the source has comma delimiters, the output has both the pipe and the comma. Another way I could do it is replace the commas within quote delimiters with a blank space in the data flow, which would eliminate the quote delimiters in the output. That I haven't tried yet.
Any suggestions? If I could bring down the hammer and tell every source "You must send it this way" I would, but my hands are tied.
I want to know is a flat file faster than a RDBMS for indexing for example a search engine indexing would a flat file be better in terms of performance, scalability etc than a RDBMS?
When I select a fieldname in a db as a primary key this will keep the uniqueness of that fieldname, but how is that work when I select 2 fieldnames (in Enterprise Manager) and click on the Key it will mark them as my primary key, but I can insert duplicates values in either fieldname.
this violates the constraint if i give the two teams on the same date..how to give the condition such that it checks for the violation of condition if the team names are interchanged.. for eg
if i give the values as Insert Into Games values('chelsea',1,'arsenal',2,'17-1-2000'); and Insert Into Games values('arsenal',1,'chelsea',2,'17-1-2000');
the above should violate the constraint ..how to do it
The other day we tried online re-indexing feature of SQL 2005 and it€™s performing faster than offline re-indexing. Could you please validate if it€™s supposed to do be this way? I always thought offline should be faster than online.
My company is upgrading one of our products; this involves some data migration and a question about existing indexes with uniqueness constraints has been raised. Here is the issue:
We have a database table with two particular columns that that we index with a uniqueness constraint. My question is: is the uniqueness constraint case sensitive?
For instance, if I have a database that is using a collation of SQL_Latin1_General_CP1_CS_AS. (case sensitive) and I insert data in these two columns as follows:
test col2test
Test col2test
would this uniqueness constraint be violated even though I'm using a case sensitive collation? I've done tests on this issue and I can't seem to insert this 2nd column; I'm just wanting to be certain that this is the case 100% of the time.
Hi, Can anyone tell me the order in which uniqueness constraints on indexes are enforced vs. when triggers are executed ? I have a unique constraint on an index and a trigger on the column on which the same index has been created. When a row is inserted, the trigger checks if the value for that column already exists in the table - if not, it inserts the row as is, else it gets the max() val of the column (based on another key column) and increments it by one, then does the insert. Creating an index across the two works fine, but if I set the Unique Values property for the index, subsequent inserts bomb out - yet there aren't any duplicates in the final table, as the trigger ensures this. Anyone got any ideas on this? My deduction is that the uniqueness constraint gets enforced before the trigger gets executed, but at the same time this *seems* illogical, as the row has not been inserted into the table at the point where the trigger is executed.
OK - maybe my Google-fu is off today but I'm trying to find out if using a sequence table will provide values that are as unique as the GUID is supposed to be.The reason is that I'm building a DW from the ground up (they won't let me expense my booze which I think is grossly unfair ) and rather than using GUID it ~seems~ like a sequence table would give better results. My concern is that the values won't be unique across tables.
So, I have this idea of a database design I was hoping to get some input on. I usually go through the process of creating a separate column for identity, like an auto-increment integer which serves as a context specific ID that easily can be passed around. However one of the short-comings I've come to realize is that the integer is just that, unique to the context and requires context information to make sense. I'm pounding the idea of creating a separate table whose sole purpose is to provide global (database wide) unique IDs. As such each row in a table can still have an ID but instead of being tied to a specific context it will always make sense within the application. A foreign key constraint can ensure that and a relation. Now, with a global ID which I can create FKs to, I don't have to create a separate table for relations between entities and can have relations through these global IDs and I think that's a good thing because if there are many relations between many entities they don't have to be defined more than once. There is also a single "hub" where all relations fit which will allow me to access most information through some relatively simple joins. My concerns are performance and Linq to SQL implication. I'm worried that Linq to SQL might start pulling an excessive amount of data and I'm worried that the practicality of such a design might hurt the long running of the application development process. But I'm surprisingly interested in what it could mean for a more ubiquitous view of data and tagging. Basically a way of any data to have relations to any other data through this yet simple design decision. If you think this is a good idea or see no particular problem with, please let me know! If you think there's problems with my idea, please motivate your critic but do tell me what I should look out for, or if you know of any better approach to my idea of being able to look at the data through some more common mechanism (which applies to all tables and the entire database mostly from a programming perspective).
I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks
I want to make sure that usernames and folder names (that are stored in the database) are all unique when created by the admin through an admin control panel.
How would I code and use an Index to make sure that the 2 columns are unique?
I've looked at examples on the net but nothing is telling me how I can create an index through Enterprise Manager? Thanks :)
I"m trying to use a BULK INSERT command to insert data into a table from a file. There is a UNIQUE Index that is being violated and the BULK INSERT fails.
I do not want to drop or disable the index, however, i also do not want to load 'duplicate' records so i keep the CHECK_CONSTRAINTS parameter.
Is there a way to have the duplicate records outputed to the ERRORFILE ?
Is there a system table column to query to determine whether an index is defined as unique in SQL Server 2000? It is easy to find this info in SQL Server 2005, but I don't see a valid column for this on dbo.sysindexes.
All- I have what i'm sure is a question with a simple answer: Supposing I want to modify the select command below to force the display of records with only unique combinations of the two fields person_id and act_session_id (bolded). How would I do this. Note that neither is a key field. (Key field headcount_id was added for completeness.) Thanks! SELECT hd.headcount_id, hd.person_id, hd.act_session_idFROM headcount as hd
I am reading "SQL Server Query Performance Tuning Distilled",on page 104 it talks about one of the index design recommendationswhich is to choose the column that has very high selectivity of valuesinstead of a column that has very few selectivity of values.My question is if I have currently indexes on my tables that have1, 2, 3, 4, ... values only on thousands of rows, are these nonclusteredindexes pretty much useless indexes that I should get rid of?And I know that pretty much the number of selectivity values willalways remain very low.Thank you
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
Hi!! I dont know if this is the correct forum for this or not, but still...Actually i wanted to know some details about SQl's Indexing services. I found this link on my hosters help pages : You need to use SQL Query Analyzer tool for this.
This will enable full-text indexing for the current database:exec sp_fulltext_database 'enable'
This creates a catalog:exec sp_fulltext_catalog 'catalogname', 'create'
This enables indexing of a table:exec sp_fulltext_table 'tablename', 'create','catalogname', 'indexname'
This adds a column to an index:exec sp_fulltext_column 'tablename', 'columnname', 'add'
This activates fulltext on a table:exec sp_fulltext_table 'tablename', 'activate'
These two enable automatic filling of the full-text index when changes occur to a table:exec sp_fulltext_table 'tablename','start_change_tracking'exec sp_fulltext_table 'tablename','start_background_updateindex' From the above i get that i need to set up my database for indexing then make a catalog and then add an index of a table to this catalog. Can anyone point any good tutorials for using this is the proper way so that performance is not affected and tells me details on updating indexes etc(esp using some criterias). Moreover does indexing columns lower the performance? Is there a workaround? I am completely new to this.
What should I be looking at if I have real-time data (constant transactions) writing to a table that is experiencing index type problems? The table needs to constantly be re-indexed, which is slowing the whole transaction process down.
Can you please help me find out if this statement is always true:
"Adding a new Index slows down updates"
This is more a general question, applicable as well for SQL Server. If this is not the appropriate subforum then I kindly ask an moderator to move this thread to the appropriate sub-forum.
Okay, so i've been creating a .net app that basically gathers data from a web page, and then passes the parameters to a s.p. i wrote in sql, fetches a count, and displays the data to the webpage. My problem layes in that i have the query command timeout set to 1:00 but alot of my quries on the larger tables take longer then that to complete, so the page is timing out quite often.
i KNOW my problem is database design, i'm running an OLAP database. trasactions only occur once a week when we run a federal DO_NOT_CALL database update. i was wondering if anyone would be so kind as to help me tune my database a little more the get some more juice out of it. i can also tell you guys that i've notice every time a query is ran, the Diqk Query length tacs out to nearlly 100% for the entire length of the query. dont know if that helps.
I have a problem on indexing. The field PK_hrSetBenefitsLeave is the primary key of the table "hrSetBenefitsLeave". When i see it on the "Manage Indexes and Keys", the identity name became PK_hrSetBenefitsLeave_1. Everytime i change it to its original name will get me error...and i can't save it.
Error msg on saving : 'hrSetBenefitsLeave' table - Unable to create index 'PK_hrSetBenefitsLeave'. There is already an object named 'PK_hrSetBenefitsLeave' in the database. Could not create constraint. See previous errors.
I tried to check using this query.
Select * from Information_Schema.Columns where column_name = 'PK_hrSetBenefitsLeave'
Hello,I need some help understanding why my indexes do not seem to be affecting mysearches. I would really appreciate help understanding what indexes I needto make this query run faster. I realize that I use wildcards when searchingfor g1.gene_name, but is there anything I can do to make that less of aproblem? I ran EXPLAIN on the search I wanted to optimize and got thefollowing:EXPLAIN SELECT c1.SFID FROM Gene g1, cDNA c1, Transcript t1, Refseq r1 WHERE(c1.SFID = t1.cDNA_SFID AND t1.gene_SFID = g1.SFID AND (g1.gene_sym = 'hh'OR g1.genbank_acc = 'hh' OR g1.gene_name LIKE '%hh%')) OR (c1.genbank_acc ='hh' OR c1.SUID = 'hh') OR (c1.SFID = t1.cDNA_SFID AND t1.gene_SFID =g1.SFID AND g1.locuslink_id = r1.locuslink_id AND (r1.mRNA_acc = 'hh'));+-------+-------+--------------------------+------+---------+------+--------+-------------------------+| table | type | possible_keys | key | key_len | ref | rows| Extra |+-------+-------+--------------------------+------+---------+------+--------+-------------------------+| r1 | index | mRNA_acc,llid,rma,rllid | rma | 25 | NULL | 20093| Using index || g1 | ALL | PRIMARY,llid,ggs,gga,gll | NULL | NULL | NULL | 190475| || c1 | ALL | PRIMARY,cga,cs | NULL | NULL | NULL | 43714| where used || t1 | index | gene_SFID,gS,cS,tg,tc | gS | 4 | NULL | 47238| where used; Using index |+-------+-------+--------------------------+------+---------+------+--------+-------------------------+I have the following indexes (which were all added after the database waspopulated):ALTER TABLE cDNA ADD INDEX cga(genbank_acc, SFID);ALTER TABLE cDNA ADD INDEX co(organism, SFID);ALTER TABLE cDNA ADD INDEX cs(SUID, SFID);ALTER TABLE Gene ADD INDEX ggs(gene_sym, SFID);ALTER TABLE Gene ADD INDEX gga(genbank_acc, SFID);ALTER TABLE Gene ADD INDEX ggn(gene_name, SFID);ALTER TABLE Gene ADD INDEX go(organism, SFID);ALTER TABLE Gene ADD INDEX gll(locuslink_id, SFID);ALTER TABLE Gene ADD INDEX gui(unigene_id, SFID);ALTER TABLE Transcript ADD INDEX tg(gene_SFID, cDNA_SFID);ALTER TABLE Transcript ADD INDEX tc(cDNA_SFID);ALTER TABLE Refseq ADD INDEX rma(mRNA_acc, locuslink_id);ALTER TABLE Refseq ADD INDEX rllid(locuslink_id);
Hi, There is a table which I regularly run a select query on. The select query always has a fixed where clause on only three of the columns with different parameters.
This is a query that runs each time:
select * from tblData where PersonNo = 2 and EmployeeType = 4 and DataDate = getdate()
This are the types of indexes the table currently has: One index for each of these three fields i.e. index1 for PersonNo index2 for EmployeeType index3 for DataDate In addition to the above, I also have created a covering index as follows index4 for PersonNo,EmployeeType,DataDate
Is what I have enough for indexes on this table please? Is there anything else I have to do on indexing this table? Thanks
I need some help with MS Indexing Services, and there doesn't seem to be much support for it on the web. Do you know of any good forums or sites?I'm using MS Indexing Services to power the search feature on my site. Should I be using something else like Sharepoint?
If you put an index on an integer type column named 'test_column' in a table that had 1,000,000,000 rows in it, and you said select top 50 * from test_table WHERE test_column = 1 since 'test_column' has an index, that would perform extremly fast wouldn't it? Cheers
I am working on SQL Server 7.0. Every weekend we go for reindexing of some tables. I want to know if it is possible to run the re-indexing of tables in parallel so that I can save time.
Our database is of size 80GB and one table is around 22GB. Rebuilding of index on this table takes a lot of time and we are unable to index the other tables.
Hoping someone could me with an ongoing indexing question that I have.
On my site, we have over the past 5 years developed what is emerging as a fairly complicated dbase structure, as features have been added to my site and relations have increased between different database tables, there has been a need to index fields in different ways, and in some instances field indexing has overlapped. For example we may have a table that has 5 fields (field1,field2,field3,field4,field5). A need to index field1 is requried because of a query that reads:
SELECT * From Table1 where field1=XXXXX
Additionally there may be a need to for another query that reads:
SELECT * From Table1 where field2=XXXXX
In this instance an index is placed on field2.... But, for example when there is the following query:
SELECT * From Table1 where field1=XXXXX and field2 = XXXXX
Is it necessary to set a new index on: field1,field2 ???
We have made the choice that yes, in fact there is...but now over time some of our tables have instances of single fields being indexed along with combinations of two single fields that have already been indexed, being indexed together. As tables have grown to over 1,000,000 records and having up to 15 or so indexes, we realize that the number of indexes maybe degrading performance. Also, indexes vary in type, e.g INT,BIGINT,Varchar fields... In the above instance, can we eliminate the multi-indexes and improve performance over all...?
On a second related question:
In the event that two tables are joined on a common field.
e.g. Select * from Table1,Table2 where Table1.field1=Table2.field1
Is it necessary to index both of these fields in tables: Table1 and Table2 ?
Hope someone can help, as we are looking to improve the efficiency of our tables as they continue to grow.
I have a database with no index on any table, I have to pull out records from them, process them and insert into a set of table in another database. There is no one to one mapping. What I have been doing is get the data into cursor and manipulate row by row and insert to target tables. This is very slow even for few thousand records and we have to do it for few hundred thousands.
The process takes long time to run (hours for 20000 records). I created indexes to speed up the operation, but with index my process just hangs, I have put some print statements within the transaction loop that also does not appear on ISQL, it appears only after I kill the process.
It's all confusing to me, index is not helping at all. I checked the query plan for queries after creating index, it displays fine but the stored procedure just stops.
