SQL get difference of row data in another row I want to calculate how many days a product has been out of stock

I have an audit.inventory table where data gets inserted after change in the inventory table.

Inventory Table is as below:

  inventoryid    productid     stock
  1              1             8
  2              2             5

And audit.inventory is same as inventory with an extra stamp field for when the change occured

audit.inventory is as below:

  inventoryid    productid     stock    stamp
  1              1             8        2018-06-26 14:59:33
  1              1             6        2018-06-26 14:59:33
  1              1             5        2018-06-26 14:47:09
  1              1             0        2018-06-21 18:30:50
  1              1             0        2018-06-04 16:57:02
  1              1             3        2018-06-04 14:58:57
  1              1             2        2018-06-02 12:54:18
  1              1             0        2018-05-27 11:44:22

So if I run this query for productid = 1

select extract(day from stamp) as day_of_month, max(stock) from
audit.inventory where productid = 1
and extract(month from stamp) = 6 group by extract(day from stamp);

Gives me:

day_of_month    stock
2               2
4               3
21              0
26              5

How can I calculate the stock on days in between, for example 1(day_of_month) will have stock 0, 3(day_of_month) will have 2 as the stock and 4-20(day_of_month) will have stock 3 and then 22-25 will have stock 0.
So the total number of days the product stock has been 0 will be 6 ie. 1,21,22,23,24,25

fiddle

WITH cte (day_of_month, stock) -- add pseudo-records
AS
( SELECT day_of_month, stock FROM test
  UNION ALL
  SELECT 1,0
  UNION ALL
  SELECT 31,1 -- June have 30 days
),
cte2 (leadday, day_of_month, stock) -- calculate lead
AS
( SELECT LEAD(day_of_month) OVER (ORDER BY day_of_month, stock), day_of_month, stock
  FROM cte
)
SELECT SUM(leadday - day_of_month) -- obtain data we need
FROM cte2
WHERE stock = 0;

To make sure the result is correct both when there exist the record for the first day of month and when the last record have stock = 0 uncomment appropriate values for test table data.


Removing a FILESTREAM windows share after uninstall I had to uninstall my local SQL 2017 Dev edition on my home PC (Windows 10) because I changed some drive structure (including where all the system databases lived) a while ago, so I needed to reinstall SQL Server to re-create those system databases (no backups since home Dev instance) and re-set the proper drive paths. I used a configuration file for the re-install process. It seems that when you uninstall SQL Server 2017 it does NOT remove the filestream share (bug?). So when I get to the install step on filestream, it says

Windows share MSSQLSERVER already exists and cannot be created for FILESTREAM file I/O. Specify another share name.

I've tried removing this existing MSSQLSERVER filestream windows share from admin command prompt by doing:

net share MSSQLSERVER /DELETE

But I get an error:

System error 3 has occurred.
The system cannot find the path specified.

So how can I delete this existing old MSSQLSERVER filestream windows share so I can proceed with the SQL 2017 setup and configure the new filestream share as part of the install process (using the same default MSSQLSERVER share name)?

I managed to find this answer on Stack Overflow.

https://serverfault.com/questions/543326/how-to-remove-a-windows-share-when-the-folder-does-not-exist-anymore/543350#543350

I removed the MSSQLSERVER share key from the registry at the below path, and rebooted. The share is no longer there in net share in command prompt.

I was able to proceed with my re-installation with FILESTREAM enabled.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Shares


SQL Server service is running under an account that is a member of the built-in Administrators group I am using RAP tool for SQL Server, I get the following alerts in RAP assessment report:

  SQL Server service is running under an account that is a member of the built-in Administrators group

Solution or suggestion provided by MS is

Create a new domain user account, MSA (Managed Service Account) or gMSA (Group Managed Service Account) without assigning any rights or permissions. Use SQL Server Configuration Manager to assign the account to the SQL Server service.
Avoid using the same Service Account for multiple SQL Instances, only use the same account for the instances that are member of the same cluster or availability group.
Review the Context and Best Practices and Learn More sections for additional considerations and details.

I am really confused exactly what i have to do or change to remove this Alert.
SQL Server is running under account MAD\SPITOPCMSQL01

What do I have to change so that this login is not member of built-in administrators?

Please help

The built-in Administrators group is a machine-local group.  You need to connect to the machine where SQL Server is running via the Computer Management console, and remove the SQL Server service account from the "Administrators" group, as in:

Be aware, the SQL Server may, for some reason, require access to the local machine via membership in the local Administrators group.  Only you can determine if this is required for your specific instance, since it is not required for an out-of-the-box install.


Knex pass over db FK constraints when I try to insert values into my FKs in the DB Browser for SQLite I get the constraints error, but when I run the seeders with knex (npm package) it pass over the FK constraints and it inserts when it should return an error.

I had before my knexfile.js like this:

    var knex = require('knex')({
    dialect: 'sqlite3',
    connection: {
      filename: './database'
    },
    useNullAsDefault: true
  });

It was needed to activate here the PRAGMA for foreign_keys. To do it change it to:

    var knex = require('knex')({
    dialect: 'sqlite3',
    connection: {
      filename: './database'
    },
    pool: {
      afterCreate: function (conn, cb) {
        conn.run('PRAGMA foreign_keys = ON', cb)
      }
    },
    useNullAsDefault: true
  });


Is there any performance issue with including the "COLLATE" as part of the query as opposed to setting it for the column/table? PostgreSQL has this "collate" concept. You can either tell a table's column to always have a "collate", or you can do it in the query.

If I do it in the query, does PG punish me somehow performance-wise? I mean, it makes sense that it can "prepare itself" if I tell the table/column to have a specific "collate", rather than it having to figure it out "on the fly", but is it the case?

Also, any general tips on whether or not I should be having the collate in the query or table/column definition?

While not an expert on PostgreSQL collations, I have used PostgreSQL a little bit (I work mostly with SQL Server) and do not see how this particular info would/could be different than what I am about to describe:

Short answer: It Depends (standard answer).

Actual answer: specifying a collation at the column level (assuming that the collation is completely independent of the encoding / character set, which is the case for PostgreSQL and others, but in SQL Server the encoding is part of the collation) is merely a default that will be used for all sorting and comparison operations that do not explicitly provide a collation via a COLLATE clause. The storage of the data is impacted by the encoding / charset (i.e. Windows-1252 vs UTF-8 vs UTF-16 vs etc), but collation has no impact on data at rest because collation is merely rules for working with the data.

That said, the "depends" part is that if you create an index, it will physically store that data (i.e. the index keys in a separate structure) using the rules of the column's collation (unless you specify the COLLATE clause when creating the index, and not all RDBMSs allow for that). If you then specify a COLLATE clause in the query that is different than the collation used for the index, you will have a performance penalty because you won't be using that index. But, to specify a collation in a query that is different than the column's collation when an index is not available for the column's collation is not a performance issue as the data was not sorted in either collation prior to the query.


Update statement with a function I'm just curious. I was trying to optimize an update statement with a function

Ex

update circle set area = fnGetArea(radius) where ...<bunch of conditions>

This update went 5-10mins and when I removed the fnGetArea, it went 5-10 seconds!

How did that happen...?

I want to google it but I'm not sure what to type in.

Please enlighten me! :D

Thanks in advance

Answering my own question, I found this link, which contains the answer:

  ...by having this as a function on your update, you are essentially
  calling the function as many number of times as there are rows in the
  table. This is not the best way to do this, and is known as row-by-row
  processing, rather than set-based.

  Try and take the processing that the function is doing and apply it to
  the table in one go rather than this one-at-a-time approach.


#1066 - Not unique table/alias: 'clients' I have Problem:

  #1066 - Not unique table/alias: 'clients'

SELECT request.*,
       clients.ClientName AS ToClient,
       clients.ClientName AS FromClient,
       drivers.DriName, requesttype.ReqTName
FROM request
    INNER JOIN clients ON clients.ClientID = request.ReqToClient
    INNER JOIN clients ON clients.ClientID = request.ReqFromClient
    INNER JOIN drivers ON drivers.DriID = request.DriID
    INNER JOIN requesttype ON requesttype.ReqTID = request.ReqTID

You joined clients twice in the same FROM clause, that's throwing the error.

The two incarnations of the table need to have different aliases (chosen: cl_to and cl_from):

SELECT request.*,
       cl_to  .ClientName AS ToClient,
       cl_from.ClientName AS FromClient,
       drivers.DriName, requesttype.ReqTName
FROM request
    INNER JOIN clients AS cl_to    -- alias 1
        ON cl_to.ClientID = request.ReqToClient
    INNER JOIN clients AS cl_from   -- alias 2
        ON cl_from.ClientID = request.ReqFromClient
    INNER JOIN drivers
        ON drivers.DriID = request.DriID
    INNER JOIN requesttype
        ON requesttype.ReqTID = request.ReqTID ;

I'd prefer to use (short) aliases for all tables:

SELECT rq.*,
       cl_to  .ClientName AS ToClient,
       cl_from.ClientName AS FromClient,
       dr.DriName, rt.ReqTName
FROM request AS rq
    INNER JOIN clients AS cl_to   ON cl_to.ClientID = rq.ReqToClient
    INNER JOIN clients AS cl_from ON cl_from.ClientID = rq.ReqFromClient
    INNER JOIN drivers AS dr      ON dr.DriID = rq.DriID
    INNER JOIN requesttype AS rt  ON rt.ReqTID = rq.ReqTID ;


HeidiSQL rounds down double values upon insert...select I have the following table:

