Relating ExecutionInstanceGUID to the SSISDB The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions using the Project Deployment model will have logging to the SSISDB turned on.

When a package executes, the System::ExecutionInstanceGUID is populated with a value that, if one were using explicit logging (to sys.sysdtslog90/sys.sysssislog) would record all the events for a specific package execution.

What I'd like to know, is how do I tie an ExecutionInstanceGUID to anything in the SSISDB catalog. Alternatively, is an SSIS package executing in the SSISDB privy to the value of its catalog.executions.execution_id

Ultimately, I am trying to use the existing, custom audit table and link it back to the detailed history in the SSISDB catalog but can't seem to find the link.

I created a SSIS project using the 2012 Deployment Model consisting of a single package. In that package, I added an OLE DB Connection Manager, pointed it to tempdb and dropped a Script Task onto the canvas. I also turned on explicit logging using that OLE DB Connection Manager and captured the OnInformation event.

SCR Fire info

I configured my script task to grab two parameters: System::ExecutionInstanceGUID and System::ServerExecutionID I will admit at this point, I had not noticed the second variable until Marian's answer. Inside the task, I raise 2 Information events so I can get the values recorded. This should be logged to both the explicit table (dbo.sysssislog) and the "free" logging (catalog.operation_messages).

    public void Main()
    {
        bool fireAgain = true;
        string description = string.Empty;
        string variable = string.Empty;
        string value = string.Empty;

        variable = "System::ServerExecutionID";
        value = Dts.Variables[variable].Value.ToString();
        description = string.Format("{0}: {1}", variable, value);
        Dts.Events.FireInformation(0, "Reporting", description, string.Empty, 0, ref fireAgain);

        variable = "System::ExecutionInstanceGUID";
        value = Dts.Variables[variable].Value.ToString();
        description = string.Format("{0}: {1}", variable, value);
        Dts.Events.FireInformation(0, "Reporting", description, string.Empty, 0, ref fireAgain);

        Dts.TaskResult = (int)ScriptResults.Success;
    }

Deploy and execute

I then deployed my project to a server and executed it.

I opened the Operations Report and and clicked into the SCR Fire info task details.

The red circled item shows that we are viewing details for Operation 8, as expected. The highlighted lines are the OnInformation events which bubbled up the values of those two system variables. Also as expected, the value of System::ServerExecutionID matched what was in the report. The value of System::ExecutionInstanceGUID was meaningless as always but it was present {3F515780-8062-40AA-B9EC-C320CBAC5EFD}.

Tying it all together

I now had a two different logs I wanted to tie together.

sysssislog query

Running this query pulled back relevant rows from the old-school logging table.

SELECT
    L.event
,   L.source
,   L.message
FROM
    dbo.sysssislog AS L
WHERE
    L.executionid = '{3F515780-8062-40AA-B9EC-C320CBAC5EFD}'
ORDER BY
    L.id ASC;

The results looked like

event   source  message
PackageStart    ParameterTest   Beginning of package execution.

OnInformation   SCR Fire info   System::ServerExecutionID: 8
OnInformation   ParameterTest   System::ServerExecutionID: 8
OnInformation   SCR Fire info   System::ExecutionInstanceGUID: {3F515780-8062-40AA-B9EC-C320CBAC5EFD}
OnInformation   ParameterTest   System::ExecutionInstanceGUID: {3F515780-8062-40AA-B9EC-C320CBAC5EFD}
PackageEnd  ParameterTest   End of package execution.

catalog.operation_messages query

Running this query against the SSISDB catalog showed all the messages that were in the above report and also confirmed I could link the value in message to operation_id as well as over to dbo.sysssislog.executionid

SELECT
    OM.*
FROM
    catalog.operation_messages AS OM
WHERE
    OM.operation_id = 8;

Those results were

operation_message_id    operation_id    message_time    message_type    message_source_type message extended_info_id
30  8   2013-04-02 21:02:34.1418917 -05:00  10  30  ParameterTest:Validation has started.   NULL
31  8   2013-04-02 21:02:34.1738922 -05:00  10  40  SCR Fire info:Validation has started.   NULL
32  8   2013-04-02 21:02:34.1768872 -05:00  20  40  SCR Fire info:Validation is complete.   NULL
33  8   2013-04-02 21:02:34.1788903 -05:00  20  30  ParameterTest:Validation is complete.   NULL
34  8   2013-04-02 21:02:34.3349188 -05:00  30  30  ParameterTest:Start, 9:02:34 PM.    NULL
35  8   2013-04-02 21:02:34.4009253 -05:00  30  40  SCR Fire info:Start, 9:02:34 PM.    NULL
36  8   2013-04-02 21:02:34.4009253 -05:00  10  40  SCR Fire info:Validation has started.   NULL
37  8   2013-04-02 21:02:34.4019251 -05:00  20  40  SCR Fire info:Validation is complete.   NULL
38  8   2013-04-02 21:02:34.4219283 -05:00  70  40  SCR Fire info:Information: System::ServerExecutionID: 8 NULL
39  8   2013-04-02 21:02:34.4259295 -05:00  70  40  SCR Fire info:Information: System::ExecutionInstanceGUID: {3F515780-8062-40AA-B9EC-C320CBAC5EFD}    NULL
40  8   2013-04-02 21:02:34.4409316 -05:00  40  40  SCR Fire info:Finished, 9:02:34 PM, Elapsed time: 00:00:00.031. NULL
41  8   2013-04-02 21:02:34.4419388 -05:00  40  30  ParameterTest:Finished, 9:02:34 PM, Elapsed time: 00:00:00.125. NULL

Wrap up

When the package is executed outside of the context of the SSISDB catalog (aka via SSDT-BI or the command line to an .ispac) the value of the the System::ServerExecutionID will be 0. It makes sense, but future readers either use a LEFT OUTER JOIN when linking sysssislog to catalog.operation_messages if you want to catch all executions of the package.

Hat tip, my hearty thanks and the answer credit go to Marian for putting me on the correct track. Given the choice between storing a GUID (16 bytes) and a bigint (8 bytes) in my summarized logging table, it's a no brainer for me: monotonically increasing big integer please.


Integrated Security Success on Master, Fails on Other Database I'm a bit perplexed as to why my connection string is selectively succeeding on the master database in SQL Server 2005. Here are the details of my app. Any ideas where to look? I'm clearly missing something. Also keep in mind that the IIS Server and SQL Server are on different machines within an Intranet environment where I am attempting to implement Windows Authentication. Thanks. :)

Connection strings:

Server=SQLServer;Database=master;Integrated Security=true --succeeds
Server=SQLServer;Database=other;Integrated Security=true --fails

Fail Error Message:

Cannot open database "other" requested by the login. The login failed.
Login failed for user 'DOMAIN\IIS_SERVERNAME$'.

My IIS Server App Pool and Authentication settings:

AppPool: .NET 4.0; Integrated Pipeline; Identity=Network Service
Authentication: Impersonation=Disabled; Windows Auth=Enabled; Kernel-mode=Enabled; Providers=Negotiate,NTLM

Update: Clearly there is a Login of some sort for the machine connecting to my SQL Server with the Integrated Security, otherwise it wouldn't succeed in connecting to the master database right? I would guess that to mean there's an issue at the database level, but I cannot figure out which user account would give it access to master because there are only a handful of users for it.

In SQL Server, as long as a LOGIN is valid, it will have access to the master database in a public role.  This is both for historical and practical reasons.  For example, stored procedures beginning with "sp_" are searched first in the master database and will execute from there.  The implication is that users need to first have access to the master database to begin with.  tempdb is another database that allows user access via the public role.

User databases do not have this as a default setup, so unless the Login is specifically given permission, or has server-wide roles such as "sysadmin", it will not be able to switch to the database context, hence your error.

The fact that you manage to log in at all using the machine account means that it is either explicitly a created LOGIN, or is a member of a group that appears under "Logins" when you browse the Object Explorer in SQL Server Management Studio.

[SERVER\Instance]
    Security
        Logins
             .... here ....

You can specifically assign roles or grant execute permissions to the machine account:

use other;
exec sp_addrolemember db_datareader, [DOMAIN\IIS_SERVERNAME$]
GRANT EXECUTE ON sp_myproc TO [DOMAIN\IIS_SERVERNAME$]


Is F included in F+? The definition in my book says:

  The set of all functional dependencies logically implied by F is the closure of F.

My question is, do we include FDs in F in F closure as well ?

If I have:

F = {A→B, B→C}
a) F+ = {A→C, A→BC, A→B, B→C}
b) F+ = {A→C, A→BC}

Which one would be correct a) or b) ?

Since each dependency implies itself (given the fact that in logic trivially every assertion implies itself), F is included in F+. But note that in your example F+ contains quite a large number of dependencies, in addition to the dependencies that you have listed, like for instance A→A, AB→C, B→B, AC→A, etc.


Migrating to Oracle Flashback for Historization on Oracle 11g Our application built using Oracle 11g  requires that we keep historic data.

The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically we need to save some stuff 10 years, and we dig for old data a few times each year. The historical data is also used frequently during development and testing for debugging purposes.

Or homemade solution is to add an extra sequence number to the primary key, inserting a copy with a new seq-no when an existing row is "modified". We are using the latest seq-no as the actual "active" data in all queries.  Effectively we are doing a subselect or join on max(seq_no) + the other primkeys whenever we access a table.

id           varchar[8],  --pk
seq_no       number,      --pk
omg_ponies    varchar[42],

The performance is ...almost adequate, but we are seeing bigger data volumes, and a lot of queries are getting unbelievably complex as our application grows. It also hinders us from using JPA or Hibernate efficiently as the model gets messed up. We are solving that with views, but it has it's complications.

So how to handle historization? Oracle seems to recommend using Flashback and it looks like a Good Thing.

But how do we migrate, keeping our (10 year long) history? Ideally, only one row should be left for each id, with the old variants (lower seq_no:s) available in the flashback logs. Preferably with an proper "old" timestamp.

