May I create not unique clustered index?

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

Maximum value for Unique ID

Hi,
Am a newbie to sql server so apologies if this question seems a bit
simple but here goes anyway. All our tables are defined with a unique
id column with the identity attribute. What is the maximum value for
this identity column and what happens if this maximum value is reached?
Many thanks in advance
Lee.It depends what type the column was declared as. IDENTITY is a property,
not a type. There is an underlying column type - it could be an integer
type (tinyint, smallint, int, bigint) or a decimal type with scale 0
(decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
of the declared type, you will get an overflow error.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...

quote:

---

> Hi,
> Am a newbie to sql server so apologies if this question seems a bit
> simple but here goes anyway. All our tables are defined with a unique
> id column with the identity attribute. What is the maximum value for
> this identity column and what happens if this maximum value is reached?
>
> Many thanks in advance
> Lee.
>

---

|||Many thanks for the response steve.
Just to make sure I understand then, if our column that has the identity
attribute is declared as int and the value tries to go above
2,147,483,647 an overflow error will be returned, rather than sql going
back through using any gaps.
Many thanks
Lee.
Steve Kass wrote:

quote:

---

> It depends what type the column was declared as. IDENTITY is a property,
> not a type. There is an underlying column type - it could be an integer
> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
> of the declared type, you will get an overflow error.
> SK
>
> "monkey" <monkey@.email.com> wrote in message
> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>
>
>

---

|||correct - if you want to see it in action, use dbcc checkident and reseed
with the max value. Note, however, that you can use negative values (in
case you start with 1 as the original seed)
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...

quote:

---

> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>
property,[QUOTE]
range[QUOTE]
>

---

|||Yes. The identity property simply generates sequential values. Whether a
value generated ends up in the table or not is irrelevant, as is the actual
data in the table. The identity property does guarantee uniqueness or that
there will be no gaps.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...

quote:

---

> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>
property,[QUOTE]
range[QUOTE]
>

---

|||Many thanks for all the responses, with your help I think I have a
pretty good understanding now.
Thanks again
lee.
monkey wrote:

quote:

---

> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>
>

---

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?
Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

Maximum number of columns exception

Folks, I'm having trouble with one of my DB's (on SQL server 2005).
I can't seem to be able to add a new column due to the following:
System.Data.SqlClient.SqlException: CREATE TABLE failed because column
'_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
columns.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
The offending call looks like:
ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
But this also doesn't work just using the Management Studio UI.
The mystery to me is that the table doesn't have anywhere close to 1024
columns. It has 22.
Any ideas?Eloy
Can you post your .NET code + table's structure to be tested?
"Eloy Perez" <EloyPerez@.discussions.microsoft.com> wrote in message
news:CA48A7AA-6FE0-45F0-A3FD-AF366E852428@.microsoft.com...
> Folks, I'm having trouble with one of my DB's (on SQL server 2005).
> I can't seem to be able to add a new column due to the following:
> System.Data.SqlClient.SqlException: CREATE TABLE failed because column
> '_Test1' in table 'TestAccountsProperties' exceeds the maximum of 1024
> columns.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> The offending call looks like:
> ALTER TABLE [TestAccountsProperties] ADD [_Test1] VARCHAR(128);
> But this also doesn't work just using the Management Studio UI.
> The mystery to me is that the table doesn't have anywhere close to 1024
> columns. It has 22.
> Any ideas?

maximum column_name length

i searched but could not find
please help
what is the maximum characters allowed for a column name?In SQL Server, all database object names (including columns) are called
identifiers. See the Books Online topic "Identifiers" in which it states the
following:
"Both regular and delimited identifiers must contain from 1 through 128
characters. For local temporary tables, the identifier can have a maximum of
116 characters."
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:u5jqCed%23FHA.2384@.TK2MSFTNGP15.phx.gbl...
>i searched but could not find
> please help
> what is the maximum characters allowed for a column name?
>|||ty for your answer|||Of course, any column name > 30 characters is assumed to be the result of a
stuck keyboard key.
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:uk2yUyd%23FHA.2300@.TK2MSFTNGP10.phx.gbl...
> ty for your answer
>

maximum characters per column

Hello:
How do you set the maximum characters per column in SQL
Server 6.5? Also, how do you specify this using T-SQL in
7.0 and 2000? Thanks.
Hi,
Did you mean the display then:-
For SQL 2000
default Query analyzer can display only 256 characters. This can be changed
by giving a higher value , say 2500.
Query analyzer -> Tools -> Options -> Results -> Maximum Characters Per
Column -> click OK
FOR SQL 6.5
There is no restriction on display, because in SQL 6.5 a varchar/char data
type can support a maximum of 255 bytes storage.
How do you set the maximum characters per column in SQL
Server 6.5?
Also, how do you specify this using T-SQL in 7.0 and 2000?
If you meant the storage per column, then it depends up on the data types.
See datatypes in books online
Thanks
Hari
MCDBA
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> Hello:
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
|||> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
For a table that doesn't yet exist:
CREATE TABLE dbo.foo
(
bar1 VARCHAR(32), -- 32 characters
bar2 VARCHAR(64), -- 64 characters
bar3 VARCHAR(8000) -- 8000 characters
)
To change one,
ALTER TABLE dbo.foo
ALTER COLUMN bar1 VARCHAR(256)
For 6.5, I believe you had to drop the table and re-create it (no ALTER).
Also, you had more limitations (e.g. VARCHAR only goes up to 255).
http://www.aspfaq.com/
(Reverse address to reply.)
|||"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
You can use the CREATE TABLE or ALTER TABLE statement to define the maximum
characters stored per column. If you are talking about limiting the maximum
character length to something less than the column width... In SQL Server
6.5 you could use a trigger, in SQL Server 7/2000 you could use a trigger or
a constraint.
Steve
|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>Hi,
>Did you mean the display then:-
>For SQL 2000
>default Query analyzer can display only 256 characters.
This can be changed
>by giving a higher value , say 2500.
>Query analyzer -> Tools -> Options -> Results -> Maximum
Characters Per
>Column -> click OK
>
>FOR SQL 6.5
>There is no restriction on display, because in SQL 6.5 a
varchar/char data
>type can support a maximum of 255 bytes storage.
>
>How do you set the maximum characters per column in SQL
>Server 6.5?
>Also, how do you specify this using T-SQL in 7.0 and
2000?
>If you meant the storage per column, then it depends up
on the data types.
>See datatypes in books online
>Thanks
>Hari
>MCDBA
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>
>.
>
|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>For a table that doesn't yet exist:
>CREATE TABLE dbo.foo
>(
> bar1 VARCHAR(32), -- 32 characters
> bar2 VARCHAR(64), -- 64 characters
> bar3 VARCHAR(8000) -- 8000 characters
>)
>To change one,
>ALTER TABLE dbo.foo
> ALTER COLUMN bar1 VARCHAR(256)
>For 6.5, I believe you had to drop the table and re-
create it (no ALTER).
>Also, you had more limitations (e.g. VARCHAR only goes up
to 255).
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>
|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>You can use the CREATE TABLE or ALTER TABLE statement to
define the maximum
>characters stored per column. If you are talking about
limiting the maximum
>character length to something less than the column
width... In SQL Server
>6.5 you could use a trigger, in SQL Server 7/2000 you
could use a trigger or
>a constraint.
>Steve
>
>.
>
|||> I was wondering if there is an
> equivalent option under 6.5.
Not that I can remember...

> And also, its T-SQL
> equivalent in setting this option using T-SQL.
There is none. You can always use TRIM() in your queries, of course...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...[vbcol=seagreen]
> Sorry, I should have been more articulate.
> I am using QA and would like to limit the # of characters
> displayed for each field/column.
> In 7 & 2K, I can set this option under
> Tools/Options/Results tab. I was wondering if there is an
> equivalent option under 6.5. And also, its T-SQL
> equivalent in setting this option using T-SQL.
> Thanks.
> message
> define the maximum
> limiting the maximum
> width... In SQL Server
> could use a trigger or
|||Hi Tiber / Rob,
There is no such option in SQL 6.5. But for your information in SQL 6.5
varchar/ char datatypes can
accoodate only 255 characters. So this option may not be required for
varchar columns. But for text data type
this will show only 255 characters.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUR5Hy2UEHA.3596@.tk2msftngp13.phx.gbl...
> Not that I can remember...
>
> There is none. You can always use TRIM() in your queries, of course...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...
>

maximum characters per column

Hello:
How do you set the maximum characters per column in SQL
Server 6.5? Also, how do you specify this using T-SQL in
7.0 and 2000? Thanks.Hi,
Did you mean the display then:-
For SQL 2000
default Query analyzer can display only 256 characters. This can be changed
by giving a higher value , say 2500.
Query analyzer -> Tools -> Options -> Results -> Maximum Characters Per
Column -> click OK
FOR SQL 6.5
There is no restriction on display, because in SQL 6.5 a varchar/char data
type can support a maximum of 255 bytes storage.
How do you set the maximum characters per column in SQL
Server 6.5?
Also, how do you specify this using T-SQL in 7.0 and 2000?
If you meant the storage per column, then it depends up on the data types.
See datatypes in books online
Thanks
Hari
MCDBA
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> Hello:
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.|||> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
For a table that doesn't yet exist:
CREATE TABLE dbo.foo
(
bar1 VARCHAR(32), -- 32 characters
bar2 VARCHAR(64), -- 64 characters
bar3 VARCHAR(8000) -- 8000 characters
)
To change one,
ALTER TABLE dbo.foo
ALTER COLUMN bar1 VARCHAR(256)
For 6.5, I believe you had to drop the table and re-create it (no ALTER).
Also, you had more limitations (e.g. VARCHAR only goes up to 255).
--
http://www.aspfaq.com/
(Reverse address to reply.)|||"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
You can use the CREATE TABLE or ALTER TABLE statement to define the maximum
characters stored per column. If you are talking about limiting the maximum
character length to something less than the column width... In SQL Server
6.5 you could use a trigger, in SQL Server 7/2000 you could use a trigger or
a constraint.
Steve|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.
>--Original Message--
>Hi,
>Did you mean the display then:-
>For SQL 2000
>default Query analyzer can display only 256 characters.
This can be changed
>by giving a higher value , say 2500.
>Query analyzer -> Tools -> Options -> Results -> Maximum
Characters Per
>Column -> click OK
>
>FOR SQL 6.5
>There is no restriction on display, because in SQL 6.5 a
varchar/char data
>type can support a maximum of 255 bytes storage.
>
>How do you set the maximum characters per column in SQL
>Server 6.5?
>Also, how do you specify this using T-SQL in 7.0 and
2000?
>If you meant the storage per column, then it depends up
on the data types.
>See datatypes in books online
>Thanks
>Hari
>MCDBA
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>> Hello:
>> How do you set the maximum characters per column in SQL
>> Server 6.5? Also, how do you specify this using T-SQL in
>> 7.0 and 2000? Thanks.
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.
>--Original Message--
>> How do you set the maximum characters per column in SQL
>> Server 6.5? Also, how do you specify this using T-SQL in
>> 7.0 and 2000? Thanks.
>For a table that doesn't yet exist:
>CREATE TABLE dbo.foo
>(
> bar1 VARCHAR(32), -- 32 characters
> bar2 VARCHAR(64), -- 64 characters
> bar3 VARCHAR(8000) -- 8000 characters
>)
>To change one,
>ALTER TABLE dbo.foo
> ALTER COLUMN bar1 VARCHAR(256)
>For 6.5, I believe you had to drop the table and re-
create it (no ALTER).
>Also, you had more limitations (e.g. VARCHAR only goes up
to 255).
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.
>--Original Message--
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
>> How do you set the maximum characters per column in SQL
>> Server 6.5? Also, how do you specify this using T-SQL in
>> 7.0 and 2000? Thanks.
>You can use the CREATE TABLE or ALTER TABLE statement to
define the maximum
>characters stored per column. If you are talking about
limiting the maximum
>character length to something less than the column
width... In SQL Server
>6.5 you could use a trigger, in SQL Server 7/2000 you
could use a trigger or
>a constraint.
>Steve
>
>.
>|||> I was wondering if there is an
> equivalent option under 6.5.
Not that I can remember...
> And also, its T-SQL
> equivalent in setting this option using T-SQL.
There is none. You can always use TRIM() in your queries, of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...
> Sorry, I should have been more articulate.
> I am using QA and would like to limit the # of characters
> displayed for each field/column.
> In 7 & 2K, I can set this option under
> Tools/Options/Results tab. I was wondering if there is an
> equivalent option under 6.5. And also, its T-SQL
> equivalent in setting this option using T-SQL.
> Thanks.
> >--Original Message--
> >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> >> How do you set the maximum characters per column in SQL
> >> Server 6.5? Also, how do you specify this using T-SQL in
> >> 7.0 and 2000? Thanks.
> >
> >You can use the CREATE TABLE or ALTER TABLE statement to
> define the maximum
> >characters stored per column. If you are talking about
> limiting the maximum
> >character length to something less than the column
> width... In SQL Server
> >6.5 you could use a trigger, in SQL Server 7/2000 you
> could use a trigger or
> >a constraint.
> >
> >Steve
> >
> >
> >.
> >|||Hi Tiber / Rob,
There is no such option in SQL 6.5. But for your information in SQL 6.5
varchar/ char datatypes can
accoodate only 255 characters. So this option may not be required for
varchar columns. But for text data type
this will show only 255 characters.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUR5Hy2UEHA.3596@.tk2msftngp13.phx.gbl...
> > I was wondering if there is an
> > equivalent option under 6.5.
> Not that I can remember...
>
> > And also, its T-SQL
> > equivalent in setting this option using T-SQL.
> There is none. You can always use TRIM() in your queries, of course...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1c94401c4530f$7ec0b5b0$a601280a@.phx.gbl...
> > Sorry, I should have been more articulate.
> >
> > I am using QA and would like to limit the # of characters
> > displayed for each field/column.
> >
> > In 7 & 2K, I can set this option under
> > Tools/Options/Results tab. I was wondering if there is an
> > equivalent option under 6.5. And also, its T-SQL
> > equivalent in setting this option using T-SQL.
> >
> > Thanks.
> >
> > >--Original Message--
> > >"Rob" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:1cbf601c452fe$cd4e7b60$a101280a@.phx.gbl...
> > >> How do you set the maximum characters per column in SQL
> > >> Server 6.5? Also, how do you specify this using T-SQL in
> > >> 7.0 and 2000? Thanks.
> > >
> > >You can use the CREATE TABLE or ALTER TABLE statement to
> > define the maximum
> > >characters stored per column. If you are talking about
> > limiting the maximum
> > >character length to something less than the column
> > width... In SQL Server
> > >6.5 you could use a trigger, in SQL Server 7/2000 you
> > could use a trigger or
> > >a constraint.
> > >
> > >Steve
> > >
> > >
> > >.
> > >
>

maximum characters per column

Hello:
How do you set the maximum characters per column in SQL
Server 6.5? Also, how do you specify this using T-SQL in
7.0 and 2000? Thanks.Hi,
Did you mean the display then:-
For SQL 2000
default Query analyzer can display only 256 characters. This can be changed
by giving a higher value , say 2500.
Query analyzer -> Tools -> Options -> Results -> Maximum Characters Per
Column -> click OK
FOR SQL 6.5
There is no restriction on display, because in SQL 6.5 a varchar/char data
type can support a maximum of 255 bytes storage.
How do you set the maximum characters per column in SQL
Server 6.5?
Also, how do you specify this using T-SQL in 7.0 and 2000?
If you meant the storage per column, then it depends up on the data types.
See datatypes in books online
Thanks
Hari
MCDBA
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
> Hello:
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.|||> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
For a table that doesn't yet exist:
CREATE TABLE dbo.foo
(
bar1 VARCHAR(32), -- 32 characters
bar2 VARCHAR(64), -- 64 characters
bar3 VARCHAR(8000) -- 8000 characters
)
To change one,
ALTER TABLE dbo.foo
ALTER COLUMN bar1 VARCHAR(256)
For 6.5, I believe you had to drop the table and re-create it (no ALTER).
Also, you had more limitations (e.g. VARCHAR only goes up to 255).
http://www.aspfaq.com/
(Reverse address to reply.)|||"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
> How do you set the maximum characters per column in SQL
> Server 6.5? Also, how do you specify this using T-SQL in
> 7.0 and 2000? Thanks.
You can use the CREATE TABLE or ALTER TABLE statement to define the maximum
characters stored per column. If you are talking about limiting the maximum
character length to something less than the column width... In SQL Server
6.5 you could use a trigger, in SQL Server 7/2000 you could use a trigger or
a constraint.
Steve|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>Hi,
>Did you mean the display then:-
>For SQL 2000
>default Query analyzer can display only 256 characters.
This can be changed
>by giving a higher value , say 2500.
>Query analyzer -> Tools -> Options -> Results -> Maximum
Characters Per
>Column -> click OK
>
>FOR SQL 6.5
>There is no restriction on display, because in SQL 6.5 a
varchar/char data
>type can support a maximum of 255 bytes storage.
>
>How do you set the maximum characters per column in SQL
>Server 6.5?
>Also, how do you specify this using T-SQL in 7.0 and
2000?
>If you meant the storage per column, then it depends up
on the data types.
>See datatypes in books online
>Thanks
>Hari
>MCDBA
>
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>For a table that doesn't yet exist:
>CREATE TABLE dbo.foo
>(
> bar1 VARCHAR(32), -- 32 characters
> bar2 VARCHAR(64), -- 64 characters
> bar3 VARCHAR(8000) -- 8000 characters
> )
>To change one,
>ALTER TABLE dbo.foo
> ALTER COLUMN bar1 VARCHAR(256)
>For 6.5, I believe you had to drop the table and re-
create it (no ALTER).
>Also, you had more limitations (e.g. VARCHAR only goes up
to 255).
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>|||Sorry, I should have been more articulate.
I am using QA and would like to limit the # of characters
displayed for each field/column.
In 7 & 2K, I can set this option under
Tools/Options/Results tab. I was wondering if there is an
equivalent option under 6.5. And also, its T-SQL
equivalent in setting this option using T-SQL.
Thanks.

>--Original Message--
>"Rob" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1cbf601c452fe$cd4e7b60$a101280a@.phx
.gbl...
>You can use the CREATE TABLE or ALTER TABLE statement to
define the maximum
>characters stored per column. If you are talking about
limiting the maximum
>character length to something less than the column
width... In SQL Server
>6.5 you could use a trigger, in SQL Server 7/2000 you
could use a trigger or
>a constraint.
>Steve
>
>.
>|||> I was wondering if there is an
> equivalent option under 6.5.
Not that I can remember...

> And also, its T-SQL
> equivalent in setting this option using T-SQL.
There is none. You can always use TRIM() in your queries, of course...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rob" <anonymous@.discussions.microsoft.com> wrote in message news:1c94401c4530f$7ec0b5b0$a60
1280a@.phx.gbl...[vbcol=seagreen]
> Sorry, I should have been more articulate.
> I am using QA and would like to limit the # of characters
> displayed for each field/column.
> In 7 & 2K, I can set this option under
> Tools/Options/Results tab. I was wondering if there is an
> equivalent option under 6.5. And also, its T-SQL
> equivalent in setting this option using T-SQL.
> Thanks.
>
> message
> define the maximum
> limiting the maximum
> width... In SQL Server
> could use a trigger or|||Hi Tiber / Rob,
There is no such option in SQL 6.5. But for your information in SQL 6.5
varchar/ char datatypes can
accoodate only 255 characters. So this option may not be required for
varchar columns. But for text data type
this will show only 255 characters.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUR5Hy2UEHA.3596@.tk2msftngp13.phx.gbl...
> Not that I can remember...
>
> There is none. You can always use TRIM() in your queries, of course...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1c94401c4530f$7ec0b5b0$a601280a@.phx
.gbl...
>

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

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

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

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

giving you the result set

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

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

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

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

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

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

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

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

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

Max() across 2 tables in one query?

I have two tables (T1 and T2) in SQL 2000 DB, each with an Integer column "I
D".
I want to get the Max(ID) across both tables. I know I could declare
variable(s) and execute separate query for each table, but I would prefer to
keep it to one, simple query.
I tried: select Max(ID) from (select Max(ID) from T1 union select Max(ID)
from T2)
but that doesn't execute.You were close:
select Max(ID) from
(select Max(ID) from T1
union ALL
select Max(ID) from T2) as X
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
news:4D0B0A20-1076-440E-9429-A8EEC1BA0851@.microsoft.com...
I have two tables (T1 and T2) in SQL 2000 DB, each with an Integer column
"ID".
I want to get the Max(ID) across both tables. I know I could declare
variable(s) and execute separate query for each table, but I would prefer to
keep it to one, simple query.
I tried: select Max(ID) from (select Max(ID) from T1 union select Max(ID)
from T2)
but that doesn't execute.|||select Max(ID) from (select Max(ID) id from T1 union all select Max(ID)
id from T2) t|||Use Pubs
Select (Select Max(Au_ID) From Authors) As 'MaxAuthorsID',
(Select Max(Emp_ID) From Employee) As 'MaxEmployeeID'
HTH
Barry|||Oops! Mis-read the post - I apologise!
It is Friday after all... ;-)
Barry|||Tom,
I guess this too will give an error...
May be it should be
select Max(ID) from
(select Max(ID) as ID from T1
union ALL
select Max(ID) from T2) as X
"Tom Moreau" wrote:

> You were close:
> select Max(ID) from
> (select Max(ID) from T1
> union ALL
> select Max(ID) from T2) as X
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
> news:4D0B0A20-1076-440E-9429-A8EEC1BA0851@.microsoft.com...
> I have two tables (T1 and T2) in SQL 2000 DB, each with an Integer column
> "ID".
> I want to get the Max(ID) across both tables. I know I could declare
> variable(s) and execute separate query for each table, but I would prefer
to
> keep it to one, simple query.
> I tried: select Max(ID) from (select Max(ID) from T1 union select Max(ID)
> from T2)
> but that doesn't execute.
>
>|||Good catch.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:C73B532C-9096-4438-8FC1-44AE770861B6@.microsoft.com...
Tom,
I guess this too will give an error...
May be it should be
select Max(ID) from
(select Max(ID) as ID from T1
union ALL
select Max(ID) from T2) as X
"Tom Moreau" wrote:

> You were close:
> select Max(ID) from
> (select Max(ID) from T1
> union ALL
> select Max(ID) from T2) as X
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> "lmcphee" <lmcphee@.discussions.microsoft.com> wrote in message
> news:4D0B0A20-1076-440E-9429-A8EEC1BA0851@.microsoft.com...
> I have two tables (T1 and T2) in SQL 2000 DB, each with an Integer column
> "ID".
> I want to get the Max(ID) across both tables. I know I could declare
> variable(s) and execute separate query for each table, but I would prefer
> to
> keep it to one, simple query.
> I tried: select Max(ID) from (select Max(ID) from T1 union select Max(ID)
> from T2)
> but that doesn't execute.
>
>

