Do large column names hamper performance?Names - no widths - yes. Increases the physical I/O required to process
queries.
HTH
Jerry
"Wes" <Wes@.discussions.microsoft.com> wrote in message
news:003C3771-3BA5-4A37-B7A9-0951B2BCAD09@.microsoft.com...
> Do large column names hamper performance?|||Possibly. At the very least longer names might increase network traffic if
they are being used in SQL batches. But if you are experiencing a
performance problem then I would look for other potential causes first.
An unusually long column name perhaps indicates either a poor naming
convention or, worse, that you are representing some element of data in a
column name. Either of those are good reasons to change the name - with or
without any performance impact. For the record, most of the column names in
my current project are less than 30 characters long.
David Portas
SQL Server MVP
--
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Thursday, March 8, 2012
Wednesday, March 7, 2012
Column Widths
Is there a way to have columns automatically size themselves based on the
contents of the column name and data? I guess I'm looking for something
similar to Excel's Format|Column|AutoFit Selection option.
What I see happening with our reports is that a column name of...
Reservation Departure Date
...appears as...
Reservation
Depart Da
te
I would prefer to see the column heading in a single line of...
Reservation Departure Date
...however, I don't want to have to resize every report column to make the
contents of the column appear like this.
Is there a simple report setting to do an autofit?Yes Andy, I face this problem too!
Up to now, do you have any ways to fix this problem'
Super thanks if anyone can fix it!
"Andy" wrote:
> Is there a way to have columns automatically size themselves based on the
> contents of the column name and data? I guess I'm looking for something
> similar to Excel's Format|Column|AutoFit Selection option.
> What I see happening with our reports is that a column name of...
> Reservation Departure Date
> ...appears as...
> Reservation
> Depart Da
> te
> I would prefer to see the column heading in a single line of...
> Reservation Departure Date
> ...however, I don't want to have to resize every report column to make the
> contents of the column appear like this.
> Is there a simple report setting to do an autofit?
>
>
contents of the column name and data? I guess I'm looking for something
similar to Excel's Format|Column|AutoFit Selection option.
What I see happening with our reports is that a column name of...
Reservation Departure Date
...appears as...
Reservation
Depart Da
te
I would prefer to see the column heading in a single line of...
Reservation Departure Date
...however, I don't want to have to resize every report column to make the
contents of the column appear like this.
Is there a simple report setting to do an autofit?Yes Andy, I face this problem too!
Up to now, do you have any ways to fix this problem'
Super thanks if anyone can fix it!
"Andy" wrote:
> Is there a way to have columns automatically size themselves based on the
> contents of the column name and data? I guess I'm looking for something
> similar to Excel's Format|Column|AutoFit Selection option.
> What I see happening with our reports is that a column name of...
> Reservation Departure Date
> ...appears as...
> Reservation
> Depart Da
> te
> I would prefer to see the column heading in a single line of...
> Reservation Departure Date
> ...however, I don't want to have to resize every report column to make the
> contents of the column appear like this.
> Is there a simple report setting to do an autofit?
>
>
Column size in emails generated using XP sendmail
Hi, with some help today I was able to get my stored procedure running
and have the results emailed to me. However, this is how its showing
up:
Accounting_Year WK_IN_FYEAR Location
GL_Account
Col Data Difference
----- ---- -----------
----
------- ------- -------
2007 49 Test1
500-001
-2587872.0200 -2587872.0200 .0000
2007 49 Test2
500-001
-3344713.5000 -3344713.5000 .0000
2007 49 Test3
500-001
You are awesome! That works... I didnt use the @.attach_results TRUE
because i was getting a system error. I used @.width set to 170 and
that worked fine.
and have the results emailed to me. However, this is how its showing
up:
Accounting_Year WK_IN_FYEAR Location
GL_Account
Col Data Difference
----- ---- -----------
----
------- ------- -------
2007 49 Test1
500-001
-2587872.0200 -2587872.0200 .0000
2007 49 Test2
500-001
-3344713.5000 -3344713.5000 .0000
2007 49 Test3
500-001
Is there anyway to line them up side by side properly? When i have two
colms selected the format comes out ok. Thanks for all the help
again!
Here is the sp:
CREATE PROCEDURE [dbo].[spEmailVariance]
(
@.SubjectLine as varchar(500),
@.EmailRecipient VARCHAR(100)
)
AS
DECLARE @.strBody varchar(5000)
set @.SubjectLine = 'Weekly Flash Update'
SET @.strBody =
'Select statement'
exec master.dbo.xp_sendmail
@.recipients= 'XX@.XXXX.com',
@.subject= @.SubjectLine,
@.query = @.strbody
RETURN
GOTake a look at the @.width and @.attach_results parameters of xp_sendmail.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||On Dec 14, 7:52 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:
Quote:
Originally Posted by
Take a look at the @.width and @.attach_results parameters of xp_sendmail.
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com
You are awesome! That works... I didnt use the @.attach_results TRUE
because i was getting a system error. I used @.width set to 170 and
that worked fine.
Column Size
From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.sqlmonster.com
Robert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/de...es_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.sqlmonster.com
Robert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/de...es_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com
Column Size
From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/d...>
_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/d...>
_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com
Column size
Hello World,
If Len (Expression) return the length of Expression
What will return the size of column ?
Thank's in advance.
MLHow do you define "size of column"? Storage size? If so, use the DATALENGTH(
) function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Michel" <m.landrain@.wanadoo.fr> wrote in message news:u$8G3hpxFHA.736@.tk2msftngp13.phx.gbl
..
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||The size of a column when created?
use the view INFORMATION_SCHEMA.COLUMNS and look for column
CHARACTER_MAXIMUM_LENGTH
For the length of data stored in a field you can use DATALENGTH
select DATALENGTH( Fielname) ,FieldName from YourTable
http://sqlservercode.blogspot.com/
"Michel" wrote:
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||A precision,
For exemple,
A column is defined as VARCHAR (10)
The size of column is 10 cars.
What will return 10 (the size of column) ?
"Michel" <m.landrain@.wanadoo.fr> a crit dans le message de
news:u$8G3hpxFHA.736@.tk2msftngp13.phx.gbl...
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||Maybe this example can be of help:
use pubs
select INFORMATION_SCHEMA.[COLUMNS].CHARACTER_MAXIMUM_LENGTH as DeclaredLength
,INFORMATION_SCHEMA.[COLUMNS].CHARACTER_OCTET_LENGTH as ActualLength
from INFORMATION_SCHEMA.[COLUMNS]
where (INFORMATION_SCHEMA.[COLUMNS].TABLE_NAME = 'authors')
CHARACTER_MAXIMUM_LENGTH is what you are looking for, yet consider also the
CHARACTER_OCTET_LENGTH column for unicode data types.
More on this here:
http://msdn.microsoft.com/library/d...br />
87w3.asp
ML
p.s. not the same ML obviously. :)
If Len (Expression) return the length of Expression
What will return the size of column ?
Thank's in advance.
MLHow do you define "size of column"? Storage size? If so, use the DATALENGTH(
) function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Michel" <m.landrain@.wanadoo.fr> wrote in message news:u$8G3hpxFHA.736@.tk2msftngp13.phx.gbl
..
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||The size of a column when created?
use the view INFORMATION_SCHEMA.COLUMNS and look for column
CHARACTER_MAXIMUM_LENGTH
For the length of data stored in a field you can use DATALENGTH
select DATALENGTH( Fielname) ,FieldName from YourTable
http://sqlservercode.blogspot.com/
"Michel" wrote:
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||A precision,
For exemple,
A column is defined as VARCHAR (10)
The size of column is 10 cars.
What will return 10 (the size of column) ?
"Michel" <m.landrain@.wanadoo.fr> a crit dans le message de
news:u$8G3hpxFHA.736@.tk2msftngp13.phx.gbl...
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||Maybe this example can be of help:
use pubs
select INFORMATION_SCHEMA.[COLUMNS].CHARACTER_MAXIMUM_LENGTH as DeclaredLength
,INFORMATION_SCHEMA.[COLUMNS].CHARACTER_OCTET_LENGTH as ActualLength
from INFORMATION_SCHEMA.[COLUMNS]
where (INFORMATION_SCHEMA.[COLUMNS].TABLE_NAME = 'authors')
CHARACTER_MAXIMUM_LENGTH is what you are looking for, yet consider also the
CHARACTER_OCTET_LENGTH column for unicode data types.
http://msdn.microsoft.com/library/d...br />
87w3.asp
ML
p.s. not the same ML obviously. :)
Column Size
From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com
Friday, February 24, 2012
Column max size for String
Hello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?
Many thanks
GrantVarchar can go up to 8000. You can usetext orntext fields which have no practical upper limit on the number of chars. Withtext orntext fields fields you lose some flexibility such as the ability to use thetext orntext field in a where clause.
text is for variable-length non-Unicode data.
ntext is for variable-length Unicode data.|||text datatypes can hold upto 2GB bytes of data ... And ntext also used the same limitation ... The only difference being text datatypes use 1 bytes for 1 character and ntext types use 2 bytes for storing single unicode character ...
Column Lenght
What is the query to find the maximum lenght used in the
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.A
Try something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A
|||SELECT MAX(DATALENGTH(column_name)) FROM Table
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A
|||Thanks.........
>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>
>.
>
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.A
Try something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A
|||SELECT MAX(DATALENGTH(column_name)) FROM Table
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A
|||Thanks.........
>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>
>.
>
Column Lenght
What is the query to find the maximum lenght used in the
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.ATry something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||SELECT MAX(DATALENGTH(column_name)) FROM Table
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||Thanks.........
>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>
>.
>
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.ATry something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||SELECT MAX(DATALENGTH(column_name)) FROM Table
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||Thanks.........
>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>
>.
>
Subscribe to:
Posts (Atom)