Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).
3
votes
3answers
140 views
Speed up application and avoid SQL Timeouts
The code works just fine, but recently performace has taken a hit and it has SQL Timeouts far too often. I have it set up so I can run 50 different version of the application two for each different ...
1
vote
1answer
38 views
Can this be made to run faster?
This is a stored procedure that takes 5-30+ minutes to run depending on the parameter they select.
It also has a nasty side effect of clogging down our SQL Server.
SET NOCOUNT ON;
DECLARE ...
3
votes
1answer
70 views
Query too slow - Optimization
I am having an issue with the following query returning results a bit too slow and I suspect I am missing something basic. My initial guess is the 'CASE' statement is taking too long to process its ...
2
votes
2answers
91 views
Injecting data into XML using a dll
This is a template for one of our data injection tokens.
When I first looked at the template, it was worse than it is now. I would like some input on this because I think that it can be cleaned up ...
1
vote
1answer
32 views
My date range generator SQL code - any limitations [closed]
I made some code to generate date ranges in SQL server 2005 and above. I am not sure if there will be any problems in this. Can you find any bugs or limitations in my code ?
Declare @StartDate ...
3
votes
2answers
64 views
SQL query clustered with repeated function calls
I have the following SQL query that computes for every date the week it falls in (a week begins on Sunday and ends on Saturday):
SELECT EntryDate
,CAST(DATEADD(DAY, 1-DATEPART(WEEKDAY, ...
1
vote
1answer
36 views
Checking Synchronization issue in SQL?
I have the following SQL. I want to make that the SP should work under load. Means it should not have any synchronization issues.
ALTER PROCEDURE [dbo].[DeleteOldDeviceID]
(
@OldDeviceID ...
1
vote
2answers
86 views
Review single step approach, rather than multiple temp tables
Overall the intent is to consolidate individual sales transactions to total debits/credits summing up the totals by year and month--retaining a running balance from start to end. This code uses ...
2
votes
2answers
35 views
SQL Server 2012: How do I eliminate multiple statements in this code to a more efficient, single statement?
This code completes under a second, however its seems like I've taken the "Mr. Bean" way around getting there.
The goal is to get the average number of orders processed in a single day based on how ...
2
votes
1answer
151 views
DAL Efficiency Help
I am attempting my first try at some c#. So far I love it, (not more than vb tho ;)) however, I am wondering if I can make these classes a bit more efficient.
Please note ###Test is my attempts at ...
2
votes
1answer
74 views
Exporting PDF From Database back to PDF Format
Answer to Exporting PDF's from SQL Server DB and writing a Map to a Text File
this was a Question that I asked in StackOverflow and then answered it when I was finished with the project.
I would ...
3
votes
1answer
88 views
Need reviews for an authentication system
AccountController Code:
public class AccountController : ApplicationController
{
public AccountController(ITokenHandler tokenStore, IUser user) : base(tokenStore, user){}
public ...
2
votes
3answers
68 views
Code conversion from Access to T-SQL
I have an Access database that I'm converting to SQL Server 2008. One of the queries in the database uses the LAST function. The table has a AutoNumber ID so true order is simple.
SELECT ...
1
vote
1answer
42 views
Metadata query performance optimization
SQL Server 2008 R2 + .NET 4.5:
I have the following metadata table:
CREATE TABLE [dbo].[Metadata](
[Id] [uniqueidentifier] NOT NULL,
[ContentId] [uniqueidentifier] NOT NULL,
...
1
vote
1answer
129 views
SQL with several joins
Is this query acceptable in terms of performance (I get the correct data) or can it be optimized?
SELECT
bankInstitution.name,
person.firstName,
person.surname,
offer.offerId,
...
1
vote
2answers
34 views
Store Procedure Timing out sometimes?
Can someone please review my this Store Procedure which is timing out?
ALTER PROCEDURE [dbo].[Insertorupdatedevicecatalog]
(@OS NVARCHAR(50)
...
2
votes
0answers
119 views
Compare Oracle Table with Sql Server Table and Update/Insert
Below is the current code wish used to update/insert records from Oracle view to Sql server table using Dapper. there is not field to check last record updated date in the oracle view so I have added ...
1
vote
2answers
171 views
My simple CMS system - security and code obscurity?
I'm working on a simple CMS with the intent of making it as secure as possible (a personal challenge) and the code as clean as possible. I think I've a long way to go so I would appreciate any input, ...
3
votes
1answer
99 views
Optimising a query
Consider the query given below:
SELECT * FROM ((SELECT c.cust_id ,
c.username ,
REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group,
...
0
votes
0answers
101 views
Binding and iterating a database table to a List<T> in C#
I would like to iterate through the records of a database table using a C# List of my custom class. Is this the most performant way to handle this? (Simplified for readability)
static void ...
2
votes
1answer
151 views
Extract data from one column of SQL Server table
I have an SQL Server table from which I want to extract data from one column (DD) and return as multiple columns.
The data is in NVARCHAR and looks like:
...
-2
votes
1answer
77 views
Oracle to MS SQL Server 2008 Code Conversion Problems [closed]
CREATE OR REPLACE FUNCTION CHI_X2 (a1 in number, b1 in number, a2 in number, b2 in number)
RETURN NUMBER IS
DECLARE @tr1 INT;
DECLARE @tr2 INT;
DECLARE @tc1 INT;
...
1
vote
2answers
69 views
Code review on selecting the number of working day minus weekend days and UK Bank holidays
I am fairly new to coding TSQL script so looking for some second view on my script.
The goal here is to to pull in some data and amongst that data to show a field with a counting the number of working ...
1
vote
1answer
585 views
how to write complex stored procedure very simple way
i have one stored procedure like this:
declare @compl integer = null, @transid integer = null, @complid integer = null
select @transid = t.transactID
from Transaction_tbl t
where ...
1
vote
4answers
59 views
How to refactor the following sql statement?
Declare @IsExistRoleAR As bit
Declare @IsExistRoleEN As bit
Declare @IsExist AS bit
set @IsExistRoleAR=(SELECT CASE WHEN COUNT(RoleID) > 0 THEN 1 ELSE 0 END AS isExists
FROM ...
1
vote
1answer
27 views
Archiving data while running update statement
I'm using sql server and I'm trying to update certain columns in a table but before it updates the data, I want to insert the data into another table.
This is my code
declare @table1 table (Id int, ...
3
votes
1answer
90 views
SQL Server 'Execute As'/Revert pattern in a 'Try/Catch' Block
I wish to ensure I am using the "best" pattern when using an Execute As/Revert from within a Try/Catch block on SQL Server 2012. The below code "seems" to behave correctly... Am I missing anything or ...
1
vote
2answers
97 views
How can I make this WHERE clause more readable?
Without changing the semantics and performance, how can I make this where clause more readable?
where
(@Category = 'all' or
(@Category = 'omitted' and Category is null) or
(@Category = ...
1
vote
1answer
95 views
Looking to optimize SQL Server merge statement
I have a merge statement that takes around 10 minutes to process 5 million or more records.
The merge statement is part of a stored procedure that takes my newly bulk loaded staging table then runs ...
2
votes
1answer
104 views
How can I improve the following stored procedure?
I have created the following stored procedure which duplicates a record in a table and also all its related records in other tables however since I am a newbie to SQL I would appreciate it if someone ...
3
votes
2answers
149 views
Simple DBLayer class review
I have created a simple DBLayer class. But I am not sure whether this class have any bug/issue using in production.
public class DBLayer
{
private static string connectionString = ...
1
vote
1answer
115 views
Request review of sql validation trigger
I have the following SQL structure (simplified for brevity):
TABLE [Posts]
[Id] INT NOT NULL IDENTITY PRIMARY KEY,
[ParentId] INT NULL,
[Type] INT NOT NULL,
FOREIGN ...
0
votes
1answer
91 views
Query Performance too Slow
Im having performance issues with this query. If I remove the status column it runs very fast but adding the subquery in the column section delays way too much the query 1.02 min. How can I modify ...
1
vote
2answers
3k views
SQL stored procedure that returns a boolean value?
CREATE PROCEDURE dbo.foo
AS
BEGIN
DECLARE @true BIT, @false BIT;
SET @true = 1;
SET @false = 0;
IF (some condition)
Select @true;
ELSE
Select @false;
END
SQL is not the language that ...
-1
votes
1answer
177 views
Using xp_cmdshell
Am I doing it fine?? Its only for setup not executed repeatedly
CREATE TABLE #temp
(
id INT IDENTITY(1, 1),
name_file VARCHAR(500),
depth_tree ...
0
votes
1answer
152 views
Daily, Weekly, Monthly Individual Tech time in task Repoert
I'm trying to create daily, monthly and weekly SQL Query report to our services time we spent int task and total billing time just want to see if I'm on right track
GO
--Daily
SELECT ...
2
votes
1answer
122 views
SQL PreparedStatement; Am I doing it right?
I am building a web app with a single (not pooled) full time (jdbc) connection between static classes and the database. This is expected to be a low traffic site and the static methods are ...
1
vote
1answer
146 views
Sql query to obtain totals and subtotals
I'm interested in knowing if there's a better/cleaner/more efficient way, to obtain totals and subtotals within a query, than my solution below.
The query works fine but I'm just intrigued to know if ...
2
votes
1answer
1k views
Custom Paging in ASP.Net Web Application
I have following code for doing custom paging from an asp.net web application.
Points of interest
It uses Link Buttons as suggested in
...
2
votes
2answers
226 views
Subquery v/s inner join in sql server
I have following queries
First one using inner join
SELECT item_ID,item_Code,item_Name
FROM [Pharmacy].[tblitemHdr] I INNER JOIN EMR.tblFavourites F ON I.item_ID=F.itemID
WHERE F.doctorID = ...
0
votes
2answers
139 views
Backup a database over an SQL connection
Developing some industrial WinForms application for some industrial setting, I wanted to provide users of our software with a convenient way to back up the database the software uses (to send it to ...
1
vote
1answer
88 views
Throwing a custom error in a stored procedure
I'm wanting to throw an error when reference keys are missing, however instead of failing through a integrity check, I want to list out the missing keys. I've created the below which works. However ...
5
votes
2answers
184 views
SQL Query Tuning
I have a mammoth SQL statement, however it's taking a long time to load (27 secs on the server). I think the issue lies with the IN statements towards the bottom (the IN statement is repeated too, can ...
1
vote
3answers
386 views
Using a Dedicated Class & Constructor to initialize Stored Procedure for SQL Transaction
I will try to be as concise as I can:
The goal: trying to universalize a specific section of a project, that is dealing with the SQL database transactions.
To assist you with your answer, I've ...
0
votes
1answer
189 views
Is this a good way to limit the occurrence of OutOfMemoryException?
I have a class that uses the SqlBulkCopy to bulk insert data into a SQL Server database. My original implementation just passed the m_buffer (which is a reference to a class that implements ...
0
votes
1answer
177 views
Review Insert/Update stored procedure
I am not a DBA nor do I play one on TV.
My application is retrieving information from Facebook and storing it into our own database. I want to retrieve the Location Post information of Id and ...
1
vote
1answer
287 views
How to dump UCCX stored procedure results via c# to SQL Server
I am writing a simple-ish c# CLI app to improve our telephony reporting system.
At present, I have a nightly DTS package which dumps a couple of tables from the Cisco telephony box into a database ...
1
vote
2answers
152 views
Is My Data Access Leaking Connections?
Folks
I have an N-Tiered ASP.Net Web Forms application which uses Enterprise Library 5.0 for data persistence. Recently I have noticed my application has been spitting out the following error
...
3
votes
1answer
155 views
Is it necessary to replace this cursor in SQL Server 2005?
The Problem
I have a cursor that I am trying to replace (perhaps unnecessarily) in an attempt to clean up a stored procedure. Essentially what it is doing is counting each note for each member in a ...
6
votes
1answer
94 views
Auditing a complex mix of reasonably small tables
I plan to use the following approach to audit the tables that represent user-editable configuration of an automated system (over the course of system's life these will be inevitably extended in their ...
