Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.
C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:

> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
>
>
|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.
|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:

> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>
|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...

> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.
|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jan

Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:
> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> > We have a table that has ~900,000 rows in the table. The table previously
> > had
> > ~1.5 million rows of data when the ~600,000 rows where deleted the table
> > size
> > shrunk ~1 G. The table is currently useing ~27 GB of storage.
> >
> > I would like to write a query to determine my largest 25 rows. This is
> > being done to determine if the storage is truely being used.
>
>|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > I was hoping to find a was to gather the information using system tables
> > ie
> > number of pages used by row.
> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...
> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jansql

Determine sequence in log

In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
any way to extract a pattern of rows? Specifically, I am trying to select al
l
records that have an audit_log_id of '13' which would be immediately followe
d
by a record with an audit_log_id of '16', which in turn would be immediately
followed by a record with an audit_log_id of '2', and display the results in
these groups of three records?
Thanks for any help.
SteveCan you post some ddl, sample data and expected result?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve B" wrote:

> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Only if there is some field, IN THE TABLE, that allows you to "determine" ro
w
sequence. I mean if there exists a ccolun, with unique values, which when
sorted, will sequence the rows in the order in which "immediately following
"
has the meaning you want it to have. If that's so, let's say that column i
s
named <LogDate>.
You have to join the table to itself, where, for each record in the first
instance of the table is "Joined" to it's Immediate Follower, by "Joining"
based on the value of LogDate being equal to the Minimum value of all the
records with LogDate > Than this records LogDate...
Select Prev.*, Next.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:

> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Thanks Alejandro.
Below is an example. I would like to return the sets of rows that are 13,
16, 2. THere are three examples in this table.
AL_EVENT_ID AL_DATETIME
2 9/24/04 9:16 AM
13 9/24/04 9:51 AM
2 9/24/04 10:21 AM
13 9/24/04 10:39 AM
13 9/24/04 1:17 PM
2 9/24/04 1:23 PM
13 9/24/04 2:05 PM
2 9/24/04 2:40 PM
2 9/24/04 2:43 PM
13 9/24/04 2:56 PM
2 9/29/04 8:13 AM
2 9/29/04 4:11 PM
2 9/30/04 8:32 AM
16 10/1/04 11:38 AM
13 10/1/04 12:28 PM
16 10/1/04 12:29 PM
2 10/1/04 12:29 PM
2 10/1/04 2:30 PM
2 10/1/04 2:46 PM
2 10/1/04 3:04 PM
13 10/1/04 4:40 PM
2 10/1/04 4:44 PM
13 10/1/04 4:58 PM
2 10/4/04 9:42 AM
13 10/4/04 3:23 PM
16 10/4/04 3:23 PM
2 10/4/04 3:23 PM
2 10/5/04 4:56 PM
2 10/6/04 9:35 AM
13 10/6/04 11:52 AM
13 10/6/04 12:47 PM
16 10/6/04 12:49 PM
2 10/6/04 12:49 PM
2 10/6/04 1:32 PM
13 10/6/04 4:06 PM
2 10/7/04 8:51 AM
2 10/7/04 11:39 AM
13 10/7/04 11:49 AM
"Alejandro Mesa" wrote:
> Can you post some ddl, sample data and expected result?
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
>
> "Steve B" wrote:
>|||Thanks very much. I'm sure this is what I need. Not quite clear though on th
e
Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't match
.
I have added some example data to Alejandro's post.
Steve
"CBretana" wrote:
> Only if there is some field, IN THE TABLE, that allows you to "determine"
row
> sequence. I mean if there exists a ccolun, with unique values, which when
> sorted, will sequence the rows in the order in which "immediately followi
ng"
> has the meaning you want it to have. If that's so, let's say that column
is
> named <LogDate>.
> You have to join the table to itself, where, for each record in the first
> instance of the table is "Joined" to it's Immediate Follower, by "Joining"
> based on the value of LogDate being equal to the Minimum value of all the
> records with LogDate > Than this records LogDate...
> Select Prev.*, Next.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > First.LogDate)
> Join AUDIT_LOG Last On
> Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > Mid.LogDate)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
>
>
> "Steve B" wrote:
>|||Sorry they were table aliases in my first incantation, and neglected to
change them... They should be First, Mid, and Last
The SQL should be
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Using your actual column name,
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > First.AL_DATETIME)
Join AUDIT_LOG Last On
Last.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > Mid.AL_DATETIME)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Thank once again for your help. It works like a charm
Steve
"CBretana" wrote:
> Using your actual column name,
> Select First.*, Mid.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > First.AL_DATETIME)
> Join AUDIT_LOG Last On
> Last.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > Mid.AL_DATETIME)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
> "Steve B" wrote:
>|||Yr very welcome!
"Steve B" wrote:
> Thank once again for your help. It works like a charm
> Steve
> "CBretana" wrote:
>

