Are unfixable spatial index corruptions considered normal? I have a spatial index for which DBCC CHECKDB reports corruptions:

DBCC CHECKDB(MyDB)
WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS

  The spatial index, XML index or indexed view 'sys.extended_index_xxx_384000' (object ID xxx) does not contain all rows that the view definition produces.  This does not necessarily represent an integrity issue with the data in this database.

  The spatial index, XML index or indexed view 'sys.extended_index_xxx_384000' (object ID xxx) contains rows that were not produced by the view definition.  This does not necessarily represent an integrity issue with the data in this database.

  CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.extended_index_xxx_384000' (object ID xxx).

Repair level is repair_rebuild.

Dropping and recreating the index does not remove these corruption reports. Without EXTENDED_LOGICAL_CHECKS but with DATA_PURITY the error is not reported.

Also, CHECKTABLE takes 45 minutes for this table although its CI is 30 MB in size and there are about 30k rows. All data in that table is point geographydata.

Is this behavior expected under any circumstances? It says "This does not necessarily represent an integrity issue". What am I supposed to do? CHECKDB is failing which is a problem.

This script reproduces the issue:

CREATE TABLE dbo.Cities(
    ID int  NOT NULL,
    Position geography NULL,
 CONSTRAINT PK_Cities PRIMARY KEY CLUSTERED
(
    ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO
INSERT dbo.Cities (ID, Position) VALUES (20171, 0xE6100000010C4E2B85402E424A40A07312A518C72A40)
GO
CREATE SPATIAL INDEX IX_Cities_Position ON dbo.Cities
(
    Position
)USING  GEOGRAPHY_AUTO_GRID
WITH (
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

This is version 12.0.4427.24 (SQL Server 2014 SP1 CU3).

I scripted the table with schema and data, fresh DB, execute. Same error. CHECKDB also has this incredible runtime of 45min. I captured the CHECKDB query plan using SQL Profiler. It has a misguided loop join apparently causing excessive runtime. The plan has quadratic runtime in the number of rows of the table! Doubly nested scanning loop joins.

Clearing all non-spatial indexes does not change anything.

I could not immediately reproduce this on 2014 - 12.0.4213.0 but do see it on SQL Server 2016 (CTP3.0) - 13.0.700.242.

On the 2014 build (with no DBCC errors) the plan looks as follows.

And on the 2016 build (with DBCC errors reported) like this.

The second plan has a single row coming out of the merge anti semi join, the first plan zero rows.

The join predicates are different with respect to what is matched to the pk0 column in the spatial index.

The first one correctly maps it to the table Primary Key, The second maps it to the Id column returned from the TVF.

According to the SQL Server 2012 internals book this is a binary(5) value for the Hilbert number of the cell so this predicate certainly is incorrect (If the Id of the single row in the base table is set to 1052031049 instead of 20171 I no longer see any DBCC errors as this happens to correspond to this value of 0xa03eb4b849).

On 2014 - 12.0.4213.0 after re-creating the table as follows I could reproduce the problem.

CREATE TABLE dbo.Cities(
    Id int  NOT NULL,
    Position geography NULL,
 CONSTRAINT PK_Cities PRIMARY KEY CLUSTERED
(
    Id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

(Note the change from ID to Id)

My 2014 instance is installed with Case Sensitive collation. So it looks as though this may have prevented the column confusion before.

So I guess a potential workaround might be to rename the column in Cities as CityId for example.

Connect Item (Microsoft bug report)


MySQL 100+ instances on one Windows server Please don't ask why I need 100+ instances on one server because I cannot say; suffice to say that I'm not impressed.  They will be very small databases and not definitely not under any great load (one or two users per instance).

The server is Windows 2012R2 64bit with 8GB RAM.
The MySQL version is 5.6.26.
innodb_buffer_pool_size=415M

After 27 instances are running I get the error in the Windows event log for every instance after that:

  InnoDB: Cannot allocate memory for the buffer pool"

The instances will have the most basic of databases, probably no more than 10 records in each and 10 - 15 entries in each record.

If needs be, I can allocate more memory.

To reduce the RAM used by each instance from ~450MB to 17MB this setting was required:

performance_schema=off

To fix the

  InnoDB: Cannot allocate memory for the buffer pool

...the setting innodb_open_files=300 had to be changed to innodb_open_files=3000.


Pairing Row Values in Any Order I'm trying to find % of unique pairs that exist in two columns. For example

Where 1,6 and 6,1 / 2,3 and 3,2 are unique pairs. So the % of matched pairs is 33%

Doesn't make much sense:

the number of pairs is 6, -- not relevant to the question...
the number of unique permuted pairs is 4 - far more relevant...
There are 2 matched pairs so I make the number of unique to matched pairs at 4/2, i.e. 50%...

Notwithstanding the objection above, what you can do is something like this, using the LEAST and GREATEST (almost standard - see discussion below) SQL functions.

This example (see fiddle) is from PostgreSQL, however see discussion at the end.

CREATE TABLE test (col1 INTEGER, col2 INTEGER);

populate with your data:

INSERT INTO test VALUES (1, 6), (2,3), (3, 2), (4, 7), (5, 8), (6, 1);

First query:

SELECT LEAST(col1, col2) AS mn_c, GREATEST(col1, col2) AS mx_c,
COUNT(*)
FROM test
GROUP BY 1, 2

Result:

mn_c    mx_c    count
   2       3        2
   4       7        1
   1       6        2
   5       8        1

Then:

SELECT COUNT(cnt1) AS matched_count,
       ROUND(COUNT(cnt1)/(SELECT COUNT(*) FROM test)::FLOAT * 100) AS percentage
FROM
(
  SELECT LEAST(col1, col2) AS mn_c, GREATEST(col1, col2) AS mx_c,
  COUNT(*) AS cnt1
  FROM test
  GROUP BY 1, 2
  HAVING COUNT(*) > 1
) AS t

Result:

matched_count   percentage
            2           33

A version of the above code should work for most servers - see here for a discussion of the LEAST and GREATEST functions in other servers - works pretty much for all of them except MS SQL Server.

p.s. have you considered what happens if you have duplicate columns as follows?

INSERT INTO test VALUES (1, 6), (2,3), (3, 2), (4, 7), (5, 8), (6, 1), (5,5), (5,5)

Note the duplication of a duplication - (5,5) - see here for the differences which emerge between my approach and that of @McNets. My solution says that there are 3/8 matched pairs, but McNets says different. Not sure if I completely understand what his SQL is doing?

Anyway, interesting question (+1) - why do you want to do this? p.s. welcome to the forum! :-)


Counting Null rows between non Null values I have a table that counts logins to a website with the first column, USER_ID, being the user's unique identifier. The next column is a list of months in the date format %m/%d/%Y, and the next column is a date which indicates whether or not that person logged into the website during that month.
What I want to generate is a 4th column that counts NULL values between non-NULL values for that 3rd column.

My question is: given the first 3 columns below, how would I write a query that generates the 4th column?

You could have a column which is the rolling count of the login column (because COUNT(Login) won’t include nulls. Then have a rolling count (less one) of that column, partitioned by it too (aka ROW_NUMBER()). This second column will reset to zero every time you have a login value, but will be able to keep increasing in between. All these should also be partitioned by the User, and you may want to do something special when COUNT(Login) is zero, because that will be the time before the user has logged in (so maybe use CASE for that).


Query Cost when filtering with varchar vs nvarchar Recently the application I'm working on encountered performance issue on a particular table. When I check the queries executing on this table, I noticed a warning about CONVERT_IMPLICIT on the predicate of the query. Apparently the data type of the column is varchar, but the query is passing in nvarchar, hence the warning.
Out of curiosity, I try to see how much difference would passing the correct data type improves query performance by checking the execution plan. I got the following result:

To my surprise, when I'm using the matching data type varchar, the query cost is double compare to using nvarchar, even though it seems like the second query is not using the most efficient execution plan.
So is the relative query cost to be trusted, or is there something I missed?
Here's the execution plan.
If even the actual execution plan is not reliable, what is a better way to compare the execution cost of 2 queries?

This is due to a Cardinality Estimate issue. Even though your first execution plan (when filtering on a VARCHAR) estimates returning more rows (when you hover over the clustered index seek operation), it's the correct estimate that matches your actual number of rows returned.
The second execution plan (when filtering on NVARCHAR) under-estimates the number of rows it believes will be returned which results in a less optimal execution plan, but even worse, it'll request less server resources (memory and CPU) than it actually needs to be allocated to process the query. This will cause the query to run slower since effectively the actual number of rows being returned is the same in both cases (same amount of data) but the query optimizer estimates only half the number of rows are being returned than what actually is.
The under-estimation of the number of rows being returned is due to the IMPLICIT_CONVERSION Warning which you can see if you hover over the SELECT operator of the second execution plan. This generally happens when the data types in your JOIN, WHERE, or HAVING clauses are of different data types than the fields they're being compared against, forcing the SQL engine to perform a cast operation. This messes with the statistics the server stores about the cardinality of the fields being compared to in the predicates, and ultimately results in a cardinality estimate issue such as the one you're currently facing.


XMLTABLE solution without getting right parenthesis missing error I am trying to select from an XML type and produce multiple rows. Have searched for my specific scenario/issue with not much success. Environment: Oracle 12c. I have commented out the offending code.

WITH XML_DATA AS
    (SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
    <response>
        <phone>
            <number>859111</number>
            <contactModes>
                <mode>AUTO_DIAL</mode>
                <mode>AUTO_TEXT</mode>
                <mode>MANUAL_DIAL</mode>
            </contactModes>
            <contactableTimeRange>
                <day1>
                    <startTime>08:00:00</startTime>
                  <endTime>20:00:00</endTime>
                </day1>
                <day2>
                    <startTime>08:00:00</startTime>
                    <endTime>20:00:00</endTime>
                </day2>
            </contactableTimeRange>
        </phone>
    </response>') doc
    FROM DUAL)
SELECT X.phone
    ,x3.d1starttime
    ,x3.d1endtime
    /*,x4.* */
    FROM XML_DATA XD,
    XMLTABLE('/response/phone' PASSING XD.DOC
         COLUMNS phone  VARCHAR2(36) PATH 'number',
                CONTACT_MODES XMLTYPE PATH 'contactModes',
                CONTACT_TIMES XMLTYPE PATH 'contactableTimeRange')  X
      /*  , XMLTABLE('/contactModes'
       passing x.contact_modes
       COLUMNS  modes VARCHAR2(10) 'node' ) AS  x2*/
       ,XMLTABLE('/contactableTimeRange/day1' PASSING X.CONTACT_TIMES
                COLUMNS D1STARTTIME VARCHAR2(10) PATH 'startTime',
                        D1ENDTIME VARCHAR2(10) PATH 'endTime') AS X3
       ,XMLTABLE('/contactableTimeRange/day2' PASSING X.CONTACT_TIMES
                COLUMNS D2STARTTIME VARCHAR2(10) PATH 'startTime',
                D2ENDTIME VARCHAR2(10) PATH 'endTime') X4;

I would like the query the return the following records. The mode values are not static, could be more or less. And same thing for the days. Could be day1 ..dayn

Phone      MODE      DAY    STARTTIME     ENDTIME
859111    AUTO_DIAL   1    08:00:00  20:00:00
859111    AUTO_DIAL   2    08:00:00  20:00:00
859111    AUTO_TEXT   1    08:00:00  20:00:00
859111    AUTO_TEXT   2    08:00:00  20:00:00
859111    MANUAL_DIAL 1    08:00:00  20:00:00
859111    MANUAL_DIAL 2    08:00:00  20:00:00

You might have wanted this:

WITH XML_DATA AS
(SELECT XMLTYPE('<?xml version="1.0" encoding="UTF-8"?>
<response>
    <phone>
        <number>859111</number>
        <contactModes>
            <mode>AUTO_DIAL</mode>
            <mode>AUTO_TEXT</mode>
            <mode>MANUAL_DIAL</mode>
        </contactModes>
        <contactableTimeRange>
            <day1>
                <startTime>08:00:00</startTime>
              <endTime>20:00:00</endTime>
            </day1>
            <day2>
                <startTime>08:00:00</startTime>
                <endTime>20:00:00</endTime>
            </day2>
        </contactableTimeRange>
    </phone>
</response>') doc
FROM DUAL)
SELECT X.phone
,x2."MODE"
,To_Number (SubStr (x3.Day, 4)) Day
,x3.starttime
,x3.endtime
/*,x4.* */
FROM XML_DATA XD,
XMLTABLE('/response/phone' PASSING XD.DOC
     COLUMNS phone  VARCHAR2(36) PATH 'number',
            CONTACT_MODES XMLTYPE PATH 'contactModes',
            CONTACT_TIMES XMLTYPE PATH 'contactableTimeRange')  X
   , XMLTABLE('/contactModes/mode'
   passing x.contact_modes
   COLUMNS "MODE" VARCHAR2(10) Path '.' ) AS  x2
   ,XMLTABLE('/contactableTimeRange/*' PASSING X.CONTACT_TIMES
            COLUMNS Day VARCHAR2(10) PATH 'name()',
                    STARTTIME VARCHAR2(10) PATH 'startTime',
                    ENDTIME VARCHAR2(10) PATH 'endTime') AS X3

Which produces:

    PHONE                                MODE              DAY STARTTIME  ENDTIME
    ------------------------------------ ---------- ---------- ---------- --------
    859111                               AUTO_DIAL           1 08:00:00   20:00:00
    859111                               AUTO_DIAL           2 08:00:00   20:00:00
    859111                               AUTO_TEXT           1 08:00:00   20:00:00
    859111                               AUTO_TEXT           2 08:00:00   20:00:00
    859111                               MANUAL_DIA          1 08:00:00   20:00:00
    859111                               MANUAL_DIA          2 08:00:00   20:00:00


MongoDB Replication - “Failed with No route to host” Hi,

Running MongoDB version 3.4.9 on two separate servers running Centos 7.

Trying to add a create a replica set but when ever I try to add a second server I get this error:

devshardcfgrs:PRIMARY> rs.add("s2-b6:26052")
{
        "ok" : 0,
        "errmsg" : "Quorum check failed because not enough voting nodes responded; required 2 but only t                                                                                he following 1 voting nodes responded: s1-b5:26051; the following nodes did not respond affirmatively: s                                                                                2-b6:26052 failed with No route to host",
        "code" : 74,
        "codeName" : "NodeNotFound"

What am I doing wrong?

This is my /etc/hosts file:

127.0.0.1   localhost

::1         localhost

172.24.1.42 s1-b5

172.24.1.43 s2-b6

All help is appreciated :)

Firewall! Go to the command line of s1-b5 and give command mongo --host 's2-b6:26052' you should have a successful login to "second" node. I guess that these two nodes are actually one machine?!? And when you use "external IP address", traffic goes thru the firewall. So, iptables-save command should show that this port 26052 is open for traffic.


Most efficient way to get stats on an entire SQL Server database What I'm looking to do is review our databases and lock down any auto-shrink settings, as well as get a handle on which databases/tables are highly fragmented.

Is there a particular script I can run to get a good idea per database?

I know I can run the following on a per table level (SQL Server 2005 at least):

DBCC SHOWCONTIG ('DB.TABLE');

But what can I run to show me all tables in a database?

Thanks

For checking fragmentation in 2005/2008 you can use the following script.  You need to set the @DB and @Table values.  If you define those as NULL then it will run on all databases and/or all tables.  If you do a single db make sure you execute in that DB's context (USE MyDB).

SELECT
    object_name(IPS.object_id) AS [Table Name],
    SI.name AS [Index Name],
        CASE IPS.Index_type_desc
            WHEN 'CLUSTERED INDEX' THEN 'Clustered'
            ELSE 'Non-Clustered'
        END AS 'Index Type',
    IPS.avg_fragmentation_in_percent as 'Avg Fragmentation (%)',
    IPS.avg_fragment_size_in_pages as 'Avg Frag Size (pages)',
    IPS.page_count as 'Page Count',
    IPS.forwarded_record_count as 'Forwarded Records',
    --IPS.avg_page_space_used_in_percent as 'Avg Page Space Used (%)',
    --IPS.record_count as 'Record Count',
    --IPS.ghost_record_count as 'Ghost Record Count',
    IPS.fragment_count as 'Fragment Count'
FROM sys.dm_db_index_physical_stats
    (
        db_id(@DB),
        OBJECT_ID(@Table),
        NULL,
        NULL ,
        'LIMITED'
    ) as IPS
JOIN sys.indexes as SI WITH (nolock)
    ON IPS.object_id = SI.object_id
    AND IPS.index_id = SI.index_id
ORDER BY 1,3,5

For autoshrink you can just check master.sys.databases:

select * from master.sys.databases
where is_auto_shrink_on = 1


How to change the expiration date of binlog files? I have a server with Ubuntu 18.04 and MySQL 8.0.11

I have binlog files in /var/lib/mysql that make several GO and my server will soon saturate.

How to change the expiration date of binlog files ?

If I go to :

nano /etc/mysql/my.cnf

The file contains :

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

If I go to :

nano /etc/mysql/conf.d/mysql.cnf

The file contains :

[mysql]

If I go to :

nano /etc/mysql/mysql.conf.d/mysqld.cnf

The file contains :

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

I can not find the file to configure. Is it safe for the database to change the timeout ?

How to change the expiration date of binlog files ?

To purge binary logs prior to a binary log file or prior to a particular date, use purge binary logs statement.

To set an expiration period for binary logs, use binlog_expire_logs_seconds. I would recommend to keep this more than 2 days, whether or not you have replication enabled.

  I can not find the file to configure.

To see the order in which MySQL looks at the configuration files, run the following:

mysqld --help --verbose | grep -A 2 "Default options are read from"

If you also want to see what values the different variables would take on start, remove the grep part.

For a variable whose value is specified multiple times in the configuration files, or multiple times in the same file, the last value would be considered by MySQL. Some of these configuration files may not already be present, in which case you can create them on your own, and under [mysqld] in the configuration file, add the parameters you want to set.

  Is it safe for the database to change the timeout ?

Yes, it is safe if there is no replication involved. Even if a slave does exist, if all the logs that are to be deleted have been already processed and executed by the slave, it is safe to delete those logs from master.


Auto-archive MariaDB10 database table I am trying to auto-archive active database table to archive database table using stored procedure and event in MariaDB10. These are the steps I am following:

Create new table everyday just before midnight in archive database.
copy rows from active table to archive table.
delete all rows from active table.

This seems to work mostly except one issue. PLC is writing to active database table every second. When the above mentioned process is taking place, there is a gap of 2 mins between last entry of archive table and first entry of active table (meaning active table is loosing 2 mins worth of data i.e 120 rows). As per my understanding this 2 mins gap is when rows are copied over to arch table and deleted from active table. There are approximately 87K rows(256MB)generated in a day.

Question is how can I reduce this 2 mins gap (loss of rows/data)? Any help or pointers would be really helpful.

I just noticed that for step 2, I am using INSERT INTO with SELECT, probably this is locking the rows of active table hence PLC is not able to insert new rows to the active table thus loosing the 2 mins of data/rows. IT is innoDB engine. Is this thinking correct? If yes, any way to get around it?

PS: I have instrumentation engineering background (and minimal experience of DBA) and I have inherited this PLC-MariaDB setup. Original DB is not indexed, also, there is no auto-archiving taking place. Thus DB is growing in size and affecting the performance when doing any queries on DB. I have indexed the DB now and performing auto-archiving.

The perhaps obvious solution would be to modify step 3: Don't just delete all rows from the active table. Instead, find a column you can use as an indicator of whether the rows are newer or older than the most recent row that will be copied to the archive table/database.

This column can be e.g. an integer used for the primary key column, or if you don't have that, perhaps a timestamp column.

So instead of deleting every row, you can do something like:

DELETE FROM active_table WHERE id <= @maxIdThatWasArchived;

Or something like:

DELETE FROM active_table WHERE ts <= @maxTimeStampThatWasArchived;


SQL Server Distributed Transactions: xp_sqljdbc_xa_commit vs xp_sqljdbc_xa_end In SQL Server 2019, can anyone definitively tell me the functional difference between xp_sqljdbc_xa_commit & xp_sqljdbc_xa_end?
My guess is that commit is attempting to commit the transaction (thank you Mr. Obvious ;-) ) and end is telling DTC "hey, all done here. Please clean up."
But, the code appears to live in SQLJDBC_XA.dll (as extended sp) so I can't view it, and I can't find a good reference to confirm my assumptions.
Thanks!

The SQL Server JDBC Driver's source code on GitHub is a great place to track this down.
@Override
public void end(Xid xid, int flags) throws XAException {
    // Called by the transaction mgr after the app closes the connection it was given from this physical
    // connection
    /*
     * Ends the work performed on behalf of a transaction branch. The resource manager disassociates the XA resource
     * from the transaction branch specified and let the transaction be completed. If TMSUSPEND is specified in
     * flags, the transaction branch is temporarily suspended in incomplete state. The transaction context is in
     * suspened state and must be resumed via start with TMRESUME specified. If TMFAIL is specified, the portion of
     * work has failed. The resource manager may mark the transaction as rollback-only. If TMSUCCESS is specified,
     * the portion of work has completed successfully.
     */
    DTC_XA_Interface(XA_END, xid, flags | tightlyCoupled);
}

src/main/java/com/microsoft/sqlserver/jdbc/SQLServerXAResource.java
So it's used by SQL Server's implementation of the XAResource interface.
For background see How It Works: SQL Server DTC (MSDTC and XA Transactions)


How can I capture error thrown by SET_LOCKTIMEOUT in ola Hallengren A stats only job for Ola Hallengren was blocked for the 180 seconds as specified below.  The job threw an error and emailed me and the other DBA.  However, upon review of the CommandLog table and the output file, there was no record of what the error was.  I eventually figured out what had happened by looking at our monitoring tool, noticing the blocking for exactly the amount of time as the locketimout value and then looking at Ola's site to find that it throws a severity 16 error when the lock timeout is reached.
Is there a way to have the Ola scripts log the error so it's easier to figure out what went wrong?
EXECUTE [dbo].[IndexOptimize]
    @Databases = 'USER_DATABASES' ,
    @FragmentationLow = NULL ,
    @FragmentationMedium = NULL ,
    @FragmentationHigh = NULL ,
    @UpdateStatistics = 'ALL' ,
    @OnlyModifiedStatistics = N'Y' ,
    @LockTimeout = 180,
    @LogToTable = N'Y';

As you're already doing, @LogToTable = 'Y', should log errors. However, I tested this and I'm not getting an error logged to the commandlog table, either, but it does get logged to an output file. I'm using the latest version of Ola's script and table on SQL 2019. If you add @LockMessageSeverity = 10 (the default is 16), the job will at least run and you won't get alerted. You may want to check your stats, though, because the update stats statement being blocked will be skipped. You could submit this as a possible bug through Ola's github.


Cluster node upgrade of SQL Server 2008 R2 to SP3 fails when moving resource I'm trying to update a cluster to SQL Server 2008 R2 SP3.  I've installed SP3 on the passive node but when I failover the cluster it errors when running through it's upgrade scripts.  The messages & error messages from the SQL Logs when it's back on it's happy node are as follows:

Msg - Exporting ##MS_AgentSigningCertificate## to N:\SQL\DATA\MPData7\MS_AgentSigningCertificate.cer
ErrMsg - Could not export certificate, trying again with random name. If this is a mkmastr build then this is an error.
Exporting ##MS_AgentSigningCertificate## to N:\SQL\DATA\MPData7\MS_AgentSigningCertificate2DC9C8FF-1B52-4B40-8F59-6B7714201D43.cer
Error: 15240, Severity: 16, State: 1.
Cannot write into file 'N:\SQL\DATA\MPData7\MS_AgentSigningCertificate2DC9C8FF-1B52-4B40-8F59-6B7714201D43.cer'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 15240, state 1, severity 16.
This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting.
Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

There are other error messages related to not being able to recover the master database but I'm not worried about them.
On reviewing the error messages I can see that the path the certificate is trying to be exported to is not the same as the default data path for the instance.
The default path for the instance is N:\SQL\Data\MPData5 which I can see from the server properties and the registry. The SQL Server service account has full read/write access to that path.

If
(the path to the master database or system databases (including certificates) exist and the SQL Server domain account have the appropriate permissions)
Then
(Look into further troubleshooting by introducing the global trace flag 902. This skips script upgrade of the database after a SP or CU patch upgrade. See link: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql
.This will let you know if your patch on the passive was actually successful. If cluster still fails to come online, then its either a permission or an unusual install. Can you check the setup bootstrap logs on the passive node to confirm that the patching was indeed successful. Check the Detail_%datetime%.txt and the Summary_%datetime%.txt files to confirm this. Let us know what you find to continue troubleshooting.


Online Schema change when DB has existing triggers I'm doing some due diligence before attempting a MariaDB schema change that really needs to be performed online, but seems like I'm going to have issues with the existing DB triggers in using a tool like Percona online schema change, Github gh-ost and even Facebook's online schema change.  Are there any options that I am missing out there? Any resources to recommendations to minimize downtime?

Yes there are.

DB doesn't have any triggers. A table can has triggers.
If you want to alter the table which hasn't fulltext index maybe online DDL can help you.(It is implemented in MySQL/MariaDB)

But gh-ost is the best choose when you have triggers on the table.

Percona Online schema change and another tools such as oak-online-alter-table,soundcloud/lhm, facebook-osc are good when your table doesn't have any triggers. But in MariaDB 10.2 you can create multiple triggers for the same event So maybe they can work (I did not test)

Tools that maybe you didn't know:

https://github.com/soundcloud/lhm

https://github.com/shlomi-noach/openarkkit

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html


Show Users That Connect To DB We are using Maria DB (v5.3 I believe) in our production environment.

One issue that we have, is that historically, the dev user (which has full rights and privileges) is used in all applications that connect to the db. We've started making application specific users and replacing those with the dev user.

Once we replace the dev user, the plan is to limit the rights/privileges on the (dev) user to only read only.

I believe we've now replace the majority of the application users from dev to their app specific user. However, I'm not 100% certain that no app is still using the dev user.

My question is, is there sql, or logs, that tell me the users who connected to to the db's w/in the past day/week/month, etc? How can i tell who connected to the db from a historical point of view?

You can record every connection using the general log.

In fact, you can record that information into a table.

In the mysql schema, there is a table called general_log. Here it is:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.01 sec)

mysql>

Of course, the table uses the CSV storage engine. You can change that to MyISAM and index it. Make a copy of the table beforehand:

use mysql
CREATE TABLE general_log_copy LIKE general_log;
ALTER TABLE general_log ENGINE=MyISAM;
ALTER TABLE general_log ADD INDEX (event_time);

To activate it, do this

1) Configure the general log to record to a table by adding these lines to my.cnf

[mysqld]
general-log
log-output=TABLE

2) Restart mysql

3) start mysql and user it for a day or two

4) Look for the dev user by running this query

SELECT * FROM mysql.general_log WHERE user_host LIKE 'dev %';

This will tell you what time and what IP dev is coming in

5) Hunt down the dev user in all your code

