Modification_Counter influence the Cardinality Estimator? I ran into something that is really mysterious to me and I would need your help to find an explanation (didn't find anything on Google).

Here is the situation. I have 3 tables and in one of them, there is like 40 millions rows with a column having null for all except 3 rows (let's call that column "param". There is a nonclustered index on that column and the statistics are good (something like:

1 NULL 0 3.596004E+07 0 1
2 bcd 0 1 0 1
3 cde 0 1 0 1
4 def 0 1 0 1

I have a query that is executed with "where Param = x".

When I executed the query with a value like "Param = 'cee'", my execution plan does a nonclustered index seek and then a merge join on the other table (with a scan of the cluster index) and it works in a millisecond.

When I run the same query with "where Param = 'abc'", the execution plan changes. It starts with a full scan of the second table (clustered index) and then the merge join on the million rows table (still seek on nonclustered index).

So far so good. Trying different thing, I rebuilt the NC index and right after that, I wasn't able to reproduce the issue. I wasn't gonna stop there! So I start changing values and putting it back to the original value so the Statistics remain good but the modification_counter increased. When the modification_counter reached 10,000, the issue came back!

As the table is huge (40 million rows), 10,000 modifications is not enough to trigger the update of the stats and as I got all of those changes during a day, my maintenance plan can't do anything to prevent the issue.

So what I'm trying to understand is:

How does the modification_counter influence the CE to take a different execution plan.

Please keep in mind that the statistics are always good when I run the query (only 3 rows in the 40 millions with a non null value and always the same 3 values) so it's not the statistics that cause that behavior.

Using SQL Server 2014 but same behavior Under 2016.

How does the modification_counter influence the CE to take a different execution plan?

One way this can happen is that the new CE in SQL Server 2014 may interpret statistics differently depending on how many modifications have occurred on that column since the last statistics update. From Seek and You Shall Scan Part II: Ascending Keys by Itzik Ben-Gan:

  The new cardinality estimator identifies that the query filter exceeds the maximum value in the histogram. It has access to the column modification counter (call it in short modctr), so it knows how many changes took place since the last refresh. When the column is unique and of an integer type or numeric with a scale of zero, SQL Server assumes it’s ascending. So it interpolates the estimate based on the distribution of the values within the existing histogram and the number of changes that took place since the last refresh.

I cannot say if that behavior is leading to your issue. You could try running both queries with trace flag 9481 to see if your issue is caused by something new in the 2014 CE:

OPTION (QUERYTRACEON 9481)

You can also use query hints to force what you think is the better plan. Comparing the hinted plan with the unhinted plan may give you a clue as to why SQL Server is choosing the different plan.


Name for intermediate aggregated data We have raw data and aggregated data as well as sometimes multiple levels of aggregation between.  Is there a name for the data in that intermediate "state" of aggregation?

Just like how we have Real time, near real time and historical...

Generally, I've seen these intermediate levels referred to by the "grain" of the aggregation at which the data is stored.

For example, if you have an intermediate aggregation in which the data is stored at the level of a person for a day, we would call that the person-day grain. This can be applied generically throughout most schemas.


Stored procedure to check if username exists I'm trying to write a stored procedure

So far I have this, but it always returns 1

CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS`(IN `USERNAME` VARCHAR(64) CHARSET utf8mb4)
SELECT CASE WHEN MAX(user_id) IS NULL THEN '0' ELSE '1' END User_exists
  FROM `dbname`.`tablename`
 WHERE `username` = USERNAME

The goal is to perform this via AJAX so the user can see whether registration is likely to succeed before submitting their registration form

Perhaps doing a COUNT is better since it must be a number always

DELIMITER $$
DROP PROCEDURE IF EXISTS `USER_EXISTS` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `USER_EXISTS`
(IN `GIVEN_USERNAME` VARCHAR(64) CHARSET utf8mb4)
BEGIN
    SET @User_exists = 0;
    SELECT COUNT(1) INTO @found
    FROM `dbname`.`tablename`
    WHERE `username` = GIVEN_USERNAME;
    IF @found > 0 THEN
        SET @User_exists = 1;
    END IF;
    SELECT @User_exists;
END $$
DELIMITER ;

You could also write it as a function

DELIMITER $$
DROP FUNCTION IF EXISTS `USER_EXISTS` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `USER_EXISTS`
(`GIVEN_USERNAME` VARCHAR(64) CHARSET utf8mb4) RETURNS INT
BEGIN
    SET @User_exists = 0;
    SELECT COUNT(1) INTO @found
    FROM `dbname`.`tablename`
    WHERE `username` = GIVEN_USERNAME;
    IF @found > 0 THEN
        SET @User_exists = 1;
    END IF;
    RETURN @User_exists;
END $$
DELIMITER ;

Then, you could call the function to get a number

SET @x = USER_EXISTS('Synnyster');
SELECT USER_EXISTS('Synnyster');

Give it a Try !!!


Mysql grant limited by IPv6 subnet I want to permit access to a mysql server over IPv6 and limit access to the particular account to our ULA subnet we are using (fdd7:03d7:6247::/48).

The manual seems to suggest that wildcards are valid in the host when using IPv4, but how do you do this with IPv6?

  You can specify wildcards in the host name. For example, 'user_name'@'%.example.com' applies to user_name for any host in the example.com domain, and 'user_name'@'192.168.1.%'.

I have tried all these values which seemed like they should be valid.

GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:3d7:6247::%'  IDENTIFIED BY  '...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:3d7:6247:%'   IDENTIFIED BY  '...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:03d7:6247:%'  IDENTIFIED BY  '...' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'fdd7:03d7:6247::%' IDENTIFIED BY  '...' WITH GRANT OPTION;

Using the % only in the host field works, so I know I have the password, and server setup properly.

GRANT ALL PRIVILEGES ON *.* TO 'zoredache'@'%' IDENTIFIED BY  '...' WITH GRANT OPTION;

The server has DNS resolution disabled with the skip-name-resolve, and I don't really want to enable it, and mess around with setting up reverse DNS zones for the ULA address space.

To repeat, how do I grant access for a user only if they connect from the  fdd7:03d7:6247::/56 network?

::, as I understand IPv6 indicates as many chunks of all zero as are needed to fill out the full length.  The question comes as to whether MySQL treats % as a single chunk and treat fdd7:03d7:6247::% as fdd7:03d7:6247:0000:0000:0000:0000:% or interpret it some other way.  (Sorry, I don't know the answer.)

A second problem is that /56 splits the middle of some chunk.  I don't know if that can be represented with %.

Anyway, here's my best guess:

'fdd7:03d7:6247:00%'

This confirms PacoHope's contention that leading zeros in each segment are optional:

SELECT HEX(INET6_ATON('1111:698:2222::'));
         -->           11110698222200000000000000000000


MySQL : Update some fields depending on each other I have a table with 16 fields as User1, User2, ... User16, ( + some other fields ) , I have a stored procedure named "Register" that get a "User Name" and put it in the first Null field of those 16 fields, for example if User1 and User2 have value, new "User Name" will put in User3, there is no problem

I have another stored procedure named "UnRegister" that get a "User Name" and set to Null the value of field that have given "User Name"

There is no problem in Updating table and putting Null for given "User Name" but the problem is after Updating User(X) to Null, User(X+1) value should put in User(X) field !

An example :

User1 = "U1"
User2 = "U2"
User3 = "U3"
User4 = "U4"
User5 = NULL
...
User16 = NULL

Now , I want to Unregister "U2" and the result values should be like this :

User1 = "U1"
User2 = "U3"
User3 = "U4"
User4 = NULL
User5 = NULL
...
User16 = NULL

I think, I should use 16 CASE statement in an UPDATE statement for all 16 fields and 16 WHEN for each CASE !

Is there another way to do that ?

Is there design problems ?!

In fact, this a tournament with 16 users

thanks ...

It is bad practice to have "arrays" stored as columns.

Instead, have another table, delete the row with 'U2', and number the entries while SELECTing WHERE ... IS NOT NULL.


How PostgresSQL stores duplicated VARCHAR values in a column I have a VARCHAR(255) column in a table with about 50 million rows. In that column there is only 10 unique values, which act as labels. I'm wondering whether Postgres does something smart and stores only those 10 values and just references them in each row or is the data stored as 50 million values?

It stores what you inserted. Normalize it.

PostgreSQL may store larger text and varchar values out-of-line in a side table, possibly compressed. But not small labels.

BTW, varchar(255) has no effect in postgres, it's the same as text with a length check constraint.


select values from a table where column values should be different Let us say there are 4 columns in a table .

cpt,cpt4mod,itemcharge,eff_date.

condition1: if the values of cpt,cpt4 and itemcharge are same, but eff_date is different. Then I need to fetch the latest record based on eff_date.

condtition2: if the values of cpt and cpt4 are same, but eff_date and itemcharge are different. Then I need to fetch the latest record based on eff_date.

condtition3:if all columns have the same values, then I need to fetch the latest record based on eff_date

condtition4: if the values of cpt,cpt4and,eff_date are same  butitemcharge  is different. Then I need to fetch all the records except the latest one

Well, condition2 seems to cover condition1 and condition3 too. What you probably need would be something like:

;WITH CTE AS
(
    SELECT  *,
            N = RANK() OVER(PARTITION BY cpt, cpt4 ORDER BY eff_date DESC)
    FROM dbo.YourTable
)
SELECT *
FROM CTE
WHERE N = 1;

This should cover everyone of your conditions.


I want to remove Json object from Json Array in Mysql 8. Column data type is Json I'm using Mysql 8.
I want to remove the specific object from JSON Array which has value like field_1568988989723 in Mysql JSON column

Here is my JSON

[{"section": {"name": "Basic Info", "order": 0, "fields": [{"name": "field_1568988989723", "order": 0}, {"name": "field_1568989125942", "order": 1}]}}]

I've used this way

UPDATE apps_object
    SET detail_view = JSON_REMOVE(
        detail_view, REPLACE(
            JSON_SEARCH( detail_view, 'all', 'field_1568992047479', null, '$**.fields' )
            , '"'
            , ''
        )
      ) where id = 34;

But it just removes

  "value":"field_1568988989723"

but not he whole object.
Is there any way to remove object in MySql not in Programing code?

I'm thankful to @wchiquito for solving my problem

[{"section": {"name": "Basic Info", "order": 0, "fields": [{"name": "field_1568988989723", "order": 0}, {"name": "field_1568989125942", "order": 1}]}}]

This is the query which solved my problem

UPDATE apps_object SET detail_view = IFNULL( JSON_REMOVE( detail_view, JSON_UNQUOTE( REPLACE( JSON_SEARCH(detail_view, 'all', 'field_1568988989723', NULL, '$**.fields'), '.name', '' ) ) ), detail_view ) where id = 34;

With the help of dbfiddle


Are there differences between which AlwaysOn AG server (Primary or Secondary Replica) I store my backups and retention maintenance plans on? I have an AlwaysOn AG system in place between two servers. Server A hosts the primary replica, Server B hosts the secondary replica.

Are there any differences if my Full & Transaction Log Backups are stored on Server A vs Server B?

Are there any differences if my backup retention maintenance plan and job is ran on Server A vs Server B (regardless of where the backups live)?

My current setup is Server A has a SQL Agent job that runs a maintenance plan on Server B to back up my databases to Server B. In that same job on Server A there's a subsequent step that runs a maintenance plan on Server A that cleans up old backups from Server B.

You should not have your backup files on the SQL server as you would lose those backup if ever there is an issue with the SQL server.

Best practice would be to store your backup outside of the SQL Server (on a shared folder for example) that can be accessed by both node of the AG.

As the primary node can switch from one or the other node, you should code your backup job to only backup on the "prefered" node (which will be set in your AG).

I would also recommand you to use something else then SQL Maintenance jobs (have a look at : https://ola.hallengren.com/)

Usually, the backup are running on the primary node as you cannot do diff backup on a secondary node and as the secondary node data files could be behind the prod one (even in synchronous mode).

For the cleanup job, you could simply use a powershell script that will delete files from that shared folder.


Why does an Oracle database require both the SYSTEM and the SYSAUX tablespaces? Quoting from Oracle documentation about logical storage structures:

  A database must have the SYSTEM and
  SYSAUX tablespaces:

Why is it compulsory to have these tablespaces?

Tradition and the ability for elegant failure degradation.
As the database has evolved over 11 versions, important views and tables that maintain and support the database have been programmed into the two tablespaces. Furthermore, they represent a fantastic logical separation between the "NEVER EVER TOUCH" and the "third parties can put stuff here."
From the docs:

The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.
If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

Thus, while it's critical to have the SYSTEM tablespace never ever fail, one doesn't need to be nearly as paranoid for SYSAUX, this allows the designers to keep the size of SYSTEM down, while allowing third-party auxiliary features to be part of the "core" database.


Elusive problem on AWS RDS MySQL I have a provisioned, single-master RDS DB (5.6.mysql_aurora.1.23.0) with a writer and a reader. The clients are a bunch of jetty applications with a pool of up to 250 connections each.
I have for some time been struggling with intermittent problems: Connection atttempts start to time out, mostly. It seems to correlate with high loads, and I have started copying the entire information_schema.processlist to a log table at regular intervals (with a timestamp, obviously), and it seems the problem becomes most pronounced when the number of connections reaches 2000. It never gets near max_connections or max_user_connections:
mysql> show variables like "%max%conn%";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| aurora_max_connections_limit | 16000 |
| max_connect_errors           | 100   |
| max_connections              | 3000  |
| max_user_connections         | 0     |
+------------------------------+-------+
4 rows in set (0.03 sec)

So, which parameters should I concentrate on in order to get past this problem? I haven't found anything likely looking, that explains why 2000 connections seem to cause this; of course, it may simply be a coincidence that such a round number pops up.

This is most likely going to be the result of memory exhaustion. Every connection to the database requires a bit of memory and, if each connection needs 1MB (a low number), you could be looking at about 2GB of memory used just for the connections.
This SQL query will let you know the maximum amount of memory any single connection can use:
SELECT ( @@read_buffer_size
       + @@read_rnd_buffer_size
       + @@sort_buffer_size
       + @@join_buffer_size
       + @@binlog_cache_size
       + @@thread_stack
       + @@tmp_table_size
       + 2*@@net_buffer_length
       ) / (1024 * 1024) AS MEMORY_PER_CON_MB;

Check that this number isn’t too high (which is a subjective number based in experience with your specific databases). If the database is having its memory exhausted regularly, then you may need either a memory-optimized instance or stricter limits on how many resources each connection may consume.


How to copy database from one server to another with script So I have a script that almost does what I want but for one server.I need it to work "Cross-Server" ex.I have a database in one server and I need to pass it to other server and deleting if it exists on second server.

What I have now does almost the same but in one instance ->

USE master;

DECLARE
    @SourceDatabaseName AS SYSNAME = 'ABCBRAGA',
    @TargetDatabaseName AS SYSNAME = 'TEST'

-- ============================================
-- Define path where backup will be saved
-- ============================================
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
    RAISERROR ('Variable @SourceDatabaseName is not set correctly !', 20, 1) WITH LOG

DECLARE @SourceBackupFilePath varchar(2000)
SELECT @SourceBackupFilePath = BMF.physical_device_name
FROM
    msdb.dbo.backupset B
    JOIN msdb.dbo.backupmediafamily BMF ON B.media_set_id = BMF.media_set_id
WHERE B.database_name = @SourceDatabaseName
ORDER BY B.backup_finish_date DESC

SET @SourceBackupFilePath = REPLACE(@SourceBackupFilePath, '.bak', '_clone.bak')

-- ============================================
-- Backup source database
-- ============================================
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = 'BACKUP DATABASE @SourceDatabaseName TO DISK = ''@SourceBackupFilePath'''
SET @Sql = REPLACE(@Sql, '@SourceDatabaseName', @SourceDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SELECT 'Performing backup...', @Sql as ExecutedSql
EXEC (@Sql)

-- ============================================
-- Automatically compose database files (.mdf and .ldf) paths
-- ============================================
DECLARE
          @LogicalDataFileName as NVARCHAR(MAX)
        , @LogicalLogFileName as NVARCHAR(MAX)
        , @TargetDataFilePath as NVARCHAR(MAX)
        , @TargetLogFilePath as NVARCHAR(MAX)

SELECT
    @LogicalDataFileName = name,
    @TargetDataFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.mdf'
FROM sys.master_files
WHERE
    database_id = DB_ID(@SourceDatabaseName)
    AND type = 0            -- datafile file

SELECT
    @LogicalLogFileName = name,
    @TargetLogFilePath = SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))) + '\' + @TargetDatabaseName + '.ldf'
FROM sys.master_files
WHERE
    database_id = DB_ID(@SourceDatabaseName)
    AND type = 1            -- log file

-- ============================================
-- Restore target database
-- ============================================
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
BEGIN
SET @Sql = 'DROP DATABASE ' + @TargetDatabaseName
EXEC (@Sql)
END

SET @Sql = 'RESTORE DATABASE @TargetDatabaseName
FROM DISK = ''@SourceBackupFilePath''
WITH MOVE ''@LogicalDataFileName'' TO ''@TargetDataFilePath'',
MOVE ''@LogicalLogFileName'' TO ''@TargetLogFilePath'''
SET @Sql = REPLACE(@Sql, '@TargetDatabaseName', @TargetDatabaseName)
SET @Sql = REPLACE(@Sql, '@SourceBackupFilePath', @SourceBackupFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalDataFileName', @LogicalDataFileName)
SET @Sql = REPLACE(@Sql, '@TargetDataFilePath', @TargetDataFilePath)
SET @Sql = REPLACE(@Sql, '@LogicalLogFileName', @LogicalLogFileName)
SET @Sql = REPLACE(@Sql, '@TargetLogFilePath', @TargetLogFilePath)
SELECT 'Restoring...', @Sql as ExecutedSql
EXEC (@Sql)

What I need it to is to pass a database from one server to other.

Use dbatools powershell module. There are numerous commands to help all sorts of administrations tasks.

Here is a migrate database example.

copy-dbadatabase -Source oldserver -Destination newserver -BackupRestore -SharedPath \\server\backup\ -Database databasename -SetSourceOffline


database design for simple comment I have a following given scenario.
A user can have zero or more item.
A user can comment on the item. (Just simple comment listing with name of people who comment, no need hierarchical structure for replying comment)

And i come out database structure like this. Not sure if this is a good database design

You design is basic and cover

1 user -> many items
1 item -> many comments
1 user -> many comments

The question is would you like the user to be able to comment on the same item multiple of times?
if yes, you do have a good design else we would need to work on it.


Can I use postgres_fdw without foreign tables defined? I have a production database "PRODdb1", with a read-only user account. I have a need to query(select statement) this database and insert the data into a secondary database named "RPTdb1". I originally planned to just create a temp table in PRODdb1 from my select, but permissions are the issue.

I've read abut dblink & postgres_fdw, but are either of these a solution for my issue? I wouldn't be creating foreign tables because my SELECT is joining many tables from PRODdb1, so I'm unfamiliar if postgres_fdw would still be an option for my use case.

Another option would be any means of getting the results of the SELECT to a .CSV file or something. My main blocker here is that I only have a read-only user to work with, but no way around that issue.

postgres_fdw is definitely the better way. As you have read-only access to the production database, you can setup a FDW on your test (secondary) database provided you have superuser access to that (secondary) server. Or at least someone who can install the extension and setup the foreign server. Once those two things are there, you can proceed with a regular user.

One thing to keep in mind: if you do use the FDW, any select on the foreign table (in your test server) will generate some load on the production server. Whether or not that is acceptable only you can know.

  my SELECT is joining many tables from PRODdb1

If you join multiple foreign tables in a single statement those joins will be pushed down to the foreign server and evaluated there (at least with the Postgres 10 and I think 9.6 as well) only the result of the join will be sent back to the calling server.

Another option is to use pg_dump to copy the data from your production server to your test server.

pg_dump --host=prodserver --dbname=prod_db --table=public.some_table --file=some_table.sql --no-owner

That will export the data for some_table into a SQL script, including the necessary CREATE TABLE statement.

You can then run that SQL script through psql to import the data into your test server:

psql --host=testserver --dbname=test_db --file=test_table.sql

If you already have the table definition in your test server, then you might want to use --data-only for pg_dump to skip that part.

That will be faster than trying to generate some CSV files.


MySql Insert signal if where clause fails As part of a chained sequence of transactional queries, I'm inserting values that exist in a temporary table into a new table.

Every query is validated for errors in PHP using:

if (!mysqli_query($dbc, ....) {$errors[] = mysqli_error($dbc);}

If an error occurs it gets pushed into the errors array and the entire sequence is not commited.

With this approach I'm having trouble validatiesg queries that DO NOT CAUSE AN ERROR but DO NOT MEET WHERE CLAUSE CONDITIONS

Is there a way to get MySQL to throw an error signal when the WHERE CLAUSE conditions are not met?

You can try using mysqli_affected_rows to find how many rows are inserted from the last query and generate your own error in case no rows are inserted.


Changing the partitioning column in SQL Server I created a partitioned table with 100+ million rows and accidentally specified the wrong datetime column as the partitioning key when I created the table.

Is there an easy way to change the partitioning column from one datetime column to another one.   In my case, we have a column InsertedDate and a CompleteDate and I accidentally used the first one instead of the second one.

Changing the partitioning column on a table with hundreds of millions of rows is likely to be a bit painful, since all rows in the table, and perhaps any associated indexes, will need to be physically moved by the operation.  You need to make sure you have enough storage space available to rebuild each partition - perhaps you'll want to move the table to a set of new filegroups, then drop the old unused filegroups afterwards.

If the table has a primary key or any unique indexes defined, they must be dropped and recreated so they include the new partitioning column in the index key.

If the table has a primary key, and that primary key is referenced in other tables, you'll need to break the referential integrity in order to change the partitioning key.  Since you are currently using the InsertedDate column as the partitioning key, and want to move that to use the CompletedDate column, which are presumably both date data-types, I doubt this affects your situation - it's just worth being aware of.

For a table with a primary key, the actual task of moving a table from one partitioning key to another consists of dropping the primary key constraint, then rebuilding the table with the new partitioning key as part of the primary key.

I've put together a little test-bed with 10,000,000 rows spread over 24 partitions so we can see this in action.

We'll do the work in tempdb, so make sure you have enough room for some growth.  First, we'll create the partition function, partition scheme, and finally the table itself:

USE tempdb;
GO
IF OBJECT_ID(N'dbo.Tab', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Tab;
END
IF EXISTS (SELECT 1 FROM sys.partition_schemes ps WHERE ps.name = N'PartScheme')
BEGIN
    DROP PARTITION SCHEME PartScheme;
END
IF EXISTS (SELECT 1 FROM sys.partition_functions pf WHERE pf.name = N'PartFun')
BEGIN
    DROP PARTITION FUNCTION PartFun;
END

CREATE PARTITION FUNCTION PartFun (datetime)
AS RANGE LEFT
FOR VALUES (
      N'2012-01-01T00:00:00'
    , N'2012-04-01T00:00:00'
    , N'2012-07-01T00:00:00'
    , N'2012-10-01T00:00:00'
    , N'2013-01-01T00:00:00'
    , N'2013-04-01T00:00:00'
    , N'2013-07-01T00:00:00'
    , N'2013-10-01T00:00:00'
    , N'2014-01-01T00:00:00'
    , N'2014-04-01T00:00:00'
    , N'2014-07-01T00:00:00'
    , N'2014-10-01T00:00:00'
    , N'2015-01-01T00:00:00'
    , N'2015-04-01T00:00:00'
    , N'2015-07-01T00:00:00'
    , N'2015-10-01T00:00:00'
    , N'2016-01-01T00:00:00'
    , N'2016-04-01T00:00:00'
    , N'2016-07-01T00:00:00'
    , N'2016-10-01T00:00:00'
    , N'2017-01-01T00:00:00'
    , N'2017-04-01T00:00:00'
    , N'2017-07-01T00:00:00'
    , N'2017-10-01T00:00:00'
);

CREATE PARTITION SCHEME PartScheme
AS PARTITION PartFun
ALL TO ([PRIMARY]);

IF OBJECT_ID(N'dbo.Tab', N'U') IS NOT NULL
DROP TABLE dbo.Tab;
CREATE TABLE dbo.Tab
(
    TabID int NOT NULL
    , CreateDate datetime NOT NULL
    , UpdateDate datetime NOT NULL
    , Data1 varchar(100) NOT NULL
    , Data2 varchar(100) NOT NULL
    , Data3 varchar(100) NOT NULL
    , CONSTRAINT PK_Tab
        PRIMARY KEY CLUSTERED
        (CreateDate, TabID)
) ON [PartScheme](CreateDate);

This fills the table with 10,000,000 rows somewhat evenly spread over each partition:

;WITH Ten AS
(
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, TenMillion AS
(
    SELECT Num = (t7.Num * POWER(10, 6))
        + (t6.Num * POWER(10, 5))
        + (t5.Num * POWER(10, 4))
        + (t4.Num * POWER(10, 3))
        + (t3.Num * POWER(10, 2))
        + (t2.Num * POWER(10, 1))
        + (t1.Num)
    FROM Ten t1
        CROSS JOIN Ten t2
        CROSS JOIN Ten t3
        CROSS JOIN Ten t4
        CROSS JOIN Ten t5
        CROSS JOIN Ten t6
        CROSS JOIN Ten t7
)
INSERT INTO dbo.Tab (TabID, CreateDate, UpdateDate, Data1, Data2, Data3)
SELECT m.Num, DATEADD(DAY, m.Num % 1825, N'2012-01-01T00:00:00')
    , DATEADD(DAY, m.Num % 1825, N'2012-02-02T00:00:00')
    , CONVERT(varchar(100), CRYPT_GEN_RANDOM(100))
    , CONVERT(varchar(100), REPLICATE('A', 100))
    , CONVERT(varchar(100), REPLICATE('B', 100))
FROM TenMillion m;

The distribution of rows in the table:

SELECT ObjectName = s.name + '.' + o.name
    , p.partition_number
    , p.rows
FROM sys.partitions p
    INNER JOIN sys.objects o ON o.object_id = p.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name = 'Tab'
    AND p.index_id = 1
ORDER BY p.partition_number;

╔════════════╦══════════════════╦════════╗
║ ObjectName ║ partition_number ║ rows   ║
╠════════════╬══════════════════╬════════╣
║ dbo.Tab    ║ 1                ║ 5480   ║
║ dbo.Tab    ║ 2                ║ 498680 ║
║ dbo.Tab    ║ 3                ║ 498680 ║
║ dbo.Tab    ║ 4                ║ 504160 ║
║ dbo.Tab    ║ 5                ║ 504160 ║
║ dbo.Tab    ║ 6                ║ 493200 ║
║ dbo.Tab    ║ 7                ║ 498680 ║
║ dbo.Tab    ║ 8                ║ 504160 ║
║ dbo.Tab    ║ 9                ║ 504160 ║
║ dbo.Tab    ║ 10               ║ 493200 ║
║ dbo.Tab    ║ 11               ║ 498592 ║
║ dbo.Tab    ║ 12               ║ 504068 ║
║ dbo.Tab    ║ 13               ║ 504068 ║
║ dbo.Tab    ║ 14               ║ 493110 ║
║ dbo.Tab    ║ 15               ║ 498589 ║
║ dbo.Tab    ║ 16               ║ 504068 ║
║ dbo.Tab    ║ 17               ║ 504068 ║
║ dbo.Tab    ║ 18               ║ 498589 ║
║ dbo.Tab    ║ 19               ║ 498589 ║
║ dbo.Tab    ║ 20               ║ 504068 ║
║ dbo.Tab    ║ 21               ║ 487631 ║
║ dbo.Tab    ║ 22               ║ 0      ║
║ dbo.Tab    ║ 23               ║ 0      ║
║ dbo.Tab    ║ 24               ║ 0      ║
║ dbo.Tab    ║ 25               ║ 0      ║
╚════════════╩══════════════════╩════════╝

This rebuilds the table on the new partitioning key after dropping the original primary key.  The effect here is we move the table from being a clustered index to a heap and back into a clustered index.  I'm doing this in a transaction to ensure I don't accidentally leave the table as a heap.

BEGIN TRANSACTION;

BEGIN TRY
    ALTER TABLE dbo.Tab
    DROP CONSTRAINT PK_Tab
    WITH (MOVE TO PartScheme(UpdateDate));

    ALTER TABLE dbo.Tab
    ADD CONSTRAINT PK_Tab
    PRIMARY KEY CLUSTERED
    (UpdateDate, TabID)
    ON PartScheme(UpdateDate);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

When we look at the sys.partitions query, we can see row counts have changed since some rows have been moved into different partitions:

SELECT ObjectName = s.name + '.' + o.name
    , p.partition_number
    , p.rows
FROM sys.partitions p
    INNER JOIN sys.objects o ON o.object_id = p.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name = 'Tab'
    AND p.index_id = 1
ORDER BY p.partition_number;

╔════════════╦══════════════════╦════════╗
║ ObjectName ║ partition_number ║ rows   ║
╠════════════╬══════════════════╬════════╣
║ dbo.Tab    ║ 1                ║ 0      ║
║ dbo.Tab    ║ 2                ║ 328800 ║
║ dbo.Tab    ║ 3                ║ 498680 ║
║ dbo.Tab    ║ 4                ║ 504160 ║
║ dbo.Tab    ║ 5                ║ 504160 ║
║ dbo.Tab    ║ 6                ║ 493200 ║
║ dbo.Tab    ║ 7                ║ 498680 ║
║ dbo.Tab    ║ 8                ║ 504160 ║
║ dbo.Tab    ║ 9                ║ 504160 ║
║ dbo.Tab    ║ 10               ║ 493200 ║
║ dbo.Tab    ║ 11               ║ 498624 ║
║ dbo.Tab    ║ 12               ║ 504068 ║
║ dbo.Tab    ║ 13               ║ 504068 ║
║ dbo.Tab    ║ 14               ║ 493110 ║
║ dbo.Tab    ║ 15               ║ 498589 ║
║ dbo.Tab    ║ 16               ║ 504068 ║
║ dbo.Tab    ║ 17               ║ 504068 ║
║ dbo.Tab    ║ 18               ║ 498589 ║
║ dbo.Tab    ║ 19               ║ 498589 ║
║ dbo.Tab    ║ 20               ║ 504068 ║
║ dbo.Tab    ║ 21               ║ 504068 ║
║ dbo.Tab    ║ 22               ║ 158891 ║
║ dbo.Tab    ║ 23               ║ 0      ║
║ dbo.Tab    ║ 24               ║ 0      ║
║ dbo.Tab    ║ 25               ║ 0      ║
╚════════════╩══════════════════╩════════╝

FYI, modifying the partitioning key on my workstation with limited ram, and all the partitions on the same disk, took over 14 minutes for 10 million rows.  You'll want to practice this on a non-production system to ensure you understand the implications and to get an idea of how much time this might take.  Understand that no client activity can take place while the rebuild is happening, so you'll need to plan for some downtime.


Exposing SQL Server database to internet I have a POS application that is used by a business. The application has a SQL Server backend and there’s a local machine that acts as a server for the DB. The other computers within the office connect to this server. This happens over TCP/IP. I am wondering if it is possible and/or how difficult it would be to expose this database and allow read access to an offsite application. Essentially I want to be able to pull data from a local database into AWS on a regular automated schedule. If anyone has any ideas that could help point me in the right direction, that would be great. This is not an application I’m a developer of, so I don’t have access to the application code or anything like that. I guess what it boils down to would be the equivalent of an off-site backup. Thanks in advance!

what I’m really looking to do is create offsite backups automatically

SQL Server can natively backup to Azure Blob Storage.  See Backup to URL.

But whatever offsite storage you use, the right solution is for SQL Server (or code running on the local network) to push the data out.  Not have code running elsewhere pull it out.

That way you don't need to open anything to the internet, or use a VPN.  You just need to open outbound ports, to connect to the internet.


dbcc shrinkfile not reducing size of log file in sql server 2008 I am shrinking log file of SQL Server 2008 R2 database, but it is not working. I was using this code:

ALTER DATABASE [database] SET RECOVERY SIMPLE
DBCC SHRINKFILE(DATABASE_LOG, 20)
ALTER DATABASE [database] SET RECOVERY FULL

I have seen this article on stackoverflow, and tried the following code as per this post, but this also did not solve my problem.

BACKUP LOG [database] TO DISK = 'D:\database.bak' GO
ALTER DATABASE [database] SET RECOVERY SIMPLE
use [database] declare @log_File_Name varchar(200)
select @log_File_Name = name from sysfiles where filename like '%LDF'
declare @i int = FILE_IDEX ( @log_File_Name)
dbcc shrinkfile ( @i , 50)
ALTER DATABASE [database] SET RECOVERY FULL

Please help.

First do a DBCC LOGINFO(); then go to the very last row.  Is the status 2?  That means it's active and it cannot be shrunk until that VLF has released that portion of the log file so next checkpoint it could flush it to disk (assuming it has resources).

If it is active you can do a select name, log_reuse_wait_desc from sys.databases to see what is using it.  Sometimes it's mirroring, another HA solution, a long transaction, etc.

You can then do a transaction log backup and checkpoint; to force the log file to roll over to the first VLF.  Actually you're in SIMPLE recovery mode so a backup is not needed, that's for FULL and BULK LOGGED only.

Once it's rolled over and it is no longer an active VLF, you can remove it.  Note that it will remove all the non active VLFs until it hits an active one.  At that point it will stop.  You'll have to roll over again.

Now would be a great time to brush up on VLF fragmentation and management so you optimize your performance and time to recovery.  While were at it, here's some other great info.


Column name on NOT NULL constaint violation not shown I'm using an Oracle 19 DB that is structured into multiple layers by using DB users. Here is a simplified overview of the structure:

User DATA: Has the table XTEST and the view TEST. The view is created with a CREATE VIEW TEST AS SELECT * FROM XTEST statement, so it contains all columns of the table.
User APP: Uses the view DATA.TEST for all SQL and DML.
The APP user does not have any grants on the table DATA.XTEST, but does have SELECT,INSERT,UPDATE,DELETE grants on the view DATA.TEST.

Now to my problem:
If a NOT NULL constraint on the table DATA.XTEST is violated, the error message doesn't show the column name. The error message is literally:

ORA-01400: cannot insert NULL into (???)

The DB used Oracle 11 some time ago and back then we had the same problem, but could fix it by granting the REFERENCES privilege on the table DATA.XTEST to the APP user (see related question: Grant privilege to see column names in null constraint violations when inserting in views). But this doesn't work any more in Oracle 19.
Does anyone know how to get the column name in the error message?

There is a security consequence to setting SQL92_SECURITY to false.
From https://www.stigviewer.com/stig/oracle_database_12c/2020-04-01/finding/V-61429

The configuration option SQL92_SECURITY specifies whether table-level
SELECT privileges are required to execute an update or delete that
references table column values. If this option is disabled (set to
FALSE), the UPDATE privilege can be used to determine values that
should require SELECT privileges. The SQL92_SECURITY setting of TRUE
prevents the exploitation of user credentials with only DELETE or
UPDATE privileges on a table from being able to derive column values
in that table by performing a series of update/delete statements using
a where clause, and rolling back the change.
In the following example, with SQL92_SECURITY set to FALSE, a user
with only delete privilege on the scott.emp table is able to derive
that there is one employee with a salary greater than 3000. With
SQL92_SECURITY set to TRUE, that user is prevented from attempting to
derive a value.

SQL92_SECURITY = FALSE
SQL> delete from scott.emp where sal > 3000;

1 row deleted

SQL> rollback;

Rollback complete

SQL92_SECURITY = TRUE
SQL> delete from scott.emp where sal > 3000;

delete from scott.emp where sal > 3000 *

ERROR at line 1: ORA-01031: insufficient privileges

If this is a concern for you, then consider using a PL/SQL API package to make changes to your table, rather than granting access directly to the user. See "Guarding Your Data Behind a Hard Shell PL/SQL API" for reference. Also see here: https://www.youtube.com/watch?reload=9&v=GZUgey3hwyI. A singleton package with definers rights could raise the error back to the app without granting excessive privileges or altering the overall security posture of your system.


How to count more than one time with different conditions? Having a table like this:

I want to get a result like this:

Something like:

SELECT ParentId, COUNT(SomeValue = 1) [CountMin], COUNT(SomeValue > 1) [CountRest]
FROM MyTable
GROUP BY ParentId

You can use SUM(CASE...)

SELECT
    ParentID,
    SUM(CASE WHEN SomeValue = 1 THEN 1 ELSE 0 END) AS CountMin,
    SUM(CASE WHEN SomeValue <> 1 THEN 1 ELSE 0 END) AS CountRest
FROM
    MyTable
WHERE
    ParentID = 123;


SQL Server Availability Group configuration steps I was preparing for a testing and got into question:
In which order should you perform the actions (5 total actions) to create availability group ?

I arranged actions as below:

  Launch the Failover Cluster Manager and configure AO-AG1 and AO-AG2 as servers in the cluster. Name the cluster WINCL1
  Enable SQL Server 2016 Always On Availability Group feature
  Add and configure the replica and create availability group listener named AGL1 on port 1433
  Create the Always On Availability Group and select the user databases for the availability group
  Select the Full data synchronization method and specify te network path: \DATA-CI1\SQL

Learning material says that correct order is below:

  Launch the Failover Cluster Manager and configure AO-AG1 and AO-AG2 as servers in the cluster. Name the cluster WINCL1
  Add and configure the replica and create availability group listener named AGL1 on port 1433
  Enable SQL Server 2016 Always On Availability Group feature
  Create the Always On Availability Group and select the user databases for the availability group
  Select the Full data synchronization method and specify te network path: \DATA-CI1\SQL

As you can see Learning material states that you "2. Add and configure replica..." before you "3. Enable SQL Server ... AG feature"

This seems wrong to me - as I remember you should "2. Enable SQL Server ... AG feature" before actually creating availability group

Who is right - me or learning material ?
Or both are wrong and steps should be rearranged differently ?

The learning material is incorrect.  You have to enable the high availability feature and restart the SQL Server service before you can add a SQL Server to an availability group.


Two SQL Servers cross-mirroring each other? I'm looking to see if I can create a "Brother's Keeper" environment in SQL Server 2008 Standard Edition.  I have a large number of databases across multiple servers.  I'm not worried about scalability (for now), but wanted to know if anyone has had practice mirroring the databases hosted on server A on server B, the ones on Server B on Server C, etc.... I'm trying to purely have a failover system without significant additional licensing/resources.

Thanks!

What you are telling is possible. Since you are running Standard Edition of SQL Server you have to bear following things :

Standard edition supports mirroring ONLY in full safety mode.
You have to be careful about number of databases your are configuring mirroring for, since it is recommended not to have more than 10 databases mirrored to secondary server. Remember that - On primary server you would need 1 global thread and 2 threads per each of the mirrored databases. On Mirrored server - 1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores. 2001270 KB article talks about the worker threads requirement.
You cannot use database snapshots on the secondary server - since you are on standard edition.

  I'm trying to purely have a failover system without significant additional licensing/resources.

I would suggest you to look into Log-shipping as an alternative solution for your scenario. It is easy to set up and is a good old proven DR technology. Minimum it allows is 1 min to take log backups. Logshipping does not have any hard limitations on the number of databases being logshipped.

Logshipping allows you to logship your databases to more than one destination which is a very good advantage over database mirroring. Also, logshipping has functionality to delay restoring of log files on the secondary server to protect against logical errors. Mirroring does not allow this.

Logshipping as a part of process provides you full backup and subsequent log backups (depending on the frequency you configured - minimum is 1 min) to allow you to have backups so that you can easily setup your environment form them. Mirroring does not.

At the end, a good Disaster Recovery strategy is totally dependent on -

How much downtime  can your business survive? and
How much data loss is acceptable ?


Finding aggregated values based on dates in another table I am using MS SQL SERVER. I have a table EMPLOYEE_ABSENCES that looks like this (it's not actually a temporary table, I made it into a temp table for this example):
CREATE TABLE #EMPLOYEE_ABSENCES
(
    EMPLOYEE_ID  VARCHAR(3),
    ABSENCE_DATE DATE
);

INSERT INTO #EMPLOYEE_ABSENCES
(
     EMPLOYEE_ID,
     ABSENCE_DATE
)
VALUES
    ('111', '2020-03-24'),
    ('222', '2020-04-16'),
    ('111', '2020-07-13'),
    ('333', '2020-07-13'),
    ('111', '2020-10-19');
GO

I have another table that looks like this:
CREATE TABLE #EMPLOYEE_POSITIONS
(
    EMPLOYEE_ID         VARCHAR(3),
    POSITION_START_DATE DATE,
    POSITION_END_DATE   DATE,
    FTE                 NUMERIC(6, 5)
);

INSERT INTO #EMPLOYEE_POSITIONS
(
     EMPLOYEE_ID,
     POSITION_START_DATE,
     POSITION_END_DATE,
     FTE
)
VALUES
    ('111', '2019-09-01', '2020-04-08', 0.75),
    ('333', '2020-01-01', '2020-09-18', 0.25),
    ('333', '2020-01-01', '2021-01-19', 0.5),
    ('222', '2020-01-19', '2021-01-19', 0.2),
    ('222', '2020-02-02', '2020-04-20', 0.3),
    ('111', '2020-05-20', '2020-07-14', 0.4),
    ('333', '2020-06-06', '2020-09-18', 0.25),
    ('111', '2020-07-15', '2021-01-20', 0.5),
    ('111', '2020-08-08', '2020-12-24', 0.3);
GO

As employees change position, their FTE changes.
An employee can hold more than one position, in which case, their FTE on any given date is the total of all FTE values they hold on that date.
For example, employee 111 holds a single position with a value of 0.5 from 2020-07-15 until 2020-08-07.
On 2020-08-08, she gains a second position with a value of 0.3 giving her a total FTE of 0.8 until on 2020-12-25 she loses her second position and returns to a single position with a value of 0.5
I need a query that will tell me the dates employees were absent in the year 2020, the employee number and their sum(FTE) on the date they were absent.
I am expecting a result like this for the above tables:
-- 2020-03-24, 111, 0.75
-- 2020-04-16, 222, 0.5
-- 2020-07-13, 111, 0.4
-- 2020-07-13, 333, 1
-- 2020-10-19, 111, 0.8

Note that two employees were absent on 13 July: employee 111 with an FTE of 0.4 on that date and employee 333 with a total FTE of 1 (0.25+0.5+0.25) on that date.
I think that the solution will involve creating a tally table of all dates in 2020, joining it to an aggregate of the EMPLOYEE_POSITIONS table to get the employees' total FTE for each day of the year, then joining that with the EMPLOYEE_ABSENCES table, but I can't seem to get it to work. Does anyone have any suggestions? Is there an easier way?
Thank you,
Brian

Something like this?
SELECT emp.Employee_Id,
       emp.Absence_date,
       SUM(FTE) AS FTE
  FROM #EMPLOYEE_ABSENCES AS emp
  JOIN #employee_Positions AS pos ON absence_date
                             BETWEEN position_start_date AND position_end_date
                                 AND pos.Employee_Id = emp.Employee_Id
 GROUP BY emp.Employee_Id,
          emp.Absence_date
 ORDER BY Absence_date ASC


Count distinct values of a column and insert them to another table So, I have a database for logs and statistics. But, the amount of data sent is too high, so I was thinking of grouping the data into another table. So the structure of the original Database is:

ID  int
problem varchar(250)
channel_number  int
username varchar(50)
ip_address  nvarchar(50)
buffer_error_type varchar(50)
source int
Is_On   bit
logtype int
viewing_channel nvarchar(20)
playtime    int
date    bigint

where the field problem has values such as: "The User cannot connect to server","User's categories are 0","User's channels are 0","Buffer Error"(here  Buffer Error is specified by the field buffer_error_type as Warning or Error) etc. In the second table I would like to perform a insert select statement, which counts every single value of the field problem + specify the Buffer_Error value as Error,ignoring the Warning. So,the second table should have this structure:

username    varchar(50)
monthSel    bigint
CategoriesZero  int
ChannelsZero    int
BufferError int
BlackScreen int
OtherLogs   int
id  int

I do not know if something like this is possible, however, I tried this query:

insert into LogsArchive.dbo.ProblemsArchive (username,monthSel,CategoriesZero,ChannelsZero,BufferError,BlackScreen,OtherLogs)
(select (username),25114721, count(distinct problem),count(distinct problem),count(distinct problem),
count(distinct problem),count(distinct problem) from DbLogs.dbo.Logs
where username in (select distinct username from DbLogs.dbo.Logs)
 group by problem,username)

But I know I can't achieve the result with this.

Try using

SUM(Case WHEN problem Like '%err1%' THEN 1 ELSE 0 END) //for problem count

'%err1%' is a sample error containing letter to rectify which error is this.


Order by custom filter without certain data I have a similar issue to the question in:
ORDER BY using custom priorities for text columns
...except that I want to only get the data out if I have certain values.  To clarify, we have a table that stores both email addresses and phone numbers in the same column.
I cannot change this structure without breaking a critical system that I do not have source code access to. I need to be able to retrieve only email addresses, and have the query show NULL when there is no email address.
The table structure is:

ID (Int)
Description Code (Int) (can use same values for both emails and phone numbers, nothing distinguishing here)
Number (varchar)

Right now, similar to the question above, I have my SQL as:
order by case
    when con2.NUMBER like '%@ourdomain.com' then 1
    when con2.NUMBER like '%@%' then 2
    else 3
    end

I would like to not show phone numbers in this output.  Is there an easy adjustment to my code to facilitate this?
Example

Records for Sally
ID    Type     Number
1     1011     (215)555-1213
2     1011     (215)555-1312
3     1012     sally@ourdomain.com
4     1013     sallyf@gmail.com

Records for Frank
ID    Type     Number
5     1013     frankiebaby@yahoo.com
6     1013     franks@ibm.com

Records for Julius
ID    Type     Number
7     1010     555-123-9876
8     1011     425-369-1470

Output would return sally@ourdomain.com for Sally and frankiebaby@yahoo.com for Frank, but NULL for Julius.
I need to prioritize one domain over any others, so addresses in our own domain would show up if it's available, if not, then google, hotmail, or whatever else comes secondary.

This will get your result set, though I did add the Name field for clarity. The common table expression (cte) with ROW_NUMBER() will get the email priority. Then I left joined that back to the base table to return the names and a NULL for Julius.

CREATE TABLE #emailOrPhone (ID INT IDENTITY(1,1), Type INT, Number VARCHAR(50), Name VARCHAR(20))

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1213','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1312','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1012,'sally@ourdomain.com','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'sallyf@gmail.com','Sally')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'frankiebaby@yahoo.com','Frank')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'franks@ibm.com','Frank')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1010,'555-123-9876','Julius')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'425-369-1470','Julius')

;WITH cte AS
(
SELECT Name, Number
, CASE WHEN Number like '%@ourdomain.com' THEN 1 ELSE 2 END AS 'emailPriority'
, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) as rn
FROM #emailOrPhone
WHERE Number like '%@%'
)

SELECT DISTINCT Names.Name, Emails.Number
FROM #emailOrPhone as Names
LEFT JOIN cte as Emails on Names.Name = Emails.Name and Emails.rn = 1
ORDER BY Names.Name, Emails.Number

DROP TABLE #emailOrPhone


How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora? I have access to a ORACLE 11g server using the following listener.ora

# listener.ora Network Configuration File: D:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

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

and it serves all the instances on that server, as I can verify by

lsnrctl status

My problem is, that when I make a fresh install of Oracle 11g in a virtual machine, I have to use a listener.ora like

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

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

   (SID_DESC =
     (GLOBAL_DBNAME = ORCL)
     (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
     (SID_NAME = orcl)
    )

   (SID_DESC =
     (GLOBAL_DBNAME = BMBK)
     (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
     (SID_NAME = BMBK)
    )

 )

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

ADR_BASE_LISTENER = C:\app\oracle

Without the SID_LIST_LISTENER enties, i.e. using a listener.ora like that from the other server I get

C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 27-NOV-2011 10:14
:24

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Anmeldung bei (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS des LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Startdatum                27-NOV-2011 10:14:08
Uptime                    0 Tage 0 Std. 0 Min. 18 Sek.
Trace-Ebene               off
Sicherheit                ON: Local OS Authentication
SNMP                      OFF
Parameterdatei des Listener C:\app\oracle\product\11.2.0\dbhome_1\network\admin\
listener.ora
Log-Datei des Listener    c:\app\oracle\diag\tnslsnr\WSV-BK-W7en64-S\listener\al
ert\log.xml
Zusammenfassung Listening-Endpunkte...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.254.200.102)(PORT=1521)))
Der Listener unterst³tzt keine Services
Der Befehl wurde erfolgreich ausgef³hrt.

The last message translates to

The listener supports no Services.

I have no idea, why these two servers behave differently. I found no hints in configuration files nor in registry keys.

Edit:

I guess the answer is somewhere buried in this post by Burleson Consulting, but I don't find the spot, where it is actually configured.

Bit late to the party on this one...

The database should register with the listener automatically, making the SID_LIST entries redundant, and this seems to be happening with your 192.168.111.111 environment. If the listener is started after the database it can take a while for it to register, and there may be situations where it doesn't do so at all.

You can attempt to make it register with an alter system register command. The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which according to the documentation is equivalent to hostname:1521.

If alter system register doesn't make the service appear in the lsnrctl status output then I'd suspect it's unable to either identify the hostname or resolve it, or it's resolving to a different address than the one the listener is on, or LOCAL_LISTENER is set to something invalid for the virtual box. You can set the LOCAL_LISTENER to either match the listener.ora directly, e.g:

alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;
alter system register;

Or you can use an alias that has those details in your tnsnames.ora. For example, if you add a tnsnames.ora entry like:

MY_LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )

Then you could:

alter system set local_listener='MY_LISTENER' scope=both;
alter system register;

The only real advantage I can see of using the tnsnames.ora version, apart perhaps from brevity, is that you can change the configuration in the SQL*Net files rather than in the database; and even that only really seems useful if you're cloning databases between machines, or have your listeners and databases running under different accounts (e.g. with a grid user for RAC/HA).

There's more on LOCAL_LISTENER here.

Edit: And this seems quite comprehensive.


Should I use VACUUM on a SQLite db with a log-structured filesystem? I'm runnig a SQLite database on nilfs (which is a log-structured filesystem). Every now and then I delete old records, so that the size of the database never surpass a certain amount.
But since the filesystem is log-structured (also, I'm running from flash memory) I am uncertain if I should also run VACUUM command or not.

When the order of pages in the file has no relation to the order of pages on the disk, reordering the file does not make sense.


Deciding which MySQL execution plan is better Background:

I have a query that I'm trying to speed up, involving 3 tables:

omgenvelope has 5 million rows
omgcust has 195 rows
omginput has 35836 rows

I'm obtaining the distinct omginputs referenced by certain omgenvelopes and grabbing some data from omgcust belonging to the omginput.

My two query variants so far:

SELECT customer, custname, idomginput, filename, omginput.laststamp
 FROM omginput, omgcust
 WHERE idomginput IN
     (SELECT DISTINCT(lastinput) FROM omgenvelope WHERE envstate NOT IN (42,46,65,70,250))
   AND idomgcust=customer ORDER BY omginput.laststamp, filename;

vs.

SELECT DISTINCT customer, custname, idomginput, filename, omginput.laststamp
  FROM omgenvelope
  JOIN omginput ON (idomginput=lastinput AND envstate NOT IN (42,46,65,70,250))
  JOIN omgcust ON (idomgcust=customer) ORDER BY omginput.laststamp, filename;

Query plans:

EXPLAIN on the upper query, with the IN (SELECT...) subquery, shows this plan (abbreviated):

  select_type: PRIMARY
        table: omgcust
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 195
        Extra: Using temporary; Using filesort
--------------
  select_type: PRIMARY
        table: omginput
         type: ref
          key: fk_omginput_omgcust1_idx
      key_len: 4
          ref: tracksdb.omgcust.idomgcust
         rows: 109
        Extra: Using where
--------------
  select_type: DEPENDENT SUBQUERY
        table: omgenvelope
         type: index_subquery
          key: fk_omgenvelope_omginput1_idx
      key_len: 4
          ref: func
         rows: 867
        Extra: Using where

An the new query that I'm, considering to use instead:

  select_type: SIMPLE
        table: omgenvelope
         type: range
          key: fk_omgenvelope_omgstate1_idx
      key_len: 4
          ref: NULL
         rows: 886220
        Extra: Using where; Using temporary; Using filesort
--------------
  select_type: SIMPLE
        table: omginput
         type: eq_ref
          key: PRIMARY
      key_len: 4
          ref: tracksdb.omgenvelope.lastinput
         rows: 1
--------------
  select_type: SIMPLE
        table: omgcust
         type: eq_ref
          key: PRIMARY
      key_len: 4
          ref: tracksdb.omginput.customer
         rows: 1

Question:

How do I quantify which one is more efficient?
The old one with the DEPENDENT SUBQUERY looks pretty simple, and doesn't yield so many rows in each step (195 * 109 * 867). The new candidate on the other hand shows (886220 * 1 * 1) rows.

So my question is how to interpret these estimated numbers of rows.
Can I just multiply them and compare the products, or do I need to think more about what the RDBMS is actually doing when executing the queries?

Multiplying the rows is invalid for several reasons:

Many times, the rows examined are an approximation (based on statistics, not accurate), good for query plan selection, but not for performance calculation
The total number of rows examined on a nested loop join (A, B) is not rows_examined_on_table_A * rows_examined_on_table_B, but rows_examined_on_table_A + rows_returned_from_table_A * rows_examined_on_table_B. Where clauses can make a huge difference on that, although it is true that the mentioned calculations is many times used as a broad approximation, assuming the indexes are being created properly and the main causes of filtering out results.
Modern MySQL versions do not use always a nested loop join approach for executing joins and subqueries. Check 5.6 subquery optimizations and other optimization documents on the same manual. Additionally, some of the new optimization techniques do not modify the predicted examined rows, which at some times can be way lower than the one printed, even if it has been calculated exactly.

In particular, on your first query, you are hitting a well know MySQL bug? limitation?  in which an IN subquery is identified as a DEPENDENT SUBQUERY, even if it really isn't, forcing the outmost query to be executed without an index (full table scan) in order to test all possible values of the first table. That is usually an indicator that it is a bad query. It seems not to bee too bad in this case, as the table is small, but it is usually an indication of bad performance.

The other thing that should bring your attention is the Using temporary; Using filesort. Filtering is not the only thing where you should focus, as these extra pieces of information are telling you that a large sorting has to be done using a temporary table (that may or may not end up on disk, but at least has to be materialized). That is another indicator of potential bad performance, that in some cases can be avoided with the right indexes.

I will not tell you which is the right query to use (partially, because I do not know all the variables: indexes, tables structure, etc., and in most cases it will depend on the particular hardware/resources available), but I will tell you the tools to decide:

Profile the query- obtain the post execution times and how much of it it is being invested in what. You can use SHOW PROFILES up to 5.5, and the performance_schema starting with 5.6.
As time can be sometimes variable (for example, depending on other queries being executed at the same time, depending on the buffer pool contents) Obtain post-execution statistics with SHOW SESSION STATUS. In particular:

FLUSH SESSION STATUS;
SELECT ... ;
SHOW STATUS like 'Hand%';

will give you the exact number of handler calls done (approximately, the number of rows read and written for that particular query- although that is not 100% accurate, as it depends on the particular engine implementation).

You may also want to monitor other status variables, like the created temporary tables, created temporary tables on disk and sort passes/sorted rows.

All of these will give you post-execution, exact, time-independent parameters to evaluate the performance of a query. Percona even has a patch for the slow log to output that information on the logs instead of using performance_schema.

With those extra pieces of information you will be able to evaluate more objectively which query is better, and not relying exclusively on EXPLAIN, which only provides limited pre-execution information.


How to set delayed replication with MariaDB 10.0.11 Is it possible to set delayed replication on MariaDB 10.0.11?
I tried the following command:

CHANGE MASTER TO MASTER_DELAY = N;

It returns the following error:

  You have an error in your SQL syntax; check the manual that
  corresponds to your MariaDB server version for the right syntax to use
  near 'MASTER_DELAY = 259200' at line 1

If not supported, are there any other recommended solutions to protect against user mistakes on the master & allow a DBA to roll back a delayed slave to the time just before the disaster ?

Thank you,

MASTER_DELAY is not available in MariaDB 10.0.x. You may want to try Percona's pt-slave-delay.

Usage:

pt-slave-delay [OPTION...] SLAVE-HOST [MASTER-HOST]

Example: To hold slavehost one minute behind its master for ten minutes:

pt-slave-delay --delay 1m --interval 15s --run-time 10m slavehost

Source: http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html


SSRS Auto refresh stops working I've got a SSRS report which is used for a wallboard to display basic stats and information to the department via a Raspberry Pi attached to a creen

This has the AutoRefresh Option set to 60s however since patching today where we turned on Snapshot Isolation to remove a lot of blocking that was occuring every morning and mid afternoon when the daily reports ran the auto refresh option seems to be failing.

I've tried re-deploying the report and even created a completely new report that just puts up the date& time to keep track of it refreshing, it sometimes works for a few minutes but then just stops refreshing

The tests I ran included a refresh every 10 seconds to see if it was timing out the execution running over 60 seconds

Out of the 5 separate executions I have just ran, one ran for 542 seconds before no longer refreshing, one ran for 339 one for 164 and two of them failed to refresh at all

Previous this would run until the wifi connection was lost on the Pi and the page errors while refreshing (NOTE all testing has been done locally on wired connection no network drops)

previous tests before that set have been done across different browses in / out incognito mode and all produce the same results

Any idea why this is happening, and potential fix that doesn't involve us stopping everything on the server again to turn off Snapshot issolation

The documentation states that you should not enable snapshot isolation on SSRS database.

Check here:

Enabling Snapshot Isolation on the Report Server Database.
You should follow the instructions there to turn it off.

Excerpt from the docs:
"...You cannot enable snapshot isolation on the report server database. If snapshot isolation is turned on, you will encounter the following error: "The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available."

As for the cause of the blocking, and ways to fix them, here are some links to help you:

SSRS Blocking Processes
SSRS issues locks to provide a multi-threading mechanism. Once all threads running the report that you wrote, end, the lock is removed. So if you see these blocks regularly, it is usually due to a slow query.
Advice on setting up a Reporting server
(advice on how to set up a second reporting instance)


How do I archive and restore selected tables using SQL Server? How do I archive and restore selected tables using SQL Server?

You have to use a 3rd party tool such as Red Gate SQL backup pro to restore. Or restore as a different DB and copy across.

Selective restore makes sense, occasionally, but selective backup usually wouldn't. You-d have to roll your own selective backup with SMO for example


improve mysql query in large tables I have a query to get data between current time and INTERVAL 15 minutes

Table calls have 39790720 items;

SELECT src,unique,dstchannel,chan,calldate
from calls
WHERE calldate BETWEEN (NOW() - INTERVAL 15 MINUTE) AND NOW()
AND (dstchannel LIKE '%TEXT1/%'
OR dstchannel LIKE '%TEXT2%'
OR dstchannel LIKE '%TEXT3%'
OR dstchannel REGEXP '^SIP/[[:digit:]]{10}-'
OR dstchannel LIKE '%TEXT4%'
OR dstchannel LIKE '%TEXT5%'
OR dstchannel LIKE '%TEXT6%'
OR dstchannel LIKE '%TEXT7%')
AND lastdata NOT LIKE '%TEXT8%'
LIMIT 39780720,39790720

Query 1 row in set (1 min 7.38 sec)

+-------------+--------------+------+-----+---------------------+-------+
| Field       | Type         | Null | Key | Default             | Extra |
+-------------+--------------+------+-----+---------------------+-------+
| calldate    | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| colum1      | varchar(80)  | NO   |     |                     |       |
| colum11     | varchar(80)  | NO   |     |                     |       |
| src         | varchar(80)  | NO   |     |                     |       |
| colum12     | varchar(80)  | NO   |     |                     |       |
| chan        | varchar(80)  | NO   |     |                     |       |
| dstchannel  | varchar(80)  | NO   |     |                     |       |
| colum2      | varchar(80)  | NO   |     |                     |       |
| colum3      | varchar(80)  | NO   |     |                     |       |
| colum4      | int(11)      | NO   |     | 0                   |       |
| colum5      | int(11)      | NO   |     | 0                   |       |
| colum6      | varchar(45)  | NO   |     |                     |       |
| colum7      | int(11)      | NO   |     | 0                   |       |
| colum8      | varchar(20)  | NO   |     |                     |       |
| colum9      | varchar(32)  | NO   |     |                     |       |
| colum10     | varchar(255) | NO   |     |                     |       |
+-------------+--------------+------+-----+---------------------+-------+

+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | calls | ALL  | NULL          | NULL | NULL    | NULL | 39791545 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+

When %TEXT1% is a variable only need contain like '%Sip/phone%' in value.

How to improve query?

To enhance the performance:

First of all, add an index on calldate. It will avoid you scanning the whole table.

In the query:

Not sure what is unique in the query.
LIMIT 39780720,39790720 means, "Retrieve 39790720 rows starting from the row number 39790720 in the result". I think you want to revise it.

About the table structure:

It is recommended to have primary key


SQL Server 2008R2 SP1 instance not available for adding features I need to add fulltext capabilities to an existing SQL Server 2008R2 instance but the one I need to upgrade is not actually available. The combobox shows only the one selected, as in the picture. It looks like it could be related to different versions of the two instances but I can't explain why. Any ideas?

I agree with Mark's comment.  Perhaps the default instance was installed with SQL 2012 (code name denali) beta or release candidate?  If that's the case then you should be able to use that installer to try to add features but older versions of the binaries (like SQL 2008 R2) would not be able to.


ASM RMAN BACKUP FAILED I'm trying to migrate a filesystem to ASM but when I start with RMAN backup, I face the following error, which is weird because I haven't performed any backups before on the same database.

RMAN> BACKUP AS COPY DATABASE FORMAT 'DATA';

Starting backup at 28-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=C:\APP\ADMINISTRATOR\ORADATA\ECC\SYSTEM01.DBF
output file name=C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\DATA tag=TAG20150728T143422 RECID=2 STAMP=886257290
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=C:\APP\ADMINISTRATOR\ORADATA\ECC\SYSAUX01.DBF
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/28/2015 14:34:59
ORA-19504: failed to create file "C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\DATA"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

Please advise

If diskgroup DATA already exists in you ASM instance, the correct syntax is:
BACKUP AS COPY DATABASE FORMAT '+DATA';

regards
Giova


postgres: could not receive data from WAL stream: ERROR: requested WAL segment has already been removed Configuration: postgres 9.6 with a 3 cluster node. db1 is the master, db2 and db3 are replicas. WAL files are archived in AWS S3 using custom pgrsync tool. Cluster managed by patroni. The archive_command and restore_command is properly configured on all the nodes.
To simulate: On db1, do heavy writes (like vacuum a large table) and then stop db1 by sudo systemctl stop patroni). db3 becomes the new leader. db2 requests more WAL files, which it gets via the proper restore command from AWS S3, becomes replica to db3.
Now, start db1 again by (sudo systemctl start patroni). But db1 (the old leader and the new to-be-replica) never comes up as a replica and gives the error message could not receive data from WAL stream: ERROR:  requested WAL segment 0000002400053C55000000AE has already been removed. This error message is reported by db3 (the leader), which db1 just logs it.
So, let's see the timeline. Initially db1 was in timeline 35 (0x23) and did write the following files to archive:
0000002300053C55000000AE   (TL is 23 and archive at 53C55/AE)
..
0000002300053C5600000043   (TL is 23 and archive at 53C56/43)

db1 is stopped at this point.
db3's logs show this:
received promote request
redo done at 53C56/A3FFC8C0
selected new timeline ID: 36
database system is ready to accept connections

and db3 copies the following files to archives
00000024.history
0000002300053C56000000A3.partial
0000002400053C56000000A3
0000002200053C55000000AE

As db3 became leader, db2 starts the process to become replica to db3 (which it successfully becomes) and here is the summary of the logs:
restored log file "00000024.history" from archive
restored log file "0000002300053C55000000AE" from archive
restored log file "00000023.history" from archive
redo starts at 53C55/AE0001A8
restored log file "0000002300053C55000000AF" from archive
...
restored log file "0000002300053C5600000042" from archive
Retrieving 0000002300053C5600000043 to pg_xlog/RECOVERYXLOG FAILED  (log by pgrsync)
...
Retrieving 0000002300053C56000000A2 to pg_xlog/RECOVERYXLOG FAILED
restored log file "0000002400053C56000000A3" from archive
consistent recovery state reached at 53C56/A3FFE900

db1 is started now and here are the logs:
LOG: database system was interrupted while in recovery at log time 2021-01-28 04:08:01 UTC
HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target
LOG: invalid record length at 53C55/AE0001E0: wanted 24, got 0
LOG: started streaming WAL from primary at 53C55/AE000000 on timeline 36
FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000002400053C55000000AE has already been removed
... and repeats this over and over

Points to note:

0000002400053C55000000AE was never written to archives by any postgres node. The old leader (db1) copied the archive 0000002300053C55000000AE (note: 0023, not 0024) before it was stopped.
The new leader (db3) copied 0000002200053C55000000AE (note: 0022, not 0024)
max_wal_size is set to 1024 on all nodes.
After db3 became the new leader, there was hardly any activity on the nodes. db3 only writes WAL files every 10 mins (archive_timeout=600s).

Questions:

Is there any thing wrong in the configuration that makes the the old leader asking for a WAL segment, which the new leader does not have?
How to restore the old leader (db1) at this state, without having to erase and start over?

We had a similar situation and ended up initializing from the current primary. There is a nasty bug here. So, to play safe my advice is to re instantiate the slave. (until the bug fix is in place)
If you have a nice reproducible case, it would help to document it so it can be studied and with a little luck fixed in the code.
Reporting bugs: https://www.postgresql.org/docs/current/bug-reporting.html
PostgreSQL 12.3, 11.8, 10.13, 9.6.18, and 9.5.22 Released!
This bug was fixed in 9.5.22 and 9.6.18 and 10.13 and 11.8 and 12.3:
Avoid premature recycling of WAL segments during crash recovery (Jehan-Guillaume de Rorthais)
WAL segments that become ready to be archived during crash recovery were potentially recycled without being archived.


Cannot perform SELECT COUNT(*), rows over 1 billion on a table I have this table that contains over a billion rows and to issue the query, I issued:

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'audit_tb' AND (index_id < 2)

Output:
1055075195

I was asked to DELETE records in 2014 so to test out, I tried Deleting the last 3 months in 2014, it's been over 1 hr and it's still executing, any ideas?  I suspect there are bad rows.

 DELETE
  FROM [audit_tb]
  where datetime >= '2014-01-01 00:00:00'
  AND datetime <= '2014-03-31 00:00:00' --3 months in 2014

The table also has a CLUSTERED INDEX.

Here is a script to delete in chunks. It deletes in chunks of 4500 records to avoid lock escalation. It performs 100 loops to delete a total of 450,000 rows.
If your database is using the FULL recovery model, you may want to backup the transaction log after each run to keep if from growing too large.

SET NOCOUNT ON;
DECLARE @continue INT
DECLARE @rowcount INT
DECLARE @loopCount INT
DECLARE @MaxLoops INT
DECLARE @TotalRows BIGINT
DECLARE @PurgeBeginDate DATETIME
DECLARE @PurgeEndDate DATETIME

SET @PurgeBeginDate = '2014-01-01 00:00:00'
SET @PurgeEndDate = '2014-03-31 23:59:59'

SET @MaxLoops = 100
SET @continue = 1
SET @loopCount = 0

SELECT @TotalRows = (SELECT COUNT(*) FROM audit_tb (NOLOCK) WHERE datetime between @PurgeBeginDate and @PurgeEndDate)
PRINT 'Total Rows = ' + CAST(@TotalRows AS VARCHAR(20))
PRINT ''

WHILE @continue = 1
BEGIN
    SET @loopCount = @loopCount + 1
    PRINT 'Loop # ' + CAST(@loopCount AS VARCHAR(10))
    PRINT CONVERT(VARCHAR(20), GETDATE(), 120)

    BEGIN TRANSACTION
        DELETE TOP (4500) audit_tb WHERE datetime between @PurgeBeginDate and @PurgeEndDate
        SET @rowcount = @@rowcount
    COMMIT

    PRINT 'Rows Deleted: ' + CAST(@rowcount AS VARCHAR(10))
    PRINT CONVERT(VARCHAR(20), GETDATE(), 120)
    PRINT ''

    IF @rowcount = 0 OR @loopCount >= @MaxLoops
    BEGIN
        SET @continue = 0
    END
END

SELECT @TotalRows = (SELECT COUNT(*) FROM audit_tb (NOLOCK) WHERE datetime between @PurgeBeginDate and @PurgeEndDate)
PRINT 'Total Rows Remaining = ' + CAST(@TotalRows AS VARCHAR(20))
PRINT ''

GO

I have used this in a SQL Agent job to run repeatedly until the required number of rows were removed. I just schedule the job to run every 15 minutes so my tran log backup job runs between runs of this job.
I hope this helps


Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize? I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation.

Would it be useful to run -check with this option or does the -optimize already take care of that?
I am only concerned because we have large tables (over 100GB) and this is on a production system and we don't want to have unnecessary downtime.

tom.

Well, I can at least save you some time wondering about this.  The mysqlcheck utility only does one thing at a time.

sqlbot@dev:~$ mysqlcheck --check --optimize --all-databases --verbose
Error:  mysqlcheck doesn't support multiple contradicting commands.

So, if you want to do both, you'll need 2 runs.  While --optimize doesn't appear to first go through the identical motions to "check" the tables, it does rebuild them, which would imply that it has to be able to read them successfully.

I've never seen CHECK TABLE find a new problem with an InnoDB table, but if it does, InnoDB will shut down the MySQL Server, according to the docs... and I would think it likely that "shut down" means "force crash" to keep the corruption contained.  On the other hand, if you have tables that are corrupt, OPTIMIZE TABLE (in InnoDB) would likely "help you find" that corruption in much the same way.

Arguably, checking the tables before optimizing seems redundant, though I would welcome correction on this point; however, it also seems potentially wise in the face of such a large amount of data that you're about to rebuild, to invest the time running through the  less obtrusive "check" process before launching into the "optimize" process.

Under the hood, all mysqlcheck really does is connect to the server as a client and execute the corresponding SQL statements.


Implementing a simple ERD in MySQL: is the implementation below 100% compliant with the ERD? ERD

Consider the following ERD:

Tables

From it, I derived the following tables:

Company
------------------
c_id        c_name
------------------

CompanyEmail
------------------
c_id        e_id
------------------

Email
------------------
e_id        e_addr
------------------

Note: the use of the relationship table (CompanyEmail) is not arbitrary.

Implementation

From those tables, I made the following implementation (tested in MySQL Workbench 6.1):

http://pastebin.com/3A0H21kw (please bear with my lack of skills :)

I guess the most important part of it (besides learning the language) was deciding which attribute is FK and the use of CASCADE. Is it [CASCADE] the implementation of parcial/strong participation? and/or weak/strong entity?

Question

Does my implementation accurately complies with the ERD? If not, what would be a better solution?

For the simple case shown, with a business rule something like "Each company can be contacted at zero to many email addresses; each email address directs to exactly one company" it would be more usual to have the company id as a foreign key in the email table.  What you have shown will work, but there will be a one-to-one relationship between Email and CompanyEmail, which is redundant.

For your linked question, where you actually have a Company, a Branch and an Employee, all of which can have many email addresses and any one email address could relate to any or all of Company, Branch and Employee, things are slightly different.  In the logical data model I would be inclined to show Email and the intersection entity types explicitly to document the rules fully.  In the physical table design, however, I would consider omiting the Email table.  This is because its only columns are e_addr and e_id, so e_id is really just a synonym for e_addr and e_addr can replace e_id in the intersection tables CustomerEmail, BranchEmail and EmployeeEmail.  You can still answer all the questions about one email address's re-use but won't have the actual table to maintain.  It would mean an email address disappeared from your system when it stopped being used.  That's probably OK in practice.


Re-page Oracle tables after removing CLOB fields I have a table that has a large amount of CLOB data and a large number of records. Even with indexes, some queries are extremely slow. My assumption is that the reason for this is two fold:

There's a lot of data being selected.
The data for these tables is spread out over a large number of pages in the db.

We decided to move the CLOB data to another table for the DATA and modify our queries to no longer select the CLOB columns and don't join to the new DATA table but our queries are still extremely slow. Is there some additional step we need to take to tell Oracle to re-optimize the table that no longer has the CLOBs?

One way to "re-page the table" is to use the ALTER TABLE... MOVE command. Behind the scenes Oracle will make a temporary copy of the table and switch to that copy. If your table has a lot of unused space in blocks then you might be able to reclaim some of that after the move. If your table has any indexes you will need to rebuild them. I struggled to find a good documentation link for MOVE but try this one.


Database Design help I am building an application for comparing two or more songs. We have millions of songs. Currently we have access to raw data files (form a company like soundcloud) consisting of

1) Daily Users.csv  with new signups

2) Daily Tracks.csv with new songs released

3) Daily streams.csv with today's streams.

For my application, the input will be two or more songs which has to be compared. The songs will be compared based on the data of users who listen to the songs.
For the database design, I am thinking of relational database with

1) One Table for for users data

2) One table for tracks data.

3) Millions of tables; one for each song. The reason for this is that on query, it will be very easy to pick up relevant tables and compute statistics for each table.

Moreover, for a particular song, for a particular user, we want to store ordered time series data of the source of stream ( collection, album page, search). I am thinking of storing it as a json in Stream Source field in Song_ID tables.

Is it a good relational database design? Or should I switch to non relational databases? In that case which database would you suggest?

If you wish to compute statistical data a Relational Database is generally better than a non-relational as all the links for your statistics will be inherent in your table design. Millions of rows will require watching your performance and adjusting appropriately but is not in the danger zone architecturally speaking, and since your data is being inserted once a day that gives you a reliable window for any index rebuilds, table statistic updates, etc...

However, do not create millions of tables for each song, that is a very bad idea. You'll instead want to create a series of tables representing the relations between your Users and the Songs that you will pull the statistics from. Based on your examples I would just add a single linking table between Users and Tracks instead of your millions of tables, it should look something like this:

CREATE TABLE UserTracks
(
    userID INT
    ,trackID INT
    ,streamSource NVARCHAR(100)
    ,CONSTRAINT FK_UserTracks_Users FOREIGN KEY (userID) REFERENCES Users (userID)
    ,CONSTRAINT FK_UserTracks_Tracks FOREIGN KEY (trackID) REFERENCES Tracks (trackID)
)
GO
CREATE CLUSTERED INDEX CI_UserTracks ON dbo.UserTracks (userID,trackID)
GO

Since it's a pure joining table between your other two tables creating the Clustered Index on the join columns that would be present most of the time should help with performance. If for some statistics you only need to do half the join (for example Users per track or Tracks per User) you'll probably want to add two more indexes like the following:

GO
CREATE INDEX NCI_UserTracks_userID_streamSource ON dbo.UserTracks (userID) INCLUDE (streamSource)
GO
CREATE INDEX NCI_UserTracks_trackID_streamSource ON dbo.UserTracks (trackID) INCLUDE (streamSource)
GO

Those should cover most of your potential performance problems right off the bat. Remember, clean INNER JOINs are your friend, make your structures do most of the work for you, avoid trying to piece the data together yourself via complex dynamic queries.


Last used index from specific table how can i take last used index from a specific table and add a +1 to it?

database login, table cars

I think i need to add it here ####

  $sql = 'INSERT INTO '.$this->options['db_table']
                  .' (carID,name, size, type, title, description)'
                  .' VALUES (#############,?, ?, ?, ?, ?)';

The best way for that is to use the sequence way which is auto increment facility,  example:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

so in that case you don't need to mention that id in the insert like:

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

also you can get the inserted id from the function

 LAST_INSERT_ID()

so here the id is auto last rec id + 1, you can see full reference about it from MySQL dev site:
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html


Issue with adding a database to an Availability Group I am having an issue with a particular AG where I am adding a DB (test123 below) to the AG and on primary it will show everything OK as follows:

However, on secondary it shows with an exclamation mark as follows and is not added to the list of DBs on the secondary instance:

I also see this message in the error log:

  Unable to access availability database 'test123' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. Nonqualified transactions are being rolled back in database test123 for an Always On Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required.

I'm using automatic seeding to populate the database on the secondary.

I have added the same DB to a different AG and this issue did not happen and it was added successfully.

The DB is in full recovery model and a full backup has been taken as well and it meets prerequisites. AG and listener looks fine, any ideas what can be the cause of this?

Unable to access availability database 'test123' because the database
  replica is not in the PRIMARY or SECONDARY role. Connections to an
  availability database is permitted only when the database replica is
  in the PRIMARY or SECONDARY role.

You reported that the database was not showing under the databases folder when viewing your secondary instance with SSMS.

If you refreshed and it was still not there, then this means that the database does not exist.
The reason would/could be that something went wrong with automatic seeding.

You should try executing these commands on the secondary instance:

ALTER AVAILABILITY GROUP [agname] JOIN;
GO
ALTER AVAILABILITY GROUP [agname] GRANT CREATE ANY DATABASE;
GO

After this you should retry automatic seeding & validate that the database was created and fully seeded on the secondary instance.

To validate the automatic seeding process , you could use the  sys.dm_hadr_automatic_seeding DMV.

SELECT start_time,
    ag.name,
    db.database_name,
    current_state,
    performed_seeding,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding autos
    JOIN sys.availability_databases_cluster db
        ON autos.ag_db_id = db.group_database_id
    JOIN sys.availability_groups ag
        ON autos.ag_id = ag.group_id;

Source


Is there a "standard way" to log network messages (events) into a database? I have two applications communicating over a LAN via events contained in network messages. Each message (i.e. an UDP packet) contains only one event.
Messages are heterogeneous, meaning that the first "field" (always an integer) is the message type, the second is a timestamp (always an integer). The other fields differ depending on the message type.

I want to log those messages into a database. Messages are totally unrelated and there is no relationship between them (since they are related to different events).
My logger application receives packets and assign them an unique incremental integer.

Basically I'd like to know if there is a standard way to store those packets/events in a database.
The most useful SQL query I'll need is to get all events in the same order I stored them.

So far I come across two possible implementations.

Implementation 1:

For each event type I can create a different table, plus a "main table":

Table Main:  Counter (INTEGER Primary Key), Timestamp (INTEGER), MessageType(INTEGER) // MessageType is optional but useful
Table Msg1:  Counter (INTEGER FOREIGN KEY), Name(STRING), Surname(STRING) ...
Table Msg2:  Counter (INTEGER FOREIGN KEY), FieldFoo(INTEGER), FieldBar(INTEGER)...
...

If I need to get all the events I have to make a JOIN using the tables. Giving the fact I might have 15-20 events I could have a very huge JOIN.

Implementation 2:

Given that 90% of fields are INTEGER, I could create a maxitable:

Table Huge: Counter(INTEGER Primary Key), Timestamp(INTEGER), MessageType(INTEGER), Field1(INTEGER) ... Field5(INTEGER), Field6(STRING), Field7(STRING)...

Basically I'm not giving a role to fields here, and their meaning depends on the field MessageType. Not a very elegant solution but prevents me to make a JOIN using 15-20 tables.
I can still filter events of the same type thanks to the MessageType field.

Efficiency is not a problem in the inserting phase here because event packets are quite rare (and I'm aware of the fact that solution #1 requires two inserts for each event).

From the point of view of my application there isn't a very big difference: both solutions are just fine for my needs since I can get the very same information from both solutions.
Solution 1 seems more elegant (at least it's "normalized") but the idea of making a JOIN using 15-20 tables makes sound a ringbell in my head. I use SQLite by the way, does it have a limit on how
many tables I can join on the same SELECT query?

Is there a standard way to approach this kind of problem (i.e. logging heterogeneous events)? My guess is that this is a common problem and thus there is a standard solution.

It seems that the very same question has been asked (and answered) on stackoveflow:

https://stackoverflow.com/questions/2797592/best-practice-logging-events-general-and-changes-database


Postgresql warning: type reference X converted to Y (SQL State: 00000 - Error Code: 0) When I execute:

CREATE VIEW foreign_keys AS
SELECT
    tc.table_name, kcu.column_name, ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc, information_schema.key_column_usage AS kcu,
    information_schema.constraint_column_usage AS ccu
WHERE
    tc.constraint_type = 'FOREIGN KEY' AND tc.constraint_name = kcu.constraint_name AND
    ccu.constraint_name = tc.constraint_name;

CREATE FUNCTION GetPermissionColumns() RETURNS TABLE(column_name foreign_keys.column_name%TYPE)
    AS $$
BEGIN
    RETURN QUERY SELECT column_name FROM foreign_keys WHERE table_name = tg_table_name AND
        foreign_table_name = 'permission';
END;
$$ LANGUAGE plpgsql;

under Postgresql 9.3 I get the following warning message:

type reference foreign_keys.column_name%TYPE converted to information_schema.sql_identifier (SQL State: 00000 - Error Code: 0)

What am I doing wrong and how can I correct it?

Answering my own question...

It turns out this is harmless INFO-level logging: https://stackoverflow.com/a/3531274/14731


Is it possible to grant trigger permissions on only one schema MySQL I am an analyst that was recently granted a schema on MySQL that I could use for some business needs.

I was told by my DBA that the permissions associated with Triggers require global super user privileges. It's reasonable that I don't have global super user privileges, but would like to have those privileges for a schema that I have ownership on.

Is this something that is possible? If so, how do you do it?

Thank you

This is going to depend greatly on what version of mysql you are on. If you are on 5.1 or higher (specifically later than 5.1.6)
https://stackoverflow.com/questions/19815837/in-mysql-what-privileges-are-required-for-executing-a-trigger

MySQL takes the DEFINER user into account when checking trigger privileges as follows:
At CREATE TRIGGER time, the user who issues the statement must have the TRIGGER privilege. (SUPER prior to MySQL 5.1.6.)
At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges:
The TRIGGER privilege. (SUPER prior to MySQL 5.1.6.)
The SELECT privilege for the subject table if references to table columns occur using OLD.col_name or NEW.col_name in the trigger body.
The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger body.
Whatever other privileges normally are required for the statements executed by the trigger.


SELECT , GROUP AND DISPLAY IN TABULAR FORM I Have a table as above. How can I get an output (as shown below) with a single mysql query.

SELECT location,
       AVG(CASE WHEN qtr = 'Q1' THEN achievement END) q1-average,
       AVG(CASE WHEN qtr = 'Q2' THEN achievement END) q2-average
FROM sourcetable
-- WHERE year = '2018'
GROUP BY location;