Solved

SELECT clause identifiers...

Posted on 2015-02-08
10
105 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 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 22

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 22

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 48

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 48

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

756 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