Tag: sql

Regular Expression to accept 'System Admin' and 'Systems Admin' strings in SQL Server

I need to keep track of strings that are equal to ‘System Admin’ and ‘Systems Admin’ I used LIKE expression with square brackets but that should contains at least one character. LIKE ‘System[] Admin’ How do I track strings that are equal to ‘System Admin’ and ‘Systems Admin’

Deadlock for Select & Insert in SQL Server 2012

Deadlock identified between Select & Insert query. Googled a lot and i understand that Select query will default take Isolation Level to READ COMMITTED even if it is set or not. Here in my case Shared (S) is applied for Select query and for Insert query an Intent exclusive (IX) is applied. Have come across […]

MS SQL ORDER BY ASC then DESC for addresses

I would like to order results up the street then back down the other side for field review workflow (100, 102, 104, 105, 103, 101). I have : ORDER BY s.situs_street, CAST([situs_num]%2 as integer), CAST(situs_num as integer) This gets me all the evens then the odds but they are both ascending. Using : ORDER BY […]

Fragmentation of one specific index is increasing too often

I have a large table and it has more than 10 indexes. I have a fragmentation problem on one specific index. In the day hours, thousands of rows are being inserted in this table and the fragmentation of just one specific index is increasing very frequently. Other indexes are OK (maybe 0.01% per hour), but […]

If I have a cast in a where clause before I check another field first will it fail?

I have a scenario where I am aggregating data between two systems. Unfortunately I am using a table adapter in Visual Studio. Select * from prsnpsht where Cast(snp_check as bigint) > 10 and sourcereference Is Null Union ALL Select * from prsnpsht where sourcereference is not null In the case above, the snp_check from the […]

Compare getdate() with two different fields

I have 2 tables: T1 T2 id Effdate E_id DOB ————– ———— 1 20161212 2 1950-02-16 00:12:24 2 20130124 5 1978-01-16 10:14:30 I want to compare getdate() < Maxdate(effdate, DOB)? I am getting datetime conversion error. for example : getdate() < MAXDATE( 20161212 , 1950-02-16 00:12:24) expected result should be from table T1: id Effdate […]

SQL Server – Find col's name by specified value

I have couple of columns with specified names. I need to select name of column, where is specified value. Example: ———————————– id| a col | b col | c col | d col | ———————————– 1 | N | N | A | N | 2 | A | N | N | N | […]

Checking records from multiple tables

I have a student table, school table and Tour table which are connected. I have passed the schoolID to the student table to determine which school the student belong to and the tourID to determine the tour going on. In a tour I can register as many student as I want but they must be […]

SQL Azure query aggregate performance issue

I’m trying to improve our SQL Azure database performamce, trying to change the use of CURSOR while this is (as everybody told me) something to avoid. Our table is about GPS information, rows with a id clustered index and secondary indexes on device, timestamp and geography index on location. I’m trying to compute some statistic […]

Creating one many to many table that is reused for many different tables when they all have a relationship to the same entity

I have a table [Team] in my Database. Many other tables require a many to many relationship with this table. This is mostly due to various records in my other table having authorisation settings based on which team the current user is in. For example a record in my [User] table can be linked to […]

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