Let's start with the basic scenario.

If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP.

First, let's consider the whole set from Production.TransactionHistory for a particular ProductID:

SELECT h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800;

This returns 418 rows, and the plan shows that it checks every row in the table looking for this - an unrestricted Clustered Index Scan, with a Predicate to provide the filter. 797 reads here, which is ugly.

So let's be fair to it, and create an index that would be more useful. Our conditions call for an equality match on ProductID, followed by a search for the most recent by TransactionDate. We need the TransactionID returned too, so let's go with: CREATE INDEX ix_FindingMostRecent ON Production.TransactionHistory (ProductID, TransactionDate) INCLUDE (TransactionID);.

Having done this, our plan changes significantly, and drops the reads down to just 3. So we're already improving things by over 250x or so...

Now that we've levelled the playing field, let's look at the top options - ranking functions and TOP.

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
)
SELECT TransactionID, ProductID, TransactionDate
FROM Numbered
WHERE RowNum <= 5;

SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
FROM Production.TransactionHistory h
WHERE h.ProductID = 800
ORDER BY TransactionDate DESC;

You will notice that the second (TOP) query is much simpler than the first, both in query and in plan. But very significantly, they both use TOP to limit the number of rows actually being pulled out of the index. The costs are only estimates and worth ignoring, but you can see a lot of similarity in the two plans, with the ROW_NUMBER() version doing a tiny amount of extra work to assign numbers and filter accordingly, and both queries end up doing just 2 reads to do their work. The Query Optimizer certainly recognises the idea of filtering on a ROW_NUMBER() field, realising that it can use a Top operator to ignore rows that aren't going to be needed. Both these queries are good enough - TOP isn't so much better that it's worth changing code, but it is simpler and probably clearer for beginners.

So this work across a single product. But we need to consider what happens if we need to do this across multiple products.

The iterative programmer is going to consider the idea of looping through the products of interest, and calling this query multiple times, and we can actually get away with writing a query in this form - not using cursors, but using APPLY. I'm using OUTER APPLY, figuring that we might want to return the Product with NULL, if there are no Transactions for it.

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM
Production.Product p
OUTER APPLY (
    SELECT TOP (5) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

The plan for this is the iterative programmers' method - Nested Loop, doing a Top operation and Seek (those 2 reads we had before) for each Product. This gives 4 reads against Product, and 360 against TransactionHistory.

Using ROW_NUMBER(), the method is to use PARTITION BY in the OVER clause, so that we restart the numbering for each Product. This can then be filtered like before. The plan ends up being quite different. The logical reads are about 15% lower on TransactionHistory, with a full Index Scan going on to get the rows out.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

Significantly, though, this plan has an expensive Sort operator. The Merge Join doesn't seem to maintain the order of rows in TransactionHistory, the data must be resorted to be able to find the rownumbers. It's fewer reads, but this blocking Sort could feel painful. Using APPLY, the Nested Loop will return the first rows very quickly, after just a few reads, but with a Sort, ROW_NUMBER() will only return rows after a most of the work has been finished.

Interestingly, if the ROW_NUMBER() query uses INNER JOIN instead of LEFT JOIN, then a different plan comes up.

This plan uses a Nested Loop, just like with APPLY. But there's no Top operator, so it pulls all the transactions for each product, and uses a lot more reads than before - 492 reads against TransactionHistory. There isn't a good reason for it not to choose the Merge Join option here, so I guess the plan was considered 'Good Enough'. Still - it doesn't block, which is nice - just not as nice as APPLY.

The PARTITION BY column that I used for ROW_NUMBER() was h.ProductID in both cases, because I had wanted to give the QO the option of producing the RowNum value before joining to the Product table. If I use p.ProductID, we see the same shape plan as with the INNER JOIN variation.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY p.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5;

But the Join operator says 'Left Outer Join' instead of 'Inner Join'. The number of reads is still just under 500 reads against the TransactionHistory table.

Anyway - back to the question at hand...

We've answered question 1, with two options that you could pick and choose from. Personally, I like the APPLY option.

To extend this to use a variable number (question 2), the 5 just needs to be changed accordingly. Oh, and I added another index, so that there was an index on Production.Product.Name that included the DaysToManufacture column.

WITH Numbered AS
(
SELECT p.Name, p.ProductID, p.DaysToManufacture, h.TransactionID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY h.ProductID ORDER BY h.TransactionDate DESC) AS RowNum
FROM Production.Product p
LEFT JOIN Production.TransactionHistory h ON h.ProductID = p.ProductID
WHERE p.Name >= 'M' AND p.Name < 'S'
)
SELECT Name, ProductID, TransactionID, TransactionDate
FROM Numbered n
WHERE RowNum <= 5 * DaysToManufacture;

SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM
Production.Product p
OUTER APPLY (
    SELECT TOP (5 * p.DaysToManufacture) h.TransactionID, h.ProductID, h.TransactionDate
    FROM Production.TransactionHistory h
    WHERE h.ProductID = p.ProductID
    ORDER BY TransactionDate DESC
) t
WHERE p.Name >= 'M' AND p.Name < 'S';

And both plans are almost identical to what they were before!

Again, ignore the estimated costs - but I still like the TOP scenario, as it is so much more simple, and the plan has no blocking operator. The reads are less on TransactionHistory because of the high number of zeroes in DaysToManufacture, but in real life, I doubt we'd be picking that column. ;)

One way to avoid the block is to come up with a plan that handles the ROW_NUMBER() bit to the right (in the plan) of the join. We can persuade this to happen by doing the join outside the CTE.

WITH Numbered AS
(
SELECT h.TransactionID, h.ProductID, h.TransactionDate, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TransactionDate DESC) AS RowNum
FROM Production.TransactionHistory h
)
SELECT p.Name, p.ProductID, t.TransactionID, t.TransactionDate
FROM Production.Product p
LEFT JOIN Numbered t ON t.ProductID = p.ProductID
    AND t.RowNum <= 5 * p.DaysToManufacture
WHERE p.Name >= 'M' AND p.Name < 'S';

The plan here looks simpler - it's not blocking, but there's a hidden danger.

Notice the Compute Scalar that's pulling data from the Product table. This is working out the 5 * p.DaysToManufacture value. This value isn't being passed into the branch that's pulling data from the TransactionHistory table, it's being used in the Merge Join. As a Residual.

So the Merge Join is consuming ALL the rows, not just the first however-many-are-needed, but all of them and then doing a residual check. This is dangerous as the number of transactions increases. I'm not a fan of this scenario - residual predicates in Merge Joins can quickly escalate. Another reason why I prefer the APPLY/TOP scenario.

In the special case where it's exactly one row, for question 3, we can obviously use the same queries, but with 1 instead of 5. But then we have an extra option, which is to use regular aggregates.

SELECT ProductID, MAX(TransactionDate)
FROM Production.TransactionHistory
GROUP BY ProductID;

A query like this would be a useful start, and we could easily modify it to pull out the TransactionID as well for tie-break purposes (using a concatenation which would then be broken down), but we either look at the whole index, or we dive in product by product, and we don't really get a big improvement on what we had before in this scenario.

But I should point out that we're looking at a particular scenario here. With real data, and with an indexing strategy that may not be ideal, mileage may vary considerably. Despite the fact that we've seen that APPLY is strong here, it can be slower in some situations. It rarely blocks though, as it has a tendency to use Nested Loops, which many people (myself included) find very appealing.

I haven't tried to explore parallelism here, or dived very hard into question 3, which I see as a special case that people rarely want based on the complication of concatenating and splitting. The main thing to consider here is that these two options are both very strong.

I prefer APPLY. It's clear, it uses the Top operator well, and it rarely causes blocking.


With partitioning you would have to do a partition per day, which puts the Pre-SQL 2012  limit of 1000 paritions in a new perspective as it would only allow for 3 years archive. With SQL Server 2012 you get 15000 partitions which is plenty for 1 partition per day.

Every day you would add a new partition. If you want to move the 61st past day partition you can do it efficiently, but is still an offline operation. See Move a Partition to a Different File Group Efficiently.

All your indexes would have to be aligned, see Special Guidelines for Partitioned Indexes.

Buying into partitioning is not an easy decission and it may be quite a big bite to chew... see How To Decide if You Should Use Table Partitioning. Specifically you should not expect performance improvements from partitioning. You should approach performance problems on time seriest by clustering by datetime.


Selecting from a table contains a XML column

As your XML is held in a table you can use the methods of the XML data-type ( eg .nodes, .value, .query etc ).  The thing you might struggle with a bit is namespaces in XML.  Start here to learn more about them: Understanding XML Namespaces.

Here are a few example queries I did against a spreadsheet similar to yours.  Try them and work through the Stairway as suggested by @MikaelEriksson and see if they start to make sense.

IF OBJECT_ID('tempdb.#tmp') IS NOT NULL
    DROP TABLE #tmp
GO

-- 1) Pull the data "as is" unpivoted, tag it with row/cell numbers
-- and optionally pivot it
;WITH XMLNAMESPACES (
    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
    'urn:schemas-microsoft-com:office:spreadsheet' AS ss
    ), cte AS (
SELECT
    t.rawXMLId,
    ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) rowNumber,
    r.c.query('.') rowXML
FROM XMLwithOpenXML t
    CROSS APPLY XMLData.nodes('Workbook/Worksheet[@ss:Name = "Sheet1"]') w(c)
        CROSS APPLY w.c.nodes('Table/Row') r(c)
)
SELECT
    c.rawXMLId,
    c.rowNumber,
    ROW_NUMBER() OVER ( PARTITION BY rowNUmber ORDER BY rowNumber ) cellNumber,
    d.c.value('.', 'VARCHAR(50)') AS cellData
INTO #tmp
FROM cte c
    CROSS APPLY c.rowXML.nodes('Row/Cell/Data') d(c)
GO

SELECT 'unpivoted' s, *
FROM #tmp

SELECT
    'pivoted' s,
    rawXMLId, rowNumber,
    [1] AS Sno,
    [2] AS Salutation,
    [3] AS PatientNRIC,
    [4] AS FirstName,
    [5] AS LastName,
    [6] AS Gender,
    [7] AS Race
FROM #tmp
PIVOT ( MAX(cellData) For cellNumber In ( [1], [2], [3], [4], [5], [6], [7] )  ) upvt
GO

-- 2) Use your knowledge about the data to specify the columns manually
;WITH XMLNAMESPACES (
    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
    'urn:schemas-microsoft-com:office:spreadsheet' AS ss
    )
SELECT
    t.rawXMLId,
    d.c.value('(Cell/Data/text())[1]', 'VARCHAR(50)') AS Sno,
    d.c.value('(Cell/Data/text())[2]', 'VARCHAR(50)') AS Salutation,
    d.c.value('(Cell/Data/text())[3]', 'VARCHAR(50)') AS PatientNRIC,
    d.c.value('(Cell/Data/text())[4]', 'VARCHAR(50)') AS FirstName,
    d.c.value('(Cell/Data/text())[5]', 'VARCHAR(50)') AS LastName,
    d.c.value('(Cell/Data/text())[6]', 'VARCHAR(50)') AS Gender,
    d.c.value('(Cell/Data/text())[7]', 'VARCHAR(50)') AS Race

