sql-server-2012 - w3toppers.com

T-SQL – Insert Data into Parent and Child Tables

Here is my solution (based on the same answer I’ve linked to in the comments): First, you must add another column to your UDT, to hold a temporary ID for the employee: CREATE TYPE dbo.tEmployeeData AS TABLE ( FirstName NVARCHAR(50), LastName NVARCHAR(50), DepartmentType NVARCHAR(10), DepartmentBuilding NVARCHAR(50), DepartmentEmployeeLevel NVARCHAR(10), DepartmentTypeAMetadata NVARCHAR(100), DepartmentTypeBMetadata NVARCHAR(100), EmployeeId int ) … Read more

get a comma delimited string from rows [duplicate]

Use STUFF and FOR XML: Create and populate sample table (Please save us this step in your future questions) DECLARE @T AS TABLE ( Name varchar(10) ) INSERT INTO @T VALUES (‘John’), (‘Vicky’), (‘Sham’), (‘Anjli’), (‘Manish’) The query: SELECT STUFF(( SELECT ‘,’ + Name FROM @T FOR XML PATH(”) ), 1, 1, ”) As [output]; … Read more

SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry [duplicate]

This is all perfectly normal. Microsoft added sequences in SQL Server 2012, finally, i might add and changed the way identity keys are generated. Have a look here for some explanation. If you want to have the old behaviour, you can: use trace flag 272 – this will cause a log record to be generated … Read more

Auto increment primary key in SQL Server Management Studio 2012

Make sure that the Key column’s datatype is int and then setting identity manually, as image shows Or just run this code — ID is the name of the [to be] identity column ALTER TABLE [yourTable] DROP COLUMN ID ALTER TABLE [yourTable] ADD ID INT IDENTITY(1,1) the code will run, if ID is not the … Read more

How to check if a column exists in a SQL Server table

SQL Server 2005 onwards: IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N’columnName’ AND Object_ID = Object_ID(N’schemaName.tableName’)) BEGIN — Column Exists END Martin Smith’s version is shorter: IF COL_LENGTH(‘schemaName.tableName’, ‘columnName’) IS NOT NULL BEGIN — Column Exists END

LocalDB deployment on client PC

You don’t need to install SQL Server Express to use LocalDB, as LocalDB is SQL Server Express, just easier to install. Once LocalDB is installed you can use AttachDbFileName property of the connection string to “open” an MDF file. Keep in mind that the same file can only be opened by a single LocalDB instance … Read more

nvarchar concatenation / index / nvarchar(max) inexplicable behavior

TLDR; This is not a documented/supported approach for concatenating strings across rows. It sometimes works but also sometimes fails as it depends what execution plan you get. Instead use one of the following guaranteed approaches SQL Server 2017+ SELECT @a = STRING_AGG([msg], ”) WITHIN GROUP (ORDER BY [priority] ASC) FROM bla where autofix = 0 … Read more

Enable remote connections for SQL Server Express 2012

Well, glad I asked. The solution I finally discovered was here: How do I configure SQL Server Express to allow remote tcp/ip connections on port 1433? Run SQL Server Configuration Manager. Go to SQL Server Network Configuration > Protocols for SQLEXPRESS. Make sure TCP/IP is enabled. So far, so good, and entirely expected. But then: … Read more

Identity increment is jumping in SQL Server database

You are encountering this behaviour due to a performance improvement since SQL Server 2012. It now by default uses a cache size of 1,000 when allocating IDENTITY values for an int column and restarting the service can “lose” unused values (The cache size is 10,000 for bigint/numeric). This is mentioned in the documentation SQL Server … Read more

query sql between two tables [duplicate]

Here’s how I’d go about it – I wrote it in SQL because I’m WAY better at that than I am at LINQ, but converting from one to the other should be straightforward: select t1.id , t1.value from table1 as t1 inner join table2 as t21 on t21.idTable1 = t1.id and t21.number = 1 inner … Read more