detecting & deleting duplicates in batch vs proc

I know how to detect & delete dups/or >dups in test with a select clause, this works fine in a small table, but if the table has a million rows say, it sounds like a proc would be faster: my question is: How do I display those rows in a proc for detecting what the problem is. The print stmt. doesn't seem to work and I wondered if I had to go through the process of building an output stream. The proc creates okay but I'm stuck after that part.

thx

Kat -- very rough code below

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc dupcount
@.count int
as
set nocount on
select categoryID,
CategoryName,
Count(*) As Dups
from Categories
group by Categoryid, CategoryName
having count(*) >1

set @.count = @.@.Rowcount
print convert(varchar(30),CategoryID) + ' ' + convert(varchar(30),@.count) + CategoryName

/*set @.count = @.@.RowCount
IF @.count > 1
print convert CategoryID, CategoryName, convert(varchar(30),@.count)*/

hello kat,

I recommend you save the records to be deleted into a table. Thats for reporting purpose and

just incase yo might have deleted something important, for backup purposes

print statement would not be ideal for a very large operations, besides its too slow

regards,

joey

|||

Hi Joey,

Thx for all your help... should I do this in a batch or in a proc?

Kind Regards,

Kat

|||

hi kat,

batch operation will do since this is just a one time operation.

you dont want to have so many SP's in your db.

anyway. Are you done with the delete statement?

I mean the delete query which will delete the duplicates?

joey

|||

Yes, thank you very much! I'm trying to find test situations and how to resolve them. This was one that I've seen happen before.

Kat

|||

joeydj wrote:

hello kat,

I recommend you save the records to be deleted into a table. Thats for reporting purpose and

just incase yo might have deleted something important, for backup purposes

print statement would not be ideal for a very large operations, besides its too slow

regards,

joey

Never save deleted records. Transactions and error handling ensure wanted records are not deleted.

If you want to delete them, delete them and be done with it. It's going to save a lot of purging time later on.

...and PRINT is for Query Analyzer and Management Studio not for application display.

Adamus

|||

If you want to display them in an application form, use SELECT not PRINT.

Research "Views" in SQL Server.

Adamus

|||

hello adam,

lets help the girl.

how do we delete duplicates

and of course leave one of them in the table

lets say i have a table name tablex

tablex

id , lastname, first,

1, cruz, juan

1, cruz, juan carlos

2, penduko, pedro

2, penduko, peter

2, penduko, pedro

3, falaypay,pacifico

3, falaypay, pacifico

3, falapay, pacifica

4, khan, cynthia

4, luster, cynthia - got married

4, khan, agnes cynthia - the encoder missed the second name

In this example i have a very dirty employees table in the data warehouse and i have 8 million of them. and it happens in real life. hahaha. if this is a warehouse there should be a timestamp but sad to say the architect has failed to remember of placing one

how can i remove the duplicates and maintain the integrity. Ok never mind integrity how do we delete the duplicate first just using the id as qualifier

regards,

joey

|||I was just wondering if anyone had an answer to Joey's post. It appears that I would have to manually look at the data to resolve the issue, any ideas?|||

You can detect duplicates using query below:

select id

from tbl

group by id

having count(*) > 1

For the delete logic to work efficiently, you need some other key column(s) in the table to determine which row to keep. This can also be some columns that uniquely identifies a row within each group.

delete tbl

where timestamp < (select max(t1.timestamp) from tbl as t1 where t1.id = tbl.id)

go

For these type of tables in a data warehouse that doesn't have any constraints you can add an identity column and then do below:

alter table tbl add uqid int not null identity

go

delete tbl

where uqid > (select min(t1.uqid) from tbl as t1 where t1.id = tbl.id)

go

If you have large number of rows to delete then you can perform the delete in batches like:

-- Works only on SQL Server 2005. Uses TOP clause support in DML

declare @.n int

