Tag Info

New answers tagged

-5

select m.id, min(m.code) as code, m.descriptor from @t as m group by m.id, m.descriptor order by m.id, code The order by is only there to produce the output table in the same order as your example, but it is entirely unnecessary. Revision to include the comment I added below - handling duplicates: select a.id, a.code, b.descriptor from ( select id, ...


1

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


2

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


0

The parameter MEMORY_TARGET is for how much physical memory Oracle can use on your machine hosting the Oracle instance; increasing tmpfs will not solve the problem; I would suggest setting MEMORY_TARGET to a little higher than the amount suggested by the error message; perhaps set it to MEMORY_TARGET=4G Assuming your machine has over 4 Gigabytes of free ...


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


0

The code below will pull back only records matching values in @NumIntAut or field NumIntAut in table variable @TAB_NumIntAut. @NumIntAut will be evaluated first then NumIntAut if the variable is NULL. INNER JOIN ( SELECT ISNULL(@NumIntAut,NumIntAut) NumIntAut FROM @TAB_NumIntAut ) NA ON NA.NumIntAut = ...


0

DECLARE @NumIntAut INT = 123 --filter by only one NumIntAut... DECLARE @CodUsu INT = 789 --or more than one NumIntAut from User DECLARE @TAB_NumIntAut TABLE (NumIntAut INT) INSERT @TAB_NumIntAut SELECT [...] INSERT @TAB_NumIntAut SELECT @NumIntAut [...] WHERE CP.NumIntAut IN (SELECT NumIntAut FROM @TAB_NumIntAut)


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


0

The MSDN Blog "SQL Server SSIS and Replication" has a post from a few years ago. http://blogs.msdn.com/b/mangeshd/archive/2009/05/20/replication-snapshot-folder-cleanup.aspx How these old snapshot folders are purged depends on the type of replication you are using: For snapshot and transactional replication: Distribution cleanup agent purges the ...


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


3

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


0

It seems I have found the issue. It was System Center Operations Monitor trying to log into SQL via the cluster name however it was set to use Local System and didn't have access. I changed this to a specific monitoring account and its working now.


0

Adding LIMIT to the query often makes Postgres choose a different query plan. If statistics or cost estimates stray too far from actual data distribution / actual costs, you may end up with a slower query, even though Postgres reckoned it would be faster. Uneven data distribution may cause the query planner to misjudge the selectivity of WHERE conditions. ...


0

kill all the connections to the database (sa) , if you don't have the permission , connect to your DB admin and he will do it .(importent to remember to do the kill statment from "master" database). execute this query: USE master go ALTER DATABASE [Your_database_In_Single_user_mode_state] SET MULTI_USER GO it should solve it . ItzikPaz


0

SELECT users, COUNT(*) FROM ( SELECT `user1` AS users FROM usuarios WHERE (`user1` is not null) UNION ALL SELECT `user2` FROM usuarios WHERE (`user2` is not null) ) sq GROUP BY users; Do the grouping outside. The trick is to use UNION ALL instead of UNION. The latter one uses a distinct on the result. You don't have to ...


0

Here is the MSDN Large Object Data Types Columns of data type image, text, and ntext are always assigned a NULL value when __$operation = 1 or __$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns ...


0

When you do your update, set the lastUpdate column equal to itself, that way it won't default in the current timestamp: update productsStock set productName="HP PRINTER 20000", lastUpdate = lastUpdate where product_reference="001";


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


0

I had run CheckDB against a 32GB db, and got this error. There were plenty space on the HD. So I increased the size of the tempdb log file to 8MB from 1MB, and closed some applications. Reran CheckDB, and it worked.


0

I started working with databases in the 70's and tend to use data dictionary to mean the physical representation of data, and the schema to mean the logical model. A schema is like a blueprint that helps you visualize the way the data is organized. A data dictionary is the list of table names, column names, lenghts, variable types and other specific, ...


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


0

Select State, Time, Row_Number() over (partition by State order by State)[Order Number] From Table Order by State, Time Should do the trick :) Also, for the Order Number - n, you can just do: Row_Number() over (partition by State order by State) - n[Order Number] If you want to do any filtering (ie. "Where Clause") on the Row_Number column, ...


