nvarchar and output to flat file.

Hello,
Im outputting a sql table to a text file. One of the columns in this table
is an nvarchar(1200) but the result in the flat file is truncated to 254
chars....how do ensure that it outputs all chars up to 1200?
thxJohn,
which approach / utility are you using to outputting the sql table to a text
file?
AMB
"John Smith" wrote:

> Hello,
> Im outputting a sql table to a text file. One of the columns in this table
> is an nvarchar(1200) but the result in the flat file is truncated to 254
> chars....how do ensure that it outputs all chars up to 1200?
> thx
>
>|||I think I gave him a suitable answer on a separate post.
Barrysql

Numeric attribute keys

Does it make sense to favor numeric columns for the attribute keys (e.g. 1,2,3, etc. for the month attribute) as opposed to names (e.g. January, February). I've noticed that the sample AdventureWorks UDM uses both approaches so I am not sure which one is best from a performance standpoint. In AdventureWorks, numeric columns are typically used for snowflake dimensions while name columns are used in star schemas (except Date dimension which uses numeric columns).

The best is to use numeric keys for your dimension attributes whenever is possible.

For one, it speeds up processing operations , especially having large dimensions. It helps Analysis Server to build optimized dimension stuctures that should lead to better query performance.

It is good practice in general to use numeric ( integer ) keys for the attributes.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you, Edward.

Is there any recommended member count threshold, above which integer keys should be considered? Obviously, having a separate integer key column for each attribute hierarchy could be counterproductive.

|||

Based on my observations. And I could be wrong :)

I wouldnt bother with numeric keys if you talking about few tens thousands members. With hundreds of thousands this could be a problem.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

sql

numbering each row

I'm sure this has been asked a bunch of times, but I can't find samples that
apply to many groups and columns. I also found ROW_NUMBER() function for SQL
2005. Unfortunately, I have SQL 2000. I realize this should be done in the
display of data but I have to get it done in a table. I believe RANK is what
I need, but didn't get it working. It would return a 1 for each row. I've
also considered using temp tables and an ID column, but that involves a LOT
of temp tables.
I need to add a row numbering column to the below results.
It must be grouped on the s.OrderNo field.
How do I get that to work?
SELECT s.OrderNo, t.Title, t.Artist, t.Location,
t.SortKey, t.MP3Files, t.OrderNo AS Disc_ID
FROM ItemStock s INNER JOIN
ItemPackCompilations p ON s.OrderNo = p.PackOrderNo INNER JOIN
ItemTitles t ON p.IncludeOrderNo = t.OrderNo
WHERE (s.Packed = 'Comp')
ORDER BY s.OrderNo, t.Title
thanks!> I'm sure this has been asked a bunch of times, but I can't find samples
> that apply to many groups and columns. I also found ROW_NUMBER() function
> for SQL 2005. Unfortunately, I have SQL 2000.
http://www.aspfaq.com/2427

> I realize this should be done in the display of data but I have to get it
> done in a table.
Why? Does a business requirement somehow state that the data must be
displayed directly from a table? Why?

> I believe RANK is what I need, but didn't get it working.
Well, this should fail for the same reason ROW_NUMBER() won't work for you.
RANK is SQL 2005+.

> SELECT s.OrderNo, t.Title, t.Artist, t.Location,
> t.SortKey, t.MP3Files, t.OrderNo AS Disc_ID
> FROM ItemStock s INNER JOIN
> ItemPackCompilations p ON s.OrderNo = p.PackOrderNo INNER JOIN
> ItemTitles t ON p.IncludeOrderNo = t.OrderNo
> WHERE (s.Packed = 'Comp')
> ORDER BY s.OrderNo, t.Title
I don't know what this is. Please see http://www.aspfaq.com/5006sql

Number of pages displayed and printed do not match

I have a report in landscape layout because I have many columns to display.
When I display this report in browser, I see there are 2 pages for this
report. But when I click the print button on page to print it, I got 4 pages
because it's printed in landscape format on paper. Now QA logged this as an
error. Does anybody have any way to make displayed and printed number of
pages match to each other?
Thanks a lot.You need to make sure that Report width + Margin width isn't more than the
given width for landscape. (29 cm or 11 inches.) If they are more than this,
you will get an extra page, and this might be empty. Sometimes an extra page
is added, but there's nothing on it, because your actual table or matrix
isn't that wide.
Kaisa M. Lindahl
"BF" <BF@.discussions.microsoft.com> wrote in message
news:DA474C38-84E0-40EF-947C-C5D61E1BA40C@.microsoft.com...
>I have a report in landscape layout because I have many columns to display.
> When I display this report in browser, I see there are 2 pages for this
> report. But when I click the print button on page to print it, I got 4
> pages
> because it's printed in landscape format on paper. Now QA logged this as
> an
> error. Does anybody have any way to make displayed and printed number of
> pages match to each other?
> Thanks a lot.|||Yes, I am very sure that my report content is not wider than the landscape
format. I spent lots of time adjusting this report. There are no empty pages
when printing.
The problem is when I print this report, I got landscape format printed 4
pages in correct format. But when I display this report within browser, the
number of lines on a browser page is more than the number of lines on the
landscape printed page. As a result, on the browser, I only have 2 pages. But
on the paper, I have 4 pages.
Any other suggestions.
Thanks.
"Kaisa M. Lindahl" wrote:
> You need to make sure that Report width + Margin width isn't more than the
> given width for landscape. (29 cm or 11 inches.) If they are more than this,
> you will get an extra page, and this might be empty. Sometimes an extra page
> is added, but there's nothing on it, because your actual table or matrix
> isn't that wide.
> Kaisa M. Lindahl
>
> "BF" <BF@.discussions.microsoft.com> wrote in message
> news:DA474C38-84E0-40EF-947C-C5D61E1BA40C@.microsoft.com...
> >I have a report in landscape layout because I have many columns to display.
> > When I display this report in browser, I see there are 2 pages for this
> > report. But when I click the print button on page to print it, I got 4
> > pages
> > because it's printed in landscape format on paper. Now QA logged this as
> > an
> > error. Does anybody have any way to make displayed and printed number of
> > pages match to each other?
> >
> > Thanks a lot.
>
>

