0
votes
2answers
47 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
38 views

Shrink Transaction Log While Using AlwaysOn Availability Group

We are using AlwaysOn Availability Group feature of SQL Server 2012. Regular full database backups and transaction log backups are done every day on the secondary database. I have read here doing the ...
0
votes
1answer
61 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 ...
2
votes
1answer
67 views

TSQL- Column DataType Length

Can anyone please explain me the below one. I used the below query in getting the table columns info, but the max length seems to be x*2 of the actual col length. I mean if I have a col with datatype ...
3
votes
1answer
189 views

Set simple recovery mode and shrink log files for all user created databases

I hope you can point me in the right direction. I'm not a frequent user of T-SQL, but I did some googleing, and found the script below. I corrected the script a bit. I want the script to: To select ...
0
votes
0answers
29 views

“Not In” returns no rows all the time [duplicate]

Whats wrong with the following query. The first part of the select always return 0 rows. DECLARE @AgentID varchar(20), @StringValues varchar(max), @ContactKey int, @Note ...
2
votes
2answers
111 views

Datetime column vs Date + Time columns

I am designing a table for a system that saves a number of logs. We are looking at ~200 entries per second. We are using SQL-Server 2012 Enterprise Edition. I have a question regarding breaking up a ...
2
votes
1answer
91 views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
11
votes
3answers
580 views

Get streak count and streak type from win-loss-tie data

I made a SQL Fiddle for this question if that makes things easier for anyone. I have a fantasy sports database of sorts and what I'm trying to figure out is how to come up with "current streak" data ...
0
votes
1answer
142 views

Keyword Search a single SQL Server table for multiple search terms and create a new table from results?

I am not a database administrator. Most of my experience is with MySQL. However I think what I want to do is in the realm of possibility. I want to text query a single large table in a SQL Server ...
0
votes
0answers
231 views

Row_Number() not working with OVER PARTITION BY

I have a query that I am "rolling up values". Lets say I have two rows that are the "same", save for one field: [CNumber] [PNumber] [date] [FullName] [Type] [P] [S] ...
1
vote
1answer
154 views

Return every instance of a string contains search

