You can significantly lower the amount of downtime by running both current and new versions in parallel. Use something like Log Shipping, Always On Availability Groups or even replication to generate a copy of your database on a new instance that is running whatever newer version you want to upgrade to. I'd go with Log Shipping as it's the simplest, technically, and is available on any edition of SQL Server. Always On AG gets you closest in sync but take a bit more work to setup. Repl is kinda in between.

It may take a while for both databases to be "almost" in sync depending on how large the database is and your infrastructure but that is not as important since your system is online the whole time. Once you get to a steady state where all existing data from the original DB has moved to the new DB and you're left with only new transactions to move, then stop transactions on the original, let all data get moved to the new DB. Actual method/command depends on what approach you take but all are well documented on MSDN.

Verify all pending transactions have been pushed to the new DB then make the necessary changes so applications point to the new DB. Finally, run validation tests against the new DB to make sure everything works as it's supposed to (even if you've tested in your pre-prod/QA environment) and re-enable applications/transactions if all tested clear.

This puts the bulk of your downtime on whatever tests you need to run and you really should not skip that part. Cutting over from Log Shipping, AlwaysOn AG or replication is a matter of minutes, assuming no server, storage or network bottlenecks.


difference between index REBUILD and REORGANIZE in relation to recompile in SQL Server 2008 R2

Index reorganize does not touches statistics so there is no chance for causing recompilation. Since when index is rebuilt with full scan stats are also updated for the column this can trigger recompilation as statistics change.


This is not what triggers do -- they act on an event, such as a change to data.

Use DBMS_Scheduler to set up a repeating event.

Consider applying a function-based index to include only those rows that need updating, and make sure the query is constructed to be able to use it.


How can an identity primary key index become fragmented?

Updates on data already there causes rows to be moved and forward pointers added

In this test, we can get 65% fragmentation on 115k densely packed rows

CREATE TABLE #FragTest (
    FragTestID int NOT NULL IDENTITY PRIMARY KEY,
    SomeString varchar(4100) NULL
    );

INSERT #FragTest (SomeString) VALUES ('a');
GO
INSERT #FragTest (SomeString)
    SELECT F1.SomeString FROM #FragTest F1 CROSS JOIN #FragTest F2;
GO 4
INSERT #FragTest (SomeString) SELECT F1.SomeString FROM #FragTest F1
GO 6

SELECT COUNT(*) FROM #FragTest

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

UPDATE #FragTest
SET SomeString = REPLICATE('b', 4100)
WHERE FragTestID < 10000 AND FragTestID % 3 = 0

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

UPDATE #FragTest
SET SomeString = REPLICATE('c', 4100)
WHERE FragTestID < 10000 AND FragTestID % 3 = 1

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

UPDATE #FragTest
SET SomeString = REPLICATE('d', 4100)
WHERE FragTestID < 10000 AND FragTestID % 3 = 2

SELECT object_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(2, OBJECT_ID('tempdb..#FragTest'), NULL, NULL, NULL);

Edit, sorry, browser trouble

If you change the middle replicate to 2000 you get <5% at the end. This happens because there is free space from moved rows in the first update


Cannot drop nonexistent constraint and cannot create it either

At a guess I'd say Marian is right and this is caused by a unique index and constraint having the same name, eg:

create table t( k1 integer, k2 integer,
                constraint u1 unique(k1,k2) using index(create unique index u1 on t(k1,k2)),
                constraint u2 unique(k2,k1) using index u1);

select count(*) from user_indexes where index_name='U1';

COUNT(*)
----------------------
1

alter table t drop constraint u1;

select count(*) from user_indexes where index_name='U1';

COUNT(*)
----------------------
1

Normally when you add a unique constraint, a unique index with the same name is created - but the index and constraint are not the same thing. Have a look at all_indexes to see if there is an index called A_DUP_CALLE_UK1 and try and figure out if it is used by something else before you drop it!


user security inheritance in SQL Server 2016

Does SQL Server 2016 allow inheritance of security, so you don't have
  to build those users in each database, and apply the same security in
  each? Some of these schemas will just have views in which the
  definitions that read schemas and tables in other databases.

What you are asking about is Ownership Chains.

In the simple case when we are within one database, when you give permissions on a view and the view is owned by the same principal that owns all the underlying tables, you just do nothing: a user will access the view without any permission on the underying tables.

In your case there are more than one database, and we are talking about
Cross-Database Ownership Chaining

You should read this paragraph in the article above to understand Potential Threats in case youenable crossdb ownership chains.

You can read more on it here: Enabling Cross-Database Access in SQL Server.

In brief, to make it work, in addition to the requirement to have the same owner you should enable both database to db chainig: ALTER DATABASE ... SET DB_CHAINING ON.

In this case you still can grant permissions only on your views without granting them on other database's tables, but you still should map your users in all databases of interest. Otherwise when accessing tables from other databases through view ownership chain breaks when you are leaving the database and you should give the permissions to all the underlying tables.


Inserting 500K records into SQL Server 2008 is taking 15s; how to optimize?

It isn't just doing one insert operation per row, there is one insert per row per index with the associated sorts too. Each of those sorts may be spooling to disk (and probably is with that much data) to there is a lot of IO going on.

When completely rebuilding a table's contents (i.e. starting with a blank table) it is usually more efficient to drop or disabling all the indexes except the clustered index (if the table has one) before inserting the rows then recreating the indexes afterwards is often more efficient.

Update

If you can't remove the indexes because they may be used, then do some analysis as @ypercube implies to ensure that they are indeed required and useful and remove any that are of doubtable benefit. You may of course be able to temporarily drop some of the indexes if not all. Experiment to see if you would benefit from a clustered index (rules of thumb say no in this example, but rules of thumb do not always apply so running your own application specific benchmarks is often a good idea).

Having said that, if you are only doing a partial update in real life the issue is moot: if you are only inserting enough data to increase the table by 1% in size then dropping the indexes would never be more efficient anyway. Also it would be worth performing your benchmarks on realistically sized data rather than an empty table if the table in real life is not going to be empty.

If you have ruled out index changes, or done as much as you can in that direction, the only thing that you can do from there is to improve the IO performance. As we know nothing of your IO subsystem we can't help you much at the moment, but a few general tips include keeping tempdb on different drives to your main database, for some IO patterns keeping logs on different spindles can help too, or look to spreading the indexes between filegroups on different drive, and so forth. If you have enterprise edition so can use partitioning and your insert blocks map to a nice partitioning scheme, you could use partition switching to do the inserts faster then switch the whole block in in one do. If your data stores are SAN based, try moving tempdb to local drives. If you have control to change the hardware perhaps have some/all structures on SSDs. Adding more ram may help but probably not: you'd have to add enough that all those index sort operations could all happen in RAM all at the same time and SQL Server might still spool to disk (because it considered the RAM better used for something else) and even if you can arrange for the whole sorting operation to be done in RAM the data still has to be written to physical media at the end, there is no avoiding that.

There are a lot of articles (and whole books) out there on the subject of optimising your database IO system unfortunately. If you list your current disk technolog{y|ies} and layout someone might be able to make suggestions for practical things to try first. No matter what you try, remember to run realistic application benchmarks: artificial benchmarks are useful as a quick and easy gauge of what to expect, but sometimes a complexity of your real workload will mean that a massive benefit seen in artificial benchmarks in dev/tes is all but destroyed in production.


High write latency on tempdb datafiles only

I seem to have solved it or at least know where it is coming from.