Furthermore, how do you handle refactoring? We see a lot of new business requirements, and occasionally we need to do major changes, like splitting a table into two, or other larger modifications caused by new requirements and features. Until now, we've been doing larger changes once or twice each year. How do we handle changes, while retaining or repopulating the history, again preferably with the proper "older" dates.

  Footnotes: We do not believe that we must leave historic entries "intact". It's OK to modify the logical model as long as we are not loosing track of changes and certain events.

It sounds like Oracle Total Recall may fit your needs:

Regulatory oversight such as Sarbanes-Oxley, HIPAA, and Basel-II, as
well as internal audits, require companies to keep historical data
available for long periods of time. Oracle Total Recall, part of
Oracle's comprehensive portfolio of database security solutions, works
with Oracle Database 11g, Enterprise Edition to help companies store
their data in secure, tamper-proof databases while keeping it
accessible to existing applications

The documentation states that it can support refactoring:

In Oracle Database 11g Release 1, the Add Column DDL operation is
supported with Flashback Data Archive. With Oracle Database 11g
Release 2, the following DDL operations are supported, with full
support for Flashback queries across all associated changes:
• Add, Drop, Rename, Modify Column
• Drop, Truncate Partition
• Rename, Truncate, Drop Table
For more complex DDL –
upgrades, split table, etc. – the Disassociate and Associate PL/SQL
procedures can be used to temporarily disable Flashback Data Archive
on specified tables.


How can I convert an Oracle dump file into SQL Server? I want to ask about converting Oracle dump files (.dmp) into SQL Server files (.bak) where during conversion I don't have to be connected to any database server.

I've searched for related technologies, such as Oradump to SQL Server. Do you have another suggestion to solve this? Open source ones I mean.

Thanks for both of your response.
I see how difficult it will, but is there any possibility to use another way in converting oracle dump file? because all of solution's converter tools always provide a connection database server. I'm so thankful what if you can suggest another tool. thanks anyway

"is there any possibility to use another way in converting oracle dump file?"

Import the dump file into Oracle. Transfer the data from Oracle to SQL Server using any number of tools (flat files, SSIS, linked servers, replication)

Forget about processing a dump file. The only thing that can process a dump file is Oracle. Or you can spend a year reverse engineering it then have to start over again when the format changes. If you want to convert Oracle data to SQL, you aren't going to be able to use a dump file unless you import into Oracle first.


Lock priority for scripts run in maintenance windows We run maintenance windows weekly and monthly. Some of our maintenance windows are online and the last thing we want is our scripts to block live queries from our applications.

Is there a way to set the priority of a query so that it cannot cause other queries to block for more than X seconds?

Example:
I run maintenance script A on tableA
Application is updating a row on tableA, waits on the maintenance script to complete.
SQL Server automatically terminates and rollback my maintenance script after X seconds of blocking.

I know there is a similar functionality with deadlocks (set deadlock victim to low).

Thanks!

Microsoft introduced the new Low Priority Lock Wait option for Index rebuilds in SQL Server 2014. Not sure what SQL Server version you are running.

Index rebuild using 'low priority lock wait':

ALTER INDEX Index_name
ON Schema.Table_name
REBUILD WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY
             (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF )))
;

Check out Paul Randal's excellent article here Low Priority Wait and Testing Index rebuilds with Low Priority Wait by Kendra Little for clear explanation.

That's specific for Index maintenance, I am assuming your maintenance plans does include Integrity checks and other stuff too.

An option would be writing some TSQL code to monitor blocking and and create and schedule a SQL agent job to automatically kill the 'low priority session' based on the SQL text its running.

If you are experiencing issues caused by deadlocks, you could probably lower the Deadlock priority value to see if that helps.

Depending on the type of resource contention, you can also try limiting the resources for specific logins using Resource Governor.

If you are using custom maintenance plans, you probably need to look into and start using Ola Hallengren's Maintenance solution.


what is the formal statement terminator with db2 sql I have added / character as statement terminator all in my db2 statements. I can run sql file in data studio client without any issue. But when I run the sql file in db2 command prompt by issuing db2 -tsvf db2.sql command I got

DB21007E End of file reached while reading the command.

error. I could resolve this issue by changing statement terminator character from / to ;.
My question is should I keep statement terminator character as / or ;? If I keep /, is there any way to run sql file from command line also?

You would need to use the -td<statement terminator> syntax. So you would need to format the above as follows:

db2 -td/ -svf db2.sql

This would tell DB2 that your statement terminator is the / character, otherwise, yes the default is the semicolon ;.


Properly escape characters in a string for JSON With the below example, how do I escape the double-quote characters that are in the @data variable?

declare @data nvarchar(max) = N'"TEST"';
declare @jsonFragment nvarchar(max);
declare @id int = 999;

set @jsonFragment = ',"' + cast(@id as nvarchar(16)) + '":"' + @data + '"';

select @jsonFragment;

The result is:

 ,"999":""TEST""

I need it to be:

 ,"999":"\"TEST\""

As I understand it, selecting for json path requires that I define a static key name - but as can be seen, I have a scenario where key names are being defined dynamically.

In my real-world use case, this string construction is occurring within a stuff statement, itself part of a column definition in a larger select that utilises CTEs - meaning it would be very hard to create a dynamic sql statement to be run using sp_executeSQL.

There is a system function for this purpose called STRING_ESCAPE that will escape the characters as needed for a given string type.

In your example:

declare @data nvarchar(max) = N'"TEST"';
declare @jsonFragment nvarchar(max);
declare @id int = 999;

set @jsonFragment = ',"' + cast(@id as nvarchar(16)) + '":"' + STRING_ESCAPE(@data, 'json') + '"';

select @jsonFragment;

Returns:

,"999":"\"TEST\""

It works with all elements that need to be escaped, such as slashes in a pathname, for example:

SELECT STRING_ESCAPE(N'C:\PathName\Temp\', 'json')

Returns

C:\\PathName\\Temp\\


Index for OR operator: a=x or b=x I have table with three integer columns id, a and b.
I want to get all records where a or b match specified parameter ordered by id:

select id, a, b from t where a=x or b=x order by id

Note that x value is same for a and b.

What it the most suitable index here?

Upd: Is there any use of the fact that we always look for same value in columns a and b? Can we create an expression index for that?

The simple and fast solution are two indexes on (a, id) and (b, id). Be sure to have a respectively b as leading column:

Working of indexes in PostgreSQL

The added id is not going to help for your particular query, you could as well just create them on (a) and (b) to get the bitmap index scans that @shx explained. But the index for two integer is exactly as big as for one column. There may be other use cases that actually benefit from the added id.

Is a composite index also good for queries on the first field?

If your actual SELECT list is narrow like in your example and some preconditions are met, I would go for index-only scans.
Create indexes on (a, b, id) and (b, a, id).

This equivalent query (given that id or at least (id,a,b) is unique) helps Postgres choose the query plan with index-only scans:

EXPLAIN
SELECT id,a,b FROM t WHERE a = 10
UNION
SELECT id,a,b FROM t WHERE b = 10
ORDER  BY id;

Sort  (cost=110.45..112.92 rows=988 width=12)
  Sort Key: t.id
  ->  HashAggregate  (cost=51.42..61.31 rows=988 width=12)
        Group Key: t.id, t.a, t.b
        ->  Append  (cost=0.42..44.02 rows=988 width=12)
              ->  Index Only Scan using t_ab_id_idx on t  (cost=0.42..17.07 rows=494 width=12)
                    Index Cond: (a = 10)
              ->  Index Only Scan using t_ba_id_idx on t t_1  (cost=0.42..17.07 rows=494 width=12)
                    Index Cond: (b = 10)

Performance depends on data distribution, write patterns and value frequencies, among other things. In my tests on pg 9.5 I see similar performance to the solution in @shx's answer - as long as we select whole rows (i.e. heap tuples are not much bigger than index tuples).

Typically, there are additional columns in the underlying table - which would not impair performance of this query at all, while the alternative loses ground since it has to read more pages for wider rows in the underlying table.

Answer to added question

  Is there any use of the fact that we always look for same value in columns a and b? Can we create an expression index for that?

I can't think of a way to capitalize on that for the table as is. An index entry can only reference a single table row. Theoretically, a GIN index on (ARRAY[a,b]) might work, but I could not get useful results with it (nor did I expect to).

You would need two rows per row in the base table (except where a = b) to enable a single pass on a b-tree index for the job. Actually possible with the help of a MATERIALIZED VIEW. The added overhead and maintenance cost only seems reasonable if you have much more read than write activity and you need to optimize performance for your given query. You need to understand MVs and know when to refresh.

CREATE MATERIALIZED VIEW mv_t AS
SELECT a AS x, id, a, b FROM t
UNION  -- eliminate dupes
SELECT b AS x, id, a, b FROM t
ORDER  BY x, id;

x is the unified search key. Rows in the base table are listed once for every distinct value in [a, b]. This query returns the same result as your original, but faster:

SELECT id,a,b FROM mv_t WHERE x = 10 ORDER BY id;

The query can benefit from a single pass on a single index now. Plus, rows in the MV are sorted physically like they are returned, which helps the approach with a simple index:

CREATE INDEX mv_t_x_idx ON mv_t (x); -- simple

Sort  (cost=88.01..90.48 rows=990 width=12)
  Sort Key: id
  ->  Index Scan using mv_t_x_idx on mv_t  (cost=0.42..38.75 rows=990 width=12)
        Index Cond: (x = 10)

Also ideal for a BRIN index (Postgres 9.5+) for huge tables:

Speed up creation of Postgres partial index

You can go for index-only scans again:

CREATE INDEX mv_t_full_idx ON mv_t (x, id, a, b);  -- covering index

Index Only Scan using mv_t_full_idx on mv_t  (cost=0.42..33.75 rows=990 width=12)
  Index Cond: (x = 10)


What is the syntax to add a constraint on an already-created table? PostgreSQL exclusion constraints are documented briefly under

DDL-Constraints
CREATE TABLE .. CONSTRAINT .. EXCLUDE As linked in ddl-constraint

But, the syntax for ALTER TABLE does not mention /EXCLUDE/ anywhere. How do I add one if one is not already present.

I can in psql, TAB-complete

ALTER TABLE foo ADD

However subsequent tabs do not provide CONSTRAINT which the documentation mentions nor anything that comes after.

Follow the example here by ypercube

ALTER TABLE table_name
  ADD CONSTRAINT only_one_is_active_value_per_name
    EXCLUDE  USING gist
    ( name WITH =,
      is_active WITH <>
    );

The ALTER TABLE command only lists examples of constraints. Constraints are actually added with the same syntax on DDL-Constraints,

CREATE TABLE foo (
  -- stuff
  EXCLUDE USING gist ( name WITH =, is_active WITH <> );
);

ALTER TABLE foo
  ADD CONSTRAINT
  EXCLUDE USING gist ( name WITH =, is_active WITH <> );


How to calculate end_date from start_date and duration in SQL Server Query I have a table with 4 fields .Start-date, Duration, End_date, Actual_date.

I am providing the start_date and duration as the input to calculate end-date.

While calculating the end_date the Saturday and Sundays are not considered.

Also if the actual_date is having value that value is saved as end_date.

Example : Start_date = 18/05/2020 , Duration : 10. So the End_date should be calculated by excluding 23/05/2020 and 24/05/2020.

I had tried different ways to archive this.

The below is the alter query for the column end_date.

This is meeting half of my requirement but dont know how to exclude the weekends (Saturday and Sunday) from the start_date and duration.

ALTER TABLE [DBName].[dbo].[TestDate] ADD End_Date as CAST(
CASE
WHEN (Actual_Date='' or Actual_Date IS NULL) then  DATEADD(day, Duration, Start_Date)
ELSE Actual_Date
END as DATE
)
GO

Please help me on this.

Iterative solution:

WITH cte AS ( SELECT id,
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, start_date) > 5
                                       THEN 9 - DATEPART(WEEKDAY, start_date)
                                       ELSE 1
                                       END, start_date) cur_date, duration-1 reminder
              FROM test
              UNION ALL
              SELECT id,
                     DATEADD(DAY, CASE WHEN DATEPART(WEEKDAY, cur_date) > 5
                                       THEN 3
                                       ELSE 1
                                       END, cur_date),
                     reminder - 1
              FROM cte
              WHERE reminder > 0 )