CREATE TEMPORARY TABLE `new_aps` (
  `MinuteStart` datetime NOT NULL,
  `ProxyListID` bigint(20) NOT NULL,
  `SuccessCount` double(22,0) NOT NULL,
  `UnknownCount` double(22,0) NOT NULL,
  `FailureCount` double(22,0) NOT NULL,
  PRIMARY KEY (`ProxyListID`,`MinuteStart`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

When I try to run the following query in HeidiSQL:

INSERT INTO new_aps (MinuteStart, ProxyListID, SuccessCount, UnknownCount, FailureCount)
SELECT NOW(), 1, 0.5, 1.5, 2.5

I get the following entry in new_aps table:

SELECT * FROM new_aps
"2020-06-11 11:58:19"   "1" "0" "1" "2"

What am I doing wrong? I need double-precision floating-point values stored in the table.

MariaDB version is 10.4.13 on Windows 10 64-bit.

Apparently HeidiSQL corrupts the table creation code. The proper table creation code is (without bounds after double):

CREATE TEMPORARY TABLE `new_aps` (
  `MinuteStart` datetime NOT NULL,
  `ProxyListID` bigint(20) NOT NULL,
  `SuccessCount` double NOT NULL,
  `UnknownCount` double NOT NULL,
  `FailureCount` double NOT NULL,
  PRIMARY KEY (`ProxyListID`,`MinuteStart`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

And the proper way to get it is from the database server, rather than HeidiSQL client:

SHOW CREATE TABLE new_aps


Execute SP whenever any job is finished I have many SQL Server jobs on the server. I need to execute a SP whenever any job execution is done. One hard way is to create a step on every job which calls that SP.

Is there any simple way to do that? Does, SQL Server execute any SP or anything after job completion?

I have tried to recreate your scenario on a test system using a simple stored procedure. The way I have approached this problem is as follows:

Create a 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'.
Create a test stored procedure to be called by the 'BackgroundProcess' job.
Create a 'BackgroundProcess' job that continuously loops, checking for jobs completed and then executes the 'TestSP' stored procedure.
Configure the 'StartBackgroundProcess' stored procedure to be called whenever SQL Server starts.

You can configure the BackgroundProcess to run at any interval within the code and it will check for completed jobs since the last run (ex. check every 30 seconds, 5 minutes, etc.).  It then fires a test stored procedure that you can replace with whatever one you were wanting to use.

/*
1. Create a 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'.
2. Create a test stored procedure to be called by the 'BackgroundProcess' job.
3. Create a 'BackgroundProcess' job that continuously loops, checking for jobs completed and then executing the 'TestSP' stored procedure.
4. Configure the 'StartBackgroundProcess' stored procedure to be called whenever SQL Server starts.
*/

-- 1. Create the 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'.

USE master
GO

IF EXISTS (SELECT * FROM master.dbo.sysobjects o WHERE o.xtype IN ('P') AND o.id = object_id('master.dbo.StartBackgroundProcess'))
DROP PROC StartBackgroundProcess
GO

CREATE PROCEDURE dbo.StartBackgroundProcess
AS
BEGIN
    EXEC msdb.dbo.sp_start_job 'BackgroundProcess'
END
GO

-- 2. Create a test stored procedure to be called by the 'BackgroundProcess' job.

USE Tempdb
GO

CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN
    SELECT 'Running Test', GETDATE()
END
GO

-- 3. Create the 'BackgroundProcess' job that continuously loops, checking for jobs completed and then executing a specified stored procedure.
-- ACTION REQUIRED: Copy/Paste the commented SQL below (up until Step 3) into a new job named 'BackgroundProcess' as a SQL Step and Save. No need to configure a schedule.
-- ACTION REQUIRED: Remove the beginning/ending comment tags from the job!
/*
USE master
GO

DECLARE @whilecounter AS INT
DECLARE @i AS INT
DECLARE @ScriptToRun varchar(250)
DECLARE @LastRun datetime
DECLARE @StatementToRun VARCHAR(5000)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype IN ('U') AND o.id = object_id('Tempdb.dbo.BackgroundProcess'))
DROP TABLE Tempdb.dbo.BackgroundProcess

CREATE TABLE Tempdb.dbo.BackgroundProcess
(
    CommandRun VARCHAR(100),
    TimeExecuted DATETIME
)

DECLARE @TimeMarker DATETIME

DECLARE @cmdQueue TABLE
(
    cmd VARCHAR(5000) NOT NULL
)

DECLARE @Runcommand TABLE
(
    cmd VARCHAR(5000) NOT NULL
)

SET @TimeMarker = (SELECT start_execution_date FROM msdb.dbo.sysjobactivity sja INNER JOIN msdb.dbo.sysjobs sj on sja.job_id = sj.job_id WHERE sj.enabled = 1 AND start_execution_date IS NOT NULL AND name = 'BackgroundProcess')

WHILE 1=1
BEGIN
        INSERT @cmdQueue
            SELECT 'EXEC ' + 'TempDB.dbo.TestSP' + ' ' + name + ', ' + @@SERVERNAME FROM msdb.dbo.sysjobactivity sja INNER JOIN msdb.dbo.sysjobs sj on sja.job_id = sj.job_id WHERE sj.enabled = 1 AND start_execution_date IS NOT NULL AND stop_execution_date >= @TimeMarker
        SET @whilecounter = (SELECT COUNT(*) FROM @cmdQueue)
        SET @i = 1
        WHILE @i <= @whilecounter
        BEGIN
            DELETE TOP(1) FROM @cmdQueue
            OUTPUT DELETED.cmd INTO @RunCommand
            INSERT Tempdb.dbo.BackgroundProcess
                SELECT cmd, GETDATE() FROM @RunCommand
            SET @StatementToRun = (SELECT TOP(1) cmd FROM @RunCommand)
            EXEC (@StatementToRun)
            DELETE FROM @RunCommand
            SET @i = @i + 1
        END
SET @TimeMarker = GETDATE()
WAITFOR DELAY '000:0:30.000' --Configure this to be the proper frequency for your situation. Ensure it is runs often enough to catch all executions of other jobs.
END
*/

-- 4. Configure the 'StartBackgroundProcess' stored procedure to be called whenever SQL Server starts.

USE master
GO

EXEC sp_procoption 'master.dbo.StartBackgroundProcess', 'startup', 'true'
GO


DB performance vs replication I am working on a db optimization (planing for future project growth) and need some help.

Currently, every table is using uniqueidentifier as PK (clustered index) and we have high index fragmentation (99%). For the new tables we started using bigint as pk but I don't want a nightmare when bidirectional replication phase comes.

I did my research and uniqueidentifier is not a huge problem (except memory), problem is clusters index on that column (http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/).

Can we do this to solve our fragmentation problem and any replication nightmare:

Add ROWGUIDCOL to PK column
Add another identity column in every table and move that cluster
index (not pk) to that column

Would this new identity column cause the same replication problems as if that was bigint PK?
I know SharePoint db primary keys are GUIDs but I am not happy with its performances and probably security has some part in that decision.
We could reduce fragmentation by using seq. GUIDs but we can't create it on application side or return it with SCOPE_IDENTITY(). OUTPUT inserted.ID hack would be time consuming because we need to rewrite complete application DAL.

Finally, is there any valid solution for this problem? Can we use bigint without any replication problem.

In a previous role at a different Organisation to where I am now employed, we faced a similar decision whereby the GUID issue (as pointed out in the link) was a factor for us (both Width and poor choice regarding Index fragmentation) - but we needed to move to Transactional Replication (Peer-to-Peer) to allow our Sites to be Geographically separated.

In our Dev environment we worked through converting most of the GUID's to Identity columns, set up the replication to match the intended Production system and ensured that the Identity columns had separate ranges per node (as per http://technet.microsoft.com/en-us/library/ms146907(v=sql.105).aspx - this also gives suggestions for Automatic Identity Range Management, which may work for you).

Sounds good so far? NO...

The Identity columns were a nightmare to manage, through our Dev testing and working on automating the separation of the ranges, we gave up on them (never made it into production) - in my opinion, it is more trouble than it's worth - we settled back on the GUID's that were already in the database and settled with the known issues of a bit of wasted memory and Index Fragmentation.

For us, this wasn't that detrimental a solution - as almost all of the usage are Write operations with fairly low reads - obviously we have a maintenance plan that tries to take care of the Fragmentation as best it can (there are some Update operations - so Read and then Write but the delay is within threshold).

My 2 pence are - avoid Identity Columns (unless you already have a great plan for them - or you can add a static unit (i.e. Server 1 always has a preceding letter A1, A2 etc. Server 2 = B1, B2, etc)) when replicating and test the life out of it - assuming you can set up a replica Dev system or whatever. Of course it depends on the business whether you can put up with the poor read access that GUIDs provide because of their Index issues!

Good Luck.


Transaction in SQL Server Consider a scenario, when 2 Stored Procedure called inside a Stored procedure like follows.
Note:  All 3 SP’s are implemented with TRANSACTION.

CREATE PROC [dbo].[SP3]
AS
BEGIN
      BEGIN TRAN
EXEC SP1
EXEC SP2

      COMMIT TRAN
      END

GO

What happens when an error happened in SP2.

a.  Commits SP1 alone and raise error for SP2

b.  Rollback SP1 and SP2

c.  Syntax Error

d.  Commits SP1 and SP2 with Error Message

Please give the answer.

Thanks.

I assume you are working on SQL Server, given question tags and T-SQL notation.

Nesting transactions in stored procedures as you are doing can turn into a mess. Basically, when you start a nested transaction, SQL Server does nothing but increment the transaction count and doesn't really start a "new" transaction. Every time you commit a transaction, you decrease the transaction count.

Usually I try to avoid using nested transactions and instead I check for existing transactions to "reuse".  Here you can find the stored procedure code template I use to handle nested procedure calls without nesting transactions.

Other than that, you have to handle commits and rollbacks with savepoints to obtain real transaction isolation from the "parent" transaction. If you don't do that, you get the default behavior, which is commit for the innermost transaction and rollback for the whole transaction stack.

Also, take into account that transactions need proper error handling and you have to make sure you get a rollback whenever you encounter an error. TRY/CATCH blocks can handle that for you, as I'm showing in the template I linked. Another thing worth noting is that even SET options can affect the code flow, so make sure you have XACT_ABORT set accordingly.


SQL extended event captured only the estimated execution plan but no actual execution plan My SQL instance version is Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288)
I am troubleshooting the application time-out and the time-out SELECT query is quite simple. It only takes less than 1 second when I manually run it...
I used extended events query_pre_execution_showplan & query_post_execution_showplan to capture the estimated/actual execution plan. It shows only the estimated execution plan but without the actual execution plan.
Looks like SQL instance just generated the estimated execution plan...
Any thoughts?
Many thanks,

I imagine that you aren't seeing the "post execution" event because the client canceled the execution.


mysql 5.6 is Not referring Indexes for deterministic function DATEDIFF I do not understand why MySQL is not using the index for the below query.

I have a table named users and its create table is

this table contains data.

Now I am running SQL select statements and it's checking its query execution plan.

for the statement

desc select *
     from   users
     where  users.created_at between '2019-05-04' and '2019-05-06';

output is

but which I am using DateDiff function query is not using indexes.

desc select *
     from   users
     where  DATEDIFF(users.created_at,'2019-05-05 23:11:56.547') =  0;

and output for this is

according to the MySQL documentation page "A function is nondeterministic if, given fixed values for its arguments, it can return different results for different invocations".

According to doc, DateDiff is a deterministic function and I am not understanding why MySQL is not taking indexes for the query.

Reason for indexes not being used is table column is an input part of the function.
Current SQL Query

select *
 from   users
 where  DATEDIFF(users.created_at,'2019-05-05 23:11:56.547') =  0;

Indexes will be used if the query is in below way

select *
 from   users
 where  users.created_at =  DATE_ADD('2019-05-05 23:11:56.547', INTERVAL 0 DAY);

since above refers to a point of time. To check day we can have to use range timestamp start of today to start off tomorrow,

select *
 from   users
 where  users.created_at between DATE_ADD('2019-05-05 ', INTERVAL 0 DAY)
and DATE_ADD('2019-05-05 ', INTERVAL 1 DAY);


Rebuild index in table with many values I'm junior DBA ,have table with 11 million values ,one of the indexes is like :

CREATE NONCLUSTERED INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl]
(
    [my_id] DESC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

For rebuild this index need only

ALTER INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl]
REBUILD;

or with big tables need to prepare something.And also one question in this table I have some columns with asc index and some with desc , can be any problems after rebuild all indexes in this table? (ex.: slow select/insert). Is good idea to rebuild this index?

SQL Server Version : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64>

First thing you should do is Apply SQL Server 2008 R2 SP3 ASAP. This is because SQL Server 2008 R2 RTM version is not supported at all by Microsoft. There are lots of fixes which have been included in this Service pack release and that would surely benefit SQL Server query performance.

  You asked, So rebuild all indexes is good idea ?

NO IT IS NOT. One should only rebuild index which is fragmented above certain value. Probably widely used value is, if index fragmentation is >30 you should go for rebuild and it is in between 10 and 30 you should go for reorganize. Please note you have standard edition so index rebuild would not be online. If you rebuild index blindly it would cause more downtime and produce more logs and thus more overhead on system. If you have narrow maintenance window you need to be highly selective with index rebuild and all this can be taken care if you go for Ola Hallengren Index rebuild solution

Other thing to note is if page_count for index, page_count can be seen from  be seen from sys.dm_db_index_physical_stats , is less than 1000 you don't need to rebuild such indexes. The reason is since page count is less pages allocated to index would be from mixed extent and these mixed pages can be lying anywhere and hence producing Logical fragmentation even after rebuild. But rest assured such small page count indexes would not affect query performance at all. You can read more about why such indexes still remain fragmented even after rebuil here

There are various options you can choose with index rebuild you can see more details here. Lot depends on your environment and configuration

You can read more about query plans from This Article. You should focus more on actual query plan than estimated query plan. Actual would tell you what exactly SQL Server is doing and what resources it is using.

You can also try SQL Sentry Plan explorer tool to get better insight into SQL Server execution plan


Giving a name to a dynamic column I have written a query to fetch a time internal:

select *, (Time + interval '2012-12-18 13:36:47' minute)  from chatnotiservice;

Is it possible to name the (Time + interval '2012-12-18 13:36:47' minute) column?

Use AS. For example:

mysql> select 'data' as mycolumnname;
+--------------+
| mycolumnname |
+--------------+
| data         |
+--------------+
1 row in set (0.00 sec)

mysql>

If you actually want to name the column after the date operation you're performing (your question isn't clear), you can do it by enclosing it in backticks. For example:

mysql> select 'data' as `Time + interval '2012-12-18 13:36:47' minute` ;
+----------------------------------------------+
| Time + interval '2012-12-18 13:36:47' minute |
+----------------------------------------------+
| data                                         |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql>

I suspect what you mean is the following:

select *,(Time + interval '2012-12-18 13:36:47' minute) AS mytime from chatnotiservice;


"Cut" records from one table into another in one oracle sql statement I have a very big table T1 and i would like to copy every 15000 records to T2.
After that I want to delete these same records.
I want a fast and efficient way.
I tried this but it wouldn't work :

merge into T2 T
using (select col1,col2 from T1 where rownum<15000) m
on (1=1)
when matched then
insert values (m.col1,m.col2)
delete where 1=1;

It's giving me a syntax error. Any ideas??
Please help.
Thanks

Try (works on sql server)

INSERT T2 (
      col1,col2
) SELECT col1,col2
      FROM    (
           DELETE T1
           OUTPUT
                   DELETED.col1,
                   DELETED.col2
           WHERE rownum<15000
      ) AS RowsToMove;


SQL Server Named Instance is not listening on Port 1434 We just installed SQL Server 2016 on a Windows 2016 server.  I am having issues connecting to the Named Instance of that server. I have noticed that the server isn't listening on port 1434 either.  Normally we would see it in log as:

Server is listening on [XX.XX.XX.XX <ipv4> 1434>

But the only thing I see in the logs are:

Server is listening on [ 127.0.0.1 <ipv4> 63229].
Server is listening on [ ::1 <ipv6> 63229].

I can seem to connect to this named instance other than on the local server.  We have apps running that will hit this Named Instance but it seems like we are having issues connecting.

I also did a netstat -ao | findstr /I "processID" and I couldn't see the 1434 ports.  I had configured my TCP/IP to use the TCP Port 1434 for my IP but it's still not working.

Any ideas why it's not listening on 1434?

I checked in Configuration Manager and everything looked fine.  TCP/IP was enabled and SQL service restarted.  It doesn't seem to be listening on that port when it starts up.

The SQL Browser service is turned on. The TCP Port 1434 is assigned to the IP but the TCP Dynamic Ports and TCP Port is blank in the IPALL.

Maybe the question is how is the named instance supposed to be set up?  This was to replace an old server that was running SQL 2012 and that server had it set this way and it's been running fine.  So I copied the same setting and we can't seem to connect to the named instance.

Some additional investigation is needed here. First, it is possible that you are filtering out too much of the netstat output in your findstr filter. Instead of "processID", check for the port number using ":1434". Also, using the -q switch instead of -a might be better as it will show bound, nonlistening ports. For example:

netstat -qof | findstr ":1434"

OR, if you start the command prompt as an administrator, you can add the -b switch to print out the name of program associated with each process (saves the step of needing to map the Process ID to the program). When doing this, the output of each entry is on 2 lines, so you won't see the additional info if you use findstr as those lines will never have the matching string. Instead, just pipe to more as follows:

netstat -qofb | more

Now, using netstat is handy to research to see if a port is in use, but for this issue it is more direct to see what SQL Server thinks is going on, and fortunately there are some views that have the info so you don't need to scan / parse the error log. You should check the following:

sys.tcp_endpoints
sys.dm_tcp_listener_states

So just execute the following:

SELECT * FROM sys.tcp_endpoints;
SELECT * FROM sys.dm_tcp_listener_states;

If the first view, sys.tcp_endpoints, shows that "Dedicated Admin Connection" has a state_desc other than "STARTED", then there is an issue.

Now, in the second view, sys.dm_tcp_listener_states, if you do have "::1" (IP v 6) and/or "127.0.0.1" (IP v 4) for ip_address then it is only listening locally (i.e. remote administrative access is OFF). If you instead see "::" (IP v 6) and/or "0.0.0.0" (IP v 4), then remote admin access is ON.

By default remote administrative access is OFF. You can enable it by executing the following (this is not an "advanced" option):

EXEC sp_configure N'remote admin connections', 1; RECONFIGURE;


SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once Table A (users)

id     | name   | ip
52433    Yashas   1.1.1.1
512      Admin    5.6.7.8

Table B (admin_record)

id    | admin | time    | date    | reason | action
52433   512     2312352   1213213   Test     WARN

id is FOREIGN key

Table B keeps a log of all actions an administrator takes on his users. And the table A stores the account information of the users & administrators.

I am trying to display a list of actions taken by a administrators on a user.

I want to write a query which gets me all the rows of table B where id = fixed number.

Here is what I want to display:

"Administrator1 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.
"Administrator2 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.
"Administrator3 [Name]" took action on "User Name" for "[Note]" on XX/XX/XXXX.

All I have is the user's id using which I need to obtain all the entries in the log along with the administrator names. I need to obtain the admin's name, users name and all the contents of table B related to a particular user in one single query. If possible, I want the to change the field name(not literally change, but want the SQL server to send the data with new field names to separate admin name & user name) of the admin's name to admin_name and user's name as . I mean something like this

SELECT users.name AS admin_name WHERE users.id = %i
AND SELECT users.name AS user_name WHERE users.id = %i

(the query isn't correct,it us just an example to show what I am trying)

Progress so far:

 SELECT player.name, player.ip, admin.name, record.date, record.time, record.action, record.reason
FROM admin_record AS record
JOIN users AS player ON player.id = record.id
JOIN users AS admin ON admin.id = record.admin
WHERE player.id=%i

where admin_record is table B & users is table A.

The question is all over the place but I think this is what you are looking for

select a.name, u.name, b.note, b.date
  from table_B as B
  join table_A as u
        on u.ID = B.ID
  join table_A as a
        on a.ID = B.admin_id
 where B.ID = @userID

you can create a line with
select a.name + " took action on " + u.name  ...


Database created in sqlcmd but missing in SQL Server Management Studio I created a database named testbase in sqlcmd in SERVER\MSSQLSERVER. However, I cannot access SERVER\MSSQLSERVER.

I also could not find testbase inside the server connection using Microsoft SQL Server Management Studio.

Appreciate any help / tips / hints.

Actually, I went to the wrong connection. It should be SERVER\MSQLSERVER rather than SERVER\MSSQLSERVER.


sp_send_dbmail with multiple reply_to addresses I got this error when I specified multiple email addresses in reply_to

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2014-08-19T12:54:35). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).
  )

