Tsql - Some | Any Why Are They Same with Different Names

TSQL - SOME | ANY why are they same with different names?

From the ANSI-92 SQL Standard (search for "SOME"). Also here, text

<some> ::= SOME | ANY

I suspect the reason is that SQL language comes from the early 1970s, but had no standard until 1986. The standard would have taken elements of the existing SQL dialects, so we have this SOME/ANY anomaly.

This blog article by Brad Schulz explains some differences: "ALL, ANY, and SOME: The Three Stooges"

How to deal with SQL column names that look like SQL keywords?

Wrap the column name in brackets like so, from becomes [from].

select [from] from table;

It is also possible to use the following (useful when querying multiple tables):

select table.[from] from table;

Can there be constraints with the same name in a DB?

No - a constraint is a database object as well, and thus its name needs to be unique.

Try adding e.g. the table name to your constraint, that way it'll be unique.

CREATE TABLE BankAccount
(
    BankAccountID   INT            PRIMARY KEY,
    EmployerCode    VARCHAR(20)    NOT NULL,
    Amount          MONEY          NOT NULL,
    CONSTRAINT FK_BankAccount_Employer 
        FOREIGN KEY (EmployerCode) REFERENCES Employer
)

We basically use "FK_"(child table)_(parent table)" to name the constraints and are quite happy with this naming convention.

Information from MSDN

That constraint names have to be unique to the schema (ie. two different schemas in the same database can both contain a constraint with the same name) is not explicitly documented. Rather you need to assume the identifiers of database objects must be unique within the containing schema unless specified otherwise. So the constraint name is defined as:

Is the name of the constraint. Constraint names must follow the rules for identifiers, except that the name cannot start with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint.

Compare this to the name of an index:

Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.

which explicitly narrows the scope of the identifier.

tsql sql multiple temp tables with same name

Broadly speaking....
Local temp tables (using a single #) are only available to the instance of the procedure that called them or procedures that were called from the same procedure as the one that created the temp table.

If you call a procedure 10 times simultaneously, each has it's own "copy" of the temp table.

From your description it sounds like you just have 4 datasets, each running a Stored Procedure and each of these SP's creates a temp table called #temp. You don't have an SP that calls the other SP's...

Assuming that's correct then it will work fine, in fact you don't even have to drop the temp table at the end of the proc as this done automatically.

The fact that the temp tables are all referenced using the same name doesn't matter, the actual name of the physical temp table stored in the database will not be #temp but something more like tempdb.dbo.#temp_______________000000000123, the point is that they are in fact all unique tables in the database but SQL Server handles the translation to/from the name you created it under automatically.

Select rows with same id but different value in another column

This ought to do it:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM YourTable
    GROUP BY ARIDNR
    HAVING COUNT(*) > 1
)

The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.

Query error with ambiguous column name in SQL

We face this error when we are selecting data from more than one tables by joining tables and at least one of the selected columns (it will also happen when use * to select all columns) exist with same name in more than one tables (our selected/joined tables). In that case we must have to specify from which table we are selecting out column.

Following is a an example solution implementation of concept explained above

I think you have ambiguity only in InvoiceID that exists both in InvoiceLineItems and Invoices Other fields seem distinct. So try This

I just replace InvoiceID with Invoices.InvoiceID

   SELECT 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
    FROM Vendors 
    JOIN Invoices ON (Vendors.VendorID = Invoices.VendorID)
    JOIN InvoiceLineItems ON (Invoices.InvoiceID = InvoiceLineItems.InvoiceID)
    WHERE  
        Invoices.InvoiceID IN
            (SELECT InvoiceSequence 
             FROM InvoiceLineItems
             WHERE InvoiceSequence > 1)
    ORDER BY 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount

You can use tablename.columnnae for all columns (in selection,where,group by and order by) without using any alias. However you can use an alias as guided by other answers

sql query to return differences between two tables

IF you have tables A and B, both with colum C, here are the records, which are present in table A but not in B:

SELECT A.*
FROM A
    LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL

To get all the differences with a single query, a full join must be used, like this:

SELECT A.*, B.*
FROM A
    FULL JOIN B ON (A.C = B.C)
WHERE A.C IS NULL OR B.C IS NULL

What you need to know in this case is, that when a record can be found in A, but not in B, than the columns which come from B will be NULL, and similarly for those, which are present in B and not in A, the columns from A will be null.

How do I compare two columns for equality in SQL Server?

What's wrong with CASE for this? In order to see the result, you'll need at least a byte, and that's what you get with a single character.

CASE WHEN COLUMN1 = COLUMN2 THEN '1' ELSE '0' END AS MyDesiredResult

should work fine, and for all intents and purposes accomplishes the same thing as using a bit field.

Sql Server stored procedure cursor name conflict

Apparently there is a local keyword.

http://www.codeguru.com/cpp/data/mfc_database/sqlserver/article.php/c7177

Why do table names in SQL Server start with dbo?

dbo is the default schema in SQL Server. You can create your own schemas to allow you to better manage your object namespace.

---

Related Topics