Advice on indexes

I have a table (detail) with 4 columns and over 30 million rows .
Say the columns are called ColA, ColB, ColC, ColD

ColA and ColB form a foreign key as this table is a 'detail' table for another master table in the database (master). The master table uses these two columns as its primary key.
To speed up joins between the two tables I have created an index over ColA and ColB

I frequently need to join the two tables together and so most of my queries are of the form:

SELECT master.*, detail.ColC, detail.ColD FROM master JOIN detail
ON master.ColA = detail.ColA and master.ColB = detail.ColB
WHERE master.ColA = @.valA
ORDER BY master.ColB, detail.ColC

This could return up to 60000 rows for a specific value of @.valA.
This is usually a sub-query that feeds directly into another query or a temporary table.

However, I also quite often need to filter the details further by ColC so I have queries like

SELECT master.*, detail.ColC, detail.ColD FROM master JOIN detail

ON master.ColA = detail.ColA and master.ColB = detail.ColB

WHERE master.ColA = @.valA AND detail.ColC = @.valC

ORDER BY master.ColB

The results set from this could be just a few hundred rows depending on @.valC

I clearly have a requirement to have an index across ColA, and ColB, presumably as the clustered index.

My question is how to speed up queries that involve ColC.
Do I create another index across ColA, ColB and ColC or just ColC by itself?
Will an index just on ColC make use of the the clustered index on the other columns?
Should I make the clustered index cover ColA, ColB and ColC instead ? etc ..

I am using SQL Express and am approaching the 4GB limit and this table and another one similar to it account for 96% of the database size.
The four columns in the detail table are all integer types and so don't take up much space per row. I am worried that a wide index will significantly add to the storage space per row and therefore significantly reduce the amount of data I can store in the database.

Any advice would be appreciated.

You defintely do not ever want to create one index that is a subset of another as you asked, ie. do not create two indexes on

ColA, ColB

ColA, ColB, ColC

The one on ColA, ColB, ColC does everything that the first one does, so it is all you need.

In your case it sounds like a clustered index on ColA, ColB, ColC should be all you need. Leaving out one column won't make much difference to the size. The size will be affected by the fillfactor and the fragmentation level of the index, so you should rebuild the index from time to time (how often depends on the nature of your updates) with a fillfactor of 100%, although a high fillfactor will increase the possibility that some updates will be slower.

|||Thanks for that.

I have done as you have said and created an index across all three columns and I am happy with both the speed of the queries and the size of the database.sql

Advice on Flat File Import

I’m looking for advice for the following scenario:

Import Source:Flat File – 2 columns (student#, lunch_bal)

Destination:SQL Table:

Trnpayuniq (PK)

Atype (all values = ‘S’)

Auniq ( from flat file – student#)

Trnamt (from flat file – lunch_bal)

Trnpayc (all values = 1)

I’ll truncate the destination table before each import.

Is the best way to create a temp table; get it populated

then update the destination table?Or

would the use of Merger (Merge Join) be the better approach.

Thanks for any direction.

Could you just added a Derived Column transform to add the constant values to your data flow?|||I'll try that. Thanks.|||

If it works, please mark the question as answered, per Phil's sticky post

Advanced SQL HELP: transform rows to columns

I have a table of 1000+ rows with 100+ different items with each Id having max 10 items

Id item
1 a
1 b
2 c
2 a
2 d
3 b
3 z

each item has an order in a different lookup table
item item_seq
a 1
b 2
c 3
d 4

The item columns (below) need to use the item order from lookup table, so the output table is as follows

Id item item2 item3
1 a b
2 a c d
3 b z

PLEASE HELP !!!Hello,
I am a little bit confused about your target but what I can catch is that you are asking how to make an output table like the one you demonstrated at the end. If it is so, I think that the table design itself is not correct and what you need is to make a one to many relation ship table composed of 2 fields that looks like the following

Id Item
1 a
1 b
2 a
2 c
2 d
3 b
3 Z
and so on

and by this way you can insert as many as items as you like with each Id

Thanks|||Thanks vm for replying Rasha.

To make matters simpler, I exclude the item order table since I found a way of doing that.
So basically the input table is
Id Item
1 a
1 b
2 a
2 c
2 d
3 b
3 Z

and the output table I need to come up with should be like

Id Item1 Item2 Item3
1 a b
2 a c d
3 b z

The CASE statement is diffucilt to use since there are 100+ different items (and new ones are added all the time) and each id can have 1 to 9 items, so columns Item1 to Item9 per id are needed.

Any ideas ?

Originally posted by rasha
Hello,
I am a little bit confused about your target but what I can catch is that you are asking how to make an output table like the one you demonstrated at the end. If it is so, I think that the table design itself is not correct and what you need is to make a one to many relation ship table composed of 2 fields that looks like the following

Id Item
1 a
1 b
2 a
2 c
2 d
3 b
3 Z
and so on

and by this way you can insert as many as items as you like with each Id

Thanks|||What you are asking for is called a cross tab query. The pivot is best handled on the client if at all possible, but it can also be done in SQL when you can't do it on the client.

-PatP|||Hi, I do not know much about Cross tab query but I think the following dissccusion may help you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19008|||Assuming that your item values are sortable for each id (that there aren't any NULL values and no duplicates), you can use:CREATE TABLE dbo.table00 (
id INT NOT NULL
, item CHAR(1) NOT NULL
)

INSERT INTO dbo.table00 (id, item)
SELECT 1, 'a'
UNION ALL SELECT 1, 'b'
UNION ALL SELECT 2, 'a'
UNION ALL SELECT 2, 'c'
UNION ALL SELECT 2, 'd'
UNION ALL SELECT 3, 'b'
UNION ALL SELECT 3, 'z'

SELECT a.id, Min(a.item)
+ Coalesce(', ' + Min(b.item), '')
+ Coalesce(', ' + Min(c.item), '')
FROM dbo.table00 AS a
LEFT JOIN dbo.table00 AS b
ON (b.id = a.id
AND a.item < b.item)
LEFT JOIN dbo.table00 AS c
ON (c.id = a.id
AND b.item < c.item)
GROUP BY a.id
ORDER BY a.id

DROP TABLE dbo.table00-PatP|||Thanks very much for your reply PAT.

I have to say your approach with the LEFT join and item> comparisons
is simple and clever.
However bearing in mind that there are 100+ different items, doing 100+ left joins...I don't how efficient will that be...
Thanks very much anyway.|||Originally posted by dim2000
However bearing in mind that there are 100+ different items, doing 100+ left joins...I don't how efficient will that be... Try it. You might be shocked (I know that I was).

A cross tab is still better handled on the client when that is possible, but the server does a better job than I thought that it would.

-PatP|||Thanks for your reply Pat.

The left joins worked quite fast actually, on the server that is not the client.

Do you know if I could use the Case or ISNULL statements for this.

e.g. CASE when item='a' THEN item1 ELSE 'None' END AS 'MCQ Raw',

I used the case statement before but only for amounts that needed to be summed up, when I tried to use it with text fields I could not get it to work,
Any ideas?

Advanced sort

I need some help writing this sql query.
I have three columns in my table
let call them
tOrderA,tOrderB,tContent
tOrderA,tOrderB are random integers from 1-10 000, tContent is a text field
so my table looks something like this
|tOrderA|tOrderB|tContent|
1 27 my
9 3 asfdf
152 16 sdfsd
18 22 dfdf
182 14 dfdsfs
tOrderA is my primary sort with levels (ie.1-100,101-200,201-300)
tOrderB is my secondary sort.
when the sort is applied the table should look like this.
|tOrderA|tOrderB|tContent|
9 3 asfdf
18 22 dfdf
1 27 my
182 14 dfdsfs
152 16 sdfsd
How would I write this query?
select tContent from tableA order by tOrderA ASC [range
1-100,101-200,201-300...] and tOrderB ASC
Thanks,
Aaronselect tOrderA, tOrderB, tContent
from tableA
order by (tOrderA + 1) / 100, tOrderB
"Aaron" wrote:

> I need some help writing this sql query.
> I have three columns in my table
> let call them
> tOrderA,tOrderB,tContent
> tOrderA,tOrderB are random integers from 1-10 000, tContent is a text fiel
d
> so my table looks something like this
> |tOrderA|tOrderB|tContent|
> 1 27 my
> 9 3 asfdf
> 152 16 sdfsd
> 18 22 dfdf
> 182 14 dfdsfs
>
> tOrderA is my primary sort with levels (ie.1-100,101-200,201-300)
> tOrderB is my secondary sort.
> when the sort is applied the table should look like this.
> |tOrderA|tOrderB|tContent|
> 9 3 asfdf
> 18 22 dfdf
> 1 27 my
> 182 14 dfdsfs
> 152 16 sdfsd
> How would I write this query?
> select tContent from tableA order by tOrderA ASC [range
> 1-100,101-200,201-300...] and tOrderB ASC
>
> Thanks,
> Aaron
>
>|||You mean:
select tOrderA, tOrderB, tContent
from tableA
order by (tOrderA - 1) / 100, tOrderB
^^^^^
With your code 99 and 100 will be in the [101 - 200] bracket.
Jacco Schalkwijk
SQL Server MVP
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:B77956D1-7821-4B24-B0A2-E898FA3B3F48@.microsoft.com...
> select tOrderA, tOrderB, tContent
> from tableA
> order by (tOrderA + 1) / 100, tOrderB
> "Aaron" wrote:
>|||Right ! Thanks! would that be the infamous "off by 2" error '
"Jacco Schalkwijk" wrote:

> You mean:
> select tOrderA, tOrderB, tContent
> from tableA
> order by (tOrderA - 1) / 100, tOrderB
> ^^^^^
> With your code 99 and 100 will be in the [101 - 200] bracket.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:B77956D1-7821-4B24-B0A2-E898FA3B3F48@.microsoft.com...
>
>

Advance SQL question

Hello everybody,

i have a advance question about a specific sql problem:

My table A have for example 3 columns.
in the third column are words seperated by ~.

ID COL2 COL3
-----
1 ab test~dummy~ddd
2 cd testdata2~sjhfdg~sdf
3 ef sd~test
4 gh sd~cv

Now i want two lists:

1.) used Values for column 3:

Values
--
test
dummy
ddd
testdata2
sjhfdg
sdf
sd
cv

2.) used values plus ID
Value ID
----
test 1
test 3
sd 3
sd 4
cv 4
dummy 1
...