max width for input fixed width column

Is there a maximum width for fixed width column?

I'm trying to read in a flat file (which, admittedly, has one very wide column), and it keeps breaking because of truncation when it tries to read in the file.

Any ideas?

Jim Work
What is the error exactly?|||Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "Column 1" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

Error: 0xC020902A at Data Flow Task, Flat File Source [1]: The "output column "Column 1" (157)" failed because truncation occurred, and the truncation row disposition on "output column "Column 1" (157)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC0202092 at Data Flow Task, Flat File Source [1]: An error occurred while processing file "C:\data\sampleDepositFile1.txt" on data row 1.

Does that help?

|||Yes, it does.

Sounds to me like there are characters in the data that don't have a match in the codepage you are working with. Could be a non-unicode/unicode issue. Are there NULLs in the data? NULLs truncate strings as well.

What codepage are you using? 1252? What kind of data do you have in the file?|||1252 (ANSI - Latin I )

This is what the connection manager defaulted to, and I believe it matches up with my source data. Should I change it to (and go back and save the source data as) UTF-8?

Thanks for any insight you can give me!
|||How big is your column in the source file, and what have you defined in the flat file source? Pull it up in the advanced editor and ensure that the columns are the correct size there.

Max width for indexed column?

Is column width a factor for an indexed column? Would indexing a
VARCHAR(100) be a bad idea? Any links to discussion of this topic?
Thanks for your advice.
- Joe Geretz -
'bad' is of course a relative term...
Yes, width of an index will affect performance. This is a LONG topic, but
here a a few thoughts...
* width of a clustered index can be more important to worry about than a NC
index since a clustered index key is propogated through ALL the NC indexex.
* yes, width does matter. Intuitively, it just makes sense. There is less
CPU and much less IO involved for a 10 byte index than a 100 byte index.
Would it be quicker for you to read a list of 10 letter words or 100 letter
sentences? Of course that's a silly example, but yes... narrow indexes will
be better all things considered.
* having a wide index isn't the end of hte world and probably isn't a big
deal with smaller data sets.
* if you DO need to index wide char based fields and you are worred about
performance... you may want to consider creating a checksum based on the
chat column and indexing the checsum col (which will be an integer). That
would be much faster...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:e7LEw0RiEHA.344@.TK2MSFTNGP10.phx.gbl...
> Is column width a factor for an indexed column? Would indexing a
> VARCHAR(100) be a bad idea? Any links to discussion of this topic?
> Thanks for your advice.
> - Joe Geretz -
>