FROM XMLwithOpenXML t
    CROSS APPLY XMLData.nodes('Workbook/Worksheet[@ss:Name = "Sheet1"]') w(c)
        --CROSS APPLY w.c.nodes('Table/Row') d(c)                   <-- include row header
        CROSS APPLY w.c.nodes('Table/Row[position() > 1]') d(c)     -- exclude row header

GO

My results:

Just a note about OPENXML, it can be good for larger pieces of XML but I tend to try and avoid it if possible due it's well known memory issues.  Your particular example is not working because of the namespaces and you have specified the wrong path (Root etc).  Post back if you really want to work with the OPENXML, but I would advise against it in this example as your data is held in a table - OPENXML can only work with once piece of XML at a time.


PostgreSQL Role Inheritance behavior in AWS RDS

Found the answer in this post: How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

The default privileges are set for the executing user unless otherwise specified with the FOR ROLE modifier.


Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View?

Typically you index views if you are often running aggregates, not to magically speed up joins. Also if you are not using table partitioning there is no good reason to investigate partition-aligned indexed views.

You should be focusing on optimizing the query, irrespective of the view, IMHO. An indexed view is not a magic turbo button (though a lot of people seem to think it is).

Also consider whether you should just run a query separately from the view anyway - often people run a lot of queries through the same view for convenience, even if the view actually touches more columns / rows than are necessary for the specific query.


Nasty, but will do what you wanted:
DECLARE @Out TABLE (BuyerId int, ItemId int);
DECLARE @BuyerId int, @Budget int, @ItemId int, @Cost int;

WHILE EXISTS(SELECT TOP 1 Budget FROM @Buyer WHERE Budget >= (SELECT MIN(Cost) FROM @Item))
BEGIN
    SELECT TOP 1 @BuyerId = BuyerId, @Budget = Budget
    FROM @Buyer
    WHERE Budget >= (SELECT MIN(Cost) FROM @Item)

    SELECT TOP 1 @ItemId = ItemId, @Cost = Cost
    FROM @Item
    WHERE Cost <= @Budget
    ORDER BY Cost DESC, ItemId ASC;

    DELETE FROM @Item WHERE ItemId = @ItemId;
    UPDATE @Buyer SET Budget -= @Cost WHERE BuyerId = @BuyerId;
    INSERT INTO @Out VALUES(@BuyerId,@ItemId)
END
SELECT * FROM @Out

UPDATE (2020-10-16):
Based on OPs comment I decided to try to do as much as possible using set based data flow. First of all solution requires to create all possible permutations without duplicates where the order doesn't matter. For that I used a recursive CTE, then out of all possible price sums we needed only these in the Buyers budget, then I ordered them form the most expensive set (as asked by OP) where we want most expensive item first then the most expensive still in the budget and so on. I ended up with having the order column, JSON array for ItemId and all other base data:
;WITH totalCostPermutations AS(
SELECT
ItemIndex = CAST(CONCAT('"',ItemId,'') as varchar(max)),
X = CAST(ROW_NUMBER() OVER (ORDER BY Cost DESC, ItemId ASC) as varchar(max)),
ItemId, Cost
FROM @Item
UNION ALL
SELECT
ItemIndex = CAST(CONCAT(i1.ItemIndex,'", "',i2.ItemId) as varchar(max)),
X = CONCAT(i1.X, ROW_NUMBER() OVER (ORDER BY i2.Cost DESC, i2.ItemId ASC)),
i2.ItemId, Cost = (i1.Cost + i2.Cost)
FROM totalCostPermutations i1
JOIN @Item i2 ON 1=1
AND i1.ItemId < i2.ItemId
)
SELECT
RN = ROW_NUMBER() OVER(PARTITION BY b.BuyerId ORDER BY Cost DESC, X ASC),
ItemId_JSON = CONCAT('{"ItemId":[',ItemIndex,'"]}'), TotalCost = Cost,  b.*
FROM totalCostPermutations c
JOIN @Buyer b ON c.Cost <= b.Budget;

At this stage our data looks like this:

But from this point I can't think of how could this be possible to tell which ItemId have already been "used" and which not, without looping.
Still, from this output it won't be hard to either loop it in T-SQL or via the app.


Where are the RETURN TABLE columns in the information schema?

Your query seems correct, so I'm not really sure what your question is? I created a function like:

CREATE OR REPLACE FUNCTION twice (x int)
   RETURNS TABLE (y int, z text)
AS $$
BEGIN
    RETURN QUERY
    SELECT x, 'a' UNION ALL SELECT x, 'a' ;
END; $$
LANGUAGE 'plpgsql';

and asked the catalog for:

SELECT r.routine_name, p.parameter_mode, p.parameter_name, p.data_type
FROM information_schema.routines r
LEFT JOIN information_schema.parameters AS p
    ON r.specific_name = p.specific_name
WHERE r.routine_name = 'twice';

routine_name    parameter_mode  parameter_name  data_type
twice           IN              x               integer
twice           OUT             y               integer
twice           OUT             z               text

Is it something that I did miss in your question?


How can I correctly choose maximum number of occurrences of a string while grouping by another field?

This isn't trivial. First, you want group by id and name and count the rows:

SELECT COUNT(*)
...
GROUP BY id, name

Then select the maximum count for every id. One way to achieve this is by window functions. The RANK() function:

RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC)

assigns a number to every row of the result (after the grouping is done), arranging them (the rows) in partitions with the same id and ordered by COUNT(*) DESC, so for every (partition of) id, the row(s) with the maximum count are assigned a rank of 1. Thus we need to put the above in a derived table and use a WHERE condition to keep only these rows:

WHERE rnk = 1

The final query is like this:

SELECT
    id, name, cnt
FROM
    ( SELECT id, name, COUNT(*) AS cnt,
             RANK() OVER (PARTITION BY id ORDER BY COUNT(*) DESC) AS rnk
      FROM tableX
      GROUP BY id, name
    ) AS tg
WHERE
    rnk = 1 ;

Tested at SQL-Fiddle

Note that if you have ties in the first place (two or more names with the same maximum count), all these will be returned. If you want strictly one row per id in the final results, you have to use the ROW_NUMBER() instead of the RANK() and possibly alter the ORDER BY clause to explicitly select how the ties will be resolved:

ROW_NUMBER() OVER (PARTITION BY id ORDER BY COUNT(*) DESC, name ASC) AS rnk

Tested: SQL-Fiddle test-2.


CSV data export to SQL Database with double quote problems

Set the text delimiter in the flat file reader properties to the double quote.


Extracting DDL Events for a database on Azure

Assuming you are on v12, then Extended Events (with Ring Buffer target) are available but there is what is known as a reduced surface area, or less things you can do.  If I run the query below to list available actions, events and targets (sourced from here) I get 941 rows on my SQL Server 2014 and only 71 rows on my v12 Azure database, as at today:

SELECT
        o.object_type,
        p.name         AS [package_name],
        o.name         AS [db_object_name],
        o.description  AS [db_obj_description]
    FROM
                   sys.dm_xe_objects  AS o
        INNER JOIN sys.dm_xe_packages AS p  ON p.guid = o.package_guid
    WHERE
        o.object_type in
            (
            'action',  'event',  'target'
            )
    ORDER BY
        o.object_type,
        p.name,
        o.name;

As you know with Azure, things get added so it may well catch up.

That said, there are three events available which would allow you to track DDL changes on your v12 Azure database:

object_altered
object_created
object_deleted

Based on these three events, I scripted this Extended Events session to track DDL on my v12 Azure database:

IF EXISTS ( SELECT * FROM sys.dm_xe_database_sessions WHERE name = 'xe_track_DDL_changes'  )
DROP EVENT SESSION xe_track_DDL_changes ON DATABASE
GO

CREATE EVENT SESSION xe_track_DDL_changes ON DATABASE
ADD EVENT  sqlserver.object_altered(
    ACTION ( sqlserver.username ) ),        -- <-- NB this was session_nt_username in on-premise SQL Server
ADD EVENT  sqlserver.object_created(
    ACTION ( sqlserver.username ) ),
ADD EVENT  sqlserver.object_deleted(
    ACTION ( sqlserver.username ) )
ADD TARGET package0.ring_buffer ( SET MAX_MEMORY = 4096 )
WITH ( EVENT_RETENTION_MODE = NO_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS );
GO

ALTER EVENT SESSION xe_track_DDL_changes ON DATABASE STATE = START;
GO

-- Do some ddl (in order to track it)
CREATE TABLE test ( id INT IDENTITY PRIMARY KEY )
ALTER TABLE test ADD addedBy SYSNAME
DROP TABLE test
GO

-- Inspect the ring buffer
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
GO

SELECT IDENTITY( INT, 1, 1 ) rowId, CAST( target_data AS XML ) AS target_data, GETDATE() dateAdded
INTO #tmp
FROM sys.dm_xe_database_sessions s      -- NB this is dm_xe_session_targets in on-premise SQL Server
    INNER JOIN sys.dm_xe_database_session_targets st ON s.[address] = st.event_session_address      -- NB this is dm_xe_sessions in on-premise SQL Server
WHERE s.name = 'xe_track_DDL_changes'
GO

ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
GO
--CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( target_data );  -- !!NB OPTIONAL - may improve performance but needs up to 5x original table size
GO

-- type of change and the user who made the change
SELECT
    t.rowId,
    e.c.value( '@name', 'VARCHAR(100)' ) ddl_action,
    o.c.value( '.', 'VARCHAR(100)' ) objectName,
    a.c.value( '.', 'VARCHAR(100)' ) [user]

FROM #tmp t
    CROSS APPLY t.target_data.nodes('RingBufferTarget/event') e(c)
        CROSS APPLY e.c.nodes('data[@name = "object_name"]/value') o(c)
        CROSS APPLY e.c.nodes('action/value') a(c)
GO

My results:

Remember to drop your Azure Extended Event sessions when you're not using them.  You might also consider an Event File target which writes to an Azure Storage container because the ring buffer can fill up and cycle.

HTH


Badly performing query plan after adding ORDER BY to Full Text CONTAINS search

The optimizer is choosing the different plan because it thinks the benefit of having the pre-sorted InsertDate (using IX_Foo_InsertDate) outweighs the cost once you add the order by clause.

The optimizer cannot be perfect, it might choose a slower path even if statistics are accurate - but it will certainly make poor choices if the statistics are not accurate - are AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS on?

If necessary, you might be able to 'trick' the optimizer like this:

SELECT top 100 Id, Name, InsertDate
FROM Foo
WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"')
ORDER BY some_function_of(InsertDate) desc

where some_function_of(InsertDate) has the same order as InsertDate


Column must appear in the GROUP BY clause or be used in an aggregate function when using CASE statement in ORDER BY clause

If all events have the same from_date:

SELECT event_id
FROM   date_ranges
WHERE  through_date >= current_date
GROUP  BY event_id, from_date  --  add it to GROUP BY
ORDER  BY from_date > current_date; -- simplified expression (assuming NOT NULL)

But then the column should be in the event table once per event.

If not, your sort order is ambiguous. I would start by asking how the same event can have different date ranges?

Your setup is broken either way.


How can this larger subquery be (much) faster?

Executon Plans
Reasons for different execution plans and/or different duration of execution plans can be because of different search predicates (aka WHERE clauses). This is what you have already observed.
When you change the WHERE clause and if the query isn't parameterised, then SQL Server's Query Optimizer will try and create a new best execution plan based on the information provided in the query.
To achieve the best execution plan, the Query Optimizer will have a peak at the statistics of the indexes/columns that could possibly be involved in the future execution plan and based on the information (data histogram) that is available in the statistics, the Query Optimizer will choose one or the other index.
That is a very short explanation on how SQL Server goes about creating an optimal execution plan based on the statistics.
Now what can impact the creation of a not so optimal execution plan?
If the data in the statistics are old or outdated or haven't been automatically updated, because the trigger value of the automatic statistics updated hasn't been reached, then the Query Optimizer could assume that an index is better than another and make a bad choice.
Trigger values for automatic statistic updates are when the following number of data rows have changed:

older than SQL Server 2016 : 20% of total rows changed + 500 rows
SQL Server 2016 or newer : Square Root (1000 * number of rows changed)

Reference: Statistics (Microsoft | SQL Docs)
Your Execution Plans
I took the liberty of having a look at your execution plans in SentryOne's Plan Explorer (free as in free beer, highly recommended, not affiliated). The advantage is that the tool displays the amount of rows for each branch of the execution.
Good Plan

Bad Plan

Observations
The execution plans are basically the same. It's just that the Clusterd Index Scan on [dbo].[RCH_DM_Printhistory] in the bad/slow plan is at the beginning and in the good/fast plan this step is performed later on.
Good Plan
If you have a look at the red number just above the lines, you will note for the good plan, that the query retrieves:

 2'874 rows from Nested Loop part
14'808 rows from the Clustered Index Scan

The Query Optimizer only has to compare 2'874 rows with 14'808 rows which is pretty fast.
Bad Plan
If you have a look at the number for the bad plan, then the query has to retrieve:

    5'759 rows from the Clustered Index Scan
4'048'577 rows from the Nested Loop part

In the bad plan the query optimizer has to compare 5'759 rows with 4 Mio rows, which is going to be slow anyway.
The Query Optimizer normally creates a plan that has tables/indexes with lower row numbers at the beginning in order to reduce the amount of data it has to compare with other tables later on in the execution plan.
Generally the execution plans seem to be the best possible execution plans for your statements.
Estimated Rows and Statistics
As pointed out by others the amount of rows estimated is always 1 compared to the actual amount of rows retrieved. This is bad, because it means the Queryy Optimizer is relying on statistics that contain the wrong information on the amount of distributed values in the indexes or relying on statistics that only have scanned a certain amount of data in the table(s).
Possible Solutions
(See script at end of post for retrieving pre-formatted statements and information on trigger level for status updates)

Update the statistics for the Clustered Index ORDERBY on the table [dbo].[RCH_DM_PRINTHISTORY]. You can retrieve the statements to update the statistics from my query at the end of the post.

Ensure Auto Update Statistics has been turned on for your database(s).
SELECT sdb.name,
       sdb.is_auto_create_stats_on,
       sdb.is_auto_update_stats_on,
       sdb.is_auto_update_stats_async_on
FROM sys.databases AS sdb

The value for is_auto_update_stats_on should be 1.

Ensure Auto Create Statistics has been truned on for your database(s).
SELECT sdb.name,
       sdb.is_auto_create_stats_on,
       sdb.is_auto_update_stats_on,
       sdb.is_auto_update_stats_async_on
FROM sys.databases AS sdb

The value for is_auto_create_stats_on should be 1.

Create the missing indexes mentioned in the execution plans. Be wary though. Sometimes the so-called "Missing Indexes" are already available. Test in a non-productive environment.

Re-write the query using temporary tables or CTEs.