UPDATE test
SET test.end_date = cte.cur_date
FROM cte
WHERE test.id = cte.id
  AND cte.reminder = 0;

fiddle

PD. It is optimal hardly... for large duration values you may int. divide it by 7 and add according weeks (5 days per each) then iterate by int. reminder only.


How to write function in sql which accept table as input and return result back as table? I want to pass a table variable to a fuction (which would contains different value at different time) as input, returning a calculated result in the form of another table, which would be declared in the function.

Something like this:

CREATE FUNCTION fn_CalculateListing(@ListingData TABLE(ROWID INT,value1 INT, value2 INT))
RETURNS TABLE
AS
BEGIN
    DECLARE @result TABLE
    (
        result1 INT,
        result2 INT
    )

    INSERT INTO @result(result1,result2)
    SELECT value1*value2, value1/value2
    FROM ListingDate

    RETURN @result
END

But the above syntax give me an error near declaration @ListingDate TABLE as incorrect syntax.

Is above syntax correct, or should I use another one?

You cannot include a table declaration in the stored procedure declaration.
If you really want to pass rows of data, create a table-valued parameter type, then use it in your stored procedure.

How about a simpler table valued function, that calculates product and quotient of 2 integers:

CREATE FUNCTION fn_CalcProductQuotient(
@Value1 INT,
@Value2 INT)
RETURNS TABLE
AS
RETURN (
SELECT @Value1 * @Value2 AS [Product]
       ,@Value1 / @Value2 AS [Quotient]
);

Make sure to use an inline user-defined function. This way the plan optimiser can parallelise your plan (if you have multiple cores) and you will get much better performance.

You can use this function as following:

SELECT  [RowID]
       ,[Value1]
       ,[Value2]
       ,Calc.[Product]
       ,Calc.[Quotient]
FROM Items i
CROSS APPLY fn_CalcProductQuotient(Value1, Value2) Calc


Why shouldn't I increase SQL Server's timeout globally everywhere? I have an application in which some reports time out before getting data. We started increasing timeout througout our code in different places, and as our database is growing bigger and bigger, we see that we're repeating/duplicating more code.

So we decided to increase database timeout for queries globally. But I have this feeling that this is wrong. Yet I'm not able to bring forward any reasons. Is it bad to increase timeout everywhere? Why shouldn't we do this?

We're using SQL Server and Entity Framework.

So we decided to increase database timeout for queries globally. But I
  have this feeling that this is wrong. Yet I'm not able to bring
  forward any reasons. Is it bad to increase timeout everywhere? Why
  shouldn't we do this?

The default client API query timeout of 30 seconds is appropriate for a typical OLTP workload, albeit arbitrary.  The value should be set high enough to allow queries on a reasonably healthy system to complete successfully yet low enough so that queries timeout when the rather than wait indefinitely when things aren't well, such as in a long-term blocking scenario or due to a bad execution plan.  There is no single timeout value that is appropriate for all queries unless the system is a 100% OLTP workload or 100% reporting.

It would be better to first address the root cause of longer-running queries before increasing the timeout value.  Perform query and index tuning, ensure hardware is adequately sized and configured properly for your workload, and make sure EF data access is optimal.  These practices will make the most efficient use of available resources, reduce compute costs, and provide the best end user experience.

If the amount of data required by queries doesn't increase with table size yet queries run longer as the number of rows in tables increase, this suggests scans. Query and tuning would be a good first step, assuming SQL Server memory is appropriate for the database size.


Delete from table in chunks I want to cleanup one of my tables which has 4 million. So I prepare this query to clean it. But it's throwing an error, can anyone please help me to fix this?

DELIMITER $$
DROP PROCEDURE IF EXISTS archive_table $$
create procedure archive_table ()
set @min=(select min(dob) from test
              where dob < DATE_SUB(CURDATE(), INTERVAL 30 day));
while @min is not null
begin
    delete from test where dob = @min;
    commit ;
    select min(dob) from test
            where dob > @min
              and dob < DATE_SUB(CURDATE(), INTERVAL 30 day) );
END WHILE;
END$$

ERROR:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
 near 'while @min is not null
begin
    delete from test where dob = @min;
    commi' at line 1

The openark kit by Shlomi Noach could be right up your alley!

From the introduction here:

oak-chunk-update: perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.

From the documentation page here:

  This utility allows for splitting long running or non-indexed
  UPDATE/DELETE oprations, optionally multi-table ones. Long running
  updating queries are often used. Some examples:

Purging old table records (e.g. purging old logs).
Updating a column on a table scale.
Deleting or updating a small number of rows, but with a non-indexed search condition.
Inserting into one table aggregated values from other tables.

The tool has a load of options with examples - I was very impressed when I used a couple of his tools for various bits and pieces.

So, kick it off with cron at 03:00 in the morning and relax!


psql won't list databases I have a situation that I'm scratching my head over, but I'm new to postgresql, so I may be overlooking something obvious.

I set up a testbed server and set access in pg_hba.conf:

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 md5

Then restarted the postgresql server and after that, I could, as my regular user, use

psql -h localhost -U postgres -l

And this gave me the table listing.

On the second system pg_hba.conf matches that other file. I restarted the database server process and yet psql -h localhost -U postgres -l as my user yields this error:

2012-03-29 23:31:17 UTC LOG:  could not receive data from client: Connection reset by peer
Password for user postgres:
psql: fe_sendauth: no password supplied
2012-03-29 23:31:29 UTC LOG:  could not receive data from client: Connection reset by peer

I do not have a password configured for that user. If I

sudo su - postgres

Then try

psql -h localhost -U postgres -l

...as postgres won't work, still prompting for a password, but if I use:

psql -l

it gives the list of databases.

This server, the second one, has a "production" database on it that another developer set up. But he doesn't know why it wouldn't let me list it as my username with the -h host -U postgres arguments on that server, while the fresh-install on the first server will list them (although the first one has nothing but the default installation on it.)

The platform is Ubuntu 11.10, postgres is 9.1. Any idea what I'm overlooking?

EDIT
Here's what's happening when I try getting a database list:

user@testbed:/etc/postgresql/9.1/main$ grep listen_addresses *
grep: pg_hba.conf: Permission denied
grep: pg_ident.conf: Permission denied
postgresql.conf:#listen_addresses = 'localhost'     # what IP address(es) to listen on;
user@testbed:/etc/postgresql/9.1/main$ psql -h localhost -U postgres -l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

user@testbed:/etc/postgresql/9.1/main$

On the other system:

user@production:/etc/postgresql/9.1/main$ grep listen_addresses *
grep: pg_hba.conf: Permission denied
grep: pg_hba.conf~: Permission denied
grep: pg_ident.conf: Permission denied
postgresql.conf:#listen_addresses = 'localhost'     # what IP address(es) to listen on;
user@production:/etc/postgresql/9.1/main$ psql -h localhost -U postgres -l
Password for user postgres:
psql: fe_sendauth: no password supplied
user@production:/etc/postgresql/9.1/main$

According to the pg_hba.conf snippet, a password is required if you connect from ::1, which is the IPv6 address for localhost.

It may be that the box on which you have the problem has the resolver configured so that the name "localhost" refers to both 127.0.0.1 (IPv4) and ::1 (IPv6), so that the command psql -h localhost... may connect to one or the other address.

I'd suggest to check the /etc/hosts file for different localhost entries, or bypass the resolver by connecting to 127.0.0.1, or update the pg_hba.conf with "trust" for IPv6 local connections.


Why my application is blocked infinitely and SQL Server never timeout and kill the deadlock I have a multi-thread application that can opened more than one database connection to perform database operation. These connections are using the same credential.

Here is a problem, the application has a thread A opened an transaction, and start deleting items from a table, but the same thread A trigger another thread which open a new connection to read same table and to look for more thing to delete.  As you can imagine, the deleting table is locked, and you can't do select on it until its transaction is completed.  As a result, the application is blocking itself.

This application logic need to be fix. However, here are my questions:

