Collation problem: February 2012

Saturday, February 25, 2012

column reference error

Hi,
Can anyone help me with what I should be looking for with this error?
(I have also tried putting single quotes around the search number in the
query - with the same results)
Thanks!
Rich.
An unhandled exception of type 'System.Exception' occurred in
receivingdb.dll
Additional information: Error number = 547
Error class = 16
Error state = 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_purchaseorders_receivinglog'. The conflict occurred in database
'Receiving', table 'purchaseorders', column 'delivery'.
DELETE FROM [receivinglog] WHERE [delivery] = 125Rich K wrote:
> Hi,
> Can anyone help me with what I should be looking for with this error?
> (I have also tried putting single quotes around the search number in
> the query - with the same results)
> Thanks!
> Rich.
>
> An unhandled exception of type 'System.Exception' occurred in
> receivingdb.dll
> Additional information: Error number = 547
> Error class = 16
> Error state = 1
> DELETE statement conflicted with COLUMN REFERENCE constraint
> 'FK_purchaseorders_receivinglog'. The conflict occurred in database
> 'Receiving', table 'purchaseorders', column 'delivery'.
> DELETE FROM [receivinglog] WHERE [delivery] = 125
You have a foreign key column in another table. You need to delete the rows
in that table that are controlled by the rwo you are deleting in this table.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OgZIYd47FHA.3976@.TK2MSFTNGP15.phx.gbl...

> You have a foreign key column in another table. You need to delete the
> rows in that table that are controlled by the rwo you are deleting in this
> table.
>
As easy as that eh?
Thanks Bob it makes sense now!

column reference as parameter of sp

Is it possible to have a column reference as the parameter of a stored
procedure? The books say parameters have to be literal expressions, so
I'm guessing it's not possible, but I thought I'd ask anyway.
I want to do something like this:
INSERT INTO Table1 ([Column1]) (SELECT ? FROM Table2)
The single, unnamed input parameter needs to be the name of one of the
columns in Table2, such as [Column2]. I want it to be a stored
procedure because the query must run many times with alternating column
references and literals.
ShaileshEither
exec a string, or look at using sp_executesql... either will work... both
are doc'd in books on line.
"Shailesh Humbad" <humbads1@.hotmail.com> wrote in message
news:FrmYa.246417$BA.59826426@.twister.columbus.rr.com...
> Is it possible to have a column reference as the parameter of a stored
> procedure? The books say parameters have to be literal expressions, so
> I'm guessing it's not possible, but I thought I'd ask anyway.
> I want to do something like this:
> INSERT INTO Table1 ([Column1]) (SELECT ? FROM Table2)
> The single, unnamed input parameter needs to be the name of one of the
> columns in Table2, such as [Column2]. I want it to be a stored
> procedure because the query must run many times with alternating column
> references and literals.
> Shailesh
>

Column Ranking on SS 2000 and/or Reporting Services report

I see a Rank function on SQL Server 2005 and this is exactly what I want. I am writing a Reporting Services report on Sql Server 2000 and the Rank function is not available on either software. The following is my data and what I want...

Name Value Rank

A 5 2

B 5 2

C 6 1

D 4 4

E 2 5

F 1 6

Any ideas on how to code a rank function on SS 2000 or Reporting Services?This article describes using a table-valued function to perform ranking. http://www.devx.com/getHelpOn/10MinuteSolution/16499/1954?pf=true|||

The SQL function in the link may not provide you the result you are looking for, as in SQL Server 2005. For example, the function may return records with ranks like 1,2,3,3,4,5 but what you need is 1,2,3,3,5,6 as per sql 2005. To do that, write an update statement at the end of the function before returning.

UPDATE t1

SET Rank = Rank +

(SELECT SUM(COUNT(Rank)-1) FROM table1 t2 WHERE t2.Rank < t1.Rank

GROUP BY Rank HAVING COUNT(Rank) > 1)

FROM table1 t1

GROUP BY Rank

HAVING COUNT(*) = 1

ORDER BY Rank'

Shyam

|||Thanks. I noticed the result set from the link. Thanks for the code.|||

Can you please mark it as answer?

Shyam

Column Query with a Variable

I have 30 fields in a table called ADP_Pay_Detail that I need to query with
the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
DED_CD_30. I have coded a counter with a WHILE statement to increment the
last number of this string, and assign the entire string to a varchar
variable named @.column_amt. All of the type conversions I need to do are
correct. I then try to write a query using this variable as a field name.
An example would be
SELECT *
FROM ADP_Pay_Detail
where @.column_amt = 30
This isn't working at all. Can anyone give me a suggestion about how to
accomplish what I want to do? The query I have to write is much more
complicated than my example, and I'd hate to have to write it 30 times to
query each column!
Thanks!
Keith
Keith>I have 30 fields in a table called ADP_Pay_Detail that I need to query with
> the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
> DED_CD_30.
Just curious if you have considered a more sensible and relational design?
Are you going to add DED_CD_31 -> DED_CD_n at some point?

> SELECT *
> FROM ADP_Pay_Detail
> where @.column_amt = 30
> This isn't working at all.
That is right, because T-SQL is not a language that allows you to construct
queries like this, you must tell it about the metadata and not let it decide
on its own. My first suggestion would be to pass all 30 columns back, and
let the app display only the column(s) it needs. This will be more flexible
as you can later decide to show 2 or 3 or all columns and you won't have
much change to do...
My more intuitive response would be to store the data and its value as data,
instead of mixing data and metadata. An example might be:
CREATE TABLE dbo.ADP_Pay_Detail
(
pdID INT PRIMARY KEY
-- , other columns
)
CREATE TABLE dbo.ADP_Pay_Detail_DED_CD
(
pdID INT FOREIGN KEY REFERENCES dbo.ADP_Pay_Detail(pdID),
column_amt TINYINT, -- CHECK CONSTRAINT perhaps?
value INT
)
Now you can say
SELECT * FROM ADP_Pay_Detail d
INNER JOIN ADP_Pay_Detail_DED_CD c
ON d.pdID = c.pdID
WHERE c.column_amt = 30
And you don't have any silly column names with data embedded in them. (Next
you can work on manageable table names.)

> Can anyone give me a suggestion about how to
> accomplish what I want to do?
As a last resort, dynamic SQL. PLEASE READ THE FOLLOWING and heed the
warnings seriously:
http://www.sommarskog.se/dynamic_sql.html|||>> I have 30 fields [sic] in a table called ADP_Pay_Detail that I need to query w
ith
the exact same query. Each field [sic] is named DED_CD_1, DED_CD_2,
etc. up to DED_CD_30. <<
Rows are not records; fields are not columns; tables are not files.
You need to get a book on RDBMS basics and read the chapter on Normal
Forms. What you have here is a 1950's COBOL file layout with a fake
OCCURS clause.
Apparently SQL is the first compiled language you have ever used. It
is also a declarative language, so writing loops in a proprietary 3GL
is a sign of poor coding.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Column Properties Window Bug in Management Studio?

