PostgreSQL performance worse when adding CPUs I ran into a strange behaviour when benchmarking PostgreSQL. The setup is like this:

Virtual box VM, with 3 GB of RAM. The host has 16GB of RAM and 4 CPUs.
Ubuntu 14.04, standard PostgreSQL 9.3 installation with the default configuration
a single database with a single table having ~4k rows

I wrote a program that runs 10k simple selects (select * from table_name where id = 1) and times how long it takes to run them all.

When I assign a single CPU to the VM, it takes ~2.5 seconds to execute the queries.

With 2 CPUs, it takes more than 4 seconds.

With 3 CPUs, it takes more than 7 seconds.

With 2 (or 3) CPUs, if I also run at the same time another program which uses 100% CPU (but doesn't use the database), performance improves.

What's going on here?

I often notice that PostgreSQL benchmarks run faster once more than half the CPUs are busy.  I think that what happens is Linux tries to keep the client and the server on different CPUs, which slows down the intense back-and-forth between them (which is the main thing your benchmark is benchmarking).  Once Linux is forced to run them on the same CPU, it gets faster.

Although I haven't seen the effect to be nearly as large as you report here.


Too much data queued in replication slots Periodically we run a batch process that sequentially backups (delete, vacuum and later remote storage in AWS' S3) for a set of about 20.000 tables with the following shell script:

#!/bin/bash
DATA_DIR=/home/admin/backup_avl_historico/data
DB_HOST=localhost
DB_USER=waypoint

#extract table list
logger 'Extracting Table List'
psql -h $DB_HOST -U $DB_USER -c "select table_name from information_schema.tables where table_name like 'avl_historico_%';" -t -o $DATA_DIR/tables.list
array=($(wc -l $DATA_DIR/tables.list))
logger ''$array
total_tables=${array[0]}
logger 'Total tables: '$total_tables

#Get max date
max_date=$(psql -h $DB_HOST -U $DB_USER -t -c "select now() - interval '12 months'")
logger 'Max date: '$max_date

array=($max_date)
#date=${array[0]}
date=${array[0]}-${array[1]}; date=${date:0:16}
logger 'Only date: '$date

#Dump each table
while read table_name
do
logger 'looping...'
    if [ ! -z "$table_name" ]; then
        logger 'Processing table '$table_name
        slots=`psql -U waypoint -h localhost -c "SELECT pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes FROM pg_replication_slots" -qt waypoint`
        while [ $slots -gt 50000000 ]
        do
            sleep 1
            echo Sleeping
              slots=`psql -U waypoint -h localhost -c "SELECT pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes FROM pg_replication_slots" -qt waypoint`
        done
        output=${table_name}_pre_${date}.csv
        psql -h $DB_HOST -U $DB_USER -t -F , -c "COPY (select * from reports.$table_name where fecha < '$max_date') TO STDOUT WITH CSV" -o ${DATA_DIR}/$output
        if [ -f ${DATA_DIR}/$output ];then
            if test -s ${DATA_DIR}/$output
            then
                logger 'Deleting records'
                psql -h $DB_HOST -U $DB_USER -c "delete from reports.$table_name where fecha < '$max_date'"
                                logger 'Gzipping '$output
                                pigz  ${DATA_DIR}/$output
                logger 'Moving to S3'
                aws s3 mv ${DATA_DIR}/$output.gz s3://backup-avl-historico/avl_historico/
                logger 'Vacuuming table'
                psql -h $DB_HOST -U $DB_USER -c "vacuum full analyze reports.$table_name"
            else
                rm ${DATA_DIR}/$output
            fi
        fi
    fi
done < $DATA_DIR/tables.list

We've recently changed improved our replication method to use replication slots,  to allow the DB handle peaks or large queues of data without de-synching between master and slave. When that change was introduced, executing the backup script would fill the slave's drive, thus rendering the process unusable. We later added a validation to wait if the replication queue exceeded 50MB but, that didn't actually help and last night the process again used all space left on the data drive of the replica node.

What could be wrong/misconfigured? What config and/or log files should I provide?

You are measuring how much WAL you have retained on the primary not yet sent to the replica.  But the hard drive filling up is the one on the replica, not the one on the master, so this is not the right thing to measure.

So what you need to measure is the amount of WAL received by the replica, but not yet replayed and eligible for removal.  You can do this by connecting to the replica (if it is in hot-standby mode) and running pg_last_wal_receive_lsn() and  pg_last_wal_replay_lsn().  But that is probably not quite right either, as just because it has been replayed doesn't mean it can be and has been removed, it still has to wait for the restartpoint.  So it would probably be better to check the disk space directly, for example by using ssh to run a df command.


Deleting NULL results Currently I'm just trying to delete objects with the Name column being Null from a temp table with the following...

delete from #PulledData
where #PulledData.Name0 = NULL

I've also tried = 'NULL', = '%NULL%' etc. but nothing seems to work. I'd appreciate some help on the matter.

delete from #PulledData where #PulledData.Name0 IS NULL


Default privileges to execute function I have a user that has select only access but was granted execute on a single function in our mysql db. The issues I'm running into is other more privileged users will drop and create this function periodically which causes my less privileged user to lose the execute privilege they were granted. Is there a way to grant a default privilege just to the one function the user needs so that when it's dropped and created I don't have to re-grant their permissions?

I find no MySQL functionality to achieve what you need, you have to create what you need.

If you use a statement like:

GRANT EXECUTE
ON FUNCTION `yourdatabase`.`yourfunction`
TO 'youruser'@'localhost';

will apply:

  14.7.1.4 GRANT Syntax

  ...

  Important

  MySQL does not automatically revoke any privileges when you drop a
  database or table. However, if you drop a routine, any routine-level
  privileges granted for that routine are revoked.

  ...

One option is to use a statement such as:

GRANT EXECUTE
ON `yourdatabase`.*
TO 'youruser'@'localhost';

of course, considering that possibly will be giving more privileges to the user really wants to give.

Another option is to have two user (internal and external) and two functions (real and mock), the real function that periodically removed and the mock function that does not eliminate. The external user only has the privilege to EXECUTE mock function and internally mock function call real function and will execute with internal user permissions.


Is password complexity policy linked to Active Directory in SQL Server We have a change in policy in the organisation and a password complexity policy will be applied to active directory. I know when Enforce password complexity is checked on a SQL Server login it gets it rules for this via windows.

However I am unsure if it is applied via AD, will this apply to SQL Server via Windows or have no effect?

As far as I know, as long as the users have been created with CHECK_POLICY they will inherit it from the domain.

If not, you'll have to enable the policy use for all of them.
Or use SQL Server 2012 BPA, as it has a check for this part (2).

  The security policy might be set in Windows, or might be received from the domain (1).

https://msdn.microsoft.com/en-us/library/ms161959(v=sql.110).aspx (1)

https://support.microsoft.com/en-us/help/2028712/understanding-password-policy-for-sql-server-logins (2)


Unable to drop non-PK index because it is referenced in a foreign key constraint I have a table named MyTable. The primary key is an identity int column named MyTableID. There is a unique clustered index on the PK column MyTableID named PK_MyTable.

I noticed there is an additional non-clustered unique index IX_MyTable_MytableID on that table with a single column MyTableID, and no other included columns. This index is obviously redundant, but when I try to delete it, I get an error message:

The constraint 'IX_MyTable_MyTableID' is being referenced by table 'OtherTable',
foreign key constraint 'FK__OtherTable__MyTableID__369C23FC'.

Why is the FK constraint relying on the non-clustered unique index instead of the primary key constraint? How do I update the FKs to use the clustered PK index instead of the other index?

Because a foreign key can point to a primary key or a unique constraint, and whoever created that foreign key possibly created it before the primary key existed (or they shifted the FK to point to the Unique index while they changed something else about the primary key). This is easy to repro:

CREATE TABLE dbo.MyTable(MyTableID INT NOT NULL, CONSTRAINT myx UNIQUE(MyTableID));

CREATE TABLE dbo.OtherTable1(ID INT FOREIGN KEY REFERENCES dbo.MyTable(MyTableID));

ALTER TABLE dbo.MyTable ADD CONSTRAINT PKmyx PRIMARY KEY(MyTableID);

CREATE TABLE dbo.OtherTable2(ID INT FOREIGN KEY REFERENCES dbo.MyTable(MyTableID));

In fact, both of these foreign keys will point to the first unique constraint defined on that column (myx).

You can fix the foreign key on the other table by dropping it and re-creating it. You will need to repeat that process for any other tables that point to this column. You can find these easily:

SELECT s.name,t.name,fk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk
ON fkc.constraint_object_id = fk.[object_id]
INNER JOIN sys.tables AS t
ON fkc.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c1
ON c1.[object_id] = fkc.referenced_object_id
AND c1.column_id = fkc.referenced_column_id
AND c1.name = N'MyTableID'
WHERE fkc.referenced_object_id = OBJECT_ID('dbo.MyTable');

Results:

dbo    OtherTable1    FK__OtherTable1__ID__32E0915F
dbo    OtherTable2    FK__OtherTable2__ID__35BCFE0A

And even generate a script to drop and re-create them (dropping the redundant unique constraint in the meantime):

DECLARE
  @sql1 NVARCHAR(MAX) = N'',
  @sql2 NVARCHAR(MAX) = N'ALTER TABLE dbo.MyTable DROP CONSTRAINT myx;',
  @sql3 NVARCHAR(MAX) = N'';

SELECT
  @sql1 += N'
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';',
  @sql3 += N'
ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY '
  + '(' + QUOTENAME(c2.name) + ') REFERENCES dbo.MyTable(MyTableID);'
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk
ON fkc.constraint_object_id = fk.[object_id]
INNER JOIN sys.tables AS t
ON fkc.parent_object_id = t.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c1
ON c1.[object_id] = fkc.referenced_object_id
AND c1.column_id = fkc.referenced_column_id
AND c1.name = N'MyTableID'
INNER JOIN sys.columns AS c2
ON c2.[object_id] = fkc.parent_object_id
AND c2.column_id = fkc.parent_column_id
WHERE fkc.referenced_object_id = OBJECT_ID('dbo.MyTable');

PRINT @sql1;
PRINT @sql2;
PRINT @sql3;
-- EXEC sp_executesql @sql1;
-- EXEC sp_executesql @sql2;
-- EXEC sp_executesql @sql3;

Results:

ALTER TABLE [dbo].[OtherTable1] DROP CONSTRAINT [FK__OtherTable1__ID__32E0915F];
ALTER TABLE [dbo].[OtherTable2] DROP CONSTRAINT [FK__OtherTable2__ID__35BCFE0A];

ALTER TABLE dbo.MyTable DROP CONSTRAINT myx;

ALTER TABLE [dbo].[OtherTable1] ADD CONSTRAINT [FK__OtherTable1__ID__32E0915F]
  FOREIGN KEY ([ID]) REFERENCES dbo.MyTable(MyTableID);
ALTER TABLE [dbo].[OtherTable2] ADD CONSTRAINT [FK__OtherTable2__ID__35BCFE0A]
  FOREIGN KEY ([ID]) REFERENCES dbo.MyTable(MyTableID);

This explicitly handles this case, where the constraint only involves a single column. It gets a little more complex if there are multiple columns involved (and this answer is not meant to solve that problem). I also didn't test if this works exactly as coded if the foreign keys point to a redundant unique index (which has the same underlying structure but is created with slightly different DDL). Exercise for the reader. :-)


How to add days to a date in PostgreSQL? I have two tables and I want to execute an update similar to this:

UPDATE table1 set table1.date = table2.date + table2.numberOfDays
FROM tabel2
WHERE table1.id = table2.table1_id

Edit: table1.date and table2.date is a date and table2.numberOfDays is a integer

I found the following solution

UPDATE table1 set table1.date = (table2.date + (table2.numberOfDays || 'day')::INTERVAL)
FROM tabel2
WHERE table1.id = table2.table1_id

OR

UPDATE table1 set table1.date = table2.date + make_interval(days => table2.numberOfDays)
FROM tabel2
WHERE table1.id = table2.table1_id


In a simple table with no complex indexes, does a large number of existing rows slow down an INSERT? I have this small, simple table:

CREATE TABLE `ads_tracker` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `type` smallint unsigned NOT NULL,
  `reference` smallint unsigned NOT NULL,
  `date` timestamp NOT NULL,
  `action` enum('view','click') NOT NULL DEFAULT 'view'
) COMMENT='' ENGINE='InnoDB' COLLATE 'utf8_unicode_ci';

It has only an index as the Primary Key, but nothing else. Will there be any noticeable difference between inserting rows now (empty table), with say 100,000 rows, and say 1,000,000 rows? I figure UPDATEs will be slower, but I'd say that an INSERT could be pretty much just as fast, as there are no indexes except for the primary key. Or am I wrong there?

When you are mass INSERTs into an InnoDB table with just a Primary Key you are building up the gen_clust_index (a.k.a. Clustered Index).

Secondary Indexes are built in conjunction with the Clustered Index. Since you have no Secondary Indexes, this reload would be just fine.

I would high recommend doing two things when reloading:

Organize your INSERTs as extended INSERTs
Set bulk_insert_buffer_size to 512M or 1G

When you use the bulk insert buffer, it helps load the data into a treelike structure that makes it easier to load into a non-empty table.


Database cannot be opened. It is in the middle of a restore? I've set up log shipping using standby mode （The "Disconnect users in the database when restoring backups" is unchecked).

I tested running the following code on secondary server/database.

declare @a int
while 1=1
begin
  select @a = count(*) from ....
end

However, the code stops with the following error when log shipping restore job start to run.

  Database cannot be opened. It is in the middle of a restore.

How to avoid the issue?

This is kind of how log shipping has to work. You need exclusive access to a database to apply a log backup, which means your script has to set the database to single_user or otherwise evict all the users before it can apply the log backup(s).

Some workarounds:

Schedule your restores for outside of business hours, and let them continue querying stale data in the meantime.
Queue up multiple log backups and apply them, say, every hour or every two hours. Users get periodic updates throughout the day, with interruptions less frequent than the log backup schedule on the primary.
Don't force your script to kick users out. This means the log restore will wait and wait and wait until there are no active connections to the database. I've never actually tried this, and its feasibility really depends on how active your secondary is.
Have multiple secondaries and cycle through them. I've written pretty extensively about this approach.


WHAT RDBMS ( SQL SERVER/ ORACLE ) close to manage MYSQL(DBA) i have a requirement for learning MYSQL for DBA. here all training institutes teach on ORACLE AND SQL SERVER.
SQL is mandatory to work on RDBMS. what RDBMS SYNTAX CLOSE TO MYSQL for DBA(backups, restore, performance tuning, etc  )

You can refer MySQL certification exam program and read MySQL documentation to know better about MySQL.

MySQL is much easy to setup and learn than SQL server and Oracle.

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=458&get_params=p_track_id:MDBA


Few questions related to MySQL Shell In the MY SQL documentation Instance Dump Utility, Schema Dump Utility, and Table Dump Utility

The compatibility option - strip_tablespaces
Does it remove all references to tablespaces or only in the GRANT statement. Can you please provide an example for how this option makes change in the dump statement?

consistent

The documentation mentions that backup locks are released before the backup then how are consistent backups taken? START TRANSACTION WITH CONSISTENT SNAPSHOT is mentioned. Does it take snapshots?
If yes, what is the memory requirement?
Thank you!

It removes them from the GRANT / CREATE statements as per the doc you linked and also per this documentation: Importing and Exporting Databases

Only the global read lock is released when the backups are started per the documentation you linked:

When all threads have started their transactions, the instance is
locked for backup and the global read lock is released.

In other words, the data is read safe but likely still holds other locks preventing the data or schema from being changed during the backup process (aka blocking transactions).
This website goes into good detail on global locks and specifically read locks in MySQL which is relevant to this question: Deep Understanding of MySQL Global Lock and Table Lock
Specifically this part mentions what type of statements are locked:

A global lock is to lock the entire database instance. MySQL provides
a way to add a global read lock. The command isFlush tables with read
lock (FTWRL)。
When you need to make the whole library read-only, you can use this
command, and then the following statements of other threads will be
blocked: data update statements (data addition and deletion), data
definition statements (including table building, table structure
modification, etc.) and submission statements for updating class
transactions.

So yes, technically the data is a snapshot in time because no other transactions are allowed to change the data or schema during the backups. The amount of memory needed is going to be dependent on the amount of data being backed up.


Managing SQL Server MDF and LDF file size I am new to SQL Server and don't fully understand its architecture. I've looked around, Googled, asked around but  found no simple (understandable) answer to the problem. I'm not even sure if it  is problem in technical terms or just the default behavior, but in terms of my business operation, it is a big problem.

I have acquired SQL Server 2008 R2 [from the developer who doesn't work here anymore], with a database of physical storage size of 2 TB. I've created a database, say TEST with

TEST.mdf default at 10 MB, 10% growth and max size: unlimited. There are 40 files comprising the TEST.mdf file say TEST_part01 through TEST_part40 with same configuration as TEST.mdf.
The log file - TEST_log.ldf file also has same configuration as TEST.mdf
The database handles millions of rows in daily basis, with lots and lots of insert, delete, update, drop etc. operations.
I am running out of space. While trying to shrink the database, I saw that the TEST.mdf could be shrunk by 41% and TEST_log.mdf by 99%.

Questions

Is 41% and 99% of recoverable space actually taken by garbage, un-used data? If so can   I selectively remove/purge (if not all than most of ) the 41% and 99% of data?
Is there any temporary or otherwise structure/objects created by the SQL Server itself,  which I can safely delete/purge?

If you just acquired an instance of SQL Server from someone, I would first ask have you gone over the instance configuration and all to understand how the previous person had it configured?

I would suggest taking a look at Brent Ozar's sp_BLITZ script. It is a stored procedure that will collect some good information for you on your instance configuration and give you a good overview of what it sees. I would read over Brent's blog on this script in detail before ever running this on your server, FULLY understand what it is doing.

Regarding your database, are you running out of disk storage or database storage? How much free space does the database contain? I would first understand the architecture of the files before I start making changes. A good article to read on file management is from SQLSkills.com here.

Just my $0.02...


Where clause for multiple rows and columns I have a UDT that receive 2 parameters which is ID and Quality

CREATE TYPE [dbo].[ItemUdt] AS TABLE(
[ID] [int] NOT NULL,
[Quality] [int] NULL
)

This UDT will be use as condition to filter records from a table

@ItemList AS ItemUdt READONLY

What i trying to do is get the records that match both ID and Quality

SELECT * FROM tbl_Item
WHERE [ID] IN (SELECT ID FROM @ItemList)
AND [Quality] IN (SELECT Quality FROM @ItemList)

However, above query will match all listed ID that consists of all Quality from the UDT.
For example, if the UDT consists of (1001, 'Good'),(1002, 'Bad'), the result will display all the ID with 1001 and 1002 together with Quality of 'Good' and 'Bad'. But my expected result is as below:

SELECT * FROM tbl_Item
WHERE ([ID] = '1001' AND [Quality] = 'Good')
OR ([ID] = '1002' AND [Quality] = 'Bad')

Is there any way I can write a query that match above format?

Some DBMSs allow tuples to be used in IN.

WHERE  (ID, Quality ) IN (SELECT ID, Quality FROM @ItemList)

SQL Server isn't one of then and just allows a single column.

You can use WHERE  EXISTS

SELECT *
FROM   tbl_Item tI
WHERE  EXISTS (SELECT *
               FROM   @ItemList I
               WHERE  I.ID = tI.ID
                      AND I.Quality = tI.Quality)


Alternative to second column for tracking a FOREIGN KEY pre-deletion Let's say I have a table A, that has one column: just an ID. This is obviously the primary key.

Next, I have a second table, which has an ID for table A, and then a second ID. The primary key is on it's own ID. Now I want to be able to delete rows from A, but not lose the foreign key in B.

There's no ON DELETE SKIP INTEGRITY or anything, so I have to use a trigger and second column to retain it:

CREATE TABLE Test1
(
    Id INT NOT NULL,
    CONSTRAINT PK_Test1 PRIMARY KEY (Id)
)

CREATE TABLE Test2
(
    Test1Id INT NULL,
    OldTest1Id INT NULL,
    Id INT NOT NULL,
    CONSTRAINT PK_Test2 PRIMARY KEY (Id),
    CONSTRAINT FK_Test2_Test1Id_Test1_Id FOREIGN KEY (Test1Id) REFERENCES Test1 (Id) ON DELETE SET NULL
)
GO

CREATE TRIGGER Test2_OldTest1Id ON Test2
AFTER INSERT, UPDATE
AS
UPDATE Test2
SET OldTest1Id = Test1Id
WHERE
    EXISTS(SELECT 1 FROM inserted WHERE inserted.Id = Test2.Id)
    AND Test1Id IS NOT NULL
GO

Are there any alternatives to this that don't require a TRIGGER or second column? Is there any way I can force referential-integrity for all but DELETE statements? I don't care about tracking what it is now vs. what it was, I just want to retain that FOREIGN KEY value forever, even if it's deleted from the referenced table.

I have a sample db<>fiddle to illustrate:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ba02ec43fff23231cb5715aaa265c1e5

I've worked with a SQL Server DB where the relationship between two tables was maintained by triggers instead of actual foreign keys (without the need for the second column).

Basically, in the "child" table:

on INSERT, check to make sure the inserted value in the "FK" column exists in the "parent" table;
on UPDATE, check to make sure any updated value in the "FK" column exists in the parent table.

In the "parent" table (using an AFTER trigger or equivalent):

on UPDATE, if the "PK" value is changed, take whatever action is appropriate if there are rows in the "child" table using the PK (change the "FK" column to  match; prevent the update if there are "children" already using the current value; do nothing; etc.)
on DELETE, take whatever action is appropriate if there are rows in the "child" table using the PK (prevent the deletion; set the value to NULL; or, in your case, do absolutely nothing).

Since the DB is not maintaining the relationship, you need to make sure you handle each of the possibilities above properly. It'll need to be documented as a known "gotcha" of the DB design, something that new devs get cued in on quickly, so they don't make assumptions regarding it.

Also, remember that disabling triggers also disables this "foreign key" relationship (unlike a true FK).

I'm not necessarily saying this would be better than your history column solution (which has its own maintenance hassles), but it is an option.


How to determine the name of a process that caused a trigger to fire Short Version:
Does anyone know of a way --inside a SQL 2000 trigger-- of detecting which process modified the data, and exiting the trigger if a particular process is detected?

Long Version
I have a customized synchronization routine that moves data back and forth between dis-similar database schemas.

When this process grabs a modified record from Database A, it needs to transform it into a record that goes into Database B. The database are radically different, but share some of the same data such as user accounts and user activity (however even these tables are structurally different).

When data is modified in one of the pertinent tables, a trigger fires which writes the PK of that record to a "sync" table.  This "sync" table is monitored by a process (a stored proc) which will grab the PK's in sequence, and copy over the related data from database A to database B, making transformations as necessary.

Both databases have triggers that fire and copy the PK to the sync table, however these triggers must ignore the sync process itself so as not to enter into "endless" loop (or less, depending on nesting limits).

In SQL 2005 and up, I use the following code in the Sync process to identify itself:

SET CONTEXT_INFO 0xHexValueOfProcName

Each trigger has the following code at the beginning, to see if the process that modified the data is the sync process itself:

IF (CONTEXT_INFO() = 0xHexValueOfProcName)
BEGIN
--  print '## Process Sync Queue detected. This trigger is exiting! ##'
  return
END

This system works great, keep chugging along, keeps the data in sync.  The problem now however is that a SQL2000 server wants to join the party.

Does anyone know of a way --inside a SQL 2000 trigger-- of detecting which process modified the data, and exiting the trigger if a particular process is detected?

Thanks guys!

SET CONTEXT_INFO exists for SQL Server 2000 too.

However, you have to query sysprocesses.context_info to read it back. CONTEXT_INFO() was new for SQL Server 2005.


Select same records that only have different values for a particular column Below is an example of my customer table. There some records having multiple values in BIRTHDAY DATE (by mistake or so). I only want to select those records that have same values for LASTNAME, MIDDLENAME, FIRSTNAME, SSN but different BIRTHDAY:

Member table

LASTNAME   MIDDLENAME   FIRSTNAME    SSN      BIRTHDAY
Jones      M            Carol        1234     17-DEC-45
Jones      M            Carol        1234     17-DEC-45
Jones      M            Carol        4425     20-APR-70
Black      S            Ted          5555     15-MAY-57
Roberts    T            Cole         1412     14-MAY-57
Roberts    T            Cole         1412     20-OCT-57
Roberts    S            Cole         1412     15-MAY-57

I would like the result to be:

LASTNAME   MIDDLEANME  FIRSTNAME    SSN      BIRTHDAY
Roberts    T           Cole         1412     14-MAY-57
Roberts    T           Cole         1412     20-OCT-57

Notice that there were few accounts with same SSN or full name in the table, they are not selected since they don't have everything same. Also Jones M. Carol with 1234 as SSN is not selected either since she does not have different Birthday for two different account.

This is my SQL query that I have so far and this is not working well necessarily.

SELECT x.FIRST_NM, x.MDL_NM, x.LAST_NM, x.SSN, x.BRTH_DT
FROM Member_table x
WHERE EXISTS
(
    SELECT FIRST_NM, MDL_NM, LAST_NM, SSN, COUNT(*)
    from Member_table
    WHERE CURRENT_RECORD_IN = 'Y'
    group by FIRST_NM, MDL_NM, LAST_NM, SSN
    having count(distinct BRTH_DT) > 1
)
ORDER BY FIRST_NM ASC, LAST_NM ASC, MDL_NM ASC, SSN ASC;

Any advice for this query?

Here's an example of using EXISTS and a correlated subquery.  I tested on SQL Server, but will probably work on other RDBMS's.

drop table if exists table1
CREATE TABLE Table1
    (LASTNAME varchar(7), MIDDLENAME varchar(1), FIRSTNAME varchar(5), SSN int, BIRTHDAY varchar(9))
;

INSERT INTO Table1
    (LASTNAME, MIDDLENAME, FIRSTNAME, SSN, BIRTHDAY)
VALUES
    ('Jones', 'M', 'Carol', 1234, '17-DEC-45'),
    ('Jones', 'M', 'Carol', 1234, '17-DEC-45'),
    ('Jones', 'M', 'Carol', 4425, '20-APR-70'),
    ('Black', 'S', 'Ted', 5555, '15-MAY-57'),
    ('Roberts', 'T', 'Cole', 1412, '14-MAY-57'),
    ('Roberts', 'T', 'Cole', 1412, '20-OCT-57'),
    ('Roberts', 'S', 'Cole', 1412, '15-MAY-57')
;

SELECT *
FROM table1 t1
WHERE EXISTS (
        SELECT *
        FROM table1
        WHERE LASTNAME = t1.LASTNAME
            AND MIDDLENAME = t1.MIDDLENAME
            AND FIRSTNAME = t1.FIRSTNAME
            AND SSN = t1.SSN
            AND BIRTHDAY <> t1.BIRTHDAY
        )

| LASTNAME | MIDDLENAME | FIRSTNAME | SSN  | BIRTHDAY  |
|----------|------------|-----------|------|-----------|
| Roberts  | T          | Cole      | 1412 | 14-MAY-57 |
| Roberts  | T          | Cole      | 1412 | 20-OCT-57 |


Using listener_name in 4 part naming convention gives sys.servers error App uses cross db connections, the dbs are in different AG's, so I need them to use 4 part naming convention - in the place of servername would be listener name. But when I run a simple query like:

select * from Listener_Name.DatabaseName.[schemaname].[objectname]

I get:

  Could not find server 'Listener_Name' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Listener_Name is not in sys.servers table.

My current environment has 2 nodes and 3 AGs, each having their own listeners.  Do I need to add the 3 listener names as linked servers on both nodes? And does anyone have a script on how best to create the linked server using listener name?   I would like it to be similar to the local server which shows up on the sys.servers table with server_id 0.

There isn't one place where I found everything written properly, which maybe is because not many people use cross AG dist txs,  so this is what needs to be done:

Have app make code change to use 4 part naming convention using the appropriate listener_name.
Create linked servers for all listeners on all nodes:

Script is from  https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-clustered-dtc-for-an-always-on-availability-group?view=sql-server-ver15 step 12:

IF NOT EXISTS (SELECT * FROM sys.servers where name = N'Listener1')
BEGIN
    EXEC master.dbo.sp_addlinkedserver @server = N'Listener1';
END

Do the same for the other listeners. above should suffice - you can do a distributed query test now (its given in the same step 12 of above link) , but as I am on SQL Server 2016 SP2+, I did this also:

ALTER AVAILABILITY GROUP AG_Name

   SET (

      DTC_SUPPORT = PER_DB

      );

Did the same for other AG's per  https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-availability-group-for-distributed-transactions?view=sql-server-2017#manage-unresolved-transactions


Grant read access to my SQL Server database to all future SQL users Can I configure my SQL Server 2014 database so that any login that will be created in the future will have read access to it automatically?

To add on my comment, you can create a trigger on a database level and add user to desired role automatically when you create a user. You can add a custom one, but for example sake i added db_datareader.

Example :

    create trigger tg_AddUserToRole on database after CREATE_USER
    as
    declare @LoginName nvarchar(30)
    set @LoginName =  (select EVENTDATA().value('(EVENT_INSTANCE/ObjectName)[1]','nvarchar(30)'))
    IF EXISTS(select 1 from sys.sysusers where name = @LoginName and  issqluser = 1)
    exec sp_addrolemember 'db_datareader', @LoginName


Dump first N documents of a collection with mongodump I am very new to MongoDB and I would like to know how to dump the first N elements of a collection with mongodump. I can't figure out the query I have to use.

And how would you dump the last N elements?

There is no direct way to limit number of records to export via mongodump; however it is possible indirectly, using the query parameter.

First you need to find last(n) documents you need to export.

Let's say you want to export top 50 documents. First find 50th document _id – if you are finding first(n), then sort ascending order and use $lte operator down in query.

db.col.find({},{_id:1}).sort({_id:-1}).skip(49).limit(1)

Note down the _id value.

Now pass your _id to mongodump and run it from console. (not from mongo shell)

mongodump \
--db <some_db> \
--collection <some_col> \
--query '{_id:{$gte:ObjectId("previously noted _id")}}'

Note: this solution exports the last(n) documents instead of first(n), but it is simple to adapt it.


Protect SQL Server from hackers Today, my SQL Server 2008 is attacked by hacker from IP address in China [4300 Login failure in last 7 hours]. I contacted GoDaddy support and below is what they told me.

EXEC sp_readerrorlog 0, 1, 'Login failed' [and result 4300+ failed login and still increasing]

  As for the SQL Server brute force attack against your server you would need to determine the best method for resolving that on the server. On some systems the best solution is to simply update the firewall on the server to block access to all connections to the port for SQL Server, 1433, so that only a specific IP can access that port, however the security of your server would be up to you and as such we would not be able to guide you on changing the scope of a windows firewall rule. You may wish to review Microsoft's knowledge base for information on using and modifying the windows firewall on your server.

How can I  update the firewall on the server to block access to all connections to the port for SQL Server, 1433, so that only my own IP address can access the SQL Server?

Here is what i have done so far (Next day):
I disabled the SA account, since all the attack was on SA username.

I tried to add a inbound rule in firewall for Port number 1433 to block anonymous IP address.
I tried to add a inbound rule in firewall for Program(sqlserver.exe) to block anonymous IP address.

but no WIN yet. login failed attempts is continue to increasing. Can someone guide me how to block anonymous IP on the firewall to access the SQL Server.

This really isn't an SQL Server issue - it seems more of a firewall issue.

If you are in a situation where your SQL Servers are directly accessible from dudes in china - I'd be sorting out why no one in your outfit is handling networking/firewalls etc

It sounds quite possible that you need to be worried about more servers than just the one you've noticed getting tickled!


Hitting a transactional table, or copying the data to a temporary table? I have the following query (Pseudo SQL warning):

select count(*), groupcolumn from table
where date is < 6 days ago  and > 7 days ago
AND date is < 13 days ago
AND date > 14 days ago... (until 28 days ago)
group by groupcolumn

I was told to optimize this as:

select count(*), groupcolumn from (
select columns from table
 where date is < 6 days ago  and > 7 days ago
union all
select columns from table
where date is < 13 days ago  and > 14 days ago
union all
(repeat until 28 days ago)) group by groupcolumn

This hits the table several times but only brings the rows i need (60K of them for the 4 days specified).

The DBA then told me this query is no good because "it's hitting a transactional table multiple times and it's bad performance". The table in question has records being inserted continuously, a couple to a dozen of them every second.

His suggestion was to change the query to:

select "only the minimum columns i need, if possible only IDs"
into #temptable
from table where date is < 6 days ago  and > 28 days ago

And then do my original query against "this much smaller table that also doesn't interfere with the transactional table".

The DBA tells me this query is better because "it's not hitting the transactional table so much". It counter intuitive to me, though, since I'm bringing 15K records per day. So instead of copying 60K records to memory,  I'll now be copying 420K records to disk, and running a second query against those.

I'm using SET TRANSACTION LEVEL READ UNCOMMITTED and WITH(NOLOCK) for every table. I don't mind dirty reads as I'm just making approximate statistics, so I don't see why "hitting the table so many times" would be an issue here.

It could reduce the time you access the table, and possibly the execution time of your query.

If that is true will depend on the indexes on the table, the size of the table, ....

An example of executing your query on an unindexed heap:

While the rows returned are 15K, the residual I/O on the predicate is high, 120K rows for each table scan.

As you already know, adding an index could change this to a seek predicate.

Reducing the time you would access the table can be done by using a temp table.

Insert the data

Add an index

Do index seeks on your temp table

Conclusion

While reducing the time accessing the table, the execution time in total was twice as long on my test server. To help you confirm if it is a solution for you, we would need the table definition, the actual query and additionaly the test data.

If it is not an option to share these things, then you could do the test yourself, and share the execution plans and the SET STATISTICS IO,TIME output.

Test data used

CREATE TABLE CountingDays(CountingDaysId int identity(1,1), groupcolumn varchar(255), countingdate datetime2)

SET NOCOUNT ON;
DECLARE @i int = 0
WHILE @i < 15000
BEGIN
INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(1,getdate())

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(2,dateadd(day,-4,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(3,dateadd(day,-7,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(@i,dateadd(day,-8,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(1,getdate())

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(2,dateadd(day,-3,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(3,dateadd(day,-6,getdate()))

INSERT INTO CountingDays(groupcolumn,countingdate)
VALUES(@i,dateadd(day,-5,getdate()))

SET @i+=1

END

SET STATISTICS IO, TIME ON;
select count(*), groupcolumn from (
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-3,getdate()) and countingdate >= dateadd(day,-4,getdate())
union all
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-4,getdate()) and countingdate >= dateadd(day,-5,getdate())
 union all
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-5,getdate()) and countingdate >= dateadd(day,-6,getdate())
union all
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-6,getdate()) and countingdate >= dateadd(day,-7,getdate())
 union all
select groupcolumn from CountingDays
 where countingdate < dateadd(day,-7,getdate()) and countingdate >= dateadd(day,-8,getdate())
union all
select groupcolumn from CountingDays
where countingdate < dateadd(day,-8,getdate()) and countingdate >= dateadd(day,-9,getdate())) a
group by groupcolumn

CREATE TABLE #temp(groupcolumn varchar(255), countingdate datetime2)
INSERT INTO #temp(groupcolumn,countingdate)
select groupcolumn,countingdate FROM CountingDays
CREATE INDEX IX_Countingdate on #temp(Countingdate) include(groupcolumn)

SET STATISTICS IO, TIME ON;
select count(*), groupcolumn from (
select groupcolumn from #temp
 where countingdate < dateadd(day,-3,getdate()) and countingdate >= dateadd(day,-4,getdate())
union all
select groupcolumn from #temp
 where countingdate < dateadd(day,-4,getdate()) and countingdate >= dateadd(day,-5,getdate())
 union all
select groupcolumn from #temp
 where countingdate < dateadd(day,-5,getdate()) and countingdate >= dateadd(day,-6,getdate())
union all
select groupcolumn from #temp
 where countingdate < dateadd(day,-6,getdate()) and countingdate >= dateadd(day,-7,getdate())
 union all
select groupcolumn from #temp
 where countingdate < dateadd(day,-7,getdate()) and countingdate >= dateadd(day,-8,getdate())
union all
select groupcolumn from #temp
where countingdate < dateadd(day,-8,getdate()) and countingdate >= dateadd(day,-9,getdate())) a
group by groupcolumn

DROP TABLE #temp


Azure SQL Elastic Pools - When to Create a new one versus upping the eDTU? I've tried to Google looking for others with the same question for about an hour now and can't find anything close, so hoping someone here might be able to offer some insight!
I'm a many-hat admin with a small software company the provides SaaS service solutions to our clients, we use Azure SQL, with Elastic Pools to host around 100 individual customer databases currently that run quite happily on a Standard Tier 100 eDTU Elastic Pool. Utilisation through the day runs between 60-80% which for our application is quite a comfortable buffer in case one of the clients should go crazy with a report query or bulk data load.
We are about to bring another 100 or so clients currently hosted on legacy VMs running SQL classic over into Azure SQL as well.
My questions is: Do I place them in the current elastic pool and up the eDTU to 200, or do I create a new separate 100 eDTU elastic pool and place them there?
Database size and Min/Max DTU per database settings, and the marginal difference in costs aren't considerations. So given that all the relative resource limits effectively double between 100 and 200 eDTU (concurrent workers/connections/sessions etc), is it just a question of how they are logically grouped?
What considerations would go into this choice? Is there something I'm missing?

It seems that you are below the limits in both cases, so it seems pretty the same.
These are the limits:

As you can see, the only thing that really seems to matter in your case and that donsen't double when you switch from 100edtu to 200edtu, is the max concurrent sessions.
So, if you are far below this limit, I suggest you to choose basing on the easiest design.
If you are reaching the session limit per pool, it's better to split it up in two 100 edtu pool.
Don't change winning team. :-)


Error on Inner Join from Nested Select Statements I am trying to flatten an EAV style table. But I need to add a couple of columns from other tables. I read online that doing nested selects might be a good approach, though when I am getting an error on the last 2 joins (m6.meta_value and m7.meta_value)
"Multi-part identifier could not be bound". I am using SQL Server 2016.

Does anyone know how I can still get my joins and work around this error?

SELECT distinct patients.ID ,
(SELECT meta_value FROM USERMETA m1 WHERE patients.ID = m1.USER_ID AND m1.meta_key = 'first') AS FirstName,
(SELECT meta_value FROM USERMETA m2 WHERE PATIENTS.ID = m2.USER_ID AND m2.meta_key = 'last') AS LastName,
(SELECT distinct meta_value FROM USERMETA m3 WHERE PATIENTS.ID = m3.USER_ID AND m3.meta_key = 'customer') AS CustomerID,

(SELECT meta_value FROM USERMETA m6 WHERE PATIENTS.ID = m6.USER_ID AND m6.meta_key = 'clinic') AS Clinic,
isnull(clinics.name, 'No Clinic') as 'Name',
(SELECT meta_value FROM USERMETA m7 WHERE PATIENTS.ID = m7.USER_ID AND m7.meta_key = 'doctorsid') AS DoctorID

from users patients
INNER JOIN user_group ON user_group.user_id = patients.ID AND user_group.group_id != 22 AND user_group.group_id = 5
INNER JOIN revision ON patients.ID = revision.wp_users_id AND revision.revision_types = 2
INNER JOIN doctors doc ON m7.meta_value = doc.bt_id  --"m7.meta_value" has error
INNER JOIN fronts ON m6.meta_value = fronts.id       --"m6.meta_value" has error

group by patients.id
order by patients.ID

Some of the meta_key= values changed between updates, but you should still get the point of this.

Using conditional aggregation inside a common table expression to flatten out your EAV style table instead of subqueries:

;with patients as (
  select
      p.ID
    , FirstName     = min(case when meta_key = 'first_name' then meta_value end)
    , LastName      = min(case when meta_key = 'last_name' then meta_value end)
    , CustomerID    = min(case when meta_key = 'customerid' then meta_value end)
    , PartnerClinic = min(case when meta_key = 'partner-clinic' then meta_value end)
    , DoctorID      = min(case when meta_key = 'doctorsid' then meta_value end)
  from users as p
    inner join user_group as ug
      on ug.user_id = p.id
     and ug.group_id != 22
     and ug.group_id = 5
    inner join revision as r
      on p.id = r.wp_users_id
     and r.revision_types = 2
    left join usermeta m
      on p.id = m.user_id
  group by p.id
)
select
    p.*
  , Name          = isnull(f.name, 'No Clinic')
  , Doctor        = isnull(d.name, 'No Doctor')
from patients as p
  left join doctors as d
    on p.DoctorID = d.bt_id
  left join fronts as f
    on p.PartnerClinic = f.id
order by p.ID


SQL Server agent job question Scenario:

I am trying to understand a strange situation. I have a SQL Server 2012 having DB engine and SQL Server agent configured using same domain account. That domain account isn't added to SQL Server logins. Checked using xp_logininfo 'Domain account', 'all' and didn't get any output. I have a SQL server job which updates a table in a DB. That job is running fine. Till now I was under the impression that SQL server agent account has to be defined in SQL Server login in-order to run the job. However, in this case the job was running fine even though SQL Server agent login is not defined in SQL Server logins. So trying to understand how it is possible. Also, since the job updates a table don't know which user/login it uses to update the table. As a test, I changed the job owner to 'sa' and non 'sa' login in both the cases the job is working fine.

So my questions are:

Is it mandatory to have SQL Server agent account defined in SQL server logins to run the job? If yes, then how come my update job is running even though there is no login present for SQL Server agent account in SQL Server logins. If no, how it internally works? Like which account it uses to start the SQL job.
What user/login my update job is using in order to update the table?

1. Is it mandatory to have SQL Server agent account defined in SQL
server logins to run the job?
See this and this.
The SQL Server Agent runs as a Windows service named NT SERVICE\SQLSERVERAGENT. The NT SERVICE\SQLSERVERAGENT login is how the Windows process that is SQL Server Agent connects to the Database Engine to read the msdb database to find out what it should do; and then do it. Both of these logins are members of the sysadmin fixed server role, so they can do anything in the Database Engine. And they need to stay that way.
As for the account that you used to run the services, this is complicated and has changed in each version of SQL Server.The short answer is that the account you specify will be used when a process tries to reach outside of the current Windows environment. But within the computer, there is a mix of authorization granted to the domain user, the service, and the Windows group SQLServerMSSQLUser$computername$MSSQLSERVER.
There are permission in the operating system that an agent account gets by default. Details here.
As per books online:

The account that the SQL Server Agent service runs as must be a member
of the following SQL Server roles:
The account must be a member of the
sysadmin fixed server role.
To use multiserver job processing, the
account must be a member of the msdb database role TargetServersRole
on the master server.
In all Windows versions, permission to log on as a service (SeServiceLogonRight)

But it should not be a member of local administration group.

We recommend choosing a Windows user account that is not a member of
the Windows Administrators group. However, there are limitations for
using multiserver administration when the SQL Server Agent service
account is not a member of the local Administrators group

2. What user/login my update job is using in order to update the table?
Reference: https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/
Job ownership is an important concept in SQL Server Agent. SQL Server Agent sets the security context for job execution based on the role of the user owning the job. By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account.
The exception to this rule is T-SQL job steps, which execute under the security context of the job owner. If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.


how to shoot SQL error 823? I had a laptop die HDD went dead which a running sql database was opened (in program)
I had to have the hard drive recovered however all i got back was a bad database which has the torn page.
All pro software seams to be able to read my database and fix if i pay the 399.00 for the software however i was hoping i could use the built in DBCC However it seams everything i try wont work so i am hoping for some help.
Here is the error when I try to bring the database online

  Msg 945, Level 14, State 2, Line 1 Database 'CompassDefaultDB' cannot
  be opened due to inaccessible files or insufficient memory or disk
  space.  See the SQL Server errorlog for details. Msg 5069, Level 16,
  State 1, Line 1 ALTER DATABASE statement failed. Msg 824, Level 24,
  State 2, Line 1 SQL Server detected a logical consistency-based I/O
  error: incorrect checksum (expected: 0x20203e65; actual: 0x1f329010).
  It occurred during a read of page (1:9) in database ID 5 at offset
  0x00000000012000 in file 'C:\Program Files\Microsoft SQL
  Server\MSSQL.2\MSSQL\Data\CompassDefaultDB_51120120410_data.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

So Then I Try

DBCC CHECKDB (CompassDefaultDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

That give me

  Msg 945, Level 14, State 2, Line 1 Database 'CompassDefaultDB' cannot
  be opened due to inaccessible files or insufficient memory or disk
  space.  See the SQL Server errorlog for details.

So Then I Try

ALTER DATABASE CompassDefaultDB SET EMERGENCY;

  Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical
  consistency-based I/O error: incorrect checksum (expected: 0x20203e65;
  actual: 0x1f329010). It occurred during a read of page (1:9) in
  database ID 5 at offset 0x00000000012000 in file 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL.2\MSSQL\Data\CompassDefaultDB_51120120410_data.mdf'.
  Additional messages in the SQL Server error log or system event log
  may provide more detail. This is a severe error condition that
  threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error
  can be caused by many factors; for more information, see SQL Server
  Books Online.

Then

DBCC CHECKDB (CompassDefaultDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

  Msg 922, Level 14, State 1, Line 1 Database 'CompassDefaultDB' is
  being recovered. Waiting until recovery is finished.

It stays in this mode forever and does not seam to finish
this database is only 2MB with like 17 Rows of data i am after.
I can restart the sql server and repeat the same steps with the same results.
Is there something i am doing wrong?
any help is greatly appricated

I do have the origional corrupted database and have tried to restore and repeat but with the same results
I have also tried to bring up a database with the same name delete the mdf file and replace with me mdf file with the same basic results.
There is no backup :(
Thanks again for looking

Attempting to repair the database isn't going to help.  Page 1:9 is a system object and system objects can't be repaired.  You'll want to script out the tables and create a new database.  Then create the tables in the new database, and copy over the data into the new database.

If the database won't open you can try setting it to emergency mode using ALTER DATABASE MyDB SET EMERGENCY which may bring the database online.  If you does you can script the data out. If not you're basically screwed.

If you had a backup...
Another option would be to restore the database to a point in time before the page became corrupt.  If you have a backup from that far back you can restore that backup, then roll the transactions logs forward and you should be able to get back to a state where you have all the current data without the corruption.


Service Broker SEND performance Previously I asked about latch contention / locks which can happen during Service Broker work: Service Broker locks.
I got a great answer on general question but I still have narrowly focused question.
MSDN ( SEND ):

Messages that are sent to services in other instances of the SQL Server Database Engine are stored in a transmission queue in the current database until they can be transmitted to the service queues in the remote instances. Messages sent to services in the same instance of the Database Engine are put directly into the queues associated with these services.

Question: Is there a way to force a message to be put in a local transmission_queue even if services are located on the same Database Engine?
It could be preferred solution in my circumstances where I have sufficient hardware resources but sometimes I get latch contention.
Why it can help is described in the link above

Test after David's answer.
Preparation:
-- Source DB
use master
go
drop database [TestSendMessage]
go
create database [TestSendMessage]
go
alter database [TestSendMessage] set enable_broker with rollback immediate;
go
use [TestSendMessage]
go
create message type datachanges_messagetype
  validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.TestSendMessage_initiatorqueue
  with status = on
     , retention = off
    , poison_message_handling ( status = on )
  on [default];
go
create service TestSendMessage_initiatorservice
  on queue TestSendMessage_initiatorqueue
  ( datachanges_contract );
go
DROP ROUTE [AutoCreatedLocal]
go
CREATE ROUTE Forwarder
    WITH
    ADDRESS = 'TCP://localhost:4022' ;
go
grant send on service::[TestSendMessage_initiatorservice] to public;
go

-- Target DB
use master
go
drop database [TestReceiveMessage]
go
create database [TestReceiveMessage]
go
alter database [TestReceiveMessage] set enable_broker with rollback immediate;
go
use [TestReceiveMessage]
go
create message type datachanges_messagetype
  validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.TestReceiveMessage_destinationqueue
  with status = on
     , retention = off
    , poison_message_handling ( status = on )
  on [default];
go
create service TestReceiveMessage_destinationservice
  on queue TestReceiveMessage_destinationqueue
  ( datachanges_contract );
go
DROP ROUTE [AutoCreatedLocal]
go
CREATE ROUTE Forwarder
    WITH
    ADDRESS = 'TCP://localhost:4022' ;
go
grant send on service::[TestReceiveMessage_destinationservice] to public;
go

-- endpoint
if not exists ( select * from sys.endpoints where name = 'ServiceBrokerEndpoint' )
create endpoint ServiceBrokerEndpoint
  state = started
  as tcp ( listener_ip = (127.0.0.1), listener_port = 4022)
  for service_broker();
go

-- Tests
use [TestSendMessage]
go

declare @handle  uniqueidentifier = null

begin dialog conversation @handle
                    from service TestSendMessage_initiatorservice
                    to   service 'TestReceiveMessage_destinationservice'
                      on contract datachanges_contract
                    with encryption = off;

select @handle; -- 0EC9CCEE-13D3-EA11-B4E1-983B8F11A147
;send on conversation '0EC9CCEE-13D3-EA11-B4E1-983B8F11A147' message type datachanges_messagetype( '[{aaa:1}]' );
select * from sys.transmission_queue

And yeah I see that local sys.transmission_queue is used using query and XE Trace:

BUT! Some time later ( maybe several seconds, I don't know exactly ) if I send several messages using the conversation, I do not see messages in sys.transmission_queue and that's what's in the XE:

IF I create a new conversation or new_broker I'll get the same situation: sys.transmission_queue usage at the beginning and then - no
select @@version

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)
    Mar 18 2018 09:11:49
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18363: ) (Hypervisor)

Perhaps the routing configuration change didn't take effect immediately.  To be sure test with a new broker.  Try this from a database other than the database containing the target service:
CREATE EVENT SESSION [ServiceBrokerRouting] ON SERVER
ADD EVENT sqlserver.broker_dialog_transmission_body_dequeue,
ADD EVENT sqlserver.broker_dialog_transmission_queue_enqueue,
ADD EVENT sqlserver.broker_forwarded_message_sent,
ADD EVENT sqlserver.broker_message_classify,
ADD EVENT sqlserver.broker_remote_message_acknowledgement

GO
ALTER EVENT SESSION [ServiceBrokerRouting] ON SERVER STATE = START;
go
DROP ROUTE [AutoCreatedLocal]
alter database current set single_user with rollback immediate
alter database current set new_broker
alter database current set multi_user

--Watch Live Data on the ServiceBrokerRouting event session
--test to ensure that new messages are delayed

--add the forwarder route
CREATE ROUTE Forwarder
    WITH
    ADDRESS = 'TCP://localhost:4022' ;

--Watch Live Data on the ServiceBrokerRouting event session
--test to ensure that new messages are routed through the transmission queue


Adding an ID column to a join table (many-to-many) so that it can be targeted I'm building a web application for a company that sells cars. They buy leads (personal details of people interested in buying cars) and try to turn them into customers. The company has a number of sales persons, and they want each lead to be distributed to 5 of their salespersons randomly.

However, every sales person must have his own individual copy of the lead. This is because sales persons work on a commission basis, so they need to be able to:

edit/add a lead's details without the other sales persons seeing it
manage tasks for each lead without the other sales persons seeing those tasks

The only option I see for this is to add an id (PRIMARY KEY) column to the salesperson_has_lead table, and change its salesperson_id and lead_id columns to foreign key columns.

However, I always read that it's wrong to add an ID (PRIMARY KEY) columns to a many-to-many join table. Does anyone see any flaws in the solution pictured above, or know of a better option? Any help will be very much appreciated!

Join tables normally have primary key which is composite : (saleperson_id, lead_id) in your case, each of these columns is also a foreign key to appropriate table. So there is no need in surrogate ID PK in simple cases.
However, you have tasks and person information (details) which are specific to relationship itself and should have FK to saleperson_has_lead (I'd get rid of "has" in table name, but it's outside of question scope). Thus, logically your model looks good to me.
There are some options on how to implement this model :
1. Add a surrogate PK (as you did), but don't forget you still need unique constraint on (saleperson_id, lead_id)
2. Don't add it, but refer to already existing composite PK in saleperson_has_lead table -(saleperson_id, lead_id).

Personally I prefer the first approach because it seems more clear.

Side note.
From what I see many of your entities (lead, saleperson,  saleperson_has_lead) storing information about people. You may want to think of adding concept of role to your model because "lead" and "saleperson" are really just roles that a particular person can play in your system.


Find string of decimal digits in Pi I am trying to find the first occurrence of "12345" in the decimal expansion of $\pi$ by using MySQL. Possibly by using the Instr function?

But I am stuck because it only displays the first 7 fractional digits.

USE test
DROP TABLE IF EXISTS pi;
CREATE TABLE pi
(
    num BLOB
) ENGINE=MyISAM;
INSERT INTO pi VALUES
('3.14159265358...');
SELECT LOCATE('12345',num) FROM pi;

Here is the output
+---------------------+
| locate('12345',num) |
+---------------------+
|               49704 |
+---------------------+
1 row in set (0.03 sec)

I had to go to http://www.piday.org/million/ and scroll/Ctrl-F dozens of times until my web browser found it. Then, I pasted about 60000 characters into that line. Then, I ran the code using MySQL 5.6.21 on my laptop in Windows 8.1.
Have Fun !!!
UPDATE 2015-03-14 15:52 EST
BTW I just discovered this website : 100,000 Digits of Pi.
This displays pi to 100,000 digits instantly. 12345 is also there.
UPDATE 2015-03-14 15:57 EST
I tried the INSTR() function
+--------------------+
| instr(num,'12345') |
+--------------------+
|              49704 |
+--------------------+
1 row in set (0.00 sec)

It works as well !!!


Separate table by often changed / not often changed data? I have a application which querys different gameservers, and stores for example if the server is online, and how many players are connected. But of course there is also data which is changed rarely.

Currently I have all this in one table.

So my question is: does it make a performance difference if I split this table up, so that often changed data is in one table, and things like the description in another?

(I have to add that I always only select the data I need, so not like Select *). But when I select the data I often have to select the often changed data + the rarely changed data at once.

Yes, it will make a difference.  How big a difference will depend both on how many rows the table has and how many columns as well as how frequently the table is read from and updated.  Depending on the database, the block size and how many rows are updated at the same time could also be factors.

Keep in mind the overhead you are introducing by splitting the table for how often the data will be queried separately vs how often it will need to be joined and the performance requirements of various reading and writing activities.


sort results by highest performing country & region I want to check which country & regions are performing the best in sales.
I have the following order table:

| order_id | amount |
|----------|--------|
| 100      | 1000   |
| 101      | 250    |
| 102      | 120    |
| 103      | 500    |
| 104      | 25     |
| 105      | 2000   |
| 106      | 300    |

And order_address table:

| order_id | country | region |
|----------|---------|--------|
| 100      | US      | CA     |
| 101      | US      | LA     |
| 102      | UK      | GL     |
| 103      | UK      | GL     |
| 104      | UK      | GB     |
| 105      | CA      | OT     |
| 106      | AU      | QL     |

I want to get orders data by country & region and sort by highest performing country (region).

SELECT
    oa.country, oa.region, SUM(o.amount) AS 'total_sales'
FROM order o
INNER JOIN order_address oa ON o.order_id = oa.order_id
GROUP BY oa.country, oa.region
ORDER BY SUM(o.amount) DESC, oa.country, oa.region

But this is not working as expected as it's breaking the country sequence. Other country results should only follow if all regions of high performing countries are displayed.

In fact, here is the desired result:

| country | region | total_sales |
|---------|--------|-------------|
| CA      | OT     | 2000        |
| US      | CA     | 1000        |
| US      | LA     | 250         |
| UK      | GL     | 620         |
| UK      | GB     | 25          |
| AU      | QL     | 300         |

I'm assuming that you want the results sorted by (total by country), then by country (in case of a tie at this level), then by (total by region), then by region (if needed, as a tie-breaker at the region level).

Here's the adjusted SQL:

SELECT
    oa.country, oa.region, SUM(o.amount) AS 'total_sales'
FROM `order` o
INNER JOIN order_address oa ON o.order_id = oa.order_id
INNER JOIN (SELECT oa1.country, SUM(o1.amount) AS ctry_sales
              FROM order_address oa1
                     INNER JOIN `order` o1 ON o1.order_id = oa1.order_id
             GROUP BY oa1.country
           ) c ON oa.country = c.country
GROUP BY oa.country, c.ctry_sales, oa.region
ORDER BY c.ctry_sales DESC, oa.country, SUM(o.amount) DESC, oa.region

We determine the total per country in the sub-query labelled c. We then join that to the two tables you already had, and add the per-country total as the first sort condition in the ORDER BY. When then move the country before the total sales by region, to make sure that we keep entries for the same country together, even if two countries have the same per-country total.

Results (as you can see in this SQLFiddle) are what you requested:

CA  OT  2000
US  CA  1000
US  LA  250
UK  GL  620
UK  GB  25
AU  QL  300


Index not being used in PostgreSQL I have three tables, call them table1, table2 and table3. table1 has a foreign key column referencing table2,
and table2 has a column referencing table3. Both foreign key columns have a B-tree index over just that column;
in addition, table1 has an additional composite index over two columns: (table2_fk, another_column). The tables also have
numerous other indexes on their other columns (table1 has five other indexes, each over a single column).

table1 has 67 million entries, table2 has 7400.

The problematic query is

(a) SELECT *
    FROM table1
    WHERE table2_fk IN (SELECT id FROM table2 WHERE table3_fk = 3);

This for some reason runs for several minutes. The two component queries,

(b) SELECT id FROM table2 WHERE table3_fk = 3;
(c) SELECT * FROM table1 WHERE table2_fk IN (13, 14);

both complete instantly. The query (b) returns two IDs (13 and 14). The queries (a) and (c) return 16 rows.

Results of query explainer (with table/column/index names replaced with the above-mentioned aliases) look as follows (edit: added EXPLAIN (ANALYZE, BUFFERS))

db=# explain (ANALYZE, BUFFERS) select * FROM table1 WHERE table2_fk IN (13, 14);
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table1  (cost=1869.86..313991.96 rows=98673 width=193) (actual time=0.033..10.826 rows=18 loops=1)
   Recheck Cond: (table2_fk = ANY ('{13,14}'::integer[]))
   Heap Blocks: exact=6
   Buffers: shared hit=9 read=5
   I/O Timings: read=10.614
   ->  Bitmap Index Scan on table1_table2_fk_idx  (cost=0.00..1845.19 rows=98673 width=0) (actual time=0.016..0.016 rows=18 loops=1)
         Index Cond: (table2_fk = ANY ('{13,14}'::integer[]))
         Buffers: shared hit=8
 Planning time: 49.277 ms
 Execution time: 10.868 ms
(10 rows)

db=# explain (ANALYZE, BUFFERS) select * FROM table1 WHERE table2_fk IN (SELECT id FROM table2 WHERE table3_fk = 3);
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Semi Join  (cost=15.16..2417375.77 rows=2153302 width=193) (actual time=39974.333..127352.240 rows=18 loops=1)
   Hash Cond: (table1.table2_fk = table2.id)
   Buffers: shared hit=74 read=1536993
   I/O Timings: read=99114.790
   ->  Seq Scan on table1  (cost=0.00..2215354.00 rows=67829000 width=193) (actual time=0.005..119121.005 rows=67398453 loops=1)
         Buffers: shared hit=71 read=1536993
         I/O Timings: read=99114.790
   ->  Hash  (cost=15.11..15.11 rows=4 width=4) (actual time=0.009..0.009 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         Buffers: shared hit=3
         ->  Index Scan using table2_table3_fk_idx on table2  (cost=0.28..15.11 rows=4 width=4) (actual time=0.006..0.007 rows=2 loops=1)
               Index Cond: (table3_fk = 3)
               Buffers: shared hit=3
 Planning time: 0.441 ms
 Execution time: 127352.285 ms
(15 rows)

I.e query (c) uses the index on table2_fk, but query (a) for some reason does not, performing an
inefficient sequential scan instead. What could be causing this?

The same database structure exists on many computers (some of which have even more entries in table1),
but query (a) is slow only on one machine.

I have tried re-indexing and vacuum/analyze with no help. The behavior didn't change when selecting
a single column instead of "select *", or when re-writing (a) using JOIN.

Try adding an "OFFSET 0" in the subquery like this:

SELECT *
FROM table1
WHERE table2_fk IN (SELECT id FROM table2 WHERE table3_fk = 3 OFFSET 0);


Problems with Insert( simple query - can't think something ) I have 2 tables, Tblservicedesk and Tblservicedesk_temp

Columns:

CodServiceDesk
ID_Solicitacao
Solicitante
Departamento
Tecnico
Grupo
Status
Prioridade
Data_Abertura_Chamado
Data_Vencimento_Chamado
Data_Conclusao_Chamado
Nota
Comentario_Atendimento
data_criacao_registro
FlagEmailNota
FlagEmailSLA
FlagEmailFechado
data_resolved

I need to update the Tblservicedesk, with values of these 3 columns (FlagEmailNota, FlagEmailSLA, FlagEmailFechado ) from the Tblservicedesk_temp table.

All of this 3 columns is NULL in Tblservicedesk, and we have values in some columns in Tblservicedesk_temp.

I need to use ID_SOLICITAÇÃO as PK.

Assuming both Tblservicedesk & Tblservicedesk_temp having same columns. You can write update statement to update the three columns (FlagEmailNota, FlagEmailSLA, FlagEmailFechado ).

UPDATE tsd
SET
   tsd.FlagEmailNota = tsdt.FlagEmailNota ,
   tsd.FlagEmailSLA = tsdt.FlagEmailSLA,
   tsd.FlagEmailFechado = tsdt.FlagEmailFechado
FROM
   Tblservicedesk AS tsd
JOIN
  Tblservicedesk_temp AS tsdt ON tsd.ID_SOLICITAÇÃO = tsdt.ID_SOLICITAÇÃO

Since I don't have enough reputation to comment hence i'm posting this as an answer, if this is not what you are looking for please let me know.


How do I determine if a database restore file should be restored to the database based on backupset? I have a production database that is fully backed up nightly and incrementally backed up hourly.  On our backup machine, we have the differential backups and full backups restored ion a regular basis in a job. However, I need to only restore a backup file if the backup is valid (meaning this backup is newer than the current database backup version).  Is there any way to accomplish this?

This is our current solution until we can get mirroring working.

All help is greatly appreciated!

There is an msdb table that contains the restore history:

select  *
from    msdb.dbo.restorehistory rh

For a database, you can look up the date of the last restore.  If you can determine the date of the incoming restore (typically the restore date is part of the file name) you could compare the two.

While this safeguards against overwriting newer restores with older ones, it's not ideal.  There can be a difference between the backup date and the restore date, and newer restores that fall in between will be ignored.

You could improve that if you knew the filename that the last restore came from, but I can't find that information in the msdb tables.


Differential backup successfully completes without taking the backup of few databases I have differential backups for multiple databases configured to run through a stored procedure.  Sometimes one or more databases have not been backed up, even though the job was "successful."

I'm calling the backup through a stored procedure that runs scheduled in a job.

I have created the output file in the job, but it is not saying anything specific.

I've checked the SQL Server logs, event logs, changed the timings of the job, however I'm still not able to get the desired result nor any specific reason.

No recovery model change has happened.

The log backup job is running fine without any issues.

SQL Server version is SQL Server 2008 R2 + SP1 on Windows Server 2008 R2.

If you are saying that some databases are being skipped, then I would bet money that you are using sp_MSforeachdb. Stop using that procedure, it is an unreliable pile of unsupported and undocumented crap that is known to skip databases in certain scenarios (usually where > 100 databases are used on a busy system).

Solution: write your own cursor with sensible options:

CREATE PROCEDURE dbo.my_own_foreachdb
  @command NVARCHAR(MAX)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @db SYSNAME, @sql NVARCHAR(MAX);

  DECLARE c CURSOR
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR SELECT name FROM sys.databases;

  OPEN c;

  FETCH NEXT FROM c INTO @db;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = 'EXEC ' + QUOTENAME(@db) + '..sp_executesql @command';

    EXEC sp_executesql @sql, N'@command NVARCHAR(MAX)', @command;

    FETCH NEXT FROM c INTO @db;
  END

  CLOSE c;
  DEALLOCATE c;
END
GO

Sorry, untested, busy day over here - but should demonstrate the approach.

Or see this and this (I wrote a more reliable and flexible replacement).


Find the source of a recurrent mass SQL edit on a server I'll try to explain my problem as clear as possible.

The server of a company I support runs many websites. This server runs Windows Server 2012 with Microsoft SQL Server 2014.

Almost all of the websites are running a proprietary web application, made by the same company.

Only some of the websites are affected by a regular mass editing of (almost) every TEXT, NTEXT and NVARCHAR(MAX) fields in their respective databases.

HTML with malicious or spam links is added to every record of the table, only in the field with the type specified above.

The server has already been scanned with several tools and all the main password (administrator, sa of SQL Server) has been changed. I also tried to run the SQL Profiler to try to identify the mass update query, but without success.

As I can imagine for now, this may be an SQL Injection attack that uses a vulnerability in the software that runs those websites, but why only some of them? Other websites with the same version never got this problem.

As you know, is there something else I can try, or I'm missing? Reply without problems if you may need other data.

SQL Injection is hard to track from SQL Server side. Instead of looking at sql server, you should look at your web server IIS logs.

Use Log Parser to parse your IIS Logs to track down the source of sql injection. e.g.

  logparser.exe -i:iisw3c -o:Datagrid -rtp:100 “select date, time, c-ip, cs-uri-stem, cs-uri-query, time-taken, sc-status from C:\wwwlogs\W3SVCXXX\u_ex1207*.log where cs-uri-query like ‘%declare%’”

Read up on

SQL Injection – a Serious Security Issue by Erland Sommarskog
SQL Injection: How it Works and How to Thwart it
Protecting Yourself from SQL Injection in SQL Server - Part 1 and Part 2 from Aaron Bertrand.


Database view listing distinct days I have a list of sensors of many machines, that stores on a second base (i.e., 86400 records each day for each machine). One of the many columns is the datetime of the value (that can also be stored separately as date and time).
I'm using PostgreSQL 9.5.

Frequently, I need to find out what dates have sensor's records available for each machine.

I thought about creating a database view of distinct dates for each machine, but after searching for "materialized views" I think I'm ok with updating this list once in a while each day, as data is stored daily.

Would anyone give any advice about doing it?

The table is similar to

ID | Machine_code | Sensor_1 | Sensor_2 | Sensor3 | Datetime
1  | Drimmer      | 12       |  15.8    | 13.4    | 2016-03-03 10:00:03 GMT+1

If it helps anyhow, I'm using Django Framework in front of it.

create materialized view foo as
  select distinct machine_code, date(datetime) from thing;

create unique index on foo(machine_code,date);

Beware that the conversion of datetime to date will occur in the timezone of whoever last created or refreshed the MV.  You might want to use this instead:

create materialized view foo as
  select distinct machine_code, date(datetime at time zone 'GMT+1') from thing;

But that might not do what you want with daylight savings time.


Insert missing dates within table I have a table with a column for ID, Date and Frequency of occurrence for that particular date and ID.

ID    date          Frequency
1     2014-05-18      5
1     2014-05-19      4
1     2014-05-20      25
2     2014-05-20      7
3     2014-05-18      4
3     2014-05-20      1
4     2014-05-18      6

I'm looking to insert the dates for each ID that aren't currently there (assuming that the date ranges from 2014-05-18 to 2014-05-20) and add a value of 0 in Frequency for those.

For example, you can see that

ID 1 has an observation for all three dates;

ID 2 is missing 2014-05-18 and 2014-05-19;

ID 3 is missing 2014-05-19;

ID 4 is missing 2014-05-19 and 2014-05-20.

Therefore, the output should look like this

ID    date          Frequency
1     2014-05-18      5
1     2014-05-19      4
1     2014-05-20      25
2     2014-05-18      0
2     2014-05-19      0
2     2014-05-20      7
3     2014-05-18      4
3     2014-05-19      0
3     2014-05-20      1
4     2014-05-18      6
4     2014-05-19      0
4     2014-05-20      0

I've attempted the following from this question. However, that will only add dates with 0 Frequency that aren't present throughout the entire table instead of on an ID level.

DECLARE @s DATE = '20140518', @e DATE = '20140520';

SELECT c.date, Frequency = COALESCE(a.Frequency,0)
FROM dbo.Calendar AS c
LEFT OUTER JOIN dbo.table AS a
ON c.date = s.[date]
WHERE c.d >= @s
AND c.d < DATEADD(DAY, 1, @e);

Using your base sample query above, this code should do what you are looking for. The important changes:

CROSS JOIN to a list of IDs that should be in the report
JOIN to the data table using Date and ID
SELECT the id from the CROSS JOIN table

The code:

DECLARE @s DATE = '20140518', @e DATE = '20140520';

SELECT
    i.ID,
    c.[date],
    Frequency = COALESCE(a.Frequency,0)
FROM
    dbo.Calendar AS c
CROSS JOIN (SELECT DISTINCT x.ID FROM dbo.table x WHERE x.date >= @s AND x.date < DATEADD(day, 1, @e)) AS i
LEFT OUTER JOIN
    dbo.table AS a
ON
    c.[date] = a.[date]
    AND a.ID = i.ID
WHERE
    c.[date]>= @s
    AND c.[date] < DATEADD(DAY, 1, @e);


SQL Server Isnull Returning 1900-01-01 when field is null The following piece of code is returning 1900-01-01 when the DOB field is null. I wanted (and expected) it to return an empty string ('') but it's not. How should I proceed to get my desired results?

isnull(convert(date,DOB,1),'')

You can't get an empty string because you're returning the DATE value type from ISNULL.
Per the docs, ISNULL

Returns the same type as check_expression. If a literal NULL is
provided as check_expression, returns the datatype of the
replacement_value. If a literal NULL is provided as check_expression
and no replacement_value is provided, returns an int.

If you're checking whether or not the value is NULL, there is no need to convert it to a date, unless you wanted to return a date value (which you don't seem to).
Instead, use:
SELECT ISNULL( DOB , '')

Which will return
''

if the value is NULL.
A NULL date is NULL (no value). An empty string, on the other hand, evaluates to 0, which in SQL Server is implicitly an integer representing the number of days since 1900-01-01.


Cannot perform alter on 'FunctionName' because it is an incompatible object type I know the same question has been asked before, but the situation is slightly different and I'd like more explanation as to why it fails in my context please, when (AFAIK) the return type is the same.

Historically in my database I've used a CLR-based table valued function to split comma-separated values into a table containing an integer column.

Because SQL2016 introduces STRING_SPLIT, I need to swap out the CLR part of this function, and replace with the native TSQL function (as I have hundreds of stored procedures that rely on this function).

If I modify this function in SSMS, I see this:

ALTER FUNCTION [dbo].[e4fn_CreateIdTable](
     @Ids [nvarchar](max),
     @separator [nchar](1))
RETURNS TABLE (
    [IdVal] [int] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [ValueSplitter].[MyCompany.ValueSplitter].[SplitToIntegers]

I hoped I be able to alter it like so:

ALTER FUNCTION [dbo].[e4fn_CreateIdTable] (
    @Ids [nvarchar](max),
    @separator [nchar](1)
)
RETURNS TABLE
AS RETURN
(
    SELECT
        CAST(NULLIF(LTRIM(RTRIM(value)), '') AS int) AS IdVal
    FROM
        STRING_SPLIT(@Ids, @separator)
)
GO

This returned the error:

  Cannot perform alter on 'dbo.e4fn_CreateIdTable' because it is an
  incompatible object type.

I assumed that was because the table definition of the output parameter was not explicitly defined.  So, I retried like this:

ALTER FUNCTION [dbo].[e4fn_CreateIdTable] (
    @Ids [nvarchar](max),
    @separator [nchar](1)
)
RETURNS @tbl TABLE  (
    IdVal int
)
AS
BEGIN
    INSERT INTO @tbl (
        Idval
    )
    SELECT
        CAST(NULLIF(LTRIM(RTRIM(value)), '') AS int)
    FROM
        STRING_SPLIT(@Ids, @separator);

    RETURN;
END
GO

Again, the same error appears.

Why does the error appear, when the return types appear to be the same?
If I drop/recreate the function, do all the stored procedures need to be recompiled?

A CLR TVF is a fundamentally different database object type than a SQL TVF so one must use 'DROP/CREATE' instead of ALTER to change the definition.

The type in sys.objects for a CLR TVF is 'FT' whereas a SQL TVF (not inline) is 'TF'.


How to script data in deterministic order When I script data in my database in SSMS Tasks->Generate Scripts and then select data-only it turns out that the insert statements are generated in different order each time when even a smallest change occurs. This makes it impossible for the developers to run a diff program with the previous scripted data and see the data changes: all the tables every time are reshuffled around even if no rows were inserted/deleted so that a diff program can't really show the few data changes in coherent manner.

Is there a way to script data in a database so that they return roughly the same order if no major data additions/deletions that might affect the order happen?

I personally would use a tool (as indeed I already use) , such as are sold by Apex, Red Gate, Toad, and others.  These could be used to script only the rows that have changed.  That would make your looking for changes very simple indeed.

If you are looking for a free tool you might examine http://opendbiff.codeplex.com/.  I have not personally used it, but some people find it useful.  (There are doubtless other such tools in the public domain.)


PostgreSQL unexplained table bloat I have a table in a Postgres 8.2.15 database.  The table bloated to almost 25GB but after running vacuum full and cluster the table size was dramatically smaller, well under 1GB.  A few weeks later and it's back up to 3.5GB and climbing.

This is not a table that has frequent deletes, so I'm at a loss as to what is causing the bloat.

This only happens to a single table.  I have a separate, structurally identical database, serving the same software; the table in that database has not shown any bloat.

Any ideas?

There are a least three major reasons for why you should upgrade to a more recent version, preferably to the current version.

As mentioned by @a_horse_with_no_name the autovacuum mechanism has been improved in many places since version 8.2.
You don't see frequent DELETEs, so the table bloat most probably comes from UPDATEs as @Milen commented. A new feature has been introduced with version 8.3, that counters this problem at its root: Heap-Only Tuples, I quote the release notes:

  Heap-Only Tuples (HOT) accelerate space reuse for most UPDATEs and
  DELETEs (Pavan Deolasee, with ideas from many others)

  UPDATEs and DELETEs leave dead tuples behind, as do failed INSERTs.
  Previously only VACUUM could reclaim space taken by dead tuples. With
  HOT dead tuple space can be automatically reclaimed at the time of
  INSERT or UPDATE if no changes are made to indexed columns. This
  allows for more consistent performance. Also, HOT avoids adding
  duplicate index entries.

Emphasis mine.
PostgreSQL 8.2 has reached end of life in Dec. 2011. No more security updates. Upgrade to a more recent version as soon as possible.

To find out whether there are, in fact, a lot of UPDATEs launched, use the configuration parameter log_statement and check your database log. Maybe a misconfigured app or a trigger launches a lot more UPDATEs than you think?

If you SET the parameter in a session it only changes for this session:

SET log_statement = 'all';

So, rather change it in your postgresql.conf:

log_statement = all

and reload. For instance with pg_ctl reload. But don't forget to change it back (and reload) or your log files may grow huge. Then check the log files to see what's actually going on.


Specificness of Tablespace I'm fairly new to working with databases and am trying to fully understand the purpose of a tablespace.

Lets say I have a system where a user will input a schedule of tasks that need to be performed. This schedule is stored in my database and sent to a process which will distribute the tasks. As tasks are completed, the results are sent to a separate process which will store the raw result data, correlate the results, store a correlation summary, and then send it out.

Would I keep all of this data in a single tablespace or would it make more sense to separate it into two tablespaces - one for the schedules and one that will store the raw results and correlated result summary?

In general, how related should tables/indexes be to be stored in the same tablespace?

Despite being answered 10 years ago, I don't think I could improve on Justin Cave's very applicable answer from this similar question on SO: Should an Oracle database have more than one tablespace for data storage?

  My bias (and this is largely a matter of personal preference) is that
  if there is no compelling benefit to creating additional tablespaces,
  life is easier with a single tablespace.

  There is no performance benefit to putting objects in different tablespaces.  There is an old myth that separating tables and indexes
  would have some performance benefits.  There is a potential benefit to
  spreading I/O over all available spindles, but that's better done with
  multiple data files in a single tablespace then with multiple
  tablespaces since Oracle does a round-robin allocation of extents in
  different data files assuming that your SAN isn't already doing
  something to even out I/O.
  If you have large, static lookup/ history tables such that you could bring a new copy of the database to the client site by just bringing
  the smaller transactional tablespaces, that would be a reason to
  consider multiple tablespaces.  But there are very few applications
  that have this sort of setup.  If you'll have to bring all 200 GB, it
  doesn't matter how many tablespaces you have.
  Along the same lines, if you have large read-only objects, putting them in a read-only tablespace can vastly decrease the time and space
  required for backups.  Again, though, this isn't particularly common
  in practice outside of data warehouses.
  If your application could run without some subset of objects, there may be a benefit to creating separate tablespaces so that you could
  take one offline and do a tablespace-level restore.  Again though, few
  applications could run without a set of objects-- if you lose the
  index tablespace, for example, the application is likely just as dead
  as had you lost everything.
  If you have a large number of empty or mostly empty tables and a number of very large tables, separate tablespaces with different
  extent allocation policies may be preferrable from a space utilization
  standpoint.  This happens occasionally with packaged apps where any
  given installation is using a relatively small percentage of the
  available tables and you don't want each of the empty tables to have a
  relatively large extent assigned to it.  With automatic extent
  management in a locally managed tablespace, this tends not to be a
  major concern, it may be more concerning if you want to use uniform
  extents.
  If different objects have different priorities for disk performance, and you have different types of disk available, separate tablespaces
  can allow you to put different objects on different sets of disks.  In
  a data warehouse, for example, you may want to put older data on
  slower, cheaper disk and newer data on more costly disk.  This doesn't
  happen much with OLTP applications.

  Unless your application falls into one of these special cases, the
  only benefit to having separate tablespaces is to appeal to a DBA's
  sense of organization.  Personally, I'm more than happy to be able to
  avoid specifying a tablespace name every time I create an object or to
  spend cycles moving objects from the "wrong" tablespace when they
  inevitably get created in the default tablespace mistakenly.
  Personally, I'm not overly concerned if a few tens of MB of space are
  "wasted" when using locally managed tablespaces with automatic extent
  management over a hand-optimized set of tablespaces with different
  uniform extent sizes.  On the other hand, good DBA's tend to be very
  concerned about things being organized "just so" so I'm not militantly
  opposed if a DBA wants to have separate index and data tablespaces
  just because that appeals to someone's sense of aesthetics.


Update part of timestamp field I'm trying to fix a glitch in one of my tables.  Several hundred records were given the wrong timestamp.  The times are correct, but the date is wrong.
I could write a little java program to fix it, but I'd much rather learn how to do it via the command line.

What sort of update could I use to fix JUST the date part of timeout and timein?

The query results for the table showing the incorrect date on timeout and timein:

mysql> select id, fauteam, timeout, timein from fauactivity limit 5;
+----+---------+---------------------+---------------------+
| id | fauteam | timeout             | timein              |
+----+---------+---------------------+---------------------+
|  1 |       1 | 2015-10-14 08:18:50 | 2015-10-14 08:19:32 |
|  2 |       1 | 2015-10-14 08:20:39 | 2015-10-14 08:31:13 |
|  3 |       1 | 2015-10-14 08:31:58 | 2015-10-14 09:01:29 |
|  4 |       1 | 2015-10-14 09:02:52 | 2015-10-14 09:03:23 |
|  5 |       1 | 2015-10-14 09:04:27 | 2015-10-14 09:06:49 |
+----+---------+---------------------+---------------------+
5 rows in set (0.00 sec)

Simply add or subtract a number of days, e.g.:

update fauactivity
set timeout = timeout - interval 1 day,
    timein = timein - interval 1 day
where ...


Comparing strings in sql I have a list of string values for example ('a', 'b', 'c', 'd', 'e'). Now I have a database lets say (T1) having two columns for example (user and permission). Every user has some permissions. For example.
|user   | permission|
| user1 | a         |
| user2 | a,b,f     |
| user3 | b,c       |
| user4 | e,d       |
| user5 | f,g       |

I want to write a query which can retrieve the users who are using those permissions. What I tried until now is below.
select * from T1 where permissions IN ('a', 'b', 'c', 'd', 'e')
After running this I only get user1 nothing else. But the answer should be user2 and others also because they are using permission a also.
Note: This is just an example. The strings I have are more than 25 and the database has more than 100 values.

What you can do is something like this (see the fiddle here):
Table:
CREATE TABLE t
(
  user VARCHAR (10) NOT NULL,
  permission VARCHAR (10) NOT NULL
);

Data:
INSERT INTO t VALUES
('user1', 'a'),
('user2', 'a, b, f'),
('user3', 'b, c'),
('user4', 'e, f'),
('user5', 'f, g');

Query:
SELECT * FROM t
WHERE permission LIKE '%a%'
OR    permission LIKE '%b%'
OR    permission LIKE '%c%'
OR    permission LIKE '%d%'
OR    permission LIKE '%e%';

Result:
user    permission
user1   a
user2   a, b, f
user3   b, c
user4   e, f

user5  is excluded because his permissions are 'f,g' and these don't appear in the sequence a-e.
p.s. welcome to the forum!


How to represent a many to many relationship with Crow Feet diagram I'm designing a database and I want to represent the relationship between users and groups:

A user can be on zero or more groups.
A group can have one or more users.

Using Microsoft Visio 2013 I've drawn this diagram:

Is correct?

Yes that is correct.

Of course you no longer actually have a many-to-many relationship in your model.  You are no longer "representing" the relationship, as you say in your question.  You have, in fact, resolved the relationship into an association entity type with two one-to-many relationships.


Return Most Recent Entry But Sum One Field What I am after here is to return the Max(date) but Sum(amt) so that way I only see the most recent information pertaining to an account, but I also see the Total Amt!  This is my DDL

Declare @Helper1 Table
(name varchar(100),inputid int, custid varchar(100), rdsdate date, amt decimal(10,2))

Insert Into @Helper1 (name, inputid, custid, rdsdate, amt) Values
('Bob', 123, 'rst11', '2017-01-01', '13.00'), ('Jack', 131, 'rst11', '2017-01-05', '-5.00')
,('Larry', 444, 'ems33', '2017-02-01', '10.00')

I tried this query - however it returns all info for custid rst11 not the Max() like I am after

Select
name
,inputid = MAX(inputid)
,custid
,rdsdate = MAX(rdsdate)
,amt = SUM(amt)
FROM @Helper1
GROUP BY name, custid
ORDER BY name asc

This is my desired returned result set (since rst11 has two entries return the max and SUM())

Jack 131 rst11 2017-01-05 8.00
Larry 444 ems33 2017-02-01 10.00

What is the proper way to achieve this in a query?

You can use a combination of ROW_NUMBER and SUM() OVER():

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER( PARTITION BY custid
                                    ORDER BY rdsdate),
            total_amt = SUM(amt) OVER(PARTITION BY custid)
    FROM @Helper1
)
SELECT  name,
        inputid,
        custid,
        rdsdate,
        total_amt
FROM CTE
WHERE RN = 1;


MySQL 5.5 Replication to Galera/Percona XtraDB Cluster We are currently investigating moving our current MySQL 5.5 Master > 5 x Slaves (with Statement Based Replication, apx 250GB data and growing) to a HA clustering option. Mainly to provide greater resilience, write scaling, and easier to carry out admin stuff like Optimising tables without taking the master offline.

We have ruled out (for the moment) MySQL ndb Cluster as it would require too many changes to our current database structure (large number of BLOB fields amongst other problems).

So we are looking at either Galera Cluster or Percona Cluster as the alternatives, as the they seem to do what we want, without 'too' steep a learning curve.

We would want to start off by setting the Cluster up as a Slave to the main Master (for testing etc), before moving over to the Cluster as our Primary system in the future. Is this possible? Reading through the documentation doesn't seem to really talk about this side of things (unless I've missed it).

Yes, it is very possible.

As the Documentation says, you should have a minimum of three nodes in a Percona XtraDB Cluster. You only need one node out of the three nodes.

Make sure all nodes in the PXC have unique server_ids.
Make sure the Master you are replicating from has a unique server_id from the PXC nodes
Make sure the master and every PXC node is configured with the following

binlog_format=ROW
log-slave-updates

Make sure the master and every PXC node is using binlog_format=ROW

Pick one node and set it up as you would a DB Server designated as a Slave via CHANGE MASTER TO.

If you are going to set up any replication filters, restrict it to just the PXC node that is your Slave. Personally, I would stay away from using replication filters altogether in this setup since one of Percona's Galera Instructors does not feel good about it.

Please be aware that data takes time to be on all nodes at the same instant of time (See Investigating MySQL Replication Latency in Percona XtraDB Cluster)

By the way, you can use any PXC as a Master to StandAlone Slaves as well

Jun 07, 2013 : Percona XtraDB Cluster and MySQL Replication
Jan 29, 2014 : Native replication from Percona XtraDB Cluster to a standard stand-alone slave
Nov 24, 2014 : What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster?

HEADS UP #1

Don't be surprised by replication lag in the PXC node acting as the MySQL Slave. After all, three nodes must write data and become eventually consistent before all committing changes together. See Follow these basics when migrating to Percona XtraDB Cluster for MySQL for more info.

HEADS UP #2

Since you are using MySQL 5.5, do not use PXC 5.6. Use PXC 5.5. Why ? Row-based binlogging in MySQL 5.5 can sometimes not be properly interpreted by a MySQL 5.6 mysqld process. This can break replication from MySQL 5.5 to MySQL 5.6. I wrote a post about this two months ago. The work around was to switch to binlog_format=STATEMENT, but you cannot do that for Percona XtraDB Cluster as it requires binlog_format=ROW.