Sub select vs declared variable in where

A question on where clauses. which is more efficient? Select * from tbl t where t.date > (select convert(date,’2013-08-21′)) Or Declare @dt as date Set @dt = (select convert(date,’2013-08-21′))) Select * from tbl t where t.date > @dt thanks

I have created a view. But I want to call it from my database instance

Here is my view. And i have run it in msdb. I have a database name ‘mytest’ IF OBJECT_ID(‘[dbo].[vwconfirmEmailSent]’) IS NOT NULL DROP VIEW [dbo].[vwconfirmEmailSent] GO CREATE VIEW [dbo].[vwconfirmEmailSent] AS SELECT sa.recipients, sa.sent_status, sa.sent_date FROM sysmail_allitems sa LEFT join sysmail_sentitems ss ON ss.mailitem_id = sa.mailitem_id LEFT JOIN sysmail_unsentitems su ON ss.mailitem_id = su.mailitem_id When i […]

Replace with like

I have an ms sql table with brief_n as a column which holds records like this xx/xx/xx: Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed di where xx/xx/xx is the corresponding date. How do I get rid of all the date and just have Lorem ipsum dolor sit amet, consectetuer adipiscing elit, sed di. […]

Variables in MS SQL Bulk Insert path string

I need to import 30 .csv files into an MS SQL SERVER database. I can use BULK INSERT, but because of the number of files I want to do it with loop. Each file is named as DP(1).csv, DP(2).csv, DP(3).csv, …, DP(30).csv. I did write a WHILE loop and with a counter that can also […]

SQL Aggreate Functions

I have table which list a number of cases and assigned primary and secondary technicians. What I am trying to accomplish is to aggregate the number of cases a technician has worked as a primary and secondary tech. Should look something like this… Technician Primary Secondary John 4 3 Stacy 3 1 Michael 5 3 […]

SQL Table Constraint to prevent column totals exceeding 100

I have a table which contains columns like this: SomeId, Int PK Item1Weighting, Int Item2Weighting, Int Item3Weighting, Int I want to add a constraint to the table that prevents the total of the three “weighting” columns on a single row exceeding a total value of 100. I’ve done quite a bit of searching and can’t […]

SQL Server Get previous value from within an existing resultset

I’m trying to get a previous value from within a result set. I have a table ‘TableA’ which looks like this – ColumnID ColumnValue 1 100 2 NULL 3 NULL 4 0 5 10 6 100 7 100 I get the following resultset using this query – “Select ColumnID, ColumnValue from TableA WHERE ColumnValue = […]

Insertion without using cursor

Hi I have a procedure in MS SQL Server 2012, in which I am performing some insertions and updating but in some piece of that procedure, I am using cursor for looping in insertion. Can some one please help me doing that looping insertion without using cursor? Complete code is below ( @TerritoryName varchar(200), @TerritoryDescription […]

t-sql: replace function truncates when binary data type is used

here is the code to re-produce the issue: DECLARE @VBIN BINARY(50) DECLARE @PASS NVARCHAR(3) DECLARE @TEXT NVARCHAR(MAX) SET @TEXT = ‘123456123789’ SET @PASS = ‘123’ SET @VBIN = CONVERT(BINARY, N’321′) SELECT REPLACE(@TEXT, @PASS, CONVERT(NVARCHAR(MAX), @VBIN)) would return ‘321’ insetead of ‘321456321789’

A SQL join with a group by and having clause not producing desired results

We have these four tables: Store ( row bigint, id uniqueidentifier, name varchar ) Products ( row bigint, id uniqueidentifier, storeID uniqueidentifier, productname varchar ) Customer ( row bigint, id uniqueidentifier, storeID uniqueidentifier, fName, lName, email ) orders ( row bigint, id uniqueidentifier, store_ID uniqueidentifier, cust_id uniqueidentifier, prod_id uniqueidentifier, date datetime ) We are designing […]

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