Tag Info

Hot answers tagged

7

I believe the term you are looking for is window functions. Most databases have support for these types of functions. Window functions belong to a type of function known as a ‘set function’, which means a function that applies to a set of rows. The word ‘window’ is used to refer to the set of rows that the function works on. Windowing functions ...


6

You can use the EXCEPT operator. For example, if the tables have identical structure, the following will return all rows that are in one table but not the other (so 0 rows if the tables have identical data): (TABLE a EXCEPT TABLE b) UNION ALL (TABLE b EXCEPT TABLE a) ; Or with EXISTS to return just a boolean value or a string with one of the 2 possible ...


5

One option is to use a FULL OUTER JOIN between the two tables in the following form: SELECT count (1) FROM table_a a FULL OUTER JOIN table_b b USING (<list of columns to compare>) WHERE a.id IS NULL OR b.id IS NULL ; For example: CREATE TABLE a (id int, val text); INSERT INTO a VALUES (1, 'foo'), (2, 'bar'); CREATE ...


4

In SQL Server, the log file is used to support transactions, and guarantee database consistency. If you remember the structure of the log file, you know that it may grow in the following cases: When there are huge DML (UPDATE, DELETE etc.) activity within database done by one transaction. When there are many transactions going at the same time and writing ...


3

The correlated subquery acts like a LEFT join between the 2 tables. If there is no row in the REF_MaterialPlantHistory matching the WHERE conditions, the subquery returns a NULL and this yields the error. Try using INNER join: UPDATE prod SET ABCIndicatorCode = hist.ABCIndicatorCode FROM DMT_TEE_DTS_Production AS prod JOIN ...


3

There are several reasons why this can happen: Make sure you are not using the query cache on your old server and not on the new one. Execute always benchmarks with SELECT SQL_NO_CACHE. Execute the same queries several times. If it is a new server -the buffer pool, filesystem cache and other buffer are probably not yet settled for best performance, ...


3

Shrinking of log file ON DAILY BASIS like one you do with query you posted is seriously bad and you should NOT do that. The main reason is that after you have shrinked the file it will grow again when new transaction comes and since INSTANT FILE INITIALIZATION is not there for Log files, its only for data files, when log file will grow DB engine will ...


3

This is not a great design. You have a couple of options to improve it: Go with your alternative (separate keys). You don't list your requirements, but if it's imperative you only have ONE child record per master record you can enforce this with check constraints. Put the parent key in the child table. If every child record refers to one master record, ...


3

In order to get the count across multiple columns, I'd suggest looking at unpivoting the data. Since you are using SQL Server 2008+, you can use CROSS APPLY to convert your multiple columns into multiple rows. Once the data is in the rows, you can easily count the occurrences. select sum(case when value = 1 then 1 else 0 end) Count1, sum(case when ...


2

Assuming referential integrity and all columns to be NOT NULL, this should be simplest and fastest: SELECT * FROM Receipe r WHERE NOT EXISTS ( SELECT 1 FROM Ingredients i JOIN RecipeIngredients ON ri.IngredientsId = i.ID WHERE ri.RecipeID = r.Id AND ri.RequiredQuantity > i.AvailableQuantity ); Basically, use a NOT EXISTS ...


2

There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL. My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.


2

... on a daily basis ... ALTER DATABASE ABC SET RECOVERY SIMPLE; Yes, this is as bad practice as it gets. Whats the point of having a backup chain if you break it daily? Now to your question: Does shrinking the log affect performance? Yes. Indirectly, because you shrink to a way too small file, you are dramatically affecting performance as log ...


2

Any shrinking of files (data or log) should be done in coordination with your backup strategy - and as others have pointed out it can be a pretty bad idea in many cases (certainly if done regularly). Side-stepping the exact question slightly: if your log files grow back to the same sort of size in a day or there-abouts then there is obviously something in ...


1

I think you want a "count of counts", which can be done with 2 GROUP BY, once to find how many times an emailaddress has been used: SELECT emailaddress, COUNT(*) AS frequency FROM tablename GROUP BY emailaddress ; and then enclosing the above in a derived table and doing another GROUP BY to find how many emailaddresses were used once, twice, ...


1

Will transactions be safely cued on the Subscribers - I imagine this will potentially cause transaction log growth? Transactions will be safely cued on the subscribers and transaction log growth will be negligible. Merge Replication utilizes DML triggers along with change tracking tables in the publication and subscription databases that contains metadata ...


1

Use a subquery that has the checklist id that has both tags SELECT B.id checklist_id, B.name checklist_name, D.name tag_name FROM ( SELECT AA.checklistId,COUNT(1) tagcount FROM checklist_tag AA INNER JOIN tag BB ON AA.tagId = BB.id WHERE BB.name IN ('tag 1', 'tag 2') GROUP BY AA.checklistId HAVING COUNT(1)=2 ) A INNER JOIN ...


1

You can use derive table to aggregate all your column values in a single column and do ordering and counting in the derived table. Example below. Select item0 as item, count(*) as cnt from (SELECT item0 from finalBuild UNION ALL SELECT item1 from finalBuild UNION ALL SELECT item2 from finalBuild) as item group by item0 order by count(*) desc;


1

Join positions to itself. In pseudo-sql: select o.position, n.position from positions as o -- old inner join positions as n -- new on o.application_id = n.application_id and o.country = n.country and o.feed_id = n.feed_id and o.created = <max value for this app, country and feed that's less than n.created> There's a ...


1

Try following. select T1.M_ID, (select T2.T_NAME from TEAM_MASTER as T2 where T1.TEAM1_ID = T2.T_ID) as t1_Name, (select T2.T_NAME from TEAM_MASTER as T2 where T1.TEAM2_ID = T2.T_ID) as t2Name from MATCH_MASTER as T1;


1

Make sure your group name returned matches (identically) to your actual workload group name. Some times, I've found, it's helpful to reconfigure even if it doesn't say it needs to be done. use master GO -- Create a test DB which our CF will be based on CREATE DATABASE RGDB; GO -- enable RG ALTER RESOURCE GOVERNOR RECONFIGURE GO CREATE FUNCTION ...


1

Assuming you have a recent enough version, infomix supports the LAG and LEAD functions over ranking operations, see http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1513.htm These will only work if the current row and the one you consider the previous row are find using the same filtering clauses though, otherwise you and ...


1

I suggest you join to previous products that match criteria and (if any exist) narrow down to the one where no later row exists: SELECT P.product_id AS product_id, ,P.deal_dt AS deal_dt ,C.deal_dt AS previous_deal_dt ,C.cancel_dt AS previous_cancel_dt FROM product_shipping p LEFT JOIN product_shipping C ON C.product_id = ...


1

You can make a table which holds the users with similar taste for every user. CREATE TABLE `similar_users` ( `uid1` INT, `uid2` INT, `score` INT, PRIMARY KEY (`uid1`, `uid2`) ) The algorithm for calculating the score can be anything, as long as a higher score means a closer taste. You you should limit the amount of similar users to a ...


1

You can also use the bcp utility to import or export data. In your case use something like the following command: bcp "SELECT A, B FROM TEST" queryout Test.txt -c -T To insert data from the file into another table: bcp Database.Schema.Table in Test.txt -T –c Hope this helps as an alternative



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