This issue deals with where to see the default value for a column in Sql Server 2005 Management Studio.

I have a default value of (1) in a column of type bit:

If you right click on the column in Management Studio object explorer and choose properties, the default value does not appear under "Default Binding" or "Default Schema" in the Column Properties window.

If you select the table and then choose View -> Summary, double click on the Columns folder, then right click -> properties on the column, again the default value does not appear under "Default Binding" or "Default Schema" in the Column Properties window.

If you right click on the table or column and choose "Modify", then select the column, the default value appears in "Default Value or Binding".

If you script the table out (right click -> Script Table as -> Create to), the default value for that column appears in the script.

I'm wondering if this is by design or a bug? Obviously right click-> properties is not a reliable way to view the properties on a column.

Hello Eric,

This issue seems to be a bug in Column Properties page. Could you please report this issue through Connect web site

https://connect.microsoft.com/SQLServer/Feedback

Click on Submit Feedback

Thanks

Sethu Srinivasan

SQL Server Manageability Dev Team

Column prefix doesnt match with a table name error

I keep getting an error of Column prefix " t" doesnt match with a table
name or alias name with the query with this sql query (part of a c#
script):
"SELECT
d.SalesRep_id,s.fname,s.lname,s.Total_BonusPerc,s.ID,s.Hire_date,s.Term_date
,
d.ddate,d.salesOff_loc_ID,d.SplitGross,
d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,c.ID,t.Amount_Revenue,t.MGTag_ID
FROM tblBankTran as t,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
= d.SalesRep_id INNER JOIN tblCustTag as c on c.ID = t.MGTag_ID WHERE
d.salesOff_loc_ID = " + 3 + "";
I'm trying to include the tblBankTran table and it's Amount_Revenue,
which the tblBanktran table field MGTag_ID is related to tblCustTag ID
field.
Thanks in advance
.NetSportsHi
This is invalid syntax
FROM tblBankTran as t,tblDeal as d INNER JOIN
Instead , FROM tblBankTran t INNER JOIN tblDeal s ON....
For details please refer to the BOL
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1118099682.466063.5530@.g43g2000cwa.googlegroups.com...
> I keep getting an error of Column prefix " t" doesnt match with a table
> name or alias name with the query with this sql query (part of a c#
> script):
> "SELECT
>
d.SalesRep_id,s.fname,s.lname,s.Total_BonusPerc,s.ID,s.Hire_date,s.Term_date
,
> d.ddate,d.salesOff_loc_ID,d.SplitGross,
> d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,c.ID,t.Amount_Revenue,t.MGTag_ID
> FROM tblBankTran as t,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
> = d.SalesRep_id INNER JOIN tblCustTag as c on c.ID = t.MGTag_ID WHERE
> d.salesOff_loc_ID = " + 3 + "";
> I'm trying to include the tblBankTran table and it's Amount_Revenue,
> which the tblBanktran table field MGTag_ID is related to tblCustTag ID
> field.
> Thanks in advance
> .NetSports
>

column position sql table

There are about 500 tables in one particular datbase. There areforeign keys set on most of the tables. I want to change the positionof the primary key column in all those tables. How can I do thatprogramatically?

What do you mean by "position" of the PK column?

|||

PK = primary key

|||

Let me rephrase my question:

What do you mean by"position" of the PK column?

|||

Hi dilbert1947,

Based on my understanding, if you mean "the column sequence number" for "the position of the primary key column", then i think there is nothing you have to worry about. In my opinion, changing the column sequence of your primary key won't affect anything in your database schema.

Hope my suggestion can help

|||

Ok let me put it this way. I had to add a primary key column (programmatically) to all the tables in a database. Now when you right click and view the table, you see the newly added primary key column as the right most column. After the primary key column was added, many foreign keys were also added in many of the tables. Now one way to change the column "position" is to manually right click on each table and click modify and then manually move the column to the top, so that every time you view the table, the primary key column appears on the left. Myself being a lazy person, I'd lprefer to write code that would do this in all the tables rather than manually modifying all the tables.

I hope that makes things clear.

|||

Do it manually in one of the tables. Then before you save the changes, click the button that says "Generate change SQL file" or something like that. Then read the file and see what the tool was going to execute.

Column placement, widths, etc.

Here's something to put on the "wish list". It could be argued that
it is outside the scope of RS, but I'll bring it up anyway. Hey - you
don't get if you don't ask! :-)
I think it would be nice if, when viewing a report in a browser, the
user could rearrange both the order and the widths of the columns.
This would also give the user the ability to hide columns that are not
of interest. If such a feature were added, it would also be nice to
be able to persist the user's preferences and/or reset to default.
Taking it one step further, it would be nice if the format stuck for
export, especially to, for example, a PDF.
Related to that is the idea of being able to sort columns. Yes, I
know there's a work-around for that, but wouldn't it be nice if the
functionality were incorporated right into RS?
I understand that these features may be planned for ActiveViews, but I
think they would be nice even for users who are not to the point of
ad-hoc reporting.
Just my thoughts - feel free to disagree! :-)
Brad.The ability for an end-user to make minor adjustments to the layout of a
prebuilt report is definitely something we want to do eventually. Our
initial focus is going to be making this work in the ad-hoc reporting
client, but I'm sure we'll get back to adding this sort of thing to static
reports at some point.
(Note: Sorting is high on the priority list).
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Bradley Plett" <plettb@.hotmail.com> wrote in message
news:raevi0d3l5b1rm1kgugpipbd9iaki3c7fv@.4ax.com...
> Here's something to put on the "wish list". It could be argued that
> it is outside the scope of RS, but I'll bring it up anyway. Hey - you
> don't get if you don't ask! :-)
> I think it would be nice if, when viewing a report in a browser, the
> user could rearrange both the order and the widths of the columns.
> This would also give the user the ability to hide columns that are not
> of interest. If such a feature were added, it would also be nice to
> be able to persist the user's preferences and/or reset to default.
> Taking it one step further, it would be nice if the format stuck for
> export, especially to, for example, a PDF.
> Related to that is the idea of being able to sort columns. Yes, I
> know there's a work-around for that, but wouldn't it be nice if the
> functionality were incorporated right into RS?
> I understand that these features may be planned for ActiveViews, but I
> think they would be nice even for users who are not to the point of
> ad-hoc reporting.
> Just my thoughts - feel free to disagree! :-)
> Brad.