Is there a way to have SQLServer detect this application deadlock and kill one of the open selection, similar to a deadlock victim exception, or some kind of timeout setting?
Is is possible to write a SQL Agent, that detect this situation and kill one of the session?
Why would SQL Server throw a deadlock victim exception if it is caused by different application, and not for the same application?

Thanks

Deadlocks and blocking locks are two different concepts that you need to understand.

A deadlock is a situation where process/action 1 is waiting for process/action 2 to finish and at the same time process/action 2 is waiting for process/action 1 to finish. In other words. They would wait forever since they are waiting on each other.

In your scenario, something else is happening:

Process 1 is doing an action and has taken a lock on a resource to complete that action, Process 2 now wants to start a action that requires a lock on the same resource. Process 2 now has to wait for process 1 to complete and the lock is released. The key here is that at any given moment, none of the processes are waiting for each other (at the same time). One process is just waiting for the other process to finish an action on the same resource. They are not waiting for each other.

I hope that's clear.

On to how we fix your issue:

Can you post the Table definition, the indexes on the table and the delete select statement. We could have a look to see if there are ways to make the likelyhood of blocking locks less.


How to get INNER JOIN result from two tables I have the three MySql tables :

table_fonts

font_id (int)
font_name (varchar)

table_charsets

charset_id (int)
charset_name (varchar) (examples: 1=latin, 2=greek, and so on)

table_charsets_by_font

font_id (int)
charset_id (int)

I am having trouble into creating my INNER JOIN query to obtain a list with all charsets (table_charsets.charset_name) listed in columns, with a value of 1 if existing, or a value of 0 if not existing — aka a TINYINT —, like this :

font_id | font_name | charset_id1 | charset_id1_existing | charset_id2 | charset_id2_existing ... and so on

For example :

1 | Arial           | 1 | 1 | 2 | 1 ... and so on
2 | Arial Black     | 1 | 1 | 2 | 1 ... and so on
3 | Times New Roman | 1 | 1 | 2 | 0 ... and so on

So far, all my attempts have failed. Could someone point me to the right direction ?
Thank you :)

Please try:

SELECT tf.font_id, tf.font_name, tcf.font_id, tc.charset_name, tcf.font_id, tcf.charset_id
FROM table_fonts tf
INNER JOIN table_charsets_by_font tcf ON tcf.font_id=tf.font_id
INNER JOIN table_charsets tc ON tc.charset_id=tcf.charset_id
WHERE tc.charset_name='latin';


restricting user just to get aggregated information Using mysql, how to restrict a user just to get the aggregated information (eg. sums, averages, count) of the table and only if the result of the number of record is greater than the threshold?

For example, if the threshold is 2, the following query should not be responded if only one supplier is living in London.

select count(*) from s where city = "london"

But this should not be responded.

select salary from s where city = "london"

Because if user knows in advance that there is only one who live in London, he can know his salary. I want to prevent that.

Plan A:  Use GRANT to not let that user see salary.

Plan B:  Provide Summary Tables (with counts, totals, etc) of stuff that you are willing to let them see, then restrict (via GRANT) their privileges on a table basis.  (Or put the Summary Tables in a different database.)


MySql 5.6: Ignores innodb_page_size setting in my.ini So I tried to increase the innodb_page_size to 32k, by setting this value in my.ini:

innodb_page_size=32k

I then stopped the MySql service, and restarted it. The service in Win looks like this:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

And I edited the correct file, and its saved:

but when I look at the variable in HeidiSql, I see the standard value of 16k:

Im not sure what I am doing wrong... Any help is appreciated!

According to the MySQL Documentation on innodb_page_size, Paragraph 2

  innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 14.6.1, “InnoDB Startup Configuration”.

You would had to have initialized that value in my.ini the day you first created the database and started mysqld for the first time. No restart of mysqld afterwards can ever change that.

If you want to create a MySQL Instance with a new size for innodb_page_size

you must setup a new datadir with no data (but do not start mysqld)
set the innodb_page_size to 32768 in the my.ini for that new instance
start mysqld against that new my.ini and new datadir
mysqldump the data out of the old instance
load the mysqldump into the new instance

Give it a try and let us know !!!

CAVEAT

Please be aware of disk I/O changes. Paragraph 3 of the same document says:

  The default 16k page size is appropriate for a wide range of workloads, particularly for queries involving table scans and DML operations involving bulk updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when single pages contain many rows. Smaller pages might also be efficient with SSD storage devices, which typically use small block sizes. Keeping the InnoDB page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.


What should I use? A string or 15 integer fields? I am developing a student tracking program where I need to store 15 exam marks.

I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical operations. However, I need as much performance as possible.

Which is better?  A single string field, or 15 individual int fields?

If you are already talking about splitting and computing, don't store this as an array.

Regardless of the relational theory and traditional normalization rules and dogma, it's simply a design which gives you MINIMAL flexibility.

Make each exam result a row.

I'm not trying to anticipate everything, but there are a very large number of things which this more granular (and, yes, normalized) and only ever so slightly more space expensive design facilitates which you may or may not need now and may or may not need in the future:

Throwing out highest and lowest result?  You will have to slice up your array and sort it.
Averaging?  You will have to slice it up and total it
Analysis of exam result by exam across students?  You'll have to slice and pivot
Sorting for counting (or instance British GCSEs, where it might be 7 As and 2Bs)?  You'll have to slice and sort

Note that all this slicing and sorting comes very cheaply in an indexed, normalized design.


What happens when I try to store more 8019 bytes in a SQL Server table row? I read -

total fixed and variable length data are still limited to 8019 bytes total

But my data can possibly be more than this. What happens if it's more. Does it still store the data correctly?

Also can someone tell me how I can store for example over 4000 bytes of text data in a column. Is that possible?

From Row-Overflow Data Exceeding 8 KB :

  A table can contain a maximum of 8,060 bytes per row. In SQL Server
  2008, this restriction is relaxed for tables that contain varchar,
  nvarchar, varbinary, sql_variant, or CLR user-defined type columns.
  The length of each one of these columns must still fall within the
  limit of 8,000 bytes; however, their combined widths can exceed the
  8,060-byte limit

That means it's really hard to hit the 8kb limit.  You could script it:

if object_id('YourTable') is not null
    drop table YourTable
go
create table YourTable (id int identity);
declare @i int = 1
declare @sql varchar(max)
while @i < 800
    begin
    set @sql =  'alter table YourTable add col' + cast(@i as varchar(24)) +
        ' decimal(38);'
    exec (@sql)
    set @i = @i + 1
    end

This will show you the error message:

  Msg 1701, Level 16, State 1, Line 1 Creating or altering table
  'YourTable' failed because the minimum row size would be 8076,
  including 65 bytes of internal overhead. This exceeds the maximum
  allowable table row size of 8060 bytes.


What is an "operational master" with respect to entity-relationship diagrams? I have a college assignment to create an entity-relationship diagram (ERD) for a database we're going to build. That's fine, but I have to include something called an "Operational Master" in the ERD. The lecturer's description is vague at best, and I can't seem to find any info about it online.

Basically speaking, we have to create an ERD for a game rental service with at least one "operational master". The only other reference to it in the lecture notes was to "add an index for the 'operational master'". It was never really explained during the lecture as far as I can recall. I get what an index is, but not what is meant by the "operational master".

Has anyone any ideas on what this might be?

You piqued my curiosity here. Cursory search on the internets mostly shows that this question haunts students since at least 2014, with no apparent relief. However, there seems to be one page that  explains what an "operational master entity" is:

  The key-only parent entity (otherwise known as an operational master entity, a collection entity, or a categorising entity, or perhaps a domain entity) stores the valid or actual range of values for the attribute.

A domain entity commonly translates to a dictionary, lookup, or dimension table. For example, for a Game entity a Publisher might be one operational master entity. The Publisher entity would have two attributes, a surrogate Id and Name.

P.S. It appears Google Books have a few more occurrences, mostly from the last century.


monitoring sql execution perfomance of an application that makes use of SP_prepare we have an application that makes heavy use of sp_prepare and sp_execute and we would like to start monitoring the execution plans of the sql. but this is made difficult by the use of sp_prepare

much of the reading i have done suggests that sp_prepare is inefficient, is this correct?
also how would i monitor such database activity?

we are looking to monitor

potential missing indexes
Longest Running Queries

if you need any more information please let me know.

Both missing indexes and long-running queries are available in DMVs, with the caveat that DMVs can get refreshed on some events, such as service restart, certain sp_configure changes, etc.

Here's a missing index query from Bart Duncan's blog post, Are you using SQL's Missing Index DMVs?:

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)
    * (migs.user_seeks + migs.user_scans) AS improvement_measure,
  'CREATE INDEX [missing_index_'
    + CONVERT (varchar, mig.index_group_handle) + '_'
    + CONVERT (varchar, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL
    AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
  ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
  ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0)
  * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact
  * (migs.user_seeks + migs.user_scans) DESC

I would change about 20 things in that query, but it's a start. This will show you the indexes you should investigate creating - but please don't just create all of the indexes suggested. I've blogged about why you don't want to do that.

And here is a query from the sys.dm_exec_query_stats documentation:

SELECT TOP 5 query_stats.query_hash AS "Query Hash",
  SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
  MIN(query_stats.statement_text) AS "Statement Text"
FROM
    (SELECT QS.*,
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

Also some bad habits here, but shrug. This will collect the information about your longest-running queries in aggregate. If you want to know about individual instances of long-running queries, you'll have to capture those yourself using a server-side trace with a duration filter, or extended events, or a 3rd party monitoring product (I'm biased because I worked there for nearly a decade).


How can get this value with json sql server? How can I get question_id field in this json file? I tried, but it returns null.

DECLARE @json NVARCHAR(MAX)
SET @json =
  N'{
            "solution": "xxxxxxxxxxxxxxxxxxxxx",
            "options": [
                {
                    "choice_id": 205073,
                    "choice": "aaaa"
                },
                {
                    "choice_id": 205074,
                    "choice": "bbbb"
                },
                {
                    "choice_id": 205075,
                    "choice": "cccc"
                },
                {
                    "choice_id": 205076,
                    "choice": "dddd"
                }
            ],
            "question_id": 12345
        }'

SELECT * FROM
 OPENJSON ( @json, '$.options')
