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).
0
votes
0answers
9 views
Whats wrong with this MDX query? [on hold]
I'm trying to the MDX query below :-
WITH
MEMBER [Measures].[OrgName] AS
[Organisation].[LEVEL8].CURRENTMEMBER.Properties("Name")
SELECT ...
3
votes
1answer
114 views
Using SQL with encryption
This is my first project I am doing in VB.NET, and also my first real programming project. There is sensitive data, so I am utilizing Microsoft's Encryption/Decryption class (clsCrypt).
For ...
12
votes
1answer
110 views
Calculating Lost Reputation
A discussion arose not long ago on the 2nd Monitor about how much reputation has been lost due to the reputation caps. There are a number of queries on the SEDE which try to address this:
A Users ...
2
votes
0answers
36 views
Am I getting the right information about installed SQL Server Instances?
I'm building an installer package for our software using Inno Setup. As a part of this, I'm also building a few different DLL's in Delphi XE2 to accommodate for some of the common tasks required by ...
4
votes
1answer
99 views
Code First Entity Framework
Is the following good design for doing entity framework code first? What am I missing for a production system? I haven't included all my code, just a snapshot...
EDIT: My application, doesn't update ...
8
votes
2answers
136 views
Should a year and month be stored as separate fields or as a date?
We have a table with calculated data that groups sales by product, year and month, to provide fast querying for statistics.
My colleague argues that the year and month should be two separate fields, ...
-6
votes
1answer
60 views
Stored Procedure logically or coding
I have this SP that I'd like improved. I've changed variable names for some reason. I am working on SQL Server 2000.
USE [DBName]
ALTER PROCEDURE [dbo].[Table_GetSomething]
@name ...
6
votes
1answer
73 views
Can you replace a REPLACE statement, or 9?
I would really like something to replace all of these REPLACE Statements.
SELECT DISTINCT
CAH.CaseNbr
, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
...
9
votes
3answers
120 views
SEDE Top Sponsors
I wanted to see the site's top sponsors - users that have paid bounties on questions that they didn't own.
I started off with a bounty-related existing query, selected the details into a subquery, ...
7
votes
2answers
41 views
SE Data Explorer Query: Average score for questions and answers, by tag
Being curious to see which tags on this site get the most attention, I developed a Stack Exchange Data Explorer query:
WITH CanonicalTags AS (
SELECT Id, TagName, Id AS AliasId, TagName AS Alias
...
1
vote
0answers
55 views
node.js Syslog server recording to ms-sql database
This 43 line program is designed to listen for Syslog messages on port 514 and record the messages to an MS SQL Server database table.
I would appreciate feedback from experienced node.js developers.
...
3
votes
1answer
41 views
Small query - removing XML tags
I have some code that I wrote almost a year ago exactly (1/9/2013) and I would like to know if I wrote it well or if it can be improved. I don't have any fun input or output, as these are not set ...
4
votes
1answer
59 views
SQL Join code check and peer review
I am still fairly green when it comes to SQL joins. The below code works but I want to check that I am doing it the best way before I copy and paste errors into other work in the future.
The idea of ...
3
votes
3answers
122 views
How to make my code faster and easier?
This is my .cs part code where I am calling storeprocedure in LINQ to SQL:
var rr_j_cat = db.allcategories().ToList();//its store procedure calling thousands of rows
if (rr_j_cat.Count() != 0)
{
...
2
votes
1answer
73 views
Best practice for storing arbitrary form data
I am currently storing form data in an XML format
<cfsavecontent variable="myFormData">
<cfoutput>
<ul class="xoxo">
<cfloop list="#form.fieldnames#" index="item">
...
4
votes
3answers
203 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 ...
2
votes
2answers
69 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
85 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
109 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
46 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
80 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
42 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
97 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
40 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
170 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 ...
4
votes
1answer
165 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 ...
2
votes
1answer
105 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
84 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
56 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
135 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
38 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
219 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
220 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
101 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
157 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
230 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:
...
-3
votes
1answer
98 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;
...
2
votes
2answers
124 views
Selecting the number of working days minus weekend days and UK bank holidays
I am fairly new to coding TSQL script and am 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 ...
1
vote
1answer
844 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
64 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
28 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
100 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
102 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
109 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
105 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
167 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
134 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
92 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
4k 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
212 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 ...
