Adding a field whose value DEPENDS on other fields in table

Hi,
Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
another field for Status (a generic field) , which has value :
1.Open if the value of the field OPEN_STATUS=T ,
2.InProcess if the value of the field INPROCESS_STATUS=T
3.Completed if COMPLETED_STATUS=T.
Is there a way to do this ?...maybe by writing these if statements? How can
this be done? I know this is not a good desgin but already many applications
are using these 3 fields which I dont want to change... So is there any way
of creating this new field in the same table based on the criteria specified
above?
Thanks for any help.
--
pmudTry creating a calculated column.
alter table t
add status as case when OPEN_STATUS='T' then 'Open' when
INPROCESS_STATUS='T' then 'InProcess' when COMPLETED_STATUS='T' then
'Completed' else 'UNKNOWN' end
go
Are those columns exclusive?, because the expressions inside CASE will be
evaluated in the same order as they appear.
AMB
"pmud" wrote:

> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to ad
d
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How ca
n
> this be done? I know this is not a good desgin but already many applicatio
ns
> are using these 3 fields which I dont want to change... So is there any wa
y
> of creating this new field in the same table based on the criteria specifi
ed
> above?
> Thanks for any help.
> --
> pmud|||You might also consider changing the name of the table. then create a view
with the original table name and include the special column in the view...
This will insulate you and theprogrammers from future changes.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9176C167-F9FB-48D4-AA84-6DA690D02B76@.microsoft.com...
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to
> add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How
> can
> this be done? I know this is not a good desgin but already many
> applications
> are using these 3 fields which I dont want to change... So is there any
> way
> of creating this new field in the same table based on the criteria
> specified
> above?
> Thanks for any help.
> --
> pmud|||the solution using a caculated field is good if the field does not have to b
e
indexed or searched against (slow).
If the field has to be indexed or searched and the table has lots of rows,
consider using a trigger to calculate and store the value.
"pmud" wrote:

> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to ad
d
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How ca
n
> this be done? I know this is not a good desgin but already many applicatio
ns
> are using these 3 fields which I dont want to change... So is there any wa
y
> of creating this new field in the same table based on the criteria specifi
ed
> above?
> Thanks for any help.
> --
> pmud

Adding a field whose value DEPENDS on other fields in table

Hi,
Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
another field for Status (a generic field) , which has value :
1.Open if the value of the field OPEN_STATUS=T ,
2.InProcess if the value of the field INPROCESS_STATUS=T
3.Completed if COMPLETED_STATUS=T.
Is there a way to do this ?...maybe by writing these if statements? How can
this be done? I know this is not a good desgin but already many applications
are using these 3 fields which I dont want to change... So is there any way
of creating this new field in the same table based on the criteria specified
above?
Thanks for any help.
pmud
Try creating a calculated column.
alter table t
add status as case when OPEN_STATUS='T' then 'Open' when
INPROCESS_STATUS='T' then 'InProcess' when COMPLETED_STATUS='T' then
'Completed' else 'UNKNOWN' end
go
Are those columns exclusive?, because the expressions inside CASE will be
evaluated in the same order as they appear.
AMB
"pmud" wrote:

> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud
|||You might also consider changing the name of the table. then create a view
with the original table name and include the special column in the view...
This will insulate you and theprogrammers from future changes.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9176C167-F9FB-48D4-AA84-6DA690D02B76@.microsoft.com...
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to
> add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How
> can
> this be done? I know this is not a good desgin but already many
> applications
> are using these 3 fields which I dont want to change... So is there any
> way
> of creating this new field in the same table based on the criteria
> specified
> above?
> Thanks for any help.
> --
> pmud
|||the solution using a caculated field is good if the field does not have to be
indexed or searched against (slow).
If the field has to be indexed or searched and the table has lots of rows,
consider using a trigger to calculate and store the value.
"pmud" wrote:

> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud
sql

Adding a field whose value DEPENDS on other fields in table

Hi,
Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
another field for Status (a generic field) , which has value :
1.Open if the value of the field OPEN_STATUS=T ,
2.InProcess if the value of the field INPROCESS_STATUS=T
3.Completed if COMPLETED_STATUS=T.
Is there a way to do this ?...maybe by writing these if statements? How can
this be done? I know this is not a good desgin but already many applications
are using these 3 fields which I dont want to change... So is there any way
of creating this new field in the same table based on the criteria specified
above?
Thanks for any help.
--
pmudTry creating a calculated column.
alter table t
add status as case when OPEN_STATUS='T' then 'Open' when
INPROCESS_STATUS='T' then 'InProcess' when COMPLETED_STATUS='T' then
'Completed' else 'UNKNOWN' end
go
Are those columns exclusive?, because the expressions inside CASE will be
evaluated in the same order as they appear.
AMB
"pmud" wrote:
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud|||You might also consider changing the name of the table. then create a view
with the original table name and include the special column in the view...
This will insulate you and theprogrammers from future changes.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9176C167-F9FB-48D4-AA84-6DA690D02B76@.microsoft.com...
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to
> add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How
> can
> this be done? I know this is not a good desgin but already many
> applications
> are using these 3 fields which I dont want to change... So is there any
> way
> of creating this new field in the same table based on the criteria
> specified
> above?
> Thanks for any help.
> --
> pmud|||the solution using a caculated field is good if the field does not have to be
indexed or searched against (slow).
If the field has to be indexed or searched and the table has lots of rows,
consider using a trigger to calculate and store the value.
"pmud" wrote:
> Hi,
> Three fields in my database table are , OPEN_STATUS, INPROCESS_STATUS,
> COMPLETED_STATUS. The values of these are T (true) or F . Now I need to add
> another field for Status (a generic field) , which has value :
> 1.Open if the value of the field OPEN_STATUS=T ,
> 2.InProcess if the value of the field INPROCESS_STATUS=T
> 3.Completed if COMPLETED_STATUS=T.
> Is there a way to do this ?...maybe by writing these if statements? How can
> this be done? I know this is not a good desgin but already many applications
> are using these 3 fields which I dont want to change... So is there any way
> of creating this new field in the same table based on the criteria specified
> above?
> Thanks for any help.
> --
> pmud

add values with select

Hi

Thnks for the time

I need to insert some values and select a value from another table to insert

insertinto products values(10,'proname','desc',(select modelfrom Products))

How can this be done.

Try:

insert into products(c1, ..., cn)

select 10, 'proname', 'desc', model

from dbo.products

go

AMB

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Hi

Thnks for the time

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

you can do it the other way around

Code Snippet

insertinto products

select 10,'proname','desc', Model

from Products

|||

Hi

I need to insert with values and select statement , can this be done

some thing like this

insertinto products values(10,'proname','desc',(select,Modelfrom Products))

|||

Put the result of the "select" statement into a variable and use the variable.

declare @.model varchar(25)

set @.modele = (select model from products where producti = @.productid)

insert into products values(10, 'proname', 'desc', @.model)

go

AMB

Add values to Database

I am using VB and sql server.

What I am wanting to do is add a record to my sql database (the data is coming from textboxes.)

Canning

Check out the Getting Started section at the top of these forums. You will find all the info (and even more) you need.

Add values to a table based on values in another table