6) Truncate the general log

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
TRUNCATE TABLE mysql.general_log;
SET GLOBAL general_log = @old_log_state;

7) If you did find 'dev' users sneaking in, wait 24 hours and go back to step 4. Otherwise, you're done

CAVEAT

Please disable the general log when you are done by removing the lines in step 1. After that, just run

SET GLOBAL general_log = 'OFF';

That's all !!!

Please see my posts about the table version of the general log:

Jan 28, 2013 : mysql logging activity from specific user or ip
Feb 11, 2012 : MySQL general log
Nov 16, 2011 : Log MySQL DB changing queries and users

I also did this using the slow log instead : How to do MySQL User Accounting

Give it a Try !!!


Why is a hash match operator in this very basic query I'm beginning to learn some about looking at execution plans and making queries more efficient

Consider these two basic queries

select distinct pat_id, drug_class, drug_name from rx

select pat_id, drug_class, drug_name from rx

and their execution plans

index being used:

CREATE CLUSTERED INDEX [ix_overlap] ON [dbo].[rx]
(
    [pat_id] ASC,
    [fill_date] ASC,
    [script_end_date] ASC,
    [drug_name] ASC
)

Even though the first query supposedly has the higher cost by a 4:1 margin it runs faster than the second one.  Why is it that a simple distinct added to the query will add the (what I assume to always be bad, corrections are welcome) hash match operator?  And why does it have the higher query cost relative to the second query if it runs faster.

