MAX and DATEDIFF: table

Showing posts with label table. Show all posts

Wednesday, March 21, 2012

Maybe I'm asking for too much.

I’m using oracle but I’m sure this problem will have similar solution to SQL. I have a list of string in a table (aa, bb, cc, dd). I also have a list of numbers that are not in the database. What I’m looking for is an output like this.

Aa, 11

Aa, 22

Bb, 11

Bb, 22

Cc, 11

Cc, 22

Dd, 11

Dd, 22

Is this possible, without having to insert the numbers in a database table and with a simple query? (this could be done with temporary table)

? Sure... SELECT YourTable.YourStringColumn, N.Number FROM YourTable CROSS JOIN ( SELECT 11 UNION ALL SELECT 22 ) N (Number) -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <ThE_lOtUs@.discussions..microsoft.com> wrote in message news:484c9f08-620e-46e9-bc35-83620c31d17d@.discussions.microsoft.com... I’m using oracle but I’m sure this problem will have similar solution to SQL. I have a list of string in a table (aa, bb, cc, dd). I also have a list of numbers that are not in the database. What I’m looking for is an output like this. Aa, 11 Aa, 22 Bb, 11 Bb, 22 Cc, 11 Cc, 22 Dd, 11 Dd, 22 Is this possible, without having to insert the numbers in a database table and with a simple query? (this could be done with temporary table)

May I create not unique clustered index?

Hi,
When create clustered index, the column must be unique? Or
I can do Create Clustered index index_name ON table
(column_name)?
Any input much appreciate!
JennyThere is nothing that stops you from having a clustered index on a
non-unique column. By having your clustered index on a unique column we get
a narrowed selectivity when quering on that column ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jenny" <jyu@.iseoptions.com> wrote in message
news:0cfc01c3a473$01686900$a301280a@.phx.gbl...
> Hi,
> When create clustered index, the column must be unique? Or
> I can do Create Clustered index index_name ON table
> (column_name)?
> Any input much appreciate!
> Jenny|||The column does not need to be unique.
"Jenny" <jyu@.iseoptions.com> wrote in message
news:0cfc01c3a473$01686900$a301280a@.phx.gbl...
> Hi,
> When create clustered index, the column must be unique? Or
> I can do Create Clustered index index_name ON table
> (column_name)?
> Any input much appreciate!
> Jenny|||no, they do not need to be unique
CREATE CLUSTERED INDEX IXNAME ON TABLENAME(COLUMNLIST)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Jenny" <jyu@.iseoptions.com> wrote in message
news:0cfc01c3a473$01686900$a301280a@.phx.gbl...
> Hi,
> When create clustered index, the column must be unique? Or
> I can do Create Clustered index index_name ON table
> (column_name)?
> Any input much appreciate!
> Jenny

Maxium Table Size, Change size?

Hello,
Can anyone table if table size can be increased? If so how?
Any help would be greatly appreciated.
Thanks in Advance.
SqLRattler
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
What is your problem?
I'm not sure if I understand your question.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> a crit dans le message de
news:ug409AvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can anyone table if table size can be increased? If so how?
> Any help would be greatly appreciated.
> Thanks in Advance.
> SqLRattler
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Maxium Table Size, Change size?

Hello,
Can anyone table if table size can be increased? If so how?
Any help would be greatly appreciated.
Thanks in Advance.
SqLRattler
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.What is your problem?
I'm not sure if I understand your question.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> a crit dans le message de
news:ug409AvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Can anyone table if table size can be increased? If so how?
> Any help would be greatly appreciated.
> Thanks in Advance.
> SqLRattler
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Monday, March 19, 2012

Maximum value of mulitple columns

Hi all

Using sql server 2005, Im trying in a query to get the maximum value of multiple columns of a table for each of its records.

What im trying to get is the last date an index was used using the table sys.dm_db_usage_stats using the date fields (last_user_seek, last_user_update...).

I looked around the forum for a solution but those i found dont seem to apply really easily to a query.

Anyone got a suggestion?

Dale:

There have been a number of discussions about a similar issue; give a look to this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=733186&SiteID=1

|||

Dale L.,

Try using new operators UNPIVOT and "CROSS APPLY".

Code Snippet

create table dbo.t1 (

pk int not null identity unique,

c1 int,

c2 int,

c3 int

)

insert into dbo.t1(c1, c2, c3) values(1, 2, 3)

insert into dbo.t1(c1, c2, c3) values(4, 6, 5)

insert into dbo.t1(c1, c2, c3) values(9, 7, 8)

select

a.pk,

b.max_value

from

dbo.t1 as a

cross apply

(

select

max(unpvt.[value]) as max_value

from

(

select

t.pk,

t.c1 as [1],

t.c2 as [2],

t.c3 as [3]

from

dbo.t1 as t

where

t.pk = a.pk

) as pvt

unpivot

([value] for [col] in ([1], [2], [3])) as unpvt

group by

unpvt.pk

) as b

drop table dbo.t1

AMB

Maximum Value

Hey guys, I have this query which tries to get the maximum values from a table.

Code Snippet

SELECT scbcrse_subj_code,

MAX(scbcrse_eff_term), scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code

FROM Courses

WHERE scbcrse_csta_code = ''A''

GROUP BY scbcrse_subj_code, SCBCRSE_CRSE_NUMB, scbcrse_coll_code, scbcrse_csta_code, scbcrse_title

ORDER BY scbcrse_subj_code

Sample Table

scbcrse_subj_code scbcrse_eff_term scbcrse_crse_numb scbcrse_coll_code scbcrse_title ACCT 200620 4010 SB Advanced Accounting ACCT 200530 4010 SB Financial Accounting IV