here is the sql snippets

EXEC msdb..sp_send_dbmail
@profile_name = 'Company',
@recipients = 'hi@sg.com',
@subject = 'PO delivery dates confirmation',
@body = 'test',
@body_format = 'HTML',
@importance = 'Normal',
@reply_to = '2@sg.com;3@sg.com'

I can't use distribution group because the reply_to is dynamic base on the query. what would be the work around for this?

@reply_to can only accept 1 email address. Its by design - see this connect.

The document reflects that as well.

  [ @reply_to= ] 'reply_to'
  Is the value of the 'reply to address' of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile. This parameter is of type varchar(MAX). SMTP secuirty settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.


Latin1_General_BIN performance impact when changing the database default collation I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the database? The database already exists with tables in it.

Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require.

Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the best performance (especially the pure code-point BIN2 versions) most implementations do not use them.

Next in raw performance terms (but only for non-Unicode strings) are the backward-compatibility SQL collations. When working with Unicode data, these collations use a Windows collation instead, with the same performance characteristics. There are subtle traps here, so you need to have good reasons to choose a SQL collation these days (unless working on a US system, where it is still the default).

Windows collations are the slowest, in general, because of the complex Unicode comparison and sorting rules. Nevertheless, these offer complete compatibility with Windows in SQL Server, and are regularly maintained to keep up with changes in the Unicode standard. For modern use that includes Unicode data, a Windows collation is generally recommended.

TL;DR

If all you want is case-sensitive comparison and sorting semantics, you should choose the _CS_ (for Case Sensitive) variation of whichever base collation provides the expected behaviour for your users' language and culture. For example, both these are case-sensitive collations:

-- Latin1-General, case-sensitive, accent-sensitive
Latin1_General_CS_AS

-- Latin1-General, case-sensitive, accent-sensitive for Unicode Data,
-- SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data
SQL_Latin1_General_CP1_CS_AS

