MS SQL Server :: Multi-column Index Vs Single Column Indexes

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 12, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):

1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.

Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???

2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???

View 1 Replies View Related

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)

Apr 16, 2007

I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):

1) I have an index, which is consisted of 4 columns.

I've read elsewhere that this index functions (as well) as an index (single column

index) on the first column of this multi-column index.

Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

seperately i need to define only 3???

2) I have a unique key consisted of multiple columns.

I'd like to save an index to this combination of columns as well (to speed up

things in DB...).

Does the definition of a multiple-columns key free me from defining the multiple-

columns index???

can anyone explain the main diference between Keys and Indices???

thanks,

Ran Kizi

View 3 Replies View Related

Create Multi Column View From Single Column Data

Jan 9, 2008

I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.

data_table
product_code month value
350 1 10
350 2 20
350 3 30

product_table
product_code profit_center
350 4520

result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 20 30

My current query gives the following result
result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30

Any direction toward a solution would be appreciated. Am using SS2005.

View 5 Replies View Related

Multi Column Index

Apr 22, 2008

Hi there,

My 'where' clause contains the following...

startdate <= @Date AND enddate > @Date

Should I put one index on 'startDate' and another on 'endDate' or should I have one index that covers both columns?

I don't know what the difference would be.

Cheers, XF.

View 1 Replies View Related

Do You Include Clustered Index Column In Other Indexes?

Apr 3, 2002

I have a database where records are Inserted by an external process.
There is no updating or deleting of the data once inserted. The table in
question has a Clustered Index on the Machine_ID (integer) (data is from
manufacturing processes). Each record bears a start and end time. Most
queries involve the Machine, a time span (start time between to points in
time), the Downtime Cause, and the Running Mode.

I want to add an index on the Start Time, the Downtime Cause, and the
Runtime Mode.

My question is: should this new index also contain the Machine_id column
or does the existence of the Clustered Index already on that column negate
its need in the new index?

RC - Dedicated to only creating original mistakes!

View 2 Replies View Related

Traditional Indexes Vs Clustered Column-store Index

Apr 3, 2015

I've been asked to look at using Clustered Columnstore indexes for one of my tables. The table contains about 5 million records with about 50 columns. The max field size is a NVarchar(MAX) with max field length currently of about 4k characters. It's only about a gigabyte's worth of data. The table is about 50% R/W operations. Currently, we have multiple indexes with no clustered index due to some performance issues that happened in the past. I've been attempting to determine if it's even really worth it to switch over. I feel that the table is still fairly small with minimal columns and don't believe there will be any noticeable improvement over traditional indexing.

View 3 Replies View Related

Exclude Records From A Table Where ID Column Is Same But Mail Code Column Is Multi-valued

Nov 12, 2012

I am trying to exclude records from a table where the ID column is the same but the Mail code Column is multi-valued.For Example: (the table looks like....)

ID Mail_code
111111 XNT
111111 N11
111111 XNC
222222 XNC
222222 XNL
333333 XNC

So, if there is any ID that has a value of XNC, I want to exclude the ID all together from my output regardless of the other values.

View 3 Replies View Related

Multi Column Grouping Vs Page/Column Break

Mar 18, 2007

In my report i would have 2 groups.

The first group should cause a real page break, the secound group should cause a column break.

Any idea on how to realize this, i've been playing with quite some settings but .....

So, any help ...

View 1 Replies View Related

Choosing Between Two Column Values To Return As Single Column Value

Sep 14, 2007

I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))

View 1 Replies View Related

Concatenate Column Value From Multiple Rows Into A Single Column

Feb 27, 2008

Hello,

I need to concatenate a column from multiple rows into a single column in a new table.

How can I do this?

SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR

Need to have

SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR

THANK YOU

View 10 Replies View Related

Differnce Between A Column That S A Primary Key And A Column That S A Key/index With Isunique=true

Sep 13, 2007

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.

View 4 Replies View Related

FK On A Column To A Table Where The Column Is Not The PK But A Unique Index

Jul 23, 2005