set @.n = 1000

while(1=1)

begin

-- delete N rows at a time

delete top(@.n) tbl

where uqid > (select min(t1.uqid) from tbl as t1 where t1.id = tbl.id)

if @.@.rowcount = 0 break

end

go

-- Older versions of SQL Server

declare @.n int

set @.n = 1000

set rowcount @.n

while(1=1)

begin

-- delete N rows at a time

delete tbl

where uqid > (select min(t1.uqid) from tbl as t1 where t1.id = tbl.id)

if @.@.rowcount = 0 break

end

set rowcount 0

go

If you cannot add identity column or have other columns that identifies a duplicate within each group you can use approach below. This will however be slower due to use of cursor.

declare @.id int, @.cnt int

declare @.dupes cursor

set @.dupes =

cursor fast_forward for

select id, count(*) - 1

from tbl

group by id

having count(*) > 1

open @.dupes

while(1=1)

begin

fetch @.dupes into @.id, @.cnt

if @.@.fetch_status < 0 break

/* Older versions of SQL Server

set rowcount @.cnt -- don't care which N-1 rows are deleted

delete tbl

where id = @.id

*/

/* SQL2005

delete top(@.cnt) tbl

where id = @.id

*/

end

/* Older versions of SQL Server

set rowcount 0

*/

go

However, this is a bad way to maintain a data warehouse. You need to build rules into the data loading and transformation process to track changes easily. You need to model your dimensions so that they can accomodate changes to attribute values. There are many standard technique available to do this.

|||

DECLARE c1 CURSOR FAST_FORWARD FOR

AS

DECLARE @.FirstName varchar(25)

Select firstname, count(firstname)

from Employees

Group by firstname

having count(firstname) > 1

open c1

fetch next from c1

into @.Firstname

While @.@.fetch_status = 0

BEGIN

insert into DeletedItems(firstname) Values(@.FirstName)

Delete from Employees

Where firstname = @.Firstname

END

fetch next from c1

into @.Firstname

CLOSE c1

DEALLOCATE c1

|||

Hi Adamus,

I thought 'cursors' were the bane of SQL, if I had a table with millions of rows, wouldn't this take forever? Or is this something new in 2005?

thx

|||

katgreen777 wrote:

Hi Adamus,

I thought 'cursors' were the bane of SQL, if I had a table with millions of rows, wouldn't this take forever? Or is this something new in 2005?

thx

CTE is the replacement in 2005 and I'm learning to code it, but yes cursors do carry a lot of overhead. Notice I use a "fast_forward" It makes a big difference.

My argument with cursors and millions of rows is, if you're dealing with millions of rows, there is no lightning fix other than hardware enhancements. You could argue until you're blue in the face with seconds, execution plans, alternative methods, and the list goes on, only to discover a well written cursor and ample thought will give you the result you need.

Adamus

|||

katgreen777 wrote:

Hi Adamus,I thought 'cursors' were the bane of SQL, if I had a table with millions of rows, wouldn't this take forever? Or is this something new in 2005?

Please take a look at the code that I posted before. You don't need cursors unless your table(s) doesn't have any key information or unique identifiers or other required columns. To delete large number of rows efficiently, you can use a batching mechanism using SET ROWCOUNT or TOP. See the examples I posted. And CTEs as mentioned in the other post doesn't really help in this case. CTE is just a query expression similar to derived tables or views and provides some additional capabilities like writing recursive queries.

|||

Hi,

Looking at the 'dirty' warehouse table above, wouldn't we just use the id number to delete the rows because that is unique and the firstname isnt?

thx

sql

Design Question for Large Table

Hi,

What's the most efficient way to store the following information:

* Table contains 1 million listings
* Each listing can be geo-targeted to any of the 200+ countries
* Searches return listings based on geo-location

Storage options:

Option #1 (normalized)
* ListingsTable (PK listingID int) [1 million rows]
* ListingGeoLocations (listingID, geoLocationID) [could be up to 200 million rows]

Option #2 (denormalized)
* ListingsTable (PK listingID int, binary(32) with bit-mask consisting of 200 bits one for each location)

Did anyone have experience with similar structures? Which option is more efficient?

Thanks,
Avoption 2 sounds inferior to me

let's say you were looking for listings corresponding to the 37th bit in the bitmask, how would you find them?

by inspecting the bitmask of all 1 million rows, that's how|||I'm too tired to answer

