1
vote
2answers
164 views

How to get Identity Column to jump ahead (by say 1000) on SQL Server 2012

I am using SQL Server 2012 and I would like to keep my pre-existing data but to cause the ID (identity) column to skip by 2000 rows. Why is this happening? I have a scenario where I may have ...
2
votes
1answer
101 views

How to find number of days since beginning of fiscal month?

This relates to my question How to find XXth day of previous month in SQL server? The fiscal month starts on 26th of each month. I need to find out from any date: The date of the start of the ...
0
votes
0answers
174 views

SQL Server table scan when query parameter doesn't exactly match column

I have a table with two columns, ID (bigint IDENTITY PRIMARY KEY) and TestId (nvarchar(50) NOT NULL). I've created an index on TestId. I insert a million rows as a test with random values for TestId. ...
3
votes
1answer
68 views

Single year column and multiple weeks columns into single year/week column with PIVOT

I have a table that looks roughly like this. (Pk int, Year int, Week1, Week2, .., Week53) The values in each "Week" column are sales data. What I want to do is create a new table from the first ...
2
votes
1answer
79 views

Finding rows with duplicate values

I have a one to many relationship between the Account table and a ScheduledPayment table. One account can have many payments but there should be a maximum of one payment per day. Although unlikely, ...
-7
votes
0answers
54 views

What's new in SQL Server 2014 T-SQL since SQL Server 2008 R2? [closed]

I want to upgrade my sql server from 2008 to 2014 and I wanna know what's new in programmability T-SQL enhancements.Now I'm installing 2014 to make few T-SQL tests. Thanks
1
vote
1answer
56 views

What relevance does the table-level ANSI_NULL setting have?

Apparently, SQL Server stores the current ANSI_NULL setting when creating a table (see related questions here and here). However, when I execute SELECT * FROM myVeryOldTable WHERE ...
2
votes
3answers
289 views

How to pull rows only from the weekends?

My table data is like this: ID Name salary date 1 a 100 1/08/2014 2 b 200 2/08/2014 . . . . 26 z 2600 26/8/2014 Now I want to return only rows from Saturday or ...
0
votes
2answers
80 views

Stored Procedure null parameter within where clause

I want to use a parameter within where clause only if it's value is provided by strongly typed dataset, this is what I am trying at the moment, I get right results when I provide parameter3 and no ...
0
votes
1answer
28 views

Backup/restore using OPENDATASOURCE OR OPENROWSET

Has anyone ever successfully taken a backup or restore to another server using OPENDATASOURCE/OPENROWSET? I am not able to use linked servers so I am exploring this idea.
0
votes
2answers
51 views

How to escape '%' inside varchar @params for RAISERROR funcion in SQL Server 2012

How I should escape the % character in params so my RAISERROR retun my message declare @msg varchar(max) = (SELECT ' Message with % ' AS MSG) if @msg is not null begin RAISERROR (@msg ,16,1); ...
0
votes
1answer
34 views

Difference between character_maximum_length and character_octet_length

Maybe there is an answer for this somewhere but, I couldn't find any efficient answer on Google for this question, therefore, I will ask this here. I wanted to get some information about my Stored ...
4
votes
0answers
51 views

Why is it @@SERVERNAME but not @@DBNAME? [closed]

I'm just curious: To get the server name, I use SELECT @@SERVERNAME but to get the current database name, I use SELECT DB_NAME() Technically, both seem to be scalar functions: @@SERVERNAME is ...
0
votes
1answer
69 views

Script that alters all stored procedure on the database

I have a database with thousands of stored procedures and I need to alter hundreds of them. In order to do it faster I thought of a way to write a script that runs over all stored procedures and adds ...
0
votes
2answers
97 views

How to reduce the run time of stored procedure by getting rid of cursors?

I have a task of reducing the run time of a stored procedure that runs for hours. Description of task: The cursor takes a set of values @Master and @Slave obtained from a query(5000+ rows), finds ...
0
votes
1answer
30 views

