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 = '&lt;your table name&gt;'
</code></pre>

It uses [`KEY_COLUMN_USAGE`][KEY_COLUMN_USAGE] to determine the constraints for a given table 
Then uses [<code>OBJECTPROPERTY(id, '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?

Adapted from the post on [How to Find Size of All the Indexes on the Database][1], you can use:

```sql
SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName,
	   OBJECT_NAME(i.OBJECT_ID) AS TableName,
	   i.[name] AS IndexName,
	   i.index_id AS IndexID,
	   8 * SUM(a.used_pages) AS [Indexsize(KB)]
FROM sys.indexes i
JOIN sys.partitions p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID, i.index_id, i.[name]
ORDER BY OBJECT_NAME(i.OBJECT_ID), i.index_id
```

Which uses the following tables:


* [`sys.indexes`][2]
* [`sys.partitions`][3]
* [`sys.allocation_units`][4]


[1]: https://blog.sqlauthority.com/2016/11/13/find-size-indexes-database-interview-question-week-097/
[2]: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql
[3]: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql
[4]: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-allocation-units-transact-sql


I have an index -- let's call it `IX_MY_INDEX` -- in a SQL Server table (both compatibility modes 80 and 90) that I would like to determine the size of. How do I do this?

**Update:** Allain Lalonde's second solution below only works when the compatibility mode is set to 90; however, the particular database I am working on is in compatibility mode 80. Does anyone have a solution for compatibility mode 80?

How do you determine the size of an index in SQL Server?

If there are a lot of data points, you can also [group ranges together][1] like this:



 SELECT FLOOR(grade/5.00)*5 As Grade, 
 COUNT(*) AS [Grade Count]
 FROM TableName
 GROUP BY FLOOR(Grade/5.00)*5
 ORDER BY 1

Additionally, if you wanted to label the full range, you can get the floor and ceiling ahead of time with a CTE. 



 With GradeRanges As (
 SELECT FLOOR(Score/5.00)*5 As GradeFloor, 
 FLOOR(Score/5.00)*5 + 4 As GradeCeiling
 FROM TableName
 )
 SELECT GradeFloor,
 CONCAT(GradeFloor, ' to ', GradeCeiling) AS GradeRange,
 COUNT(*) AS [Grade Count]
 FROM GradeRanges
 GROUP BY GradeFloor, CONCAT(GradeFloor, ' to ', GradeCeiling)
 ORDER BY GradeFloor

**Note**: In some SQL engines, you can `GROUP BY` an Ordinal Column Index, but with MS SQL, if you want it in the `SELECT` statement, you're going to need to group by it also, hence copying the Range into the Group Expression as well.

**Option 2**: You could use [case statements to selectively count values into arbitrary bins and then unpivot them][2] to get a row by row count of included values

[1]: https://web.archive.org/web/20180205094300/https://www.wagonhq.com/sql-tutorial/creating-a-histogram-sql "ARCHIVE of https:&#47;&#47;www.wagonhq.com/sql-tutorial/creating-a-histogram-sql"
[2]: https://stackoverflow.com/a/52105218/1366033

Let's say I have a database column 'grade' like this:

    |grade|
    |    1|
    |    2|
    |    1|
    |    3|
    |    4|
    |    5|

Is there a non-trivial way in SQL to generate a histogram like this?

    |2,1,1,1,1,0|

where 2 means the grade 1 occurs twice, the 1s mean grades {2..5} occur once and 0 means grade 6 does not occur at all.

I don't mind if the histogram is one row per count. 

If that matters, the database is SQL Server accessed by a perl CGI through unixODBC/FreeTDS.

**EDIT:** Thanks for your quick replies! It is okay if non-existing values (like grade 6 in the example above) do not occur as long as I can make out which histogram value belongs to which grade.

Generating a histogram from column values in a database

### 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

You *could* [save the results into a temp table][0], but it would be even better to go directly to the source on `master.dbo.sysprocesses`. 

Here's a query that will return almost the exact same result as [`sp_who2`][1]:



 SELECT spid,
 sp.[status],
 loginame [Login],
 hostname, 
 blocked BlkBy,
 sd.name DBName, 
 cmd Command,
 cpu CPUTime,
 physical_io DiskIO,
 last_batch LastBatch,
 [program_name] ProgramName 
 FROM master.dbo.sysprocesses sp 
 JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
 ORDER BY spid 

Now you can easily add any `ORDER BY` or `WHERE` clauses you like to get meaningful output.

----

Alternatively, you might consider using [**Activity Monitor**][2] in SSMS (<kbd>Ctrl</kbd> + <kbd>Alt</kbd> + <kbd>A</kbd>) as well


[0]: http://www.sqlmatters.com/Articles/sp_who2%20-%20filtering%20and%20sorting%20the%20results.aspx
[1]: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-sql
[2]: https://stackoverflow.com/q/1248423/1366033

Under SQL Server, is there an easy way to filter the output of sp_who2? Say I wanted to just show rows for a certain database, for example.

SQL Server: Filter output of sp_who2

You can check whether a transaction is already open by checking if `cmd.Transaction` is `null`. 

Then wrap your code accordingly so if a transaction is already open, then the calling function owns that transaction and will commit it / roll it back appropriately. 


 
 //begin transaction unless one was already started
	bool newTransaction = cmd.Transaction == null;
	if (newTransaction) cmd.Transaction = cmd.Connection.BeginTransaction();

	try {
		// do Stuff Here
		cmd.ExecuteNonQuery();
 cmd.ExecuteNonQuery();

		// commit if it's our to commit
		if (newTransaction)	cmd.Transaction.Commit();

	} catch (SqlException ex) {
		if (newTransaction && cmd.Transaction != null) cmd.Transaction.Rollback();
		throw;
	}
 
Then the parent function can pass in a command and optionally choose to begin it's own transactional block, or if not, one will be created and commited by the called function.

I am getting a `SqlConnection does not support parallel transactions.` exception and [this answer](https://stackoverflow.com/questions/407320/strange-sql2005-problem-sqlconnection-does-not-support-parallel-transactions/432165#432165) mentions its when a connection tries to open two transactions. This is exactly what i am doing. I thought nested transactions were ok (i was using sqlite for the prototype).

How do i check if the connection is already in a transaction? I am using Microsoft SQL Server Database File.

Check if a connection is in a transaction

Many solutions will break for any strings that have *less than 2 words*, which is increasingly likely for people hoping to parse the first *n* number of words.

Let's first look at the query, and then how we can tell if it actually evaluated correctly. 
For that, we need to nest multiple [`CHARINDEX`][charindex] statements, which take the following params:



<pre><code>CHARINDEX( expressionToFind , expressionToSearch [ , start_location ] )
</code></pre>

Charindex will return the *first* index where it finds the specific string. What we keep doing is kicking the ball down the road by adding in a `start_location` equal to the first found instance +1 so it'll find the 2nd, 3rd, 4th instance, etc. Also, instead of [`SUBSTRING(@str, 0,...`][substring] we can just use [`LEFT(@str,...`][left] to capture the first portion of the string, but calculating how far deep to go is the hard part anyway:




 DECLARE @string VARCHAR(1000) = 'One Two Three';
 SELECT LEFT(@string, CHARINDEX(' ', @string,
 CHARINDEX(' ', @string,
 CHARINDEX(' ', @string,
 CHARINDEX(' ', @string)+1)+1)+1))


But this will **fail** if we don't have the minimum number of words: 
Technically, it'll just keep looping around infinitely. Once it runs out of spaces, it'll start indexing again from the beginning

[![Char Index Demo][1]][fiddle]

**Pop quiz**... what will the above query resolve to? Hover below for answer
>! `SELECT LEFT('One Two Three', 3) -- 'One'`

In the solution in [Extracting a specific number of words from a string in sql][2], we can check that the last `CHARINDEX` produced a non-zero value, meaning it hit a space at that level of depth. But also, since the nested charindex is kind of unwieldy, we can get at that information at little more directly by [counting the number of occurrences of a certain substring in a SQL varchar?][3]

### So the final solution should look like this to grab the first *4 words*:



 DECLARE @string VARCHAR(1000) = 'One Two Three Four Five Six';
 
 SELECT CASE WHEN LEN(@string)-LEN(REPLACE(@string, ' ', '')) < 4 
 			THEN @string
 			ELSE LEFT(@string, CHARINDEX(' ', @string,
 CHARINDEX(' ', @string,
 CHARINDEX(' ', @string,
 CHARINDEX(' ', @string)+1)+1)+1))
 	 END

If there are less than 4 spaces, we'll just return the whole string. For more than four, we'll find the position of the 4th space and return the left portion of the string all the way to that position


[1]: https://i.stack.imgur.com/iSPPH.png
[2]: https://www.sqlservercentral.com/Forums/FindPost460865.aspx
[3]: https://stackoverflow.com/q/738282/1366033
[fiddle]: https://jsfiddle.net/KyleMit/cdppkpft/
[charindex]: https://learn.microsoft.com/en-us/sql/t-sql/functions/charindex-transact-sql
[substring]: https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql
[left]: https://learn.microsoft.com/en-us/sql/t-sql/functions/left-transact-sql

I have string like this "  This is a hello world example"

Now I want first two words of the sentence as my output in SQL Server. i.e. This is .

Another example:
Original sentence : "Complete word exercise"
Output: Complete word



First two words in the string - sql server

As @Hutch pointed out, one of the major limitations of `sp_who2` is that it does not take any parameters so you cannot sort or filter it by default. You can [save the results into a temp table][temp sp_who2], but then the you have to declare all the types ahead of time (*and remember to `DROP TABLE`*).

Instead, you can just go directly to the source on `master.dbo.sysprocesses`

I've constructed this to output almost exactly the same thing that `sp_who2` generates, except that you can easily add `ORDER BY` and `WHERE` clauses to get meaningful output.



 SELECT spid,
 sp.[status],
 loginame [Login],
 hostname, 
 blocked BlkBy,
 sd.name DBName, 
 cmd Command,
 cpu CPUTime,
 physical_io DiskIO,
 last_batch LastBatch,
 [program_name] ProgramName 
 FROM master.dbo.sysprocesses sp 
 JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
 ORDER BY spid 

[temp sp_who2]: http://www.sqlmatters.com/Articles/sp_who2%20-%20filtering%20and%20sorting%20the%20results.aspx

How to get detailed list of connections to database in sql server 2005?

### What is Soundex?

[**Soundex**][1] is:

> a phonetic algorithm for indexing names by sound, *as pronounced **in English***; first developed by Robert C. Russell and Margaret King Odell in 1918

### How does it Work?

There are several implementations of Soundex, but most implement the following steps:

1. Retain the first letter of the name and drop all other occurrences of vowels and `h,w`: 
 `|a, e, i, o, u, y, h, w | → "" |` 
2. Replace consonants with numbers as follows (after the first letter): 
 `| b, f, p, v | → 1 |` 
 `| c, g, j, k, q, s, x, z | → 2 |` 
 `| d, t | → 3 |` 
 `| l | → 4 |` 
 `| m, n | → 5 |` 
 `| r | → 6 |` 
3. Replace identical adjacent numbers with a single value (*if they were next to each other prior to step 1*): 
 `| M33 | → M3 |` 
4. *Cut* or *Pad* with zeros or cut to produce a 4 digit result: 
 `| M3 | → M300 |` 
 `| M34123 | → M341 |` 

Here's an interactive demo in [**jsFiddle**][2]:

[![jsFiddle Demo][3]][2show]

And here's a demo in SQL using [**SQL Fiddle**][7]

In SQL Server, [`SOUNDEX`][4] is often used in conjunction with [`DIFFERENCE`][5], which is used to score how many of the resulting digits are identical (just like the game mastermind†), with higher numbers matching most closely.

[![Mastermind][6]][6]


### What are the Alternatives?

It's important to understand the limitations and criticisms of soundex and where people have tried to improve it, notably only being rooted in English pronunciation and also [discards a lot of data, resulting in more false positives][11].

-----

Both [**Metaphone**][8] & [**Double Metaphone**][9] still focus on English pronunciations, but add much more granularity to the [nuances of speech in Enlgish][10] (ie. `PH` → `F`)

Phil Factor wrote a [Metaphone Function in SQL][13] with the [source on github][12]

----

Soundex is most commonly used on identifying similar names, and it'll have a really hard time finding any **similar nicknames** (i.e. `Robert` → `Rob` or `Bob`). Per this question on a [Database of common name aliases / nicknames of people][14], you could incorporate a lookup against similar nicknames as well in your matching process. 

Here are a couple free lists of common nicknames:

* [**SOEMPI** - `name_to_nick.csv` | Github][15]
* [**carltonnorthern** - `names.csv` | Github][16]


### Further Reading:

* [Fuzzy matching using T-SQL][17]
* [SQL Server – Do You Know Soundex Functions?][18]


 [2show]: http://jsfiddle.net/KyleMit/zyjLph3n/show
 [1]: https://en.wikipedia.org/wiki/Soundex
 [2]: http://jsfiddle.net/KyleMit/zyjLph3n/
 [3]: https://i.stack.imgur.com/g04t6.png
 [4]: https://learn.microsoft.com/en-us/sql/t-sql/functions/soundex-transact-sql
 [5]: https://learn.microsoft.com/en-us/sql/t-sql/functions/difference-transact-sql
 [6]: https://i.stack.imgur.com/KPCBj.png
 [7]: http://sqlfiddle.com/#!18/9eecb/30588
[8]: https://en.wikipedia.org/wiki/Metaphone
[9]: https://en.wikipedia.org/wiki/Metaphone#Double_Metaphone
[10]: https://en.wikipedia.org/wiki/Ghoti
[11]: https://stackoverflow.com/a/6576522/1366033
[12]: https://github.com/Phil-Factor/SQLMetaPhone/blob/master/SQLMetaphone.sql
[13]: https://www.red-gate.com/simple-talk/blogs/string-comparisons-sql-metaphone-algorithm/
[14]: https://stackoverflow.com/q/2381522/1366033
[15]: https://github.com/MrCsabaToth/SOEMPI/blob/master/openempi/conf/name_to_nick.csv
[16]: https://github.com/carltonnorthern/nickname-and-diminutive-names-lookup/blob/master/names.csv
[17]: https://stackoverflow.com/q/921978/1366033
[18]: http://www.sqlservergeeks.com/sql-server-do-you-know-soundex-functions/

Here's an example of Soundex code in SQL:



 SELECT SOUNDEX('Smith'), SOUNDEX('Smythe');
 ----- ----- 
 S530 S530 

How does `'Smith'` become `S530`?

In this example, the first digit is `S` because that's the first character in the input expression, but how are the remaining three digits are calculated?

How does the Soundex function work in SQL Server?

As Aaron Bertrand pointed out, you can use the undocumented `xp_regread` in SQL Server 2005 and SQL Server 2008, but there is a better way, starting with SQL Server 2008R2 SP1.

From the article [How to identify the SQL Server Service Account in T-SQL][1], you can use [`sys.dm_server_services`][2] like this:



 SELECT DSS.servicename,
 DSS.startup_type_desc,
 DSS.status_desc,
 DSS.last_startup_time,
 DSS.service_account,
 DSS.is_clustered,
 DSS.cluster_nodename,
 DSS.filename,
 DSS.startup_type,
 DSS.status,
 DSS.process_id
 FROM sys.dm_server_services AS DSS;

![SQL Service Account Query Results][3]

[1]: http://sqlity.net/en/1868/identify-sql-server-service-account-in-t-sql/
[2]: https://msdn.microsoft.com/en-us/library/hh204542.aspx
[3]: http://i.imgur.com/HdfsUZF.png

How can I get the Service Account name for the SQL Agent service for a particular SQL Server (SQL 2005).
Is it possible to get using SQL statements or WMI ?



Get Service Account Details of the SQL Agent Service

You can find trigger objects either on [`sys.objects`][1] view with an [`xtype`][2] = `'tr'` or, starting in SQL 2008, on the [`sys.triggers`][3] view.

You can get the definition for triggers (or any other object) by joining to [`sys.sql_modules.definition`][4] or by calling [`OBJECT_DEFINITION(object_id)`][5]. 
Just don't use [`sys.syscomments.text`][6] because it caps out at `nvarchar(4000)`

In any combination of the above (really doesn't matter) you could generate a single file to populate all of your scripts by terminating each definition with "Go" like this:



 SELECT m.definition, 'GO'
 FROM sys.triggers tr
 JOIN sys.sql_modules m ON tr.object_id = m.object_id

Just take the resulting output and save it in a single file and execute to recreate every trigger. 

### Generate Individual Scripts

The problem is a little more complex if you want to generate individual files for each script. 
...mostly in automating the file generation; the previous solution leans on just presenting data and letting you save it.

You can use [`bcp utility`][7], but I find SQL to be pretty clumsy at dealing with generating files. Alternatively, just grab the data in powershell and then generate files from there where you have a lot more fine tuned access and control. Here's a script that will grab all the triggers and create them into folders for each table.



 # config
 $server = "serverName"
 $database = "dbName"
 $dirPath = 'C:\triggers'
 
 # query
 $query = @"
 SELECT TOP 5
 t.name AS TableName, 
 tr.name AS TriggerName,
 m.definition As TriggerScript
 FROM sys.triggers tr
 JOIN sys.tables t ON tr.parent_id = t.object_id
 JOIN sys.sql_modules m ON tr.object_id = m.object_id
 "@
 
 # connection
 $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $sqlConnection.ConnectionString = "Server=$server;Database=$database;Integrated Security=True"
 $sqlConnection.Open()
 
 # command
 $sqlCmd = New-Object System.Data.SqlClient.SqlCommand($query, $sqlConnection)
 $sqlCmd.CommandTimeout = 10 * 60 # 10 minutes
 
 
 # execute
 $reader = $sqlCmd.ExecuteReader()
 while ($reader.Read())
 {
 # get reader values
 $tableName = $reader.GetValue(0)
 $triggerName = $reader.GetValue(1)
 $triggerScript = $reader.GetValue(2)
 
 $filepath = "$dirPath\$tableName\$triggerName.sql"
 
 # ensure directory exists
 [System.IO.Directory]::CreateDirectory("$dirPath\$tableName\")
 
 # write file
 New-Item -Path $filepath -Value $triggerScript -ItemType File
 }
 $reader.Close()
 $sqlConnection.Close()

[1]: https://msdn.microsoft.com/en-us/library/ms177596.aspx
[2]: https://stackoverflow.com/q/16243857/1366033
[3]: https://msdn.microsoft.com/en-us/library/ms188746.aspx
[4]: https://msdn.microsoft.com/en-us/library/ms175081.aspx
[5]: https://msdn.microsoft.com/en-us/library/ms176090.aspx
[6]: https://msdn.microsoft.com/en-us/library/ms186293.aspx
[7]: https://msdn.microsoft.com/en-us/library/ms162802.aspx "Bulk Copy Program"

I would like to generate a script/extract all of the triggers I have in database, so that I can use them in another one. How can I do this?

How to export triggers from a database?

To add to gonzalo's answer, both the *string literal* and the *field* need to *support unicode characters*.

### String Literal

Per Marc Gravell's answer on [What does N' stands for in a SQL script ?][2]:

> `'abcd'` is a literal for a <code>[var]char</code> string, occupying 4 bytes memory, and using *whatever code-page the SQL server is configured for*. 
> `N'abcd'` is a literal for a <code>n[var]char</code> string, occupying 8 bytes of memory, and using UTF-16. 

Where the `N` prefix stands for "*National*" Language in the SQL-92 standard and is used for representing unicode characters. For example, in the following code, any unicode characters in the basic string literal are first encoded into SQL Server's "code page":

[![`SELECT '≤' AS ['≤'], N'≤' AS [N'≤']`][1]][1]

> **Aside**: You can [check your code page][4] with the following SQL:
> 
 
> SELECT DATABASEPROPERTYEX('dbName', 'Collation') AS dbCollation;
 SELECT COLLATIONPROPERTY( 'SQL_Latin1_General_CP1_CI_AS' , 'CodePage' ) AS [CodePage];
> The default is [Windows-1252][3] which only [contains these 256 characters][5]

### Field Type

Once the values are capable of being *passed*, they'll also need to be capable of being *stored* into a [column that supports unicode types][8], for example:

* `nchar`
* `nvarchar`
* `ntext`

**Further Reading**:

* [Why do we need to put N before strings in Microsoft SQL Server?][6]
* [What is the meaning of the prefix N in T-SQL statements?][7]
* [You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server][9]
* [Why do some SQL strings have an 'N' prefix?][10]





[1]: https://i.stack.imgur.com/vmK7p.png
[2]: https://stackoverflow.com/a/14353311/1366033
[3]: https://en.wikipedia.org/wiki/Windows-1252 
[4]: https://stackoverflow.com/q/7321159/1366033
[5]: https://www.w3schools.com/charsets/ref_html_ansi.asp
[6]: https://softwareengineering.stackexchange.com/q/155859/87466
[7]: https://stackoverflow.com/q/10025032/1366033
[8]: https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-2017
[9]: https://web.archive.org/web/20150114081311/http://support.microsoft.com:80/kb/239530/en-us "ARCHIVE of http:&#47;&#47;support.microsoft.com/kb/239530/en-us"
[10]: https://web.archive.org/web/20150208024547/http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html "ARCHIVE of http:&#47;&#47;databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html"

I am trying to insert `≤` and `≥` into a symbol table where the column is of type `nvarchar`.

Is this possible or are these symbols not allowed in SQL Server?



Inserting special characters (greater/less than or equal symbol) into SQL Server database

1. Adapted from this post on how to [how to fix .sql file not opening with SSMS 2008][1], you should start by opening up the registry (`regedit`)
2. Go to `HKEY_CLASSES_ROOT\.sql` and change the default value to `ssms.sql.11.0` (SSMS 2012 is v11 of the product). This tells your computer the application string it should look for when trying to open files of type `.sql`

   ![.sql][.sql]
  
3. The registry takes that name you just entered and then looks up what to actually go do in another registry setting.  

  Go `HKEY_CLASSES_ROOT\ssms.sql.11.0\Shell\Command` and change the default value to:

        "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe" "%1" /dde

   ![ssms.sql.11.0][ssms.sql.11.0]

3. Turn your computer off and on again for good measure.  

   If it still doesn't work for whatever reason.  Right click on any SQL file.  Go to Properties and click to change the file it opens with.  You may have two SQL options available.  Try one then the other.

  ![open with][open with]

[1]: http://darrenmyher.com/2012/11/28/how-to-fix-sql-file-not-opening-with-ssms-2008/
[album]: http://imgur.com/a/FqLoq
[.sql]: http://i.imgur.com/tyupxeP.png
[ssms.sql.11.0]: http://i.imgur.com/MsEAM0o.png
[open with]: http://i.imgur.com/ZHxxFqd.png

Installation of SSMS 2012 alongside any other existing SSMS installation, such as SSMS 2008 R2 has a problem with setting the default program for opening .SQL files to the new SSMS 2012 version.  

Dragging .sql file to the query window works successfully. However, when a user double clicks on the .sql file a new instance of 2008 R2 can open as the .sql associated entry. Changing with Default Open With makes no difference when doubleclicking on new files. Additionally, altering the command line script for the opening can perhaps achieve changing the .sql to opening in SSMS 2012, but it still causes repeated instances of ssms instead of using the existing instance (causing additional load and memory usage). 

Opening .sql files in SSMS 2012 as default program and with existing instance

I don't know how you are connecting Emplyee(sic?) and Employment, but you want to use a join to connect two tables and in the join specify how the tables are related.  Joins usually look best when they have aliases so you don't have to repeat the entire table name.  The following query will get you all the information from both Employment and Distribution tables where the distribution location is equal to california.  You can join employee to employment to get name as well.


    SELECT *
    FROM Employment e
    JOIN Distribution d on d.DistributionID = e.DistributionID
    WHERE d.Location = 'California'


This will return the contents of both tables.  To select particular records use the alias.[Col_Name] separated by a comma in the select statement, like d.DistributionID to return the DistributionID from the Distribution Table

I am trying to use a second `SELECT` to get some ID, then use that ID in a second `SELECT` and I have no idea how.

    SELECT Employee.Name 
    FROM Emplyee, Employment 
    WHERE x = Employment.DistributionID 
    (SELECT Distribution.DistributionID FROM Distribution 
           WHERE Distribution.Location = 'California') AS x

**This post got long, but here is a short "tip"**

While the syntax of my select is bad, the logic is not. I need that "x" somehow. Thus the second `select` is the most important. Then I have to use that "x" within the first `select`. I just don't know how

**/Tip**

This is the only thing I could imagine, I'm very new at Sql, I think I need a book before practicing, but now that I've started I'd like to finish my small program.

**EDIT:**

Ok I looked up joins, still don't get it

    SELECT Employee.Name 
        FROM Emplyee, Employment 
        WHERE x = Employment.DistributionID 
        LEFT JOIN Distribution ON
        (SELECT Distribution.DistributionID FROM Distribution 
               WHERE Distribution.Location = 'California') AS x

Get error msg at `AS` and `Left`

I use name to find ID from upper red, I use the ID I find FROM upper red in lower table. Then I match the ID I find with Green. I use Green ID to find corresponding Name

![enter image description here][1]

I have `California` as output data from C#. I want to use `California` to find the DistributionID. I use the DistributionID to find the EmployeeID. I use EmployeeID to find Name

**My logic:**

    Parameter: Distribution.Name (from C#)

    Find DistributionID that has Distribution.Name
    Look in Employment WHERE given DistributionID 
          reveals Employees that I am looking for (BY ID)
    Use that ID to find Name
          return Name

Tables: 

**NOTE:** In this example picture the Employee repeats because of the select, they are in fact singular

In "Locatie" (middle table) is Location, I get location (again) from C#, I use `California` as an example. I need to find the `ID` first and foremost!

![enter image description here][2]


Sory they are not in english, but here are the create tables: 

![enter image description here][3]


  [1]: http://i.stack.imgur.com/SqgB6.jpg
  [2]: http://i.stack.imgur.com/LV4uO.png
  [3]: http://i.stack.imgur.com/6oQte.png

How to use the result from a second select in my first select

The two highest voted answers use a lot of deprecated tables that should be avoided. 
Here's a much cleaner way to do it.

### Get all the tables on which a stored procedure depends:



 SELECT DISTINCT p.name AS proc_name, t.name AS table_name
 FROM sys.sql_dependencies d 
 INNER JOIN sys.procedures p ON p.object_id = d.object_id
 INNER JOIN sys.tables t ON t.object_id = d.referenced_major_id
 ORDER BY proc_name, table_name


*Works with MS SQL SERVER 2005+*

### List of Changes:
* [`sysdepends`][sysdepends] should be replaced with [`sys.sql_dependencies`][sys.sql_dependencies]
 * The new table uses `object_id` instead of `id`
 * The new table uses `referenced_major_id` instead of `depid`
* Using [`sysobjects`][sysobjects] should be replaced with more focused system catalog views
 * As [marc_s pointed out][marc_s], instead use [`sys.tables`][sys.tables] and [`sys.procedures`][sys.procedures]
 * **Note**: This prevents having to check where `o.xtype = 'p'` (etc.)
* Also, there is really no need for a CTE which uses [`ROW_NUMBER()`][ROW_NUMBER] *just* in order to make sure we only have one of each record set returned. That's what [`DISTINCT`][DISTINCT] is there for!
 * In fact, SQL is smart enough to use DISTINCT behind the scenes. 
 * I submit into evidence: [**Exhibit A**][SQLFiddle]. The following queries have the same [Execution Plan][1]!

 

 -- Complex
 WITH MyPeople AS (
 SELECT id, name,
 ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id, name) AS row
 FROM People)
 SELECT id, name
 FROM MyPeople
 WHERE row = 1
 
 -- Better
 SELECT DISTINCT id, name
 FROM People

 

[sysdepends]: http://msdn.microsoft.com/en-us/library/ms190325.aspx
[sys.sql_dependencies]: http://msdn.microsoft.com/en-us/library/ms174402.aspx
[sysobjects]: http://technet.microsoft.com/en-us/library/ms177596.aspx
[sys.tables]: http://technet.microsoft.com/en-us/library/ms187406.aspx
[sys.procedures]: http://technet.microsoft.com/en-us/library/ms188737.aspx
[ROW_NUMBER]: http://technet.microsoft.com/en-us/library/ms186734.aspx
[DISTINCT]: http://technet.microsoft.com/en-us/library/ee634943.aspx

[marc_s]: https://stackoverflow.com/questions/16229493/1366033/#comment23216013_16229560

[SQLFiddle]: http://sqlfiddle.com/#!18/5ba48/1


 [1]: https://i.stack.imgur.com/sU32U.png

There are lot of tables and sp in the db. I find the tables name which are used in the specific sp (stored procedure).

sp_depends `%sp_name%` not give the desire result. I am also used `INFORMATION_SCHEMA.TABLES`,`INFORMATION_SCHEMA.ROUTINES` tables.

But the result is not full fill my requirment.


How can I get the list of tables in the stored procedure?

### Update for SQL Server 2012+

I always find the `CONVERT` magic numbers hard to remember.  You can use [`FORMAT`][1] instead and provide a formatting pattern string like this:


```sql
SELECT FORMAT(GetDate(),'yyyy-MM')
```

**See Also**: [Convert date to YYYYMM format][2]

[1]: https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql
[2]: https://stackoverflow.com/a/49890439/1366033

need the date in the format yyyy-mm and should be grouped to get 
the count but when i give the 

 group by date is says invalid

i have found this solution 

can anyone help me by providing other solution


 select 
 count(*) as count, 
 Convert(char(10), RH.updated_datetime, 105) as date,
 SUM( 
 datediff(SECOND, 
 PRI.procedure_performed_datetime ,RH.updated_datetime
 )
 )/count(*) as average_reporting_tat 
 from 
 report R, 
 report_history RH, 
 study S, 
 procedure_runtime_information PRI, 
 priorities PP, 
 patient P, 
 "procedure" PR 
 
 where 
 
 RH.report_fk=R.pk and RH.pk IN ( 
 select pk from ( 
 select * from report_history where report_fk=r.pk 
 ) as result 
 where old_status_fk IN (21, 27)
 ) AND R.study_fk = S.pk 
 AND S.procedure_runtime_fk = PRI.pk 
 AND PRI.procedure_fk = PR.pk 
 AND S.priority_fk = PP.pk 
 AND PRI.patient_fk = P.pk 
 AND RH.updated_datetime >= '2012-05-01' 
 AND RH.updated_datetime <= '2013-09-12' 
 group by Convert(char(10), RH.updated_datetime, 105)

need date in the format yyyy-mm in mssql


Try the below

    select daily_user_count, count(*) from (
    SELECT COUNT(*) as daily_user_count FROM Activity
    GROUP BY entity_uid, date
    ) Z
    group by daily_user_count
    


I'd like to know how often a particular user does an activity on the same day



 SELECT COUNT(*) FROM Activity
 GROUP BY entity_uid, date

This returns a long lists of counts: i.e.

 1
 1
 2
 1

I'd then like to group the results of this query that tell me how often each number appears within the result set, like this

 # Count(*)
 1 3
 2 1

Here's what I have, but it doesn't work



 Select x = (
 SELECT COUNT(*) FROM Activity
 GROUP BY entity_uid, date
 ),
 Count(*)
 GROUP BY x

How can I select the results of a subquery in a variable?

Group results of grouped query

This doesn't specifically use load or fill, but you can do this pretty easily by just reading in the data reader and only setting values when the incoming value isn't null. By getting the [`SchemaTable`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable(v=vs.100).aspx) from the `SqlDataReader`, you can still retain the simplicity of the load or fill commands by not having to write out every column name. By adding an Extension method with the name Load that takes in a default row parameter, you can retain the look and feel of the Load function while implementing the functionality yourself

**Load Extension Method with Default Row Arg:**



 ''' <summary>
 ''' Fills a <c>System.Data.DataTable</c> with values from a data source using the supplied <c>System.Data.SqlDataReader</c>. 
 ''' Each row is new row is given the default values from the new row and incoming nulls are ignored
 ''' </summary>
 ''' <param name="dt">DataTable to fill</param>
 ''' <param name="reader">A <c>System.Data.SqlDataReader</c> that provides a result set.</param>
 ''' <param name="defaultRow">Default values for each DataRow</param>
 ''' <remarks></remarks>
 <Extension()> _
 Public Sub Load(ByVal dt As DataTable, ByVal reader As SqlDataReader, ByVal defaultRow As DataRow)
 Dim newRow As DataRow
 'get incoming data fields
 Dim columns As List(Of String)
 columns = reader.GetSchemaTable.AsEnumerable _
 .Select(Function(r) CStr(r("ColumnName"))).ToList()

 While reader.Read()
 'make new row and set default item array
 newRow = dt.NewRow()
 newRow.ItemArray = defaultRow.ItemArray
 'copy over new values
 For Each col As String In columns
 If Not IsDBNull(reader(col)) Then newRow(col) = reader(col)
 Next
 dt.Rows.Add(newRow)
 End While
 End Sub

**Call into your Load Method Like This:**



 Dim roleTable As New CodeSetSchemas.EntityByRoleDataTable()
 Using reader As SqlDataReader = cmd.ExecuteReader()
 roleTable.Load(reader, roleTable.NewEntityByRoleRow())
 End Using

I have an xsd DataSet Schema which allows me to define a the structure for a `DataTable`. For every column, I've set a default value so when constructing a new row, I get instantiated values ("" for strings and 0 for Ints) instead of `DbNull`

I'd like to preserve that when using either [`DataTable.Load`](http://msdn.microsoft.com/en-us/library/7x8ccbsb.aspx) or [`SqlDataAdapter.Fill`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.fill.aspx) as in either of the two following method calls



 Dim table1 As New CodeSetSchemas.EntityByRoleDataTable()
 Using reader As SqlDataReader = cmd.ExecuteReader()
 table1.Load(reader)
 End Using
 
 Dim table2 As New CodeSetSchemas.EntityByRoleDataTable()
 Using adapter As New SqlDataAdapter(cmd)
 adapter.Fill(table2)
 End Using

But when either of these methods create a new row, they actually write DbNull from the database into the row. If I set the `AllowDBNull` property on each column to `False`, I get the following exception:

 Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Is there a way to preserve my default values for each row when filling a DataTable?

Use default column values when loading DataTable to avoid Nulls

*As of SQL Server 2012+*, you can use [<code>FORMAT(value, format [, culture ])</code>][1]

Where the `format` param takes any valid [standard format string][2] or [custom formatting string][3]

**Example**:

```sql
SELECT FORMAT(GETDATE(), 'dd-MMM-yyyy')
```

**Further Reading**: 

* [How to convert DateTime to VarChar][4]
* [How to convert date to a format `mm/dd/yyyy`][5]
* [sql server convert date to string MM/DD/YYYY][6]

[1]: https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql
[2]: https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings
[3]: https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

[4]: https://stackoverflow.com/q/74385/1366033
[5]: https://stackoverflow.com/q/18568410/1366033
[6]: https://stackoverflow.com/q/11854775/1366033

I'm using following line of code in SQL:

    REPLACE(CONVERT(VARCHAR(11),getdate(),106), ' ','-') 

to convert given date to `MMM-DD-YYYY` format, this code is working perfectly fine if current date is more that 10th. 

But I'm getting error when its tries to convert single digit date, e.g when I'm converting date `9 jan 2014`. It is converted to `Jan--9-2014`.

Which is actually wrong as its contain one extra ` - `. 

Please help me to resolve this issue.

converting date to DD-MMM-YYYY format

You can use a recursive query to build up a table of whatever numbers you want. Here we stop at 24. Then left join that to your comments to ensure every hour is represented. You can turn these into times easily if you wanted. I also changed your use of `hour` as a column name as it is a keyword.

 ;with dayHours as (
 	select 1 as HourValue
 	union all select hourvalue + 1
 	from dayHours
 	where hourValue < 24
 )
 ,
 CommentTimes As (
 SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) As HourValue,
 COUNT(*) AS Count
 FROM Comments
 WHERE UserId = ##UserId##
 GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate)))
 SELECT h.Hour, c.Count
 FROM dayHours h
 left JOIN CommentTimes c ON h.HourValue = c.HourValue

This feels simple, but I can't find an answer anywhere. 
I'm trying to run a query by time of day for each hour. So I'm doing a `Group By` on the hour part, but not all hours have data, so there are some gaps. I'd like to display every hour, regardless of whether or not there's data. 

Here's a sample query:



 SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) As Hour,
 COUNT(*) AS Count
 FROM Comments
 WHERE UserId = ##UserId##
 GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate))

