How to populate additional rows after some calculation or addition

It would help if you could give us the DDL and insert statements for your data.

I have made a test setup with records available for weight 100 and 50, and wrote a generator function that includes new records for weight between 101 and 10000, but only execute it for weight 350 and category 1 (else I need too much additional samples available).

Fiddle SQL-Server 2017

Start

Weight  Cost    CategoryId
   100   600             1
    50   400             1

End

Weight  Cost    CategoryId
   100   600             1
    50   400             1
   350  2200             1            -- 2200 = 3*600+400

SQL:

    with
      x (n) as (
            select cast(101 as decimal(8,1)) union all
            select (cast(x.n + 0.5 as decimal(8,1))) n from x where x.n <= 350 /*10000*/
    )
    , y (m) as (
            select 1 union all select (y.m + 1) m from y where y.m <= 1 /*10*/
    )
    , input as (
            select n, cast(floor(n)/100 as int) h, n%100 u, m from x
            cross join y
    )
    insert into table1
    (
        Weight,
        Cost,
        CategoryId
    )
    select
        i.n Weight,
        (i.h * t1.Cost + t2.Cost) Cost,
        i.m CategoryId
    from input i
    inner join table1 t1 on t1.CategoryId = i.m and t1.Weight = 100
    inner join table1 t2 on t2.CategoryId = i.m and t2.Weight = i.u
    -- TEST
    where i.n = 350
    option (maxrecursion 0);


Can NOLOCK in a stored procedure be overridden when executing it?

No, query-level hints and options override session and server-level settings. Basically, a hint applied at the query level is the lowest common denominator, and wins (except in the case of MAXDOP and Resource Governor, but that's slightly different than what we're talking about here anyway).

This used to be documented for SQL Server 2000, but this verbiage has been removed from more modern versions of the documentation.


Here is my recommended approach:

On the tablet, split into two databases. One is the master copy, the other is the local changes.
The tablet considers the master copy to be read-only
At sync time, e.g. when the network is available, you simply ditch the master and reload it from the "real" master that is running on your website
But before doing this, you load all the rows from the local changes, into your real master. After you have done this, you delete all the local changes from the tablet.
You will need to ensure no clashes of primary keys for new data, and you will need to build an app so that users can reconcile conflicting changes to existing data
If you consider this a master-master or master-slave situation and try to make it work with the traditional MySQL replication tools, you are opening the door to a world of pain
However if you are feeling ambitious enough to re-architect everything, you could so something very elegant with messaging/queuing middleware - this is probably how you would have done it if you are starting from scratch, my suggestions are around retro-fitting this onto what you already have.


If you don't have privileges to DROP table you should DELETE in chunks.

Use MySQL Limit clause to delete in chunks.

DELETE FROM TABLE_NAME LIMIT 10000;


Login in server suddenly lost mapping to their Users in databases

Since an Availability Group is just a container of databases, you should treat users and logins the same way you would for a mirrored database, log shipping, etc. And that is that the logins on a secondary server must be created with the same explicit SID as the logins on the primary server. Otherwise the link between the server-level login and the database-level user will be broken upon failover - which I suspect is what happened here (I'm not going to try to retrace all of your steps and figure out how many times you failed over and back - let's focus on preventing this from happening again).

You say that you re-synced with the SIDs, but I don't know what that generic phrase means. You should include the code that demonstrates what you did, because if it isn't doing what you think it's doing, we may be able to tell you why, but we can't do that with just hand-waving.

The script I think you should be using is the one from Robert Davis, which Mike Walsh describes here (I'm not going to copy the code here):

Transferring Logins to a Database Mirror

There is also an SSMS add-in written by Jonathan Kehayias that helps automate synchronization of things like logins and jobs across all the nodes involved with an availability group:

Synchronize Availability Group Logins and Jobs
SQL Server Management Studio Availability Group Add-in

However, this add-in only works in SSMS 2012/2014.

Length of time is irrelevant here. You had a year of uptime. Yay! You could failover tomorrow and be right back in the same boat.


changing float->numeric casts from assignment to implicit, dangerous?

Please be aware that different rounding algorithms are used for different data types.

https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-TABLE

  When rounding values, the numeric type rounds ties away from zero,
  while (on most machines) the real and double precision types round
  ties to the nearest even number. For example:

SELECT x,
  round(x::numeric) AS num_round,
  round(x::double precision) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) as x;
  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0
  0.5 |         1 |         0
  1.5 |         2 |         2
  2.5 |         3 |         2
  3.5 |         4 |         4


SQL Server restore database with active connections

You must switch to single user mode:

-- Close all connections and rollback all transaction
ALTER DATABASE Test_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

RESTORE FILELISTONLY
   FROM disk = '\\staging_server\path\to\db_backup.bak'
-- Restore the files for
RESTORE DATABASE Test_DB
   FROM disk = '\\staging_server\path\to\db_backup.bak'
   WITH replace,
   MOVE 'Test_DB' TO 'D:\R2_BIN\Test_DB.mdf',
   MOVE 'Test_DB_log' TO 'D:\R2_BIN\Test_DB_log.ldf',
   stats = 5
GO

ALTER DATABASE Test_DB SET MULTI_USER;
GO


SIZE is no valid input at this position error in MySQL 5.7

Function need a static definition of the returning type. Size of VARCHAR should be declared explicitly like VARCHAR(50).


Which is better: Selecting across linked servers vs migrating the database

You'll likely get the best performance by copying the content of the remote table locally first, then running the query.  Especially if you filter the remote table through a remote view or a stored proc on the remote server.

Joining across a linked server can be brutal on performance depending on the implementation details of the query.


Can different tables have columns with the same name if those columns are not primary keys?

SQL employs a concept known as domain name integrity which means that the names of objects have a scope given by their container.

Column names have to be unique, but only within the context of the table that contains the columns. Table names have to be unique, but only within the context of the schema that contains the tables, etc.

When you query columns you need to reference the schema, table and column that you are interested in, unless one or more of these can be inferred. When you write a query, you need to reference the tables in your query by name directly or by using an alias, e.g. Customer.ID or C.ID from Customer C, etc., unless your query is so simple that it only references one table.

There was a time when there was a technical requirement for uniqueness of all column names, which applied to old ISAM databases and to languages like COBOL in the 1960s and 70s. This got dragged along for no good reason into dBase in the 1980s and has stuck as a convention well into the relational and object DBMS eras. Resist this outdated convention.

When the shift from flat file and network databases to relational databases happened in the 1970s and 80s, the idea of joining tables was new.  So some people chose the convention that a unique name could be repeated if one column was a reference to another (i.e. foreign keys).  This concept is called a "natural join" and a lot of people still advocate for doing this.

I am not a fan of natural joins because it requires you, ultimately, to throw out the concept of domain name integrity and force the whole column reference into the column name.

The issue with natural joins is that you either have to be hypocritical or you have to make your column names long and unreadable.  Let me illustrate: It may sound like a good idea that the primary key of the Customer table is CustomerID.  Then in your Invoice table, your foreign key to Customer is also called CustomerID.  This would be a natural join and it all sounds good so far.  Here is the problem.  What if your convention is to have a column on every table called LastUpdatedDate?  So are you meant to join every table to every other table by LastUpdatedDate?  Of course not.  This is the absurdity of natural joins.  In order to avoid this absurdity you would need to jam the table name into the column name as a prefix.  However, if you have multiple schemas in your database, you can't stop there.  You also need to add the schema to the column name, and so it goes.

Another place where natural joins break down is when you have multiple relationships between the same two tables.  You if you need two references to Employee on your Invoice table (Sold By and Approved By, for example) you can't call them both EmployeeID.


How to set ENVREFERENCE when creating a SQL Server job

I've used code like the following in my job scripts to associate the step with the proper environment:

--Call SSIS package. Figure out the server and Environment Reference variables
DECLARE @EnvReference   INT = (SELECT TOP 1 EnvironmentReferenceID FROM SSISDB.rpt.Package WHERE PackagePathName = '\SSISDB\Folder\Project\Package.dtsx\');
DECLARE @Command        VARCHAR(2000) = FORMATMESSAGE(N'/ISSERVER "\"\SSISDB\Folder\Project\Package.dtsx\"" /SERVER %s /ENVREFERENCE %i /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E', @@SERVERNAME, @EnvReference);
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Name',
                @step_id=1,
                @on_success_action=3,
                @on_fail_action=3,
                @subsystem=N'SSIS',
                @command=@Command,
                @database_name=N'master',
                @output_file_name=N'E:\Log\Job.log',
                @flags=40;

By the way the view rpt.Package is just this:

SELECT  PackagePathName = FORMATMESSAGE('\SSISDB\%s\%s\%s\', F2.name, PJ.name, PK.name),
    EnvironnmentPathName = FORMATMESSAGE('\SSISDB\%s\%s', F.name, E.name),
    EnvironmentReferenceID = ER.reference_id,
    ProjectFolder = F.name,
    Project = PJ.name,
    Package = PK.name,
    EnvironmentFolder = F2.name,
    Environment = E.name
FROM    catalog.folders AS F
    INNER JOIN catalog.environments AS E ON E.folder_id = F.folder_id
    INNER JOIN catalog.environment_references AS ER ON (ER.reference_type = 'A'
            AND ER.environment_folder_name = F.name
            AND ER.environment_name = E.name)
        OR (ER.reference_type = 'R'
            AND ER.environment_name = E.name)
    INNER JOIN catalog.projects AS PJ ON PJ.project_id = ER.project_id AND PJ.folder_id = F.folder_id
    INNER JOIN catalog.packages AS PK ON PK.project_id = PJ.project_id
    INNER JOIN catalog.folders AS F2 ON F2.folder_id = PJ.folder_id

Make sure to replace the references: Folder, Project, Package, Job with your own strings.


Is ORDER BY on two indexed columns will be slow?

If ID is the clustered index, on SQL Server this index will have ID added as the last index key.

For a non-unique non-clustered index on a clustered index table the clustered index keys that aren't explicitly listed as non-clustered index keys will be added as additional non-clustered index keys. And the non-clustered index will actually be stored as a unique index.  EG your index on (a,b,c,d) is implemented as a unique index on (a,b,c,d,id).

If the non-clustered index is unique, the clustered index keys will be present on the leaf level of the index, but will not be index keys.


How can I write a query to recursively execute with varying parameter values?

You can use an INLINE USER DEFINED FUNCTION for this purpose.

Have a look at Inline UDF rules:

The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable, because it is set by the format of the result set of the SELECT statement in the RETURN clause.
There is no function_body delimited by BEGIN and END.
The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
The table-valued function accepts only constants or @local_variable arguments

CREATE TABLE SomeTable(ID int, SomeDate datetime);

INSERT INTO SomeTable VALUES
(1, '20170301'),(1, '20170305'),(1, '20170310'),(1, '20170315'),(1, '20170320'),(1, '20170325'),(1, '20170329'),
(1, '20170401'),(1, '20170404'),(1, '20170412'),(1, '20170418'),(1, '20170422'),(1, '20170424'),(1, '20170430'),
(2, '20170303'),(2, '20170308'),(2, '20170312'),(2, '20170319'),(2, '20170323'),(2, '20170325'),(2, '20170330'),
(2, '20170405'),(2, '20170406'),(2, '20170412'),(2, '20170418'),(2, '20170422'),(2, '20170424'),(2, '20170430');
GO

CREATE FUNCTION dbo.fnCount(@MinDate datetime)
RETURNS TABLE
AS
RETURN
    SELECT   ID, COUNT(ID) NumID
    FROM     SomeTable
    WHERE    SomeDate > @MinDate
    GROUP BY ID
GO

DECLARE @XDays Datetime = '20170401';
DECLARE @YDays Datetime = '20170308';

SELECT t1.ID, t1.NumID as [X-Days], t2.NumID as [Y-Days]
FROM   dbo.fnCount(@XDays) t1
JOIN   dbo.fnCount(@YDays) t2
ON     t1.ID = t2.ID
GO

ID | X-Days | Y-Days
-: | -----: | -----:
 1 |      6 |     12
 2 |      7 |     12

dbfiddle here


Query takes too much time (2 stages. each stage fast, together - nightmare)

You could insert the rows from the subquery into a temporary table, then JOIN against that table.  Something like:

SELECT min_event_no = MIN(event_no)
INTO #events
FROM t_events WITH (NOLOCK)
WHERE order_no = 2783190
    and complaint_id = 80898;

SELECT *
FROM t_events WITH (NOLOCK)
INNER JOIN #events e ON t_events.event_no = e.min_event_no
WHERE order_no = 2783190

This makes SQL Server perform the two operations independently, which may make this query faster.  Potentially you could add appropriate indexes, but without the table definition in your question it's impossible to say.

Be aware of potential issues arising from the use of WITH (NOLOCK)


SQL Server Replication can in some situations hide errors from you. The behavior you are seeing could for example be caused by the snapshot agent not finishing, likely because of an incorrectly configured permission set.

Check out my Stairway to Replication here: http://www.sqlservercentral.com/stairway/72401/

Levels 1 - 5 give a solid foundation to the security requirements when setting up replication.
Level 10 describes how to troubleshoot a replication setup. This includes information about where to find error messages and how to interpret them.


How to check if a column in a query can produce NULL?

Since SQL (with recursive CTEs) is Turing complete, figuring out if a column in the result could be NULL or not is probably as complex as the halting problem and cannot be computed.
So either run the query and examine the result or restrict yourself to very simple queries (but even then the problem will be non-trivial).
My personal recommendation is to give up.


Deleting rows from big table: what then?

Rebuild the table

ALTER TABLE yourtablename REBUILD


Converting an SVN repository to SQL database

The command line SVN client can export the log to an XML file with the --xml option.

http://svnbook.red-bean.com/en/1.5/svn.ref.svn.c.log.html


SELECT sql_function(...) vs SELECT * FROM sql_function(...)

You typically use a function in FROM if it is a set-returning function (SRF). Such functions return a result set rather than a single value or tuple.
But, confusingly, this is not written in stone in PostgreSQL:

You can also call a SRF in the SELECT list, as in SELECT f().
As the documentation says:

Functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the set-returning function is invoked, and an output row is generated for each element of the function's result set.

You can use any function in the FROM clause. It is then treated as a SRF that returns only a single row.


Combining a query for value and an insert MySQL

Use INSERT .. SELECT instead of INSERT .. VALUES:

INSERT INTO accounts (name       , login       , ..., rank        , ...)
SELECT                'some name', 'some login', ..., ranks.rankID, ...
FROM ranks
WHERE ranks.name = 'some rank' /* ORDER BY ranks.somefield LIMIT 1 */

The values You need to insert directly (name, login, etc.) are added into SELECT part literally, the data obtained from ranks table is selected by common way. If there exists more than 1 record in ranks appropriate to criteria You use - add ordering and limiting to select (and add into destination table) one record only.


Set uncommitted read as default in Oracle SQL Developer with DB2 database

There are a couple of ways to achieve this. Firstly, you can simply run the statement

SET CURRENT ISOLATION UR

in your session before executing other statements.

Alternatively, you can set a property in the JDBC URL you use to connect to the database, like so:

jdbc:db2://host:port/database:defaultIsolationLevel=1;

The value 1 corresponds to the constant java.sql.Connection.TRANSACTION_READ_UNCOMMITTED (note that it is implementation-dependent and may break).


SQL Schema Change; Modification of Existing Table vs Creation of New

If your changes are not frequent and your parent table does not have a lot of data, then you could just update your schema on that table.

However, potential downsides are changes in the schema could propagate to changes in stored procedures and views.  This could be because of adding columns or removing columns.

(Of course, any change made in a database schema, will have some ripple effects that must be coped with.)

I like the parent table to be as stabile as possible.  Having a related table or tables to provide new requirements is a good general rule according to how I look at schemas.

But it is definitely an opinion-based choice that will depend on what you are most comfortable with doing.


Slow Parallel SQL Server query, almost instant in serial

Is there a way to fix this without changing the code?

Create the following index to eliminate the eager index spools:
-- Give this index a better name
CREATE INDEX i ON dbo.TABLE2 (COLUMN4, COLUMN7) INCLUDE (COLUMN3);

With that in place, you should get a plan similar to:

The other problems described in the question will almost certainly disappear once you give the optimizer good indexes.
It is possible (though unlikely) your query suffers from an undetected intra-query parallelism deadlock (IQPD). A very detailed investigation would be needed to confirm that. The vast majority of IQPDs are detected and can then be resolved by spilling exchange buffers to tempdb. An undetected IQPD will result in the query being stuck forever.


How to work around Sequelize not supporting composite foreign keys

I have never seen Sequelize, but your error reads:

 TypeError: Cannot read property 'toUpperCase' of undefined

Where it says of undefined I would suggest your code is trying to convert a string to uppercase, but in fact, your string is null and the conversion (toUpperCase) fails on null.

That said, I don't see toUpperCase in the query you supplied, but you have the line model:invitations - does this mean that your query is acting on some data structure (table?) called 'invitations'?

If so, I would check there for the toUpperCase function. For each instance of the function, see whether it is being called on something that might have a null value. If so, then somehow ensure you don't get a null value - for example, add filter criteria to exclude null values, or use a casting function like isNull (if it exists for you) or coalesce to replace nulls with an empty string, like this:

 coalesce(columnName, '')

Failing the above, post a little more info on what your data query really looks like, and why you think the composite foreign keys may relate to the error.


Time to apply transaction logs: does it matter how many logs?

There is a very small amount of overhead associated with reading headers from backup files.  The more backup files, the more overhead needed; however the amount of data actually read for each header is relatively small. The actual rollback/roll-forward work performed by SQL Server would be no different - SQL Server looks at the transactions in the restore file, and applies them to the database.

If your database has a lot of transactions per second, then each log backup will have a fair number of transactions within it, and the above logic applies.  If, however, you have a very low number of transactions over the 5 minute period, then the amount of data in any given 5-minute log backup would be quite small, and having lots of small log backups might increase the time needed for restore.

Having transaction log backups taken every 5 minutes, instead of every hour, gives you more up-to-date backups.  You could conceivably take that a bit too far, by say, doing a BACKUP LOG every 15 seconds.  This would create a huge number of backup files that would result in a myriad of other issues such as time required to read the file system structures for the folder containing the backups.  The key here is to use a bit of common sense, and balance the need for recent backups against the need to keep the number of files as small as you can.


This parse node based XML. It's different to read attributes but it isn't as common

I had this lying around as a demo with 3 slightly different XPath queries

DECLARE @foo XML

SELECT @foo = N'
<harrys>
    <harry>
        <fish>0.015000000000</fish>
        <bicycle>2008-10-31T00:00:00+01:00</bicycle>
        <foo>ü</foo>
    </harry>
    <harry>
        <fish>0.025000000000</fish>
        <bicycle>2008-08-31T00:00:00+01:00</bicycle>
        <foo>ä</foo>
    </harry>
</harrys>
'

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/*') x(item)
    CROSS APPLY
    x.item.nodes('./*') AS y(item)

SELECT
    CAST(CAST(x.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(x.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(x.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('harrys/harry') x(item)

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/harrys') x(item)
    CROSS APPLY
    x.item.nodes('./harry') AS y(item)


MySQL - MySqlDump uses \n instead of null

There are no options for handling the output of NULLs.
You may want to experiment with --hex-blob. This will dump BINARY, VARBINARY, BLOB fields in hexadecimal format. This should make the data portable. You can see what this option produces for NULL values.
mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n

Give it a Try and let us all know if this helps.
UPDATE 2012-01-13 18:26 EDT
This may sound very gross but you can pipe the output of mysqldump into sed as follows:
mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n | sed 's/\\N/NULL/g'

Of course, you cannot import that into MySQL until you want the string NULL to be the value to be imported.


Why would a single ghost record remain in a few random indexes?

You can find the answer in this article Inside the Storage Engine: Ghost cleanup in depth

When a record is deleted, apart from it being marked as a ghost
record, the page that the record is on is also marked as having ghost
records in one of the allocation maps – the PFS page (post coming
soon!) – and in its page header. Marking a page as having ghost
records in a PFS page also changes the database state to indicate that
there are some ghost records to cleanup – somewhere. Nothing tells the
ghost cleanup task to clean the specific page that the delete happened
on – yet. That only happens when the next scan operation reads the
page and notices that the page has ghost records.

So it's not cleaned up until the page is read again


Does Postgresql apply the same type of locks on UPDATE with range condition and on UPDATE with in-set condition?

Yes, both statements will take a UPDATE lock on all the modified rows.

See the documentation on row locks:

  The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns.


How do I recover deleted rows from one table while having Recovery SIMPLE Model? I am using Microsoft SQL Server 2014

I don't think, you get any recommendation on fn_dblog as it's undocumented. So got following options:

Recent Full Backup: Restore recent full backup as copy without overwriting existing database then you should be able to recover particular table or rows within table.
Third-party tools: you can use third-party tools that can directly read your transaction log to recover data from particular transaction, you need to take database offline to read .log file by tool. However, chances are less in this method since the recover model is SIMPLE which truncates the log automatically


Data Collection reports fail because login is from an untrusted domain

I have confirmed this is an issue with how the reports get executed on the server -- I reproduced the issue exactly by using two virtual machines, neither of which was in a domain.

Moreover, I created a test user (member of the Users built-in group and nothing more) on the SQL box, ran Management Studio locally as that user, logged in with sa, and attempted to run the report. As I expected, it failed, and returned the error Failed to connect to server MDWTestSQL. --> Login failed for user 'MDWTESTSQL\TestUser'.

This is clear indication that the reports are implemented to only use Windows authentication for some portion of it. Therefore, sufficient SQL-level permissions must be granted to the Windows principal running Management Studio, even if you authenticate to the server using a SQL login.

There are two workarounds:

Use pass-through authentication: create a matching (user name and password) local login on the workstation, and impersonate the local login to run Management Studio remotely. (I tested that this works.)
Use pass-through authentication by creating new matching local accounts on both boxes, and set up SQL permissions for the new account.

As I mentioned in chat, this problem is "fixed" in the 2012 Management Studio -- the reports in the menu are hidden if the Windows account doesn't have permissions to run the reports.


Is MAX(rowversion) optimized for the table?

No, it doesn't appear there is any special treatment for rowversion columns.  This small demo confirms the entire index is scanned to find the MAX value:
CREATE TABLE dbo.RvTest
(
    Id int NOT NULL,
    rv rowversion,

    CONSTRAINT PK_RvTest PRIMARY KEY (Id)
);
GO

INSERT INTO dbo.RvTest
    (Id)
SELECT DISTINCT
    [number]
FROM master.dbo.spt_values v1
WHERE
    v1.[number] IS NOT NULL;
GO

SELECT MAX(rv) FROM dbo.RvTest;

Interactive db<>fiddle link
This makes sense when you consider that the rowversion is unique across the whole database, rather than a specific table:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion.

It doesn't look like SQL Server stores table-specific metadata about rowversions.
If you need that check to be very fast, there's nothing stopping you from indexing that column yourself:
CREATE NONCLUSTERED INDEX IX_rv ON dbo.RvTest (rv DESC);

Which results in a single row being read for that MAX query:

As a side note, as Panagiotis Kanavos has mentioned in the comments, this is usually used for optimistic concurrency checks - to tell if a specific row has changed since the last time it was read.  The way you're using it is still valid (keeping the last maximum, and checking the current max to see if any data in the table has changed), although the built-in Change Tracking feature might be attractive to you as an alternative.


how do you reference server logins from a visual Studio database project?

Right click on references in your SSDT project. Click on Add Database reference.

Fill out the database reference information here. I bring in the server I am connecting to as a dacpac object, then reference it as a project in the current solution. Seems to work best for me.

EDIT: I just tested using a database project in the same solution as well and it resolved the references correctly.

Edit 2: Why this works - Projects within a VS solution do not "talk" to each other, by default. In the real world, servers and databases do "reference" each other. What we are doing here is explicitly making that connection, because the scope of a SSDT project can only be one database. Once you add the SSDT project and sync all the objects, including the security, you need to add that project as a reference in order for it "see" the database objects at the server level.


Move Oracle data to and from MySQL with the help of PHP?

How about if you export your data to an external script file using MySQL dump (you have the options of exporting only the data and not the sturcture), which can be done through PHP and you run another PHP script of load the data into Oracle using straight PHP or an oracle client.

Instead of using MySQL dump you can also generate an SQL file which contains the records which have changed

You can also schedule the jobs using Cron but the import into Oracle job should happen some time, maybe an hour or two depending on the time it takes to generate the dump file.


Ola Hallengren Index Maintenance with compressed indexes

Yes - I don't see how Ola's script could possibly alter the existing compression level of an index.

If you look at the source for Ola's Index Maintenance, you will not see the word COMPRESSION anywhere.  In order to alter the compression level, his script would actually have to have the word COMPRESSION in it.


How to avoid duplicating information in a series of nested SELECT statements?

;WITH x AS
(
  SELECT g.guestid, e.[year], e.run, c = COUNT(*)
  FROM dbo.guest AS g
  INNER JOIN dbo.[order] AS o
  ON g.guestid = o.guestid
  INNER JOIN dbo.eventseat AS es
  ON o.orderid = es.orderid
  INNER JOIN dbo.event2 AS e
  ON e.eventid = es.eventid
  WHERE e.incometype = 'T'
  AND e.eventtype in ('SS', 'BMF')
  AND es.status in ('2','4')
  AND es.price NOT IN ( '0.00', '5.00')
  AND e.[year] = '2010'
  AND e.run IN ('SS-DANCE', 'SS-FILM', 'SS-OPERA')
  GROUP BY g.guestid, e.[Year], e.run
),
y AS
(
  SELECT * FROM x PIVOT (MAX(c) FOR run IN
  ([SS-DANCE],[SS-FILM],[SS-OPERA])) AS z
)
SELECT y.guestid, y.[year], g.[first], g.[last],
  [SS-DANCE] = COALESCE(y.[SS-DANCE], 0),
  [SS-FILM]  = COALESCE(y.[SS-FILM], 0),
  [SS-OPERA] = COALESCE(y.[SS-OPERA], 0)
FROM y
INNER JOIN dbo.guest AS g
ON y.guestid = g.guestid;

I'm not sure if my where clause is going to work for you as written. This currently assumes you only care about these three types (dance, film and opera) and you only care about the year 2010. If you need to cross years you can remove that WHERE clause. If you only care about dance in 2010 and film in any year you'll need to write a more complex set of where clauses.

Some comments:

try to avoid reserved words like order, year, first and last. These only complicate matters because they need to be enclosed in square brackets (which are preferable over "double quotes" mostly for readability and unambiguity).
always use dbo. (or whatever schema prefix is appropriate).
as I suggested above, stick to explicit INNER JOIN syntax. FROM x, y, z is for the birds.


This does not appear to be a conditional.  The extra information was added in v10 with commit 9eb344faf5, and is always printed in that version and up for a VACUUM VERBOSE (it apparently is not printed for a CLUSTER, which does seem undesirable).

If you want consistent behavior, you need to upgrade all your database instances to the same version of PostgreSQL.


Referencing another table from a computed column

You could use an indexed view for this. Assuming that the relationships are simple (foreign key) relationships, I don't see why this would cause any issues.

Simple example, tested in dbfiddle.uk:

create table game
( game_id int not null primary key,
  game_name varchar(100) not null
) ;

create table area
( area_id int not null primary key,
  area_name varchar(100) not null
) ;

create table player
( player_id int not null primary key,
  player_name varchar(100) not null
) ;

insert into game
values
  (1, 'chess'),
  (2, 'go'),
  (3, 'reversi'),
  (4, 'backgammon'),
  (5, 'hex'),
  (6, 'havannah'),
  (7, 'pacman') ;

insert into area
values
  (11, 'usa'),
  (12, 'russia'),
  (13, 'greece'),
  (14, 'uk'),
  (15, 'france'),
  (16, 'hungary'),
  (17, 'ukraine'),
  (18, 'belgium'),
  (19, 'canada'),
  (20, 'new zealand')  ;

insert into player
values
  (7, 'John'),
  (8, 'Mary'),
  (9, 'Alex'),
  (10, 'Anna'),
  (11, 'Fred'),
  (12, 'Fay') ;

23 rows affected

create table playground
( playground_id int not null identity primary key,
  game_id int not null references game,
  area_id int not null references area,
  player_id int not null  references player,
  various_stuff varchar(100) null default 'abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz-abcdefghijklmnopqrstuvwxyz',
  constraint playground_uq
    unique (game_id, area_id, player_id)
) ;

create view dbo.play
  (game_id, area_id, player_id, name)
WITH SCHEMABINDING
as
select
    pg.game_id,
    pg.area_id,
    pg.player_id,
    name = g.game_name + '-' + a.area_name + '-' + p.player_name
from dbo.playground as pg
  join dbo.game   as g on g.game_id   = pg.game_id
  join dbo.area   as a on a.area_id   = pg.area_id
  join dbo.player as p on p.player_id = pg.player_id ;

 -- create an index on the view
create unique clustered index play_cix
    on play (game_id, area_id, player_id) ;

insert into playground
  (game_id, area_id, player_id)
select game_id, area_id, player_id
from game, area, player ;

420 rows affected

--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;

select -- top (10)
   game_id, area_id, player_id, name
from play
   WITH (NOEXPAND)            -- Hint used because we are in Express edition
  ;
set statistics xml off;

game_id | area_id | player_id | name
------: | ------: | --------: | :--------------------------
      1 |      11 |         7 | chess-usa-John
      1 |      11 |         8 | chess-usa-Mary

   --- a few hundred rows omitted

  7 |      20 |        11 | pacman-new zealand-Fred
  7 |      20 |        12 | pacman-new zealand-Fay

  | Microsoft SQL Server 2005 XML Showplan                                                                                |
  | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
  | <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.8" Build="14.0.3015.40"><BatchSequence><Batch><Statements><StmtSimple StatementText="select -- top (10) &#xa;   game_id, area_id, player_id, name &#xa;from play &#xa;   WITH (NOEXPAND)" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00522548" StatementEstRows="420" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xBA9BAED6D700FD27" QueryPlanHash="0xE0086846AA2CCE7F" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="88"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2073864"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="15" CpuTime="15"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="420" EstimatedRowsRead="420" EstimateIO="0.00460648" EstimateCPU="0.000619" AvgRowSize="174" EstimatedTotalSubtreeCost="0.00522548" TableCardinality="420" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="game_id"></ColumnReference><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="area_id"></ColumnReference><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="player_id"></ColumnReference><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="name"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="420" Batches="0" ActualExecutionMode="Row" ActualElapsedms="15" ActualCPUms="15" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="420" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="1" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="game_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="area_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="player_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Column="name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9787548beea04054bb68c8990bb6cdab]" Schema="[dbo]" Table="[play]" Index="[play_cix]" IndexKind="ViewClustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |

dbfiddle here

and the execution plan of the query that selects from the view, showing that the clustered index on the materialized view is used:


You have to use DDL event groups that can be used to run a DDL trigger or an event notification, and also the Transact-SQL statements they cover.

Refer BOL for more details on what can be tracked.

I have posted an answer here for How to get notified when a deadlock occurs using Event notification. The concept is similar and can be applied to audit Full text catalog.


MariaDB 10.0.36: Table './mysql/servers.MYI' is read only

Changed datadir= in /etc/my.cnf from /home/mysql/ and moved databases to /var/lib/mysql/:

datadir                        = /var/lib/mysql/

and MariaDB 10.0.36 started fine.

But why? What do I miss?

Regards,
Alex.


Have users update SSMS. This was a bug in an earlier release of SSMS2016. Earlier this year I had the same issue, but only with some users. Those users had not updated their SSMS. Once they did, issue went away. Unfortunately I do not remember which version had this issue.


Oracle Database 12c: TNS:permission Denied (CentOS)

This syntax does not use the listener at all:

sqlplus -prelim / as sysdba

But you faced an authentication error, because the OS group based SYSDBA authentication failed. Check the below:

cat $ORACLE_HOME/rdbms/lib/config.c

There you should see a line like this:

#define SS_DBA_GRP "John"

Only those users can use / as sysdba that are members of the OS group defined above. If you installed the database software with the OSDBA group set to John, and changed your group to Users, then you will not be able to use / as sysdba.

If you want to change the OSDBA group to your new group, edit the above file, set the desired value, shutdown everything running from the ORACLE_HOME (database, listener), then run

$ORACLE_HOME/bin/relink all


The DBA role includes system privileges (like SELECT ANY TABLE), not object privileges.


HOWTO

Your query needs to do the following:

UNPIVOT the data
PIVOT the data to get the COUNT()
add missing rows

sub-query of all row LEFT OUTER JOIN calculated data
use of then not-well-known MODEL clause

and, of course, the ORDER BY clause

EXAMPLE 1 (sub-query)

WITH DATA_SET
AS
    (   SELECT  1   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                5   AS OVERALL,
                4   AS COMFORT,
                5   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  2   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                4   AS OVERALL,
                4   AS COMFORT,
                4   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  3   AS REVIEW_ID,
                'b' AS PRODUCT_ID,
                4   AS OVERALL,
                5   AS COMFORT,
                4   AS FIT,
                5   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  4   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                3   AS OVERALL,
                2   AS COMFORT,
                2   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  5   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                2   AS OVERALL,
                1   AS COMFORT,
                2   AS FIT,
                1   AS APPEARANCE
        FROM    DUAL
    )
,unpivot_data as (
  SELECT  product_id, score, catagory
  FROM    DATA_SET
    UNPIVOT ( score
              for catagory in (
                overall, comfort, fit, appearance
              )
            )
  )
,count_values as (
  select product_id, score, overall, comfort, fit, appearance
  from unpivot_data
  pivot (
    count(catagory)
    for catagory in ( 'OVERALL' AS overall, 'COMFORT' as comfort, 'FIT' as fit, 'APPEARANCE' as appearance)
  )
)
,all_rows as (
  select *
  from (select distinct product_id from data_set),
       (select level as score from dual connect by level <= 5)
)
select a.product_id, a.product_id
  ,nvl( b.overall, 0) overall
  ,nvl( b.comfort, 0) comfort
  ,nvl( b.fit, 0) fit
  ,nvl( b.appearance, 0) appearance
from all_rows a
  left outer join count_values b
    on (a.product_id=b.product_id and a.score=b.score)
order by a.product_id, a.score

EXAMPLE 2 (model)

WITH DATA_SET
AS
    (   SELECT  1   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                5   AS OVERALL,
                4   AS COMFORT,
                5   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  2   AS REVIEW_ID,
                'a' AS PRODUCT_ID,
                4   AS OVERALL,
                4   AS COMFORT,
                4   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  3   AS REVIEW_ID,
                'b' AS PRODUCT_ID,
                4   AS OVERALL,
                5   AS COMFORT,
                4   AS FIT,
                5   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  4   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                3   AS OVERALL,
                2   AS COMFORT,
                2   AS FIT,
                4   AS APPEARANCE
        FROM    DUAL
        UNION ALL
        SELECT  5   AS REVIEW_ID,
                'c' AS PRODUCT_ID,
                2   AS OVERALL,
                1   AS COMFORT,
                2   AS FIT,
                1   AS APPEARANCE
        FROM    DUAL
    )
,unpivot_data as (
  SELECT  product_id, score, catagory
  FROM    DATA_SET
    UNPIVOT ( score
              for catagory in (
                overall, comfort, fit, appearance
              )
            )
  )
,count_values as (
  select product_id, score, overall, comfort, fit, appearance
  from unpivot_data
  pivot (
    count(catagory)
    for catagory in ( 'OVERALL' AS overall, 'COMFORT' as comfort, 'FIT' as fit, 'APPEARANCE' as appearance)
  )
)
select *
from count_values
model return all rows
  DIMENSION by (product_id,score)
  measures ( overall, comfort, fit, appearance )
  rules (
    overall[ for product_id in ( select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( overall[ cv(product_id), cv(score) ], 0),
    comfort[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( comfort[ cv(product_id), cv(score) ], 0),
    fit[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( fit[ cv(product_id), cv(score) ], 0),
    appearance[ for product_id in (select distinct product_id from data_set), for score in (1,2,3,4,5)] =
      nvl( appearance[ cv(product_id), cv(score) ], 0)
    )
order by product_id, score


Oracle 12c Installation (Grid) on RHEL 7.1 CLSRSC-214: Failed to start 'ohasd'

Add following:
cat /etc/systemd/system/ohasd.service

[Unit]

Description=ohasd daemon

[Service]

ExecStart=/etc/init.d/init.ohasd run >/dev/null 2>&1

Reload the deamon:
systemctl daemon-reload

Enable the ohasd:
systemctl enable ohasd.service
systemctl start ohasd.service

Check if it is running:
ps -ef|grep ohas

Run the root.sh script again


What should I ask my DBA in order to diagnose a slow application?

While a good DBA can find out a lot on his own, it never hurts to give a lot of information.

Which application you're using. If you know server/db ect give that as well.
Test/Prod
When the issue occurs, eg. I open this form and it's slow, or when I do this...
What's slow and what's fast. For some applications it can be perfectly acceptable to wait 1-2 seconds, while others have to perform near instantly. It can often be really easy to get a first bump in performance, but after that it takes a lot longer to make it just a little bit faster
Which login you're using

Especially if he's external + perhaps a junior it never hurts to just give him all you've got.

After that it's mostly up to the DBA to setup monitoring.

Log WhoIsActive to a table
Use the build in query store if version is 2016 or higher
Buy a third party tool, eg. SentryOne/Red gate monitor

After he setup monitoring you could also mail him "I'm having issues right now, could you see if you notice anything special?" Do not expect him to reply right away, but it gives him a window that he can check later where he knows the issue occurred.


CREATE TRIGGER tr
BEFORE INSERT
ON tnt_item
FOR EACH ROW
SET NEW.itemNo = 1 + COALESCE((SELECT MAX(t1.itemNo)
                               FROM tnt_item t1, tnt_group t2, tnt_group t3
                               WHERE t1.groupID = t2.groupID
                                 AND t2.ClientID = t3.ClientID
                                 AND t3.groupID = NEW.groupID), 0);

fiddle


Postgresql: Aggregate objects into an (json) array (subquery issues)

If you are on 9.4 something like this might be what you are after:

select json_object(array_agg(id)::text[],array_agg(rw)::text[])
from( select id
           , ( select to_json(array_agg(row_to_json(t)))
               from (select typ,prop from bgb where id=b.id) t ) rw
      from bgb b
      group by id ) z;


How to convert Extended Event action mdmget_TimeStampUTC to datetime?

The value 132085458320550473 is the number of ticks since January 1, 1601. Use the below function to convert it to datetime:

CREATE FUNCTION dbo.ConvertTicksToDateTime ( @Ticks bigint )
  RETURNS datetime
AS
BEGIN
    DECLARE @DateTime datetime2 = '16010101';
    SET @DateTime = DATEADD( DAY, @Ticks / 864000000000, @DateTime );
    SET @DateTime = DATEADD( SECOND, ( @Ticks % 864000000000) / 10000000, @DateTime );
    RETURN DATEADD( NANOSECOND, ( @Ticks % 10000000 ) * 100, @DateTime );
END


Without seeing the table schema, I see two options:

Adjust the schema so that the Foreign Keys(FK) are not part of the Primary Key(PK); only the AUTO_INCREMENT column is the PK. The Foreign Keys can still be Unique Indexes to prevent multiple likes of a particular track by the same user. This won't work of course if the FKs are part of a partitioning scheme.
Take out the AUTO_INCREMENT column and add a TIMESTAMP(0) DEFAULT CURRENT_TIME column. The timestamp column will take up as much space as an INT AUTO_INCREMENT column. The downside to this is all rows before adding the timestamp column would not have an order.


What ciphers need to be enabled to install and run SQL Server 2014 SP1 on Windows 10?

After re-enabling all the ciphers and SSL 3.0 I completely uninstalled the broken installation.

I logged out and used a second administrator account to wipe the User Profile who installed the SQL Server. I removed any fragments of SQL Server Installation in the registry that i could find.

Also i deleted any user profile created by SQL Server and removed them from the registry.

After this the Installation worked, almost anyways, the default Service account for SSAS failed to start the AS Service but switching to another service account worked.

Still not sure if it was a corrupted User Profiler or the fact that SSL 3.0 and Ciphers were disabled. It could have been a combination.


sys.dm_db_stats_properties seems to be misbehaving for a small table - how to get the number of records of a table in a different way?

Looking for row counts in stats objects is problematic.  Especially since stats are not updated automatically every time a row is inserted, deleted, or modified.

Use sys.dm_db_partition_stats instead.  It returns row count information for every partition in the database.

SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name)
    , dbps.partition_number
    , dbps.row_count
    , dbps.*
FROM sys.schemas sch
    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
    INNER JOIN sys.dm_db_partition_stats dbps ON o.object_id = dbps.object_id
WHERE o.is_ms_shipped = 0
    AND (dbps.index_id = 0 OR dbps.index_id = 1); --heap or clustered indexes only

I wrote a blog post on SQLServerScience.com that shows some of the problems with the stats properties DMV in relation to row counts.  That post uses this query to obtain row counts, and shows when the stats object was last updated:

SELECT Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N'.' + QUOTENAME(s.name)
    , ddsp.stats_id
    , last_updated = CONVERT(datetime2(1), ddsp.last_updated)
    , ddsp.rows
    , mod_count = ddsp.modification_counter
    , ddsp.unfiltered_rows
FROM sys.schemas sch
    INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
    INNER JOIN sys.stats s ON o.object_id = s.object_id
    OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp
WHERE o.is_ms_shipped = 0;


Is there a good way to link tables and sequences?

Easy in Oracle:

comment on table mytable is 'This is a table comment';

comment on column mytable.mycolumn is 'This is a column comment';

You can then view the comments using the USER_TAB_COMMENTS and USER_COL_COMMENTS data dictionary views.

This answer covers it for SQL Server.


How to compare the database of a backup from another environment

It sounds to me like the used procedure is somehow suboptimal :-)
We have two easy ways to create an identical test Database from a live DB:
Simple Cloning and Snapshot Standby.
Simple Cloning is the RMAN command duplicate target database:
http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV010

Snapshot Standby is even more comfortable, because you can do it very fast & easy multiple times subsequently after your live DB did change:
http://uhesse.wordpress.com/2010/09/24/snapshot-standby-database-in-action/


How to select the line with the max(date)?

SELECT B.* FROM
(
    select id,max(date) date
    from table1 group by id
) A INNER JOIN table1 B USING (id,date);

You should create this index to help this run faster

ALTER TABLE table1 ADD INDEX (id,date);

Sample Data Loaded

mysql> use test
Database changed
mysql> drop table if exists table1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table table1
    -> (id int not null,
    -> `group` varchar(10) not null,
    -> date date,
    -> key (id,date)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1 (id,`group`,date) values
    -> (1,'a','2013-01-01'),
    -> (1,'b','2014-01-01'),
    -> (2,'a','2012-01-01'),
    -> (2,'b','2013-01-01');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+----+-------+------------+
| id | group | date       |
+----+-------+------------+
|  1 | a     | 2013-01-01 |
|  1 | b     | 2014-01-01 |
|  2 | a     | 2012-01-01 |
|  2 | b     | 2013-01-01 |
+----+-------+------------+
4 rows in set (0.00 sec)

mysql>

Here is the query executed

mysql> SELECT B.* FROM
    -> (
    ->     select id,max(date) date
    ->     from table1 group by id
    -> ) A INNER JOIN table1 B USING (id,date);
+----+-------+------------+
| id | group | date       |
+----+-------+------------+
|  1 | b     | 2014-01-01 |
|  2 | b     | 2013-01-01 |
+----+-------+------------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!


Will TRUNCATE stop the increase of the DB2 database size?

The fact that you are using the alter tablespace ... reduce max syntax indicates that you're using automatic storage (or DMS) tablespaces.

Knowing that, truncating a table only frees up the space that the table occupies within a tablespace. (i.e., Db2 releases the extents that are assigned to the table.)

The reason Db2 can't just give back the space that was freed in the tablespace automatically is because there is no guarantee that the extents that were allocated to the table were located contiguously at the "end" of the tablespace.

Therefore, when you deallocate space in the tablespace (via alter tablespace ... reduce max), Db2 must first "fill in the holes" by moving other in-use extents into the unused extents that were freed when you truncated the table.

See the documentation for reclaimable storage for more details and some pictures of this.


The following aggregates the products as you want it:

select c.sku, array_agg(p.ordering_id) as ids
from collections c
  join products p on c.sku = any(p.inside)
group by c.sku;

The above returns:

sku | ids
----+------------
10A | {1234,5678}
20A | {5678}

Now this can be used as the source of an update statement:

update collections
   set components = t.ids
from (
    select c.sku, array_agg(p.ordering_id) as ids
    from collections c
      join products p on c.sku = any(p.inside)
    group by c.sku
) t
where collections.sku = t.sku;


I ended up using this command:

mongo --quiet --eval "var c=new Mongo();var o=[];db.adminCommand('listDatabases').databases.forEach(function(d){o=o.concat(c.getDB(d.name).currentOp(true).inprog.filter(function(d){return d.op=='query';}).map(function(d){return d.query;}));});o" > cursor.json

The expanded form of the actual js in there is

var conn = new Mongo();
var cursorInfo = [];
db.adminCommand('listDatabases').databases.forEach(function(d) {
    var database = conn.getDB(d.name)
    cursorInfo = cursorInfo.concat(database.currentOp(true).inprog.filter(function(d) {
                return d.op == 'query';
            }).map(function(d) {
                return d.query;
            }));
});
cursorInfo

The command will get the information from db.currentOp(true).inprog about current querying cursors for every db and save it into the file cursor.json.


In MySQL one connection uses one CPU core.  Period.  End of discussion.
Well...

InnoDB has some background threads, for example for handling I/O, purging, pruning, etc.
MySQL 8.0.xx has a very limited number of queries for which it will spawn multiple threads.

average around 25% of its CPU,

Yeah, MySQL is very fast at performing well written, well indexed queries.  So, thousands of qps might use only a fraction of a CPU.  CPU is rarely the limiting factor in MySQL systems.  When it is, usually adding a composite index "fixes" it.

distributed over all cores/threads well

That's up to the OS.  Just note that each connection lives in a "thread" (or "process") and it up to the OS to allocate CPU cores to threads.
If you are averaging 25% of one CPU, the OS rarely has a good reason to wake up the other CPU.  Now we are getting into gory details of hardware -- NUMA, affinity, etc, etc.  It may be power consumption, non-uniform memory access, etc.
Even if you get it to use both CPUs, each at about 12.5%, users may not see any improvement in latency.
Multiple threads:

May be blocked waiting on I/O -- which probably has only one way to get to disk
May be blocked on various Mutexes -- At somewhere around 64 threads, this becomes a problem.  Not much of an issue at 25.
May be blocked for transactional integrity -- effectively serializing certain types of actions; hence additional CPUs are useless.

What's the real goal??

More throughput?  Either you have not hit the limit yet, or you are I/O bound.
Lower latency?  The extra CPU, even if used, won't help much.

Either of those may be helped by improving the queries.  Turn on the slowlog; use pt-query-digest; then let's discuss the worst couple of queries.


I run sp_who on the database, and there is a "TASK MANAGER" process. What is it?

Any SPIDs below 50 are system processes. "TASK MANAGER" is one of these. I'm not sure exactly what it does but it is initiated only by the server itself, dealing with system level jobs. Seeing this on the database "sp_who" is nothing to worry about.

I highly doubt this process is the reason why you are getting kicked out of the DB running in single user mode, but if you are curious what the process might be doing you could run dbcc inputbuffer(the_spid_of_process) to check if it is running any commands.


Renaming table and putting a view in its place on Postgres

If you do this in production, be aware of prepared statements. Those have already been parsed, rewritten (and the query plan cached). The effect kicks until prepared statements are deallocated (which can take a long time).

To check for prepared statements (of the same session only!):

TABLE pg_prepared_statements;

The effect also extends to plpgsql functions that handle SQL commands like prepared statements internally.

Normally, prepared statements are forced to be re-planned after any change to involved objects. But your actions circumvent this security mechanism.

Also, most queries will just keep working. But not all.

Demo

CREATE TEMP TABLE foo (id int);
INSERT INTO foo VALUES (4);
PREPARE x AS SELECT foo FROM foo WHERE id > $1;  -- uses row type

This works:

ALTER TABLE foo ADD COLUMN t text;
EXECUTE x(3);  -- automatically re-planned

But this does not:

ALTER TABLE foo RENAME TO foo1;
CREATE VIEW foo AS TABLE foo1;
ALTER TABLE foo1 ADD COLUMN t text;

EXECUTE x(3);  -- not re-planned!

ERROR:  cached plan must not change result type

The manual on PREPARE:

  Although the main point of a prepared statement is to avoid repeated
  parse analysis and planning of the statement, PostgreSQL will force
  re-analysis and re-planning of the statement before using it whenever
  database objects used in the statement have undergone definitional
  (DDL) changes since the previous use of the prepared statement. Also,
  if the value of search_path changes from one use to the next, the
  statement will be re-parsed using the new search_path. (This latter
  behavior is new as of PostgreSQL 9.3.) These rules make use of a
  prepared statement semantically almost equivalent to re-submitting the
  same query text over and over, but with a performance benefit if no
  object definitions are changed, especially if the best plan remains
  the same across uses. An example of a case where the semantic
  equivalence is not perfect is that if the statement refers to a table
  by an unqualified name, and then a new table of the same name is
  created in a schema appearing earlier in the search_path, no automatic
  re-parse will occur since no object used in the statement changed.
  However, if some other change forces a re-parse, the new table will be
  referenced in subsequent uses.

The above is another example where the "semantic equivalence is not perfect".


What Rules Determine When SQL Server Use a CTE as an "Optimization Fence"?

...list of known cases where SQL Server will reliably honor the CTE as an optimization fence

Any such list would rely on observed behaviour, with no guarantee of reliability.

The SQL Server query optimizer never treats a common table expression as an optimization fence per se, though some constructions are clearly difficult to optimize across. Recursive CTEs are a good example of this.

CTEs are treated very similarly to views/inline functions/subqueries/derived tables and inlined into the query. Any observed 'fence' behaviour depends on the optimizer either not being able to, or deciding not to, optimize across that in-principle permeable border.

Generally speaking, the simpler and more 'relational' the CTE is, the more likely it is that the optimizer will be able to move bits around.

Features that would allow the optimizer to consider, or force it to materialize the 'result' of a CTE have been suggested, but not yet implemented:

The optimizer should considering materialising results of CTEs
Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n)

In the meantime, the most common workaround is to explicitly materialize the intermediate result set in a temporary table or table variable. This obviously requires a scenario not limited to a single statement.


APEX 5: Page item (Select List) with LOV must have values from a another table which return PK (FK of current table)

You have to change your Select List type. Choose SQL query in List of Values section, as a source of a list write following:

select usertype, usertype_id from usertypes


Find all ancestor nodes of a HierarchyId using a single SQL statement?

To get "all parent nodes of a given node":

select *, position.GetAncestor(1), position.GetAncestor(1).ToString()
from employee
where position=hierarchyid::Parse('/3/1/')

EmpId  EmpName       Position  (No column name)  (No column name)
5      VP Financing  0x7AC0    0x78              /3/

but there will only ever be one due to the nature of hierarchies.

If you really want to get all immediate children nodes of a given node:

select *
from employee
where position.IsDescendantOf(hierarchyid::Parse('/3/1/'))=1
      and position.GetLevel()=hierarchyid::Parse('/3/1/').GetLevel()+1

EmpId  EmpName              Position
6      Accounts Receivable  0x7AD6
10     Accounts Payable     0x7ADA

-- EDIT

I see that you want all ancestor nodes. Perhaps try an approach like this:

select *
from employee
where hierarchyid::Parse('/3/1/2/1/').IsDescendantOf(Position) = 1

or

select * from employee
where ( select position
        from employee
        where empname='Accountant 4' ).IsDescendantOf(Position) = 1

here is a CTE method for comparison:

with w as ( select * from employee where empname='Accountant 4'
            union all
            select e.*
            from employee e join w on(w.position.GetAncestor(1)=e.Position) )
select * from w;


What are efficient ways to deal with database schemas that are shared between code branches?

I have successfully used the following methodology, elaborated in Version Control and your Database:

maintain a version number in metadata (I use a database extended property)
any schema change is coded as a script that updates from current version to next version
application ships with all the scripts to upgrade from version 0 (initial deployment) all the way to current version
Every change is done through a script. Including 'system' data changes like dictionaries and lookup table entries.
when deployed, the application checks the on-disk schema version, then runs all the upgrade steps to bring the schema to current required version

I often hear the opinion of 'how is this different from just keeping the object definition scripts under source control?'. The difference is huge, because when you deploy a new version of your app you're not going to simply create a new database. Most times your app will have to upgrade the existing database, including the existing data. This is a crucial difference, your upgrade steps need to ensure the integrity and consistency of existing data during the upgrade. Some operations are trivial in code (add a non-nullable column with default value to the table object definition script, done), but they are in fact hugely painful at actual deployment (the table has 1.5 Billion rows, the add column would run out of log space if done the 'simpleton' way).

How does this works with branching:

when the branch is created, it snaps the current schema version, say version 1.6
as the team start working on the branch, it adds a new version, 1.7, and then it starts coding the upgrade step from 1.6 to 1.7
the upgrade step gets changed as modifications are done in the branch. It always runs the script that upgrade from v 1.6 to 1.7, but what exactly those scripts do, is subject to the normal code iterations and check-ins in the branch
branch finishes development, it prepares for the reverse integration (to be merged back into baseline)

it does a new forward integration from the baseline to the branch. If the integration does not bring any changes to the schema version, all things are good, the branch can reverse integrate as-is. version 1.7 becomes the new baseline version.
the interesting stuff is when another branch has reverse integrated into base in the meantime and now the base schema version has changed to, say, 1.7. In this case our branch has to bump it's deployment target schema version to 1.8 and do a review of the upgrade step that was formerly from 1.6 to 1.7 to see how it operates in the new environment, upgrading from 1.7 to 1.8. Logical schema conflicts have to be resolved, script may require changes, testing has to be done. Once completed, the branch can reverse integrate into base. The deployed target version of the product now becomes 1.8.
when another branch that has forked at schema version 1.6 wants to reverse-integrate, it needs to it will have to bump it's schema version to 1.9, test the upgrade script from 1.8 to 1.9, then it can integrate back into the base.

Notice that there is no tool involved, no magic schema diff scripting, no wizards and no right-button-click-generate-script involved. This is a 100% developer driven process, based on source (scripts). Many find this whole process elaborate, but it works. In fact, as a SQL Server user, you have already leveraged the results of this process in your daily use of SQL Server: SQL Server itself uses a very similar database upgrade process and, as you probably expect, the product development process makes extensive use of branching and the problem you mentioned is a very real problem that has to be solved.

BTW, how the branching/integration actually occurs differs between source control products, I'm using the terms familiar from the perforce integrate mode of operation.


Is it a good practice to apply Vertical Partition to a newly created model?

Yes, it is a valid physical design modification to accommodate more flexible locking behavior.

If you join the two tables and expose it to your ORM with a view, I don't think the ORM will know the difference (unless it attempts to look at the view metadata like looking for primary key or something during code generation).

Your view should be updatable, and even if it isn't due to some peculiarity of updatable view limitations can always be made updatable using INSTEAD OF triggers.

And yes, I would certainly consider it.  Whether I would consider it before actually doing load testing on a single table design, I'm not sure.

Here's an example in SQL Server:

CREATE TABLE main
( ID INT IDENTITY NOT NULL
 ,DATA VARCHAR(255)
 ,CONSTRAINT PK_MAIN PRIMARY KEY CLUSTERED (ID ASC)
 );

CREATE TABLE aux
( ID INT NOT NULL
 ,MOREDATA VARCHAR(255)
 ,CONSTRAINT PK_AUX PRIMARY KEY CLUSTERED (ID ASC)
 );

GO

CREATE VIEW unified
AS
  SELECT main.*, aux.MOREDATA
  FROM main
  INNER JOIN aux
    ON aux.ID = main.ID;
GO

INSERT INTO main (DATA) VALUES ('somedata');

INSERT INTO aux (ID, MOREDATA) VALUES (SCOPE_IDENTITY(), 'some more data');

Then:

BEGIN TRANSACTION;

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id;

SELECT *
FROM unified;

UPDATE unified
SET MOREDATA = 'changed data'
WHERE ID = 1;

SELECT *
FROM unified;

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id;

COMMIT TRANSACTION;


Party Data Model / Universal Data Model Example

I think you need to step back first and familiarise yourself with the notation, which seems to be Barker:

http://www.agiledata.org/essays/dataModeling101.html

The Party Role structure does illustrate a hierarchy of a type, but each of the subtypes uses the same primary key, the Party Role ID.
Presumably, when implemented each of these entities would have varying attributes, so wouldn't be implemented as a single PartyRole table.  Also, there will be business rules of what other tables the party roles could relate to.
But in terms of Party Relationships, what you have seems correct


Error parsing YAML config file: yaml-cpp

The error messages indicate the specific line and column where the YAML parser is having an issue with your configuration file, but if you're not familiar with the format it can be difficult to work out what is expected.
Two sets of changes are required to make your config valid YAML:

Add a "space" between the systemLog.path and storage.dbPath keys and their values
YAML requires a space between key/value pairs, so reports: "error at line 4, column 8: illegal map value".

Remove the double quotes from your path values
YAML interprets backslashes inside quoted strings as introducing an escape character, so reports: "error at line 3, column 16: unknown escape character". As an alternative, you could also leave the path quoted but either escape the backslashes (\\) or use forward slashes.

The following config should work (assuming "G:\NodeApps\data\" has the correct directory and file permissions):
systemLog:
    destination: file
    path: G:\NodeApps\data\log
storage:
    dbPath: G:\NodeApps\data

There are several online testers for YAML syntax that can be useful to troubleshoot problems (eg: YAML Lint).


You could try setting the sql_mode to use ANSI_QUOTES and then dropping the column that way:

SET sql_mode = 'ANSI_QUOTES';

ALTER TABLE "schema"."table" DROP COLUMN "`";


Oracle 11g: performance improvements of inserts

Just saw the update, 60-col table with mostly VARCHAR(2k) fields -- that is (potentially) a monster table.

First things first...

You have to understand your bottleneck FIRST. On the app side, go all the way back to your single-threaded batch-insert solution (1/2/3k at a time) and begin running it and login to the DB machine and run a 'top' -- see how much time the DB process is taking AND how much (if any) wa% time the machine is showing.

If top is showing you ANY wa% time, that means your DB is I/O bound and you likely need to consider multiple DB machines (shards) or consider throwing SSDs on the host machine.

That's it; your research stops here. It doesn't matter how much CPU the DB was taking or how saturated your app client was; if you are hitting I/O latency issues on the host DB, that is as fast as it will EVER go for you.

TIP If hardware changes are out of the question, depending on the filesystem you are running (Linux) you can try disabling logging or metadata writing for the DB to slightly improve performance at the filesystem level. You can do something similar on NTFS, but this will only give you a little boost. This won't be 2x.

Now, second things second...

Let's say you had next to no wa% time but your CPU is pegged fully by the DB process. Your only option now is to introduce more DB machines (shards) and split the work.

Again, you're done with your research if this is the case. Nothing you can do to tweak the CPU to go faster.

Lastly, third things... third...

Let's say the DB isn't doing much of anything. Then, go to the client machine running the batch insert and check the CPU load -- is it pegged? If so, fire up some more machines doing the exact same batch inserts and see if you can get a linear ramp.

If the CPU isn't pegged, fire up some more threads on the same machine until it is pegged and see how the DB scales.

I think you may have already tried that, so my guess is that either your client host was already pegged (and more threads isn't going to make a difference) or the DB was already hitting its limit and can't scale any farther.

Addendum

Doing raw inserts on an unindexed table that has no garbage in it is essentially an APPEND operation which should be going as fast as the disk can handle the writes.

Creating more tables on the same host machine isn't going to help, if anything it will increase your disk seeks (to get to the other tables on disk to append to) and will slow things down.

It is critical to figure out that bottleneck first, then we can optimize the hell out of it.

Hope that helps! Keep us posted.


It's possible to change the data type without data loss or breaking FK dependencies.  It depends on whether you have some downtime where you can execute the required change though.

Changing from datetime to datetime2(7) will require every row in the table / indexes to be updated, since these two types are stored differently on disk.  This can take a little time, depending on how many rows your table has (you said "millions").

The change would look like this in T-SQL (you should definitely do things like this in T-SQL rather than the GUI, by the way):

ALTER TABLE dbo.YourTable ALTER COLUMN CreationMoment datetime2(7);

The ALTER statement here requires SCH-M (schema modification) locks: on the table being changed, and on the tables that have foreign key relationships to this one.  That means that this statement will wait until there are no concurrent transactions before it's able to run.

Once it starts running, it will also block any concurrent activity on the tables until it completes.

Here's a toy example that creates a table with 10,000,000 rows with dates, and another table with 1,000 rows referencing the first table:

USE [master];
GO
IF EXISTS (SELECT null FROM sys.databases d WHERE d.[name] = '248612')
    DROP DATABASE [248612];
GO

CREATE DATABASE [248612];
GO
USE [248612];

CREATE TABLE dbo.DateTest
(
    Id int IDENTITY PRIMARY KEY NOT NULL,
    SomeDate datetime NOT NULL
);

CREATE TABLE dbo.OtherTable
(
    Id int IDENTITY PRIMARY KEY NOT NULL,
    DateTestId int NOT NULL,

    CONSTRAINT FK_OtherTable_DateTestId
        FOREIGN KEY (DateTestId)
        REFERENCES dbo.DateTest (Id)
);

INSERT INTO dbo.DateTest
SELECT TOP (10000000)
    DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 364 ), '2019-01-01')
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2
    CROSS JOIN master.dbo.spt_values v3;

INSERT INTO dbo.OtherTable
SELECT TOP (1000)
    Id
FROM dbo.DateTest;

That code takes about 30 seconds to run on my machine.  With no other concurrent activity, the ALTER statement takes about 10-15 seconds:

ALTER TABLE dbo.DateTest ALTER COLUMN SomeDate datetime2(7);

Ideally, you could do this kind of thing on a test system to assess about how long the blocking will be in production once the ALTER begins.

Regarding my "concurrent activity" statements, if I open a transaction and update a row in dbo.DateTest, the ALTER won't even start.  It sits and waits on the SCH-M lock that it needs, which you can see by running sp_WhoIsActive @get_locks = 1:

<Database name="248612">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="DateTest" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

If I commit or rollback that update transaction, the ALTER starts, and I can see that it takes out locks on the DateTest and OtherTable tables (abbreviated XML here):

<Database name="248612">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="DateTest" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="FK_OtherTable_DateTestId" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="OtherTable" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

This will block writes on the tables involved until the ALTER completes.


The alter table was probably waiting to get an exclusive lock on the table. If you have transactions touching the table, the ALTER TABLE will wait until all those transactions are committed

You can check if your statement is waiting for such a lock by looking at pg_stat_activity and pg_locks.

If you have sessions that are shown as idle in transaction those are a potential cause for that.

Unrelated, but: Postgres 8.4 is no longer supported (=maintained). You should plan your upgrade to a supported version (9.3 or 9.4) as soon as possible. If you have to stick with 8.4 you should at least be running the latest 8.4 version which is 8.4.22


Get minimum value row based on column values

Keep in mind that if CURDATE() doesn't fit any range of dates you'll get no rows.

select   id,
         min(price) price,
         special_price,
         special_from_date,
         special_to_date
from     prices
where    special_price is not null
and      special_from_date is not null
and      special_to_date is not null
and      (curdate() >= special_from_date
          and
          curdate() <= special_to_date)
group by id

  id |    price | special_price | special_from_date | special_to_date
---: | -------: | ------------: | :---------------- | :--------------
2648 | 299.0000 |       85.0000 | 2017-12-29        | 2018-02-28

dbfiddle here


How do I determine what is activating my SQL service broker proc?

Check the queue monitor state in sys.dm_broker_queue_monitors for troubleshooting:

SELECT QUOTENAME(OBJECT_SCHEMA_NAME(queue_id, database_id))  + N'.' + QUOTENAME(OBJECT_NAME(queue_id, database_id)),*
FROM sys.dm_broker_queue_monitors;

Rusanu has a good blog post (Understanding Queue Monitors) that describes the internals of the monitor state machine that seems to be in an unexpected state. Although the troubleshooting section in his post is mostly about the more common problem of activation not occurring rather than visa-versa, you may find the information helpful in researching the cause.

Try toggling the activation status on the queue to see if that fixes things.  Add the code for the activated proc to your question if you need more help.


In this case I would use a where clause to further qualify the data you are getting back in this case something like:

SELECT *, DATEDIFF(dateCompleted,dateRequested) AS Time
FROM ticket
WHERE ((dateCompleted IS NOT NULL) AND (dateRequested IS NOT NULL))
ORDER BY Time;

Since you said you are also trying to return only 4 rows you should use LIMIT 4 in your query, however I'm not sure if that would go before or after your ORDER BY.

If you have tried some things, let us know what you have already tried and what the errors are, this can help prevent people from reinventing the wheel and get your problem fixed faster.


Postgres Streaming Replication lagging, using lots of CPU and little I/O

Current (true at least in PostgreSQL 9.4 and older) PostgreSQL releases have single-threaded WAL recovery.

This means that replay of the write-ahead log occurs in only one recovery worker, and is thus able to benefit less from I/O concurrency than a normal running master. This can result in WAL replay lagging behind in cases where the replica and master have similar hardware, even when you'd expect the master (which is generally also under more load) to be the slower server.

(It'd be  great to improve this, but wanted it enough to do the work yet, and nobody seems keen to fund it at the moment).

That said, your case with very high CPU use doesn't seem to fit with issues of low I/O concurrency. In your situation I'd be attaching gdb and seeing what the replay proess was doing, or using perf top to examine what the system as a whole is up to, then dig deeper with perf once I had some clues. I'd also be looking closely at iotop, vmstat, iostat, the PostgreSQL logs, dmesg, etc.

After investigation

Profiles revealed that most time was being spent in DropRelFileNodeBuffers.

That does a linear scan through shared_buffers whenever a relfilenode is deleted - caused by truncate, drop table, cluster, drop index, etc. This must be done during WAL replay, as well as on the main node.

So this suggests that:

Your shared_buffers is probably very big; and
You're probably doing lots of operations that delete relfilenodes

Reducing shared_buffers on the replica may well help.


No properties option in Index context menu

This functionality is not available for Azure yet.
You have to use INDEXPROPERTY.  Explained here with example for Azure.

On a side note Azure SQL Server v12.0.2000.8 is not SQL Server 2014. You cannot compare versions between Azure and On-premises.  Rather you should compare features if you have to.


This should work (as superuser):

CREATE ROLE rwc;    -- read/write/create user
CREATE SCHEMA foo;
ALTER  SCHEMA foo OWNER TO rwc;
-- GRANT ALL ON SCHEMA foo TO rwc;  -- alternatively

CREATE ROLE r;      -- read user
GRANT USAGE ON SCHEMA foo TO r;

CREATE ROLE rw;     -- read/write user

GRANT r TO rw;
GRANT rw TO rwc;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT SELECT ON TABLES TO r;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo GRANT USAGE ON SEQUENCES TO r;

ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT INSERT, UPDATE, DELETE ON TABLES TO rw;
ALTER DEFAULT PRIVILEGES FOR ROLE rwc IN SCHEMA foo
GRANT SELECT, UPDATE ON SEQUENCES TO rw;

You probably missed one of these steps.
You may need do adjust existing objects additionally since default privileges are only granted to new objects.


Oracle - Exchange partitions between two List partitioned tables

Answer left in a comment by Andrew Sayer:
You can create a non-partitioned staging table with the right structure. You then partition exchange from Table_A to the staging table, then from the staging table to Table_B. What are you going to do with the old data now in Table_A?


Dynamic result in select statement mysql

I could not understand your requirement. Hope it works :

select t.table_schema,t.table_name,t.AUTO_INCREMENT
from information_schema.TABLES t,information_schema.COLUMNS c
where c.EXTRA = 'auto_increment' and t.table_schema=c.table_schema and t.table_name=c.table_name


Your intuition is close, but disregards concurrency.

You can have medium/high latency, high throughput, massively concurrent systems - any individual request may take longer on Cassandra than on some other systems, but you can run far more requests in parallel, allowing total throughput to be higher. In Cassandra's case, the SEDA architecture allows many concurrent requests per machine (typically on the order of ~128 or ~256 read and write threads per machine), and the distributed nature allows you to scale up with many machines (single clusters on the order of thousands of machines).


Why does an SQL Server Agent job try to create a procedure

Seeing the procedure create statement popping up is expected behaviour here, it has nothing to do with the job agent itself. And the job step is not recreating the procedure, it is only running it.

Example

If I create this procedure:

CREATE PROC dbo.Waitfordelay
as
WAITFOR DELAY '00:00:10';
SELECT 5;

And then run it in a query window:

EXEC dbo.Waitfordelay;

Then run your query:

loginame       program_name                                    sql_text
Domain\User    Microsoft SQL Server Management Studio - Query  CREATE PROC dbo.Waitfordelay  as  WAITFOR DELAY '00:00:10';  SELECT 5

The create proc also shows up.

If you want more information on the procedure call you could use something like dbcc inputbuffer

dbcc inputbuffer(spid)

For me this returns:

EventType   Parameters  EventInfo
Language Event  0   EXEC dbo.Waitfordelay

Or run sp_whoisactive with the parameter @get_outer_command set to 1.

exec [dbo].[sp_WhoIsActive]
        @get_outer_command = 1;
enter code here

Example result:

dd hh:mm:ss.mss session_id  sql_text                                 sql_command
00 00:00:00.733 54          <?query --WAITFOR DELAY '00:00:10'; --?> <?query --EXEC dbo.Waitfordelay --?>

running sp_whoisactive might also give you more information on what part your procedure is stuck on.

  I don't do any DDL in my procedure (or otherwise) and am wondering
  what causes the job to try to create the procedure and why the job
  hangs?

The create procedure part is cleared up, the actual problem of the job hanging will be guesswork without having a reproducable example or more information.

Is the job running a query? Can parameter sniffing be an issue here?

What are the wait types when running the job? What is the amount of cpu time / reads?

Running below query (yours adapted) when the jobstep is stuck might give more information:

SELECT
req.total_elapsed_time,
req.cpu_time,
req.reads,
req.last_wait_type
,req.wait_type,
req.wait_time,
sql.text  sql_text
FROM
sys.dm_exec_requests     req
cross apply sys.dm_exec_sql_text(req.sql_handle) sql;

We would need more details to know what the actual problem is.


Are SQL Server Database writes slower with Snapshot Isolation?

is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller.

No, this is incorrect. It somehow implies that writes in the presence of versioning have higher latency as each write has to touch the disk (for tempdb) which is not true. The write into the tempdb is also a write into 'cache'. The only 'wait' occurs at COMMIT time when the log has to be hardened. Is true that with versioning both the DB log and the tempdb log have to be hardened, but this does not necessarily implies higher latency (the IO should be paralel on different storage paths, the tempdb is stored on separate drive from your heavily used LDF, right?). For a full blown explanation read How It Works: Bob Dorr's SQL Server I/O Presentation I really hope your DBA understands this better than you are conveying it here.

As I mentioned in your other post: snapshot has no cost for INSERTS and the cost for updates and deletes can easily be mitigated. Row Versioning Resource Usage explains the trade offs. At this moment you should be probably testing with a realistic workload, which is the only way to properly asses the impact you would experience.


Why am I getting error 1064 when creating a table?

When creating a table, you should not use single quotes (') to enclose table or column names, but instead use backticks (`) to enclose table and column names, when required.

See Schema Object Names in the MySQL documentation.

If ANSI_QUOTES mode is enabled, it is also permissible to quote identifiers within double quotation marks (").


You could just alter the table and convert the column in one step:

alter table ogrtable
  alter column arrcolumn
    type varchar
    using (array_to_string(arrcolumn, ', '));


While keeping your approach to the solution, the query will be

SELECT s.id,
       s.class,
       s.name,
       s.score,
       FIND_IN_SET( s.score, ( SELECT GROUP_CONCAT( DISTINCT ss.score
                                                    ORDER BY ss.score DESC )
                               FROM scores ss
                               WHERE s.class = ss.class ) ) AS `rank`
FROM scores s
-- WHERE class = 'Grade 1'
ORDER BY class, `rank`


Database Cache Memory in Performance Monitor drops down significantly after DBCC CheckDB

We have been monitoring some SQLServer: Memory Manager's metrics, and
  noticed that after DBCC CheckDB job, metric

  Database Cache Memory (KB) drops down significantly. If to be exact,
  it dropped from 140 GB cached DB memory to 60 GB

This is correct, you can clearly see this behaviour when this example DBCC CHECKDB command completes at 21h45

Why

This behaviour is due to the database snapshot created by the DBCC command being dropped, removing all of its objects in memory.

You can replicate the behaviour by creating a snapshot of a database, loading some data in memory, and then dropping that snapshot

  CREATE DATABASE MY_DATABASE
     GO
     USE MY_DATABASE
     GO
     CREATE TABLE dbo.bla(id int identity(1,1) PRIMARY KEY NOT NULL,
                          val int,
                          val2 char(100));

    INSERT INTO dbo.bla(val,val2)
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),'bla'
    FROM master..spt_values spt
    CROSS APPLY master..spt_values spt2;
    GO

    CREATE DATABASE MY_DATABASE_SNAPSHOT
     ON
     (
     NAME ='MY_DATABASE',
     FILENAME ='D:\DATA\MY_DATABASE.ss'
     )
     AS SNAPSHOT OF MY_DATABASE;

     GO

    USE MY_DATABASE_SNAPSHOT
    GO
    SELECT * FROM dbo.bla;

     SELECT
      COUNT(file_id) * 8/1024.0 AS BufferSizeInMB
    FROM sys.dm_os_buffer_descriptors;

BufferSize before dropping the snapshot

BufferSizeInMB
1061.70312  --before

Dropping the snapshot

USE master
GO
DROP DATABASE MY_DATABASE_SNAPSHOT ;

BufferSize after dropping the snapshot

BufferSizeInMB
824.179687 --after

  Second question is why "buffer cache hit ratio" did not change after
  DBCC CheckDB completes ?

This depends on how fast the data is loaded back in your buffer cache.

If your buffer pool fills up over a longer time, it should amount to this ratio staying higher on average.

This corresponds with this part of your question:

  ... It (Buffer pool datasize) dropped from 140 GB cached DB memory to
  60 GB. and after that, slowly ramp up again during the week...