Bulk database update/insert from CSV file I am implementing application specific data import feature from one database to another.

I have a CSV file containing say 10000 rows. These rows need to be inserted/updated into database.

There might be the case, where couple of rows may present in database that means those need to be updated. If not present in database, those need to be inserted.

One possible solution is that, I can read one by one line, check the entry in database and build insert/update queries accordingly. But this process may take much time to create update/insert queries and execute them in database. Some times my CSV file may have millions of records.

Is there any other faster way to achieve this feature?

There's a nice technology available in Oracle called External Tables. In your scenario, you could access your external plain-text data using External Tables from within the database and update your existing data in database with SQL statements you love and are used to – for example, INSERT, MERGE etc.

In most cases, using Oracle supplied utilities is the best way to perform ETL. And because your question sounds more like administrative one I suggest you to look at my previous post on DBA Stack Exchange "Update Oracle database from CSV".

UPDATE: This approach works pretty well for reading external data in database. Generally, you define external data format every time you need to process the plain-text file which has new format. Once external table is created you can query it just like a real database table. Whenever there is a new data to import, you just replace the underlying file(s) on the fly without need to recreate external table(s). Since external table can be queried as any other database table, you can write SQL statements to populate other database tables.

The overhead of using External Tables is usually lower compared to other techniques you would implement manually because this technology was designed with performance in mind taking into account the Oracle Database architecture.