Max width for indexed column?

Is column width a factor for an indexed column? Would indexing a
VARCHAR(100) be a bad idea? Any links to discussion of this topic?
Thanks for your advice.
- Joe Geretz -'bad' is of course a relative term...
Yes, width of an index will affect performance. This is a LONG topic, but
here a a few thoughts...
* width of a clustered index can be more important to worry about than a NC
index since a clustered index key is propogated through ALL the NC indexex.
* yes, width does matter. Intuitively, it just makes sense. There is less
CPU and much less IO involved for a 10 byte index than a 100 byte index.
Would it be quicker for you to read a list of 10 letter words or 100 letter
sentences? Of course that's a silly example, but yes... narrow indexes will
be better all things considered.
* having a wide index isn't the end of hte world and probably isn't a big
deal with smaller data sets.
* if you DO need to index wide char based fields and you are worred about
performance... you may want to consider creating a checksum based on the
chat column and indexing the checsum col (which will be an integer). That
would be much faster...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:e7LEw0RiEHA.344@.TK2MSFTNGP10.phx.gbl...
> Is column width a factor for an indexed column? Would indexing a
> VARCHAR(100) be a bad idea? Any links to discussion of this topic?
> Thanks for your advice.
> - Joe Geretz -
>

Max width for indexed column?