I have this sample table and data CREATE TABLE [dbo].[SampleTable]( [Id] [int] IDENTITY(1,1) NOT NULL, [TextColumn] [nvarchar](1000) NOT NULL, CONSTRAINT [PK_SampleTable] PRIMARY KEY ...
3
votes
1answer
256 views

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
0
votes
0answers
97 views

How can I get rid of the “No Join Predicate” warning

I have this fairly straight-forward snippet of a query: DECLARE @ClientGUID numeric(16) = 9000011916900200; SELECT ch.GUID, c.LastName, c.FirstName, mrn.ClientIDCode AS MRN FROM Client c INNER ...
1
vote
1answer
2k views

stored procedure with multiple statements does not execute all statements when run from job

** see updates below I have a stored procedure (SQL Server 2012) that deletes old records from a table, then inserts new records from a view to synchronize the table with the view. When I execute the ...
1
vote
1answer
62 views

Difference between sql agent account logged in as NTLM and UPN?

Can someone please tell the difference between a sql agent account logged in as NTLM and UPN? What is the difference if the sql agent account is logged in as: domain\username and username@domain.com? ...
0
votes
1answer
119 views

SQL Server sp_addrolemember in stored procedure for different database

I have a stored procedure to restore the latest backup of a prod database to an acceptance database. Once it's restored I need to grant a user read access to the database. Use statements are not ...
0
votes
4answers
100 views

T-SQL: Complete Database in one table

at a relational data model every logical relation is a single table with values and so on. I know someone who is this too much work and he wants to create only one table which contains all. This could ...
3
votes
1answer
131 views

Frequent Delete & Insert, impact on performance

In my application, we have very few updates. Most of the data gets deleted and inserted. My question is, how this will impact the performance? We are using SQL Server 2012. Table statistics (will ...
0
votes
2answers
189 views

why stored procedure performance is very high compared to plain select query

I have a table with more than 2 million rows, with needed indexes. When I run a simple query it takes so much time so I decide to stop executing it; while when I wrap the query in a stored procedure, ...
1
vote
1answer
137 views

Performance of dynamic SQL Server reporting queries?

I have to build an application that allows the end user to select what columns they want in their report. For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns ...
5
votes
1answer
212 views

How to avoid a race condition with cross db proc calls? and other concerns

I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a ...
1
vote
2answers
450 views

Expose temp tables to SSRS in stored procedure

Problem: Compare data of a given date range to the same data of up to 3 previous years, and display the tabular data and a graph in an SSRS report. However currently, my stored procedure that I have ...
2
votes
1answer
37 views

What is the difference between node types and atomic types in XML

Im Studying TSQL and I came to the chapter regarding XML, and here a question that I cannot find an answer: "What is the difference between node types and atomic types"? Thank you!
3
votes
1answer
2k views

How to changes or update local server connection in Maintenance plan job

Two days back our client changed one of our Dev Server name After Server Renamed, All my maintenance jobs and other jobs are failing because server name mismatch. We are using sql server 2012 ...
0
votes
2answers
122 views

Using IF and other conditions in T-SQL (trying to import data from Oracle using SQL Server SSIS)

I am using SQL Server 2012 SSIS package to fetch data from Oracle and insert it into SQL Server 2012. I am doing it for the first time and every thing is fine i.e. all data from a particular table ...
3
votes
3answers
143 views

Inserting two rows from single row

I have to import users from the usersToImport table into the userContact table. usersToImport contains telephone and e-mail information in a single row for each user, but userContact stores one kind ...
0
votes
1answer
162 views

Can this query be optimized further?

I have 100K records in my Documents table. This is my query which gives a page of 50 records in roughly 750 ms. I would like to further bring it down if possible. I am using Row_Number function to ...
2
votes
1answer
438 views

How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used

I have a function that includes: SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS This is useful, for example, to remove accents in French; for example: ...
2
votes
1answer
113 views

Query does not use index when selecting all records [closed]

This is my first time handling a large database, and I was wondering why the two queries below are different in terms of execution plan. I have setup an index on a specific table: CREATE ...
0
votes
1answer
1k views

Uncommitable T-SQL transaction after trigger applied to table

Good morning, I have written a Trigger on a table that should evaluate the entered values (Length, width and Height) and based on those entered values, should update a column (Type) in the same record ...
3
votes
1answer
50 views

How is ORDER BY logically Processed

Take this example: SELECT SalesOrderID, CustomerID, SalesPersonID, OrderDate FROM Sales.SalesOrderHeader ORDER BY OrderDate, SalesOrderID DESC OFFSET 0 ROWS FETCH NEXT ...
3
votes
3answers
834 views

Select rows after a specific ID

I have the following query, it chooses from a news articles table. Due to multiple sorting factors, I cannot locate the next page of date. The parameter I have is the last ID in the query result set, ...
1
vote
1answer
47 views

Foreign Key off of a UNIONed View

I have a view that looks like this: CREATE view reference.Test WITH SCHEMABINDING as SELECT reference.HighlevelTestId as TestId, Name, IsActive, cast(1 as bit) as ...
-2
votes
1answer
260 views

How to enter multiple checkbox list items into database for eventual reporting [closed]

(I tried posting this on SO but went unansered. I'll just post these theory type questions here from now on) I'm in the process of creating the first of a few CheckBoxList for a manually created ...
2
votes
1answer
107 views

Execution Plan not matching Stored Proc

Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists. The execution plan shows an INNER JOIN of the M and A tables, where in the ...
1
vote
1answer
101 views

Can I use Try_Cast to give me a query result column with different datatypes?

I have a column that is varchar(max). It contains mostly valid XML. But sometimes it has invalid xml (that I still need). I am trying to make a view for this table that allows the value to display ...
1
vote
3answers
163 views

DB comparer field by field for all tables

I have a liveDB and I have a development DB. Now when I make a change to development DB, I want to find out the changes later when I am deploying my application on server. For eg. I may have added ...
3
votes
3answers
995 views

Conditional WHERE Clause in a Table Valued Function

I have a table valued function CREATE FUNCTION [dbo].[DateRange] ( @StartDate date, @EndDate date, @Location varchar(25), @Device varchar(25) ) ...
7
votes
2answers
1k views

How to flatten results of a table with two related “many” tables?

I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them. I have the following tables (somewhat abbreviated ...
1
vote
1answer
2k views

Backup stored procedure for full, differential and log backup

I wrote a simple stored procedure for backing up FULL, Differential and LOG backup. This is my stored procedure: Alter PROCEDURE BackupDB ( @BaseLocation varchar(1024), @BackupType ...
9
votes
3answers
2k views

Unique Identifier with Extra Characters Still Matching in Select

We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID. For ...
0
votes
2answers
356 views

Is there a sortable ('order by') alternative to CURRENT_TIMESTAMP

I'm in the process of looking for a way to find a sortable alternative to a column that is populated with CURRENT_TIMESTAMP and I'm stumped at the moment. I have a web based form with multiple ...
3
votes
1answer
453 views

LIKE character length limitation

The background to this question can be found here. The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be ...
2
votes
1answer
168 views

Parameter triggers different execution plan, but why?

I have a table CREATE TABLE [dbo].[Numbers] ( [Date] [date] NULL, [Time] [time](3) NULL, [Value] [char](10) NULL ) and the table has > 10 Billion rows, therefore it ...
9
votes
1answer
563 views

Forcing an index spool

I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear. However, I end up with a ...
1
vote
1answer
969 views

SQL Server Full-Text Indexer with stoplists/stopwords

As I'm working on a graduate project (Textmining with SQL Server 2012 Semantic Search) I run into a situation where I need to post a question on this website, hoping someone can help me. This ...
6
votes
3answers
3k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...
4
votes
1answer
2k views

SQL Server 2012 is putting [brackets] around Table and Column Names

Is there anyway to turn that off? I'm unable to find anything in options, and they're really a distraction.