Statement to Retrieve Statistics Information
SELECT 'DBCC SHOW_STATISTICS ([' + [sch].[NAME] + '.' +[so].[NAME] + '] , [' + [ss].[NAME] + ']) WITH STAT_HEADER'       AS [SHOW_STATISTICS],
       'update statistics ' + [sch].[name] + '.' + [so].[name] + ' ' + [ss].[name] + ' WITH FULLSCAN'                    AS [UPDATE_STATISTICS] -- PAGECOUNT=100, ROWCOUNT=100 | FULLSCAN
       'DBCC UPDATEUSAGE(' + DB_NAME() + ', ''' + [sch].[NAME] + '.' +[so].[NAME] + ''')'                                AS [UPDATE_USAGE],
       [sch].[name] + '.' + [so].[name]                        AS [TableName],
       [ss].[name]                                             AS [Statistic],
       [sp].[last_updated]                                     AS [StatsLastUpdated],
       [sp].[rows]                                             AS [RowsInTable],
       [sp].[rows_sampled]                                     AS [RowsSampled],
       [sp].[modification_counter]                             AS [RowModifications],
       100 / (1.0 * [sp].[rows]) * [sp].[modification_counter] AS [PercentChanged],
       SQRT(1000 * [sp].[rows])                                AS [> 2014 Algorithm Change Value],
       CASE
            WHEN SQRT(1000 * [sp].[rows]) < [sp].[modification_counter] THEN 1
            ELSE 0
       END                                                     AS [Auto Update > 2014 Triggered],
       [sp].[rows] * 1.0 / 100 * 20 + 500                      AS [<=2014 Algorithm Change Value],
       CASE
            WHEN [sp].[rows] * 1.0 / 100 * 20 + 500 < [sp].[modification_counter] THEN 1
            ELSE 0
       END                                                     AS [Auto Update <= 2014 Triggered]
FROM   [sys].[stats] [ss]
       JOIN [sys].[objects] [so]
            ON  [ss].[object_id] = [so].[object_id]
       JOIN [sys].[schemas] [sch]
            ON  [so].[schema_id] = [sch].[schema_id]
       OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id], [ss].[stats_id]) sp
WHERE  1 = 1
       AND [so].[type] = 'U'
           -- AND [sp].[modification_counter] > 0
           -- AND 100/(1.0*[sp].[rows])*[sp].[modification_counter] < 10.0     -- maximum percentage change (certain tables have a high volatility)
           -- AND 100/(1.0*[sp].[rows])*[sp].[modification_counter] > 0.001    -- minimum percentage change (we aren't going to be looking at statistics with a very low percentage of change)
           -- AND [sp].[rows] > 1000000                                        -- only look at statistics which contain more than 1'000'000 rows.
           -- AND [sp].[last_updated] < dateadd(hh,-1,getdate())               -- only look at statistics which have been updated more than an hour ago
       AND [sch].[name] = 'dbo'
       AND [so].[name] = 'RCH_DM_PRINTHISTORY'
       -- AND [ss].[name] NOT LIKE '_WA_Sys%'                                  -- Exclude automatically create statistics
       -- AND [ss].[name] not like '_dta_stat%'                                -- Exclude statistics crete by the Database Tuning Advisor
       -- AND (SQRT(1000 * [sp].[rows]) < [sp].[modification_counter] OR [sp].[rows] * 1.0 / 100 * 20 + 500 < [sp].[modification_counter])
ORDER BY
       [sch].[name] + '.' + [so].[name] ASC,
       [ss].[name] ASC,
       [sp].[last_updated] DESC;

This statement is pre-configured for the RCH_DM_PRINTHISTORY table mentioned in this question. Change the value for [so].[name] to search different tables.


Optimized Way of Scheduling a Differential Backup

First, this assumption is questionable:

  "Cannot use transaction logs as it would affect my load performance -
  datasets are potentially huge with large transactions"

When you do work in the database, your transactions are logged regardless. Full recovery mode just means your logged work sticks around until the next log backup. If non-logged operations are truly important to you, then you need to look at using minimally logged operations like bulk inserts. This is way harder than it sounds - check out the SQL 2008 data loading guide for more details. (For example, you may not be able to do it on a table that already has multiple nonclustered indexes and data in it.)

If it really is a good fit, though, check out this Serverfault post on estimating the size of your differential backup.


I would use extended events for this to break down the wait stats per query.  You can then see how much of it was waiting on "Network_IO" vs. mostly everything else.  There are a lot of wait stats and most of them would be server side work but not all so make sure you review all the other significant wait stats and also the time it took to come up with the query plan.

I'd recommend either downloading a trial of the SQL Sentry Plan Explorer and put your query in there.  It'll tell you right away how long it took, you'll want to do this from the client machine.

This is the link to SQL Sentry Plan Explorer.  This is super easy to use and doesn't require any coding. Simply install the free product on the client machine you want to test from, activate the free pro mode for 2 weeks (I would recommend buying this tool, it's great), then look at your wait stats.

This one will provide you with the wait stats per query (From Paul's blog):

    — Drop the session if it exists.
    IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'MonitorWaits')
        DROP EVENT SESSION MonitorWaits ON SERVER
    GO

    CREATE EVENT SESSION MonitorWaits ON SERVER
    ADD EVENT sqlos.wait_info
        (WHERE sqlserver.session_id = 53 /* session_id of connection to monitor */)
    ADD TARGET package0.asynchronous_file_target
        (SET FILENAME = N'C:\SQLskills\EE_WaitStats.xel',
        METADATAFILE = N'C:\SQLskills\EE_WaitStats.xem')
    WITH (max_dispatch_latency = 1 seconds);
    GO

--Then use this:
SELECT xmv.map_key, xmv.map_value
FROM sys.dm_xe_map_values xmv
JOIN sys.dm_xe_packages xp
    ON xmv.object_package_guid = xp.guid
WHERE xmv.name = 'wait_types'
    AND xp.name = 'sqlos';
GO

-- Start the session
ALTER EVENT SESSION MonitorWaits ON SERVER STATE = START;
GO

-- Go do the query

-- Stop the event session
ALTER EVENT SESSION MonitorWaits ON SERVER STATE = STOP;
GO

And then I can see how many events fired:

SELECT COUNT (*)
FROM sys.fn_xe_file_target_read_file
    ('C:\SQLskills\EE_WaitStats*.xel',
    'C:\SQLskills\EE_WaitStats*.xem', null, null);
GO

13324

/* And then pull them into a temporary table and aggregate them (various ways of doing this, I prefer this one): */

— Create intermediate temp table for raw event data
CREATE TABLE #RawEventData (
    Rowid  INT IDENTITY PRIMARY KEY,
    event_data XML);

GO

-- Read the file data into intermediate temp table
INSERT INTO #RawEventData (event_data)
SELECT
    CAST (event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file (
    'C:\SQLskills\EE_WaitStats*.xel',
    'C:\SQLskills\EE_WaitStats*.xem', null, null);
GO

SELECT
    waits.[Wait Type],
    COUNT (*) AS [Wait Count],
    SUM (waits.[Duration]) AS [Total Wait Time (ms)],
    SUM (waits.[Duration]) – SUM (waits.[Signal Duration]) AS [Total Resource Wait Time (ms)],
    SUM (waits.[Signal Duration]) AS [Total Signal Wait Time (ms)]
FROM
    (SELECT
        event_data.value ('(/event/@timestamp)[1]', 'DATETIME') AS [Time],
        event_data.value ('(/event/data[@name=''wait_type'']/text)[1]', 'VARCHAR(100)') AS [Wait Type],
        event_data.value ('(/event/data[@name=''opcode'']/text)[1]', 'VARCHAR(100)') AS [Op],
        event_data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT') AS [Duration],
        event_data.value ('(/event/data[@name=''signal_duration'']/value)[1]', 'BIGINT') AS [Signal Duration]
     FROM #RawEventData
    ) AS waits
WHERE waits.[op] = 'End'
GROUP BY waits.[Wait Type]
ORDER BY [Total Wait Time (ms)] DESC;
GO

-- Cleanup
DROP TABLE #RawEventData;
GO

This script from Paul Randall will provide you all the wait stats over a period of time:


"SELECT LAG(id) FROM cb_cat WHERE id=450";

Window functions work on the result after the WHERE clause is applied. Since id is unique and you ask for exactly one id, that result has a maximum of one row. There just isn't any previous row whatsoever.
Further more relational tables represent relations which are a special form of (multi) sets. And (multi) sets aren't ordered. Unless there's an explicit ORDER BY, the order the tuples of a result are returned in is to be considered random. Window functions depending on an order therefore also need an ORDER BY specified in the OVER clause, unless they just should pick some random row. You didn't specify what defines your order, I assume it's the id in the following.
Using lag() you'd have to select all rows in a derived table where you apply the lag() and then go for the row with the id of 450 in the outer SELECT.
SELECT l
       FROM (SELECT id,
                    lag(id) OVER (ORDER BY id) l
                    FROM cb_cat) x
       WHERE id = 450;

This can be optimized a little. Since id defines the order, we actually only need to look at the records with an id lower than or equal to 450 in the inner SELECT. (I have doubts the optimizer would figure this out by itself. But maybe I'm underestimating its capabilities.)
SELECT l
       FROM (SELECT id,
                    lag(id) OVER (ORDER BY id) l
                    FROM cb_cat
                    WHERE id <= 450) x
       WHERE id = 450;

But if we think of it, what we actually want is the the largest id that is less than 450. lag() isn't the best tool to get this, LIMIT is. We get all ids less than 450, order them in descending order and pick the first one of that ordered result.
SELECT id
       FROM cb_cat
       WHERE id < 450
       ORDER BY id DESC
       LIMIT 1;


Show specific data from a partitioned table in SQL Server. Data Archiving using Table Partitions

If you want to archive it and do not want it to be returned in default queries, then remove it from the table and add the filegroup for this partition to another table tblUserDetailsArchive. Then any user could query

select * From tblUserDetailsArchive

or

select * From tblUserDetailsArchive where datecreated < '20140101'

to get the older rows if required.


Index on a highly updated field causing excessive locks in SQL server

Instead of using the DateChanged column in the where clause perhaps you could add an IDENTITY column, and use it in the .Net client to keep track of processed rows?  The .Net client could periodically save the last processed identity value in case it needs to restart.

This would allow you to get rid of the index on DateChanged completely, and queries getting the new rows would be fast since they would be performing a range scan on values greater than the last processed identity value.  Something like:

SELECT
  [TrackSessionID],
  [DataPartID],
  [UserMasterID],
  [RecordID],
  [DateChanged]
FROM dbo.TrackSessions
WHERE [TrackSessionID] > @LastTrackSessionID
ORDER BY DateChanged DESC;

A potential downside to this approach might be the IDENTITY column becoming a source of contention for inserts into the table.  That could potentially be mitigated by using several partitions with a partition function that uses a hash function to decide which partition each insert goes into.

Something like this comes to mind:

CREATE PARTITION FUNCTION pf_TrackSessions (INT)
AS RANGE RIGHT FOR VALUES (1, 2, 3);

CREATE PARTITION SCHEME ps_TrackSessions
AS PARTITION pf_TrackSessions
ALL TO ([SessionFileGroup1]
    , [SessionFileGroup2]
    , [SessionFileGroup3]
    , [SessionFileGroup4]);

CREATE TABLE dbo.TrackSessions
(
    TrackSessionsID INT NOT NULL IDENTITY(1,1)
    , TrackSessionsIDModulus AS (TrackSessionsID % 4) PERSISTED
    , DataPartID INT NOT NULL
    , UserMasterID INT NOT NULL
    , RecordID INT NOT NULL
    , DateChanged DATETIME2(7)
        CONSTRAINT DF_TrackSessions_DateChanged
        DEFAULT (GETDATE())
    , CONSTRAINT PK_TrackSessions
        PRIMARY KEY CLUSTERED
        (TrackSessionsIDModulus, TrackSessionsID)
) ON ps_TrackSessions (TrackSessionsIDModulus);
GO

The above DDL requires 4 filegroups, one for each partition.  Rows would be evenly distributed among the 4 filegroups based on the modulus of the identity value.  This reduces contention against the allocation bitmap pages by splitting the load across 4 sets of each structure.


PostgreSQL Full Text Search (FTS), what is the difference between ::tsvector and to_tsvector()?

The difference between the string->tsvector cast and the function-constructor to_tsvector(text)

the cast (::) assumes the input is already a tsvector that's been casted to text. It does not use the language-specific stubbing mechanisms or store positional information, it simply assumes it is given a space-delimited text-search tokens (positions and lexemes) and assumes they've already undergone the proceess.
to_tsvector() takes string input and does all the requisite work to tokenize it, including normalizing and stubbing.

You should probably avoid the cast for everything but text representations of tsvector.

Examples

Here we read the string 'test testing tested' as three tsvector lexemes with undefined positions, notice the output lacks stubbing

test=# select 'test testing tested'::tsvector;
         tsvector
---------------------------
 'test' 'tested' 'testing'
(1 row)

Here we "convert" a string of words to lexemes with positions by sending the string through the process specified in your specific TEXT SEARCH CONFIGURATION, in my case for english.

test=# SELECT to_tsvector('test testing tested');
 to_tsvector
--------------
 'test':1,2,3
(1 row)

Here we explicitly tell PostgreSQL to use the simple text search configuration.

test=# SELECT to_tsvector('simple', 'test testing tested');
           to_tsvector
---------------------------------
 'test':1 'tested':3 'testing':2
(1 row)

So what is the cast used for? You could however use it for something like this

SELECT $$'test':1 'tested':3 'testing':2$$::tsvector @@ 'testing';

Here is another case demonstrating what it does.. (returns true)

SELECT to_tsvector(test) = to_tsvector(test)::text::tsvector
FROM ( VALUES
  ( 'This is my test. Hello from testing test tested' )
) AS t(test);

If you're getting the tsvector over the wire, or serializing it as text in the case of pg_dump, that can be useful.


Why “sudo vim /etc/my.cnf” file is empty?

.my.cnf in your home directory is only read by the mysql clients, so this is not where you should set system variables for the mysql server. .my.cnf can be used e.g. for your credentials so that you can log in without having to type the username and password every time.

The system variables for the server as well as system wide for all users can be configured in /etc/my.cnf and files included by that file. If you want to edit this or change any system variables, make sure to set the variables in the correct section of the file. See the documentation on option files for more details and examples.

SHOW VARIABLES; will show all system variables and their values - this includes variables you haven't explicitly set in your .cnf files as it will also show variables that just have default values.


You should re-evaluate your database design. A relational database is not designed to store arrays in columns. Actually, elements in arrays are "columns" itself. So, what you're trying to do is store multiple columns in a single column...

I'd recommend two tables:
Persons (PersonId, Name,...)
Friends (FriendsId, PersonId, FriendId, Accepted), where both, PersonId and FriendId have a foreign key to Persons.PersonId. The column Accepted will be Zero as long as it is not accepted. When accepted, just change it to 1. Therewith, you don't have to move rows arond and take care that there are no inconsistent data (or even missing data).


How to write a query that will retrieve the items with sold amount

Not exists will work here

 select item_id, amount_sold
       from table t
   where transaction_date = trunc(sysdate)
     and not exists (select 1
                       from table t1
                      where t1.item_id = t.item_id
                          and t1.transaction_date = trunc(sysdate)-1;

EDIT based on OP's latest observations :

select item_id, SUM(amount_sold) as amount
       from table t
   where trunc(transaction_date) = trunc(sysdate)
     and not exists (select 1
                       from table t1
                      where t1.item_id = t.item_id
                          and trunc(t1.transaction_date) = trunc(sysdate)-1
 group by item_id;


despite having never done this before

that's not a problem at all - I just would recommend you do all this on a test server first. the whole process. then you do it in live.

This is how I would do it:

you create the domain account sqlaudits as you mentioned and take note of its password as you will need it later on
create a login for sqlaudit on the server you want to audit
grant sqlaudits all the permissions it might need: on that server

Check the permissions for altering an audit:

a)To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission.

b)Users with the ALTER ANY SERVER AUDIT permission can create server audit specifications and bind them to any audit.
run ssms as a different user using the sqlaudits account credentials

to run ssms a different user press the shift and right click on the ssms icon and that will give the options below:

Now you are sqlaudits and the objects you create will be technically owned by you


Keep the simple dependencies as is:

b -> f
d -> e

Remove the dependent columns from the other dependencies.

a -> b,c,d
b,c -> a,d

At this point you have two candidate keys a and b,c.  As we already have a dependency on b.  I would keep the b,c dependency and drop the dependency on a.  It would appear that b,c defines a weak entity (or repeating group) related to b.  I would consider creating a unique key on a.


Verbose Oracle RDBMS error log output examples?

I'm not sure what you really want but the official documentation is a good place to start, all 86 pages of it.

Just thinking about Oracle error codes gets me all nostalgic for those cryptic messages

ORA-00600: internal error code  (something happened but we can't tell you)
ORA-03113: end-of-file on communication channel (you, or your database or your connection are down, somewhere...)
ORA-12154: TNS: could not resolve the connect identifier specified (your database does not want to talk to you)

You also may be thinking of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which will tell you more about errors in PL/SQL programs.  This is not the same as your question about verbose messages for SQL queries.


No database is fully SQL compliant. But out of the SQL databases that provide any form of scaling you have PostgreSQL w/ PostGIS and MySQL to choose from.

PostGIS is the superior choice. For why see my comparison here.


The table locks on MyISAM can be a killer and migrating to InnoDB is probably one of the best things you can do to continue to improve scalability.  Of course, your change to innodb_buffer_pool_size won't impact tables that aren't InnoDB.

One problem, however, is that the version of InnoDB in MySQL 5.0 is still quite primitive compared to later releases, especially when it comes to multicore machines and internal scaling.  This is discussed at length in High-Performance MySQL, which was last updated a little over a year ago but is still quite useful even though MySQL 5.6 was discussed in a mix of present- and future-tense due to its release status at the time.  I don't have any affiliation with the book or its authors, I just think it's a great reference.  If you don't have it, I'd recommend it, because it goes into a lot of detail about what to do, what not to do, and why.

But, if the system under consideration were my system, I'd be planning to upgrade to MySQL 5.5, at a minimum, and possibly MySQL 5.6, because of the significant improvements in the internals of InnoDB as well as elsewhere.

Looking at your config, the query cache is always something to consider when you're looking at performance issues.  It could be that a larger cache would help (perhaps 128M to 256M) but it's also possible that a smaller or disabled query cache might be beneficial, since it does represent a global choke-point that every SELECT query has to pass through.  The appropriate setting is almost entirely workload-specific.

Nothing jumps out at me in your configuration as being particularly sub-optimal, but I would add that if you've been tempted to use any of the tuning "scripts" you find online... try to resist that temptation.  "Tune" only what you have a specific reason to tune, and only one parameter at a time.  After you successfully upgrade, try to remove as much of the customized values as possible (except innodb_buffer_pool_size) and let the behavior of the new version with its default values dictate what needs to be tweaked.

The officially recommended path when upgrading across versions is always to do a full mysqldump from the old, and restore on the new installation, though it is possible to do a "binary" upgrade where you simply start the new version code against the old version's datadir and do mysql_upgrade.  The official path would be to go from 5.0 to 5.1, and then from 5.1 to 5.5.

http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html
http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

There's a lot to digest but the bottom line is that 5.0 is at end-of-life and hasn't had so much as a bugfix release in over a year... and the newer versions represent a substantially-improved product that should not require major changes in your application.


How would I grant select on a table being used in a different database to a user role in SQL?

Open SQL Server Management studio 2008.
Expand the server instance > Security > Logins > and locate the
log in you have having an issue with.
Right click the user and select Properties.
Select User Mappings.
Locate the database you want the user to have access to and check
the checkbox in the map column.
Set the correct database membership for this user and click OK.
Try your code again, be sure to qualify the path properly like
[database_name]..[Table_Name] in your FROM.


postgreSQL update set only where cast is possible and ignore error?

Just remove the ':' characters and the update will work:

UPDATE mactable SET mac_address = CAST(replace(mac_address_temp,':','') as macaddr);

To get rid of everything that's not a hex digit:

postgres=# insert into mactable (mac_address_temp) values ('AAB5:-4f-27:e2-99');
INSERT 0 1
postgres=# select regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g')
postgres-# from mactable;
 regexp_replace
----------------
 AAB54f27e299
 AAB54f27e299
(2 rows)

postgres=#

So the update would be:

UPDATE mactest SET mac_address = CAST(regexp_replace(mac_address_temp, '[^a-fA-F0-9]', '', 'g') as macaddr);


Length of varchar column - is there an advantage to using 255?

I rant against 255 occasionally.  Sure, there used to be some reasons for '255', but many are no longer valid, and even counter-productive.
In MySQL, there are reasons to stop at 191, 255, 767, 3071, 64K, and probably other values.  Some depend on Engine, some on CHARACTER SET, etc.
A VARCHAR is stored as a 1- or 2-byte length plus enough bytes for the current text in whatever charset you have specified.  However, the choice of 1 or 2 is not driven only by the individual column; it is driven by the total row size.  That is, this is not a valid excuse for using 255.
Use a length that is

Big enough to conservatively never be exceeded, yet
As small as seems reasonable.

While I am ranting...  CHAR (fixed length) is rarely advised.  And almost always it should be CHARACTER SET ascii -- country_code, postal_code, Y/N, M/F, MD5, UUID, base64, etc.  (MD5 and UUID should be taken a step further, but that is another rant.)
Potential negative impacts of blindly using '255':

If you have a lot of columns, you could hit a max row size limit and CREATE TABLE will fail.
You could overflow a limit on index size.
Complex SELECTs may need a temp table, and may use MEMORY for it.  In this case, VARCHAR(255) becomes CHAR(255) for the temp table.  And, if using utf8, that is 755 bytes for every row!  (8.0 fixes this design flaw?)

Related:  Don't blindly use BIGINT for all numbers.  It takes 8 bytes.  Even INT is overkill, at 4 bytes.  See MEDIUMINT, SMALLINT, and TINYINT.  Be aware that the (2) on INT(2) means nothing; it still takes 4 bytes.


Calling a stored procedure in a stored procedure

This can be good thing.

If you have to create bar and mark objects for other reasons or in other place then having a reusable stored proc eases maintenance and allows all checks in one place.

If I have to create n bar objects I'd probably loop n times rather than doing it set based if it wasn't critical for performance. Maintainability wins out for me. Of course, maybe my stored proc should deal with 1 to n bar creations... but then again did I know this up front? :-)

My answer here shows you how to nest stored procs with error handling: What is the best method to add error handling in SQL 2005 stored procs?


How to link rows in a many-to-many relationship where types have parents, grandparents etc

Option 2.

Only save a row for the assigned category, no extra rows for category parents. Don't store redundant information in your tables. That's bad for general performance and for maintenance. (Also don't save the name of the product in table prod_cat redundantly)

A recursive query on category is cheap and simple. You might encapsulate it in a function for convenience:

CREATE OR REPLACE FUNCTION f_cats_of_id(_id int)
  RETURNS TABLE (category_id int) AS
$func$
WITH RECURSIVE cte AS (
   SELECT $1 AS parent_id  -- not checking existence, cheapest

   UNION ALL
   SELECT c.id
   FROM   cte
   JOIN   category c USING (parent_id)
   )
TABLE cte
$func$  LANGUAGE SQL;

And this query to ...

  ... get all products that 'are' in category with id 1 (including subcategories)

SELECT DISTINCT p.*  -- assuming you want all columns of table product.
FROM   f_cats_of_id(1)
JOIN   cat_prod cp USING (category_id)
JOIN   product p ON p.id = cp.product_id;

DISTINCT because ...

  ... a Product can be in multiple categories

The same product might be listed more than once, via category and sub-category. DISTINCT folds duplicates.

db<>fiddle here


Compare Two Columns, Remove Duplicate Characters

Assuming that you mean to remove the character of CA from CB in the same row and that there's always just one character in CA, you can do it like this:

UPDATE your_table
SET CB = REPLACE(CB, CA, '');


There's no broad standard that I've seen used or heard widely recommended.  Some recommend prefixing tables with tbl but some are very against that.  Some, like myself, prefer to name the ID column simply so you always know what the ID column of any table will be called ID while others, like my coworker, prefer to put the tablename (ie - serverid) so what it's called in another table matches the column name in the parent table.

The most important thing is that the scheme you use remains consistent, especially within the same database.  Most DBAs can pick up on the naming format as long as it doesn't change within an environment.


Why is the value - despite of using GREATEST() - still out of range?


How to find out if a row with a specific primary key exists in a table in SQL

I actually tried to look at this link: https://www.w3schools.com/sql/sql_join.asp
I describes how to INNER JOIN. It seems that I got the correct result using the below code.

Assuming that testTable is the one with 100 Million Rows
Assuming that testTable2 is the one with 5000 Rows

I could find the exact matches for FeatureNbr and DateTime as a result.
Please tell if I do miss anything there or if I am thinking wrong so I can edit the post.
SELECT testTable.DateTime, testTable.FeatureNbr
FROM testTable
INNER JOIN testTable2 ON testTable.FeatureNbr=testTable2.FeatureNbr AND
                         testTable.DateTime=testTable2.DateTime;


Checks to be done, before/after changing the windows login password?

Read this MSDN page for details about what to do on the SQL Server box.  This is taken from that page:

The SQL Server Database Engine and SQL Server Agent run on a computer as a service using credentials that are initially provided during setup. If the instance of SQL Server is running under a domain account and the password for that account is changed, the password used by SQL Server must be updated to the new password. If the password is not updated, SQL Server may lose access to some domain resources and if SQL Server stops, the service will not restart until the password is updated.
Change the password of the accounts used by the Database Engine and the SQL Server Agent in SQL Server 2012 by using SQL Server Configuration Manager.

If applications or other services are connecting to the SQL Server using that domain account, they will also need to know the updated password.  You may want to look at enabling logon auditing to help understand where those applications are.


ORA-01653 Unable to Extend Table AGILE.A_DW_TXN_LOG BY 128 in Tablespace AGILE_DATA3

The error is stating that either all of your tablespace data files have reached the limit of their size supported by the OS (usually 32GB each) and cannot grow, or that the storage media has physically run out of space and they cannot grow. Since the size of the tablespace appears to be about 25GB (the BYTES column) and the upper limit is 32GB (the MAXBYTES column), I'd say it is most likely that your disk is full.
That said, datapump will not automatically empty tables unless you tell it to. By default it will skip existing tables entirely and not load anything. Use the TABLE_EXISTS_ACTION parameter to tell datapump what to do in cases where tables already exist, as described here: https://docs.oracle.com/database/121/SUTIL/GUID-C9664F8C-19C5-4177-AC20-5682AEABA07F.htm#SUTIL936


Transaction Log backup overwrites full backup on same media

You are specifying INIT on both your FULL and transaction log backups.

If you want to 'append' the transaction log backups to the FULL backup, specify INIT on the FULL and NOINIT on the transaction log backups.

If you don't want your transaction log backups 'appended' to your FULL, I'd suggest generating a dynamic transaction log backup command 'appending' a timestamp onto the transaction log backup file name and using INIT.

Here is an 'example'

SET NOCOUNT ON
declare @BackupTimeStamp varchar(25)
declare @BackupCommand varchar(max)
declare @VerifyCommand varchar(max)
declare @ServerName varchar(max) = (select @@SERVERNAME)

set @BackupTimeStamp =
    right('00' + CONVERT(varchar(4),datepart(year,sysdatetime())),4) + '_' +
    right('00' + CONVERT(varchar(2),datepart(month,sysdatetime())),2) + '_' +
    right('00' + CONVERT(varchar(4),datepart(day,sysdatetime())),2) + '_' +
    right('00' + CONVERT(varchar(4),datepart(hour,sysdatetime())),2) +
    right('00' + CONVERT(varchar(4),datepart(minute,sysdatetime())),2) +
    right('00' + CONVERT(varchar(4),datepart(second,sysdatetime())),2)

set @BackupCommand =
'
BACKUP LOG [SCOPERATIONAL] TO
DISK = ''\\FBPISILON01\DBBackups-SC\' + @ServerName + '\Xlogs\SCOPERATIONAL_backup_' + @BackupTimeStamp + '.bax'' ' +
'WITH FORMAT, INIT,  NAME = ''SCOPERATIONAL_BACKUPS\SCOPERATIONAL_backup_' + @BackupTimeStamp + ''''
print @BackupCommand
exec (@BackupCommand)  --should probably be sp_executesql