Now, there is a column which is called scbcrse_title which shows the title of the course, which in this case on the table the titles are different. One is called Advanced Accounting and the other is called Financial Accounting IV. My question is, how can I only show the highest number on the scbcrse_eff_term? The problem occurs when there are different titles.

Does scbcrrse_title need to be part of the group by? Can you just group on crse_numb, coll_code, and subj_code only ? Then subselect the title using the three group by values where eff_term is max? Something like the below (i did not check the code for errors sorry)

Code Snippet

SELECT C.scbcrse_subj_code,

C.scbcrse_crse_numb,

C.scbcrse_coll_code,

C.scbcrse_csta_code,

MAX(C.scbcrse_eff_term),

(SELECT scbcrse_title

FROM Courses

WHERE scbcrse_subj_code = C.scbcrse_subj_code

AND scbcrse_crse_numb = C.scbcrse_crse_numb

AND scbcrse_coll_code = C.scbcrse_coll_code

AND scbcrse_csta_code = C.scbcrse_csta_code

AND scbcrse_eff_term) = (SELECT MAX(C.scbcrse_eff_term) FROM Courses WHERE C.Cscbcrse_csta_code = 'A' GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code ) AS [Title]

FROM Courses C

WHERE C.Cscbcrse_csta_code = 'A'

GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code

ORDER BY scbcrse_subj_code

|||Thanks Dave, I'll check the code to see if it works. And yes, the GROUP BY command does force me to include scbcrse_title.

|||

Maybe this:

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as eff_term

FROM Courses

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||Diango,

You can do this without a GROUP BY as follows:

SQL Server 2000

SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code = ''A''
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code

In other words, choose all rows for Courses where the table does not contain a later (measured by eff_term) row with the same (subj_code, crse_numb, coll_code) combination. This assumes you want one result row for each (subj_code, crse_numb, coll_code) combination, and you can adjust the inner WHERE clause if this is not the set of columns that you want one row for each combination of.

In SQL Server 2005, you have another option:

SQL Server 2005

WITH Courses_ranked AS (
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code,
RANK() OVER (
PARTITION BY scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code
ORDER BY scbcrse_eff_term DESC
) AS rk
)
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses_ranked
WHERE rk = 1

Both solutions will return the "latest" row (or rows in the case of ties for latest) for each combination (scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code) in the table.

Steve Kass
Drew University
http://www.stevekass.com
|||The first solution almost worked but it had the problem that when there was a code that only had one value, it got omitted. The second solution I have never done before so I'm having some problems implementing it.|||I notice I left the = 'A' out of the inner query in the first solution. That could be it, but it could also be something about your data that you didn't mention. If you post some sample data and your adapted query that fails, I can take a look.

SK
|||That worked, you rock! As soon as I added the ''A'' in the inner join, it came back with the correct result.|||Steve, I don't mean to be a pain in the ass, but I have a new issue. I'm still a bit of a noob so bare with me. Your code worked great by the way, it did the job as intended. New update is required which I wasn't aware. I want to take into account those courses that do have '' I '' as well as the maximum value on the scbcrse_eff_term. I can do this easily by simply removing the WHERE clause where scbcrse_csta_code = ''A'' on both occasions. The thing is, for those courses that have the maximum value which have an '' I '' I need those courses removed completly. ( I don't mean delete those records that '' I '' , I just mean Omitting them some how.|||I hope I'm understanding, because you didn't give any specific example.

You want to see the latest row for each (subject code, course number, college code) from among those rows with csta code either 'A' or 'I', only if that latest row happens to be one of the 'A' rows. Note that if 'A' and 'I' are the only possible values of csta_code, you don't have to say WHERE scbcrse_csta_code IN ('A','I'). I think this will do it.

SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code IN ('A','I')
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
AND C.scbcrse_csta_code IN ('A','I')
)
AND Courses.scbcrse_csta_code = 'A'
ORDER BY scbcrse_subj_code
|||

Sorry, I should have explained myself a little better. Ok, this is your code:

AND C.scbcrse_csta_code = ''A''
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code

From the original code, we had a clause WHERE = ''A''. By having those two clauses, it returned the top value from scbcrse_eff_term Where scbcrse_csta_code = A, agreed? I found out later that the original requirement was wrong. The correct requirement was, get the highest scbcrse_eff_term as long as the class is active, or otherwise known as having a csta_code of ''A''. I know it sounds the same but let me explain further.

For example:

scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code

ACCT 4041 195600 A

ACCT 4041 200023 A

ACCT 4041 221457 I

From the original code, it should return the second row with a value of 200023 because it's the highest row that has a csta_code of ''A''. Here is where it changes, on the example, since the highest value for the class is 221457 and has a csta_code of '' I '' the class has become inactive and therefore ACCT 4041 must no longer show up in our query. So in essence, every class that has the highest scbcrse_eff_term with a value of csta_code '' I '' should not show up at all in our query return. So ACCT 4041 would not show up on our list.

|||

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as scbcrse_eff_term

FROM Courses

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||Dale, that's not working for me. I'm getting some errors when I try to implement it. I get the error of it's not a group by function. I also think that the code would return the maximum value of the courses that have a csta_code value of ''A'', not taking into account '' I ''. Which I do want to take into account '' I '' , but if the maximum scbcrse_eff_term of a course has a csta_code of '' I '' the class should be omitted completely from the result. It's not just eliminating all the '' I '', but it's eliminating all the courses from the list that has a maximum scbcrse_eff_term with scbcrse_csta_code of '' I ''. I don't know if that makes sense to you.|||

See if this is better.

I forgot the group by in the subquery.

This will gather up all the courses with the most current date, then only keep the ones that are A.