Is column width a factor for an indexed column? Would indexing a
VARCHAR(100) be a bad idea? Any links to discussion of this topic?
Thanks for your advice.
- Joe Geretz -'bad' is of course a relative term...
Yes, width of an index will affect performance. This is a LONG topic, but
here a a few thoughts...
* width of a clustered index can be more important to worry about than a NC
index since a clustered index key is propogated through ALL the NC indexex.
* yes, width does matter. Intuitively, it just makes sense. There is less
CPU and much less IO involved for a 10 byte index than a 100 byte index.
Would it be quicker for you to read a list of 10 letter words or 100 letter
sentences? Of course that's a silly example, but yes... narrow indexes will
be better all things considered.
* having a wide index isn't the end of hte world and probably isn't a big
deal with smaller data sets.
* if you DO need to index wide char based fields and you are worred about
performance... you may want to consider creating a checksum based on the
chat column and indexing the checsum col (which will be an integer). That
would be much faster...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:e7LEw0RiEHA.344@.TK2MSFTNGP10.phx.gbl...
> Is column width a factor for an indexed column? Would indexing a
> VARCHAR(100) be a bad idea? Any links to discussion of this topic?
> Thanks for your advice.
> - Joe Geretz -
>

max value of table column

Hi all,
Could anyboyd tell me how i can get the max value of a table column.
A column in my table contains numeric values. in my table footer i
want to display
the max value of that column.
my table itself has one group. This group is called GroupByHour and is
simply a group by hour for all 24 hours in a day (from 00h until 23h).
In the specific column of each line i calculate a value.
It is the max value of those calculated values that i want in the
footer of this column.
I don't know if i explained myself clearly enough, so here is an
example:
hour | ColA | ColB | ColC | ColD
---
00 15 14 16 16
01
02
03 20 23 21 23
...
23 75 16 55 75
----
90 51 114 75
In the tablefooter of ColA, ColB and ColC you have the sumed value for
the columns.
The footer of ColD displays the max value of ColD.
So, what expression or function do i have use to get the max value of
ColD in my table footer?
Greetings
VinnieWon't the "Max( Fields!ColD.Value )" work?
There is a Max for 2 numbers, and a max for column values. Look in 'Common
Functions -> Operators'
//Andrew
> Hi all,
> Could anyboyd tell me how i can get the max value of a table column.
> A column in my table contains numeric values. in my table footer i
> want to display
> the max value of that column.
> my table itself has one group. This group is called GroupByHour and is
> simply a group by hour for all 24 hours in a day (from 00h until 23h).
> In the specific column of each line i calculate a value.
> It is the max value of those calculated values that i want in the
> footer of this column.
> I don't know if i explained myself clearly enough, so here is an
> example:
> hour | ColA | ColB | ColC | ColD
> ---
> 00 15 14 16 16
> 01
> 02
> 03 20 23 21 23
> ...
> 23 75 16 55 75
> ----
> 90 51 114 75
> In the tablefooter of ColA, ColB and ColC you have the sumed value for
> the columns.
> The footer of ColD displays the max value of ColD.
> So, what expression or function do i have use to get the max value of
> ColD in my table footer?
> Greetings
> Vinni|||On Aug 8, 6:24 pm, Andrew Backer <awbac...@.gmail.com> wrote:
> Won't the "Max( Fields!ColD.Value )" work?
> There is a Max for 2 numbers, and a max for column values. Look in 'Common
> Functions -> Operators'
> //Andrew
>
> > Hi all,
> > Could anyboyd tell me how i can get the max value of a table column.
> > A column in my table contains numeric values. in my table footer i
> > want to display
> > the max value of that column.
> > my table itself has one group. This group is called GroupByHour and is
> > simply a group by hour for all 24 hours in a day (from 00h until 23h).
> > In the specific column of each line i calculate a value.
> > It is the max value of those calculated values that i want in the
> > footer of this column.
> > I don't know if i explained myself clearly enough, so here is an
> > example:
> > hour | ColA | ColB | ColC | ColD
> > ---
> > 00 15 14 16 16
> > 01
> > 02
> > 03 20 23 21 23
> > ...
> > 23 75 16 55 75
> > ----
> > 90 51 114 75
> > In the tablefooter of ColA, ColB and ColC you have the sumed value for
> > the columns.
> > The footer of ColD displays the max value of ColD.
> > So, what expression or function do i have use to get the max value of
> > ColD in my table footer?
> > Greetings
> > Vinnie- Hide quoted text -
> - Show quoted text -
Hi Andrew,
I am aware of the Max function, but i cannot specify the column.
ColD is just a title of the column. In fact the name for the column is
TableColumn15
But if i do =max(Fields!TableColumn15.value) i get an error returned :
"Error 1 [rsFieldReference] The Value expression for the textbox
'textbox168' refers to the
field 'TableColumn15'. Report item expressions can only refer to
fields within the current data set scope or,
if inside an aggregate, the specified data set scope."
Is there a way to say to RS that you want the max value of all values
in a specific column.
I cannot calculate this value in my footer because the group contains
24 max values (one for each hour),
and in the report footer i would have the max value of the total of
the group (which i don't need of course).
So .. anybody have some more info on this ?
Greetings
Vinnie

Max value of table column

hello,

i have a table with different columns

my table has 1 group (group per hour)

my last column of my table is a calculation of different values from that line.

in my footer of my column i want to display the max value of that column.

somehow i can't manage to achieve this.

this i an example of my table (how i want i to look like)

hour | ColA | ColB | ColC | ColD

00 15 14 16 16

01

02

03 20 21 23 23

....

23 55 16 75 75

90 51 114 75

ColD is the max value of ColA, ColB and ColC.

The footer contains the sumed values for A, B and C

In the footer for ColD i would like to display the max value of ColD (in this case 75).

This may be a stupid question, but i'm stuck on it ..

Vinnie

Here the query,

Code Snippet

Create Table #data (

[hour] Varchar(100) ,

[ColA] int ,

[ColB] int ,

[ColC] int ,

[ColD] int

);

