Tag Info

Hot answers tagged

6

This is not possible. The INSERT statement takes a single destination object as per the syntax (BOL reference): [ WITH <common_table_expression> [ ,...n ] ] INSERT { [ TOP ( expression ) [ PERCENT ] ] [ INTO ] { <object> | rowset_function_limited [ WITH ( <Table_Hint_Limited> [ ...n ] ) ] } ...


4

This is a frequent problem with poorly chosen clustered keys. Time series in general should be organized by time, since most queries ask for time ranges. Case in point, your query. If you have a correlation between id and timestamp then you can add an appropriate id based predicate: SELECT TOP 10 * FROM messages m INNER JOIN ...


4

Bjorn, There is nothing out of the box that will force the developers to use non-deprecated features (as they are deprecated, not removed). The most tailored solution that could be created is with DDL trigger(s), but note that they can be tricky... especially if not extremely familiar with them. I would suggest PBM, but it has limitations that won't ...


3

As ypercube commented No, if the query is what you show, he is totally wrong. It's pretty sargable as it is. You can verify this by: Creating a simple test table with a [Date] column. Insert a large number of rows with varying dates. NOTE: In the above "large number" and "varying dates" is a precaution to ensure that your query is selective enough. ...


3

There is no inherent order inside a table. There is no pre-stablished order when you insert rows. If run for example the following query: SELECT A.A, B.B FROM tblA A JOIN tblB B ON A.id = B.id Without an ORDER BY, then no particular order will be used. If you want some particular sorted results, you must use an ORDER BY clause. You could use a ...


2

The column max_length is the maximum column length in bytes. For the National-Character set string types (NCHAR and NVARCHAR), each character requires two-bytes, so NCHAR(10) would have a max_length value of 20.


2

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong). Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages: The optimizer has ...


2

Assuming the primary key of children is (horror) id: select p.* from parents p inner join children c on c.parentId = p.id where c.firstname like 'tom' and not exists ( select 1 from children c2 where c2.parentId = p.id and c2.id <> c.id ) ; or using GROUP BY: select p.* from parents p inner join ( ...


2

--Use Script to Shrink Log files of all databases other than the system DBs. USE MASTER GO SET QUOTED_IDENTIFIER ON GO SET ARITHABORT ON GO DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200) DECLARE DatabaseList CURSOR FOR SELECT name,recovery_model_desc FROM sys.databases ...


2

First of all, what you are about to design is probably a VERY bad idea. A much better solution would be to have a dynamic schema where you add new tables and have the application understand how to query those table (you could place them in a schema). This largely avoids all the locking and query plan issues you are bound to run into with this model. There is ...


2

You are asking 2 different questions. So I will try to answer this accordingly. First is to identify deprecated features using Use Server side trace or Use Extended Events as Sean pointed out. Now to enforce coding standards, you can use Policy Based Management or From Codeplex - Enterprise Policy Management Framework


2

Simple answer for this is: SELECT mt.Id , mt.TranDate , mt.TranType , CASE WHEN mt.TranType = 1 THEN mt.TranAmount ELSE NULL END AS DepAmt , CASE WHEN mt.TranType = 0 THEN mt.TranAmount ELSE NULL END AS WitAmt FROM dbo.MyTable mt ORDER BY mt.Id;


1

You can calculate the Delay in a cross apply and use dateadd() to add it to st_date in the main query. select D1.Split3_ID, D1.CU_ID, D1.order_id, D1.st_date, D1.sku, D1.Priority, D1.Delay, dateadd(day, D2.Delay, cast(D1.st_date as date)) as CourseDate from dbo.set_dates as D1 cross apply ( ...


1

The problem you are actually facing is that you are calling two items asynchronously on your end, but they in fact need to run in order on the database end per your requirements. There is nothing that says the first call should finish before the second since it's asynchronous. The process you have in place does not make sure that happens. IMHO either use a ...


1

As Thomas points out, if your goal is to ensure that the operation is atomic (both inserts either work or fail in unison) then what you are looking for is transactions like so: BEGIN TRANSACTION INSERT INTO <table1> ... INSERT INTO <table1> ... COMMIT TRANSACTION The official documentation for this is at ...



Only top voted, non community-wiki answers of a minimum length are eligible