SELECT clause identifiers...

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
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
[ ] is T-SQL specific and like Oracle (and any other databases I would guess) because it follows standard SQL also use " ".


Thanks,

Mike
0
 
Steve WalesSenior Database AdministratorCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
With emphases on the bold portions, do you think the query will run okay?

SELECT "Order Amount", "2ndAddress", "!TheReason", "TheReason!", "From" FROM SomeTable;
0
 
Steve WalesSenior Database AdministratorCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
 
PortletPaulfreelancerCommented:
>>"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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Is this paid service?
0
 
PortletPaulfreelancerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.