MD5 Hashing rows?

I'm trying to implement some sort of security checking against database
modification. I'm thinking to store a list with Hash values for row sets.
i.e.
[pseudocode]
byte[] returnHash = MD5Hash( SELECT stuff FROM database WHERE junk )
[/pseudocode]
Is there a way to do an MD5 hash or equivilent without outputting the result
to a file and just hashing the file?
Is there a better way how to achieve the same goal?
Any comments appreciated. Thanks for your time!
-EdgarsEdgars Klepers wrote:
> I'm trying to implement some sort of security checking against database
> modification. I'm thinking to store a list with Hash values for row sets
.
> i.e.
> [pseudocode]
> byte[] returnHash = MD5Hash( SELECT stuff FROM database WHERE junk )
> [/pseudocode]
> Is there a way to do an MD5 hash or equivilent without outputting the resu
lt
> to a file and just hashing the file?
> Is there a better way how to achieve the same goal?
> Any comments appreciated. Thanks for your time!
> -Edgars
In SQL Server 2005 you could use the HashBytes function.
In earlier versions I think you'll have to use the .NET crypto classes
or Microsoft's COM crypto API. That means client side code or a call to
external code from SQL Server. Maybe you could write an extended proc
to do it (would require C++).
SQL Server 2000 has the CHECKSUM / BINARY_CHECKSUM functions but these
are just simple checksums not strong hashes.
David Portas
SQL Server MVP
--|||If you're using SQL Server 2000, there is an extended stored procedure
for MD5 hashing (and it's quick)
http://www.codeproject.com/database/xp_md5.asp|||markc600@.hotmail.com wrote:
> If you're using SQL Server 2000, there is an extended stored procedure
> for MD5 hashing (and it's quick)
> http://www.codeproject.com/database/xp_md5.asp
That's . Thanks for the link.
David Portas
SQL Server MVP
--|||I did come across that. How would one put in an entire row, or more
importantly an entire row set into that function to hash?
"markc600@.hotmail.com" wrote:

> If you're using SQL Server 2000, there is an extended stored procedure
> for MD5 hashing (and it's quick)
> http://www.codeproject.com/database/xp_md5.asp
>|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> In SQL Server 2005 you could use the HashBytes function.
Beware that in SQL 2005 RTM, HashBytes returns a random value if you pass it
a NULL value. SQL Server MVP Steve Kass has filed bug about it, and the
bug has been acknolweged as fixed, although it is unknown what result
hasbytes(NULL) yields after the fix.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Exactly how will depend upon your business requirements.
At the simplest level you can concatenate the relevant columns
select dbo.fn_md5( coalesce(colA,'') + coalesce(colB,'') )
from sometable
However, this may give you unexpected collisions in that if you have
a row with, for example, colA='X' and colB='YZ' and another row with
colA='XY' and colB='Z'. It also doesn't distinguish NULLs from empty
strings.
This may be acceptable to you though.
Also consider folding all character data to upper case and
removing leading/trailing spaces.
Lots of options, you decide.
Regards.

Maximum Table Size

We have been recommended by our database designers that 20million rows is th
e
maximum number of rows a data warehouse should be on SQLServer.
What is the opinion of you guys on this benchmark? Have you seen tables
bigger than that? Did you notice any performance impact.
Our table is at 14million rows and is about 14gb, we are analysing all
opportunities at present and would like a second opinion please.
Regards,
Marc> What is the opinion of you guys on this benchmark? Have you seen tables
> bigger than that?
YES!

> Did you notice any performance impact.
There are always performance concerns. The guideline should be more focused
on proper indexing and row widths, rather than number of rows.
A|||My recommendation is to sack your database designers and hire some people
who know what they are talking about. Seriously. The 20 million row comment
is pure rubbish.
There are SQL Server databases (not many though, because not many companies
have that much data) that have tables with billions of rows. 20 million rows
should not present any problems at all in a properly designed SQL Server
database, and I have seen numerous tables that contain that number of rows
or more.14 GB in itself is not much for a SQL Server database, but if the
table only contains 14 million rows, that is 1000 bytes per row, a rather
large rowsize for a datawarehouse fact table. A datawarehouse fact table
should almost exclusively contain numeric columns, and these columns should
be designed to be as small as possible, usually they are 4 byte integers. If
that is the case you are talking about 250 columns in that table, which is
possible from a proper logical design point of view, but sounds a bit much
too me. SQL Server can of course easily handle 250 columns in a table, it is
usually an indication of a bad database design though, to have that many
columns in one table.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:55BE7712-1CAE-4E6A-9EEA-05286669D9D2@.microsoft.com...
> We have been recommended by our database designers that 20million rows is
> the
> maximum number of rows a data warehouse should be on SQLServer.
> What is the opinion of you guys on this benchmark? Have you seen tables
> bigger than that? Did you notice any performance impact.
> Our table is at 14million rows and is about 14gb, we are analysing all
> opportunities at present and would like a second opinion please.
> Regards,
> Marc|||Depends on a lot of things
for example the wider the tables the slower the selects (less rows on a page
)
CPU, Disk etc etc etc
http://sqlservercode.blogspot.com/
"marcmc" wrote:

> We have been recommended by our database designers that 20million rows is
the
> maximum number of rows a data warehouse should be on SQLServer.
> What is the opinion of you guys on this benchmark? Have you seen tables
> bigger than that? Did you notice any performance impact.
> Our table is at 14million rows and is about 14gb, we are analysing all
> opportunities at present and would like a second opinion please.
> Regards,
> Marc|||> We have been recommended by our database designers that 20million rows is theed">
> maximum number of rows a data warehouse should be on SQLServer.
If your database designers say this then what do they recommend you do about
it? Sounds like either a sales pitch or a lame excuse for poor performance.
There is no fixed limit on the number of rows in a SQL Server table. Even if
there were such a limitation it would be irrelevant in a DW scenario because
you can implement a partitioned view across many tables on many different
devices.
14GB is a fairly modest sized data warehouse. DW on the terabyte scale is
pretty normal in SQL Server today.
David Portas
SQL Server MVP
--|||thx jacco,
we have a table 95 columns wide with 3228 characters.
the table is actually 18million rows(my mistake), we have had some
performance issues with it such as when linked to other large
tables(4million+ records).
Do you think the number of records or the row size is more important
Most of our columns are integers [ID's] but we do have some with
smalldatetime and one with a 19 char length!
we have people coming to talk teradata/oracle etc etc but no one has yet
identified the database design flaws yet. How can we look at this in more
detail especially in respect to the 20million maximum rowcount table size!
Appreciate your input
Marc|||As David emphasised, there is no limit to the number of rows in a table in
SQL Server. That the (flawed) design and lack of performance tuning skills
of your database designers doesn't allow for a reasonable performance with
20 millions row on your system, is their fault and not SQL Server's.
It's almost impossible to give proper advice in a newsgroup post on database
design flaws and performance issues in what seems a reasonably large system.
The best thing you can do is get an independent consultant in for a few days
to check the system. It will cost some money upfront, but will save you
loads in the long run. A number of MVPs work as independent consultants, and
I can forward your details to them if you contact me offline.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:40A756CA-0E23-4F44-8EE0-D742C1EF9C99@.microsoft.com...
> thx jacco,
> we have a table 95 columns wide with 3228 characters.
> the table is actually 18million rows(my mistake), we have had some
> performance issues with it such as when linked to other large
> tables(4million+ records).
> Do you think the number of records or the row size is more important
> Most of our columns are integers [ID's] but we do have some with
> smalldatetime and one with a 19 char length!
> we have people coming to talk teradata/oracle etc etc but no one has yet
> identified the database design flaws yet. How can we look at this in more
> detail especially in respect to the 20million maximum rowcount table size!
> Appreciate your input
> Marc
>|||> Do you think the number of records or the row size is more important
Neither. Optimal design and implementation are immeasurably more important.
Lousy design can destroy performance with only a few thousand rows. Since yo
u
(or your namesake) just stated in another thread that you "always use
cursors" you may not need to look any further than that for an explanation o
f
why you can't scale.
David Portas
SQL Server MVP
--
"marcmc" wrote:

> thx jacco,
> we have a table 95 columns wide with 3228 characters.
> the table is actually 18million rows(my mistake), we have had some
> performance issues with it such as when linked to other large
> tables(4million+ records).
> Do you think the number of records or the row size is more important
> Most of our columns are integers [ID's] but we do have some with
> smalldatetime and one with a 19 char length!
> we have people coming to talk teradata/oracle etc etc but no one has yet
> identified the database design flaws yet. How can we look at this in more
> detail especially in respect to the 20million maximum rowcount table size!
> Appreciate your input
> Marc
>|||To add to what everyone else says, after design (and a 95 column wide table
may or may not be a design issue, depending on if this is a fact table or
not (if so, then 80+ dimensions may be an issue, but I degress) the hardware
is the key. Too often people who claim some fixed number as a maximum don't
think of a Windows server as scalable. A lot will depend on your disk
subsystem for example. You might be doing your work on IDE drives, or a
slow Raid-5 array, or one of unlimited possibilites. When you start to
approact a large size/many users, the cost does go up greatly, but it is
likely not SQL Server's fault (as these vendors may tell you it is, since
they have a vested interest in you going to Oracle on their hardware)
As Jacco says in particular, you need someone to look at all of these
factors independent of a hardward/software vendor (ie they aren't
salespeople!) to get a valid look at what is going on.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:40A756CA-0E23-4F44-8EE0-D742C1EF9C99@.microsoft.com...
> thx jacco,
> we have a table 95 columns wide with 3228 characters.
> the table is actually 18million rows(my mistake), we have had some
> performance issues with it such as when linked to other large
> tables(4million+ records).
> Do you think the number of records or the row size is more important
> Most of our columns are integers [ID's] but we do have some with
> smalldatetime and one with a 19 char length!
> we have people coming to talk teradata/oracle etc etc but no one has yet
> identified the database design flaws yet. How can we look at this in more
> detail especially in respect to the 20million maximum rowcount table size!
> Appreciate your input
> Marc
>

Maximum size of table?

SQL20 Ent SP3 WIN2K Adv SP3
I'm hoping for some guidance here.
I've a single table with 184,000,000 rows, I think this is probably
a good candidate for some kind of partitioning.
My question is, how many rows would constitute a decent sized
table, before partitioning?
I know it's a very subjective question and depends on row size
database size and others, but any opinions would be most welcome.It's more on what you do with the data and how you access it. Explain a
little of how you access this table and we can give a better answer.
--
Andrew J. Kelly
SQL Server MVP
"Stressed" <k@.c.co.uk> wrote in message
news:uIKG5g7SDHA.2248@.TK2MSFTNGP11.phx.gbl...
> SQL20 Ent SP3 WIN2K Adv SP3
> I'm hoping for some guidance here.
> I've a single table with 184,000,000 rows, I think this is probably
> a good candidate for some kind of partitioning.
> My question is, how many rows would constitute a decent sized
> table, before partitioning?
> I know it's a very subjective question and depends on row size
> database size and others, but any opinions would be most welcome.
>|||Sorry,
In addition to the previous, Data Junction is used to load the data, query
analyzer is used by our analysts to query the data.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ehcR3u7SDHA.2148@.TK2MSFTNGP11.phx.gbl...
> It's more on what you do with the data and how you access it. Explain a
> little of how you access this table and we can give a better answer.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Stressed" <k@.c.co.uk> wrote in message
> news:uIKG5g7SDHA.2248@.TK2MSFTNGP11.phx.gbl...
> > SQL20 Ent SP3 WIN2K Adv SP3
> >
> > I'm hoping for some guidance here.
> >
> > I've a single table with 184,000,000 rows, I think this is probably
> > a good candidate for some kind of partitioning.
> >
> > My question is, how many rows would constitute a decent sized
> > table, before partitioning?
> >
> > I know it's a very subjective question and depends on row size
> > database size and others, but any opinions would be most welcome.
> >
> >
>|||When you do these updates right after you import the data does it involve
any of the previous rows or just the new ones? How about the subsets, are
they created only from the new data? Sounds like you work mainly with
blocks of data, maybe by date. If that's true then you may consider
partitioning the data by date (weeks, Month, quarter etc) so it's easier to
work with only the relevant data. If you do keep it in a single table then
make sure you have a clustered index on the column(s) that will allow you to
differentiate the current data. Otherwise you may be scanning the entire
table over and over for these updates and queries.
--
Andrew J. Kelly
SQL Server MVP
"Stressed" <k@.c.co.uk> wrote in message
news:uaxCE27SDHA.1556@.TK2MSFTNGP10.phx.gbl...
> Thanks for taking the time to reply.
> Table is used in a warehousing type environment. Initially a bulk load,
> then incremental loads, approx once a month. Once the load has taken
> place a series of updates are performed, following that, the table is
> used to create subsets of data in another database, for shipping to our
> client sites for review.
> In short, much loading, some updating, much querying.
> I hope this gives a reasonable insight.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ehcR3u7SDHA.2148@.TK2MSFTNGP11.phx.gbl...
> > It's more on what you do with the data and how you access it. Explain a
> > little of how you access this table and we can give a better answer.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Stressed" <k@.c.co.uk> wrote in message
> > news:uIKG5g7SDHA.2248@.TK2MSFTNGP11.phx.gbl...
> > > SQL20 Ent SP3 WIN2K Adv SP3
> > >
> > > I'm hoping for some guidance here.
> > >
> > > I've a single table with 184,000,000 rows, I think this is probably
> > > a good candidate for some kind of partitioning.
> > >
> > > My question is, how many rows would constitute a decent sized
> > > table, before partitioning?
> > >
> > > I know it's a very subjective question and depends on row size
> > > database size and others, but any opinions would be most welcome.
> > >
> > >
> >
> >
>

Maximum number of rows?

Hello Everyone
I have been told by our DBA that SQL Server 2000 has problem
with tables that contain more than 13 million reocords, in particula
with indexes. He was not more specific(!)
Our group is in the process of developing an application and a couple
of the tables we will be using will contain up to 50 million records
My worry here, is that if what our DBA said is correct that we need t
take this into account early in the DB design phase because an
structural changes to the DB have impact on the procedures, functions
etc as well as the application
I had not heard any such statement previously made and in fact my experienc
has been quite positive with larger tables (over 20 million).
Nevertheless I am still somewhat concerned at the prospect of finding
out he was correct "after the fact", so to speak, and thus causing considerabl
effort going into a work around after we are in production
If anyone has heard or knows of anything of the sort I would be very thankfu
for your advice or to hear your experiences, especially under wha
circumstances if any does SQL Server have problems
Regards
MariN of rows is limited only with storage capacity (check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp).
Personally, I worked with a table with 3.5 billions rows without a
problem...
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Mario" <anonymous@.discussions.microsoft.com> wrote in message
news:94D5DF8D-D5BE-4563-A4A3-E29485D36E09@.microsoft.com...
> Hello Everyone:
> I have been told by our DBA that SQL Server 2000 has problems
> with tables that contain more than 13 million reocords, in particular
> with indexes. He was not more specific(!).
> Our group is in the process of developing an application and a couple
> of the tables we will be using will contain up to 50 million records.
> My worry here, is that if what our DBA said is correct that we need to
> take this into account early in the DB design phase because any
> structural changes to the DB have impact on the procedures, functions,
> etc as well as the application.
> I had not heard any such statement previously made and in fact my
experience
> has been quite positive with larger tables (over 20 million).
> Nevertheless I am still somewhat concerned at the prospect of finding
> out he was correct "after the fact", so to speak, and thus causing
considerable
> effort going into a work around after we are in production.
> If anyone has heard or knows of anything of the sort I would be very
thankful
> for your advice or to hear your experiences, especially under what
> circumstances if any does SQL Server have problems.
> Regards,
> Mario
>|||I am not aware of any problems at all. 13 million rows is quite a small
number as far as SQL Server is concerned. I have no ideas where the "DBA"
plucked this figure from - I would be interested to hear where. Even if you
use bigint as the PK for a table you could have 9,223,372,036,854,775,807
rows (I can't say that number, so have to write it down as digits). :-)
--
Mark Allison
SQL Server MVP
http://www.allisonmitchell.com
"Mario" <anonymous@.discussions.microsoft.com> wrote in message
news:94D5DF8D-D5BE-4563-A4A3-E29485D36E09@.microsoft.com...
> Hello Everyone:
> I have been told by our DBA that SQL Server 2000 has problems
> with tables that contain more than 13 million reocords, in particular
> with indexes. He was not more specific(!).
> Our group is in the process of developing an application and a couple
> of the tables we will be using will contain up to 50 million records.
> My worry here, is that if what our DBA said is correct that we need to
> take this into account early in the DB design phase because any
> structural changes to the DB have impact on the procedures, functions,
> etc as well as the application.
> I had not heard any such statement previously made and in fact my
experience
> has been quite positive with larger tables (over 20 million).
> Nevertheless I am still somewhat concerned at the prospect of finding
> out he was correct "after the fact", so to speak, and thus causing
considerable
> effort going into a work around after we are in production.
> If anyone has heard or knows of anything of the sort I would be very
thankful
> for your advice or to hear your experiences, especially under what
> circumstances if any does SQL Server have problems.
> Regards,
> Mario
>|||You'll run against the storage capacity limits of SQL Server long before you
reach that number. The maximum size for a database is 1,048,516 Tera Bytes,
and you're talking about approximately 9,000,000 Tera rows.
"DBA" indeed. Mario, both Mark and me are on the market at the moment ;-)
--
Jacco Schalkwijk
SQL Server MVP
"Mark Allison" <mark@.allisonmitchellyourpants.c0m> wrote in message
news:uieUWhW8DHA.2524@.TK2MSFTNGP11.phx.gbl...
> I am not aware of any problems at all. 13 million rows is quite a small
> number as far as SQL Server is concerned. I have no ideas where the "DBA"
> plucked this figure from - I would be interested to hear where. Even if
you
> use bigint as the PK for a table you could have 9,223,372,036,854,775,807
> rows (I can't say that number, so have to write it down as digits). :-)
> --
> Mark Allison
> SQL Server MVP
> http://www.allisonmitchell.com
> "Mario" <anonymous@.discussions.microsoft.com> wrote in message
> news:94D5DF8D-D5BE-4563-A4A3-E29485D36E09@.microsoft.com...
> > Hello Everyone:
> >
> > I have been told by our DBA that SQL Server 2000 has problems
> > with tables that contain more than 13 million reocords, in particular
> > with indexes. He was not more specific(!).
> >
> > Our group is in the process of developing an application and a couple
> > of the tables we will be using will contain up to 50 million records.
> >
> > My worry here, is that if what our DBA said is correct that we need to
> > take this into account early in the DB design phase because any
> > structural changes to the DB have impact on the procedures, functions,
> > etc as well as the application.
> >
> > I had not heard any such statement previously made and in fact my
> experience
> > has been quite positive with larger tables (over 20 million).
> > Nevertheless I am still somewhat concerned at the prospect of finding
> > out he was correct "after the fact", so to speak, and thus causing
> considerable
> > effort going into a work around after we are in production.
> >
> > If anyone has heard or knows of anything of the sort I would be very
> thankful
> > for your advice or to hear your experiences, especially under what
> > circumstances if any does SQL Server have problems.
> >
> > Regards,
> > Mario
> >
>|||Jacco
my feelings exactly.
Earlier today I asked him to provide a source for his statement should anything turn up I'll post back here
Thanks again
Mario

Maximum number of rows?

Hello Everyone:
I have been told by our DBA that SQL Server 2000 has problems
with tables that contain more than 13 million reocords, in particular
with indexes. He was not more specific(!).
Our group is in the process of developing an application and a couple
of the tables we will be using will contain up to 50 million records.
My worry here, is that if what our DBA said is correct that we need to
take this into account early in the DB design phase because any
structural changes to the DB have impact on the procedures, functions,
etc as well as the application.
I had not heard any such statement previously made and in fact my experience
has been quite positive with larger tables (over 20 million).
Nevertheless I am still somewhat concerned at the prospect of finding
out he was correct "after the fact", so to speak, and thus causing considera
ble
effort going into a work around after we are in production.
If anyone has heard or knows of anything of the sort I would be very thankfu
l
for your advice or to hear your experiences, especially under what
circumstances if any does SQL Server have problems.
Regards,
MarioN of rows is limited only with storage capacity (check
http://msdn.microsoft.com/library/d...br />
8dbn.asp).
Personally, I worked with a table with 3.5 billions rows without a
problem...
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Mario" <anonymous@.discussions.microsoft.com> wrote in message
news:94D5DF8D-D5BE-4563-A4A3-E29485D36E09@.microsoft.com...
> Hello Everyone:
> I have been told by our DBA that SQL Server 2000 has problems
> with tables that contain more than 13 million reocords, in particular
> with indexes. He was not more specific(!).
> Our group is in the process of developing an application and a couple
> of the tables we will be using will contain up to 50 million records.
> My worry here, is that if what our DBA said is correct that we need to
> take this into account early in the DB design phase because any
> structural changes to the DB have impact on the procedures, functions,
> etc as well as the application.
> I had not heard any such statement previously made and in fact my
experience
> has been quite positive with larger tables (over 20 million).
> Nevertheless I am still somewhat concerned at the prospect of finding
> out he was correct "after the fact", so to speak, and thus causing
considerable
> effort going into a work around after we are in production.
> If anyone has heard or knows of anything of the sort I would be very
thankful
> for your advice or to hear your experiences, especially under what
> circumstances if any does SQL Server have problems.
> Regards,
> Mario
>|||I am not aware of any problems at all. 13 million rows is quite a small
number as far as SQL Server is concerned. I have no ideas where the "DBA"
plucked this figure from - I would be interested to hear where. Even if you
use bigint as the PK for a table you could have 9,223,372,036,854,775,807
rows (I can't say that number, so have to write it down as digits). :-)
Mark Allison
SQL Server MVP
http://www.allisonmitchell.com
"Mario" <anonymous@.discussions.microsoft.com> wrote in message
news:94D5DF8D-D5BE-4563-A4A3-E29485D36E09@.microsoft.com...
> Hello Everyone:
> I have been told by our DBA that SQL Server 2000 has problems
> with tables that contain more than 13 million reocords, in particular
> with indexes. He was not more specific(!).
> Our group is in the process of developing an application and a couple
> of the tables we will be using will contain up to 50 million records.
> My worry here, is that if what our DBA said is correct that we need to
> take this into account early in the DB design phase because any
> structural changes to the DB have impact on the procedures, functions,
> etc as well as the application.
> I had not heard any such statement previously made and in fact my
experience
> has been quite positive with larger tables (over 20 million).
> Nevertheless I am still somewhat concerned at the prospect of finding
> out he was correct "after the fact", so to speak, and thus causing
considerable
> effort going into a work around after we are in production.
> If anyone has heard or knows of anything of the sort I would be very
thankful
> for your advice or to hear your experiences, especially under what
> circumstances if any does SQL Server have problems.
> Regards,
> Mario
>|||You'll run against the storage capacity limits of SQL Server long before you
reach that number. The maximum size for a database is 1,048,516 Tera Bytes,
and you're talking about approximately 9,000,000 Tera rows.
"DBA" indeed. Mario, both Mark and me are on the market at the moment ;-)
Jacco Schalkwijk
SQL Server MVP
"Mark Allison" <mark@.allisonmitchellyourpants.c0m> wrote in message
news:uieUWhW8DHA.2524@.TK2MSFTNGP11.phx.gbl...
> I am not aware of any problems at all. 13 million rows is quite a small
> number as far as SQL Server is concerned. I have no ideas where the "DBA"
> plucked this figure from - I would be interested to hear where. Even if
you
> use bigint as the PK for a table you could have 9,223,372,036,854,775,807
> rows (I can't say that number, so have to write it down as digits). :-)
> --
> Mark Allison
> SQL Server MVP
> http://www.allisonmitchell.com
> "Mario" <anonymous@.discussions.microsoft.com> wrote in message
> news:94D5DF8D-D5BE-4563-A4A3-E29485D36E09@.microsoft.com...
> experience
> considerable
> thankful
>|||Jacco,
my feelings exactly.
Earlier today I asked him to provide a source for his statement should anyth
ing turn up I'll post back here.
Thanks again!
Mario

maximum number of rows to fetch

Anybody knows how to change the default 1000 of "maximum number of rows to
fetch" in EM to sth else?
You get that prompt by Open Table-->Return Top...
Thanks,
Wenlei
Wenlei Fang wrote:
> Anybody knows how to change the default 1000 of "maximum number of
> rows to fetch" in EM to sth else?
> You get that prompt by Open Table-->Return Top...
> Thanks,
> Wenlei
I wouldn't use SQL EM for that anyway. Use QA and add a TOP X to the
query to return only the rows you need. SQL EM leaves pages on the
server until you scroll to see them in the UI. This leaves shared locks
on the server on the unfetched pages.
David Gugick
Imceda Software
www.imceda.com
|||I'm totally agree with your point. But what I want to do is to change it to
a smaller number, say 5, so we won't lock any rows and still can view some
sample data.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmPR5vHFFHA.2756@.TK2MSFTNGP15.phx.gbl...
> Wenlei Fang wrote:
> I wouldn't use SQL EM for that anyway. Use QA and add a TOP X to the query
> to return only the rows you need. SQL EM leaves pages on the server until
> you scroll to see them in the UI. This leaves shared locks on the server
> on the unfetched pages.
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||AFAIK there is no way to do this. No documented method anyway.
I second David G's advice. Avoid EM and use Query Analyzer to sample
and view data.
David Portas
SQL Server MVP
|||Davids,
Thank you for your input. Sometimes it is easier and quicker to view table
structure, sample data and insertion through EM than QA as long as you know
what you are doing. And I'm looking for undoc method such as registry hack
etc.
Regards,
Wenlei
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108653945.639057.128320@.g14g2000cwa.googlegr oups.com...
> AFAIK there is no way to do this. No documented method anyway.
> I second David G's advice. Avoid EM and use Query Analyzer to sample
> and view data.
> --
> David Portas
> SQL Server MVP
> --
>

maximum number of rows to fetch

Anybody knows how to change the default 1000 of "maximum number of rows to
fetch" in EM to sth else?
You get that prompt by Open Table-->Return Top...
Thanks,
WenleiWenlei Fang wrote:
> Anybody knows how to change the default 1000 of "maximum number of
> rows to fetch" in EM to sth else?
> You get that prompt by Open Table-->Return Top...
> Thanks,
> Wenlei
I wouldn't use SQL EM for that anyway. Use QA and add a TOP X to the
query to return only the rows you need. SQL EM leaves pages on the
server until you scroll to see them in the UI. This leaves shared locks
on the server on the unfetched pages.
David Gugick
Imceda Software
www.imceda.com|||I'm totally agree with your point. But what I want to do is to change it to
a smaller number, say 5, so we won't lock any rows and still can view some
sample data.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmPR5vHFFHA.2756@.TK2MSFTNGP15.phx.gbl...
> Wenlei Fang wrote:
> I wouldn't use SQL EM for that anyway. Use QA and add a TOP X to the query
> to return only the rows you need. SQL EM leaves pages on the server until
> you scroll to see them in the UI. This leaves shared locks on the server
> on the unfetched pages.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||AFAIK there is no way to do this. No documented method anyway.
I second David G's advice. Avoid EM and use Query Analyzer to sample
and view data.
David Portas
SQL Server MVP
--|||Davids,
Thank you for your input. Sometimes it is easier and quicker to view table
structure, sample data and insertion through EM than QA as long as you know
what you are doing. And I'm looking for undoc method such as registry hack
etc.
Regards,
Wenlei
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108653945.639057.128320@.g14g2000cwa.googlegroups.com...
> AFAIK there is no way to do this. No documented method anyway.
> I second David G's advice. Avoid EM and use Query Analyzer to sample
> and view data.
> --
> David Portas
> SQL Server MVP
> --
>

maximum number of rows to fetch

Anybody knows how to change the default 1000 of "maximum number of rows to
fetch" in EM to sth else?
You get that prompt by Open Table-->Return Top...
Thanks,
WenleiWenlei Fang wrote:
> Anybody knows how to change the default 1000 of "maximum number of
> rows to fetch" in EM to sth else?
> You get that prompt by Open Table-->Return Top...
> Thanks,
> Wenlei
I wouldn't use SQL EM for that anyway. Use QA and add a TOP X to the
query to return only the rows you need. SQL EM leaves pages on the
server until you scroll to see them in the UI. This leaves shared locks
on the server on the unfetched pages.
--
David Gugick
Imceda Software
www.imceda.com|||I'm totally agree with your point. But what I want to do is to change it to
a smaller number, say 5, so we won't lock any rows and still can view some
sample data.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmPR5vHFFHA.2756@.TK2MSFTNGP15.phx.gbl...
> Wenlei Fang wrote:
>> Anybody knows how to change the default 1000 of "maximum number of
>> rows to fetch" in EM to sth else?
>> You get that prompt by Open Table-->Return Top...
>> Thanks,
>> Wenlei
> I wouldn't use SQL EM for that anyway. Use QA and add a TOP X to the query
> to return only the rows you need. SQL EM leaves pages on the server until
> you scroll to see them in the UI. This leaves shared locks on the server
> on the unfetched pages.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||AFAIK there is no way to do this. No documented method anyway.
I second David G's advice. Avoid EM and use Query Analyzer to sample
and view data.
--
David Portas
SQL Server MVP
--|||Davids,
Thank you for your input. Sometimes it is easier and quicker to view table
structure, sample data and insertion through EM than QA as long as you know
what you are doing. And I'm looking for undoc method such as registry hack
etc.
Regards,
Wenlei
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108653945.639057.128320@.g14g2000cwa.googlegroups.com...
> AFAIK there is no way to do this. No documented method anyway.
> I second David G's advice. Avoid EM and use Query Analyzer to sample
> and view data.
> --
> David Portas
> SQL Server MVP
> --
>

maximum limit on results

Is there a maximum limit on results returned? I didn't think so, but I'm
seeing only ~3000 rows of a 100,000+ row table in my report.Sorry, answered my own question. I was confusing pages and # of results.
Still, is there an upper limit? I cannot find any other documentation
besides 4 MB is an upper limit on report size.|||There is no limit on the number of rows per dataset or on the number of
datasets per report. However, you should consider that datasets with many
rows will need more processing time and reports with complex layouts and
many pages more rendering time.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy S." <andymcdba1@.nospam.yahoo.com> wrote in message
news:%239t$dUcwEHA.1308@.TK2MSFTNGP09.phx.gbl...
> Sorry, answered my own question. I was confusing pages and # of results.
> Still, is there an upper limit? I cannot find any other documentation
> besides 4 MB is an upper limit on report size.
>

Maximum height for table

How do I set the maximum vertical size of a table? I would like to set it based on total vertical size in inches or a maximum number of rows returned.

Thanks for any help.

Brad

Click View -> Properties window. The dimensions for a table/matrix can be changed in this window.

|||

Hi Greg,

Do you know how does this height create any difference, what i mean is usually this doesnt restrict the table from expanding or so.

|||So you're saying that you want to cut off the rest of your data if it goes past the height you specify?

|||

Hi Greg -

I don't think I stated my question clearly. What I'm trying to do is limit the number of rows returned in my table. So for example, if the dataset connected to my table contained 30 rows, is it possible to cap the number that are returned to 20? If not, is it possible to set the "canGrow" property to a maximum height?

Thanks.

Brad

|||

I would do this in your SQL query.

SELECT TOP 20 * FROM .....

|||

Yes, but unfortunately I am in a situation where I cannot control the results of the stored procedure. I am only able to use pre-existing SPs and build the report.

Brad

|||

Please try visibility condition of that section, use =iif(rownumber(nothing)>=20,true,false).

Priyank

|||

That's a good thought but unfortunately does not do exactly what I need. Using my example from earlier, that code returns 20 rows of data, and then 10 blank lines. My table is still expanded to the full 30 rows however.

Any other ideas?

Thanks.

Brad

|||

Are you using it in the visibility of the section and not for the indivisual textbox. What i mean to say is select the complete detail section of the table and then in the visibility condition of the section write this condition, It shold supress the complete sectiona dn shold not give you blank rows.

|||

I had selected the textbox and the full table, but not the detail portion of the table.

Putting the code in the detail portion worked like a charm, thank you!

max(tag) for pair of matched rows (was "Need help on query")

I've got a table of transactions which are linked up in pairs based on the column 'Ref'. 'F's are the identifiers of each transaction while 'S's points to the 'F' of its matching pair. I need to select all the transactions with the larger 'Tag' for each pair, can someone point me in the right direction? :confused:

Tag Ref Type
-- -- --
1 200 F
1 201 S
2 201 F
2 200 S
3 202 F
3 203 S
4 203 F
4 202 S
5 204 F
5 205 S
6 205 F
6 204 SIs this what you are after?

select t.tag, t.ref, t.type
from t
join (select max(tag)as tag, ref from t
group by ref) b
on t.tag = b.tag
and t.ref = b.ref

giving you the result set

6 205 F
6 204 S
4 203 F
4 202 S
2 201 F
2 200 S|||Yes it is, thanks!

max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.
You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>
|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> column
> I
> numeric
>
|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:

> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>
>
|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...[vbcol=seagreen]
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:

max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> column
> I
> numeric
>|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:

> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>
>|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...[vbcol=seagreen]
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:
>

max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
>> I need to find the highest number in one column. Here is a catch, the
> column
>> type is varchar and some of the rows will have non number character in
>> it.
> I
>> am trying to find the highest number ignoring any row that is not a
> numeric
>> value. Here is my query.
>>
>> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
>> Thanks for any input.
>>
>|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:
> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> >
> > You may want to cast the column as an int within the max
> >
> >
> > SELECT max(cast(item_abrv as int)) as mx from item where
> > isnumeric(item_abrv) = 1
> >
> > "UGH" <nospam@.noSPam.com> wrote in message
> > news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> >> I need to find the highest number in one column. Here is a catch, the
> > column
> >> type is varchar and some of the rows will have non number character in
> >> it.
> > I
> >> am trying to find the highest number ignoring any row that is not a
> > numeric
> >> value. Here is my query.
> >>
> >>
> >> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> >>
> >> Thanks for any input.
> >>
> >>
> >
> >
>
>|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:
>> I tired that and I got an error. its said converting the varchar value
>> '100ISBN' to a column of data type int. I just need the query to leave
>> out
>> any field that has non numeric characters in it.
>> Thanks.
>> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
>> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>> >
>> > You may want to cast the column as an int within the max
>> >
>> >
>> > SELECT max(cast(item_abrv as int)) as mx from item where
>> > isnumeric(item_abrv) = 1
>> >
>> > "UGH" <nospam@.noSPam.com> wrote in message
>> > news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
>> >> I need to find the highest number in one column. Here is a catch, the
>> > column
>> >> type is varchar and some of the rows will have non number character in
>> >> it.
>> > I
>> >> am trying to find the highest number ignoring any row that is not a
>> > numeric
>> >> value. Here is my query.
>> >>
>> >>
>> >> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
>> >>
>> >> Thanks for any input.
>> >>
>> >>
>> >
>> >
>>