Tag Info

Hot answers tagged

10

This is expected behaviour at the moment the function gets evaluated on the DELETE stream. So it actually behaves like this (pseudo code) DELETE k OUTPUT Deleted.name, ROW_NUMBER() OVER (ORDER BY Deleted.object_id) as r FROM ( SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r FROM #o k ) k WHERE r <> 1 --OUTPUT returns rows ...


5

This is a typical "greatest N per group" problem which is usually solved using window functions: select caseno, date, time, tranno, pcode, pdesc, user from ( select a.caseno,a.date,a.time,a.tranno,b.pcode,c.pdesc,a.user, row_number() over (partition by a.caseno order by a.tranno desc) as rn from tablea a right join tableb b on ...


5

Another approach, which still uses dynamic SQL but no ugly cursor scaffolding (and allows you to examine the step which failed and the error number generated, without bubbling the error to the caller): DECLARE @step INT = 0, @result INT = 0, @sql NVARCHAR(MAX) = N''; DECLARE @tbl TABLE([step] INT PRIMARY KEY, [pname] NVARCHAR(513)); INSERT ...


4

If you don't need the result values later you can do it shorter this way: -- procedures to test with create proc proc1 as print '1' return 0 GO create proc proc2 as print '2' return 1 GO create proc proc3 as print '3' return 0 GO if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)') GO alter PROCEDURE dbo.testproc AS ...


3

Size is one consideration. An int can hold up to -2,147,483,648 in four bytes. A char will need 11 bytes to hold the same value. There are built-in functions to manipulate the various data types. DATEADD() and DATEDIFF() are two examples. This will not be possible with date-stored-as-text. Constantly CASTing back and forth will not make for efficient ...


3

Ok, so when you ran SELECT * FROM sys.columns WHERE object_id = object_id('tb1') for each of the tables you found that the collation was not the same as the database default. At this point you have two options. Change the collation in the query All you have to do here is use the COLLATE phrase after the column that needs it. select distinct Number, ...


3

Your colleague is correct that it is easier to simply not think about it and just store everything as a varchar. But this comes at a large cost in terms of space requirements, performance, flexibility in querying data, and most importantly, lack of data integrity. This is not just a one-time cost; it is paid repeatedly over the lifecycle of the ...


3

Maybe something like this: with ranked_visits as ( SELECT w.website_id, v.visitor_id, count(wv.visit_id) as visits, row_number() over (partition by w.website_id order by count(wv.visit_id) desc) as rnk FROM website_visits wv JOIN websites w ON wv.website_id = w.website_id JOIN visitors v ON wv.visitor_id = ...


3

No, your case does not block on Oracle, nor should it. Concurrency is one the the major reasons to use an RDBMS instead of say, Excel. But just to make it interesting, if you insert the same value in both sessions, it will then block. Session #1 CREATE TABLE t1 (c1 NUMBER PRIMARY KEY); INSERT INTO t1 VALUES (1); -- do not commit transaction Session #2 ...


2

If you are going to use custom collations for specific databases then yes, you'll need to make the collations match whenever you are joining or unioning data from the two databases. In fact you will need to do this with many metadata queries anyway. Just look at catalog views like sys.tables: SELECT c.name, c.collation_name FROM sys.all_columns AS c INNER ...


2

SQL Server optimizer does constant folding, when possible. But is not a black-or-white issue, there are many shades of gray. See Compute Scalars, Expressions and Execution Plan Performance or Troubleshooting Poor Query Performance: Constant Folding and Expression Evaluation During Cardinality Estimation. You also need to read Conor vs. Runtime Constant ...


2

I found the course at Standford to be helpful - if you sign up, you can view past lectures on many of these topics. https://class.stanford.edu/courses/Engineering/db/2014_1/info


2

Using PIVOT: WITH combined AS ( SELECT o.customer_no, EXTRACT(month FROM o.entry_date) AS order_month, EXTRACT(year FROM o.entry_date) AS order_year, t.order_final_tot FROM orders o JOIN order_totals t ON (o.order_no = t.order_no) ) SELECT * FROM combined PIVOT ( SUM(order_final_tot) AS ...


2

I suggest your first option, with two improvements and some simplifications. ( SELECT 1 -- irrelevant what you select here FROM client_category_price WHERE sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1 -- may be redundant ) UNION ALL -- not just UNION ... UNION ALL ( SELECT 1 FROM work_order_item WHERE sellable_id = ...


2

Try making a temp table with just the data you need and doing a join to that. For each self join do another temp table. I would start with one at a time and check the performance.


2

There are several situations in which it's better to represent numbers using some kind of numeric data-type. It's a little more efficient, but that's just the beginning. You get support for built-in arithmetic using SQL operators without performing type conversions at run time. Not only do type conversions slow things down, but they can result in numerous ...


2

Do you really need ROW_NUMBER and OUTPUT? How about a nice set-based DELETE, eg something like this: USE tempdb SET XACT_ABORT ON BEGIN TRAN SELECT * INTO #o FROM sys.objects -- Identify records to keep SELECT TOP 2 object_id INTO #keep FROM #o ORDER BY object_id -- Delete others DELETE o FROM #o o ...


1

If you want to condense it into a single SELECT, this would work: SELECT DISTINCT ON (coalesce(t2_id, t1_id), t2_id) t1_id, t2_id FROM t0 ORDER BY coalesce(t2_id, t1_id), t2_id, t1_id; Equivalent, except for sort order. SQL Fiddle. If you want this to be fast, I'd try a functional index: CREATE INDEX t0_func_idx ON t0 (coalesce(t2_id, t1_id), ...


1

A way to order such string is Step1: Create Split Function CREATE FUNCTION [dbo].[fnSplit] ( @List VARCHAR(8000), @Delimiter CHAR(1) = ',' ) RETURNS @Temp1 TABLE ( ItemId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Item VARCHAR(8000) NULL ) AS BEGIN DECLARE @item VARCHAR(4000), @iPos INT SET @Delimiter = ISNULL(@Delimiter, ',') SET @List = ...


1

You can't use contains because there is no Oracle Text index on the database source. You could, I suppose, write a query that copied the data from dba_source to a custom table, create an Oracle Text index on that table, and search that table using the contains function. It would generally make more sense, though, to just query dba_source with a like query ...


1

Try this instead: UPDATE student SET [Status] = CASE WHEN struc.Mode IS NOT NULL THEN 'Cleared' ELSE 'Not Cleared' END FROM FeeStudent student LEFT JOIN FeeStructure struc ON student.Mode = struc.Mode AND student.[Level] = struc.[Level] AND student.SEM = struc.Semester AND student.Paid = struc.Amount


1

Since this old question has been dug up anyway, I'll mention that you can use the built-in XQuery support in DB2 for regular expression matching, something along the lines of select whatever from users where xmlcast( xmlquery('fn:matches($USER_NAME,"^a[aofdmep][a-z][a-z0-9]{4}[sidbfkfpo]")') as integer) = 1 XMLQUERY above calls the XQuery ...


1

Most likely the statistics on the field were out of date, adding the index create/updated statistics with a full table scan. More info on statistics https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/engine-independent-table-statistics/


1

Just looking at your definitions (thank you very much for that) I see: No index on to support WHERE dbo.EntryTypes.EntryType = 'Error'. (But this may not be needed if it is a small table with just a few entries.) Your fk_* columns in MAIN have check constraints, but no indexes. You should create some indexes since they are used to join to several ...


1

From what I can see you have two problems. The first is the doubling. At a guess it is because of the SELECT statement in your INSERT statement. SELECT @NewMetricID, NULL, sm.sortorder, ISNULL(sm.MetricOrder, 1), ISNULL(sm.CategoryOrder, 1), sm.RptCurrentGroup, 'System', ...


1

From Books Online database_default - Causes the COLLATE clause to inherit the collation of the current database. If you are executing your second query from the common database then the value from master.dbo.sysdatabases is being coerced into common's collation, not the other way around as you suppose.


1

When using a databse to sort the data one needs to know the structure of the data. In other words, there must be a field delimiter and a row delimiter. Then you can import the file into a SQL-table and index it and sort it the way you want. Importing flat files is easy done with tools provided by any Database Engine, for example SQL Server Integration ...



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