To find out I setup perfmon to monitor avg. ms per write operation and it looks like this. which leads me to the conclusion that those extreme spikes only happen during nightly index rebuilding (I'm using Ola Hallengrens IndexMaintenance-solution for this), normal operation is fine.

My learning: don't rely on cumulated stats in sys.dm_io_virtual_file_stats only, but they are a good indicator to look further for the origin.


Returning results from many functions / statements

Unfortunately, as you already noticed, pgAdminIII shows only the latest result set.  A logical solution could be transforming your 10-50 result sets into one.   This can be achieved using UNION ALL:

SELECT * FROM importsitechanges(33, ...)
    UNION ALL
SELECT * FROM importsitechanges(22, ...)
    UNION ALL
...


Recursively call STUFF for results in a table valued function


SSIS - Setup OLEDB Connection to SQL Server Always On Listener

In short, the OLEDB v11 doesn't support the MultiSubnetFailover parameter.  Check out this helpful article.  You can instead set the ConnectionTimeout to 30 seconds, which seems to let it failover to the other node, but then every OLEDB step in your package could take 30 seconds to fire up.


How to make use of Batch Mode with an UNPIVOT (a loop join)?

One approach might be to use a #temp table for the values and also introduce a dummy equijoin column to allow for a hash join. For example:

-- Create a #temp table with a dummy column to match the hash join
-- and the actual column you want
CREATE TABLE #values (dummy INT NOT NULL, Col0 CHAR(1) NOT NULL)
INSERT INTO #values (dummy, Col0)
VALUES (0, 'A'),
        (0, 'B'),
        (0, 'C')
GO

-- A similar query, but with a dummy equijoin condition to allow for a hash join
SELECT v.Col0,
    CASE v.Col0
        WHEN 'A' THEN cs.DataA
        WHEN 'B' THEN cs.DataB
        WHEN 'C' THEN cs.DataC
        END AS Col1
FROM ColumnstoreTable cs
JOIN #values v
    -- Join your dummy column to any numeric column on the columnstore,
    -- multiplying that column by 0 to ensure a match to all #values
    ON v.dummy = cs.DataA * 0

Performance and query plan

This approach yields a query plan like the following, and the hash match is performed in batch mode:

If I replace the SELECT statement with a SUM of the CASE statement in order to avoid having to stream all those rows to the console and then run the query on a real 100MM row columnstore table I have lying around, I see fairly good performance to generate the requisite 300MM rows:

CPU time = 33803 ms,  elapsed time = 4363 ms.

And the actual plan shows good parallelization of the hash join.

Notes about hash join parallelization when all rows have same value

The performance of this query depends heavily on each thread on the probe side of the join having access to the full hash table (as opposed to a hash partitioned version, which would map all rows to a single thread given that there is only one distinct value for the dummy column).

Fortunately, this is true in this case (as we can see by the lack of a Parallelism operator on the probe side) and should reliably be true because batch mode builds a single hash table that is shared across threads. Therefore, each thread can take their rows from the Columnstore Index Scan and match them to that shared hash table. In SQL Server 2012, this functionality was much less predictable because a spill caused the operator to restart in Row mode, both losing the benefit of batch mode and also requiring a Repartition Streams operator on the probe side of the join which would cause thread skew in this case. Allowing spills to remain in batch mode is a major improvement in SQL Server 2014.

To my knowledge, row mode does not have this shared hash table capability. However, in some cases, typically with an estimate of fewer than 100 rows on the build side, SQL Server will create a separate copy of the hash table for each thread (identifiable by the Distribute Streams leading into the hash join). This can be very powerful, but is much less reliable than batch mode since it depends on your cardinality estimates and SQL Server is trying to evaluate the benefits versus the cost of building a full copy of the hash table for each thread.

UNION ALL: a simpler alternative

Paul White pointed out that another, and potentially simpler, option would be to use UNION ALL to combine the rows for each value. This is likely your best bet assuming that it's easy for you to build up this SQL dynamically. For example:

SELECT 'A' AS Col0, c.DataA AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'B' AS Col0, c.DataB AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'C' AS Col0, c.DataC AS Col1
FROM ColumnstoreTable c

This also yields a plan that is able to utilize batch mode and provides even better performance than the original answer. (Although in both cases the performance is fast enough that any selecting or writing the data to a table quickly becomes the bottlneck.) The UNION ALL approach also avoids playing games like multiplying by 0. Sometimes it's best to think simple!

CPU time = 8673 ms,  elapsed time = 4270 ms.


Confused about the "Maximum Failures in the Specified Period" setting on SQL Server Cluster

I would expect that forcing failover (either by moving the services manually or rebooting the owner node) more than twice would cause the resources to stay 'Offline', but this isn't the case.

Correct, that isn't the case. Moving the services manually isn't a failure - you've told it to go ahead and change the owner, this isn't a failure. Rebooting also isn't viewed as a failure as long as it was controlled - for example, I run the shutdown -r command or click start->restart. You're telling the server,  "Hey, have a nice controlled reboot - no worries."

  What is the purpose of that property and how do I simulate things correctly to test behaviour?

AFAIK the purpose of the property is so you don't ping-pong around servers indefinitely, potentially causing more issues. If you exhaust the number of retries that implicitly assumes that each node was attempted to host the role without success - so why keep flopping around causing more logs to be spewed for no reason?

To simulate - have an actual failure... force shutdown (not cleanly) a machine (blue screen it on purpose - something like NotMyFault). Rip the disks out from under it, something to cause an ACTUAL failure.


Suddenly have to rebuild indexes to prevent site from going down

The problem is not really index fragmentation. It is more associated with the index statistics. If you are using MyISAM, sudden spikes of INSERTs would throw the statistics off in the eyes of the MySQL Query Optimizer. This would cause the MySQL Query Optimizer to take horrific guesses within the EXPLAIN plans of SELECT queries.

If you are using InnoDB, ANALYZE TABLE becomes completely useless.

As long as the table is reasonably small, ANALYZE TABLE is all you can really do for MyISAM. Rebuilding indexes may help InnoDB tables periodically.

As long as tables are not heavily written, index statistics will remain stable and query EXPLAIN plans will remain more consistent.

Just remember: once you have tons of INSERTs, UPDATEs, and DELETEs, all bets are off for trustworthy index statistics until the next rebuild or ANALYZE TABLE.


Use CREATE DOMAIN with a CHECK constraint. This works in PostgreSQL 9.1. It's documented to work in at least 8.0+. "A partial workaround is to use domain types as members of composite types."

create domain angle as float check (value between -90 and 90);

create type axis as (
    major_axis float,
    minor_axis float,
    angle angle
);

create table sample(
    axis1 axis,
    axis2 axis
);

This INSERT statement should succeed.

insert into sample values
(row(0, 0, 35), row(0, 0, 35));

But this one should fail.

insert into sample values
(row(0, 0, 93), row(0, 0, 35));
ERROR: value for domain angle violates check constraint "angle_check"
SQL state: 23514


As the official documentation says, unless you specify failure groups explicitly,
ASM will place each disk into its own failure group
(it doesn't have any knowledge about your HW infrastructure).

Note that Oracle doesn't recommend using an OS LVM with ASM.


How do I enable md5 authentication for only one user?

pg_hba.conf looks only for the first match. Try switching the order..

local   all             scale              md5
local   all             all                ident

You'll have to reload the conf

SELECT pg_reload_conf();

Or, using something like

service postresql reload


It is unlikely that the cursor you are using is the cause of the delay in returning results.  Most likely, the delay is due to running many select statements over possibly a large amount of data.  Having said that, I likely wouldn't use the DECLARE CURSOR cur FOR ... construct to solve this issue.

I generally approach this class of problem by generating a single dynamic SQL statement, then execute that statement using either sp_executesql() or EXEC (), as in:

IF COALESCE(OBJECT_ID('dbo.TableList'), 0) <> 0
DROP TABLE dbo.TableList;

CREATE TABLE dbo.TableList
(
    DatabaseName SYSNAME NOT NULL
    , SchemaName SYSNAME NOT NULL
    , TableName SYSNAME NOT NULL
);

INSERT INTO dbo.TableList(DatabaseName, SchemaName, TableName)
VALUES ('Database1', 'Schema1', 'Table1')
    ,  ('Database2', 'Schema2', 'Table2');

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = '';

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END
    + 'SELECT ''' + tl.DatabaseName
        + '.' + tl.SchemaName
        + '.' + tl.TableName
        + ''', * '
    + 'FROM ' + QUOTENAME(tl.DatabaseName)
        + '.' + QUOTENAME(tl.SchemaName)
        + '.' + QUOTENAME(tl.TableName) + ';'
FROM dbo.TableList tl;

PRINT (@cmd);
EXEC sp_executesql @cmd;

This constructs statements required from the TableList table, appending each statement into a variable.  The variable contents are then executed using the sp_executesql system stored procedure.  This methodology can be used for a multitude of problems where you need to generate T-SQL statements based on the results of some query.

The output will be a single result set for each row in TableList - you probably don't want to do this for more than about 50 or 100 tables since the output might become unmanageable.  If you have a lot of tables, you may want to consider inserting the output into a temporary table, then running a single select statement against that table.

If the structure of the tables listed in the TableName column are identical, you can use a UNION ALL to join the output into a single resultset:

SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10)
    + 'UNION ALL' + CHAR(13) + CHAR(10) END
    + 'SELECT ''' + tl.DatabaseName
        + '.' + tl.SchemaName
        + '.' + tl.TableName
        + ''', * '
    + 'FROM ' + QUOTENAME(tl.DatabaseName)
        + '.' + QUOTENAME(tl.SchemaName)
        + '.' + QUOTENAME(tl.TableName) + ';'
FROM dbo.TableList tl;

Be aware that UNION ALL is generally much faster than UNION since the ALL option doesn't need to run a DISTINCT operator over the data.


Does this normalized model for artist music records make sense?

Here's an example of similar Data Model: http://www.databaseanswers.org/data_models/cd_collection/index.htm

This model has a lot of clarity than what you have describe. Take a look.


My suspicion is that the table in question uses a feature that is only available in SQL Server 2005 and above and so that is why you cannot see it in your 2000 instance. This is similar to what happens when you try to open a SQL 2005 or above instance from Enterprise Manager - except you get a more descriptive error. If you desperately need to see this table in SQL 2000 remove the offending feature (if at all possible) from the table (it might just be a data type), if you cannot remove the feature then have a think about whether it is possible to have the link the opposite way around?

I hope this helps you.


Comparing Columns that can contain NULLS - is there a more elegant way?

You can use this approach from Paul White's article Undocumented Query Plans: Equality Comparisons

SELECT *
FROM   A
       INNER JOIN B
         ON A.ID = B.ID
            AND EXISTS(SELECT A.*
                       EXCEPT
                       SELECT B.*)


All those 4 procedures require the sysadmin fixed server role or CONTROL SERVER permission. What are the pros and cons of granting this level of permission to a user that's not a DBA? This is like opening Pandora's box :-).


How to Reorganize Microsoft Access Table

I think your best bet is to do the restructuring in Access. After you're confident everything works, you can

create tables and constraints in PostgreSQL,
export tables from Access as CSV files, and
load the CSV files into PostgreSQL.

Everything works includes application code.

Make sure your Access database is backed up, copied, and not in use before you make structural changes.

To restructure tables in Access,

select distinct rows into a new table ("make table" query), or
create views (saved queries) that select the right rows and columns, or
copy a table, open it in design view, and delete irrelevant columns. (You'll probably want to delete some columns from the original table, too.)

I'm pretty sure Access lets you export the results of a query, so you can build queries that have the right structure, then export the queries.


Yes:

ALTER TABLE the_table ENGINE=MEMORY;

That said, I must strongly advice against doing this because the contents of memory tables are lost once the MariaDB server is shut down.

InnoDB is perfect for having some rows in memory and some on disk: It will put as much into memory as it can, and if there isn't enough memory for all of them, then it will only keep those that are accessed the most frequently. Also, it will persist the data to disk so that it's not lost when the MariaDB server is shut down.


Application with "configurable" columns : how to model this

What you're thinking about is either "row modelling" or "entity-attribute-value" (EAV) - See Wikipedia - either of which is a common subject for religious wars. I won't get too far into the pros vs. cons. If you read up on EAV and row modeling you'll see lots of discussion on that.

What I will say is that you should model all of the common predicates using properly normalized tables and rely on EAV (or row modelling) only for the customizable columns (if at all).  This is how many highly customizable packaged solutions deal with allowing users to add their own attributes to existing objects.  It isn't clean, easy or efficient, but it is probably the best compromise for allowing this particular type of customization when you have no way of reasonably predicting what the customized columns might be at design time.


How to Traverse All Foreign Keys to Generate All Possible Paths

I would try one or both of the following:

You've already added a Hops counter column in the anchor. In the recursive part of the CTE, you can filter WHERE cte2.Hops<20
In the first cte common table expression, I would add WHERE fk.parent_object_id!=fk.referenced_object_id to exclude infinite self-referencing foreign keys (like parent-child hierarchies).


The main performance differences

The main differences here are that the better performing query is pushing down the seek predicate on CodeMasterID on all 4 tables (2 temporal tables (actual & history)) where the select on the view appears to not do that until the end (filter operator).

TL DR;

The issue is due to to parameters not pushing down to window functions in certain cases such as views. The easiest solution is adding OPTION(RECOMPILE) to the view call as to make the optimizer 'see' the params at runtime if that is a possibility. If it is too expensive to recompile the execution plan for each query call, using an inline table valued function that expects a parameter could be a solution. There is an excellent Blogpost by Paul White on this. For a more detailed way on finding and resolving your particular issue, keep on reading.

The better performing query

Codemaster table

Deal table

I love the smell of seek predicates in the morning

The big bad query

Codemaster table

This is a predicate only zone

The Deal table

But the optimizer did not read 'The art of the deal™"

...and does not learn from the past

Until all that data reaches the filter operator

So, what gives?

The main problem here is the optimizer not 'seeing' the parameters at runtime due to the window functions in the view and not being able to use the SelOnSeqPrj (select on sequence project, further down in this post for reference).

I was able to replicate the same results with a test sample and using SP_EXECUTESQL to parameterize the call to the view. See addendum for the DDL / DML

executing a query against a test view with a window function and an INNER JOIN

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155;

Resulting in about 4.5s of cpu time and 3.2s elapsed time

 SQL Server Execution Times:
   CPU time = 4595 ms,  elapsed time = 3209 ms.

When we add the sweet embrace of OPTION(RECOMPILE)

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1 OPTION(RECOMPILE)',N'@P1 INT',@P1 = 37155;

It is all good.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 98 ms.

Why

This all again supports the point of not being able to apply the @P1 predicate to the tables because of the window function & parameterization resulting in the filter operator

Not only an issue for temporal tables

See addendum 2

Even when not using temporal tables, this happens:

The same result is seen when writing the query like this:

DECLARE @P1 int = 37155
SELECT * FROM  dbo.Bad2
Where CodeMasterID = @P1;

Again, the optimizer is not pushing down the predicate before applying the window function.

When omitting the ROW_NUMBER()

CREATE VIEW dbo.Bad3
as
SELECT
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID;

All is well

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad3
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 33 ms.

so where does all that leave us?

The ROW_NUMBER() is calculated before the filter is applied on the bad queries.

And all this leads us to this blogpost from 2013 by Paul White
on window functions and views.

One of the important parts for our example is this statement:

  Unfortunately, the SelOnSeqPrj simplification rule only works when the
  predicate performs a comparison with a constant. For that reason, the
  following query produces the sub-optimal plan on SQL Server 2008 and
  later:

DECLARE @ProductID INT = 878;

SELECT
    mrt.ProductID,
    mrt.TransactionID,
    mrt.ReferenceOrderID,
    mrt.TransactionDate,
    mrt.Quantity
FROM dbo.MostRecentTransactionsPerProduct AS mrt
WHERE
    mrt.ProductID = @ProductID;

This part corresponds to what we have seen when declaring the parameter ourselves / using SP_EXECUTESQL on the view.

The actual solutions

1: OPTION(RECOMPILE)

We know that OPTION(RECOMPILE) to 'see' the value at runtime is a possibility. When recompiling the execution plan for each query call is too expensive, there are other solutions.

2: Inline table valued function with a parameter

CREATE FUNCTION dbo.BlaBla
(
    @P1 INT
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
    (
     SELECT
     ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID) AS Deal_HistoryID,
     cm.CodeMasterID,CM.ManagerID,
     cm.ParentDeptID,d.DealID,
     d.CodeMasterID as dealcodemaster,
     d.EvenMoreBlaID
    FROM dbo.CodeMaster2  cm
    INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID
    Where cm.CodeMasterID = @P1
    )
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.BlaBLa(@P1)',N'@P1 INT',@P1 = 37155

Resulting in the expected seek predicates

     SQL Server Execution Times:
       CPU time = 0 ms,  elapsed time = 0 ms.

With about 9 logical reads on my test

3: Writing the query without the use of a view.

The other 'solution' could be writing the query entirely without the use of a view.

4: Not keeping the ROW_NUMBER() function in the view, instead specifying it in the call to the view.

An example of this would be:

CREATE VIEW dbo.Bad2
as
SELECT
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID;

GO
SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT ROW_NUMBER() OVER (PARTITION BY CodeMasterID ORDER BY CodeMasterID) AS Deal_HistoryID,* FROM  dbo.Bad2
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155;

There should be other creative ways around this issue, the important part is knowing what causes it.

Addendum #1

CREATE TABLE dbo.Codemaster
(
     CodeMasterID int NOT NULL PRIMARY KEY CLUSTERED
   , ManagerID INT  NULL
   , ParentDeptID int NULL
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Codemaster_History))
;

CREATE TABLE dbo.Deal
(
     DealID int NOT NULL PRIMARY KEY CLUSTERED
   , CodeMasterID INT  NULL
   , EvenMoreBlaID int NULL
   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Deal_History))
;

INSERT INTO dbo.Codemaster(CodeMasterID,ManagerID,ParentDeptID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

INSERT INTO dbo.Deal(DealID,CodeMasterID,EvenMoreBlaID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

CREATE INDEX IX_CodeMasterID
ON dbo.Deal(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Deal_History(CodeMasterId);

CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster_History(CodeMasterId);

SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.SysStartTime) AS Deal_HistoryID,
cm.*, d.*
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID
Where cm.CodeMasterID = 37155;

-- Guud
GO
CREATE VIEW dbo.Bad
as
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID, cm.SysStartTime) AS Deal_HistoryID,
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster FOR SYSTEM_TIME ALL cm
INNER JOIN dbo.Deal FOR SYSTEM_TIME ALL d ON cm.CodeMasterID = d.CodeMasterID

GO
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155

-- Very bad shame on you

Addendum #2

CREATE TABLE dbo.Codemaster2
(
     CodeMasterID int NOT NULL PRIMARY KEY CLUSTERED
   , ManagerID INT  NULL
   , ParentDeptID int NULL

);

CREATE TABLE dbo.Deal2
(
     DealID int NOT NULL PRIMARY KEY CLUSTERED
   , CodeMasterID INT  NULL
   , EvenMoreBlaID int NULL
);

INSERT INTO dbo.Codemaster2(CodeMasterID,ManagerID,ParentDeptID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

INSERT INTO dbo.Deal2(DealID,CodeMasterID,EvenMoreBlaID)
SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum1,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum2,
                    ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) as rownum3
FROM MASTER..spt_values as spt1
CROSS JOIN MASTER..spt_values as spt2;

CREATE INDEX IX_CodeMasterID
ON dbo.Deal2(CodeMasterId);
CREATE INDEX IX_CodeMasterID
ON dbo.Codemaster2(CodeMasterId);

SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterId) AS Deal_HistoryID,
cm.*, d.*
FROM dbo.CodeMaster2 cm
INNER JOIN dbo.Deal2 d ON cm.CodeMasterID = d.CodeMasterID
Where cm.CodeMasterID = 37155;

-- Guud
GO
CREATE VIEW dbo.Bad2
as
SELECT ROW_NUMBER() OVER (PARTITION BY cm.CodeMasterID ORDER BY cm.CodeMasterID) AS Deal_HistoryID,
cm.CodeMasterID,CM.ManagerID,cm.ParentDeptID,d.DealID, d.CodeMasterID as dealcodemaster,d.EvenMoreBlaID
FROM dbo.CodeMaster2  cm
INNER JOIN dbo.Deal2  d ON cm.CodeMasterID = d.CodeMasterID

GO
SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL
N'SELECT * FROM  dbo.Bad2
Where CodeMasterID = @P1',N'@P1 INT',@P1 = 37155


if you want to select unique record from other join table which has same FK in multiple row you can use your query as

 SELECT
        wpss_quesset.quesset,
        wpss_questions.*
    FROM wpss_quesset
    JOIN wpss_questions
        ON wpss_quesset.id = wpss_questions.ques_set_id
   GROUP BY wpss_quesset.id

OR If you want to get all record that of related FK you should use group concat with group by

Example

 SELECT
        wpss_quesset.quesset,
        GROUP_CONCAT( wpss_questions.question)
        wpss_questions.*
    FROM wpss_quesset
    JOIN wpss_questions
        ON wpss_quesset.id = wpss_questions.ques_set_id
   GROUP BY wpss_quesset.id


Checking the data latency on an Always On Availability Group in ASYNC mode

It is worth noting that the value for last_commit_time is defined differently based on whether it is being queried from the secondary or primary replica.

  On the secondary database, this time is the same as on the primary
  database.

  On the primary replica, each secondary database row displays the time
  that the secondary replica that hosts that secondary database has
  reported back to the primary replica. The difference in time between
  the primary-database row and a given secondary-database row represents
  approximately the recovery point objective (RPO), assuming that the
  redo process is caught up and that the progress has been reported back
  to the primary replica by the secondary replica.

In addition to the above, a secondary replica that has a delay of a few seconds on the commit will yield a positive number, but one that hasn't finished the commit yet will yield a negative number in your query.

So if you have a latency of 10 seconds but query at the 5 second mark, since there is no commit time yet on the secondary for the primary's last commit, you're going to get the previous commit time which is probably before the primary's latest commit time. This is expected with asynchronous replication.


SQL Server configuring both UCP and MDW on a single server

You should use the same database for UCP and MDW.

Actually, you can't use a different database. UCP is in fact a specialized collection set that uploads its data to a MDW database. The MDW database is unique for a SQL Server instance and Data Collector cannot upload different collection sets to different MDW databases.

The database name for UCP must be "sysutility_mdw" and cannot be changed, so if you decide to activate UCP, you will have to stick to that name. That's why I always advise naming the MDW database "sysutility_mdw", so you can activate UCP in the future. To be fair, very few people use it.


Where is authentication information saved in master?

I wonder is there a way to retrieve that information? If yes, which tables stores it? (except contained database users)

For SQL Server authentication you have password stored in hash format you can see it from sys.sql_logins, the column password_hash stores password in hash format. The information about SQl server logins are stored in master database and each login has SID respective to it. Only SA login has same SID no matter what server it is. To get SQL authenticated logins you can use below query

SELECT name
FROM sys.server_principals
WHERE TYPE = 'S'
and name not like '%##%'

You can use sys.database_principals and sys.server_principal to gain more information about logins and users.

Regarding would you be able to recover the hashed password or decrypt it in some somway, the answer is NO you cannot.


MySQL Database Poisoning: How to recover to a known state quickly

You can use the pt-slave-delay tool from Percona Toolkit to keep a replica delayed by the amount of time you choose.


Database stuck in synchronized mode and another one in 'Initializing / In Recovery' mode

Use the query SHUTDOWN WITH NOWAIT on the server, then try restarting the services for the server.
Refer link for more information:
https://blog.sqlauthority.com/2018/02/09/sql-server-change-pending-state-sql-server-configuration-manager/#:~:text=WORKAROUND%2FSOLUTION,and%20able%20to%20run%20queries.


If a DW fact table is not uniquely identified by all dimensions, what problems?

I don't think you will run into a lot of problems when querying your data warehouse except for the one you already stated (cross fact queries) although if you are aware of how your design is done and only aggregate over dimensions I think you should still be able to query that. You couldn't join on the dimension keys in the fact table but you could still aggregate.

If you only care about aggregations on a daily granularity there is no need to have a dimension with a finer granularity in my opinion.

Where you might run into problems is if you try to do incremental loads or update fact records in your ETL instead of doing a full load but there are ways around that.

You could use a fact table surrogate key as Kimball describes.
You could use the technique described by Vincent Rainardi
You could include "supporting" columns in your fact table that you never report on but support your ETL strategies (in your case timestamp maybe)


One to one relationship naming conventions

Is there a naming convention about how can I name primary columns with a one-to-
  one relationship?

I am sure there are plenty of them. Choose one.

For example, I would name the reference

CodeREF

And if having multiple I would use describtive names as per the objects that I will manifest (RawCoderREF, ReviewedCodeREF)


Restore it to a dummy DB, purge the data that you don't want them to have, make another full backup, drop the dummy DB, and then provide them the copy of the BAK file at that point.


Will table inheritance in PostgreSql suit this situation?

It is viable provided that you note that keys and indexes must be declared on each child table.

In fact this is pretty close to an ideal use for table inheritance.  Just make sure you are familiar with the gotchas, and that you pay close attention to foreign key creation in the user schemas.

One caveat is that you can't cascade CREATE statements into all the schemas.  This would have to be scripted.  You could however create plpgsql functions to do this.  Just note that such statements usually are not parameterized so you need to use quote_ident() and quote_literal as appropriate.

As per request here's an example.  In the master schema.  Note the key and unique constraints here are for documentation purposes only.

CREATE SCHEMA master;
SET SEARCH_PATH="master;

CREATE TABLE users (
   id bigserial not null unique,
   login text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE roles (
   id bigserial not null unique,
   role_name text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE user_roles (
   user_id bigint references users(id),
   role_id bigint references roles(id),
   primary key (user_id, role_id)
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

RESET SEARCH_PATH;

Naturally if not on PostgreSQL 9.2 drop the check constraints since they would be inherited in prior versions and then no tables could have rows.

Then in a child schema:

CREATE SCHEMA customer123;
SET SEARCH_PATH 'customer123';

CREATE TABLE users(unique (id), primary key(login)) INHERITS (master.users);
CREATE TABLE roles(unique (id), prikary key(role_name)) INHERITS (master.roles);
CREATE TABLE user_roles(
     foreign key users_id REFERENCES customer123.users(id),
     foreign key role_id REFERENCES customer123.roles(id),
     primary key (users_id, role_id)
);
RESET SEARCH_PATH;

Note in this case you must carefully control access to the master schema because:

SELECT tableoid, login from master.users;

gives you every user account of every tenant plus enough info to determine which tenant it is.


Several PC Cannot Connect To MySQL Server on Certain Time

This has nothing to do with the MySQL server, so no troubleshooting on that side should be needed.  It is also unrelated to usernames, machine names, and passwords.

C:\>perror 10055
Win32 error code 10055: An operation on a socket could not be performed because
the system lacked sufficient buffer space or because a queue was full.

The MySQL server is not returning this error.  Windows, on the client side, is generating this error locally, when you're trying to connect, because of a resource shortage on the client system.  Of course, a reboot of the client would clear this right up... until you hit the limit again.

The most likely cause of this would be that you have an application running on those same machines is not releasing system resources properly... It wouldn't necessarily even have to be the application that's accessing MySQL but that's probably a good bet if that's the only application where you're experiencing problems.

This command, run on the client that is experiencing the problems, will list all of your currently open network sockets, as well as the name of the application that owns them:

C:\netstat -a -b -n

Running this on a machine that is unable to connect should give you an idea of which application is causing the problem because you will likely see an inordinate amount of sockets owned by one application.

The Windows error is also known as WSAENOBUFS.


WiredTiger - "too many open files error" during resync of a secondary replica set member

checkout your /etc/security/limits.conf file and change soft and hard limits according to your need.

* soft nofile 4096

* hard nofile 4096

in the first column * means this limit is applied for all users except root user.
you also need to edit /etc/pam.d/common-session* file add the following line in the end :

session required pam_limits.so

or else

  ulimit -n 65535 (and no reboot required)

Finally, logout/reboot .


How to access a SQL Server database using another user with permissions in the same intranet

You can open SSMS as her Windows user account by doing either of the following:

Right click the SSMS shortcut while holding the SHIFT key. Click on Open as another user and enter the other users credentials.
Create a shortcut in the following format:

  C:\Windows\System32\runas.exe /savecred /user:DOMAIN\USERNAME
  "C:\Program Files (x86)\Microsoft SQL
  Server\140\Tools\Binn\ManagementStudio\Ssms.exe"

You'll need to replace the path to SSMS.exe with your version.

Once you open the shortcut and enter the password for the account, you can then simply double click the shortcut in the future as it will store your credentials for later use.


Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions?

The actual NTFS log file doesn't "shrink" from a transaction log backup, but VLFs (Virtual Log Files) within the transaction log are marked for reuse (because they are now backed up and persisted on media) allowing the wrap-around of transaction log use to occur.  If you aren't backing up the transaction log, or not frequently enough then there will be not available VLFs and that will cause the transaction log to grow (provided that autogrowth is set) to accommodate additional transaction log entries.

  2.At first I was thinking I should shrink the log files after every 5:00 AM backup. After reading up on how that's bad for performance I now believe that I need to take regular log backups every couple of hours during the day. Is that correct?

Routine and scheduled file shrinkage is not a good idea.  Only when you need to reclaim much needed space should you consider a DBCC SHINKFILE.  Also, when you are continuously growing your transaction log, you could be hindering other things such as the recovery of the database.  With too many VLFs in the transaction log (a common problem when the transaction log is only grown by a small storage increment) the amount of time to recovery the database could be longer than desired.

  3.My normal full backup of the database/logs happens every day at 5:00 AM and sometimes takes 3 hours. If I schedule the log backups to happen every hour, what will happen when the log backup collides with the 5:00 AM backup?

Nothing will happen, that is a completely legal operation.  See this below graph from MSDN.  Where there is a black dot, those two operations can not occur at the same time.  As you can see, a database backup and a transaction log are allowed concurrently.

The takeaway here is you should be backing up your transaction log more frequently.  NTFS file growth isn't the only problem you could run into by not backing up your transaction log more frequently.  If you were to have storage failure and your transaction log is lost, then you can only restore to the point in time of your last transaction log backup.  If the transaction log is lost, you won't be able to backup the tail of the log and restore to point-in-time of the failure.  In your case, you could potentially lose 24 hours worth of data.  But if you backup your transaction logs every, say, 30 minutes then your maximum data loss would be 30 minutes.  In that case, if your transaction log is gone, and you have your full backup and your intact log chain you could restore to that last log backup.

TechNet documentation on Transaction Log Truncation


Searching a big table for a few results without scanning the whole table

but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?)

Correct; it'll just limit how many results are returned.

For some kinds of query it can also limit how many are scanned in the first place, but you can't rely on that in the general case.

  does PostgreSQL database have to create this 1M search result rows temporarily in memory and then give me my needed 100 results?

Create, yes. In memory, not necessarily. It'll often spill to disk, or discard unneeded results as it goes so it only keeps the top 100. It depends on the details of the query.

  or is there any way to tell PostgreSQL to stop searching as soon as it find 100 records?

Write a query where that is possible. You haven't shown the query, or schema, so this part is impossible to usefully answer.

In general, if you have an ORDER BY on some kind of search relevance field then the whole data set must be searched then filtered for the top-n results.

In short: "It depends". Specifically, it depends on the schema, available indexes, and on exactly what you're searching for and what results you want.

This:

SELECT id
FROM mytable
WHERE somefield > 100
ORDER BY somefield DESC
LIMIT 100;

would only scan the needed rows if there was an index on mytable(somefield DESC).

This:

SELECT id, title
FROM mytable
WHERE title LIKE '%something%'
ORDER BY calculate_relevance(title, 'something')
LIMIT 100;

would always scan the whole table, both because an infix text pattern match (LIKE '%blah%') can't use a b-tree index, and because you can't create an expression index on a function taking a literal parameter like the case of the imaginary calculate_relevance function above.

So... it depends totally on what you're searching for and how.

Is this answer too hand-wavey and general for you? So's the question. Supply specifics, and you'll get more specific answers. A http://sqlfiddle.com/ of the schema with sample data, plus your PostgreSQL version, a table of the expected results, and a description of what you want to find is usually the minimum for this kind of thing.

Update: A few problems here.

There's no index on tag1 and/or tag2 so Pg must do a seqscan of the table, searching it until it finds 100 results. This is extremely inefficient.
There's no ORDER BY, so PostgreSQL can return whatever results it feels like for a LIMIT and return them in any order.
Your schema shows signs of being unnecessarily denormalized. tag1, tag2, etc usually suggest bad table design. I strongly recommend that you study relational database design and normalization.

In this specific case, you'd be able to avoid the full table scan if you CREATE INDEX con_tag1_tag2_idx ON con(tag1, tag2). But that'll only work for searches that use tag1 and optionally also tag2. It won't help (much) with searches for just tag2, searches for ref1, etc. (Thanks Erwin for the important note on multicolumn index use with the first column not included, it was a surprise to me).

Don't create lots of indexes. Each one uses disk space and takes disk I/O to insert, update and delete.

The answer is often proper normalization.

Overall I think you need to buy/borrow a couple of good introductory books on relational database design and basic to intermediate SQL, preferably PostgreSQL-focused, and do some study.


Do Indexes in the T-SQL Data Warehouse automatically go over and optimize into the SSAS Cube?

The answer you quoted is worded a bit confusing but it says that it improves the performance of processing the cube, not querying it.

  So the point is, create appropriate indexes on source tables to
  improve the performance of the query which SSAS fires while processing
  the cube or while retrieving data from the source. If you have access
  to the source data, you can use this DMV to identify missing indexes
  or you can use the Index Tuning Advisor for identifying and creating
  missing indexes on the source.

Querying the cube doesn't retrieve data from the source, it retrieves data from the SSAS storage engine.

What can happen however is that your cube has a drillthrough action that action may read from the actual source database and filter your fact table on the dimension keys which were filtering the measure in the cube, in which case it may benefit from indexes in your data warehouse.


Dynamic Number of Columns in Pivot Table

Working from Aaron Bertrand's Script to create dynamic PIVOT queries in SQL Server and with a sample data set defined thus:

if object_id('#ObtainedMarkEntry') is not null drop table #ObtainedMarkEntry;
create table #ObtainedMarkEntry(
    StudentID       int not null,
    SubjectID       int not null,
    ObtainedMarks       int not null
);
insert #ObtainedMarkEntry(StudentID, SubjectID, ObtainedMarks)
values
        (1,100,90) ,(1,101,70)
       ,(2,100,65) ,(2,300,55)
;

then this SQL:

declare @columnsSrc nvarchar(max) = N''
       ,@columnsDst nvarchar(max) = N''
       ,@sql        nvarchar(max)
       ,@KeyColumns nvarchar(max) = N'StudentID'
       ,@compatibility int = (
            select top 1 compatibility_level from sys.databases
            where name = db_name()
            order by Name
       );
declare @GroupBy nvarchar(max) =
--case when @compatibility <= 90
--   then case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns + ' with rollup' end
--   else case when len(@KeyColumns)=0 then '' else 'group by rollup (' + @KeyColumns + ')'    end
--end
case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns end

;

select
    @columnsSrc += nchar(10) + N',' + quotename(SubjectID)
   ,@columnsDst += nchar(10) + N',sum(isnull(' + quotename(SubjectID) + N',0)) as '
                                               + quotename(SubjectID)
  from (
      select SubjectID
      from #ObtainedMarkEntry
      group by SubjectID
  ) as x
  order by x.SubjectID
;

set @sql = N'
select ' +
  case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
  STUFF(@columnsDst, 1, 2, '') + '
from (
  select' + nchar(10) +
    case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
'      SubjectID, ObtainedMarks
   from #ObtainedMarkEntry
) as j
pivot (
  sum(ObtainedMarks) for SubjectID in ('
  + stuff(replace(@columnsSrc, ',p.[', ',['), 2, 1, '')
  + ')
) as p' + nchar(10) +
@GroupBy +
';'
;

--print @sql;
exec sp_executesql @sql;
go

yields as desired:

StudentID   100         101         300
----------- ----------- ----------- -----------
1           90          70          0
2           65          0           55


What is the best way to remove this table partition?

To remove table partitioning entirely, rebuild all indexes with a filegroup specification instead of partition scheme.  Use CREATE INDEX with the DROP_EXISTING=ON option to do that efficiently.

See MSDN: CREATE INDEX page for syntax.


What does EXPLAIN show for that query?  What does mysqltuner show? What does free -m show?

If turning the query cache off helped, I'd suspect you are running out memory and your machine is starting to swap.  Swapping is a pretty effective way to kill MySQL's performance.  The solution would be to either increase the memory of the machine, or tune MySQL to require less memory.


What is the correct/fastest way to negate @> in Postgres?

I don't think there is any highly effective way to index generic versions of that expression.  If you find a way to do so, I think you will be famous (well, famous within database circles, anyway).

If it is always exactly NOT column @> array['thing'] where 'thing' does not change, you could build special-purpose indexes, like

create index on foo (("column" @> array['thing']));

(or a partial index version of that)

Once 9.6 is released, you will be able to do the seq scan in parallel, and so get a boost that way.


How to install SQL Server Management Console in 2008 Express Version?

Do any of these links help?

http://connectsql.blogspot.com/2009/07/how-to-install-sql-server-management.html
http://blogs.msdn.com/b/bethmassi/archive/2011/02/18/step-by-step-installing-sql-server-management-studio-2008-express-after-visual-studio-2010.aspx
http://msdn.microsoft.com/en-us/library/bb500441%28v=sql.100%29.aspx


Permissions in triggers when using cross database certificates

The problem here is that while the Certificate links the stored procedure in DatabaseA with the User in DatabaseB that has INSERT permissions on the two tables, the Trigger on the table being inserted into directly from the stored procedure is another module in the chain, and permissions acquired from Certificates do not pass along to other modules in a chain. Meaning, the Certificate allowed the Stored Procedure to insert into the table via the User, and even execute the Trigger. But, no permissions have been given to the Trigger to do anything related to objects (doing something like SELECT 1; would work).

In this case, permissions need to be given to the Trigger, via that same Certificate, so that it can take whatever actions are necessary. This can be accomplished, at the very least, by counter-signing the Trigger. And you do that by executing ADD COUNTER SIGNATURE TO [TriggerSchema].[TriggerName] BY CERTIFICATE ...;. After that, it should just work, even without direct INSERT permission for the Certificate-based User on the table being inserted into by the Trigger.

The example code below reproduces the problem, fixes the problem by adding a counter signature, but does not grant INSERT permissions to the trigger-populated table.

CLEANUP

USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseA')
BEGIN
    PRINT 'Dropping [DatabaseA] DB...';
    ALTER DATABASE [DatabaseA] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseA] SET ONLINE;
    DROP DATABASE [DatabaseA];
END;

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseB')
BEGIN
    PRINT 'Dropping [DatabaseB] DB...';
    ALTER DATABASE [DatabaseB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseB] SET ONLINE;
    DROP DATABASE [DatabaseB];
END;

IF (SUSER_ID(N'JohnnyLunchbucket') IS NOT NULL)
BEGIN
  PRINT 'Dropping [JohnnyLunchbucket] Login...';
  DROP LOGIN [JohnnyLunchbucket];
END;

IF (OBJECT_ID(N'tempdb..#CertInfo') IS NOT NULL)
BEGIN
  PRINT 'Dropping [#CertInfo] Temp Table...';
  DROP TABLE #CertInfo;
END;

SETUP

USE [master];

EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [DatabaseA] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [DatabaseB] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO

-- Default for both options should be OFF, but just to be sure:
ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseA] SET TRUSTWORTHY OFF;

ALTER DATABASE [DatabaseB] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseB] SET TRUSTWORTHY OFF;
GO

CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'OhSoSecure;)';

USE [DatabaseA];

CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];
GO

--DROP PROCEDURE dbo.InsertIntoTableWithoutTrigger;
CREATE PROCEDURE dbo.InsertIntoTableWithoutTrigger
(
    @SomeValue NVARCHAR(50)
)
AS
SET NOCOUNT ON;

INSERT INTO [DatabaseB].[dbo].[TableWithoutTrigger] (SomeValue)
VALUES (@SomeValue);
GO

GRANT EXECUTE ON dbo.InsertIntoTableWithoutTrigger TO [JohnnyLunchbucket];
GO

CREATE PROCEDURE dbo.InsertIntoTableWithTrigger
AS
SET NOCOUNT ON;

INSERT INTO [DatabaseB].[dbo].[TableWithTrigger] (SomeOtherValue)
VALUES (NEWID());
GO

GRANT EXECUTE ON dbo.InsertIntoTableWithTrigger TO [JohnnyLunchbucket];

CREATE CERTIFICATE [PermissionsCert]
  AUTHORIZATION [dbo]
  ENCRYPTION BY PASSWORD = 'WeakPassword'
  WITH SUBJECT = 'Used to test granting permissions to code',
  EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE TO [dbo].[InsertIntoTableWithoutTrigger]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';

ADD SIGNATURE TO [dbo].[InsertIntoTableWithTrigger]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';

-- Save Certificate info in temporary table so we can recreate in DatabaseB
SELECT CERTENCODED(CERT_ID(N'PermissionsCert')) AS [PublicKey],
       CERTPRIVATEKEY(CERT_ID(N'PermissionsCert'), 'OtherPassword', 'WeakPassword')
              AS [PrivateKey]
INTO   #CertInfo;
GO

USE [DatabaseB];

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'CREATE CERTIFICATE [PermissionsCert] AUTHORIZATION [dbo] FROM BINARY = '
               + CONVERT(NVARCHAR(MAX), [PublicKey], 1)
               + N' WITH PRIVATE KEY (BINARY = '
               + CONVERT(NVARCHAR(MAX), [PrivateKey], 1)
               + N', DECRYPTION BY PASSWORD = N''OtherPassword'''
               + N', ENCRYPTION BY PASSWORD = ''WeakPassword'');'
FROM   #CertInfo;

PRINT @SQL;
EXEC (@SQL);

CREATE USER [PermissionsUser] FROM CERTIFICATE [PermissionsCert];

--DROP TABLE dbo.[TableWithoutTrigger];
CREATE TABLE dbo.[TableWithoutTrigger]
(
  [TableWithoutTriggerID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_TableWithoutTrigger] PRIMARY KEY,
  [SomeValue] NVARCHAR(50)
);

GRANT INSERT ON [dbo].[TableWithoutTrigger] TO [PermissionsUser];

CREATE TABLE dbo.[TableWithTrigger]
(
  [TableWithTriggerID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_TableWithTrigger] PRIMARY KEY,
  [SomeOtherValue] NVARCHAR(50)
);

GRANT INSERT ON [dbo].[TableWithTrigger] TO [PermissionsUser];

CREATE TABLE dbo.[TablePopulatedByTrigger]
(
  [TablePopulatedByTriggerID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_TablePopulatedByTrigger] PRIMARY KEY,
  [DuplicatedValue] NVARCHAR(50)
);
GO

CREATE TRIGGER dbo.CopySomeOtherValue
ON dbo.[TableWithTrigger]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.[TablePopulatedByTrigger] ([DuplicatedValue])
        SELECT ins.[SomeOtherValue]
        FROM   inserted ins;
END;
GO

TEST 1: Trigger fails

USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [DatabaseB].[dbo].[TableWithoutTrigger];
SELECT * FROM [DatabaseB].[dbo].[TableWithTrigger];
SELECT * FROM [DatabaseB].[dbo].[TablePopulatedByTrigger];
INSERT INTO [DatabaseB].[dbo].[TableWithoutTrigger] ([SomeValue]) VALUES (N'test 0');
USE [DatabaseB];
/* -- All 5 statements above get the following error:
Msg 916, Level 14, State 1, Line xxxxxx
The server principal "JohnnyLunchbucket" is not able to access the database
    "DatabaseB" under the current security context.
*/

EXEC [dbo].[InsertIntoTableWithoutTrigger] @SomeValue = N'test A'; -- SUCCESS!!!

EXEC [dbo].[InsertIntoTableWithTrigger]; -- ERROR:
/*
Msg 916, Level 14, State 1, Procedure CopySomeOtherValue, Line xxxxxx
The server principal "JohnnyLunchbucket" is not able to access the database
    "DatabaseB" under the current security context.
*/

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

-- Check to make sure that dbo.InsertIntoTableWithoutTrigger really did work:
SELECT * FROM [DatabaseB].[dbo].[TableWithoutTrigger];
-- 1    test A

TEST 2: Trigger succeeds

Please note that the only change being made is ADD COUNTER SIGNATURE; there is no GRANT INSERT ON dbo.TablePopulatedByTrigger TO [PermissionsUser];.

USE [DatabaseB];

ADD COUNTER SIGNATURE
    TO dbo.[CopySomeOtherValue]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';
GO

USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

INSERT INTO [DatabaseB].[dbo].[TableWithTrigger] ([SomeOtherValue]) VALUES (N'Test B');
INSERT INTO [DatabaseB].[dbo].[TablePopulatedByTrigger]([DuplicatedValue]) VALUES ('Test B')
/*
Msg 916, Level 14, State 1, Line xxxxxx
The server principal "JohnnyLunchbucket" is not able to access the database
    "DatabaseB" under the current security context.
*/

EXEC [dbo].[InsertIntoTableWithTrigger]; -- SUCCESS!!!

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

SELECT * FROM [DatabaseB].[dbo].[TableWithTrigger];
SELECT * FROM [DatabaseB].[dbo].[TablePopulatedByTrigger];
-- 2    968DB092-C3DE-4E4B-92B9-E21CA551A5FA
-- 1    968DB092-C3DE-4E4B-92B9-E21CA551A5FA


MySQL command "SHOW DATABASES" returns invalid databases

You need to make sure they do not contain any tables

Please run this query

select
    table_schema AS BlankDatabases
from
    (select
        A.table_schema, COUNT(B.table_schema) table_count
     from
        (select
            schema_name AS table_schema
        from
            information_schema.schemata
        where
            schema_name not in ('information_schema','mysql','performance_schema')
        ) A
        left join
        (select
            table_schema
        from
            information_schema.tables
        where
            table_schema not in ('information_schema','mysql','performance_schema')
        ) B
        using (table_schema)
    group by
        A.table_schema
    ) AA
where
    table_count = 0;

This will tell you which databases do not have any tables

For example, let's say you run that query and get the following output

mysql> select table_schema BlankDatabases from
    -> (select A.table_schema,COUNT(B.table_schema) table_count from
    -> (select schema_name table_schema
    -> from information_schema.schemata
    -> where schema_name not in
    -> ('information_schema','mysql','performance_schema')) A
    -> left join
    -> (select table_schema
    -> from information_schema.tables
    -> where table_schema not in
    -> ('information_schema','mysql','performance_schema')) B
    -> using (table_schema)
    -> group by A.table_schema
    -> ) AA where table_count = 0;
+----------------+
| BlankDatabases |
+----------------+
| junk           |
| test           |
+----------------+
2 rows in set (0.00 sec)

mysql>

You can just run

DROP DATABASE junk;
DROP DATABASE test;

In your case, you should surround the database names with back quotes (`)

DROP DATABASE `@feff...@feff`;

If you are having a hard time, go to the OS and remove the folder.

For example, if your datadir is /var/lib/mysql and you are removing @feff...@feff, do this

cd /var/lib/mysql
rm -rf "@feff...@feff"

GIVE IT A TRY !!!


Why is datalength of decimal 5 regardless of precision?


How to properly add IPv6 data of GeoLite2 into existing table?

To directly answer your question, no, you can't use the exact same code from that tutorial with the IPv6 version.

Here are a few reasons:

The LOAD DATA LOCAL INFILE query that inserts the data uses MySQL's INET_ATON() function, which won't work with IPv6. Thankfully MySQL has another function INET6_ATON() which supports both IPv4 and IPv6, although it does return a different datatype, VARBINARY(16) instead of integer. Which brings me to point #2...
In the tutorial, the ip_blocks table is created with the ip_from and ip_to columns as int, but as mentioned above, you need VARBINARY(16) to be compatible with IPv6.
The LOAD DATA LOCAL INFILE query also does some integer math to calculate a min  and max IP address based on the CIDR range in the CSV. This won't work with the binary format returned from the INET6_ATON() function.

Most of these obstacles stem from the fact that the IPv6 address space is much larger than the IPv4 space (128 bits versus 32 bits), which means IPv6 can't be stored as a normal integer. So you will need to modify the code and underlying storage quite a bit to make it work.

You can probably use MaxMind's converter tool to get a min and max IP address from the IPv6 range in the CSV, then use the BETWEEN keyword to find IP addresses in that range. See this question for background info: https://stackoverflow.com/questions/42414946/check-if-ipv6-is-inside-range


Use the mysqlbinlog utility

mysqlbinlog --help gives the following:

-j, --start-position=#
                  Start reading the binlog at position N. Applies to the
                  first binlog passed on the command line.
--stop-position=#   Stop reading the binlog at position N. Applies to the
                  last binlog passed on the command line.

Suppose your binary log is called mysql-bin.012345. You would do the following for positions 10 - 2500 to attach it to a mysqldump called MySQLData.sql:

Either

mysqlbinlog --start-position=10 --stop-position=2500 mysql-bin.012345 > SQLFromBinLog.sql
cat SQLFromBinLog.sql >> MySQLData.sql

or append directly with

mysqlbinlog --start-position=10 --stop-position=2500 mysql-bin.012345 >> MySQLData.sql

Give it a Try !!!


This question is very similar with your other question.

As already stated there, I would prefer a translation table like this:

Table Translations

Code (PK) (Your translation code)
type (mobile, desktop, maybe something else later)
language_id (your language code - 1:EN, 2:FR, 3:DE,...)
value (your translation)


How to insert values into a new column which is added into an existing table?

You need to update the existing table.

mysql> create table tbl1(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into tbl1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> alter table tbl1 add column name varchar(20);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from tbl1;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> update tbl1 set name='Jay' where id=1;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tbl1;
+------+------+
| id   | name |
+------+------+
|    1 | Jay  |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> update tbl1 set name='Joe' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tbl1;
+------+------+
| id   | name |
+------+------+
|    1 | Jay  |
|    2 | Joe  |
+------+------+
2 rows in set (0.00 sec)


Run SQL query with python in SQL Server raises error: SqlSatelliteCall error: Unsupported input data type in column

Sometimes the error messge can be misleading. In this case it's not. You're trying to use unsupported data type:

  Unsupported input data type in column 'ProductID'. Supported types:
  bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float,
  char, varchar, nchar, nvarchar, varbinary.

These two columns are causing the error:

ProductID numeric(18,3)
RegionID numeric(18,3)

According to Microsoft docs, bigint is the SQLtype equivalent to numeric Python type.

Please, try to run your code like this:

exec sp_execute_external_script
@language = N'python', @script = N'

import pandas as pd
inventory = pd.DataFrame(sql_inventory)

',
@input_data_1 = N'SELECT TOP 10 ShopCode, QTY, Date, Amount, ID FROM inventory',
@input_data_1_name = N'sql_inventory'

If I'm right, it should run fine. Since both columns ProductID and RegionID are numeric type you must either CAST or CONVERT those columns to refer to them in the query, but beware of the possibility of precision loss when converting from numeric to float or real.

  Should I specify data types every time I want to read from a query?

No. Just use the supported data types and you should be fine.


How to rename an index in informix using numbering

I realized that once you rename an index like the below:

RENAME INDEX 584_1973 TO 584_1973_tmp;

You cannot revert, you will need to drop and recreate. If it is a unique index constraint, then it will auto recreate with the original numbering after dropped.


How can I minimize the risk of accidentally modifying the wrong database?

One thing I like to do in SSMS is to use Custom Colours when connecting to database.
So you choose a nice bright Red for Live databases, and a gentle blue or green for dev or test systems.
I used to use the inbuilt SSMS, but these days I prefer the SSMS Tools Addon Colour coding.

Or like this for SSMS Tools (A really nice addon, and I find the colour better when it's on top, rather than on the bottom like the built in on)


Let's first distinguish between keys and indexes, key is part of the logical model and is often implemented with an unique index. You can however create a unique index without creating a key, but that can not be referenced by a foreign key.

A candidate key is something that uniquely identifies a row in a table, in SQL one of the candidate keys are normally used as a primary key (I never really understood why one of the c.k. is concidered "better" than the others, but that's another story), and the remaining c.k becomes unique constraints.

A unique constraint can be used the same way as a primary key can. Consider:

create table A ( x ... not null
               , y ... not null
               , z ... not null
               ,     unique (x)
               ,     primary key (y,z) );

create table B ( x ...
               ,   ...
               ,     foreign key (x) references A (x) );

create table C ( y ...
               , z ...
               ,   ...
               ,     foreign key (y, z) references A (y, z) );

B references the unique constraint and C references the primary key constraint.

NOT NULL is yet another kind of constraint. In your case you can enforce this for email without declaring it unique.

The next aspect of your post concerns the stability of a key, a key should be stable (but that doesn't mean it can never change, it does not have to be immutable). Some DBMS implements ON UPDATE CASCADE that can be of help for such operation, still if the key is distributed around your model it will be a pain updating it.

In your case I would probably choose another candidate key as the primary key, and declare email to be NOT NULL and UNIQUE.


How to create a server instance of just ComputerName instead of ComputerName\SQLEXPRESS

You cannot change the Instance Name after it has been installed.  But you can uninstall that SQL Server instance and reinstall as a DEFAULT instance.  Ideally you should make a backup of the server, then restore the backups to the new server.

You can technically detach databases and attach again after the reinstall, but that includes the risk of losing your detached databases. See Aaron Bertrand's post:

https://blogs.sentryone.com/aaronbertrand/bad-habits-file-backups/

When installing a default instance of SQL Server Express at the Instance Configuration page, you must choose the Named Instance radio button and type in the default instance name of MSSQLSERVER.

All default instances use the MSSQLSERVER internally, but they just do not make it visible to you.


How can I use the same primary key across multiple tables as a foreign key?

I suspect you are linking the tables backwards.
You can most definitely have multiple tables' FK point to the same table's PK. The FK is defined on the table doing the pointing, not the table being pointed at.


“ORA-03146: invalid buffer length for TTC field” after upgrade from Oracle 12 to 19c

JDBC application fails with ORA-03146: Invalid Buffer Length For TTC Field After Database Upgrade to 19c (Doc ID 2729562.1)

Updating a 12c database to 19.6 can result in this issue. In this
particular case, the database was upgraded from 12.1.0.2.
The ORA-3137 [3146] is a protocol exception which means that the
database has not consumed all the data from the user buffer for the
long bind.
This issue is addressed in Bug:30955880 - ORA-3137 [3146] [4] /
ORA-3137 [3146] [94] UPDATING LOB WITH LARGE BIND which was closed as
duplicate of unpublished Bug 31625618 - DML OVER A LOB COLUMN USING
LONG BIND FAILS WITH AN ORA-3137 [3146] ERROR WHEN THE BIND SIZE IS >
256K BYTES.

Then:
Bug 31625618 - DML Over a LOB Column Using Long Bind Fails with ORA-3137 [3146] [4] / ORA-3137 [3146] [94] When the Bind Size is > 256k Bytes (Doc ID 31625618.8)

Clients (JDBC/OCI) performing DML's on a LOB column intermittently
fail  with the below errors, when the bind length is > 256k bytes:
ORA-03137: malformed TTC packet from client rejected: [3146] [4]    ORA-03137: malformed TTC packet from client rejected: [3146] [94]
ORA-03146: invalid buffer length for TTC field

I quoted this part to highlight that this issue was not specific to JDBC.
Fix for Bug 31625618 exists as a oneoff patch for 19c versions below 19.10.
The bug is fixed in database version 19.10, which will be released next week.
Currently you are on 19.3. Apply the necessary patches.


Interpretation of a column in SQL Server profile trace

The object ID is actually a partition id. This question over on SO provides a simple query to retrieve the needed information.

A side note, since you are using SQL Server 2008 R2 I would highly suggest taking a look into the System Health session that contains the recent deadlocks that have occurred, among other things. Depending on the activity of your instance you may need to pull this information out on a scheduled basis since it is buffer memory so does not stay there long. It is much more efficient to pull this information out than trying to setup a trace and waiting for it to occur again.

I actually wrote up a blog post on this area that includes a script to parse out the information where it is a bit more readable. The blog post is here, the script is below:

;WITH xDeadlock (Contents)
AS
(
select CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph
FROM
    (select CAST(target_data as xml) as TargetData
    from sys.dm_xe_session_targets st
    join sys.dm_xe_sessions s on s.address = st.event_session_address
    where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)
), Victims AS
(
SELECT    ID = Victims.List.value('@id', 'varchar(50)')
FROM xDeadlock
CROSS APPLY xDeadlock.Contents.nodes('//deadlock/victim-list/victimProcess') AS Victims (List)
), Locks AS
(
SELECT  --xDeadlock.DeadlockID,
        MainLock.Process.value('@id', 'varchar(100)') AS LockID,
        OwnerList.Owner.value('@id', 'varchar(200)') AS LockProcessId,
        REPLACE(MainLock.Process.value('local-name(.)', 'varchar(100)'), 'lock', '') AS LockEvent,
        MainLock.Process.value('@objectname', 'sysname') AS ObjectName,
        OwnerList.Owner.value('@mode', 'varchar(10)') AS LockMode,
        MainLock.Process.value('@dbid', 'INTEGER') AS Database_id,
        MainLock.Process.value('@associatedObjectId', 'BIGINT') AS AssociatedObjectId,
        MainLock.Process.value('@WaitType', 'varchar(100)') AS WaitType,
        WaiterList.Owner.value('@id', 'varchar(200)') AS WaitProcessId,
        WaiterList.Owner.value('@mode', 'varchar(10)') AS WaitMode
FROM    xDeadlock
        CROSS APPLY xDeadlock.Contents.nodes('//deadlock/resource-list') AS Locks (list)
        CROSS APPLY Locks.List.nodes('*') AS MainLock (Process)
        CROSS APPLY MainLock.Process.nodes('owner-list/owner') AS OwnerList (Owner)
        CROSS APPLY MainLock.Process.nodes('waiter-list/waiter') AS WaiterList (Owner)
), Process AS
(
-- get the data from the process node
SELECT  --xDeadlock.DeadlockID,
        [Victim] = CONVERT(BIT, CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = ISNULL(Deadlock.Process.value('../../@victim', 'varchar(50)'), v.ID)
                                     THEN 1
                                     ELSE 0
                                END),
        [LockMode] = Deadlock.Process.value('@lockMode', 'varchar(10)'), -- how is this different from in the resource-list section?
        [ProcessID] = Process.ID, --Deadlock.Process.value('@id', 'varchar(50)'),
        [KPID] = Deadlock.Process.value('@kpid', 'int'), -- kernel-process id / thread ID number
        [SPID] = Deadlock.Process.value('@spid', 'int'), -- system process id (connection to sql)
        [SBID] = Deadlock.Process.value('@sbid', 'int'), -- system batch id / request_id (a query that a SPID is running)
        [ECID] = Deadlock.Process.value('@ecid', 'int'), -- execution context ID (a worker thread running part of a query)
        [IsolationLevel] = Deadlock.Process.value('@isolationlevel', 'varchar(200)'),
        [WaitResource] = Deadlock.Process.value('@waitresource', 'varchar(200)'),
        [LogUsed] = Deadlock.Process.value('@logused', 'int'),
        [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
        [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
        [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
        [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
        [BatchStarted] = Deadlock.Process.value('@lastbatchstarted', 'datetime'),
        [BatchCompleted] = Deadlock.Process.value('@lastbatchcompleted', 'datetime'),
        [InputBuffer] = Input.Buffer.query('.'),
        xDeadlock.[Contents],
        [QueryStatement] = Execution.Frame.value('.', 'varchar(max)'),
        TranCount = Deadlock.Process.value('@trancount', 'int')
FROM    xDeadlock
        CROSS APPLY xDeadlock.Contents.nodes('//deadlock/process-list/process') AS Deadlock (Process)
        CROSS APPLY (SELECT Deadlock.Process.value('@id', 'varchar(50)') ) AS Process (ID)
        LEFT JOIN Victims v ON Process.ID = v.ID
        CROSS APPLY Deadlock.Process.nodes('inputbuf') AS Input (Buffer)
        CROSS APPLY Deadlock.Process.nodes('executionStack') AS Execution (Frame)
)
-- get the columns in the desired order
SELECT  p.Victim,
        p.LockMode,
        LockedObject = NULLIF(l.ObjectName, ''),
        l.database_id,
        l.AssociatedObjectId,
        LockProcess = p.ProcessID,
        p.KPID,
        p.SPID,
        p.SBID,
        p.ECID,
        p.TranCount,
        l.LockEvent,
        LockedMode = l.LockMode,
        l.WaitProcessID,
        l.WaitMode,
        p.WaitResource,
        l.WaitType,
        p.IsolationLevel,
        p.LogUsed,
        p.ClientApp,
        p.HostName,
        p.LoginName,
        p.TransactionTime,
        p.BatchStarted,
        p.BatchCompleted,
        p.InputBuffer
FROM
    Locks l
    JOIN Process p ON p.ProcessID = l.LockProcessID
--WHERE p.TransactionTime > '2013-10-01'
ORDER BY p.Victim DESC,
        p.ProcessId;


How to make SQL query to calculate the range from permutation of 123

Maybe this:

WITH Nums(Num) AS (
    SELECT 1 UNION SELECT 2 UNION SELECT 3
)
SELECT *
FROM Nums x
CROSS JOIN Nums y
CROSS JOIN Nums z
WHERE x.Num <> y.Num
AND x.Num <> z.Num
AND y.Num <> z.Num
ORDER BY x.Num, y.Num, z.Num

Assumes you are using mysql 8+ with CTEs. Also I have not tested this on mysql, only SQL Server, but hopefully the syntax is the same. Also note your 2nd to last and last results appear to be out of order, unsure if this was intentional.


Is it possible to clean up duplicate entries with just SQL?

Your data sample seems to suggest that the two datetime columns are the only columns that can change in each group of duplicates. In other words, duplicates are determined by columns player_id, season_id, playlist_id, matches_played, rank_points, tier, division.

One other assumption that your data sample prompts is that a row with a greater timestamp corresponds to a row with a greater no_update_at as well. (In fact, the two columns always have the same value in your example.)

Finally, for the purpose of this answer, I am also going to assume that when you are saying "first" or "last" (row in the rectangle), you are talking about the earliest or latest datetime value.

With those out of the way, you can generate the desired result set from your table if you just group the rows by the columns listed above and take MIN(timestamp) for timestamp and MAX(no_update_at) for no_update_at – that is, like this:

SELECT
  player_id,
  season_id,
  playlist_id,
  MIN(timestamp) AS timestamp,
  matches_played,
  rank_points,
  tier,
  division,
  MAX(no_update_at) AS no_update_at
FROM
  graph_player_ranked
GROUP BY
  player_id,
  season_id,
  playlist_id,
  matches_played,
  rank_points,
  tier,
  division
;

You could store that in a temporary table:

CREATE TEMPORARY TABLE tmp_graph_player_ranked
AS
SELECT
  ...
;

then truncate the source:

TRUNCATE graph_player_ranked;

and populate it from the temporary table:

INSERT INTO
  graph_player_ranked
    (
      player_id,
      season_id,
      playlist_id,
      timestamp,
      matches_played,
      rank_points,
      tier,
      division,
      no_update_at
    )
SELECT
  player_id,
  season_id,
  playlist_id,
  timestamp,
  matches_played,
  rank_points,
  tier,
  division,
  no_update_at
FROM
  tmp_graph_player_ranked
;

Of course, if the table is referenced by other tables, you cannot simply remove all data – you will probably need to disable or remove the foreign keys before re-populating the table and enable or recreate them afterwards. (That is an entirely different problem that would need to be addressed separately, in case you need help with it.)


PHP MYSQL Update Two Tables Using Cross Joins

You can do an UPDATE of multiple tables in a single UPDATE statement, if that is really what you want to do here. It would be something like this for a specific 'rest' row (PNR = 5):

UPDATE res
INNER JOIN rest ON res.id = rest.PNR and res.TRAIN_NUMBER=rest.Tr_Num
SET
  res.book = res.book + 1,
  rest.age = 21
WHERE (rest.class ='AC I' OR rest.class ='AC II' OR rest.class ='AC III') AND rest.PNR = 5;

However, and maybe I misunderstand, but I suspect what you really want to do is actually an INSERT + an UPDATE in a single transaction. You want to INSERT a new row into 'rest', and then update the relevant numbers in 'res'.

To do this, turn off autocommit in PHP, then INSERT INTO rest ... and UPDATE res ..., and then execute a COMMIT. Make sure to catch any DB-related exceptions, and handle them by executing ROLLBACK instead of COMMIT.

This way, the operation is atomic. Other DB sessions looking at the tables will always see the results of either none or all of the updates in the transaction (assuming you're not using the non-default READ UNCOMMITTED transaction isolation level).

There is not really a good way to do an INSERT and an UPDATE in a single query, except you could wrap the two statements in a stored procedure, and then call that stored procedure to execute both statements in one go. Refer to the documentation for the CREATE PROCEDURE syntax for details and/or see e.g. a stored procedure tutorial at w3resource.com. Note that it's still a good idea to wrap the two statements in a transaction inside the stored procedure.


How to force mysqldump to produce /*!40101 SET character_set_client = utf8mb4 */;

I have checked out mysql-5.4.46, and searched client/mysqldump.c for "SET character_set_client".

It finds

[:~/Source/mysql-server/client]↥ 9c3a49ec84b* ± grep 'SET character_set_client' mysqldump.c
          "/*!50003 SET character_set_client  = %s */ %s\n"
          "/*!50003 SET character_set_client  = @saved_cs_client */ %s\n"
                  "SET character_set_client = utf8;\n"
                  "SET character_set_client = @saved_cs_client;\n");
                "/*!40101 SET character_set_client = utf8 */;\n"
                "/*!40101 SET character_set_client = @saved_cs_client */;\n",
            "/*!50001 SET character_set_client      = %s */;\n"
            "/*!50001 SET character_set_client      = @saved_cs_client */;\n"

From this, it seems that some instances of "SET character_set_client" are fixed at utf8 in this version and are not even variable.

One of them is the /*!40101 SET character_set_client = utf8 */; line you quote as being faulty.

After checking out current 8.0 HEAD, I get

[:~/Source/mysql-server/client]↥ 8.0* ± grep 'SET character_set_client' mysqldump.cc
          "/*!50003 SET character_set_client  = %s */ %s\n"
          "/*!50003 SET character_set_client  = @saved_cs_client */ %s\n"
                  "/*!50503 SET character_set_client = utf8mb4 */;\n"
                  "SET character_set_client = @saved_cs_client;\n");
              "/*!50503 SET character_set_client = utf8mb4 */;\n"
              "/*!40101 SET character_set_client = @saved_cs_client */;\n",
        "/*!50001 SET character_set_client      = %s */;\n"
        "/*!50001 SET character_set_client      = @saved_cs_client */;\n"

So while there are still fixed SET character_set_client statements, they now default to utf8mb4. Note that all other statements are %s-ed, and further checking shows they defer to whatever you --default-character-set=utf8mb4 in your commandline.

My suggestion is you dump, possibly remotely, with a newer mysql 8.0 binary. In general, upgrading is adviseable, as 5.6 is an outdated unsupported version of the database (yes, I know, it's complicated. It always is.)


Not true, unless they meant "materialised views"

ORAFAQ

  Permanent tablespaces are used to store user data and user created objects like tables, indexes and materialized views.


MySQL Trigger: Insert depending on NEW.column of type enum

I had to use delimiter to make it work but because delimiter is a mysql-console command it didn't work by putting it in my script.

phpMyAdmin has a different way to set a delimiter. Below of the script-input there is another input to set a delimiter so at then and my script looked like this:

CREATE TRIGGER insert_after_new_kpi_trig
    AFTER INSERT ON KPI
    FOR EACH ROW
    BEGIN
    IF NEW.kpi_type = 3 THEN BEGIN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projects p;
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams t;
        END;
    ELSEIF NEW.kpi_type = 1 THEN
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams t;
    ELSEIF NEW.kpi_type = 2 THEN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projectsp;
    END IF;
    END $$ --Thats the delimiter I set.


It looks like replication was not set up properly; I'm guessing that that means that when a transaction gets written to the log and replication fails, the log entry doesn't get marked as ready-to-be-cleaned-up, so the entries just pile up and the log utilisation goes up with it.
I deleted all replication configuration, and the log file utilisation dropped from 99% to 0.1%, so now I can drop the log file back to something more manageable.


Is SQL*Net message from dblink > 30% a performance issue?

Most likely means that the the shadow process(es) that support the dblink connection(s) are idle. Well they are definitely idle. The only question is are these idle periods between cursors or within an executing cursor. If they are within an executing cursor then whatever is driving the cursor execution, the fetching, is slow. But odds are it's simply an idle dblink connection. See http://kylehailey.com/oracle-sqlnet-wait-events/


Finding cause of high write IOPS on RDS Postgesql

To log autovacuum activity you can set log_autovacuum_min_duration parameter:

  Causes each action executed by autovacuum to be logged if it ran for
  at least the specified number of milliseconds. Setting this to zero
  logs all autovacuum actions. Minus-one (the default) disables logging
  autovacuum actions. For example, if you set this to 250ms then all
  automatic vacuums and analyzes that run 250ms or longer will be
  logged. In addition, when this parameter is set to any value other
  than -1, a message will be logged if an autovacuum action is skipped
  due to the existence of a conflicting lock. Enabling this parameter
  can be helpful in tracking autovacuum activity. This parameter can
  only be set in the postgresql.conf file or on the server command line;
  but the setting can be overridden for individual tables by changing
  table storage parameters.

One possible cause could also be temporary files used by some queries for sorting and/or joins: you can try to check in  pg_stat_database view columns temp_files and temp_bytes . You can also set log_temp_files parameter to 0 (instead of default -1) to log all temporary files creation:

  Controls logging of temporary file names and sizes. Temporary files
  can be created for sorts, hashes, and temporary query results. A log
  entry is made for each temporary file when it is deleted. A value of
  zero logs all temporary file information, while positive values log
  only files whose size is greater than or equal to the specified number
  of kilobytes. The default setting is -1, which disables such logging.
  Only superusers can change this setting.


Query to get the physical file name of the database

You can try this.

@Stringss is a variable to simulate the field you already obtained

DECLARE @String NVARCHAR(MAX)='S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'

SELECT REVERSE(SUBSTRING(REVERSE(@String),0,CHARINDEX('\',REVERSE(@String),0)))


Search between dates where all dates appear

I believe you want to get house_id where date exists for each day between @startDate and @finalData.

Sample queries with 3 different house_id: SQL Fiddle.

Query:

SET @startDate := CAST('2016-02-01' as date);
SET @finalDate := CAST('2016-02-04' as date);

SELECT house_id
    , COUNT(*)
    , DATEDIFF(@startDate, @finalDate)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
GROUP BY house_id;

Query 1 in SQL Fiddle.

This query GROUP BY house_id and output the COUNT of days between @startDate and @finalData:

house_id    COUNT(*)    DATEDIFF(@startDate, @finalDate)
1           3                       3
2           2                       3
3           3                       3

From this query, it is obvious that only rows where COUNT(*) = DATEDIFF(@startDate, @finalDate) should be returned.

If (house_id, date) is not unique, COUNT(*) must be replaced by COUNT(DISTINCT `date`).

Query with all days

If you only want house_id where COUNT(*) = DATEDIFF(day, @startDate, @finalDate), the query must use the HAVING clause:

SELECT house_id
    , COUNT(*)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
GROUP BY house_id
HAVING COUNT(*) = DATEDIFF(@finalDate, @startDate)

Query 2 in SQL Fiddle.

Output:

house_id    COUNT(*)
1           3
3           3

Query where status = X

Status value can be checked in the WHERE clause:

SELECT house_id
    , COUNT(*)
FROM @tests AS T1
WHERE `date` > @startDate AND `date` <= @finalDate
    AND status = '1'
GROUP BY house_id
HAVING COUNT(*) = DATEDIFF(@finalDate, @startDate)

Query 3 in SQL Fiddle.

Output:

house_id    COUNT(*)
1           3


Improve execution time of query in PostgreSQL with multiple joins

Try going with an EXISTS test. First, let's be explicit about where the columns are coming from, also you don't need col = TRUE and col = FALSE on bools. You can simply say WHERE col or WHERE NOT col, I think that's a bit cleaner.

SELECT w.contacturl, w.url
FROM websites AS w
WHERE w.contactable
  AND NOT w.contacted
  AND w.url IN (
    SELECT s.url
    FROM scans AS s
    WHERE s.scanid IN (
      SELECT distinct(m.scanid)
      FROM misspelled AS m
      WHERE m.word = ?
    )
  )
;

Now, with EXISTS,

SELECT w.contacturl, w.url
FROM websites AS w
WHERE w.contactable
  AND NOT w.contacted
  AND EXISTS (
    SELECT 1
    FROM scans AS s
    WHERE s.scanid IN (
      SELECT distinct(m.scanid)
      FROM misspelled AS m
      WHERE m.word = ?
    )
    AND w.url = s.url
  )
;

But you can do even better. DISTINCT just reduces duplicates for IN. With EXISTS duplicates are simply ignored.

SELECT w.contacturl, w.url
FROM websites AS w
WHERE w.contactable
  AND NOT w.contacted
  AND EXISTS (
    SELECT 1
    FROM scans AS s
    WHERE w.url = s.url
    AND EXISTS (
      SELECT 1
      FROM misspelled AS m
      WHERE m.scanid = s.scanid
      AND m.word = ?
    )
  )
;

That should be a lot faster, normally.


Index fragmentation while continuously processing

What you're doing is you're using a table as a queue. Your update is the dequeue method. But the clustered index on the table is a poor choice for a queue. Using tables as Queues actually impose quite stringent requirements on the table design. Your clustered index must be the dequeue order, in this case likely ([DataType], [DataStatus], [ProcessDate]). You can implement the primary key as a nonclustered constraint. Drop the non-clustered index Idx, as the clustered key takes its role.

Another important piece of the puzzle is to keep the row size constant during processing. You have declared the ProcessThreadId as a VARCHAR(100) which implies the row grows and shrinks as is being 'processed' because the field value changes from NULL to non-null. This grow-and-shrink pattern on the row causes page splits and fragmentation. I can't possibly imagine a thread ID that is 'VARCHAR(100)'. Use a fixed length type, perhaps an INT.

As a side note, you do not need to dequeue in two steps (UPDATE followed by SELECT). You can use OUTPUT clause, as explained in the article linked above:

/**************************************/
CREATE TABLE [Table]
(
    [PrimaryKeyId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
    [ForeignKeyId] [INT] NOT NULL,
    /* more columns ... */
    [DataType] [CHAR](1) NOT NULL,
    [DataStatus] [DATETIME] NULL,
    [ProcessDate] [DATETIME] NOT NULL,
    [ProcessThreadId] INT NULL
);

CREATE CLUSTERED INDEX [Cdx] ON [Table]
(
    [DataType],
    [DataStatus],
    [ProcessDate]
);
/**************************************/

declare @BatchSize int, @ProcessThreadId int;

/**************************************/
WITH cte AS (
    SELECT TOP (@BatchSize) [PrimaryKeyId], [ProcessThreadId] , ... more columns
    FROM [Table] WITH ( ROWLOCK, UPDLOCK, READPAST )
    WHERE [DataType] = 'X'
    AND [DataStatus] IS NULL
    AND [ProcessDate] < DATEADD(m, -2, GETDATE()) -- older than 2 months
    AND [ProcessThreadId] IS NULL
)
UPDATE cte
SET [ProcessThreadId] = @ProcessThreadId
OUTPUT DELETED.[PrimaryKeyId] , ... more columns ;
/**************************************/

In addition I would consider moving successfully processed items into a different, archive, table. You want your queue tables to hover near zero size, you do no want them to grow as they retain 'history' from unneeded old entries. You may also consider partitioning by [ProcessDate] as an alternative (ie. one current active partition that acts as the queue and stores entries with NULL ProcessDate, and another partition for everything non-null. Or multiple partitions for non-null if you want to implement efficient deletes (switch out) for data that has passed the mandated retention period. If things get hot you can partition in addition by [DataType] if it has enough selectivity, but that design would be really complicated as it requires partitioning by persisted computed column (a composite column that glues together [DataType] and [ProcessingDate]).


Ordering with custom paging when using pivot to convert a query

Mikael's question helped me immensely.

I was not paying attention to the OVER clause which has an OrderBy.

Altering that got the ordering working correctly with the custom paging.

So I replace the line:

SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER(ORDER BY ID) Corr

with the line:

SELECT *, ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ' ' + @sortDir + ') Corr