Why mongodb requires arbiter in a 4 members replica set? sorry for my trivial question, I read the mongodb documentation but still I do not understand well.
The documentation says that the majority of the members should be considered at the start up configuration of the cluster.
So, if I have 4 members as my replica set, the majority is 3 members.
  After the fail over of one member I still have 3 members up and running and they can proceed correctly to perfom the election, why should I need an arbiter for this scenario??
  I understand perfectly the need of an arbiter in case of only 2 members, in that case the majority is 2 so if one node goes down the other can not elect himself.

In a replica set configuration with an even number of voting members, an arbiter or additional secondary is strongly recommended (but not required) because:

An odd number of voting members avoids potential cases where you could have a tied vote (extending the election process) or fail to elect a primary (if it is possible to have a network split between half your nodes).
An odd number of voting members increases fault tolerance. A five member replica set has the same majority as a four member replica set (3 votes required), but the five member replica set allows for 2 members to be unavailable (versus only 1 node of fault tolerance for a four member replica set).
As you have noted, if you have a replica set with only two members the strict majority of 2 (n/2+1) does not allow for any fault tolerance. Three members is the minimum recommended replica set size if you want to allow for automatic failover and high availability.

MongoDB 3.0+ will log a warning if you have an even number of voting replica set members, but does not prevent you from using this configuration. The log warning will be similar to:

  replSet warning: even number of voting members in replica set config - add an arbiter or set votes to 0 on one of the existing members

As at MongoDB 3.2, replica sets can have a maximum of 7 voting members with up to 50 members in total (see: Replica Set Limits).


Shared logarch across multipe db2 HADR instances Background

We are looking into building a multiple standby hadr cluster. The Auxiliary standby is in a geographically separate location.

from Developer works

  In a multiple standby system, the archived log files can be scattered among
  all databases' (primary and standbys) archive devices. A shared archive
  is preferred because all files are stored in a single location.

and Developer works

  Share archive for databases at the same site is recommended. For remote
  sites, you will need to make a decision based on the network speed,
  reliability, and ease of management

My Question

What method/solution can be used to create a shared archive.

We are running on X86 SLES 11.4 with HDS san.
Primary and Standby is in the same datacentre but Aux-standby is 30km away.
Would a simple NFS share be used and replication to the Aux.

Thank you

We are using a HADR environment with only one standby. Our log files are shared using a simple NFS. That works fine for us, but the two servers are located next to each other.

I assume if a NFS share works with standby 30 km away depends on the network connection (speed and reliability) to the external location.

We are going to locate our primary and standby now in two separate buildings, but they are only a few hundred meters away. For this we will have two SAN systems that are mirrored, one in each building. So the transfer of the data will be handled by the SAN system transparent to the DB2 servers. The two building are connected by fibre channel cables.

We do this for security reasons as it would make no sense to have the SAN system located in building 1 together with the primary while the standy is located in building 2 but has to access the SAN in building 1. If the data center in building 1 is going down, the standby in building 2 will be of no use without SAN access.

How you should setup your environment highly depends on your requirements and goals. Do you want to accelerate the your database access or do you want to increase the security?


TEXT data migrate from Sybase to SQL Server I'm working on migrating Sybase ASE 15.7 SP134 database to SQL Server 2008R2, using SSMA for Sybase 2014.

I'm facing an issue where a table in SybaseDB has 2 columns with the data type TEXT. The SQL Server table with datatype "text" is mismatching on Japanese data characters.

Questions are:

How can we keep same text as Sybase while migrating data?
How to keep Non-ASCII characters as it is during migration?

Please comment if more information can be provided.

Stop using text data types -- they're long deprecated. If you need to, use NTEXT instead to preserve Unicode characters. But that's not a great idea.
Do the smart thing and use NVARCHAR(MAX) instead, because that's not deprecated, and will handle your Unicode long text data just fine.


How to fix this error Idk? how to fix it I am new in Oracle?

I tried to connect RMAN and it showed me this error...

please check your alert.log for your database instance
(your database instance must running).

if you use catalog database, it must be open (the target database does not need to be open).


Create View in specified database with dynamic sql? I am writting a dynamic sql to drop and create view in different database.

So I wrote:

set @CreateViewStatement =
                '
                USE ['+ @DB +'];
                CREATE VIEW [dbo].[MyTable]
                AS

                SELECT ........something
exec (@CreateViewStatement)

It gives me error:

  'CREATE VIEW' must be the first statement in a query batch.

If I remove the USE DATABASE statement it works fine, but then the database is not specify anymore....

How can I solve this problem?

You can use nested EXEC calls. The database context changed by the USE persists to the child batch.

DECLARE @DB SYSNAME

SET @DB = 'tempdb'

DECLARE @CreateViewStatement NVARCHAR(MAX)
SET @CreateViewStatement = '
      USE '+ QUOTENAME(@DB) +';
      EXEC(''
             CREATE VIEW [dbo].[MyTable] AS
             SELECT 1 AS [Foo]
      '')

                          '
EXEC (@CreateViewStatement)