My thought was to Join to a table that already had numbers 1 through 24 so that the incoming data would get put in it's place.
 
Can I do this with a CTE?



 WITH Hours AS (
 SELECT i As Hour --Not Sure on this
 FROM [1,2,3...24]), --Not Sure on this
 CommentTimes AS (
 SELECT DATEPART(HOUR, DATEADD(HH,-5, CreationDate)) AS Hour,
 COUNT(*) AS Count
 FROM Comments
 WHERE UserId = ##UserId##
 GROUP BY DATEPART(HOUR, DATEADD(HH,-5, CreationDate))
 )
 SELECT h.Hour, c.Count
 FROM Hours h
 JOIN CommentTimes c ON h.Hour = c.Hour

###Here's a sample [Query From Stack Exchange Data Explorer](https://data.stackexchange.com/stackoverflow/revision/185460/239532/comments-by-hour)

Select Consecutive Numbers in SQL

This does it:

    ;WITH versions (suffix) AS (
        SELECT '_R1' UNION ALL
        SELECT '_v2' UNION ALL
        SELECT '_v1'
    )
    SELECT	name, 
    		REPLACE(A.name,ISNULL(B.suffix,''),'') VersionlessName
    FROM applications A
    LEFT JOIN versions B
    	ON A.name LIKE '%'+B.suffix