When a record is inserted or updated records in Table1 I want a record to be inserted into table3 for each record ID that is in table2 if the table2.id does not already exist in table3. What is the best way to do this? Could this be done with a trigger? If it could how would I write such a trigger. I have never written one before and this sounds like a hand full for my first effort. Your help will be greatly appreciated.

Thanks

You could do it in a trigger. They're really not hard. It's just SQL you want to run whenever a certain action happens on your table. Pretty straightforward. Another option is to use stored procedures to do all of your inserts and updates. Then, just code what could go in the trigger directly into your stored procedure.

Pete

Add values of distinct rows

Hello All,

I have a scenario wherein I wanted to add the values of distinct transaction number, how do I do that. i.e.

Financial organisation (GROUP (Company))

12/31/2003 606.60 approved 123 (Transaction Number) (1st Row)

5643577(Wok Report) (2nd Row)

01/05/2004 1000.00 approved 124 (Transaction Number) (to Satisfy $1000 three work reports were raised)

5643500(Wok Report)

5643501(Wok Report)

5643502(Wok Report)

02/05/2004 2000.00 approved 125 (Transaction Number) (to Satisfy $2000 three work reports were raised)

5643300(Wok Report)

5643301(Wok Report)

5643302(Wok Report)

Total 3606.60 (Total per Company group)

I want to add the visible row values only. Because the 1000.00 and 2000.00 was repeating for 01/05/2004 and 02/05/04 I am hinding the repeated values.

Please help me completing the above task.

Thanks,

Deepak Vodethela

Deekpak,

Unfortunatley, from my experiences, this is not possible as of SSRS 2005. I tried to do the same thing recently and I ended up changing my SQL to give me what I wanted.

|||

Thanks for the reply Todd. Can't we write a custom function to do the needful i.e. which has to check for the distict Transaction number and pick up the value and do the sum by group. I am not an expert at SSRS but just curious.

To change my query it will be a very big task. Right now I am joining 11 tables to get the required fields and if I want to add the sum on a group then it'll be a laborious task.

Any help would be appriciated.

Add two result values to a new result?

Hi!
This is probably simple (or impossible) but I can't figure out how to add to
values into 1 in a result.
Something like this:
select
sum( lala ) as sum_1,
sum( lele ) as sum_2,
add( sum_1 + sum_2) as sum_tot
from myTable
I could ofcourse use lala and lele in a new sum statement, but both can be
quite complicated statements and I thought there might be an easier way
around?
Regards,
PeterSELECT sum_1,
sum_2,
sum_1+sum_2 As sum_tot
FROM
(SELECT sum(lala) as sum_1,
sum (lele) as sum_2
FROM mytable ) X
"Peter Hartln" <peter@.data.se> wrote in message
news:ugHGLt$JGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi!
> This is probably simple (or impossible) but I can't figure out how to add
> to values into 1 in a result.
> Something like this:
> select
> sum( lala ) as sum_1,
> sum( lele ) as sum_2,
> add( sum_1 + sum_2) as sum_tot
> from myTable
> I could ofcourse use lala and lele in a new sum statement, but both can be
> quite complicated statements and I thought there might be an easier way
> around?
> Regards,
> Peter
>
>|||Hi
CREATE TABLE Test
(
col1 INT,
col2 INT
)
INSERT INTO Test VALUES (1,5)
INSERT INTO Test VALUES (20,30)
CREATE FUNCTION dbo.calc_sum
(
@.arg1 AS int,
@.arg2 AS int
)
RETURNS int
AS
BEGIN
RETURN (SELECT @.arg1+@.arg2)
END
GO
SELECT SUM(col1),SUM(col2),dbo.calc_sum(SUM(col1),SUM(col2))
FROM Test
"Peter Hartln" <peter@.data.se> wrote in message
news:ugHGLt$JGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi!
> This is probably simple (or impossible) but I can't figure out how to add
> to values into 1 in a result.
> Something like this:
> select
> sum( lala ) as sum_1,
> sum( lele ) as sum_2,
> add( sum_1 + sum_2) as sum_tot
> from myTable
> I could ofcourse use lala and lele in a new sum statement, but both can be
> quite complicated statements and I thought there might be an easier way
> around?
> Regards,
> Peter
>
>|||"Peter Hartln" <peter@.data.se> wrote in message
news:ugHGLt$JGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi!
> This is probably simple (or impossible) but I can't figure out how to add
> to values into 1 in a result.
> Something like this:
> select
> sum( lala ) as sum_1,
> sum( lele ) as sum_2,
> add( sum_1 + sum_2) as sum_tot
> from myTable
> I could ofcourse use lala and lele in a new sum statement, but both can be
> quite complicated statements and I thought there might be an easier way
> around?
> Regards,
> Pete
The easiest way would be to do the addition in the client application.
Otherwise, no, you have to repeat the SUMs.|||Try this
SELECT sum_lala, sum_lele, sum_lala + sum_lele AS sum_lala_lele
FROM (SELECT SUM(lala) AS sum_lala, SUM(lele) AS sum_lele
FROM andys_test_table) summary

add total row to file via script component

Hi, I have a flat file source going into a script component which manipulates some values and puts those values in a flat file destination. What I now need to do is total a column within the script component and add that value as a row to the destination. At the moment data from source looks something like

ABC, 3

DEF, 5

FGH, 6

I have a variable in script component holding the running total I now want to add that to destination as a row e.g.

ABC, 3

DEF, 5

FGH, 6

Total, 14

This is probably very easy but I don't now how to add the Total row via the component. At the moment all rows are manipulated in the ProcessInputRow sub of component and output column values are assigned here. How do I add the Total row as I need to do this once last input row is complete.

Thanks

You'll need to use an asynchronous output to accomplish this. You could add an async output in addition to the synchronous output you get by default, and just write the running total to it.

See this topic in Books Online:

"Creating an Asynchronous Transformation with the Script Component"

|||You could also just use a multicast and an Aggregate transform to accomplish the same thing.|||Great thanks, Asynchronous Transformation worked

add total row to file via script component

Hi, I have a flat file source going into a script component which manipulates some values and puts those values in a flat file destination. What I now need to do is total a column within the script component and add that value as a row to the destination. At the moment data from source looks something like

ABC, 3

DEF, 5

FGH, 6

I have a variable in script component holding the running total I now want to add that to destination as a row e.g.

ABC, 3

DEF, 5

FGH, 6

Total, 14

This is probably very easy but I don't now how to add the Total row via the component. At the moment all rows are manipulated in the ProcessInputRow sub of component and output column values are assigned here. How do I add the Total row as I need to do this once last input row is complete.

Thanks

You'll need to use an asynchronous output to accomplish this. You could add an async output in addition to the synchronous output you get by default, and just write the running total to it.

See this topic in Books Online:

"Creating an Asynchronous Transformation with the Script Component"

|||You could also just use a multicast and an Aggregate transform to accomplish the same thing.|||Great thanks, Asynchronous Transformation worked

Add to SSRS data extension with config file

Hello,

I try to add an data extension who take the information to connect to the database in a config file. But It's could not retreive the values corresponding to the key.

I'm using the class ConfigurationManager from the namespace System.Configuration.

ConfigurationManager.AppSettings["QueryModel"]

Without the config file, the extension is working well.... I got the impression that reporting service and Visual Studio BI don't find the config file.

If someone can help me or have some advices. I will be pleased

Hi,

Finally, I found how to manage data Extension with config file.

The solution is :