Keep in mind, that EACH transaction log backup will continue to be larger UNTIL a non-COPY_ONLY backup is taken.

Additionally, should you decide to create transaction log backups that are not appended to your FULL backups, you'll need to create some sort of pruning/purging process to delete older transaction log backups.


Data modeling, double foreign key problem

Like @J.D. mentions in his comment it is not obvious what problem you are trying to solve. If I were to guess PARAM_VAL could be consructed as:
CREATE TABLE param_val
( id_quali ... NOT NULL
, id_param ... NOT NULL
, id_cand ... NOT NULL
, PRIMARY KEY (id_quali, id_param, id_cand) -- I guess
, FOREIGN KEY (id_quali, id_param)
      REFERENCES param_weight(id_quali, id_param)
, FOREIGN KEY (id_quali, id_cand)
      REFERENCES quali_participation(id_quali, id_cand)
);


Generate permutations and combination for name column

If you split each name into a separate table of the following structure,

CREATE TABLE dbo.NameParts
(
  ID int NOT NULL,
  NamePart varchar(30) NOT NULL
);

(where ID marks name parts that belong to the same name, to avoid permutation of parts of different names) – then you can use a recursive CTE to produce the permutations:

WITH
  permutations AS
  (
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(t.NamePart AS varchar(500)),
      Level = COUNT(*) OVER (PARTITION BY t.ID)
    FROM
      dbo.NameParts AS t
    UNION ALL
    SELECT
      ID = t.ID,
      FullNameVariation = CAST(p.FullNameVariation + ' ' + t.NamePart AS varchar(500)),
      Level = p.Level - 1
    FROM
      dbo.NameParts AS t
      INNER JOIN permutations AS p ON t.ID = p.ID
    WHERE 1=1
      AND p.Level > 1
      AND ' ' + p.FullNameVariation + ' ' NOT LIKE '% ' + t.NamePart + ' %'
  )