Script to Add a Job to a SQL Server DB

I want to add a job to my DB to make a back up weekly but i want to do it from an script, i've serched and with what i found i made this: CREATE SCHEMA job go CREATE PROC BACKUPS @dir varchar(max) AS ...
1
vote
1answer
22 views

Enable Agent Database Mail - TSQL

I have several new servers that need Database Mail configured on them. I found the TechNet article describing how to add the profile and account via procedure calls, but is there a way to enable ...
0
votes
1answer
45 views

Does it make sense to order condition in where query?

We have a clustered index in table Tbl with order of A,B,C. Does it make senses to write a query to have WHERE A = @a, B = @b, C = @c or it'll be as same as WHERE C = @c, B = @b, A = @a Does the ...
1
vote
1answer
61 views

HierarchyID: Get all descendants for a list of parents

I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I ...
2
votes
3answers
154 views

Removing a string prefix

I have a string of numbers that I need to trim a portion from using TSQL. The string of numbers will always start with 101 then it will have a set of 0s and a set of random numbers. Example: ...
0
votes
1answer
34 views

Derive daily delta from total daily counts? Tsql

I have a table with columns: ID, tableName, totalRowCount, Date Each day I'm appending the number of rows in the relevant table. What I want to do is create a table which has the number of new ...
5
votes
1answer
149 views

Splitting a csv column from select query into multiple columns

I have a stored procedure SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN ...
0
votes
1answer
33 views

Getting null block_st.text when querying for blocks

We are using the blocking query from this article which queries sys.dm_tran_locks. http://simplesqlserver.com/2013/05/21/blocking-capturing-and-monitoring/ The issue we've ran into is that often ...
1
vote
1answer
46 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
2
votes
2answers
236 views

How do I include nulls during comparisons in SQL Server?

So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results. For example, the following query doesn't return rows where value IS NULL ...
0
votes
2answers
118 views

Long running simple query SQL Server 2008 R2 [closed]

I have a very simple query running in a SQL Server 2008 R2 instance: SELECT TOP 10 * FROM messages m INNER JOIN recipients r ON m.id = r.id WHERE m.timeStamp > '2014-07-14T00:00:00.000' AND ...
4
votes
1answer
85 views

Symmetric difference operation in Transact-SQL?

I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and EXCEPT. I haven't been able to find an operator that does the ...
7
votes
3answers
188 views

Optimize select on subquery with COALESCE(…)

I have a large view that I use from within an application. I think I've narrowed down my performance problem, but I'm unsure how to fix it. A simplified version of the view looks like this: SELECT ...
2
votes
1answer
47 views

Strange behaviours with SET options in SSMS

I am investigating the effect of various SET options on the creation of indexed views on SQL Server 2014 CU1. The following code: ensures that the SET options are as they should be creates a table ...
2
votes
2answers
105 views

How I can force the TSQL-Developers to use not deprecated TSQL?

I want to avoid the using of deprecated code. Like the new feature Nr. 8 http://msdn.microsoft.com/en-us/library/ms178053.aspx I want to force them to use "WITH" for example, or a semicolon at the ...
3
votes
1answer
87 views

Sargable Queries

Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being ...
-1
votes
1answer
64 views

Divide single column value into multiple column depending upon the bit value of another column [closed]

I have a table containing following fields. Id varchar(5), TranDate date, TranType bit, TranAmount decimal(15,3) My table looks like this: Id TranDate TranType TranAmount ...
1
vote
1answer
51 views

set date value based on other row values

Sorry for the Header, I can't find the right way to describe what I want, possibly the reason why I can't get a solution by googling :) ... Here my problem: I've got a table with the following ...
0
votes
1answer
42 views

Join reorders results [duplicate]

Is there any particular reason why adding an additional join to an ordered table would reorder the result set? Using a left join on the existing result set table-valued function is similar to below ...
0
votes
1answer
87 views