WITH (
              choice_id varchar(8000) '$.choice_id',
              question_id int '$.question_id'
 )

Result

You treat question_id as if it was a child to options when it is in fact a sibling to options.

You can use json_value in the column list to get the value for question_id.

select O.choice_id,
       json_value(@json, '$.question_id') as question_id
from openjson(@json, '$.options')
     with (
            choice_id varchar(8000) '$.choice_id'
          ) as O;

If you have an array of objects on the root you would first need to shred on $ to get one row per object and then use a cross apply to get the objects.

declare @json nvarchar(max)
set @json = N'
[
   {
      "solution":"xxxxxxxxxxxxxxxxxxxxx",
      "options":[
         {
            "choice_id":205073,
            "choice":"aaaa"
         },
         {
            "choice_id":205074,
            "choice":"bbbb"
         },
         {
            "choice_id":205075,
            "choice":"cccc"
         },
         {
            "choice_id":205076,
            "choice":"dddd"
         }
      ],
      "question_id":12345
   },
   {
      "solution":"xxxxxxxxxxxxxxxxxxxxx",
      "options":[
         {
            "choice_id":205073,
            "choice":"aaaa"
         },
         {
            "choice_id":205074,
            "choice":"bbbb"
         },
         {
            "choice_id":205075,
            "choice":"cccc"
         },
         {
            "choice_id":205076,
            "choice":"dddd"
         }
      ],
      "question_id":22345
   }
]'

select json_value(T1.value, '$.question_id'),
       T2.choice_id
from openjson(@json, '$') as T1
  cross apply openjson(T1.value, '$.options')
              with (choice_id varchar(8000))as T2;


Is Service Pack 2 completely installed on Passive Node of SQL Server 2012 Instance? We are applying SQL Server 2012 Service Pack 2 (SP2) to a two-node active/passive cluster, starting with the passive node (as per best practices):

How to install a Service Pack at a SQL Server 2012 Failover Instance:
Best Practices
(http://blogs.msdn.com/b/john_daskalakis/archive/2014/09/05/how-to-install-a-service-pack-at-a-sql-server-2012-failover-instance-best-practices.aspx)

Applying SP2 to the passive node produced a result indicating four installation failures (as per FIGURE 1).

FIGURE 1

When we begin to re-apply SP2, we can see that the Build Number is correct and the Upgrade Status is "Incompletely Installed" (as per FIGURE 2).

FIGURE 2

AND SO THE QUESTION:
Will the passive node remain in an Incompletely Installed upgrade status until the active node is failed over to the passive (making the passive node ACTIVE)?

The logs led us to review our Cluster services:

There was an error to lookup cluster resources. Error: There was a failure to call cluster code from a provider. Exception message: Generic failure . Status code: 4104. Description: .
HResult : 0x86d80014
FacilityCode : 1752 (6d8)
ErrorCode : 20 (0014)
Data:
    errorMessage = There was a failure to call cluster code from a provider. Exception message: Generic failure . Status code: 4104. Description: .

We determined that the replication agent service was the cause of the incomplete installation.

We removed the service from Cluster Resources and re-applied SP2 without success. We stopped the service on the node and re-applied SP2 without success.

Error: Failed to run patch request for instance: MSSQLSERVER (exit code: -2032664552)

We uninstalled the service from the server nodes, and then successfully "re-applied" SP2.

Follow-up actions:

We'll be contacting the replication agent vendor to discuss why this
was required.
Review the Microsoft document "Patching SQL Server Failover Cluster Instances with Cluster-Aware Updating (CAU)", available here, which I discovered today.


AlwaysON Failover Questions I actually have 2 Databases for one application under a SQLServer 2012 ALwaysON,
I want to add another Database for a different application... It is recommended to create a new AlwaysON AG Group?

I would like to know if is possible to have something like this:

AG Group 1
Server 1

DBAPP1(PRIMARY)
DBAPP2(PRIMARY)
DBAPP3(SECONDARY)

AG Group 1
Server 2

DBAPP1(SECONDARY)
DBAPP2(SECONDARY)
DBAPP3(PRIMARY)

Is this possible? Or just dumb?

Thanks!

I don't think so, when you failover you failover the whole AG. So in order to achieve this you'll need another AG. And probably another SQL Server License since you usually only license the active node in an AlwaysOn.


Eager spool for update and delete on partitioned table When I update or delete on partition table it shows eager spool in execution plan.

Image for Update query execution plan showing eager spool:-

I can’t understand why it is doing eager spool only when I am updating or deleting on partition table, same query when I run on non partition table it does not do eager spool.

The eager spool has to do with the size of data you're deleting.

In this case, you're updating a large quantity of rows on a table with several indexes. You're also updating a field that's covered by a lot of nonclustered indexes, and SQL Server has to update all of those indexes as well.

To avoid this operation, update less rows (as a percentage of the table) or index the table less heavily.


Find Difference In Time I am attempting to write a stored procedure to determine the difference in seconds between two times.  If I were to hardcode the values I know I can use

CAST(datediff(second, [start], [end])/3600.0 As Decimal(18,4)))

but I am needing to only get the difference if a condition is met.  My thought is to do something like this

Declare @diff decimal(18,4)
Create Table #Dope
(
  date1 datetime
  ,inlate decimal(16,4)
)

INSERT INTO #Dope (date1) Values
('2016-01-04 08:08:47.000'),
('2016-01-04 08:25:59.000'),
('2016-01-04 08:27:47.000'),
('2016-01-04 08:00:00.000')

if CAST(date1 As Time) > '08:00:00.000'
--Here is where the capture would come but I am unsure of how to type it
SET @diff = CAST(datediff(second, [], [date1])/3600.0 As Decimal(18,4))

How do I set the 1st datetime in my Set statement to be 8 a.m. of the day that I am needing the difference in s econds?  Meaning, I can't just hardcode '08:00:00.000' into my Set statement as the two dates my be different which would give me seconds spanning days and that is not what I am after.  I just want to see the difference in seconds between date1 and '08:00:00.000'

First of all I'm not sure why you are dividing by 3600 .. that would return you 1/3600th of a second.

Second you are over thinking things a bit.  Try this:

CREATE TABLE #NotDope
(
  date1 datetime
)

INSERT INTO #NotDope (date1) Values
('2016-01-04 08:08:47.000'),
('2016-01-04 08:25:59.000'),
('2016-01-04 08:27:47.000'),
('2016-01-04 08:00:00.000'),
('2016-01-04 07:55:00.000')

SELECT date1,
    CASE WHEN CAST(date1 AS Time) > '08:00:00.000' THEN
        datediff(second, '08:00:00', CAST([date1] AS time))
    ELSE 0 END AS SecondsLate
FROM #NotDope

All you need to do is cast your date as a time data type to get rid of the date part.


Understanding the load running on my SQL server We have a project this year to move lot of physical SQL Servers, starting with medium critical servers onto Virtual machines.
Generally on physical boxes we have a large count of logical processors, for example 80. Now when moving to VM we get 64 vCores/vCPU.
My understanding is: it's ok to be on low vCores/vCPU in VM till the time their speed is faster. I mean clock speed compared to one on physical.
This bought up an interesting debate with my colleague on how above behaves differently between servers where the majority of load is either single threaded queries vs parallel ones.
Following are a few questions:

How true is above?

Is there a way we can determine even approximately the servers where the queries are running serially vs parallel?  I mean a way to say that my SQL Server has 70% queries which just run single threaded or parallel?