The first query is using a parallel plan, meaning the "work" was split into multiple tasks carried out by multiple threads. The cumulative CPU time was therefore higher than for the serial plan used for your second query.

As to why the distinct causes the hash match operator to appear in the plan; an aggregate or sort operation is required to determine the DISTINCT result. @SQL_Kiwi might pop up with a more indepth explanation shortly but the hash match operator is apparently favoured for larger result sets.

  The optimizer tends to prefer the Hash Match Aggregate on larger
  rowsets, with fewer groups, where there is no reason to produce a
  sorted output, and where the incoming rows are not sorted on the
  DISTINCT expression(s).  Larger inputs favour hash matching because
  the algorithm generally scales well (although it does require a memory
  grant) and can make good use of parallelism.  Fewer groups are better
  for hashing because it means fewer entries in the hash table, and the
  memory needed to store unique values is proportional to the number of
  groups (and the size of the group).  Hash matching does not require or
  preserve the order of the incoming row stream. [Source]


count null values of table from information_schema tables Is there any way to count null values in a column by using information_Schema tables? EG:

 SELECT table_name, table_rows
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA ='myschema'
 and table_name='mytable'
 and table_rows is null;

Clarifications from comments:

I can not get the number of null values directly from the table as the table is large and not even returning the count of null values, so I thought information_schema would be the best option to get count null values from table. But I don't know how to do this. Is there a way?

I have a table named cal and one of its column contains null values. I just need to count the number of those null values. As table is large I cannot get result by using select count(*) from tablename where column_name is null.

So I need a query which counts null values from Information_Schema somehow.

About the suggestion to add index:

  But can I add an index on a column if column_type = mediumtext?

MySQL version 5.1.73 and table is MyISAM.

No, you cannot use information schema for this. Information_schema.TABLES only stores an (approximate) count of the number of rows in a table. Not counts of values or nulls.

You should have an index on the column and use the simple query instead:

-- if there isn't an index in place, add one:
alter table my_table
  add index (my_column_idx)
    (my_column) ;

select count(*)
from my_table
where my_column is null ;

  But can i add an index on a column if column_type = mediumtext?

Well, no.

  MySQL version 5.1.73 and table is MyISAM.

OK, now we have 3 problems.

First, you are in a very old, unsupported version of MySQL. Upgrade. At least to 5.5, if not to 5.6 or 5.7. This is not urgent of course and only partially related to the problem. Even at 5.7, we still can't have an index on a mediumtext column.