How to deal with async calls to a stored procedure that updates data

I have asynchronous calls from Entity Framework to a stored procedure. The stored procedure selects data to a temp table and later on, it updates the real table. This works fine for sync calls. The ...
1
vote
2answers
1k views

Adding Data to Multiple Tables Using Single Query [closed]

I want to add data to multiple tables using a single INSERT INTO query, can I do this? Please guide me and provide me syntax if this is possible.
5
votes
2answers
245 views

Efficient query for an advanced EAV model

I'm kind of stuck on how to build an effecient query which returns the data of the following EAV structure. Today, a product table already exists, containing 4 fixed fields. We want to upgrade the ...
0
votes
1answer
53 views

Problem creating database using default mdf and ldf path

I'm trying to create a database using the default paths on several SQL Servers at once. To achieve this I'm trying to use a script that should create the database on each server, however I've run into ...
-3
votes
2answers
118 views

how to solve slow running sub query TSQL

I have a problem with a slow running query. I have analysed the query with the sql server profiler but I can't seem to find a good solution. I did find why it is running slow. If ask the full ...
-2
votes
1answer
42 views

Add default constraint from another table

How to add a default constraint to a table such that the value that will be used as default will be from another table's columns? i have created a table MY_TBL in which having a column CURRENCY_CD ...
0
votes
3answers
327 views

How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure?

Assume I have a table looks like this: Scores { Id uniqueidentifier, ScoredAt datetime, Value int } Basically I want to create a stored procedure that works similar to this ...
3
votes
3answers
365 views

T-SQL Calculate average value for Duration Field

Can anyone please help me on this.. I have a column in my table that stores the duration in an nvarchar field formatted as '00:00:00'. How do I calculate the average duration? I read some other ...
2
votes
1answer
59 views

SQL CMD Mode Issue

SET QUOTED_IDENTIFIER on declare @source_server_name sysname IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp; create table #tmp( id int identity(1,1), server sysname ) insert into #tmp ...
0
votes
2answers
166 views

NULL value returned when concatenating a string in SQL while loop! [closed]

I'm trying concatenate a string in a while loop, but it only returns NULL. Is there something I'm missing? declare @o int = 10000000/1000000; declare @i int = 0; declare @s nvarchar(200); while @i ...
1
vote
1answer
73 views

How to use COALESCE with multiple rows and without preceding comma?

I'm trying to achieve the following: California | Los Angeles, San Francisco, Sacramento Florida | Jacksonville, Miami Unfortunately, I'm getting ",Los Angeles, San Francisco, Sacramento, ...
0
votes
2answers
96 views

Run “USE” with linked server alternatives

I am using SQL server linked servers and find some difficulties while running queries. Below is the actual query. USe testdb SELECT 'Identitiy property OFF' where COLUMNPROPERTY( ...
0
votes
1answer
71 views

getting error incorrect syntax near @certificate

I have created a certificate and now I want to remove certificate signature and drop the certificate. When I supply the certificate name directly it is working but when I'm making use of local ...
1
vote
2answers
115 views

Change DateTime format

How can i return a date only format (DD/MM/YYYY) from a DateTime format column? example below Modified Date 2007-09-01 02:00:00.000 2005-09-01 05:00:00.000 Output: Date 09-01-2007 09-01-2005
6
votes
5answers
2k views

Why can't I use a CASE statement to see if a column exists and not SELECT from it?

Why does something like this not work? SELECT CASE WHEN NULLIF(COL_LENGTH('Customers', 'Somecol'), '') IS NULL THEN NULL ELSE Somecol END AS MyTest FROM Customers; I am just checking if the column ...
0
votes
1answer
48 views

Right outer join seems to not work because of where clause filter

I have the following query, I need to modify it, so that it gives me the result for originDocForCurrency.id = 68 if there is no matching for commission.referenzid = 68. I tried the RIGHT OUTER JOIN ON ...