Column permissions

1. I denied some user the select permissions on some columns in a table.
However, this user is able to see these columns through the views (SQL
Server 2000). Is there a way to automatically deny the permissions on the
same columns in all the views or it's necessary to set the permissions in
every view?
2. If a column is unchecked in the permissions window, is this the same as
permission granted?
3. I've read somewhere that users should not have access to the tables, only
have access to the views. What is a reason for this recommendation?
Thanks.1) Because of ownership chaining, if the user can read the column in
the view, they can read the column in the table, despite the
permissions being denied.
2) If the column is unchecked, that is permissions revoked, if it is
checked that is permission granted and the red X is denied.
3) This recommendation is based on a few things. First, the problem you
are seeing is remedied by using only views. You don't have to manage
certain permissions twice if everything is in views. Additionally, this
creates an abstraction layer, where the table can be changed and as
long as you update the view, the user never knows about the change.
This also applies to applications. Additionally, direct table access
opens up holes that a DBA may forget to plug. For instance, adding a
new "confidential" column and not denying permission on it. If you
are using views, (not views that just select * from a table), then the
new column will not be available until you explicitly make it so.
Eric Johnson
Consortio Services
Vik wrote:
> 1. I denied some user the select permissions on some columns in a table.
> However, this user is able to see these columns through the views (SQL
> Server 2000). Is there a way to automatically deny the permissions on the
> same columns in all the views or it's necessary to set the permissions in
> every view?
> 2. If a column is unchecked in the permissions window, is this the same as
> permission granted?
> 3. I've read somewhere that users should not have access to the tables, on
ly
> have access to the views. What is a reason for this recommendation?
> Thanks.

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
BkThe order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:
> There is no 'default' ordering of rows. Itzik sheds some interesting light
> on the subject:
> http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
> http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
> http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> > Can someone tell me what defines the order of SQL output columns when i
> > use a
> > default query on a table like "SELECT * from <Table> ".
> >
> > Is there a way to alter the default ORDER of these columns?
> >
> > I am aware of the ORDER BY but will not be able to use it for various
> > reasons.
> >
> > Regards
> > Bk
>|||As mentioned by Arnie, it is considered a bad practice to use "SELECT *"
in production code (with the exception of its use in an EXISTS clause).
When using SELECT * on a table, the columns in the resultset will match
the order in the table definition. There is no way to change this using
DML. The only way to change its order (apart from explicitely naming the
columns in the desired order) is to redefine the table.
HTH,
Gert-Jan
BK-Chicago wrote:
> Can someone tell me what defines the order of SQL output columns when i use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
Bk
The order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:

> There is no 'default' ordering of rows. Itzik sheds some interesting light
> on the subject:
> http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
> http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
> http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
>

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use
a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
BkThe order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/artic...blog_92886.html
http://www.sqlmag.com/article/artic...blog_92887.html
http://www.sqlmag.com/article/artic...blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:

> There is no 'default' ordering of rows. Itzik sheds some interesting ligh
t
> on the subject:
> http://www.sqlmag.com/article/artic...blog_92886.html
> http://www.sqlmag.com/article/artic...blog_92887.html
> http://www.sqlmag.com/article/artic...blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
>|||As mentioned by Arnie, it is considered a bad practice to use "SELECT *"
in production code (with the exception of its use in an EXISTS clause).
When using SELECT * on a table, the columns in the resultset will match
the order in the table definition. There is no way to change this using
DML. The only way to change its order (apart from explicitely naming the
columns in the desired order) is to redefine the table.
HTH,
Gert-Jan
BK-Chicago wrote:
> Can someone tell me what defines the order of SQL output columns when i us
e a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk

Column order/presentation in virtual table (result set from viewor UDF)

I was just messing around with some ad hoc views and table returning
UDFs today so I could look at and print out data from a small table
and noticed something strange.

If I stick my select statement into a View the columns are returned in
the order I specify in the SELECT, but if the same statement is in a UDF
(so I can specify a parameter), the columns are not returned in the
order specified in statement.

I know that relations don't have a specified column order, but it was my
understanding that a SELECT statement could be used to define how you
want your data presented. Views seem to respect the order specified in
the SELECT, but functions don't.

What am I missing? Is there some way to force the order of the columns
returned from a SELECT?

View:

CREATE VIEW dbo.View1
AS
SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,
ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE '4.2.%')

Column order from this view:
Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,
ElseStmt, NextStmt

Function:

ALTER FUNCTION dbo.Function1
(@.SearchPrm varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,
ChildStmt, ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE @.SearchPrm) )

Column order from this function:
Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,
ParentStmt, ForStmt

Table:
(I know that this table isn't entirely normalized, but it serves my
purposes to have a matrix instead of a fully normalized relation):

CREATE TABLE dbo.tblStmt (
StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,
Ident VARCHAR(255),
Text TEXT,
ErrorText TEXT,
Type INT,
ParentStmt VARCHAR(255),
ChildStmt VARCHAR(255),
IfStmt VARCHAR(255),
ForStmt VARCHAR(255),
ThenStmt VARCHAR(255),
ElseStmt VARCHAR(255),
NextStmt VARCHAR(255),
FullName VARCHAR(255),
LocalName VARCHAR(255),
Method INT
)

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')

INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')I forgot to mention an important detail. I'm creating the VIEW and
FUNCTION within the context of a Microsoft Access ADP file that's
pointed at the SQL Server 2000 database in question.

If I execute this statement in Query Analyzer:
SELECT * FROM dbo.Function1('4.2.%')
the columns are output as specified in the column list inside the
Function definition (Ident, Text, ..., etc.).

If I double-click on the Function's object in MS Access and enter 4.2.%
in the prompt for the parameter, then the column list is output in the
strange order as noted below (Type, Text, ElseStmt, ..., etc.).

So, this may actually be a Microsoft Access problem, but if anyone has
any information, I'd appreciate it. Thanks.

Beowulf wrote:

Quote:

Originally Posted by