Look up normalization on google|||Option #1.

As Rudy pointed out, you are going to have problems doing bitwise calculations on bitmasks over 30 bits, after which you exceed the capacity of the BigInt datatype.

Also, while under scenario #1 your table COULD reach 200 million rows, in reality what is the average number of countries you expect for each geolocation? Probably significantly less than 200.|||I understand that option #1 is normalized and will allow the search for listings in a specific country using query. However, say when a search is performed, it is based on an set of keywords (which are also tied to listings).

So say you looking for "dentists". You can about 100 listings that are related. Now, the APPLICATION (not SQL) will simply "filter" the 100 listings by doing geo-specific bitwise comparissons and will only display the listings that have the proper country bit set.

Still Option #1? The search needs to be very fast.|||You are NOT going to do bitwise operations on a 200 value bitmask.|||So say you looking for "dentists". You can about 100 listings that are related. you can? you can what? how does that work?|||you can? you can what? how does that work?

Ask Harry potter|||Why not have a
Listings table (Pk ListingID)
Locations Table (PK LocationID)
and,
ListingLocations table (PK ListingID, LocationID)

ListingLocations is an intersection table, used to define a many-to-many relationship. The two required fields, ListingID and LocationID are also foreign keys to the appropriate table.

Design Advice

Hello,
We are storing largish (about 1 million rows) datasets which are imported
from CSV files.
We do no manipulation of the statistical content, so records are stored as
follows:
Year Gender Age Value
1998 Male 18 100
1998 Male 19 150
1998 Female 18 45
1998 Female 19 60
So there were 100 males aged 18 in this particular survey.
The gender and age are being stored as varchar fields, and the value as an
integer field.
The problem is that some surveys have invalid data - such as a n.a. (not
available), n.p. (not published), and n.y.a (not yeat available). So we may
need to store:
1997 Female 19 n.a
I need to reflect this invalid data to the user when he is completing
dynamic cross-table reports of the data Eg the user can filter just too 1998,
or to all years - if all years were selected, I need to return 1997 Female 19
as n.a.
Can anybody offer some advice to me for which is the best way to store this?
I considered using NULL's, but I had two issues; ideally I'd like the
aggregation routine to give me a NULL if a NULL is aggreated, however it
treats it as a 0; and there would be no easy way for me to differentiate
between the different types of invalid data.
Sorry about the long post.
Any advice would be greatly appreciated.
Mark
NULL is the obvious way to record the unknown amounts. The additional
"status" code for invalid data could go in another column. For example:
CREATE TABLE Surveys (yr INTEGER NOT NULL CHECK (yr BETWEEN 1990 AND 2100),
gender CHAR(1) NOT NULL CHECK (gender IN ('M','F')), age INTEGER NOT NULL
CHECK (age BETWEEN 0 AND 150), pop_count INTEGER NULL, status CHAR(3) NOT
NULL CHECK (status IN ('NA','NP','NYA','OK')), CHECK ((pop_count IS NOT NULL
AND status = 'OK') OR (pop_count IS NULL AND status <> 'OK')), PRIMARY KEY
(yr,gender,age))
(I used "Pop_count" just because "Value" isn't a very informative name for a
column. Value also a reserved word.)
I'm not sure what you mean when you say that the "aggregation routine"
treats NULLs as zero. The SUM aggregate in SQL will ignore NULLs and return
a total without them. If all values in an aggregation are NULL then the
result is NULL. If you want to return a NULL sum if *any* value in the
aggregation is NULL then you can use CASE like this:
SELECT CASE WHEN COUNT(pop_count)=COUNT(*) THEN SUM(pop_count) END
FROM Surveys
David Portas
SQL Server MVP
|||I use Null as well for numeric fields, for character fields I use either
null or NA whichever the users prefer.. About the only way you can aggregate
null values is to count them...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"mallen" <mallen@.discussions.microsoft.com> wrote in message
news:2D445832-520C-4AD1-AC0F-9176B63DF483@.microsoft.com...
> Hello,
> We are storing largish (about 1 million rows) datasets which are imported
> from CSV files.
> We do no manipulation of the statistical content, so records are stored as
> follows:
> Year Gender Age Value
> 1998 Male 18 100
> 1998 Male 19 150
> 1998 Female 18 45
> 1998 Female 19 60
> So there were 100 males aged 18 in this particular survey.
> The gender and age are being stored as varchar fields, and the value as an
> integer field.
> The problem is that some surveys have invalid data - such as a n.a. (not
> available), n.p. (not published), and n.y.a (not yeat available). So we
may
> need to store:
> 1997 Female 19 n.a
> I need to reflect this invalid data to the user when he is completing
> dynamic cross-table reports of the data Eg the user can filter just too
1998,
> or to all years - if all years were selected, I need to return 1997 Female
19
> as n.a.
> Can anybody offer some advice to me for which is the best way to store
this?
> I considered using NULL's, but I had two issues; ideally I'd like the
> aggregation routine to give me a NULL if a NULL is aggreated, however it
> treats it as a 0; and there would be no easy way for me to differentiate
> between the different types of invalid data.
> Sorry about the long post.
> Any advice would be greatly appreciated.
> Mark