Second, you are using MyISAM? Still, in 2016? I guess it matches well with 5.1. Anyway, convert the tables and application to use InnoDB. That's not urgent or related to the issue either but you'll get benefits (transactions, foreign keys, etc). But no, even with InnoDB, we still can't have an index on a mediumtext column.

So, what to do? various ways - thrown not in any specific order:

Add a TINYINT computed (GENERATED) column on something like CASE WHEN column IS NOT NULL THEN 1 END and index it. Requires upgrade to 5.7
Add a TINYINT computed (VIRTUAL) column, as the previous suggestions, and index it. Requires replacing MySQL 5.1 with MariaDB 5.2 or 5.3 (or upgrading to 5.5 and then replacing with MariaDB 5.5 or 10+)
Add a TINYINT column (like the computed ones) but fill the values and keep them correct by your application or triggers. This does not require upgrading but enforcing integrity in the application is not recommended. Considering the use of MyISAM though, integrity does not seems a priority.
Banish the Nulls! Split the table into two, moving only the mediumtext column into the 2nd table (and the primary key). The 2nd table will only have rows for the non-null values of the column. Then you only need to count rows of that table. You can use the information schema for that! (or just count, MyISAM is fast in counting the whole table.) Does not require any upgrade but your applications will have to change to deal with the new, slightly altered table structure.
use a prefix index (thnx to @Michael-sqlbot for the suggestion). This does not require any upgrade or modifications at all:

-- a prefix index on the mediumtext column:
alter table my_table
  add index (my_column_1_idx)
    (my_column(1)) ;      -- one is enough

I haven't tested this but should work as it is. It may require to use WHERE LEFT(my_column, 1) IS NULL in your count query but you can test.


