snipt

Ctrl+h for KB shortcuts

SQL

SQL - Custom Identity Column with Char

CREATE TABLE TableName (
[tID] [int] IDENTITY (1, 1) NOT NULL ,
[dat] INT,
[ProperID] AS ('I' + CAST([tID] as varchar))
) ON [PRIMARY]
GO

----------- WITH PADDING
CREATE TABLE [dbo].[RCTIPeriods](
	[rctiPeriodID] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceNo] AS ('H' + RIGHT('00000000'+ CAST(rctiPeriodID AS VARCHAR(6)), 6)),
	[rctiMonth] [int] NULL,
	[rctiYear] [int] NULL,
 CONSTRAINT [PK_RCTIPeriods] PRIMARY KEY CLUSTERED 
(
	[rctiPeriodID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
https://snipt.net/embed/ea815e54105f8d3cc2995d0b5781d939/
/raw/ea815e54105f8d3cc2995d0b5781d939/
ea815e54105f8d3cc2995d0b5781d939
sql
SQL
18
2019-06-16T17:30:28
True
False
False
/api/public/snipt/13620/
sql-custom-identity-column-with-char
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">TableName</span> <span class="p">(</span> </span><span id="L-2"><a name="L-2"></a><span class="p">[</span><span class="n">tID</span><span class="p">]</span> <span class="p">[</span><span class="nb">int</span><span class="p">]</span> <span class="k">IDENTITY</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span> <span class="mi">1</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="p">,</span> </span><span id="L-3"><a name="L-3"></a><span class="p">[</span><span class="n">dat</span><span class="p">]</span> <span class="nb">INT</span><span class="p">,</span> </span><span id="L-4"><a name="L-4"></a><span class="p">[</span><span class="n">ProperID</span><span class="p">]</span> <span class="k">AS</span> <span class="p">(</span><span class="s1">&#39;I&#39;</span> <span class="o">+</span> <span class="k">CAST</span><span class="p">([</span><span class="n">tID</span><span class="p">]</span> <span class="k">as</span> <span class="nb">varchar</span><span class="p">))</span> </span><span id="L-5"><a name="L-5"></a><span class="p">)</span> <span class="k">ON</span> <span class="p">[</span><span class="k">PRIMARY</span><span class="p">]</span> </span><span id="L-6"><a name="L-6"></a><span class="k">GO</span> </span><span id="L-7"><a name="L-7"></a> </span><span id="L-8"><a name="L-8"></a><span class="c1">----------- WITH PADDING</span> </span><span id="L-9"><a name="L-9"></a><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="p">[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">RCTIPeriods</span><span class="p">](</span> </span><span id="L-10"><a name="L-10"></a> <span class="p">[</span><span class="n">rctiPeriodID</span><span class="p">]</span> <span class="p">[</span><span class="nb">int</span><span class="p">]</span> <span class="k">IDENTITY</span><span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">1</span><span class="p">)</span> <span class="k">NOT</span> <span class="k">NULL</span><span class="p">,</span> </span><span id="L-11"><a name="L-11"></a> <span class="p">[</span><span class="n">InvoiceNo</span><span class="p">]</span> <span class="k">AS</span> <span class="p">(</span><span class="s1">&#39;H&#39;</span> <span class="o">+</span> <span class="k">RIGHT</span><span class="p">(</span><span class="s1">&#39;00000000&#39;</span><span class="o">+</span> <span class="k">CAST</span><span class="p">(</span><span class="n">rctiPeriodID</span> <span class="k">AS</span> <span class="nb">VARCHAR</span><span class="p">(</span><span class="mi">6</span><span class="p">)),</span> <span class="mi">6</span><span class="p">)),</span> </span><span id="L-12"><a name="L-12"></a> <span class="p">[</span><span class="n">rctiMonth</span><span class="p">]</span> <span class="p">[</span><span class="nb">int</span><span class="p">]</span> <span class="k">NULL</span><span class="p">,</span> </span><span id="L-13"><a name="L-13"></a> <span class="p">[</span><span class="n">rctiYear</span><span class="p">]</span> <span class="p">[</span><span class="nb">int</span><span class="p">]</span> <span class="k">NULL</span><span class="p">,</span> </span><span id="L-14"><a name="L-14"></a> <span class="k">CONSTRAINT</span> <span class="p">[</span><span class="n">PK_RCTIPeriods</span><span class="p">]</span> <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="n">CLUSTERED</span> </span><span id="L-15"><a name="L-15"></a><span class="p">(</span> </span><span id="L-16"><a name="L-16"></a> <span class="p">[</span><span class="n">rctiPeriodID</span><span class="p">]</span> <span class="k">ASC</span> </span><span id="L-17"><a name="L-17"></a><span class="p">)</span><span class="k">WITH</span> <span class="p">(</span><span class="n">IGNORE_DUP_KEY</span> <span class="o">=</span> <span class="k">OFF</span><span class="p">)</span> <span class="k">ON</span> <span class="p">[</span><span class="k">PRIMARY</span><span class="p">]</span> </span><span id="L-18"><a name="L-18"></a><span class="p">)</span> <span class="k">ON</span> <span class="p">[</span><span class="k">PRIMARY</span><span class="p">]</span> </span></pre></div> </td></tr></table>
sql