Design Advice

Hello,
We are storing largish (about 1 million rows) datasets which are imported
from CSV files.
We do no manipulation of the statistical content, so records are stored as
follows:
Year Gender Age Value
1998 Male 18 100
1998 Male 19 150
1998 Female 18 45
1998 Female 19 60
So there were 100 males aged 18 in this particular survey.
The gender and age are being stored as varchar fields, and the value as an
integer field.
The problem is that some surveys have invalid data - such as a n.a. (not
available), n.p. (not published), and n.y.a (not yeat available). So we may
need to store:
1997 Female 19 n.a
I need to reflect this invalid data to the user when he is completing
dynamic cross-table reports of the data Eg the user can filter just too 1998
,
or to all years - if all years were selected, I need to return 1997 Female 1
9
as n.a.
Can anybody offer some advice to me for which is the best way to store this?
I considered using NULL's, but I had two issues; ideally I'd like the
aggregation routine to give me a NULL if a NULL is aggreated, however it
treats it as a 0; and there would be no easy way for me to differentiate
between the different types of invalid data.
Sorry about the long post.
Any advice would be greatly appreciated.
MarkNULL is the obvious way to record the unknown amounts. The additional
"status" code for invalid data could go in another column. For example:
CREATE TABLE Surveys (yr INTEGER NOT NULL CHECK (yr BETWEEN 1990 AND 2100),
gender CHAR(1) NOT NULL CHECK (gender IN ('M','F')), age INTEGER NOT NULL
CHECK (age BETWEEN 0 AND 150), pop_count INTEGER NULL, status CHAR(3) NOT
NULL CHECK (status IN ('NA','NP','NYA','OK')), CHECK ((pop_count IS NOT NULL
AND status = 'OK') OR (pop_count IS NULL AND status <> 'OK')), PRIMARY KEY
(yr,gender,age))
(I used "Pop_count" just because "Value" isn't a very informative name for a
column. Value also a reserved word.)
I'm not sure what you mean when you say that the "aggregation routine"
treats NULLs as zero. The SUM aggregate in SQL will ignore NULLs and return
a total without them. If all values in an aggregation are NULL then the
result is NULL. If you want to return a NULL sum if *any* value in the
aggregation is NULL then you can use CASE like this:
SELECT CASE WHEN COUNT(pop_count)=COUNT(*) THEN SUM(pop_count) END
FROM Surveys
David Portas
SQL Server MVP
--|||I use Null as well for numeric fields, for character fields I use either
null or NA whichever the users prefer.. About the only way you can aggregate
null values is to count them...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"mallen" <mallen@.discussions.microsoft.com> wrote in message
news:2D445832-520C-4AD1-AC0F-9176B63DF483@.microsoft.com...
> Hello,
> We are storing largish (about 1 million rows) datasets which are imported
> from CSV files.
> We do no manipulation of the statistical content, so records are stored as
> follows:
> Year Gender Age Value
> 1998 Male 18 100
> 1998 Male 19 150
> 1998 Female 18 45
> 1998 Female 19 60
> So there were 100 males aged 18 in this particular survey.
> The gender and age are being stored as varchar fields, and the value as an
> integer field.
> The problem is that some surveys have invalid data - such as a n.a. (not
> available), n.p. (not published), and n.y.a (not yeat available). So we
may
> need to store:
> 1997 Female 19 n.a
> I need to reflect this invalid data to the user when he is completing
> dynamic cross-table reports of the data Eg the user can filter just too
1998,
> or to all years - if all years were selected, I need to return 1997 Female
19
> as n.a.
> Can anybody offer some advice to me for which is the best way to store
this?
> I considered using NULL's, but I had two issues; ideally I'd like the
> aggregation routine to give me a NULL if a NULL is aggreated, however it
> treats it as a 0; and there would be no easy way for me to differentiate
> between the different types of invalid data.
> Sorry about the long post.
> Any advice would be greatly appreciated.
> Mark

