Tag: sql-server-2012

Index on a Date column – use Between/And or Greater/Less

I was asked this question at an interview. AdventurWorks2012.Sales.CreditCard table. First, I created a non-clustered index on ModifiedDate column to simulate the interview question. I need to find records for, let’s say July, 2007 SELECT cc.CreditCardID, cc.ModifiedDate FROM sales.CreditCard cc WHERE cc.ModifiedDate >= ’07/01/2007′ OR cc.ModifiedDate < ’08/01/2007′ ———— SELECT cc.CreditCardID, cc.ModifiedDate FROM sales.CreditCard cc […]

MSSQL CLR Trigger problems

So normally I don’t get stuck on SQL and I don’t give up with ample amounts of my own research. but I have exhausted all thoughts I can and all possibilities that I can think of. Situation: programmer built a CLR trigger for me. ( I am SQL Guy, not C# guy). This trigger uses […]

Finding columns used in a calculation in a SQL Server view

I’m trying to put a data dictionary together for a client using SQL Server MDS. In order to avoid tracking the specific location in the data warehouse/marts/source systems of the fields being defined, I’m hoping to use the system views as much as possible. This works fine for tracking the fields themselves. However, the users’ […]

Perfomance difference executing a view with NHibernate

I have a SqlServer 2012 view with about 10 Inner Joins running in a table with >2 millions rows. 1 – Executing that view with NH 4.1 (using Query with Linq) takes ~20 seconds 2 – Executing that view with NH 4.1 (using CreateSQLQuery with sql generated by the query above) takes ~2 seconds 3 […]

Finding original start date to absence period

Could you please take a look at the following task? I have DATA table (it contains data for previous week): CREATE TABLE DATA ( EMPLOYEE nvarchar(50), ABSENCE_START_DATE datetime, ABSENCE_END_DATE datetime, ABSENCE_TYPE nvarchar(50) ) ABSENCE_START_DATE – date when absence starts ABSENCE_END_DATE – date when absence ends ABSENCE_TYPE – type of absence Current table contains the following […]

What is the difference between nvarchar(4000) and nvarchar(max)?

This question already has an answer here: What is the maximum characters for the NVARCHAR(MAX)? 5 answers Are there any disadvantages to always using nvarchar(MAX)? 22 answers

SQL Server hierarchy query for primary/secondary variables

This is going to be tough to explain, but essentially we have a table of user tracked activities..example: customerid Year Month Day team_name Activityname 123456789 2017 6 1 CTM Officer ECM Clean-up (EOI) 123456789 2017 6 1 CTM Officer Investigation Simple 123456789 2017 6 1 CTM Officer RM – Call Regular 123456789 2017 6 1 […]

Extracting data from XML Nodes

I am testing this simple script and just wondering why it didn’t work. DECLARE @myDoc xml DECLARE @ProdID int SET @myDoc = ‘<Datatype xmlns=”V8_0_1″ id=”113″ name=”PF_CleaningMode” hdl=”47/4/SB8_3-910-8243-19/0/113″ odobjid=”915″> <Datatypevalue id=”2″ name=”Intermittent” /> <Datatypevalue id=”1″ name=”Continuous” /> <Datatypevalue id=”0″ name=”Off” /> </Datatype>’ SET @ProdID = @myDoc.value(‘(Datatype/@id)[1]’, ‘int’ ) SELECT @ProdID Hope you can assist. Thanks.

SQL Server: Grouping calculated column results

I’m a total newbie to SQL Server and was trying to learn by making tasks for myself to solve using Northwind database. I have hit a wall when I was trying to write a query that would result in giving me Net Total Value for whole order. dbo.[Order Details] has three columns that I need […]

Get all employees under manager with CTE

I have a table which has employee details EmpId ManagerId Level Value 1 0 5 CEO 2 1 4 EMP 3 1 4 ORG 4 2 3 NULL 5 2 3 NULL 6 2 2 NULL 7 1 1 NULL 8 5 0 NULL Now, I have to start wil Employee Id 2 and found […]

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