My understanding from what you've been saying is that you want to disregard all entries for the class if its current row is an I.

This should do just that.

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as scbcrse_eff_term

FROM Courses

GROUP BY scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||

It doesn't bring back the desired result. Let me show you what I mean.

scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code

ACCT 4041 195600 A

ACCT 4041 200023 A

ACCT 4041 221457 I

This is an example of a class that has gone inactive. This table has been poorly designed, which is why it's so challenging to create the proper effect. As you can see from this class, it has become inactive. When we run your query, it returns for example ACCT 4041 200023 and a code of A. The desired result is that this course doesn't come back at all because the maximum value for it is 221457 and because it has a scbcrse_csta_code of '' I ''.

Steve's code is great because it really can eliminate duplicates and it also brings the highest value of a course no matter what csta_code it has, because I removed the two WHERE clauses. Now, from the result set, I need to remove the courses that have a maximum value with a csta_code of '' I '' combination.

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 Rowsize screw up by MSSQL 2000

I've got a table that has too many fields to fit on one page ( So i get that
warning when adding fields ).
Most of those fields were added after the creation of the table.
When I try to edit a record, I get the maxrowsize error.
When I try to add a new record to this table with only one field filled in,
I get the maxrowsize error.
So now I change one field ( from varchar 50 -> varchar 51 ) via Entreprise
Manager.
All changes via Entreprise Manager do a drop and recreate of a table.
Then I can edit the existing records and add new records to it without a
problem.
Is there a difference for MSSQL of you add fields <-> creating the table in
one piece?
Is this a known problem/bug ? How can I avoid this in the future ?
PS : Still awaiting answer from customer on SP-version installed there but
would be at least 3 because it's on Win2K3.
Regards,
Sven Peeters
BelgiumSven
Don't use EM . Perfom such operation in the QA
CREATE TABLE eee
(
col1 VARCHAR(4000),
col2 VARCHAR(4000),
col3 VARCHAR(1000)
)
--Warning: The table 'eee' has been
--created but its maximum row size (9027)
-- exceeds the maximum number of bytes per row (8060).
-- INSERT or UPDATE of a row in this table will fail if
-- the resulting row length exceeds 8060 bytes.
If you deal with large amount of data you might consider using TEXT datatype
instead.
CREATE TABLE eee11
(
col1 TEXT,
col2 TEXT,
col3 VARCHAR(1000)
)
DROP TABLE eee,eee11
"Sven Peeters" <SvenPeeters@.discussions.microsoft.com> wrote in message
news:281B4941-F6EE-4098-A7E1-DCA82C039B36@.microsoft.com...
> I've got a table that has too many fields to fit on one page ( So i get
> that
> warning when adding fields ).
> Most of those fields were added after the creation of the table.
> When I try to edit a record, I get the maxrowsize error.
> When I try to add a new record to this table with only one field filled
> in,
> I get the maxrowsize error.
> So now I change one field ( from varchar 50 -> varchar 51 ) via Entreprise
> Manager.
> All changes via Entreprise Manager do a drop and recreate of a table.
> Then I can edit the existing records and add new records to it without a
> problem.
> Is there a difference for MSSQL of you add fields <-> creating the table
> in
> one piece?
> Is this a known problem/bug ? How can I avoid this in the future ?
> PS : Still awaiting answer from customer on SP-version installed there but
> would be at least 3 because it's on Win2K3.
> Regards,
> Sven Peeters
> Belgium|||That's not the problem, most fields were added by T-SQL command.
Cannot change/add records to this table ( which has data below the maxsize )
.
I change one field ( make it even bigger ) in EM and after that I can
change/add records again.
That's the same table and data, only difference is that the table was
dropped and recreated ( with data ).
"Uri Dimant" wrote:

> Sven
> Don't use EM . Perfom such operation in the QA
>
> CREATE TABLE eee
> (
> col1 VARCHAR(4000),
> col2 VARCHAR(4000),
> col3 VARCHAR(1000)
> )
> --Warning: The table 'eee' has been
> --created but its maximum row size (9027)
> -- exceeds the maximum number of bytes per row (8060).
> -- INSERT or UPDATE of a row in this table will fail if
> -- the resulting row length exceeds 8060 bytes.
>
> If you deal with large amount of data you might consider using TEXT dataty
pe
> instead.
> CREATE TABLE eee11
> (
> col1 TEXT,
> col2 TEXT,
> col3 VARCHAR(1000)
> )
>
> DROP TABLE eee,eee11
>
> "Sven Peeters" <SvenPeeters@.discussions.microsoft.com> wrote in message
> news:281B4941-F6EE-4098-A7E1-DCA82C039B36@.microsoft.com...
>
>|||PS : SQL Version : 8.00.760 (sp3)
"Sven Peeters" wrote:

> I've got a table that has too many fields to fit on one page ( So i get th
at
> warning when adding fields ).
> Most of those fields were added after the creation of the table.
> When I try to edit a record, I get the maxrowsize error.
> When I try to add a new record to this table with only one field filled in
,
> I get the maxrowsize error.
> So now I change one field ( from varchar 50 -> varchar 51 ) via Entreprise
> Manager.
> All changes via Entreprise Manager do a drop and recreate of a table.
> Then I can edit the existing records and add new records to it without a
> problem.
> Is there a difference for MSSQL of you add fields <-> creating the table i
n
> one piece?
> Is this a known problem/bug ? How can I avoid this in the future ?
> PS : Still awaiting answer from customer on SP-version installed there but
> would be at least 3 because it's on Win2K3.
> Regards,
> Sven Peeters
> Belgium

maximum row size.. 8060 bytes ?