usually query leave to the sql server enegine to manage how a query will be executed and this bring to focus on the parallelism settings of the instances (or databases in the newer versions of sql server).
It's a matter of concurrency. Here you can find the sql server ingine parameters that govern the parallelism in query execution:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15
MAXDOP instance parameter determines the max number of cpu that a single query can use to run.
There is another parameter that govern the threshold for using a parallel plan or not.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-cost-threshold-for-parallelism-server-configuration-option?view=sql-server-ver15
If sql server expects a cost higher than this parameter then it will use a parallel plan (i.e. more than one logical cpu)
with this query you can count the number of parallel plans in cache against single thread plans:
SELECT
SUM(
    case when p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        max(//p:RelOp/@Parallel)', 'float') > 0 then 1 else 0 end
) as #num_parallel_plans,
SUM(
    case when p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
        max(//p:RelOp/@Parallel)', 'float') = 0 then 1 else 0 end
) as #num_nonparallel_plans
FROM
sys.dm_exec_cached_plans cp
CROSS apply sys.dm_exec_query_plan(cp.plan_handle) p
WHERE
cp.cacheobjtype = 'Compiled Plan'
OPTION (MAXDOP 1)


You can use JOIN in the FROM part instead of WHERE. This is the multi-table delete syntax, details in  mysql docs: DELETESyntax.

Example:

DELETE fa.*
FROM cce_sa_fa AS fa
  JOIN examinations AS ex
    ON ex.id = fa.exam
WHERE fa.exam IN (1,2,...)
  AND ex.name <> fa.name ;

Another way would be to convert the IN subquery to EXISTS:

DELETE
FROM cce_sa_fa AS fa
WHERE EXISTS
      ( SELECT *
        FROM examinations AS ex
        WHERE ex.id = fa.exam
          AND ex.name <> fa.name
      )
WHERE fa.exam IN (1,2,...) ;


Truncation vs Deletion When `session_replication_role = replica` When I first run

SET session_replication_role = DEFAULT;

CREATE TABLE users (
    id serial PRIMARY KEY
);

CREATE TABLE posts(
    id serial PRIMARY KEY,
    user_id INTEGER REFERENCES users(id)
);

And then run

INSERT INTO users(id) VALUES (1);
INSERT INTO posts(id, user_id) VALUES (1, 1);

SET session_replication_role = replica;

DELETE FROM users;

It successfully deletes the row in users even though this violates referential integrity because the session_replication_role = replica. So far so good.

However, when I instead run

INSERT INTO users(id) VALUES (1);
INSERT INTO posts(id, user_id) VALUES (1, 1);

SET session_replication_role = replica;

TRUNCATE users;

I get this output:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "posts" references "users".
HINT:  Truncate table "posts" at the same time, or use TRUNCATE ... CASCADE.

For performance reasons, I really want to be able to use TRUNCATE and not DELETE. How can I get around this issue without using TRUNCATE users CASCADE? I can not use cascading deletes because my DB may have other foreign keys that reference the users table that I do not want to delete.

First and foremost you should know the name of the FOREIGN KEY constraint:
CREATE TABLE users (
    id serial PRIMARY KEY
);

CREATE TABLE posts(
    id serial PRIMARY KEY,
    user_id INTEGER,

    CONSTRAINT fk_post_users
        FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO users(id) VALUES (1);
INSERT INTO posts(id, user_id) VALUES (1, 1);

Then you can DROP FOREIGN KEY and after that, TRUNCATE the table.
ALTER TABLE posts DROP CONSTRAINT fk_post_users;
TRUNCATE users;

Now, posts table remains in an inconsistent state, but you can add the foreign key again using NOT VALID option, that does not check current values.
ALTER TABLE posts ADD CONSTRAINT fk_post_users
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

db<>fiddle here
Quoted from Postgres docs:

ADD table_constraint [ NOT VALID ]
This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option. Foreign key constraints on partitioned tables may not be declared NOT VALID at present.
The addition of a foreign key constraint requires a SHARE ROW EXCLUSIVE lock on the referenced table.
Additional restrictions apply when unique or primary key constraints are added to partitioned tables; see CREATE TABLE.


SQLite3 not using covering index with json_extract expression I am attempting to create an index in SQLite3 (3.18) using json_extract expressions. My aim is to execute queries that only require the index to yield results. The reason for this is that json_extract is an expensive operation which would hinder performance when operating on larger data sets and/or values. I concluded I need a covering index to suit my needs.

Step 1 - Testing the theory using a normal table structure

CREATE TABLE Player (
    Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    FirstName TEXT NOT NULL,
    MiddleName TEXT,
    LastName TEXT NOT NULL
);

CREATE INDEX Player_FirstName ON Player (
    FirstName ASC,
    LastName ASC
);

EXPLAIN QUERY PLAN SELECT
    FirstName, LastName
FROM
    Player
WHERE
    LENGTH(LastName) > 10
ORDER BY
    FirstName
LIMIT
    10
OFFSET
    0

Yields

SCAN TABLE Player USING COVERING INDEX Player_FirstName

This is exactly what I expect. The query planner figured that the Player_FirstName index is appropriate due to the ORDER BY clause, and since the WHERE statement operates only on a value that is also in that index, it doesn't need to read the table. Finally, the SELECT statement includes only the indexed columns therefore resulting in a query that doesn't touch the table at all.

Step 2 - Testing the theory with an extract expression

CREATE TABLE PlayerJ (
    Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    Data TEXT NOT NULL
);

CREATE INDEX PlayerJ_FirstName ON PlayerJ (
    JSON_EXTRACT(Data, '$.FirstName') ASC,
    JSON_EXTRACT(Data, '$.LastName') ASC
);

EXPLAIN QUERY PLAN SELECT
    JSON_EXTRACT(Data, '$.FirstName') AS FirstName,
    JSON_EXTRACT(Data, '$.LastName') AS LastName
FROM
    PlayerJ
WHERE
    LENGTH(LastName) > 10
ORDER BY
    FirstName
LIMIT
    10
OFFSET
    0

Yields

SCAN TABLE PlayerJ USING INDEX PlayerJ_FirstName

This is not what I expected. The query planner seems to have figured out that the ORDER BY clause is on JSON_EXTRACT(Data, '$.FirstName'), and thus seems to have selected the appropriate index. But that is where my reasoning ends abruptly. What is going on here? I would have expected the query planner to figure out that this is the same as the previous test, and the index would be used as a covering index. But it doesn't.

Why not? And how can this second test be modified so that it runs only against the index?

The documentation says:

  The SQLite query planner will consider using an index on an expression when the expression that is indexed appears in the WHERE clause or in the ORDER BY clause of a query.

So expressions in the SELECT clause will not use the expression index.

Using a covering index is not as much an improvement over using a normal index for searching/sorting as using a normal index would be over using no index at all, so this optimization has not (yet?) been implemented for expression indexes.


How to fan out rows with a specified range of values? I have a table which essentially contains 3 columns: ID, FIRST, LAST. They're all integers.

I'm wondering if there's a way to use generate_series() to get a query result where for each row in the original table, there are many rows containing numbers between FIRST and LAST?

E.g. for a row with data (42, 5, 8) the output contains rows (42, 5), (42, 6), (42, 7) and (42, 8).

This can be achieved using generate_series()

with test_data (id, "first", "last") as (
   values (42, 5, 8),
          (43, 6, 9)
)
select td.id, g.nr
from test_data td
  cross join lateral generate_series(td."first", td."last", 1) as g(nr)
order by td.id, g.nr;

Online example: https://rextester.com/TWE47868


That number 9619362665 (9,619,362,665) is too big

Click This Link to See the Max Integer Types.

The biggest INT is 2147483647 (2,147,483,647).

If you want to store numbers larger than 2147483647, change the column as follows

ALTER TABLE USER MODIFY COLUMN MOBILE_NUMBER BIGINT;

Then, the INSERT should work.


MySQL JDBC does not behaves as the MySQL Workbench I'm running the following from the MySQL Workbench, and from my local tomcat using jdbc.

START TRANSACTION;
CREATE SCHEMA IF NOT EXISTS `ROOT`;
CREATE TABLE IF NOT EXISTS `ROOT`.`all_tables` (
    `_id` BIGINT NOT NULL AUTO_INCREMENT,
    `class_type` VARCHAR(100) NOT NULL,
    `schema` VARCHAR(30) NOT NULL,
    `table_name` VARCHAR(30) NOT NULL,
    `server_id` VARCHAR(30) NULL,
    `fields` VARCHAR(0) NOT NULL, PRIMARY KEY (`_id`),
UNIQUE INDEX `_id_UNIQUE` (`_id` ASC));
COMMIT;

It is a copy of query right before the execution which I compose in order to run via the jdbc which fails time after time with the following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE SCHEMA IF NOT EXISTS `ROOT`;
CREATE TABLE IF NOT EXISTS `ROOT`.`all_table' at line 2

So technically even if only execute the create table this fails with the same error, only line 1.

BUT, when I run the same query via the MySQL Workbench, it works wonderfully... time and time again!

Any idea what I'm missing?

Thanks.

Maybe the problem is that the Workbench is running your statements one at a time without you being noticed, while JDBC exactly sends to the database what you exactly ask it to send.

See this answer, where a parameter is used on the connection driver to allow many SQL statements over a single JDBC statement object.


How to add a Default constraint while creating a table? SQL Server I am trying to create a new table with columns followed by their constraint as shown below.

Create tblTest(
columns..
..
..
Gender int,
Constraint DF_tblTest_Gender Default 3 For Gender,
..
..
..
)

However, I am getting an error message near the default constraint as,

  'Incorrect syntax near 'for''

You can name the constraint inline:
CREATE TABLE tblTest(
  --
  --
  Gender int CONSTRAINT DF_tblTest_Gender DEFAULT 3,
  --
) ;

As the CREATE TABLE msdn page shows:

DEFAULT
... To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

In the same page, we can find that the only options for <table_constraint> are PRIMARY KEY, FOREIGN KEY and CHECK constraints:

< table_constraint > ::=
  [ CONSTRAINT constraint_name ]
{
   { PRIMARY KEY | UNIQUE }
       {
         NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
         | NONCLUSTERED HASH (column [ ,... n ] )
               WITH ( BUCKET_COUNT = bucket_count )
       }
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
    | CHECK ( logical_expression )
}

so if you want to add a default constraint (naming or not) the only ways are by doing it inline or with an ALTER TABLE statement.


Unrecognised system process saturating CPU We have switched to our backup DC today as primary site Bandwidth has gone.

We are getting issues with the most powerful DB server; it is spawning the following process for several db's on the server. One database is simply a small scratch db that is used for maintenance tools, so not replicated or mirrored.

Does anyone know or recognize the following code?

() select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
from [<dbname>].[sys].[filetable_updates_2105058535] with (readpast)
order by table_id

This was indeed down to some structure being left over from a SQL2012 AV group as had been doing some testing on these servers during a period when they were not in production as they were the only hardware in different DC's suitable.

During the next window of opportunity I rebooted the servers and the performance of both servers has vastly improved and the processes are no longer appearing. (added to my list of issues to all the ones Brent details on his website with SQL2012 AV group issues)

2012 AlwaysON Setup

A must read for anyone attempting to configure these. As noted in Brent's articles I found a number of pitfalls, more tricky than expected and currently have decided to postpone the rollout due to my own issues with unsuccessful multisubnet failover (i could only get this to work in 1 direction) and above mentioned performance overhead.


Find related names in the table I have a table with 2 columns, name and related_name.

CREATE TABLE _test(
[name] NVARCHAR(50),
related_name NVARCHAR(50)
)
GO

INSERT INTO dbo._test
([name], related_name)
VALUES

('a', 'b'),
('b', 'c'),
('c', 'd'),
('e', 'f'),
('f', 'g')

I want to write a UDF, which will get a name and will return all the related names. If a is related with b and b is related with c this means that a is related with c.

So the expected result for parameter 'a', 'b', 'c', 'd' must be the same.

select * from my_func('a')

The result must be

result
------
a
b
c
d

And the result for

select * from my_func('e')

must be

result
------
e
f
g

You can use a recursive CTE for this, provided you don't have more than 100 relations (since you can't add MAXRECURSION inside the function, so you'd have to apply it on any query that references the function - see an example here).

The first step is to find all the parent-less parents, then use that as an anchor for recursion.

CREATE FUNCTION dbo.my_func(@input nvarchar(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN
  (
    WITH Parents(name) AS
    (
      -- find parent-less parents:
      SELECT name FROM dbo._test
      EXCEPT
      SELECT related_name FROM dbo._test
    ),
    children AS
    (
      -- use parents as anchor
      SELECT orig = name, name FROM Parents
      UNION ALL
      SELECT c.orig, related_name
        FROM dbo._test AS t
        INNER JOIN children AS c
        ON c.name = t.name
    )
    SELECT name
    FROM children AS o
    -- now filter only to a single, common anchor
    WHERE EXISTS
    (
      SELECT 1 FROM children WHERE name = @input AND orig = o.orig
    )
  );

Then you can query:

SELECT name FROM dbo.my_func(N'b');

Results:

name
----
a
b
c
d

Of course it is easy to break this with a circular reference. Don't do that.


Use Older Versions Of SQL Server Databases With 2012? We have been asked to move from SQL Server 2008 R2 over to 2012, the installation completed a few hours ago. Ever since I've been trying to attach databases from 2008 over to 2012.

It seems to be having no effect. Error messages below:

  Attach database failed for Server 'Development\SQLSERVER'.  (Microsoft.SqlServer.Smo)
  Database 'PS_EmpData' cannot be upgraded because its non-release version (539) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

I mean, this table is quite a big database; and the time it would take to re-create with all the inserts would be unreal. We are still operating with our SQL 2008 R2 for production, but development process is underway on 2012.

My overall question is, do we have to re-create the database to use 2012 or is there a work around?

The version (539) indicated in the error message says that this database is a Sql Server 2000 database and thus is not supported in Sql Server 2012 See here

Probably in your SqlServer 2008R2 this particular database has a compatibilty level set to Sql Server 2000. You could try to change this level going to the properties of the database, Options page  and changing the compatibility level to 2008, then retry to attach the database to Sql Server 2012


How to replace a single table with Identity Primary Key column from a database backup? I need to restore a table from a backup. My understanding is the only way to get a single table back into the database is to copy it into a temporary table. Then drop the old table and rename the new one to the old name. My problem comes with this particular step due to the Primary Key column being an Identity column. The table also has multiple foreign key constraints. How can I copy one table from my backup database into the original database and still maintain the Primary key identity column and all foreign constraints?

Here is my current scripted attempt:

CREATE TABLE dbo.Incidents_tmp
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[IncidentAction_ID] [int] NULL,
[IncidentCompromise_ID] [int] NULL,
[IncidentDamage_ID] [int] NULL,
[IncidentReportingEntity_ID] [int] NULL,
[IncidentStatus_ID] [int] NULL,
[IncidentType_ID] [int] NULL,
[IncidentID] [varchar](20) NULL,
[Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_Incidents_tmp] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Incidents_tmp ON
GO

IF EXISTS ( SELECT *
        FROM DB_old.dbo.Incidents )
INSERT INTO DB_new.dbo.Incidents_tmp
        (
        ID ,
        IncidentAction_ID ,
        IncidentCompromise_ID ,
        IncidentDamage_ID ,
        IncidentReportingEntity_ID ,
        IncidentStatus_ID ,
        IncidentType_ID ,
        IncidentID
        )
    SELECT
    ID ,
    IncidentAction_ID ,
    IncidentCompromise_ID ,
    IncidentDamage_ID ,
    IncidentReportingEntity_ID ,
    IncidentStatus_ID ,
    IncidentType_ID ,
    IncidentID ,
    FROM DB_old.dbo.Incidents TABLOCKX
GO

SET IDENTITY_INSERT DB_new.dbo.Incidents_tmp OFF
GO

ALTER TABLE dbo.IncidentSubject DROP CONSTRAINT
FK_IncidentSubject_Incidents;
ALTER TABLE dbo.IncidentsReportLog DROP CONSTRAINT
FK_IncidentsReportLog_Incidents;
ALTER TABLE dbo.Incidents DROP CONSTRAINT PK_Incidents;

DROP TABLE DB_new.dbo.Incidents
GO

Exec sp_rename 'Incidents_tmp', 'Incidents'

Exec sp_rename 'PK_incidents_tmp', 'PK_Incidents'

ALTER TABLE [dbo].[IncidentSubject]  WITH CHECK ADD  CONSTRAINT
[FK_IncidentSubject_Incidents] FOREIGN KEY([Incident_ID])
REFERENCES [dbo].[Incidents] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[IncidentSubject] CHECK CONSTRAINT
[FK_IncidentSubject_Incidents]
GO

When I try adding the constraints back in I get an error stating:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_IncidentSubject_Incidents". The conflict occurred in database "DB_new", table "dbo.Incidents", column 'ID'.

EDIT:
Some additional info. The user of this application deleted 175 incidents by accident. Unfortunately she didn't tell us until two weeks later and then it wasn't even routed to us until almost a month later. In users have been accessing the application and making changes so a complete database restore is not an option. The user is requesting just the deleted incidents be restored. I looked into the database and noticed an incidents table with the missing data in the current database and the 175 records existing in the backed up database. Hope that provides some better context for why I'm trying to restore just a single table of incidents. It starting to look like additional tables need to be restored.

Based on the information you have provided in your question, I think you will need to restore a good backup (with the missing rows for Incidents) into a test database.  You'll need to insert into the real current Incidents table the missing rows from the backup version of the Incidents table (making sure you properly use the SET IDENTITY_INSERT command).  Using SET IDENTITY_INSERT ON allows explicit values to be inserted into the identity column of a table thus allowing you to insert the deleted rows.  Make sure you issue the SET IDENTITY_INSERT OFF after you have completed your inserts.  You'll need to do the exact same comparison and insert for the children tables for each of the parent rows you are restoring since the children were automatically deleted when the parent rows were deleted (due to cascade delete FK's).

After you have addressed all children tables, you should be able to add the FK's back again with CHECK.

You might want to prototype this on a test server.

--Demo IDENTITY_INSERT
--Create test table
IF OBJECT_ID('dbo.TestTable') IS NOT NULL
    DROP TABLE dbo.TestTable
GO

CREATE TABLE [dbo].[TestTable] (
    TestId INT identity(1, 1) NOT NULL
    ,TestColumn VARCHAR(5)
    ) ON [PRIMARY]
GO

--insert a few test rows
INSERT INTO TestTable (TestColumn)
VALUES ('one'),('Two')

--select the two rows - the id values will be 1 and 2
SELECT * FROM TestTable

--delete the two rows
DELETE FROM testtable

--set identity_insert on to allow you to explicitly insert id values
SET IDENTITY_INSERT dbo.TestTable ON

--insert id 1 and 2
INSERT INTO TestTable (TestId,TestColumn)
VALUES (1,'one'),(2,'Two')

--set identity_insert off
SET IDENTITY_INSERT dbo.TestTable OFF

--select the current rows - should be id 1 and 2
SELECT * FROM TestTable


Strategies for organising SQL Server with large amount of RAM We now have a new server for our DB and amongst other things we have 128GB of RAM available (previously I had 16GB) . I know SQL Server is very good at managing it's resources, but I was wondering if there are any special settings or strategies that I should employ in either the server/db settings or processing code (stored procs/indexes etc) to ensure that SS takes best advantage of the available ram.

The DB is about 70GB and it's a non transactional db (it's a data warehouse). So basically large WRITE followed by massive READ is the normal flow of things.

Even though you have memory to spare at the moment (70GB database vs 128GB server) I would still configure the maximum server memory as per latest guidance.

  ...reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed
  from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB
  RAM.

That equates to 110GB on a 128GB server. Reasoning being that the data warehouse will likely continue to grow and could eventually grab more than you want it too, so put the correct limit in now. It will get forgotten about otherwise.

When your data volume gets beyond the 110GB limit, pay closer attention to the servers free memory. If you consistently have additional GB to spare you may consider raising the limit by a few GB.

Other than that your only other fine tuning options are with trace flags, which should be treated with the caution they deserve. Test, test and test some more.

The three candidates I can think of that would make sense are:

TF834 - Large page allocations
TF2301 - Query Processor Modelling Extensions (typically good for OLAP, potentially problematic for OLTP)
TF4199 - Query Optimiser Changes

As has been mentioned in an answer previously, Thomas Kejser has referred to TF834 as SQL Servers only "go faster" switch. TF2301 enables optimisations which are beneficial to DW type queries. TF4199 requires reading the linked document, so do.


How can I properly query this? I am planning on making a comment-reply system inside of a site. (A simple one)

I searched through youtube related to this, and I found a good source. The problem is, he has a 2 separate queries, one for parent comment and two for reply to parent comment and what's more is that the second query is inside a foreach loop (bad practice). So, I need to revised of how he query the comments and the tables.

This is the table I came up with:

comment_table
id | post_id | comment_id | date | message | reply_id

//In the source, he has 2 tables, parent and child comment table.

I want to create a single query where parent comments and child comments are get back at the same time, but I am not sure if it will work.

Let's say I have a table like this.

id | post_id | comment_id |     date | message | reply_id
 1 |       2 |          3 | jan 2016 |   hello |
 2 |       2 |          4 | feb 2016 |  hello2 |
 3 |       2 |          5 | mar 2016 |  hello3 |        3

Note: reply_id is where the comment_id replied to.

I need to output this results like this:

Comments

hello2 //comment
hello //comment
   hello3 //reply

This is what I need to do:

Get all comments in DESC order by date while checking if reply_id is not empty, and if not empty, it will check it based on reply_id in DESC order by date.

My problem is, the I only thing that I know how to query is the first part and that is to Get all comments in DESC order by date, the rest is I do not know.

My question is, is this possible to query? If so, what syntax do I need here? If you have a better solution than mine, please tell me.

The name reply_id is a bit confusing.  Consider in_reply_to or parent_id.
I assume that post_id is the id for the entire thread?  Maybe it should be called thread_id?  ("Post", to me, is somewhat synonymous with "post".)

I don't think you need two tables, at least not for comments.  However, if you do have information about the thread as a whole, I recommend a separate table for that; it would have PRIMARY KEY(thread_id).

What's the problem with this?

SELECT ...
    FROM ...
    WHERE thread_id = 2
    ORDER BY id DESC;

Can have multiple replies to a single comment?  If so, you should change the question to make that clear.  It adds a level of complexity.

You seem to have a "hierarchical" tree.  But, since you have post_id, the above query should get all of the messages, approximately in chronological order.

Why have comment_id if you already have id?

Or, start over...

Information about the entire Thread is in a separate table, and identified by thread_id.
The Comments are in a second table, with PRIMARY KEY(thread_id, comment_id). comment_id can simply be an AUTO_INCREMENT.
Each comment in a "thread" has the same thread_id.
Each row has a unique comment_id.
"Replies" need to identify their parent_id.


Minimum value on most recent date per account MS ACCESS SQL I have a MS ACCESS DB table Riskscore with the three fields: CHACCTBS, CHRQDATE, CHSCORE and want to find the lowest CHSCORE on the most recent CHRQDATE per CHACCTBS where it is possible to have multiple CHSCORE values on the same CHRQDATE.  There should be one record returned for each CHACCTBS.  There are about 17,000 records.  Once the query has run, there should be 7008 records returned.

CHACCTBS, CHRQDATE, CHSCORE
101    1/1/2014    781
101    1/1/2014    791
101    12/31/2013   717
221    10/01/2013   512
235    1/2/2014     685
235    10/31/2013   600

The query should return:

101    1/1/2014    781
221    10/01/2013   512
235    1/2/2014     685

Thanks!

This would be a good place to use window functions if your RDBMS supports them. The example below is T-SQL so if you are not on SQL Server you may have to adapt the syntax and/or keywords.

Window Functions are ANSI Standard starting with SQL:2003, and as such you likely have access to them with any modern RDBMS. If you are on SQL Server read about their implementation of Window Functions here.

SELECT CHACCTBS, CHRQDATE, CHSCORE
FROM
(
    SELECT
         MIN(CHSCORE) OVER(PARTITION BY CHACCTBS) As CHACCTBS
        ,CHRQDATE
        ,CHSCORE
        ,ROW_NUMBER() OVER(PARTITION BY CHACCTBS ORDER BY CHRQDATE DESC, CHSCORE) As Limiter
    FROM RiskScore
) X
WHERE Limiter = 1

The subquery returns your MIN and other columns, and creates a filter by assigning row numbers by your partition and ordered by descending dates. You then take the first row of each partition in the WHERE clause.


High Availability Options For SQL Server I'm looking to upgrade my single server SQL Server 2008 environment to a platform with much greater redundancy. The current system was put in 5 years ago when the company started and so was built down to as low a cost as possible. However, we're now well established with a large client base and as such the replacement system needs to be much more redundant.

We have a few sites around the country (as we're part of a larger group with multiple sites), so have access to backup offices etc and remote sites to host servers.

I'd like suggestions about how best to achieve having our two or three key databases available throughout hardware failure or lack of access to site. I'm thinking of some type of cluster with a pair of local servers, then one off-site server with a copy of the DBs on. The DBs themselves are actually very small, so hardware can be pretty low spec and I can get away with a few physical boxes. However, I'm conscious that SQL Enterprise 2016 is VERY expensive as we'd have to licence by core.

I'm open to all suggestions/options etc. We're a pure MS house, so we've no other OS platforms. I'm reading conflicting reports regarding DB mirroring vs clustering etc.

Thanks

For your onsite solution Standard Edition allows you to implement a two node Failover Cluster Instance with Basic Availability Group or database mirroring. https://msdn.microsoft.com/en-us/library/mt614935.aspx

For your offsite solution you could implement log shipping.

They are using the "Always On" naming convention for a lot of the high availability options now.  There is Always On Failover Cluster Instances which is the old Windows Server Cluster Service that is for hardware failover with SQL Failover Cluster Instance that is for SQL Instance failover.
Then, there is Always On Availability Groups which requires Enterprise Edition and consists of a group of databases that can failover to replicas and replicas can be read-only, if one so desires.  Basic Availability Group is a limited version of the Always On Availability Group, and is for just one database. It will completely replace database mirroring when it is removed from future versions.  Lastly, database mirroring still exists.

SQL Server 2016 Edition Feature Comparison:
https://msdn.microsoft.com/en-us/library/cc645993.aspx

If you can fulfill business requirements with Standard Edition, then there is less reason to go virtual, which adds a whole other layer of complexity.


Can't figure out this "trick" to optimize MySQL query Here's an excerpt of this article:

SELECT id, name, address, phone
FROM customers
ORDER BY name
LIMIT 10 OFFSET 990;

  MySQL is first scanning an index then retrieving rows in the table by
  primary key id. So it’s doing double lookups and so forth.

  The following piece just uses the primary key:

SELECT id
FROM customers
ORDER BY name
LIMIT 10 OFFSET 990;

I don't figure out the difference between those two queries despite the explanation, especially the evoked double lookup...

May someone explain it in more detail?

The table has a secondary index on name(let's call it idx_name for the reference). Internally MySQL stores records in secondary indexes as a pair of (key, value), where key is indexed field and value is the primary key. In this case it will be (name, id).

To execute the first query MySQL decided to use index idx_name. But in order to satisfy the query it has to take id for each value of name and go to PRIMARY index in order to get address and phone values. That's why they call it "additional lookup".

For the second query MySQL has all necessary fields in index idx_name. Remember, id is the part of the index?


group records according to column hi how can make this result query:

to this:

I have this code and i know it's still farfetched:

select
        max(case subcod when 'DISABILITY' then A end) as disability,
        max(case subcod when 'DISMEMBERMENT' then B end) as dismemberment,
        max(case subcod when 'MEDICAL' then C end) as medical,
        max(case subcod when 'OTHERS' then D end) as others
    from
        (
        select subcod, description as A, null as B, null as C, null as D from grp_appl_ref where ref_id = 'RIDERS' and subcod = 'DISABILITY'
        union
        select subcod, null, description, null, null from grp_appl_ref where ref_id = 'RIDERS' and subcod = 'DISMEMBERMENT'
        union
        select subcod, null, null, description, null from grp_appl_ref where ref_id = 'RIDERS' and subcod = 'MEDICAL'
        union
        select subcod, null, null, null, description from grp_appl_ref where ref_id = 'RIDERS' and subcod = 'OTHERS'
        )

I was wondering if there's another reserved word instead of max so i can remove the null values so the cells with values would move up.

is it possible?

thanks,

Use CASE statement as following

SELECT
CASE WHEN ref_id = 'RIDERS' and subcod = 'DISABILITY' THEN   description ELSE NULL END AS DISABILITY,
CASE WHEN ref_id = 'RIDERS' and subcod = 'DISMEMBERMENT' THEN   description ELSE NULL END AS DISMEMBERMENT,
CASE WHEN ref_id = 'RIDERS' and subcod = 'MEDICAL' THEN   description ELSE NULL END AS MEDICAL,
CASE WHEN ref_id = 'RIDERS' and subcod = 'OTHERS' THEN   description ELSE NULL END AS OTHERS
FROM grp_appl_ref

If at any stage need to sort out records while putting NULL values at the end use CASE statement in ORDER BY clause

ORDER BY CASE columnName WHEN NULL THEN 1 ELSE 0 END


How to reduce database size with BLOB fields I have a database that's used to store fingerprints. Under my conception this should be a small database but the problem is that the original developer was probably tripping on peyote and decided it would be a great idea to store the fingerprints in BLOB fields.

Thanks to this magic trick the database is now 3 GB in size and the frontend its crashing all the time, no wonder since a simple SELECT TOP(1000) takes about a minute from MSSMS.

So I'm left with a doubt about how to proceed. Is there a way I could reduce the size of the table containing the pictures? Or maybe another alternative to this madness.

Regards.

3GB is not really a lot of data.  My laptop has 16GB memory, our datawarehouse is 3TB and that would be a fairly small warehouse.  The maximum database size according to here is 524,272 TB!?  So I think performance here is more about the kit you are on, plus index and query design.

You could refactor as Russell has suggested.  However you could also maintain the original one-table design with appropriate indexing, either a non-clustered covering index, or even full-text indexing to support the 'search for record' type queries - this should not include the blob.  Then only get the fingerprint images on an individual basis, when required by the application.  To put this another way, do not include the blob column in SELECTs where it is not explicitly required.  This will have the same effect as splitting the data into two tables without the redesign.  Don't attempt to get all the fingerprints at once!

Please tell us more about the spec of kit you are on, the version of SQL Server you are running, the table DDL (including indexes) plus what type of queries the application normally runs.


Call stored procedure via trigger or SQL Server Agent At the moment, I need to synchronize data between two SQL Server databases on separate servers but on the same network. As the schema between the tables that are to be synced differ, I've been developing a solution that utilizes a custom queue to transfer data. This involves triggers for each table that inserts the new/updated fields into a transfer queue table, to which a periodically run stored procedure via SQL Server Agent checks for entries, acts accordingly, then deletes those entries.

My question is whether or not it'd be more efficient to remove the transfer queue table and instead evoke the stored procedure via the trigger and transfer the data that way. Would the trigger simply call the stored procedure and complete or would it wait for the stored procedure to complete before exiting itself?

To answer your question directly, the trigger will not finish until the SP finishes, meaning your DML statements that cause the trigger to fire will also not finish (until the fired trigger finishes). This may cause long duration of open transactions with locks everywhere.

So including SP inside trigger is a big NO. This can cause huge performance issue when your SP slows down for whatever reason. It has direct negative impact to your workload throughput.

Not sure about your sql server version, if your version is SQL Server 2008+, you may consider Change Data Capture as your solution.


Mysql database offline-online synchronization I need to synchronize database on server with database of local server upon active internet connection.

For its implementation I execute mysqlbinlog utility to read transaction logs and write the transaction queries into a sql file

Thereafter I execute sql file on database server to apply transaction updates on database on server

But I need to fetch only latest transaction logs within specific time interval across multiple binary log files . Please assist me in its implementation

As per your requirement you're suggesting leads to this solution:

mysqlbinlog --start-datetime=<START_DATETIME> --stop-datetime=<STOP_DATETIME> binlog.xxx > binlog.xxx.sql

Though this is very much prone to corrupt your data and/or introduce inconsistency! Not recommended!

There can be multiple solutions to one problem, if you could share the scenario you would get a better solution.

For eg:

If you're planning to make application specific changes on development (local) server and want to push that to live, use version control to manage the SQLs and exec to live.
If it is only specific database, you can setup replication and only move replicate changes to that db (or even tables).