The results are:

    ╔════════════╦═════════════════╗
    ║    name    ║ VersionlessName ║
    ╠════════════╬═════════════════╣
    ║ MyApp_v2   ║ MyApp           ║
    ║ MyApp_v1   ║ MyApp           ║
    ║ MyApp      ║ MyApp           ║
    ║ YourApp_R1 ║ YourApp         ║
    ╚════════════╩═════════════════╝
And [**here is**][1] the modified sqlfiddle.


  [1]: http://sqlfiddle.com/#!3/400cb/1

I'm trying to strip off version suffixes (`_v1`...) from a whole bunch of app names. 

For example, let's say I have the following data:

    CREATE TABLE applications 
     (
       name varchar(20)
     );
    
    INSERT INTO applications
     (name)
    VALUES
     ('MyApp_v2'),
     ('MyApp_v1'),
     ('MyApp'),
     ('YourApp_R1');

I could normally do this by nesting a lot of replace statements:

    SELECT REPLACE(REPLACE(REPLACE(
             name,
           '_R1', ''),
           '_v2', ''),
           '_v1', '')
           As AppNameWithoutSuffix
    FROM applications

But I have a lot of version numbers to check for, so I was hoping for something cleaner.  Based on the following questions:

* [SQL Nested Replace][SO1]
* [Are multiple Replace statements a bad practice/is there another way to write them?][SO2]