SELECT
  ID,
  FullNameVariation
FROM
  permutations
WHERE
  Level = 1
ORDER BY
  ID,
  FullNameVariation
;

Applying the query to this data sample:

INSERT INTO dbo.NameParts
VALUES
  (1, 'Sam'), (1, 'Mak'), (1, 'John'),
  (2, 'Donatella'), (2, 'Nobatti'),
  (3, 'Sam'), (3, 'Buca'),
  (4, 'Juan'), (4, 'Soponatime'),
  (5, 'Aaron'), (5, 'Spacemuseum')
;

produces the following output:

ID  FullNameVariation
--  -----------------
1   John Mak Sam
1   John Sam Mak
1   Mak John Sam
1   Mak Sam John
1   Sam John Mak
1   Sam Mak John
2   Donatella Nobatti
2   Nobatti Donatella
3   Buca Sam
3   Sam Buca
4   Juan Soponatime
4   Soponatime Juan
5   Aaron Spacemuseum
5   Spacemuseum Aaron

You can play with this solution using a live demo at db<>fiddle.uk.


Sorry, I need a 50 rep to comment, so...  Here's a couple of things to start.

Are you by chance using ZFS for your filesystem (it can throttle the disk unexpectedly)?
Are your config files (nearly) identical (with exception of replication master/slave settings)?  The config should be the same as the other slave except maybe memory buffers (sounded like the server HW was different).  https://www.percona.com/doc/percona-toolkit/2.2/pt-config-diff.html
(Run on the slave, and post) SHOW SLAVE STATUS\G
(Run on the master, and post) SHOW MASTER STATUS\G
Check the Slave Status a few times (maybe a minute apart). Are both Exec_Master_Log_Pos and Read_Master_Log_Pos (or Retrieved_Gtid_Set and Executed_Gtid_Set), moving (ideal is if they are catching up)?
Are the relay logs being stored as tables or files?

Edit
After reading Rick's post, I went back and noticed (thought at first both were SBR) your cluster that runs faster is running SBR (statement based replication), and the slow one is RBR (row based).  Rick's question about the type of queries and how many binlogs you generate in say an hour is important.

Sometimes RBR is faster, and sometimes SBR wins.  Always important to test and benchmark all scenarios.


How to Recover My database From Old WampServer?

Before I start, I will say that I am not familiar with wampserver but I am familiar with MySQL and LAMP.

First things first, are your tables within your databases MyISAM, InnoDB or a mixture of both? - You can tell on the file level by looking at the suffixes.

MyISAM tables have the table name with the following suffixes:

.frm
.MYD
.MYI

InnoDB tables have the table name with the following suffixes:

.frm
.ibd

If they are MyISAM, copy the tables straight into the new MySQL database directory and this will work as long as the files have the correct read write permissions and ownership (mysql).

If you have InnoDB tables then this will be an issue because you can't simply copy them into a location, in this case what you would have wanted to do is:

MySQL dump the databases, then MySQL import
Convert them to MyISAM across the board, make a  copy of the file system, then move them into the new database directory, then change back to InnoDB if needed.

** Please note, do not copy the mysql db if you have it within your backup into the new MySQL directory because this will cause all sorts of issues.


Since it's only UPDATE,INSERT you can say:

IF EXISTS (SELECT 1 FROM deleted)
  -- update
ELSE
  -- insert

You have a bigger problem, though. There is no such thing as new.Id, and an insert or update can affect multiple rows (in some platforms, triggers fire per row; in SQL Server, they fire per operation). So you need to either:

Use a loop to call the stored procedure for all of the RecordId values in inserted.
Stop using a stored procedure for this and perform whatever logic it does inside the trigger, as a set-based operation.


Creating a SQL Login and User on an AlwaysOn Replica

You need to create the login on the DB2 server. Use Robert's script so that it grabs the sid and password, well if it's a SQL account that is.

Then grant the permission on DB1. That permission will auto-magically get copied over to DB2 due to the AG.

There is no way to just grant permission on DB2. It has to be done on DB1. You can control access by disabling the login on DB1 though.


Load them all at once - WAMP.  Their current version has 64-bits (or 32-bits) with

Apache 2.4.9
MySQL 5.6.17
PHP 5.5.12
PHPMyAdmin 4.1.14
SqlBuddy 1.3.3
XDebug 2.2.5

That should be easier and better than installing end-of-life software.


Help needed with SQL Server query: Accessing columns not in GROUP BY clause

In SQL Server 2017 or Azure SQL Database:

SELECT vYear, STRING_AGG(vDate, ' and ')
FROM #tmpTable
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear;

In SQL Server 2016 or lower:

SELECT vYear, STUFF((SELECT ' and ' + vDate
  FROM #tmpTable
  WHERE vYear = t.vYear
  ORDER BY vDate
  FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,5,'')
FROM #tmpTable AS t
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear;


Equivalant MongoDB query of MySQL date query with arithmetic operations

The equivalent MongoDB Query is

 db.temp_table.aggregate([{$project:{field1:1,field2:1,hours:{$abs:{$divide:[{$subtract:[ISODate(),"$datefield"]},3600000]}},time:{$sum:[{$multiply:["$field1","$field2","$field2"]},{$multiply:"$field3",{$divide:[1,{$abs:{$divide:[{$subtract:[ISODate(),"$datefield"]},3600000]}}]}]}]}}}])

Fields to be shown can be added as per the requirement.


mysql starts and stops servers automatically non stop

Of course you will see your terminal running the grep command .. that's pretty much to be expected. Please learn some console basics (either mac os or free bsd related  as that is the underlaying system that mac os builds on )

If ps -> grep really only shows its own instance then there is nothing else with "mysql" in its process name.
Which pretty much explains that you cant kill the process afterwards - because the console-command did its work and then the process ended regularly. Hence your second attempt "generated" a new process ID (new process) ...

At the time of your ps -> grep NO "other" mysql process is running.


Large PRIMARY KEYs and UNIQUE KEYs are a very bad idea for InnoDB. If you want a real performance boost for subsequent retrieval, you must use smaller PRIMARY KEYs. Why?

The smaller the PRIMARY KEY, the better things will be
Secondary indexes have a primary key associated with each secondary key. Bigger PRIMARY Key, bigger Secondary Index. and Vice-Versa
Caching improves with smaller primary keys

Here is what you should do with 64-char keys

STEP01) Create a Key Table for 64-character keys

Start with a table that will hold the 64-character key and associate it with an auto incremented field

CREATE TABLE big64keytable
(
    bigkey   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    big64key CHAR(64),
    PRIMARY KEY (bigkey),
    UNIQUE KEY (big64key)
) ENGINE=MyISAM;

STEP02) Create a Stored Function to Get numeric ID for the 64-character key

First, create the SQL to INSERT your 64-character key and then retrieve the numeric ID

SET @givenkey = '8g4gbf3g7b5gf4n3gfn8g927534';
INSERT IGNORE INTO big64keytable (big64key) VALUES (@givenkey);
SELECT bigkey FROM big64keytable WHERE big64key = @givenkey;

Now take this simple retrieval algorithm and place it in a Stored Function

DELIMITER $$

DROP FUNCTION IF EXISTS `GetBigKey` $$
CREATE FUNCTION `GetBigKey` (GivenKey CHAR(64)) RETURNS INT UNSIGNED
DETERMINISTIC
BEGIN

    DECLARE rv INT UNSIGNED;

    INSERT IGNORE INTO big64keytable (big64key) VALUES (GivenKey);
    SELECT bigkey INTO rv FROM big64keytable WHERE big64key = GivenKey;
    RETURN rv;

END $$

DELIMITER ;

Going forward, simply generate a numeric unsigned ID  (4 bytes ) for each 64-character key.

Here are two of my past posts in the DBA StackExchange on other large PRIMARY KEY issues

Dec 22, 2011 : File name as primary key?
Oct 18, 2012 : MySQL: Unique constraint on large column


Why is my SQL server using ~4 GBs of memory?

SQL Server uses that memory because it needs it, and typically won't release it unless there is pressure from the operating system to do so (since it assumes that you'll need that much memory again). So it is actually a good thing that it is using 4 GB of RAM, and if you don't need that memory for other things, why do you care?

You have basically two options:

Limit SQL Server to something lower (e.g. 2 GB). This will make your queries slower, but at least you'll still have all that precious RAM to sit idle.
Restart SQL Server when you're done with any high-memory operations that push it up over 4 GB. This will allow your queries to run with the memory they need, but still return that memory when finished. Not my idea of fun times, but hey...


Export and Import large database from one rds of amazon to newly created amazon rds

You could just use the RDS feature "Restore to point in time", which will create a full point-in-time snapshot of your current RDS instance:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html


Backup from Azure SQL Managed Instance and restore on an On-Prem version

Currently the only way to restore a database from Azure to OnPrem is the bacpac method.

Also FYI:
I am currently looking at Azure MI migration but due to the fact you can't point a DNS CNAME at it I am unable to Migrate.


Can't connect to a PG database from MicroOLAP, but can connect from SSH?

From the error dialog it is clear that something around your connection is wrong.

First be sure that everything you included here matches: you have an error about localhost and port 3082 and you present a connection dialog with a foreign host and port 5432.

