Why does the query plan start with a not-filtered-for table when the filtered-for table's entries exceed a certain threshold? We process data for our customers. Each customerrequest and is associated with one or more items (less than two on average) and falls into one of <100 categories, foreign keys to which are also present on the items. Customers often want tabular views/exports of their items that include columns of all three tables. Our ORM generates a reasonable-looking query along the lines of
SELECT *
FROM customeritem itm
  INNER JOIN customerrequest req ON itm.request_id=req.id
  INNER JOIN category cat ON cat.id=itm.category_id
WHERE customerrequest.customer_id=123

This works reasonably well for many customers, producing a query whose ANALYZE-is looks like this:

The query plan starts with the requests table on which the WHERE condition operates, then joins the items, then the categories, which intuitively seems the only way to go. However, for customers with many requests, a different plan is chosen and the query takes ages to execute (ANALYZE alone usually takes minutes):

Now the query plan starts with the (unfiltered) category table, then joins the items and only as a last step the requests table on which the WEHRE condition (which shrinks to results from an 8-digit to a 5 or 6-digit number) operates. If I remove the category join from the select, the query plan resembles the good one above (minus the final row for category join, of course).
Is there an intuitive explanation why this happens?

Perhaps you've noticed the large discrepancy between rows and r_rows for customeritem in the second analysis.  According to MariaDB's manual, that table could use an "analyze" itself.


User-shared queries: Dynamic SQL vs. SQLCMD I have to refactor and document a number of foo.sql queries which will be shared by a team of DB tech support (for customer configurations and things like that). There are types of tickets that come regularly where each customer have their own servers and databases, but otherwise the schema are the same across the board.
Stored procedures are not an option at the current time. I'm debating whether to use dynamic or SQLCMD, I have not used much of either as I am a bit new at SQL Server.
SQLCMD scripting I feel definitely "looks" cleaner to me, and easier to read and make small changes to the queries as needed, but also forces the user to enable SQLCMD mode. Dynamic is more difficult as the syntax highlighting is loss due to query being written using string manipulation.
These are being edited and ran using Management Studio 2012, SQL version 2008R2. What are some of the pros/cons of either method, or some of the SQL Server "best practices" on one method or the other? Is one of them "safer" than the other?
Dynamic example:
declare @ServerName varchar(50) = 'REDACTED';
declare @DatabaseName varchar(50) = 'REDACTED';
declare @OrderIdsSeparatedByCommas varchar(max) = '597336, 595764, 594594';

declare @sql_OrderCheckQuery varchar(max) = ('
use {@DatabaseName};
select
    -- stuff
from
    {@ServerName}.{@DatabaseName}.[dbo].[client_orders]
        as "Order"
    inner join {@ServerName}.{@DatabaseName}.[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ({@OrderIdsSeparatedByCommas});
');
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@ServerName}',   quotename(@ServerName)   );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@DatabaseName}', quotename(@DatabaseName) );
set @sql_OrderCheckQuery = replace( @sql_OrderCheckQuery, '{@OrderIdsSeparatedByCommas}', @OrderIdsSeparatedByCommas );
print   (@sql_OrderCheckQuery); -- For debugging purposes.
execute (@sql_OrderCheckQuery);
SQLCMD example:
:setvar ServerName "[REDACTED]";
:setvar DatabaseName "[REDACTED]";
:setvar OrderIdsSeparatedByCommas "597336, 595764, 594594"

use $(DatabaseName)
select
    --stuff
from
    $(ServerName).$(DatabaseName).[dbo].[client_orders]
        as "Order"
    inner join $(ServerName).$(DatabaseName).[dbo].[vendor_client_orders]
        as "VendOrder" on "Order".o_id = "VendOrder".vco_oid
where "VendOrder".vco_oid in ($(OrderIdsSeparatedByCommas));

Just to get these out of the way:

Technically speaking, both of these options are "dynamic" / ad hoc queries that are not parsed / validated until they are submitted. And both are susceptible to SQL Injection since they are not parameterized (though with the SQLCMD scripts, if you are passing in a variable from a CMD script then you do have an opportunity to replace ' with '', which may or may not work depending on where the variables are being used).
There are pros and cons to each approach:

SQL scripts in SSMS can be easily edited (which is great if that is a requirement) and working with results is easier than with output from SQLCMD. On the down-side, the user is in an IDE so it is easy to mess up the SQL, and the IDE makes it easy to make a great variety of change without knowing the SQL to do it.
Running scripts via SQLCMD.EXE does not allow the user to easily make changes (without editing the script in an editor and then saving it first). This is great if the users aren't supposed to be changing the scripts. This method also allows for logging each execution of it. On the down-side, if there is a need to routinely edit the scripts then it would be quite cumbersome. Or, if users need to scan through 100k rows of a result set and/or copy those results to Excel or something, then that is also difficult in this approach.

If your support folks aren't doing ad hoc queries and are just filling out those variables, then they don't need to be in SSMS where they can edit those scripts and make unwanted changes.

I would create CMD scripts to prompt the user for the desired variable values and then call SQLCMD.EXE with those values. The CMD script could even log the execution to a file, complete with time stamp and variable values submitted.

Create one CMD script per SQL script and place in a networked shared folder. A user double-clicks on the CMD script and it just works.

Here is an example that:

prompts the user for the server name (no error checking on that yet)
prompts the user for the database name

if left blank, it will list the databases on the specified server and prompted again
if the database name is invalid, the user will be prompted again

prompts the user for the OrderIDsSeparatedByCommas

if blank, prompts the user again

runs the SQL script, passing in the value of %OrderIDsSeparatedByCommas% as the SQLCMD variable $(OrderIDsSeparatedByCommas)
logs the execution date, time, ServerName, DatabaseName, and OrderIDsSeparatedByCommas to a log file named for the Windows Login running the script (this way, if the log directory is network and there are multiple people using this, there won't be any write contention on the log file like there might be if the USERNAME were to be logged in the file per entry)

if the log file directory does not exists, it will be created

Test SQL script (named: FixProblemX.sql):

SELECT  *
FROM    sys.objects
WHERE   [schema_id] IN ($(OrderIdsSeparatedByCommas));

CMD script (named: FixProblemX.cmd):

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION

SET ScriptLogPath=\\server\share\RunSqlCmdScripts\LogFiles

CLS

SET /P ScriptServerName=Please enter in a Server Name (leave blank to exit):

IF "%ScriptServerName%" == "" GOTO :ThisIsTheEnd

REM echo %ScriptServerName%

:RequestDatabaseName
ECHO.
SET /P ScriptDatabaseName=Please enter in a Database Name (leave blank to list DBs on %ScriptServerName%):

IF "%ScriptDatabaseName%" == "" GOTO :GetDatabaseNames

SQLCMD -b -E -W -h-1 -r0 -S %ScriptServerName% -Q "SET NOCOUNT ON; IF (NOT EXISTS(SELECT [name] FROM sys.databases WHERE [name] = N'%ScriptDatabaseName%')) RAISERROR('Invalid DB name!', 16, 1);" 2> nul

IF !ERRORLEVEL! GTR 0 (
    ECHO.
    ECHO That Database Name is invalid. Please try again.

    SET ScriptDatabaseName=
    GOTO :RequestDatabaseName
)

:RequestOrderIDs
ECHO.
SET /P OrderIdsSeparatedByCommas=Please enter in the OrderIDs (separate multiple IDs with commas):

IF "%OrderIdsSeparatedByCommas%" == "" (

    ECHO.
    ECHO Don't play me like that. You gots ta enter in at least ONE lousy OrderID, right??
    GOTO :RequestOrderIDs
)

REM Finally run SQLCMD!!
SQLCMD -E -W -S %ScriptServerName% -d %ScriptDatabaseName% -i FixProblemX.sql -v OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%

REM Log this execution
SET ScriptLogFile=%ScriptLogPath%\%~n0_%USERNAME%.log
REM echo %ScriptLogFile%

IF NOT EXIST %ScriptLogPath% MKDIR %ScriptLogPath%

ECHO %DATE% %TIME% ServerName=%ScriptServerName%    DatabaseName=[%ScriptDatabaseName%] OrderIdsSeparatedByCommas=%OrderIdsSeparatedByCommas%   >> %ScriptLogFile%

GOTO :ThisIsTheEnd

:GetDatabaseNames
ECHO.
SQLCMD -E -W -h-1 -S %ScriptServerName% -Q "SET NOCOUNT ON; SELECT [name] FROM sys.databases ORDER BY [name];"
ECHO.
GOTO :RequestDatabaseName

:ThisIsTheEnd
PAUSE

Be sure to edit the ScriptLogPath variable towards the top of the script.

Also, the SQL scripts (specified by the -i command-line switch for SQLCMD.EXE) might benefit from having a fully-qualified path, but not entirely sure.


Why does elimination of an if-statement cause notable speed up in my function? I have the following table:

CREATE TABLE [dbo].[StateChanges]
(
    [RecordId] uniqueidentifier NOT NULL,
    [LinkedObjectId] uniqueidentifier NOT NULL,
    [ChangeType] TINYINT not null,
    [ChangeTime] DATETIME not null,
    -- some other columns too
)

with the following indices:

-- yes, clustered over GUID. I know it's not very good.
CREATE CLUSTERED INDEX StateChangesIndex ON
    [StateChanges](RecordId)
GO

CREATE INDEX LastChangeIndex_Object_Type_Time] ON
    [StateChanges] (LinkedObjectId, ChangeType, ChangeTime)
    INCLUDE(SomeColumnsHere)
GO

And I have a function that goes like this initially:

CREATE FUNCTION [dbo].[ObjectHasInterestingChanges_v1]
(
    @objectId uniqueidentifier
)
RETURNS BIT
AS
BEGIN
    IF (SELECT TOP(1) 1 FROM StateChanges
        WHERE
    LinkedObjectId=@objectId AND
            (ChangeType = 1 OR ChangeType = 2 ) )
        IS NOT NULL
    BEGIN
        RETURN 1;
    END
    RETURN 0;
END

This function is being called for a large number of rows processed by an outer query - one thousand time for the very same three thousand rows.

If I try to SELECT ObjectHasInterestingChanges_v1(something) all I have is a "constant scan" in the plan which isn't interesting so I extract the contents into a separate query and look into the execution plan. The plan is as follows initially:

"index scan" over LastChangeIndex_Object_Type_Time - as expected
"top" of "index scan result"
"left outer join" result of "top" with some "constant scan" result
"compute scalar" of "left outer join result"
"cond with query" of "compute scalar"
that's it

I look into sys.dm_exec_query_stats table and note the number of execution for this function, number of reads and time taken.

I then change the function like this:

CREATE FUNCTION [dbo].[ufn_ObjectHasInterestingChanges_v2]
(
    @objectId uniqueidentifier
)
RETURNS BIT
AS
BEGIN
    DECLARE @result BIT
    SET @result = 0
    SELECT TOP(1) @result=1 FROM StateChanges
        WHERE LinkedObjectId=@objectId AND
           (ChangeType = 1 OR ChangeType = 2 )
    RETURN @result;
END

It yields exactly the same results. If I look into the plan it's the following now:

"index seek"
"top"
"compute scalar"
"select"
that's it

I run the very same outer query in the same database such that exactly the same data is being processed and exactly the same data is being read and the background workload is exactly the same (zero) and so I'm sure that the comparison is fair and reasonable.

I look into sys.dm_exec_query_stats again. I see that

the number of executions is the same for v1 and v2 - large number close to three millions
the number of logical reads, physical reads and physical writes is the same for v1 and v2
"worker time" is 53.5 seconds for v1 and 48.3 seconds for v2 - 10 percent speed up
"total time" is 86 seconds for v1 and 67 seconds for v2 - 22 percent speed up

Why is this? Even if "worker time" is decreased because if-statements are somehow time consuming why would "total time" decrease even further? "total time" also accounts for I/O and "total time" decreasing more than "worker time" would imply some I/O speedup which is completely unreasonable here.

Where does this notable difference in both "worker time" and "total time" come from after such minor code change?

Sample data, tests with the actual functions + possible rewrite can be found below

DECLARE @objectid uniqueidentifier
select top(1) @objectid = GUIDS
from #TEMP;

-- function 1 ,omitted the return 0

IF (SELECT TOP(1) 1 FROM StateChanges
        WHERE
    LinkedObjectId=@objectId AND
            (ChangeType = 1 OR ChangeType = 2 ) )
        IS NOT NULL
    BEGIN
      SELECT 1;
    END

-- Function 2 changed the returns

    DECLARE @result BIT
    SET @result = 0
    SELECT TOP(1) @result=1 FROM StateChanges
        WHERE LinkedObjectId=@objectId AND
           (ChangeType = 1 OR ChangeType = 2 )
    SELECT @result;

Function V1:

A Nested loop left outer join is present in the plan, as to facilitate the IF ... IS NOT NULL check

Function V2

No nested loop operator, a more simple, straightforward plan

As seen, a nested loop left join was added because of the IF check, if we execute the query without the IF statement:

DECLARE @objectid uniqueidentifier
select top(1) @objectid = GUIDS
from #TEMP;
SELECT TOP(1) 1 FROM StateChanges
        WHERE
    LinkedObjectId=@objectId AND
            (ChangeType = 1 OR ChangeType = 2 );

No more Nested loops and the same plan as V2 is seen

Which should explain the increased worker time, since adding the NL join to check for the IS NULL will increase CPU usage.

To answer your question, yes the IF statement should increase your CPU usage, as a result of the additional operators.

Testing with the previous defined tables, indexes, functions and adding some data

CREATE TABLE #TEMP(GUIDS uniqueidentifier)
DECLARE @GUID uniqueidentifier
declare @i int =1
WHILE @i <= 1000
BEGIN

SET @GUID = NEWID()
INSERT INTO #temp(GUIDS)
VALUES(@GUID)
SET @I += 1
END

SET NOCOUNT OFF;
INSERT INTO [dbo].[StateChanges]([RecordId],[LinkedObjectId], [ChangeType],[ChangeTime], SomeColumnsHere )
SELECT GUIDS, GUIDS , 5, getdate(), 'Blabla'
from   #TEMP

Testing the functions

SELECT [dbo].[ObjectHasInterestingChanges_v1](GUIDS)
FROM #temp;

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

SELECT [dbo].ufn_ObjectHasInterestingChanges_v2(GUIDS)
FROM #temp ;

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

they appear to get the same CPU time with a low dataset and no matches (nothing matches because Changetype is not 1 or 2 in my data).

On another note, you could change the function to outer apply

SELECT ISNULL(a.bla,0)
FROM #TEMP
OUTER APPLY
(SELECT TOP(1) 1 as bla  FROM StateChanges
        WHERE
    LinkedObjectId=GUIDS AND
            (ChangeType = 1 OR ChangeType = 2 ) ) as a

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

Test 2, adding some mathing data

DECLARE @GUID uniqueidentifier
declare @i int =1
WHILE @i <= 1000
BEGIN

SET @GUID = NEWID()
INSERT INTO #temp(GUIDS)
VALUES(@GUID)
SET @I += 1
END

SET NOCOUNT OFF;
INSERT INTO [dbo].[StateChanges]([RecordId],[LinkedObjectId], [ChangeType],[ChangeTime], SomeColumnsHere )
SELECT GUIDS, GUIDS ,2, getdate(), 'Blabla'
from   #TEMP;

Testing the functions and the rewrite again

SELECT ISNULL(a.bla,0)
FROM #TEMP
OUTER APPLY
(SELECT TOP(1) 1 as bla  FROM StateChanges
        WHERE
    LinkedObjectId=GUIDS AND
            (ChangeType = 1 OR ChangeType = 2 ) ) as a;

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

SELECT [dbo].[ObjectHasInterestingChanges_v1](GUIDS)
FROM #temp;

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

SELECT [dbo].ufn_ObjectHasInterestingChanges_v2(GUIDS)
FROM #temp ;

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


MariaDB & MySQL - Trigger not firing: “Field '__' doesn't have a default value” (Old Bug not really fixed?) I think my problem might be caused by this old MySQL bug (and this one: /bug.php?id=6295 ), in which they were checking the column constraints before running triggers. But, according to that bug, they fixed it years ago.

I was originally running on MariaDB v10.1.19; but according to this MariaDB ticket: jira.mariadb .org/browse/MDEV-10002, they also fixed this in v10.1.6.

I have tried it on:

[FAILS] -> MariaDB v10.1.19
[FAILS] -> MySQL v5.6
[WORKS] -> MySQL v5.7

Is there perhaps a config problem with my setup that’s causing this to not work in MariaDB? Or is it more likely that the bug got broken again since its original fix? I don’t want to bother the MariaDB devs if it turns out to be my fault.

The Problem

I have a restricted view which filters on a base table using a discriminator column. The view does not contain the discriminator column. In the base table, this column does not allow NULL values.

I created a BEFORE INSERT trigger to populate this column when inserting data through the view, but as far as I can tell, the trigger is not firing. When inserting a record into the view, the DB throws the error:

Field 'groupId' doesn't have a default value

I’ve tried doing several insert tests on the base table, and it looks like the trigger is just not firing at all. I searched around online for other people with this problem, but they all seemed to just be syntax errors. I don’t have much experience with this kind of “deep” SQL, but I think mine’s correct.

Here’s an example implementation, I also made a SQL Fiddle:

P.S. Sorry about the 2 links with spaces in them. StackExchange won't let me post more than 2 links; apparently, I'm 4 reputation short.

CREATE TABLE privateNotes (
  id int(11) NOT NULL,
  userId int(11) NOT NULL,
  text varchar(500) NOT NULL,
  groupId int(11) NOT NULL
);

INSERT INTO privateNotes (id, userId, text, groupId) VALUES
(30, 1, '[John] A Secret Message',         101),
(32, 2, '[George] A Secret Message',       101),
(34, 3, '[Sarah] A Secret Message',        202),
(36, 4, '[Mary] A Secret Message',         202);

-- --------------------------------------------------------

-- Get
CREATE FUNCTION getCurrentGroupId()
    RETURNS int(11)
    NO SQL
    BEGIN
      RETURN @currentGroupId;
    END //

-- Set
CREATE FUNCTION setCurrentGroupId(groupId_in int(11))
    RETURNS int(11)
    NO SQL
    BEGIN
      SET @currentGroupId = groupId_in;
      RETURN @currentGroupId;
    END //

-- --------------------------------------------------------

CREATE VIEW myPrivateNotes AS
SELECT
  privateNotes.id AS id,
  privateNotes.userId AS userId,
  privateNotes.text AS text
FROM
  privateNotes
WHERE
  (
    privateNotes.groupId = getCurrentGroupId() /* 101, 202, etc. */
  );

-- --------------------------------------------------------
-- Trigger

CREATE TRIGGER before_insert_privateNotes
    BEFORE INSERT ON privateNotes
    FOR EACH ROW BEGIN
        IF NEW.groupId IS NULL THEN
            SET NEW.groupId = getCurrentGroupId();
        END IF;
    END //

-- --------------------------------------------------------
-- ------- --
--  TESTS  --
-- ------- --

-- View Insert Test
SELECT setCurrentGroupId(999);
//
-- (Uncomment) Error: Field 'groupId' doesn't have a default value
-- INSERT INTO privateNotes (id, userId, text) VALUES (50, 51, 'a cool message');
//

-- View Select Tests
--
-- Copy the full chunk bellow to the Sql box over there ->
-- Don't forget to change the delimeter to: [//]
SELECT setCurrentGroupId(NULL);
//
SELECT * from myPrivateNotes; -- Returns Zero Results [Correct]
//

SELECT setCurrentGroupId(101);
//
SELECT * from myPrivateNotes; -- Returns Valid Results [Correct]
//

I opened a ticket with MariaDB. They have confirmed that it's still broken, and it has been put in the backlog to fix it.

Update:

The ticket has been fixed in MariaDB versions: 10.1.21 & 10.2.4


PostgreSQL Serialisation failure on different ids Serializable isolation mode can be used to avoid race conditions when upserting equal ids. So for create table u(uid int primary key, name text); if we run two similar transactions T1 and T2:

begin isolation level serializable;
select * from u where uid = 1;

And then continue with for T1 and T2:

insert into u (uid, name) values (1, 'A');

After commit; only the first succeeds while the other throws a serialisation failure.

This is a very neat feature of the mode to handle unique key violations in complex transactions and not resorting to a specific 'hack' insert ... on conflict. However even if uids are different, say uid = 2 and uid = 3, both transactions T1 and T2 still won't be able to commit.

How could it be possible? Supposedly they create different predicate SIReadlocks and select uses index scan. Where's the trick?

The reason is that the table is completely empty.

See the following code in _bt_first in src/backend/access/nbtree/nbtsearch.c:

if (!BufferIsValid(buf))
{
    /*
     * We only get here if the index is completely empty. Lock relation
     * because nothing finer to lock exists.
     */
    PredicateLockRelation(rel, scan->xs_snapshot);
[...]
    return false;
}
else
    PredicateLockPage(rel, BufferGetBlockNumber(buf),
                      scan->xs_snapshot);

Normally, the index scan would put an SIRead lock on the index page where the index entry should be, but since the index is empty, PostgreSQL resorts to putting an SIRead lock on the whole table.

Now since both transactions do that, you get a serialization error at the end of one of them, because the writes conflict with the table-wide read lock.

If the table were not empty, you would notice that concurrent transactions like these sometimes succeed, because they affect different index pages. If the affected uids are sufficiently close together that they are on the same index page, you would still experience a “false positive” serialization error. This is documented:

  While PostgreSQL's Serializable transaction isolation level only allows concurrent transactions to commit if it can prove there is a serial order of execution that would produce the same effect, it doesn't always prevent errors from being raised that would not occur in true serial execution. In particular, it is possible to see unique constraint violations caused by conflicts with overlapping Serializable transactions even after explicitly checking that the key isn't present before attempting to insert it.

To see what kind of SIReadLock PostgreSQL uses, inspect pg_locks.


mysql - cancel source call I want to cancel calling a source file on a mysql server which includes an enormous amount of data. It appears to be a sqldump that is broken into many small updates for many tables.

First I tried killing the process,

mysql> show processlist\G
mysql> kill ###;

This appears to only cancel a single query inside the source file; a new process pops up and the server continues to execute the source file.

Then I tried stopping the mysql service,

bash> sudo service mysql stop

But this only paused the problem; the server threw errors constantly until I started the service again, where it picked up where it left off again.

What are my options to fully cancel this query?

By "source file", do you mean the source command in the commandline tool mysql?  If so, you need to kill that client, not the individual commands in PROCESSLIST.

Go to the OS's task manager.  (You may need to be root.)

# ps -axl | grep mysql  (or other args of your picking)
# kill -9 1234567       (using the PID from the ps)


Change table schema in the workbench I have three different schemas, ended up creating all the tables in one, but now I want to put them in their correct schemas.

How can I do this without using scripts?
the version of Workbench that use is 6.3.4

On the "MySQL Model" page you can cut and paste your objects between schemas:


charindex wrong result Charindex returns no record if only one letter is provided.
If parameter matches the whole word it shows the record.
Why???

USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[filter_opombe]
@opombe_par nchar(256)
AS
BEGIN
    SELECT Id_opombe
    FROM opombe
    WHERE (CHARINDEX(@opomba_par, opomba,0) > 0)
END

Thanks for all your replies in advance.

You must declare it as (n)varchar(x) if you need a variable length string:

ALTER PROCEDURE [dbo].[filter_opombe]
(
@opombe_par nvarchar(256)
)
AS
BEGIN
(
SELECT        Id_opombe
FROM            opombe
WHERE        (CHARINDEX(@opomba_par, opomba,0) > 0)
)
END

With a (n)char data type, shorter string will be stuffed with white space at the end: 'x' with data type nchar(5), becomes 'x    '.


Difficulty in framing a query I have a table with data as

A   51 12.01pm
A   53 03.00pm
B   51 04.00pm
C   51 11.55am
C   51 12.00pm
C   53 12.05pm
D   53 03.00pm
D   51 04.00pm
E   53 05.00pm
E   53 05.02pm
F   51 03.21pm
F   51 09.32pm

and my output should be the count of data having the latest status 53 with the latest time

Output should be A and C

WITH cte AS ( SELECT col1,
              FIRST_VALUE(col2) OVER (PARTITION BY col1
                                      ORDER BY col3 DESC) fv
              FROM table )
SELECT col1
FROM cte
WHERE fv = 53

If your server is more ancient than 2012, do the same by using ROW_NUMBER() and adding the condition rownumber=1.


Adding a Far DR secondary server Log shipping on already configured SQL server We have a setup where there are 3 SQL Server instances in which 1 primary,1 Near Secondary,1 Far Secondary.

So the there is already log shipping configured between Primary and Near DR Secondary.

Now i have to configure/Add log shipping between the Primary and the Far DR Secondary.

Need to add 1 database in the log shipping size is 1.1 TB

The issue is copying a full backup might take more than 12 hours from primary to really far DR and there is 4 schedules of daily full backup on Primary each of 6 hours interval.

Kindly help with a plan so that i can configure the log shipping on the Far DR without any delay and one more thing we cannot stop the existing log shipping config.

The issue is copying a full backup might take more than 12 hours from primary to really far DR and there is 4 schedules of daily full backup on Primary each of 6 hours interval.
... configure the log shipping on the Far DR without any delay ...

These two are competing items. If it takes 12 hours to transfer and you must transfer full backups every time then I don't see how that will fit in a 6 hour window, let alone be able to do anything of use with it.
You'll either need to push back on doing this or ask for better bandwidth.
If, however, you don't have to send the same full backup each time, then a one time full with logs between may be possible depending on the rate of change in the database. Obviously this all depends on what the requirements say.

[comment]...consider using mirroring or AlwaysOn High Availability Groups instead.

Potentially, depending upon the requirements (which we currently don't have or know), although this could lead to other undesirable consequences if the latency is very high. Availability groups do not like latency.


PK as ROWGUIDCOL or use a separate rowguid column? There's a long-winded debate going on here so I'd like to hear other opinions.

I have many tables with uniqueidentifier clustered PK. Whether this is a good idea is out of scope here (and it's not going to change anytime soon).

Now, the database has to be merge published and the DEVs are advocating the use of a separate rowguid column instead of marking the existing PK as the ROWGUIDCOL.

Basically, they say that the application should never bring into its domain something that is used by replication only (it's only "DBA stuff" for them).

From a performance standpoint, I see no reason why I should add a new column to do something I could do with an existing one. Moreover, since it's only "DBA stuff", why not let the DBA choose?

I kind of understand the DEVs' point, but I still disagree.

Thoughts?

EDIT: I just want to add that I'm in the minority in this debate and the DEVs questioning my position are people I respect and trust. This is the reason why I resorted to  asking for opinions.
I might also be missing something and could have misunderstood their point.

Basically, they say that the application should never bring into its domain something that is used by replication only (it's only "DBA stuff" for them).

I agree completely. But... the primary key isn't only used for replication (presumably the application uses it in some way). The argument makes no sense in this context.

In any event, as far as I'm aware, there are only 2 ways for this "DBA stuff" to cross the domain boundary:

If the application is using queries that reference the ROWGUIDCOL column like this:

DECLARE @a table (id uniqueidentifier ROWGUIDCOL);

SELECT ROWGUIDCOL FROM @a;

I'm assuming none of your columns have this property yet, so the application wouldn't be doing this. (By the way, ROWGUIDCOL is a completely separate concept from replication. It just so happens that merge replication uses it.)
The primary key column would no longer be updatable. If the application is doing this and changes aren't going to be made to use another algorithm, there's no choice but to add a new column to the table, and therefore no discussion is necessary.

Other than those behaviours, the ROWGUIDCOL property is completely transparent. You can add it, and the application would never know. Any type of data replication scenario should be as transparent as possible to applications.


trying to connect to MSSQL server using named pipe in HeidiSQL I'm trying to connect to a MSSQL server in our office from my local desktop using HeidiSQL. I am able to connect to this server with both SQL Server Management Studio and ODBC with no problems. The connection uses integrated Windows authentication. I installed the MS OLE DB SQL 18.2.2.0 x64 driver. My settings in HeidiSQL are:

network type: Microsoft SQL Server (named pipe)

library: MSOLEDBSQL

hostname: <server>\<instance>

use windows authentication is checked

every time I try to connect I get:

  SQL Error (0): SQL Server Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF] Login timeout expired

It doesn't tell me what protocol is being used, and I don't see any options to change it. Does "protocol" in this case refer to the library?

Is there some way to change the protocol in Heidi?

Try following:

Enable Named pipes visa configuration manager

Restart particular SQL Service
Have a look at SQL log (recent entries after restart), it must state the named pipe is ready without any error (as showed screenshot below)

If SQL error log looks good, try again with named pipe, make sure the hostname match with named pipe (configured in step:1)

If still no luck, you may proceed with TCP/IP protocol, just make sure you are able to do telnet localhost 1433 to be sure the TCP/IP is enable and ready for connections. If any issue, you can follow Step 1 and 2 for enabling TCP/IP, and add a New Rule at Windows Firewall to accept incoming connections on SQL Port# (default: 1433).

Additional steps for custom port configuration


Change the system wide default for maxrecursion How do I change the system-wide default value of MAXRECURSION?

By default it is 100, but I need to increase it to something like 1000.

I am unable to use query hints since I'm using a program that takes my query and executes it for me and I can't get around this limitation, unfortunately.

However, I do have admin rights on the server instance.  I've poked around in the server facets, but I don't see anything there related to query options or recursion.  I assume there has to be a place somewhere where I can update the system-wide default.

Any ideas?

If your queries have a common shape, you might be able to add the required maxrecursion hint using one or more plan guides.

There can be a knack to getting them right. If you add specific query details to your question, we might be able to work that out for you. Typically, you would trace the SQL actually hitting the server, or obtain a parameterized form using the built-in procedure sys.sp_get_query_template, and then create a TEMPLATE and/or OBJECT/SQL plan guide.

See the documentation for further information:

Plan Guides
Create a New Plan Guide
Create a Plan Guide for Parameterized Queries
Specify Query Parameterization Behavior by Using Plan Guides
Apply a Fixed Query Plan to a Plan Guide
Attach Query Hints to a Plan Guide
View Plan Guide Properties
Use SQL Server Profiler to Create and Test Plan Guides
Validate Plan Guides After Upgrade

Plan guides will need to be revalidated whenever the application code changes, and when SQL Server is patched or upgraded. This should just be part of your normal testing cycle.

Note that plan guide validation using sys.fn_validate_plan_guide may incorrectly report a failure if the guided statement references a temporary table. See this question:

Plan guide validation with fn_validate_plan_guide gives false positives

The Plan Guide Successful and Plan Guide Unsuccessful Profiler and Extended Events classes can also be used to monitor plan guide applications.

Connect was retired before the product improvement suggestion Allow MAXRECURSION limit values other than 100 for views and UDFs by Steve Kass was implemented. If you would like to take it up with Microsoft now, see the options at SQL Server help and feedback.


Manually modify master-bin.index I have an issue with MariaDB Server 5.5.56 replication. The slave got stuck and when I realized that we had already lost some of the bins. So this is the scenario :

In master :

MariaDB [(none)]> show binary logs;
+-------------------+------------+
| Log_name          | File_size  |
+-------------------+------------+
| master-bin.000320 | 1073742333 |
| master-bin.000321 | 1074247558 |
| master-bin.000322 |  753717941 |
| master-bin.000323 |  883803465 |
+-------------------+------------+
4 rows in set (0.01 sec)

cat master-bin.index
./master-bin.000320
./master-bin.000321
./master-bin.000322
./master-bin.000323

In slave ( excerpt )

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State:
              Master_Log_File: master-bin.000312
          Read_Master_Log_Pos: 405852801
               Relay_Log_File: mariadb-relay-bin.000942
                Relay_Log_Pos: 405852988
        Relay_Master_Log_File: master-bin.000312
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 405852703
              Relay_Log_Space: 405853426
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

So my idea to try and recover the slave is :

1.- In master : stop mariadb service
2.- In master : copy from backup bins 312 to 319
3.- In master : manually add bins 312 to 319 to master-bin.index
4.- In master : start mariadb service
5.- In slave : start slave

Would that work ? Is there any other way to solve my problem ? I know manually modifying master-bin.index is not recommended but I can not think of any other alternative to avoid recreating the slave.

Thanks in advance.

The best advice would be to rebuild the slave. Missing 8 binary logs means you may be missing a lot of transactions. Another option would be to execute CHANGE MASTER to and move to the next available binary log. You may need to skip slave errors along the way until the slave 'catches up' BUT you have to use pt-table-checksum and pt-table-sync to "resync" the data between these nodes.


How to use ON DUPLICATE KEY for UPDATE Consider a table of

CREATE TABLE test
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
external_id int(11),
number smallint(5),
value varchar(255),
UNIQUE INDEX (external_id, number),
PRIMARY KEY(id)
) ENGINE=InnoDB

Updating the value of number as

UPDATE test SET number=number + 1 WHERE external_id='X'

or

UPDATE test SET number='Y' WHERE external_id='X'

Since external_id-number couple is unique, it will not update if the new value of number exists.

How can I use something like INSERT ... ON DUPLICATE KEY to increase the value of number by number + 1 to reach an available value according to the UNIQUE Constraint and complete the UPDATE task?

First a sample table

mysql> drop database if exists ali;
Query OK, 1 row affected (0.10 sec)

mysql> create database ali;
Query OK, 1 row affected (0.00 sec)

mysql> use ali;
Database changed
mysql> CREATE TABLE test
    -> (
    -> id int(11) unsigned NOT NULL AUTO_INCREMENT,
    -> external_id int(11),
    -> number smallint(5),
    -> value varchar(255),
    -> UNIQUE INDEX (external_id, number),
    -> PRIMARY KEY(id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql>

Let's insert an initial row and SELECT it back

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (200,15,'Y')
    -> ON DUPLICATE KEY UPDATE number = number + 1;SELECT * FROM test;
Query OK, 1 row affected (0.06 sec)

+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     15 | Y     |
+----+-------------+--------+-------+
1 row in set (0.00 sec)

mysql>

Let's insert the same thing again

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (200,15,'Y')
    -> ON DUPLICATE KEY UPDATE number = number + 1;SELECT * FROM test;
Query OK, 2 rows affected (0.10 sec)

+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
+----+-------------+--------+-------+
1 row in set (0.00 sec)

mysql>

OK let's try the value column at the same time

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (201,15,'X')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,value = VALUES(value);
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     15 | X     |
+----+-------------+--------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (201,15,'X')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,value = VALUES(value);
Query OK, 2 rows affected (0.06 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     16 | X     |
+----+-------------+--------+-------+
2 rows in set (0.00 sec)

mysql>

This time change value and number

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (202,15,'Z')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,
    -> value = VALUES(value);
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     16 | X     |
|  8 |         202 |     15 | Z     |
+----+-------------+--------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (202,15,'A')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,
    -> value = VALUES(value);
Query OK, 2 rows affected (0.07 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     16 | X     |
|  8 |         202 |     16 | A     |
+----+-------------+--------+-------+
3 rows in set (0.00 sec)

mysql>

Hope this helps.

Give it a Try !!!


Data Access Tool failed to connect db2 I have a DB2 installed on windows 2012

Server name: test017330VM

OS: Windows Server 2012 R2 Datacenter

DB2 port:50000

DB2 Version:DB2 v11.1.1010.160

Database:dbtest.  (this is created by command 'DB2 Create database dbtest')

Local user:db2admin. (DB2 was installed under this account and it has the admin privilege on both windows and db2).

Password:Aa1!Aa1!;

I tried to run  Data Access Tool (v4) on the same server, but got the error immediate after I click the connect:

  Could not connect to data source 'New Data Source':
  An internal network library error has occurred. A network level syntax error has occurred.

Here is the connection string:

  Provider=DB2OLEDB;User ID=db2admin;Password=Aa1!Aa1!;Initial Catalog=dbtest;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=test017330VM;Network Port=50000;Package Collection=MSPKG;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/NT;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Datetime As Date=False;AutoCommit=True;Authentication=Server;Decimal As Numeric=False;Derive Parameters=False;LoadBalancing=False;Persist Security Info=True;Cache Authentication=False;Connection Pooling=False;

Please shed some light on it.

why it's a syntax error?
What is MSPKG  in the parameter Package Collection=MSPKG?
what is 'DB2/NT' correct?

resolved by upgrading the Data Access Tool (v4) to Data Access Tool (v5)


Answer to the new question:

SELECT `dept_name`
    , `emp_name`
    , `emp_sal`
    , `ej_joining date`
FROM (
    SELECT emp.`dept_name`, emp.`emp_name`, emp.`emp_sal`, emp.`ej_joining date`
        , @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
        , @dept_id := `emp_dept_id` as `dept_id`
    FROM (
        SELECT DT.`dept_name`
            , EMP.`emp_name`, EMP.`emp_sal`, EMP.`emp_dept_id`
            , EJ.`ej_joining date`
        FROM `Dept` DT
        INNER JOIN `Employee` EMP ON DT.`dept_id` = EMP.`emp_dept_id`
        INNER JOIN `JoiningDate` EJ ON EJ.`ej_emp_id` = EMP.`emp_id`
        WHERE DT.`dept_status` = '1' AND EMP.`emp_status` = '1'
        ORDER BY Dt.`dept_id`, RAND()
    ) emp
    CROSS JOIN (
        SELECT @dept_id := 0 as dept_id, @num := 0 as num
    ) v
) num
WHERE num.num <= 2
ORDER BY `dept_name`, num.num
;

SQL Fiddle

This answers the original question

This first query will randomly order rows by dept_id. Each row in each group of dept_id is given a incremental number (num) from 1 to n.

Random order query:

SELECT `emp_name`, `emp_sal`
        , @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
        , @dept_id := `emp_dept_id` as `emp_dept_id`
FROM (
    SELECT `emp_name`, `emp_sal`, `emp_dept_id`
    FROM `Employee`
    ORDER BY emp_dept_id, RAND()
) emp
CROSS JOIN (
    SELECT @dept_id := 0 as dept_id, @num := 0 as num, @numx := 0 as numx
) v

The Dept table can then be joined to this query. A filter on num value being 1 or 2 will give the first 2 rows of each dept_id. SQL Fiddle
The order changes for each execution and the first 2 rows change as well.

Main query:

SELECT DT.`dept_name`, num.`emp_name`, num.`emp_sal`
FROM `DEPT` DT
INNER JOIN (
    SELECT `emp_name`, `emp_sal`
            , @num := IF(@dept_id = `emp_dept_id`, @num +1, 1) as num
            , @dept_id := `emp_dept_id` as `emp_dept_id`
    FROM (
        SELECT `emp_name`, `emp_sal`, `emp_dept_id`
        FROM `Employee`
        ORDER BY emp_dept_id, RAND()
    ) emp
    CROSS JOIN (
        SELECT @dept_id := 0 as dept_id, @num := 0 as num
    ) v
) num
    ON DT.`dept_id` = num.`emp_dept_id`
WHERE num.num <= 2
ORDER BY  DT.`dept_name`, num.num


best column type for hashed data in mysql In my use case I have a table with a column containing hashed (sha256) IDs. I wonder which data type is better considering that It must be unique and I need to search on it.

The sha algorithm produce a binary stream out of the box. I can transform it hexadecimal or base64 before storing it. I wonder if it is convenient or necessary for my needs

If you are starting with hex, use UNHEX() when storing and HEX() when fetching.  Use BINARY(32) for the datatype.  This occupies a constant 32 bytes.

If you are starting with binary, then be careful when escaping to store/load.  Probably best to do a double convert each way.

Be sure to have lots of RAM.  Once the index becomes bigger than the buffer_pool, the system will slow down because of the randomness of sha256 (or any other hash or uuid).

If you go with Base64, then use CHAR(43) COLLATE ascii_bin, which will be a constant 43 bytes, and will be case-sensitive.

256 is gross overkill.  If you have about 2^85 entries, there is only one chance in 2^85 of having any hash collisions.  For a mere MD5, those numbers are 9 trillion.


is it reasonable to use NoSQL for my project Imagine you want to write a webapp for selling your car online.

A car has a lot of attributes. Like how many doors, color, aircondition, etc. Some of them are simple boolean values, others might need to store more information (number of doors).

Some Accounts will become upgraded to "re/taider" accounts and they will have the possibility to sell more then one car.

From what I learned at school this actually screams for a relational database model.
But I would really like to get into NoSQL.

Would it be very silly to use Mongodb or Couchdb in this case?

Here is a more specific question, which exceeds my understanding and might give you a clue of my thinking.

A car has many attributes and you want to search for a car with as many attributes you like.

Now the only way I know how to handle this in NoSQL is to have a lot of Map-reduced views.
However, with only 12 attributes there is a possibility of 4096 different combinations. One can not do 4096 map-reduces for this use case. It can't be the common way, I mean it does not feel right.

Given that you say you believe this data model screams "relational" the thought that comes to mind is, "I have a nail and I would really like to use a crescent wrench.... Can you?  Yes.  Are you aware of the tradeoffs?  Doubtful."

The basic point about NoSQL is that it makes sense when your data use cases are fairly simple and you have encapsulated that behind a full-featured API.  The example I give for an area where NoSQL would generally be very good would be the backplane of an LDAP directory forest.

For the application you mention your big issue is likely to be flexibility in reporting.  You would have to purpose build your reports in advance and that's the major cost.  For an application like this, that is a very big cost.

One other point I would make is that you may find that there are opportunities to use NoSQL along side the RDBMS.  For example you could implement a caching system for the web app using it.


Move (Restore) SQL Server 2005 database which does not exist before I did not ready about this but found out after trying to restore a database from remote server and it wont restore if the database did not exist before. What I would do  is

create a .bak file (backup the database)
Copy the .bak file to the remote server using shared folder
Run restore in SQL Server Management Studio to restore the database

It will give me error. I think I found it almost accidently (or out frustration, just restored it on top of the database, and it worked).

Now as I am typing the question, I realize move is probably not the same thing as restore. For restore, the database must already exist?

I am in the same position again to restore a database which does not exist on my system.

1. What procedure do I follow?

2. Do I need to set permission on the .bak file before restoring successfully on remote Server?

3. Or is there a better way?

Am I missing anything? I would  like to know, what are the rules when restoring (or moving) the database.

Edit : Adding the dialog error. I just repeated the process. I am not sure this was the exact error I was getting before. I renamed the original db that I have. Then I import using SSMS.

No, for a restore you don't have to have the database existing already (be mindful though to not have the with replace option set in your restore database command).

The below statement would create a new database from the backup:

RESTORE DATABASE [YourNewDbName]
FROM  DISK = N'C:\YourBackupFile.bak'
WITH  FILE = 1,
    MOVE N'YourDataFile' TO N'C:\YourNewDbName.mdf',
    MOVE N'YourLogFile' TO N'C:\YourNewDbName_1.ldf',
    NOUNLOAD,
    STATS = 10
GO

Obviously, modify the parameters accordingly.  But this doesn't require YourNewDbName to exist prior to the restore database.


In PostgreSQL is the row ordering preserved in functions and CTEs? In SELECT statements the ordering of the returned rows is not guaranteed if the ORDER BY clause is not specified. This is true for "normal" tables.

Is this also true for ordered tables generated using WITH expressions (CTEs)? Ordered tables returned by functions? I assume that not. Is this somewhere explicitly stated in the documentation?

Specifically, can I assume that this (more efficient) query:

WITH ordered AS ( SELECT * FROM table1 ORDER BY col1 )
SELECT sum(col2) result FROM
generate_series(0,50) nr,
LATERAL (SELECT * FROM ordered LIMIT 100 OFFSET nr*100) a
GROUP BY nr ORDER BY nr;

will be equivalent to this query:

SELECT sum(col2) result FROM
generate_series(0,50) nr,
LATERAL (SELECT * FROM table1 ORDER BY col1 LIMIT 100 OFFSET nr*100) a
GROUP BY nr ORDER BY nr;

For functions:

If I have such function:

CREATE FUNCTION do_sort(name text[]) RETURNS TABLE(name text) AS $$
SELECT name.name FROM unnest($1) name ORDER BY name.name ASC;
$$ LANGUAGE SQL IMMUTABLE;

can I make such wrapper function and assume that the array will always be correctly sorted?

CREATE FUNCTION do_sort_returns_array(name text[]) RETURNS text[] AS $$
SELECT array_agg(name) FROM do_sort($1);
$$ LANGUAGE SQL IMMUTABLE;

In SELECT statements the ordering of the returned rows is not guaranteed if the ORDER BY clause is not specified. This is true for all tables, simple or complicated queries.
Now, having that as basis, we should consider that Postgres has implemented CTEs in a peculiar way. They are always materialized (see: PostgreSQL’s CTEs are optimisation fences).
That doesn't mean that you should rely on this. When using a CTE, like in the first query, an ORDER BY should be added as well when referencing the CTE, as the ORDER BY inside the CTE may be removed (I'm not saying that it will always be removed as my tests show that a sort is performed. But it may be removed in a future optimizer change, as an ORDER BY without LIMIT is redundant):
WITH ordered AS ( SELECT * FROM table1 ORDER BY col1 )    -- redundant ORDER BY
SELECT sum(col2) result FROM
generate_series(0,50) nr,
LATERAL (SELECT * FROM ordered ORDER BY col1        -- ORDER BY added
         LIMIT 100 OFFSET nr*100) a
GROUP BY nr ORDER BY nr;

Update, from Craig Ringer, after a comment I made in his above linked  blog post:

It’s like quite a few other cases – right now PostgreSQL will always return the rows in the order they’re output by the CTE, but you shouldn’t technically rely on it. Who knows what future features could change that?
Unfortunately IIRC it’s also not smart enough to recognise that the rows are ordered correctly if you add another ORDER BY in the outer query. (Haven’t tested, but pretty sure). So it’s not necessarily free to do it the “right” way.

So, if you want the results ordered in a specific ordering, add an ORDER BY in the final SELECT. You could get away without it now (and you might get some slight performance gain) but there is no guarantee that this will not change in a future release.
Another reason for explicitly using ORDER BY is that you may not be always maintaining the code you write now. Another developer may try to reorganize the query without CTEs, using derived tables or LATERAL joins. They would need to know that your query relies in the order provided by the CTE so it needs to be either used in the final SELECT or commented or documented somewhere.

Now, another way to write the query would be adding a LIMIT in the CTE and using ROW_NUMBER():
WITH ordered AS
    ( SELECT col2, (ROW_NUMBER() OVER (ORDER BY col1) - 1) / 100 AS nr
      FROM table1
      ORDER BY col1 LIMIT 5100 )
SELECT n.nr, sum(o.col2) AS result
FROM generate_series(0, 50) AS n (nr)
    LEFT JOIN ordered AS o
      ON n.nr = o.nr
GROUP BY n.nr
ORDER BY n.nr ;

This way, the CTE will only be materialized for the 5100 rows you want and not for all (possibly millions) rows of the table. Even better if there is an index on (col1, col2). You could even remove the generate_series() and/or remove the CTE:
SELECT o.nr, sum(o.col2) AS result
FROM
    ( SELECT col2, (ROW_NUMBER() OVER (ORDER BY col1) - 1) / 100 AS nr
      FROM table1
      ORDER BY col1 LIMIT 5100
    ) AS o
GROUP BY o.nr
ORDER BY o.nr ;


Backup failure for a DB part of Always-On Availability group - SQL 2012 Ent Edition We have a DB that we are unable to take full backups of or able to join it in AG group. The T-logs are getting succesful backups. The DB is stuck in doing a CHECKPOINT that is blocked by a DB STARTUP process. Is there anyway to clear this internal process without having to reboot? It has been sitting like this for a few days. We are able to restore the DB using the T-logs but the DB is not highly available due to not being part of the AG. We are on SQL 2012 Enterprise edition. Please don't give me an answer that I need to upgrade to latest version.

Has anyone experienced this issue in SQL 2012 or any other version when using AAG?

MS discovered that this is an issue with 2012 CU3. We ran into this bug: https://support.microsoft.com/en-us/help/3033492/alwayson-availability-groups-may-be-reported-as-not-synchronizing-afte

This problem is resolved in SQL 2012 SP4.

In the interim - the solution is a reboot to get the DBs off the bad state.


Make a newer version of the LDF-file work with an older version of the MDF-file Is it possible to combine a "newer version" of the LDF file with an older version of the MDF file and keep changes logged in the LDF? Lets assume there has been no log-backups in the time between as I guess that would make this truly impossible.

Steps:

Database in full recovery mode
Take a full backup
Make some changes
Detach database
Make a filecopy of the LDF-file
Restore from full backup in step 2
Detach database
Make a filecopy of the MDF-file
Throw away the backup (important step)
Delete the database
Somehow make the MDF and LDF play together with the change made in
 step 3 still in place.

Just attaching the MDF and LDF gives an error:

  The log scan number (38:195:41) passed to log scan database 'mic' is
  not valid.

I also tried a sequence of

Create database
Take database offline
Replace the MDF and LDF files
Bring online
Do a log backup
Drop database
Create database with attach MDF-file only
Make full backup
Restore full backup (norecovery)
Restore log-backup from Step 5

I have a vivid memory that this worked just fine for a while but then it did not any more.

  The log in this backup set terminates at LSN 38000000021300001, which
  is too early to apply to the database. A more recent log backup that
  includes LSN 39000000006500001 can be restored.

Is it possible to combine a "newer version" of the LDF file with an older version of the MDF file and keep changes logged in the LDF?

Assuming you want the database to be transactionally consistent, no.

  Just attaching the MDF and LDF gives an error

In this case it's because the log number is no valid, for whatever reason given the database file's metadata. This is a symptom of the exact process you've described. In this case it'd just be better to use the create database to regenerate the log (or DBCC commands) because either way the database isn't going to be consistent which it already wouldn't be with the above steps you've outlined. Six of one, half dozen the other.


Using T-SQL to dynamically create Extended Properties I would like to add Description and Status in the Extended Properties of a large database to all my tables and columns so that someone can fill in the values.

It would be very laborious to add these two properties to each table and column. Is there a way to use T-SQL to be able to generate these via script?

As Jon mentioned, you can do it using system stored procedures.

Ref: Glenn Berry wrote this for 2008 and this is to add extended property on a table. Also MSSQL Tips has a good example as well.

You can adapt his script for columns as well.

-- Routine to add or update an extended property on a table
DECLARE @TableName SYSNAME
set @TableName = N'Kin_Test';   -- Name of table
DECLARE @MS_DescriptionValue NVARCHAR(200);
SET @MS_DescriptionValue = N'Short table description - Kin';    -- Short description
DECLARE @MS_Description NVARCHAR(200)
set @MS_Description = NULL;

SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(200)) AS [MS_Description]
FROM sys.extended_properties AS ep
WHERE ep.major_id = OBJECT_ID(@TableName)
AND ep.name = N'MS_Description' AND ep.minor_id = 0);

IF @MS_Description IS NULL
    BEGIN
        EXEC sys.sp_addextendedproperty
         @name  = N'MS_Description',
         @value = @MS_DescriptionValue,
         @level0type = N'SCHEMA',
         @level0name = N'dbo',
         @level1type = N'TABLE',
         @level1name = @TableName;
    END
ELSE
    BEGIN
        EXEC sys.sp_updateextendedproperty
         @name  = N'MS_Description',
         @value = @MS_DescriptionValue,
         @level0type = N'SCHEMA',
         @level0name = N'dbo',
         @level1type = N'TABLE',
         @level1name = @TableName;
    END
GO
-- End of routine


How do I stop/disable PITR and safely clean WAL segments? Our production server is running PostgreSQL v8.2.3 on CentOS release 5.2 (Final).

We've setup PITR in our production server.  For some reasons, after setting up PITR, we're not able to manage and maintain it.  Eventually, our WAL archive drive (secondary drive) has become full (100% use) and additional WAL archive segments that are to be archived got accumulated in pg_xlog/ directory itself (which is available in primary drive)

PITR SETUP DETAILS

There are 2 drives:

Primary drive (pgsql/data/ directory resides) is 400 GB.
Secondary drive (WAL archives) is 300 GB.  All WAL archives are written to this secondary drive.

Now, we've decided to stop/disable PITR.

My question is, in this situation, how do I stop/disable PITR and safely clean up all WAL segments from both the drives?

What are the recommended/suggested ways? Experts advice/idea/suggestion on this are highly appreciated.

To stop the archiving in 8.2, you set archive_command to empty ('') and restart the server.  The data directory should clean itself up.  The archives on your secondary drive you can just throw away.


How do you kick users out of a SQL Server 2008 database? We need to do a restore, and cannot because other users are connected.
We thought we had disconnected every process, but apparently not.

How can we, from Management Studio, kick off everyone else so we can do this backup?

I always use the following:

USE master; -- get out of dbname myself
GO
-- kick all other users out:
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
-- prevent sessions from re-establishing connection:
ALTER DATABASE [dbname] SET OFFLINE;

Sometimes this may take a while, and sometimes it is blocked because you're the one running it, and you have an active connection to the database. Check for other query windows that might have the same database context - this can include open dialogs, Object Explorer, IntelliSense, long-running jobs, etc.

When I'm done making my changes to that database's config, I simply:

ALTER DATABASE [dbname] SET ONLINE;
ALTER DATABASE [dbname] SET MULTI_USER;

Though, sometimes, the thing I need to do to that database requires the database to be online, so sometimes I have to leave it in single-user mode and do this:

ALTER DATABASE [dbname] SET ONLINE;
GO
USE [dbname];

Now I can make my changes, and then when I'm ready for other users to connect, simply:

ALTER DATABASE [dbname] SET MULTI_USER;


Single node SQL Server 2014 cluster at DR environment We are setting up a new Disaster Recovery environment.
On Production we have SQL Server cluster with two nodes.
For DR we decided that one node should be enough. The plan is to add the second node ASAP if disaster happens.

We requested our managed cloud company to create a single node cluster for us. However, they try to discourage us from doing that and suggest installing non-clustered instance instead, so when disaster happens we will build brand a brand new cluster with two brand new nodes and then migrate to it from non-clustered instance.
This option sounds like more work for us if disaster happens.
We will need to migrate all the databases to the clustered instance from non-clustered instance. I also expect a longer downtime if we do that, as the opposite to just adding a second node to the existing cluster.

Tech person from this cloud company said that single node cluster is complicated solution and is prone to all kinds of problems like "split brain", that it can stuck all of a sudden and they have seen so many issues with single cluster nodes. He said that Microsoft does not recommend having single node clusters. He wasn't very specific and I'm not sure I understood what exactly problems we will have.

I tried to google for some specific problems, but failed to find were Microsoft would suggest to avoid having single node clusters, especially when it comes to DR.

Is there a reason to avoid single node cluster solution for DR and go with non-clustered instance instead?

Overall, there is no need for a cluster IMHO. If you need HA at the DR site, then do it - but if you don't, I don't see a purpose for it as the stand alone will work just fine. The reason I say this, is that they will be two distinct clusters which makes it a little hard to justify.

  Tech person from this cloud company said that single node cluster is
  complicated solution and is prone to all kinds of problems like "split
  brain", that it can stuck all of a sudden and they have seen so many
  issues with single cluster nodes.

So I don't know if the tech person didn't understand, or what, but you can't split brain with a single node... so, I don't know why (s)he thought that, but that's incorrect. There really aren't any issues with a single node cluster other than it's a single node cluster which defeats the point of having a cluster TBH. It's a different story if it'll eventually be built out, but if you're going to wait for a disaster to do so... you're already behind the ball.

Keeping just a single instance, not in a cluster would be faster to replicate as well should something happen.

In the end it's up to you, unless I had a much more compelling reason I'd go with just a single server without clustering. Also, don't listen to their techs anymore ;)


Grant reference on type What does GRANT REFERENCES ON TYPE::dbo.myType TO public exactly mean?

I found this statement together with every single user defined type we have. I wonder if this is useful or not. AFAIK this permission is required only for creation of new objects (e.g. a table or function) to reference the data type - so in case the regular database user will never create new database schema objects, does this kind of explicit permission make any sense?

According to BOL, the REFERENCES permission allows the following:

  The REFERENCES permission on a table is needed to create a FOREIGN KEY
  constraint that references that table.

  The REFERENCES permission is needed on an object to create a FUNCTION
  or VIEW with the WITH SCHEMABINDING clause that references that
  object.

There is no reason for a public user to have this permission when they are unable to CREATE or ALTER any other objects.


MYSQL Stored procedure to fetch entire row from a table I am a beginner to SQL and currently practicing the concept of the stored procedure. I have a table Fetch_Marks that contains 20 records with Student_No and Marks as columns. Now I would like to create a stored procedure for this in which the input parameter will be Student_No and when we enter the input (Let's say I gave the Student_No as 1), it should fetch the row of that particular student from the table Fetch_Marks. Is there any workaround for this? If yes, please help.

You basically don't need a procedure for that.
Procedures get interesting with CURSOR and LOOP when you need "normal" programming besides SQL to makes some thong much more complex
DROP procedure IF EXISTS `proc_get Student`;

DELIMITER $$
CREATE PROCEDURE `proc_get Student` ( IN _student_no BIGINT)
BEGIN
    SELECT * FROM Fetch_Marks WHERE Student_No  = _student_no;
END$$

DELIMITER ;

But I am guessing this is some kind of homework, the variable _student_no is as it is because you have so a optical marker that it is a locally defined variable
Here's another example:
In this, we are adding a second IN variable and in which both are checked if they are NULL or NOT and then selects the wanted data
DROP procedure IF EXISTS `proc_get Student`;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `proc_get Student`( IN _student_no BIGINT,IN _roll_no BIGINT)
BEGIN
    IF ( _student_no IS NOT NULL AND _roll_no IS NULL) THEN
        SELECT * FROM Fetch_Marks WHERE Student_No  = _student_no;
    END IF;
END$$

DELIMITER ;


List table sizes for all tables on all databases Is there a simple way of listing the size of every table in every database on a MSSQL server?

I have used a query on sys.tables to get results for a single database, but we have >100 databases per server, so a way of getting the same results but for all databases would be great.

Currently I'm having to create a temporary list of databases from master.sys.databases, and then iterate over that with a cursor, building a query and inserting the results into a temp table with EXEC sp_executeSQL @SQLString.

If you wanted to get this across all of your environment, for all of your databases...and you don't mind using PowerShell... You will need run this from a machine that at least has SQL Server 2008 Management Studio installed.

# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

function Get-TableSize ([string[]]$server) {
    foreach ($srv in $server) {
        $s = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $srv

        $s.Databases.Tables |
            ? {-Not $_.IsSystemObject} |
                Select @{Label="Server";Expression={$srv}},
                    @{Label="DatabaseName";Expression={$_.Parent}},
                    @{Label="TableName";Expression={$_.Name}},
                    @{Label="SizeKB";Expression={$_.DataSpaceUsed}}
    }
}

As labeled the DataSpaceUsed SMO object outputs in "KB", you can modify this to be the measurement of your choice by just putting the abbreviated reference for it. So if I wanted "MB": $_.DataSpaceUsed/1MB.

In the function ([string[]]$server), the brackets "[]" mean the parameter accepts an array of objects. So if you have your servers listed in a file you can call the function like so:

$list = get-content .\ServerList.txt
Get-TableSize -server $list | Out-GridView

I prefer using Out-GridView initially to review the output, and it copies easily straight into Excel for me. You can also output this to the other supported formats of PowerShell if desired.

Example with screenshot, you can also just list the servers out:


Is it dangerous to use Linked Server? (SQL Server 2008) I wonder what are the security risks in using Linked Server.

You are creating a path to another location, be it SQL Server, Oracle, DB2 whatever.  So the security risks are there but can be whatever you make of them.  For example if you set up a linked server to a SQL Server and tell it to impersonate SA for every connection then the security risks are massive.  If however you tell it to only use the users current security context the risks are less so.  With any additional "option" that you turn on or use you increase your security risks by a certain amount.  You just need to make sure that you do your due diligence about how you set it up.

Personally I prefer to use the "Be made using the login's current security context" option.  No SQL Server IDs will be able to use it and only AD logins that are on both machines will be able to do so.  If you want to use the option "Be made using this security context" option then make sure that the user you are giving out has minimal permissions and that those are permissions you are willing to give out to EVERYONE.


Adding windows domain group to ORA_DBA not working in Oracle 12c I am running into an odd situation with adding windows domain group to ORA_DBA group to grant sysdba privileges.

I am getting an invalid username/password - logon denied error while connecting to an instance  in Oracle 12c.

But, if I add my individual windows account to ORA_DBA group, I am able to login just fine.

I have tried to research for an entire day but haven't had much luck. Any help would be greatly appreciated.

  Note : The windows domain group is part of the Administrator group on the server.

This is how my sqlnet.ora file looks like:

SQLNET.AUTHENTICATION_SERVICES = (NTS)
SQLNET.INBOUND_CONNECT_TIMEOUT = 120
NAMES.DIRECTORY_PATH =(TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = WORLD
NAMES.DEFAULT_ZONE = WORLD
TRACE_LEVEL_CLIENT = OFF
AUTOMATIC_IPC = OFF

Most likely you can't.

As part that oracle documentation is clear, you have to be part of the ORA_DBA group to have the sysdba privilege with your Windows account. Being in a subgroup means that you have all the privileges of the parent group without being part of it As the same logic of oracle privileges and roles.


Getting percentages for each day/category Using MySql 8.0, I have a table as below, and I'm trying to get the percentage of cancellations for each day.
| Request_at | Status              | Freq |
| ---------- | ------------------- | ---- |
| 2013-10-01 | completed           | 2    |
| 2013-10-01 | cancelled_by_client | 1    |
| 2013-10-02 | completed           | 2    |
| 2013-10-03 | completed           | 1    |
| 2013-10-03 | cancelled_by_driver | 1    |

How do I do so? The result should give:
| Date       | % cancel   |
| ---------- | -----------|
| 2013-10-01 |       0.33 |
| 2013-10-02 |       0.00 |
| 2013-10-03 |       0.50 |

SELECT Request_at AS `Date`,
       SUM(CASE WHEN Status LIKE 'cancelled%'
                THEN Freq
                ELSE 0
                END) / SUM(Freq) AS `% cancel`
FROM source_table
GROUP BY Request_at


Close button action when manually running a job I started a job from SSMS on my laptop using Right Click > Start Job at Step... When I was ready to be done for the day the job was still running so I selected Close thinking it was operating on the server and would continue on the server.

Today I couldn't find the job for that run so I'm second guessing how that popup operates (screen shot of popup in question below).

What happens when the Close button is clicked? If the job is terminated how exactly is it terminated (e.g. something trappable; the job in question is an Operation System type running under a proxy account).

The "close" button simply closes the dialog; it does not stop the running job.

You can use the following stored procedure to see the current runtime state of a given job:

EXEC msdb.dbo.sp_help_jobactivity @job_name = '<job name here>';

To prove the "close" button does not actually stop a running job, I've created a test job:

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TestJob',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=2,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_page_operator_name=N'<Operator Name>', @job_id = @jobId OUTPUT;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'WAITFOR DELAY ''00:00:15'';',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The job takes 15 seconds to complete, by virtue of the WAITFOR DELAY statement.  It does nothing else.

Open a new query window, and add the following T-SQL to it:

EXEC msdb.dbo.sp_help_jobactivity @job_name = 'TestJob';

Run the job via the SSMS user interface, and immediately hit the "Close" button.
Run the job activity query in step 1 by hitting the F5 key or clicking the "Execute" button.

The status of the job will be shown, something like:

╔════════════╦══════════════════════════════════════╦══════════╦═════════════════════════╦══════════════════════╦═════════════╦═════════════════════════╦═══════════════════════╦═════════════════════════╦═════════════════════╦═════════════════════════╦════════════════╦═════════╦════════════╦═════════════════════╦═════════════════════╦═══════════════════╗
║ session_id ║                job_id                ║ job_name ║   run_requested_date    ║ run_requested_source ║ queued_date ║  start_execution_date   ║ last_executed_step_id ║ last_executed_step_date ║ stop_execution_date ║ next_scheduled_run_date ║ job_history_id ║ message ║ run_status ║ operator_id_emailed ║ operator_id_netsent ║ operator_id_paged ║
╠════════════╬══════════════════════════════════════╬══════════╬═════════════════════════╬══════════════════════╬═════════════╬═════════════════════════╬═══════════════════════╬═════════════════════════╬═════════════════════╬═════════════════════════╬════════════════╬═════════╬════════════╬═════════════════════╬═════════════════════╬═══════════════════╣
║      34156 ║ 2BDBF2EA-7E23-447B-A070-97274FFD7EF5 ║ TestJob  ║ 2018-01-31 08:51:12.000 ║                    4 ║ NULL        ║ 2018-01-31 08:51:13.000 ║ NULL                  ║ NULL                    ║ NULL                ║ NULL                    ║ NULL           ║ NULL    ║ NULL       ║ NULL                ║ NULL                ║ NULL              ║
╚════════════╩══════════════════════════════════════╩══════════╩═════════════════════════╩══════════════════════╩═════════════╩═════════════════════════╩═══════════════════════╩═════════════════════════╩═════════════════════╩═════════════════════════╩════════════════╩═════════╩════════════╩═════════════════════╩═════════════════════╩═══════════════════╝

In the above output, the stop_execution_date column is NULL, indicating the job is still running.  If you wait 15 seconds for the job to complete, and re-run step 1, you'll see the output is changed to reflect the status of the job, as in:

╔════════════╦══════════════════════════════════════╦══════════╦═════════════════════════╦══════════════════════╦═════════════╦═════════════════════════╦═══════════════════════╦═════════════════════════╦═════════════════════════╦═════════════════════════╦════════════════╦═══════════════════════════════════════════════════════════════════════════════════════════════╦════════════╦═════════════════════╦═════════════════════╦═══════════════════╗
║ session_id ║                job_id                ║ job_name ║   run_requested_date    ║ run_requested_source ║ queued_date ║  start_execution_date   ║ last_executed_step_id ║ last_executed_step_date ║   stop_execution_date   ║ next_scheduled_run_date ║ job_history_id ║                                            message                                            ║ run_status ║ operator_id_emailed ║ operator_id_netsent ║ operator_id_paged ║
╠════════════╬══════════════════════════════════════╬══════════╬═════════════════════════╬══════════════════════╬═════════════╬═════════════════════════╬═══════════════════════╬═════════════════════════╬═════════════════════════╬═════════════════════════╬════════════════╬═══════════════════════════════════════════════════════════════════════════════════════════════╬════════════╬═════════════════════╬═════════════════════╬═══════════════════╣
║      34156 ║ 2BDBF2EA-7E23-447B-A070-97274FFD7EF5 ║ TestJob  ║ 2018-01-31 08:51:12.000 ║                    4 ║ NULL        ║ 2018-01-31 08:51:13.000 ║                     1 ║ 2018-01-31 08:51:13.000 ║ 2018-01-31 08:51:28.000 ║ NULL                    ║        3514780 ║ The job succeeded.  The Job was invoked by User me.  The last step to run was step 1 (Step1). ║          1 ║                   0 ║                   0 ║                 0 ║
╚════════════╩══════════════════════════════════════╩══════════╩═════════════════════════╩══════════════════════╩═════════════╩═════════════════════════╩═══════════════════════╩═════════════════════════╩═════════════════════════╩═════════════════════════╩════════════════╩═══════════════════════════════════════════════════════════════════════════════════════════════╩════════════╩═════════════════════╩═════════════════════╩═══════════════════╝

The dbo.sp_help_jobactivity stored procedure references the dbo.sysjobactivity table.  Microsoft docs for that table shows they use the following construct to determine the current status of a job:

CASE
    WHEN sja.start_execution_date IS NULL THEN 'Not running'
    WHEN sja.start_execution_date IS NOT NULL
        AND sja.stop_execution_date IS NULL THEN 'Running'
    WHEN sja.start_execution_date IS NOT NULL
        AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus'


Postgres - MultiXactId error I have a table which has just started giving me the following error when trying to set a primary key, create an index or take a backup:

ERROR: MultiXactId 1092514672 has not been created yet -- apparent wraparound
SQL state: XX000

After a bit of searching online, I've not found a clear solution to the issue. There is some indication it might be a bug in older versions of Postgres.

I tried taking a backup of the table using pg_dump, as one option might be to just restore a fresh version, but got the same error.

Another possible solution I came across was to run select * from table for update;, but this also does not work with the same error given.

Running Postgres 9.4.3 on Debian 8.1. (Postgres has not been updated recently, thought I'd mention this as for some people this seems to cause the appearance of the error.)

Has anyone come across this before? Any ideas for a solution?

Update:
As suggested in the answer below from Erwin, I tried VACUUM FREEZE tbl;, but this also returned the same error.

Update 2: I have updated to Postgres version 9.4.8. This has had no effect, the error remains.

I would advise to backup first ...  but that's a catch 22, since you can't.

Either way, since the error revolves around a fictional xid wraparound I thought this might solve your immediate problem:

VACUUM FREEZE tbl;

The chapter Preventing Transaction ID Wraparound Failures in the manual has details.

But turns out, this runs into the same problem.

Of course, you can delete and recreate the table if you have the data outside Postgres.

The only remaining option I see is to upgrade to the latest point release and hope your problem is covered in the various patches that have been added, like @a_horse commented.

However, after some more research I found that the core hackers have been working on a solution for just this problem in the past weeks.
Details in this thread on pgsql-hackers.
The commit message.

So I would hold back and upgrade straight to Postgres 9.4.9, which should be out soon:
The next point release is planned for August 11th, 2016 at the latest.


Normalizing data for joins, dedicated columns, a view or.. something else? As an example, i have the following tables

USERS, USER_JOBS and USER_HOUSES

USER_JOBS keeps a list of jobs for a given user_id

USER_HOUSES keeps a list of houses for a given user_id

the common use case is to see a user, his current job and his current house.

it looks like this :

To get the result i want i would need to join USERS with USER_JOBS and USER_HOUSES using a complex query (matching the latest seq in jobs and houses by last_update column)

here's one for just joining with USER_JOBS

SELECT USERS.USER_ID,
       USERS.DESCRIPTION,
       USER_JOBS.JOB_ID
  FROM USERS, USER_JOBS
 WHERE USER_JOBS.USER_ID = USERS.USER_ID
   AND USER_JOBS.SEQ = (SELECT B.SEQ
                          FROM USER_JOBS B
                         WHERE B.USER_ID = USERS.USER_ID
                           AND B.LAST_UPDATE = (SELECT MAX(C.LAST_UPDATE)
                                                  FROM USER_JOBS C
                                                 WHERE C.USER_ID = USERS.USER_ID))

(not even sure if that works, either way.. not fun)

I'm trying to find the best alternative, since there might be a lot of these

alternative A:

I create columns that make this a straight join to the corresponding SEQ in either table
the actually work happens when those tables are written to (either via trigger, or manually) to update the main tables 'pointers' to the latest seq.

so harder write (although not that bad), much easier read.

PROS:
 * easier queries
 *
alternative B:

create a view that does the complex SQL of joining

There is no generic best solution, normalization is a trade-off between time, space, redundancy and its consequences.

Given your use case is to see a user, his current job and his current house, the original model is fine. And I think the SEQ column is unnecessary.

SEQ and LAST_UPDATE columns are redundant, they both contain information about a change. SEQ simply represents a change number, but LAST_UPDATE even contains the date of change. The number of changes can be simply found by counting rows for a specific USER_ID, so unless you have multiple changes in a second (the smallest unit a DATE type can store), the SEQ column is not mandatory, and USER_ID, LAST_UPDATE could be the PK. The query you write is very real, I have seen queries like that a lot of times, but you can do even better with analytic functions:

SELECT U.USER_ID,
       U.DESCRIPTION,
       UJ.JOB_ID
  FROM USERS U,
  ( SELECT JOB_ID
    FROM (SELECT JOB_ID, ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY LAST_UPDATE DESC) as R FROM USER_JOBS)
    WHERE R = 1
  ) UJ
 WHERE U.USER_ID = UJ.USER_ID AND U.USER_ID = :B1;


Issues installing SQL Server 2012 I am trying to install SQL Server 2012. During the install I get an error saying

  Error installing SQL Server Database Engine Services Instance Features
  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: TCP
  Provider, error: 0 - No connection could be made because the target
  machine actively refused it.) Error code: 0x84BB0001

I have run the setup  and given my user login and password as the account and password credentials for the all SQL server services (I'm happy with this, just need the program up and running to extract some data from a .mdf file)

I have set the database engine configuration to Mixed Mode and specified my password for the system administrator account.

The database engine does not install and the program tells me to uninstall these components and re-install them. I have done that several times now (using different credentials etc) not sure what to do next.

I have previously had SQL 2008 running on this machine so dont think there would be anything preventing me from getting SQL 2012 to work?

I also managed to install the express version which installs fine. But the .mdf file I need to use is larger than 10gb.

The error message is the generic "I can't reach the SQL instance you've specified."  There are no components to "uninstall/reinstall", mixed mode/Windows mode are simply configuration settings.  I generally recommend you run in mixed mode.

Two things to check:

If this is a SQL Express instance, you should make sure your connecting to [Computer Name]\SQLEXPRESS.  SQL Express installs as a named instance, so as such needs to be connected to as a named instance.
By default, SQL Server installs without TCP protocols enabled, which would prevent most connections.  Open your SQL Server Configuration Manager, browse to SQL Server Network Configuration, and then Protocols for SQLEXPRESS.  Select that and look for TCP/IP and make sure it's enabled.

As for your file being larger than 10 GB, you'll need to use Standard Edition or higher, nothing you can do to get around that.  You can have a separate instance running alongside the Express instance, but if you want you can simply uninstall your Express instance as you would any other Windows program.


SQL Server help tables? If I write in SQL Server :

SELECT number FROM master..spt_values WHERE Type = 'P' ORDER BY Number

I get a list of sequential numbers which I can already use, instead of writing a table with sequential numbers.

This can be much helpful for small joins, row numbering etc.

I was wondering if there are other hidden tables like this one which can help me do other things as well? For example, I might want to select someNonImportantTextColmn from master..unknown and to apply a function of mine.

Where can I find a complete list of these "help tables"?

spt_values is not exactly a help table for your use. It is an undocumented system table used by internal stored procedures for a variety of tasks.

Since it contains neither unique nor contiguous numbers, and because it is undocumented and unsupported (and could simply vanish in a future version), I strongly recommend you don't use this table for anything.

You can always generate a sequence based on row_number from documented and supported system views, such as sys.all_objects, sys.all_columns, or a cross join of the two if you need a large number.

Or just build a Numbers table and store the numbers for yourself. This is likely to be more efficient in most cases because, if you use it enough, it will always be accessed in memory without having to calculate the sequence.


Change AWS's Read Replica's streaming replication to trigger based replication We have a Postgres RDS instance for which we have created a read replica instance. The problem we are facing is that, that whenever there is a long running query(about 30-40 mins) on Read Replica, it doesn't complete giving a conflict error. The reason for this conflict is that there was an update on the main RDS, which was getting reflected to Read Replica as it is following streaming replication approach.
For this very reason and also due to new requirement, we need to make this replication process daily, i.e. instead of continous updation we need a trigger based approach(something like a daily backup approach).

I saw in the AWS's console,there are various parameters we can set. Is there some parameter which I can set to achieve my requirement? Or the only way out for me is to delete the read replica instance and use a trigger based replication tool like Bucardo and use an EC2 instance for this?

I would really prefer the approach through which I could tweak some parameter and achieve my objective without having to delete the Read Replica instance.

The problem we are facing is that, that whenever there is a long running query(about 30-40 mins) on Read Replica, it doesn't complete giving a conflict error.

This behavior is controlled by the parameters max_standby_streaming_delay / max_standby_archive_delay. You can fiddle with these parameters in the RDS Parameter Group used by your Read Replica instance to allow more time for queries against your Read Replica to complete.

  For this very reason and also due to new requirement, we need to make this replication process daily, i.e. instead of continous updation we need a trigger based approach(something like a daily backup approach).

If you'd like a snapshot of your primary database refreshed nightly, you could do this with a cron job restoring RDS snapshots every night. I don't think RDS has a button to do this automatically for you, but it shouldn't be too hard to script up a nightly create-db-snapshot + restore-db-instance-from-db-snapshot using the AWS CLI, or boto, or whatever interface to AWS you like. You could even maintain a Route53 entry which would always point to the most-recent instance, and leave the old instances lingering for a day or so before being killed off, so that sessions running against existing instances overnight wouldn't be interrupted.

  I saw in the AWS's console,there are various parameters we can set. Is there some parameter which I can set to achieve my requirement? Or the only way out for me is to delete the read replica instance and use a trigger based replication tool like Bucardo and use an EC2 instance for this?

Supposedly it is possible to hook up Bucardo to RDS now that RDS Postgres supports the session replication role, but if you want a nightly snapshot I think you'll be much better off using RDS instance snapshots.


How to generate unique Auto increament id field across multiple data centers I have multiple mysql servers deployed in three different data centers. Now my database has few tables with Auto increment id column. I want these columns in all three data center must have unique id across the data centers. Is it possible in Mysql or i need to generate these columns programmatically ?

For example in data center D1 has table T1 with id 1, then data centers D2 and D3 must not generate id 1 for table T1.

If you are using replication to 'replicate' data between the different data centers then you can use something like:

auto_increment_increment=3

auto_increment_offset=1 #data center 1

auto_increment_offset=2 #data center 2

auto_increment_offset=3 #data center 3

which will then offset the auto increment accordingly. e.g.

data center1 - 1, 4, 7

data center2 - 2, 5, 8

data center3 - 3, 6, 9


SQL Return Count of Answers with 0's for Choices from another table My question went dead after one unhelpful answer on SO here

I need this to be optimized because it will be called often. It is going into a stored procedure.

I am given @slide (tblILDSlide.id) and @sessionid (tblILDSession.id)

I need my results to look like this:

--lets say students had answered a(3),b(10),c(0),d(5) in a survey (test)
 |Answer Counts
0|3
1|10
2|0 --this needs to show as a zero and not be skipped
3|5

Since questions will each have different numbers of possible answers I need to have the rows seeded from the @@rowcount of Choice for the QID that is here:

**dbo.tblSurveyAnswerChoice
 QID (int, not null)
 Sequence (int, null)
 Choice (nvarchar(100), null) --this column will vary and is for choices
 --like true/false, Yes, No, Red Blue,
 --or even Completely Agree,Somewhat Agree...,Completely Disagree

To get the QID I have to use @slide and:

**dbo.tblILDSlide
 id (PK, int, not null)
 slide_type(FK, int, not null)
 question_id(FK, int, null)
 media_url(nvarchar(100), null)

I need a row for every choice (with zeros for choices with no responses). The responses are kept here:

 --there can be more than one response per question
 --like for multiple-multiple choice (check all that apply)
**dbo.tblResponses
 response_id(FK, int, not null)
 answer_sequence (int, null) --accepts 1,2,3 etc for choice
 other (nvarchar(50), null) --for text answers

To get them I have to use response_id which I can get by using @sessionid AND @slide here:

**dbo.tblSlideResponseInfo
 id (PK, int, not null)
 user_id(nvarchar(50), not null)
 slide_id (FK, int, not null)
 response_date (datetime, not null)
 session_id (FK, int, not null)

Copied from the post at stackoverflow:  My problem is when there is a possible choice with no answers I get no count. So I know I need to grab the possible choices which are available [in the other table ...]  I am thinking I need to return a blank row for every answer that exists in the tblSurveyAnswerChoices then somehow join the other counts to that, but I can't figure it out.

(copying my own answer from the same question on StackOverflow)

Your post already includes a good answer:  "I am thinking I need to return a blank row for every answer that exists in the tblSurveyAnswerChoices then somehow join the other counts to that."

Here is one way to do so:

select choice, ifnull(countanswervalue,0)
from tblsurveyanswerchoice
left join
    (select answer_sequence, count(answer_sequence) as countanswervalue
    from tblslideresponses
    group by answer_sequence)
    as countanswer on countanswer.answer_sequence=tblsurveyanswerchoice.sequence
/* the where clause is what you already posted */
where qid in (select question_id from tblidslide where id = @slide)

The use of left join ensures that any choice in the first table will be listed.  By default, left join would return NULL for items not existing in the second table, but ifnull lets you replace NULL with 0.

NOTE: ifnull in MySQL should be replaced by isnull in TSQL


Mysql join between three tables query I'm new to Mysql, just started today. I have to use the syntax shown here to add values, so I want to get the director name from the movie with id 289

INSERT INTO `db`.`movies` (`id`, `name`)
VALUES (289,"The movie title");

INSERT INTO `db`.`ddirectos` (`directorsid`, `moviesid`)
VALUES (1,289);

INSERT INTO `db`.`directors` (`id`, `name`,`last`)
VALUES (1,"roxy","roxylast");

SELECT dir.name
    FROM directors dir
    INNER JOIN ddirectos ddir ON    dir.id = ddir.directorsid
    INNER JOIN movies mov ON mov.id = ddir.moviesid
    WHERE mov.id = 289

I believe this is what you are looking for.


Can I Improve the design of my Transaction Table? I have a transaction table that logs credits and debits. The numbers are always int (-1 to usually 100). My issue is that Debits have a lot more data than credits, and it feels weird having this data in the same table.

Columns
ID |
ClientID |
CardCodeID |
ClientDeviceID |
ClientEmployeeID |
ProjectID |
PlantID |
MaterialID |
Amount |
CreatedAt

A credit only uses ClientID, Amount,MaterialID and CreatedAt, and a Debit uses All the columns. Is there a more effecient way to design this table?

Given balances are a sum of amounts, you are storing one of debits or credits as negative right?, the same table is ok. Having a few NULLable fields is ok too even if slightly redundant.


Automatically execute query in MS SQL Studio every hour I support an application in a big enterprise, one of my roles is to clean-up data. There is a query I need to execute every hour, and I would like to automate it. Due to organization policies, I can't create SQL Server Agent jobs or modify schema, I can only manipulate data.

An endless

WHILE(1=1)
BEGIN
WAITFOR DELAY '01:00';
--do work
END

does the job for me, but I shrug at the thought of a perma-open connection.

Ideally, I would script the MS SS itself to execute a given piece of code every hour, but I'm not sure if that is possible.

Is there any solution to this problem?

Your friend is sqlcmd (Microsoft Technet)

Create a SQL file with the script required to run your cleanup job
Run the script with sqlcmd.exe and any required parameters
Create a Windows Scheduled Task and add the command with all the required parameters

E.g.

sqlcmd -d YOUR_DB -E -i YOUR_SCRIPT.SQL -o OUTPUTFILE.TXT

Good luck.


Unexpected growth Transaction log file 100 GB which is part of Transactional Replication I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.

Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.

While database under testing mode...

Subscriber of replication is also primary replica of Always On Availability Group.

How to reduce the size of transaction log file?

I had taken log backup and in full recovery model.

SELECT name, log_reuse_wait_desc FROM sys.databases;

...returns LOG_BACKUP.

Replication is running successfully. Subscriber is receiving changes from publisher.

You may run below scripts to check the log reuse wait of the database(s):

SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;


How to retrieve whether database has any tables? Knowing to use

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

to query for a list of tables, I'm currently finding no way to retrieve a list of non-system tables only.

I.e. the above query also lists me system tables like "sysdiagrams" or "dtproperties".

So my question is:

How to query a database for existance of any user tables?

The following from sys.tables will work. is_ms_shipped defines whether or not the object is a Microsoft table, however sys.tables should contain only user-defined tables

SELECT * FROM sys.tables WHERE is_ms_shipped = 0

To exclude sysdiagrams from the list, here is something I've borrowed from Aaron Bertrand on this forum

SELECT o.*
FROM sys.objects o
LEFT OUTER JOIN
sys.extended_properties ep
ON o.[object_id] = ep.major_id
AND ep.class_desc = 'OBJECT_OR_COLUMN'
AND ep.[name] = 'microsoft_database_tools_support'
WHERE
o.is_ms_shipped = 0
AND ep.major_id IS NULL
AND type = 'U';

You can also check out this answer on Stack Overflow