I wanted to create a CTE that stored all the prefixes and then [`REPLACE`][Replace] them all like this:

    ;WITH versions (suffix) AS (
        SELECT '_R1' UNION ALL
        SELECT '_v2' UNION ALL
        SELECT '_v1'
    )
    SELECT REPLACE(a.name, v.suffix, '') As AppNameWithoutSuffix
    FROM applications a,
         versions v

But this does not quite work.  The multiple from statements gives me the cartesian product of both tables and only strips out the suffix on the rows where the value happens to line up.

### [Demo in Sql Fiddle][Sql Fiddle]

*Note*: I know I could convert this to a function, but I'd rather keep everything within a single query if possible.  

[SO1]: https://stackoverflow.com/q/13088921/1366033
[SO2]: https://stackoverflow.com/q/5475428/1366033
[Sql Fiddle]: http://sqlfiddle.com/#!18/165bb/3

[Replace]: http://msdn.microsoft.com/en-us/library/ms186862.aspx

Use Column value as string pattern in REPLACE function

It appears this is **not possible**.

According to [Andrew Barber's comment on "Get windows user login name from sql server"][1]:

> If they are logged in as a SQL Server user, they are *not* logged in as a Windows user; no Windows credentials are passed at all; so you simply can't get that information directly

[1]: https://stackoverflow.com/q/25629000/#comment40043473_25629000

We are using a SQL Login as a vehicle for issuing permissions, but once authenticated through SQL, we'd still like to know the Active Directory name of the connected user?

We've tried:

```sql
SELECT SUSER_NAME()
SELECT SUSER_SNAME()
SELECT SUSER_SID()
SELECT USER_NAME()
SELECT USER_ID()
SELECT SYSTEM_USER
SELECT SESSION_USER
SELECT ORIGINAL_LOGIN()
```

Is that information still available somewhere?

**Note**: This is different from how to [get windows user login name from sql server][1] since we're using *SQL Login*, not a *Windows Login* to connect to SQL Server


[1]: https://stackoverflow.com/q/25629000/1366033

Get current user while using SQL Authentication

In ASP.NET, we have an application that prompts users for their AD credentials using Basic Authentication and ASP.NET Impersonation.

The application then connects to SQL Server with the following connection string



 SqlClient.SqlConnection cnn = new SqlClient.SqlConnection();
 cnn.ConnectionString = 
 "Server=" + MenuServer + ";" + 
 "Database= " + MenuDatabase + ";" + 
 "Trusted_Connection=Yes;" + 
 "Pooling = False;";
 cnn.Open();

99% of the time, this passes along the user context just fine, but sporadically we get the following error from SQL Server:

> Login failed for user '*WebServerName*$'. Reason: Could not find a login matching the name provided

Meaning that it has been unable to pass along the user credentials and instead defaulted to the IIS worker process. Interestingly, when we catch the error, the current user will still be accurately recorded with the following code:



 string userName = Environment.UserName;

**Questions**: 

* What user context does the Integrated Security pass along to the database?
* Is it possible to programmatically check the user before calling `cnn.Open()` to confirm we have a real user?

ASP.NET loses user context when connecting to database

These questions ([one][SO1], [two][SO2], [three][SO3]) all identified how to return an empty string instead of `NULL` *for a single column*:

    SELECT ISNULL(ColA,'') AS ColA FROM Table1

However, I have a query that returns a whole bunch of columns that I'm too lazy to select individually (don't worry - this isn't a production query)

    SELECT * FROM Table1

Is there a way to return an empty string for all columns returned by the wildcard operator?

For Example, the following naive attempt *won't work*:

    SELECT ISNull(*,'') FROM Table1

[SO1]: https://stackoverflow.com/q/21233442/1366033
[SO2]: https://stackoverflow.com/q/12394545/1366033
[SO3]: https://stackoverflow.com/q/17244808/1366033

Replace NULL with empty string in SQL for ALL columns

There are several functions available in T-SQL under the [Conversion][1] category, all of which have variants prefixed with `TRY_` which:

> Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.


* [`CAST`][2] / [`TRY_CAST`][4]
* [`CONVERT`][2] / [`TRY_CONVERT`][5]
* [`PARSE`][3] / [`TRY_PARSE`][6]

You can use them like this:

```sql
SELECT TRY_CAST('2020-05-27' AS DATE)
SELECT TRY_CONVERT(DATE, '2020-05-27')
SELECT TRY_PARSE('2020-05-27' AS DATE)
```



[1]: https://learn.microsoft.com/en-us/sql/t-sql/functions/conversion-functions-transact-sql
[2]: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15
[3]: https://learn.microsoft.com/en-us/sql/t-sql/functions/parse-transact-sql?view=sql-server-ver15
[4]: https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-ver15
[5]: https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver15
[6]: https://learn.microsoft.com/en-us/sql/t-sql/functions/try-parse-transact-sql?view=sql-server-ver15

I have the following code in a stored procedure in MSSQL Transact:

    CAST(CONVERT(Datetime, aof.Transactiondate) AS date)

aof.Transactiondate is a varchar(8) and should be written in the form of '20160202' or '20160117'.
Today I found out that in rare instances aof.Transactiondate can be a '1' and thus my code crashes completely.

Can I do anything to make sure that the above don't crash and sets a defult date or something instead of crashing??

Theoretically I could make sure that there is 8 chars in the varchar and then seperate the char into 4, 2 and 2 block and finally make sure that block one is between 2015 and 2016 (only relevant at the moment), second block is between 1 and 12 and third block is between 1 and 31 but that seems to be a huge amount of work.

Safe convert from string to date in Transact

You can also use the [`BETWEEN`][1] operator in a `JOIN` like this:

```sql
SELECT a.id, a.Count, b.SizeName
FROM tableA a
JOIN tableB b ON a.id BETWEEN b.LowerLimit AND b.UpperLimit
```

[1]: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/between-transact-sql?view=sql-server-ver15

I have the two following tables:

```none
| ID  | Count |
| --- | ----- |
| 1   | 45    |
| 2   | 5     |
| 3   | 120   |
| 4   | 87    |
| 5   | 60    |
| 6   | 200   |
| 7   | 31    |
```

```none
| SizeName | LowerLimit | UpperLimit |
| -------- | ---------- | ---------- |
| Small    | 0          | 49         |
| Medium   | 50         | 99         |
| Large    | 100        | 250        |
```
    

Basically, one table specifies an unknown number of range names and their associated integer ranges. So a count range of 0 to 49 from the person table gets a `small` designation. 50-99 gets '`medium`' etc. I need it to be dynamic because I do not know the range names or integer values.

Can I do this in a single query or would I have to write a separate function to loop through the possibilities?

How to join a number with the range of number from another table

## Using Async / Await

Here's how to open a connection, query, and close using async await


```js
const sql = require('mssql/msnodesqlv8')

let config = {
    driver: "msnodesqlv8",
    server: serverName,
    database: databaseName,
    options: { trustedConnection: true }
};

// connect to db
let cnn = await sql.connect(config)

// query
let result = await sql.query(query)

// close connection
await cnn.close()
```

I am trying to write a script in node.js to query a MSSQL database. I am new to javascript, new to node.js, new to VSCode, but I know a few things about SQL. I have working code, but the connection never seems to close, and I cannot get the values OUT of the function. 

SO, I have this chunk of code, which I got from the example from [npm][1]:

    const sql = require('mssql');
    var dbConfig = {
        server:'theServer',
        database:'theDB',
        user:'un',
        password:'pw',
        port:1433
    };
    
    sql.connect(dbConfig).then(pool => {
        // Query     
        return pool.request()
        .query('select top 10 * from THE_TABLE')
    }).then(result => {
        console.log(result);
    }).catch(err => {
        // ... error checks
    })

This works, and I can see the 10 results get logged in the console. However, the code never stops running. How do I get the connection to close up and stop?

I really want the results to be saved into a variable, so I changed the code to this:

    const sql = require('mssql');
    var dbConfig = {
        server:'theServer',
        database:'theDB',
        user:'un',
        password:'pw',
        port:1433
    };

    let theList;
    sql.connect(dbConfig).then(pool => {
        // Query     
        return pool.request()
        .query('select top 10 * from THE_TABLE')
    }).then(result => {
        theList= result;
    }).catch(err => {
        // ... error checks
    })
    
    console.log(theList);

This returns 'undefined' to the console for theList, and again the connection never seems to cose, and the script never shuts down.

How do I just grab the results of the query and move on down the road??

  [1]: https://www.npmjs.com/package/mssql

Close MSSQL connection using mssql in node.js

Here's the [full list of reserved keywords][2] broken up by color:

[![Reserved Keywords by Color][1]][1]

**Operators** (Grey)  

ALL, AND, ANY, BETWEEN, CROSS, EXISTS, IN, INNER, IS, JOIN, LEFT, LIKE, NOT, NULL, OR, OUTER, PIVOT, RIGHT, SOME, UNPIVOT

**System Functions** (Magenta)  

COALESCE, COLLATE, CONTAINS, CONVERT, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, NULLIF, SESSION_USER, SYSTEM_USER, TSEQUAL, UPDATE

**Keywords** (Blue)  

ADD, ALTER, AS, ASC, AUTHORIZATION, BACKUP, BEGIN, BREAK, BROWSE, BULK, BY, CASCADE, CASE, CHECK, CHECKPOINT, CLOSE, CLUSTERED, COLUMN, COMMIT, COMPUTE, CONSTRAINT, CONTAINSTABLE, CONTINUE, CREATE, CURRENT, CURRENT_DATE, CURSOR, DATABASE, DBCC, DEALLOCATE, DECLARE, DEFAULT, DELETE, DENY, DESC, DISK, DISTINCT, DISTRIBUTED, DOUBLE, DROP, DUMP, ELSE, END, ERRLVL, ESCAPE, EXCEPT, EXEC, EXECUTE, EXIT, EXTERNAL, FETCH, FILE, FILLFACTOR, FOR, FOREIGN, FREETEXT, FREETEXTTABLE, FROM, FULL, FUNCTION, GOTO, GRANT, GROUP, HAVING, HOLDLOCK, IDENTITY, IDENTITYCOL, IDENTITY_INSERT, IF, INDEX, INSERT, INTERSECT, INTO, KEY, KILL, LINENO, LOAD, MERGE, NATIONAL, NOCHECK, NONCLUSTERED, OF, OFF, OFFSETS, ON, OPEN, OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML, OPTION, ORDER, OVER, PERCENT, PLAN, PRECISION, PRIMARY, PRINT, PROC, PROCEDURE, PUBLIC, RAISERROR, READ, READTEXT, RECONFIGURE, REFERENCES, REPLICATION, RESTORE, RESTRICT, RETURN, REVERT, REVOKE, ROLLBACK, ROWCOUNT, ROWGUIDCOL, RULE, SAVE, SCHEMA, SECURITYAUDIT, SELECT, SET, SETUSER, SHUTDOWN, STATISTICS, TABLE, TABLESAMPLE, TEXTSIZE, THEN, TO, TOP, TRAN, TRANSACTION, TRIGGER, TRUNCATE, UNION, UNIQUE, UPDATETEXT, USE, USER, VALUES, VARYING, VIEW, WAITFOR, WHEN, WHERE, WHILE, WITH, WRITETEXT


  [1]: https://i.stack.imgur.com/FgAzI.png
  [2]: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-2017

Microsoft SQL Management Studio highlights some special words coloring them in blue or in pink. I would like to know which rule determines the color to use.

For example, consider the two following statements:

    SELECT * FROM myTable
    UPDATE myTable SET myFlag = 'X'


[![SELECT * FROM myTable UPDATE myTable SET myFlag = 'X'][1]][1]

`UPDATE` is in pink while `SET`, `SELECT`, & `FROM` are in in blue.


  [1]: https://i.stack.imgur.com/DdgtY.png

Meaning of words colored with Blue and Pink in MSQLSMS

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

Here are the complete steps, adapted from Stefan Timovski's article on [How to Create SQL Server Management Studio 18 (SSMS) Extension][1]

1. **Install [Visual Studio 2017][2] with *Extensions Toolkit***

 If you're not sure you have the extensions toolkit, you can open the Visual Studio Installer and modify your current install to make sure you have extensions installed

 [![Visual Studio Installer][3]][3] 
 [![Visual Studio Extension Development][4]][4]

2. **Create New Extension Project**

 Go to File New Project (<kbd>Ctrl</kbd> + <kbd>Shift</kbd> + <kbd>N</kbd>)

 [![File > New > Project][5]][5]

 Choose Extensibility > VIX Project

 [![Extensibility > VSIX Project][6]][6]

 If you don't have these options, go make sure you did step 1

3. **Add a New Command Item**

 Add a new item (<kbd>Ctrl</kbd> + <kbd>Shift</kbd> + <kbd>A</kbd>)

 [![Add New Item][7]][7]

 Select Extensibility and just for demo purposes grab a custom command

 [![Extensibility > Custom Command][8]][8]

4. **Debug in Visual Studio**

 The command file will add a menu item to Tools > Invoke *Command1*. If you hit debug, Visual Studio will launch a Debuggable instance, fully loaded with your current extension. Hit play or hit <kbd>F5</kbd>

 The first time may take a minute to boot-up, but it should go faster thereafter

 [![Launch Debug][9]][9]

 The click event handled in located in *Command1*.cs > `Execute`, and you can add breakpoints.

 [![Debugger Breakpoint][10]][10]

 Here's the working message box

 [![Callback][11]][11]

5. **Get filepath for SSMS**

 The default installation path for SSMS 18 should be:

 C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe

 If it's not there, to identify the startup location of any app, you can <kbd>Shift</kbd> + Right Clicking on the app icon and select "Open file location"

 [![SSMS Location][12]][12]

6. **Set Launch to SSMS**

 Open up the project properties in VS (<kbd>Alt</kbd> + <kbd>Enter</kbd>)

 [![Project Properties][13]][13]

 Go to **Debug** > **Start External Program** and paste in the path

 [![Debug > Start External Program][14]][14]

 Remove the command line args as they're no longer applicable to SSMS

7. **Set Deploy VSIX to SSMS**


 The "Extensions" subdirectory should be in the same directory as SSMS. Also, add an extra folder with your project name like this

 C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Extensions\VSIXProject1

 Go to **VSIX** > and select "**Copy VSIX content to the following location**"

 [![VSIX Copy][15]][15]

8. **Set Permissions**

 Lastly, in order to programmatically invoke any apps on C Drive, you'll need admin privileges, so you need to open Visual Studio in Admin Mode. You can do that by right clicking on the application like this

 [![Run As Admin][16]][16]

9. **Hit Run & Debug**

 Presto! Blamo! Your extension should now be running SSMS

 [![SSMS Debugger][17]][17]

### Further Reading

Historically, each extension needed to be whitelisted, but according to the [release notes for SSMS 18][18], one big change (for the better) is 

> Package IDs no longer needed to develop SSMS Extensions 
> In the past, SSMS was selectively loading only well-known packages, thus requiring developers to register their own package. This is no longer the case.


Since SSMS 18 uses the *Visual Studio 2017 Isolated Shell*, many of the extension developer documentation is available under the [Visual Studio Extension Docs][19]


 [1]: https://www.codeproject.com/Articles/1377559/How-to-Create-SQL-Server-Management-Studio-18-SSMS
 [2]: https://visualstudio.microsoft.com/downloads/
 [3]: https://i.stack.imgur.com/KM1FS.png
 [4]: https://i.stack.imgur.com/YTTcX.png
 [5]: https://i.stack.imgur.com/GTM5p.png
 [6]: https://i.stack.imgur.com/Y9YmZ.png
 [7]: https://i.stack.imgur.com/Qg2Xy.png
 [8]: https://i.stack.imgur.com/RA12s.png
 [9]: https://i.stack.imgur.com/eUa1Q.png
 [10]: https://i.stack.imgur.com/R9x2b.png
 [11]: https://i.stack.imgur.com/2azx4.png
 [12]: https://i.stack.imgur.com/nCvLM.png
 [13]: https://i.stack.imgur.com/W2CuZ.png
 [14]: https://i.stack.imgur.com/uLXFx.png
 [15]: https://i.stack.imgur.com/GVXi9.png
 [16]: https://i.stack.imgur.com/t2OFU.png
 [17]: https://i.stack.imgur.com/tshEu.png
 [18]: https://learn.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-2017#180-preview---cumulative-changelog-through-rc1
 [19]:https://learn.microsoft.com/en-in/visualstudio/extensibility/extensibility-hello-world?view=vs-2019

[SQL Server Management Studio 18 RC1][0] became available March 28, 2018

This question has already been asked [for SSMS 17][1], but there are slight variations when authoring extensions for different releases of SQL Server Management Studio.

**What are the steps to getting a Hello World application up an running in SSMS 2019?**



[0]: https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
[1]: https://stackoverflow.com/q/47240020/1366033

How to Create an Extension for SSMS 2019 (v18)

One method uses a little bit more storage, but it accomplishes what you want:

    create table addresses (
        . . .,
        codeset as (convert(varchar(?), 'states')) persisted,
        foreign key (codeset, state) references codevalues (codeset, codevalue)
    );

The `?` is for the length of the `varchar()` column in the `codevalues` table.

Happily, you don't need triggers or user-defined functions to accomplish this.

Unhappily, you need to persist the `codeset` in each row, so that eats up a wee bit of space in the table.



## Setup

Here's two simplified* tables that illustrate the problem I have 
\*The actual tables are built such that I can't really refactor the columns or split them apart easily

### Table: `CodeValues`



<pre><code>| CodeSet | CodeValue | CodeText |
|---------|-----------|----------|
| States | 1 | Vermont |
| States | 2 | Hawaii |
| YN | 1 | Yes |
| YN | 2 | No |
</pre></code>

Where `CodeSet` + `CodeValue` are a composite primary key

### Table: `Address`



<pre><code>| AddressID | Zip | State |
|-----------|-------|-------|
| 1 | 96701 | 2 |
| 2 | 05001 | 1 |
| 3 | 05602 | 1 |</pre></code>

Where `AddressID` is the primary key

----


**What is the appropriate DB constraint to add to `Address.State`?**



It should *always* be a value that is present in `CodeValues Where CodeSet = 'States'`, but I don't believe I can create a [Foreign Key that is part of a Composite Primary Key][1]

Should it just be a [check constraint based on a query][2] like this?

```sql
CREATE FUNCTION checkCodeValues(
 @codeSet VARCHAR(50),
 @codeValue SMALLINT

)
RETURNS BIT
AS
BEGIN
 IF EXISTS (SELECT * FROM CodeValues WHERE CodeSet = @codeSet
 AND CodeValue = @codeValue)
 RETURN 1
 RETURN 0
END
```

```sql
ALTER TABLE Address
 WITH CHECK ADD CONSTRAINT CK_State
 CHECK (checkCodeValues('States', State))
```

My concern is that db design tools won't really recognize the full impact of this constraint against, when it really is a FK, but just against a subsection of the table.

[1]: https://stackoverflow.com/q/12679312/1366033
[2]: https://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint

Add FK / Constraint to subsection of table

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