Tag: sql-server-2012

SQL Sliding Math

I have a requirement to achieve this in SQL YYYYQ| A | Desired Column ————————– 3Q12 | A1 | A1 4Q12 | A2 | A1 * A2 1Q13 | A3 | A1 * A2 * A3 2Q13 | A4 | A1 * A2 * A3 * A4 I’m using SQL 2012 and tried to use […]

Finding duplicate values in a table with a twist

I have a data file that has many duplicate values. I want both the original and the duplicate values to be identified and original and duplicate values to be ordered side by side. My Data files headings are like this along with the data: I want the data to be like this: I have already […]

Query section symbol (§) i SQL Server 2012 full text index

We are using the SQL Server 2012 for doing full text indexing of legislative documents. However, it appears that in 2012 it is not possible to create queries containing characters like the section symbol (§). I can’t seem to find the documentation on MSDN that states which characters are “un-queryable”. In our use case, it […]

Insertion without using cursor

Hi I have a procedure in MS SQL Server 2012, in which I am performing some insertions and updating but in some piece of that procedure, I am using cursor for looping in insertion. Can some one please help me doing that looping insertion without using cursor? Complete code is below ( @TerritoryName varchar(200), @TerritoryDescription […]

TVP vs loop for each parameter

I am in a situation where I need to do some comparison for some products that are in a shopping cart. The cart can have as many items as the user desires but the average over time is roughly 5-15 items per cart. My question is one about optimization/overhead. I need to check what department […]

Fixed VS Flexible roles in DB

I was reading an article at http://www.codeproject.com/Articles/561797/Top-20-exciting-features-of-SQL-Server-2012-Part-2#Featurenumber9(Revolution):-Powerview There I read Let’s say you have two sets of database user one programmers and the other DBA’s. The programmers should be able to fire insert, update and delete queries while DBA’s should be able to create database, backup and do maintenance related activities. But DBA’s should not […]

How to retrieve SQL Server Check Constraint Dependency Columns

If I create the following table create table test( id1 int, id2 int, id3 int constraint CK1 check (id3 > 2), constraint CK2 check (id1 > id2), ) I can find the dependencies of CK1 by querying select * from sys.check_constraints. The parent_column_id will return the correct answer of 3. The However, CK2 is a […]

checktable with repari_allow_data_loss taking so much time

I am not sure whats going on. But, When i run alter database myDatabase set emergency ALTER DATABASE myDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE dbcc checktable (‘myTable’, repair_allow_data_loss) alter database myDatabase set multi_user Its taking so much time. (running for last 30 minutes and still running) Can anyone tell me, is something going wrong or […]

How can I merge these 2 queries together?

I’ve written a query to lookup column metadata based on a database (Tracker) and table (Work): SELECT Cols.Name, TYPE_NAME(Cols.user_type_id) Type, CAST(ISNULL(OBJECTPROPERTY(OBJECT_ID(Keys.CONSTRAINT_NAME), ‘IsPrimaryKey’), 0) AS BIT) PrimaryKey, Cols.is_identity [Identity], Cols.is_nullable Nullable, CAST(ColumnProperty(object_id, Cols.name, ‘IsComputed’) AS BIT) Computed, CASE WHEN Cons.CONSTRAINT_TYPE IS NULL THEN NULL ELSE Keys.CONSTRAINT_NAME END ForeignKey FROM sys.columns Cols LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE Keys ON […]

TSQL – Extract ID that starts with specific char and has a certain length from string

I want to extract badly entered IDs from a column. I know that the ID always starts with E0, and is always followed by exactly four digits. Here’s my approach so far: declare @t table(myvalue varchar(100)) insert @t values(‘_badE04746_bad’) select * from @t select stuff(myvalue, 1,patindex(‘%[^0-9]E0[0-9]%’, myvalue + ‘0’), ”) from @t I’m able to […]

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