Insert Into #data Values('00','15','14','16',NULL);

Insert Into #data Values('03','20','21','23',NULL);

Insert Into #data Values('23','55','16','75',NULL);

Select

hour,

max(case when colid=1 Then case when hour is not null then data1 else data2 end end) as [cola],

max(case when colid=2 Then case when hour is not null then data1 else data2 end end) as [colb],

max(case when colid=3 Then case when hour is not null then data1 else data2 end end) as [colc],

max(case when colid=4 Then data1 end) as [cold]

from

(

select

hour,

Isnull(id,4) colid,

Max(Case When id=1 Then cola

When id=2 Then colb

When id=3 Then colc End) data1,

Sum(Case When id=1 Then cola

When id=2 Then colb

When id=3 Then colc End) data2

from

#data

cross join

(select 1 id Union Select 2 Union Select 3) as d

Group By

hour,id

With Cube

) as Data

Group by

hour

Order By

isnull(hour,99999)

|||

Hi,

my problem is with a table in reporting services.

my data already comes from a dataset.

so my question is how to retreive the max value of a table column.

also, i work with reporting models and not with query based datasets.

but that has nothing to do with my question.

Greetings

Vinnie

|||

How much of this table have you implemented successfully? Everything but the last value at the bottom right?

Are you using report builder or Business Intelligence Studio?