Is it posible to produce such a list with nearly one SQL -Statement or with
temporaly tables ?

Thanks in advance

T.Kindermann
Database Administrator

--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>Thomas Kindermann wrote:
> Is it posible to produce such a list with nearly one SQL -Statement ?

Yes, it is possible:

SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

SELECT ID, substring('~' + COL3 + '~', Number + 1,
charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
FROM (
SELECT TOP 250 number
FROM master..spt_values WHERE number>0
GROUP BY number ORDER BY number
) Numbers, TheTable
WHERE Number <= len('~' + COL3 + '~') - 1
AND substring('~' + COL3 + '~', Number, 1) = '~'

This queries work with up to 250 words in each row.

However, it may be better to use other ways. For more informations, see
this excellent article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/arrays-in-sql.html#tblnum-core

Razvan|||Am 23 Jun 2005 02:39:18 -0700 schrieb Razvan Socol:

> Thomas Kindermann wrote:
>> [1 zitierte Zeile ausgeblendet]
> Yes, it is possible:
> SELECT DISTINCT substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> SELECT ID, substring('~' + COL3 + '~', Number + 1,
> charindex('~', '~' + COL3 + '~', Number + 1) - Number - 1) AS Value
> FROM (
> SELECT TOP 250 number
> FROM master..spt_values WHERE number>0
> GROUP BY number ORDER BY number
> ) Numbers, TheTable
> WHERE Number <= len('~' + COL3 + '~') - 1
> AND substring('~' + COL3 + '~', Number, 1) = '~'
> This queries work with up to 250 words in each row.
> However, it may be better to use other ways. For more informations, see
> this excellent article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/arrays-in-sql.html#tblnum-core
> Razvan

GENIAL SUPER,

you are my good ;-))))))))