You can see these definitions using sys.fn_helpcollations

Examples

Four tables that are exactly the same except for the collation; one binary, one case-sensitive, one case-insensitive, and one SQL case-sensitive:

CREATE TABLE #Example_BIN
(
    string nvarchar(50)
        COLLATE Latin1_General_BIN
        NOT NULL
);

CREATE TABLE #Example_CS
(
    string nvarchar(50)
        COLLATE Latin1_General_CS_AI
        NOT NULL
);

CREATE TABLE #Example_CI
(
    string nvarchar(50)
        COLLATE Latin1_General_CI_AI
        NOT NULL
);

CREATE TABLE #Example_SQL
(
    string varchar(50) -- Note varchar
        COLLATE SQL_Latin1_General_CP1_CS_AS
        NOT NULL
);

Same sample data for each table:

INSERT #Example_BIN
    (string)
VALUES
    (N'A'),
    (N'a'),
    (N'B'),
    (N'b'),
    (N'C'),
    (N'c');

INSERT #Example_CS
SELECT EB.string
FROM #Example_BIN AS EB;

INSERT #Example_CI
SELECT EB.string
FROM #Example_BIN AS EB;

INSERT #Example_SQL
SELECT EB.string
FROM #Example_BIN AS EB;

Now we want to find strings greater than 'a':

SELECT EB.string AS BIN
FROM #Example_BIN AS EB
WHERE EB.string > N'a'
ORDER BY EB.string;

SELECT EC.string AS CS
FROM #Example_CS AS EC
WHERE EC.string > N'a'
ORDER BY EC.string;

SELECT EC2.string AS CI
FROM #Example_CI AS EC2
WHERE EC2.string > N'a'
ORDER BY EC2.string;

SELECT ES.string AS SQL
FROM #Example_SQL AS ES
WHERE ES.string > 'a' -- not Unicode
ORDER BY ES.string;

Results:

╔═════╗
║ BIN ║
╠═════╣
║ b   ║
║ c   ║
╚═════╝

╔════╗
║ CS ║
╠════╣
║ A  ║
║ b  ║
║ B  ║
║ c  ║
║ C  ║
╚════╝

╔════╗
║ CI ║
╠════╣
║ B  ║
║ b  ║
║ C  ║
║ c  ║
╚════╝

╔═════╗
║ SQL ║
╠═════╣
║ B   ║
║ b   ║
║ C   ║
║ c   ║
╚═════╝

Finally...

Note though, if we use a Unicode literal with the SQL collation, the implicit conversion rules result in a Windows collation comparison:

SELECT ES.string AS SQL
FROM #Example_SQL AS ES
WHERE ES.string > N'a'
ORDER BY ES.string;

...and the SQL collation results change:

╔═════╗
║ SQL ║
╠═════╣
║ A   ║
║ B   ║
║ b   ║
║ C   ║
║ c   ║
╚═════╝


Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres? In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000).

Offset of -07:52:58?

Some example code. Note that I mixed use of TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE, so read carefully.

SET TIME ZONE 'America/Los_Angeles' ;

SELECT (TIMESTAMP WITH TIME ZONE '2015-01-01 00:00:00.0',
        TIMESTAMP WITH TIME ZONE '0001-01-01 00:00:00.0Z',
        TIMESTAMP WITHOUT TIME ZONE '0001-01-01 00:00:00.0Z') ;

("2015-01-01 00:00:00-08","0001-12-31 16:07:02-07:52:58 BC","0001-01-01 00:00:00")

I am surprised by that second value: 0001-12-31 16:07:02-07:52:58 BC. I understand that we must go backwards eight hours as America/Los_Angeles is eight hours behind UTC with an offset of -08:00. But instead of -08:00 the offset is -07:52:58. Why?

No Problem Under UTC

No such issue when entering data under UTC.

SET TIME ZONE 'UTC' ;

SELECT (TIMESTAMP WITH TIME ZONE '2015-01-01 00:00:00.0',
        TIMESTAMP WITH TIME ZONE '0001-01-01 00:00:00.0Z',
        TIMESTAMP WITHOUT TIME ZONE '0001-01-01 00:00:00.0Z');

("2015-01-01 00:00:00+00","0001-01-01 00:00:00+00","0001-01-01 00:00:00")

No Year Zero

By the way, the date portion seems to be correct. It seems there is no year 0000, that being the pivot point between the “BC” and “AD” eras. Take the first moment of year 0001, subtract an hour, and you get the year 0001 BC – so no year zero.

SET TIME ZONE 'UTC' ;

INSERT INTO moment_  -- TIMESTAMP WITH TIME ZONE.
VALUES ( TIMESTAMP '0001-01-01 00:00:00.0Z' - INTERVAL '1 hour' ) ;

SET TIME ZONE 'UTC' ;

TABLE moment_ ;

The result is the year 0001 BC, so we jump from 0001 to 0001 BC; no year zero 0000.

"0001-12-31 23:00:00+00 BC"

On 18th of November, 1883 at 12:00 (new time), standard time was adopted by the American railroads.
This means that before that time, Los Angeles used actual local time, based on mean solar time. After that, it was moved to its local time zone, which, being an integral offset of hours from the Greenwich Mean Time, was slightly different from the previous time.
Want to know more?

Download the tzdata Timezone Database from IANA: Time zones.

Inside, you'll find the definitions of the (many) timezones, which have lots of variations over time, along with lots of comments detailing what changes where made and when. It's entertaining reading!

Wikipedia has also some interesting facts, in the Wikipedia: Time zone page, regarding the 1883, November 18 change:

Railway time
...
Timekeeping on the American railroads in the mid-19th century was somewhat confused. Each railroad used its own standard time, usually based on the local time of its headquarters or most important terminus, and the railroad's train schedules were published using its own time. Some junctions served by several railroads had a clock for each railroad, each showing a different time.
... Dowd's system was never accepted by American railroads. Instead, U.S. and Canadian railroads implemented a version proposed by William F. Allen, the editor of the Traveler's Official Railway Guide. The borders of its time zones ran through railroad stations, often in major cities. For example, the border between its Eastern and Central time zones ran through Detroit, Buffalo, Pittsburgh, Atlanta, and Charleston. It was inaugurated on Sunday, November 18, 1883, also called "The Day of Two Noons", when each railroad station clock was reset as standard-time noon was reached within each time zone. The zones were named Intercolonial, Eastern, Central, Mountain, and Pacific. ...

Also note that this is not specific to Postgresql. This is valid for any software or operating system that uses the tzdata database (though of course many will be limited to dates either post 1970 or post 1901, so 1883 is beyond reach, but there are many, many other adjustments all over the place at different times).


Selecting a View consumes 4GB memory but executing SQL statement of View only 70 MB I am facing high memory consumption on my database server. During diagnosis, I found a view consuming 4 GB memory. But when I executed it's statement (select statement of view) it's taking only 70 MB.

What can be the reason?

You can see screenshot here

Here is Estimated execution plan of the view.Execution Plan
and here is actual one Actual Eplan

I am using SQL Server 2008 R2 E2. And I am using this query to know memory utilization and other things.

    select
                db_name(sp.dbid) [DB Name],sp.spid,sp.blocked,er.wait_type,er.wait_time,er.wait_resource,er.total_elapsed_time,st.text,qp.query_plan
                ,ec.net_packet_size,ec.client_net_address,es.host_name,es.program_name,es.client_interface_name
                ,es.status,es.cpu_time,qmg.requested_memory_kb,qmg.granted_memory_kb,es.total_scheduled_time,es.total_elapsed_time
                ,es.reads,es.writes,es.logical_reads

    from
                    sys.dm_exec_requests er
    inner join      master.dbo.sysprocesses sp
    on              er.session_id=sp.spid
    inner join      sys.dm_exec_connections ec
    on              er.session_id=ec.session_id
    inner join      sys.dm_exec_sessions es
    on              ec.session_id=es.session_id
    inner join      sys.dm_exec_query_memory_grants qmg
    on er.session_id=qmg.session_id
    cross apply     (select text from sys.dm_exec_sql_text(er.sql_handle)) st
    cross apply     (select * from sys.dm_exec_query_plan(er.plan_handle)) qp
    Order by cpu_time desc

"Select * from View" consumes 4GB memory but when I execute SQL statement of view. It takes 70 MB only. I am measuring requested memory and granted memory. Using above script.

The 'actual' execution plan explains the issue - the estimated number of rows coming into the SORT is 5.3 million, so Sql Server allocates a large block of memory to hold it.  However, the 'actual' number of rows is much less.

A sort operation needs to spool all the data and it estimates that there is 3.7GB to spool. Additionally the amount of memory needed to actually sort is more than the actual size of data itself.


Why is XML taking up more storage than VARCHAR(MAX)? We have large tables storing XML data as varchar(MAX). The data is for reference/historical purposes, it's not queried. Based on what I've read, storing as XML datatype instead of VARCHAR(MAX) should result in space savings, but my tests show otherwise.  See below, where the size of t1_XML is smaller than t1_NVARCHARMAX, but larger than t1_VARCHARMAX.

set nocount on;

drop table t1_XML;
drop table t1_VARCHARMAX;
drop table t1_NVARCHARMAX;

create table t1_XML(col1 int identity primary key, col2 XML);
create table t1_VARCHARMAX(col1 int identity primary key, col2 varchar(max));
create table t1_NVARCHARMAX(col1 int identity primary key, col2 nvarchar(max));

go

declare @xml XML = '<root><element1>test</element1><element2>test</element2><element3>test</element3><element4>test</element4><element5>test</element5></root>'
    , @x int = 1;

while @x <= 10000
begin
    begin tran

    insert into dbo.t1_XML (col2) values (@xml);
    insert into dbo.t1_VARCHARMAX (col2) values (cast(@xml as varchar(max)));
    insert into dbo.t1_NVARCHARMAX (col2) values (cast(@xml as varchar(max)));

    commit tran

    set @x += 1;
end

exec sp_spaceused 'dbo.t1_XML';
exec sp_spaceused 'dbo.t1_VARCHARMAX';
exec sp_spaceused 'dbo.t1_NVARCHARMAX';

There are two things to know about the XML datatype that together explain what you are experiencing:

As noted in @EvanCarroll's answer, the XML datatype is optimized. Meaning, rather than repeat element and attribute names (which are typically repeated quite a bit and are a large part of why so many people, sometimes rightfully-so, complain about XML documents being so bulky), a dictionary / lookup list is created to store each unique name once, given a numeric ID, and that ID is used to populate the structure of the document. This is why the XML datatype is quite often a better way to store XML documents.
Additionally, the XML datatype uses UTF-16 (Little Endian) to store string values (both element and attribute names as well as any actual string content). This datatype does not use compression, so strings are essentially 2 or 4 bytes per character, with most characters being the 2-byte variety.