Hello everyone, I am new to ERWIN and I need helps from the experts outthere.We are using ERWin 4.1.2771 and have reversed engineered some MS SQLServer 2000 databases.The problem we are having is that we have a FK on a column to a tablewhere the PK of the referencing table is on another column (such as anidentity column). We have a unique index on the column in the PK tableand SQL Server allows you to build a FK reference even though thecolumn is not defined as the PK.Does anyone know how to create this type of FK within Erwin?Thank You

View 2 Replies View Related

Clustered Index On Client_ID+ORderNO+OrdersubNo, If I Create 3 Noncluster Index On Said Column Will It Imporve Performance

Dec 5, 2007

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

View 3 Replies View Related

Identity Column And Indexes

Sep 21, 1999

The largest table in the database is not referenced by any other table. Primary key is identity column. I know that I shouldn�t make clustered index on that column, but what if I don�t index that column at all? Will it be some negative consequences of that decision?

View 2 Replies View Related

Two Non-cluster Indexes On Same Column?

Feb 5, 2014

We have two non cluster indexes on the same column for a table. let me know will it impact negatively on table like performance etc. ?

View 4 Replies View Related

Using SELECT ... WHERE Column IN (..., ...) Use The Indexes !?!?

Jan 25, 2008

Anyone can explain to me if the expression
SELECT ... WHERE Column IN (..., ...)
use the indexes or it's better to use the
SELECT ... WHERE Column = ... OR Column = ...

what is the best for the engine!?

Regards
Paulo Aboim Pinto
Odivelas - Portugal

View 4 Replies View Related

Multiple Indexes On The Same Column

Jan 21, 2008

Hi All

I have a table with indexes as follows

license_detail_prim
clustered, unique located on PRIMARY
component_id, license_id

LICENSE_DTL_IDX_LICENSE_ID
nonclustered located on PRIMARY
license_id

PK__license_dtl__6EB64F9B
nonclustered, unique, primary key located on PRIMARY
component_id, license_id

XIF39license_dtl
nonclustered located on PRIMARY
license_id

from the above, it is evident that multiple indexes have been created on the same columns

What is the side effects of havins such table design.

Thanks

View 5 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

Comparing Column Names And Indexes

Feb 17, 2004

Is there a way to compare tables in 2 different databases to find out if they have the same indexes and column names and keys. Or maybe a tool i dont know about.

View 1 Replies View Related

Multi Column Case, Perhaps?

May 20, 2002

I have 6 columns:
Column1a, Column1, Column2a, Column2, Column3a, Column3

I need a statement that will go through the whole table as follows:
For each row, if column3 is not null then return column3 as columnB and column3a as ColumnA.
If column3 is null then if column2 is not null then return column2 as columnB and column2a as columnA.
If column3 & column2 is null then return column1 as columnB and column1a as columnA.

Any ideas?
Thanks!

View 2 Replies View Related

Multi Aliases With The Same Column?

Jun 17, 2008

TABLE1
======================

PriceList
---------
1
2
3
1
2
3
1
2
3

Price
-----
777
888
999
777
888
999
777
888
999
(pretend these columns are side by side)
======================

I need to make a query to:
SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3

the output that i want is:

PRICE1
------
777
777
777

PRICE2
------
888
888
888

PRICE3
-----
999
999
999
(pretend these columns are also side by side)

View 6 Replies View Related

Sum Up Multi Values Same Column

Feb 8, 2008

Hi All,

I have the following Ex:

Table A
Col A <- Col that I need to check against
Col B
Col C <- Sum this col

So if Col A has the following values:
Col A Col C
2 10.00
4 15.00
2 25.00
4 15.00
3 10.00
3 5.00
7 4.00
9 20.00

I need to bring back the sums of 2, 4 and 3 in one resultset. How would I do this?

Thanks,

JJ

View 10 Replies View Related

Multi-column IN Clause

Nov 1, 2007

Hi,

I have a [TestTable] table with three rows. The pair of columns [Test1] and [Test2] are id, the [Test3] is a data column. First, I get a table variable with list of id pairs. Next, I would like to update the rows of that ids. However, I have not found the elegant way how to do it. For one column it is simple, just IN clause, which does not work (or I could not find how) for multi-columns. Does someone have a hint?

Thanks,

Martin

Note: The example bellow is dummy; on the other hand, I hope it shows the important points. Please, do not beat me on syntax errors.

