How do I improve a 3 column, composite clustered index on a large table when the developer insists there is no other way to achieve uniqueness? They say a uniqueindentifier column will not work.
View 5 RepliesHi,I created a composite index (lastname, firstname). I know the followingqueries will use this index:WHERE lastname = ...WHERE lastname = ... AND firstname = ...Also this won't use the index:WHERE firstname = ...But how about: WHERE firstname = .. AND lastname = ...And why?Thanks a lot,Baihao--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
View 13 Replies View RelatedWant to check my thinking with you folks...
I have a table with a clustered composite index, consisting of 3 columns, which together form a unique key. For illustration, the columns are C1, C2 & C3.
Counts of distinct values for columns are C1 425, C2 300,000 & C3 4,000,000
C3 is effectively number of seconds since 01/01/1970.
The usage of the table is typically, insert a row, do something else, then update it.
Currently, the index columns are ordered C3,C1,C2. Fill factor of 90%.
My thinking is that this composite index is better ordered C1,C2,C3.
My reasoning is that having C3 as the leading column, biases all the inserts towards one side of the indexes underlying B-tree, causing page splits. Also, there'll be a bunch of "wasted" space across the tree, as the values going into C3 only ever get bigger (like an identity), so the space due to the fill factor in lower values never gets used.
Welcome your thoughts.
I created composite clustered index key based on Gender and Salary column
The Query executed Successfully and <g class="gr_ gr_135 gr-alert gr_tiny gr_spell undefined ContextualSpelling multiReplace" data-gr-id="135" id="135">i</g>
got composite index key id Gender(-), Salary I <g class="gr_ gr_310 gr-alert gr_gramm undefined Grammar only-ins replaceWithoutSep" data-gr-id="310" id="310">want</g> know why Gender(-) display like this?
And Gender is <g class="gr_ gr_391 gr-alert gr_spell undefined ContextualSpelling ins-del multiReplace" data-gr-id="391" id="391">nvarchar</g> (20)
Hi folks, i need an advise.
I've a gerand table customers_orders table with customer_id and order_id.
Whenever we have to find orders, for customer, this table is involved. Hey; i know u'll be angry y the heck this gerand exist but i've to blame the older dudes then.
Now this table has composite clustered index; CUSTOMER_ID+ORDER_ID.
The tables have grown over GB size; i see HASH INNER JOIN rather than MERGE for the GEREND and CUSTOMER table join.
Is it good to use composite clustered index; or should i clustered one the columns in the GEREND and other to normal index. What performance impact it could be.
Howdy!
When we create a composite key(col1,col2) clustered index is created in both col1 and col2.So how come "only one clustered can be created for a table" is justified?
View 8 Replies View RelatedHi everyone,
I have some problems on composite nonclustered indexes. I could not exatly understand their logic.
In my opininon, suppose that we have a table called Order and we create a composite nonclustered index on this table for OrderID column and OrderDate column. So I am using this query;
SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate
So in here, I think our first research is based on OrderID and ten after ordering our data pointer according to the OrderID and then our index is converted to an index which is based on OrderDate while performing ordering. So is this correct ??
Would you please explain this ?
Thanks
Is that possible on SQL Server 2000 and onwards?
View 1 Replies View RelatedWould it be OK to use varchar(5) instead of char(5) as the first field of acomposite clustered index?My gut tells me that varchar would be a bad idea, but I am not finding muchinformation on this topic on this when I Google it.Currently the field is Char(4), and there is a need to increase it to hold 5characters.TIA
View 2 Replies View RelatedMade an interesting discovery today - a column of data type Bit is not allowed to participate in composite key. Rather surprised.
I have a certain unique code that exhibits 2 unique states, which rendered the use of the boolean column, so the uniqueness goes <code>-1 and <code>-0. Is there any 'hack' so to speak to avoid using a Tinyint for the boolean column instead?
Hello,
I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.
The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold
1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.
A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.
I would like to have some tips, recommendations and alternatives for what I should do in this case.
I have several tables where I have used the command
distinct first_name, last_name, dob
to unduplicate.
I was wondering if I could create a new column in my table that combines the existing records for first_name, last_name and dob?
Hi All!I would like to have a composite PK on 3 columns, one of them is nullCREATE TABLE TableA (ColA int NOT NULL ,ColB int NOT NULL ,ColC char (3) NULL ,......)GOALTER TABLE TableA ADDCONSTRAINT TableA_PK PRIMARY KEY CLUSTERED(ColA,ColB,ColC)GOSQL Server does not allow having a composite PK with one nullable column:What is wrong to have values?1,100,NULL1,200,ABC1,200,ABD.....Code in C applies to Values in B and for some values in B the code does not exist.I can work out and define a special Code:NEV(not existing value), but in general I do not understand this restriction.Thanks
View 3 Replies View RelatedHi, I want to replace the indexes in my database to a different
filegroup. How can I do that using T-SQL? I only found a way that
uses the EM, but I have a lot of indexes and I hate to do it manually.
Thanks a lot.
I have data like below
Potter, James J
Williams, Ted R
Allen, Gary G
I want to remove Middle Name from the output
Potter, James
Williams, Ted
Allen, Gary
My Query:
SELECT
CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN
REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '')
ELSE Supervisor END AS NewSupervisor from data d
However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"
Do we have any way to say sql to replace after certain index?
Dear All.
We had Teradata 4700 SMP. We have moved data from TD to MS_SQL SERVER 2003. records are 19.65 Millions.
table is >> Order_Dtl
Columns are:-
Client_ID varchar 10
Order_ID varchar 50
Order_Sub_ID decimal
.....
...
..
.
Pk is (ClientID+OrderId+OrderSubID)
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am thank u to all who read or reply.
Arshad
Manager Database
Esoulconsultancy.com
(Teradata Master)
10g OCP
I have a script that loops through a series of tables to send data to a table from each of the tables. My issue is that not all tables have the columns I need in them. What I would like is to replace the column with another column when it does not exist. Something like below
Select Misisng_Column(A.Name, replace with B.Name) as Name
FROM SomeTable A
Cross Join (Select Name FROM AnotherTable) B
AnotherTable has one record in it. To avoid a Cartesian issue. Like I said just an example
In my real script the table aliased as A is from a list of tables in a sys.tables query that loops through to the end.
Hello Everyone,
I'm a newbie to SSIS. While experimenting with it I've encountered an issue which I'm hoping someone of you could help me out with. I have need to make a specific transformation which as output would have to produce rows with a new calculated column that replaces single column from input. New column has different data type than input column it is replacing. I've used Derived Column Transformation (DER) to do the first part of the work - appending new column and calculating its value (based solely on value from single original column that has to be replaced). Question is how should I do second part, task of removing no longer needed column from the pipeline? I've tried in DER instead of Derived Column being added as new column, selecting Replace 'column' but as it seems it is meant to replace only column data and not column data type (what I've expected). I've also tried using Copy Column Transformation (CPYC) but as it turns out CPYC transformation just (logically) duplicates data in the pipeline with optional different allias.
I have two tables with the following structure:
Table 1
---------
id | Name
---------
1 | John
2 | Debbie
3 | Kim
4 | Mary
Table 2
---------
id | Name
---------
654 | John
415 | Debbie
68 | Kim
289 | Mary
Table 2 id's values are all messed up and I need to replace them so that they look exactly like in Table one, my guess is that query will have to replace value of "Id" based on the "Name" column, I hope I made myself clear.
Hi,
I am trying to replace a string column with 'Y' or 'N' in my SSRS report. I tried the following expressions, but no one works for me.
=IIf(Fields!ExpectedWords.Value Is Null, 'Y', 'N')
=IIf(Fields!ExpectedWords.Value = '', 'Y', 'N')
=IIf(IsDBNull(Fields!ExpectedWords.Value), 'Y', 'N')
Would you please tell me what's wrong? Your suggestions would be appreciated.
Thanks alot.
I've searched through this forum, and am extremely disappointed that something as simple as this can't be done without resorting to a script component (which I refuse to do):
I have a Lookup transformation that returns a GUID value -- or not, so I've configured its error output to ignore failure. That should leave a NULL in the output column, and I should be able to chain a Derived Column transformation that replaces the Lookup output column (e.g. foo) with an expression like:
isnull(foo) ? newid() : foo
But there's no newid() function! Who was the genius that decided SSIS should implement different expressions than Transact SQL? Is there a workaround, like (DT_GUID)GETDATE()?
Or do I have to craft a SQL statement in the Lookup that guarantees a non-NULL result?
I have an update where I need to replace the first two numbers of a string with SF and remove the last number - e.g.:
029929229 >> SF992922
I have an update statement which works in Oracle, but I cannot get it to work in SQL Server. The statement is below:
UPDATE oraowner.ems_sam
SET sam_id = replace(sam_id,sam_id,'SF'||substr(sam_id, 3, length(sam_id) - 3));
Hi, I was wondering if it is posible as in derived columns to replace an input column with the output one in Data Conversion?
What I can see that when a data conversion is done, only we can do it in a new column and not in the same one we are using as input.
Do you know if this it is posible?}
Thanks for your help!
Beli
Hi,
Please, What s the differnce between a column that s a primary key and a column that s a "key/index with Isunique=true"?
Thanks a lot.
Hi
How do i remove Carriage return in a varchar column?
Thanks
What is the correct syntax to replace a field data nvarchar(50)
Current data = 0020-10-02
Change = 2003-10-02
Thank you in advance.
How do you replace quotes in an expression
I mean for example if I needed to replace xx in a string with empty string then the following works: REPLACE(SelectedString, "xx","")
But the example I have needs to actually replace quote marks in a string with an empty string and REPLACE(SelectedString, " " ","") doesn't work. I tried guessing a few option like "E or &QTE or something...
Any ideas ?
Thanks
Richard
Hi,
We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.
Just to be clear:
column id (int)
column name (string)
Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table
Which transformation do I use to replace the value of the id column with this new id?
thanks,
Stephane
I have a SQL Server 2005 database that has a table with a TEXT column. This TEXT column has XML data in it. The length of the XML data in each record in the table is about 700,000 characters. What is the quickest most efficient method to replace a nodes text with another value? I.E., <LogoLarge>aasdfasdfaasadfasdfsdfasdfadsf</LogoLarge> with <LogoLarge>a</LogoLarge>. This table has about 2 million records. Thanks in advance for your help.
View 2 Replies View RelatedHi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.
View 1 Replies View RelatedTrying to replace a string in a table and it is not working the path can be like OM-WD08-1 reportData.raw
USE Config
DECLARE @OldPath varchar(30), @NewPath varchar(30)
-- Initialize the variable
SET @OldPath ='OM-WD08-1';
SET @NewPath ='AA-PC';
UPDATE AnatomyConfigs
SET Path = REPLACE(Path,@OldPath,@NewPath) WHERE Path IS NOT NULL
AND Path LIKE @OldPath
GO
Hello All.
Hopefully someone out there will have an idea as this isdriving me nuts.
Ihave some sample problem. I want to use function replace() on Derived Column.
For example.
when strDate = 2007/03/22
I used ==> replace(strDate, "/", "") ==> 20060322
But If strTest = "123.10" ====>> 123.10
How can i do to replace ( " )double qoute ?
by function replace()
what is a statement for replace (") in Derived Column ?
please tell me for this event.
any suggesstion appreciated
Thank you very much.
Chonnathan
Hi,
I've a text column (text datatype) that contains carriage return and line feed.
Syntax-wise, how can I replace these by a space?
Thanks.