Then you have to teach PostgreSQL that it should accept connections from your home.  This can be done via the pg_hba.conf file.  You will need a line added like this (the header is already there) - if you don't have access to it, ask the person in charge of it to do this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    dnizetic        dnizetic        161.53.120.19/32        md5


Oracle 10g: Optimizing an update query that uses a subquery

Try the below to update unique issuer id and issuer name instead of using Distinct by using Group by together with the index on Issue Id and Issuer Name in Temporary reporting table

Kindly refer the below link
Fast Query To Get TOP-N Distinct Rows From A Huge Data Table

UPDATE TBL_REPORTING REP
SET(REP.ISSUER_ID, REP.ISSUER_NAME
) = ( SELECT TMP.ISSUER_ID
                   ,TMP.ISSUER_NAME
            FROM   TMP_REPORTING TMP
            WHERE  TMP.CMD_KEY = REP.CMD_KEY
            AND    TMP.REPORTING_KEY = REP.REPORTING_KEY
            GROUP BY TMP.ISSUER_ID
                   ,TMP.ISSUER_NAME
        )
WHERE  REP.CMD_KEY = v_cmd_key;


Does putting single quotation marks around numeric constants really protect from SQL injection in MySQL?

Short answer, no.  The quoting trick is easily defeated by including your own closing quote and then a comment symbol to eliminate the final concatenated quote, precisely as in your example.

To protect yourself from SQL injection you must use bind variables.  Changing your example to SELECT * FROM table WHERE ID = :X and then binding the user's input to X solves the problem instantly and completely.  It is impossible to over-emphasize how important this practice is!


Oracle : Redo / Undo for nologging table + Append + archivelog

It looks like you set your indexes to unusable.
I believe that just means the index won't be used if you query the table.

However, during your loading, the index will be maintained with inserted and updated values, thus generating redo even when nologging is used.

"nologging" for index will only work when used with create index statement.

There may be other reasons but this is one of them.

Try the following and see if it generates less or no redos:

Drop indexes
Load table with /*+APPEND*/(will be faster too without indexes on
the table)
Create index with "nologging" option


How to best store limited specifications?

You're correct that a new table is the best way to store it. Referencing Type from the ID of the value in a secondary table will allow you to normalize your data. This helps ensure integrity, reduce duplicate data, and allow for effective querying of any type.

You're also correct that this results in the creation of several tables - welcome to the world of relational schemas :) It may seem like a pain now, but long term this method will be the best for storing, updating, deleting, and querying your data.

In any of these scenarios, you should always create a separate table for each entity, whether they have 3 data types or 10 data types that may or may not change. Requirements may change down the road and you should plan for the unexpected! You can tell what fields should be branched out into separate tables by seeing if it follows the rule for third normal form,

  Each column must depend on directly on the primary key.

Since Type does not follow this, it is best as a separate table.

Further Reading: Litt's Tips on Normalization


Deployment script always re-create constraints and indexes

It is normally because there is a difference between how they are written and how sql stores them.

As a one time thing to a compare back from the db to the project and you should see some difference on the keys, apply them and it should stop doing it.


The 'DATALENGTH()' function returns the length of just the data, those extra two bytes are considered "meta-data" - information about the data and/or its storage.

You get the same value if you pass the function a string literal (DATALENGTH('12345') returns 5) - this wouldn't be the case if the function accounted for storage overheads as well as the string's content.

To account for storage overhead the DATALENGTH function would need to know or be able to derive where a value has come from, and the structure of that location, if the data is compressed, etc., and accounting for all that would make the function far less efficient.


Oracle Nested Query with Group By and Having Clauses

If I understand correctly:

select *
  from (SELECT D.DID, AVG(F.DIST) as AvgDist
        from Destinations D
        join Flights F ON F.DID = D.DID
        GROUP BY D.DID) avgdist
  join
       (SELECT F.AID,F.DID, SUM(F.DIST) as fDist
        from Flights F
        join Destinations D ON F.DID = D.DID
        GROUP BY F.AID,F.DID) sumdist
        on avgdist.did = sumdist.did
  where avgdist.avgdist < sumdist.fDist;


How do I force a JOIN to use a specific index in MySQL?

Even though you are using FORCE INDEX the Query Optimizer took over as expected.

The pass through the lineitem table exceeded more than 5% of the total index entries. This is what lead to the Query Optimizer to dismiss using the index. Oracle, MSSQL and PostgreSQL would not have done any different.

Here is my sick attempt at refactoring the query

select sum(l_extendedprice* (1 - l_discount)) as revenue
from
(
    select p_partkey l_partkey,'S' contsize from part
    where p_brand = 'Brand#12'
    and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    and p_size between 1 and 5
    union
    select p_partkey,'M' contsize from part
    where p_brand = 'Brand#23'
    and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and p_size between 1 and 10
    union
    select p_partkey,'L' contsize from part
    where p_brand = 'Brand#34'
    and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    and p_size between 1 and 15
) partkeys
left join listitem using (l_partkey)
where
    l_shipmode in ('AIR', 'AIR REG') and
    l_shipinstruct = 'DELIVER IN PERSON' and
    IF(contsize='S',IF(l_quantity >= 1 and l_quantity <= 1 + 10,1,0),
        IF(contsize='M',IF(l_quantity >= 10 and l_quantity <= 10 + 10,1,0),
            IF(contsize='L',IF(l_quantity >= 20 and l_quantity <= 20 + 10,1,0),0)
        )
    )
;

You may need to index the tables

ALTER TABLE parts ADD INDEX brand_container_size_ndx
(p_brand,p_container,p_size,p_partkey);
ALTER TABLE listitem ADD INDEX partkey_shipinstruct_shipmode_quantity_ndx
(l_partkey,l_shipinstruct,l_shipmode ,l_quantity);

Give it a Try and let us know if it is fast or if it even works.


Migrating the Data is a Job in itself
Keeping Data in sync is a job is yet another job.
Creating a new front end is yet another job.

there's lots of ways to tackle the problem.

there's a lot that can go wrong with data migrations, so if you've never done one before unless the business is yelling for it I'd move very slowly and carefully.

1st I'd replicate the Schema and just do a simple import into sql.

From there build out what you want your new schema to look like and build your scripts for moving data around into your new schema, make sure you're logging every single insert update delete etc, it's easiest if you log the command that you ran to move it, and the Command to undo the operation in a central table.

then build your new front end against that and test it thoroughly.

then test your scripts for exporting from access and importing into your new schema, then you can automate the Export import process until it runs perfectly every time I'd probably do at least 100 or so executions to test the process before the move.

I wouldn't worry about keeping it in sync until you've got buy in from the business with your new process and application then I'd just set a time for the Switch.

If you have to have 2 simultaneous systems In place you've got a whole different set of problems.

Which one becomes the source of truth? do you need to be able to export everything back out to access at any given time.

Just about any change in scope will add additional time to the Project.

Hit me up if you have any other questions. I've done a couple around 50 or so of these small scale data migrations from flat files to sql, from access to sql, from sql to access, oracle to MongoDB, from SQL to Mongodb, mysql to sql, mongodb to SQL and they take a lot longer than you'd think if you want to do it right.


Are you looking for last_insert_id

INSERT INTO payments (date, amount) ;
SET @last_id_in_table1 = LAST_INSERT_ID();
 SELECT CURRENT_DATE date, amount
 FROM payments WHERE id = @last_id_in_table1;


What's the deal with transactions in a stored procedure called by an SSRS report?

On the data source of an SSRS report, there is a property for "Use single transaction when processing the queries". There are a couple different behaviors tied to marking this checkbox:

1) As you noticed, SSRS will use an explicit transaction to process the data sets using this data source, if this checkbox is marked. When it is not marked, there is no explicit transaction.

2) When this checkbox is not marked, data sets using the data source will execute in parallel, using separate connections to the server. If the checkbox is marked, then the data sets use a single connection and are processed serially.

Additionally, there may be different motivations for using this checkbox. The two major ones are:

1) Read consistency: If you are using Snapshot Isolation, you may want all data sets to be consistent to a single point in time. In this case, using a single explicit transaction and using the Snapshot isolation level can ensure all statements within the transaction are consistent to a single point in time.

2) Performance: Depending on a number of factors (which are really a separate topic), you may see dramatic performance difference between running the queries for different data sets in parallel vs running them serially.


Using column alias in a WHERE clause doesn't work

The manual clarifies here:

  An output column's name can be used to refer to the column's value in
  ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses;
  there you must write out the expression instead.

That's according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied.
Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless). See:

PostgreSQL: How to return rows with respect to a found row (relative results)?

Best to avoid column aliases that conflict with input column names a priori.

Aside: the subquery in your example is just noise since the WHERE clause is part of the outer query, so the example can be simplified to:

select id, email as electronic_mail
from users t
where electronic_mail = '';  -- doesn't work


Is there a penalty for using BINARY(16) instead of UNIQUEIDENTIFIER?

Should I be concerned?

Well, there are a couple of things here that are a little concerning.

First: while it is true that a UNIQUEIDENTIFIER (i.e. Guid) is a 16-byte binary value, it is also true that:

All data can be stored in binary form (e.g. INT could be stored in BINARY(4), DATETIME can be stored in BINARY(8), etc), hence #2 ↴
There is probably a reason for having a separate datatype for GUIDs outside of mere convenience (e.g. sysname as an alias for NVARCHAR(128)).

The three behavioral differences that I can find are:

Comparing UNIQUEIDENTIFIER values in SQL Server, for better or for worse, is not actually done the same way as comparing BINARY(16) values. According to the MSDN page for Comparing GUID and uniqueidentifier Values, when comparing UNIQUEIDENTIFIER values in SQL Server:

  the last six bytes of a value are most significant

While these values are not frequently sorted, there is a slight difference between these two types. According to the MSDN page for uniqueidentifier:

  ordering is not implemented by comparing the bit patterns of the two values.

Given that there are differences in how GUID values are handled between SQL Server and .NET (noted in the "Comparing GUID and uniqueidentifier Values" page linked above), pulling this data out of SQL Server into app code might not be dealt with properly in the app code if needing to emulate the SQL Server comparison behavior. That behavior can be emulated by converting to a SqlGuid, but would a developer know to do that?

Second: based on the following statement

  It does this for everything including primary keys.

I would be concerned in general for system performance by using GUIDs as PKs instead of as Alternate Keys along with using an INT or even BIGINT as the PK. And even more concerned if these GUID PKs are the Clustered Indexes.

UPDATE

The following comment, made by the O.P. on @Rob's answer, brings up an additional concern:

  it was migrated from I think MySQL

GUIDs can be stored in 2 different binary formats. So, there could be cause for concern depending on:

what system the binary representation was generated on, and
if the string values were used outside of the original system, such as in app code or given to clients to use in import files, etc.