|||

Hello Greg,

Thanks for the answer.

I am using visual studio to build my reports.

The table itself works fine. The only thing i cannot accomplish is to

have the max value of that specific table column in my footer.

Is what i'm asking impossible to do or am i that stupid that it's right under

my nose and cannot figure it out ?

any help is welcome.

Greetings

Vinnie

|||

VSempoux wrote:

Hello Greg,

Is what i'm asking impossible to do or am i that stupid that it's right under

my nose and cannot figure it out ?

Neither.

VSempoux wrote:

also, i work with reporting models and not with query based datasets

I don't understand this. So there is no query to generate your report? You said you weren't using report builder, so how could you be using reporting models?

We have to understand how you are going about this before we can help you. The only way I know of how to get that max value is to write it into a query....

|||

Okay Greg,

Let me clarify my situation.

I am writing reports for an application that is in dev for the moment.

We want to give our customers the opportunity to also create their own report, based on the data that

we use in our standard reports that are supplied with the app.

So, in order to don't have to do the work twice, i am making report models that i use in my reports.

With VS or BIS you can choose your original datasource, so in my case it is a report model.

My orignal report models are based on queries that i have put into views.

So in my report model data source view i select all the data from the sql view and from that i generate

my model. Afterwards i add some calculated fields in the model itself, if i have the need for them.

