Tag Info

Hot answers tagged

5

Yes but not with row_number(). You can use either the window aggregate min(): select id, code, descriptor from ( select id, code, descriptor, min_code = min(code) over (partition by id) from @t ) as t where code = min_code ; Or the rank() window function (or the dense_rank() one, they'll both work the same for the rnk = 1 check): ...


4

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


4

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

update a set a.CHK = 1 from tableA a join tableB b on b.IDA = a.IDA and b.IDB = a.IDB


3

Of course you could also write this using a CTE: ;WITH t AS ( SELECT id, code, descriptor, min_code = MIN(code) OVER (PARTITION BY id) FROM @t ) SELECT id, code, descriptor FROM t WHERE code = min_code; You cannot say: SELECT id, code, descriptor FROM @t WHERE code = MIN(code) OVER (PARTITION BY id); Because: Msg 4108, Level 15, State 1, ...


3

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


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


2

By index file, you mean you place indexes on a dedicated filegroup that maps to the file in question? Yes, it could indeed be smaller. Indexes can become fragmented over time (inserts and updates need to split pages when inserting into a range) causing free space on your pages over that defined by your fillfactor. If your fillfactor is say 80%, this is not ...


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

I have now found the problem thanks to SQL Profiler, update statistics with fullscan and your help :). It was caused by a bug in the script that used the built in functions in the ecommerce software to update products but it didn't reused the deliverydates why 545 deliverydates were added every hour and together with bad indexes on that table it slowed down ...


1

extract() the epoch from the time component after casting to time (effectively removing the "day" component): SELECT extract(epoch FROM ts::time) AS sec_of_day You get the "number of seconds", including fractional seconds if there are any. Very short and fast. Test (with timestamps in unambiguous ISO format): SELECT extract(epoch FROM ts::time) AS ...


1

I think you are looking to do something like this WHERE CP.NumIntAut IN ( SELECT CASE WHEN @NumIntAut IS NOT NULL THEN @NumIntAut ELSE NumIntAut END FROM @TAB_NumIntAut ) Assuming that I understand your question correctly. ...


1

You could do this using one of the following queries: select date_part('second', timestamp '2011-02-01 20:23:43'); select to_char(timestamp '2011-02-01 20:23:43', 'SS'); select extract(second from timestamp '2011-02-01 20:23:43'); Have fun.


1

Use the extract() method: select extract(second from current_timestamp) + extract(minute from current_timestamp) * 60 + extract(hour from current_timestamp) * 60 * 60; of course this can be put into a function: create or replace function total_seconds(p_timestamp timestamp) returns int as $$ select (extract(second from p_timestamp) + ...


1

Questions to ask could include: What fits well with your current skills? (That might make it 'easier' to use.) Is your focus on getting something working soon, or on developing new skills? What is the cost of your choices (money, time, effort, unfamiliarity, etc)? With your list you do emphasize cost as a factor and the database is not expected to be ...


1

You can calculate a running total in SQL 2008 R2 by using the ROW_NUMBER() window function along with a correlated sub-query to calculate the running total column. Here is an example using some data I have available. You would need to amend the query accordingly to suit your tables/data and would likely need to use a PARTITION BY within the ROW_NUMBER ...


1

Zip codes should be stored as text, as some start with 0 (screwing up formatting/sorting) and there is no reason to do math on them. Also, if you want to store global postal codes, they often contain letters. Phone numbers are a maybe for text, especially if there might be extensions. Or you want to store numbers like 1-800-GOT-JUNK. There's no reason to do ...


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



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