A way to syncronize data in sql express I have 2 servers, one with SQL standard and the second one is express.
I wanted to know if there is any tool or any way to synchronized the databases and making it work like replication without the replication (because i don't have the agent in the express edition of corse).
Thanks.

You could try Windows Synchronization Manager

https://technet.microsoft.com/en-us/library/ms151193(v=sql.105).aspx

Windows Synchronization Manager is a utility available with Microsoft Windows 2000 and later versions. If Microsoft SQL Server is running on the same computer as Synchronization Manager, you can do the following:

Synchronize a subscription.

Reinitialize a subscription.

Remove a subscription.

It is particularly useful for synchronizing subscriptions on SQL Server Express, because this version of SQL Server does not include SQL Server Agent, which, by default, runs the replication agents on other versions of SQL Server.


Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus.  Here is a worst case example containing multiple statements each with blank lines, semicolons, and forward slashes:

INSERT INTO t1 VALUES ('a

;
/
');

INSERT INTO t1 VALUES ('b

;
/
');

DELETE FROM t1 WHERE c1 = 'c

;
/
';

For various reasons these statements need to be run from SQL*Plus.  The blank lines are easy to resolve with a simple...

set sqlblanklines on

I am aware that the sqlterminator can be changed and/or turned off, but both would require modifications to the code, the former moves the problem without resolving it and neither resolves the embedded slash issue.

The best answer would be a way to allow these statements to run without modification by changing the environment in some way (as sqlblanklines does).  If that isn't possible, then perhaps there is a way to programmatically modify the scripts.  I am trying to avoid manual changes.

You can do most of this by using a login.sql. login.sql is executed during - surprising - login and is loaded from your SQLPATH or current directory. For the examples you gave, your really chose the worst case.

Problem is the sqlterminator. Whatever you put in there, the forward slash is maintained as a free sqlterminator. Next to that, sqlplus first scans for the sqlterminator and does this before scanning to the string terminator. A bug if you ask me. The forward slash can be used in a string as long as it is not alone on a seperate line. As soon as sqlplus finds the character specified as sqlterminator, it ignores everything else and stops reading.

The forward slash can be handled, as long as it is not alone on a line.

login.sql contains:

prompt run login.sql
show sqlterminator
show sqlblanklines
set sqlblanklines on
set sqlterminator ';'
show sqlterminator
show sqlblanklines
prompt ready login.sql
set echo on

leigh.sql contains:

INSERT INTO t1 VALUES ('fail bc semicolon
a;a
/
');

INSERT INTO t1 VALUES ('fail bc solo /

aa
/
');

INSERT INTO t1 VALUES ('ok / not solo

aa
/a
');

DELETE FROM t1 WHERE a = 'c

a/
';

run the script:

sqlplus leigh/leigh@orcl @leigh
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 9 22:36:20 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

run login.sql
sqlterminator ";" (hex 3b)
sqlblanklines OFF
sqlterminator ";" (hex 3b)
sqlblanklines ON
ready login.sql
SQL> INSERT INTO t1 VALUES ('fail bc semicolon
  2  a;a
  3  /
ERROR:
ORA-01756: quoted string not properly terminated

SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL>
SQL> INSERT INTO t1 VALUES ('fail bc solo /
  2
  3
  4  aa
  5  /
ERROR:
ORA-01756: quoted string not properly terminated

SQL> ');
SP2-0042: unknown command "')" - rest of line ignored.
SQL>
SQL> INSERT INTO t1 VALUES ('ok / not solo
  2
  3
  4  aa
  5  /a
  6  ');

1 row created.

SQL>
SQL> DELETE FROM t1 WHERE a = 'c
  2
  3
  4  a/
  5  ';

0 rows deleted.

No need to fiddle with begin/end blocks. Can't handle sqlterminator inside command, no matter where it is, in a string or not, can't handle lines with forward slash alone on a line in a string.


Warn about table recreation on table change By default, if you try to change a table using the designer making any of the following changes, you get an error message:

You change the Allow Nulls setting for a column.
You reorder columns in the table.
You change the column data type.
You add a new column.

Error message:

I know that one can turn this off by changing the "Prevent saving changes that require table re-creation' option in SSMS options.

I find that for some tables, (the ones with important data in them), I want this warning. However, for a lot of other tables, I don't care whether they get blown away and then re-created. Right now, I'm just constantly changing the setting back and forth, but what I'd really like is a way to be warned but still be given the option to proceed anyway.

Is there any setting anywhere that would cause behaviour like this?

No, the option is to either always prevent it or to not warn you at all.

What you should do, is merely learn and understand what types of changes will force SSMS to drop the table and re-create it, or stop using the designer UI altogether and use DDL. In some cases you can write DDL such that it doesn't have to drop and re-create the table (SSMS isn't smart enough for all of these cases), but in all cases at least you'll know what's going to happen.


Improve MySQL Query Performance - Too many records I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during administration process. My server load drastically increases when I do search for phone numbers in the entire database. Since I dont want to keep duplicate phone records in the database, I used to check the existence of phone number in the whole database, which cause my application a little bit slower. My Question: How do I improve querying the entire database with high performance.

phone1 -> Datatype Varchar(10)

I tried indexing and it do a little for me. Any other ways to improve performance of my system.

Table Structure:

CREATE TABLE `phone_directory` (
  `lead_id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
  `list_id` BIGINT(14) UNSIGNED DEFAULT NULL,
  `gmt_offset_now` DECIMAL(4,2) DEFAULT '0.00',
  `first_name` VARCHAR(30) DEFAULT NULL,
  `middle_initial` CHAR(1) DEFAULT NULL,
  `last_name` VARCHAR(30) DEFAULT NULL,
  `address1` VARCHAR(100) DEFAULT NULL,
  `address2` VARCHAR(100) DEFAULT NULL,
  `address3` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(50) DEFAULT NULL,
  `state` CHAR(2) DEFAULT NULL,
  `postal_code` VARCHAR(10) DEFAULT NULL,
  `phone1` VARCHAR(12) DEFAULT NULL,
  `phone2` VARCHAR(12) DEFAULT NULL,
  `phone3` VARCHAR(12) DEFAULT NULL,
  `email` VARCHAR(70) DEFAULT NULL,
  `fax_number` VARCHAR(255) DEFAULT NULL,
  `manager_name` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(6) DEFAULT NULL,
  PRIMARY KEY (`lead_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Query:

SELECT * FROM phone_directory WHERE phone1 IN ('315XXXXXXX','0315XXXXXXX');

SELECT * FROM phone_directory WHERE phone2 IN ('315XXXXXXX','0315XXXXXXX');

SELECT * FROM phone_directory WHERE phone3 IN ('315XXXXXXX','0315XXXXXXX');

I'd recommend to not have three columns phone1, phone2 and phone3, but have a related table for phones, and have your data fully normalized (or, further normalized):

 CREATE TABLE `phone_directory` (
   `lead_id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
   `list_id` BIGINT(14) UNSIGNED DEFAULT NULL,
   `gmt_offset_now` DECIMAL(4,2) DEFAULT '0.00',
   `first_name` VARCHAR(30) DEFAULT NULL,
   `middle_initial` CHAR(1) DEFAULT NULL,
   `last_name` VARCHAR(30) DEFAULT NULL,
   `address1` VARCHAR(100) DEFAULT NULL,
   `address2` VARCHAR(100) DEFAULT NULL,
   `address3` VARCHAR(100) DEFAULT NULL,
   `city` VARCHAR(50) DEFAULT NULL,
   `state` CHAR(2) DEFAULT NULL,
   `postal_code` VARCHAR(10) DEFAULT NULL,
   `email` VARCHAR(70) DEFAULT NULL,
   `fax_number` VARCHAR(255) DEFAULT NULL,
   `manager_name` VARCHAR(255) DEFAULT NULL,
   `status` VARCHAR(6) DEFAULT NULL,
   PRIMARY KEY (`lead_id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8;

 CREATE TABLE phones
 (
     lead_id INT(9) NOT NULL REFERENCES phone_directory(lead_id),
     phone VARCHAR(12) NOT NULL,
     priority tinyint DEFAULT 1,  -- If you need to give them priorities (1, 2, 3, ...), or sort them
     CONSTRAINT unique_phones UNIQUE(phone), -- You don't want repetead telephones. This enforces it.
     PRIMARY KEY(lead_id, phone)  -- Covering index + clustering... for the sake of efficiency
 ) ;

Your check now is only:

SELECT
    lead_id, phone
FROM
    phones
WHERE
    phone IN ('315XXXXXXX','0315XXXXXXX');

On average, this will be 3x faster ... and you can have 0, 1, 2, 3 or any number of phones for a given lead_id, and your UNIQUE constraint enforces uniqueness, so, if there's an error at some point in your application, the database helps you avoid a mistake.

dbfiddle here

NOTE  1: It is not strange to have one company have more than 3 telephone numbers: you're covering all bases.

NOTE 2: As phone numbers aren't going to use non-ASCII characters, you could save some space (and increase slightly in speed, as less data is going to move around) by specifying a single-char collation, such as latin1_bin.

dbfiddle here


How should I index a UUID in Postgres? I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUID values in Postgres? I'm split between using hashing and using a trie, unless there's already something built-in that it uses automatically. Whatever I use is going to be handling huge amounts of data.

The SP-GiST operator family "text_ops" indexes using a trie. Because UUIDs are quite long and very dissimilar, these sound appealing even though I would only ever do full match searches.

There's also a hash option. Hashing is O(1), and I won't need to do any comparisons besides equality of course, but because UUIDs are quite long, I'm afraid that generating hashes from them would waste a lot of time.

Or is this something that depends too much on system and use specifics?

I'd rather use bigserial in most cases, but I've been told to use uuid for this. We need uuid because we might have multiple servers using different databases, so there isn't a guarantee that we'll have unique bigints. We could use a different sequence (and seed) for each server, but it's still not as flexible as UUIDs. For example, we wouldn't be able to migrate database entries from one server to another without converting the IDs and their references everywhere.

Use PostgreSQL's built-in uuid data type, and create a regular b-tree index on it.

There is no need to do anything special. This will result in an optimal index, and will also store the uuid field in as compact a form as is currently practical.

(Hash indexes in PostgreSQL prior to version 10 were not crash-safe and were really a historical relic that tended to perform no better than a b-tree anyway. Avoid them. On PostgreSQL 10 they've been made crash-safe and had some performance improvements made so you may wish to consider them.)

If for some reason you could not use the uuid type, you would generally create a b-tree on the text representation or, preferably, a bytea representation of the uuid.


Why does TokuDB say to remove innodb_buffer_pool_%? The TokuDB quick start guide says "After converting your tables to TokuDB, you should comment out the global options related to memory caches (ie: innodb_buffer_pool%)."

If TokuDB supports both TokuDB and InnoDB database engines, why would I comment out these settings?

The reason is that you don't want to have heavyweight tables both for the TokuDB engine and the InnoDB engine.

These two engines both need their resources. They are both very smart about managing resources inside their own scope, and yet they are completely ignorant about one another.

So while InnoDB can coordinate its I/O between multiple query executions, it is unaware that TokuDB may at the same time use the disk in some way.

In particular, the  "After converting your tables to TokuDB, you should comment out the global options related to memory caches (ie: innodb_buffer_pool%)." relates to the convention that you provide InnoDB with most of the OS available memory. In such case little to no memory remains for TokuDB to operate with.

A situation where you would have large tables in both engines is undesired and would very probably lead to poor performance for both engines. It's just fine to leave some small, non-frequently accessed InnoDB tables with a small allocation of memory.


Why does adding a computed column prevent predicate pushdown? I have a weird situation that I don't quite understand.

I have a table like this kinda:

CREATE TABLE dbo.cc_demo
     (
         id INT IDENTITY PRIMARY KEY,
         up_action INT,
         down_action INT,
         last_action_date DATETIME
     );

INSERT dbo.cc_demo ( up_action, down_action, last_action_date )
SELECT TOP 5000000
       nums.num % 500000, nums.num % 500000, DATEADD(MINUTE, nums.num, GETDATE())
FROM
       (   SELECT     ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS num
           FROM       master..spt_values AS sv
           CROSS JOIN master..spt_values AS sv2 ) AS nums;

If I run this query, the plan is just a regular clustered index scan.

SELECT *
FROM   dbo.cc_demo AS cd
WHERE  cd.last_action_date >= '20270601'
AND    cd.last_action_date < '20270901';

But if I add a computed column and index it, my plan changes for the worst.

ALTER TABLE dbo.cc_demo
    ADD total_actions AS up_action + down_action;

CREATE INDEX ix_total_actions ON dbo.cc_demo (total_actions);

Now it scans the clustered index and then filters stuff out way later on!

It asks for an index, and that changes the plan back to what I'd expect, but why do I have to add this?

CREATE NONCLUSTERED INDEX [WORLDSTAR]
    ON dbo.cc_demo ( last_action_date )
    INCLUDE ( up_action, down_action, total_actions );

That doesn't seem logical.

Performance:

Pre-computed column:

Table 'cc_demo'. Scan count 1, logical reads 17990,

 SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 224 ms.

Post computed column + index:

Table 'cc_demo'. Scan count 1, logical reads 17990

 SQL Server Execution Times:
   CPU time = 594 ms,  elapsed time = 591 ms.

Here's the breakdown:

When I add the computed column, the plan doesn't change
When I index the computed column, it does
I'm not too worried about the performance yet
I'm just curious why the plan changes this way after I index the computed column

I'm just curious why the plan changes this way after I index the computed column

It's not indexing the computed column that really matters; any additional non-covering index can produce the same effect (so long as the table occupies more than one page). With only a clustered index present (or a fully covering nonclustered index), the optimizer can produce a TRIVIAL plan, with the filtering condition fully pushed down.

When there are non-trivial access method choices to make, the query goes through cost-based optimization. Cost-based optimization contains more complex and powerful index-matching rules, as you might expect, but the presence of multiple compute scalars due to computed column expansion and projection can prevent predicates (filters) being pushed down the execution plan, when the computed column is not PERSISTED.

SQL Server tries to push predicates (filters) as far down the logical query tree as it can early in the compilation process, but it can be quite conservative about doing this when non-persisted computed columns are involved. A filter that is not pushed down early is unlikely to be pushed further during cost-based optimization.

Simplified Demo

DROP TABLE IF EXISTS #CC;

CREATE TABLE #CC
(
    id integer IDENTITY PRIMARY KEY,
    c0 integer NULL,
    c1 integer NULL,
    c2 integer NULL,
    c3 AS c1 + c2
);

-- Just enough rows to fill more than one page
-- (so not all data access is trivial)
INSERT #CC
    (c0, c1, c2)
SELECT
    SV.number, SV.number, SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 324;

-- Trivial plan (only a clustered index to choose from)
SELECT * FROM #CC AS C WHERE C.c0 >= 1;

-- Add a non-covering index *not* on the computed column
CREATE INDEX ic2 ON #CC (c2);

-- Filter not pushed
SELECT * FROM #CC AS C WHERE C.c0 >= 1;

Workarounds

There are several ways to workaround this limitation in your example:

Do not project the total_actions column. If the column is not needed, computations to derive its value cannot get in the way of predicate pushing.
Make the computed column PERSISTED. This allows the optimizer consider plans that read the persisted value from the base table, avoiding the computation.
Specify the clustered index using a hint e.g. WITH (INDEX(1)). This allows a trivial plan, since it removes the question of access method choice.
Specify the index on the computed column using a (INDEX(ix_total_actions)) hint. While not covering (so no trivial plan), this hint also allows the column to come from storage rather than being computed. The index does not include last_action_date, so the predicate is applied in a Key Lookup. This plan may therefore be quite inefficient.
...etc.


query plan caching (sql server) Is there a measure of time frame as to which the execution plan of inline queries will cached?

There is an algorithm (will find it), but practically it will stay there:

while being used (Edit: See AdaTheDev's answer)
until statistics update (also implied by index rebuild)
until definition changes (including whitespace and case)
SQL instance restart
until severe memory pressure

SQL Server 2008 article


sp_addUser through sp_execute (SQL) in master db The task is to automate restoring a group of databases, fixing orphaned users, and granting SELECT/INSERT/UPDATE/DELETE permissions.

use master
go

ALTER DATABASE [myDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    RESTORE DATABASE [myDatabase] FROM  DISK = N'D:\MSSQL\Backups\myDatabase.bak' WITH  FILE = 1
    ,  MOVE N'myDatabase' TO N'D:\MSSQL\Data\myDatabase.mdf'
    ,  MOVE N'myDatabase_log' TO N'D:\MSSQL\Logs\myDatabase.ldf'
    ,  NOUNLOAD
    ,  REPLACE
    ,  STATS = 5
ALTER DATABASE [myDatabase] SET MULTI_USER
go

use myDatabase
go

sp_adduser 'userSelectOnly'
go

exec sp_change_users_login 'Auto_Fix', 'userSelectOnly'
go

grant select to userSelectOnly
go

Like this, in SSMS, it works great. Since there are several users and several databases, I'd like to put the database names and users into tables and loop through each, e.g.

    select @sql = 'ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

    + 'RESTORE DATABASE [' + @dbname + '] FROM  DISK = N''D:\MSSQL\Backups\' + @dbname + '.bak'' WITH  FILE = 1
        ,  MOVE N''' + @dbname + ''' TO N''D:\MSSQL\Data\' + @dbname + '.mdf''
        ,  MOVE N''' + @dbname + '_log'' TO N''D:\MSSQL\Logs\' + @dbname + '.ldf''
        ,  NOUNLOAD
        ,  REPLACE
        ,  STATS = 5'

    + 'ALTER DATABASE [' + @dbname + '] SET MULTI_USER'' -- extra tick to fix the SE code highlighter

    exec sp_executeSQL @sql

    @sql = 'use ' + @dbname
    exec sp_executeSQL @sql

    select @sql = 'grant select to ' + @current_loop_user
    exec sp_executeSQL @sql

Followed by all the login/user lines. I've tried it, and it neither do the job nor throw obvious errors, am I going to just have to type it all out longhand?

Your dynamic text is not valid:

you should add spaces between IMMEDIATE and RESTORE, STATS = 5 and ALTER,
you should remove additional unclosed quotation mark here: SET MULTI_USER'' --> SET MULTI_USER'

Your

@sql = 'use ' + @dbname
    exec sp_executeSQL @sql

is executed in it's own context so you changed db context not for your session but only within your dynamic code execution so your next piece of dynamic code that grants is still executed in the context of current database.

You should always print your dynamic code before try to execute it and you'll see where the errors are.

But the best solution for your situation is to write once a plain(not dynamic) code for all databases, save it and run every time you need it.


Error installing Clustering with SQL Server 2012 on Windows Server 2012 We are installing SQL Server 2012 on Windows Server 2012 and trying to accomplish clustering.

We've setup all the core functionality on the servers: advanced cluster preparation on all nodes and then completion on the final node. However, when we get to completion phase it completes and at the end it throws an error.

Here are the error details:

  Feature: Data Quality Services
  Status: Failed: see logs for details
  Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail.
  Next Step: Use the following information to resolve the error, run RemoveNode on all the nodes of this instance, and then run PrepareFailoverCluster and then run CompleteFailoverCluster process again.
  Component name: SQL Server Database Engine Services Instance Features
  Component error code: 0x80131577
  Error description: The given key was not present in the dictionary.

I've tried rebuilding the system a few times and tried running the commands it referrers to with no avail. I feel like it's something simple that I'm just missing, any ideas anyone?

Install with the pre-SP1 SQL 2012 media.  Then, after your cluster is up, follow the procedure to apply SP1.  It appears the 11/2012 SP1 slipstream installer was not tested for this component when being installed from scratch.

This media worked:  en_sql_server_2012_enterprise_edition_x86_x64_dvd_813294.iso

This did not: SQLServer2012SP1-FullSlipstream-ENU-x64.iso


the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases? Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it.

quote from MySQL 5.7 Reference Manual  / GRANT Syntax

  REFERENCES    Enable foreign key creation. Levels: Global, database,
  table, column.

quote from PostgreSQL 9.6 / GRANT

  REFERENCES To create a foreign key constraint, it is necessary to have
  this privilege on both the referencing and referenced columns. The
  privilege may be granted for all columns of a table, or just specific
  columns.

is GRANT REFERENCES only about creating a foreign key constraint? In what business case does it make sense to forbid creating a foreign key constraint (but allow to create tables)? Can you give me real world examples?

If you have the ability to create a foreign key constraint, you are actually getting permission to check for the existence/non-existence of a certain value in a certain column (or set of columns) of a table. You may not have the privilege to SELECT the whole list of values in this column, but you can already know something about them. You could try to insert values on the referencing table. The ones that are allowed, you already know exist in the referenced table.

If you should know absolutely nothing about those values, you cannot be granted the privilege to make a foreign key constraint on the column.

Add to that the considerations pointed out by @Erwin, WRT to being able to prevent deletes from the referenced tables.


MYSQL: used variable on IN condition EDIT] The query below is not the actual query which I am having issues with performance. It is only a simplified query. As I only would like to know if it is possible to store multiple results into a variable and used that variable into the IN condition.

I had this query below inside a procedure  which I would like to change for optimization.

SELECT * FROM request WHERE facility_id IN (SELECT facility_id FROM facility);

What I would like to do was to store the subquery into a variable and used that variable on the main query.

SET @facilities = (SELECT facility_id FROM facility);

SELECT * FROM request WHERE facility_id IN (@facilities);

But I got an error Subquery returns more than 1 row.
How should I do it?
Any help is appreciated.

I have found some link that suggest that way http://www.mysqlperformancetuning.com/how-to-speed-up-mysql-by-optimization?nocache=1#comment-961. But I cannot figure it out.

You can use the GROUP_CONCAT aggregate function to produce a comma-delimited list of values:

SET @facilities = (SELECT GROUP_CONCAT(facility_id) FROM facility);

The result will be just a string value, however. If you use it like this:

SELECT * FROM request WHERE facility_id IN (@facilities);

you cannot expect to get a match on every ID in the @facilities list. This is because @facilities is not expanded into a list and instead is treated as a single item of the IN list. The commas inside it will be seen as just characters inside a string value, not as syntactic delimiters.

In order to use @facilities the way you want, you will have to build and execute a dynamic query around the value of @facilities. It could be something like this:

@stmt = CONCAT('SELECT * FROM request WHERE facility_id IN (', @facilities, ')');
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

This way the query will not contain a reference to @facilities – instead, it will have the contents of @facilities as its integral part, and the commas will thus be treated as part of the syntax.

Each query that you want to use the @facilities list in would have to be executed in the above manner. I think you will agree it is not very convenient. There are other considerations to keep in mind as well. When you use a dynamic query and concatenate the contents of a variable into the dynamic query as described, you may be open to SQL injection attacks. Also, with a large number of items in an IN list the performance may degrade.

I would suggest that instead of a variable you use a temporary table to store the IDs:

CREATE TEMPORARY TABLE tmp_facilities AS SELECT facility_id FROM facility;

A table is much more flexible in that you are not tied to just one syntactic structure. Although you could still use the temporary table in an IN predicate, as in your example:

SELECT
  *
FROM
  request
WHERE
  facility_id IN (
    SELECT facility_id FROM tmp_facility
  )
;

you could also use it in an EXISTS subquery:

SELECT
  *
FROM
  request AS r
WHERE
  EXISTS (
    SELECT * FROM tmp_facility AS f WHERE f.facility_id = r.facility_id
  )

;

or in a filtering join:

SELECT
  r.*
FROM
  request AS r
  INNER JOIN tmp_facilities AS f ON f.facility_id = r.facility_id
;

Having many options how to write your queries may be beneficial when you need to tune them for performance.


Update Grouped records I have the following query that returns duplicate values:

  SELECT
    [t].[Field1], [t].[Field2], COUNT([t].[Field2])
  FROM [dbo].[Table1] AS [t]
  WHERE [t].[Field2] <> ''
  GROUP BY [t].[Field1], [t].[Field2]
  HAVING COUNT([t].[Field2]) > 1

Given that statement, I'd like to write an update statement to clear Field2 under these conditions.  How would I write this?

You can do this much simpler using a CTE:

;WITH x AS
(
  SELECT
    Field1, Field2,
    c = COUNT(*) OVER(PARTITION BY Field1, Field2)
  FROM dbo.Table1
  WHERE Field2 <> ''
)
UPDATE x SET Field2 = '' WHERE c > 1;

Or with a more cumbersome (and potentially less efficient, did not test) self-join:

UPDATE t SET Field2 = ''
FROM dbo.Table1 AS t
INNER JOIN (SELECT Field1, Field2, c = COUNT(Field2)
            FROM dbo.Table1
            WHERE Field2 <> ''
            GROUP BY Field1, Field2
            HAVING COUNT(Field2) > 1) AS x
 ON x.Field1 = t.Field1
 AND x.Field2 = t.Field2;


Capture Data Change Vs Triggers I am constructing a database that needs to keep track of historical data. It is a Electronic medical system. All data needs to be updated in real time so the user can see what changes happened in the application.

Would CDC be the way to go or should I just have triggers on the database that pushes the information into a Audit tables

There is no definitive answer here because each solution has its own pros and cons.

CDC

Easier to setup and you don’t need to worry about creating triggers
and storing trigger data
Available only in SQL Server Enterprise edition so price may be an
issue

Triggers

More flexibility to adopt this to your needs (you can audit sql
server users, applications and more…)
More management and planning will be required upfront. You need to design the triggers, figure out how to store history data, how to do
reporting and probably more…

One of these two is enough – you don’t need to implement both.

If you decide to use triggers I’d recommend you find a third party tool that can generate triggers for you, create reports and such. I know that ApexSQL Audit can do this but note that it’s a premium tool (has a free trial though).


Archival copy of table, including overwrote data I have database server, with a database and table. The database and table can not be changed by me to ensure 3rd party support but the server configuration and MySQL configuration can be. On that table data is overwrote like so,

John Doe, red, car
Jane Doe, blue, boat

Incoming data John Doe, red, boat

results in

John Doe, red, boat
Jane Doe, blue, boat

A second database server, with a database and table that I can and need to change. Needs to contain a modifiable copy of the table but with the historical data. With the above example, it needs to contain,

John Doe, red, car
Jane Doe, blue, boat
John Doe, red, boat

Right now I have this working, but if you are wondering why I am posting this, it's because I don't like the current process. It's error prone. Right now I am using mysqlbinlog to read a binlog file from the 1st server in mixed mode and greping and awking and sed'ing the info I need and then changing the statements to UPDATE instead of REPLACE. Then I pipe that into MySQL. All on one line. This of course is far from ideal. No error checking, no daemon, no reconnection. If the source or destination server dies, all goes up in smoke.

Any ideas on a better method?

There are multiple ways you can solve this problem, but this would be the quickest and dirtiest.

You would create a trigger that executes during every change to a row inside the table you want to essentially "version". The trigger copies the change in information as a completely new row inside another table (I call them an audit table, I.E: users_audit). You would be creating the trigger on your replica (not your master). The replica would contain the new AuditLog table and in return would contain all the rows that were changed for the original table on your master. Of course you would need to version the rows, for which, I recommend using a TIMESTAMP column. This post below gives a good explanation of this technique:

https://stackoverflow.com/questions/12563706/is-there-a-mysql-option-feature-to-track-history-of-changes-to-records

Hope this helps.


Need Help Designing Tennis Ladder Database I'm creating a tennis ladder website using PHP/MySQL/CSS & HTML.  A tennis ladder is basically a system where you challenge a player and if you beat them you go one spot above them on the ladder rankings list.  Originally I designed the website so that it was a single ladder that users could register to join and then participate in.  That was up and running fairly well (in tests), but now I've decided I want to further expand it to allow multiple ladders.  Here's what I'm trying to accomplish:

A user can join many different ladders.
A ladder can have many different users.
Each ladder has a type, name, and description.
A ladder match will keep record of who won, who lost, and the score and date played of the match.
Stats will be collected for each player within that SPECIFIC ladder.

Here's what the tables for the ladder DB look like:

Here's a rudimentary visual representation of what some test data would look like when displayed.  This will change to represent a select ladder's data.

This is the first real web app I've attempted, so any advice on schema structure would be appreciated.  Originally when I had conceived of it as just 1 ladder that everyone could join I had combined the User table fields with the UserLadderStats fields, and there were no Ladder or Ladder_Users tables.  LadderMatch table has remained almost the same, but it now has foreign key references.

This looks good, except I would combine the Ladder_Users table with the UserLadderStats table. There's no reason to have them separate and it just introduces the chance for data integrity problems. Just move all the stats onto the Ladder_Users, and then you can drop UserLadderStats.

Also, note that the WinnerUserID and LoserUserID in LadderMatch are foreign keys, even though they aren't explicitly marked as such.


Why index on low selectivity column hurts query performance The table is simple:
CREATE TABLE `t1` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `name` int DEFAULT '1',
  `LastName` int DEFAULT '0',
  UNIQUE KEY `idx_ID` (`ID`) USING BTREE,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB;

DELIMITER $$
DROP PROCEDURE IF EXISTS populate_t1;
CREATE PROCEDURE populate_t1(IN num INT)
BEGIN
    DECLARE COUNT INT DEFAULT 0;
    WHILE COUNT < num DO
        INSERT INTO t1(`name`,`LastName`) VALUES(round(rand()+5, 0), round(rand()*10000, 0));
        SET COUNT = COUNT + 1;
    END WHILE;
END $$
DELIMITER ;

CALL populate_t1(1000000);
CREATE INDEX idx_name ON t1 (`name`);
OPTIMIZE TABLE t1;

The column name has very low selectivity (i.e., only 2 distinct values). Query
> SELECT * FROM t1 WHERE Name=5;
> OK, 499845 records, Time: 0.840s

would be slower than the query which use a full table scan
> DROP INDEX idx_name ON t1;
> OK
> SELECT * FROM t1 WHERE Name=5;
> OK, 499845 records, Time: 0.258s

This performance drop is expected, but I want to figure out why.
I know that the index is like an English dictionary index in the first several pages of an English dictionary. And the index is stored in a B+tree in my example I suppose. So with an index on column name, MySQL would know which rows satisfy name=5. Then MySQL fetches these rows into the result set. I think the process above would be quicker than scanning the whole table (but I am wrong). So what I want to ask is which process makes (e.g., loading index from disk to memory, searching in the B+tree, etc.) the index scan slower than the full table scan.
Further, I know that if the table is larger, the performance difference would be larger (I still want to know why).
I would be very grateful for your help!
Thanks!

Access to a row using a non-clustered index, which idx_name is, requires extra random I/O: b-tree lookup finds the clustered (primary key) index value, then you need to go and fetch the actual row from the clustered index.
The alternative is a sequential scan of the clustered index itself, which does not incur that extra I/O cost and is also simply more efficient by being sequential, not random.


Could I get some feedback on my first normalisation design? Im creating a database for a project with Olympics as my case study. I have drafted a rough table storing information I would like to store and display in my database below (an unnormalised table):

I have drawn up this diagram to understand the basic relationships between my current existing tables (Athletes, Events, Medals & Country). But I also see the need to create a Sports table as well as an Events table as different Sports contain different events. From trying to follow normalisation stages my current database design is looking as such:

Could I ask for some feedback on this structure? I'm having a hard time applying 2NF and 3NF for this database. Please note this is a school project. I'm not asking for any straight up answers. Just a guidance from anyone with a better head on them for databases than I do.

Community wiki answer:

A given athlete can play in multiple sports, so you should probably separate that. Also I do not understand the ranking part attached to countries. This should be at best a view, not a table.

As a general advice, do not store ages for individuals, but date of birth, because this does not change while the age changes each year.

You should just store the result (position) number for each athlete and competition, not medals that will be derived from it.

You also need to take into account ex-aequos. Also, even for a given competition, a given athlete may participate in multiple steps (semi-finals, final, etc.) so you may need to cater for that.


File size when extracting from SQL Server to CSV I am attempting to extract a table to CSV using BCP from SQL Server 2014 and the size of the file once the data is extracted is actually larger than the size of the database itself.

One thing to note is that one of the fields in this table is an nvarchar(max) and could contain values that exceed 8000 chars.

I was just wondering if this was normal behavior when extracting this type of data to file, or whether there was some trick that I could use to shrink the file size without any data loss.

Native format (-n) is the most compact, and best if you're moving from one SQL Server to another.  The files will also zip quite nicely.


How to break down MySQL big table into multiple to solve operational issue This is my current table schema in MySQL
we are not facing any performance issue in our application but there are operational issue .
The size of this table is 7 TB (6TB data and 1 TB index ) and has 4 Billions rows .
Because of the this one table having this big size we are not able to do any alter table on this table .
We have to use percona which takes 1 week to complete .
so to handle this we have decided to break this table in to 3 .
There are two columns which stores xml file that we want to move to separate table and this two column alone takes 2.5 TB of storage .
DETAILS longtext, and SUMMARY varchar(4000) DEFAULT NULL,
Along with it we also want to move few more columns to another table so that all three tables will become lighter
like third table we want to move USES_TYPE varchar(255) NOT NULL,  STEP_TYPE varchar(255) NOT NULL, NAME varchar(1500) DEFAULT NULL, and REMARKS varchar(1000) DEFAULT NULL,
CREATE TABLE `app_data` (
`ID` varchar(255) NOT NULL,
`USES_TYPE` varchar(255) NOT NULL,
`STEP_TYPE` varchar(255) NOT NULL,
`CUST_ID` varchar(255) DEFAULT NULL,
`DETAILS` longtext,
`DATE_TIME` datetime(6) DEFAULT NULL,
`GROUP_ID` varchar(255) DEFAULT NULL,
`SYSTEM_ID` varchar(255) DEFAULT NULL,
`NAME` varchar(1500) DEFAULT NULL,
`CUSTOMER_ID` varchar(255) DEFAULT NULL,
`REMARKS` varchar(1000) DEFAULT NULL,
`SUMMARY` varchar(4000) DEFAULT NULL,

PRIMARY KEY (`ID`),
KEY `IDX_APP_DATA_CID_OT` (`CUST_ID`,`USES_TYPE`) USING BTREE,
KEY `IDX_APP_DATA_SYSTEM_ID` (`SYSTEM_ID`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Now my final table will be comething like

Table one

CREATE TABLE `app_data_table1` (
`ID` varchar(255) NOT NULL,
`DETAILS` longtext,
`SUMMARY` varchar(4000) DEFAULT NULL;
)

Table Two would be like

CREATE TABLE `app_data_table2` (
`ID` varchar(255) NOT NULL,
`USES_TYPE` varchar(255) NOT NULL,
`STEP_TYPE` varchar(255) NOT NULL,
`NAME` varchar(1500) DEFAULT NULL,
`REMARKS` varchar(1000) DEFAULT NULL;
)

and table three

CREATE TABLE `app_data` (
`ID` varchar(255) NOT NULL,
`CUST_ID` varchar(255) DEFAULT NULL,
`DATE_TIME` datetime(6) DEFAULT NULL,
`GROUP_ID` varchar(255) DEFAULT NULL,
`SYSTEM_ID` varchar(255) DEFAULT NULL,
`CUSTOMER_ID` varchar(255) DEFAULT NULL,

PRIMARY KEY (`ID`),
KEY `IDX_APP_DATA_CID_OT` (`CUST_ID`) USING BTREE,
KEY `IDX_APP_DATA_SYSTEM_ID` (`SYSTEM_ID`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

I am new to data base but this is what i am coming up i know this is not optmised so my humble request is to pleae guide me on this.
Once we do this we need to use join to dispaly on UI or where ever we fetch so will that be slower ?

There are other things you could and should do, also.

Normalization.  For example, how many different values are there of GROUP_ID or SYSTEM_ID?  Rather than using a string, use a TINYINT UNSIGNED -- 1 byte and a range of 0..255, or maybe a SMALLINT UNSIGNED -- 2 bytes and 0.65K.  Etc.
Are any of the columns "rare"?  I mean are they NULL in many of the rows?  If so, design the split so that a new row is not a full 4 billion rows, but, instead, has only the needed rows.  Then use LEFT JOIN when you need to get NULLs back.
How were the UUIDs generated?  If they are standard, it is simple to compress down to BINARY(16) (16 bytes) from 36 bytes.
Don't blindly use (255), find a sensible, but conservative limit.  (This has a small impact on performance.)
What are the main queries now?  And after moving to 3 tables?  We should make sure the indexes are 'right' for all of them.  If the split is done 'wrong' certain queries will become much slower and cannot be fixed by indexes.
DETAILS takes about 2.5TB?  Probably that will shrink to less than 1TB if you compress it.  But do it in the client, not the server.  And then put the result in a MEDIUMBLOB.  And uncompress in the client after fetching.
Consider PARTITION.  It is unlikely to help anything.  However, if you purge "old" data, the process is much faster than DELETE.  It will not help with ALTER TABLE.
What do you use ALTER for?  There are some cheap tricks to perform for some tasks.
The PK being varchar(255) sounds awful; let's discuss alternatives.  Does any combination of other columns provide a unique key?
Do SELECT AVG(LENGTH(...)), AVG(LENGTH(...)), ... FROM t to see what is likely to be the benefit from each column.

"Alter is used mainly for adding column and adding index" --

MySQL 8.0 has faster ALTER operations.  Some are "INSTANT".  You need to look carefully at the details to see which are fast versus which need a copy.  And some flavors block other actions more than others.
If the only goal is to add a new column, consider adding a new table instead.  This is similar to your original idea but without any of the downtime.  It can also take advantage of having rows only where necessary (see my LEFT JOIN comment above).
But...  If you need to search the new set (with multiple tables), some queries can be efficient, some will be terribly inefficient.  Please provide some concrete examples of what you have done in the past or might do in the future; I will explain in more detail.  Perhaps start a new Question and show the old CREATE TABLE and the new one, together with the SELECT that involves the new column; then ask about performance.


Will Postgres handle the Unix Epoch problem? I was wondering if Postgres will be handling the unix epoch problem coming in 2038? I have read about this and am wondering.

It's about a productivity thing obviously because it is so far away, but I am curious.

If you look at https://www.postgresql.org/docs/11/functions-datetime.html in the documentation it displays data about the to_timestamp function that returns a timestamp with time zone value from an epoch value. Complete description is: "Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp"

The input type of the function is double precision.

From https://www.postgresql.org/docs/11/datatype-numeric.html you can see that double precision is defined as 8 bytes with 15 decimal digits precision.
A little down below you can read:

  The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits.

So 1E+308 seconds after 1970-01-01 should carry you far after 2038...

In fact if you look at https://wiki.postgresql.org/wiki/TodoDone84, which lists all features that have been done in PostgreSQL 8.4 you can see:

  Completed item: Extend timezone code to allow 64-bit values so we can represent years beyond 2038


SQL Server Stretch database, and archiving table to SQL Azure SQL Server 2016 Standard Edition

We have a generic need: A large, growing table.

For performance reasons, we want to move obsolete/old data out to an archive table.

SQL Azure would be the perfect home.

The question is:  Should we use a table in a "stretch" db (new in SQL 2016), or should we simply manage this in application code?

Stretch table could be good fit for your need. However i suggest to check the current limitations of enabling stretch feature.

https://msdn.microsoft.com/en-us/library/mt605114.aspx

The other manual way of doing this is partitioning the table and switching out to another table in the same DB.


Too many records on database and how to handle it? MySQL is it good to clone? I am doing a social network and came up with the following concern regarding database storage. Let's put facebook as an example, imagine 2,000 photos per user, just 1,000 users, it would equal to 2,000,000 rows in total assuming there is a row per photo.

Now imagine 100,000 users it would be two billion rows (in a single table?)

The problem is how to handle this?
With clones of the same table and then selecting by user id which is assigned to one sector?

suppose

photos_0
photo, date

photos_1
photo, date

and so on, then storing the suffix, 0, 1, etc along the user id and working from that point.

Is there any other better known solution?

//media

mysql_query("DROP TABLE IF exists media");
$vals='sbid int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(sbid),
vids varchar(200),
vidshd varchar(200),
vidso varchar(200),
duration varchar(200),
videow varchar(200),
videoh varchar(200),
notify varchar(200),
pics varchar(200),
picss varchar(200),
picsm varchar(200),
picsr varchar(200),
picsa varchar(200),
faces longtext,
descr longtext,
location varchar(200),
sbidv varchar(200),
id varchar(200),
albumid varchar(200),
albumn varchar(200),
norder int(200),
oldorder int(200),
title varchar(200),
shot varchar(200),
shott varchar(200),
nye varchar(200),
visibility varchar(200),
datetimep int(200),
datetimep_pp int(200)';
$vals=str_replace("
"," ",$vals);
$predefined='';
mysql_query("CREATE TABLE media ($vals) AUTO_INCREMENT=10000000;");

The above is the sample table,
would I go with storing up to billions of records on it, then updating and selecting from it normally where id='$uid' and $uid=theuser's id.

It is not the final table structure but I think it wouldn't be possible to use one table only so I thought about cloning and then when altering a table the need to alter all of the tables becomes necessary which sounds difficult to have to alter hundreds or thousands of tables using lots of queries whereas if it were one table only then it would mean a single query, that is one of the concerns I have about cloning but I also don't know if it is the actual solution to such eventual problem.

Partitioning and proper indexing plus do not forget proper multi server setup. Start from MySQL documentation on indexing and partitioning. When you hit such db size you should be able to afford somebody who knows what to do.

For big datasets you might want to look in to other solutions like Hadoop. Do not forget caching Redis/memcached.

Based of my experience with big data sets. Your table has too many columns, does not have proper indexing. So in case you do proper indexing and partitioning on production(properly setup) speed on indexed, partitioned columns would start degrade after 40,000,000-50,000,000
 per partition.


Why would increase in innodb_buffer_pool_size slow down MySQL? 5.1.68-cll - MySQL Community Server on CentOS

The system has 32GB of RAM.

I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB).

Time taken for a series of identical operations increased from 720 to 822 seconds (14% increase).

This was the result only a single test at each setting. But 4 previous tests performed a few months ago resulted in times between 726 and 740s.

I just tried running it again with 8GB, and the time taken was 719s.

Why would more memory result in a slower process?

EDIT: More details on process

The process that I'm testing involves emptying some tables and rebuilding them from data from existing tables. I'm not sure if it's using SELECT INSERT or if it's SELECTing the data, then using PHP to create long INSERT statements. If that matters then I can find out.

There are no schema definition changes being made.

Here is the output of numactl --hardware while the server is relatively idle:

root@server [~]# numactl --hardware
available: 1 nodes (0)
node 0 cpus: 0 1 2 3 4 5 6 7
node 0 size: 32740 MB
node 0 free: 6216 MB
node distances:
node   0
  0:  10

And free -m

root@server [~]# free -m
             total       used       free     shared    buffers     cached
Mem:         32081      25864       6216          0       2591      12791
-/+ buffers/cache:      10482      21599
Swap:        15994         16      15977

Edit by RolandoMySQLDBA

Please run this query

SELECT
    InnoDBSpace / POWER(1024,1) InnoDB_KB,
    InnoDBSpace / POWER(1024,2) InnoDB_MB,
    InnoDBSpace / POWER(1024,3) InnoDB_GB
FROM
(
    SELECT SUM(data_length+index_length) InnoDBSpace
    FROM information_schema.tables
    WHERE ENGINE='InnoDB'
) A;

RESULT:

InnoDB_KB InnoDB_MB InnoDB_GB
8413536 8216.34375 8.02377319335938

and this one

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages%';

RESULT:

Innodb_buffer_pool_pages_data
410035
Innodb_buffer_pool_pages_dirty
204
Innodb_buffer_pool_pages_flushed
826954
Innodb_buffer_pool_pages_free
99231
Innodb_buffer_pool_pages_misc
15022
Innodb_buffer_pool_pages_total
524288

During running of process:

root@server [~]# numactl --hardware
available: 1 nodes (0)
node 0 cpus: 0 1 2 3 4 5 6 7
node 0 size: 32740 MB
node 0 free: 5461 MB
node distances:
node   0
  0:  10

and:

             total       used       free     shared    buffers     cached
Mem:         32081      26658       5423          0       2603      12948
-/+ buffers/cache:      11106      20975
Swap:        15994         16      15977

From what you gave me thus far

The CPU has the illustrious task of accessing all 32GB RAM.
InnoDB's Buffer Pool is 8GB (524288 * 16384)

524288 (Innodb_buffer_pool_pages_total)
16384 (Innodb_page_size))

InnoDB Architecture

CONJECTURE #1

Since InnoDB allocates its buffer pool contiguously, one could only imagine if there is any form of fragmentation of data and index pages among other things in the Buffer Pool. What other things? There are also updates for secondary non-unique indexes posted first inside the Buffer Pool before they are merged into the System Tablespace file (better known as ibdata1). Please notice the Insert Buffer within the InnoDB Architecture.

CONJECTURE #2

While a larger buffer pool can reduce disk I/O, a buffer pool that's too large may have issues, such as the fragmentation issue I just mentioned.

Perhaps sparse memory, though contiguous, is not a good thing either. I discussed something of that nature back on Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?

I noticed the miscellaneous pages (Innodb_buffer_pool_pages_misc) within the buffer pool. That takes up 235 MB (16384 * 15022). Just some overhead for indexing and row locks. Notwithstanding, this can be victimized by a large buffer pool with lots of sizable empty gaps.

CONJECTURE #3

Data and index pages are not the only things that occupy the Buffer Pool. An annoying neighbor they must share space with is the Insert Buffer. That section of the Buffer Pool contains the changes for all non-unique indexes. Depending on the number of indexes each InnoDB table has, up to half of the Buffer Pool could be used. This should be a good reminder to look over all InnoDB table and remove redundant indexes

Here are other posts that describes how to hunt down redundant indexes and why to get rid of them:

Jan 26, 2012 : MySql - find redundant indexes
Dec 10, 2011 : Do I have duplicate key indexes?
Sep 07, 2011 : MySQL CPU Usage
May 17, 2011 : Do I need to add a new single column index to a table if a multi-column index on that field already exists?

Think about it: If a table has redundant indexes, INSERTs and UPDATEs to a table will results in rebalancing tree pages 45% of of the time (in the worst case scenario). Such tree rebalancing with happen multiple times for a table with a lot of indexes. Removing redundant indexes cuts down the time spent writing those changes to the Insert Buffer and in turn, to the Index Pages of each InnoDB table.

Increasing the Buffer Pool Size leaves room for the Insert Buffer to run amok if you have InnoDB tables with too many indexes, especially redundant indexes. Another telltale sign that you need to look for such tables is to run this query:

SELECT table_schema,table_name,data_length,index_length
FROM information_schema.tables WHERE engine='InnoDB'
AND data_length/(data_length+index_length) < 0.8;

This will give you a general idea which tables need to have the indexes looked over for redundancy of leading columns.

EPILOGUE

Keep in mind that the Buffer Pool holds

Data Pages
Index Pages
Insert Buffer for Index Changes
Adaptive Hash Index
Opened tables info
Miscellaneous Overhead
Refer to the InnoDB Architecture

Wasted space in a Buffer Pool could take more time to sift through, especially with a single core that is solely responsible for all of RAM.

My advice would be to keep the innodb_buffer_pool_size at 10G.


Show recent select queries on Firebird database file Is there a way to log or query the n recent SELECT queries to an FDB database file? I have already tried using the MON$ and RDB$ tables but I didn't find anything useful.

There is no history of queries in Firebird. As indicated by Verace in the comments, you can look at one of my answers on Stack Overflow that provides an alternative: using the trace API.

However the trace API requires active monitoring, which has some overhead. As an alternative you could also look at FBScanner, which acts as a proxy between Firebird and your client, which allows it to register the prepared and executed queries.


Connecting to database engine again when a new query or running view When I connect to a server

via SSMS, I can connect fine albeit a slow.

When I click on a new query or try to run a view in a database on this server, the same prompt comes up but with everything greyed out and then after a while the prompt disappears and the view runs or new query tab opens:

It's like SSMS is trying to connect to the server again every time I run something or even when clicking new query.  How do I stop this prompt appearing?

It's like SSMS is trying to connect to the server again every time I run something or even when clicking new query.

That's because it is. That's how it works, each connection is a new connection. The initial connection dialog you see if for object explorer. Each open "new query" tab is its own connection.

  How do I stop this prompt appearing?

It's appearing because there is either very high network latency or the pre-login/login times are long. Almost always it's due to very bad latency/undersized vms. Check your network round trip time to the server and check that the server is under heavy load or undersized.

Adding in Aaron's comments which are also on point:

  Could also be a not-very-efficient logon trigger or an expensive
  resource governor classifier function on that server (connecting via a
  local SSMS on the VM might rule this out). The error log might yield
  some clues too, or an extended events session that captures
  connection/log in information.


why user with db_owner & db_BackupOperator database role is unable to restore database? I'm in sysadmin server role on the server .
All Programmers are in dbCreator Server role , So they can create databases for themselves and they have in db_owner & db_Backupoperator database role for their own databases.

All programmer have remote desktop access to the server just like me . I would like every Programmer be able to backup and restore their own databases .

But When they are trying to restore , in Specifying back-up dialog , when user click on add button they get this error ( while i can restore ).

C:\Program files\Microsoft SQL Server\mssql10_50.sql2008\backup
Cannot access the specified path or file on the server .
Verify that you have the necessary security privileges and that the path or file exists

I appreciate any help .

"Their database" for the dbcreator means owner in the sys.databases.owner_sid sense
Not members of db_owner.
The actual rights needed are described under RESTORE (and my added bold)

If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).
RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.


SqlPackager.exe excluding users in report Using SSDT 2012 I generate the following to determine the changes required between two databases and end up with Diff.xml

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe" /Action:Extract /SourceDatabaseName:MyDb_TestData /TargetFile:MyDb_TestData.dacpac /p:IgnoreExtendedProperties=True /p:IgnorePermissions=False /p:ExtractApplicationScopedObjectsOnly=True
"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe" /Action:Extract /SourceDatabaseName:MyDb /TargetFile:MyDb.dacpac /p:IgnoreExtendedProperties=True /p:IgnorePermissions=False /p:ExtractApplicationScopedObjectsOnly=True

"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe" /a:DeployReport /sf:MyDb.dacpac /tf:MyDb_TestData.dacpac /tdn:MyDb_TestData /op:Diff.xml

A snippet looks as follows:

however I don't want to include SQL Users in the comparison. However in the EXTRACT and REPORT actions there does not appear to be any way to exlude these (although PUBLISH does). Setting IgnorePermissions to FALSE also does not do the trick.

Is there any way to exclude users somehow?

https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx#DeployReport Parameters and Properties

look into:

/p:DropObjectsNotInSource= {True | False}

/p:DoNotDropObjectType=Users

/p:ExcludeObjectType=users

/p:IgnoreUserSettingsObjects

Some combination of these will likely give you the Output you want.   Search that link for the specifics.   I had a very similar issue, in that SSDT Schema compare and SQLPackage.exe generated different results


Database Mail works in Production but not in Test Database I have a Production database that is able to send mail. And a Test that is not able to. They are both on the same SQL Server. The error is (SQL Server 2008):

  Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1
  EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I am an intern and have no previous experience with SQL server (just SQL DML, DDL). I tried to debug this by looking at the permissions of the 2 databases but they are the same. So I don't even know how people can send mail from the production.

I looked online and in order for users to be able to send mail they have to be a user under msdb and have the DatabaseMailUserRole. In my case it's a Windows domain. What's funny is that this domain isn't there, so how is production sending mail? I also tried putting the domain there as a user and gave them the permission, but still no luck for the test database.

How do I determine why/how the production database is able to send mail, so that I can debug? Thank you, I would appreciate any help.

Let's say it's too complicated to debug and I want to copy the production database to the test database (as an exact duplicate), will all permissions carry over?

To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database.

To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail.

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = '<user or role name>';
GO

Source: Microsoft docs  Permission denied on sp_send_dbmail


Storing large files (~1GB) in MySQL We are considering a solution that will store large files in MySQL with InnoDB. The size of the files can range from 250MB - 1GB (might grow up to 50GB in future). Here is what the plan is.

Create two tables similar to FILES(id, name) and FILE_PARTS(id, file_id, sequence int, data LONGBLOB).
Add a reference to each file in table FILES and 1GB chunks of the large file in the FILE_PARTS. This can theoretically let us store files of any size.
Wherever and whenever we need these files, we can get the parts and
join them together on the disk to get the original file.
We can also limit the amount of data in the FILE_PARTS by deleting old data. We should be able to limit ourselves within 100GB-200GB or so.

I want to understand what kind of limitations we might run into and what strategies can help us in getting this to work. Should we do a few more things? Are we missing something here? Is there a way to keep the FILE_PARTS table in a file of its own while the rest of the database is in its own file? How would database backups react?

Note: We know that storing files in relational databases is generally not recommended, but we have got some constraints in our organization that we got to live with. If you know of reasons as to why it would absolutely not work (being 5x inefficient is okay with us), please certainly let me know so that I can convince my folks here.

(OK, assuming that files bigger than 4GB must be stored in a table...)

Going past 4GB requires chunking of some form, since that is the LONGBLOB limit.

There are many settings that will trip you up.  The settings occur in MySQL server, mysqldump, MySQL replication, Apache, networking, various timeouts, etc, etc.  A 1MB chunk might be safe for most situations; 1GB is very likely to hit some limit that will be hard to get around.  max_allowed_packet, for example, has a hard limit of 1GB, but a practical limit much lower.  Anyway, there is not much code or speed difference between 200 chunks of 1GB and 200K chunks of 1MB.  In fact, smaller chunks might be faster.

When I implemented something similar a decade ago, I decided that a BLOB of 50KB was a good chunk size, and I compressed each chunk separately with client code.  My setup involved replication, so I was careful to insert each chunk separately so as not to hog the replication stream.

Shoveling 50GB around will take a lot of time.  Do not do everything in a single InnoDB transactions.  Instead, store chunks optimistically in separate transactions and worry about trash only if you crash before the final store of the master record.

Most image formats are already compressed, so it is a waste to re-compress such.

Yes, your 2 tables are approximately 'correct'.  No, I don't see a 5x problem.  However, there might be 2x degradation over simply storing as a file.


How to prevent SSDT publishing from dropping columns I want to create a publishing profile that DOES a complete schema comparison and publishing BUT does not drop any tables or any columns that have been removed between the old and new version.

I am aware of the BLOCK possible data loss option in the settings. AFAIK this stops the publishing process in case DATA will be lost. So the question is, will the schema upgrade process be interrupted (in the middle of nowhere?) or will it be completed but the affected tables will be excluded?

IF a table has added and removed columns, will the new columns be added to the schema although the blocking of dataloss option stops the schema update because of the potential data loss?

EDIT 09.08.2016:

I wanted to add those links to complement the question with additional information about the backgrounds:

MS Connect entry to this BUG
MS Social discussion about this BUG

There are three parts to this Question, so let's start with the "Block on Data Loss" option, dropped Tables, and then dropped Columns.

"Block on Potential Data Loss" Option

If you enable the "Block incremental deployment if data loss might occur" option:

on the "Debug" tab of "Project Properties"
using the /p:BlockOnPossibleDataLoss=True command-line option
specifying <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss> in a publish config file

then SSDT will script out statements for each affected table, such as the following:

/*
The column [dbo].[Table1].[New2] is being dropped, data loss could occur.
*/
IF EXISTS (select top 1 1 from [dbo].[Table1])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO

/*
Table [dbo].[DontDropMe] is being dropped.  Deployment will halt if the table contains data.
*/
IF EXISTS (select top 1 1 from [dbo].[DontDropMe])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO

These statements are placed into the incremental** deployment / publish script ahead of all other changes. This causes the publish process to stop completely if any rows are found in any of those Tables. Hence, there is no such thing as a partial deployment when using the "Block on Data Loss" option.

** "Create" scripts always drop and recreate the Database, so there won't be any data to lose ;-).

Tables Dropped from the Model but still in the Target

By default, Objects (and Columns are not Objects) are not dropped from the Target. Objects that exist in the Target but are missing from the Model are only dropped if you:

enable the "DROP objects in target but not in project" option on the "Debug" tab of "Project Properties"
use the /p:DropObjectsNotInSource=True command-line option
specify <DropObjectsNotInSource>True</DropObjectsNotInSource> in a publish config file

If you are using the "Drop Objects not in Source" option but want to prevent the dropping of just Tables, you can exclude them by:

going to "Advanced Deployment Settings" (the "Advanced..." button on the "Debug" tab of "Project Properties"), then going to the "Drop" tab, and enabling the "Do not drop tables" option (in the scrollable list).
using the /p:DoNotDropObjectType=Tables command-line option
specifying <DoNotDropTables>True</DoNotDropTables> in a publish config file

Columns Dropped from the Model but still in the Target

This one is a bit trickier since Columns are not objects, and there is no specific deployment option to ignore this particular situation. Here are your options as best as I can tell:

Ignore tables entirely. You can do this by:

going to "Advanced Deployment Settings" (the "Advanced..." button on the "Debug" tab of "Project Properties"), then going to the "Ignore" tab, and enabling the "Exclude tables" option (in the "Excluded Object Types" scrollable list).
using the /p:ExcludeObjectType=Tables command-line option
specifying <ExcludeTables>True</ExcludeTables> in a publish config file

The downside here is that no new Columns or new Tables will be published. You can still manage those yourself by adding deployment scripts to the Project and setting their "Build Action" (a property of the SQL script) to "PreDeploy".
Create a "Deployment Contributor", which is a means of writing code to the SSDT API, to control how the DDL generation

Main page: Customize Database Build and Deployment by Using Build and Deployment Contributors
Example: Walkthrough: Extend Database Project Deployment to Modify the Deployment Plan

This is a very flexible and powerful option, especially if your religion practices self-flagellation ;-).

UPDATE: Even better, Ed Elliot published a blog post in which he details how to use a Deployment Contributor: Inside an SSDT Deployment Contributor. Even betterer, the example he used is to do just this: prevent the dropping of columns that are not in the Model. Even bestest, the example code has actually been incorporated into his
AgileSqlClub SqlPackage Deployment Filter project, and the feature is described in the following blog post: Deployment Contributor KeepTableColumns Filter. And, if the feature doesn't do exactly what you need, then the source code is available and you can make any changes you want and recompile.

Don't use SSDT for Schema changes. Just use it to push code (Stored Procedures, Functions, Triggers, SQLCLR Objects, etc). You can write your own schema deployment scripts and include them in the Project. You can have them included in the deployment scripts by setting their "Build Action" to "PreDeploy".
File a Connect Suggestion to have an option added for "DoNotDropColumns" or something like that :-)


MySQL server taking multiple seconds to run COUNT(*) query using an index on a MyISAM table I have a MySQL database on a shared webhost, which has been running with its application for a couple years.

Today, this simple query:

SELECT COUNT(*) FROM tickets WHERE id = 123

Took 16 seconds to run. There is an index on id, the table is MyISAM, and this query usually takes milliseconds. The table has about 1 million rows, and the query should return a COUNT of about 250,000.

SHOW PROCESSLIST shows that all the time is being spent Sending data. I don't have root access, so no profiling help.

I have the same database on another server from the same host, and the query executes in 0.06 seconds.

The host thinks everything is running normally on the server. Is there anything I can point them at?

mysql> EXPLAIN SELECT COUNT(*) FROM tickets WHERE id = 214;
| 1 | SIMPLE | tickets | ref | id | id | 2 | const | 215307 | Using index |

The problem appears to have been that the cardinality of my indices was NULL. I am unsure why the indices would revert to this state when they were apparently fine yesterday.

I discovered this problem by running SHOW INDEX FROM tickets.

I ran ANALYZE TABLE tickets. Now the EXPLAIN looks subtly different-- the rows have dropped to 1:

mysql> explain select count(*) from tickets WHERE id = 214;
|  1 | SIMPLE      | tickets | const | PRIMARY       | PRIMARY | 2       | const |    1 | Using index |


Granting Roles on Group Of People Is there anyway to grant specific roles on group of people such as :

Grant SELECT ON Jsmith To (SELECT user_name FROM dba_users);

However, this doesn't work.

Thanks in advance,

begin
  for u in (select username from dba_users)
  loop
    execute immediate 'grant select on jsmith to "' || u.username || '"';
  end loop;
end;
/


MySQL Select additional rows if initial query has specific column value returned I have this sample DB table:

id  type    c_owner_id  c_linked_id
1   parent  1           2
2   parent  1           3
3   related 2           3

My script is provided a c_id. I SELECT out all rows where this c_id is the c_owner_id OR c_linked_id.

What I would like is to have in the same query an additional SELECT if the c_linked_id equals the provided c_id AND type equals 'parent' that would select the rows that have a relationship with the same parent.

Pseudo SQL would be:

SELECT * FROM
    table_name AS orig
WHERE
    orig.c_owner_id = $c_id
    OR orig.c_linked_id = $c_id
IF
    orig.type = 'parent' AND orig.c_linked_id = $c_id
THEN
    (
        SELECT * FROM
            table_name AS sub
        WHERE
            sub.type = 'parent'
            AND sub.c_owner_id = orig.c_owner_id
    )

Is this type of "IF THEN SELECT" statement possible in MySQL?

Conditional inclusion of rows can be emulated using UNION and JOIN to the original table:

http://sqlfiddle.com/#!9/80555b/5

SELECT
    orig.*
FROM
    relationships AS orig
WHERE
    c_owner_id = $c_id
    OR c_linked_id = $c_id

-- this will add some additional rows where orig rows would match the IF cond
UNION

SELECT
    sub.*
FROM
    relationships AS sub
JOIN
    relationships AS orig ON sub.c_owner_id = orig.c_owner_id
WHERE
    sub.type = 'parent'
    AND orig.type = 'parent'
    AND orig.c_linked_id = $c_id;

You can define specific ordering for the added rows to come after the right "main" rows. You can only order the resultset of entire UNION as ORDER BY in unioned parts has no meaning.


Set Identity as (11 - 2) in sql table I have a issue,

I want to set the ID column, where the ID should start from 11-2 then it should auto- increment by 1 like

11-3, 11-4.

I am using sql-server 2008 R2. Please help

Use an IDENTITY column for the incrementing ID value, then prefix it with 11- as a computed column.

CREATE TABLE [Table2]
    (
    ID int IDENTITY(1,1),
    ID_pretty AS ('11-' + CAST(ID AS nvarchar))
    CONSTRAINT pk_ID PRIMARY KEY (ID))
GO


SQL Server Primary key / clustered index design decision Looking for some advice regarding a table / index design decision I've got to make on some tables that I've got to port into SQL server from an existing 4GL based database.

I've got a product history table that is inserted into frequently (never updated) and the table has this kind of structure

ProductNo        String(20)
CreatedDateTime  DateTime
Description      String(100)

At the moment the primary key is made up of a combination of ProductNo and CreatedDateTime in an attempt to define a unique index key. We can have many records per productno.

I'll be creating some 1 to 1 related tables and don't want to carry both the productno and the createddatetime fields into the related tables to act as foriegn keys. I also think this combination is a little fragile in order to guarantee uniqueness.

So, I'm planning to add a new field to the table 'ProductHistoryPK' as an incrementing Int or SequentialGuid to act as the primary key and a foreign key to related tables.

In terms of indexes I'm thinking of creating

Non-clustered primary key on the new ProductHistoryPK field.
Clustered Index on the ProductNo field as this is field that is
often searched on.

Any thoughts or pointers regarding this?

Thanks...

You are correct to separate "clustered index" from "primary key":

A clustered index is the organisation of data on disk is better if

narrow
numeric
increasing (strictly monotonic)

The primary key identifies a row

Note: GUIDs make poor clustering keys

In this case, with the surrogate column, the table has 2 candidate keys:

ProductHistoryID
ProductNo + CreatedDateTime

Assumed convention states that the ProductHistoryID becomes the PK, but you can leave the PK on (ProductNo, CreatedDateTime): it will just be non-clustered. Which leads to indexes:

clustered index should be on ProductHistoryID
unique non-clustered index on (ProductNo, CreatedDateTime)

Example

CREATE TABLE Product (
    ProductHistoryID int NOT NULL IDENTITY (1,1) NOT NULL,
    ProductNo ...
    CreatedDateTime ...

then you a choice of

    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductHistoryID)
    CONSTRAINT UQ_Product UNIQUE NONCLUSTERED (ProductHistoryID)

or

    CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductNo, CreatedDateTime)
    CONSTRAINT PK_Product UNIQUE CLUSTERED (ProductHistoryID)

Also, the pattern you have is a "type 2 Slowly Changing Dimension"


mysql need the mysql.timezone* tables to be filled properly for explicit and implicit TZ conversions. They are not created and filled automatically on installation.

Install fresh timezones on your host. Usually they are provided by zoneinfo package.
Run mysql_tzinfo_to_sql /path/zoneinfo | mysql -u root mysql

Refer the mysql manual for further information: https://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html


SQL Server 2008 and extended properties In SQL Server, what do you guys use extended properties for? Some articles and blogs are suggesting they be used for self-documentation of the database and its objects but I can't see this being used properly... is this largely ignored by most?

Largely ignored, as you're saying.

Extended properties are a bit awkward to read and write both from T-SQL and GUI. IMHO documentation should be maintained elsewhere (database projects, project documentation etc.).

Here's a good article on extended properties, which also addresses some of your doubts.


Restore of database using Mysql DUmp How would i verify that whatever the backup i have taken is correctly restored or not.

EG:

I am taking backup on live system where inserts are going on.backup size is around 50 GB.I have started the restore of the backup but for some reason i have not enable the log and also my session end.So how i will verify that restore was done successfully.

Before taking the backup i have even not calculated the data_lenght,index_lenght and tables row.So is there any way so that i can confirm that restore is successful

regards
Suraj

You can check if the return value for the command, if 0 then OK else failure.

Put may put it in shell script as follows:

 mysql < dump.sql 1>err 2>&1
 if [ $? -ne 0 ]; then
    echo "dump load failed.";
 fi;

or manually run echo $? and determine the result.


What is the meaning of this operator: *= I have a very old Access application that uses SQL Pass-Through Queries.  This application has been nursed along through multiple server upgrades, and we recently moved up to SQL 2012.

I now get a syntax error.  The last two lines of the code are where the problem is.  I believe it is some kind of JOIN, but I've never seen this syntax.  Removing the "*" allows the query to run, but leaves out data.  Is there a easy replacement of this syntax or do I need to redesign these queries using the JOIN phrasing?

SELECT survey.hole,survey_date,pit,cu,rec,zn, mn,north,east,elevation,
       rtrim(min_type) min_type,rtrim(rock_type) rock_type,
       rtrim(alt_type) alt_type, rtrim(min_style) min_style
FROM survey,assay,geology
WHERE  mined_out IS NULL
AND pit = (SELECT fmpit FROM tyrctl WHERE source = 'export by date')
AND  (survey_date >= (SELECT fmdate FROM tyrctl WHERE source = 'export by date')
AND survey_date <= (SELECT todate FROM tyrctl WHERE source = 'export by date'))
AND survey.hole *= assay.hole
AND survey.hole *= geology.hole

Your query is using the pre-ANSI (and possibly original) JOIN syntax. Back in "the day", joining tables was done by specifying all of the tables, separated by commas, in the FROM clause, and then specifying the join condition(s) in the WHERE clause. Equating the current ANSI style with the old style gives you the following chart of options:

[INNER] JOIN: TableA.Field = TableB.Field
LEFT [OUTER] JOIN: TableA.Field *= TableB.Field
RIGHT [OUTER] JOIN: TableA.Field =* TableB.Field
 FULL [OUTER] JOIN: TableA.Field *=* TableB.Field
CROSS JOIN: {nothing specified to relate the two tables}

The pre-ANSI syntax is / was supported (sometimes with slight variation) by the various RDBMSes. However, it has been deprecated for a while in Microsoft SQL Server, and was officially removed in SQL Server 2012 (as you are now seeing). This was noted in the Discontinued Database Engine Functionality in SQL Server 2012 MSDN page (2nd to last row in the grid).

In your query, you have 3 tables:

FROM survey,assay,geology

and two LEFT JOIN conditions:

AND survey.hole *= assay.hole
AND survey.hole *= geology.hole

But yes, you can rewrite it as follows:

SELECT sy.hole, survey_date, pit, cu, rec, zn, mn, north, east, elevation,
       rtrim(min_type) AS [min_type], rtrim(rock_type) AS [rock_type],
       rtrim(alt_type) AS [alt_type], rtrim(min_style) AS [min_style]
FROM survey sy
LEFT JOIN assay ay
       ON ay.hole = sy.hole
LEFT JOIN geology gy
       ON gy.hole = sy.hole
WHERE  mined_out IS NULL
AND pit = (SELECT fmpit FROM tyrctl WHERE source = 'export by date')
AND (
      survey_date >= (SELECT fmdate FROM tyrctl WHERE source = 'export by date')
  AND survey_date <= (SELECT todate FROM tyrctl WHERE source = 'export by date')
    )

Technically, you don't need the parenthesis around the two survey_date conditions in the WHERE clause as they are all AND conditions, but if you prefer it for readability then it is fine.

As @ypercube pointed out in a comment on this answer, the query can be further simplified to since the 3 subqueries in the WHERE clause all reference the same row of the same table. So that could look at follows:

SELECT sy.hole, survey_date, pit, cu, rec, zn, mn, north, east, elevation,
       rtrim(min_type) AS [min_type], rtrim(rock_type) AS [rock_type],
       rtrim(alt_type) AS [alt_type], rtrim(min_style) AS [min_style]
FROM survey sy
INNER JOIN tyrctl tl
        ON sy.survey_date >= tl.fmdate
       AND sy.survey_date <= tl.todate
       AND sy.pit = tl.fmpit
       AND tl.source = 'export by date'
LEFT JOIN assay ay
       ON ay.hole = sy.hole
LEFT JOIN geology gy
       ON gy.hole = sy.hole
WHERE  mined_out IS NULL;


How to INNER JOIN and OUTER JOIN within the same table? I am using PostgreSQL and I have the following tables:

Product

ID | Name | Price

Receipts

ID | Month | Quantity | Product_ID (FK)

Sales

ID | Month | Quantity | Product_ID (FK)

I am trying to come up with a query that displays something like this:

    Month | Product.Name | Amount received | Amount sold

It is very important to keep in mind that there can be more than one receipt or sale of the same product on the same month. I need to come up with a query that tells me the amount received and the amount sold of a particular product on a each month.  It is possible that a product is not sold in a month (that's why the OUTER JOIN below).

The best that I've got so far is this:

    SELECT s.month, p.name, r.quantity, SUM(s.quantity)
    FROM product AS p
    INNER JOIN receipts AS r
       ON p.id_product = r.product_id
    LEFT OUTER JOIN sales AS s
       ON p.id_product = v.product_id
    GROUP BY s.month, p.name, r.quantity

Let's say in the same month there were 2 receipts of keyboards. The first one with 100 keyboards and the second one with 50 keyboards. Also, there was a sale of 10 keyboards and then another one of 20 keyboards.

An example of what is returned by this query:

     Month | Product.Name | Amount received | Amount sold
       6   |   Keyboard   |       100       |    30
       6   |   Keyboard   |       50        |    30

This one works fine when there are mutiple sales (apart from the repetition) but not when there are multiple receipts. I was thinking that I should relate s.month and r.month with an INNER JOIN, but I don't know how.

Sorry for the bunch of information at once. If there is anything unclear, let me know.
Can anyone help me out?

Thanks!

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors.

Something like this will work:

SELECT
    m.month,
    p.name,
    COALESCE(r.receipts_quantity, 0) AS receipts_quantity,
    COALESCE(s.sales_quantity, 0)    AS sales_quantity
FROM
    product AS p
  CROSS JOIN
    ( VALUES (1),(2),(3),(4),(5),(6) ) AS m(month)     -- months wanted in the report
  LEFT OUTER JOIN
    ( SELECT month, product_id, SUM(quantity) AS receipts_quantity
      FROM receipts
      GROUP BY month, product_id
    ) AS r
    ON  p.id_product = r.product_id
    AND m.month = r.month
  LEFT OUTER JOIN
    ( SELECT month, product_id, SUM(quantity) AS sales_quantity
      FROM sales
      GROUP BY month, product_id
    ) AS s
    ON  p.id_product = s.product_id
    AND m.month = s.month ;

If you want to avoid having results for products that did not have any activity in a month, you can add the condition:

WHERE r.month IS NOT NULL
   OR s.month IS NOT NULL


Do any SQL dialects permit the logical sequence of SELECT clauses? SELECT in ISO/IEC-standard SQL prescribes the following syntactical order for the sub-clauses:

SELECT
    projection-expressions
FROM
    sources
WHERE
    predicate-expression
GROUP BY
    key-expression
HAVING
    predicate-expression
ORDER BY
    ordering-expressions

While the logical execution order is this:

FROM
    sources
WHERE
    predicate-expression
GROUP BY
    value-expression
HAVING
    value-expression
SELECT
    projection-expressions
ORDER BY
    ordering-expressions

To novice users of SQL it becomes surprising that a projection defined in the SELECT clause is not available in the WHERE or GROUP BY clauses even though it's declared first - given that computer programs generally follow the top-down execution order.

It's also surprising that SQL authors are required to repeat their expressions  in SELECT, WHERE, and GROUP BY clauses redundantly or use a subquery which does not lend itself to a succinct query. At least when the user is familiar with the actual clause execution order they know why they need to repeat themselves, but that doesn't stop it being frustrating.

This problem, and other related issues, is documented in this article I found: https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ and it's no surprise that a QA on StackOverflow has almost 30,000 views: https://stackoverflow.com/questions/3241352/using-an-alias-column-in-the-where-clause-in-postgresql

It made me wonder if any implementations of SQL allow for this more "logical" ordering of clauses. I note that Linq in .NET does actually follow this order, though I wouldn't describe it as being a true SQL implementation, but indeed, in Linq the equivalent would be:

source // FROM equivalent
    .Where( predicate-expression )
    .GroupBy( key-expression )
    .Where( predicate-expression ) // HAVING equivalent
    .Select( projection-expression )
    .OrderBy( ordering-expression )

(I also like how Linq lets you add a Select() projection anywhere in the command sequence so you can use your evaluated expressions without needing to invoke expressions again).

So, do any SQL implementation let you express queries in a more logical structure?

implementations of SQL

No, SQL is standardized. There aren't vastly different implementations of a standard. That kind of defeats the purpose. There was/is always a fight over the relational-calculus (which SQL pretends to be) vs relational-algebra approach. It an old debate,

Having selected a model for the data, our next step was to
select a query language. Two families of higher level data sublanguages
for a relational data base are based on, alternatively, the relational
algebra (derived from the algebra of sets) or the relational calculus
[Codd, 197lb , 1971c and Date , 1975] (derived from the predicate calculus). Codd [1971c] and Date [1975] have compared these two and found the relational calculus to be superior, particularly for use as a target language for a - natural language system. The main reason for their choice of the relational calculus as a target language was that the calculus is non-procedural; i.e., a query in the relational calculus conveys little information about how to proceed in searching the data base. The relational algebra is more procedural which makes the automatic construction of a query by a natural language system somewhat more difficult. - Implementation of a Query Language based on the Relational Calculus 1970

And, again from Codd himself,

Early in the development of the relational model (1969-1972), I invented
two languages for dealing with relations: one algebraic in nature, and one
based on first-order predicate logic [Codd 1971a]. I then proved that the
two languages had equal expressive power [Codd 1971d], but indicated that
the logic-based language would be more optimizable (assuming that flow
tracing was not attempted) and easier to use as an interface to inferential software on top of the DBMS. - Codd 1990, The Relational Model For Database Management Version 2

There are certainly a ton of implementations to both approaches. That said, SQL as an implementation is pretty fixed on the calculus approach.
LINQ problems
These problems never really went away with the algebraic aproach, take for instance from this blog,.
context.Cars
  .OrderBy(x => x.Id)
  .Skip(50000)
  .Take(1000)
  .ToList();

Requires hand-optimization as,
context.Cars
    .Where(x => context.Cars
        .OrderBy(y => y.Id)
        .Select(y => y.Id)
        .Skip(50000)
        .Take(1000)
        .Contains(x.Id)
    )
    .ToList();

If you have multiple different relations with different performance characteristics (indexes, most of which are implemented as indirect and abstract) and statistics, it's just hard to write procedural set logic that works efficiently.


Requirements for IIS server used for web sync, merge replicaiton Short question: Can anyone tell me what the requirements (especially when it comes to SQL components) are for the web server used as web sync for merge replication?

Background:

I have a solution which uses merge replication to do one-way data sync with the client application of the solution.

The server uses SQL Server 2008, the client SQL Server 2008 Express and initiates the pull subscription using RMO. The request goes through Web Sync. All is fine when the IIS and the DB is on the same server - the problem occurs when the IIS is on a standalone web server.

From the error logs, it seems certain SQL components are required on the web server to make this work - but I haven't been able to find which ones. I've tried installing SQL Server 2008 Express on this server, with no luck.

So: Can anyone tell me what the requirements (especially when it comes to SQL components) are for the web server used as web sync for merge replication?

As mentioned in How to: Configure IIS for Web Synchronization, SQL Server connectivity components must be installed on the computer that is running IIS.  Also, If you use the Configure Web Synchronization Wizard (which is recommended), you'll also need to install SQL Server Management Studio on the computer that is running IIS.


Why does restore take a long time? Although, the database backup file is only 2GB, the restore can take up to 15-20 minutes even on a x64 12G RAM, i7 machine. When I backup similar sized backup files made from a different database, the restore finishes in 3 seconds.  The backup file contains only one backup set (I always check overwrite existing data option).  On other slower machines it takes even longer to restore.

What could be causing the restore to take so long?

The backup size is simply used 8k pages. These 8k pages are part of the MDF. On restore, the MDF and LDF files may need recreated (if already existing and different in some way) or just created

So backup file size isn't an indicator of space used on disk by the restored database. And this ignores backup compression too

So, if the MDF is 100GB (with 2GB actually used), : unless you have Instant File Initialisation on, then the 100GB needs zeroed. Note the LDF must be zeroed.


Error statement is not logged, only *password---- with many dashes I use extended events to store database errors, like this:

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        [severity] >= (11)
    )
)
ADD TARGET package0.asynchronous_file_target
(
    SET filename='J:\ServerXmlOutput\ErrorCapture.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

This works great.
Sometimes, the SQL statement (sql_text) that is logged contains only the following:

*password------------------------------------------------------------------------------------------------------------------------------------------

What is the reason, and how do I find out which statement is causing this?

It is confirmed that this is caused by a column called password or an argument @password.

A column called abc_password does not cause this behaviour.


Sending SQL Server query as CSV via e-mail Currently I'm using SSIS w/ a hacky solution to get around it not supporting auth on SMTP servers.

Is there a better way I can save a query to a CSV file, then mail it as an attachment?

Thanks

Use database mail to send the email as that supports attachments and auth on SMTP.


Database design for students and families/parents I'm designing a database to hold our students and their parents/guardians. Heres what I have so far:

Conditions

I need to identify student siblings (ie; brothers/sisters that are part of the same family)
A family can have an unlimited number of guardians
Brothers/Sisters always have the same set of guardians
Guardians may share the same address
Guardians may have no address, could just be an email or phone number

My joining table [families] seems a little redundant to me. Is this the best way to do it? Even though its sparse now I can see us having to add family level information in the future. For example invoices are sent out on the family level, not per student or guardian.

Should I store email/phone numbers in a seperate table?

How about creating a separate Table called Person ? You will need separate tables for storing Guardian or Student related information and they both can refer to person Id.

Instead of family you can have relation table that joins two different Person with Extra column of RelationshipType (and of course another lookup table.)

Address table can connect to person table as well and same as Address you can have another table for contact details as well with contact type lookup table that stores values like phone number, email, mobile, work email, work phone etc.

This will also help you in scenarios where an ex student becomes a guardian.

Hope that helps.

Maulin Thaker


Calculating total "on row" bytes for each row ... the easy way We want to calculate the total "on row" storage bytes for each row in the table.  As we understand it, we must add up the DATALENGTH() of each column while also accounting for NULLs and things like VARCHAR(MAX) which only have a 24-byte pointer "on row".  We are aware there is also some overhead for each row which is not accounted for in the query below.

SELECT   ROW_ID,

         CASE
              WHEN COLUMNPROPERTY(OBJECT_ID('EXAMPLE_TABLE'),'COL1','PRECISION') = -1 THEN 24
              ELSE ISNULL(DATALENGTH(COL1), 1)
         END
          +
         CASE
              WHEN COLUMNPROPERTY(OBJECT_ID('EXAMPLE_TABLE'),'COL2','PRECISION') = -1 THEN 24
              ELSE ISNULL(DATALENGTH(COL2), 1)
         END
          +
         CASE
              WHEN COLUMNPROPERTY(OBJECT_ID('EXAMPLE_TABLE'),'COL3','PRECISION') = -1 THEN 24
              ELSE ISNULL(DATALENGTH(COL3), 1)
         END
          +
         ...
         ...
         AS ROW_SIZE

FROM     EXAMPLE_TABLE

ORDER BY ROW_SIZE DESC
;

What a beast!  And it's only an approximation.

Then we discovered

DBCC SHOWCONTIG ('EXAMPLE_TABLE') WITH TABLERESULTS

which returns MaximumRecordSize.  This reveals that there is already an algorithm buried somewhere within SQL Server which is capable of calculating the exact size of a row.

How can we access that algorithm directly?

How can we access that algorithm directly?

In so far as the answer to this question, specifically, you can't access it directly. There is nothing where you can say SELECT GetMeMaxRowSize(MyTable, MyPartition, MyIndex).

However, as LowlyDBA has pointed out, you can use sys.dm_db_index_physical_stats to give you more information than ShowContig. The quite interesting thing is that if you run DBCC SHOWCONTIG(), capturing deprecated information, you should see a message to use the aforementioned DMV in place of the ShowContig command.