I got a warning like this.
Warning: The table 'tbSOTransHeader' has been created but
its maximum row size (9911) exceeds the maximum number of
bytes per row (8060). INSERT or UPDATE of a row in this
table will fail if the resulting row length exceeds 8060
bytes.
My questions are :
1. Is it true max row size is 8060 bytes ?
2. Can we make it larger ?
Thanks.Yes the max amount of actual data in the row can only be 8060. You can
create a table, view etc larger than that with variable length columns as
long as when populated they don't total more than that. NO you can't make
it larger.
--
Andrew J. Kelly
SQL Server MVP
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:082a01c3728f$48917d50$a301280a@.phx.gbl...
> I got a warning like this.
> Warning: The table 'tbSOTransHeader' has been created but
> its maximum row size (9911) exceeds the maximum number of
> bytes per row (8060). INSERT or UPDATE of a row in this
> table will fail if the resulting row length exceeds 8060
> bytes.
> My questions are :
> 1. Is it true max row size is 8060 bytes ?
> 2. Can we make it larger ?
> Thanks.
>|||Is there a function or stored procedure in T-sql to
calculate bytes in a row ?
thx.
>--Original Message--
>Yes the max amount of actual data in the row can only be
8060. You can
>create a table, view etc larger than that with variable
length columns as
>long as when populated they don't total more than that.
NO you can't make
>it larger.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:082a01c3728f$48917d50$a301280a@.phx.gbl...
>> I got a warning like this.
>> Warning: The table 'tbSOTransHeader' has been created
but
>> its maximum row size (9911) exceeds the maximum number
of
>> bytes per row (8060). INSERT or UPDATE of a row in this
>> table will fail if the resulting row length exceeds 8060
>> bytes.
>> My questions are :
>> 1. Is it true max row size is 8060 bytes ?
>> 2. Can we make it larger ?
>> Thanks.
>>
>
>.
>|||use datalength.
ex: select datalength(col1) + datalength(col2) ... from <table>
--
-Vishal
kresna rudy kurniawan <kresnark@.yahoo.com> wrote in message
news:163c01c37293$55393f80$a601280a@.phx.gbl...
> Is there a function or stored procedure in T-sql to
> calculate bytes in a row ?
> thx.
>
> >--Original Message--
> >Yes the max amount of actual data in the row can only be
> 8060. You can
> >create a table, view etc larger than that with variable
> length columns as
> >long as when populated they don't total more than that.
> NO you can't make
> >it larger.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:082a01c3728f$48917d50$a301280a@.phx.gbl...
> >> I got a warning like this.
> >>
> >> Warning: The table 'tbSOTransHeader' has been created
> but
> >> its maximum row size (9911) exceeds the maximum number
> of
> >> bytes per row (8060). INSERT or UPDATE of a row in this
> >> table will fail if the resulting row length exceeds 8060
> >> bytes.
> >>
> >> My questions are :
> >>
> >> 1. Is it true max row size is 8060 bytes ?
> >> 2. Can we make it larger ?
> >>
> >> Thanks.
> >>
> >>
> >
> >
> >.
> >|||kresna rudy kurniawan wrote:
> I got a warning like this.
> Warning: The table 'tbSOTransHeader' has been created but
> its maximum row size (9911) exceeds the maximum number of
> bytes per row (8060). INSERT or UPDATE of a row in this
> table will fail if the resulting row length exceeds 8060
> bytes.
> My questions are :
> 1. Is it true max row size is 8060 bytes ?
> 2. Can we make it larger ?
> Thanks.
All columns of the row have to fit in the page (maximum 8060 bytes),
except for data types text, ntext and image. For those data types, only
16 bytes (for each text/ntext/image column) are needed in the page that
stores the row.
So if the current data does not fit the page, it might be an option to
change large char, nchar, varchar or nvarchar columns to text or ntext.
Hope this helps,
Gert-Jan

maximum row size problem

Hi,
I've got a table in a database [columns: int, int, int, nvarchar(100), nvarchar(200), nvarchar(500), nvarchar(4000) ]

The problem I have is that when I try to insert a record, sqlserver is returning "Cannot create a row of size 9629 which is greater than the allowable maximum of 8060."

I had a search before posting here and best answer I could find was to alter maxlen in sysindexes - however, I'm far from being an expert on sqlserver so I dont really want to just blindly alter things and find I screw something up.

Any ideas on how to solve this?

Thanksnvarchar(4000)? Why? Why not an NTEXT?

NTEXT can be up to 2gb, and does NOT count against the limit.

Otherwise you can NOT change this limit. 8060 is a hardcoded limit.|||ok, i'll try that, thanks

Maximum Row Size Limitation

