Column space utilization

Hello

I created a table with column name "description" as varchar(8000). My doubt is if I am not storing 8000 characters in this column, will SQL Server use memory space needed for 8000 characters ? or will it use only the space that needs for my text. ?

Thanking You
NavaneethIt will only use the space for your text.|||Thank you marcel,

I am planning to implement search functionality for my website. So choosing which datatype for search keywords will be optimum for me ? I need to store large number of data in this filed. I planned for text type. Is it a good choice ? Will text type utilize more space than any other datatype ? How much big my SQL database can grow ?|||You might was to look into using Full Text Search. Search in Book On Line for this topic.

Thanks,|||

Hello!

Moving forward you should NOT USE TEXT data types. TEXT has been obsoleted. VARCHAR(MAX) is the replacement.

Charles Hyman

Senior Consultant

MCTS SQL Server 2005

MCTS Biztalk Server

MCTS Vista Config

MCITP Database Administration

MCITP Database Developer

TALLAN Inc.

www.tallan.com

|||Thank you,

But upto my knowledge I think varchar can store only 8000 characters ? In new versions is it increased ?|||varchar(max) can store as much information as text. (so upto 2GB)

Thanks,

Column space

Is there a command so that I can enter in my query to make a 1 space before
my results. The reason is that I'm currenlty running a report that gives me
patients demographics in a .CSV file and I used DTS to automate the process
the last information on each patient is SS# this file will then need to be
imported to to another vendor application. The problem is that the vendor
found a bug on their application that cuts the first digit on the SSN and in
order for them to fix their app. it will take months and I can't wait this
long. So what i'm trying to do is move the results on the last field (SSN#)
one space to the right so that the results on the .CSV file will be
,*111-11-1111
* blank space
Is this possible without manually editing the .CSV file?
Thanks,
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> Is there a command so that I can enter in my query to make a 1 space
> before
> my results. The reason is that I'm currenlty running a report that gives
> me
> patients demographics in a .CSV file and I used DTS to automate the
> process
> the last information on each patient is SS# this file will then need to be
> imported to to another vendor application. The problem is that the vendor
> found a bug on their application that cuts the first digit on the SSN and
> in
> order for them to fix their app. it will take months and I can't wait this
> long. So what i'm trying to do is move the results on the last field
> (SSN#)
> one space to the right so that the results on the .CSV file will be
> ,*111-11-1111
> * blank space
> Is this possible without manually editing the .CSV file?
> Thanks,
There are a couple of ways of handling this.
1. Does it have to be a space? I would suggest some other identifying
mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
won't erase that space.
2. In the query itself, you can do something like this:
SELECT col1, col2, '*' + SSNColumn
FROM tablename
3. If that is undesirable, then you can always create a view with the above
SELECT statement and then run DTS against the view rather than the base
table.
Rick Sawtell
MCT, MCSD, MCDBA
|||So if I want to try this without the asteriks (*) or a tilde(~). I would
type my query as: SELECT col1, col2, + SSNColumn
FROM tablename
Or: SELECT col1, col2, '' + SSNColumn
FROM tablename
"Rick Sawtell" wrote:

> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> There are a couple of ways of handling this.
> 1. Does it have to be a space? I would suggest some other identifying
> mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
> won't erase that space.
> 2. In the query itself, you can do something like this:
> SELECT col1, col2, '*' + SSNColumn
> FROM tablename
> 3. If that is undesirable, then you can always create a view with the above
> SELECT statement and then run DTS against the view rather than the base
> table.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
|||Thanks Rick Sawtell,
The Second option worked perfectly.
SELECT col1, col2, ' ' + SSNColumn
FROM tablename
"Carlos Santos" wrote:
[vbcol=seagreen]
> So if I want to try this without the asteriks (*) or a tilde(~). I would
> type my query as: SELECT col1, col2, + SSNColumn
> FROM tablename
> Or: SELECT col1, col2, '' + SSNColumn
> FROM tablename
>
>
> "Rick Sawtell" wrote:
|||Carlos Santos wrote:
> Thanks Rick Sawtell,
> The Second option worked perfectly.
> SELECT col1, col2, ' ' + SSNColumn
> FROM tablename
> "Carlos Santos" wrote:
>
You may want to use SPACE(1) instead so it's clear in the proc what's
prefixing the string. You could easily add a second space and may not
realize it from the code. Some performance testing may be in order, but
I wouldn't expect that using the SPACE function would incurr much
overhead.
David Gugick
Imceda Software
www.imceda.com

Column space

Is there a command so that I can enter in my query to make a 1 space before
my results. The reason is that I'm currenlty running a report that gives me
patients demographics in a .CSV file and I used DTS to automate the process
the last information on each patient is SS# this file will then need to be
imported to to another vendor application. The problem is that the vendor
found a bug on their application that cuts the first digit on the SSN and in
order for them to fix their app. it will take months and I can't wait this
long. So what i'm trying to do is move the results on the last field (SSN#)
one space to the right so that the results on the .CSV file will be
,*111-11-1111
* blank space
Is this possible without manually editing the .CSV file?
Thanks,"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> Is there a command so that I can enter in my query to make a 1 space
> before
> my results. The reason is that I'm currenlty running a report that gives
> me
> patients demographics in a .CSV file and I used DTS to automate the
> process
> the last information on each patient is SS# this file will then need to be
> imported to to another vendor application. The problem is that the vendor
> found a bug on their application that cuts the first digit on the SSN and
> in
> order for them to fix their app. it will take months and I can't wait this
> long. So what i'm trying to do is move the results on the last field
> (SSN#)
> one space to the right so that the results on the .CSV file will be
> ,*111-11-1111
> * blank space
> Is this possible without manually editing the .CSV file?
> Thanks,
There are a couple of ways of handling this.
1. Does it have to be a space? I would suggest some other identifying
mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
won't erase that space.
2. In the query itself, you can do something like this:
SELECT col1, col2, '*' + SSNColumn
FROM tablename
3. If that is undesirable, then you can always create a view with the above
SELECT statement and then run DTS against the view rather than the base
table.
Rick Sawtell
MCT, MCSD, MCDBA|||So if I want to try this without the asteriks (*) or a tilde(~). I would
type my query as: SELECT col1, col2, + SSNColumn
FROM tablename
Or: SELECT col1, col2, '' + SSNColumn
FROM tablename
"Rick Sawtell" wrote:
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> > Is there a command so that I can enter in my query to make a 1 space
> > before
> > my results. The reason is that I'm currenlty running a report that gives
> > me
> > patients demographics in a .CSV file and I used DTS to automate the
> > process
> > the last information on each patient is SS# this file will then need to be
> > imported to to another vendor application. The problem is that the vendor
> > found a bug on their application that cuts the first digit on the SSN and
> > in
> > order for them to fix their app. it will take months and I can't wait this
> > long. So what i'm trying to do is move the results on the last field
> > (SSN#)
> > one space to the right so that the results on the .CSV file will be
> > ,*111-11-1111
> > * blank space
> >
> > Is this possible without manually editing the .CSV file?
> >
> > Thanks,
> There are a couple of ways of handling this.
> 1. Does it have to be a space? I would suggest some other identifying
> mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
> won't erase that space.
> 2. In the query itself, you can do something like this:
> SELECT col1, col2, '*' + SSNColumn
> FROM tablename
> 3. If that is undesirable, then you can always create a view with the above
> SELECT statement and then run DTS against the view rather than the base
> table.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>|||Thanks Rick Sawtell,
The Second option worked perfectly.
SELECT col1, col2, ' ' + SSNColumn
FROM tablename
"Carlos Santos" wrote:
> So if I want to try this without the asteriks (*) or a tilde(~). I would
> type my query as: SELECT col1, col2, + SSNColumn
> FROM tablename
> Or: SELECT col1, col2, '' + SSNColumn
> FROM tablename
>
>
> "Rick Sawtell" wrote:
> >
> > "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> > news:29A4E586-F079-4471-8AB4-DF58E0D5E4E9@.microsoft.com...
> > > Is there a command so that I can enter in my query to make a 1 space
> > > before
> > > my results. The reason is that I'm currenlty running a report that gives
> > > me
> > > patients demographics in a .CSV file and I used DTS to automate the
> > > process
> > > the last information on each patient is SS# this file will then need to be
> > > imported to to another vendor application. The problem is that the vendor
> > > found a bug on their application that cuts the first digit on the SSN and
> > > in
> > > order for them to fix their app. it will take months and I can't wait this
> > > long. So what i'm trying to do is move the results on the last field
> > > (SSN#)
> > > one space to the right so that the results on the .CSV file will be
> > > ,*111-11-1111
> > > * blank space
> > >
> > > Is this possible without manually editing the .CSV file?
> > >
> > > Thanks,
> >
> > There are a couple of ways of handling this.
> >
> > 1. Does it have to be a space? I would suggest some other identifying
> > mark like an asteriks (*) or a tilde(~). That way any TRIM type functions
> > won't erase that space.
> >
> > 2. In the query itself, you can do something like this:
> > SELECT col1, col2, '*' + SSNColumn
> > FROM tablename
> >
> > 3. If that is undesirable, then you can always create a view with the above
> > SELECT statement and then run DTS against the view rather than the base
> > table.
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
> >|||Carlos Santos wrote:
> Thanks Rick Sawtell,
> The Second option worked perfectly.
> SELECT col1, col2, ' ' + SSNColumn
> FROM tablename
> "Carlos Santos" wrote:
>
You may want to use SPACE(1) instead so it's clear in the proc what's
prefixing the string. You could easily add a second space and may not
realize it from the code. Some performance testing may be in order, but
I wouldn't expect that using the SPACE function would incurr much
overhead.
--
David Gugick
Imceda Software
www.imceda.com

Column Group Row Alignment - Matrix

I just can not understand why when I add text to a group header the report displays more group row space but when I export to excel the extra space disappears....

What the....Help Microsoft please explain...

Thank You...

Any help on this would be great...

Please

Column Delimiter as a SPACE?

I'm in a flat file connection manager editor and I have a flat file where the columns are separated by a space. Does anyone know how to specify a space in the column delimiter option? I've tried {' '} and {\s} but these don't work. Not sure what the syntax is for indicating a space. Thanks in advanceJust hit the space bar in the column delimiter box. Make sure that the first row is delimited by a space (even if it's a header).

Column Change on Large Table (Revised)

Hello,
I have to change a datatype (int to bigint) for a column in a table with
over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!
Here are two ideas. Both should be preceded by a full backup IMHO.
Change your recovery mode to simple, add a new nullable BIGINT column, then
run an UPDATE in a loop, truncating the log each iteration.
SET ROWCOUNT 10000;
SELECT 'starting...';
WHILE @.@.ROWCOUNT > 1
BEGIN
UPDATE table SET BigIntColumn = IntColumn WHERE BigIntColumn IS NULL;
END
SELECT '...finished';
Then you can drop the old column (you will need to drop
constraints/indexes/schemabound views/functions etc. first) and rename the
new one.
ALTER TABLE table DROP COLUMN IntColumn;
EXEC sp_rename 'table.BigIntColumn', 'IntColumn', 'COLUMN';
To be safe if you have any views that point I would DROP/CREATE or run
sp_refreshview. You didn't say what version of SQL Server you were using...
there may be other factors / consequences...
If you can take the table offline for an extended amount of time, you could
build an almost identical table (the int column changed to bigint) on
another system (which does have the room to duplicate the table), then copy
the data over to the new table, drop the existing table, create the same
table (with int changed to bigint) and copy the data back (there are wizards
and/or DTS/SSIS for this task).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16AE2454-32A0-46B6-A0DB-01580391562D@.microsoft.com...
> Hello,
> I have to change a datatype (int to bigint) for a column in a table with
> over 10million rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>

Column Change on Large Table (Revised)

Hello,
I have to change a datatype (int to bigint) for a column in a table with
over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!Here are two ideas. Both should be preceded by a full backup IMHO.
Change your recovery mode to simple, add a new nullable BIGINT column, then
run an UPDATE in a loop, truncating the log each iteration.
SET ROWCOUNT 10000;
SELECT 'starting...';
WHILE @.@.ROWCOUNT > 1
BEGIN
UPDATE table SET BigIntColumn = IntColumn WHERE BigIntColumn IS NULL;
END
SELECT '...finished';
Then you can drop the old column (you will need to drop
constraints/indexes/schemabound views/functions etc. first) and rename the
new one.
ALTER TABLE table DROP COLUMN IntColumn;
EXEC sp_rename 'table.BigIntColumn', 'IntColumn', 'COLUMN';
To be safe if you have any views that point I would DROP/CREATE or run
sp_refreshview. You didn't say what version of SQL Server you were using...
there may be other factors / consequences...
If you can take the table offline for an extended amount of time, you could
build an almost identical table (the int column changed to bigint) on
another system (which does have the room to duplicate the table), then copy
the data over to the new table, drop the existing table, create the same
table (with int changed to bigint) and copy the data back (there are wizards
and/or DTS/SSIS for this task).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:16AE2454-32A0-46B6-A0DB-01580391562D@.microsoft.com...
> Hello,
> I have to change a datatype (int to bigint) for a column in a table with
> over 10million rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>

Column Change on Large Table

Hello,
I have to change a datatype for a column in a table with over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!
From what data type? To which data type?
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B760FA66-130F-43F6-B840-C9233AD09F6E@.microsoft.com...
> Hello,
> I have to change a datatype for a column in a table with over 10million
> rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>
|||From int to bigint.
"Aaron Bertrand [SQL Server MVP]" wrote:

> From what data type? To which data type?
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:B760FA66-130F-43F6-B840-C9233AD09F6E@.microsoft.com...
>
>

Column Change on Large Table

Hello,
I have to change a datatype for a column in a table with over 10million rows
and don't have a lot of log space to deal with.
What's the best method for achieving this task with the least amount of
logging?
Any help appreciated.
Thanks in advance!From what data type? To which data type?
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B760FA66-130F-43F6-B840-C9233AD09F6E@.microsoft.com...
> Hello,
> I have to change a datatype for a column in a table with over 10million
> rows
> and don't have a lot of log space to deal with.
> What's the best method for achieving this task with the least amount of
> logging?
> Any help appreciated.
> Thanks in advance!
>|||From int to bigint.
"Aaron Bertrand [SQL Server MVP]" wrote:
> From what data type? To which data type?
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:B760FA66-130F-43F6-B840-C9233AD09F6E@.microsoft.com...
> > Hello,
> >
> > I have to change a datatype for a column in a table with over 10million
> > rows
> > and don't have a lot of log space to deal with.
> >
> > What's the best method for achieving this task with the least amount of
> > logging?
> >
> > Any help appreciated.
> > Thanks in advance!
> >
>
>