I was just messing around with some ad hoc views and table returning
UDFs today so I could look at and print out data from a small table
and noticed something strange.
>
If I stick my select statement into a View the columns are returned in
the order I specify in the SELECT, but if the same statement is in a UDF
(so I can specify a parameter), the columns are not returned in the
order specified in statement.
>
I know that relations don't have a specified column order, but it was my
understanding that a SELECT statement could be used to define how you
want your data presented. Views seem to respect the order specified in
the SELECT, but functions don't.
>
What am I missing? Is there some way to force the order of the columns
returned from a SELECT?
>
View:
>
CREATE VIEW dbo.View1
AS
SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,
ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE '4.2.%')
>
Column order from this view:
Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,
ElseStmt, NextStmt
>
Function:
>
ALTER FUNCTION dbo.Function1
(@.SearchPrm varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,
ChildStmt, ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE @.SearchPrm) )
>
Column order from this function:
Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,
ParentStmt, ForStmt
>
Table:
(I know that this table isn't entirely normalized, but it serves my
purposes to have a matrix instead of a fully normalized relation):
>
CREATE TABLE dbo.tblStmt (
StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,
Ident VARCHAR(255),
Text TEXT,
ErrorText TEXT,
Type INT,
ParentStmt VARCHAR(255),
ChildStmt VARCHAR(255),
IfStmt VARCHAR(255),
ForStmt VARCHAR(255),
ThenStmt VARCHAR(255),
ElseStmt VARCHAR(255),
NextStmt VARCHAR(255),
FullName VARCHAR(255),
LocalName VARCHAR(255),
Method INT
)
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')
>
INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')

|||It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Razvan|||Razvan Socol wrote:

Quote:

Originally Posted by

It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Thanks for the reply, even though the question turned out to be off-topic.

Column order issue

Hi
When I want to use all (or at least many) columns in a table, I'm used to
just click the "Column" header in the Object browser window and then drag it
over to the "code pane". In Query Analyzer I was use to get all the columns
in the same order as they where showed in the object browser to the left.
When using MicroSoft SQL Server Mamagement Studio, I get the columns in
alphabetically order when I drag them over, eventhough they are shown in
another order in the Object Browser window.
Does anybody know if this is a setting that can be changed somewhere?
Regards
SteenAre you on May CTP? When I drag the columns folder to the query window, I get the columns listed in
the same order as in my CREATE TABLE statement...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi
> When I want to use all (or at least many) columns in a table, I'm used to just click the "Column"
> header in the Object browser window and then drag it over to the "code pane". In Query Analyzer I
> was use to get all the columns in the same order as they where showed in the object browser to the
> left. When using MicroSoft SQL Server Mamagement Studio, I get the columns in alphabetically order
> when I drag them over, eventhough they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
> Regards
> Steen
>|||Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:
> Are you on May CTP? When I drag the columns folder to the query
> window, I get the columns listed in the same order as in my CREATE
> TABLE statement...
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>> Hi
>> When I want to use all (or at least many) columns in a table, I'm
>> used to just click the "Column" header in the Object browser window
>> and then drag it over to the "code pane". In Query Analyzer I was
>> use to get all the columns in the same order as they where showed in
>> the object browser to the left. When using MicroSoft SQL Server
>> Mamagement Studio, I get the columns in alphabetically order when I
>> drag them over, eventhough they are shown in another order in the
>> Object Browser window. Does anybody know if this is a setting that can
>> be changed somewhere?
>> Regards
>> Steen|||Strange. I don't have a 2000 to test against. Perhaps there is a difference. I would report this to
the beta forums if I were you...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi Tibor
> I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the same table into a Qury
> Analyser I get the columns in the same order as they are shown.
> I've just tried to get the "CREATE TABLE" from the table, and here the columns are listed in the
> "correct" order which is the order they have been typed in and not alphabetically.
> Regards
> Steen
> Tibor Karaszi wrote:
>> Are you on May CTP? When I drag the columns folder to the query
>> window, I get the columns listed in the same order as in my CREATE
>> TABLE statement...
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>> Hi
>> When I want to use all (or at least many) columns in a table, I'm
>> used to just click the "Column" header in the Object browser window
>> and then drag it over to the "code pane". In Query Analyzer I was
>> use to get all the columns in the same order as they where showed in
>> the object browser to the left. When using MicroSoft SQL Server
>> Mamagement Studio, I get the columns in alphabetically order when I
>> drag them over, eventhough they are shown in another order in the
>> Object Browser window. Does anybody know if this is a setting that can be changed somewhere?
>> Regards
>> Steen
>|||This is a multi-part message in MIME format.
--040307040208010607010408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I've got the June CTP and I see the same behaviour with SSMS & a
SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Steen Persson (DK) wrote:
>Hi Tibor
>I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
>same table into a Qury Analyser I get the columns in the same order as they
>are shown.
>I've just tried to get the "CREATE TABLE" from the table, and here the
>columns are listed in the "correct" order which is the order they have been
>typed in and not alphabetically.
>Regards
>Steen
>Tibor Karaszi wrote:
>
>>Are you on May CTP? When I drag the columns folder to the query
>>window, I get the columns listed in the same order as in my CREATE
>>TABLE statement...
>>"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>>news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>>
>>Hi
>>When I want to use all (or at least many) columns in a table, I'm
>>used to just click the "Column" header in the Object browser window
>>and then drag it over to the "code pane". In Query Analyzer I was
>>use to get all the columns in the same order as they where showed in
>>the object browser to the left. When using MicroSoft SQL Server
>>Mamagement Studio, I get the columns in alphabetically order when I
>>drag them over, eventhough they are shown in another order in the
>>Object Browser window. Does anybody know if this is a setting that can
>>be changed somewhere?
>>Regards
>>Steen
>>
>
>
--040307040208010607010408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I've got the June CTP and I see the same behaviour with SSMS &
a SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions.<br>
</tt>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Steen Persson (DK) wrote:
<blockquote
cite="miduMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.microsoft.com"
type="cite">
<pre wrap="">Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Are you on May CTP? When I drag the columns folder to the query
window, I get the columns listed in the same order as in my CREATE
TABLE statement...
"Steen Persson (DK)" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:spe@.REMOVEdatea.dk"><spe@.REMOVEdatea.dk></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com">news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi
When I want to use all (or at least many) columns in a table, I'm
used to just click the "Column" header in the Object browser window
and then drag it over to the "code pane". In Query Analyzer I was
use to get all the columns in the same order as they where showed in
the object browser to the left. When using MicroSoft SQL Server
Mamagement Studio, I get the columns in alphabetically order when I
drag them over, eventhough they are shown in another order in the
Object Browser window. Does anybody know if this is a setting that can
be changed somewhere?
Regards
Steen
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--040307040208010607010408--|||Steen Persson (DK) (spe@.REMOVEdatea.dk) writes:
> When I want to use all (or at least many) columns in a table, I'm used
> to just click the "Column" header in the Object browser window and then
> drag it over to the "code pane". In Query Analyzer I was use to get all
> the columns in the same order as they where showed in the object browser
> to the left. When using MicroSoft SQL Server Mamagement Studio, I get
> the columns in alphabetically order when I drag them over, eventhough
> they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
Like Tibor, I was not able to repeat this. And I also tried against
SQL 2000.
I can't recall having seen any setting for this.
If you do this on Northwind..Orders, what do you see? I see:
OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,
ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||OK, so I was able to repeat this on SQL 2000. And investigating the issue
further by using Profiler, I found that the bug applies to both SQL 2000 and
SQL 2005. To wit the cause is there the underlying SELECT statement does not
have any ORDER BY clause. The queries are different, but both are missing
ORDER BY.
I've filed bug FDBK32428 about this. In the bug report I left open for
both alphabetic order and column-number order, but indicated that the
latter is probably what users expects.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Column Order in Table

