Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
MS SQL Server Interview Question:
Is the Order of Columns in the SET Clause Important in MS SQL Server?
Submitted by: AdministratorThe answer is NO. The order of columns in the SET clause of the UPDATE statement is NOT important. You probably already noticed from the previous tutorial. There is a BIG DIFFERENCE among SQL Server, MySQL and Oracle on update multiple columns with previous values:
* SQL Server provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important
* MySQL provides you the updated values on columns names used in new value expressions. So the order of columns in the SET clause is important.
* Oracle provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important
Here is a good tutorial exercise:
SELECT * FROM ggl_links
-- Check the old values
SELECT * FROM ggl_links WHERE url = 'www.globalguideline.com'
GO
id url notes counts created
101 www.globalguideline.com Good. 999 2006-04-30
-- Update "id" before "counts"
UPDATE ggl_links SET id = id+200, counts = id*2
WHERE url = 'www.globalguideline.com'
GO
(1 row(s) affected)
Submitted by: Administrator
* SQL Server provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important
* MySQL provides you the updated values on columns names used in new value expressions. So the order of columns in the SET clause is important.
* Oracle provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important
Here is a good tutorial exercise:
SELECT * FROM ggl_links
-- Check the old values
SELECT * FROM ggl_links WHERE url = 'www.globalguideline.com'
GO
id url notes counts created
101 www.globalguideline.com Good. 999 2006-04-30
-- Update "id" before "counts"
UPDATE ggl_links SET id = id+200, counts = id*2
WHERE url = 'www.globalguideline.com'
GO
(1 row(s) affected)
Submitted by: Administrator
-- Check the new values
SELECT * FROM ggl_links WHERE url = 'www.globalguideline.com'
GO
id url notes counts created
301 www.globalguideline.com Good. 202 2006-04-30
-- Reset to old values
UPDATE ggl_links SET id = 101, counts = 999
WHERE url = 'www.globalguideline.com'
(1 row(s) affected)
Notice that the "id" in the "counts" new value expression is taking the old value of the "id" column, not the updated value, even the "id" column is updated before the "counts" column.
Now try this on a MySQL server, you will get different result.
Submitted by: Administrator
SELECT * FROM ggl_links WHERE url = 'www.globalguideline.com'
GO
id url notes counts created
301 www.globalguideline.com Good. 202 2006-04-30
-- Reset to old values
UPDATE ggl_links SET id = 101, counts = 999
WHERE url = 'www.globalguideline.com'
(1 row(s) affected)
Notice that the "id" in the "counts" new value expression is taking the old value of the "id" column, not the updated value, even the "id" column is updated before the "counts" column.
Now try this on a MySQL server, you will get different result.
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.