It's not possible to do exactily like a simple application, add a configuration file app.config don't work with extension.

I found a part of the answer on this forum. it's was talking about IExtension.SetConfiguration.

In fact to solve this problem, we need to add a tags in the RSReportDesigner.config file( PrivateAssemblies Directory of Visual Studio 8) or in the RSReportServer.config ( Report Server Directory), just after the definition of the extension

Code Snippet

<Extension Name="My Extension" Type="Microsoft.Samples.ReportingServices.MyExtension.ExtensionClass,Microsoft.Samples.ReportingServices.MyExtension">
<Configuration>
<MyExtensionConfigurationData>
<MyExtensionData1>Value</MyExtensionData1>
<MyExtensionData2>Value</MyExtensionData2>
</MyExtensionConfigurationData>
</Configuration>

</Extension>

You need to implemente the SetConfiuration Method inside your class who implement the IExtension Interface.

Code Snippet

public void SetConfiguration(string configuration)

{

configXML = configuration;

}

the content of the string "configXML" while be the block corresponding to the <MyExtensionConfigurationData> tag

Next, you have to add a fonction to parse the XML block and put it inside a List or a Map...

Bye

Add Preceeding Zero

I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?Your datatype for NumVal should be a character type, and you have to use +
instead of &
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"box2003" <box2003@.yahoo.com> wrote in message
news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
I have a field in a table, datatype is varchar. The field contains values
such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
want to change the field to precede all field values with 0 to yield,
01000000, 01000003, 05000001. I have attempted this using the follow but,
does not work returning error
UPDATE NumVal_1
SET NumVal = '0' & NumVal
WHERE Left(NumVal,1) > 0
Invalid operator for data type. Operator equals boolean AND, type equals
varchar
Am I even close to being on the right track?|||I had just found the & and + problem, coming in from Access I use the &.
Thank you.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:OGI49IgYFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Your datatype for NumVal should be a character type, and you have to use +
> instead of &
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "box2003" <box2003@.yahoo.com> wrote in message
> news:%23ZIkgDgYFHA.2380@.tk2msftngp13.phx.gbl...
> I have a field in a table, datatype is varchar. The field contains values
> such as, 1000000, 1000003, 5000001 (Left most position is always > 0). I
> want to change the field to precede all field values with 0 to yield,
> 01000000, 01000003, 05000001. I have attempted this using the follow but,
> does not work returning error
> UPDATE NumVal_1
> SET NumVal = '0' & NumVal
> WHERE Left(NumVal,1) > 0
> Invalid operator for data type. Operator equals boolean AND, type equals
> varchar
> Am I even close to being on the right track?
>
>

Add column with fixed number of values (text) to the select statement

Hello,

I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.

I found on the net something to start with

Adding Columns in SQL SELECT statements by Andy Kramek http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx

SELECT *, CAST( 0 AS INT) AS newint FROM sample

but it just add a column with value 0 to the set of results.

|||

What is the version you are using... (2000/2005) Give some sample data & required output..

|||

I'm using ver. 2005. The select is very simple concerns the tables with historical rates for curves.

So there are four columns:

1) Date

2) Name of the curve

3) DF

4) Implied rate

there are 17 rows of data

and I need to add column with fixed tenors

O/N

T/N

S/N

1W

2W

1M

2M

3M

4M

5M

6M

7M

8M

9M

10M

11M

1Y

|||

Ok.. do you want the result as bellow,

1) Date

2) Name of the curve

3) DF

4) Implied rate

5)O/N

6)T/N

7)S/N

8)1W

9)2W

10)1M

..

..

17)IY

if yes what data you want to display on this 17 columns

|||

Your questions not clear properly. But I think this will help you,

USE PUBS
Go

SELECT COUNT(*) row_num, A.au_lname
FROM authors A, authors B
WHERE A.au_lname > B.au_lname
GROUP BY A.au_lname
ORDER BY COUNT(*)

|||

I want results to look like this:

Tenor / Date / Name of the curve/ DF / Implied rate

O/N 1/08/07 EUR_CURVE 0.9999987 4.020000

T/N 1/08/07 EUR_CURVE . .

S/N 1/08/07 EUR_CURVE . .

1W 1/08/07 EUR_CURVE . .

2W 1/08/07 EUR_CURVE . .

1M 1/08/07 EUR_CURVE . .

.

.

.

1Y 1/08/07 EUR_CURVE . .

tks

Add Column Values To the Bottom Of the Row By Condition

Hi
I am getting some results from stored procedure.
Name Value1 Value2 Value3 Total
North 1 2 3 6
North 4 5 6 15
South 5 6 7 18
Footer (North) 5 7 9 21
Footer(South) 5 6 7 18
I displaying footer for each group and i want to display Total value of
the column for that respective group Name.Is therea way to add all
column value depending up on the condition.
For North add only 2 rows.
Can any one help on this issue.
Thankstry sum in the group
=Sum(field!abc.value, "group1") you can have scope in the sum.
Amarnath
"vamsi" wrote:
> Hi
> I am getting some results from stored procedure.
> Name Value1 Value2 Value3 Total
> North 1 2 3 6
> North 4 5 6 15
> South 5 6 7 18
> Footer (North) 5 7 9 21
> Footer(South) 5 6 7 18
>
> I displaying footer for each group and i want to display Total value of
> the column for that respective group Name.Is therea way to add all
> column value depending up on the condition.
> For North add only 2 rows.
> Can any one help on this issue.
> Thanks
>

Add auto number field to view

I have a simple view something like "SELECT * from <table_name> order
by field1,field2,field3
I have a program that needs to read these values in by the order I have
ordered them in the database however it needs an incremental numeric
value to do so. Is there any way to add to the view, a field that after
the order by is done, that places a numeric incremental value in
another virtual or real column?
Thanks.
JRhttp://www.aspfaq.com/2427
"JR" <jriker1@.yahoo.com> wrote in message
news:1149872089.709215.141860@.i39g2000cwa.googlegroups.com...
>I have a simple view something like "SELECT * from <table_name> order
> by field1,field2,field3
> I have a program that needs to read these values in by the order I have
> ordered them in the database however it needs an incremental numeric
> value to do so. Is there any way to add to the view, a field that after
> the order by is done, that places a numeric incremental value in
> another virtual or real column?
> Thanks.
> JR
>|||JR wrote:
> I have a simple view something like "SELECT * from <table_name> order
> by field1,field2,field3
> I have a program that needs to read these values in by the order I have
> ordered them in the database however it needs an incremental numeric
> value to do so. Is there any way to add to the view, a field that after
> the order by is done, that places a numeric incremental value in
> another virtual or real column?
> Thanks.
> JR
on 2005, use row_number()|||Using SQL Server 2000.
Alexander Kuznetsov wrote:
> JR wrote:
> on 2005, use row_number()|||I was looking at the rank option however with that it seems to want you
to define each and every column in your view in the group by clause.
Aaron Bertrand [SQL Server MVP] wrote:
> http://www.aspfaq.com/2427
>
>
> "JR" <jriker1@.yahoo.com> wrote in message
> news:1149872089.709215.141860@.i39g2000cwa.googlegroups.com...|||>I was looking at the rank option however with that it seems to want you
> to define each and every column in your view in the group by clause.
Which you should be doing anyway; don't be lazy, it doesn't pay off in the
long run!
http://www.aspfaq.com/2096|||OK, I'll give it a try however I have over 50 columns so kind of a
pain.
Aaron Bertrand [SQL Server MVP] wrote:
> Which you should be doing anyway; don't be lazy, it doesn't pay off in the
> long run!
> http://www.aspfaq.com/2096|||> OK, I'll give it a try however I have over 50 columns so kind of a
> pain.
CREATE VIEW tmp_vw_foo
AS
SELECT * FROM SomeTable
GO
SELECT COLUMN_NAME+',' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tmp_vw_foo'
ORDER BY ORDINAL_POSITION
The most work you're going to do, aside from copy and paste, is deleting the
last comma.
Yep, that's a real pain. :-)|||Sweet. Thanks for that Aaron.
Aaron Bertrand [SQL Server MVP] wrote:
> CREATE VIEW tmp_vw_foo
> AS
> SELECT * FROM SomeTable
> GO
> SELECT COLUMN_NAME+',' FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'tmp_vw_foo'
> ORDER BY ORDINAL_POSITION
> The most work you're going to do, aside from copy and paste, is deleting t
he
> last comma.
> Yep, that's a real pain. :-)

