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
Solved

SELECT clause identifiers...

Posted on 2015-02-08
10
104 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 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL convert date to string 4 34
Query for timesheet application 3 17
Where is the Help Section? 8 25
SQL Recursion schedule 13 14
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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