Hi,
I know that physical order of a column is not important in tables but I like
to know if it is possible to force column order when I alter a table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila> I know that physical order of a column is not important in tables but I
like
> to know if it is possible to force column order when I alter a table to
add
> column or change the order later.
Only by dropping the table and re-creating it.
> It seems that the orders are stored in syscolumns table but I think there
> should be a system sp/func to alter that.
No, do not attempt this. Why do you care where the column is?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||In addition to what Aaron said...
From BOL
System tables should not be altered directly by any user.
Don't do this, ever.
I tried this as an experiment in a test database once and screwed up royally
the database.
Good thing it was a test database as I had no other choice then to delete
it.
There is no system sp/func for this and there probably never will be as the
order of the columns is not important. You can reorder columns in Enterprise
Manager but behind the scenes it drops and recreates the table.
"Leila" <Leilas@.hotpop.com> wrote in message
news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I know that physical order of a column is not important in tables but I
like
> to know if it is possible to force column order when I alter a table to
add
> column or change the order later.
> It seems that the orders are stored in syscolumns table but I think there
> should be a system sp/func to alter that.
> Thanks,
> Leila
>|||> No, do not attempt this. Why do you care where the column is?
Just interested!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uKMreex0EHA.3408@.tk2msftngp13.phx.gbl...
> > I know that physical order of a column is not important in tables but I
> like
> > to know if it is possible to force column order when I alter a table to
> add
> > column or change the order later.
> Only by dropping the table and re-creating it.
> > It seems that the orders are stored in syscolumns table but I think
there
> > should be a system sp/func to alter that.
> No, do not attempt this. Why do you care where the column is?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||FWIW, I was interested too, just on general principle. I know it doesn't
"matter" all that much, but I want to know how to do whatever I want with
the data.
I knew it was possible to do it in the Enterprise Manager, but I didn't know
it was dropping and recreating the table. Thanks.
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
> In addition to what Aaron said...
> From BOL
> System tables should not be altered directly by any user.
> Don't do this, ever.
> I tried this as an experiment in a test database once and screwed up
> royally
> the database.
> Good thing it was a test database as I had no other choice then to delete
> it.
> There is no system sp/func for this and there probably never will be as
> the
> order of the columns is not important. You can reorder columns in
> Enterprise
> Manager but behind the scenes it drops and recreates the table.
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> I know that physical order of a column is not important in tables but I
> like
>> to know if it is possible to force column order when I alter a table to
> add
>> column or change the order later.
>> It seems that the orders are stored in syscolumns table but I think there
>> should be a system sp/func to alter that.
>> Thanks,
>> Leila
>>
>|||If you want to see the code:
In Enterprise manager open a table in design mode
Change the order of a column (don't save the change)
Click the "Save Change Script" icon
In one of my base tables, this produced 287 lines of code.
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:u0A2Zx$0EHA.2884@.TK2MSFTNGP11.phx.gbl...
> FWIW, I was interested too, just on general principle. I know it doesn't
> "matter" all that much, but I want to know how to do whatever I want with
> the data.
> I knew it was possible to do it in the Enterprise Manager, but I didn't
know
> it was dropping and recreating the table. Thanks.
>
> "raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
> news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
> > In addition to what Aaron said...
> >
> > From BOL
> > System tables should not be altered directly by any user.
> >
> > Don't do this, ever.
> > I tried this as an experiment in a test database once and screwed up
> > royally
> > the database.
> > Good thing it was a test database as I had no other choice then to
delete
> > it.
> >
> > There is no system sp/func for this and there probably never will be as
> > the
> > order of the columns is not important. You can reorder columns in
> > Enterprise
> > Manager but behind the scenes it drops and recreates the table.
> >
> > "Leila" <Leilas@.hotpop.com> wrote in message
> > news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
> >> Hi,
> >> I know that physical order of a column is not important in tables but I
> > like
> >> to know if it is possible to force column order when I alter a table to
> > add
> >> column or change the order later.
> >> It seems that the orders are stored in syscolumns table but I think
there
> >> should be a system sp/func to alter that.
> >> Thanks,
> >> Leila
> >>
> >>
> >
> >
>|||Even more useful info! Thanks again.
You might have noticed, I'm new to SQL Server (from FoxPro).
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:uQotB3$0EHA.1188@.tk2msftngp13.phx.gbl...
> If you want to see the code:
> In Enterprise manager open a table in design mode
> Change the order of a column (don't save the change)
> Click the "Save Change Script" icon
> In one of my base tables, this produced 287 lines of code.
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:u0A2Zx$0EHA.2884@.TK2MSFTNGP11.phx.gbl...
>> FWIW, I was interested too, just on general principle. I know it doesn't
>> "matter" all that much, but I want to know how to do whatever I want with
>> the data.
>> I knew it was possible to do it in the Enterprise Manager, but I didn't
> know
>> it was dropping and recreating the table. Thanks.
>>
>> "raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
>> news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
>> > In addition to what Aaron said...
>> >
>> > From BOL
>> > System tables should not be altered directly by any user.
>> >
>> > Don't do this, ever.
>> > I tried this as an experiment in a test database once and screwed up
>> > royally
>> > the database.
>> > Good thing it was a test database as I had no other choice then to
> delete
>> > it.
>> >
>> > There is no system sp/func for this and there probably never will be as
>> > the
>> > order of the columns is not important. You can reorder columns in
>> > Enterprise
>> > Manager but behind the scenes it drops and recreates the table.
>> >
>> > "Leila" <Leilas@.hotpop.com> wrote in message
>> > news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
>> >> Hi,
>> >> I know that physical order of a column is not important in tables but
>> >> I
>> > like
>> >> to know if it is possible to force column order when I alter a table
>> >> to
>> > add
>> >> column or change the order later.
>> >> It seems that the orders are stored in syscolumns table but I think
> there
>> >> should be a system sp/func to alter that.
>> >> Thanks,
>> >> Leila
>> >>
>> >>
>> >
>> >
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C4D4C1.127F3640
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Anthony,
It's arguable that DBMSs would be better if they stayed closer to the =relational model, but since column ordinal positions are part of the =ANSI SQL standard, I think it's appropriate to provide that attribute of =a column in metadata. If it's a downfall of anything, it's a downfall =of ANSI SQL, not each DBMS. SQL Server doesn't expose physical column =order to the user, and while it doesn't, it could even vary from row to =row without the user knowing (it doesn't, but it could, so long as =select * queries returned columns in order of their (virtual, and stored =in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical =characteristic of the database. They are part of the metadata, just =like column types and names, and they aren't a reflection of the =physical layout of the data. Microsoft does document the way in which =column data is stored within a row, since it can be beneficial to know =for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are =stored before variable-length columns, for example (regardless of =ordinal position) and without looking it up, I'm not sure whether the =ordinal position attribute of a column (which is exposed in the ANSI =INFORMATION_SCHEMA metadata views) is even respected within those two =categories. Long data (text, ntext, image), can even be stored out of =the row's data page, and tables with a non-clustered index store some =column data in more than one place. The physical layout of data in a =SQL Server table is not exposed to the user.
That said, I agree that columns should almost always be named, and the =few T-SQL features that rely on the column's ordinal position should be =avoided if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:OZecUbL1EHA.1652@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, =under the hood. That's laudable; however, because SS is a physical =system, it is limited to physical media and, thus, must store =information about column order because, as a physical system, it must =manipulate the information at the physical level.
The downfall of most DBMS products is that they often expose certain =physical characteristics that should have been shielded from =end-users...even Database Administrators, Engineers, and Developers. =This is just another case where this is not so.
Any DML should manipulate column-level information on a NAME basis =only, and, thus, ordinal position is irrelevant. Therefore, any attempt =to alter this is meaningless. Now, you can affect the outcome but, as =the other respondents have said, you must drop and recreate or create a =temp table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
-- "Leila" <Leilas@.hotpop.com> wrote in message =news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables =but I like
to know if it is possible to force column order when I alter a table =to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think =there
should be a system sp/func to alter that.
Thanks,
Leila
--=_NextPart_000_001D_01C4D4C1.127F3640
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Anthony,
It's arguable that DBMSs would be better if =they stayed closer to the relational model, but since column ordinal positions are =part of the ANSI SQL standard, I think it's appropriate to provide that =attribute of a column in metadata. If it's a downfall of anything, it's a =downfall of ANSI SQL, not each DBMS. SQL Server doesn't expose physical =column order to the user, and while it doesn't, it could even vary from row to =row without the user knowing (it doesn't, but it could, so long as select * =queries returned columns in order of their (virtual, and stored in metadata, not =physical) ordinal position.
SQL Server's column ordinal positions are not an =exposed physical characteristic of the database. They are part of the =metadata, just like column types and names, and they aren't a reflection of =the physical layout of the data. Microsoft does document the way in =which column data is stored within a row, since it can be beneficial to =know for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save perhaps for =some undocumented DBCC commands. Fixed-length columns are stored before =variable-length columns, for example (regardless of ordinal position) =and without looking it up, I'm not sure whether the ordinal =position attribute of a column (which is exposed in the ANSI INFORMATION_SCHEMA metadata =views) is even respected within those two categories. Long data (text, =ntext, image), can even be stored out of the row's data page, and tables with a =non-clustered index store some column data in more than one place. =The physical layout of data in a SQL Server table is not exposed to the user.
That said, I agree that columns should almost =always be named, and the few T-SQL features that rely on the column's ordinal =position should be avoided if at all possible.
Steve Kass
Drew University
"AnthonyThomas" wrote in message news:OZecUbL1EHA.1652=@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, under the =hood. That's laudable; however, because SS is a physical system, it is =limited to physical media and, thus, must store information about column order =because, as a physical system, it must manipulate the information at the =physical level.

The =downfall of most DBMS products is that they often expose certain physical =characteristics that should have been shielded from end-users...even Database =Administrators, Engineers, and Developers. This is just another case where this =is not so.

Any DML =should manipulate column-level information on a NAME basis only, and, thus, =ordinal position is irrelevant. Therefore, any attempt to alter this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp =table, migrate the data, drop the original, and, then, rename the temp. =This is how the Visual Database designer does it.

Sincerely,

Anthony = Thomas

--
"Leila" wrote in =message news:eZRp6Ox0EHA.3616=@.TK2MSFTNGP11.phx.gbl...Hi,I know that physical order of a column is not important in tables but =I liketo know if it is possible to force column order when I alter =a table to addcolumn or change the order later.It seems that the =orders are stored in syscolumns table but I think thereshould be a system =sp/func to alter that.Thanks,Leila

=
--=_NextPart_000_001D_01C4D4C1.127F3640--|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C4D6C0.C2567F50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I do like to have some semblance of order in column order, if only for =display and design purposes. For instance, it's a lot easier for me to =deal with a table if the columns name, address, city, state, and zip =appear next to each other and in that order.
"Steve Kass" <skass@.drew.edu> wrote in message =news:eSW%230rO1EHA.2192@.TK2MSFTNGP14.phx.gbl...
Anthony,
It's arguable that DBMSs would be better if they stayed closer to =the relational model, but since column ordinal positions are part of the =ANSI SQL standard, I think it's appropriate to provide that attribute of =a column in metadata. If it's a downfall of anything, it's a downfall =of ANSI SQL, not each DBMS. SQL Server doesn't expose physical column =order to the user, and while it doesn't, it could even vary from row to =row without the user knowing (it doesn't, but it could, so long as =select * queries returned columns in order of their (virtual, and stored =in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical =characteristic of the database. They are part of the metadata, just =like column types and names, and they aren't a reflection of the =physical layout of the data. Microsoft does document the way in which =column data is stored within a row, since it can be beneficial to know =for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are =stored before variable-length columns, for example (regardless of =ordinal position) and without looking it up, I'm not sure whether the =ordinal position attribute of a column (which is exposed in the ANSI =INFORMATION_SCHEMA metadata views) is even respected within those two =categories. Long data (text, ntext, image), can even be stored out of =the row's data page, and tables with a non-clustered index store some =column data in more than one place. The physical layout of data in a =SQL Server table is not exposed to the user.
That said, I agree that columns should almost always be named, and =the few T-SQL features that rely on the column's ordinal position should =be avoided if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:OZecUbL1EHA.1652@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of =SS, under the hood. That's laudable; however, because SS is a physical =system, it is limited to physical media and, thus, must store =information about column order because, as a physical system, it must =manipulate the information at the physical level.
The downfall of most DBMS products is that they often expose certain =physical characteristics that should have been shielded from =end-users...even Database Administrators, Engineers, and Developers. =This is just another case where this is not so.
Any DML should manipulate column-level information on a NAME basis =only, and, thus, ordinal position is irrelevant. Therefore, any attempt =to alter this is meaningless. Now, you can affect the outcome but, as =the other respondents have said, you must drop and recreate or create a =temp table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
-- "Leila" <Leilas@.hotpop.com> wrote in message =news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables =but I like
to know if it is possible to force column order when I alter a =table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I =think there
should be a system sp/func to alter that.
Thanks,
Leila
--=_NextPart_000_0008_01C4D6C0.C2567F50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I do like to have some =semblance of order in column order, if only for display and design purposes. For instance, =it's a lot easier for me to deal with a table if the columns name, address, =city, state, and zip appear next to each other and in that order.
"Steve Kass" wrote in message news:eSW%230rO1EHA.=2192@.TK2MSFTNGP14.phx.gbl...
Anthony,

It's arguable that DBMSs would be better if =they stayed closer to the relational model, but since column ordinal =positions are part of the ANSI SQL standard, I think it's appropriate to =provide that attribute of a column in metadata. If it's a downfall of =anything, it's a downfall of ANSI SQL, not each DBMS. SQL Server doesn't expose = physical column order to the user, and while it doesn't, it could even =vary from row to row without the user knowing (it doesn't, but it could, so =long as select * queries returned columns in order of their (virtual, and =stored in metadata, not physical) ordinal position.

SQL Server's column ordinal positions are not an =exposed physical characteristic of the database. They are part of the =metadata, just like column types and names, and they aren't a reflection of =the physical layout of the data. Microsoft does document the way in =which column data is stored within a row, since it can be beneficial to =know for troubleshooting, design, optimization, and so on. But no =T-SQL language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are stored =before variable-length columns, for example (regardless of ordinal position) =and without looking it up, I'm not sure whether the ordinal position attribute of a column (which is exposed in the ANSI INFORMATION_SCHEMA metadata views) is even respected within those two categories. Long data (text, ntext, image), can even be stored =out of the row's data page, and tables with a non-clustered index store some =column data in more than one place. The physical layout of data in a =SQL Server table is not exposed to the user.

That said, I agree that columns should =almost always be named, and the few T-SQL features that rely on the column's ordinal = position should be avoided if at all possible.

Steve Kass
Drew University

"AnthonyThomas" wrote in message news:OZecUbL1EHA.1652=@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, under the =hood. That's laudable; however, because SS is a physical system, it is =limited to physical media and, thus, must store information about column order =because, as a physical system, it must manipulate the information at the =physical level.

The =downfall of most DBMS products is that they often expose certain physical characteristics that should have been shielded from end-users...even = Database Administrators, Engineers, and Developers. This is =just another case where this is not so.

Any =DML should manipulate column-level information on a NAME basis only, and, thus, =ordinal position is irrelevant. Therefore, any attempt to alter this =is meaningless. Now, you can affect the outcome but, as the other = respondents have said, you must drop and recreate or create a temp =table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.

Sincerely,

Anthony Thomas

--
"Leila" wrote =in message news:eZRp6Ox0EHA.3616=@.TK2MSFTNGP11.phx.gbl...Hi,I know that physical order of a column is not important in tables =but I liketo know if it is possible to force column order when I =alter a table to addcolumn or change the order later.It seems that =the orders are stored in syscolumns table but I think thereshould =be a system sp/func to alter =that.Thanks,Leila

--=_NextPart_000_0008_01C4D6C0.C2567F50--|||>>
I do like to have some semblance of order in column order, if only for
display and design purposes. For instance, it's a lot easier for me to deal
with a table if the columns name, address, city, state, and zip appear next
to each other and in that order.
Okay, so if you build the table and somehow forget to include the address
column, then drop the table and re-create it. They're not going to change
the ALTER TABLE command for cosmetics.
--
http://www.aspfaq.com/
(Reverse address to reply.)

Column Order in Table

Hi,
I know that physical order of a column is not important in tables but I like
to know if it is possible to force column order when I alter a table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila
> I know that physical order of a column is not important in tables but I
like
> to know if it is possible to force column order when I alter a table to
add
> column or change the order later.
Only by dropping the table and re-creating it.

> It seems that the orders are stored in syscolumns table but I think there
> should be a system sp/func to alter that.
No, do not attempt this. Why do you care where the column is?
http://www.aspfaq.com/
(Reverse address to reply.)
|||In addition to what Aaron said...
From BOL
System tables should not be altered directly by any user.
Don't do this, ever.
I tried this as an experiment in a test database once and screwed up royally
the database.
Good thing it was a test database as I had no other choice then to delete
it.
There is no system sp/func for this and there probably never will be as the
order of the columns is not important. You can reorder columns in Enterprise
Manager but behind the scenes it drops and recreates the table.
"Leila" <Leilas@.hotpop.com> wrote in message
news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I know that physical order of a column is not important in tables but I
like
> to know if it is possible to force column order when I alter a table to
add
> column or change the order later.
> It seems that the orders are stored in syscolumns table but I think there
> should be a system sp/func to alter that.
> Thanks,
> Leila
>
|||> No, do not attempt this. Why do you care where the column is?
Just interested!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uKMreex0EHA.3408@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> like
> add
> Only by dropping the table and re-creating it.
there
> No, do not attempt this. Why do you care where the column is?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||FWIW, I was interested too, just on general principle. I know it doesn't
"matter" all that much, but I want to know how to do whatever I want with
the data.
I knew it was possible to do it in the Enterprise Manager, but I didn't know
it was dropping and recreating the table. Thanks.
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
> In addition to what Aaron said...
> From BOL
> System tables should not be altered directly by any user.
> Don't do this, ever.
> I tried this as an experiment in a test database once and screwed up
> royally
> the database.
> Good thing it was a test database as I had no other choice then to delete
> it.
> There is no system sp/func for this and there probably never will be as
> the
> order of the columns is not important. You can reorder columns in
> Enterprise
> Manager but behind the scenes it drops and recreates the table.
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
> like
> add
>
|||If you want to see the code:
In Enterprise manager open a table in design mode
Change the order of a column (don't save the change)
Click the "Save Change Script" icon
In one of my base tables, this produced 287 lines of code.
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:u0A2Zx$0EHA.2884@.TK2MSFTNGP11.phx.gbl...
> FWIW, I was interested too, just on general principle. I know it doesn't
> "matter" all that much, but I want to know how to do whatever I want with
> the data.
> I knew it was possible to do it in the Enterprise Manager, but I didn't
know[vbcol=seagreen]
> it was dropping and recreating the table. Thanks.
>
> "raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
> news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
delete[vbcol=seagreen]
there
>
|||Even more useful info! Thanks again.
You might have noticed, I'm new to SQL Server (from FoxPro).
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:uQotB3$0EHA.1188@.tk2msftngp13.phx.gbl...
> If you want to see the code:
> In Enterprise manager open a table in design mode
> Change the order of a column (don't save the change)
> Click the "Save Change Script" icon
> In one of my base tables, this produced 287 lines of code.
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:u0A2Zx$0EHA.2884@.TK2MSFTNGP11.phx.gbl...
> know
> delete
> there
>
|||Understand that you just want to know more about the clockworks of SS, under the hood. That's laudable; however, because SS is a physical system, it is limited to physical media and, thus, must store information about column order because, as a physical system, it must manipulate the information at the physical level.
The downfall of most DBMS products is that they often expose certain physical characteristics that should have been shielded from end-users...even Database Administrators, Engineers, and Developers. This is just another case where this is not so.
Any DML should manipulate column-level information on a NAME basis only, and, thus, ordinal position is irrelevant. Therefore, any attempt to alter this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp table, migrate the data, drop the original, and, then, rename the temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas

"Leila" <Leilas@.hotpop.com> wrote in message news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables but I like
to know if it is possible to force column order when I alter a table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila
|||Anthony,
It's arguable that DBMSs would be better if they stayed closer to the relational model, but since column ordinal positions are part of the ANSI SQL standard, I think it's appropriate to provide that attribute of a column in metadata. If it's a downfall of anything, it's a downfall of ANSI SQL, not each DBMS. SQL Server doesn't expose physical column order to the user, and while it doesn't, it could even vary from row to row without the user knowing (it doesn't, but it could, so long as select * queries returned columns in order of their (virtual, and stored in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical characteristic of the database. They are part of the metadata, just like column types and names, and they aren't a reflection of the physical layout of the data. Microsoft does document the way in which column data is stored within a row, since it can be beneficial to know for troubleshooting, design, optimization, and so on. But no T-SQL language constructs exist to access the information that way, save perhaps for some undocumented DBCC commands. Fixed-length columns are stored before variable-length columns, for example (regardless of ordinal position) and without looking it up, I'm not sure whether the ordinal position attribute of a column (which is exposed in the ANSI INFORMATION_SCHEMA metadata views) is even respected within those two categories. Long data (text, ntext, image), can even be stored out of the row's data page, and tables with a non-clustered index store some column data in more than one place. The physical layout of data in a SQL Server table is not exposed to the user.
That said, I agree that columns should almost always be named, and the few T-SQL features that rely on the column's ordinal position should be avoided if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:OZecUbL1EHA.1652@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, under the hood. That's laudable; however, because SS is a physical system, it is limited to physical media and, thus, must store information about column order because, as a physical system, it must manipulate the information at the physical level.
The downfall of most DBMS products is that they often expose certain physical characteristics that should have been shielded from end-users...even Database Administrators, Engineers, and Developers. This is just another case where this is not so.
Any DML should manipulate column-level information on a NAME basis only, and, thus, ordinal position is irrelevant. Therefore, any attempt to alter this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp table, migrate the data, drop the original, and, then, rename the temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas

"Leila" <Leilas@.hotpop.com> wrote in message news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables but I like
to know if it is possible to force column order when I alter a table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila
|||I do like to have some semblance of order in column order, if only for display and design purposes. For instance, it's a lot easier for me to deal with a table if the columns name, address, city, state, and zip appear next to each other and in that order.
"Steve Kass" <skass@.drew.edu> wrote in message news:eSW%230rO1EHA.2192@.TK2MSFTNGP14.phx.gbl...
Anthony,
It's arguable that DBMSs would be better if they stayed closer to the relational model, but since column ordinal positions are part of the ANSI SQL standard, I think it's appropriate to provide that attribute of a column in metadata. If it's a downfall of anything, it's a downfall of ANSI SQL, not each DBMS. SQL Server doesn't expose physical column order to the user, and while it doesn't, it could even vary from row to row without the user knowing (it doesn't, but it could, so long as select * queries returned columns in order of their (virtual, and stored in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical characteristic of the database. They are part of the metadata, just like column types and names, and they aren't a reflection of the physical layout of the data. Microsoft does document the way in which column data is stored within a row, since it can be beneficial to know for troubleshooting, design, optimization, and so on. But no T-SQL language constructs exist to access the information that way, save perhaps for some undocumented DBCC commands. Fixed-length columns are stored before variable-length columns, for example (regardless of ordinal position) and without looking it up, I'm not sure whether the ordinal position attribute of a column (which is exposed in the ANSI INFORMATION_SCHEMA metadata views) is even respected within those two categories. Long data (text, ntext, image), can even be stored out of the row's data page, and tables with a non-clustered index store some column data in more than one place. The physical layout of data in a SQL Server table is not exposed to the user.
That said, I agree that columns should almost always be named, and the few T-SQL features that rely on the column's ordinal position should be avoided if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message news:OZecUbL1EHA.1652@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, under the hood. That's laudable; however, because SS is a physical system, it is limited to physical media and, thus, must store information about column order because, as a physical system, it must manipulate the information at the physical level.
The downfall of most DBMS products is that they often expose certain physical characteristics that should have been shielded from end-users...even Database Administrators, Engineers, and Developers. This is just another case where this is not so.
Any DML should manipulate column-level information on a NAME basis only, and, thus, ordinal position is irrelevant. Therefore, any attempt to alter this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp table, migrate the data, drop the original, and, then, rename the temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas

"Leila" <Leilas@.hotpop.com> wrote in message news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables but I like
to know if it is possible to force column order when I alter a table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila

Saturday, February 25, 2012

column reference error

column reference as parameter of sp

Column Ranking on SS 2000 and/or Reporting Services report

Column Query with a Variable

Column Properties Window Bug in Management Studio?

Column prefix doesnt match with a table name error

column position sql table

Column placement, widths, etc.

Column permissions

Column Ordering in SELECT statement

Column Ordering in SELECT statement

Column Ordering in SELECT statement

Column order/presentation in virtual table (result set from viewor UDF)

Column order issue

Column order issue

Column order issue

Column Order in Table

Column Order in Table

Collation problem

Blog Archive

About Me