number of columns in table

How can I determine the number of columns in a table?join syscolumns and sysobjects tables to count the number of columns.|||or use INFROMATION_SCHEMA.COLUMNS|||select * from syscolumns where id = object_id('<table name>')

Number of columns for all tables in a database

Hi - I apologize in advance for the newbie question...
I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.
I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)
Is there a query that can output a grid listing the number of fields
per each table in a selected database?
If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.
I REALLY appreciate any help sent my way.
Cheers!
Joel
Hi Joel
No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.
There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.
exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'
If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)
Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)
Hopefully, this has given you a start...
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegr oups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>
|||Just do a GROUP BY and you are there:
SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
For number of rows, we need to know what version of SQL Server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegr oups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>
|||Thank you BOTH so much for your help.
I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.
We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...
Thanks!
Tibor Karaszi wrote:[vbcol=seagreen]
> Just do a GROUP BY and you are there:
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
> For number of rows, we need to know what version of SQL Server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
> news:1159539224.878544.293450@.i42g2000cwa.googlegr oups.com...
|||Does the row count need to be exact? If not, use the sysindexes system table, something like:
SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt
And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname') WITH COUNT_ROWS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159544274.480475.86270@.b28g2000cwb.googlegro ups.com...
> Thank you BOTH so much for your help.
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
> Thanks!
>
> Tibor Karaszi wrote:
>
|||Joel,
I think I am a little more bored than the rest of them.
--This procedure will loop through the database list and load a table
--in tempdb with the column count for each table.
USE tempdb
IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END
CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_namevarchar(200) NOT NULL
, col_cntint NULL )
DECLARE @.db_namevarchar(200)
DECLARE @.count as int
DECLARE @.cmdvarchar(2000)
SET @.cmd = ''
SET @.db_name = ''
DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null
OPEN dbs
FETCH NEXT FROM dbs into @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.db_name
SET@.cmd = 'USE [' + @.db_name + ']'
PRINT@.cmd
EXECUTE( @.cmd)
SET @.cmd = 'INSERT INTO tempdb..table_specs
SELECT''' + @.db_name + ''', a.name, count(*)
FROM[' + @.db_name + ']..sysobjects a, [' + @.db_name + ']..syscolumns
b
WHEREa.id= b.id
ANDa.type= ''U''
GROUP BY a.name'
EXECUTE (@.cmd)
PRINT @.cmd
FETCH NEXT FROM dbs into @.db_name
END
CLOSE dbs
DEALLOCATE dbs
-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs

Number of columns for all tables in a database

Hi - I apologize in advance for the newbie question...
I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.
I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)
Is there a query that can output a grid listing the number of fields
per each table in a selected database?
If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.
I REALLY appreciate any help sent my way.
Cheers!
JoelHi Joel
No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.
There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.
exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'
If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)
Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)
Hopefully, this has given you a start...
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Just do a GROUP BY and you are there:
SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
For number of rows, we need to know what version of SQL Server...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Thank you BOTH so much for your help.
I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.
We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...
Thanks!
Tibor Karaszi wrote:
> Just do a GROUP BY and you are there:
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
> For number of rows, we need to know what version of SQL Server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
> news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> > Hi - I apologize in advance for the newbie question...
> >
> > I have been asked to gather some db statistics. We have 11 dbs and
> > some have 70 or more tables.
> >
> > I need to gather is number of fields for each table in the database.
> > I've used this so far:
> > SELECT COUNT(*)
> > FROM INFORMATION_SCHEMA.COLUMNS
> > WHERE TABLE_NAME = 'pub_info'
> > and doing this for each table in each database. However, this is going
> > to take me three lifetimes. ;o)
> >
> > Is there a query that can output a grid listing the number of fields
> > per each table in a selected database?
> >
> > If the query could simultaneously list the number of rows as well that
> > would be a bonus, but not necessary.
> >
> > I REALLY appreciate any help sent my way.
> >
> > Cheers!
> > Joel
> >|||Does the row count need to be exact? If not, use the sysindexes system table, something like:
SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt
And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname') WITH COUNT_ROWS
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159544274.480475.86270@.b28g2000cwb.googlegroups.com...
> Thank you BOTH so much for your help.
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
> Thanks!
>
> Tibor Karaszi wrote:
>> Just do a GROUP BY and you are there:
>> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
>> FROM INFORMATION_SCHEMA.COLUMNS
>> GROUP BY TABLE_NAME
>> For number of rows, we need to know what version of SQL Server...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
>> news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
>> > Hi - I apologize in advance for the newbie question...
>> >
>> > I have been asked to gather some db statistics. We have 11 dbs and
>> > some have 70 or more tables.
>> >
>> > I need to gather is number of fields for each table in the database.
>> > I've used this so far:
>> > SELECT COUNT(*)
>> > FROM INFORMATION_SCHEMA.COLUMNS
>> > WHERE TABLE_NAME = 'pub_info'
>> > and doing this for each table in each database. However, this is going
>> > to take me three lifetimes. ;o)
>> >
>> > Is there a query that can output a grid listing the number of fields
>> > per each table in a selected database?
>> >
>> > If the query could simultaneously list the number of rows as well that
>> > would be a bonus, but not necessary.
>> >
>> > I REALLY appreciate any help sent my way.
>> >
>> > Cheers!
>> > Joel
>> >
>|||Joel,
I think I am a little more bored than the rest of them.
--This procedure will loop through the database list and load a table
-- in tempdb with the column count for each table.
USE tempdb
IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END
CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_name varchar(200) NOT NULL
, col_cnt int NULL )
DECLARE @.db_name varchar(200)
DECLARE @.count as int
DECLARE @.cmd varchar(2000)
SET @.cmd = ''
SET @.db_name = ''
DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null
OPEN dbs
FETCH NEXT FROM dbs into @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.db_name
SET @.cmd = 'USE [' + @.db_name + ']'
PRINT @.cmd
EXECUTE ( @.cmd)
SET @.cmd = 'INSERT INTO tempdb..table_specs
SELECT ''' + @.db_name + ''', a.name, count(*)
FROM [' + @.db_name + ']..sysobjects a, [' + @.db_name + ']..syscolumns
b
WHERE a.id = b.id
AND a.type = ''U''
GROUP BY a.name'
EXECUTE (@.cmd)
PRINT @.cmd
FETCH NEXT FROM dbs into @.db_name
END
CLOSE dbs
DEALLOCATE dbs
-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs

Number of columns for all tables in a database

Hi - I apologize in advance for the newbie question...
I have been asked to gather some db statistics. We have 11 dbs and
some have 70 or more tables.
I need to gather is number of fields for each table in the database.
I've used this so far:
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'pub_info'
and doing this for each table in each database. However, this is going
to take me three lifetimes. ;o)
Is there a query that can output a grid listing the number of fields
per each table in a selected database?
If the query could simultaneously list the number of rows as well that
would be a bonus, but not necessary.
I REALLY appreciate any help sent my way.
Cheers!
JoelHi Joel
No need to apologize, it's a perfectly valid question. The only thing
'newbie' is that you didn't tell us what version you were using.
I will assume SQL Server 2000.
There's no easy way to do this for all databases in a single query, but this
will give you all tables in one database:
SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
To get all databases, you would have to use an undocumented command that
basically 'loops' through all the databases. It will not give you a single
resultset however.
exec sp_MSforeachdb
'USE ?; SELECT TABLE_NAME, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME'
If you wanted all the output in a single table, you could change the command
to insert all the rows into a temp table, and then as a final step you could
select from the temp table. You might want to try doing that on your own as
a homework exercise. :-)
Also, listing the number of rows is a different problem. There is no where
that is stored in a guaranteed way, so you would have to go through and
count in each table. That would not be a single query. There is a system
table that contains an estimate of the number of rows. It is called
sysindexes, and you could join that to INFORMATION_SCHEMA.COLUMNS. But if I
were doing it, as long as I was having to go to the real system tables
anyway, I would skip the INFORMATION_SCHEME and use all system tables:
sysobjects, syscolumns and sysindexes. (But there would still be the problem
of getting all info from all databases together.)
Hopefully, this has given you a start...
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Just do a GROUP BY and you are there:
SELECT TABLE_NAME, COUNT(*) AS noOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
For number of rows, we need to know what version of SQL Server...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...
> Hi - I apologize in advance for the newbie question...
> I have been asked to gather some db statistics. We have 11 dbs and
> some have 70 or more tables.
> I need to gather is number of fields for each table in the database.
> I've used this so far:
> SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'pub_info'
> and doing this for each table in each database. However, this is going
> to take me three lifetimes. ;o)
> Is there a query that can output a grid listing the number of fields
> per each table in a selected database?
> If the query could simultaneously list the number of rows as well that
> would be a bonus, but not necessary.
> I REALLY appreciate any help sent my way.
> Cheers!
> Joel
>|||Thank you BOTH so much for your help.
I will take on the homework assignment suggested by Kalen as it is good
exercise for me as a novice.
We are using SQL Server 2000. -- You see! This is how much of a
newbie I am! I didn't even know it made a difference!! :o) If anyone
knows a quickie way to get the rows until I can get to my homework that
would be great. Just trying to get this information out the door to
mgmt for now...
Thanks!
Tibor Karaszi wrote:[vbcol=seagreen]
> Just do a GROUP BY and you are there:
> SELECT TABLE_NAME, COUNT(*) AS noOfColumns
> FROM INFORMATION_SCHEMA.COLUMNS
> GROUP BY TABLE_NAME
> For number of rows, we need to know what version of SQL Server...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "JoelBarish" <JoelForBoards@.gmail.com> wrote in message
> news:1159539224.878544.293450@.i42g2000cwa.googlegroups.com...|||Does the row count need to be exact? If not, use the sysindexes system table
, something like:
SELECT OBJECT_NAME(i.id) AS table_name, i.rowcnt, COUNT(*) AS NoOfColumns
FROM sysindexes AS i
JOIN syscolumns AS c
ON i.id = c.id
WHERE i.indid IN(0,1) AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
GROUP BY OBJECT_NAME(i.id), i.rowcnt
And before you run above, run (below should make rowcount exact):
DBCC UPDATEUSAGE('dbname') WITH COUNT_ROWS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JoelBarish" <JoelForBoards@.gmail.com> wrote in message
news:1159544274.480475.86270@.b28g2000cwb.googlegroups.com...
> Thank you BOTH so much for your help.
> I will take on the homework assignment suggested by Kalen as it is good
> exercise for me as a novice.
> We are using SQL Server 2000. -- You see! This is how much of a
> newbie I am! I didn't even know it made a difference!! :o) If anyone
> knows a quickie way to get the rows until I can get to my homework that
> would be great. Just trying to get this information out the door to
> mgmt for now...
> Thanks!
>
> Tibor Karaszi wrote:
>|||Joel,
I think I am a little more bored than the rest of them.
--This procedure will loop through the database list and load a table
-- in tempdb with the column count for each table.
USE tempdb
IF EXISTS ( SELECT name from sysobjects where name = 'table_specs' )
BEGIN
DROP TABLE table_specs
END
CREATE TABLE table_specs
( db varchar(200) NOT NULL
, tbl_name varchar(200) NOT NULL
, col_cnt int NULL )
DECLARE @.db_name varchar(200)
DECLARE @.count as int
DECLARE @.cmd varchar(2000)
SET @.cmd = ''
SET @.db_name = ''
DECLARE dbs CURSOR FOR
SELECT name FROM master..sysdatabases where name is not null
OPEN dbs
FETCH NEXT FROM dbs into @.db_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.db_name
SET @.cmd = 'USE [' + @.db_name + ']'
PRINT @.cmd
EXECUTE ( @.cmd)
SET @.cmd = 'INSERT INTO tempdb..table_specs
SELECT ''' + @.db_name + ''', a.name, count(*)
FROM [' + @.db_name + ']..sysobjects a, [' + @.db_name + ']..syscolumn
s
b
WHERE a.id = b.id
AND a.type = ''U''
GROUP BY a.name'
EXECUTE (@.cmd)
PRINT @.cmd
FETCH NEXT FROM dbs into @.db_name
END
CLOSE dbs
DEALLOCATE dbs
-- SELECT db, sum(col_cnt) FROM tempdb..table_specs GROUP BY db
-- SELECT * FROM tempdb..table_specs