Having multiple refs in an intersection table I have two tables, BOOK and JOURNAL.  I want to store their authors in an intersection table called AUTHORS.  Is it acceptable practice to have mulitple reference id columns (kind of FK's I think) to other tables i.e. BOOK and JOURNAL?  This is to save having separate author tables for Books and Journals.
So, AUTHORS would look like:

ID
BOOK_ID
JOURNAL_ID
NAME

0
54
212
A.R Smith

1
598
NULL
J.R. Hartley

This is a possible design based on Joel Brown's correct answer (+1).  The WorkIds could come from the same sequence.

Author Table

╔══════════╦═══════════╦══════════╗
║ AuthorID ║ FirstName ║ LastName ║
╠══════════╬═══════════╬══════════╣
║        0 ║ A.R       ║ Smith    ║
║        1 ║ J.R.      ║ Hartley  ║
╚══════════╩═══════════╩══════════╝

AuthorWorks Table

╔══════════╦════════╗
║ AuthorId ║ WorkId ║
╠══════════╬════════╣
║        0 ║      1 ║
║        0 ║      2 ║
║        1 ║      3 ║
║        0 ║      4 ║
╚══════════╩════════╝

Book Table

╔════════╦══════════╗
║ WorkId ║  Title   ║
╠════════╬══════════╣
║      1 ║ asdf     ║
║      3 ║ jkl      ║
║      4 ║ zxcvzxcv ║
╚════════╩══════════╝

Journal Table

╔════════╦══════════╗
║ WorkId ║  Title   ║
╠════════╬══════════╣
║      2 ║ dfghdfgh ║
╚════════╩══════════╝


Double queue reader or is it still busy? I came across this line in the verbose log on replication QueueReader Agent job startup.

  The Queue Reader Agent has encountered the error 'Another queuereader agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent.' when connecting to 'distribution' on 'Svr01\Inst01'. Ensure that the publication and subscription are defined properly and that both servers are running.

The first clue I had that something was wrong was the vague replication message:

  The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Replication IS working but Replication Monitor is showing red X's.
This second error has shown up from time to time and sometimes goes away.
For a while now I though this was a bug as it has been reported a few times on Connect.

Assuming the first quoted error is correct, how can I find the double QueueReader or previous request?

  tasklist /FI "imagename eq qreader.exe"

Returns nothing.

  SELECT job_id, name, enabled FROM msdb..sysjobs WHERE category_id = 19  ORDER BY date_created;

Returns 1 row on each distributor.

Setup:

Svr01 = Publisher, Distributor, SQL 2005
Svr02 = Publisher, Distributor, SQL 2005, Subscribor (Svr01)
Svr03 = SQL 2005, Subscribor (Svr01, Svr02)

Both Svr01 and Svr02 are reporting these errors.

Resources:

DBA.Stackexchange search results

MSDB forum

Solved it.

The Queue Reader agent is only used in snapshot or updatable transactional replications.

I just deleted the job and emptied tables MSqreader_agents & MSqreader_history from the distribution database. Finally I killed the qrdrsvc.exe process.

Helpful links:
http://social.msdn.microsoft.com/Forums/en-US/a6de412b-ebae-4a39-b00f-8f724237e14e/queue-reader-agent-error
http://technet.microsoft.com/en-us/library/ms147378.aspx


Does spgist extension in PostgreSQL 9.2.3 support float or int array? SP-GiST is an abbreviation for space-partitioned GiST in PostgreSQL 9.2.3. It can be used to find the similar tuple by creating the sp-gist index on the column.

e.g:

create index pt_spgist_idx on geo using spgist(point);

'Point' column on table 'Geo' is Point type of Geometric Types.

and we can use sql sentence to find the nearest point of (34.34898,-92.82934) with spgist index 'pt_spgist_idx':

select point from geo where point ~= '(34.34898,-92.82934)'

We can see that sp-gist index supports Geometric Point type.

The question is:

Does spgist extension in PostgreSQL 9.2.3 support float or int array? For I want to use sp-gist index in int array whose size is 500.

No, by default SP-GiST only supports these operators over point ranges box and text

Name            Indexed Data Type   Indexable Operators
kd_point_ops    point               << <@ <^ >> >^ ~=
quad_point_ops  point               << <@ <^ >> >^ ~=
range_ops       any range type      && &< &> -|- << <@ = >> @>
box_ops         box                 << &< && &> >> ~= @> <@ &<| <<| |>> |&>
text_ops        text                < <= = > >= ~<=~ ~<~ ~>=~ ~>~

That's not to say that it can not work over float or int[] but it may be awkward unless they have any special internal designation you can exploit for partitioning. That is to say, if your int[] and array are likely to produce balanced trees, or are equally distributed there is no point whatsoever (afaik).


Testing an Oracle DB connection before it is made - ERROR? I tried to make a new oracle DB connection with user name and password. I entered the connection name, user name and password in the new connection window. When I click the "test" button or the "connect" button, I get the following error:
Status : Failure - Test Failed: ORA-01017: invalid username/password; logon denied

Why am I getting this error ? What does the Test & Connect button actually do ?

The error is telling your user login username or password is incorrect. Remember that from 11g onwards, passwords are case sensitive.

The test & connect button tests if the entered details (TNSName, port etc) are correct & then proceeds to connect to the database. In your case, your credentials are wrong & you're getting the error message.


Difference between [mysql] and [mysqld] in MySQL configuration file There are multiple sections in MySQL configuration file:

[mysql]
[mysqld]
[myisamchk]
[mysqld_safe]
[mysqlhotcopy]
[mysqldump]
[client]

I found that same variables with different values have been defined under these sections.

What are the specific purpose behind defining these sections?
Is it necessary to define all?
How MySQL interprets these variables?

MySQL calls these section "Groups". The [mysqld] group contains the variables apply to the MySQL Server (the mysqld process). The [mysql] group contains the variables for the client program (mysql).

The [client] option group is read by all client programs (but not by mysqld), so "mysql", "mysqldump" etc...

None of these groups are mandatory but usually we set at least the [mysqld] because it's where we configure the server.

About how MySQL interprets these variables, MySQL Documentation says:

  If an option group name is the same as a program name, options in the
  group apply specifically to that program.

Max


Insert a range of numbers from a call to sp_sequence_get_range Given the following code:

CREATE SEQUENCE dbo.NextTestId AS [bigint]
 START WITH 10 INCREMENT BY 2 NO CACHE
GO

DECLARE
  @variableNumberOfIdsNeeded INT = 7, -- This will change for each call
  @FirstSeqNum SQL_VARIANT , @LastSeqNum sql_variant, @SeqIncr sql_variant;

EXEC sys.sp_sequence_get_range @sequence_name = N'dbo.NextTestId',
            @range_size = @variableNumberOfIdsNeeded,
            @range_first_value = @FirstSeqNum OUTPUT,
            @range_last_value = @LastSeqNum OUTPUT,
            @sequence_increment = @SeqIncr OUTPUT;

-- The following statement returns the output values
SELECT @FirstSeqNum AS FirstVal, @LastSeqNum AS LastVal, @SeqIncr AS SeqIncrement;

I get a result like this:

FirstVal    LastVal    SeqIncrement
-------     -------    --------------
38          50         2

I would like to have a set based way to take these results and insert each value (skipping by SeqIncrement) into this table:

DECLARE @newIds TABLE (IdType VARCHAR(100), [NewId] BIGINT)

So when I am done, a SELECT * from @newIds would return:

IdType    NewId
-------   -------
TestId    38
TestId    40
TestId    42
TestId    44
TestId    46
TestId    48
TestId    50

NOTE: I don't want to use a loop if possible.  Also, I will need to get a variable amount of results (this one shows 7, but it will change each call).

I think there may be a cross apply or some such thing that can do this.  But I can't seem to figure it.

This should be good up to about 2,500 values (depending on version):

;WITH x(n) AS
(
  SELECT TOP (@variableNumberOfIdsNeeded)
    (ROW_NUMBER() OVER (ORDER BY number)-1)
    * CONVERT(BIGINT, @SeqIncr)
    + CONVERT(BIGINT, @FirstSeqNum)
  FROM master.dbo.spt_values
  ORDER BY number
)
--INSERT @newIds([NewId])
SELECT n FROM x;

If you need more, or are worried about using the view master.dbo.spt_values, see other possible techniques for generating sets without looping here:

Generate a set or sequence without loops – part 1
Generate a set or sequence without loops – part 2
Generate a set or sequence without loops – part 3


Count the number of occurrences of certain values of TagName column I want to count the number of occurrences of different sensor rows in SQL, but I seem to be doing it wrong and seemingly I am not visualizing it correctly.
If I was doing this in pseudocode in a C style language, I would do it like this:
FOR i in range(taglist[i]):

    print(taglist[i], count(taglist[i]) )

I have been trying this:
SELECT COUNT(*)
FROM   (
           SELECT DISTINCT [TagName]
           FROM   [A2ALMDB].[dbo].[AlarmMaster]
           WHERE  (TagName LIKE '%Sensor%' OR GroupName LIKE'%Sensors%')
       ) a

It returns 66, but I want it to return the count of each of the distinct tagnames that are returned in SELECT ... FROM (...)a.
Can anyone help me with how I should be trying to get all the counts of my different sensor occurrences to total instead of a count of all the distinct tagnames?

Sounds like a simple group by:
select tagname, count(*)
from alarmmaster
where tagname like '%Sensor%'
   or groupname like '%Sensor%'
group by tagname;


LSCopy job fails on SQL Server 2005 - Insufficient space I'm having some trouble getting my LSCopy job of Log-Shipping on my SQL Server 2005 server, to work.

The LSBackup job is configured so the backup is made locally (D: drive)

The LSCopy job is configured to copy from the primary (\server_name\d$) and copies it locally to the secondary server.

The LSRestore is restores the local copy to the secondary database with NORECOVERY

The LSBackup job runs fine, copies the file to the location specified.

The LSCopy job fails. The error message states that it goes to the source for settings. Gets the settings (I assume) then begins the copying.

This errors saying:

  Error: Not enough server storage is available to process this command.

What I'm not sure about is whether the destination folder needs to be a network share, or if just \server_name\d$ is accepted.

Any help will be really appreciated!

The destination path for the LSCopy job has to be an UNC Path for all practical purposes, to make things simpler to debug I would reccomend not to use the default \d$ shares as you will have to change the permissions on them but connecting to \localhost\c$ is a network connection. Now the error

  ***Error: Not enough server storage is available to process this command.

Is an operating system error. but not an error from the SQL Server. You are probably starving the computer on memory or having some network issues, If on Windows Server 2003 a quick reboot of the secondary server will fix this temporary and then I would look into memory settings on the server


Can I export a maintenance plan without using Integration Services? I'm trying to export a simple maintenance plan from an SQL Server instance.

I want to check the export into source control, and apply the exported maintenance plan to functionally identical instances.

StackOverflow and SQL Server Newbie recommend using Integration Services to export the maintenance plan.

When I try to connect to Integration Services on the Export target, I receive the following error:

  Connecting to the Integration Services service on the computer
  "WEBSERVER" failed with the following error: The specified service
  does not exist as an installed service.

We chose to disable Integration Services on WEBSERVER because we use this box only for serving data to consumer applications. All data on WEBSERVER is replicated from a backend instance. Integration Services is used heavily for processing data on the backend instance.

Is there a documented way to export a maintenance plan without using Integration Services? Does Microsoft support it?

Maintenance plans are stored in msdb.dbo.sysssispackages like any other SSIS packages that are stored to SQL Server.  I have a handy post on SSIS Package Extract from MSDB that should cure what ails you.


Unexpected CASE evaluation logic I always understood that the CASE statement worked on a 'short-circuit' principle in that evaluation of subsequent steps does not take place if a prior step is evaluated to true. (This answer Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition? is related but doesn't appear to cover this situation and relates to SQL Server).
In the following example, I wish to calculate the MAX(amount) between a range of months that differs based on how many months are between the start and paid dates.
(This is obviously a constructed example but the logic has valid business reasoning in the actual code where I see the issue).
If there are < 5 months between the start and paid dates then Expression 1 will be used otherwise Expression 2 will be used.
This results in the error "ORA-01428: argument '-1' is out of range" because 1 record has an invalid data condition that results in a negative value for the start of the BETWEEN clause of the ORDER BY.
Query 1
SELECT ref_no,
       CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
          MAX(amount)
             OVER (PARTITION BY ref_no ORDER BY paid_date ASC
             ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
             AND CURRENT ROW)
       ELSE
-- Expression 2
           MAX(amount)
             OVER (PARTITION BY ref_no ORDER BY paid_date ASC
             ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
       END
    END
  FROM payment

So I went for this second query to first eliminate anywhere this can occur:
SELECT ref_no,
       CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0
       ELSE
          CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
             MAX(amount)
                OVER (PARTITION BY ref_no ORDER BY paid_date ASC
                ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
                AND CURRENT ROW)
          ELSE
             MAX(amount)
                OVER (PARTITION BY ref_no ORDER BY paid_date ASC
                ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
          END
       END
  FROM payment

Unfortunately, there is some unexpected behaviour that means that the values Expression 1 WOULD use are validated, even though the statement will not be executed because the negative condition is now trapped by the outer CASE.
I can get around the issue by using ABS on the MONTHS_BETWEEN in Expression 1, but I feel like this should be unnecessary.
Is this behaviour as expected ? If so 'why' as it seems illogical to me and more like a bug ?

This will create a table and test data. The query is simply me checking that the correct path in the CASE is being taken.
CREATE TABLE payment
(ref_no NUMBER,
 start_date DATE,
 paid_date  DATE,
 amount  NUMBER)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('01-01-2016','DD-MM-YYYY'),3000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('12-12-2015','DD-MM-YYYY'),5000)

INSERT INTO payment
VALUES (1001,TO_DATE('10-03-2016','DD-MM-YYYY'),TO_DATE('10-02-2016','DD-MM-YYYY'),2000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('03-03-2016','DD-MM-YYYY'),6000)

INSERT INTO payment
VALUES (1001,TO_DATE('01-11-2015','DD-MM-YYYY'),TO_DATE('28-11-2015','DD-MM-YYYY'),10000)

SELECT ref_no,
       CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN '<0'
       ELSE
          CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
             '<5'
         --    MAX(amount)
         --       OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS
         --       BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
         --       AND CURRENT ROW)
          ELSE
             '>=5'
         --    MAX(amount)
         --       OVER (PARTITION BY ref_no ORDER BY paid_date ASC ROWS
         --       BETWEEN 5 PRECEDING AND CURRENT ROW)
          END
       END
  FROM payment

So it was hard for me to determine what your actual question was from the post, but I assume it is that when you execute:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0 THEN 0
   ELSE
      CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
         MAX(amount)
            OVER (PARTITION BY ref_no ORDER BY paid_date ASC
            ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
            AND CURRENT ROW)
      ELSE
         MAX(amount)
            OVER (PARTITION BY ref_no ORDER BY paid_date ASC
            ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
      END
   END
FROM payment

You still get ORA-01428: argument '-1' is out of range?

I don't think this is a bug. I think it is an order of operation thing. Oracle needs to do the analytics on all of the rows returned by the resultset. Then it can get down to the nitty gritty of transforming the output.

A couple of additional ways around this would be to exclude the row with a where clause:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
   -- Expression 1
      MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
         ROWS BETWEEN MONTHS_BETWEEN(paid_date, start_date) PRECEDING
         AND CURRENT ROW)
   ELSE
   -- Expression 2
       MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
         ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
   END
END
FROM payment
-- this excludes the row from being processed
where MONTHS_BETWEEN(paid_date, start_date) > 0

Or you could embed a case in your analytic like:

SELECT ref_no,
   CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 5 THEN
-- Expression 1
      MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
               ROWS BETWEEN
               -- This case will be evaluated when the analytic is evaluated
               CASE WHEN MONTHS_BETWEEN(paid_date, start_date) < 0
                THEN 0
                ELSE MONTHS_BETWEEN(paid_date, start_date)
                END
              PRECEDING
              AND CURRENT ROW)
   ELSE
-- Expression 2
       MAX(amount)
         OVER (PARTITION BY ref_no ORDER BY paid_date ASC
         ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
   END
END
FROM payment

Explanation

I wish I could find some documentation to back up the order of operation thing, but I haven't been able to find anything...yet.

The CASE short-circuit evaluation happens after the analytic function is evaluated. The order of operations for the query in question would be:

from payment
max over()
case.

So since the max over() happens before the case, the query fails.

Oracle's analytic functions would be considered a row source. If you execute an explain plan on your query, you should see a "window sort" which is the analytic, generating rows, which are fed to it by the previous row source, the payment table. A case statement is an expression that is evaluated for each row in the row source. So it makes sense (to me at least), that the case happens after the analytic.


SQL Server data extract with limited performance impact Background:

I have a SQL Server 2008 Enterprise Edition database containing three tables. The tables contain ~3 million, ~14 million, ~14.5 million rows respectively.

Problem:

I need to do a data extract from the database for reporting purposes. This will involved joining the three tables and writing each row to a text file. This database is currently taking production traffic and I want to limit performance impact. This data extract should not prevent reads, writes, or updates from occuring during the course of the process.

From the research I have done, I believe adding the 'NOLOCK' hint to the data extract query will allow the production traffic to behave normally while still accomplishing my primary task of the data extract. I'm assuming the dirty reads associated with the 'NOLOCK' hint will be isolated to the data extract query.

Am I correct to assume that the 'NOLOCK' hint on the data extract query will only affect that one particular query? Does it affect all queries being executed on the database while the data extract query is executing?

Is there a better approach to accomplishing my goal than what I've presented above?

Many ways you can approach this scenario.

  I need to do a data extract from the database for reporting purposes.

Backup restore approach is best suited if you want to do this on an infrequent basis and do a static reporting of data.
Database Snapshot is an enterprise version feature only and this also gives you a static point-in-time view of the database.

Note that database snapshots take up space on disk, and having too many can fill up your disk space, especially in a production environment where data is updated frequently. In addition, using database snapshots will degrade performance a bit because I/O is increased on the database due to the fact that the data pages are copied as the write operation is performed.

Refer to Microsoft SQL Server Database Snapshots and Synonyms to over come some limitations that snapshots have and workarounds.

Look into Replication especially T-Rep if you want to do near to real-time reporting and want to offload the reporting work from the primary server. T-Rep will give you the benefits of Transactional consistency, Low latency, High Throughput, Minimal overhead as well as the ability to filter rows.
Lastly if none of the options are feasible to you, then with proper testing look into changing the isolation level of the database to RCSI (Read Committed Snapshot Isolation). This will be at the COST of TEMPDB performance.

-- check if RCSI is enabled
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = '<dbname>'
-- Enable RCSI
ALTER DATABASE <dbname> SET READ_COMMITTED_SNAPSHOT ON

From The Data Loading Performance Guide :

  RCSI basically will prevent queries reading data  to block or be blocked by other queries modifying data in the same table. It is a powerful alternative to NOLOCK because it guarantees a complete, transactionally consistent view of the data and does not require a special hint. While RCSI originally targeted scenarios common to OLTP workloads, the feature can be a powerful tool in data warehouse workloads or scenarios involving large-scale bulk insert operations.

  RCSI is enabled as a database-wide setting. When enabled, reader queries do not acquire shared locks on rows, pages or tables, and as a result they are not blocked by X or BU-locks taken by others. Instead, new or modified rows in a table carry a 17-byte version identifier, and the before-images of any rows being changed by a transaction (updated or deleted) are copied to tempdb using the row versioning mechanisms within SQL Server. Reader queries consider only those rows that were committed as of the start of the query – by ignoring any later version numbers and referencing tempdb for appropriate earlier versions of rows.

Also read up on : Comparing Different Results with RCSI & Read Committed from SQL CAT team.

LASTLY, to touch on NOLOCK hint, I would suggest you to read up

SQL Server NOLOCK Hint & other poor ideas.
Is the NOLOCK (Sql Server hint) bad practice?
THE SIDE EFFECT OF NOLOCK


Foreign Key with a Constant Let's say I have a table A, that has two columns: one is an ID for ThingA, and one is an ID for ThingB. The primary key is (ThingA, ThingB).

Next, I have a second table, but this time it's restricted to entries in table A that have ThingB = 3. The primary key is ThingA, because ThingB is a constant of 3.

Initially, I had thought I could simply:

FOREIGN KEY (ThingA, 3) REFERENCES A(ThingA, ThingB)

But I've learned that's not the case, and I have to create a column for the ThingB:

ThingB INT NOT NULL DEFAULT(3) CHECK(ThingB = 3)

Then,

FOREIGN KEY (ThingA, ThingB) REFERENCES A (ThingA, ThingB)

Is there an alternative to this that doesn't require an extra column, or the DEFAULT + CHECK? One alternative is a persisted, computed column, but I hate that idea too as it's basically a cheat and still adds a new column with physical storage. While on it's own, the INT won't be big, there are several million rows that need it across several tables, and I'd rather not maintain the extra columns.

Here's sample DDL to illustrate the situation:

CREATE TABLE Test1
(
    ThingA INT NOT NULL,
    ThingB INT NOT NULL,
    PRIMARY KEY (ThingA, ThingB)
);

CREATE TABLE Test2
(
    ThingAVal INT NOT NULL,
    ThingBVal INT NOT NULL DEFAULT(3) CHECK(ThingBVal = 3),
    Val INT NOT NULL,
    FOREIGN KEY (ThingAVal, ThingBVal) REFERENCES Test1 (ThingA, ThingB)
);

And I've created a db<>fiddle that demonstrates my (current) solution:

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=edba40478f104ac017c3444138a07762

If the answer is "No", I'll accept it, but I'm curious if there are any other alternatives.

You said you didn't want to add an extra column to a number of tables that need to link back to Test1 in this way (ie on ThingA, 3).

How about adding one persisted computed column to TestA that shows the value ThingA if ThingB is 3, and null otherwise?

Then your foreign key references the new column only, based on ThingA in the referencing table.

alter table Test1 add SpecialThingA as
    (case ThingB when 3 then ThingA else null end) persisted;

and

FOREIGN KEY (ThingA) REFERENCES Test1 (SpecialThingA)

In other words - one new column on Test1, rather than a new column on Test2 (to hold '3') and Test3, and ....


What is the best way to model an invoicing system with different forms of invoice items? I am doing some preliminary database modeling for a Line-Of-Business application that I am creating. The application will be a Practice Management System for a medical practice/clinic. I am looking for advice on the best practice this particular situation to ease maintenance and data access in the future.

My situation is as follows:

This practice offers both products (vitamins, supplements, etc) and services (consultations with practitioners).
Billing Item is the name given to the items (lines) on a particular invoice for a customer.
Product billing items have attributes such as name, cost, description, etc.
Service billing items are related to a particular appointment (and only one), represented by the foreign key AppointmentID.
Each appointment is also related to an AppointmentType table entry (many appointments to one appointment type) which describes the cost, name & description of the appointment (e.g. Its attributes).

In modeling the BillingItem table, in order for it to hold both types of data, it would need to contain redundant columns to hold both types of data (either an AppointmentID or product details). The downside to this being the loss of the ability to constrain data since I must now allow nulls on all columns.

The alternative is to have two tables, ProductBillingItem & ServiceBillingItem. Each of the tables contains the relevant data (either an AppointmentID or product details). The downside to this being that for the retrieval of every single invoice, both tables must be queried and then added together in my application.

I have attached a screenshot of my invoice E/R model so far for the sake of reference. (The billing item table obviously has not been modeled to either of the approaches yet)

Which approach is the best practice for this kind of situation?

If you want the "keep it simple" approach, you can in fact put both types of "items" in the same table, while maintaining proper integrity constraints. Generally speaking, this is what it would look like:

Put an ItemType column in BillingItem, which is a foreign key to some other table that will hold the two (or more) item types you're dealing with.
Add a nullable foreign key column AppointmentID.
Add constraints to the ItemType table which stipulates which columns (including the AppointmentID foreign key) must or must not be null based on the ItemType.

But I would probably prefer to do something like this (and have in the past):

Create one table with all the shared fields (BillingItem in this case), and a composite primary key with ID and Type.
Create tables for each subtype (appointments, physical goods) which have the columns specific to those goods, and have the same primary key columns as BillingItem.
Add a constraint to each subtype table forcing a specific value for Type ('A', 'P', etc.)
Create a foreign key from the subtype tables' primary keys to the BillingItem table's primary key.
Optional: Create a view that flattens/denormalizes the data by UNIONing multiple queries that fetch all the different subtypes and provide nulls for the columns that don't apply. This is just to simplify querying.

Simplified example:

CREATE TABLE BillingItem (
    ID int NOT NULL,
    Type char(1) NOT NULL,
    Cost money NOT NULL,
    CONSTRAINT PK_BillingItem PRIMARY KEY CLUSTERED (ID, Type),
    CONSTRAINT CK_BillingItem_Type CHECK (Type IN ('A', 'P')) --Or make Type a foreign key to a lookup table
)

CREATE TABLE BillingItem_Appointment (
    ID int NOT NULL,
    Type char(1) NOT NULL DEFAULT 'A',
    Start datetime NOT NULL,
    AppointmentID int NOT NULL,
    CONSTRAINT PK_BillingItem_Appointment PRIMARY KEY CLUSTERED (ID, Type),
    CONSTRAINT CK_BillingItem_Appointment_Type CHECK (Type = 'A'),
    CONSTRAINT FK_BillingItem_Appointment_BillingItem FOREIGN KEY (ID, Type) REFERENCES BillingItem (ID, Type),
    CONSTRAINT FK_BillingItem_Appointment_Appointment FOREIGN KEY (AppointmentID) REFERENCES Appointment (ID)
)

CREATE TABLE BillingItem_Product (
    ID int NOT NULL,
    Type char(1) NOT NULL DEFAULT 'P',
    Name varchar(50) NOT NULL,
    ManufacturerID int NOT NULL,
    CONSTRAINT PK_BillingItem_Product PRIMARY KEY CLUSTERED (ID, Type),
    CONSTRAINT CK_BillingItem_Product_Type CHECK (Type = 'P'),
    CONSTRAINT FK_BillingItem_Product_BillingItem FOREIGN KEY (ID, Type) REFERENCES BillingItem (ID, Type)
)


Getting value from a table based on one of the possible foreign keys I have following tables:

car_type (Possible values - hatchback, sedan etc)
car_model (has car_type and name of car model and brand for eg. Toyota Camry)
car_version (has car_model and version of car for eg. ZXI)

car_panel (name of car panel for eg. Front Door (Left))
car_panel_problem (has car_panel and other problem type for eg. Dent)
car_panel_price (has car_panel_problem and price) - price should be based on one or more of car_type, car_model and car_version

Now, I would like to query and know what is the price for a car_panel knowing it's car_version.

if car panel price is there on car version then output it
else if car panel price is there on car model then output it
else if car panel price there on car type then output it
or null

I am thinking of creating car panel price table with nullable car_type, car_model and car_version columns and a price column. Then get the data using 3 queries.

select * from car_panel_price where car_version = <input version>;
select * from car_panel_price where car_model = <input version.car_model>
select * from car_panel_price where car_type = <input version.car_model.car_type>

Is there a better way to define the schema or query?

Please do not downvote if some information is incomplete. I will add it if you ask.

You're ganging the tables together in a very awkward manner. Say you have a table of all car types, a table of all car models, a table of all car versions, a table of all car parts and finally a table with problems (dent, etc). Then you can have one intersection table that relates all of these together with the price to fix the problem. Using simplified data, a row of this table might look like this:

S, TC, Z, FDP, D, $250

This row shows that the price to repair a dent in the front door panel of a Toyota Camry sedan, ZXI is $250.


Copy a row from a table to another with the same columns but different order in a trigger I have two tables:

Document

Columns: {DocumentColumns}

DocumentVersion

Columns: DocumentVersion_id(autoincrement), {DocumentColumns}

{DocumentColumns} are the same in both tables, but their order can differ.

I need that any change that happens in Document, must be added in DocumentVersion, so I created this trigger :

Create trigger copyDocumentVersionTrigger on dbo.Document after insert,update as
insert into [DocumentVersion] select null as DocumentVersion_id,* from inserted

When I want to execute this request I get :

  Msg 206, Level 16, State 2, Procedure copyDocumentVersionTrigger, Line 1 [Batch Start Line 0]
      Operand type clash: bit is incompatible with uniqueidentifier

Because the third column in Document is a bit, and the third one in DocumentVersion is a unique identifier ( and they are totally different columns, that only means that t-sql doesn't care about the named columns in the select, it only cares about the positions).

{DocumentColumns} can be changed dynamically after the creation of the trigger, so I can't just put explicitly the names.

Is there any simple solution to achieve this?

Apparently we don't consider it "simple" to obtain column names of the tables from sys.columns or from Information_Schema.Columns (which handily has a table name column), and then.... hmm, yes, if you construct a "dynamic SQL" statement to call with EXEC() then it won't have access to the [inserted] and [deleted], so you need to copy the row key column to a temporary table... I suppose that isn't simple.  Unless you are doing it one row at a time - then the row key column, is just one variable, and by calling sp_executesql with the appropriate parameter... hmm, again, today may be a headache day.

However... if that's a Microsoft SQL Server error message, then you can again use dynamic SQL to create view objects on one or both of the tables that simply have the column order changed - and you can write in your trigger (I think) if you want,

INSERT DocumentVersionView SELECT * FROM inserted;

INSERT DocumentVersionView SELECT dv.* FROM DocumentView dv JOIN inserted di ON blah blah blah

Whichever is required.  The INSERT will write to the table in the view.

Update: "Of course" DocumentVersionView doesn't have column DocumentVersion_id; the table column gets assigned automatically by your auto-increment.


SSRS Linked Report with allow multiple parameters I'm trying to create a linked report in SSRS to a report that has an "allow multiple values" parameter called CompanyCode.

If i call it with a single parameter it works fine, but I can't figure out how to call it with multiple.  Here's what I've tried:

CompanyCode1,CompanyCode2

and

CompanyCode1

CompanyCode2

and

'CompanyCode1','CompanyCode2'

I' pretty sure you actually have to use a function for that.

From a quick google search, here are a few options:

=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")

Or

=join(Parameters!<your param name>.Value,",")

One thing I remember vividly from my SSRS/ReportBuilder days was that when you're passing the value in from the main to the sub report (and it has multiple values), you pass the param in as "[@paramName]" and not "Parameters!.param.Value"


Shrinking the log file does not reduce size I have a database which has a 350 MB data file (.mdf) and a 4.9 GB log file (.ldf). The recovery model is set to FULL.

When I try to shrink the log file, it's not shrinking.

I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file.

When I ran

DBCC SQLPerf(logspace)

I found that the log size is 4932 MB and Log space used is 98.76%!

Then I tried this command

USE <databasename>;
DBCC loginfo;

Now almost all VLFs are "status 2" which means all are in use.

I tried to take a log backup and then shrink the log file. Shrinking didn't reduce the size.

I changed the recovery model to SIMPLE and tried shrinking again, but this also didn't help.

I checked for open transactions

DBCC opentran (database);

and found that no transaction is open now.

What is stopping me from shrinking the log file? How can I solve this?

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.


Getting duplicate primary key for auto increment column I have a MySQL Innodb table with 'id' as auto increment primary key column.

My Scripts are doing following operations:

insert ignore into tablename set x='a', y='b'
update ignore tablename set x='a', y='b' where id = 'smthing'
delete from tablename where  id = 'smthing'

Now after running the script when I insert something manually (insert into tablename set x='ax', y='bx') I get the following error:

Duplicate Entry for Auto Increment Value.

On check I see that Auto Increment is trying to put the highest possible value of int that MySql will support as new value even and it already exist in db.

The table has two index: id+x, id+y though idk if this is related to this issue.

I reached the max possible value of an int column. Problem resolved by altering table and setting big int than int.

The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215

Reference:
http://help.scibit.com/mascon/masconMySQL_Field_Types.html


AlwaysON AG Readable Secondary is set to 'Yes'. How to connect from the client? For AlwaysON AG, if Readable Secondary is set to 'Yes', do you use the listener name or the secondary replica instance name to connect for read-only purposes?

I know when Read Secondary is set to 'Read-intent only', you are required to specify ApplicationIntent=readonly and the database to be routed to the secondary replica. However, my understanding is that if you set it to 'Yes' instead of 'Read-intent only', you have to explicitly specify the secondary replica instance name instead of the listener name. Is that correct?

If you want to access a secondary replica in an Always On configuration, the Readable Secondary property must be set to "Read-Intent" or "Yes".

If you set "Read-Intent" then your connection string must specify ApplicationIntent=ReadOnly otherwise the connection is rejected. If you set "Yes", your connection is allowed without ApplicationIntent=ReadOnly, however, any non-read queries you run will raise an error.

If you set "No" then all connections to the secondary will return an error. NOTE: That these conditions only apply when specifying an AG-joined database in the connection string. If you connect to a system database or non-AG database then you can connect to a secondary without issue, but you can't access the AG-joined databases. See here for more info.

Lastly, when connecting you have two options. Firstly, you can simply connect to the secondary replica via the normal name for that replica. Secondly, you can connect via the listener name.

When connecting via listener name, if you have configured read-only routing and your connection string specifies ApplicationIntent=ReadOnly then you will be routed to a readable secondary for your connection. If you do not specify ApplicationIntent=ReadOnly or you have not configured read-only routing, then you will connect to the primary replica. See here for more info.


Is table partitioning improving performance? Is it worth it? I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person several years ago, it has around 100 GB of data and it is increasing every 10 minutes (it stores 10 min data from several units -> 144 records per day per device). Several tables have around 10 milion rows.
The point is that I think the main tables have been designed in a way that is not the most efficient or appropiate for the kind of queries that will be usually executed. Now I need to proof if what I say is better than what it is already implemented.
The DB is extensive in number of tables, but the structure can be simplified by the following diagram:

The Date_Id field is generated automatically by a function using the DateTime field. There are two indexes in both tables. The cluster index for each table contains the PK fields in the same order. The second index for the Unit table contains only the Unit_Id field, while the second index in UnitData contains the Unit_Id and DateTime fields in this order.

However, I think the design should be this one:

In this case only a clustered index for the PK fields will be needed. For this DB design the usual query would be something like:

SELECT ud.*
FROM Unit u, UnitData ud
WHERE u.Unit_Id = ud.Unit_Id and ud.DateTime >= 'dd-MM-yyyy'
ORDER BY ud.Unit_Id, ud.DateTime

Now comes the thing that I really don't understand: I've been told that the only reason for having a Date_Id column is to use it as partitioning column for this table. I've asked about the real necessity of having this table partitioned and the asnwer was "to run queries more efficiently when wanting daily or monthly data". I didn't know to much about partitioning before this, so I checked these links:

http://msdn.microsoft.com/en-us/library/ms190787.aspx

How Does Table Partitioning Help?

Improve performance by partitioning

Considering that the ideal query would be filtering by device and datetime, the questions are:

What do you think would be the most efficient and ideal query for the first DB design (with partitioning)?
Do you really think that the most efficent query against the first DB design is better than the second one (the one I wrote above)?
If the previous one was affirmative, do you really think the improvement is worth enough having two extras fields (Id and Date-Id) and an extra index?

Thank you very much!!

Using partitioning is only going to help your query performance if the partitioning scheme is built to serve your specific queries.

You're going to have to review your query patterns and see how they are accessing the table in order to identify the best approach. The reason for this is you can only partition on a single column (the partitioning key) and this is what will be used for partition elimination.

There are two factors that affect if partition elimination can occur and how well it will perform:

Partition Key - Partitioning can only occur on a single column and your query must include that column. For example, if your table is partitioned on date and your query uses that date column, then partition elimination should occur. However, if you don’t include the partition key within the query predicate, the engine can not perform elimination.
Granularity - If your partitions are to big, you won’t gain any benefit from elimination because it will still pull back more data than it needs to. However, make it to small and it becomes difficult to manage.

In many ways, partitioning is just like using any other index, with some added benefits. However, you don’t realize those benefits unless you're dealing with incredibly large tables. Personally, I don't even consider partitioning until my table is over 250 GB in size. Most of the time, well defined indexing will cover many of the use cases on tables smaller than that. Based on your description, you're not seeing huge data growth, so it could be that a properly index table will perform just fine for your table.

I would strongly recommend that you review whether partitioning is actually necessary to solve your issues. One would usually partition a very large table for the purpose of:

Distributing data between different types of disk so that more “active” data can be placed on faster, more expensive storage while less active data is placed on cheaper, slower storage. This is mostly a cost savings measure.
Assisting in index maintenance for extremely large tables. Since you can rebuild partitions individually, this can assist in keeping indexes properly maintained with minimal impact.
Leveraging partitioning for improved archival process.  See sliding windows.


Does the SELECT statement count into DML? Looking at the name Data Manipulation Language (DML) I would assume, that all contained statements are actually for manipulating data.

As far as I know the SELECT statement can only be used to query data, not change it. (Not taking INSERT ... SELECT into account here.)

So is SELECT part of DML and if so, why?

You do manipulate and restrict data: GROUP BY, ORDER BY, TOP, JOIN, lock hints, etc

You just don't change the database state when you do.

It boils down to whether you read "manipulate" to include "change state"


Why is an aggregate query significantly faster with a GROUP BY clause than without one? I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one.

For example, this query takes almost 10 seconds to run

SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1

While this one takes less than a second

SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1
GROUP BY CreatedDate

There is only one CreatedDate in this case, so the grouped query returns the same results as the ungrouped one.

I noticed the execution plans for the two queries are different - The second query uses Parallelism while the first query does not.

Is it normal for SQL server to evaluate an aggregate query differently if it doesn't have a GROUP BY clause? And is there something I can do to improve the performance of the 1st query without using a GROUP BY clause?

Edit

I just learned I can use OPTION(querytraceon 8649) to set the cost overhead of parallelism to 0, which makes makes the query use some parallelism and reduces the runtime to 2 seconds, although I don't know if there's any downsides to using this query hint.

SELECT MIN(CreatedDate)
FROM MyTable
WHERE SomeIndexedValue = 1
OPTION(querytraceon 8649)

I'd still prefer a shorter runtime since the query is meant to populate a value upon user selection, so should ideally be instantaneous like the grouped query is. Right now I'm just wrapping my query, but I know that's not really an ideal solution.

SELECT Min(CreatedDate)
FROM
(
    SELECT Min(CreatedDate) as CreatedDate
    FROM MyTable WITH (NOLOCK)
    WHERE SomeIndexedValue = 1
    GROUP BY CreatedDate
) as T

Edit #2

In response to Martin's request for more info:

Both CreatedDate and SomeIndexedValue have a separate non-unique, non-clustered index on them. SomeIndexedValue is actually a varchar(7) field, even though it stores a numeric value that points to the PK (int) of another table. The relationship between the two tables is not defined in the database. I am not supposed to change the database at all, and can only write queries that query data.

MyTable contains over 3 million records, and each record is assigned a group it belongs to (SomeIndexedValue). The groups can be anywhere from 1 to 200,000 records

It looks like it is probably following an index on CreatedDate in order from lowest to highest and doing lookups to evaluate the SomeIndexedValue = 1 predicate.

When it finds the first matching row it is done, but it may well be doing many more lookups than it expects before it finds such a row (it assumes the rows matching the predicate are randomly distributed according to date.)

See my answer here for a similar issue

The ideal index for this query would be one on SomeIndexedValue, CreatedDate. Assuming that you can't add that or at least make your existing index on SomeIndexedValue cover CreatedDate as an included column then you could try rewriting the query as follows

SELECT MIN(DATEADD(DAY, 0, CreatedDate)) AS CreatedDate
FROM MyTable
WHERE SomeIndexedValue = 1

to prevent it from using that particular plan.


horizontal partitioning or multiple tables? I'm developing an application and need to propose a database schema.

We plan for a MySQL database with millions of records which should be distributed over multiple machines for performance and availability reasons.

The schema contains an element Category and we will have from 10 to 100 different categories.  The number of categories will vary during the system's lifetime.  Each query will be relevant for a single category only, and must be ACID-compliant.  This means several queries for different categories can (and should!) run in parallel.

I see the following options:

Define a single table and partition it horizontally.
Use separate tables for each category.

Since I don't have experience in administering database servers and we only have a small amount of categories, I'm wondering which of the two options is easier to administer and what are their other advantages and disadvantages.

I just ran across this old question.  In general, administering 1 table would be easier than administering 10-100 tables.  Imagine trying to ALTER TABLE across 100 tables!

Just because you have millions of rows doesn't mean you'll need multiple servers.  Good queries, good indexes, good disks, etc., are all performance items.  Fewer servers are also easier to maintain.

What did you end up doing?


SQL trigger when a row is updated question My problem is as follows.

I an creating a MySQL database for a company that whenever a row is updated on the DB i want to have a trigger that calls a cURL request to transfer the updated information to our clients.

Is doing this the best way to do this? or is it better to do this in the php script that transfers the data to us?

Thank you for your time and experience.

first create one temp table , and create records whenever your updating row. for example if you are updating one row with id=24, save this id as refference id into temp table.then using Php  if temp table contains any records call curl request and delete temp records once curl request is completed. by using cron jobs run this php scripts repeatedly


SQL Server - How to compress XML inside Trigger? I am using SQL Server Standard Edition 2012.

When doing Audit using Trigger, the fields of the audited row(inserted or deleted) are being inserted into the table in XML Format. However, as observed, the space occupied is 10 times the original row (checked using sp_spaceused command, suspected due to the tags).

I am wondering, is it possible to compress XML inside the trigger (means using SQL)?

EDIT:
Here's an example of the XML (there are around 50 columns in total). As observed, this really occupies A LOT of spaces.

<t>
  <MyKey>1</MyKey>
  <ClientKey>10</ClientKey>
  <DocNum>doc number</DocNum>
  <Source></Source>
  <DocType>doc</DocType>
  <GLDate>1900-01-01T00:00:00</GLDate>
  <GLDesc></GLDesc>
  <FY>2119</FY>
  <Period>4</Period>
  <CurrKey>-1</CurrKey>
  <InvoiceDate>1900-01-01T00:00:00</InvoiceDate>
  <InvNum></InvNum>
  <TaxInvNum></TaxInvNum>
  <CreatedBy>-1</CreatedBy>
  <CreatedDate>1900-01-01T00:00:00</CreatedDate>
  <UpdatedBy>23</UpdatedBy>
  <UpdatedDate>1900-01-01T00:00:00</UpdatedDate>
  <VoidBy>-1</VoidBy>
  <VoidDate>1900-01-01T00:00:00</VoidDate>
  <RejectBy>-1</RejectBy>
  <RejectDate>1900-01-01T00:00:00</RejectDate>
  ...
  ...
  ...
</t>

While XML is certainly more bloated than native datatypes, it is not as bad as most people would assume, or even incorrectly calculate, as long as you are using the XML datatype. The XML datatype (in SQL Server) has internal optimization whereby it stores a dictionary of element and attribute names and a tree structure for the document. Meaning, element names are not doubled, and a name repeated 100 times is listed only once. Also, all structural whitespace (i.e. that's not part of a value) is removed.

Still, even with this optimization, you still run into two issues with XML:

column names are present in each document (i.e. each row of the audit table has to repeat the column names, whereas those column names only exist once in the meta-data that defines the Table that is being audited).
all data is serialized to strings (well, unless you're using "typed" XML, which means you have an associated XML Schema / XSD, but how often does that happen?). Sometimes this can actually help, such as when you have INT values that are positive and a single digit: 0 - 9. Or it can be equivalent when the values are -9 up through -1 and 10 up through 99, since those two ranges represent the same 4 bytes as the INT. If you are wondering why my maths seem so far off, keep in mind that XML data is UTF-16 (Little Endian), meaning: 2 bytes (well, for most of the characters you would regularly deal with, but 4 bytes per character for Supplementary Characters). So the -9 is 2 characters which is 4 bytes, same as 99. But then 3 characters is 6 bytes and it just goes up from there.

The date values are DATETIMEOFFSET which is 8 - 10 bytes. The values shown in the XML have 0 fractional seconds which would be 8 bytes. The values in the XML are in the form of 1900-01-01T00:00:00. That is 19 characters, which is really 38 bytes.

As far as compression goes, you have two choices:

If you are using SQL Server 2016 or newer, you could compress the XML values using the built-in COMPRESS function, and then DECOMPRESS. These two functions are the same as GZip and GUnzip, respectively.
If you are using SQL Server 2005, 2008, 2008 R2, 2012, or 2014, you could get the same GZip algorithm via SQLCLR. This can be coded somewhat easily, or you can just grab the SQL# SQLCLR library (which I wrote, but these two functions are in the Free version), which contains the Util_GZip and Util_GUnzip functions.

In either case, you can save quite a bit of space by compressing the values. However, you also lose the ability to use define any XML Indexes on this data, and you can't query it directly using XML functions such as .value or .nodes. Querying the data can still be done, but you would first need to use either DECOMPRESS or Util_GUnzip (or similar SQLCLR scalar UDF) on the value to turn it back into usable XML.

Regarding attribute-based XML vs. element-based XML: in most cases you are better off with attribute-based as it does take up less space than element-based, and often performs a little better. However, the size difference between the two is much more obvious when the XML is stored as plain text, whether in VARCHAR / NVARCHAR or a text file encoded as ASCII, UTF-8, or UTF-16 / Unicode. But when stored in SQL Server in an XML datatype (variable or column), the internal optimization reduces the required space, especially if there are element and/or attribute names that are repeated quite often.

For example, take all of the rows in master.sys.all_columns.

DECLARE @ElementBased XML;
SET @ElementBased = (
                     SELECT * FROM master.sys.all_columns FOR XML PATH('Row')
                    );

DECLARE @AttributeBased XML;
SET @AttributeBased = (
                       SELECT * FROM master.sys.all_columns FOR XML RAW('Row')
                      );

SELECT @ElementBased AS [ElementBasedXML],
       @AttributeBased AS [AttributeBasedXML],

       DATALENGTH(@ElementBased) AS [ElementBasedBytes],
       DATALENGTH(@AttributeBased) AS [AttributeBasedBytes],
       ((DATALENGTH(@AttributeBased) * 1.0) / DATALENGTH(@ElementBased)) * 100
               AS [XmlAttributeSizeRelativeToElementSize],

       DATALENGTH(CONVERT(NVARCHAR(MAX), @ElementBased)) AS [ElementBasedNVarCharBytes],
       DATALENGTH(CONVERT(NVARCHAR(MAX), @AttributeBased)) AS [AttributeBasedNVarCharBytes],
       ((DATALENGTH(CONVERT(NVARCHAR(MAX), @AttributeBased)) * 1.0) /
         DATALENGTH(CONVERT(NVARCHAR(MAX), @ElementBased))) * 100
               AS [NVarCharAttributeSizeRelativeToElementSize]

       -- The following 2 fields require either SQL# ( https://SQLsharp.com/ )
       -- be installed, or using SQL Server 2016 (or newer), in which case you
       -- would use the COMPRESS function.
       ,DATALENGTH(SQL#.Util_GZip(CONVERT(VARBINARY(MAX), @ElementBased)))
               AS [ElementBasedGZippedBytes],
       DATALENGTH(SQL#.Util_GZip(CONVERT(VARBINARY(MAX), @AttributeBased)))
               AS [AttributeBasedGZippedBytes]

Returns the following (minus the two fields with the actual XML values):

ElementBasedBytes                           1717896
AttributeBasedBytes                         1544661
XmlAttributeSizeRelativeToElementSize            89.92  (rounded-up)

ElementBasedNVarCharBytes                  11778162
AttributeBasedNVarCharBytes                 6923728
NVarCharAttributeSizeRelativeToElementSize       58.79  (rounded-up)

ElementBasedGZippedBytes                     193629
AttributeBasedGZippedBytes                   146881


Postgresql connection refused from java connection class public class PostgreSQLJDBC {
    public static void main(String args[]) {
        System.out.println("looking for db connection");
        Connection c = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager //139.59.175.170 eller 127.0.0.1/32

                    .getConnection("jdbc:postgresql://178.62.33.183:5432/steinar");
        } catch (SQLException e) {
            System.out.print("sql exception thrown ");
            e.printStackTrace();
            System.err.println(e.getClass().getName()+": "+e.getMessage());
            System.exit(0);
        }
        catch (ClassNotFoundException e){
            e.printStackTrace();
        }
        System.out.println("Opened database successfully");
    }
}

So this is my simple connection class in java. My original error message complained that the ip-adress from my computer was not in the pg_hba.conf file:

FATAL: no pg_hba.conf entry for host "ip", user "user", database "db",

All the connection profiles are set to trust. Then i added it along with my computer username, and now i get the following error message:

Connection to 178.62.33.183:5432 refused.
Check that the hostname and port are
correct and that the postmaster is
accepting TCP/IP connections.

Port and listenadresses are set to 5432 and '*' in the postgresql.conf file

# IPv4 local connections:
host    all             myUserName         myIp            trust

Line i added in my pg_hba.conf file. Every time a change this file, psql command stops working:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

You are connecting using java via TCP/IP and using psql via named pipe. The database server uses pg_hba.conf configuration file for checking connections. This file, when changed, does not require the database to restart, but it should be notified for reloading it. In that file you need at least one line for psql executed locally, and one line for java connection. The line you showed starts wish host, so this is for TCP/IP connection. Before that, you might add a line for psql like local all all trust. The first all is the database name, the second one is the user name.

About the connection: your database is configured correctly, so maybe, your TCP connection is blocked by a firewall. Does you server really have that IP address, or is there any firewall that forward connection from that IP? In the former case, test your java connection from the server, in the latter check your firewall settings.