Thanks

Thomas
--
-----------------------
Thomas Kindermann
E-MAIL: Reply to TKINDER<x>@.GMX.DE without <x>

Advance PIVOT function in SQL2005

Below is an example of a pivot table from the help in SQL2005.
My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?

I would like to use this for a daily report where the columns would be the dates?

Thanks.

GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorIDThis is not standardized SQL. You may want to ask in a product-specific group|||moving thread to SQL Server forum|||My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?Yes. To have an unknown number of columns\ values you need to use dynamic sql.
http://www.sommarskog.se/dynamic_sql.html

ADO 2.8 Is there any limitation on number of columns?

Hi,

I'm using ADO 2.8 in a vb.net code. The .Net framework version is 1.1 and windows server 2003.

I'm firing a query that result in 256 columns and few hundred rows. Here is the snapshot of the code

adoRs = New ADODB.Recordset

With adoRs

.CursorLocation = CursorLocationEnum.adUseClient ' adUseClient

.ActiveConnection = adoCn

.CursorType = CursorTypeEnum.adOpenStatic ' adOpenStatic

.LockType = LockTypeEnum.adLockBatchOptimistic ' adLockBatchOptimistic

.Open(strSQL)

End With

The returned record set is empty with all the the 256 columns name. Could anyone shed light why it is returning empty recordset. Is there any limitation on number of columns that a recordset can hold.

Thanks in advance.

With regards

Ganesh

Is it possible for you to try limiting the number of columns?
If it is, you could easily experiment and see whether adding additional columns to the resultset makes it to be empty or not.|||

It doesn't seem like you have a limitation on the number of columns because you do get all the columns back, just the entire recordset is empty. It could mean that the query doesn't return any results. Could you run your query in management studio to verify that it actually returns some rows that you are missing via ADO. If so, please include the query and we would be able to assist you further.

HTH,

|||

The Query return few hundred rows. I have checked that and there is nothing wrong with the query. In .Net 2.0 it returns some data in the recordset and it behaves abnormally.But our present environment is 1.1

With regards

Ganesh

ADO 2.8 Is there any limitation on number of columns?

Hi,

I'm using ADO 2.8 in a vb.net code. The .Net framework version is 1.1 and windows server 2003.

I'm firing a query that result in 256 columns and few hundred rows. Here is the snapshot of the code

adoRs = New ADODB.Recordset

With adoRs

.CursorLocation = CursorLocationEnum.adUseClient ' adUseClient

.ActiveConnection = adoCn

.CursorType = CursorTypeEnum.adOpenStatic ' adOpenStatic

.LockType = LockTypeEnum.adLockBatchOptimistic ' adLockBatchOptimistic

.Open(strSQL)

End With

The returned record set is empty with all the the 256 columns name. Could anyone shed light why it is returning empty recordset. Is there any limitation on number of columns that a recordset can hold.

Thanks in advance.

With regards

Ganesh

Is it possible for you to try limiting the number of columns?
If it is, you could easily experiment and see whether adding additional columns to the resultset makes it to be empty or not.|||

It doesn't seem like you have a limitation on the number of columns because you do get all the columns back, just the entire recordset is empty. It could mean that the query doesn't return any results. Could you run your query in management studio to verify that it actually returns some rows that you are missing via ADO. If so, please include the query and we would be able to assist you further.

HTH,

|||

The Query return few hundred rows. I have checked that and there is nothing wrong with the query. In .Net 2.0 it returns some data in the recordset and it behaves abnormally.But our present environment is 1.1

With regards

Ganesh