Number of columns and performance

Hi,

I'm designing a new database and I have a doubt in which surely you
can help me.
I'm storing in this database historical data of some measurements and
the system in constantly growing, new measurements are added every
day.
So, I have to set some extra columns in advance, so space is available
whenever is needed and the client doesn't have to modify the structure
in SQL server.
The question is: the more columns I add "just in case", the slower the
SQL reads the table?
Of course the "empty" columns are not included in any query until they
have some valid data inside.
Will I have better performance if I configure only the columns being
used at the moment, without any empty columns?

Thanks in advance.

Ignacio"Nacho" <nacho.jorge@.gmail.comwrote in message
news:1177509485.676313.103910@.s33g2000prh.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I'm designing a new database and I have a doubt in which surely you
can help me.
I'm storing in this database historical data of some measurements and
the system in constantly growing, new measurements are added every
day.
So, I have to set some extra columns in advance, so space is available
whenever is needed and the client doesn't have to modify the structure
in SQL server.

Umm... I don't see why you have to do this now.

Do it later.

I mean will you even know the type or size of the columns now?

And how will you even give them meaningful names when they have none now.

Quote:

Originally Posted by

The question is: the more columns I add "just in case", the slower the
SQL reads the table?
Of course the "empty" columns are not included in any query until they
have some valid data inside.
Will I have better performance if I configure only the columns being
used at the moment, without any empty columns?

A tad (since there's a small metadate overhead on the leave nodes).

But more importantly, you'll have a messy schema on your hands. I'd wait to
add them.

Quote:

Originally Posted by

>
Thanks in advance.
>
Ignacio
>

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Nacho (nacho.jorge@.gmail.com) writes:

Quote:

Originally Posted by

I'm designing a new database and I have a doubt in which surely you
can help me.
I'm storing in this database historical data of some measurements and
the system in constantly growing, new measurements are added every
day.
So, I have to set some extra columns in advance, so space is available
whenever is needed and the client doesn't have to modify the structure
in SQL server.
The question is: the more columns I add "just in case", the slower the
SQL reads the table?
Of course the "empty" columns are not included in any query until they
have some valid data inside.
Will I have better performance if I configure only the columns being
used at the moment, without any empty columns?

As always with performance questions, there are several "it depends".

If these just-in-case columns are varchar (or nvarchar or varbinary),
they only take up two bytes each extra, which may not be cause for
alarm. On the other hand, if they are char(50) or some other fixed
length, they take up the full space, NULL or not.

Next thing that matters is how the access against the tables are. If
there plenty of table scans, or for that matter range seeks in the clustered
index, like OrderDate BETWEEN @.fromdate AND @.todate, there is certainly
a performance cost for adding more bytes to every rows. More bytes per
row, fewer rows per page, and more pages to read for the same number
of rows. On the other hand, if access mainly is by non-clustered index
and key lookup, then the extra bytes do not have the same cost.

But this latter pattern is rarely the only access pattern for a table,
so the conclusion is that, yes, there is a cost. But how big the cost
is, is more difficult to tell.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Number of Columns

How can I tell how many column is returned in a query like this?
Select *.general, lname.newbusiness, contact.newbusiness, ….
ThanksThere is no built-in functions in SQL Server which does this. However, most
client side data access APIs will have a mechanism of identifying the number
of columns in the columns collection of the resultset.
Anith|||Depends how you are returning the data. For example, the Count porperty
of the ADO Fields collection gives this information.
Anyway, it is generally considered bad practice to use SELECT * in a
production application. List all the column names individually. Query
Analyzer lets you click and drag the column list into the editing
window so you don't have to do lots of typing.
--
David Portas
SQL Server MVP
--|||Actually, I don't really know that syntax, but I guess you mean something
similar to this :
select table1.*, table2.some_field, table2.some_otherfield, table3.*
etc
The easiest way I can see is by actually counting the fields in the
resultset. If you do not want the results than add a WHERE 1 = 2 to the end,
that way you will get the structure of the resultset, but without any data
in it (and without having to wait for the server to do all the work)
A rather complex route to find out upfront would be to do it like this :
select total_number_of_columns = (SELECT COUNT(*) FROM syscolumns col JOIN
sysobjects obj ON obj.id = col.id and obj.name = 'table1' and xtype =
'U') -- table 1 : * = all columns
+ 2 -- table 2 only two
columns asked for
+ (SELECT COUNT(*) FROM
syscolumns col JOIN sysobjects obj ON obj.id = col.id and obj.name =
'table3' and xtype = 'U') -- table 3 : * = all columns
Probably works, but I wonder what it's use is.
Cu
Roby
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||Emma,
Not sure what you are trying to accomplish, but here is one way to do it in
t-sql:
Select 1 as '1', 2 as '2', 3 as '3', 4 as '4', 5 as '5', 6 as '6'
into ##p
select count(*) from tempdb..syscolumns where id = object_id('tempdb..##p')
Ilya
"Emma" <Emma@.discussions.microsoft.com> wrote in message
news:678A98EB-AC14-4C4B-B3F0-9D81D837B24F@.microsoft.com...
> How can I tell how many column is returned in a query like this?
> Select *.general, lname.newbusiness, contact.newbusiness, ..
> Thanks
>|||This won't work in every case. SELECT INTO requires that the column
names are unique so if you join two tables and don't alias the columns
then it may fail. Perhaps the OP knows that her two tables won't have
conflicting column names but if the columns were really fixed and known
in advance then she wouldn't need a query to count them. If all the
columns are aliased then they are presumably known and therefore the
query is still pretty pointless. I guess the real question here is
exactly why the OP wouldn't know at development time how many columns
would be returned by her queries.
--
David Portas
SQL Server MVP
--|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!|||Since you havent provided the full query, I'll try to help you with
what you had given me.
Run the following in Qeary Analyzer.
SP_HELP general
find the number of columns and then add each columns that followes
afterwords...
NOTE:
It will help if people post questions with proper information and Code
and be clear on what they are looking to solve!!!!!!!!!!!!!!!!!

number manipulation in non-identity columns

hi.

i am using ms sql server 2000.

can somebody tell me what the code would be to remove all the values
in a given column and replace them with the associated number of the row
with each execution.

so, if i have a column:

nums
|1|
|2|
|3|
|4|

and somebody deletes record |2|

i would like the nums colum to update to

|1|
|2|
|3|

not:

|1|
|3|
|4|

it seems simple but i am having a hard time with this.
how is it done?

thanks.

SET XACT_ABORT ON
BEGIN TRANSACTION
DELETE FROM MyTable WHERE nums=@.nums
SET NOCOUNT ON
UPDATE MyTable SET nums=nums-1 WHERE nums>@.nums
SET NOCOUNT OFF
COMMIT TRANSACTION

Now I would never recommend actually doing that. It's resource intensive. You'd be better off not using a "nums" column, and using something like a CreateDate column of type datetime. Then eitehr using a subquery or SQL 2005's rank command, or a stored procedure to return a "nums" to you (In the case of using CreateDate, return back the number of other columns with a lower CreateDate), Like:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTable

Now you can go ahead and delete records and nums will adjust for you in order of CreateDate.

|||hi. thanks a lot for your reply.

i am trying to follow your last (least expensive) suggestion.
i am a little confused by the "t1" selects.

i have the following table:

PersonalPhotos
photo_id PK
photo_name
photo_location
photo_size
user_name
photo_date
photo_number

I was previouly using a stored procedure to create the non-identity number column in
photo_number.

I am now trying your code:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.CreateDate) nums
FROM PersonalPhotos