0

It's an internal table that is used during the import of a bacpac. If all is well it should be gone when the import is done. If it remains in the database the import wasn't completed successfully.


0

Look for all last_payment_Date values that are less than the first of this month SELECT `Name`, `Acc_No` FROM `user_table_details` where `last_payment_Date` < (CURDATE() - INTERVAL (DAY(CURDATE()) - 1) DAY); Give it a Try !!!


0

Assuming the data is constrained to avoid duplicates, how about something like this? This is assuming Microsoft SQL Server syntax. SELECT United.PolicyName FROM (SELECT PolicyName FROM dbo.Policies WHERE Setting = 'DiskUsage' AND State = 'Enabled' AND Value = 1 UNION ALL SELECT PolicyName FROM dbo.Policies WHERE Setting = 'Memory' AND State = ...


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


0

Yes, I figured out that the select statement was returning multiple records (Cartesian product). I was so focused on the output that I didn't pay enough attention to what could be returned with the select part of the insert. I needed another key field in the select part of the insert.


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


0

Matty, I use Notepad++ (free) for this purpose. It it is able to handle very big files by not loading all data in memory. (Though I have to say that 1.8GB is big!) UltrEdit does the same job. Another way is to use the Unix "head -nN [filename]" command which delivers you just the top N lines. For example: head -n 5 foo.txt , for the first 5 lines of file ...


0

The question is not very clear but I think you need s simple correlated subquery: SELECT [CaseNumber] ,[CenterCode] ,[YearCode] ,[SubVerbatim] FROM [dbo].[ToxExpSub] AS a WHERE EXISTS ( SELECT 1 FROM [dbo].[ToxExpSub] AS b WHERE b.SubPoisindexCode = 6931087 AND b.[CaseNumber] = a.[CaseNumber] ) ;


0

The following post answers my question and we ignored the stoplist when we created the indexes. https://community.oracle.com/thread/2181803


0

This sounds like a classic case of stale statistics. At 500 rows per hour for 30 days, we are only talking about 360,000 rows. I know you said you ran Sp_updatestats, but that only updates a sampling. Instead, run EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;' If that doesn't improve the performance, then you probably need to consider ...


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


0

It has a very easy way to have everything on one page! Simply right click on the diagram area, and choose "Copy Diagram to Clipboard"! Then you can paste it in Paint and save it as any picture type you want...


0

It's almost certainly a matter of the data for the first run are not in the cache and have to be read from disk. After that first run then the data is in cache and the query can run much more quickly. After several minutes other data needs to be in the cache and so the data for this query get's pushed out. There is a simple way to test this. Run the ...


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


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

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


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


0

If you simply include the column which holds these values into an order by clause they will come out in the sequence you want. For example select ThisColumn from MyTable order by ThisColumn This will work if your values genuinely are all form shown i.e. two digits, a slash and one digit. If you have other formats the more involved solutions will be ...


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


0

There are different data types in sql to store the data. if the all data are same then varchar is good to store. but in the future, you need the operation on the data, you can't because of limited functionality. better to go with the data type of data.


0

First you find the position of your separating character ( / ). Then you order your table by left side of separator and right side of separator. SELECT field ,CHARINDEX('/', field) as [Position of /] ,LEFT(field, CHARINDEX('/', field)-1) as Left_Sort ,RIGHT(field, LEN(field) - CHARINDEX('/', field)) as Right_Sort from #string order by ...


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


0

The Shipping Address is a property of each and every order, not of the customer. Even if it was a property of the Customer, it would be necessary to know where past orders were shipped to if a customer relocated. Therefore all that can be stored as a Customer property are the Default Shipping Address (and Default Billing Address), for pre-populating each ...


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


0

Test the inner query to see how well it performs. If you are missing an index on the foreign key, performance may be very slow. The size of your result set can cause performance issues with inner joins. The inner join may need to be run once for each row in your result set. Not having enough memory can cause an issue as data may need to be re-read from ...


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



Top 50 recent answers are included