Is the maximum row size per table really limited to
8K bytes? Or is this the internal organization of SQL
data structures. I have imported a legacy table that
wound up having over 100 columns that came in as
NVARCHAR(255) or roughly 25K + in size. Any comments?
ThanksIt will allow you to create the object with the warning something as
The table % has been created but its maximum row size (%) exceeds the
maximum number of bytes per row (8060)
Ex:
create table test_size
(i1 nvarchar(255),
i2 nvarchar(255),
i3 nvarchar(255),
i4 nvarchar(255),
i6 nvarchar(255),
i8 nvarchar(255),
i9 nvarchar(255),
i10 nvarchar(255),
i11 nvarchar(255),
i12 nvarchar(255),
i14 nvarchar(255),
i19 nvarchar(255),
i20 nvarchar(255),
i21 nvarchar(255),
i22 nvarchar(255),
i23 nvarchar(255),
i24 nvarchar(255),
i30 nvarchar(255),
i31 nvarchar(255),
i32 nvarchar(255),
i33 nvarchar(255))
This is because you are using nvarchar datatype which is variable length
data type. Hence it will give you
error at runtime if actual data that you are going to insert goes beyond
8060 bytes. Also point to be noted
that nvarchar table 2 bytes to store a single character.
So SQL server is happy to create a table though a table crosses limit of
8060 bytes per row so far you use
variable data type like varchar/nvarchar but it will throw you an error
while inserting/updating data if actual
row size that a row will occupy goes beyond this limit.
--
-Vishal
"wringland" <bill@.tiati.com> wrote in message
news:001001c34fa3$3447a540$a401280a@.phx.gbl...
> Is the maximum row size per table really limited to
> 8K bytes? Or is this the internal organization of SQL
> data structures. I have imported a legacy table that
> wound up having over 100 columns that came in as
> NVARCHAR(255) or roughly 25K + in size. Any comments?
> Thanks|||And Dinesh is referring to the "hard" limit of the row size. When you have
varchar columns, you need to figure in the "soft" factor: the actual limit
is applied to the actual size of content. If you have 100 nvarchar(255)
columns and each column has only 1 character in it, the row size would be
(100 (columns) x 1 (character each column) x 2 (for Nvarchar) + 9 (row
overhead) ) = 209 bytes.
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:OsEUMR6TDHA.3700@.tk2msftngp13.phx.gbl...
> Bill,
> The maximum row size is 8060 bytes except for blob data types as TEXT ,
> IMAGE etc.That means your 25K table wont fit in.Moreover, you are using
the
> unicode datatype, NVARCHAR, which itself take twice as much storage space
as
> non-unicode data types.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "wringland" <bill@.tiati.com> wrote in message
> news:001001c34fa3$3447a540$a401280a@.phx.gbl...
> > Is the maximum row size per table really limited to
> > 8K bytes? Or is this the internal organization of SQL
> > data structures. I have imported a legacy table that
> > wound up having over 100 columns that came in as
> > NVARCHAR(255) or roughly 25K + in size. Any comments?
> >
> > Thanks
>|||Thanks for the reply. Can you perhaps explain why the
table was imported into SQL and the table design show the
proper format and size for the columns. All does seem to
be well with the data.
Thanks
>--Original Message--
>Bill,
>The maximum row size is 8060 bytes except for blob data
types as TEXT ,
>IMAGE etc.That means your 25K table wont fit in.Moreover,
you are using the
>unicode datatype, NVARCHAR, which itself take twice as
much storage space as
>non-unicode data types.
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"wringland" <bill@.tiati.com> wrote in message
>news:001001c34fa3$3447a540$a401280a@.phx.gbl...
>> Is the maximum row size per table really limited to
>> 8K bytes? Or is this the internal organization of SQL
>> data structures. I have imported a legacy table that
>> wound up having over 100 columns that came in as
>> NVARCHAR(255) or roughly 25K + in size. Any comments?
>> Thanks
>
>.
>|||> Thanks for the reply. Can you perhaps explain why the
> table was imported into SQL and the table design show the
> proper format and size for the columns. All does seem to
> be well with the data.
Because the data is not "full" - you will only get an error if you attempt
to put more than 8,060 bytes of *actual data* into the table. I am guessing
that many of your nvarchar(255) columns are NULL or blank.|||Bill,
As mentioned in other posts, SQL server will let you create the table with a
warning about data truncation.While inserting data, the moment a particular
rowsize exceeds 8060, the rest would be truncated and you wouldnt even
know.Means while querying the table, you may be looking at incomplete and
thus false data.
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"wringland" <bill@.tiati.com> wrote in message
news:009201c34fa7$6db8dbb0$a401280a@.phx.gbl...
> Thanks for the reply. Can you perhaps explain why the
> table was imported into SQL and the table design show the
> proper format and size for the columns. All does seem to
> be well with the data.
> Thanks
> >--Original Message--
> >Bill,
> >
> >The maximum row size is 8060 bytes except for blob data
> types as TEXT ,
> >IMAGE etc.That means your 25K table wont fit in.Moreover,
> you are using the
> >unicode datatype, NVARCHAR, which itself take twice as
> much storage space as
> >non-unicode data types.
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"wringland" <bill@.tiati.com> wrote in message
> >news:001001c34fa3$3447a540$a401280a@.phx.gbl...
> >> Is the maximum row size per table really limited to
> >> 8K bytes? Or is this the internal organization of SQL
> >> data structures. I have imported a legacy table that
> >> wound up having over 100 columns that came in as
> >> NVARCHAR(255) or roughly 25K + in size. Any comments?
> >>
> >> Thanks
> >
> >
> >.
> >|||> As mentioned in other posts, SQL server will let you create the table with
a
> warning about data truncation.While inserting data, the moment a
particular
> rowsize exceeds 8060, the rest would be truncated and you wouldnt even
> know.
Actually in this case, wouldn't you get the "string or binary data would be
truncated" error message?|||I have five fields each varchar(5000) and about
8 other columns between 50 and 100 bytes each.
so in answer to your question, none is > 8060.
>--Original Message--
>What is your current table structure? Are there one or
two columns forcing
>it beyond 8,060, or do you have 100 columns that are 255
characters?
>
>
>"Alice" <mygth@.hotmail.com> wrote in message
>news:056401c34fce$de321fb0$a101280a@.phx.gbl...
>> I am running into similar situation,
>> I am creating new table with rowsize > 8060,
>> my rowsize is 25000, is there any way by which
>> I can increase the rowsize to this limit?
>> I read on microsoft site that this limiation existed
>> on 6.5 and u.s. pack 2 fixed it. Any ideas?
>> BTW, I am on Sql 7.
>
>.
>|||Alice,
>> Then why do they say US patch 4 fixes it for sql 6.5?
What article are you talking about?
Thanks!
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alice" <mygth@.hotmail.com> wrote in message
news:04a101c34fda$1dcc50e0$a401280a@.phx.gbl...
> well, I was thinking on those lines, but I am surprised
> that we cannot have > 8060 characters in a row.
> Then why do they say US patch 4 fixes it for sql 6.5?
> unless I am mistaken.
> >--Original Message--
> >> I have five fields each varchar(5000) and about
> >> 8 other columns between 50 and 100 bytes each.
> >>
> >> so in answer to your question, none is > 8060.
> >
> >If you expect to ever fill more than 8,060 characters
> across the row, then
> >you're going to have to make a decision. You could use
> text columns, or you
> >could put each varchar(5000) in its own table with a
> foreign key to this
> >tables pk. Are you expecting to use all 5000 characters
> in any row/column
> >combination? If not, you might consider decreasing the
> size to reduce the
> >number of columns you'll have to store off-row.
> >
> >
> >.
> >