Looking at the particular test XML document you are using, and the VARCHAR datatype (1 to 2 bytes per character, most often the 1-byte variety), we can now explain what you are seeing as being a result of:

Each of your elements (root, element1, etc) are used only once, so the only savings of placing the names into the lookup list is to cut the size in exactly half. But, the XML type uses UTF-16 so the size of each string is twice as much, cancelling out the savings of moving the element names into the lookup list. At this point, if only looking at the document structure (i.e. element names) then there is should effectively be no difference between the XML type and the VARCHAR version.
But, the string content in each element (i.e. test) takes up twice the number of bytes: 8 bytes in XML as opposed to 4 bytes in VARCHAR. Given that there are 5 instances of "test" per each row, that is 20 extra bytes per row for the XML type. At 10k rows, that is 200,000 extra bytes of the 600,000 byte difference. The rest is internal overhead of the XML type and the additional page overhead of the additional number of datapages needed to store the same number of rows due to each row being slightly larger.

To better illustrate this behavior, consider the following two variations of XML data: the first being the exact same XML as in the question, and the second being almost the same, but with all elements being the same name. In the second version, all element names are "element1" so that they are the same length as each element in the original version. This is results in the VARCHAR data length being the same in both cases. But the element names being the same in the second version allow the internal optimizations to be more noticeable.

-- Original XML (unique element names -- "element1", "element2", ... "elementN"):
DECLARE @xml XML =  '<root><element1>test</element1><element2>test</element2>
<element3>test</element3><element4>test</element4><element5>test</element5></root>';
SELECT DATALENGTH(@xml) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml)) AS [VarcharBytes];

-- More "typical" XML (repeated element names -- all "element1"):
DECLARE @xml2 XML = '<root><element1>test</element1><element1>test</element1>
<element1>test</element1><element1>test</element1><element1>test</element1></root>';
SELECT DATALENGTH(@xml2) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml2)) AS [VarcharBytes];

Results:

ElementNames    XmlBytes    VarcharBytes
------------    --------    ------------
Unique          197         138
Non-Unique      109         138


Is there a generic framework/design pattern/library for real time replication of MySQL database onto other servers? Summary

I need to backup in real time updates/inserts to a MySQL database. Cron/batch jobs appear to be unsuitable for this particular requirement due to the time interval meaning that the data would only be as recent as the last cron job date/time.

Background

I have three LAMP (Linux, Apache, MySQL, PHP/Perl) servers on three locations each issuing small amounts of data regularly requested by client applications on other machines. Each server logs the data that it has issued. Each server holds configuration data that determines the kind of data issued to the clients. Both the logs and the configuration data are to be stored in a mysql database on the server.

Considered solution - servers backup each other in real time, cron jobs not suitable it seems

I am considering backup strategies for each server. Cron job based backups at certain intervals are unsuitable, owing to the necessity for the logs to be current in the backup, containing the very last entry before the server failed. For example, a cron job say every 4 hours would have data that is 4 hours old and not data since then.

So the strategy I am considering is to use the servers to backup themselves, sending their log data to each other, each log entry identifying which server it was from, as well as date stamp. This strategy seems bespoke and specific to this application because the code and data structures are specific. So I wondered:

Is there a design pattern or library or even a framework that does real time MySQL replication?

Answered questions I have already considered on Stack Overflow are:

https://stackoverflow.com/questions/7182791/daemon-software-to-update-mysql-database-in-background (similar question, but their domain - bidding needs a different solution from mine, answers here appear to be based around bidding amount, my domain isn't so much about content of the database entries, just that the latest needs to be backed up)
https://stackoverflow.com/questions/6471879/is-there-a-way-to-listen-for-a-database-event-and-update-a-page-in-real-time (more about keeping the UI up to date to reflect the database (nice question, will use it sometime) but mine is about backup)
https://stackoverflow.com/questions/5221164/need-advice-on-developing-real-time-system-using-php-mysql (nice broad question that would cover my question but theirs is again a slightly different domain concerned with technologies I am not using: OpenID, JQuery, PHP frameworks such as CodeIgnitor)
https://stackoverflow.com/questions/4200515/how-do-i-do-realtime-database-polling-in-mysql-php (for my problem, probably don't want to poll for changes as this has similar limitations to cron in that data is only as up to date as last poll)
Also looked at MySQL documentation, but could not find anything for real time replication, only batch based solutions. (Willing to stand corrected though if others can suggest to me otherwise)

Perhaps I am misreading this, but I am having difficulty understanding how your requirement is any different from built-in MySQL replication.

With MySQL replication, the master database writes changes to a binary log file.  The slaves connect to the master, read the binary logs, write them to their local relay log, and play the statements back.

Please understand that realtime copy and backup are not the same thing.  A real-time slave can help you recover from certain types of problems, but cannot resolve data-corruption issues (oops, I dropped that table!).  For that, you need point-in-time backups.  With a backup and a copy of the binary logs, you can recover from almost any type of failure by replaying the binary logs up to the point where the problem occurred.


Clustering a heap by date in stead of unique composite key I've been working on improving the performance of a large reporting database.
This database is 2TB in size, and one of the larger tables is a heap with three non-clustered indexes on it.

The non-clustered indexes each deal with a combination of Case_ID's with relevant data.
However the case id's are not at all unique, as each case entry is stored in the same heap with the Case_ID.
Inquiries at the business side of things have led me to the fact that a combination of Case_ID, Line_Number and Document_ID is unique.

However, nearly all of the queries are claused by dates (using a datetime column named DATE).
Usually by WHERE DATE = 'xxxx-xx-xx 00:00:00.000, or WHERE DATE > 'xxxx-xx-xx 00:00:00.000'. And the Line_Number and Document_ID values are in most cases not even included in the reports.

As such, I created a clustered index on the heap (on DEV), keyed on the datetime column.
Performance of the reports has increased by a decent amount, reducing query times from 5 minutes to 3 minutes for most of my test reports, and I haven't seen any negative impact on the reports not claused by date.

I am however concerned that I'm looking too far as I understand that non unique clustered indexes are rarely the solution. Is what I'm doing a valid approach? Or should I simply index on the unique composite key, and create a covering index on the datetime column?

Non-clustered indexes use the clustered index key value as a pointer back to the data.  If you create a compound clustered index, you are making your non-clustered indexes that much larger.

When choosing a clustered index, especially on tables with hundreds of millions of rows, try to choose a single narrow column if possible.  i.e. choose an integer over a varchar field.

The Datetime field is 8 bytes with is relatively narrow.  If your data is inserted by date in ascending order (e.g. as time progresses) the records will be inserted at the tail end of the table.  Pages and extents will be added as necessary.

If your data data is spread out all over the place however, you will end up with page splits as new records are shoved in between existing records.  Make sure your incoming date data is accurate.

Be warned that SQL will add an internal 4 byte "uniquifier" to your clustered index if it is not unique.

So, go for the single column clustered index.  Queries that use the datetime column will benefit greatly, and the other indexes will not suffer.


Postgres RDS: is there a way to use LDAP authentication? We have a client interested in using LDAP (specifically Active Directory) for user authentication on a PostgreSQL AWS RDS (Aurora, specifically) database.

Anybody doing this successfully?

As much I am aware this may not be possible.
Currently, AWS has IAM authentication for postgres which in my humble opinion has a bit further to go before it can be adopted. ie once the IAM user is mapped to the DB Role any once can create a token on behalf on a DB role.


SQL Server Management Studio Doesn't do "Full" Backup Very new to SQL Server. I'm trying to bring a backup from one our clients Windows 2012 Server Install to our Linux Install. We have the site working perfectly on Linux, but can't seem to do an arbitrary DB dump to get new data. They are running SQL Server 2012 Express (I'm assuming because it didn't have "Maintenance Plans" under the Management tab of the object Explorer)

Or in transact SQL:

BACKUP DATABASE [YourDB] TO  [YourDB_backup] WITH NOFORMAT, NOINIT,  NAME = N'YourDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

When I pulled this down to my Linux server it didn't have updated information (that was created 5-6 hours earlier). Why is this?

To ensure I wasn't making a mistake I deleted the entried DB on my Linux machine with:

USE master;
GO
DROP DATABASE YourDB;
GO

And then uploading it again as dictated here:

RESTORE DATABASE YourDB
FROM DISK = '/var/opt/mssql/backup/YourDB.bak'
WITH MOVE 'YourDB' TO '/var/opt/mssql/data/YourDB.mdf',
MOVE 'YourDB_Log' TO '/var/opt/mssql/data/YourDB_Log.ldf'
GO

Interestingly, their backup task that runs at midnight does have the new information, but I can't see where that task occurs to compare the script.

The issue is the defualt backup value of NOINIT
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