The issue with where the binary representation was generated has to do with the byte ordering of the first 3 out of the 4 "fields". If you follow the link above to the Wikipedia article, you will see that RFC 4122 specifies to use "Big Endian" encoding for all 4 fields, yet Microsoft GUIDs specify using "Native" Endianness. Well, Intel architecture is Little Endian, hence the byte order for the first 3 fields is reversed from systems following the RFC (as well as Microsoft-style GUIDs generated on Big Endian systems). The first field, "Data 1", is 4 bytes. In one Endianness it would be represented as (hypothetically) 0x01020304. But in the other Endianness it would be 0x04030201. So if the current database's BINARY(16) field was populated from an import file using 0x01020304 binary notation and that binary representation was generated on a system following the RFC, then converting the data currently in the BINARY(16) field into a UNIQUEIDENTIFIER will result in a different GUID than what was originally created. This does not really pose a problem IF the values never left the database, and the values are only ever compared for equality and not ordering.

The concern with ordering is simply that they won't be in the same order after converting to UNIQUEIDENTIFIER. Fortunately, if the original system really was MySQL then ordering was never done on the binary representation in the first place since MySQL only has a string representation of UUID.

The concern with the string values being used outside of the database is more serious, again, if the binary representation was generated outside of Windows / SQL Server. Since the byte ordering is potentially different, then the same GUID in string form would result in 2 different binary representations, depending on where that conversion took place. If app code or customers were given a GUID in string form as ABC coming from a binary form of 123 and the binary representation was generated on a system following the RFC, then that same binary representation (i.e. 123) would translate to a string form of DEF when converted to a UNIQUEIDENTIFIER. Likewise, the original string form of ABC would convert to a binary form of 456 when converted to a UNIQUEIDENTIFIER.

So, if the GUIDs never left the database then there isn't much to be concerned about outside of ordering. Or, if the import from MySQL was done by converting the string form (i.e. FCCEC3D8-22A0-4C8A-BF35-EC18227C9F40) then it might be ok. Else, if those GUIDs were given to customers or in the app code, you can test to see how they convert by getting one and converting via SELECT CONVERT(UNIQUEIDENTIFIER, 'value found outside of the database'); and see if you find the expected record. If you can't match records then you might have to keep the fields as BINARY(16).

In all likelihood there won't be an issue, but I am mentioning this because under the right conditions there could be an issue.

And how do new GUIDs get inserted anyway? Generated in the app code?

UPDATE 2

If the previous explanation of the potential issue related to importing binary representations of GUID generated on another system was a little (or a lot) confusing, hopefully the following will be a little clearer:

DECLARE @GUID UNIQUEIDENTIFIER = NEWID();
SELECT @GUID AS [String], CONVERT(BINARY(16), @GUID) AS [Binary];
-- String = 5FED23BE-E52C-40EE-8F45-49664C9472FD
-- Binary = 0xBE23ED5F2CE5EE408F4549664C9472FD
--          BE23ED5F-2CE5-EE40-8F45-49664C9472FD

In the output shown above, the "String" and "Binary" values are from the same GUID. The value beneath the "Binary" line is the same value as the "Binary" line, but formatted in the same style as the "String" line (i.e. removed "0x" and added the four dashes). Comparing the first and third values, they aren't exactly the same, but they are very close: the right-most two sections are identical, but the left-most three sections are not. But if you look closely, you can see that it is the same bytes in each of the three sections, just in a different order. It might be easier to see if I show only those first three sections, and number the bytes so it is easier to see how their order differs between the two representations:

String  =  15F2ED3234BE – 5E562C – 7408EE
Binary = 4BE3232ED15F – 62C5E5 – 8EE740 (in Windows / SQL Server)

So within each grouping, the ordering of the bytes is reversed, but only within Windows and also SQL Server. However, on a system that adheres to the RFC, the binary representation would mirror the sting representation because there would not be any reversal of the byte order.

How was the data brought into SQL Server from MySQL? Here are a few choices:

SELECT CONVERT(BINARY(16), '5FED23BE-E52C-40EE-8F45-49664C9472FD'),
       CONVERT(BINARY(16), 0x5FED23BEE52C40EE8F4549664C9472FD),
    CONVERT(BINARY(16), CONVERT(UNIQUEIDENTIFIER, '5FED23BE-E52C-40EE-8F45-49664C9472FD'));

Returns:

0x35464544323342452D453532432D3430
0x5FED23BEE52C40EE8F4549664C9472FD
0xBE23ED5F2CE5EE408F4549664C9472FD

Assuming it was straight binary-to-binary (i.e. Convert #2 above), then the resulting GUID, if converted to an actual UNIQUEIDENTIFIER, would be:

SELECT CONVERT(UNIQUEIDENTIFIER, 0x5FED23BEE52C40EE8F4549664C9472FD);

Returns:

BE23ED5F-2CE5-EE40-8F45-49664C9472FD

Which is wrong. And that leaves us with three questions:

How was the data imported into SQL Server?
What language is the app code written in?
What platform is the app code running on?


How can I create postgres users for employees that have limited access?

ALTER DEFAULT PRIVILEGES
      FOR ROLE james
      IN SCHEMA public
      GRANT SELECT ON TABLES TO james;

This grants select on tables created by 'james', to 'james'.  Which is generally unnecessary, as the creator will usually be owner and will automatically have access.  You need to change first occurrence of 'james' to whoever it is that will be creating the new tables.

Also, you should probably change the last occurrence of 'james' to something like 'readonly' or 'developer', and than grant that role to 'james', rather than naming 'james' explicitly.


The system catalog for types, pg_catalog.pg_type, has the following column:

typtypmod integer not null

as you can see by running \d pg_type.

There is no way to store something you can't fit into an integer because that's how the catalog stores typmods.

The non-negative requirement appears to stem from the use of -1 as reserved value.

The specific problem you describe sounds better suited to the addition of a column trigger. It sounds like what you really want is DOMAINs and the ability to add triggers on domains - but unfortunately at this point there is no support for triggers on domains.


Much slower SQL application performance after upgrading from Windows Server 2008 R2 to Windows Server 2012 R2

Originally self-answered in comments:

After doing a lot of testing, this issue appears to occur when crossing the temporary network bridge from our new environment over to our old environment. Although we haven't figured out the root cause at the network layer, we have decided to leave the centralized server on our old environment and move it last during our infrastructure migration.

We kept seeing a lot of ASYNC_NETWORK_IO wait types with the queries. After doing a lot of testing with different configurations and opening tickets with Cisco and vmWare, vmWare asked us to try to disable Receive Side Coalescing (RSC) on hosts.

After making that change, performance was back to normal (actually better on the new equipment, which is what we expected). See the VMware Knowledge Base entry:

After upgrading a virtual machine to hardware version 11, network dependent workloads experience performance degradation (2129176).


SQL Server Merge Replication Computed Column Conflict Resolution

The problem is that replicate dcolumn drop from replicating. Then replicatinon of object (views, store procedures and function) fail in creating subscription. Then this column should e added manualy to table. Then after part wwich create object you should remove calculated column becaus ebcp fail with field to big error. Then after removal bcp pass.
But you don't have calculate column on subscriber. But now you can do that only through publisher. Then you should drop calculate column (but this is problem if you have FK otr index on it because you shoul drop them to). Then recreate calculated column (and recreate FK and index (index manualy on subscribers too). This is not normal.
Calculated column (persisted) should be replicated by definition (SQL staements) always in creation of snapshot and only not field with bcp and realy replicated data.


Duplicate tables and procedures in master database

Did you create the table with a script run through SSMS?

Is it possible that if you did you didn't include USE [SomeDB] at the top of the script and instead had the current DB set to master?

So doing this:

Instead of this:

You can see when a specific table was created by looking at the system views, as in:

SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , o.create_date
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type_desc = 'USER_TABLE';

This may help you understand where/when those tables were created.

Results from the query:

If you really need to prove where these are coming from, you probably want to create a DDL trigger at the server level to capture events such as CREATE TABLE etc, however that is beyond the scope of this answer.  A Server-level DDL Trigger would allow you to capture all data-definition events for future events; i.e. it wouldn't help solve where these existing tables came from.


I think that your type key is the main problem for you ! as you have it set to the max index size in mysql !
 Try to see the real need of those 255(767 byte index) that is big for a table with that many rows.
 And check the distinct values in this column 'type' maybe a partition can be used in order to get rid of unnecessary reads.


When you use mysqldump --single-transaction, you are not supposed to break the transaction.

I wrote a post 1.5 years ago discussing the SQL that can break transaction : Transactional DDL workflow for MySQL

In the future, never run DDL during a mysqldump because it triggers an implicit commit.


To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question

From Tibor Karaszi's Blog:

So what is the problem? Why shouldn't I shrink database files on a regular basis?
Have a look at below list and then you can determine for yourself whether or not you want to shrink database files regularly:

Each page moved will be logged to the transaction log.
Say you have a database with 50GB used space (data and indexes pages), and the shrink will shuffle 40 GB towards the beginning of the file. The log file will for this shrink operation need 40GB, likely auto-grow to that size (if you don't have 40GB free space in the log file already). The following log backup wll be 40GB in size, plus the "regular" log records. This doesn't seem to happen if the database is in simple recovery mode, possibly beacuse CHECKPOINT will prune the log regurarly during the shrink.
  (Applies to shrinking of data files.)
After the shrink, as users add rows etc in the database, the file has to grow again.
  Growing a database file is an expensive operation, it takes time and it also hurts performance (lots of resource usage). Also, some modifications will be blocked until the grow operation has finished.
  (Applies to shrinking of both data and log files.)
SQL Server 2005 and later:
As of SQL Server 2005, we have "instant file initialization" which means that database files can be created and also grow very fast since Windows doesn't "zero out" the data in the database file. Instant file initialization is only available for data files, not log files. Also, instance file initialization requires that the service account for the SQL Server service has the SE_MANAGE_VOLUME_NAME windows privilige, which can be assigned using the Perform Volume Maintenance Tasks security policy. This is by default only granted to Administrators.
There are situations where autogrow doesn't "catch up" with the space usage requirements.
This will result in an error message from SQL Server when the modification is performed, returned to the client application: error 1105 if data is full and 9002 if log is full.
  (Applies to shrinking of both data and log files.)
Moving datapages around will fragment your database.
  Say you rebuild your indexes (which will requires free space in the database), and then shrink the database. The shrink will essentially undo the index rebuild, leaving you with fragmented indexes. Don't believe me? This is easy to test for yourself.
What if you do it the other way around, shrink first, then rebuild? Well, the rebuld need free space in the database for the largest index that you rebuild, and it is likely you have a large table with a clustered index. A friend of mine had a 4GB used space db, where almost all space was one 4GB table. He did a shrink and then rebuild, where the re-build immediately "bumped up" the db size to 8GB.
  (Applies to shrinking of data files.)
Heavy shrinking and growing of database files will fragment your file system, which will further hurt performance.
  (Applies to shrinking of both data and log files.)
Repeatedly srinking and subsequent growing of transaction log files will typically result in many virtual log files which can cause long startup time for the database. This can manifest itself as long startup time for a database, long restore time, delays for transaction replication etc. Check out this blog post for more information.