Select data from a table to insert/update another similar table I have two tables (in two databases):
create table catalog_product_entity_text
(
    value_id int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id smallint unsigned default 0 not null comment 'Store ID',
    entity_id int unsigned default 0 not null comment 'Entity ID',
    value longtext null comment 'Value',
    constraint CATALOG_PRODUCT_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
        unique (entity_id, attribute_id, store_id),
    constraint CATALOG_PRODUCT_ENTITY_TEXT_STORE_ID_STORE_STORE_ID
        foreign key (store_id) references store (store_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_TEXT_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references eav_attribute (attribute_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_TEXT_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references catalog_product_entity (entity_id)
            on delete cascade
)

create table catalog_product_entity_varchar
(
    value_id int auto_increment comment 'Value ID'
        primary key,
    attribute_id smallint unsigned default 0 not null comment 'Attribute ID',
    store_id smallint unsigned default 0 not null comment 'Store ID',
    entity_id int unsigned default 0 not null comment 'Entity ID',
    value varchar(255) null comment 'Value',
    constraint CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID
        unique (entity_id, attribute_id, store_id),
    constraint CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID_STORE_STORE_ID
        foreign key (store_id) references store (store_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references eav_attribute (attribute_id)
            on delete cascade,
    constraint CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references catalog_product_entity (entity_id)
            on delete cascade
)

My _text table contains some data that I would like to transfer to the _varchar table, but there's no correlation with the primary keys. The primary key is just auto increment.
Logically the tables are connected by the set (attribute_id, store_id, entity_id).
I would like to perform two actions:

Select all the values from _text table for specific attribute ids, e.g.
select cpet.attribute_id, cpet.store_id, cpet.entity_id, cpet.value
from catalog_product_entity_text cpet
where cpet.attribute_id in
      (<myids>)
  and cpet.value is not null;

Now for each row in the above results I'd like to:

If there's corresponding (attribute_id, store_id, entity_id) in the _varchar table, update that row.

If there's no corresponding (attribute_id, store_id, entity_id) in the _varchar table, add a new row with the data from the source row.

If I had a common primary key, I'd use REPLACE INTO, but in this case I don't think it's suitable.
How can I build such a query? It doesn't need to be a single query.

Here's how to update the records that exist in catalog_product_entity_varchar and relate to a record in catalog_product_entity_text:
UPDATE catalog_product_entity_varchar cpev
INNER JOIN catalog_product_entity_text cpet
    ON cpet.attribute_id = cpev.attribute_id
    AND cpet.store_id = cpev.store_id
    AND cpet.entity_id = cpev.entity_id
SET cpev.value = cpet.value
WHERE cpet.attribute_id IN (<myids>)
  AND cpet.value IS NOT NULL;

After you do the update, then you can insert what's missing like this:
INSERT INTO catalog_product_entity_varchar (attribute_id, store_id, entity_id, value)
SELECT cpet.attribute_id, cpet.store_id, cpet.entity_id, cpet.value
FROM catalog_product_entity_text cpet
LEFT JOIN catalog_product_entity_varchar cpev
    ON cpet.attribute_id = cpev.attribute_id
    AND cpet.store_id = cpev.store_id
    AND cpet.entity_id = cpev.entity_id
WHERE cpet.attribute_id IN (<myids>)
  AND cpet.value IS NOT NULL
  AND cpev.attribute_id IS NULL; -- This last predicate ensures we only insert new records (i.e. the join didn't match any records in cpev to this record in cpet, if cpev.attribute_id is null here)


Splitting the table to get more clusterization options - will it help? Complete theory.

Let's assume we have a table with a few millions rows. The table has clustered index over PK, and it is important for quick fetching of particular rows. However, the table also has a column kind of CreatedDateTime (non clustered index), and by reasons of report building over particular time span, obviously, it would be great to cluster it too.

Will it help, if I split the table in two - TMain with clustered PK, and TMainTime, with (MainId) as non clustered PK, and (CreatedDateTime) for clustered index? It looks like I can determine the timespan required using TMainTime, searching over its clustered datetime, and then, with MainId values found, fetch them searching over clustered PK in TMain table. Will it ever add something to performance? Or will it eliminate any possible gain by JOIN operation over the two tables?

Since this is a theoretical question I'm sure there will be corner cases where that is a good idea but as a general rule I wouldn't go down that path.

You will, as you state, complicate your regular queries by having to join both tables and you will also complicate your inserts/updates.

A well designed, properly normalized, database with proper indexing on a server that has been sized well should perform adequate with a couple of million rows (and a lot more than that).

For reference, the stack exchange network runs on a mainstream RDBMS and uses no special design tricks. You can verify that for yourself by downloading the database.


Are AD Groups the exact same as individual windows logins with explicit permissions? Does any application requires a windows/domain login to be explicitly added to a SQL Server instance or by replacing the login with an AD group that belongs to the server and has elevated permissions I would achieve the same thing?

To clarify, I am referring specifically to application accounts (In this case we have an account for TFS (team foundation services)) and not actual "users" accounts.  Will any application fail if they can't find an explicit windows/domain user on the server?

Microsoft's doc about Connecting Through Windows Authentication says:

  By using Windows Authentication, Windows groups can be created at the
  domain level, and a login can be created on SQL Server for the entire
  group. Managing access from at the domain level can simplify account
  administration.

There are no remarks about restrictions for apps when using a group instead of a login. Also, John F. Tamburo' article says:

  The group membership is used as a path to access; all activity is
  logged against the actual user's account.  sp_who2 and every
  auditing tool will show logins based on the actual end user; and
  DDL/DML changes will be audited to that user.  No more guessing who
  used the application's SQL login - It's gone!  For applications that
  run as Windows services, the service account under which the
  application runs is made a member of the necessary groups. (emphasis added)

So, as long as the group mapped as the SQL Server login keeps the needed privileges for the applications, no problem would be found on that change.


Oracle 12c Installation in Windows 7 error INS-30131 I am trying to install Oracle 12c in a Windows 7 x64 computer and after a while I get this error:

SEVERE: [FATAL] [INS-30131] Initial setup required for the execution of installer validations failed.
   CAUSE: Failed to access the temporary location.
   ACTION: Ensure that the current user has required permissions to access the temporary location.
*ADDITIONAL INFORMATION:*
- Framework setup check failed on all the nodes
- Cause: Cause Of Problem Not Available
- Action: User Action Not Available
Summary of the failed nodes
maddy-pc
- Version of exectask could not be retrieved from node "XXXXXX"
- Cause: Cause Of Problem Not Available
- Action: User Action Not Available

Does anybody have an idea how to solve this problem?

I found the problem. For reasons I don't understand Oracle needs access to the file system through the administrative network share (i.e. \computer\c$) and I had disabled it.

Things to check to solve this issue:

Remove your computer from workgroups.
Set HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System DWORD value LocalAccountTokenFilterPolicy to 1
Set HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\LanmanServer\Parameters DWORD value AutoShareWks to 1


Design Tagging module in the database I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By tagging I mean that we wish to allow the user from the front end to define tags for these entities (keywords), something like Facebook tagging. We would like to make it extensible for any future entity that may appear later in the system and we want it to be taggable too. Initially we thought that this could be a many to many relation ship between each entity that we want it to be taggable and each tagged entity in the database. For example, if we want to tag users in photos, then many users can be tagged in a single photo and many photos may have the same user tagged into them. But this design complicate things because we will need many to many relationship between all the taggable entities and the objects that we want to add tags to. So we will have a many to many relationship between user and photos, user and video, .... and this is only for the user entity, if we have other entity we will do the same, so it's complex and non extensible. So we tried to enhance that and we came with the following idea.

We will have a table with no relationships at all, it's not connected to any other table, this table will contain the following, the tag id, tag value, the entity object id (ex: user id), the entity object type (ex: user), the taggable object id (ex: photo id), the taggable object type (ex: photo). and we will add indexes for entity object id column and entity object type, also for taggable object id and taggable object type to fasten the search process.

But I was wondering if there any pattern for that, some design pattern that solves this issue, and which of the above 2 solutions is better.

What you describe in your second paragraph sounds like the Entity Attribute Value pattern.

This is a known pattern for dealing with the type of data you are talking about. When you need to be able to be extensible, drive the types from the data and allow you to start tracking new types as you grow. It allows you stay flexible and agile and be a little more forgiving and generic in your database design and schema.

This pattern works, even in relational databases, but it does take more work to get good performance, and can cause you headaches down the line depending on how many rows, how many inserts you are doing and how frequently you query it. This SO Question talks about some of the pitfalls nicely in the firs answer listed with 20 votes. I have seen this model work well, especially in environments where the new types and entities really do come in and we want to let that be more application driven or user driven but we were fighting with performance more often in that model than a traditional relational database model but we needed the flexibility and we made it work. Definitely look at that SO question and do some more research on EAV and whatever DBMS you are using to find examples from others who have tried the same.


psql: how to turn off context messages at raise notice If I use raise notice, I get context messages at each execution.  Can I turn them
off and just get the NOTICE?

NOTICE:  d = [2,1] ,tn: (1,2,"{NULL,NULL}","{{-1,4},{-1,-1}}",1)
CONTEXT:  SQL statement "SELECT aPt( vl , tn_id, color )  from tnodes where tnode_id = tn_id"
PL/pgSQL function "apt" line 42 at PERFORM
SQL statement "select  aPt(vl, tn.chldrn[d[1]][d[2]], color) from tnodes where tnode_id = tn_id"
PL/pgSQL function "apt" line 46 at SQL statement
SQL statement "SELECT aPt('{0.5,1}',itVar,1)"
PL/pgSQL function "bi" line 16 at PERFORM

I'm using postgres 9.1.14,
Thanks.

The solution :
As write – dezso Feb 17 '15 at 14:56, using \set VERBOSITYE 'terse' is the right way.
I've to add this option in my scripts using psql :
psql --dbname=DB_production --VERBOSITY=terse

But, I can't understand why on others instances, servers, environments, I haven't to do this.


Is there a way to get an event when there's a change in a table? Using Azure Sql Server, and in C# if that's relevant, is there a way to get an event when data in specific table changes?

In one table we need an event if any column changes in a row.

In a 2nd table we need an event only when either of 2 columns change (although any change would be ok).

And we need to know which row.

Is it possible to do this? If so, how?

Update: We need a call into our C# code for this event. A database trigger where the database can do something doesn't help, our program needs to take action on a change.

thanks - dave

Windows Azure SQL Database (WASD) does partially support triggers so you could roll your own auditing.  See here.  Here's a really simple example:

CREATE TABLE dbo.Users (
    userId INT IDENTITY PRIMARY KEY,
    userName VARCHAR(30) NOT NULL UNIQUE,
    dateAdded DATETIME NOT NULL DEFAULT GETDATE(),
    addedBy VARCHAR(30) NOT NULL DEFAULT 'admin',
    dateModified DATETIME NULL,
    modifiedBy VARCHAR(30) NULL,
)
GO

INSERT INTO dbo.Users ( userName )
VALUES ( 'wBob' ), ( 'David' )
GO

CREATE TRIGGER dbo.utrg_Users ON dbo.Users
FOR UPDATE
AS
BEGIN

    -- INSERT INTO ... other table
    SELECT 'old record' source, *
    FROM deleted

END
GO

UPDATE dbo.Users
SET userName = 'wDavid'
WHERE userName = 'David'

WASD has also recently previewed an Audit feature which does include DML auditing.  It's only available for the Basic, Standard and Premium editions and writes off to an audit log stored on an Azure storage account (not a conventional relational table) so you could also have a look at that.  Looks like an interesting feature and includes an Excel template for quickly analysing the logs.  Start here.


Oracle 11g production database copy to USB drive What is the correct commands for backing up a production Oracle 11g database (while still running) to an external USB drive or different directory.

I am new to Oracle and but have experience with SQL Server.  Essentially, I am looking for the equivalent of a COPY_ONLY backup - something which will not interfere with the existing backup process.

The goal is to take the backup offsite and use it to develop migration scripts for a new application.  Any insights would be appreciated.

To get a consistent single-file snapshot of a running Oracle instance DataPump Export can be used.

You need to have access to the server's filesystem (or a shared filesystem) in order to copy the resulting dump file.

The process would be to

run expdp (for one or multiple schemas)
copy the dump file to the USB stick
run impdp to import the dump into the development database


Restore dropped mysql database I create a database each year from the previous year, back up and then clean up the tables. So I have databases for the past 3 years.

This year I did not do a backup and accidentally dropped the database of 2011.  The schema is always the same for all the databases, but of course the entries are different for each year.

After dropping the database of 2011, the folder no longer exists in C:\Program Files\MySQL\MySQL Server 5.0\data, but I see ib_logfile0 and ibdata1 files under the above path.  Is there any way I can restore the dropped database?

There was no backup done for 2011 and I do not know what is binary logging.

You can try and use data recovery software to recover the blocks as odds are the blocks haven't been overwritten yet.  If they have been overwritten, then the data is gone.

I'm sorry to say, but you've just learned why backups are important.


Updating mysql trigger via information_schema -- access denied? I have some wrong "definer"s for some triggers in MySQL (actually MariaDB). I figured the easiest way of correcting them would be some version of:

use information_schema;
update triggers set definer=current_user() where trigger_schema='foobar';

It produces:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

In the same session, I can drop and re-create the triggers with "drop trigger" and "create trigger", so I'm not understanding the permissions issue. Is there some privilege that my user needs that it doesn't have?

What you are doing is impossible because the INFORMATION_SCHEMA database is a database made up of in-memory read-only temporary tables (See my post How is INFORMATION_SCHEMA implemented in MySQL?)

You are going to have to drop the triggers and recreate them

I wrote a post on that before (Oct 02, 2011 : Can mysqldump dump triggers and procedures?)

Here is what you can do for all triggers in the foo database

STEP 01 : mysqldump the triggers

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump -${MYSQL_CONN} -d -t --skip-routines --triggers foo > foo_make_triggers.sql

For clarification

-d is the same as --no-data
-t is the same as --no-create-info

STEP 02 : Create a script to drop all triggers

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('DROP TRIGGER ',trigger_name,';')"
SQL="${SQL} FROM information_schema.triggers"
SQL="${SQL} WHERE trigger_schema = 'foo'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > foo_drop_triggers.sql

STEP 03 : Verify Contents of Each File

vi -R foo_drop_triggers.sql
vi -R foo_make_triggers.sql

STEP 04 : Edit all the definers in foo_make_triggers.sql as you know how

STEP 05 : Login to mysql and run this

use foo
source foo_drop_triggers.sql
source foo_make_triggers.sql

Test this on a staging server please

GIVE IT A TRY !!!


Help required with query (join) optimization I am working with a fact table source query and I have observed that performance of the query is pathetic. It has increased from 1:00 min to 6:30 min by just using a function in the select clause which converts the date format. It only has 7 tables joined on simple On condition (No crazy stuff).

Going forward I need to add couple of more tables to the join list. This will only make the performance way worse. I need to fine tune the current query before I starting adding to it.

Here is the query:

 SELECT [dbo].[dFK](oew.StartDate) AS StartDate, -- INTEGER DATE!
    [dbo].[dFK](oew.EndDate) AS EndDate,
    [dbo].[dFK](oew.EffectiveDate) AS EffectiveDate
FROM    OpenEnrollmentWindow oew
    INNER JOIN ProductYear py ON oew.OrganizationProductYearID = py.ID
    INNER JOIN Marketplace m ON py.MarketplaceID = m.ID
    INNER JOIN Organization o ON m.OrganizationID = o.ID
    INNER JOIN Consumer c ON c.OrganizationID = o.ID
    LEFT JOIN OpenEnrollmentWindowProduct oewp ON oew.ID = oewp.OrganizationOpenEnrollmentWindowID
    LEFT JOIN OpenEnrollmentWindowProductType oewpt ON oew.ID = oewpt.OrganizationOpenEnrollmentWindowID

Here is the definition of the function:

CREATE FUNCTION [dbo].[dFK]
(@dt as sql_variant)
RETURNS int
AS
BEGIN
    DECLARE @type varchar(128)
    DECLARE @iDate int
    SET @type = CONVERT(varchar(128), SQL_VARIANT_PROPERTY(@dt, 'BaseType'))
    SET @iDate =
        CASE
            WHEN @type = 'int' AND @dt >= 19000101 AND @dt <= 20451231 THEN CONVERT(int, @dt)
            WHEN @type = 'int' AND @dt < 19000101 OR @type = 'int' AND @dt > 20451231 THEN 1
            WHEN @dt IS NULL THEN 1
            WHEN (@dt < CAST('1900-01-01 00:00:00.000' AS DATETIME) OR @dt > CAST('2045-12-31 11:59:59.000' AS DATETIME)) AND @type = 'datetime' THEN 1
            WHEN (@dt < CAST('1900-01-01' AS DATE) OR @dt > CAST('2045-12-31' AS DATE)) AND @type = 'date' THEN 1
            ELSE FORMAT(CAST(@dt AS DATETIME2), 'yyyyMMdd')
        END
    RETURN @iDate
END

GO

This is used as a fact table source. The date is converted to avoid a reverse lookup against date dimension. Let's just say it has to be converted at server side only. It is spitting out some 6 million rows. Now I do understand that's quite a lot, and that's why I am seeking some query optimization suggestions here.

The problem is the use of scalar functions. These are executed once per reference per row, and the current internal implementation is such that this is very nearly as expensive as running a separate query per invocation (18 million times, for 6 million rows with three function references per row).

A quick solution is to convert the function to an in-line table-valued function. These are in-lined into the query text, much in the same way views are expanded before query optimization. So the first step is to translate the function to:

CREATE FUNCTION dbo.dFK_InLine
    (@dt as sql_variant)
RETURNS table
AS
RETURN
    SELECT
        ReturnValue =
        CASE
            WHEN CA.datatype = 'int'
                AND @dt >= 19000101 AND @dt <= 20451231
                THEN CONVERT(integer, @dt)
            WHEN (CA.datatype = 'int' AND @dt < 19000101)
                OR (CA.datatype = 'int' AND @dt > 20451231)
                THEN 1
            WHEN @dt IS NULL
            THEN 1
            WHEN (@dt < CAST('1900-01-01 00:00:00.000' AS DATETIME)
                OR @dt > CAST('2045-12-31 11:59:59.000' AS DATETIME))
                AND CA.datatype = 'datetime'
            THEN 1
            WHEN (@dt < CAST('1900-01-01' AS DATE)
                OR @dt > CAST('2045-12-31' AS DATE))
                AND CA.datatype = 'date'
            THEN 1
            ELSE FORMAT(CAST(@dt AS DATETIME2), 'yyyyMMdd')
        END
    FROM
    (
        VALUES
        (
            CONVERT(varchar(128), SQL_VARIANT_PROPERTY(@dt, 'BaseType'))
        )
    ) AS CA (datatype);

Then modify the source query to use it:

SELECT
    SD.ReturnValue AS StartDate, -- INTEGER DATE!
    ED.ReturnValue AS EndDate,
    EFD.ReturnValue AS EffectiveDate
FROM OpenEnrollmentWindow oew
CROSS APPLY dbo.dFK_InLine(oew.StartDate) AS SD
CROSS APPLY dbo.dFK_InLine(oew.EndDate) AS ED
CROSS APPLY dbo.dFK_InLine(oew.EffectiveDate) AS EFD
INNER JOIN ProductYear py
    ON oew.OrganizationProductYearID = py.ID
INNER JOIN Marketplace m
    ON py.MarketplaceID = m.ID
INNER JOIN Organization o
    ON m.OrganizationID = o.ID
INNER JOIN Consumer c
    ON c.OrganizationID = o.ID
LEFT JOIN OpenEnrollmentWindowProduct oewp
    ON oew.ID = oewp.OrganizationOpenEnrollmentWindowID
LEFT JOIN OpenEnrollmentWindowProductType oewpt
    ON oew.ID = oewpt.OrganizationOpenEnrollmentWindowID;

All that said, this is still quite an...unusual strategy, particularly the use of sql_variant and the CASE logic. You might get better value from refactoring the design to use a strong types, and a more traditional model.


Low-level relational operators from SQL I have been asked to outline the sequence of commands using low-level operators (join/project/select) that are used in the following SQL statement;

SELECT d.name, k.city
FROM DOG d, KENNEL k
WHERE d.kennel_name = "Holiday Kennels"
AND d.kennel_name = k.name;

My understanding would be you would project d.name and k.city like this;

PROJECT d.name, k.city

However beyond this I am unsure how you would use join and select to get the clauses?

Any help would be great, thanks.

I assume you're talking about Relational Algebra here.

A SELECT operation returns whole tuples (rows) based on some predicate.  The predicate is defined in the SQL statement by the WHERE clause.

The PROJECT algebra operation is where you select a subset of attributes from each tuple.  The PROJECT operation is defined in the SQL statement by the columns you specify.  In SQL, the SELECT and PROJECT operations are combined into the SELECT clause.

A JOIN operation is used to combine two sets of tuples by a common and related attribute.  In this example, you are using an INNER JOIN defined in the FROM and WHERE clause.  I generally prefer to declare my joins solely in the FROM clause, but it works either way.

So, the SELECT operation is:

WHERE d.kennel_name = "Holiday Kennels"

The PROJECT operation is:

SELECT d.name, k.city

And the JOIN operation is:

FROM DOG d, KENNEL k
...
AND d.kennel_name = k.name;


SSIS OLEDB Command not firing triggers Got a bit of a strange one that I can't seem to work out.

We have a vendor database ( so we have no control over the table structure, foreign keys, etc...) that's on SQL Server 2012 SP2. We have a large amount of data that's getting loaded daily via an SSIS package.

The SSIS package is inserting rows using an OLEDB Command that is simply executing a stored procedure

EXEC StoredProc @Param1, @Param2, @Param3, @Param4, @Param5

The simplified version of what this procedure does is as follows

 INSERT INTO TableA (Col1, Col2)
 VALUES(@Param1, @Param2)
 SET @IdentA = SCOPE_IDENTITY()

 INSERT INTO TableB (Col3, Col4)
 VALUES(@IdentA, @Param3)
 SET @IdentB = SCOPE_IDENTITY()

 INSERT INTO TableC (Col5, Col6)
 VALUES(@IdentB, @Param4)
 SET @IdentC = SCOPE_IDENTITY()

 INSERT INTO TableD (Col7, Col8, Col9, Col10)
 VALUES(@IdentA,@IdentB,@IdentC,@Param5)
 SET @IdentD = SCOPE_IDENTITY()

 INSERT INTO TableE (Col11, Col12, Col13, Col14, Col15)
 VALUES(@IdentA,@IdentC,@IdentD,@Param3,@Param5)

We have triggers on each of the tables involved to transfer newly inserted data across to another system.

What appears to be happening is that the triggers do not fire when the procedure is run from SSIS. Everything works as expected when running the procedure from SQL Management Studio.

Anyone got any thoughts on why this could be occurring, and how we can resolve the issue.

Cheers
Phil

I was not able to reproduce your error (although I only tested with one table in the mix).

An OLE DB Destination has the ability to disable trigger and other check constraints when it loads but the OLE DB Command does not provide a similar facility.

Set up

I created a simplistic table: a key, a value and an empty field that will be populated, via trigger, when the row is updated. I created a trigger that will update the empty field for the modified row. Finally, I created a stored procedure to handle actually performing the UPDATE statement.

CREATE TABLE dbo.dba_128344
(
    Foo int NOT NULL
,   Bar char(1) NOT NULL
,   Blee datetime NULL
);
GO
CREATE TRIGGER tr_dba_128344_update
ON dbo.dba_128344
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE
        D
    SET
        Blee = CURRENT_TIMESTAMP
    FROM
        Inserted AS I
        INNER JOIN
            dbo.dba_128344 AS D
            ON D.Foo = I.Foo;
END
GO
CREATE PROCEDURE dbo.dba_128344Update
(
    @Foo int
,   @bar char(1)
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE
        DBA
    SET
        DBA.Bar = @bar
    FROM
        dbo.dba_128344 AS DBA
    WHERE
        DBA.Foo = @Foo;

    -- Induce a 1 second delay
    -- Allows us to ensure we aren't cheating
    WAITFOR DELAY '00:00:01';
END
GO

My package is simplistic. A data flow with an OLE DB Source and an OLE DB Command.

My OLE DB Source uses the following query which just calculates the next value of Bar in the ASCII table and also brings in the original value. A will become B, B becomes C, C becomes D.

SELECT
    D.Foo
,   D.Bar AS OldBar
,   CHAR(ASCII(D.Bar) + 1) AS NewBar
FROM
    dbo.dba_128344 AS D;

The OLE DB Command uses the following statement.

EXECUTE dbo.dba_128344Update ?, ?;

OLE DB Connection Managers require the use of the ? as an zero ordinal based replacement system. Here I mapped column Foo to the parameter @Foo and NewBar to the parameter @bar because I'd hate to be consistent in my casing...

After execution, as expected I saw my dates populated with ~ 1 second delay between each row to commensurate with the row-by-row nature of the OLE DB Command.

Foo Bar Blee
10  B   2016-02-04 22:53:18.043
20  C   2016-02-04 22:53:19.050
30  D   2016-02-04 22:53:20.053

Biml

If you'd like to confirm you're seeing the same behaviour on your system, you're in luck. Biml, the Business Intelligence Markup Language, is an XML dialect that can be fed into a translator to generate SSIS packages. It's pretty slick. All you need to do is download and install the free add-in BIDS Helper

Once installed, right click on an SSIS project and select Add new Biml File
Double click BimlScript.biml and paste the following content
1, Adjust line 3 to make that point to a valid server and database where you ran the above DDL
Save and then right click on BimlScript.biml and select "Generate SSIS Packages". Whoosh Out comes dba_128344.dtsx

GIVE IT A TRY!!!

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>
    <Packages>
        <Package Name="dba_128344">
            <Tasks>
                <Dataflow Name="DFT Demo Trigger">
                    <Transformations>
                        <OleDbSource
                            ConnectionName="CM_OLE"
                            Name="OLESRC Demo Data">
                            <DirectInput><![CDATA[-- SourceQuery
SELECT
    D.Foo
,   D.Bar AS OldBar
,   CHAR(ASCII(D.Bar) + 1) AS NewBar
FROM
    dbo.dba_128344 AS D;
]]></DirectInput>
                        </OleDbSource>

                        <OleDbCommand
                            ConnectionName="CM_OLE"
                            Name="Trigger Tester">
                            <DirectInput><![CDATA[EXECUTE dbo.dba_128344Update ?, ?;]]></DirectInput>
                            <Parameters>
                                <Parameter SourceColumn="Foo" TargetColumn="@Foo" DataType="Int32" />
                                <Parameter SourceColumn="NewBar" TargetColumn="@bar" DataType="AnsiString" Length="1"/>
                            </Parameters>
                        </OleDbCommand>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>


Unexpected empty result select a,b from table1
where a not in (select x as a from table2)
AND b not in (select y as b from table3)

The above query gives an empty result. Why? And how should I construct this combined query?

whereas:

-- gives a=5,b=6 & a=7,b=8
select a,b from table1
where a not in (select x as a from table2)

-- gives a=1,b=2 & a=5,b=6
select a,b from table1
where b not in (select y as b from table3)

Individually, the queries work; but together they give no result although I think they match row2 from table1

table1     table2     table3
a   b      x    y      x   y
1   2      1    0      3   4
5   6      0    0      0   0
7   8      9    5      0   8

I want result of query as:

   a  b
   5  6

where a is not x in table2 and simultaneously b is not y in table3.

SORRY yes aasim.abdullah
the query does work and so does this one

use the following query to get desired result.

select a,b from (select a,b from table1 where a not in (select x as a from table2))temptable where b not in (select y as b from table3)

So my new question is which query first or second has speed performance advantage & whysince my actual table is seriously huge.I am using MYSQL

Your query is correct and it should return one row (5,6)

DECLARE @table1 TABLE ( a INT, b INT )
DECLARE @table2 TABLE ( x INT, y INT )
DECLARE @table3 TABLE ( x INT, y INT )

INSERT  INTO @table1
        ( a, b )
VALUES  ( 1, 2 ),
        ( 5, 6 ),
        ( 7, 8 )

INSERT  INTO @table2
        ( x, y )
VALUES  ( 1, 0 ),
        ( 0, 0 ),
        ( 9, 5 )

INSERT  INTO @table3
        ( x, y )
VALUES  ( 3, 4 ),
        ( 0, 0 ),
        ( 0, 8 )

SELECT  a, b
FROM    @table1
WHERE   a NOT IN ( SELECT   x AS a
                   FROM     @table2 )
        AND b NOT IN ( SELECT   y AS b
                       FROM     @table3 )


Data Warehouse how normalized for tracking metadata changes? I need to build out a warehouse structure that is populated by periodic feeds consisting of a mix of scraped data, authority sources, and primary sources. In order to prioritize which data should be used for a data mart, I need to keep track of each data point's creation (when did we get this data?), update (when did the data change?), and existing match (when was the last time this data showed up?). From my research it looks like 5NF is the level of normalization I should use.

For example, I may get a feed which gives Student Names along a list of their favorite classes and rank order. Another feed may come in with the same data, but might have an incomplete list of classes, or lack rank order. Every feed would come in with a unique and reliable student identifier

I would was thinking there would be at least three tables for just that data:

Student (s_id, name)
Student_Class (s_id,class_id)
Student_Class_Order (s_id,class_id,order_num)

Each of those would also have columns for isDeleted, created_time, created_feed_id, modified_time, modified_feed_id, matched_time, matched_feed_id.

What kind of structure would make the most sense? It seems like any new combination of datapoint should be inserted, and I should not have updates (except for a soft-delete flag). Any ideas?

The short answer is: define your end result ( requirement) and work backwards from there.

Normalization goes out the window pretty quick with a DW schema.

In my case we have 3 schemas for our ETL.

Staging: Tables are extracted into our DW exactly as they appear in the source system plus a few columns for meta data as you describe. The key one being the extract time.
Transform: These tables have exactly the same schema as the end result dimensions and fact tables. At this point we are denormalising data from an OLTP schema into a DW star schema. Again it has fields for ETL meta data. The original extract datetime can travel with the records.
Store: This our end result, a DW star schema which is read by our OLAP cube. To Load our data we compare the transform with the final datastore tables. New rows get inserted. If a row has changed we retire the old and insert the new. To do this we have fields for is_current,  valid_from datetime and valid_to datetime.

Technically we dont need 3 schemas. It's just a convention, and it allows us to keep things tidy.

Read about Kimball methodology and Slowly Changing Dimensions. Type 1 or 2 etc. You will see examples of what fields you will need to store the meta data required.

Talk to your business about what they want or need the DW to be capable of. Whether you need SCD's or not will have a significant impact on your schema, meta data required and ultimately what level of normalisation that is possible.


How to select rows that date is in range from ranges table I have a table1 that hold ranges

rangeId, rangeName, fromDate, toDate,cat

another table2 has rows

rowTitle,rowDate

I need to

select  all rows
from table2
that table2.rowDate is between table1.fromDate
                           and table1.toDate
and cat = 1

Something like :

select t2.*
from table2 t2
join table1 t1 on t2.rowdate between t1.fromDate and t1.toDate
where t1.cat = 1


Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks? There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, varchar and text). The total index_length is 2.2GB and the data_length is 6GB. All these tables use MyISAM and they have a read/write ratio of 66/33.

Here's the output from /etc/init.d/mysql status during idle time:

Server version          5.1.37-1ubuntu5
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
...

Threads: 27  Questions: 5430994  Slow queries: 59  Opens: 298  Flush tables: 1  Open tables: 128  Queries per second avg: 182.695

The number of queries per second is at about 300 during peak time.

In the last couple of weeks, the database has become so slow that sometimes queries are locking for 2+ minutes. So, I increased key_buffer_size to 4GB (I have 8GB on the machine). The lock times have decreased. Yet, it's still unbearable during peak hours, especially when the reads/writes ratio approaches 50/50.

The server is running on a virtual environment so I/O is not great and most of the select queries do a ORDER BY <some_indexed_field> on results sets with an average of 50k rows.

I've successfully tuned this database before when it was still smaller but now I'm stuck.

Update:
Disk utilization easily reaches 100% during the day.

You have to use InnoDB. Here is why :

The major advantages of InnoDB over MyISAM

InnoDB caches data and index pages, whereas MyISAM only caches index pages.
InnoDB is designed for ACID-compliant transactions
InnoDB is designed for row-level locking, MyISAM uses table-level locking.
InnoDB is designed for Multiversion Concurrency Control, critical for transaction isolation
InnoDB can be tuned to engage multiple cores

MyISAM is great in a read-heavy environment. Although you can configre private caches on a per-table basis, MyISAM never caches data.

InnoDB is your best bet !!!

CAVEAT

Get enough RAM on the DB Server to cache everything in the InnoDB Buffer Pool (75% of Installed RAM).


Finding all functions/stored procedures that reference a certain procedure/function? I have been given the task to move reports from SSRS to our internal ASP.Net application and this has involved me re-writing a few functions and a stored procedure to both improve performance, make them output in forms better consumed by the application and make any requested additions to the reports.

What I also need to do is remove the old functions and procedures from the system as I go, before I do this though I want to make sure it will not break anything else. Is there a way I can go though all the precedures and functions to see if any of them contain references to the ones I will be removing?

There are thousands of these to go though and I would rather not have to open each one at a time in SSMS to look. So if there is some table I can query or even if there is a .sql file that SQL server is hiding that I could dig though, that would be great.

sys.sql_expression_dependencies should give you what you want if you don't want to search through the definition of every single object (especially when references may or may not have schema prefixes).

I think you can get what you want with the following query:

SELECT
    o.name AS ReferencingObject,
    sd.referenced_entity_name AS ReferencedObject
FROM sys.sql_expression_dependencies  AS sd
INNER JOIN sys.objects AS o
    ON o.object_id = sd.referencing_id
WHERE sd.referenced_entity_name = 'my_object_name';


MySQL trigger before insert replace the data with relational table I have 2 table orders and transactions

I want to write trigger in orders table, which will replace the value of currently inserting row (orders table INSERT query) with transactions data and also update a flag of transactions table.

MySQL query for insert is

INSERT INTO `cl201_orders`(`order_id`, `transaction_id`, `transaction_ammount`, `gateway_id`, `name`, `address`)  VALUES ('a23', NULL, NULL, NULL, 'John', 'NZ');

MySQL query for transactions data selection will be

SELECT `transaction_id`, `transaction_ammount`, `gateway_id`
FROM `transactions`
WHERE `order_id` = 'a23'
  AND gateway_id IS NOT NULL
  AND active = 1
ORDER BY `transaction_id` ASC
LIMIT 1;

Before order Insert

transactions table

transaction_id | order_id | transaction_ammount | gateway_id | active
----------------------------------------------------------------------
123            | a23      |  10                 | NULL       | 1
252            | a23      |  33                 | 23         | 1
321            | a23      |  25                 | NULL       | 1
431            | 1cd      |  50                 | 45         | 0

orders table

order_id | transaction_id | transaction_ammount | gateway_id | name | address
-----------------------------------------------------------------------------
1cd      | 431            | 50                  | 45         | Roy  | IN

After order Insert

transactions table

transaction_id | order_id | transaction_ammount | gateway_id | active
----------------------------------------------------------------------
123            | a23      |  10                 | NULL       | 0
252            | a23      |  33                 | 23         | 0
321            | a23      |  25                 | NULL       | 0
431            | 1cd      |  50                 | 45         | 0

orders table

order_id | transaction_id | transaction_ammount | gateway_id | name | address
-----------------------------------------------------------------------------
1cd      | 431            | 50                  | 45         | Roy  | IN
a23      | 252            | 33                  | 23         | John | NZ

Table structure:

CREATE TABLE `prefix_orders` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `order_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `customer_email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `transaction_ammount` decimal(10,2) NOT NULL DEFAULT '0.00',
    `transaction_id` int(11) unsigned DEFAULT NULL,
    `gateway_id` int(11) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `order_id` (`order_id`),
    KEY `gateway_id` (`gateway_id`),
    KEY `transaction_ammount` (`transaction_ammount`),
    KEY `transaction_id` (`transaction_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `prefix_transactions` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `transaction_id` int(11) DEFAULT NULL,
    `order_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    `gateway_id` int(11) DEFAULT NULL,
    `transaction_ammount` double(10,2) DEFAULT NULL,
    `active` tinyint(1) DEFAULT '1',
    PRIMARY KEY (`id`),
    UNIQUE KEY `transaction_id` (`transaction_id`,`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I can achieve this at PHP end but I'm curious to know how I can do it with trigger

Please Note:

For The Sake Of Simplicity I have omitted several column from both the tables.
MySQL version: 10.0.24

It may help you in case of AFTER INSERT.

CREATE TRIGGER trigger_name AFTER INSERT ON orders
    FOR EACH ROW BEGIN IF (SELECT COUNT(*)
              FROM transaction
              WHERE order_id=NEW.order_id) != 0
    THEN
        UPDATE transaction SET active = 0 WHERE order_id=NEW.order_id;
    END IF;
END


Why might a table's data space take up 4x the size of the raw data? I have a table with 490 M rows and 55 GB of table space, so about 167 bytes per row.  The table has three columns: a VARCHAR(100), a DATETIME2(0), and a SMALLINT.  The average length of the text in the VARCHAR field is about 21.5, so the raw data should be around 32 bytes per row: 22+2 for the VARCHAR, 6 for the DATETIME2, and 2 for the 16-bit integer.

Note that the space above is data only, not indices.  I'm using the value reported under Properties | Storage | General | Data space.

Of course there must be some overhead, but 135 bytes per row seems like a lot, especially for a large table.  Why might this be?  Has anyone else seen similar multipliers?  What factors can influence the amount of extra space required?

For comparison, I tried creating a table with two INT fields and 1 M rows.  The data space required was 16.4 MB: 17 bytes per row, compared to 8 bytes of raw data.  Another test table with an INT and a VARCHAR(100) populated with the same text as the real table uses 39 bytes per row (44 K rows), where I would expect 28 plus a little.

So the production table has considerably more overhead.  Is this because it's larger?  I'd expect index sizes to be roughly N * log(N), but I don't see why the space required for actual data to be non-linear.

Thanks in advance for any pointers!

EDIT:

All of the fields listed are NOT NULL.  The real table has a clustered PK on the VARCHAR field and the DATETIME2 field, in that order.  For the two tests, the first INT was the (clustered) PK.

If it matters: the table is a record of ping results.  The fields are URL, ping date/time, and latency in milliseconds.  Data is constantly appended, and never updated, but data is deleted periodically to cut it down to just a few records per hour per URL.

EDIT:

A very interesting answer here suggests that, for an index with much reading and writing, rebuilding may not be beneficial.  In my case, the space consumed is a concern, but if write performance is more important, one may be better off with flabby indices.

After discussions in the comments on the original question, it appears in this case the lost space is caused by the choice of clustered key, which has led to massive fragmentation.

Always worth checking the state of fragmentation via sys.dm_db_index_physical_stats in these situations.

Edit: Following update in comments

The average page density (prior to rebuild of the clustered index) was 24%, which fits perfectly with the original question. The pages were only 1/4 full, so the total size was 4x the raw data size.


Simple query hangs on a specific column condition I have two servers, and I a dumped the database of the first to restore it on the second.

Everything works great, except that on the second server, I can't do any selection based on a specific column "user_id" anymore : SELECT * FROM products WHERE user_id = 12 limit 1;

Each time I run it on the second server, it stays still, without any error. And every other request that concerns a selection based on the user_id column is also stuck (where user_id IS NOT NULL, etc.).

I launched mysqlcheck, everything seemed ok, I restarted mysql, but nothing works.

Any idea how to troubleshoot this ?

EDIT :

Curiously, SELECT * FROM products WHERE user_id IS NULL limit 1; works fine.

I might be guessing on this one, but I think the key distribution must be insane.

POSSIBILITY #1

Please run this

SELECT * FROM
(
    SELECT IFNULL(user_id,-1) user_id,COUNT(1) prod_count
    FROM products GROUP BY IFNULL(user_id,-1)
) A ORDER BY prod_count DESC;

If you can send the output to a text file, look for user_id 12 and compare its prod_count to the total number of rows in the products table. My guess is that user_id 12 is probably more than 5% of the table. The Query Optimizer would give up using any index on user_id and just do a full table scan. To make sure that is the case, please run

EXPLAIN SELECT * FROM products WHERE user_id = 12;

POSSIBILITY #2

There may be stale index stats and you need to run

ANALYZE TABLE products; if there are a lot of INSERTs, UPDATEs.
OPTIMIZE TABLE products; if there are a lot of INSERTs, UPDATEs, and DELETEs.

GIVE IT A TRY !!!


Isolation level issue I need some help in choosing the correct isolation level on my transactions.

My situation is as follows. I have a table with a unique constraint over two columns. For the sake of the argument, lets say the table is called Animal and the columns are Name and Owner. So every owner can only have one pet with a certain name.

My problem is that I'm receiving errors during inserts because they are coming in at such a phase that I'm getting exceptions triggered by the unique constraint. Inserts are happening by stored proc. At the start of the SP I do a select from Animal where Owner=x and Name=y to determine if the animal exists, if so, select the Id and move on, else insert the new animal. The rest of the proc uses the Id to insert/update Animal details in other tables.

I know I can set the isolation level to Serializable, but I'm hesitant to do so because I know that it can cause deadlocks and since I'm having to do loads of inserts rapidly, this is a very real threat.

Any advise will be welcomed, thanks.

Answer originally left by Dan Guzman in comments:

The method I usually use is an explicit transaction plus a UPDLOCK, HOLDLOCK locking hint on the SELECT query. That will help avoid deadlocks and avoid the race condition, without resorting to serializable isolation for the entire transaction.

Also SERIALIZABLE won't really help as the initial select will get shared (S) range locks.  So both sessions will get the S lock, and then, instead of a PK violation, you'll get a deadlock.

With the UPDLOCK in addition to HOLDLOCK, only one session can select the same key regardless of whether it exists (holding update key lock) or not (holding update key range lock). This will serialize access to the specified key in this code path.

This worked for the OP, who implemented it as follows:

@Id = SELECT *
  FROM ANIMAL WITH(UPDLOCK, HOLDLOCK)
  WHERE Owner = x AND Name = y
IF(@id <> NULL)
  UPDATE ANIMAL
ELSE
  INSERT ANIMAL

--DO OTHER STUFF


Does Lazy Writer spend resources on flushing out pages from the buffer pool? In MS SQL Server, does Lazy Writer spend resources to make pages out of the buffer pool after periodically checking free buffer list and marking some clean pages as free? Or free marked clean pages continue to stay in the buffer pool until another page is needed to retrieve from the disk and the latter overwrites the free marked clean page?

does Lazy Writer spend resources to make pages out of the buffer pool after periodically checking free buffer list and marking some clean pages as free

Yes it does, this is primary responsibility of Lazy writer. Its job is to make sure there is some free buffer so that new pages can be brought into memory when required.

Lazy writer would periodically scan buffer pool, look for pages which are least recently used, or you can say "cold pages" and flush them to disk. In case there is sufficient memory available Lazy writer would not do anything. In case of memory being required to bring more page into buffer pool Lazy writer would flush old pages, create space and bring new pages. It does not marks pages as clean


Using LIKE for a Range of Numbers In my WHERE clause, I'm trying to use LIKE for a range of numbers.  For example:

WHERE rr.sequence LIKE '05600%'
   OR rr.sequence LIKE '05601%'
   OR rr.sequence LIKE '05602%'
OR rr.sequence BETWEEN LIKE('056035%' AND '056038%')

Otherwise, I would need to use a LIKE statement for each potential number combination between '056035%' and '056038%'

It looks like you need to do the following:

OR rr.sequence LIKE '05603[5-8]%'

But in general, I agree with Kin's comment on the question: "storing numbers as strings is not a good idea".


mysql Lock wait timeout exceeded; try restarting transaction we are running java application, running for ages, back end is db is MySQL, recently updated to mysql 5.6. Everything was running fine, past week started getting this error
 Lock wait timeout exceeded; try restarting transaction which looks like never stop ending
don't know what to do with this error to stop. why it is occurring all of sudden

You can set variable innodb_lock_wait_timeout=100 for lock time to 100 sec.

mysql> set innodb_lock_wait_timeout=100;

Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 100   |
+--------------------------+-------+

The transaction which is timeout, try to lock table which is hold by another process. and your timeout variable set with little number of second. so it shows error.
You can see more status by the command.

SHOW ENGINE INNODB STATUS\G

You can see list of locked tables by-

 show open tables where in_use>0;

Now see the thread which is using this table

  show full processlist;

now you can kill that thread or wait to complete it.


Index to filter a view before joining the underlying tables Using SQL Server 2008 R2

Suppose you have a query like the following

select top 1 *
from view_cms_user
where UserId = 1234 or UserPasswordRequestHash = 'anxazno121o3n'

view_cms_user is basically:

select *
from cms_user
left outer join cms_usersettings
    on cms_user.UserId = cms_usersettings.UserSettingsUserId

Now, the query execution plan looks like the follow:

Now, this query takes a long time because the filter happens after the join between the two tables in the view, but cms_usersettings actually contains both of the fields necessary to answer this question, since UserSettingsUserId is a foreign key field referencing cms_user.UserId.

I tried creating a non-clustered index with both fields in it (UserSettingsUserId, UserPasswordRequestHash), but SQL Server ignored it. Currently it seems to being using an Index Scan of IX_CMS_UserSettings and doing nested loops with the key lookup operator to return all the fields on the table (because of the select *).

IX_CMS_UserSettingUserId contains the field: UserSettingUserId asc

PK_CMS_UserSetting is clustered key with UserSettingId (note: not the UserSettingUserId).

I cannot change the query, only the indexes.

If I change the query to an 'and' instead of an OR it's instant.

edit: In response to some of the comments below, there are already some indexes on the table in question, each in the image includes the column of the same name only:

Also including all the columns would be undesirable since there are about 50.
The table has 1,239,541 rows.

Also, UserPasswordRequestHash is 'Null' 95% of the time.

Could I through indexing alone influence SQL server to run this much faster.

Possibly.

There are all sorts of things you could try with indexing, including creating a filtered index to exclude the 95% of UserPasswordRequestHash entries that are null, expanding existing indexes to include more columns, or adjusting indexes so the chances of finding the first match is likely to occur earlier.

The query optimizer does not currently have the ability to explore separating the OR predicates into a UNION ALL, pushing each predicate below a local TOP (1) as a human query writer might naturally do.

It also has to make assumptions about the distribution of data to assess the chances of finding the first match, as mentioned above. In addition, there are all sorts of ways that the optimizer's calculations could be 'off', leading to it selecting the 'wrong' index strategy. Not being able to push index hints through a view also limits the opportunities for using a query hint via a plan guide, for example.

So, the best answer I can give from the information given is "possibly".

If you want a more concrete answer to your question, please supply complete DDL for the tables and indexes, and scripts for the existing statistics. Also, let us know how many logical processors, and how much memory, the instance is configured to use. I realize that is a lot of detail to provide, but a specific answer likely does depend on one or more of these details.


DB2 Insert and Deadlocks My application has 4 different threads doing inserts into the same DB2 table simultaneously.

Could this cause a deadlock / lockwait situtation?

Theoretically I don't see a problem here; as one insert statement is not going to lock a row required by another insert statement because all are independent insert statements.

Also, I commit after each insert so transaction log wouldn't fill up.

I'm looking for a high performance model while avoiding any potential lock situtation. Please provide your thoughts.

I don't see a problem either.

If your application logic allows, you might consider committing every N rows.

For improved performance, you might consider whether you can do blocked inserts of multiple rows at a time.

(I use DB2 for i. If you are on LUW or z/OS, your mileage may vary.)


Limit redo for materialized view complete refresh or manual equivalent A materialized view(MV) log can be used to allow a MV to do a fast refresh which only modifies the data that has changed.  However, various conditions prevent the MV from using the log and therefore require a complete refresh.  Oracle implemented an atomic complete refresh as a delete and insert of every record.  It does this even if there are ultimately no changes to the data.

Is there a way to make this replication intelligent with regard to redo generation?  A MERGE followed by a DELETE requires querying the source twice.  Would it be worth it to bulk collect the data to do a BULK MERGE and DELETE?  Is there a better way?

Update:

I explored using a global temporary table as a staging area.  Although they use less than half the redo, they still use to much.

Good question. I "solved" this problem for my situation a while back by making the MV's and any indexes on them NOLOGGING. There was no point to it my situation - I was doing a full refresh of the view anyway, why would I need redo?


SQLcl history (up-arrow) rewrites my queries Version

sql -version
SQLcl: Release 18.4.0.0 Production

I type this:

SQL>  exec dbaspace.long_ops;

SID  % Done  Start Time          Rem [s] Elapsed Message
==== ======= =================== ======= ======= ======================================================================================

There are currently no long running operations.

PL/SQL procedure successfully completed.

I press ARROW UP key to get the last command and SQLcl modifies my history like this:

SQL> BEGIN dbaspace.long_ops; END;;

Error starting at line : 1 in command -
BEGIN dbss.long_ops; END;;
Error report -
ORA-06550: line 1, column 26:
PLS-00103: Encountered the symbol ";"
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

The rewritten history command does not work

Yup, I see that, it's a bug. Will log it and we can hope for a fix in version 19.4.

Just a quick note, you're missing out on features available in versions 19.1, 19.2, and soon to be version 19.3.


What Other Information Is Stored in The Page Header A SQL Server database page is defined as to be 8192 bytes in size.
There is some header information which is said to be 96 bytes in size.

And if you have ever tried to create a table with more than 8053 bytes of column definitions then you will hit the error message:

Creating or altering table 'Generated_Data_GUID' failed because the
minimum row size would be 8061, including 7 bytes of internal overhead.
This exceeds the maximum allowable table row size of 8060 bytes.

Following is an example table DDL:

CREATE TABLE [dbo].[Generated_Data_GUID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](4000) NULL,
    [Data2] [char](4000) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY]

With the above DDL if I change the column definition for column Data3 to be char(10), then I will hit the error message.

The byte sizes for each column type are as follows:

int               :  4 bytes
uniqueidentifiere : 16 bytes
char(n)           :  n bytes
datetime2(n)      :  6 bytes if n < 3
                     7 bytes if n = 3 or n = 4
                     8 bytes if n > 4

If we do some simple maths, then we end up with the following calculation:

Page Size         : 8192 bytes
                   -----------
Header            :   96 bytes -
Internal Overhead :    7 bytes -
Max Size          : 8053 bytes -
                   -----------
Missing Data      :   36 bytes
                   ===========

Question

What do these 36 bytes contain?

Reference Material

Pages and Extents Architecture Guide (Microsoft Docs)
Inside the Storage Engine: Anatomy of a page (SQLSkills.com)

Paul Randal actually answers this exact question in the comments on the blog post you linked to:

The 8060 bytes is the maximum size of one record, not the amount of data space on the page – 8096 bytes.
For a maximum-sized record of 8060 bytes, add two bytes for the slot array entry, 10 bytes for a possible heap forwarded-record backpointer, 14-bytes for a possible versioning tag, and that’s 26 bytes used. The other 10 bytes are for possible future use.
If there are more than one record on the page, all 8096 bytes of data space can be used.

So, in answer to the question in the body of your post:

What do these 36 bytes contain?

The "extra" 36 bytes in a page are used as follows:

10 bytes reserved for heap forward-record backpointer
14 bytes reserved for versioning tag pointing to the version store in tempdb
12 bytes available for the slot array

in the case you outlined of one large record, there are 10 bytes of "wasted" space here.  There is room for 5 more 2-byte slot array entries

Just to confirm that the table defined in the question is, in fact, 8060 bytes wide, let's have a full repro.
First we'll set up the database and the table, and insert one row into it.  I'm adding a clustered index because heaps are the worst.
USE master;
GO

CREATE DATABASE PageJunk;
GO

USE PageJunk;
GO

CREATE TABLE [dbo].[Generated_Data_GUID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](4000) NULL,
    [Data2] [char](4000) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY];
GO

CREATE CLUSTERED INDEX PK_Generated_Data_GUID
    ON Generated_Data_GUID (ID);
GO

INSERT INTO [dbo].[Generated_Data_GUID]
    ([GUID], SEQGUID, Data1, Data2, Data3, EntryDate)
VALUES
    (NEWID(), NEWID(), REPLICATE('1', 4000), REPLICATE('2', 4000), REPLICATE('3', 9), '2018-01-01');
GO

We can see all the pages allocated the index by running this DBCC command:
DBCC IND ('PageJunk', 'Generated_Data_GUID', 1);
GO

The one with a page type of 1 is the index page (page ID 336).  We can dump all kinds of information about that page with this other DBCC command:
DBCC TRACEON (3604); -- needed for the next one to work
GO

DBCC PAGE (PageJunk, 1, 336, 3);
GO

Here are some important snippets from the output of that command.  From the HEADER section:
m_freeCnt = 34

This means there are 34 bytes of free space on the page.  That's the 36 you outlined in your original post, minus the 2 bytes for the slot array entry.  Speaking of which:
m_slotCnt = 1

This means there is only one record on this page.
Now, down in the record section:
Slot 0 Offset 0x60 Length 8060

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 8060

This indicates that the single record stored on this page is 8060 bytes (which is the sum of all the data type storage sizes plus the 7 bytes per-record overhead).
So we do have a fully sized, 8060 byte record on this page.  However, we can still squeeze an additional 34 bytes onto this page if we try harder.
For instance, I could create a table that's 2015 bytes wide.  Each row would then take up 2015 + 7 (internal overhead) + 2 (slot array) = 2024 bytes in the page.  So four rows should add up to 8096 bytes, exactly filling the space left over after the 96 byte header.  Let's try it in the same database:
CREATE TABLE [dbo].[QuarterPage](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](981) NULL,
    [Data2] [char](981) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY];
GO

CREATE CLUSTERED INDEX PK_QuarterPage
    ON QuarterPage (ID);
GO

INSERT INTO [dbo].[QuarterPage]
    ([GUID], SEQGUID, Data1, Data2, Data3, EntryDate)
VALUES
    (NEWID(), NEWID(), REPLICATE('1', 981), REPLICATE('2', 981), REPLICATE('3', 9), '2018-01-01');
GO 4

Now we find our page, and there is only one as expected:
DBCC IND ('PageJunk', 'QuarterPage', 1);
GO

So now we want to get info on page 352:
DBCC PAGE (PageJunk, 1, 352, 3);
GO

And here's the good stuff:
m_slotCnt = 4
m_freeCnt = 0

No free space!  This page is slap full with our 4 rows.


Will PostgreSQL optimize query when the same aggregate usage? SQL would not let use aliases (s,d) in other columns, so, my query is:

SELECT
   employe,
   sum(salary + bonus) AS s,
   sum(debt) AS d,
   (sum(salary + bonus) - sum(debt)) AS total
      FROM test_table
      GROUP BY employe;

This query contain aggregate sum(salary + bonus) two times.

Will PostgreSQL optimize it (use result from first aggregate in second aggregate) or do sum again for the second aggregate?

The reason you can't use the aliases is that they don't exist during aggregation, renaming is performed after aggregation. I don't know the answer to your question, I guess the only way to find out for sure is to look at the plan. However, have you considered nesting your query?

SELECT employe, s, d, s-d as total
FROM (
    SELECT employe, sum(salary + bonus) AS s
         , sum(debt) AS d,
    FROM test_table
    GROUP BY employe
) as t;

another option is to use a CTE:

WITH t as (
    SELECT employe, sum(salary + bonus) AS s
         , sum(debt) AS d,
    FROM test_table
    GROUP BY employe
)
SELECT employe, s, d, s-d as total
FROM t;


Updating a set of rows in a table with values from a different set of rows. Both sets are from the same table I want to update a set of rows in a table with data from a different set of rows from the same table.
Ex: I want to update Customer Invoice Amounts from year 2018 with Customer Invoice Amounts from Year 2017. In the below scenario I want to update Set2 with Amount and Product from Set1 for the respective Customer and Line Item.

Set 1

Customer, Year, Invoice Line, Amount, Product

Cus1, 2017, Line1, 200, PR1
Cus1, 2017, Line2, 300, PR2
Cus2, 2017, Line1, 500, PR2

Set2

Cus1, 2018, Line1, 0,
Cus1, 2018, Line2, 0,
Cus2, 2018, Line1, 0,

Can I accomplish this using MERGE command. I am not planning to insert data I just want to update the Set2 with any available data in Set1.

Assuming that all rows in 2018 have a matching one in 2017 (with same values in Customer and Invoice Line) or you want to update only the matching rows, then you only need an UPDATE, not MERGE:

UPDATE upd
SET Amount  = old.Amount,
    Product = old.Product
FROM dbo.Invoices AS upd
    JOIN dbo.Invoices AS old
    ON  upd.Customer       = old.Customer
    AND upd.[Invoice Line] = old.[Invoice Line]
WHERE upd.Year = 2018
  AND old.Year = 2017 ;

You may want to add AND upd.Amount = 0 as a safeguard so not to update good data but only rows with 0.


Is there always a MySQL user that has all privileges on all databases? For example, does root always have all privileges on all databases?

If I create a new database, will this user automatically have all privileges on the new database without having to run a GRANT command?

Mysql by default creates a single or multiple root user accounts (this depends on the mysql version) that are indeed superusers and have full access to all databases that you create on that mysql server. However, these accounts are initialised as superusers and you can remove their access rights based on your requirements. Or at least you can rename the superuser accounts from root to something else to make it even  more harder to hack your mysql server. Any superuser account with all privileges granted to *.* and with grant option added will have full access to any newly created database on the mysql server.

Technically, it is possible not to have any  superuser account in a mysql server, but it does not make much sense. Such accounts should be used as a last resort only, but they can be handy in case of an emergency.


Is this normalization done right? In this normalization question

When we convert it into 1NF according to the given site the answer is

Is there a need to take out movie category?
Won't it cause information lose?

Also when we convert it in 2NF the table of movies doesn't contain category column.

Isn't there transitive dependency between movie name and its category?
What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples

I believe removing the category without showing it as being moved into a "movies" reference table is an oversight.

Yes, removing that column as shown in  your question does result in data loss.


Teradata 15: Failed [5315 : HY000] An owner referenced by user does not have SELECT WITH GRANT OPTION access to DBC.RoleMembersV.RoleName I am using Teradata 15.  Using a powerful database user sysdba, I created the following view:

REPLACE VIEW DBA_TABLES_TEST.ROLE_ALL_APP_USERS AS
SELECT   A.ROLENAME
   FROM  DBC.ROLEMEMBERSV A
   WHERE A.ROLENAME LIKE 'TG%';

I got the following errors:

Executed as Single statement.  Failed [5315 : HY000] An owner referenced by user does not have SELECT WITH GRANT OPTION access to DBC.RoleMembersV.RoleName.
Elapsed time = 00:00:00.015

STATEMENT 1: Select Statement failed.

Then I used DBC to grant sysdba the select right, but try the view again, it did not work:

GRANT SELECT ON DBC TO SYSDBA WITH GRANT OPTION;

Any idea how can I grant the correct rights to sysdba?

Thank you so much!

Oh, I just figured out the correct grant statement.  All my views are working now.  I can audit my user/database/role permissions using these views.

GRANT SELECT ON DBC TO DBA_TABLES_TEST WITH GRANT OPTION;

Thank you.


Why do these similar queries use different optimisation phases (transaction processing vs quick plan)? The example code in this connect item

Shows a bug where

SELECT COUNT(*)
FROM   dbo.my_splitter_1('2') L1
       INNER JOIN dbo.my_splitter_1('') L2
         ON L1.csv_item = L2.csv_item

Returns the correct results. But the following returns incorrect results (on 2014 using the new Cardinality Estimator)

SELECT
    (SELECT COUNT(*)
    FROM dbo.my_splitter_1('2') L1
     INNER JOIN dbo.my_splitter_1('') L2
        ON L1.csv_item = L2.csv_item)

As it incorrectly loads the results for L2 into a common sub expression spool then replays the result of that for the L1 result.

I was curious as to why the difference in behaviour between the two queries. Trace Flag 8675 shows that the one that works enters search(0) - transaction processing and the one that fails enters search(1) - quick plan.

So I assume that the availability of additional transformation rules  is behind the difference in behaviour (disabling either BuildGbApply or GenGbApplySimple seems to fix it for example).

But why do the two plans for these very similar queries encounter different optimization phases? From what I've read search (0) requires at least three tables and that condition certainly isn't met in the first example.

Each stage has entry conditions. "Having at least three table references" is one of the entry conditions we talk about when giving simple examples, but it's not the only one.

Generally, only basic joins and unions are allowed for entry to search 0; scalar subqueries, semi joins etc. prevent entry to search 0. This stage really is for the very common OLTP-type query shapes. The rules necessary to explore the less common things just aren't enabled. Your example query has a scalar subquery, so it fails entry.

It also depends how you count table references. I've never looked deeply into this with functions, but it's possible the logic is counting the Table Valued Functions as well as the table variables they produce. It could even be counting the table reference inside the function itself - I'm not sure; though I do know that functions are just hard work all round.

The bug with GenGbApplySimple is ugly. This plan shape was always a possibility, but rejected for cost reasons until the change to 100-rows assumed table variable cardinality came in. It is possible to force the problematic plan shape on the pre-2014 CE with a USE PLAN hint, for example.

You're correct about the new Connect item being the same issue reported previously.

To provide an example, the following query qualifies for search 0:

DECLARE @T AS table (c1 integer NULL);

SELECT U.c1, rn = ROW_NUMBER() OVER (ORDER BY U.c1)
FROM
(
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
) AS U;

Making a small change to include a scalar subquery means it goes straight to search 1:

DECLARE @T AS table (c1 integer NULL);

SELECT U.c1, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -- Changed!
FROM
(
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
) AS U;


What makes using an Oracle Wallet to connect to a data source more secure than using a username and password to do the same? What makes using an Oracle Wallet to connect to a data source more secure than using a username and password to do the same? If someone knows the connection string for using an Oracle Wallet to connect to a data source, isn't this just as good as having the username and password? If not, why not?

Wallets can be configured to only be valid from a specific client, so
access to the specific host is also required, whereas
username/password text can be easily ported elsewhere with
copy/paste.
Using wallets also allows most applications to be redeployed between
environments (like dev and prod) without requiring reconfiguration or
changes to credentials.
Placing the credentials in an external store allows password
management policies to be more easily enforced without changing
application code whenever user names or passwords change.


Oracle Database authentication through Active Directory We have Oracle 11g database running on a RHEL 6 server.
and we have a Active Directory domain running on windows 2008 server in same network.
I want to authenticate Oracle database users using this AD.
I searched but found only documents related to kerberos authentication where the database clients directly authenticate with AD and then contact Oracle database.
I cant use this setup as we don't have any network connection between client softwares like Toad and Active directory. The client softwares run in a different network and not connected to this windows domain.  These clients can only connect to Oracle database.
I want the following setup

User will enter domain user name and password in the client software
The Oracle database server has to pass these details to AD and try to authenticate
Based on AD response, oracle has to allow or block the user

Please let me know if this is possible with my environment.

Note: the RHEL server on which Oracle is installed is joined to this Widows Domain using SSSD and this OS authentication n works without issues

This is called Enterprise User Security, which is available in Enterprise Edition databases.

You can do this with Oracle Virtual Directory or Oracle Unified Directory (preferred).

They both require the Directory Services Plus licence.

The required steps for this can be found at for example:

Enterprise User Security (EUS) with Active Directory (AD) Integration Using OUD Proxy (Doc ID 1571196.1)

A public walkthrough:

Configure EUS with OUD, AD and DB12c


Best way to pg_dump PostgreSQL on RDS to S3 I need to dump my PostgreSQL on RDS to a file on S3 periodically (to use it elsewhere than AWS).

Is there a better way than running a script that runs pg_dump and uploads results to S3 using e.g. boto3?

You can directly upload the (gzipped) pg_dump results to s3 using the aws cli:

pg_dump -Z 9 -v -h ${DATABASE_HOST} -U ${DATABASE_USER} -d ${DATABASE_NAME} | aws s3 cp --storage-class STANDARD_IA --sse aws:kms - s3://my-bucket/dump.sql.gz

At work we wrapped this in a script which additionally fetches the required credentials from parameter store and then stores the backup in s3. The script runs within a docker container as a scheduled ecs task. Maybe the code in this repository will help you.


SQL Server Internal Inconsistency on GROUP BY I recently recovered a database from a hard-drive failure using DBCC CHECKDB WITH REPAIR ALOW DATA LOSS.

Now I can select records from a table-valued function to retrieve sales records, but using a GROUP BY on the same data causes an "internal inconsistency" error.

Running DBCC CHECKDB returns zero inconsistency errors.

I have run everything I know to detect any torn pages, but have found none.

How do I find the torn page or force a rebuild?

Basically, you have to restore. Do you have a backup?

While this is restoring, read "Misconceptions around database repair" and the rest of Paul Randal's site.

Harsh, but true.


score ranking no tie, if tie order by score submit date I have a very simple MySQL table where I save highscores. It looks like that:

Id     Name     Score    SubmitDate
So far so good. The question is: How do I get what's a users rank EVEN THEIR SCORE IS TIE, I want them have diff ranking base on the submission date (older submission date have higher ranking)? For example, I have a users Name or Id and want to get his rank.

An Example

Id  Name    Score    SubmitDate
1   Ida     100      2014-08-01 21:37:17
2   Boo     99       2014-08-02 21:37:17
3   Lala    99       2014-08-03 21:37:17
4   Bash    102      2014-08-04 21:37:17
5   Assem   99       2014-08-05 21:37:17

In this very case, got 3 persons have the same score which are 99. Boo rank would be 3, then Lala, then come to Assem because I want to arrange by submit date.

The query should return one row, which contains (only) the required Rank.

Expected Result should be like below:

Id  Name    Score    SubmitDate              Rank
4   Bash    102      2014-08-04 21:37:17     1
1   Ida     100      2014-08-01 21:37:17     2
2   Boo     99       2014-08-02 21:37:17     3
3   Lala    99       2014-08-03 21:37:17     4
5   Assem   99       2014-08-05 21:37:17     5

Any idea how to accomplish this, I saw a lot solution have the same ranking but I do not want this output. Thanks!


SQL*Loader-926, ORA-04031 unable to allocate x bytes of shared memory A customer received this error while importing serveral files. The import routine (VB.NET) is using SQL*Loader (Release 11.2.0.3.0) via Process on each table separately (64 in total) and is called weekly for many years now.
Last week following error was thrown:

value used for ROWS parameter changed from 1024 to 795 (all tables are imported with ROWS=1024, no BINDARRAY parameter specified)

  SQL*Loader-926: OCI error while executing delete/truncate (due to
  REPLACE/TRUNCATE keyword) for table x ORA-04031: unable to allocate
  4160 bytes of shared memory ("shared pool","unknown object","sga
  heap(1,0)","modification ")

I'd googled the ORA-4031 and determined the current memory informations:

3,08 GB (shared pool) (select sum(bytes), pool from v$sgastat GROUP BY pool),
922MB free memory (select * from v$sgastat where name = 'free memory';) and
1,18 GB shareable memory (select sum(SHARABLE_MEM) from v$db_object_cache;)

To resolve the issue several posts pointed out to look at the views v$SGA_TARGET, v$PGA_TARGET, v$SGA_TARGET_ADVICE, v$PGA_TARGET_ADVICE, v$SGA_DYNAMIC_COMPONENTS and so on, but i have no dba privilege. (table or view does not exist).

Thus i'd like to know whether i can resolve the issue only with SQL*Loader settings or must a DBA investigate into this?

EDIT:
control file: LOAD DATA INFILE '<filepath>' INTO TABLE x TRUNCATE(<columns>)
call: sqlldr.exe userid=... log=... ctl=... bad=... rows=1024

If this was a one-time error, just try running SQL*Loader again.

If this happens repeatedly, you will need the help of the DBA.

The above views barely contain anything informative related to this issue. (Also there is no such view as V$SGA_TARGET or V$PGA_TARGET.) When this error occurs, the database instance creates a trace file on the database server (alert and file location can be found in the alert log), that is what you will need for further investigation.


Maximum table capacity in SQL Server 2008 I have an application which inserts more than 1 billion rows annually into a table. This table contains some varchar and bigint columns and one blob column as well.

The 1 billion rows consist of history data which are kept for tracking purpose. So I was wondering whether there will be a table capacity limitation if I continue in this structure according to this MSDN article about maximum table size.

Does the data file size mentioned in that link refer to the table data file group?

There is no practical limit except disk space. I read the table you linked to entirely and checked it.

If you need to go above 16TB you need multiple files (a simple procedure).


Dumping mssql db from one server and restoring on another on Mac/Linux Using either MacOS or Linux, my objective is to copy a database from one MSSQL database server to another.  The source database server is remote, while the target database server (which will be restored from the source) is running on port 1433 on a local docker container.
Unlike for postgres (pg_dump utility is well-documented), I have not found much documentation online about how to practically use command line tools like sqlcmd (which are avaiable after brew install mssql-tools), to connect to, copy and restore a database on SQL server. Are there any suggestions for a basic template of commands to use for copying databases and restoring them using the terminal in MacOS/Linux?

In SQL Server you use BACKUP and RESTORE commands for this.  These both read and write files relative to the server, not the client.  So you'll need to write the backup to a storage location accessible to both SQL Server instances.  Backups can be written to and read from the local filesystem, SMB shares, or Azure Blob Storage.


When do Azure SQL Database index rebuilds occur? Azure backups occur every few minutes as per the documentation for Automated backups:

  SQL Database supports self-service for point-in-time restore (PITR) by automatically creating full backup, differential backups, and transaction log backups. Full database backups are created weekly, differential database backups are generally created every 12 hours, and transaction log backups are generally created every 5 - 10 minutes, with the frequency based on the compute size and amount of database activity.

When do index rebuilds occur in Azure SQL? Are they automatic or manually selected in a maintenance window?

When do index rebuilds occur in Azure SQL? Are they automatic or manually selected in a maintenance window?

The index rebuilds, at the time of writing the answer, in SQL Server is not automatic, I hope you are not mixing it with automatic index tuning. There is no "SQL Server agent" in Azure SQL database you have the option to schedule jobs via Elastic Job Agent to run Ola Hallengren Index maintenance job for index rebuild/reorganize and stats update, you can also use your personal index maintenance script. Please note Elastic Job agent is still in preview and requires the Azure SQL database to be on size S0 and above. Read more about Database elastic jobs

You also have option to create Azure automation account for job scheduling


Check if backup is already in progress There is SQL Agent Job which is doing backup of database and after backup there are several steps for delete database objects, creating some files, etc... Job is pretty much generic one, but I need some kind of check if it's there some backup of database which is running and if it's running how to implement some kind of WHILE loop, to wait until backup is finished and then to start this one.

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DB_Backup',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Create Backup]    Script Date: 11/28/2016 1:41:01 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'USE [DB]
GO
DBCC SHRINKFILE (N''DB_log'' , 2)
GO
DBCC SHRINKFILE (N''DB_log2'' , 0)
go
DBCC SHRINKFILE (N''DB'' , 2)
GO
USE MASTER
GO

BACKUP DATABASE [DB] TO  DISK = ''C:\DBbackup''
GO
'

You can customize this to your needs - (I agree that you should not be shrinking files as part of a scheduled maintenance task - Read these words of wisdom.)

DECLARE @SqlText VARCHAR(max)
DECLARE @BackupIsRunning BIT = 1 --Force initial value to 1

WHILE @BackupIsRunning = 1
BEGIN
    SELECT @SqlText = s.TEXT --Check for any BACKUP commands currently running
    FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
    WHERE r.command IN (
            'BACKUP DATABASE'
            ,'BACKUP LOG'
            )

    IF @@ROWCOUNT = 0 --If @@ROWCOUNT = 0, then no backups are running
    BEGIN
        SET @BackupIsRunning = 0 --Drop out of WHILE loop

        BREAK
    END

    WAITFOR DELAY '00:00:10' --Take a break and check again
END


Way around Composite Primary Key requiring Composite Foreign Key Here is a table called Make (where name is the PK)

name|other columns
----------------------
  A | ...
  B | ...
  C | ...

And a table called Type (where make, name together is the PK and make references Make.name as FK)

make|name|other columns
------------------------
  A |SUV | ...
  B |SUV | ...
  B |UTE | ...
  C |UTE | ...

Finally a table called Model (where make, type, name together is the PK and make, type reference Type.make, Type.name as FK)

make|type|name|other columns
-----------------------
  A |SUV |CRV | ...
  A |SUV |HRV | ...
  B |UTE |DMAX| ...

It seems to me the Model.make is redundant in the Model table, but it is necessary only because the Type table uses a composite key make, name.

What is the way to get rid of this redundancy?

I believe I can add an unique id column in the Type table, so that the Model table can reference that as FK. But it seems this extra column is just another form of redundancy, as make, name can uniquely identify every row already?

Both the designs are possible, reasonable, and non-redundant (in the sense that no data can be eliminated in one solution without losing information): 1) leaving the PK of Type as the pair (make, name) and use it as foreign key in Model, or introducing a surrogate PK in Type and using it in Model.

And their pro and cons balances: the first option allows to perform more efficiently a join between Model and Make, the second one uses slightly less memory.


How are limits in mysql on size of columns etc calculated From the mysql docs,
1 - on joins,

The maximum number of tables that can be referenced in a single join is 61. This also applies to the number of tables that can be referenced in the definition of a view.

2 - row size

Every table (regardless of storage engine) has a maximum row size of 65,535 bytes

3 - on number of columns

There is a hard limit of 4096 columns per table
InnoDB permits up to 1000 columns.]

Is there an explanation of how these numbers have been calculated, or are these all constants assumed during system design?

The 61 is probably arbitrary.  If you have a 61-way JOIN, you have an awful schema.
The row size limit is not quite right.  What page is that on?  It is ignoring that MEDIUM/LONGTEXT/BLOB fields are not included in that limit.  (But, for that matter, I don't know where 64K-1 applies.)  In InnoDB, there is an "about 8K" limit excluding TEXT, etc that can be pushed to off-block storage.
The InnoDB limit was raised to 1017 recently.  The 4096 limit smells like a 12-bit fields in the Handler (Engine-agnostic) code.

I like limits, too.  Here are some more.


Empty result set when matching NULL? If I run a statement such as SELECT * FROM stuff WHERE something=NULL I will get an empty result set.

I know that the solution is to use something IS NULL. I also understand why: SQL interprets NULL as unknown, and it makes logical sense that if something is unknown, it can’t match something else.

What puzzles me is the meaning of the result set. I have always understood that you can’t match something=NULL, so why is is acceptable? It would suggest that under some circumstances, one might get a different result?

Alternatively, why does SQL not accept something=NULL as an idiom equivalent to IS NULL? It wouldn’t be the only idiom which doesn’t have a literal interpretation (such as count(*) vs SELECT *).

The question is: since something=NULL is not an error, does it mean something useful?

You can compare things to NULL.

something=NULL is not an error.

It is a Boolean expression that evaluates to unknown. SQL uses three valued logic. The possible results of a Boolean expression are true, false or unknown.

The WHERE clause only returns rows where the predicate evaluates to true.

Whilst comparing to a literal null with =null is rarely (if ever) useful it is certainly useful to be able to use an col = something predicate against a column which may contain nulls and the semantics of how = something operates against null are the same there.


INSERT INTO #temp taking 1000 times longer than SELECT INTO #temp I am shredding an xml blob that is being passed to me and loading a temp table for further processing.  One of the things I noticed while working through the process is that performing an INSERT INTO is taking like 20 minutes while doing a SELECT INTO is taking seconds.  The DB statistics are up to date and we are running daily maintenance plans so not sure what is going on.  I know that SELECT INTO bypasses the logs which would make it faster, but I as not assuming that the INSERT INTO would be so much worse.

EDIT: Based on comments adding some more details

Why would the INSERT INTO be taking so much longer than the SELECT INTO for a temp table.

Query example below.  I am shredding XML and inserting into a temp Table I have both the INSERT INTO and SELECT INTO commented out.  The INSERT INTO for just 100 records is showing 382,000+ logical reads while the SELECT INTO is showing none.

;with cteScen AS (
SELECT    DENSE_RANK() OVER ( ORDER BY scenario_node ) AS scenario ,
        RANK() OVER ( PARTITION BY scenario_node ORDER BY scen_val ) AS level_order ,
        scen_val.value('text-value[1]',
                       'varchar(100)') AS 'scen_value' ,
        scen_val.value('(distribution/@id)[1]',
                       'int') AS 'dist'
FROM      @scenarioXML.nodes('/scenarios/scenario')
        AS scenario ( scenario_node )
        CROSS APPLY scenario.scenario_node.nodes('./values/value')
        AS x ( scen_val ))
--INSERT INTO #tmpTbl(Scenario, Level_Order, Scenario_Value, Distribution_ID)
Select top 100 *
--INTO #tmpTbl
From cteScen

Any thoughts?

There are a couple of connect items that might be relevant here.

An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1
Poor xml performance with table variable
Bad performance when inserting data from element-centric documents
INSERT from .nodes with element-based XML has poor performance on SP2 with x64

I have not been able to reproduce what you see with your query so I can not say for sure what is happening for you but one issue was that there was an eager spool step in the query plan that was not needed. That eager spool can be removed by using a trace flag in the first fixed versions of SQL Server. I am not sure if that is needed in later versions. The eager spool was in there "due to a general halloween protection logic". That might explain why you see the bad performance when you insert to an already existing table. If you have a look at the query plans for both your queries you should see the difference if that is the case.

There are also some workarounds suggested that you can try and I think you should try to specify the text node when you fetch text-value.

scen_val.value('(text-value/text())[1]', 'varchar(100)') AS 'scen_value' ,

If you don't do that SQL Server will aggregate all text values below text-value. For your XML you probably don't see a difference in the result but if text-value was a mixed content node with both text and sub nodes you would.

declare @XML xml = '
<root>
  <item>item text <subitem>sub item text</subitem>
  </item>
</root>'

select @XML.value('(/root/item)[1]', 'varchar(50)') as C1,
       @XML.value('(/root/item/text())[1]', 'varchar(50)') as C2

Result:

C1                           C2
---------------------------- ----------------------------
item text sub item text      item text