Media Set Options
These options operate on the media set as a whole.
{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

Go to the options tab and change the selection to overwrite, or change the SQL to INIT

Alternatively you can restore from the NOINIT backup using the directions at this post SQL Recover from .bak file with NOINIT


Deciding the best way to partition (Postgresql) I have been trying to research this for quite a while now and I am having a hard time trying to figure it out. I am out of ideas other then setting up an extra test database and running through all of the possibilities to see how they perform. Hopefully someone can pass on a bit of knowledge to prevent having to do that.

I have a database (Postgresql 9.2 with PostGIS) with a large amount of spatial data in it. Typically I look at it by location and date, however I do have to be able to search across all of the fields pretty regularly. The amount of data we receive in a day varies and continues to grow over time, so we cant guarantee the size of a partition on disc or the number of rows in one if we partition by date. I have read that setting a limit on the number of rows in a partition helps, because you can basically make a maximum size for each partition and allow for faster reads, but I do not have a sequential id field to partition off of either.

I have found functions that will allow me to partition by date, or possibly partition off of a calculated serial field (not sure on this one yet). My main question for now is: how should I decide which is the best way to do it? Is there an inherent benefit to one way or the other? Is there even a way to tell which will be better without implementing both and testing them?

It isn't even clear if you should be partitioning at all.

PostgreSQL's table partitioning is a bit primitive and comes with some limitations in enforcing referential integrity, etc. If your query pattern doesn't overwhelmingly favour filters on a particular field where you can benefit from constraint exclusion it might not help you much.

Partitioning can ease certain maintenance tasks, in particular bulk drops of data that rotates through by age. Dropping the whole of last month's data can be easier than doing a big slow DELETE with follow-up VACUUMing. This alone is rarely worth partitioning for, though.

You really do need to test for your workload and query pattern. Use the data in pg_stat_user_indexes and pg_stat_user_tables on your system as it's currently running to guide you about query patterns. I also recommend installing the pg_stat_statements extension, which will collect more info about query patterns.


Subquery returned more than 1 value In my database I have two tables: projects and comp_types. When a new project is created a trigger is hit which inserts a concatenated value in a column in the projects table.

A fully working SQL Fiddle.
Please run this and you will see, it works.

However when I try to run it in SQL Server I receive the following error:

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Screenshot: http://i.stack.imgur.com/38rKV.jpg

All tables in the SQL Server are fresh, with no data (just like the fiddle).
What am I doing wrong?

Your trigger has no WHERE clause, does not correlate to the row(s) that were just inserted, and doesn't handle the case where multiple rows might be inserted in a single operation (unlike some platforms, in SQL Server a trigger fires per statement, not per row). So it will work exactly once: when you insert the very first row into the projects table. Try the following instead:

CREATE TRIGGER dbo.create_proid -- please always use schema prefix
ON dbo.projects
FOR INSERT
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE p
    SET pro_id = CONVERT(VARCHAR(20), c.comp_type)
      + '-'
      + RIGHT('00000' + CONVERT(NVARCHAR(20), i.client_id),
        CASE WHEN i.client_id < 100000 THEN 5 ELSE 6 END)
      + '-'
      + RIGHT('00000' + CONVERT(NVARCHAR(20), i.id),
        CASE WHEN i.id < 100000 THEN 5 ELSE 6 END)
  FROM dbo.projects AS p
  INNER JOIN inserted AS i
  ON p.id = i.id
  INNER JOIN dbo.comp_types AS c
  ON p.comp_type_id = c.comp_type_id;
END

Not sure why you're storing the pro_id value when you can determine it at runtime. Do you also have an update trigger that maintains it when the comp_type_id changes? What if a comp_type is deleted?

Also please always use schema prefix and don't convert to NVARCHAR without specifying a length. And why are you using the text data type? This has been deprecated for ages - you should be using NVARCHAR(MAX) or VARCHAR(MAX) only if these names and descriptions will really exceed 4000/8000 characters (highly unlikely from the naming).


failed with the following error: "Write on "....bak" failed: 27(error not found) BACKUP DATABASE is terminating abnormally This issue has been puzzling me for some time now and I'm running out of ideas! Any help would be much appreciated! Here's the problem:

MS SQL Server 2008 running on Windows Server 2008R2 (Virtualized)
Database backups are failing for all (larger) Databases on a particular server. Error code 27 suggests "The drive cannot find the sector requested.".

There is plenty of available space on backup drive, and I have tried backing up to other drives on the server which also fail.

I am able to backup the system databases without any trouble. (presumably due to their small size)

I created a new blank database and was able to back this up.

Manual backups of the main Databases fail (using 'right click' or TSQL). They get to 30% and the fail as if they have run out of space.

This is a virtual server running on a two node cluster. I've tried failing the VM to the other node, but this doesn't help.

Tried splitting the backup into five parts, but the job starts hanging again at 30%.

I tried manually copying one of the old backups to the drive using Windows Explorer. The file copied successfully.

It feels like a problem with the drives, however it's only an issue from within SQL Server.

Standard options for me to check would be the following, but this has been done by you or does not match exactly because the backup at least starts:

Disk space sufficient? (If possible do a chkdsk)
Write Access granted to SQL Server?

Next step would be to check throughput of backup drive because SQL Server backup is sensible in this area, some timers in the background are watching. Backup to a USB-Drive / Stick is usually a very bad idea because of this.

Now for the more exotic stuff:

H:-Drive is NTFS formatted? Otherwise File size >4 GB might be an issue.
As we are dealing with a VM here: Is there some kind of disc overcommitment happening on Hyper-V or SAN level? Just because you see a e.g. 1 TB drive does not guarantee that this is covered by physical disks somewhere.
Any I/O errors in windows system event log? If something really bad is happing on I/O level you can find it there.


Concurrent reads and updates Let's say I have a table with 50,000 transactions that need to be processed in the background in the same order they were received. I have a Windows service that polls for transactions, picks them up, updates the transaction table that that record is picked by this instance at a particular time. All this works perfectly well.

Now, suppose I want to scale out the processing and run another instance of the same service on another machine, how do I ensure that both instances do not pick up the same records?

I  either have both instances picking the same record and one instance overwriting update of the other or if I have additional filters in the update query to prevent that I have one instance picking up some records and updating it and the other instance running but not able to do anything simply because the record is already updated.

Is there a way to solve this?

My favourite way of achieving this is the OUTPUT clause. Here is an example:

SET NOCOUNT ON;

-- example setup for the queue table
DECLARE @queue TABLE (
    transaction_id int PRIMARY KEY,
    processed bit
);

-- some sample data
INSERT INTO @queue
VALUES
    (1,0),
    (2,0),
    (3,0),
    (4,0),
    (5,0);

Now the processing thread:

-- processing thread
WHILE 1 = 1
BEGIN
    DECLARE @transaction_id int;

    DECLARE @row_to_process TABLE (
        transaction_id int
    );

    BEGIN TRAN;

    BEGIN TRY

        DELETE FROM @row_to_process;

        UPDATE Q
        SET processed = 1
        OUTPUT inserted.transaction_id
            INTO @row_to_process
        FROM (
            SELECT TOP(1) transaction_id, processed
            FROM @queue
            WHERE processed = 0
            ORDER BY transaction_id
        ) AS Q;

        IF @@ROWCOUNT = 0
        BEGIN
            COMMIT;
            -- sleep for 5 seconds then restart the loop
            WAITFOR DELAY '00:00:05';
            CONTINUE;
        END

        SELECT @transaction_id = transaction_id
        FROM @row_to_process;

        IF @transaction_id IS NOT NULL
        BEGIN
            RAISERROR(N'processing transaction N. %d.',1,1, @transaction_id) WITH NOWAIT;
            EXEC whatever_processes_the_row @transaction_id;
        END

        COMMIT;

    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END

If you're marking the row as processed by deleting it, it works in the same way: the only thing you have to do is change the UPDATE statement into a DELETE statement and pull the current @transaction_id from the DELETED logical table.


removing specific Key and value from Hstore in postgres i have a data which is in Hstore form so i want to select a specific key and value  from data and then delete them,  as i use this query it is find all rows which this key and value exist on it but i want those rows which is only involve this key and value not all

select * from topdog where tags @> 'created_by=>JOSM'

thanks

Seems that you should use the = (equality) operator instead of @>(containment).

It will filter only the exact rows and you could use them in a delta statment.


Clustered Index Update I am trying to understand why is my query (update statement) causing clustered index update in the plan? Based on my clustered index (in the where clause), I am just updating columns in my table. What is the need for SQL Server to do clustered index update? I am not updating clustered key for SQL Server to reorganize/order the table according to clustered key, instead it has to update the columns or NC index on them wherein clustered key pointer to the NC rows will be the same (since I am not updating the clustered key). Can someone explain me why such behavior from SQL Server?

A clustered index stores all columns from the table, and is effectively the table's data source. Therefore, updating the table in any way will update the clustered index.


Conditional relationships? One-to-many relationship using a pivot table for only a type of entity I have the following tables:

users:
  - id
  - name
  - user_type_id (references id on user_types)

neighborhoods:
  - id
  - name

homes:
  - id
  - house_number
  - neighborhood_id (references id on neighborhoods)

user_types:
  - id
  - label

The available user_types are resident, neighborhood_admin, super_admin. Each type of user has access to different parts of the system. Only residents must have a relationship with the homes table.

I was thinking adding a pivot table like:

residents:
  - user_id (references id on users)
  - home_id (references id on home)

but I'm not sure if this approach is correct. I've used pivot tables for many-to-many relationships only. Also, using code I would have to check the user_type before assigning a home, but I could just as easily assign a home to a super_admin if I'm not careful.

How can I make the database enforce this restriction so that it doesn't have to be done through code?

UPDATE: I forgot to add, neighborhood_admins must be assigned to a neighborhood. I could add the following table:

neighborhood_admins:
   - user_id (references id on users)
   - neighborhood_id (references id on neighborhoods)

But this creates a problem. The users table now has two different relationships to the neighborhoods table; if the user is a neighborhood_admin, then the relationship is obtained from the pivot table neighborhood_admin but if the user is a resident, then we must first obtain the home relationship and then the home's neighborhood.

I'm thinking I might as well just add a neighborhood_id column to the users table and be done with it, but I don't like those relationship triangles. Also, that column would be null for super_admins so it couldn't be a foreign key.

In most RDBMSs you'll have the ability to set-up column conditionals, which are useful for cases like this. Say your user types are 1, 2 and 3 (res, admin, super respectively). You can set users this way:

CREATE TABLE users (
  id serial NOT NULL PRIMARY KEY,
  name varchar(100) NOT NULL,
  user_type_id int NOT NULL REFERENCES user_types,
  home_id int NULL REFERENCES homes,
  -- a resident must have a home, a non-resident cannot have a home
  CHECK ((user_type_id = 1 AND home_id IS NOT NULL) OR
         (user_type_id <> 1 AND home_id IS NULL))
);


How to resolve ORA-01031: insufficient privileges error on Windows installation? I installed Oracle 11G on my Windows 2008 R2 Server. I also installed Oracle Client Libraries using separate installation media. After the client installation, when I try to log into the database using:

C:\>sqlplus / as sysdba

I get a following error:

  ORA-01031: insufficient privileges

This worked before the client installation. My account is on the ORA_DBA group. My account is also in the Administrator group. I don't have ORACLE_SID set in my environment variables list. Does it have to be? This worked before the client installation, and I did not have that variable at that time either.

UAC is on at the lowest level, and I always run cmd as admin.

I have an sqlnet.ora file like this in the folder:

C:\app\myaccount\product\11.2.0\dbhome_1\NETWORK\ADMIN

sqlnet.ora

# sqlnet.ora Network Configuration File: C:\app\myaccount\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

tnsnames.ora

# tnsnames.ora Network Configuration File: C:\app\myaccount\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.mydb.com)
    )
  )

listener.ora

# listener.ora Network Configuration File: C:\app\myaccount\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\myaccount\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\myaccount\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\myaccount

From the windows registry (using regedit), from the path Computer > HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ORACLE I can find two keys:

KEY_OraClient11g_home1
KEY_OraDb11g_home1

And under those keys I can find ORACLE_HOME variables. So it seem that the client installation generated also ORACLE_HOME so that I have now two ORACLE_HOMEs. I don't know if this has something to do with this problem?

How to resolve this error on my Windows machine?

The problem is that your Oracle Client installation is in the system PATH before your Oracle Server installation (in fact, you even mention that it worked before you installed the client).
Proceed as follows:

Remove the Oracle Client as it's only confusing things. Use the uninstall option but afterwards check that the directory and the Oracle Client registry key is gone and remove by hand if necessary.
(It's possible to get this working by having the Oracle client home in the path after the server home, but it's much simpler to just have the single install. An Oracle server installation already contains a complete Oracle client anyway).