Code Block
DECLARE @MyTableVar table(
Test1 int NOT NULL,
Test2 int NOT NULL
);

SELECT [Test1],[Test2] INTO @MyTableVar FROM [TestTable] WHERE [Test3] = '%dd%';

UPDATE [TestTable] SET [Test3] = [Test3] + 'ds'
WHERE ([Test1], [Test2]) IN (SELECT [Test1], [Test2] FROM @MyTableVar);

View 3 Replies View Related

Multi Column Keys

May 16, 2007

Is it possible to create multi column keys in SQL Express? If so, how?

Thanks

MisterT

View 1 Replies View Related

Multi Column Subreport

Aug 22, 2006

Hi,

I am trying to use a multi column report as a subreport. I want to know how can I do it without loosing multi column.

Thanks,

-Rohit

View 9 Replies View Related

Multi Column Subquery? W/ Alias

Jun 12, 2008

What I need to do is to create 3 columns with 3 different aliases from the same table that will return all the values during the following conditions:

when pricelist = 1

when pricelist = 2

when pricelist = 3

pricelist
--------
1
2
3

Price
--------
912 -- (linked with 1)
234 -- (linked with 3)
56 -- (linked with 2)
3245 -- (linked with 3)
234 -- (linked with 1)
65 -- (linked with 2)

these 2 columns are in the same table^^

so what i want my query to generate is:

Price1
--------
912
234

Price2
--------
56
65

Price3
--------
234
3245

Any help is apprecieated, thanks

if the above does not make sense to you maybe this will:
"can you make 3 aliases of the same column and only display the rows inside each column where pricelist = 1 for the 1st alias... where price = 2 for the 2nd alias...where pricelist = 3 for the 3rd alias"

View 10 Replies View Related

Help!!!!--Problems About The Multi Column Report!

Nov 2, 2006

I have a 2 coulmns report ,one group ,when I print the report,it does not paging correctly.For example,suppose every column can display 20 rows, if every group has 20-40 rows ,the print is ok,it can paging correctly,but if one group has 10 rows or more than 40 and less than 60,the two groups will print on one paper,how can I print the every group on different paper?

Any advice would be much appreciated.

Thanks.

View 4 Replies View Related

Problem Rendering Multi Column

Mar 18, 2007

I've added a link to a sample on how my test-column report renders.

http://www.leboeuf.be/MSDN/Dagelijkse%20Verkoop%20Merk.pdf

Any idea what's happening, while normally previewing the report (column by page) this problem doesn't occur .... The rendering is the same to TIFF

http://www.leboeuf.be/MSDN/Dagelijkse%20Verkoop%20Merk.tif

Any suggestion on what is the problem and even better, if there is a solution.

View 1 Replies View Related

Multi Column Report With Graph

Oct 29, 2007

Hi,
I have created a multi column report (2 columns), which is working fine. However, I need to have a chart on the same report, but it needs to be the width of the page, and not just the width of one column.

If I could put the chart in the report footer / header that would be perfect, but obviously I can't!

Has anyone else come across this, and any ideas / suggestions you have would be appreciated!

Cheers
Chris

View 3 Replies View Related

Multi Column Display In A Report??

Nov 30, 2007

I have a table with a data cell which is printing top-to-down

1
2
3
4
5
6
7
8

I want it to print like

1 2 3 4 5 6 7

How to do this??

The cell is in a group detials sections. and I am using Reporting Services 2000 with VS.NET 2003

Thanks in Advance

View 12 Replies View Related

Multi-column Report Problem

Apr 12, 2007

I am trying to get a simple list to wrap and print in multiple columns on my report. I have indicated 3 multi-columns in the report properties and see the multiple columns in the layout, but I must need to do something else because the report is still printing in one column to multiple pages. This should all fit on 1 page if the list will wrap or snake as it says it the directions for using multi-column reports.

Any help would be appreciated. Thanks

View 1 Replies View Related

Transact SQL :: IsNull From Multi Column

May 13, 2015

How to replace this Case When

SELECT CASE WHEN Col_1 IS NULL THEN -1 ELSE Col_2 END AS C
FROM MyTable

View 7 Replies View Related