?
Solved

SELECT clause identifiers...

Posted on 2015-02-08
10
Medium Priority
?
118 Views
Last Modified: 2015-02-08
Regular identifiers:
- OrderAmount


Irregular identifiers:   <-- Q1: Is use of the word irregular correct here?
- Order Amount         <-- has space
- 2ndAddress            <-- starts with a number
- !TheReason            <-- starts with sign other than (_, @, or #)
- TheReason!            <-- it contains sign other than (_, @, or #)
- From                       <-- it is a T-SQL reserved word.

Q2: Are the SELECT clauses bellow correct?

SELECT "Order Amount", "2ndAddress", "!TheReason", "TheReason!", "From" FROM SomeTable

or

SELECT [Order Amount], [2ndAddress], [!TheReason], [TheReason!], [From] FROM SomeTable
0
Comment
Question by:Mike Eghtebas
  • 5
  • 3
  • 2
10 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 2000 total points
ID: 40596998
The documentation defines what a regular identifier is:

https://msdn.microsoft.com/en-us/library/ms175874.aspx

It doesn't use the word irregular, but the opposite of regular is irregular, so ....  I guess it fits.

Delimited identifiers in SQL Server use square brackets.  So the second version is correct.

(FYI, Oracle uses double quotes to cater for non standard identifier names, SQL Server uses [ ])
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40597014
[ ] is T-SQL specific and like Oracle (and any other databases I would guess) because it follows standard SQL also use " ".


Thanks,

Mike
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 40597024
Ah, you're right, I didn't read the delimiters part of the documentation completely :)   I've just always used [ ] when needed, had never seen " " used in T-SQL.

You learn something new everyday.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40597033
With emphases on the bold portions, do you think the query will run okay?

SELECT "Order Amount", "2ndAddress", "!TheReason", "TheReason!", "From" FROM SomeTable;
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 40597156
When in doubt, test it out!

Create table SomeTable ("Order Amount" char(2), "2ndAddress" char(2), "!TheReason" char(2),
                        "TheReason!" char(2), "From" char(2))
go

Command(s) completed successfully.

insert into SomeTable values ('A', 'B', 'C', 'D', 'E')
go
(1 row(s) affected)

SELECT "Order Amount", "2ndAddress", "!TheReason", "TheReason!", "From" FROM SomeTable
go

Order Amount 2ndAddress !TheReason TheReason! From
------------ ---------- ---------- ---------- ----
A            B          C          D          E 

Open in new window


In general, avoid irregular object names like the plague.   They just cause more problems than they are worth, in my opinion.  If not now, down the road when someone else has to deal with them.
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40597179
I agree with you on staying away from irregular identifiers. Also, I didn't have access to SQL Server to test this. Furthermore, I had an earlier okay from you on this. I just wanted to double check it.

Thank you for the test.

Mike
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40597629
>>"I didn't have access to SQL Server to test this. "

then try http://sqlfidde.com 
there is a drop down to choose dbms type; it supplies both mssql 2008 and 2012v.handy
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40597700
Is this paid service?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40597720
No. It is limited by 8000 characters of ddl including inserts. So its great for small tests but not for anything needing large amounts of data
0
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40597723
Thank you.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question