I have tried creating a t1 table with a t1_date column but that doesnt seem to work. If i replace
all the t1s with personalphoto and all the CreateDates with photo_date, i get results, but no
values in the nums column (which i could have guessed).

I am sorry, i am not a sql programmer (but i am an eager learner) and nothing is obvious.
further clarification would be appreciated.

thanks.|||i am still working on this. can somebody help clarify?
thanks|||I am pretty sure Motley meant to use a T1 table alias:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTableT1|||ok, thanks for helping out. i'll try that when i get home from work.
much appreciated.|||

hi.

i am home now and trying this. i am still confused.

as stated above i have a PersonalPhotos table with

a photo_date column that i'd like to use to delete

records and adjust numbers so they are *always* sequential.

I do not have a T1 table, but created one per Motley's suggestion.

I gave it 2 columns (t1_id, t1_date).

now, i have adjusted Motley's recommendation:

SELECT t1.*,(SELECT COUNT(*) FROM MyTable WHERE MyTable.CreateDate<t1.CreateDate) nums
FROM MyTable

to apply to the table i am working with - "PersonalPhotos":

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.t1_date) nums
FROM PersonalPhotos

When that didnt work, I tried Tmorton's suggestion:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos WHERE PersonalPhotos.photo_date<t1.t1_date) nums
FROM PersonalPhotos t1

None of this works :(

The errors I am getting are all about the existence (or lack thereof) of the t1

table/columns. In the above select statement, the error I get is:

"Invalid column name 't1_date'." but, t1.t1_date clearly lives in table t1.

could somebody PLEASE explain to me:

1) what is the point of creating the new t1 table (or, do i need to manually create it)?

2) what should the columns be in the new t1 table?

3) why i am getting the Invalid column error?

clarification greatly appreciated.

|||

pbd22:

1) what is the point of creating the new t1 table (or, do i need to manually create it)?

You should NOT create a new table. Using the alias "t1" makes it possible to use the same table "PersonalPhotos" twice in the same query.

pbd22:

2) what should the columns be in the new t1 table?

There should be no new t1 table.

pbd22:

3) why i am getting the Invalid column error?

And actually, I had put the table alias against the wrong table reference.

Try your query like this; you should have better luck:
SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date
|||ok, thank you.
that makes more sense to me. i am at work now but will
try your suggestion when i get home tonight. thanks for
helping to clarify.|||Hi.

I have tried your suggestion. I am not getting the following:

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 't1' does not match with a table name or alias name used in the query.

The SQL i used was as you suggested:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

I have also tried variations on this select statement:

SELECT t1.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos t1
ORDER BY PersonalPhotos.photo_date

The above throws the following error:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'PersonalPhotos' does not match with a table name or alias name used in the query.

I will keep trying (i seem to not be having much luck with this SQL) and will let you know if i stumble
on the answer. In the mean time, if you have more suggestions, I would appreciate it.

thank you.|||OK, this suggestion comes with Terri's Golden Guarantee that it will not generate an error:

SELECTPersonalPhotos.*,(SELECT COUNT(*) FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

Note that the t1.* was replaced with a PersonalPhotos.*, because this is the table name being referred to in the FROM clause (and is what threw me off on my first reply).|||thank you terri! you rock. that one did the trick. I have one last tiny question :)
this select statement now does exactly what i want, but i need the numbers to
read 1 - N for the current user, not all users. Right now, the select is for all users
and, as a result, a user may see (9,10,11,12,13,14) next to his six pictures. My
user column is "user_name". also, how do i get the count to start at 1 (not zero)
for each user? so:

1) how do i get the count to be user-specific?
2) how do i get the count to start at 1, not zero?

thanks.|||You can use a query like this:

SELECT PersonalPhotos.*,(SELECT COUNT(*)+1 FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date AND PersonalPhotos.user_name = t1.user_name) nums
FROM PersonalPhotos
ORDER BY PersonalPhotos.photo_date

I need to add that typically this sort of thing (adding row numbers) is done on the front end, where it is much less intensive. SQL Server has to work pretty hard to execute this query, as I believe it is doing an extra SELECT statement for each row in your table.|||hi. thanks. this solution now works.

i put the following code in my stored procedure (with a few changes):

BEGIN
SELECT PersonalPhotos.user_name,(SELECT COUNT(*)+1 FROM PersonalPhotos t1 WHERE PersonalPhotos.photo_date<t1.photo_date AND PersonalPhotos.user_name = t1.user_name) nums
FROM PersonalPhotosWHERE user_name = Context.User.Identity.Name.ToString()
ORDER BY PersonalPhotos.photo_dateDESC
END

i am assuming it returns a "nums" column that can be read by ASP. In the HTML,
I have the following line in my GridView control:

<asp:BoundField HeaderText="Number"DataField="nums" ReadOnly="True" HeaderStyle-HorizontalAlign="Center" ItemStyle-HorizontalAlign="Center" /
and, get the following error:

A field or property with the name 'nums' was not found on the selected data source.

how do i get the created "nums" column to appear next to the pics on the client?

thanks again.|||

thanks. i took your advice and am currently figuring this out for the client.

thanks for all your help!

Number limit of table columns in transactional replication

I was setting up transactional replication for a big table and got the
following error message:
The article cannot be created on table 'TEST' because it has more than 255
columns
Is that the limit of replication in SQL Server 2000? If yes, Is there any
way to increase the limit, or it can be configured?
Thanks!
This is the limit and it isn't configurable . One possible solution is to
partition the table into smaller tables and replicate each one separately.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Nulls in columns additions when 1 or more column values is blank

I am running into an issue when adding data from multiple columns into
one alias:

P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION

If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?

Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.

ThanksISNULL(P.CITY,'')
Techhead wrote:

Quote:

Originally Posted by

I am running into an issue when adding data from multiple columns into
one alias:
>
P.ADDR1 + ' - ' + P.CITY + ',' + ' ' + P.STATE AS LOCATION
>
If one of the 3 values is blank, the value LOCATION becomes NULL. How
can I inlcude any of the 3 values without LOCATION becoming NULL?
>
Example, if ADDR1 and CITY have values but STATE is blank, I get a
NULL statement for LOCATION. I still want it to show ADDR1 and CITY
even if STATE is blank.
>
Thanks
>

|||You can use COALESCE, something like this will do it:

COALESCE(P.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
COALESCE(P.STATE, '') AS LOCATION

Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:

COALESCE(P.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jun 4, 3:29 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

You can use COALESCE, something like this will do it:
>
COALESCE(P.ADDR1, '') + ' - ' + COALESCE(P.CITY, '') + ', ' +
COALESCE(P.STATE, '') AS LOCATION
>
Also, you can play with formatting variations based on what you want to get
when one of the columns is NULL, like this:
>
COALESCE(P.ADDR1, '') + COALESCE(' - ' + P.CITY, '') + COALESCE(', ' +
P.STATE, '') AS LOCATION
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

Somebody at work told me to use this:

SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END

It seems to work. Is this similar as to what is described above?|||Techhead (jorgenson.b@.gmail.com) writes:

Quote:

Originally Posted by

Somebody at work told me to use this:
>
SELECT CASE WHEN P.STATE IS NULL THEN '' ELSE P.STATE END
>
It seems to work. Is this similar as to what is described above?

Yes, coalesce is a shortcut for the above. The nice thing with coalesce is
that it accept a list of values, and will return the first value that
is non-NULL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Nulls debate

Hi!
What's your opinion about whether null columns should ever allow nulls (or not)?
Thanks in advance for your comments.
We don't have to hash this out again, do we?
http://groups.google.com/groups?hl=e...A*sqls erver*
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Francisco" <anonymous@.discussions.microsoft.com> wrote in message
news:A2A108C8-BD9F-4B70-9921-A306FB506B5F@.microsoft.com...
> Hi!
> What's your opinion about whether null columns should ever allow nulls (or
not)?
> Thanks in advance for your comments.
>
|||If your business rules determine data for a column may or may not be present
then nulls should be allowed in your table design. Now on the flip side if
you business rules say there should always be data for a column, then you
should NEVER design your table to allow nulls since this violates your
business rule.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Francisco" <anonymous@.discussions.microsoft.com> wrote in message
news:A2A108C8-BD9F-4B70-9921-A306FB506B5F@.microsoft.com...
> Hi!
> What's your opinion about whether null columns should ever allow nulls (or
not)?
> Thanks in advance for your comments.
>
|||Sorry Aaron, I didn′t mean to offend you.
Sorry my ignorance. Perhaps I make a mistake to express my doubt.
|||Why do you think you offended me?
It's just a very common debate, and the google threads should provide you
with all the information that could possibly be offered again...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Nulls debate