Maximum Row Size in SQL Server 2000

Using this code:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (8000) NULL ,
[COMMENT2] [varchar] (8000) NULL
) ON [PRIMARY]
GO
I get the error: Warning: The table 'test' has been created but its maximum
row size (16029) exceeds the maximum number of bytes per row (8060).
However, I can create a table with smaller field lengths:
CREATE TABLE [dbo].[test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[COMMENT1] [varchar] (10) NULL ,
[COMMENT2] [varchar] (10) NULL
) ON [PRIMARY]
GO
I can then alter the field lengths in Enterprise Manager back to 8000 and
not get the error. I also notice I can import data from a text file and a
table will be created with numerous varchar fields that are each 8000 in
size. Why cannot I create the table with multiple 8000 length fields but SQL
Server allows me to modify an existing table or import into a table that has
multiple 8000 length fields?
Thank you.Hi,
like you said, you get a "Warning" no error. SQL Server just wants to
keep you informed that the data *might* be truncated, if you insert
more than 8000 characters, but the table *will* be created.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||The warning you get is only a warning. The table is still created. Read the warning text carefully.
The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
UPDATE), then that operation will fail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
> Using this code:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (8000) NULL ,
> [COMMENT2] [varchar] (8000) NULL
> ) ON [PRIMARY]
> GO
> I get the error: Warning: The table 'test' has been created but its maximum
> row size (16029) exceeds the maximum number of bytes per row (8060).
> However, I can create a table with smaller field lengths:
> CREATE TABLE [dbo].[test] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [COMMENT1] [varchar] (10) NULL ,
> [COMMENT2] [varchar] (10) NULL
> ) ON [PRIMARY]
> GO
> I can then alter the field lengths in Enterprise Manager back to 8000 and
> not get the error. I also notice I can import data from a text file and a
> table will be created with numerous varchar fields that are each 8000 in
> size. Why cannot I create the table with multiple 8000 length fields but SQL
> Server allows me to modify an existing table or import into a table that has
> multiple 8000 length fields?
> Thank you.
>
>
>|||Thank you for the information. So I can have a table with numerous varchar
8000 fields and I'm ok as long as a single inserted row does not contain more
than 8060 characters. Is this correct?
"Tibor Karaszi" wrote:
> The warning you get is only a warning. The table is still created. Read the warning text carefully.
> The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
> UPDATE), then that operation will fail.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
> > Using this code:
> >
> > CREATE TABLE [dbo].[test] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [COMMENT1] [varchar] (8000) NULL ,
> > [COMMENT2] [varchar] (8000) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > I get the error: Warning: The table 'test' has been created but its maximum
> > row size (16029) exceeds the maximum number of bytes per row (8060).
> >
> > However, I can create a table with smaller field lengths:
> >
> > CREATE TABLE [dbo].[test] (
> > [ID] [int] IDENTITY (1, 1) NOT NULL ,
> > [COMMENT1] [varchar] (10) NULL ,
> > [COMMENT2] [varchar] (10) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > I can then alter the field lengths in Enterprise Manager back to 8000 and
> > not get the error. I also notice I can import data from a text file and a
> > table will be created with numerous varchar fields that are each 8000 in
> > size. Why cannot I create the table with multiple 8000 length fields but SQL
> > Server allows me to modify an existing table or import into a table that has
> > multiple 8000 length fields?
> >
> > Thank you.
> >
> >
> >
> >
> >
> >
>
>|||Try it :
INSERT INTO TEST VALUES (REPLICATE('*', 80000), REPLICATE('*', 44))
A +
Brian P a Ã©crit :
> Thank you for the information. So I can have a table with numerous varchar
> 8000 fields and I'm ok as long as a single inserted row does not contain more
> than 8060 characters. Is this correct?
> "Tibor Karaszi" wrote:
>> The warning you get is only a warning. The table is still created. Read the warning text carefully.
>> The table is created, but if you , for a row, try to have > 8060 bytes (when you do INSERT or
>> UPDATE), then that operation will fail.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
>> news:7F71DB7B-6F5E-4ABC-90AA-A988691D095A@.microsoft.com...
>> Using this code:
>> CREATE TABLE [dbo].[test] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [COMMENT1] [varchar] (8000) NULL ,
>> [COMMENT2] [varchar] (8000) NULL
>> ) ON [PRIMARY]
>> GO
>> I get the error: Warning: The table 'test' has been created but its maximum
>> row size (16029) exceeds the maximum number of bytes per row (8060).
>> However, I can create a table with smaller field lengths:
>> CREATE TABLE [dbo].[test] (
>> [ID] [int] IDENTITY (1, 1) NOT NULL ,
>> [COMMENT1] [varchar] (10) NULL ,
>> [COMMENT2] [varchar] (10) NULL
>> ) ON [PRIMARY]
>> GO
>> I can then alter the field lengths in Enterprise Manager back to 8000 and
>> not get the error. I also notice I can import data from a text file and a
>> table will be created with numerous varchar fields that are each 8000 in
>> size. Why cannot I create the table with multiple 8000 length fields but SQL
>> Server allows me to modify an existing table or import into a table that has
>> multiple 8000 length fields?
>> Thank you.
>>
>>
>>
>>
FrÃ©dÃ©ric BROUARD, MVP SQL Server, expert bases de donnÃ©es et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modÃ©lisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Correct. And, btw, this restriction has been removed in SQL Server 2005 ("page overflow").
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian P" <BrianP@.discussions.microsoft.com> wrote in message
news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
> Thank you for the information. So I can have a table with numerous varchar
> 8000 fields and I'm ok as long as a single inserted row does not contain more
> than 8060 characters. Is this correct?
>|||Brian
But be aware that SQL Server 2005 row_overflow data only applies to variable
length fields.
You cannot have multiple char(8000) columns, for example.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> Correct. And, btw, this restriction has been removed in SQL Server 2005
> ("page overflow").
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
>> Thank you for the information. So I can have a table with numerous
>> varchar
>> 8000 fields and I'm ok as long as a single inserted row does not contain
>> more
>> than 8060 characters. Is this correct?|||Thanks everyone for the good information. I appreciate the feedback.
Brian
"Kalen Delaney" wrote:
> Brian
> But be aware that SQL Server 2005 row_overflow data only applies to variable
> length fields.
> You cannot have multiple char(8000) columns, for example.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ecx6jdYXGHA.4132@.TK2MSFTNGP04.phx.gbl...
> > Correct. And, btw, this restriction has been removed in SQL Server 2005
> > ("page overflow").
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Brian P" <BrianP@.discussions.microsoft.com> wrote in message
> > news:4AA95B81-5246-470E-A270-11810106F346@.microsoft.com...
> >> Thank you for the information. So I can have a table with numerous
> >> varchar
> >> 8000 fields and I'm ok as long as a single inserted row does not contain
> >> more
> >> than 8060 characters. Is this correct?
> >>
>
>

