Tag Info

New answers tagged

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

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 ...


0

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


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. ...


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 ( ...


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.


0

It looks to me like you don't need the derived table. Is there any reason you couldn't write it like this: SELECT Sum(x) * 0.1, Sum(y), a FROM tx INNER JOIN ty ON tx.a = ty.a WHERE x = 1 GROUP BY a This probably won't solve all of your performance issues, but if you set statistics IO on and look at the logical ...


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

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 ...


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 ...


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 ] ) ] } ...


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 ...


0

I'm pretty confident in this solution. I'm not certain about performance but any opinions about this approach are definitely welcome! Basically for each character in the string @String if the ASCII value of the character is between the ASCII values of '0' and '9' then keep it, otherwise replace it with a blank. CREATE FUNCTION [dbo].[fnStripNonNumerics]( ...


0

Can anyone advice me how this can be achieved? The most perfect solution would be 1 table result, containing all data and each parameters being shown as a column in that same product row. Is this too complex to handle at database level? You can use dynamic sql to pivot the EAV into a table with many columns (be aware of the column limit of 1024), ...


1

Comment from @ypercube is correct -- in this case, the syntax error you're encountering is because the previous line did not end in a semicolon: Declare RowID, CountAll INT DEFAULT 0 Should be: Declare RowID, CountAll INT DEFAULT 0; In MySQL's stored procedure language, you have access to two different types of variables. The first you declare with ...


0

In MySQL the assignment in a SELECT is done with := SELECT @CountAll := COUNT(*) FROM WebUsers WHERE RTRIM(UserID) = RTRIM(@UserID)


0

The sqlcmd syntax for setting variables is :setvar. You can access environment variables from sqlcmd mode (using the $(variableName) syntax) however I find these case-sensitive and it's a bit fussy about what you can and can't do. Try this, hopefully it makes sense: :setvar mypath "$(SystemRoot)" :out $(SystemRoot)\test.txt -- will work print ...


2

Try using dynamic sql: SET @createDatabase = 'CREATE DATABASE [test] ON PRIMARY CONTAINMENT = NONE ( NAME = N''test'', FILENAME = ''' + @filenamedata + ''', SIZE = 51200KB , FILEGROWTH = 10240KB ) LOG ON ( NAME = N''test_log'', FILENAME = ''' + @filenamelog + ''' , SIZE = 5120KB , FILEGROWTH = 5120KB )' EXEC sp_executesql @createDatabase Another member ...


0

It appears you have several Clustered Index Scans on the far right that take up the bulk of the query cost. You will have to give some more detail about these (I am struggling to read it), basically, we need to check if we can't put better indexes on it. Also, if you are using SQL Server 2012 or 2014, with Enterprise or Dev edition, try putting a ...


2

This assumes that your week starts on a Sunday, and that you only have data from the past. If you need to collect data for this week/month/year/today and exclude data from the future, you need to calculate a start and end range instead of a single cutoff. CREATE PROCEDURE dbo.GetHighScores -- always use schema prefix! @scope VARCHAR(5) -- why an int? AS ...


0

It is not the most elegant and using functions in the WHERE clause like this is not a good idea Performance wise as you get Table Scans, but should get you started ! This will give the max score for the current Day, month or Year CREATE PROC MaxScore ( @scope CHAR(1) ) AS SET NOCOUNT ON IF @scope = 'm' SELECT MAX(value) FROM ...


0

Create three different procedures - GetMonthHighScores, GetWeekHighScores and GetDayHighScores. Call the appropriate one for the given parameter.


1

You can use an INSERT trigger and select the value from the other table as the value for this column. This will be done for each insert so this is at least a performance issue. I'm afraid this is not a very good solution because this is intransparent. There would be better solutions with a better architecture. Maybe this is rather a client software task.


1

If you can't afford to refactor this field as an integer right now but you need better performance or simplicity for aggregate calculations, you may want to consider a computed field: ALTER TABLE timetable ADD DurationSeconds AS DATEDIFF(SECOND, '00:00:00', Duration); You can then index this field if needed. Based on a comment below, here's an ...


7

Store duration in seconds as an integer; then average is quite easy. Right now you're trying to take an average of a string converted to a float converted to a datetime converted to a time. If that doesn't sound wrong to you, read it again. Then consider that time represents a point time. What is the average of 3:12 AM and 4:57 PM? Meet in the middle? ...


5

I assume you must have invalid data stored in your nvarchar field. I created a simple test to obtain average duration, which works: DECLARE @T TABLE ( duration NVARCHAR(8) ); INSERT INTO @T VALUES ('00:00:05'); INSERT INTO @T VALUES ('00:01:04'); INSERT INTO @T VALUES ('00:02:03'); INSERT INTO @T VALUES ('03:00:02'); INSERT INTO @T VALUES ('04:00:01'); ...


1

You have an order of operations problem here. You are expecting SQLCMD (which runs first) to use variables you define in T-SQL (which doesn't run until after SQLCMD is finished). Try a simpler script to demonstrate this: DECLARE @x VARCHAR(32) = 'string x'; :setvar x @x :setvar y "string y" SELECT '$(x)','$(y)'; Result: @x string y Basically, ...



Top 50 recent answers are included