Make sure that the path to the server's home is in your PATH. And no other Oracle home is in the path.

Make sure that your account is in the ORA_DBA group. Double check this, especially if you are using domains.

If all else fails, you could remove SQLNET.AUTHENTICATION_SERVICES=(NTS) from your sqlnet.ora as this will force you to use password authentication.


Getting two or more column values through a Cursor SQL Server I have two tables like this:
Table Experiment  (I will just use one roadID (rID) for now)

rID | latBegin |longBegin |latEnd   |longEnd
041 | 23.875464|-80.456798|30.549879|-83.5465521
041 | 33.776563|-81.157526|33.248261|-81.2468425

For each of the above rows I want the cursor to get the rID, latBegin,longBegin and check the distance from the lat and long in
Table ExperimentDetails

rID | lat      |long      |temp |direction|tension
041 | 33.665632|-81.137952|56.23|L        |54.54
041 | 23.245632|-80.127952|56.23|L        |23.54
041 | 23.454555|-80.232456|66.23|L        |13.54
041 | 23.568763|-80.346586|26.23|L        |63.54
041 | 23.796545|-80.446586|86.23|L        |83.54
041 | 23.996545|-80.946586|96.23|L        |22.54

The steps for the query are:
Step 1: Get latBegin and longBegin for a specific rID from Table Experiment
Step 2: Go to ExperimentDetails and run this calculation to get the distance difference for each lat and long in the Details table

Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000

Step 3: Get the minimum value for this new associated column in Details Table
Step 4: Get the associated temp,direction, and tension values for that minimum value and place it in a new table (Not done yet> Help needed)
Step 5: Get the average of temp, tension for the previous 30 feet or 360 inches from the min value and place it in another table (columns: rID, latBegin,longBegin,avgTension,avgtemp Not done yet>Help needed)

This is my code so far:

      DECLARE @rID int,@latBegin decimal(15,10),@longBegin decimal(15,10),@minV decimal(15,10),@temp decimal(15,10)

    DECLARE MY_CURSOR CURSOR
      LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
    SELECT DISTINCT rid,latbeg,longbeg
    FROM dbo.Experiment
    WHERE rID='041'

    OPEN MY_CURSOR
    FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin
    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID Order by rID)
, @temp=temp

        FROM dbo.ExperimentDetails
            WHERE rid='041'

        Insert into dbo.Test(rid,lat,lon,min,temp) values   (@rid,@latBegin,@longBegin,@minV,@temp)

        FETCH NEXT FROM MY_CURSOR INTO @rID,@latBegin,@longBegin
    END

    CLOSE MY_CURSOR
    DEALLOCATE MY_CURSOR

The Test table looks like this:

rID|lat      |long      |min |temp
041|23.875464|-80.456798|12.1|96.23
041|33.776563|-81.157526|11.0|96.23

The temp data is repeated for the last row, which is not correct

It should look like this:

rID|lat      |long      |min |temp
041|23.875464|-80.456798|12.1|83.54
041|33.776563|-81.157526|11.0|54.54

I have given as much information as a I could, but if you need more please ask, would appreciate any help, been banging my head on this since yesterday. Currently I'm trying to bring in temp, direction, and tension in addition to @minV, but it does not let me do it

For Step 5:
The calculation in the cursor looks like this in theory for Details table:

rID |lat       |long      |temp |DISTDIFF
041 | 23.245632|-80.127952|56.23|372.12
041 | 23.454555|-80.232456|66.23|300.22
041 | 23.568763|-80.346586|26.23|50.48
041 | 23.796545|-80.446586|86.23|12.10

Then when inserting I check the minimum value i.e. 12.10 and insert the associated values with that row.
For step 5, I need to find the value closest to 360 BEFORE the minimum value not after (there might be other values closer after), which in this case is 372.12 and then take the average of temp like this:

rID |minlat    |minlong   |lastlat  |lastlong  |Avgtemp
041 | 23.796545|-80.446586|23.245632|-80.127952|58.73

Using Sql Server 2014

I think I have a solution for you... but!!! IMPORTANT: You are not considering having more than 1 record in DETAIL that equals the Min Value...
You also are missing the TENSION column in TEST table

Anyway, to answer your question try replacing this:

SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID Order by rID)
, @temp=temp

        FROM dbo.ExperimentDetails
            WHERE rid='041'

        Insert into dbo.Test(rid,lat,lon,min,temp) values   (@rid,@latBegin,@longBegin,@minV,@temp)

With this:

    SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID)
    FROM dbo.ExperimentDetails  WHERE rid=@rid

   Insert into dbo.Test(rid,lat,lon,min,temp,tension)
   select rid, @latBegin,@longBegin,@minV,temp,tension from
   dbo.ExperimentDetails  WHERE rid=@rid and Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) = @minV

Good Luck!

---FOR STEP 5 -----

First of all,
I will assume that you already have created the other table
let's say: Temp360

So, include another 2 variables in the DECLARE zone...
let's say: @min360 decimal(15,10) and @avgtemp decimal(15,10)

DECLARE @rID int,@latBegin decimal(15,10),@longBegin decimal(15,10),@minV decimal(15,10),@temp decimal(15,10), @min360 decimal(15,10),@avgtemp decimal(15,10)

then use this code inside your cursor loop:

//part 1... what we already know + JUST getting the AvgTemp

        SELECT @minV=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude), @avgtemp = AVG(temp)
        FROM dbo.ExperimentDetails  WHERE rid=@rid

       Insert into dbo.Test(rid,lat,lon,min,temp,tension)
       select rid, @latBegin,@longBegin,@minV,temp,tension from
       dbo.ExperimentDetails  WHERE rid=@rid and Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) = @minV

//part 2... something similar + the Average Temp we already got.

        SELECT @min360=Min(Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) over (Partition by rID)
        FROM dbo.ExperimentDetails  WHERE rid=@rid  and
Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) >=360

       Insert into dbo.Test360(rid,lat,lon,min,avgtemp,tension)
       select rid, @latBegin,@longBegin,@min360,@avgtemp,tension from
       dbo.ExperimentDetails  WHERE rid=@rid and Round(Sqrt(POWER((Abs(lat - @latBegin)),2) + POWER((Abs(long - @longBegin)),2)) * 3958.73926185, 4) * 1000,latitude) = @min360


Quotename function in SQL server The question I have is

Why use:

QUOTENAME ('simple string', '''');

Instead of:

QUOTENAME ('simple string', ' ' ');

if I want to use single quotation as the delimiter?  I assume it has to do with escaping, but can't find any detailed explanation about this function.

Two single-quotes together indicate a zero-length string.

Three single-quotes together indicate an escaped single quote; i.e. ''' becomes '

Four single-quotes together indicate an escaped single quote followed by another single quote, which is translated into a quotated single quote, similar to what you desire.  I know, it's confusing.  Try writing nested dynamic SQL, and you'll pretty quickly find strings with 7, 8, or more single quotes in a row.

Consider the output of the following three statements:

SELECT '[' + '' + ']'
SELECT '[' + '''' + ']'
SELECT '[' + '''-''' + ']'

[]

[']

['-']

Typical usage of QUOTENAME() is to protect against SQL Injection when concatenating T-SQL strings, by escaping text that is provided by a user.  For instance:

DECLARE @MyInput VARCHAR(50);
SET @MyInput = 'some table name';
DECLARE @cmd NVARCHAR(1000);
SET @cmd = 'SELECT * FROM ' + QUOTENAME(@MyInput) + ';';
PRINT (@cmd);

will output SELECT * FROM [some table name];.

If you don't use the QUOTENAME() function:

DECLARE @MyInput VARCHAR(50);
SET @MyInput = 'some table name';
DECLARE @cmd NVARCHAR(1000);
SET @cmd = 'SELECT * FROM ' + @MyInput + ';';
PRINT (@cmd);

You get SELECT * FROM some table name; which is not valid T-SQL because of the spaces in some table name.

Here comes little bobby tables:

You can see the effect of quotename in the following code:

IF OBJECT_ID('tempdb..#Students') IS NOT NULL
DROP TABLE #Students;
CREATE TABLE #Students
(
    StudentName VARCHAR(50)
);

INSERT INTO #Students (StudentName) VALUES ('Max');

SELECT *
FROM #Students;

    DECLARE @MyInput VARCHAR(50);
    SET @MyInput = 'Robert'';DROP TABLE #Students--';
    DECLARE @cmd NVARCHAR(1000);
    SET @cmd = 'SELECT * FROM #Students WHERE StudentName = ''' + QUOTENAME(@MyInput, '''') + ''';';
    PRINT (@cmd);
    EXEC (@cmd);

SELECT *
FROM #Students;

GO

    DECLARE @MyInput VARCHAR(50);
    SET @MyInput = 'Robert'';DROP TABLE #Students--';
    DECLARE @cmd NVARCHAR(1000);
    SET @cmd = 'SELECT * FROM #Students WHERE StudentName = ''' + @MyInput + ''';';
    PRINT (@cmd);
    EXEC (@cmd);

SELECT *
FROM #Students;

The output:

With the QUOTENAME() function, we do get an error message, but without the function, we allow our user to drop the #Students table.

Note, I'm using 4 single quotes in my QUOTENAME() function, since that is actually interpreted as one single quote, and used to wrap the text provided to the function, thereby making that text "safe".

Erland Sommarskog has an excellent piece on dynamic SQL at http://www.sommarskog.se/dynamic_sql.html - this is well worth reading (and understanding!) if you are considering doing dynamic SQL.


Why is MariaDB 10 refusing to use my index on a date field? I have the following table definition:

CREATE TABLE `bets` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_mp` bigint(20) NOT NULL DEFAULT '0',
  `coin` char(3) COLLATE utf8_unicode_ci NOT NULL,
  `game_id` tinyint(4) NOT NULL DEFAULT '1',
  `group_turn_id` bigint(20) NOT NULL,
  `uid` int(11) NOT NULL,
  `direction` tinyint(1) NOT NULL DEFAULT '0',
  `bet_amount` double NOT NULL,
  `multiplier` double NOT NULL,
  `target` double NOT NULL DEFAULT '0',
  `result` double NOT NULL,
  `profit` double NOT NULL,
  `win` tinyint(1) NOT NULL,
  `seed` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `bets_time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=1189677 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

You'll note that there is a primary key as well as a secondary index on the 'time' field. Mysteriously though, the following query refuses to use the bets_time index:

EXPLAIN SELECT COUNT(1) AS num_bets, SUM(bet_amount) AS total_wager FROM bets WHERE `time` > (NOW() - INTERVAL 24 HOUR)

yields the following:

+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|    1 | SIMPLE      | bets  | ALL  | bets_time     | NULL | NULL    | NULL | 1132313 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+

As you can see it sees bet_time as a possible index, but does not use it. Any ideas as to why this might be happening? I guess I could use FORCE INDEX on the query, but I'd rather avoid hardcoding such query logic into the SQL and prefer an 'proper' solution.

Thanks a lot for any ideas.

The optimizer seems to think that your index is not selective enough. I think that you can do one or more of the following things:

ANALYZE TABLE bets;
ALTER TABLE bets ADD INDEX bets_time_amount (time, amount); /* this is a covering index */
add a FORCE INDEX to your SELECT


Merge two separate UPDATEs into one I have two separate UPDATEs as follows:
UPDATE mytab
SET colA = 1
WHERE id IN ( SELECT id FROM mytab
              JOIN othertab ON mytab.otid = othertab.id
              WHERE othertab.Lock = 'SomeLock' AND mytab.colA IS NULL);
UPDATE mytab
SET colB = 2
WHERE id IN ( SELECT ID FROM mytab
              JOIN othertab ON mytab.otid = othertab.id
              WHERE othertab.Lock = 'SomeLock' AND mytab.colB IS NULL);

I wish them as one merged UPDATE query. What I did is:
UPDATE mytab
SET colA =
CASE WHEN colA IS NULL THEN 1 ELSE colA END,
colB =
CASE WHEN colB IS NULL THEN 2 ELSE colB END
WHERE id IN ( SELECT id FROM mytab
                  JOIN othertab ON mytab.otid = othertab.id
                  WHERE othertab.Lock = 'SomeLock');

In general it does what I want on both SQL Server and Oracle. What is concerning me here is the performance of the merged query. In that shape it will update every row that is returned after filtering by predicate. Any better solutions?

I don't know enough about Oracle to know if the below is valid syntax there or not... or if there are niftier tricks specific to that RDBMS.  However, on SQL Server I would do something like this.
/** If you are updating small numbers of rows then this is probably good enough
    on SQL Server.  Properly indexed tables, etc.
    **/
UPDATE mytab
SET colA = COALESCE(colA, 1)
    , colB = COALESCE(colB, 2)
WHERE EXISTS (SELECT TOP (1) 1 FROM othertab WHERE othertab.id = mytab.otid AND othertab.Lock = 'SomeLock')
    AND (   colA IS NULL
            OR colB IS NULL
            )

/** Larger numbers of rows could do better like this.
    **/
UPDATE M
SET colA = COALESCE(colA, 1)
    , colB = COALESCE(colB, 2)
FROM mytab AS M
    INNER JOIN othertab AS O ON O.id = M.otid AND O.Lock = 'SomeLock'
WHERE M.ColA IS NULL
    OR M.ColB IS NULL


sql server log shipping: how to monitor that the replica is fresh? (e.g. that log shipping is working) I want a monitor on my replica (destination) server to message me:

a) once a day, telling me: "Data as of  is ### minutes old"

b) any time data is older than N minutes (indicating log shipping has gone down)

I typically have tlogs moving every 5 minutes, on a very active database.

Thank you!

a) once a day, telling me: "Data as of is ### minutes old"

You have to  get the status using  master..sp_help_log_shipping_monitor to get the status. There are many scripts available that will send out HTML email through SQL Agent job like

Get an overview report on your log shipping setups
Logshipping status report Throgh Mail
Automating SQL Server Transactional Log Shipping Alerts

  b) any time data is older than N minutes (indicating log shipping has gone down)

The job LSAlert_XX where XX is the instance name, run a job  exec sys.sp_check_log_shipping_monitor_alert to check if the restore of the log has fallen behind N mins. You can edit/configure it to send out emails in case where it has fallen behind N mins.


Query databases that have MYISAM tables that do not have only fulltext index tables I am trying to query from the information_schema where the database (table_schema) has MYISAM tables but does not have only fulltext index tables.

The statistics tables is very poorly designed for the query that I need. If you look, the table_schema and table_name appear multiple times.  Thus querying where table_name != 'fulltext' is not an option because there could be another row in statistics table for that table that is Btree which usually the case for PK indexes on tables. This results in that table that has a fulltext coming back using the above mentioned where clause in query.

So with that being said here is my Query so far:

SELECT DISTINCT
    (a.TABLE_SCHEMA)
FROM
    information_schema. TABLES a
INNER JOIN information_schema.statistics b ON a.TABLE_NAME = b.TABLE_NAME,
 (
    SELECT DISTINCT
        c.TABLE_SCHEMA,c.table_name
    FROM
        information_schema.statistics c
    WHERE
        c.index_type = 'FULLTEXT'

) AS fullTextTables
WHERE
    a. ENGINE = 'MyISAM'
AND a.table_schema NOT IN (
    'information_schema',
    'mysql',
    'performance_schema'
)
AND (a.Table_schema != fullTextTables.Table_schema AND b.table_name != fullTextTables.table_name);

The problem is that when I run this I am still getting back databases that only have 1 or 2 tables left in them that have not been converted from MYISAM; those tables still have Fulltext indexes. The Mysql version is 5.5 and not 5.6. Note: upgrading at this point is not an option.

Any help amending this query to get proper results would be appreciated.

MyISAM tables with FULLTEXT index and other index(es).

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME
    FROM  information_schema.statistics AS s1
    JOIN  information_schema.statistics AS s2  USING (TABLE_SCHEMA, TABLE_NAME)
    JOIN  information_schema.TABLES AS t       USING (TABLE_SCHEMA, TABLE_NAME)
    WHERE  s1.index_type = 'FULLTEXT'
      AND  s2.index_type != 'FULLTEXT'
      AND  t.ENGINE = 'MyISAM'
      AND  TABLE_SCHEMA  NOT IN ('information_schema', 'mysql', 'performance_schema');

# MyISAM tables with no FT index:

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME
    FROM  information_schema.TABLES AS t
    WHERE  t.ENGINE = 'MyISAM'
      AND  TABLE_SCHEMA NOT IN ('information_schema', 'mysql',
                'performance_schema'
                          )
      AND  NOT EXISTS
      ( SELECT  *
            FROM  information_schema.statistics
            WHERE  index_type = 'FULLTEXT'
              AND  TABLE_SCHEMA = t.TABLE_SCHEMA
              AND  TABLE_NAME = t.TABLE_NAME
      );

# MyISAM tables, counting index_types:

SELECT  DISTINCT TABLE_SCHEMA, TABLE_NAME,
        SUM(index_type = 'FULLTEXT') AS FT_indexes,
        SUM(index_type != 'FULLTEXT') AS others
    FROM  information_schema.TABLES AS t
    JOIN  information_schema.statistics USING(TABLE_SCHEMA, TABLE_NAME)
    WHERE  t.ENGINE = 'MyISAM'
      AND  TABLE_SCHEMA NOT IN ('information_schema', 'mysql',
                'performance_schema'
                          )
    GROUP BY  1,2;

Tack on a HAVING FT_indexes = 0 to get just those with no FT index.

Etc.


SQL Server : ETL Stage Index We have a very large staging table (> 80 GB). From our source system we load invoice data in the staging table. From the staging we transform the data and load into DWH/Fact. Every day we delete the current month, then reload from source into stage. The stage contains complete history over time.

In some DW loads we only need the current month. Sometimes year and previous year.

What is a better index strategy:

Clustered index on a date column (Fiscal Period)
Primary key with IDENTITY as surrogate key
Clustered index for natural key (some kind of line item e.g. invoice number)

All queries contain the date column (Fiscal Period) and sometimes additional columns like Invoice type as non-clustered index. In the ETL we can disable the non-clustered index but not the clustered index.

Which of the three types has the best performance for:

Insert into Stage table
Query the Stage table

If it possible for you to use partitioning, I would highly recommend leveraging it in this situation as you know that you're working with periodic data loads which fall into months or years. With partitioning, you could reserve the creation of your indexes on other columns that you may need on things such as InvoiceNo, etc. Let partitioning take care of isolating which month or period you are working with.

If you are fairly sure about the fact that date is integral portion of your join or searches, then you I would associate a "smartkey" for your loading strategy and place a clustered index on that instead.

If you are truly determined to use the date as an index seek, then I would place an non-clustered index on this field as you'll likely have to scan rows in your use-case anyway. Keep in mind creating a clustered index on date and then incurring updates or deletes will fragment your table.


How do I select distinct json columns? I have over 10,000 geojson rows in a table. How do I go about selecting only distinct coordinates?

The data was imported from a csv file into PostgreSQL 9.5. The columns in the table are:

Trip Duration (seconds)
Start Time and Date
Stop Time and Date
Start Station Name
End Station Name
Station ID
Station Lat/Long

The imported contains I took the latitude and longitude columns and turned it into a geojson object:

update h set thestartgeom =  st_asgeojson(startstationgeom);

The column thestartgeom in the table is

  thestartgeom text;

The database contains 10,000+ lat/lng and other records. Some of the coordinates are duplicated. I am trying to eliminate all duplicates.

startstationgeom is a geometry(POINT,4326).

Here is the geojson column:

                    thestartgeom
---------------------------------------------------------------------

{"type":"Point","coordinates":[40.6882247924805,-73.9793853759766]}
{"type":"Point","coordinates":[40.7329139709473,-74.0071105957031]}
{"type":"Point","coordinates":[40.7429084777832,-73.9770584106445]}
{"type":"Point","coordinates":[40.7160568237305,-73.9919052124023]}
{"type":"Point","coordinates":[40.7497177124023,-74.0029525756836]}
{"type":"Point","coordinates":[40.7349281311035,-73.9920043945312]}
{"type":"Point","coordinates":[40.7281455993652,-73.9902114868164]}
{"type":"Point","coordinates":[40.691650390625,-73.9999771118164]}
{"type":"Point","coordinates":[40.7501983642578,-73.9909286499023]}
{"type":"Point","coordinates":[40.7503814697266,-73.9833908081055]}

You'll want PostGIS's ST_GeomFromGeoJSON().

SELECT DISTINCT ST_GeomFromGeoJSON(geojson)
FROM tbl;

Should work to get the points. However may find this is faster... I'd be interested in seeing a benchmark. ST_Union will eliminate duplicate points and geometries too.

SELECT ST_Union(ST_GeomFromGeoJSON(geojson))
FROM tbl;

Update

Is it faster, what advantages and disadvantages does this have?

DISTINCT requires a SORT. faster to sort floats than strings.
My ST_Mem_Size(ST_GeomFromGeoJSON(geojson)) shows 32 bytes (two floats).
Not sure how much space the raw JSON takes, likely more than 32 bytes.
Likely have to have these as ::geography at some time.
Processing the JSON and creating points is a one time cost but it may be significant.
ST_GeomFromJSON supports all geometry objects. Including MultiPoints.

Update2

If you already got the type as startstationgeom, just do

SELECT ST_AsGeoJSON(ST_Union(startstationgeom)) FROM table

That will output only unique geometries.