Add a column to a view

hello,
I am trying to add a column to a view. This is a generic column not pulling
from the other tables but will be updated based on the values of the other
columns.
I thought I simply altering the view and add a column and set a value, if
the other values were found true. However, it is not allowing me to add a
column unless it is a integer value. Any suggestions?you cannot add a column here.
You use alter view to change the vew definition. You will have to give the
full sql stament with the computed column (if I am not wrong) in the view.
use it this way. Its very similar to create view just the keyword create is
replaced by alter. Hope this helps.
alter view vname
as
select .... col1 + col2 as new_col
from table1
where <conditions>|||> I thought I simply altering the view and add a column and set a value, if
> the other values were found true. However, it is not allowing me to add a
> column unless it is a integer value.
What does "not allowing" mean? Can you show the DDL for the table
referenced in the view, the existing code for the view, the change you are
attempting, and the exact text of the error? A lot of the people here are
pretty smart, but not many are psychic.
A|||Well, I tried to alter the view but it not giving me what I want.
Currently, I am pulling all the columns from other tables. However, I want
to create a new column called columnD and set it to 1 if columnA, columnB an
d
columnC are 1 else set it to 0.
"Omnibuzz" wrote:

> you cannot add a column here.
> You use alter view to change the vew definition. You will have to give the
> full sql stament with the computed column (if I am not wrong) in the view.
> use it this way. Its very similar to create view just the keyword create i
s
> replaced by alter. Hope this helps.
> alter view vname
> as
> select .... col1 + col2 as new_col
> from table1
> where <conditions>|||Are you using Query Analyzer or the view editor in Enterprise Manager? What
code are you trying to run? This sounds like it needs a CASE expression,
and the ability to understand CASE is a serious limitation in Enterprise
Manager. As I asked before, if you can provide *SPECIFIC* information, we
may be able to help.
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:9240DFD4-B5A0-482B-B64E-AE13F3B31E4E@.microsoft.com...
> Well, I tried to alter the view but it not giving me what I want.
> Currently, I am pulling all the columns from other tables. However, I want
> to create a new column called columnD and set it to 1 if columnA, columnB
> and
> columnC are 1 else set it to 0.
> "Omnibuzz" wrote:
>|||>I have it sort of working but it is not totally correct. So i am sing a
> better way in adding a column to the view based on the below criteria.
And I'd like my car to run better! But if I can't be bothered to give my
mechanic more information, he's going to tell me to go jump off a bridge...|||Since you haven't given the defn, I have created a sample. See if this is
what you wnted. Hope this helps.
create table tbl (a int,b int,c int)
go
insert into tbl values (1,1,1)
insert into tbl values (1,1,0)
create view vw1
as
select a,b,c from tbl
select * from vw1
-- till above was your setup...
--now do this
alter view vw1
as
select a,b,c, case when a=b and b=c and c=1 then 1 else 0 end as d
from tbl
select * from vw1|||Initially,
I wrote to add columnD
alter view sam_generic
AS
select columnD, a.columnA, a.columnB,a. columnC, m.member, v.history,
u.category, c.content, l.letter
from tableA AS a INNER JOIN
tableU on a.category = u.category LEFT OUTER JOIN
tableC on a.id = c.id LEFT OUTER JOIN
tableL on a.id = l.id LEFT OUTER JOIN
tableM on a.id = m.id LEFT OUTER JOIN
tableV on a.id =v.id
Set columnD = 1 Where Exists (a.columnA = 1) AND (if a.columnB = 1) AND (if
(a. columnC=1)
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "not allowing" mean? Can you show the DDL for the table
> referenced in the view, the existing code for the view, the change you are
> attempting, and the exact text of the error? A lot of the people here are
> pretty smart, but not many are psychic.
> A
>
>|||I was using query analyzer. I didn't think about using case statements. I'll
try that.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you using Query Analyzer or the view editor in Enterprise Manager? Wh
at
> code are you trying to run? This sounds like it needs a CASE expression,
> and the ability to understand CASE is a serious limitation in Enterprise
> Manager. As I asked before, if you can provide *SPECIFIC* information, we
> may be able to help.
>
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:9240DFD4-B5A0-482B-B64E-AE13F3B31E4E@.microsoft.com...
>
>|||I was submiting the information that you requested. I was working on multipl
e
tasks, so give me a minute to send the information to you.
"Aaron Bertrand [SQL Server MVP]" wrote:

> And I'd like my car to run better! But if I can't be bothered to give my
> mechanic more information, he's going to tell me to go jump off a bridge..
.
>
>

Add a checkbox report item

Hi there,

I am using SQL Server 2005 Reporting Services. Right now, I have a textbox with 0 or 1 values in my report and I want to show a checkbox: checked for value 1 and uncheck for 0. Is there anyway to add a checkbox in the report?

Thanks in advance.

semaj

A bit of a hack, but I think the only way to do this is to create 2 images of a checkbox (checked and unchecked), place that on your report and set the source to be an expression based on your field value.

=Iif(Fileds!check.Value = 0, "unchecked.gif", "checked.gif")

Add "(all)" to dataset

Hi list!
I have a set of report parameters and some are dropdowns (combo) from
datasets. In adition to the values in a dropdown I want the value "(all)" as
the first value in each dropdown. How do I do that?Hello -
You will need to first make sure your parameter will Allow NULL values in
the Rpeort Parameter properties. Next, in your dataset that drives the
parameter drop down list, UNION the 'ALL' with a NULL value, something like
this.
Select MyID, Description from MyTable
UNION
Select NULL, 'All'
In your Rpeort Properties, if you have MyID as the Value field and
Description as the Label field, 'All' will now be displayed in the dropdown
and when selected will pass the NULL value to the report and return all of
the data you expect. I had to something very similar in my projects, prior
to driving the dropdowns in custom forms with code.
--
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"J. Kando 411" <J. Kando 411@.discussions.microsoft.com> wrote in message
news:DA7D6DCC-F9AF-4C88-8276-70B9C968E187@.microsoft.com...
> Hi list!
> I have a set of report parameters and some are dropdowns (combo) from
> datasets. In adition to the values in a dropdown I want the value "(all)"
> as
> the first value in each dropdown. How do I do that?

Actual storage of Varchar values

I have a table with a definition similar to the following.
create table sizetest (pk int identity(1,1)
, V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
, V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
, V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
I have varied the length of column V9 between 16 and 40. When X <= 16 the
table is created without warning. When x = 40 I am told that the row length
is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have not
been able to come up with a definition for varchar storage that matches the
results I am seeing. This Reference
(http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says that n
+ 2 is the answer but that does not work with any of this data I have
generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is not
equal to 8084 and is more than 8060 which BOL says is the max allowed. So I
understand the warning but not the numbers. On the other hand (8 * 1002) + 20
+ 4 = 8040 which is not equal to 8064 and is less than 8060. So why is this
one getting the warning?
I changed the table definition to 3 varchar fields 4000, 4000, and x and got
similar results but different numbers. Still no predicatble results.
How can I determine exactly what potential strorage is required if all of
the varchar fields are full.
My C++ developer won't believe me unless I can give him the details and show
a way to reliably predict the row length for any combination of column
types/sizes.
Thanks
Ray Herring
n+2 is the storage for the varchar fields themselves, but there is other
overhead required in each row.
You did not say what version you are using, but the documentation you
referred to is for SQL Server 2008, which is not in production yet, and the
docs are incomplete.
Take a look here for some information about additional overhead bytes:
http://msdn2.microsoft.com/en-us/library/ms189124.aspx
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
> I have a table with a definition similar to the following.
> create table sizetest (pk int identity(1,1)
> , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
> , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
> , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
> I have varied the length of column V9 between 16 and 40. When X <= 16 the
> table is created without warning. When x = 40 I am told that the row
> length
> is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have
> not
> been able to come up with a definition for varchar storage that matches
> the
> results I am seeing. This Reference
> (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
> that n
> + 2 is the answer but that does not work with any of this data I have
> generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is
> not
> equal to 8084 and is more than 8060 which BOL says is the max allowed. So
> I
> understand the warning but not the numbers. On the other hand (8 * 1002) +
> 20
> + 4 = 8040 which is not equal to 8064 and is less than 8060. So why is
> this
> one getting the warning?
> I changed the table definition to 3 varchar fields 4000, 4000, and x and
> got
> similar results but different numbers. Still no predicatble results.
> How can I determine exactly what potential strorage is required if all of
> the varchar fields are full.
> My C++ developer won't believe me unless I can give him the details and
> show
> a way to reliably predict the row length for any combination of column
> types/sizes.
> Thanks
> --
> Ray Herring
|||Thanks Kalen
I thought about the version a little after I posted. I am using 2000 right
now.
I found an additional reference (I think 2k5) that mentions a 24 byte over
head that occurs when a column overflows a page and gets forwarded.
When I add piece in I come within +/- 1 byte of the correct numbers.
Ray Herring
"Kalen Delaney" wrote:

> n+2 is the storage for the varchar fields themselves, but there is other
> overhead required in each row.
> You did not say what version you are using, but the documentation you
> referred to is for SQL Server 2008, which is not in production yet, and the
> docs are incomplete.
> Take a look here for some information about additional overhead bytes:
> http://msdn2.microsoft.com/en-us/library/ms189124.aspx
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
>
>
|||This overflow doesn't happen on SQL 2000 though. Row overflow is new in
2005. And you are getting your errors because the overflow is not allowed;
if you were using 2005, you wouldn't get these errors.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:41896F83-7882-4045-9174-E239F1D25308@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen
> I thought about the version a little after I posted. I am using 2000
> right
> now.
> I found an additional reference (I think 2k5) that mentions a 24 byte over
> head that occurs when a column overflows a page and gets forwarded.
> When I add piece in I come within +/- 1 byte of the correct numbers.
> --
> Ray Herring
>
> "Kalen Delaney" wrote:
|||This is SQL 2000. I have about 100 client sites that have not moved to 2005.
The tables are being created and/or altered from scripts run in Isqlw.
This is the warning message.
"Warning: The table 'xxxxx' has been created but its maximum row size (8065)
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."
I have not received a runtime error on an insert or update so far but I
figure that is only a matter of time.
The table definition has 13 colums including a BigInt(primary key), 2 ints
, 1 datetime, 2 varchar(500) and 7 varchar(1000).
The pk is a clusterd index. The pk and the 2 ints are also foreign keys.
There are no other constraints or indexes.
So using what I have found the row size should be 8 + 4 + 4 + 8 + (7*1002) +
(2*502) = 8042. This should fit according to the documentation I have on 2K.
So where do the additional 23 bytes come from?
If you add the 24 bytes for a column overflow (I understand that is 2K5)
then the total is 8066 which is over the limit but not equal to the total
cited in the warning message.
Ray Herring
"Kalen Delaney" wrote:

> This overflow doesn't happen on SQL 2000 though. Row overflow is new in
> 2005. And you are getting your errors because the overflow is not allowed;
> if you were using 2005, you wouldn't get these errors.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:41896F83-7882-4045-9174-E239F1D25308@.microsoft.com...
>
>
|||I imagine I got your table script wrong because there is an extra byte you
didn't tell us about? When I run this I get the same message but 8065, not
8064 (this is on 8.00.2039):
CREATE TABLE dbo.splunge
(
BigID BIGINT,
FooID INT,
BarID INT,
EventDate DATETIME,
vc500_1 VARCHAR(500),
vc500_2 VARCHAR(500),
vc1000_1 VARCHAR(1000),
vc1000_2 VARCHAR(1000),
vc1000_3 VARCHAR(1000),
vc1000_4 VARCHAR(1000),
vc1000_5 VARCHAR(1000),
vc1000_6 VARCHAR(1000),
vc1000_7 VARCHAR(1000)
);
GO
DROP TABLE dbo.splunge;
Warning: The table 'splunge' has been created but its maximum row size
(8064) 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.
Some interesting stuff about this here, including 14 bytes of overhead that
are not documented in 2000 BOL or the Inside SQL Server 2000 book (though
Steve says the content was presented on Kalen's site
www.insidesqlserver.com, but I cannot find it now, since it has been
replaced with the 2005 version):
http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
So if we use Steve's information, we have:
2 bytes for status bits
2 bytes for # of columns
24 bytes for fixed length data
2 bytes for storing the length of fixed length data
8000 bytes for variable length data
18 bytes for variable length overhead
2 bytes for null bitmap (9-16 nullable columns)
8050
+14 bytes for reserved pointer space = 8064
|||>I imagine I got your table script wrong because there is an extra byte you
>didn't tell us about? When I run this I get the same message but 8065, not
>8064 (this is on 8.00.2039):
Sorry, I got that backwards, obviously. You claim 8065 but I get 8064.
And it made no difference if I made BigID PK and/or identity, or the ints
nullable, or the datetime nullable...
|||Thanks Aaron
I don't know why there is a one byte difference in our numbers. Actually my
calculations also differ by one from the warning message.
The columns in the table are in a different order. Do you think that might
be related?
Anyway the detail you have provided is sufficient for what I need to pass on
to the developer.
Thanks again.
Ray Herring
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sorry, I got that backwards, obviously. You claim 8065 but I get 8064.
> And it made no difference if I made BigID PK and/or identity, or the ints
> nullable, or the datetime nullable...
>
|||There are another two bytes needed for the pointer in the slot array at the
end of the page, but this is counted against the size of the row.
And another two bytes for the first variable length column. I can't get the
numbers to add up right now either, but I'm not going to worry about it.
Also, I don't think the space for the pointer is counted against the total.
With Snapshot Isolation in SQL 2005, the max row size is just increased.
FYI, the null bitmap has a bit for every column, not just the nullable ones.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:6C3D5975-0885-4BE5-94EB-33D1B5312130@.microsoft.com...
>I imagine I got your table script wrong because there is an extra byte you
>didn't tell us about? When I run this I get the same message but 8065, not
>8064 (this is on 8.00.2039):
> CREATE TABLE dbo.splunge
> (
> BigID BIGINT,
> FooID INT,
> BarID INT,
> EventDate DATETIME,
> vc500_1 VARCHAR(500),
> vc500_2 VARCHAR(500),
> vc1000_1 VARCHAR(1000),
> vc1000_2 VARCHAR(1000),
> vc1000_3 VARCHAR(1000),
> vc1000_4 VARCHAR(1000),
> vc1000_5 VARCHAR(1000),
> vc1000_6 VARCHAR(1000),
> vc1000_7 VARCHAR(1000)
> );
> GO
> DROP TABLE dbo.splunge;
> --
> Warning: The table 'splunge' has been created but its maximum row size
> (8064) 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.
> Some interesting stuff about this here, including 14 bytes of overhead
> that are not documented in 2000 BOL or the Inside SQL Server 2000 book
> (though Steve says the content was presented on Kalen's site
> www.insidesqlserver.com, but I cannot find it now, since it has been
> replaced with the 2005 version):
> http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
> So if we use Steve's information, we have:
> 2 bytes for status bits
> 2 bytes for # of columns
> 24 bytes for fixed length data
> 2 bytes for storing the length of fixed length data
> 8000 bytes for variable length data
> 18 bytes for variable length overhead
> 2 bytes for null bitmap (9-16 nullable columns)
> 8050
> +14 bytes for reserved pointer space = 8064
|||Thanks Kalen and Arron.
I had just found a reference
"http://msdn2.microsoft.com/en-us/library/ms189124.aspx" (2005 again) that
mentions the null bit map and also other over head bytes.
I guess the row size limit is not simply 8K, 8060, 8092, xxxx but rather
depends on how you define it. It seems that MS would have rolled all of the
basic overhead (null bit map, etc) into the basic page size so that we would
only need to work out the variable and fixed lenght columns.
I guess I will start falling back on the =/-10% sort of estimate. It sounds
like even the column over flow is a mixed blessing performance wise.
Thanks for the assistance guys.
Ray Herring
"Kalen Delaney" wrote:

> There are another two bytes needed for the pointer in the slot array at the
> end of the page, but this is counted against the size of the row.
> And another two bytes for the first variable length column. I can't get the
> numbers to add up right now either, but I'm not going to worry about it.
> Also, I don't think the space for the pointer is counted against the total.
> With Snapshot Isolation in SQL 2005, the max row size is just increased.
> FYI, the null bitmap has a bit for every column, not just the nullable ones.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:6C3D5975-0885-4BE5-94EB-33D1B5312130@.microsoft.com...
>
>

Actual storage of Varchar values

I have a table with a definition similar to the following.
create table sizetest (pk int identity(1,1)
, V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
, V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
, V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
I have varied the length of column V9 between 16 and 40. When X <= 16 the
table is created without warning. When x = 40 I am told that the row length
is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have not
been able to come up with a definition for varchar storage that matches the
results I am seeing. This Reference
(http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says that n
+ 2 is the answer but that does not work with any of this data I have
generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is not
equal to 8084 and is more than 8060 which BOL says is the max allowed. So I
understand the warning but not the numbers. On the other hand (8 * 1002) + 20
+ 4 = 8040 which is not equal to 8064 and is less than 8060. So why is this
one getting the warning?
I changed the table definition to 3 varchar fields 4000, 4000, and x and got
similar results but different numbers. Still no predicatble results.
How can I determine exactly what potential strorage is required if all of
the varchar fields are full.
My C++ developer won't believe me unless I can give him the details and show
a way to reliably predict the row length for any combination of column
types/sizes.
Thanks
--
Ray Herringn+2 is the storage for the varchar fields themselves, but there is other
overhead required in each row.
You did not say what version you are using, but the documentation you
referred to is for SQL Server 2008, which is not in production yet, and the
docs are incomplete.
Take a look here for some information about additional overhead bytes:
http://msdn2.microsoft.com/en-us/library/ms189124.aspx
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
> I have a table with a definition similar to the following.
> create table sizetest (pk int identity(1,1)
> , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
> , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
> , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
> I have varied the length of column V9 between 16 and 40. When X <= 16 the
> table is created without warning. When x = 40 I am told that the row
> length
> is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have
> not
> been able to come up with a definition for varchar storage that matches
> the
> results I am seeing. This Reference
> (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
> that n
> + 2 is the answer but that does not work with any of this data I have
> generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is
> not
> equal to 8084 and is more than 8060 which BOL says is the max allowed. So
> I
> understand the warning but not the numbers. On the other hand (8 * 1002) +
> 20
> + 4 = 8040 which is not equal to 8064 and is less than 8060. So why is
> this
> one getting the warning?
> I changed the table definition to 3 varchar fields 4000, 4000, and x and
> got
> similar results but different numbers. Still no predicatble results.
> How can I determine exactly what potential strorage is required if all of
> the varchar fields are full.
> My C++ developer won't believe me unless I can give him the details and
> show
> a way to reliably predict the row length for any combination of column
> types/sizes.
> Thanks
> --
> Ray Herring|||Thanks Kalen
I thought about the version a little after I posted. I am using 2000 right
now.
I found an additional reference (I think 2k5) that mentions a 24 byte over
head that occurs when a column overflows a page and gets forwarded.
When I add piece in I come within +/- 1 byte of the correct numbers.
--
Ray Herring
"Kalen Delaney" wrote:
> n+2 is the storage for the varchar fields themselves, but there is other
> overhead required in each row.
> You did not say what version you are using, but the documentation you
> referred to is for SQL Server 2008, which is not in production yet, and the
> docs are incomplete.
> Take a look here for some information about additional overhead bytes:
> http://msdn2.microsoft.com/en-us/library/ms189124.aspx
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
> > I have a table with a definition similar to the following.
> > create table sizetest (pk int identity(1,1)
> > , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
> > , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
> > , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
> > I have varied the length of column V9 between 16 and 40. When X <= 16 the
> > table is created without warning. When x = 40 I am told that the row
> > length
> > is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I have
> > not
> > been able to come up with a definition for varchar storage that matches
> > the
> > results I am seeing. This Reference
> > (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
> > that n
> > + 2 is the answer but that does not work with any of this data I have
> > generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which is
> > not
> > equal to 8084 and is more than 8060 which BOL says is the max allowed. So
> > I
> > understand the warning but not the numbers. On the other hand (8 * 1002) +
> > 20
> > + 4 = 8040 which is not equal to 8064 and is less than 8060. So why is
> > this
> > one getting the warning?
> > I changed the table definition to 3 varchar fields 4000, 4000, and x and
> > got
> > similar results but different numbers. Still no predicatble results.
> > How can I determine exactly what potential strorage is required if all of
> > the varchar fields are full.
> >
> > My C++ developer won't believe me unless I can give him the details and
> > show
> > a way to reliably predict the row length for any combination of column
> > types/sizes.
> > Thanks
> >
> > --
> > Ray Herring
>
>|||This overflow doesn't happen on SQL 2000 though. Row overflow is new in
2005. And you are getting your errors because the overflow is not allowed;
if you were using 2005, you wouldn't get these errors.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Ray" <Ray@.discussions.microsoft.com> wrote in message
news:41896F83-7882-4045-9174-E239F1D25308@.microsoft.com...
> Thanks Kalen
> I thought about the version a little after I posted. I am using 2000
> right
> now.
> I found an additional reference (I think 2k5) that mentions a 24 byte over
> head that occurs when a column overflows a page and gets forwarded.
> When I add piece in I come within +/- 1 byte of the correct numbers.
> --
> Ray Herring
>
> "Kalen Delaney" wrote:
>> n+2 is the storage for the varchar fields themselves, but there is other
>> overhead required in each row.
>> You did not say what version you are using, but the documentation you
>> referred to is for SQL Server 2008, which is not in production yet, and
>> the
>> docs are incomplete.
>> Take a look here for some information about additional overhead bytes:
>> http://msdn2.microsoft.com/en-us/library/ms189124.aspx
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://DVD.kalendelaney.com
>>
>> "Ray" <Ray@.discussions.microsoft.com> wrote in message
>> news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
>> > I have a table with a definition similar to the following.
>> > create table sizetest (pk int identity(1,1)
>> > , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
>> > , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
>> > , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
>> > I have varied the length of column V9 between 16 and 40. When X <= 16
>> > the
>> > table is created without warning. When x = 40 I am told that the row
>> > length
>> > is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I
>> > have
>> > not
>> > been able to come up with a definition for varchar storage that matches
>> > the
>> > results I am seeing. This Reference
>> > (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
>> > that n
>> > + 2 is the answer but that does not work with any of this data I have
>> > generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which
>> > is
>> > not
>> > equal to 8084 and is more than 8060 which BOL says is the max allowed.
>> > So
>> > I
>> > understand the warning but not the numbers. On the other hand (8 *
>> > 1002) +
>> > 20
>> > + 4 = 8040 which is not equal to 8064 and is less than 8060. So why
>> > is
>> > this
>> > one getting the warning?
>> > I changed the table definition to 3 varchar fields 4000, 4000, and x
>> > and
>> > got
>> > similar results but different numbers. Still no predicatble results.
>> > How can I determine exactly what potential strorage is required if all
>> > of
>> > the varchar fields are full.
>> >
>> > My C++ developer won't believe me unless I can give him the details and
>> > show
>> > a way to reliably predict the row length for any combination of column
>> > types/sizes.
>> > Thanks
>> >
>> > --
>> > Ray Herring
>>|||This is SQL 2000. I have about 100 client sites that have not moved to 2005.
The tables are being created and/or altered from scripts run in Isqlw.
This is the warning message.
"Warning: The table 'xxxxx' has been created but its maximum row size (8065)
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."
I have not received a runtime error on an insert or update so far but I
figure that is only a matter of time.
The table definition has 13 colums including a BigInt(primary key), 2 ints
, 1 datetime, 2 varchar(500) and 7 varchar(1000).
The pk is a clusterd index. The pk and the 2 ints are also foreign keys.
There are no other constraints or indexes.
So using what I have found the row size should be 8 + 4 + 4 + 8 + (7*1002) +
(2*502) = 8042. This should fit according to the documentation I have on 2K.
So where do the additional 23 bytes come from?
If you add the 24 bytes for a column overflow (I understand that is 2K5)
then the total is 8066 which is over the limit but not equal to the total
cited in the warning message.
--
Ray Herring
"Kalen Delaney" wrote:
> This overflow doesn't happen on SQL 2000 though. Row overflow is new in
> 2005. And you are getting your errors because the overflow is not allowed;
> if you were using 2005, you wouldn't get these errors.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> news:41896F83-7882-4045-9174-E239F1D25308@.microsoft.com...
> > Thanks Kalen
> > I thought about the version a little after I posted. I am using 2000
> > right
> > now.
> > I found an additional reference (I think 2k5) that mentions a 24 byte over
> > head that occurs when a column overflows a page and gets forwarded.
> > When I add piece in I come within +/- 1 byte of the correct numbers.
> > --
> > Ray Herring
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> n+2 is the storage for the varchar fields themselves, but there is other
> >> overhead required in each row.
> >>
> >> You did not say what version you are using, but the documentation you
> >> referred to is for SQL Server 2008, which is not in production yet, and
> >> the
> >> docs are incomplete.
> >>
> >> Take a look here for some information about additional overhead bytes:
> >> http://msdn2.microsoft.com/en-us/library/ms189124.aspx
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.InsideSQLServer.com
> >> http://DVD.kalendelaney.com
> >>
> >>
> >> "Ray" <Ray@.discussions.microsoft.com> wrote in message
> >> news:5B7540FA-BBC4-4B77-BC73-EA6FFE1524BE@.microsoft.com...
> >> > I have a table with a definition similar to the following.
> >> > create table sizetest (pk int identity(1,1)
> >> > , V1 Varchar(1000) , V2 varchar(1000) , V3 varchar(1000)
> >> > , V4 Varchar(1000) , V5 varchar(1000) , V6 varchar(1000)
> >> > , V7 Varchar(1000) , V8 varchar(1000) , V9 varchar(x)
> >> > I have varied the length of column V9 between 16 and 40. When X <= 16
> >> > the
> >> > table is created without warning. When x = 40 I am told that the row
> >> > length
> >> > is 8084 or 24 bytes too big. X= 20 says 8064 or 4 bytes too big. I
> >> > have
> >> > not
> >> > been able to come up with a definition for varchar storage that matches
> >> > the
> >> > results I am seeing. This Reference
> >> > (http://msdn2.microsoft.com/en-us/library/ms176089(SQL.100).aspx) says
> >> > that n
> >> > + 2 is the answer but that does not work with any of this data I have
> >> > generated. For Example (8 * 1002) + 42 + 4 (for the pk) = 8062 which
> >> > is
> >> > not
> >> > equal to 8084 and is more than 8060 which BOL says is the max allowed.
> >> > So
> >> > I
> >> > understand the warning but not the numbers. On the other hand (8 *
> >> > 1002) +
> >> > 20
> >> > + 4 = 8040 which is not equal to 8064 and is less than 8060. So why
> >> > is
> >> > this
> >> > one getting the warning?
> >> > I changed the table definition to 3 varchar fields 4000, 4000, and x
> >> > and
> >> > got
> >> > similar results but different numbers. Still no predicatble results.
> >> > How can I determine exactly what potential strorage is required if all
> >> > of
> >> > the varchar fields are full.
> >> >
> >> > My C++ developer won't believe me unless I can give him the details and
> >> > show
> >> > a way to reliably predict the row length for any combination of column
> >> > types/sizes.
> >> > Thanks
> >> >
> >> > --
> >> > Ray Herring
> >>
> >>
> >>
>
>|||I imagine I got your table script wrong because there is an extra byte you
didn't tell us about? When I run this I get the same message but 8065, not
8064 (this is on 8.00.2039):
CREATE TABLE dbo.splunge
(
BigID BIGINT,
FooID INT,
BarID INT,
EventDate DATETIME,
vc500_1 VARCHAR(500),
vc500_2 VARCHAR(500),
vc1000_1 VARCHAR(1000),
vc1000_2 VARCHAR(1000),
vc1000_3 VARCHAR(1000),
vc1000_4 VARCHAR(1000),
vc1000_5 VARCHAR(1000),
vc1000_6 VARCHAR(1000),
vc1000_7 VARCHAR(1000)
);
GO
DROP TABLE dbo.splunge;
--
Warning: The table 'splunge' has been created but its maximum row size
(8064) 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.
Some interesting stuff about this here, including 14 bytes of overhead that
are not documented in 2000 BOL or the Inside SQL Server 2000 book (though
Steve says the content was presented on Kalen's site
www.insidesqlserver.com, but I cannot find it now, since it has been
replaced with the 2005 version):
http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
So if we use Steve's information, we have:
2 bytes for status bits
2 bytes for # of columns
24 bytes for fixed length data
2 bytes for storing the length of fixed length data
8000 bytes for variable length data
18 bytes for variable length overhead
2 bytes for null bitmap (9-16 nullable columns)
8050
+14 bytes for reserved pointer space = 8064|||>I imagine I got your table script wrong because there is an extra byte you
>didn't tell us about? When I run this I get the same message but 8065, not
>8064 (this is on 8.00.2039):
Sorry, I got that backwards, obviously. You claim 8065 but I get 8064.
And it made no difference if I made BigID PK and/or identity, or the ints
nullable, or the datetime nullable...|||Thanks Aaron
I don't know why there is a one byte difference in our numbers. Actually my
calculations also differ by one from the warning message.
The columns in the table are in a different order. Do you think that might
be related?
Anyway the detail you have provided is sufficient for what I need to pass on
to the developer.
Thanks again.
--
Ray Herring
"Aaron Bertrand [SQL Server MVP]" wrote:
> >I imagine I got your table script wrong because there is an extra byte you
> >didn't tell us about? When I run this I get the same message but 8065, not
> >8064 (this is on 8.00.2039):
> Sorry, I got that backwards, obviously. You claim 8065 but I get 8064.
> And it made no difference if I made BigID PK and/or identity, or the ints
> nullable, or the datetime nullable...
>|||There are another two bytes needed for the pointer in the slot array at the
end of the page, but this is counted against the size of the row.
And another two bytes for the first variable length column. I can't get the
numbers to add up right now either, but I'm not going to worry about it.
Also, I don't think the space for the pointer is counted against the total.
With Snapshot Isolation in SQL 2005, the max row size is just increased.
FYI, the null bitmap has a bit for every column, not just the nullable ones.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:6C3D5975-0885-4BE5-94EB-33D1B5312130@.microsoft.com...
>I imagine I got your table script wrong because there is an extra byte you
>didn't tell us about? When I run this I get the same message but 8065, not
>8064 (this is on 8.00.2039):
> CREATE TABLE dbo.splunge
> (
> BigID BIGINT,
> FooID INT,
> BarID INT,
> EventDate DATETIME,
> vc500_1 VARCHAR(500),
> vc500_2 VARCHAR(500),
> vc1000_1 VARCHAR(1000),
> vc1000_2 VARCHAR(1000),
> vc1000_3 VARCHAR(1000),
> vc1000_4 VARCHAR(1000),
> vc1000_5 VARCHAR(1000),
> vc1000_6 VARCHAR(1000),
> vc1000_7 VARCHAR(1000)
> );
> GO
> DROP TABLE dbo.splunge;
> --
> Warning: The table 'splunge' has been created but its maximum row size
> (8064) 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.
> Some interesting stuff about this here, including 14 bytes of overhead
> that are not documented in 2000 BOL or the Inside SQL Server 2000 book
> (though Steve says the content was presented on Kalen's site
> www.insidesqlserver.com, but I cannot find it now, since it has been
> replaced with the 2005 version):
> http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
> So if we use Steve's information, we have:
> 2 bytes for status bits
> 2 bytes for # of columns
> 24 bytes for fixed length data
> 2 bytes for storing the length of fixed length data
> 8000 bytes for variable length data
> 18 bytes for variable length overhead
> 2 bytes for null bitmap (9-16 nullable columns)
> 8050
> +14 bytes for reserved pointer space = 8064|||Thanks Kalen and Arron.
I had just found a reference
"http://msdn2.microsoft.com/en-us/library/ms189124.aspx" (2005 again) that
mentions the null bit map and also other over head bytes.
I guess the row size limit is not simply 8K, 8060, 8092, xxxx but rather
depends on how you define it. It seems that MS would have rolled all of the
basic overhead (null bit map, etc) into the basic page size so that we would
only need to work out the variable and fixed lenght columns.
I guess I will start falling back on the =/-10% sort of estimate. It sounds
like even the column over flow is a mixed blessing performance wise.
Thanks for the assistance guys.
--
Ray Herring
"Kalen Delaney" wrote:
> There are another two bytes needed for the pointer in the slot array at the
> end of the page, but this is counted against the size of the row.
> And another two bytes for the first variable length column. I can't get the
> numbers to add up right now either, but I'm not going to worry about it.
> Also, I don't think the space for the pointer is counted against the total.
> With Snapshot Isolation in SQL 2005, the max row size is just increased.
> FYI, the null bitmap has a bit for every column, not just the nullable ones.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:6C3D5975-0885-4BE5-94EB-33D1B5312130@.microsoft.com...
> >I imagine I got your table script wrong because there is an extra byte you
> >didn't tell us about? When I run this I get the same message but 8065, not
> >8064 (this is on 8.00.2039):
> >
> > CREATE TABLE dbo.splunge
> > (
> > BigID BIGINT,
> > FooID INT,
> > BarID INT,
> > EventDate DATETIME,
> > vc500_1 VARCHAR(500),
> > vc500_2 VARCHAR(500),
> > vc1000_1 VARCHAR(1000),
> > vc1000_2 VARCHAR(1000),
> > vc1000_3 VARCHAR(1000),
> > vc1000_4 VARCHAR(1000),
> > vc1000_5 VARCHAR(1000),
> > vc1000_6 VARCHAR(1000),
> > vc1000_7 VARCHAR(1000)
> > );
> > GO
> > DROP TABLE dbo.splunge;
> >
> > --
> > Warning: The table 'splunge' has been created but its maximum row size
> > (8064) 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.
> >
> > Some interesting stuff about this here, including 14 bytes of overhead
> > that are not documented in 2000 BOL or the Inside SQL Server 2000 book
> > (though Steve says the content was presented on Kalen's site
> > www.insidesqlserver.com, but I cannot find it now, since it has been
> > replaced with the 2005 version):
> >
> > http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/
> >
> > So if we use Steve's information, we have:
> >
> > 2 bytes for status bits
> > 2 bytes for # of columns
> > 24 bytes for fixed length data
> > 2 bytes for storing the length of fixed length data
> > 8000 bytes for variable length data
> > 18 bytes for variable length overhead
> > 2 bytes for null bitmap (9-16 nullable columns)
> >
> > 8050
> >
> > +14 bytes for reserved pointer space = 8064
>
>