Then in the report itself i define datasets.

But for my datasets i can only do drag and drop the fields (enteties) i need in my report.

Of course i can define filters for the datasource.

Does this give you a better insight into my situation ?

If it would make your life easier, i could sent you some screenshots ...

Greetings

Vinnie

|||

VSempoux wrote:

My orignal report models are based on queries that i have put into views.

Then I suppose the answer is you need to create a field in the model with a query that selects the max of the dataset that produces ColA, ColB and ColC.

When I say dataset here, I am not talking about the SQL dataset. I am talking about the collection of data. It really makes no difference whether it comes from a dataset, a view, or a donkey for that matter.

hour | ColA | ColB | ColC | ColD

00 15 14 16 16

01

02

03 20 21 23 23

....

23 55 16 75 75

90 51 114 75

|||

Hi,

I already have such a field and it's doing what it's suposed to do.

But this field is set into the table group (remember the group that groups per hour)

So, when my table is displayed after generation, i have 24 lines with in the last column the max values

of several columns.

example :

this is how my table layout looks like

table group= hour(Fields!TimeValue.value) -> this groups all my data per hour

table group header contains 7 colums

column 1 : hour(Fields!TimeValue.value) sorted ascending

column 2-3-4-5-6 contains a calculation like this : (sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value))*100

this returns a ratio value

column 7 contains an expression that gives me max value of column 2-3-4-5. this is the expression i use :

=CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100))))))

This expression returns the highest ratio value for a specif hour.

At the end i have 24 values in column 7, each time the highest value per hour.

In my table footer i cannot use the expression above, because this will calculate the highest value for the total 24 hours (which is of course different from the highest single value per hour).

Do you see now what i want to do ?

Since i do various other calculations inside my report with fields like Fields!CountTest1.value, .... i cannot do this (i think) in my sql view.

Fields!CountTest1 is also a calculated field within my dataset (=iif(Fields!TestState.Value=101 and Fields!ContactTotalTest.Value=1,1,0))

Soooooo, the only simple thing i want ... the max value, of the generated max values per hour, in my table footer !

I don't know how i can make this more clear.

Greetings

Vinnie

|||See this post:

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

Ian