Hi!
What's your opinion about whether null columns should ever allow nulls (or n
ot)?
Thanks in advance for your comments.We don't have to hash this out again, do we?
ion+group%3A*sqlserver*" target="_blank">http://groups.google.com/groups?hl=...p%3A*sqlserver*
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Francisco" <anonymous@.discussions.microsoft.com> wrote in message
news:A2A108C8-BD9F-4B70-9921-A306FB506B5F@.microsoft.com...
> Hi!
> What's your opinion about whether null columns should ever allow nulls (or
not)?
> Thanks in advance for your comments.
>|||If your business rules determine data for a column may or may not be present
then nulls should be allowed in your table design. Now on the flip side if
you business rules say there should always be data for a column, then you
should NEVER design your table to allow nulls since this violates your
business rule.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Francisco" <anonymous@.discussions.microsoft.com> wrote in message
news:A2A108C8-BD9F-4B70-9921-A306FB506B5F@.microsoft.com...
> Hi!
> What's your opinion about whether null columns should ever allow nulls (or
not)?
> Thanks in advance for your comments.
>|||Sorry Aaron, I didn′t mean to offend you.
Sorry my ignorance. Perhaps I make a mistake to express my doubt.|||Why do you think you offended me?
It's just a very common debate, and the google threads should provide you
with all the information that could possibly be offered again...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Nulls being allowed when they shouldnt be?

I have a simple table, for some reason, certain columns seem to accept
Nulls even though they shouldn't, for example the I can set the 'Name'
field to Null using my web application or directly in Enterprise
Manager. field How do I prevent this? However the 'RecCreated' doess
not permit nulls.

CREATE TABLE [dbo].[Group] (
[GroupID] [int] IDENTITY (1000, 1) NOT NULL ,
[Name] [nvarchar] (50) NOT NULL ,
[Description] [nvarchar] (750) NULL ,
[RecCreated] [datetime] NOT NULL ,
[RecUpdated] [datetime] NOT NULL ,
[RecCreatedBy] [int] NOT NULL ,
[RecUpdatedBy] [int] NOT NULL ,
[RecActive] [int] NOT NULL
) ON [PRIMARY]
GO
thanks for any help you can give on thisIf your adding data to varchar field using EM are actually entering an empty
string? and most likely this is what your web application is doing as well.

1) You really shouldn't be using EM to enter data into your tables, or
rather you shouldn't be relying on it as a primary means to enter data.
It's great for testing and the like but it should be limitted to that.
2) It sounds like your web application needs some additional middle tier
logic to make sure that empty strings aren't passed into the database.

Hopefully these ideas help,
Muhd.

