What are the arguments against or for putting application logic in the database layer? NOTE The audience of programmers.se and dba.se is different, and will have different viewpoints, so in this instance I think it's valid to duplicate What are the arguments against or for putting application logic in the database layer? on programmers.se.

I couldn't find discussion on dba on this already, and the original post says it all, so:

  Most software developers want to keep
  application logic in the application
  layer, and it probably feels natural
  for us to keep it here. Database
  developers seem to want to put
  application logic in the database
  layer, as triggers and stored
  procedures.

  Personally I would prefer to keep as
  much as possible in the application
  layer to make it easier to debug and
  keep the responsibilities of the
  layers separate.

  What are your thoughts on this, and
  what should or should not be ok to
  implement in the database layer?

N.B. I'm not the OP for that question, but left the original wording intact.

Assorted thoughts...

Your database code will outlive your application client technology.
Think of ADO.NET -> Linq -> EF as well as assorted ORMs. Whereas you can still run SQL Server 2000 code from last millenium against all of the above client technologies.

You also have the multiple client issue: I have .net, java and Excel. That's 3 sets of application logic.

"Business logic" shouldn't be confused with "data integrity logic". If you do have clients starting transactions and doing assorted checks, that's a lot of db calls and a long transaction.

Application logic doesn't scale for high data volumes. We have 50k rows per second using stored procs. A sister team using Hibernate can't get one per second


Comparing 2 functions for any differences in the result set Recently, we've done some optimizations to the functions and now the BI Manager has tasked me with a request to compare the result of the 2 functions as below making sure the row counts match, along with the exact values of each field. In the same request he is expecting this to be a while loop that will loop all the YTD ranges dating back 20 years. (not sure if this is really practical in order to test the validity). However, the while loop is not a mandate for the same. Is there a way to compare the result of the 2 functions without hard coding the ranges and automating the same like for e.g declaring a variable and then based on the date range calculating the next date range etc without the user intervention.

Last but not the least a script that would validate there are no differences in the results of both the functions which means that the optimization did work. I am thinking of using except clause but I am not sure if it would work with functions. Also, if that won't work what else would be a possible way of doing it.

I would really appreciate for all the help. It is a big impact for the company if I could complete this task. However, I am not even sure if this is something that should be even handled by a DBA.

Functions
Original Function: report.fnReportDealCore_original(@StartDate, @EndDate, -1)

New Optimized Function: report.fnReportDealCore(@StartDate, @EndDate, -1)

Example of Run Dates dating back 20 years

1/1/1997 – 1/31/2997
2/1/1997 – 2/28/1997
3/1/1997 – 3/31/1997
….
1/1/1998 – 1/31/1998
2/1/1988 – 2/28/1998
…
Through 9/30/2017

Thanks

I believe this should help you.  First I create a counter variable that's equal to the total months you need to evaluate, in this case you specified January 1997 to September 2017. Next I create the start and end dates based on that counter.  Doing it this way allowed me to work around having special logic for the last day of every month (and leap years).  We throw it in a while loop based on the counter and use except logic to get any results that don't match.

Edit: I wanted to improve this so that there weren't four places where dates are hard coded, but since we know the dates for this particular question, I wanted to keep it as clean as possible and not add more date manipulation functions.

declare @StartDate date, @EndDate date, @counter int = (select datediff(mm,
'1/1/1997', '9/30/2017'));
while @counter >= 0
begin
    set @StartDate = dateadd(mm, @counter, '1/1/1997');
    set @EndDate = dateadd(mm, @counter, '1/31/1997');

    --Finding results in the original that are not in the new
    /*
    Select x, y, z
    FROM report.fnReportDealCore_original(@StartDate, @EndDate, -1)
    EXCEPT
    Select x, y, z
    FROM report.fnReportDealCore(@StartDate, @EndDate, -1)
    */

    --Finding results in the new that are not in the original
    /*
    Select x, y, z
    FROM report.fnReportDealCore(@StartDate, @EndDate, -1)
    EXCEPT
    Select x, y, z
    FROM report.fnReportDealCore_original(@StartDate, @EndDate, -1)
    */

    set @counter -= 1;
end


Maximum size transaction log and impact I was doing some research on what the recommended maximum size for the transaction log, but I cannot find any substantiate or documented answer. I learned that a log size of about 25 - 50 percent of the DB is not uncommon. I also know that you want to set a recommended number of VLFs and must tune autogrowth to prevent VLF fragmentation. This can slow down your queries and log backups.

The only thing I cannot find is what would be a recommended maximum size in relation to your database size or possibly workload. Of course, you probably don't want your log file to grow beyond the database size, but what would be the exact impact? Of course, log backups will take longer if the log file is huge AND the log file is filled for example 80 percent.

But what if I have a db of 1 TB and a log file of (hypothetically :-) ) of 2 TB, which would be filled for 10 percent. I can imagine that log backups will take longer because the log backup has to scan the complete log maybe? If your VLFs are not fragmented, then there is not really a problem right?

Log backups are critical to SQL Server and let's say you would create a log disk that is 2 TB, make the log file 500 GB. Now, the backup fails for a few hours, the log fills for 1.5 TB. Your log backup will take longer, much longer, sure. We have a performance impact because of the autogrowth events, yes, but we made sure the DB does not stop. In such a scenario, when the backup is fixed and the first log backup has run, the log filing is low, but the log is unusually large. Now, I cannot think of any critical reason to get the log file back to the normal ( let's say that 500 GB). Of course you don't want to do that during production hours of course due to locking. However, I cannot imagine that there would be absolutely no impact with such a huge log file (which would be mainly empty).

Edit:

