Here's another way from the question [get table primary key using sql query][Get Primary Key]:



<pre><code>SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
  AND TABLE_NAME = '<i>&lt;your table name&gt;</i>'
</code></pre>

It uses [`KEY_COLUMN_USAGE`][KEY_COLUMN_USAGE] to determine the constraints for a given table  
Then uses [<code>OBJECTPROPERTY(<i>id</i>, 'IsPrimaryKey')</code>][OBJECTPROPERTY] to determine if each is a primary key


[KEY_COLUMN_USAGE]: http://msdn.microsoft.com/en-us/library/ms189789.aspx

[OBJECTPROPERTY]: http://msdn.microsoft.com/en-us/library/ms176105.aspx
[Get Primary Key]: https://stackoverflow.com/q/3930338/1366033

Simple question, how do you list the primary key of a table with T-SQL? I know how to get indexes on a table, but can't remember how to get the PK.

How do you list the primary key of a SQL Server table?

### Problem

You cannot drop the User Defined Table Type as long as it's referenced by anything else:

> Cannot drop type '*dbo.MyTableType*' because it is being referenced by object '*MyStoredProcedure*'. There may be other objects that reference this type.

It would be nice if SSMS gave you a listing of all other objects, but if you don't have many, a partially manual approach might work fine.

### Find Usages


To get a list of all SPs that use your TVP type, you can query `sys.sql_expression_dependencies`

```sql
SELECT OBJECT_NAME(d.referencing_id)
FROM sys.sql_expression_dependencies d
WHERE d.referenced_id = TYPE_ID('MyTableType')
```

### Steps

1. Select all SPs identified above and select `DROP and CREATE to` new window
2. Go through each window, and just highlight / execute the `DROP PROCEDURE` section
3. Now you can select your type and select `DROP and CREATE to` new window and make any changes
4. Go back to the list of SP's windows you opened and execute the `CREATE` section


### Further Reading

* [Altering user-defined table types in SQL Server][2]
* [How to do ALTER TABLE TYPE efficiently][3]
* [List user defined types in a SQL Server database][1]

[1]: https://stackoverflow.com/a/54496/1366033
[2]: https://stackoverflow.com/q/11410722/1366033
[3]: https://dba.stackexchange.com/q/196482/31340

I am not getting option like 'ALTER TO' when am right clicking on TVP

How to ALTER the Table Value Parameter

If you don't like the look of nested subqueries, you can take [GarethD's great solution][GarethD] and convert it into a Common Table Expression (CTE)

    WITH Transactions AS (
        SELECT ProductID, 'Bill' [Type], Amount FROM Bills
          UNION ALL
        SELECT ProductID, 'Ret'  [Type], Amount FROM Returns
    )
    SELECT  p.*,  [Type], Amount
    FROM    Products p
    JOIN    Transactions t
                ON t.ProductID = p.ProductID

### [Here's a demo in SqlFiddle][fiddle]

[GarethD]: https://stackoverflow.com/a/9032048/1366033
[fiddle]: http://sqlfiddle.com/#!18/336a8/1

My Problem is the following:
Say you have three different tables (Products, Bills and Returns)

    | ProductId | Name |
    =====================
    | 1         | Car  |
    
    | BillId | ProductId | Amount |
    =================================
    | 1      | 1         | 100$   |
    | 2      | 1         | 200$   |
    
    | ReturnId | ProductId | Amount |
    ===================================
    | 1        | 1         | 50$    |

How would a SINGLE Query look like to get the following output:

    | Product-ID | Name | Type | Amount |
    =====================================
    | 1          | Car  | Bill | 100$   |
    | 1          | Car  | Bill | 200$   |
    | 1          | Car  | Ret  | 50$    |

I was trying with all sorts of Joins, and somehow I can't get my head around this.
What am I doing wrong?
The closest solution I have found till now was something like this:

    SELECT p.*,
           (CASE
               WHEN b.Amount IS NOT NULL THEN 'Bill'
               ELSE 'Ret'
           END) AS Type,
           COALESCE(b.Amount, r.Amount) AS Amount
    FROM Products p
    LEFT JOIN Bills b ON b.ProductId = p.ProductId
    LEFT JOIN Returns r ON r.ProductId = p.ProductId

One thing is very important to me: The real scenario-query is MUCH bigger, and I don't want to copy/paste the whole logic of there query as if it would be the case when using a Union.

T-SQL: Single-Query Join with either one Table or another

When using [`Group By`][2], the SELECT statement may only contain fields in the Group By statement or aggregate functions (like SUM or COUNT).

You can use [a Common Table Expression (CTE)][1] to consolidate the logic of building the `CardStatus` into a select statement, and then funnel that into a new query which leverages it in both the group by and select statements



    ;WITH BalanceCards AS (
        SELECT a.balance,
               (CASE WHEN c.status='Active'                THEN 'Active'
                     WHEN c.status IN ('Inactive','Other') THEN 'Inact'
                     WHEN c.status IS NULL                 THEN 'No Card' 
                END) AS CardStatus
        FROM people p
        JOIN accounts a ON p.id = a.PeopleFK
        LEFT JOIN cards c ON p.id = c.PeopleFK
    )
    SELECT bs.CardStatus, 
           SUM(bs.balance) As Total, 
           COUNT(*) As [Count]
    FROM BalanceCards bs
    GROUP BY bs.CardStatus

[1]: https://technet.microsoft.com/en-us/library/ms190766
[2]: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