maximum row size exceeds the maximum number of bytes per row (8060

Hi,
I have created a table (say Table1) with few columns, with one of the
columns (say column1) having the data type as Varchar(8000).
Now I run the below query -
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
if not exists (select * from dbo.syscolumns
where id = object_id(N'[dbo].[Table1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1
and name = 'Column1')
ALTER TABLE dbo.Table1 ADD
Column1 varchar(500) NULL
GO
COMMIT
Now that the column Column1 already exists, the add column statement won't
be executed. But still I get the warning -
"Warning: The table 'Table1' has been created but its maximum row size
(8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes."
My question is, now that the column already exists, the statement itself
won't be executed. Then why do I still get this warning?That has to do with the sequence of query processing and when the warning is generated. Obviously
the warning (the fact that this table will have a row size for which you can exceed the limit in
your data) is generated in a stage which is earlier than when the statements are actually executed.
In other words, the If statement haven't been executed at this stage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"swap@.@.@." <swap@.discussions.microsoft.com> wrote in message
news:73AC8FFC-4041-4B22-AB25-4CCDB94598FC@.microsoft.com...
> Hi,
> I have created a table (say Table1) with few columns, with one of the
> columns (say column1) having the data type as Varchar(8000).
> Now I run the below query -
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> if not exists (select * from dbo.syscolumns
> where id = object_id(N'[dbo].[Table1]')
> and OBJECTPROPERTY(id, N'IsUserTable') = 1
> and name = 'Column1')
> ALTER TABLE dbo.Table1 ADD
> Column1 varchar(500) NULL
> GO
> COMMIT
> Now that the column Column1 already exists, the add column statement won't
> be executed. But still I get the warning -
> "Warning: The table 'Table1' has been created but its maximum row size
> (8579) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
> of a row in this table will fail if the resulting row length exceeds 8060
> bytes."
> My question is, now that the column already exists, the statement itself
> won't be executed. Then why do I still get this warning?
>

maximum row size exceeds

hi,
I receive following Warning can any one tell me why this is occurs and how
can I eliminate it.
Warning: The table 'Part_Tags' has been created but its maximum row size
(14647) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
of a row in this table will fail if the resulting row length exceeds 8060
bytes.
Farhan Iqbal
On Mon, 24 May 2004 02:52:29 -0700, Farhan Iqbal wrote:

>hi,
>I receive following Warning can any one tell me why this is occurs and how
>can I eliminate it.
>
>Warning: The table 'Part_Tags' has been created but its maximum row size
>(14647) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE
>of a row in this table will fail if the resulting row length exceeds 8060
>bytes.
>
>Farhan Iqbal
>
Hi Farhan,
Your table definition includes at least one, probably more varchar,
nvarchar of varbinary columns. The theoretic maximum number of bytes in a
row, if all these columns are filled with the maximum length for that
column, is 14,647 bytes. The minimum length (is all varying length columns
are length zero) is below 8,060 bytes.
This is a warning message, there is no error -yet! But as soon as you try
to insert values into the table that make the total length of one row
exceed 8,060 bytes, you will get an error. SQL Server can't handle rows
that exceed 8,060 bytes of data.
Simple repro script to try it for yourself:
-- this will yield a warning similar to the one above,
-- but the table will be created.
create table testit(pk int not null primary key,
vc1 varchar(6000) not null,
vc2 varchar(6000) not null)
go
-- total length < 8,060 - no error
insert testit (pk, vc1, vc2)
select 1, replicate ('x', 3000), replicate ('y', 3000)
go
-- total length > 8,060 - error and insert rejeected
insert testit (pk, vc1, vc2)
select 2, replicate ('x', 6000), replicate ('y', 6000)
go
-- check that only first row was inserted
select * from testit
go
-- growing data beyond 8,060 bytes fails as well
update testit
set vc1 = replicate ('z', 6000)
where pk = 1
go
-- check that row was not updated
select * from testit
go
-- cleanup
drop table testit
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Simply change design of your table to mark column as text

Maximum row count of 50389 exceeded ?

Hi,
Im using VB6 to return values in an SQL 2000 table and get the following
error:
Error in ScrollBox.refresh
Maximum row count of 50389 exceeded
17713 rows have not been displayed
Does anyone know if this problem is related to SQL 2000 table limitations or
is it VB based ?
Thanks for any information.
Scott.
Maximum row count of 50389 exceeded ?
scott
VB's issue
"scott" <nospamscott@.yahoo.com> wrote in message
news:uEucDictEHA.2124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Im using VB6 to return values in an SQL 2000 table and get the following
> error:
> Error in ScrollBox.refresh
> Maximum row count of 50389 exceeded
> 17713 rows have not been displayed
> Does anyone know if this problem is related to SQL 2000 table limitations
or
> is it VB based ?
> Thanks for any information.
> Scott.
> Maximum row count of 50389 exceeded ?
>
>
|||cheers

Maximum row count of 50389 exceeded ?

Hi,
Im using VB6 to return values in an SQL 2000 table and get the following
error:
Error in ScrollBox.refresh
Maximum row count of 50389 exceeded
17713 rows have not been displayed
Does anyone know if this problem is related to SQL 2000 table limitations or
is it VB based ?
Thanks for any information.
Scott.
Maximum row count of 50389 exceeded ?scott
VB's issue
"scott" <nospamscott@.yahoo.com> wrote in message
news:uEucDictEHA.2124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Im using VB6 to return values in an SQL 2000 table and get the following
> error:
> Error in ScrollBox.refresh
> Maximum row count of 50389 exceeded
> 17713 rows have not been displayed
> Does anyone know if this problem is related to SQL 2000 table limitations
or
> is it VB based ?
> Thanks for any information.
> Scott.
> Maximum row count of 50389 exceeded ?
>
>|||cheers

Maximum row count of 50389 exceeded ?

Hi,
Im using VB6 to return values in an SQL 2000 table and get the following
error:
Error in ScrollBox.refresh
Maximum row count of 50389 exceeded
17713 rows have not been displayed
Does anyone know if this problem is related to SQL 2000 table limitations or
is it VB based ?
Thanks for any information.
Scott.
Maximum row count of 50389 exceeded ?scott
VB's issue
"scott" <nospamscott@.yahoo.com> wrote in message
news:uEucDictEHA.2124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Im using VB6 to return values in an SQL 2000 table and get the following
> error:
> Error in ScrollBox.refresh
> Maximum row count of 50389 exceeded
> 17713 rows have not been displayed
> Does anyone know if this problem is related to SQL 2000 table limitations
or
> is it VB based ?
> Thanks for any information.
> Scott.
> Maximum row count of 50389 exceeded ?
>
>|||cheers

Maximum Record (Row) lenght?

I could not find how to visualize the record lenght of a
table in SQL Server Ent. Manager. Is there a way?
Is it true that the record lenght for a single table
cannot exceed 8086 bytes in SQL Sever?
THANK YOU!
DDursun (anonymous@.discussions.microsoft.com) writes:
> I could not find how to visualize the record lenght of a
> table in SQL Server Ent. Manager. Is there a way?
I have never seen any information on this, but I have not looked for it
either, and I use EM sparingly.
> Is it true that the record lenght for a single table
> cannot exceed 8086 bytes in SQL Sever?
Yes. The exception is if you use text, ntext or image columns. These
datatypes can accomdate up to 2GB for a single value. But they are
not stored within the row, but in a spoce of their own.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi Dursun,
For in-depth information follow the links
http://www.sql-server-
performance.com/ac_65_data_structure.asp ( for SQL Server
6.5)
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4885 (
for SQL Server 7.0).
Best Regards
Chip
>--Original Message--
>Dursun (anonymous@.discussions.microsoft.com) writes:
>> I could not find how to visualize the record lenght of
a
>> table in SQL Server Ent. Manager. Is there a way?
>I have never seen any information on this, but I have not
looked for it
>either, and I use EM sparingly.
>> Is it true that the record lenght for a single table
>> cannot exceed 8086 bytes in SQL Sever?
>Yes. The exception is if you use text, ntext or image
columns. These
>datatypes can accomdate up to 2GB for a single value. But
they are
>not stored within the row, but in a spoce of their own.
>
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>.
>

Wednesday, March 21, 2012

Maybe I'm asking for too much.

May I create not unique clustered index?

Maxium Table Size, Change size?

Maxium Table Size, Change size?

Monday, March 19, 2012

Maximum value of mulitple columns

Maximum Value

Maximum Table Size

Maximum size of table?

Maximum Rowsize screw up by MSSQL 2000

maximum row size.. 8060 bytes ?

maximum row size problem

Maximum Row Size Limitation

Maximum Row Size in SQL Server 2000

Maximum Row Size in SQL Server 2000

maximum row size exceeds the maximum number of bytes per row (8060

maximum row size exceeds

Maximum row count of 50389 exceeded ?

Maximum row count of 50389 exceeded ?

Maximum row count of 50389 exceeded ?

Maximum Record (Row) lenght?

MAX and DATEDIFF

Blog Archive

About Me