Tag: sql-server-2012

Today vs. same day last 3 weeks

I have data like in the below example, each cell is number of events. | 29/09/2016 | 06/10/2016 | 13/10/2016 | Today | Hour | |————|————|————|——-|——| | 470 | 649 | 652 | 140 | 0 | | 159 | 322 | 354 | 925 | 1 | | 874 | 399 | 412 | […]

Alter Table 'DEFAULT' not setting default values – SQL Server

I am trying to alter an existing column and adding an integer column. While adding integer column i am setting DEFAULT as 10 but default is not setting while adding the column ALTER TABLE dbo.Contacts ADD Col1 INT DEFAULT 10 I require to do explicit update script to set all past records as default values […]

Transpose in SQL Server 2012

I have the following: Country | StateCity ——–+———— USA | MO USA | LA USA | OH CANADA | Ontario CANADA | Toronto and am looking to transpose and unify the header with a result like USA CANADA MO Ontario LA Toronto OH

SQLBulkCopy with CLR UDT gives “Could not find method 'Read' for type 'MyNamespace.MyType' in assembly 'MyType'”

I have written a SQL Server CLR User-defined type (UDT) in SQL Server 2012. I have been able to access it though SQL test scripts, and have used it as a local variable, defined it in a table, and tested it through Visual Studio and SQL Server Management Studio. We have an service that uses […]

Exclude whole group based on single value

Table T CID Code 1 A 1 B 1 C 2 C 2 A 2 B 3 A 3 D I want the output to have only those CID groups where Code <> ‘C’ Desired Output: CID Code 3 A 3 D Query Attempt: SELECT DISTINCT CID ,Code FROM Table T WHERE Code <> ‘C’ […]

Calculating SUM on 3 related tables

I have three tables that are in 1-to-many relationships. Since my business scenario is not easy to explain, I am going to use a more familiar terms: Customers -> Invoices -> InvoiceDetails Lets assume that there exist Customers.Value1, Invoices.Value2, InvoiceDetails.Value3, all of type double (real). I need to get the summaries of Value1, Value2 and […]

Temp Table creation is throwing error in same session even dropping and recreating also

I have below code. In this i am dropping and re-creating a same temp table in same session. But table is not getting dropped while creating it second time. It’s throwing an error.Can any one help me with reason? Thanks in advance. IF OBJECT_ID(‘TEMPDB..#Table1′,’U’) IS NOT NULL BEGIN DROP TABLE #Table1 END CREATE TABLE #Table1 […]

Executing Queries Stored in a Column of a Table

I need some help about executing queries stored in a column of a table. My table name is Stats. It includes some reporting queries like this: id | key | query —— | —— | —— 1 | reportA | select 1 2 | reportB | select 2 3 | reportC | select count(id) from […]

Automatic cache sizing for sequences in SQL Server 2012

I was trying to create a sequence in SQL Server 2012 using below T-SQL script: Create Sequence ConteoCycling AS int START WITH 2 MINVALUE 0 MaxVALUE 4 INCREMENT BY 1 GO I gave it a min (0) and max (4) range values so that it recycles itself once the given range is consumed. After successful […]

Purge job optimization

SQL Server 2008 R2 Enterprise I have a database with 3 tables that I am keeping a retention time of 15 days. This is a logging database that is very active and about 500 GB in size and eats about 30GB a day unless purged. I can’t seem to get caught up on one of […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.