I have 3 tables that I need to join, count/sum data on, and group the data. Below are very simplified versions of my tables.

        People p
    ID 
    1
    2
    3
        Accounts a
    ID | Balance | PeopleFK
    1  | 50      | 1
    2  | 25      | 2
    3  | 15      | 3

        Cards c
    ID | Status | PeopleFK
    1  | Active | 1
    2  | Inact  | 1
    3  | Active | 2
    4  | Other  | 2


What I have so far:

    select sum(a.balance),count(c.*),
    case when c.status='Active' then 'Active'
    case when c.status IN ('Inactive','Other') then 'Inact'
    case when c.status is null then 'No Card' end as 'CardStatus'
    from people p
    join accounts a on p.id=a.PeopleFK
    left join cards c on p.id=c.PeopleFK
    group by c.status


Required result would be something like:

    Balance  |  CardStatus  |  CardCount
    50       |  Active      |  2
    25       | Inactive     |  1
    0        | NoCard       |  1


But my result groups by all of the Card statuses. I understand why this happens but I'm not sure how I can get around this without writing separate queries for each case.

So instead of Inactive and Other grouping together, they group apart.

I'm still toying with this but any suggestions?

Using case, count, and group by in 1 query

This seems like an iteration of the [tag:greatest-n-per-group] problem

I'm not quite certain what constraints you're looking to impose

1. Largest Date
2. Most Recent Date (but not in future)
3. Closest Date to today (past or present)

Here's an example table and which row we'd want if queried on **6/3/2019**:

```
| Item | RequiredDate | Price |
|------|--------------|-------|
| A    | 2019-05-29   |    10 |
| A    | 2019-06-01   |    20 | <-- #2
| A    | 2019-06-04   |    30 | <-- #3
| A    | 2019-06-05   |    40 | <-- #1
| B    | 2019-06-01   |    80 |
```


But I'm going to guess you're looking for **#2**

We can identify we the row / largest date by grouping by `item` and using an aggregate operation like `MAX` on each group

```
SELECT o.Item, MAX(o.RequiredDate) AS MostRecentDt 
FROM Orders o
WHERE o.RequiredDate <= GETDATE()
GROUP BY o.Item
```

Which returns this:

```
| Item | MostRecentDt |
|------|--------------|
| A    | 2019-05-29   |
| A    | 2019-06-01   |
| B    | 2019-06-01   |
```

However, once we've grouped by that record, the trouble is then in joining back to the original table to get the full row/record in order to select any other information not part of the original `GROUP BY` statement

Using `ROW_NUMBER` we can sort elements in a set, and indicate their order (highest...lowest)

```sql
SELECT *, ROW_NUMBER() OVER(PARTITION BY Item ORDER BY RequiredDate DESC) rn
FROM Orders o
WHERE o.RequiredDate <= GETDATE()
```

```
| Item | RequiredDate | Price | rn |
|------|--------------|-------|----|
| A    | 2019-05-29   |    10 | 1  |
| A    | 2019-06-01   |    20 | 2  |
| B    | 2019-06-01   |    80 | 1  |
```

Since we've sorted `DESC`, now we just want to query this group to get the most recent values per group (`rn=1`)

```sql
WITH OrderedPastItems AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Item ORDER BY RequiredDate DESC) rn
  FROM Orders o
  WHERE o.RequiredDate <= GETDATE()
)
SELECT * 
FROM OrderedPastItems
WHERE rn = 1
```

Here's a [**MCVE in SQL Fiddle**][1]

**Further Reading**:

* [SQL selecting rows by most recent date][2]
* [Select row with most recent date per user][3]

[1]: http://sqlfiddle.com/#!18/495c0/11
[2]: https://stackoverflow.com/q/189213/1366033
[3]: https://stackoverflow.com/q/17038193/1366033

I am trying to get the closest date for item no and price based on the current date. The query is giving me output, but not the way I want. 

There is a different price for the same item and it's not filtering. 

Here's my query:

```
SELECT distinct [ITEM_NO]
     ,min(REQUIRED_DATE) as Date
     ,[PRICE]
  FROM [DATA_WAREHOUSE].[app].[OHCMS_HOPS_ORDERS]
  where (REQUIRED_DATE) >= GETDATE() and PRICE is not null
  group by ITEM_NO,PRICE
  order by ITEM_NO
```

[![Current vs Expected Output][1]][1]

Any Ideas?

  [1]: https://i.stack.imgur.com/pW29k.png

Query item with closest date based on current date

I'm trying to make it easier to read some SQL code where we need to hardcode in some large numbers

I'd like to do something like this:

```sql
SELECT 3_800_000
```

Which, according to [this post][3], is really treated like this:

```sql
SELECT 3 _800_000
SELECT 3 AS [_800_000]
```
JS allows [numeric separators][1]

```
let x = 1000000000000
let y = 1_000_000_000_000
console.log(x==y) // true
```

Also, C# added [digit separators][2] in 7.0 as well

```cs
// These are equivalent.
var bigNumber      = 123456789012345678;
var bigNumberSplit = 123_456_789_012_345_678;
```

Is something similar possible in T-SQL?

**Note**: I'm not looking for a way to format the output, I'm looking for a way to make the source code easier to read for big numbers

[1]: https://github.com/tc39/proposal-numeric-separator
[2]: https://learn.microsoft.com/en-us/dotnet/csharp/whats-new/csharp-version-history#c-version-70:~:text=Binary%20Literals%20and%20Digit%20Separators
[3]: https://stackoverflow.com/q/35988034/1366033