To clear op my a little bit;  my question is actually what is the impact of a scenario where a transaction log is huge (let's say 3x the size of the db) but log backups are taken regularly and it will normally be filled for only about 10%. Maybe slower log backups, possible really bad VLF fragmentation?

I don't want to do this. I only want to understand what the impact will be when I find a case where that is the issue (yes, there people who do this...). Of course I want to get it back to normal, but based on the impact, I can determine a priority. High; fix it now not matter what downtime, medium; fix it within a week because it is not critical, low; fix it during the next maintenance window.

Again; iam not interested in how to size it, but what would be the impact in a scenario where the file is already huge AND regular log backups are taken AND it is only filled for max 10% or so. Also, I want to be able to no only say that this is a bad, bad, bad, bad idea, but also why. Hope this helps to clear up my question. Btw, I tried to reproduce this scenario in my home lab, but it is difficult to see what the impact would be.

You are looking at the problem wrong.

Assuming full recover; the size of your transaction log (T-log) is determined by your work load and how often you take log backups.

It is completely reasonable to have a 3 TB database with a 1 GB T-log if there is a lot of old data, that has simple reports run against it occasionally.  Backup the T-logs hourly and it may never grow.

In most cases you will want to take T-log backups every one to four hours.  How much space is required to hold the T-logs for your heaviest work load in that time defines how big your t-log should be (plus a bit extra just in case).

If you have a virtual server, use a separate drive for for the T-logs.  For a brand new build, in most cases I start with 10GB of disk and start the ndf file at 250MB, watch it grow (auto grow 50MB) for a few days or weeks and then decide "What is the right amount of VLFs for my requried log size?" then I adjust them.  I recheck about every 3 months.

See my post about VLF files here

Edit

  not matter what downtime

There is no down time for the fix, you want to do it off peak hours partly to reduce resource competition, but mostly because you want to get the ndf file as small as possible before you start a planned growth.  It is easiest to do this when there is little or no activity.

  tune autogrowth to prevent VLF fragmentation

Not really sure what you mean by this.  VLF files are written to in sequence. One gets full, the next available in line gets written to.

  I can imagine that log backups will take longer because the log backup

Maybe a minuscule impact, but nothing worth worrying about.

  impact of a scenario where a transaction log is huge

If you are not currently having performance issues, impact is mostly limited to restore times.  Which is more related to the number of VLFs as opposed to the size of the file.

If you are having performance issues, they are likely tied to indexes or something data or procedure related or possibly hardware.  If it the performance issue is index related, you want to fix it and get regular index maintenance started, then address VLF files (because heavy index maintenance is hard on t-logs, the first time)

In short, do VLF maintenance when you have the opportunity. It has been ugly for a long time.  You are not going to see any magical improvements in daily performance.


Unable to trace "Arithmetic overflow error converting numeric to data type numeric" error in profiler I have a .net application. Its trying to create tables, views and insert/update data into many tables. while application running i got an error.

I tried to trace the query in SQL Profiler, but i didn't find any error. Is there any way to track the Error statement or Is there any other events i need to add in profiler.

I tried already following events (exception):

RPC: Completed
RPC: started
SP: started
SP: Completed
SP: Stmtstarted
SP: StmtCompleted
SQL: Batchstarted
SQL: BatchCompleted

Using the follow link you can set everything up to track the exception This link

In short: Set up a blank trace, add the following events :

Exception under Errors and Warnings
RPC: Completed under Stored Procedures
RPC: Starting under Stored Procedures
SQL: BatchCompleted under TSQL
SQL: BatchStarting under TSQL

From there on, you can see the error popping up in the trace, the query above the error will be your query that's giving the error.

If that's not giving you the exception, i'd debug the .NET code to get the SQL code that's being executed, if you have access to the source code.


How does SSMS connect to a server's database without the instance name? I have a question that I am confused about. I am also new to database stuff as well so this question may be basic.

For example, at my work, we use SSMS to connect to our server's databases.

When connecting from SSMS, we enter in (server_name\instance). Like for example PC100\SQLexpress.

But for some, only the server is entered and not the instance along with it. How does this work? I thought in order to connect, we always need to type an instance after the server name?

Like for example, some we need PC100\SQLexpress in order to connect to the server's databases.
And then for others we just need to type in PC200 to connect, and not needing to type the instance.

Can anyone explain?

For SQL Server the default instance of a  is SQLServerName\MSSQLSERVER.  However, since MSSQLSERVER is the default instance name that is never displayed.

A SQL Server using the default instance is only referenced by the SQLServerName.

Actually, you can install SQL Server Express and give it the instance name of MSSQLSERVER and you will get the default instance.  Of course, any server can only have one default instance.

That is probably most useful when installing a SQL Server Express and you do not want to have SQLExpress for the part of the name.

When installing a default instance of SQL Server Express at the Instance Configuration page, you must choose the Named Instance radio button and type in the default instance name of MSSQLSERVER.

Just take the steps to name the default instance name MSSQLServer (which will not appear).  Any other instance name on the computer needs a unique name, such as SQLServerName\Alpha, SQLServerName\Beta, et cetera.

Note: Installing SQL Server Express will, by default behavior, put the SQLExpress into the server name.  But that is not the default name. Whenever there is \Sometext name, then that is called a named instance.


Elastic queries available in SQL Server 2017 Are elastic queries available in SQL Server 2017?

Can see them documented for Azure SQL:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview

but nothing for SQL Server. My assumption is that it's not available.

As the doc says its still in preview with limitations. So no, sql server 2017 on premise does not have that feature as of now.

Once it is in GA, it might be available in sql server (possibly with a Service pack or in newer version of sql server).

Other features like Query Store and adaptive join, etc were first avilable in Azure and then they got into onpremise SQL Serve, so I believe if a feature is featured in Azure, it will eventually get into onpremise sql server may be through a service pack or new version of the software.


SQL Server Express support for TLS1.2 I have installed SQL Server 2014 Express Edition ( 12.0.2000.8 x64 ) on a Windows server 2012 machine.

Because of the POODLE vulnerability, our IT department is going to disable TLS 1.0 and all other previous security protocols on this server.

Where can I find a newer version of SQL server Express Edition that can support OR work with TLS 1.2?

You don't strictly need a newer version of SQL Server; however as a general thing I always prefer to be using the most recent version (especially in this case, because in SQL Server 2016 SP1, you get all kinds of new features in Express Edition).

You do need to apply one of the following updates, though (I go through more details in this blog post). They are listed in my order of preference:

Service Pack 2 (+ SP2 CU#3, ideally)
Service Pack 1 + SP1 GDR (minimum) (+ SP1 CU#10, ideally)
RTM CU#14
RTM GDR update - technically this is all that should be required, but you are missing out on so many other fixes from over two years of updates, this is "the absolute, I don't recommend it, bare minimum" IMHO

That's the gist of it; if you want to get into all the nitty gritty, here is a KB article I'm stealing from Max's deleted post.


Find specific query in Query Store I have enabled Query Store on a database. I have a specific query which I want to track. I have a lot of details about the query from sp_BlitzCache (like SQL Text, SQL Handle, SQL Hash, Plan Cache Handle/Hash, etc...).

Am I able to search Query Store with the info from sp_BlitzCache to track down the query there? I want to force a specific execution plan because the query suffers parameter sniffing issues.

One way to do this is to query the Query Store views directly for the info you've gained from the plan cache:

SELECT
    qsq.query_id,
    qsq.last_execution_time,
    qsqt.query_sql_text
FROM sys.query_store_query qsq
    INNER JOIN sys.query_store_query_text qsqt
        ON qsq.query_text_id = qsqt.query_text_id
WHERE
    qsqt.query_sql_text LIKE '%your query text%';

The sys.query_store_query table also has some of the other fields (query_hash, last_compile_batch_sql_handle, statement_sql_handle, etc), which might find the query you're looking for more reliably.

Basically the point of that is to find the "query id" in Query Store, as you'll need that to find plans for it and force one.

Once you have the query_id in hand, you can go to the UI in SSMS and find the query using the "Tracked Queries" node:

From there you can click on the plan you want, and click "Force Plan" to force it:


MongoDB connection limit I´m testing around with MongoDB and NodeJS. I´m stuck with the problem that I can create max 4899 connections to MongoDB. I´ve set the poolSize in the NodeJS Mongo driver to 40000.
Also I´ve set the needed ulimits of the mongo server.

Does anyone have an idea why the mongo server refuses new connections as soon
as 4899 are reached? The problem is definitely on th DB server side and not on client side.

I´m using the newest versions of all components (nodeJS V8, Mongo, Node mongo native driver)

limits configuration:

/etc/sysctl.conf
net.ipv4.ip_local_port_range = 1024 65000

/etc/security/limits.conf
mongodb         hard    nofile          1048576
mongodb         soft    nofile          1048576
mongodb         soft    nproc           1048576
mongodb         hard    nproc           1048576
mongodb         hard    stack           16384
mongodb         soft    stack           16384
mongodb         hard    memlock         unlimited
mongodb         soft    memlock         unlimited

/lib/systemd/system/mongodb.service
# (file size)
LimitFSIZE=infinity
# (cpu time)
LimitCPU=infinity
# (virtual memory size)
LimitAS=infinity
# (open files)
LimitNOFILE=500000
# (processes/threads)
LimitNPROC=500000

mongo serverstats:

db.serverStatus().connections
{
        "current" : 4899,
        "available" : 395101,
        "totalCreated" : NumberLong(5279)
}

Finally found the solution by myself:

in the systemd service configuration file /lib/systemd/system/mongodb.service
set the following values to override the systemd tasklimit:

# (disable systemd tracking of number of tasks)
TasksAccounting=false

# (disable systemd tasklimit) possible values: infinity, a absolute number, or percentage of system wide limit
TasksMax=infinity


Are two logically equal indices physically the same index? I have a table with this definition:

CREATE TABLE public.delete_operation
(
  id bigint NOT NULL,
  parent_id bigint,
  pdu character varying,
  progress smallint NOT NULL DEFAULT 0,
  description character varying,
  state character varying(31) NOT NULL,
  status character(1) NOT NULL DEFAULT 'A'::bpchar,
  CONSTRAINT delete_operation_pkey PRIMARY KEY (id),
  CONSTRAINT "Ref_delete_operation_to_delete_operation" FOREIGN KEY (parent_id)
      REFERENCES public.delete_operation (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Index on id created implicitly.

Then I want to create an index on parent_id (which refers to same id column with already created index).

My question is: would it be physically same index with different names or will it double this index ?

How can the 2 indexes be physically the same? The values of id could be more example:

1,2,3,4,6,7,8,9

while the parent_id values could be:

NULL,1,1,8,NULL,NULL,2,NULL

So the answer to the question is: No, they would be two completely separate and different structures. It doesn't make any other sense. Even if the parent_id were all, one by one, identical to the id values, it still would not make any sense either. Because the server cannot know or be sure that the values will be always identical. Rows can be updated and new rows with different id - parent_id values can be inserted. So two separate indexes have to be used.


Mongo initial sync failing with InvalidSyncSource I'm trying to add a new 3.4 (RocksDB engine) member to a ~2 TB replica set currently consisting of 3.2 (WiredTiger engine) members and running into problems with the initial sync. Because of the different storage engine, the common suggestion of using a filesystem snapshot to bootstrap the new member won't work.

Replication starts fine, but eventually runs into InvalidSyncSource errors. Strangely enough, the log entries appear to claim that the OpTime of the primary server is more than 30 seconds behind that of the secondary, which sounds impossible to me. I have confirmed that the primary server remained in state PRIMARY throughout the entire operation, and that the clocks on all hosts are in sync.

First indication that something's not right (initial sync source is db-primary.xxxxxxxxxxxxxxx:27017):

2017-04-28T10:58:52.183+0200 I REPL     [replication-5] re-evaluating sync source because our current sync source's most recent OpTime is { ts: Timestamp     1493369879000|2, t: -1 } which is more than 30s behind member db-secondary.xxxxxxxxxxxxxxx:27017 whose most recent OpTime is { ts: Timestamp 1493369931000|2, t: -1 }
2017-04-28T10:58:52.183+0200 I REPL     [replication-5] Finished fetching oplog during initial sync: InvalidSyncSource: sync source db-primary.xxxxxxxxxxxxxxx:27017 (last optime: { ts: Timestamp 0|0, t: -1 }; sync source index: -1; primary index: -1) is no longer valid. Last fetched optime and hash: { ts: Timestamp 1493369932000|1, t: -1 }[3606797002863800866]
2017-04-28T10:58:52.183+0200 E REPL     [replication-5] Error fetching oplog during initial sync: InvalidSyncSource: sync source db-primary.xxxxxxxxxxxxxxx:27017 (last optime: { ts: Timestamp 0|0, t: -1 }; sync source index: -1; primary index: -1) is no longer valid

I'm not sure if the "Timestamp 0|0" in the 2nd & 3rd lines here are meaningful but they also look strange to me. Mongo happily continues to sync until it has copied all databases, after which this happens:

2017-04-29T01:16:52.317+0200 E REPL     [initial sync-0] Initial sync attempt failed -- attempts left: 9 cause: InvalidSyncSource: sync source db-primary.xxxxxxxxxxxxxxx:27017 (last optime: { ts: Timestamp 0|0, t: -1 }; sync source index: -1; primary index: -1) is no longer valid
2017-04-29T01:16:53.379+0200 I REPL     [initial sync-0] sync source candidate: db-primary.xxxxxxxxxxxxxxx:27017

What could be causing the initial sync to fail in this way? The only thing I can come up with is that an attempt to read the OpTime from the primary hangs for quite a while and returns late enough to be compared to a much more recent reading from the secondary, but I would think/hope that Mongo would account for that. In addition, there is a reliable 1 Gbit network link between all hosts involved, with no indication that anything went wrong on the network level.

Regardless of possible explanations, can anyone suggest an approach that might prevent this issue from occurring?

Note that I have succesfully performed this operation on a much smaller (~300 GB) replica set, but that might have just been lucky if the problem occurs randomly.

This looks like known bug what is gonna be fixed in version 3.4.5 what hopefully comes out before end of this month.


Mysql binary logs are not deleted after expiration I have 3 Mysql 5.6 nodes in one galera cluster in the first DC and 3 MariaDB 10.3 nodes in another galera cluster in the DR site. One node on the 1st DC is the master for one slave node on the DR site. In all nodes binary logs are enabled to be ready anytime act as masters.
The problem is that despite the fact that expire_logs_days = 2 binary logs are not deleted in any node . I have tried to stop one node, delete everything under /var/lib/mysql/ and restart but nothing changed. I still delete the logs by hand using PURGE BINARY LOG commands. Any idea why this cloud be happening?

I have checked expire_logs_days with mysql command and I also have it in the my.cnf

Finally the binary logs was rotating normally. The problem is that expire_logs_days=2 means 2 days excluding the present one or something like that, probably a translation of days in minutes. :)
The last mysql mariadb versions set binary log expiration in minutes.


Lost postgreSQL password in Windows installation I have postgresql 9.2 installed on a windows XP machine. I have lost the postgres password.

I looked at answers given to a similar question asked 6 months ago. The answer given there required the editing of a file pg_hba.conf however I cannot find that file anywhere. Windows search doesn't find it.

I have tried uninstalling the postgreSQL installation but that seems to leave behind the password and security settings as on installing again the step to set the postgres password is missed out.

Help please?

Having previousley tried to delete PostgreSQL and reinstall it I realise I had neglected to delete the files the uninstall leaves behind.  In a Windows XP environment these are found in C:/Program Files/PostgreSQL/9.2  Deleting the remaining files in the directory 9.2 and then deleting the directory PostgreSQL allowed me to perform a completely new install of PostgreSQL without any failures.  Of course the new installation process requires input of new passwords, this time I made a note of them :)


Extended events vs SQL Audit - performance implications I would like to setup an audit trail kind of system on my database to monitor for UPDATE/INSERT statements on a specific table with very high activity. I have two options in front of me: using the SQL Server built-in Audit system or go with Extended Events.

As SQL Server Audit internally uses Extended Events, I assume there will be some kind of overhead when I use Audit instead of Extended Events directly.

Is there any way I can do some testing to analyze which system is impacting the server more? If I could know what actually happens when any XE session is created it would help me in analyzing the impact on the server.

We considered triggers and left out that option because of overhead. But that was just decided based on information from the Internet.

One benefit of the Audit that comes to mind is that it will automatically record who turns it on and off, XE won't do that out of the box (though you might find an event that tracks XE stop/start). You may also find that the two capture different data, depending on exactly what you want.

Regarding doing some testing, you would need to have a database backup, capture a trace of the application under load, and then restore the copy while doing a replay / replay with audit / replace with XE and comparing performance data.

Which performance data? It's up to you. For some ideas - Linchi Shea did a comparison between Audit and Trace by focusing on Transactions/sec, while Kehayias did a comparison between Trace and XE by focusing on batches/sec and overall replay runtime.

http://sqlblog.com/blogs/linchi_shea/archive/2012/01/24/performance-impact-sql2008-r2-audit-and-trace.aspx
http://sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events

I'd encourage you to read both and their comments because you should know that no matter what you do it will be open to interpretation. It's difficult to get an apples for apples comparison. Also, a trace/replay can fail to simulate load properly - for example when your application is doing lots of bulk loads from disk files that no longer exist.

But the important thing is that you try at least one thing, so you can justify your decisions, and also blog about it for the rest of us.


How does PostgreSQL decide what to name its tablespace directories? Using the psql CLI tool with a PostgreSQL 9.6.1 server running on localhost, I created a new tablespace with

create tablespace my_tablespace location /tmp/data;

and found that this created a directory called PG_9.6_201608131.

I can see that 9.6 is the version, but what is the last string of numbers? I'd like to use this to create databases in a Makefile, so it will be helpful to know how that number is derived (rather than just hard-coding it).

I look at the source code of postgres9.6.

/* tablespace.c */

create_tablespace_directories(const char *location, const Oid tablespaceoid)
{
    ....
    location_with_version_dir = psprintf("%s/%s", location,TABLESPACE_VERSION_DIRECTORY);
    ....
    if (mkdir(location_with_version_dir, S_IRWXU) < 0)
    {
        ....
    }
}

/* catalog.h */

#define TABLESPACE_VERSION_DIRECTORY    "PG_" PG_MAJORVERSION"_" \
                                CppAsString2(CATALOG_VERSION_NO)

/* catversion.h */

#define CATALOG_VERSION_NO  201608131

I found PG_9.6_201608131 is made up of three parts.

  'PG_'  +  PG_MAJORVERSION  + '_' + CATALOG_VERSION_NO

In PostgreSQL9.6

PG_MAJORVERSION is 9.6

CATALOG_VERSION_NO is 201608131

PG_MAJORVERSION and CATALOG_VERSION_NO both are macro definition in c, it won't change.


Postgres ignoring GIN index for LIKE query? I am working in Postgres 9.4. I have a table and index like this:

                                              Table "public.title"
             Column              |          Type           |                     Modifiers
---------------------------------+-------------------------+----------------------------------------------------
 id                              | integer                 | not null default nextval('title_id_seq'::regclass)
 pty_addr                        | character varying(1000) | not null

I have a GIN index on the pty_addr column:

"title_pty_add_15f367_gin" gin (pty_addr)

My query is this:

EXPLAIN ANALYZE SELECT * FROM "title" WHERE  pty_addr LIKE '%FOO%';

This query is performing a sequential scan, taking 1.2 seconds:

                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on title  (cost=0.00..193271.60 rows=599 width=4446) (actual time=1.191..1220.640 rows=7945 loops=1)
   Filter: ((pty_addr)::text ~~ '%FOO%'::text)
   Rows Removed by Filter: 3592055
 Planning time: 0.330 ms
 Execution time: 1220.992 ms

Why isn't this query using the index?

If you want to index for LIKE which are not prefix queries, or for ILIKE queries, then you should look into the pg_trgm extension, giving an index that would look like:

"title_pty_add_15f367_gin" gin (pty_addr gin_trgm_ops)

Also, there have been substantial improvements since 9.4.  You will be doing yourself a favor to upgrade to at least 9.6.


SQL Server does not exist or access denied from a VMWare guest I've made the configuration to allow remote connections to SQL Server 2012, however I still cannot connect to it from a VMware Workstation Guest (using both NAT and Bridge).

I can ping the host machine from the guest VM when using NAT (the same is not true for bridge config).

Be sure that the SQL Server IP address is listed in the IP Addresses page in TCP/IP protocol properties ( You can access it from SQL Server Configuration Manager )

Also be sure to restart SQL Server service after you do changes.


Help using regular expressions I have two ORACLE tables.

First table:

ID    l_name f_name belong_to

123   Smith  John        A Club

456   Jones  Mary        The Club, Your Club, My Club

789   Moore  Sue         The Club, A Club, Some Club

007   Wills  Art         My Club, Some Club, Two Club

Second table:

Club_ID  Name

1            A Club

2            The Club

3            Your Club

4            My Club

5            Some Club

6            Two Club

The idea is to link these tables based on the name of the club in the 'name' field of the second table AND have a row of output showing the person's name with each club he or she belongs to, like this:

l_name    f_name    belong_to

Smith     John      A Club

Jones     Mary      The Club

Jones     Mary      Your Club

Jones     Mary      My Club

Moore     Sue       The Club

Moore     Sue       A Club

Moore     Sue       Some Club

Wills     Art       My Club

Wills     Art       Some Club

Wills     Art       Two Club

The belong_to field in the first table can have multiple clubs separated by ', ' (i.e. comma space).

SELECT distinct id, l_name, f_name, trim(regexp_substr(belong_to, '[^,]+', 1)) club_str
FROM first_table

So far, this only returns the first club a person belongs to in each record of the first table.

As Mark Sinkinson said storing comma separated list in the table is the bad idea.Reason there is a better structure to store list that is 'TABLE' :).

Instead you can create third table to store belonging club information.
However you can get your result using the following query.

SELECT tbl1.id,
TRIM(REGEXP_SUBSTR(tbl1.belongto, '[^,]+',1, lines.column_value)) Belongto
FROM tbl1,
TABLE(CAST(MULTISET(SELECT level FROM dual CONNECT BY INSTR(tbl1.belongto,',',1, level-1)>0) AS SYS.odciNumberList))lines;

And look at the plan of the above query its quite expensive.:(

| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU) |Time     |

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 16336 |   430K|    65  (11)| 00:00:01 |

|   1 |  NESTED LOOPS               |      | 16336 |   430K|    65  (11)| 00:00:01 |

|   2 |   TABLE ACCESS FULL         | TBL1 |     2 |    50 |     2   (0)| 00:00:01 |

|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|      |  8168 | 16336 |    32  (13)| 00:00:01 |

|*  4 |    CONNECT BY WITHOUT FILTERING     |      |       |       ||      |

|   5 |     FAST DUAL               |      |     1 |       |     2   (0)| 00:00:01 |

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

So consider to change your design for betterment.
Thanks


Why Do Replication Deletes Require sysadmin Access I am running merge replication with SQL 2012.

There seems to be a nasty consequence of the delete triggers added for replication in SQL 2012.

Inside the delete triggers are this,

select @xe_message = CAST('replica_id: ' + convert(nvarchar(100), @replnick) + ', article_id: ' + convert(nvarchar(100), @tablenick) + ', rowguid: ' + case when @article_rows_deleted = 1 then convert(nvarchar(100), @rowguid) else N'0' end + ', generation: ' + case when @is_mergeagent = 1 then N'0' else convert(nvarchar(100), @newgen) end + ', Reason: -1' AS varbinary(1000));
        exec master..sp_repl_generateevent 1, N'Event : ppm_insert', @xe_message

And an error occurs when someone does a delete if they are not in the sysadmin role,

Msg 8189, Level 14, State 10, Procedure sp_repl_generateevent, Line 1
You do not have permission to run 'SP_TRACE_GENERATEEVENT'.

So are you telling me that every user of my database has to have sysadmin access if they are going to be doing deletes?

This seems wrong. Is there a way to turn this tracing off or an alternative way to remove this requirement?

So are you telling me that every user of my database has to have sysadmin access if they are going to be doing deletes?

No, as per BOL's documentation on sp_trace_generateevent:

  User must have ALTER TRACE permission.


reading content of master database at a different server I have a query for example

select * from sys.databases
select * from sys.configurations

I believe this information is stored at master database.
I would like to have a look at configuration settings in the past.

Can I restore msdb/master databases at different server as regular user databases and make sense of those mentioned queries?

Looks like select * from restored_master.sys.databases just ignores current database. I know an option to replace master/msdb using startup parameters - that should work, but a bit longer option and need compatible version of server.

Can I restore msdb/master databases at different server as regular user databases and make sense of those mentioned queries?

Couple of options you can use without restoring system database :

GUI - Registered Servers

PowerShell


Error on changing ownership of database from Files Page in database properties dialog box I've restored a database and created a new user(same user from which I've logged in) as db_owner. Now when I wanted to see database diagram got an error

  Database Diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use files page of Database Properties dialog box or the Alter Authorization statement to set the database owner to a valid login, then add the database support objects

Now when I tried to change ownership from Files page of Database Properties I am getting error as

  Set Owner failed for Database 'ABC' . (Microsoft.SqlServer.SMO)
  Additional Info: an exception occurred in SMO
  The proposed new database owner James-PC\James is mapped as user James in this database.
  Parameter name: James-PC\James"

Please help me fix the issue so that I may be able to see database diagrams on this computer after restoring the database backup from another system.

The database owner is the login in sys.server_principals that owns the database, as defined by its SID.  After a restore it may easily be the case that the login that was used to restore the database is not the login that was the previous owner of the database.  (This is even more likely to happen when the database if moved between servers.)

So, there could be three settings that you are having trouble with

The database owner_sid is not equal to the database's db_owner sid. You can compare this for database ABC by:

SELECT owner_sid FROM sys.databases where name = 'ABC';

SELECT sid from ABC.sys.database_principals WHERE name = 'dbo';
Next you are getting a message saying that your proposed database owner_sid also exists as a user in sys.database_principals.  We know this from the error message about mapping the proposed owner.

So your steps are:

 USE ABC;
 DROP USER James;
 ALTER AUTHORIZATION ON DATABASE::ABC TO [James-PC\James];

You dropped the user which is not needed, since you are making it the dbo.  Once that is out of the way, then your login can be made the owner of the database.

EDIT: You can also use SSMS to delete the James user from ABC database.  Then you could return to the Database properties File tab to set the database owner. (Sorry, I tend to think scripts.)


ASM - How the asm search the disks and where is this parameter from? I'm a begginer in Oracle ASM and when I will create a diskgroup the ASM shows me this parameter:

Where is this parameter from? I don't remember set any parameter with this name. I know that usually Oracle set this name as default when is not set a preferential parameter name, but in the creation process I don't saw this configuration.

Take a look at the ASM_DISKSTRING initiallization parameter for your ASM instance.

ASM_DISKSTRING


Query performance differs greatly between a development setup and production I'll try to keep this question straightforward, though I am dealing with a big ball of mud.

When I run my test query acrossed linked servers both located locally (on a shared virtual host), the query is fast at about 9 seconds.

When I run the same query acrossed linked servers (one local, one about 1,200 miles away) it is MUCH slower at 5:23

I am trying to learn how to analyze an execution plan, but are there other probable causes for this sort of thing?

Edit: Based on @Mat's comment, here is an example.

DISCLAIMER: I do not vouch for the quality of this code.

SELECT * INTO TempQTYOnHand FROM [RemoteServer].remoteDB.dbo.QuantityOnHand

DELETE FROM [RemoteServer].remoteDB.dbo.QuantityOnHand

INSERT INTO [RemoteServer].remoteDB.dbo.QuantityOnHand
SELECT DISTINCT ProductId, Onhand FROM Inventory
WHERE ProductID IN (SELECT ProductId FROM TempQTYOnHand)

DROP TABLE TempQTYOnHand

Is this a "chatty" query, and also, I guess chatty would mean that it is round tripping for each row on the INSERT?

On an average of 3 executions, does the below code run any faster?

SELECT ProductId INTO TempQTYOnHand FROM [RemoteServer].remoteDB.dbo.QuantityOnHand

DELETE FROM [RemoteServer].remoteDB.dbo.QuantityOnHand

SELECT DISTINCT ProductId, Onhand INTO NEWQuantityOnHand FROM Inventory
WHERE ProductID IN (SELECT ProductId FROM TempQTYOnHand)

INSERT INTO [RemoteServer].remoteDB.dbo.QuantityOnHand
SELECT ProductId, Onhand FROM NEWQuantityOnHand

DROP TABLE TempQTYOnHand
DROP TABLE NEWQuantityOnHand

That one is a small, small difference but it won't hurt to test anyway, right?

Also, there are articles that say pushing is slower than pulling, and a few say otherwise.

So, in the interest of experimenting, try both cases where you also invoke pushing from the linked server. Yeah, sadly- you'll have to create a stored procedure on the linked server to push the data, and then call it from your main query so it 'pushes' the data.


How to prevent message »Pager won't be used for less than 500 lines.« I have \pset pager_min_lines 500 in my ~/.psqlrc. Now, each time I start psql, it shows the message »Pager won't be used for less than 500 lines.«. I don't need that message, I specificly asked for this behavior, I don't need psql to tell me. This get's even more annoying, when I run psql from scripts where this message spams the output. How can I get rid of that message without using -X/--no-psqlrc?

The message comes from the execution of the \pset command, which you asked psql to execute upon start.
Change your .psqlrc file and tell psql to be quiet:
\set QUIET on
\pset pager_min_lines 500
\set QUIET off


could not save history to file "/var/lib/postgresql/.psql_history": No such file or directory I'm following this tutorial to set up my postgresql db.

http://inventtheweb.blogspot.ca/2012/05/install-postgresql-for-django.html

After I finish running the CREATE DATABASE command and run \q

I get the following response.

could not save history to file "/var/lib/postgresql/.psql_history": No such file or directory

What exactly is causing this and will this cause me any issues?

More Information: The version of postgresql that is being installed is 9.3 and I'm installing it on Ubuntu Server 14.04 LTS

Yes, the PGSQL system user (usually postgres) has its home directory set to /var/lib/postgresql. I'm presuming you installed your database in a different location. Since that directory doesn't exist, the PSQL program cannot access that folder to create its history file. (PSQL is the interactive command line for PGSQL).

Honestly, it's not that big of a deal. That history file is just a list of commands that were executed inside the PSQL interface.

Since you're done installing, I doubt you'll ever see the message again. That is, unless you connect to PSQL using your own account and you continue to get a similar error message. In that case, your HOME variable might be wrong. You're not logged in to your system as postgres are you? If you are planning on using that user interactively (not advised at all), then you'll have to set its HOME directory to some valid folder.

Here are some reference solutions.


How to dump only create table statements (or table definitions only) with pg_dump in Postgresql I want only the table definitions, not wiews nor functions.

I have a schema named "cvp". I try:

    pg_dump --schema-only -n cvp -t 'cvp.*' --format plain

And I get:

pg_dump: No matching tables were found

I try also without -n cvp or changing -t to -t *.* -t cvp.

I'am using PostgreSQL 11.

I've just tried this:

pg_dump -s -t test.* -d test

It nicely exported all table definitions, as expected.  -s is the same as --schema-only.


sql server getting total for each group Not sure how to go about this:-

I have a query

select name, city, salary from Employee

I can get a sum by doing a grouping

select name, city, sum(salary) total_salary from Employee group by name, city

How can I get the total per city as a separate row under each city ?

Expected Sample Result:

Name City Salary
n1   c1   10
n2   c1   20
T1   c1   30
n3   c2   20
n4   c2   50
T2   c2   70

You can use GROUP BY with the GROUPING SETS () modifier:

select name, city, sum(salary) total_salary
from Employee
group by grouping sets ((city, name), (city)) ;

Example of use from Microsoft Technet: Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS


Regular data transfer from SQL Server to MySQL Our company uses an application with a SQL Server database where we store all of our Project Item data.
Our company's Portal Website uses a MySQL database that holds other Project information (location, start date, billing agent etc...)
These two databases are currently only "connected" via a read only ASP.NET API on SQL Server.

We want to expand on what the SQL Server database currently offers us, however we don't want to make any changes to the SQL Server database considering it is also updated by the company that maintains the application that uses it.

For example, the SQL Server database doesn't currently have any change tracking and doesn't send notifications to relevant parties when key data is changed.

Our current thinking is, we create a lightweight duplicate of the SQL Server database on our Portal's MySQL database (only taking over the information we need) and periodically take over changes via our ASP.NET API, which should allow us to extend the database to safely implement change tracking, increase automation with change notifications and some other nice stuff.

As many of you have probably picked up, I'm pretty new to all this myself so any and all guidance is appreciated. My questions are:

Does this sound like an efficient / effective way to get what we want?
What are the advantages / disadvantages of our current trajectory?
What better / alternative ways are there of reaching our goals?
Any other questions I should be asking?

I believe the SQL Server version is 2012.

You do have Change Tracking in SQL 2012 version and you can use that to set up alerts for key data changes. And regarding, getting data from MySQL, you can create a linked server from MSSSQL server pointing to MySQL and read the data directly using OPENQUERY to sync the data between both.

See this link to set up linked server:

https://www.mssqltips.com/sqlservertip/4570/access-mysql-data-from-sql-server-via-a-linked-server/

That would be the easiest way I believe. There are other ways too as I know.


Informix Older than 15 Minutes How can I do this MySQL statement in an IBM Informix?

select type, channel, teilnr, starttime, endtime, usedtime, host
from online_time
where starttime < DATE_SUB(NOW(),INTERVAL 15 MINUTE)
order by starttime desc

I tried this one in Informix, but it gave a syntax error.

select type, channel, teilnr, starttime, endtime, usedtime, host
from online_time
where starttime < subdate(CURRENT, INTERVAL "15" MINUTES)
order by starttime desc

SELECT type, channel, teilnr, starttime, endtime, usedtime, host
FROM online_time
WHERE starttime > (CURRENT - 15 UNITS MINUTE)
ORDER BY starttime DESC


A question on MySQL SELECT Algorithm/Function Let's say I have 100,000 rows in a table, and each of the rows has a boolean value. Only ten of these rows have a value of 1, the rest are 0.

How would MySQL perform a SELECT on only the rows with a boolean value of 1? Would it have to do a binary search through all the values? Or would it be smart enough to pull out only the ones with a boolean value of 1 without having to search through the entire list?

To see how MySQL is doing something you want to look at the execution plan. More information on MySQL Execution plans at http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html . You should think of SQL as a declarative language, you tell it to do something , and it writes a program to do that. The execution plan shows you the program it wrote.

SQL will be able to look up specific entries when there is an index on the column ( like an index from a book). However, a binary field with a split distribution is a classic example of a column that doesn't have good selectivity, in other words, it isn't going to narrow down the search very much.


Advice to set minimum preferable value for MEMORY_TARGET I have a server having 90GB of RAM.

I have set MEMORY_TARGET to 60GB, because OEM showed me the following graph:

Which tells that, if you set the parameter to the value of 60GB, the performance will be the maximum. After 60GB, performance does not change.

Now we want to migrate our server to the virtual machine and my sysadmins want me to say the minimum preferable value for RAM to be allocated for the new server.

For that I need determine the minimum preferable value for MEMORY_TARGET. How to calculate it?

This depends on many factors. The OEM advisor is not the smartest. Your minimum requirements will be somewhere between the minimum size that allows your database to start and what you have now.
Smart would be to check the size of the shared_pool components after the instance has been running a few days. Less memory will cause problems because allocations from the shared pool will fail and it causes a lot of additional parsing.
Next to that, add memory meant for caching database blocks. More blocks means more caching and most of the time this means a better performance. There is a point where adding more memory no longer improves the performance.
Most of the times this is below what the OEM memory advisor tries to make you believe. Don't forget that Oracle is also a hardware selling company ....
If performance is no issue, the minimum requirement is the size calculated for the shared pool plus a few megabytes. If performance is important add a few GB to that and monitor your applications performance.


DBCC DBINFO incorrect dbi_dbccLastKnownGood I'm trying to determine why DBCC DBINFO is returning different results than what is contained within the error log. Here's the setup:

Secondary Replica (read only access -- Asynchronous Commit AlwaysOn HA AG)

Job ran at at 02/05/2018 09:00:00 PM successfully, with a duration of 02:22:21
Only step in the job is DBCC CHECKDB(myDB)
When I run DBCC DBINFO('myDB') WITH TABLERESULTS I see dbi_dbccLastKnownGood  02/05/2018 00:00:40.113. This should be 02/05/2018 11:22 PM.
When I check the error log, I see 02/05/2018 11:22:09 PM DBCC CHECKDB(myDB) executed by myAccount found 0 errors and repaired 0 errors. Elapsed time : 2 hours 22 minutes 9 seconds. Internal database snapshot has split point LSN ... and first LSN ...

I decided to check the PRIMARY REPLICA where DBCC CHECKDB is also running (this is all set up by the vendor).

Subplan ran at 02/05/2018 00:00:00 AM successfully, with a duration of 02:14:40
When I run DBCC DBINFO('myDB') WITH TABLERESULTS I see dbi_dbccLastKnownGood  02/05/2018 00:00:40.113 which is the same as the RoR but it should be 02/05/2018 02:15 AM.
When I check the error log, I see 02/05/2018 02:15:12 AM DBCC CHECKDB(myDB) WITH no_infomsgs exextued by myAccount found 0 error and repaired 0 errors. Elapsed time : 2 hours 14 minutes 40 seconds. Internal database snapshot has split point LSN ... and first LSN ...

I can't figure out where this DBCC DBINFO results is coming from, and why it doesn't match the SQL logs. I set up a job to send me an alert when the dbi_dbccLastKnownGood is > 24 hours.

Any ideas? Let me know if more information is needed.

READ ONLY REPLICA (Asynchronous AlwaysOn HA AG)

Like Erik pointed out, AG Replicas are currently read only so it can't update the values here. Additionally, if it did update the values it would just be overwritten the next time anything was updated on the primary and the log block sent to the secondary.

I decided to check the PRIMARY REPLICA where DBCC CHECKDB is also running
•Subplan ran at 02/05/2018 00:00:00 AM successfully, with a duration of 02:14:40
•When I run DBCC DBINFO('myDB') WITH TABLERESULTS I see dbi_dbccLastKnownGood  02/05/2018 00:00:40.113 which is the same as the RoR but it should be 02/05/2018 02:15 AM

The job started 2:14:40 ago, which is the time of the last known good checkdb - that's true. You started it then, it ran for 2 hours and 14 minutes +/- some agent stuffs. The times look fine to me, it's not like the job ran for 5 seconds and shows a last good of 6 hours ago.
I believe the issue is you expect the CHECKDB to show the good value at the end of the run versus being updated when it was executed. That shouldn't be a problem.


Can one make MySQL cluster on a unique index rather than a primary key? The MySQL docs states that:

  When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

But that's not the only possibility, you can cluster off of a unique index instead:

  If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

It would seem to follow that if I first create a unique index on a table, it will be marked as clustered; I can then create a primary key, and it will be nonclustered:

CREATE TABLE Tmp_CUQTest
(
ID1 INT NOT NULL,  -- Desired clustering field
ID2 INT NOT NULL   -- Desired PK field
);

CREATE UNIQUE INDEX CUC_Tmp ON Tmp_CUQTest (ID1);
ALTER TABLE Tmp_CUQTest ADD CONSTRAINT PRIMARY KEY PK_Tmp (ID2);

However, when I inspect the resulting table, my hopes are dashed:

Key      Type               Uni  Columns
PRIMARY  BTREE (clustered)  YES  ID2
ID1      BTREE              YES  ID1

Another question here on DBA.SE implies that creating an index as part of the CREATE TABLE script, rather than afterward, as a separate CREATE INDEX statement, may make a difference.  However, I get the same result.

Is there a way to force MySQL to cluster on a unique index of my choice, other than the primary key, or does this "fallback option" only apply to tables with no primary key at all?

PRIMARY KEY

InnoDB has exactly one PRIMARY KEY on each table.
The PRIMARY KEY is always clustered; there cannot be any other "clustered" index.
That PK is either an explicitly stated PK (this is preferred), the first UNIQUE key (with certain limitations), or a hidden sequence number (not quite the same as `AUTO_INCREMENT; 6 bytes).
The PK is UNIQUE and is and index.
The PK (and other indexes) may contain multiple columns ("composite" index.
The table is structured as a BTree (actually a B+Tree), with all columns present, and BTree is ordered according to the PK.

Secondary Keys

All other indexes are called "secondary indexes".  ("key" == "index")
The structure is a B+Tree, ordered according to the key
The "rows" of this BTree contain copies of the column(s) of the PRIMARY KEY (that is, the 'clustered index').  This is how the secondary key can get to the row.
Note that looking up a row via a secondary key requires drilling down two BTrees.  (Unless it is a "covering" index).
A UNIQUE KEY is two things: a secondary key (unless promoted to PK), and a "uniqueness constraint".

Other notes

InnoDB has not concept of a "ROWNUM" or "row locator", unlike some other vendors.  The PK serves as the row locator.
MySQL has no "Heap" or "Bit" indexes on InnoDB.
InnoDB has only BTree, SPATIAL, and FULLTEXT; I am not discussion the latter two in this Answer.
For most purposes, it does not matter whether you create the indexes inside the CREATE TABLE statement, or do CREATE INDEX later.  Ditto for defining FOREIGN KEYs.
Structurally, the PK+data is a B+Tree; each secondary index is a B+Tree.  These B+Trees look, feel, smell, etc, identical.  The only difference is what you find in the 'rows'.

AUTO_INCREMENT

There are many caveats.  It is not necessarily consecutive numbers; there are many ways that gaps can occur.  Only trust that they will be 'unique'.
AUTO_INCREMENT does not have to be declared PRIMARY KEY, or even UNIQUE.

A trick.  It is possible to do the following.  Is it what you are fishing for??

CREATE TABLE ... (
  id INT AUTO_INCREMENT,
  foo ...,
  ...
  PRIMARY KEY (foo, id),  -- to get clustering on `foo` and uniqueness (via `id`)
  INDEX(id)               -- sufficient to keep AUTO_INCREMENT happy.
) ENGINE=InnoDB;


Allowing Foreign Key to be Null I'm trying to create a relationship between two tables where one column in TABLE A is the foreign key for a column in TABLE B. However, there is one row in TABLE B that is currently null in that column and it's giving me an error. However, from Googling the issue, the general consensus seems to be that null is an acceptable value in a foreign key field. But I'm still unable to create this relationship in SSMS due to the error message. How can I bypass this?

Error:

Unable to create relationship 'FK_RELATIONSHIP'.
The ALTER TABLE statement conflicted with the FOREIGN KEY
constraint "FK_RELATIONSHIP". The conflict occurred in database "DATABASE",
table "TABLE A", column 'COLUMN 1'.

SQL Code as requested:

USE [DATABASE]
GO
/****** Object:  Table [dbo].[TABLEA]    Script Date: 10/02/2012 17:44:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TABLEA](
[COLUMN1] [nvarchar](4) NOT NULL,
CONSTRAINT [PK_TABLEA] PRIMARY KEY CLUSTERED
(
[COLUMN1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TABLEB]    Script Date: 10/02/2012 17:44:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TABLEB](
[COLUMN1] [nvarchar](15) NOT NULL,
[COLUMN2] [nvarchar](4) NULL,
 CONSTRAINT [PK_TABLEB] PRIMARY KEY CLUSTERED
(
[COLUMN1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,   ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Thanks

For anyone who stumbles here with the same question. An empty cell is not the same as a NULL cell. Empty cells are '' not NULL!


Synonyms from instance I have two different DB instances.
Is there any way to find out from instance2 (from all_synonyms view) list of all objects using synonyms from instance2 to instance 1 ...?

I want to, for example, connect on one instance, and query all object from another instance which have created synonyms for utilization in another instance ...

SELECT * FROM ALL_SYNONYMS WHERE DB_LINK = 'your_link_name_here';

?


Postgresql createrole permission for user I created a role with createrole flag, but users in this role trying to create users fail with permissions denied. Do I need to assign the createrole to each user instead of just the role (group)?

my steps were as follow:

create role group1 with nologin createrole;
create user user1 PASSWORD 'password' in role group1;

then login as user1 and trying to create user newuser fails with the error permission denied to create role

According to the docs, you have to use SET ROLE before actually using the createrole privilege.

Connect as user1 and issue

SET ROLE group1;
CREATE ROLE newuser;

Also, beware - CREATEROLE is in fact equivalent of superuser. This is also said in the docs.


Grant access to a table to all users Is it possible to assign a grant to a table for all users, or a default permission so that when new users are created they will have the specific grants for that table to SELECT, UPDATE, INSERT and DELETE?

What I am about to propose would be considered by many to be a hack, but will get you what you want.
STEP 01 : Create a special database called specialdata
CREATE DATABASE specialdata;

STEP 02 : As root@localhost, place the table you want into the specialdata database
CREATE TABLE specialdata.specialtable LIKE mydb.mytb;
INSERT INTO specialdata.specialtable SELECT * FROM mydb.mytb;

STEP 03 : Create anonymous user access to the specialdata database with the needed grants
INSERT INTO mysql.db
SET host='%',user='',db='specialdata',
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y';
FLUSH PRIVILEGES;

STEP 04 : There is no STEP 04
Every user should have access to any table in specialdata
Give it a Try !!!


Using WITH and IF in the same query I'm trying to use the WITH clause and IF statement, but it does not seem to work and I cannot find any reason why it would not. Syntax should be correct and still it is complaining about the syntax.

The error I receive is "Incorrect syntax near ';'. I have tried removing ; characters, but then it says "Incorrect syntax near the keyword 'IF'.

Is it even possible to use the WITH and IF syntax like this? The query does work without the IF statements (with only one of the select queries).

With measurements as
        (
        (SELECT * from measurement_database1)
        UNION ALL
        (SELECT * from measurement_database2)
        UNION ALL
        (SELECT * from measurement_database3)
        );

    IF @device= 'en1' AND @other_parameter= 'yes'
        (
        select * from measurements where device like 'en1' and op like 'yes'
            );
    IF @device= 'en2' AND @other_parameter= 'no'
        (
        select * from measurements where device like 'en2' and op like 'no'
            );
    IF @device= 'en3' AND @other_parameter= 'yes'
        (
        select * from measurements where device like 'en3' and op like 'yes'
            );

I'm running Microsoft SQL Server 2012 11.0.6607.3.

It may help you more if you think about it, not as a WITH statement but as a Common Table Expression (CTE).

That way you can go back to the Microsoft Documents on CTEs and read the following line;

  A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE
  statement that references some or all the CTE columns. A CTE can also
  be specified in a CREATE VIEW statement as part of the defining SELECT
  statement of the view.

Although technically you can define a CTE and then not reference it at all but then what would be the point in defining it? e.g.

WITH Missing AS (SELECT 'The Point' AS What)
SELECT * FROM sys.messages;

Going back to your case, you could rewrite it using CASE statements instead of IF statements but again what's the point? You don't need them.

You're query can be re-written to:

DECLARE @device char(3) = 'en3',
        @other_parameter varchar(3) = 'yes';
WITH measurements AS (
                         (SELECT    *
                          FROM  measurement_database1)
                         UNION ALL
                         (SELECT    *
                          FROM  measurement_database2)
                         UNION ALL
                         (SELECT    *
                          FROM  measurement_database3)
                     )
SELECT  *
FROM    measurements
WHERE   device LIKE @device
  AND   op LIKE @other_parameter;

So there's no need of IF statements at all!
HTH


Inline table-valued function vs inline sql I'm seeing some odd behavior in trying to tune an inline table-valued function. Specifically, if I take the guts of the function and it with the same parameters as I'd have called the function with, I see about a 3x performance gain. So something like this (greatly simplified):

create function dbo.FooToDate(@ToDate date)
returns table
as
return (

select f.a, f.b, b.c
    from dbo.[Foo] as f
    left join dbo.Bar as b
       on f.fId = b.fId
    where f.ToDate >= @ToDate

)
go
declare @ToDate date = '2012-12-21'

--runs in about 4 seconds
select f.a, f.b, b.c
from dbo.[Foo] as f
left join dbo.Bar as b
   on f.fId = b.fId
where f.ToDate >= @ToDate

--runs in about 12 seconds
select a, b, c from dbo.FooToDate(@ToDate)

When I look at the query plans for my actual situation, the overall shape is quite different. My understanding of inline TVFs was that the opimizer was able to unroll the function, so this behavior is somewhat odd to me. Is there a simple (or not-so-simple) explanation?

In SQL Server 2000 and earlier we all were accustomed to using alternating forms of logically equivalent queries as a means of query tuning.

Since then the optimizer has improved so much that we are surprised if two different but logically equivalent queries result in different execution plans.

However, the search space of all possible execution plans for a given query is of significant size even for only moderately complex queries so that it is impossible for the optimizer to look at all of them. The optimizer uses a mix of heuristics and rules and information about the data to determine where to look next after having found a first suitable plan. If the query is complex enough even a trivial change like this can set the optimizer of on totally different paths that in the end lead to such big differences in run time. (Check out my post on join hints for a more in detail discussion of this: http://sqlity.net/en/1443/a-join-a-day-join-hints/

Often we can remedy a situation like this by making sure that the information the optimizer is basing its decisions on is as accurate as possible. That means, you should make sure that your tables are appropriately indexed and that all statistics are up to date. You might also look into adding additional - maybe filtered - statistics to the tables involved.


Slow MySql Server Performance - What and How to check? start:disclaimer

I'm not a MySql Server DBA; I know mostly of MSSQL - which is why I need your help.

end:disclaimer

I've been asked to check why the MySql server engine is performing poorly - I have not seen nor held the databases involved and would like to know where to start.

Where do I start?

What questions should I be asking those who have access to MySql - I don't even know if they are using phpmyadmin or some other tool.

Essentially:

What items should I ask for and how would I respond to each piece they give?

What are the key items to ask for when the issue is in the performance of the database?

in MSSQL I can check sp_who2 for existing connections to see if anything is blocking, what is the counterpart in mysql?
* do not have to be specific as there can be many kinds of results for each item but would like to help get the ball rolling since it is affecting users - apparently they set up the mysql engine without having a dba on-board.

Log slow queries- If your system has a ton of queries, it gets tougher to find out which queries are slowing your system. MySQL provides a tool to log slow queries for further analysis
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
EXPLAIN Extended command shows details about your queries when your have no idea what is happening http://dev.mysql.com/doc/refman/5.0/en/explain-extended.html
To speed up your queries use Index - A good practice is add index by seeing which fields are in the WHERE clause add index for them.
Also if you are retrieving all fields from a table, the query gets slower by fetching all data from disk. In SELECT query you should specify which fields you need to bring instead to bring them all with *
Make use of Query Cache http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html
Make sure that your MySQL server configuration file options are optimized according to your hardware
http://dev.mysql.com/doc/refman/5.5/en/option-files.html
Make sure that you are using optimized data types while creating a table structure
For example "Comments" fields has size of 256 characters, reply it to MYSQL with a field with type VARCHAR(256) instead of using TEXT. The query will be much faster.

Procedure_Analyse() can help you in finding optimal data types:

http://www.mysqlperformanceblog.com/2009/03/23/procedure-analyse/

http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

There are open source tools from Percona - http://www.percona.com/software/percona-toolkit which can be helpful for MySQL DBA's and specially those who are new to MySQL these tools will provide good assistance to them.


Property Owner is not available for Database 'DBNAME' Property Owner is not available for Database 'DBNAME'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

I am getting this error when trying to 'right click' a properties on the database.
Does anyone know how to fix this ?

I've tried following:

use DBNAME
EXEC sp_changedbowner 'myUser'

Getting the following message:

  The proposed new database owner is already a user or aliased in the database.

Any Ideas how to fix this ?

myUser is probably already a database user.  You shoudl be passing a login principal to the stored procedure.  Do something like this:

use DBNAME
go

exec sp_changedbowner 'myLoginThatShouldBeDBO'
go


Get records which met SELECT condition over time and range them by period of time when condition was met I have the following task: I want to execute fixed SELECT statement against SQL Server table with some fixed condition and I need to do it after specified period of time multiple times... Let's say N times with 1 minute interval between each execution with final goal to display only records which were returned all N times... Final goal is to check if some specific records persist in select results over time or maybe even order them by time they were returned - i.e. one record was returned in 3 first SELECT executions and interval was set to 1 minute hence I can say it was persistent for 3 minutes, another record was returned 5 times - so I can conclude it persisted / met my condition for 5 minutes.

Nature of my condition/data dictates that records not meet this condition for more than one minute and I want to catch abnormal records which meet my condition for prolonged period of time.

From explanation above it seems that it all involves some cycle, temp table and condition to put results in yet another temp table. But I'm posting it here to check if I maybe missing some simpler approach/solution which will allow to achieve the same result?

No, from what you describe, I think that you'll wind up having to track these using some secondary tables.

It sounds like you want to know how long record X stayed in a given status, whether that's the latest status or not. From your description, I'm not sure if records can wind up in a given status multiple times, or if hitting any status is a one-time event. That would impact how this needs to be set up.

It also sounds like there are statuses that matter, and others that do not matter. Hopefully, if there are a large number of records, most records should wind up in statuses that you're not interested in.

So, you'd want to do something like the following:

Create a table to hold current status info (let's call that StatusDuration_Active), and a parallel table to keep historical info (StatusDuration_History). Let's assume it will have at least the following fields:

a unique ID for each of the above tables (and I'd make this an IDENTITY column). The _History ID will be unconnected to the _Active ID (that is to say, when a record moves to _History, it will get a new ID). Unique IDs are useful in case you need to delete records, among other things - and you will want to delete records from _History periodically.
record_id - this will hold the primary key value from the table you're tracking. It can be made a foreign key, but might be useful without being one. Presumably, you'd want to know about a long duration in a given status, even if the record was deleted afterwards.
status - the status value from the table you're tracking.
first_seen - datetime (or datetime2) column holding the time when the records was seen in this status, when it hadn't been in this status at the previous check.
last_seen - same datatype as previous; the last check when the record was still in this status.
times_seen - integer value showing the number of consecutive times your process saw this record at this status. Useful for a cross-check against the first and last times, in case your process that checks these goes offline at some point (accidentally or on purpose); you could add code to remove or warn if times_seen < (DATEDIFF(minute, first_seen, last_seen) - 10), for instance.

Create a procedure that:

Create a temp table as follows:

CREATE TABLE #StatusDuration
     ( tmp_id INT IDENTITY(1,1) NOT NULL
      ,record_id <datatype>
      ,status <datatype>
      ,first_seen datetime
      ,times_seen INT DEFAULT 0
     );

where record_id will hold the primary key of the table you're tracking, and status will hold the current status from that table.
Selects the current time into a variable (@CurTime);
Selects all records currently in a status you're interested in into #StatusDuration; set first_seen to @CurTime;
Copies all records from StatusDuration_Active where the record ID and status are not in the temp table into StatusDuration_History; these rows should be copied exactly as is;
Updates all records in #StatusDuration that are in StatusDuration_Active with the same record_id and status, updating the temp table's first_seen and times_seen with the values from StatusDuration_Active.
TRUNCATE TABLE StatusDuration_Active
Insert all records in #StatusDuration into StatusDuration_Active; set last_seen to @CurTime, and times_seen to times_seen + 1

Set up a job to run the procedure every minute.

Depending on the number of records returned, you could delete records from StatusDuration_Active that got moved to _History, update records that are already present, and insert new records, instead of the TRUNCATE TABLE method. However, if there are a large number of records to be deleted, this might be the fastest method to use.

You'll need to confirm that all this runs within a minute (or schedule it to run less frequently). I'd expect it to be OK, but I'm not sure how many records you'll have.

Note that the TRUNCATE TABLE on StatusDuration_Active will reset the IDENTITY value back to 1 (or whatever you started with) - one of the reasons the IDs shouldn't track over). An ID column on #StatusDuration may actually be unnecessary; I include them wherever possible, because sometimes they are necessary.

As noted, you'll want to clear the oldest rows (optionally keeping the most interesting ones, cases where the statuses remained at a particular duration for longer than normal) from StatusDuration_History, or it will eat up a lot of space, and will take longer and longer to query (and, depending on how you index it, possibly longer and longer to insert into, which may be more of a concern).

When reporting, you may want to use the WITH (NOLOCK) table hint, as both tables will be updated once a minute, and you don't want to interfere with that. If your queries run in a few seconds, and your procedure does as well, then no major worries. If you do use the table hint, note that it'll be possible to hit a row that's in _Active, and to hit the same row because it's been copied to _History. I'd keep the possibility in mind; you could use UNION instead of UNION ALL to combine the data from the two tables to work around this, but I'd not worry about it unless you see it happening and notably affecting your results.


How can we trim bad data and output clean trimmed data using DQS or MDS? Until now, we have used stored procedures for data massaging and cleansing but we have now decided to move to using Data Quality Services (DQS) and Master Data Management (MDS) in our data warehouse projects.

There is one very common function which we use in our stored procedures - it trims and replaces the , character with white space e.g.:

UPDATE [Table] SET Column=(ltrim(rtrim(replace(Column,',',''))))

I have spent hours to figure out a way to achieve this using DQS and MDM but couldn't find any way to achieve the same goal.

I would really appreciate if you guys can help me out with this.
In fact, thank you already for your precious time.

I think if you can use SQL Server 2017, this new function Trim() Can help you write it better. if you insist on using DQS for this matter, I think this article can help you find your way. Sorry that I couldn't write it for you. Hope it helps.


Update OLAP Cube data We are using OLAP in our project and we are facing a scenario where we need to update the cube with real time data on daily basis.

I need some suggestions about an optimal way for updating the data in the cube.

Whenever new data arrives and made an entry in that table we need to update the current values in the cube like a trigger.

It's hard to give you a specific guidance on your best setup, but in any case there is no such thing as a trigger writing to an OLAP cube.

I can give you some starting points though.

You need to look into different storage options for your cubes.

By default cubes use MOLAP (Multidimensional OLAP) in which you process a cube on a scheduled basis[1] and all data and aggregations are stored in the OLAP database. This has a latency as the relational data is only visible in the cube after processing.

This is by far the easiest option from a developer perspective, but won't resolve your requirement.

[1] With MOLAP, see below for the proactive caching options

There is also a ROLAP (Relational OLAP) storage mode, which stores the aggregations in indexed views in a relational database. There is no copy of the data in the cube but the data is available immediately in the cube.

This off course has an effect on the query performance to your SSAS cube. Generally the querys will return slower and processing time will be longer.

If performance is too slow, there is a third option called HOLAP (Hybrid OLAP), which stores the data in the relational database but the aggregations in the OLAP database. When the source data is updated the aggregations will be processed again. This offers zero latency, and relatively decent performance when the queries are resolved from the aggregations, but slow performance when the users drill down or the query has to access the relational store.

Proactive caching

You should also look into Proactive caching which determines how often the aggregations are updated. This should be a balance between the load that the processing puts on your relational database and the performance of the SSAS queries and the latency in your cubes.

If you use proactive caching the cube listens to changes in the underlying data source and processes your MOLAP partitions on the schedule you choose.

Have a look at this article Introduction to SQL Server Proactive Caching for an explanation of how that works


Incorrect syntax error when creating view Error message returned:

  Msg 102, Level 15, State 1, Procedure vw_IVStockAging, Line 12
  Incorrect syntax near '–'.

When I execute this:

CREATE VIEW vw_IVStockAging
AS SELECT IV00101.ITEMNMBR AS ITEMNUMBER,
IV00101.ITEMDESC AS ITEMNAME,
...
(IV10200.QTYRECVD – IV10200.QTYSOLD) AS QTYAVAILABLE
FROM ...

Seems pretty simple, you have a long dash (–) where you meant to have a minus sign (-). The difference is subtle to the human eye, but not to SQL Server.


SSMS Generated Table Scripts Scenario: In production we have the same functional DB at different physical sites. The column order on a table don't match each other so I am writing a script to amend this (a 3rd party application requirement).

Action: To do this I copied the table to a test server, made the changes required in the GUI and then used the save to script functionality. The generated script essentially creates a new table with the proper order, copies the data into there, deletes the original table and then renames the new table to the old/proper name.

Query: The generated table definition includes in part column definitions such as:

STAGE3 float(53) NULL

Using the more common Script Table as> Create to new query window the same column is generated as:

[STAGE3] [float] NULL

1) Why is there a difference?

2) Does it matter?

Environment:
Server SQL2000
SSMS 2008, Script for server version set to SQL Server 2000
Production destinations SQL2000, SQL2008, SQL2008R2

1. Why is there a difference?

Simply because different code within SSMS generates the script differently. The SSMS GUI is well-known for being inconsistent (and broken in places). It's quite likely the features were just implemented by different developers or teams.
A more definitive answer would involve call to Microsoft Support.

2. Does it matter?

No, it doesn't matter.
SSMS added square brackets around the column and table names, so the script will work in cases where users named tables or columns using, for example, reserved keywords or spaces.
The result is semantically identical albeit inconsistent. SQL Server Books Online states the default float precision is 53:
float and real (Transact-SQL)
From that link:

float [ ( n ) ]
Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.


Better way to concatenate a sequence of string replacements in Amazon RDS? I have a concatenation of string replacements in this way:

CREATE OR REPLACE FUNCTION norm(t text) RETURNS text AS $$
        declare t1 text;
        declare t2 text;
        ...
        declare t10 text;
        BEGIN
            select replace (lower(t), ',', ' ') into t1;
            select regexp_replace (t1, '[^0-9a-z\s-]', '', 'g') into t2;
            ...
            select regexp_replace (t10, 'n?oise?.*', '', 'g') into t10;
            RETURN trim(t10);
        END;
$$ LANGUAGE plpgsql;

At the moment it has 10 different operations and the number keeps on grow. Also it has the inconvenient that changing position to transformations forces to rename variables.

Is there a better way to manage this situation?

With plperlu and Regexp::Assemble

It depends on the length of the string and the length of the input. You can create a fully optimized regexp, using Perl's Regexp::Assemble and run it with plperlu.

The below code caches the regex for the entire session, so subsequent calls should be stupid fast.

CREATE OR REPLACE FUNCTION replace_from_all(text, text[])
RETURNS TEXT
AS $$
  use strict;

  my ( $text, $rearray ) = @_;
  my $re;

  my $allre = join '', @$rearray;
  if ( exists $_SHARED{$allre} ) {
    $re = $_SHARED{$allre};
  }
  else {
    require "Regexp/Assemble.pm";
    my $ra = Regexp::Assemble->new;
    $ra->add($_) for @$rearray;
    $re = $_SHARED{$allre} = $ra->re;
  }

  $text =~ s/$re//ge;
  return $text;
$$
LANGUAGE plperlu
IMMUTABLE;

Now you can call it with,

SELECT replace_from_all('foobar', ARRAY['o', 'b'] );

Alternatively, you hard core the regexes in there, but if you're going to do that you may as well chop out Regexp::Assemble, and just paste a precompiled regex.

With plperl

CREATE OR REPLACE FUNCTION replace_from_all(text, text[])
RETURNS TEXT
AS $$
  use strict;

  my ( $text, $rearray ) = @_;
  my $regexes;

  my $allre = join '', @$rearray;
  if ( exists $_SHARED{$allre} ) {
    $regexes = $_SHARED{$allre};
  }
  else {
    $regexes = $_SHARED{$allre} = [map qr/$_/, @$rearray];
  }

  foreach my $re ( @$regexes ) {
    $text =~ s/$re//ge;
  }
  return $text;
$$
LANGUAGE plperl
IMMUTABLE;

Speed

For this example I took the following sample data which is 300,000 rows

CREATE TABLE foo
AS
  SELECT t.x FROM generate_series(1,1e5)
  CROSS JOIN ( VALUES
    ('You don''t like diacriticals àèìòùáéíóú'),
    ('You do like noiseless numbers'),
    ('And whatever you like to try also, removing commas, , , ')
) AS t(x);

For comparison, the above version by @joanolo takes 8.04 seconds

SELECT norm(x) FROM foo;

Using plperl with,

SELECT replace_from_all(x, ARRAY['[^0-9a-z\s-]', 'n?oise?.*'])
FROM foo;

It takes 5.0 seconds.


How do I do factorials in SQL Server? In PostgreSQL, I often times want to do something like find the factorial of 7. I can do that very simply with

SELECT 7!;

-- PostgreSQL is so full featured
-- it even supports a prefix-factorial
SELECT !!7;

Even Excel has FACT,

=FACT(7)

How do I do that with SQL Server 2017 Enterprise?

I am not aware of a built-in function to do this. You need to roll your own. Here is how I do it:

SELECT SQL#.Math_Factorial(5); -- 120

The Math_Factorial function is in the Free version of the SQL# SQLCLR library (that I wrote).

OR

if you do not need it in function / UDF form, then it might be more efficient to do the following:

DECLARE @BaseNumber INT = 5,
        @Result BIGINT = 1;

;WITH cte AS
(
  SELECT TOP (@BaseNumber) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [Num]
  FROM   sys.columns
  ORDER  BY Num
)
SELECT  @Result *= [Num]
FROM    cte;

SELECT @Result;
-- 120

Both approaches shown above take into account the "special" condition of passing in 0 as the "BaseNumber" and it returning 1 instead of 0.

SELECT SQL#.Math_Factorial(0); -- 1

For the T-SQL approach, just make @BaseNumber = 0 and it will return 1 (no need to copy and paste it again here just for that).


Pros and Cons of SQL Server Database Mail I'm planning to use SQL Server Database Mail.

I'll have a table where mail receiver details and mail content is stored and I want to schedule this mailer (every minute) to send mails to the new receiver records added to this table.

I would like to know pros and cons from the experts who already have used, or are aware of, this Database Mail feature.  Is this the correct system for sending mails, or is there better options to send mails from SQL Server?

Update: Above mentioned table is a kind of queue table for mails to be sent. There are many operations going on in the application and all these operations need to send mail once each transaction is complete. Hence, they store that information in this table. Later on, at every minute, we retrieve data from this table and send mails accordingly, marking that record as sent. I hope this better clarifies my requirements.

Why would you build a table to log the emails into, then have another process come through and read the emails from the table in order to send them via database mail.

Why not just modify your application to send the emails directly via database mail.

Database mail uses SQL Service Broker under the covers to handle the email sending.  When you tell it to send an email it drops the email into a queue, then a background process comes through and grabs the email and actually sends it.  In your current design:

You drop the message into a table
A process reads the email
That process sends it to database mail
Database mail puts it into a queue
Database mail reads from the queue and sends the email

Why not remove steps 1, 2 and 3 from the process.


Search Across Multiple Rows I have a database that is full of doping results for athletes.

+-----------+------+----------+
| athleteId | test | outcome  |
+-----------+------+----------+
|    100011 |    1 | Pending  |
|    100012 |    1 | Positive |
|    100012 |    2 | Negative |
|    100013 |    2 | Positive |
|    100013 |    3 | Negative |
|    100014 |    2 | Positive |
|    100022 |    1 | Negative |
|    100022 |    2 | Positive |
|    100022 |    3 | Positive |
|    100023 |    2 | Negative |
|    100030 |    1 | Negative |
|    100031 |    2 | Disputed |
|    100033 |    4 | Pending  |
|    100036 |    5 | Positive |
|    100037 |    5 | Positive |
|    100038 |    5 | Positive |
|    100051 |    1 | Pending  |
|    100054 |    1 | Positive |
+-----------+------+----------+

What I'm needing to return is the athleteId where a test returned a positive, and then another test returned a negative.
That narrows it down to athleteIds 100012, 100013, 100022, but I'm not sure how to structure a query to return this.

Any help is greatly appreciated!

Assuming the test numbers suggest the order in which the tests are taken and you want athletes having a Negative test coming after the last Positive test of theirs, you can group your table by athleteId and then:

get the last test number where the outcome is 'Negative':

MAX(CASE outcome WHEN 'Negative' THEN test END)

get the last test number where the outcome is 'Positive':

MAX(CASE outcome WHEN 'Positive' THEN test END)

compare the two results to see if the former is greater than the latter:

HAVING
  MAX(CASE outcome WHEN 'Negative' THEN test END) >
    MAX(CASE outcome WHEN 'Positive' THEN test END)

Full query:

SELECT
  athleteId
FROM
  aTable
GROUP BY
  athleteId
HAVING
  MAX(CASE outcome WHEN 'Negative' THEN test END) >
    MAX(CASE outcome WHEN 'Positive' THEN test END)
;

If you want athletes who have a Negative test coming after at least one Positive test (not necessarily the last one), you could try a different approach:

get a Negative test;
see if there exists an earlier Positive test for the same athlete.

Or like this in SQL:

SELECT DISTINCT
  athleteId
FROM
  aTable AS neg
WHERE
  outcome = 'Negative'
  AND EXISTS
  (
    SELECT
      *
    FROM
      aTable AS pos
    WHERE
      pos.athleteId = neg.athleteId
      AND pos.outcome = 'Positive'
      AND pos.test < neg.test
  )
;


Is it safe to disable InnoDB doublewrite buffering when setting Innodb_page_size = filesystem page size? As I understand, doublewrite buffering is needed to avoid partial write, because Innodb_page_size default is 16K, while filesystem page size normallly is 4K; I wonder if I set "Innodb_page_size =  filesystem page size", then I could disable  InnoDB doublewrite buffering safely?

Background info...  The "torn page" problem occurs when part of an InnoDB block is written to disk, but the physical write died before writing all the low level (usually 512-byte) blocks.  This leads to an unreadable block for InnoDB.  The double-write buffer, and its extra write, makes it possible to recover from a torn page.

The hardware needs to "guarantee" atomic writes of 16KB.  FusionIO is the only drive that I know of to provide that guarantee.  And that gives them a advantage by saying that you can turn off double-writes.

RAID controllers with a battery-backed-write-cache should be able to provide such a guarantee, but it is unclear whether the OS confuses the issue.  But, then, writes are virtually free, so turning off double-write does not matter much.

I hesitate to say yes or no to your question because I don't know what is underneath the "filesystem".  It may turn the InnoDB block into multiple 512-byte disk writes, thereby still allowing torn pages.

O_DIRECT is a related topic to look at.

Setting innodb_page_size to 4K has other issues (even if you could get the drive to play nice) --

Records are limited to about 2000 bytes instead of about 8000.
You are asking for more I/Os.  This may be good or bad.  Random point queries of small rows will work faster with 4K blocks; most other situations may work slower.


GROUP_CONCAT's Rows to Columns I have this table with 16 rows only:

http://www.jassimrahma.com/temp/attendence_table.png

and I am using below code now to split the time of attendance into columns and getting this result:

http://www.jassimrahma.com/temp/attendence_result.png

but I am not happy with it! for example, 7th July, there is only one fingerprint so it should be only F1 but it's repeating it in F1, F3 and F4.

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 1), ',', -1) AS F1,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 2), ',', -1) ,NULL) AS F2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 4), ',', -1) AS F4
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

How can I fix this please?

Thanks,
Jassim

SUBSTRING_INDEX will repeat the value if the value doesnt not end with the respective delimiter,so just concat a comma at the end of each string

SELECT DATE(attendance_date_time) AS attendance_date, SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 1), ',', -1) AS F1_time,
IF(LENGTH(GROUP_CONCAT(TIME(attendance_date_time))) - LENGTH(REPLACE(GROUP_CONCAT(TIME(attendance_date_time)), ',', '')) > 1,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 2), ',', -1), NULL) AS F2_time,
-- SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME(attendance_date_time)), ',', 3), ',', -1) AS F3_time,
IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 3), ',', -1), NULL) AS F3_time,
SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(GROUP_CONCAT(TIME(attendance_date_time)),','), ',', 4), ',', -1) AS F4_time
FROM employee_attendance
GROUP BY DATE(attendance_date_time);

FIDDLE


PostgreSQL keeps asking for user password with "trust" authentication enabled I created a user john with createuser and didn't supply any passwords for it, i.e. no -P option specified. I then proceeded to add the following line above all other lines in the pg_hba.conf file:
host    all             john            127.0.0.1/32            trust

I then restarted the server but even after that, when I try to run psql -U john it keeps asking for a password. I even ran the command psql -c "alter role john with password null;" but it changed nothing. PostgreSQL keeps asking for a password for john. What am I missing?
Running PostgreSQL 13.2 on Windows 10.

By checking the logs, it got apparent that PostgreSQL was matching the ::1/128 IPv6 address, which had scram-sha-256 as its authentication method. adding a line like
host    all             john            ::1/128            trust

fixed the problem.
Also, there is no need for restarting the DBMS after changes to pg_hba.conf.


How to join two columns and find unique combinations I have the following topological structure (topo) with their types:

id(string) |   id_from(string) | id_to(string) | type(string)
 '111'     |    'aaa'          |   'bbb'       |   'type1'
 '222'     |    'bbb'          |   'ccc'       |   'type2'
 '333'     |    'ddd'          |   'bbb'       |   'type3'

Now I want to find all distinct connected type-combinations

id_from = id_to OR
id_to = id_to OR
id_from = id_from

The result should look like this:

type_from(string) | type_to(string)
    'type1'       |      'type2'
    'type2'       |      'type3'
    'type1'       |      'type3'

My first approach is to create an index on id , id_from , id_to. The SQL query i tried is:

WITH p AS
(
   SELECT DISTINCT ON ( id ) id , from , to , type FROM topo
)
SELECT DISTINCT ON( p.type , t.type )
p.type AS type_a , t.type AS type_b FROM p
JOIN topo t ON
t.id_from = p.id_to OR
t.id_to = p.id_to OR
t.id_from = p.id_from

It works but it takes several minutes without an result. Any way to achieve this (faster)?

EDIT: I'm not even sure if this query can be done much faster for several million rows. My current best solution is to compute this query once and to store it as an combination table. Whenever I need this values, I just query the combination table.

Try something like

    SELECT LEAST(t1.type, t2.type), GREATEST(t1.type, t2.type)
    FROM topo t1, topo t2
    WHERE t1.id_from = t2.id_to
UNION
    SELECT LEAST(t1.type, t2.type), GREATEST(t1.type, t2.type)
    FROM topo t1, topo t2
    WHERE t1.id_from = t2.id_from
      AND t1.id > t2.id
UNION
    SELECT LEAST(t1.type, t2.type), GREATEST(t1.type, t2.type)
    FROM topo t1, topo t2
    WHERE t1.id_to = t2.id_to
      AND t1.id > t2.id


Replication reverted partitioned table to primary filegroup, can I re-link to the partitions? We have 2 servers:

Server A. production (no partitions)
Server B. New Server (partitions and compression)

We restored a backup to the new server, partitioned a large table by datetime and turned on compression, and we were happy.
Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression.
We cannot query any old data on the new server, only data from when we turned on replication.
The clustered index is the old one, and compression is not on, and no partitions to be seen in SSMS, it's using the PRIMARY filegroup  (same as the current live server is using).
On the physical disk, I can see the partition files, and they are still massive with GBs of data, so I know the data is there, but it appears the table has lost the link to it.
It appears turning on replication replicated the filegroup side of things. Is there a way to point the table back to partitions and save all the work we did getting it ready for switch over?
It's a pretty large database. This one table is 11TB on the production server.
SQL Server 2017 Enterprise Edition.

Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression.

By default replication will initialize the subscriber with a copy of the table from the publisher.  This could have dropped your existing table on the subscriber.

On the physical disk, I can see the partition files, and they are still massive with GBs of data, so I know the data is there, but it appears the table has lost the link to it.

Replication won't change files or filegroups, but if the table on those filegroups was dropped, there's no way to recover it other than restoring a backup.
If you want to copy the publication data to the subscriber before starting replication, that's called Manual Initialization.  It allows you to tell replication that you've already initialized the subscriber and to just start applying changes.
Also when you set this up again, note that it's not normally necessary to land partitions on different filegroups.  It's more complicated and doesn't perform any better, so unless you are trying to put older data on slower disks, you can just put all the partitions on the same filegroup.  Using a non-default filegroup, and adding multiple (6 is a good choice) equal-sized files to the filegroup are both good practices.