"grist2mill" <grist2mill@.excite.com> wrote in message
news:46e240e.0410080831.b3bd7d8@.posting.google.com ...
>I have a simple table, for some reason, certain columns seem to accept
> Nulls even though they shouldn't, for example the I can set the 'Name'
> field to Null using my web application or directly in Enterprise
> Manager. field How do I prevent this? However the 'RecCreated' doess
> not permit nulls.
>
> CREATE TABLE [dbo].[Group] (
> [GroupID] [int] IDENTITY (1000, 1) NOT NULL ,
> [Name] [nvarchar] (50) NOT NULL ,
> [Description] [nvarchar] (750) NULL ,
> [RecCreated] [datetime] NOT NULL ,
> [RecUpdated] [datetime] NOT NULL ,
> [RecCreatedBy] [int] NOT NULL ,
> [RecUpdatedBy] [int] NOT NULL ,
> [RecActive] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> thanks for any help you can give on this|||Can you post some code that will reproduce the problem. I assume you are
aware that NULL is different to the empty string? How did you check that
NULLs were present in the table? Can you actually run the following queries
in Query Analzer and see what they return:

SELECT [name]
FROM dbo.[Group]
WHERE [name] IS NULL

SELECT COLUMNPROPERTY(OBJECT_ID('dbo.[Group]'),'name','AllowsNull')

You can't always believe what you see in Enterprise Manager because the
display isn't always refreshed when you would expect it to be. Also, I
wouldn't trust EM as a method for entering data into tables.

P.S. "GROUP" is a reserved word. It's not a good idea to use reserved words
for table names. Anyway "Group" is too meaningless to make a good table
name. Group of what?

--
David Portas
SQL Server MVP
--|||"grist2mill" <grist2mill@.excite.com> wrote in message
news:46e240e.0410080831.b3bd7d8@.posting.google.com ...
>I have a simple table, for some reason, certain columns seem to accept
> Nulls even though they shouldn't, for example the I can set the 'Name'
> field to Null using my web application or directly in Enterprise
> Manager. field How do I prevent this? However the 'RecCreated' doess
> not permit nulls.
>
> CREATE TABLE [dbo].[Group] (
> [GroupID] [int] IDENTITY (1000, 1) NOT NULL ,
> [Name] [nvarchar] (50) NOT NULL ,
> [Description] [nvarchar] (750) NULL ,
> [RecCreated] [datetime] NOT NULL ,
> [RecUpdated] [datetime] NOT NULL ,
> [RecCreatedBy] [int] NOT NULL ,
> [RecUpdatedBy] [int] NOT NULL ,
> [RecActive] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> thanks for any help you can give on this

In Enterprise Manager, if you type NULL in the table data screen, it will
put the literal string 'NULL' into the table, which is not the same as a
real NULL; you need Ctrl+0 to get a real NULL. I suspect your application is
doing the same thing, or perhaps displaying an empty string as NULL, which
it isn't.

EM isn't a good application for modifying data, because it's not always
completely clear what it's doing. You should use Query Analyzer instead,
because then you can control every detail of the SQL you execute, and you
can also save scripts for future use more easily.

Simon|||Thanks to all for your help and tips.

So if I understand things correctly, (having run David's suggested
queries),

if you set a column to be nvarchar to not accept nulls, the column
will still accept empty strings. So to make a field mandatory, I must
enforce this at the application level (which is not such good
practice, because if another application should access the table I
won't know if they correctly enforce that certain fields should be
mandatory).

Have I understod things correctly (or should a be using a different
data type from nvarchar)?|||Thanks to all for your help and tips.

So if I understand things correctly, (having run David's suggested
queries),

if you set a column to be nvarchar to not accept nulls, the column
will still accept empty strings. So to make a field mandatory, I must
enforce this at the application level (which is not such good
practice, because if another application should access the table I
won't know if they correctly enforce that certain fields should be
mandatory).

Have I understod things correctly (or should a be using a different
data type from nvarchar)?|||On 11 Oct 2004 03:05:00 -0700, grist2mill wrote:

>if you set a column to be nvarchar to not accept nulls, the column
>will still accept empty strings. So to make a field mandatory, I must
>enforce this at the application level

Hi grist2mill,

You can use a CHECK constraint:

CREATE TABLE xxxx (...,
NonEmptyCol nvarchar(78) NOT NULL
CHECK (NonEmptyCol <> ''),
...)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo,
thats the problem solved
regards
GM

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<ouokm0loui5brq4hi1f7qnuqbdd4fm3kl0@.4ax.com>...
> On 11 Oct 2004 03:05:00 -0700, grist2mill wrote:
> >if you set a column to be nvarchar to not accept nulls, the column
> >will still accept empty strings. So to make a field mandatory, I must
> >enforce this at the application level
> Hi grist2mill,
> You can use a CHECK constraint:
> CREATE TABLE xxxx (...,
> NonEmptyCol nvarchar(78) NOT NULL
> CHECK (NonEmptyCol <> ''),
> ...)
> Best, Hugo

NULLS --> blanks ?

I wrote a simple data flow with an OLE DB source and destination. I do a direct mapping of the columns ( colA - > colA) with no transformations needed. I found that colA on the destinatin does not allow NULLS (required by the program that accesses that database) while colA on the source supports and has NULLs, Is there any accomodaiton for handling NULLS (like mapping them to blanks) in the direct copy approach or do I need to read each row and test colA_ISNull?

TIA,

barkingdog

There is no way of dealing with them other than testing them for nullability and changing them accordingly.

This is easily achieved with a Derived Column component.

-Jamie

|||

>>>> This is easily achieved with a Derived Column component.

Excellent suggestion!. I acomplsihed the same thing but with a more arduous Script task. A Derived Column seems like a much better approach.

Thanks,

Barkingdog

Nullable got me confused

I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueExceptionwhen I try to GetInt32 but not when I try GetString. Why the difference?

Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.

I tested with SqlDataReader and I was even unluckier than you: both GetInt32 and GetString returned aSqlNullValueException. So I managed to determine whether the returned data from database is null and then get its value:

SqlDataReader sdr = command.ExecuteReader(CommandBehavior.SequentialAccess);
sdr.Read();
int i= sdr.IsDBNull(1)?0:sdr.GetInt32(1);

|||Doesn't it seem like a huge oversight by the developers? Null values are pretty common in databases, but the SqlDataReader can't handle them.

We are forced to check for null for every column that is nullable in the database, or write our own DataReader. At the same time, some other smart developers created nullable types in C#, so the discrepancy seems even bigger to me: to get a value (possibly null) from a nullable column into a nullable int, I need to go through hoops in order to avoid an exception.

int? x;
if(reader.IsDbNull(0) {
x = null;
} else {
x = reader.GetInt32(0);
}|||

Microsoft created System.Nullables in .NET FCL(framework class library) 2.0 to deal with the known issues in .NET 1.1. Try the thread below for more info. Hope this helps.

http://forums.asp.net/thread/1300858.aspx

Nullable Columns and Query Index Relief

I seem to recall reading an article that suggested Nullable columns may
prevent that column from receiving index relief in Query Plans.
Am I remembering incorrectly, or is this actually the case...?
If you have a link to an article that proves\disproves this I would be
greatful.
I am attempting to put together a case for removing nulls from our DB over
time and need as much ammo as I can muster.
I am the only "Anti-Null" guy here.
Thanks in advance,
Greg Jackson
PDX, OregonGreg,
SQL Server treats a NULL just like any value in an index.
Null pros and cons in general has been discussed in many places (just read
Date's books vs. Codd and Celko, for instance). If you search this newsgroup
archive, you will find such discussions as well.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:OP$$jv4BEHA.3568@.tk2msftngp13.phx.gbl...
> I seem to recall reading an article that suggested Nullable columns may
> prevent that column from receiving index relief in Query Plans.
> Am I remembering incorrectly, or is this actually the case...?
>
> If you have a link to an article that proves\disproves this I would be
> greatful.
> I am attempting to put together a case for removing nulls from our DB over
> time and need as much ammo as I can muster.
> I am the only "Anti-Null" guy here.
>
> Thanks in advance,
>
> Greg Jackson
> PDX, Oregon
>|||yeah, I have lots of data on Null pros and cons, I was just looking for the
Index Relief thing.
Guess, I was just reaching.
Thanks again Tibor, Jaxon is keeping you busy this week....
:-)
GAJ

Null values cause row height to change

I have a fairly simple report with several columns in the detail section. I
have set the row height to 0.15in (text is 6pt) and set both CanGrow = false
and CanShrink = false for all textboxes in the row.
The report always renders perfectly in the Visual Studio preview tab and
when exporting to PDF, but I run into problems when viewing it on the report
server webpage. Any row with a Null value in one of its fields is rendered
twice as high as the nomal rows. If I change my query so that it doesn't
return nulls, all of the rows render with the same height as expected.
Anyone else run inot this behaviour?I found a cheap way to get around the problem by replacing null values in my
report with a single space. I used expressions like the following:
=iif(IsNothing(Fields!MyField.Value)," ",Fields!MyField.Value)
This is a pain to have to enter in every field though. I would still like to
know a way to get it to render properly in HTML without having to go to all
this trouble.|||there are two options in ADVANCED PROPERTIES of the textbox (or cell of
a table) which meant expand the textbox to accomodate the data and
shrink the textbox to accomodate the data i think by unchecking those
options (ie not allowing shrinking/expansion of textbox) will resolve
the problem check that out n let me know what result comes out
thanx