Design Advice

Hello,
We are storing largish (about 1 million rows) datasets which are imported
from CSV files.
We do no manipulation of the statistical content, so records are stored as
follows:
Year Gender Age Value
1998 Male 18 100
1998 Male 19 150
1998 Female 18 45
1998 Female 19 60
So there were 100 males aged 18 in this particular survey.
The gender and age are being stored as varchar fields, and the value as an
integer field.
The problem is that some surveys have invalid data - such as a n.a. (not
available), n.p. (not published), and n.y.a (not yeat available). So we may
need to store:
1997 Female 19 n.a
I need to reflect this invalid data to the user when he is completing
dynamic cross-table reports of the data Eg the user can filter just too 1998,
or to all years - if all years were selected, I need to return 1997 Female 19
as n.a.
Can anybody offer some advice to me for which is the best way to store this?
I considered using NULL's, but I had two issues; ideally I'd like the
aggregation routine to give me a NULL if a NULL is aggreated, however it
treats it as a 0; and there would be no easy way for me to differentiate
between the different types of invalid data.
Sorry about the long post.
Any advice would be greatly appreciated.
MarkNULL is the obvious way to record the unknown amounts. The additional
"status" code for invalid data could go in another column. For example:
CREATE TABLE Surveys (yr INTEGER NOT NULL CHECK (yr BETWEEN 1990 AND 2100),
gender CHAR(1) NOT NULL CHECK (gender IN ('M','F')), age INTEGER NOT NULL
CHECK (age BETWEEN 0 AND 150), pop_count INTEGER NULL, status CHAR(3) NOT
NULL CHECK (status IN ('NA','NP','NYA','OK')), CHECK ((pop_count IS NOT NULL
AND status = 'OK') OR (pop_count IS NULL AND status <> 'OK')), PRIMARY KEY
(yr,gender,age))
(I used "Pop_count" just because "Value" isn't a very informative name for a
column. Value also a reserved word.)
I'm not sure what you mean when you say that the "aggregation routine"
treats NULLs as zero. The SUM aggregate in SQL will ignore NULLs and return
a total without them. If all values in an aggregation are NULL then the
result is NULL. If you want to return a NULL sum if *any* value in the
aggregation is NULL then you can use CASE like this:
SELECT CASE WHEN COUNT(pop_count)=COUNT(*) THEN SUM(pop_count) END
FROM Surveys
--
David Portas
SQL Server MVP
--|||I use Null as well for numeric fields, for character fields I use either
null or NA whichever the users prefer.. About the only way you can aggregate
null values is to count them...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"mallen" <mallen@.discussions.microsoft.com> wrote in message
news:2D445832-520C-4AD1-AC0F-9176B63DF483@.microsoft.com...
> Hello,
> We are storing largish (about 1 million rows) datasets which are imported
> from CSV files.
> We do no manipulation of the statistical content, so records are stored as
> follows:
> Year Gender Age Value
> 1998 Male 18 100
> 1998 Male 19 150
> 1998 Female 18 45
> 1998 Female 19 60
> So there were 100 males aged 18 in this particular survey.
> The gender and age are being stored as varchar fields, and the value as an
> integer field.
> The problem is that some surveys have invalid data - such as a n.a. (not
> available), n.p. (not published), and n.y.a (not yeat available). So we
may
> need to store:
> 1997 Female 19 n.a
> I need to reflect this invalid data to the user when he is completing
> dynamic cross-table reports of the data Eg the user can filter just too
1998,
> or to all years - if all years were selected, I need to return 1997 Female
19
> as n.a.
> Can anybody offer some advice to me for which is the best way to store
this?
> I considered using NULL's, but I had two issues; ideally I'd like the
> aggregation routine to give me a NULL if a NULL is aggreated, however it
> treats it as a 0; and there would be no easy way for me to differentiate
> between the different types of invalid data.
> Sorry about the long post.
> Any advice would be greatly appreciated.
> Mark