How to Use OPENROWSET function within a Transactional SSIS Package I have an SSIS Package and one of its components is "Execute SQL TasK"
and I've written an OPNEROWSET query inside this task.  When I configure the transaction option of the whole package from "Supported" to "Required" I receive this error:

  The requested operation could not be performed because OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" does not support the required transaction interface.

When you want to configure the Transaction in SSIS you have to run the service
"Distributed Transaction coordinator" and it seems that this service is in conflict with openrowset. What should I do?  Has anybody come across this issue?

You can turn off DTC Promotion for a linked server

EXEC master.dbo.sp_serveroption @server=N'LincServer', @optname=N'remote proc transaction promotion', @optvalue=N'false'

And allow non-transacted updates for the linked server provider:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1

Which might resolve this issue.  Note for the first one you'd need to create a linked sever definition, instead of using the ad-hoc version of OPENROWSET.

But using ACE from a linked server in an SSIS package is a strange thing to do.  SSIS should connect directly.

From the ACE download page:

  The Access Database Engine 2016 Redistributable is not intended:  . .
  .  To be used by a system service or server-side program where the
  code will run under a system account, or will deal with multiple users
  identities concurrently, or is highly reentrant and expects stateless
  behavior. Examples would include a program that is run from task
  scheduler when no user is logged in, or a program called from
  server-side web application such as ASP.NET, or a distributed
  component running under COM+ services.

Microsoft Access Database Engine 2016 Redistributable

So it's better to use ACE from a short-lived process, possibly running under a regular user.  For SSIS package execution uses short-lived processes, and you can control the identity of those processes.


Is this db/table structure sensible? I'm not very experienced in database design so bear with me.

I've taken over a ruby/rails app. with a connected MySQL/innodb database. As I'm updating the server to the latest ruby/rails version I also wanted to look at the DB structure. My main concern is a specific database design decision which makes it very hacky to use with rails and I wanted to get some external input on this.

We are tracking spatial data for various systems from customers every x minutes which results in a lot of data. We don't have that data in one big "positions" table but split into a table for each system and each year. So e.g. for a system with id=1 and in the current year we have a "positions_1_2015" table.

So for, like, 1,000 systems over 3 years we already have 3,000 tables which seems a lot to me. When I want to change a column I have to change each single table instead of just one. I also have to do some hacking for this mechanism to work with rails activerecord logic.

Right now, summed up, we have about 300,000,000 positions from various customers. They are write once/read often and by multiple concurrent users.

So my question: is this table splitting a sensible design?

The argument from my clients is that they can more easily backup/archive all data and a lot of small tables are more manageable than one single huge table. But above all each client has access to their specific tables so that performance should be better when 100+ customers want to access their spatial data at the same time.

So is this true? Will performance be worse if Ihave 100+ concurrent users on a single big 300M row table?

Thanks for your help!

Edit: SHOW CREATE for one position table

CREATE TABLE `positions_1000_2015` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `world_x` double DEFAULT NULL,
  `world_y` double DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `status` int(11) DEFAULT NULL,
  `coworker_id` int(11) DEFAULT NULL,
  `raw_data` varchar(255) CHARACTER SET latin1 COLLATE latin1_german1_ci    DEFAULT NULL,
  `user_data` varchar(1024) DEFAULT NULL,
  `map_text` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `drive_number` int(11) DEFAULT NULL,
  `course` int(11) DEFAULT NULL,
  `speed` int(11) DEFAULT NULL,
  `max_speed` int(11) DEFAULT NULL,
  `distance` double DEFAULT NULL,
  `gsm_quality` int(11) DEFAULT NULL,
  `sat_count` int(11) DEFAULT NULL,
  `private` int(11) DEFAULT NULL,
  `user_created` tinyint(1) NOT NULL DEFAULT '0',
  `io_01` int(11) DEFAULT NULL,
  `io_02` int(11) DEFAULT NULL,
  `io_03` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `timestamp` (`timestamp`),
  KEY `world_x` (`world_x`),
  KEY `world_y` (`world_y`),
  KEY `status` (`status`),
  KEY `timestamp_status` (`timestamp`,`status`),
  KEY `status_timestamp` (`status`,`timestamp`),
  KEY `idx_raw_data` (`raw_data`),
  KEY `drive_number` (`drive_number`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1412811 DEFAULT CHARSET=utf8

OK - I'll try and answer this question, however you should realise that there is no "right" answer - just assessments based on situation and experience.

This is a classic question about multi-tenancy - shared versus isolated. There's a very good article here from Microsoft.

AIUI, MySQL has a limit of 32,000 databases per server - but if you're running into that limit, you should obtain more server instances long before that happens. Anyway, there are file limits associated with various OS configurations - but I don't have a clue about Amazon's limitations.

I'm more of a believer in isolated schemas with a caveat about reference tables and the like - use a qualified schema.table.name for, say, cities or similar.

Just a note, when I refer to a given post in my answer, please take the time to look at the entire thread - as I said, there are valid differences of opinion on this matter.

From my answer here, "You may have some data duplication issues with a multi-tenant approach, but that's a matter for you to assess in the light of your application/usage/servers/budget..."

One of the better answers that I have seen is here - however it requires monitoring, expertise and effort - you'll get nothing for nothing (except here where the advice is free! :-) )

My own recommendation is that you change your partitioning/sharding scheme to make it by customer (and possibly by year if that suits you), but I would NOT have a database per monitored system - that strikes me as overkill/unproductive/not_necessary/confusing. That way, your number of schemas is either your number of clients (or no. clients * years).

My understanding is that these data are a logging kind of scenario, so therefore, previous years would not be modified very frequently.

Under my scenario, any database modifications would only occur for no._of_clients (or no._of_clients * years) and I believe that it may present a simplification of your application and your job.

Again, I emphasise that there are alternative valid viewpoints that could be argued - this is my 0.02c based on my own experience and knowledge. Another good thread is here.

For GIS tasks, I would definitely prefer PostgreSQL, but it could be that MySQL 5.7 (and greater) will be a big improvement with the adoption of the Boost.Geometry library for GIS functions.


Restore database with Availability Groups I have high availability enabled with two servers and when trying to restore a database I realised I had to un-synchronize the database and then remove it from the availability group.

I use Ola Hallagrens maintenance solution. Is there something I can amend with Ola's script to automatically remove DB from availability group when I run the restore job or is there a script already available, or simply not do-able?

Thanks

I think you need to revise your question with clarity:

Olas scripts meant for following, not for restoring database.

Backup
DBCC - Integrity Check
Maintenance - Index and Statistics

Aside from this, you cannot DROP or RESTORE a database while it's part of Availability Group. You can remove a database from availability group with following commands:

PRIMARY Replica:

ALTER AVAILABILITY GROUP AGName remove database DatabaseName;

SECONDARY Replica:

ALTER database DatabaseName set HADR OFF;


Postgres role permission problem I have a role called readwrite, created by a user called postgres. This role has following grants applied:

GRANT CONNECT ON DATABASE confere TO readwrite;
GRANT USAGE, CREATE ON SCHEMA airflow_staging TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA airflow_staging TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA airflow_staging GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA airflow_staging TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA airflow_staging GRANT USAGE ON SEQUENCES TO readwrite;

After that I created a user called confere and assigned it to readwrite role:

create user confere with password '...'
grant readwrite to confere

When I run a create table statement with postgres user, confere select it without any problem:

--as postgres
create table airflow_staging.test (a text)

--as confere
select * from airflow_staging.test
a|
-|

But when I create a table with confere user, postgres cannot see its content because it gets a owner error:

--as confere
create table airflow_staging.test_2 (a text)

--as postgres
select * from airflow_staging.test_2
SQL Error [42501]: ERROR: permission denied for table teste_2

Why? Is there a way for postgres user to see tables created by confere?

Obs.: This is not a PostgreSQL superuser, I'm running Google Cloud SQL and Postgres 11

Your ALTER DEFAULT PRIVILEGES doesn't grant to "postgres" (unless "postgres" is also a member of "readwrite") and only grants for tables created by "postgres" (assuming you were logged on as "postgres" at the time you ran it).

Execute the ALTER DEFAULT PRIVILEGES again, this time while logged in as "confere".  And either grant "readwrite" to "postgres", or change the TO part of the ALTER DEFAULT PRIVILEGES.


Can I use hypertables in timescaledb merely to get better insert rates? I have a PostgreSQL db that I have a large bulk load running into. I wish this load to be as fast as possible. I'm already using the copy command etc.

I have been reading about timescaledb and how it offers improved insert performance. However, I wonder if there is any downside to using hypertables instead of regular tables, if I only care about insert performance?

(Timescale person here.)

Yes, you should be able to get much higher insert rate in a TimescaleDB hypertable than a normal table.

The primary downside of hypertables is that there are a couple limitations they expose related to the way we do internal scaling.  In particular:

We only allow a key to be marked as UNIQUE if it includes all partitioning keys (in its prefix).  So if you partition on a time column, the time column could be unique, or you could build a unique composite key on (time, device_id).  But this means you can't build a standard auto-increment id as the primary key (note primary keys are be definition UNIQUE).  But we find that doesn't typically make sense for time-series data.
You can define a foreign-key constraint from your hypertable to a regular table, but we don't currently allow the opposite: a FK from a regular table to a hypertable.  (But same as the UNIQUE constraint limitation above, this rarely makes sense or can be designed around.)

If you have other questions, Docs (docs.timescale.com) or community Slack (slack.timescale.com) are great resources.


Can't backup a Postgres database: FATAL: Peer authentication failed for user "postgres" I can connect to my database no problem remotely, either from PHP scripts on our webserver or using PGAdmin3.

Unfortunately, when I try to run pg_dump backups locally on the server itself, I get:

pg_dump --username=postgres -W omega | gzip > omega.10.10.13.gz

pg_dump: [archiver (db)] connection to database "omega" failed:
FATAL:  Peer authentication failed for user "postgres"

Previously I'd had no password at all for my database, but to try to get around this I actually gave the postgres user a password. Still no dice, peer authentication fails every time.

Here's the settings in my pg_hba.conf file... please tell me what I can do to fix this. Really want to run some backups on my database.

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

host    all             all             70.89.205.250/24        md5
host    all             all             23.21.112.163/24        md5
host    all             all             24.188.1.163/24         md5

In pg_hba.conf, change this line:

local   all             all                                     peer

to:

local   all             all                                     password

Then you can log in locally using a password.


Do I need to stop the service to back up the database? We have several database driven applications using MS SQL Express, Pervasive, MySQL, and some sort of flat file database.  PostgreSQL "may" be added later.

We have backup software that is VSS aware.  I need to backup the database files off the filesystem with regular Windows-based backup software.  We do not have SQL plugins for the backup software.

Do I need to stop the database service before the backup system runs at night, to ensure the database files are in a "consistent" state that can be restored?  Or will the VSS snapshot taken by the backup software allow me to reliably restore data 100% of the time?

I simply need a quick & dirty way of copying the data files with my typical, vss aware, backup software.  Using the SQL plugin would only provide back ups for MS SQL Express anyway.

In SQL Server you do not backup the database's files directly.  You issue a BACKUP command which creates a new file, usually with a .bak extension.  You do not need to stop SQL Server to do this.  It is this new file which is moved to tape & off site.  To restore you will present this .bak file to a RESTORE command.  Here's a previous question on the subject and the Microsoft site.  I imagine the other RDBMSs would work similarly but I claim no expertise with them.  Read the publisher's documentation.

For a flat file system I imagine you would definitely have to stop all write activity to ensure integrity of the data within the files you may hope to restore later.  Again, ask the developers.


the value of undo_sql in flashback_transaction_query view is always null so I have a table defined as follow:

`create table test.test as (select * from dual);

and ran the following update statements:

update test.test set dummy = '1' where dummy = 'X';
commit;

I want to find the undo_sql for the transaction above, so I ran the following statement to find the transaction id

SQL> select dummy, versions_xid, versions_startscn, versions_endscn from test.test
versions between scn minvalue and maxvalue ;

D VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN
- ---------------- ----------------- ---------------
1 0A000500B00A0000           2802291
X                                            2802291

and ran the following statement to find the undo_sql statement

SQL> select undo_sql from flashback_transaction_query
where xid = (select  versions_xid from test.test versions
between scn minvalue and maxvalue where versions_xid is not null);

UNDO_SQL
--------------------------------------------------------------------------------

as you can see, I got nothing, if I run the following statement, I also get nothing.

SQL> select undo_sql from flashback_transaction_query where undo_sql is not null;

no row selected

looks like undo_sql is always null;

Is there anything I didn't configure ?  Here are the values from v$system_parameter

SQL> select name, value from v$system_parameter where name like '%undo%';

NAME                 VALUE
----------------     ----------------
undo_management      AUTO
undo_tablespace      UNDOTBS1
undo_retention       18000

As far as I remember, you should have supplemental logging be enabled so that database can collect the data needed to reconstruct the undo statements used for Flashback Transaction Query:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

For more information on enabling supplemental logging, read "Configuring Your Database for Oracle Flashback Transaction Query" section in Advanced Application Developer's Guide and "Supplemental Logging" section in Database Utilities (LogMiner is another utility which depends on supplemental logging).


Lock a table on purpose without returning results In an update script I'm locking several tables.

BEGIN TRANSACTION
    SELECT Top 0 Null FROM TableA with (holdlock, tablockx)
    SELECT Top 0 Null FROM TableB with (holdlock, tablockx)
    ...

I want to suppress results for it in order to focus on real script results. Is there an elegant way to xlock a table without getting a result set?

There may be other ways, but this technique seems simple enough:

SET NOCOUNT ON
BEGIN TRANSACTION
declare @DummyVariable INT
SET @DummyVariable = (SELECT  Top 0 Null FROM Test1 with (holdlock, tablockx))


Storing summed values I'm trying to create a database to store values for Profit & Loss statements and I'm wondering what the best way to structure this would be. Specifically, amounts that are are equated from other numbers such as: operating income = gross income - operating expenses, and also operating expenses = wages + rent.

Should there be a field for each value, even if it is a result of an equation, or should I only keep the non-equated values, and equate the others when querying or pulling the data into a report?

In the former I would have to more fields and there's potential for the summed numbers to be entered incorrectly, the latter seems like it would have a complex structure, and would need complex queries. Keep in mind that the P&L statement I'm actually working with has many more accounts and summed accounts.

Is there a best practice for this, or place to get more info on this? I really appreciate the help

The correct answer is:  it depends.  Here are factors you should take into account:

is there any possibility of the figures changing?  Accounts are sometimes restated.  If you had a summary table you would have to change that as well if there was a restatement.
you say you are trying to create a database.  Does this mean you already have an OLTP (online transaction processing) database and the new one would a data warehouse?  If so then you need to research data warehouse schemas like the snowflake design and, more importantly, decide the key values you are tracking.  One way of describing a data warehouse is one huge summary table.  Reporting and analysis are often easier with summarized data.
or maybe you just want to add some tables to summarize the data and keep it in the same database as the source material.  If so there are many other tools like views, materialized views that could be used that are dynamic.
there is a trade off between summarizing data.  Once you take a snapshot you have to make sure it stays in sync with the real data.  Or you can build a structure on top of the original data with views.
if the amount of data is small or can be broken into smaller parts you could fill a summary table with a procedure that marks each entry when it was created.  Then if fiscal 2011 is restated due to whatever you just delete all fiscal 2011 entries from the summary table and rebuild with your procedure.  This may or may not pass auditor standards depending on your location, your industry and your companies practices.


column_name ordered same as describe table I was under impression that default order of columns is internal mysql order but for some reason describe table and selecting column_name gives me compleatly different order.

How can I set ORDER BY to be same as in 'describe table' ('default' table order - not by name)?

Server version: 8.0.12 MySQL Community Server - GPL
mysql> describe assetType;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| assetTypeID | int(11)     | NO   | PRI | NULL    | auto_increment |
| assetName   | varchar(45) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT column_name FROM information_schema.columns WHERE table_name='assetType';
+-------------+
| COLUMN_NAME |
+-------------+
| assetName   |
| assetTypeID |
+-------------+
2 rows in set (0.00 sec)

SELECT column_name FROM information_schema.columns
WHERE table_name='assetType'
ORDER BY ORDINAL_POSITION;

The field ORDINAL_POSITION is there to help

mysql> desc information_schema.columns;
+--------------------------+----------------------------+------+-----+---------+-------+
| Field                    | Type                       | Null | Key | Default | Extra |
+--------------------------+----------------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(64)                | YES  |     | NULL    |       |
| TABLE_SCHEMA             | varchar(64)                | YES  |     | NULL    |       |
| TABLE_NAME               | varchar(64)                | YES  |     | NULL    |       |
| COLUMN_NAME              | varchar(64)                | YES  |     | NULL    |       |
| ORDINAL_POSITION         | int(10) unsigned           | NO   |     | NULL    |       |
| COLUMN_DEFAULT           | text                       | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)                 | NO   |     |         |       |
| DATA_TYPE                | longtext                   | YES  |     | NULL    |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21)                 | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21)                 | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(10) unsigned        | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(10) unsigned        | YES  |     | NULL    |       |
| DATETIME_PRECISION       | int(10) unsigned           | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(64)                | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(64)                | YES  |     | NULL    |       |
| COLUMN_TYPE              | mediumtext                 | NO   |     | NULL    |       |
| COLUMN_KEY               | enum('','PRI','UNI','MUL') | NO   |     | NULL    |       |
| EXTRA                    | varchar(57)                | YES  |     | NULL    |       |
| PRIVILEGES               | varchar(154)               | YES  |     | NULL    |       |
| COLUMN_COMMENT           | text                       | NO   |     | NULL    |       |
| GENERATION_EXPRESSION    | longtext                   | NO   |     | NULL    |       |
| SRS_ID                   | int(10) unsigned           | YES  |     | NULL    |       |
+--------------------------+----------------------------+------+-----+---------+-------+
22 rows in set (0.02 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+
1 row in set (0.00 sec)

mysql>


Oracle 11gr2 instant client installation I have an application that uses Oracle 11gr2. For the Oracle client installation, I first install InstantClient and then run the setup again, choose Custom, and choose "Oracle Database Utilities" since the InstantClient doesn't have exp/imp with it.

My question is, instead of doing 2 steps for installation, how can I just do it once? What corresponding options do I have to select in Custom mode to install InstantClient? I have attached a pic for the complete list of items available.

Thanks.

I'd say that Oracle Call Interface and Oracle Java Client, plus may be SQL*Plus if you want to be able to run scripts, should be sufficient.


Export Oracle tables based on owner + all users from database I would like to export all user and roles objects from Oracle 10g database to another.
I googled it but I cannot find out how to do it.

First I use this command:

  exp system/root@[DB_Name] file=[Back_file_name] owner=([Owner_Name])

But the problem was that some table settings depend on user privileges. Hence I want to also export user objects.

For example it is possible to export all objects with this command:

  exp system/root@[DB_Name] Full=Y file=[Back_file_name]

But this command does not export the needed objects for me.

I would like to export only:

tables by owner name
all users
maybe roles

Is this possible?

Data Pump export/import is what you're looking for. You can use this to export your schema definitions and load them into the target database, specifying which type of objects you require.

You can select specific types of objects using the INCLUDE parameter, so to just export tables you would:

expdp include=table ...

Provided you run the export/import as a user with sufficient privileges, this will also create the users on the target when you import.

Full documentation for Oracle 10.2:

Data Pump Export

Data Pump Import


why to use isnull(field,'') =2? I have a field to update which uses isnull(field,'') = 2. But I am getting error as the field has character value in it?

for exaample : field = 'E'
It says conversion failed converting varchar value 'E' to datatype int

Your problem is that you are working with two different data types. ISNULL(field,'') implies that you are working with a character field.  2 however is a number.  Which do you need?

You can do something like this:

ISNULL(field,'') = '2'

But if field happens to be '02' then the result will be false.

Or if field is actually a number then you could do something like this:

ISNULL(field,0) = 2

But if field is a string data type that you are using to hold numbers and you just happen to have an 'Q' in the field then you are going to get a conversion error.

I'm assuming the first option is best since you are getting the error on 'E'.

When you use the code you actually have it is doing an implicit conversion behind the scenes.  If you look at the XML execution plan for the following code:

CREATE TABLE #DataTypeTest (Col1 varchar(50))

INSERT INTO #DataTypeTest VALUES ('1'),('2')

SELECT *
FROM #DataTypeTest
WHERE ISNULL(Col1,'') = 2

You'll see this line (talking about the ISNULL)

 <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,isnull([tempdb].[dbo].[#DataTypeTest].[Col1],''),0)=(2)">

You can see how it's actually converting the ISNULL in to an INT data type.  This is because you are comparing it to a integer.  It doesn't convert the 2 to a string because INT is higher than VARCHAR on the data type precedence list.

Now when it tries to convert the ISNULL formula into an integer it of course fails when the column contains an E.


Execution Plan is NOT using INDEX, It uses Table Scan I know when it comes to using an index or a table scan, SQL Server uses statistics to see which one is better.
I have a table with 20 million rows. I have an index on (SnapshotKey, Measure) and this query:
select Measure, SnapshotKey, MeasureBand
from t1
where Measure = 'FinanceFICOScore'
group by Measure, SnapshotKey, MeasureBand

The query returns 500k rows. So the query selects only 2.5% of the table's rows.
The question is why SQL Server does not use the nonclustered index I have, and uses a table scan instead?
Statistics are updated.
Good to mention that the query performance is good though.
Table Scan

Forced Index

Table/Index Structure
CREATE TABLE [t1](
    [SnapshotKey] [int] NOT NULL,
    [SnapshotDt] [date] NOT NULL,
    [Measure] [nvarchar](30) NOT NULL,
    [MeasureBand] [nvarchar](30) NOT NULL,
    -- and many more fields
) ON [PRIMARY]

No PK on table, as it is a data warehouse.
CREATE NONCLUSTERED INDEX [nci_SnapshotKeyMeasure] ON [t1]
(
    [SnapshotKey] ASC,
    [Measure] ASC
)

Index seek might not be the best choice if you return many rows and/or the rows are very wide. Lookups can be expensive if your index is not covering. See #2 here.

In your scenario, the query optimizer estimates that performing 50,000 individual lookups will be more expensive than a single scan. The optimizer's choice between scan and seek (with RID lookups for the columns needed by the query, but not present in the nonclustered index) is based on the estimated cost of each alternative.

The optimizer always chooses the lowest cost alternative it considers. If you look at the Estimated Subtree Cost property in the root node of the two execution plans, you will see that the scan plan has a lower estimated cost than the seek plan. As a result, the optimizer chose the scan. That is essentially the answer to your question.

Now, the cost model used by the optimizer is based on assumptions and "magic numbers" that are quite unlikely to match your system's performance characteristics. In particular, one assumption made in the model is that the query starts executing with none of the required data or index pages already in memory. Another is that sequential I/O (expected for a scan) is cheaper than the random I/O pattern assumed for RID Lookups. There are many other such assumptions and caveats, far too many to go into in detail here.

Nevertheless, the cost model as a whole has been shown to produce generally "good enough" plans for most queries, on most database schemas, on most hardware configurations, most of the time, everywhere. That is quite an achievement, if you think about it.

Model limitations and other factors will sometimes mean the optimizer chooses a plan that is not, in fact, "good enough" at all. You report that "performance is good", so that does not seem to be the case here.


How can I rewrite for SQL Server a trigger that writing for Oracle? How can I rewrite for SQL Server a trigger that was originally written for Oracle ?

Here is my Oracle trigger code:

CREATE OR REPLACE TRIGGER P000KUL_TEST
BEFORE
INSERT
ON P000KUL
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
SELECT TO_CHAR(SYSDATE, 'dd/mm/RRRR') INTO :NEW.SYSTEM_DATE FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'hh:mi') INTO :NEW.SYSTEM_TIME FROM DUAL;
END;

You can use trigger like this:

CREATE TRIGGER tr_P000KUL_INSERT
ON P000KUL
    FOR INSERT
    AS
    update inserted
    set system_date = convert(varchar, getdate(), 103),
        system_time = convert(varchar, getdate(), 108);
go


SQL Server 2014 SQL Job CmdExec and access denied I have a little trouble with SQL Agent Job.

It should execute program in network directory like \\x\y\z.exe.

Job Step uses proxy account with credentials of a user, which can full access that directory.

Unfortunately job still can't access network directory with error:

  Access is denied

If I set permission on directory to Everyone, there is no problem. Do I miss something?

I am using a UNC path. xp_cmdshell is enabled. Proxy account is fine. I am using it also to run SSIS packages, which doesn't show any problems.
CmdExec subsystem is set to proxy Account
Sharing and UAC permission on directory is set properly.

Is it because SQL Agent job is using not supported authentication type to access directory?

When you configure a Proxy account, you have to check "CmdExec" subsystem in order to use CMDExec with this Proxy Account.


Triggers or views on frequently updating table We have a table which is frequently updating. We have a scheduler to update this table which runs every 10 minutes. There are not more than 50k records in this table but when scheduler runs, database server consumes almost 100%. So now we are thinking to apply triggers on all tables that are used in this scheduler (which runs a SP to update that table) or should we make a simple view? Any help is appreciated. Thanks.

... not more than 50k records ... database server consumes almost 100%

Heavy CPU load suggests (to me) that there's lots of stuff happening in memory, which suggests poorly performing queries doing a lot of table scanning.

Look in the Slow Query log to see which queries are taking along time to run and look to tune them.


Where do Linked Server Queries get executed? I have two instances ServerA and ServerB, and I have created a linked server in ServerA for ServerB as Linksrv_B.

I can execute a query on ServerA using the four part naming convention:

SELECT * FROM Linksrv_B.master.sys.databases

or OPENQUERY():

SELECT * FROM OPENQUERY(Linksrv_B, 'SELECT * FROM master.sys.databases')

I know linked server works across heterogeneous database using distributed transaction as a rowset.

Where does the OLEDB provider gets initiated/connected? ServerA or ServerB?
Where does the query gets executed on ServerA or ServerB? Does optimizer comes into play if so on which server?
Where does the result set get cached?
Could anyone give us a detailed explanation how linked server works.

1 - The OLEDB provider is initiated on Server A, Server B sees it as a normal connection and query, it doesn't know that Sever A is a SQL Server, it's just a client to it.

2 - The query is executed on both.  Server A passes the query to Server B which executes it, then Server A works with the results.  The optimizer comes into play on both, but can't talk to the other servers.

Given a query of no complexity the performance will be equivalent.  However, when you start getting into complex queries or large datasets, you will see performance gains by fetching just what you need from Server B into a temp table and then using that with the data contained on Server A.  (Assuming you need to mix and match data across servers). Essentially, with the OPENQUERY syntax, you pass the query as a whole to server B (hopefully including a where clause) which then returns data to Serer A.  With the four part naming, you have no control and the optimizer rapidly will decide to just fetch all of the data in the table from Server B and then eliminate based on other criteria (joins, where, etc.).

3 - The result is not cached (except as SQL caches data normally).  Unless you store the results from the linked server query into a temp table somewhere.

4 - If you are familiar with how applications connect to a database and execute queries then the process is very similar.  Except for authentication differences it's the same, just that it allows for easily mixing and matching data from multiple servers into a single result set.  But your performance will depend greatly on how much data is pulled from Server B and staying aware of the optimizer's limited ability to prevent you from pulling the entire table from Server B, even if you only ended up needing 5 of those rows.


PostgreSQL complaining about shared memory, but shared memory seems to be OK I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..:

WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

But the problem remains if PostgreSQL is just restarted with service postgresql restart, I suspect max_locks_per_transaction won't tune nothing.

I am a bit estranged because the troubleshooting lists for this error is not working for me.

MORE INFO 1409291350: Some details missing but I keep the core SQL result.

postgres=# SELECT version();
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2,
 64-bit

And:

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.1 LTS
Release:        14.04
Codename:       trusty

Your comment about intensive dropping and creating and the notice you received regarding increasing max_locks_per_transaction hint that you're dropping and creating many objects in the same transaction. Each of those results in a lock, which each requires a small amount of shared memory. Because of this, max_locks_per_transaction limits the number of locks you can hold within a transaction (to prevent any one transaction from using all of shared memory).

You could either increase that limit a bit (I would recommend against setting it arbitrarily large or you will run in to a separate situation of actually running out of total shared memory) or do your drops and creates either in batches of transactions or as one drop/create per transaction.

Edit: Apparently I was wrong about how max_locks_per_transaction works. From the documentation, the total number of locks available is max_locks_per_transaction * (max_connections + max_prepared_transactions) - any one transaction may hold more than max_locks_per_transaction, as long as the number of locks held everywhere is less than this total value.


Performing an upgrade of the database I need to upgrade from PostgreSQL 9.2.24 to 12.
However, once the upgrade was complete, I see that the size of the data directory for the old and new one is different. The old one (/var/lib/pgsql/data) is 91G, and the new one (/var/opt/rh/rh-postgresql12/lib/pgsql/data) is 31G. Aren't both the directories supposed to be in the same size? Furthermore, from the /var/lib/pgsql/upgrade_rh-postgresql12-postgresql.log, the upgrade steps are these :
Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID in new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
  template1
  postgres
  zabbix
                                                            ok
Setting minmxid counter in new cluster                      ok
Copying user relation files
  /var/lib/pgsql/data/base/12923/12880
  /var/lib/pgsql/data/base/12923/12882
  /var/lib/pgsql/data/base/1/12880
  /var/lib/pgsql/data/base/1/12882
  /var/lib/pgsql/data/base/16385/12880
  /var/lib/pgsql/data/base/16385/12882
  /var/lib/pgsql/data/base/16385/16386
  /var/lib/pgsql/data/base/16385/16386_fsm
  /var/lib/pgsql/data/base/16385/16386_vm
  /var/lib/pgsql/data/base/16385/16404
  /var/lib/pgsql/data/base/16385/16406
  /var/lib/pgsql/data/base/16385/16407
  /var/lib/pgsql/data/base/16385/16409
  /var/lib/pgsql/data/base/16385/16410
  /var/lib/pgsql/data/base/16385/16410_fsm
  /var/lib/pgsql/data/base/16385/16410_vm
  /var/lib/pgsql/data/base/16385/16418

Why is still showing the old data directory and not the new one?

pg_upgrade does not copy WAL, nor pg_log, nor any random cruft you put in the old directory which is not under PostgreSQL's control.  Use something like du -sh /var/lib/pgsql/data/* to see where the space is actually being used in old and new servers.

Why is still showing the old data directory and not the new one?

It is showing you the files which are being copied from old to new.  Why should it show you the name of the destination, rather the name of the source?  The latter seems more natural to me.


Database/Storage engine suggestion for a project involving heavy inserts? I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which  I need to monitor daily certain activities associated with them.

For example, let there be a set of 100 users say U1,U2,...,U100

I need to insert their daily scores into my database.

Consider the total score obtained for a user U1 for period June 30 - July 6, is as  follows

June 30 - 99
July 1 - 100
July 2 - 102
July 3 - 102
July 4 - 105
July 5 - 105
July 6 - 107

The database should keep daily scores of each users ,like

For user U1,

July 1- 1pt (100-99)
July 2- 2pt (102-100)
July 3- 0pt (102-102)
July 4- 3pt (105-102)
July 5- 0pt (105-105)
July 6- 2pt (107-105)

Similarly the database should hold daily details of the full set of user's.

And on a later phase ,
I envision to take aggregate reports out of these data like total points scored on each day,week,month,etc; and to compare it with older data.

I need to start things from the scratch.I am experienced with PHP as a server side script and MYSQL. I am confused on the database side ?
Since I need to process about a million insertion daily,what all things should be taken care of ?

Does MySQL fits my requirement,If so what storage engine should be used ? Initially I envision to create a user table with a foreign key user id and monthly score tables with dates as fields.And later on I got suggestion to write things into a csv/excel first and then to load them to table after a particular period.

Does file insertion makes things more favorable in this regard.

Or should I try for some other databases,NoSQL methods ?

EDIT

I am summarizing my requirements,I need to have a database of a million user's whose points are to be updated every day as separate entries.
This will be done regularly such that there should a field per day for each using showing daily points,which could be aggregated in weekly/monthly/yearly basis.
I am confused on the database design as well issue which could happen ever after deployment.
A million or more DB operations are done every day.How the server and other things are to be considered in this case.

Any help will be highly appreciated.Thanks in advance.

Let's break this question up into a few parts.

Q: I need to insert 1mm rows a day. Is that a lot?

Not really. 1mm divided by 24 hours divided by 60 minutes divided by 60 seconds gives you about 12 inserts per second. For a rough frame of perspective, it's not unusual to see 1,000 inserts per second in typical commodity servers with no tuning.

Granted, your load won't be perfectly averaged out like that - you'll have bursts of load - but I wouldn't make database platform decisions based on less than 10k-20k inserts per second. Any platform out there will work fairly well.

Q: How should I structure the data?

Zoom out - don't think table, think databases. If you're going to be keeping this data permanently, and it's truly insert-only with no updates, then you probably want to start a new database for time lengths. Your inserts may only go into one table in one database, but every year, create a new database (MyApp_2015) and seal the old 2014 data as read-only. You can stop backing it up (as long as you've still got a good backup once), stop doing index maintenance, statistics updates, etc.

The PHP will only ever have to know about the current database for inserts, making your design a lot easier. The archival process becomes a DBA task much later down the road as long as you go in knowing that there will be more than one database involved.

If you were doing more than 1,000 inserts per second sustained, and you wanted easier performance management, then I'd also suggest building sharding into the initial design regardless of the database platform. Don't get me wrong, any modern database can handle over 1,000 inserts per second, but designing sharding in now just gives you more flexibility later on. At 12 inserts per second, it's just not worth the design/testing hassle.

Q: How should I do reporting?

In an ideal world, reports would not be done against the live server. Run the reports against a restored or replicated copy of the database. This does two things: it reduces load on the live server, and it validates your backups, guaranteeing that you've got your valuable data elsewhere.


How can I change the names of all tables to uppercase? User created one database, for example TestDb.

Within this database the user created 30 tables, with the table name in lower case letters (Eg:Employee).

Is it possible to change the tables names to upper case (eg:EMPLOYEE) in SQL Server 2008 R2?

Thanks.

To rename table names in UPPER Case:

SELECT 'EXEC sp_rename '
+ '''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ''', '
+ '''' + UPPER(TABLE_NAME) + ''''
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

To rename Columns names in UPPER Case:

SELECT 'EXEC sp_rename ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' +
COLUMN_NAME + ''', ''' +
UPPER(COLUMN_NAME) + ''',''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'),
'isMSShipped') = 0

Result generated by above commands can run on query window to rename table name or column name in upper case respectively.


How to get MS Access to reach a Linked server on MSSQL We have an MS Access DB that has been in place for 16 years in a Front End/BackEnd configuration. It is now running under the latest version of MS Access using ACCDB files. Since its inception, we have added the element of connecting to a remote MySQL server to access some of its tables R/O. This works fine. We create an ODBC connection to the remote MySQL server, then in Access we choose - External Data>>ODBC Database>>Link to the data source, select the ODBC driver and then select the tables we need. They then appear as linked tables with a globe icon.

For reasons too lengthy to get into, we have the need to move the MS Access Back End to an MSSQL server. So we have set up one on the LAN  (SQL 2012). It works fine, and I can do basically the same type of ODBC connection in Access to reach any DB we create on that server.

We then use the SQL Server Management Studio to create a Linked Server of the remote MYSQL server. This works fine as well. Using the SSMS, we can see all the tables of the Linked server and even query data using the OPENQUERY method. We cannot seem to use the 4-part name ("servername.databasename.schemaname.tablename") to query the data. Any attempt to do so returns an error similar to this:

... contains no columns that can be selected or the current user does not have permissions on that object.

But the real question is - what would I do in MS Access to reach the Linked server? When I create an ODBC connection to the MSSQL server, all I can see is the DBs created there. I cannot see the Linked Server, therefore I cannot link any of its tables to MS Access. I have scoured the web, and some threads touch on this, but are not clear enough for me to figure this out.

Thanks in advance to anyone who can help.

As Sean Lange mentioned, MS Access cannot use a linked server which is only accessible from within the SQL Server. But MS Access is external to SQL Server.

If you think of MS Access as the Client in a 2-tier Client/Server project, then it should be clear that you can use the 3-part naming convention (ServerName.Schema.Object) to access the SQL Server.

If you have sufficient rights, you can update data in SQL Server from MS Access through the common use of T-SQL to manipulate SQL Server data.

If you want the SQL Server itself to fetch data from a MS Access database, then you could create a Linked Server in SQL Server that would reach out through a provider to get data from the MS Access.

https://msdn.microsoft.com/en-us/library/ms190479.aspx gives a sample provide setup from the Northwind database.

EXEC sp_addlinkedserver
   @server = N'SEATTLE Payroll',
   @srvproduct = N'',
   @provider = N'MSDASQL',
   @datasrc = N'LocalServer';
GO


Why is random password generation the same when it's inside while loop? TSQL I have a random password generator.  It works fine by itself.  When I put it inside of a while loop, I get the same password even though the seed appears to be different.

declare @count int = 0
declare @end int = 500

while (@count < @end)
    BEGIN
    DECLARE @r varchar(6)
    select @r = coalesce(@r, '') +char(
    case when r between 0 and 9 then 48
    when r between 10 and 35 then 55
    else 61 end + r)
    from master..spt_values
    cross join
    (SELECT CAST(RAND(ABS(CHECKSUM(NEWID()))) *61 as int) r) a
    where type = 'P' and number < 6

    print(newId())
    print(@r)

    set @count = @count + 1
END

This produces an output like the following:

As you can see, the guid is different every time, but the password is the same.
How can I make the password different with each iteration?

You need to clear the value of @r for each loop.

declare @r varchar(6);
set @r = ''; -- Clear previous value
select @r = coalesce(@r, '') + char ...

I can see that you thought declare @r varchar(6); would do that for you but it does not.

Have a look at Variables scope which are defined within a while block in stored procedures - SQl Server for more fun with variable declarations.


How do I change name format in TSQL Query Result? I have a simple tsql select query that returns a user's name in this format:
e.g. Doe, John

How do I update the output so that it is in the format John Doe?

I have tried using a split string function but it returns multiple rows rather than 2 columns... https://stackoverflow.com/questions/10914576/tsql-split-string

DECLARE @Name varchar(100) = 'Doe, Jane'

SELECT SUBSTRING(@Name,CHARINDEX(',',@Name)+1,LEN(@Name) - CHARINDEX(',',@Name) )+ ' ' + SUBSTRING(@Name,1,CHARINDEX(',',@Name)-1)


Noob database design problem This may be a bit of a general problem to post but I could really do with some guidance and this seems like the best place.

The problem stems from the strange situation I find myself in at work; I am a data analyst in a medium sized company. We have a multitude of different systems which our IT department access/update/etc using MySQL. At present the only way for me to obtain data to work with is via manual CSV downloads, which I then work with in Access & Excel, which is both ridiculously tedious and inefficient.

Since my arrival at the company I have been pushing for SQL access to the database tables, so I can better make sense of the data and automate updates etc, but the head of IT has basically run me down every time I have taken problems to him, using my inexperience of SQL as justification for shutting me out. In short he is territorial and is unwilling to allow anyone else access to the data, making up excuses about overloading the server, not knowing the data etc etc.

Following advice from my boss I am now learning MySQL. The plan is that I get an extract of the database tables of our CRM system and fully get to grips with them using MySQL. I hope to be able to reorganise the messy tables into a new structure, good enough to sit Qlikview over. This should then allow us to go to the head of IT and counter his excuses about data access. I have used SQL in previous jobs a little and have a lot of experience with Access so I am finding it straightforward. However I am unsure where to begin in terms of designing a whole new database that I hope to use going forward (I don't want any work I do now to go to waste).

In summary:

I have MySQL installed on my PC with Heidi SQL as my workbench
I will shortly be receiving a large extract of tables from out CRM software. These are poorly documented and I will need to do a lot of work with them to get the required output
My end goal is a well structured database with well organised and documented tables, which bring together the useful data from the CRM tables
Where should I start with this?
How should I store the tables? With Access I have one 'Source Tables' database that simply contains all my major tables, and I have other project specific databases that link to these tables. This is so I only ever need to change 1 copy of the tables.
What ETL processes, tools should I use?
What else do I need to think about? I have been reading a lot about indexing etc, but is there anything specific to this problem that I may miss?

Sorry for the essay. I'm not really sure where else to go with this, IT are definitely not going to help me!

Any help would be appreciated. Thanks

Where should I start with this?

This is a really tough question. You're basically tasked with designing, administering, and migrating to a net-new database using new technologies without much support. This isn't exactly a recipe for success at the enterprise level. In my opinion, just getting your data loaded and accessible in a mySQL database will be the first and most important hurdle. Here's a response on how to convert well formatted CSV dumps to table using HeidiSQL:
https://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table

In terms of database design, I can't really help. However getting a "copy" of what current databases look like is a great starting point. This allows you to "select into new_table query_from_source_tables".

  How should I store the tables?

I would store them as a MySQL database called something along the lines of "source" or "legacy". You can grant other databases access as needed to these tables.

  What ETL processes, tools should I use?

Entirely up to you. I like, generally, doing 1-off queries and then turning them into views if they need to persist/update through time. ETL is entirely up to the demand of the things which access the database. In my opinion, kick the can on this until you have things working in MySQL with your legacy dump and have a decent new design.

  What else do I need to think about? I have been reading a lot about indexing etc, but is there anything specific to this problem that I may miss?

I wouldn't worry about indexing until you're worried about performance tuning. That usually comes after proof-of-concept. I would specifically think about sustainable database design and/or migration plans to a new database structure on the applications that leverage the old database.

In terms of specific problems, I think you've just got your work cut out for you to redesign a database system. Focus on one "major" thing at a time, so you can focus more on what you're doing.


Handling .bak and .dif files to optimize storage usage I'm working with a number of different servers that are severely lacking in backups.

I want to implement a back up plan using simple mode with full backups weekly followed by a series of differential backups throughout the week.

Is it a viable plan to perform the full and differential backups by overwriting the same file? Again I am trying to limit the amount of storage used and from my understanding if I'm taking a differential backup on some interval the data is cumulative therefore only the most recent .dif file in conjunction with the full .bak file will be necessary. Therefore each time a differential backup is performed it would overwrite the previous. Is this safe or am I misunderstanding something crucial?

Each differential backup is composed of the pages that have changed since the last full backup. So if you take a full backup on Sunday, then diffs on Monday, Tuesday, etc. then restoring the database will only require Sunday's full backup, followed by the most recent day's differential.

However, by overwriting your diffs each day, you are losing the ability to restore to a previous point in time. Example: Let's assume you take backups at 1 AM daily. On Thursday afternoon, you discover that data got corrupted on Wednesday and you need to get back to the last known good state. You can't, because the differential backup at 1 AM Wednesday (which would be clean) has been overwritten by the Thursday 1 AM backup which is corrupt.

So to answer your question, it is safe if and only if you assume that you will never have to revert to anything but the most recent backup, and that backup is always correct and corruption-free. I am not staking my job on that assumption.

I would not endorse the scheme you're proposing. Storage is relatively cheap - how much is the data worth to the company? Move the backups off to another, larger, cheaper datastore on a regular basis if space is at a premium, so that you can comply with any data retention policies, regulations or laws as needed.


Zapier & MS SQL - Use azure DB to increase security? I'm interested in using Zapier to automate some processes that should be fired upon certain records appearing as the result of a custom query. Zapier seems to be a good tool as it can do things based upon said query. In this case i need to fire off some data to a webhook, and then update the original record.

I could build this out myself, but why re-invent the wheel?

What am i concerned about is their security. Their documents require you to open up port 1433, and whitelist their IP address, then supply a username and password to them that they use to connect.

As long as our security is configured properly:

Firewall: NAT policy only transforms if the IP matches
Firewall: rules only allow requests from zaps IP to communicate
Using a secure password
Restricting the account to only the tables/data needed

I'm worried about this security, my solution was setting up a simple Azure DB, and using Azure DB Sync to replicate the tables from the onpremise database to the azure cloud DB, and then have zapier connect to that. Azure Sync seems to be a bit more secure in the way it transports data.

Am i just complicating my life without actually increasing security? Is there a better way to do this?

Azure connections are encrypted (SSL/TLS) by default when connecting to Azure SQL Database although it is vulnerable to man-in-the-middle attack.

You have many choices to increase security. You can use Transparent Data Encryption (TDE) as explained here. You can also use dynamic data masking, row-level security, Always Encrypted and Cell-level encryption.

To receive alerts over anomalous activities with your Azure SQL Database you can enable Threat Detection.

Finally, you can make use of Azure Active Directory authentication and multi-factor authentication to enhance security.


Postgres multi tables vs single table performance I am building a db using PostgreSQL for a survey application. The legacy system uses separate tables for each survey which has around 1 million rows each(maximum). Since the columns are exactly the same for all the tables, I was wondering if having separate table is more efficient than having a single table with an Foreign key column pointing to the survey id instead of having separate table for every survey id.

What is the trade-off and which approach is better scalable?

Do you find yourself writing lots of queries in which you combine rows from many of those tables together with UNION or UNION ALL?  If not, then the current setup is probably more efficient than the single table.

Once you have tens of thousands of tables, you can have scalability problems when doing things like backup, dump, and restore, and upgrades.  The nature of those problems would depend on which PostgreSQL version you are using, which tools you are using to do those things, and the details of your database objects.


Does using an auto increment of 1 on numeric PK hurts performance? A teacher of mine said that having an numeric PK with auto increment is a bad practice because it hurts performance.

He also said that Oracle database doesn't have an easy way to set up auto increment because of this.

He said that increment should be done on app instead of DB.

The performance penalty, if I remember correctly, would happen while inserting many rows, it had something to do with allocating a certain number of PKs IDs or something.

Is this true? If yes why?

I think the answer is that "it depends". In a lot of use cases, using an auto increment PK is good practice.

In InnoDB, the default storage engine in MySQL and MariaDB, the performance penalty of using the auto increment feature depends on the auto increment lock mode used. Two of the modes come at the cost of a table-level lock that is released once the statement has completed, which means only one statement can run at any one time. The third option (interleaved lock mode) doesn't lock the tables.

Oracle (12c) actually has auto incrementing keys (identity columns). In previous versions you had to use sequences, which can serve the same purpose, and more. They are a bit more cumbersome to use, though, than auto incrementing keys.

The suggestion that applications should be generating these ID numbers on their own without the aid of the RDBMS' sequence or auto increment features really isn't realistic in a lot of use cases.


How to find all participants of a chat thread? In my database I have the following tables: user, post, post_participant, and post_reply, largely as inspiration from this answer.

One thing I'm having trouble with figuring out though is how to find all the participants of a post.

For example, one way would be to just do a SELECT DISTINCT user on the post_reply table for a given post_id, but this would be missing the original poster if they hadn't replied to their own thread.

Okay, easy enough: do a SELECT on the post table as well and then do UNION ALL with the SELECT on the post_reply table. The problem with that though is that for my application I need to order this result set by post date, so the author should always be on top, and UNION ALL messes up the order.

Okay, fair enough, I can then just use multiple CTEs (union the first, distinct the second, and then finally order by). Except at this point it's become quite complex just to find out all the participants for a post.

The SELECT DISTINCT may also get slow once a post has many replies as well, since I'd imagine it's O(n).

An alternative is that the original poster and all posters can be added to the post_participant table.

The problem with that though, is that I'm not really sure if it's possible to do this efficiently. Do I just need to do INSERT INTO post_participant ON CONFLICT DO NOTHING literally every time anyone makes a reply to a post? That seems extremely inefficient. Or is there a way to cheaply determine whether or not it's someone's first reply to a post, to avoid this database call?

It seems like there are no good solutions to this. Either I have to do an additional write attempt on every reply call, or I have to do a slow and complex DISTINCT UNION every time a post is opened. Is there a better way?

The problem with that though is that for my application I need to order this result set by post date, so the author should always be on top, and UNION ALL messes up the order.

Actually, the order should be preserved as UNION ALL is implemented to just append rows. (Might be revealing to include the offending query in your question.)
Also, you might merge tables post and post_replyinto a single table. Depends.

And if you typically have many rows per user in a chat, use a recursive CTE to get the distinct count of users with best performance using a matching index:

Fast way to count distinct column values (using an index?)


Set MultiSubnetfalilver=Yes to linked servers in standalone SQL Server 2016 implications I am using 1 node SQL Server 2016, having linked servers using MSOLEDBSQL provider and Provider string "MultiSubnetFailover=Yes".
Are there any implications/negative impacts, if i use "MultiSubnetFailover=Yes" to create linked servers in standalone SQL Server 2016.
OR "MultiSubnetFailover=Yes" should only be used in Alwayson setup.

I dont see a benefit of using MultiSubnetFailover for a sandalone server since you dont have failovers, no listener is configured and its a single point of failure. There is obviously a performance gain when using MultiSubnetFailover in a DR situation.


Oracle sqldeveloper Database Diff - not seeing indexes/sequences I am using Oracle SQL Developer's database diff tool to compare schemas.  (SQL developer version : 3.2.09)  It works great on some environments, but in one, I can only see differences in tables - the sequences and indexes are all treated as if they are new and not compared against existing ones.

I've confirmed the indexes exist in the target environment, and that my user account should have permission to see them - I have both "select any dictionary" system privilege and the "select_catalog_role" role.  Further, I do see the indexes if I explicitly query "all_indexes" and "all_ind_columns" tables.

Any ideas what permissions I might be missing?  Again, this works on another environment.

This may not be an answer but worth to check up. Some indexes may have been set to invisible as an alternative to being dropped. These are still maintained by the database but ignored by the optimizer. You'll be able to see them by querying the dictionary, but the compare tool may ignore them.

SELECT INDEX_NAME, INDEX_TYPE, VISIBILITY
FROM ALL_INDEXES
WHERE INDEX_NAME LIKE 'EMP%';


MYSQL Social Networking website - Database Design, Data Growth and Hardware Introduction

I have a system around one million users (community based). It has around 6000 groups (fixed , no change). But there is no discussion board for these groups yet.

Building discussion forum

We are building a discussion forum for the above groups. Each group has 12 categories (fixed, never change). The discussion forum is like Facebook wall. We will have 12 Facebook walls for each group (one wall for one category).

Database Design

For the above mentioned features I have 12 main tables (for each category) and 12 comments tables (for each category). Total 24 tables.

All the tables have similar structure (main and comments table).

CREATE TABLE `tdb_forum` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
   group_id mediumint unsigned NOT NULL,
 ..... Other columns .....
 )
 CREATE TABLE `tdb_forum_comment` (
   `id` int unsigned NOT NULL AUTO_INCREMENT,
    forum_id int unsigned NOT NULL ,
  ..... Other columns ..... )

I am going to use MySQL 5.5 and InnoDB

Data growth expectations

Since one million users are ready to use it immediately after launching, average 1 entry for person in main table per day and average 4 entries for person in  comments table. So per month comments table reach 60 million records in month.

Hardware I am planning

Base Hardware

Intel Xeon E3-1230 (3.20GHZ 8MB Cache)
16GB DDR3 ECC
Primary Disk: Hardware Raid-1
2x2000GB 7.2k RPM SATAII RE4 64MB Cache

Additional Options

  Hardware RAID Controller : Adaptec 2405 - 128MB Cache - 4 Ports
    Chassis : Standard SuperMicro Rackmount
    Public Port Speed : 100Mbs Public
    Public Bandwidth : 10TB Premium Transfer
    IP Addresses : 8 IP Addresses
    Control Panel : cPanel / WHM

Questions I have

Since the data growth is 60 Million per table (per month), should I create a separate database for each group (6000 databases) having all the 12 tables, so in future I don't have to go for any partitioning or sharding? It will be straight forward in reading and writing if my code handles all the database connections well. Even for future growth (max 15 million users), I will not have any problems. But having 6000 databases, do you think any issues like for back up and for replication?
For the main tables and comments table, is it OK to have int as an ID or can I change it to BigInt  or any other suggestions?
Do you think my hardware is good for initial one million users for an average use? Or am I renting too much? If so, what is the best hardware for my case, at least for first year?

You need to increase the amount of RAM, by a LOT.  Based on the load you are expecting I'd want at least 64 Gigs in there, if not 128 or 256.  Given that most of the database will become pretty stale pretty quick I'd say that one database should be just fine.  I've got tables with billions of rows which are OLTP.  Granted they are MSSQL, not MySQL but the ideas are the same.


How to change all columns from one type to another, changing all FK columns as well? In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint.

For a few of those, it's an imminent problem, since this optimistic optimization clashes with the fact that we'll (in some cases, very soon) need more rows for these tables than what tinyint allows.

For other tables, the problem lies in the near future, since it has been decided we'll switch providers and DB engine to a mostly compatible RDBMS, but that lacks tinyint support...

Each of these tables has as well between 2 to 6 foreign key relationships pointing to them, so not only we have to change these columns but the FK ones as well.

Could it be possible to somehow script this change in masse, changing all columns where name = id and type = tinyint (e.g. to smallint or even int) and at the same time alter all the tables with FK referencing these columns so they match as well?

I've seen this for changing all columns from one type to another; but I have yet to deal with the foreign-key situation.

This might work:

(backup db just in case)
DISABLE FKs.
Using a suitable SELECT into information_schem.columns, construct the ALTER TABLE statements.
Enable FKs.


Refresh postgres database on nightly basis I've got a Postgres 9.2 database (production) which is almost 90 GB, I need to restore production database to development postgres server every day, what I think is to schedule a backup an restore script as cron job on linux, but the problem is it won't work in long term, as the database is growing too fast, so base backup and restore every night would take longer and longer as the size of database is increasing.

Is there a better way to refresh the development database on daily basis? for example using log shipping or something which only update the development database with delta changes.

PS: for some security reason, I can't use replication during business hours.

If you're ok with the development db being read only you could use replication using wal shipping. Instead of continuously shipping, do it on a daily basis.

See: 25.2. Log-Shipping Standby Servers

If you need it in write mode it makes things a bit more complex as you'll need to keep the prod copy in read and clone it.
If your storage subsystem support snapshots than it's trivial.

Btw, 90gb should clone pretty fast - how long does it currently take? What disks are you using?


Populate one table with data from another one on selected cells I have a problem with filling empty cells in one table with data from another one.

I got a table with user emails - some of the fields are empty, some are already containing emails. I gathered the list of emails from the users who doesn't have emails in the table. I am going to import it as an separate table, and then update an exiting one. So to demonstrate:

Existing table (USERS):

ID      Email
1       aaa@bbb.com
2       NULL
3       bbb@bbb.com
4       NULL
5       NULL

The second table (MYTABLE) has exactly the same structure, but it has only ID and Emails of users who have NULL in original one.

I used this as a test, but find out that that code is overwriting emails if this user exist in both tables, and I don't want this:

UPDATE users
SET users.email = (SELECT mytable.email
                     FROM mytable
                     WHERE mytable.id = users.id)
WHERE EXISTS (SELECT mytable.email
                     FROM mytable
                     WHERE mytable.id = users.id);

So is it possible to update ONLY cells with NULL value?

This would work ;

UPDATE users
SET users.email = (SELECT mytable.email
                     FROM mytable
                     WHERE mytable.id = users.id)
WHERE EXISTS (SELECT mytable.email
                     FROM mytable
                     WHERE mytable.id = users.id)
AND users.email IS NULL;


Identify When Dates Returned Are 1st, 2nd Or 3rd Date in Table For the 1st 3 orders, a credit is given, so I am needing a way to flag the 1st 3 dates for a customer.  My issue that I am having with my query is it flags the first 3 dates returned regardless of if they were the first 3 dates historically.  In my sample data below, lets take custnum 123, the first 3 order dates are

01/04/2013
01/08/2013
01/22/2013

So each of those should be identified as 1st order, 2nd order, 3rd order.  No other date for custnum 123 should be flagged as historically the first three orders are the orders above.  Below is my sample data, and the query I have (which is inaccurate) that flags the first three dates the query returns NOT the first three dates historically!  What should I change in order to flag the first three dates historically?

CREATE TABLE [Calendar] ( [CalendarDate] DATETIME )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '01/01/2013'
SET @EndDate = DATEADD(d, 1095, @StartDate)

WHILE @StartDate <= @EndDate
BEGIN
   INSERT INTO [Calendar] ( CalendarDate )
   SELECT @StartDate

   SET @StartDate = DATEADD(dd, 1, @StartDate)
END
Create Table #silly
(
  ID int IDENTITY(1,1) PRIMARY KEY,
  custnum int not null,
  cheese varchar(100),
  orderdate date
)

Insert Into #silly Values
('123', 'swiss', '01/04/2013'),
('123', 'yum', '01/08/2013'),
('123', 'aged', '01/22/2013'),
('123','typenah', '02/01/2013'),
('123', 'becher', '02/14/2014'),
('11', 'becher', '02/14/2014'),
('11', 'yum', '01/08/2013'),
('23','typenah', '02/01/2013'),
('23', 'swiss', '01/04/2013')

Declare @date1 date, @date2 date
Set @date1 = '01/31/2013'
Set @date2 = '01/15/2015'

;with datestest as (
Select
[b].[id]
,[b].[custnum]
,[b].[cheese]
,CAST([cal].[CalendarDate] As Date) As [Date],
b.orderdate
,Row_number() Over(Order by b.orderdate) rn
from [Calendar] cal
RIGHT join #silly b
ON CAST(cal.[CalendarDate] As DATE) = CAST(b.orderdate As Date)
where CAST(cal.[CalendarDate] As DATE) >= @date1
and  CAST(cal.[CalendarDate] As DATE)  <= @date2
and b.custnum = '123'
)
Select  [id]
,[custnum]
,[cheese]
,[orderdate]
,case when rn=1 then '1st Order'
when rn=2 then '2nd Order'
when rn=3 then '3rd Order'
else '' end As [Date]
from datestest

First, let's address the way you populate the calendar table - I think you hit a majority of my bad habits/best practices posts:

CREATE TABLE dbo.Calendar
(
  CalendarDate datetime PRIMARY KEY
);
  -- always use schema prefix
  -- always terminate statements with ;
  -- why datetime instead of date?
  -- why no key or clustered index? (I added one.)

DECLARE @StartDate datetime, @EndDate datetime;
  -- why not declare together?

SELECT @StartDate = '20130101',
  -- never use regional, ambiguous formats
  @EndDate = DATEADD(DAY, 1095, @StartDate);
  -- avoid lazy shorthand like d/dd
  -- also why not just add 3 years instead of manually figuring out # of days?

INSERT dbo.Calendar WITH (TABLOCK) (CalendarDate)
  SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate + 1))
    d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY object_id) - 1, @StartDate)
  FROM sys.all_objects;
  -- doesn't really matter for 1000 rows but generally a set-based query
  -- is going to be much more efficient than any kind of while loop

For more information see this series and tip:

Generate a set or sequence without loops - part 1
Generate a set or sequence without loops - part 2
Generate a set or sequence without loops - part 3
Creating a date dimension or calendar table in SQL Server

Now, the problem with your query is that your filter only returns two rows from the table, and these are the only two rows that ROW_NUMBER() sees. So, I'm going to assume you want those two rows returned to be labeled "4th order" and "5th order"? Also, I'm not sure exactly what you need the calendar table for, but I'll keep it in for now, because maybe it's there for some reason you haven't revealed in your question.

DECLARE @date1 date = '20130131', @date2 date = '20150115';

;WITH datestest AS
(
  SELECT [b].[id] ,[b].[custnum] ,[b].[cheese],
  CAST([cal].[CalendarDate] AS date) AS [Date],
  b.orderdate, ROW_NUMBER() OVER (ORDER BY b.orderdate) AS rn
  FROM #silly AS b
  LEFT OUTER JOIN dbo.Calendar AS cal
  -- RIGHT JOINs far less intuitive IMHO
  ON CAST(cal.[CalendarDate] AS date) = CAST(b.orderdate AS date)
  WHERE cal.[CalendarDate] <= @date2
    -- don't need the cast here
    -- also, leave out the begin date filter here so that ROW_NUMBER() see all history
  AND b.custnum = '123'
)
Select [id] ,[custnum],[cheese],[orderdate],
OrderNumber = CASE
WHEN rn = 1 THEN '1st Order'
WHEN rn = 2 THEN '2nd Order'
WHEN rn = 3 THEN '3rd Order'
WHEN rn = 4 THEN '4th Order'
WHEN rn = 5 THEN '5th Order'
ELSE '' END
FROM datestest
WHERE [Date] >= @date1;

Remove the WHEN rn = 4 / 5 lines if you really just want '' when it's not one of the first three orders.

And if you want to use this for more than one customer at a time, you can just add a partition clause to the OVER():

DECLARE @date1 date = '20130131', @date2 date = '20150115';

;WITH datestest AS
(
  SELECT [b].[id] ,[b].[custnum] ,[b].[cheese],
  CAST([cal].[CalendarDate] AS date) AS [Date], b.orderdate,
    ROW_NUMBER() OVER (PARTITION BY b.custnum ORDER BY b.orderdate) AS rn
    -------------------^
  FROM #silly AS b
  LEFT OUTER JOIN dbo.Calendar AS cal
  ON CAST(cal.[CalendarDate] AS date) = CAST(b.orderdate As date)
  WHERE cal.[CalendarDate] <= @date2
)
Select [id] ,[custnum],[cheese],[orderdate],
OrderNumber = CASE
WHEN rn = 1 THEN '1st Order'
WHEN rn = 2 THEN '2nd Order'
WHEN rn = 3 THEN '3rd Order'
ELSE '' END
FROM datestest
WHERE [Date] >= @date1;

Again, both of these queries can work just fine without the calendar table, so I'm not sure I understand its presence.

Ok, now, some links for reading, in order of appearance above:

Schema prefix
Statement terminators
Ambiguous date formats
Lazy shorthand like D/DD


Does shrinking a SQL Server transaction log remove transactions from the log? I know shrinking the log is counter-indicated and has been covered many times, but I just encountered the following, baffling scenario:

We had a run-away query attempt to delete so many records that the transaction log file grew to 30GB on a Windows Server 2008-R2 machine running SQL Server 2008 with the database set to FULL recovery mode. My boss ran a script to backup the database once and then shrink the transaction log to the point where transaction log file was just 1024K. He says the size of the transaction log did not shrink by executing the backup and had the shrink run in a loop to decrease the unused space. How can it reduce the size of the file 30,000 times by just cleaning up unused space? Are we losing useful data by shrinking the log?

"Shrinking" the log is actually "clearing" the log.

The log is basically a list of operations that have been performed, and is used as a reference for rollbacks, restores, checkpoints, etc.

The space wasn't "unused" but it contained transactions that had been committed and were reflected in the backup that was performed.

Your boss cleared out all the transactions in the log file, but since you had just run a backup this is acceptable since none of those transactions were uncommitted.


SQL Update Where Select I have a table where I want to update "time 2" row, in the user's last "time 1" entry. I was try this, but not function.

UPDATE `temp` SET `time2`=NOW() WHERE user = 'kraso' (SELECT max(time1) FROM 'temp')

Unless there was some trouble with StackOverflow code formatting - It looks like the problem is the use of quotations around the 'temp' in the max select query.

(SELECT max(time1) FROM 'temp')

Since temp is a table in this case, quotations will not work. Change these single quotations to backticks as shown in the rest of your update statement.

UPDATE `temp` SET `time2`=NOW() WHERE user = 'kraso' and time1 = (SELECT max(time1) FROM `temp` where user = 'kraso')


Error While Calling A Procedure What is wrong in this procedure?

I have a table family:

Name          Null?    Type
------------- -------- ------------
NAME          NOT NULL VARCHAR2(25)
NICK_NAME              VARCHAR2(15)
GENDER        NOT NULL CHAR(1)
AGE           NOT NULL NUMBER(3)
MARITL_STATUS NOT NULL VARCHAR2(15)
QUALIFICTN    NOT NULL VARCHAR2(20)
NO_OF_CHLDRN           NUMBER(2)

I have created a procedure to insert values in this table. The procedure is compiled and created successfully:

create or replace procedure "insert_family"
(NAME varchar2 ,
NICK_NAME varchar2 ,
GENDER VARCHAR2,
AGE number,
MARITL_STAUS varchar2,
QUALIFICTN varchar2,
NO_OF_CHLDRN number)
is
begin
insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
end;

But when I try to call this procedure through:

BEGIN
insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0);
END;
/

It gives me this error:

  BEGIN
  insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0);
  END;
  Error report -
  ORA-06550: line 2, column 1:
  PLS-00201: identifier 'INSERT_FAMILY' must be declared
  ORA-06550: line 2, column 1:
  PL/SQL: Statement ignored
  06550. 00000 -  "line %s, column %s:\n%s"
  *Cause:    Usually a PL/SQL compilation error.
  *Action:

The problem seems to be in procedure as there is no parameter shown to
have values in debug mode:

When you create an object in Oracle with double quotes, Oracle creates that objects with the same case as you entered it. Try dropping the "insert_family"   procedure and creating it without the double quotes.

This should help.

SQL> CREATE TABLE family
  2       ( NAME          VARCHAR2(25),
  3         NICK_NAME     VARCHAR2(15),
  4         GENDER        CHAR(1),
  5         AGE           NUMBER(3),
  6         MARITL_STATUS VARCHAR2(15),
  7         QUALIFICTN    VARCHAR2(20),
  8         NO_OF_CHLDRN  NUMBER(2) );

Table created.

SQL>
SQL> create or replace procedure "insert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> create or replace procedure "Insert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> create or replace procedure "iNsert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> create or replace procedure "inSert_family"
  2     ( NAME varchar2 ,
  3       NICK_NAME varchar2 ,
  4       GENDER VARCHAR2,
  5       AGE number,
  6       MARITL_STAUS varchar2,
  7       QUALIFICTN varchar2,
  8       NO_OF_CHLDRN number)
  9  is
 10  begin
 11  insert into family values(NAME,NICK_NAME,GENDER,AGE,MARITL_STAUS,QUALIFICTN,NO_OF_CHLDRN);
 12  end;
 13  /

Procedure created.

SQL>
SQL> exec insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0);
BEGIN insert_family('AYUSH','SHUKLA','MALE',12,'UNMARRIED','SCHOOL',0); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'INSERT_FAMILY' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> select object_name, object_type, status from user_objects where upper(object_name)=upper('inSert_family') order by 1;

OBJECT_NAME                    OBJECT_TYPE          STATUS
------------------------------ -------------------- ------------
Insert_family                  PROCEDURE            VALID
iNsert_family                  PROCEDURE            VALID
inSert_family                  PROCEDURE            VALID
insert_family                  PROCEDURE            VALID

4 rows selected.


SQL Server how to get around the transaction log filling up when updating a column to an int I have a SQL Server 2005 table called BRITTNEY_SPEARS_MARRIAGES and it has the following columns:

MarrigeId tinyint,
HusbandName varchar(500),
MarrigeLength int

Now I have another table BRITTNEY_SPEARS_MARRIAGE_STORIES

StoryId int,
MarriageId tinyint,
StoryText nvarchar(max)

The problem is we want to update MarrigeId column to an int from a tinyint. We just feel that Brittney is going to have lots of marriages before everything is said and done.

Now the BRITTNEY_SPEARS_MARRIAGE_STORIES table has 18 million rows in it (hey the girl has some issues) so when we go to do the update the transaction log fills up and our SQL Server box dies.

How can we get around this?

Is there anyway to say "Hey SQL Server I'm going to update this column and make it bigger. Trust me on this SQL Server. Please don't fill up the transaction log while you attempt to validate everything?"

There's no way to tell SQL Server not to use the transaction log.

What you can do is set the recovery model of the database to SIMPLE, which will overwrite old log entries as space is needed.  You should not do this on your production server, however, because you won't be able to do certain types of restores, such as point-in-time restores.

Alternatively, you can set your transaction log file to be larger -- as an unscientific rule of thumb I'd make sure that either A) your transaction log has at least about 1.5x more free space than the size of your table or B) that your transaction log can auto-grow to a drive which has at least about this amount of disk space free.

You can free transaction log space by backing up the log.  If you don't care about the log contents, throw the file away.   A shortcut for this is BACKUP LOG <Your Database Name> TO DISK = 'NUL:'.  Again, don't do this on a production server unless you are absolutely sure you understand the implications.

Another thing to be careful of (though it's not entirely germane to your question) is to make sure the table you're expanding has a clustered index defined on it.  If it does not, the table could incur a very large amount of heap fragmentation, and potentially become needlessly large on a change like this.


Any known issues upgrading from MySQL 5.1.73 to 5.6.21? I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21.

I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone has encountered any specific problems when they have gone the 5.1 to 5.6 route?

One that I have heard of is that certain TIMESTAMP columns might end up with incorrect timestamps. But I am curious if there are any other ones that people have heard of or run into.

To narrow down on the issues I am concerned about, I am only concerned of anything where there might be data loss or consistency issues

Thanks! IS

ISSUE #1 : Upgrade Path

Your grant tables can get screwed up because you are leaping two versions instead of one.

I just helped someone a week ago because they did just that (MySQL service stops after trying to grant privileges to a user). The solution for that question was to manually fix the mysql.user. Rather than going through that heavy-handed route, there are two ways go about this.

TECHNIQUE #1 : Upgrade the Politically Correct Way

Upgrade From MySQL 5.1 to MySQL 5.5
Run mysql_upgrade --upgrade-system-tables using MySQL 5.5's binaries
Upgrade From MySQL 5.5 to MySQL 5.6
Run mysql_upgrade --upgrade-system-tables using MySQL 5.6's binaries
Generate the System Tables that use InnoDB (See my post InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6) since the MySQL 5.6 version of mysql_upgrade does not create them.

TECHNIQUE #2 : Upgrade Directly from MySQL 5.1 to MySQL 5.6

I already described how to upgrade two versions

Apr 11, 2013 : MySQL upgrade 5.0.88 to latest (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

ISSUE #2 : FULLTEXT Indexes

If you use FULLTEXT indexes before MySQL 5.6, those tables use the MyISAM Storage Engine. You need to convert those tables to InnoDB (See my post When to switch from MyISAM to InnoDB? under NEED #2). Then, separately tune the InnoDB FULLTEXT options (See my post MySQL FullText search on string shorter than 3 chars returns no rows).

ISSUE #3 : Security Issues

I am sure you are aware of this since you are going straight to MySQL 5.6.21

For the rest who do not know, as of October 16, 2014, Oracle just announced that there are security issues with MySQL 5.5 and MySQL 5.6. This was also announced at Amazon. Both MySQL 5.5.40 and MySQL 5.6.21 will have the security patches in places, so it is best to upgrade to one of these.


Where is MSOLEDBSQL provider driver? "The 'MSOLEDBSQL' provider is not registered on the local machine." This has been going on. At first MS said they will not support TLS1.2 for OLEDB then they said, they will. You can read about it here, where they said, the support comes in March 2018

Now it looks like package has arrived. I installed it. Then I ran soft without change to connection string using old style Provider=sqloledb. That was expected to fail and it did. I changed to new style Provider=MSOLEDBSQL (see first link). And now I get

  "The 'MSOLEDBSQL' provider is not registered on the local machine."

Ok. I didn't install provider after all. But I can't find it either. I thought, may be this is part of Native Client update. But that came out Jan 2018. I installed it, it didn't break anything, it didn't fix anything. Then I came across this provider that was just released. Installed it. This seem to be new ODBC version.

Bottom line, where do I get MSOLEDBSQL? Thank you

The new OLE DB driver, MSOLEDBSQL, was released today. This new driver includes the support for the latest TLS 1.2 standards and is backwards compatible with SQL Server Native Client 11 (SQLNCLI11). See the Microsoft SQLNCLi team blog announcement.


Why/when does creating a mysql trigger require SUPER privileges? I'm attempting to parse and understand what the MySQL doc says about when SUPER privileges are required for creating triggers. They say:

  If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege[1]

This sort of implies that there are circumstances where it does not require the SUPER privilege. Are they referring to the log_bin_trust_function_creators=1 setting, or is there something else?

Secondly, in [2] they say:

  ... trusted not to create stored functions that will cause unsafe events to be written to the binary log.

Just what exactly is the danger here? What kinds of events might be "unsafe" in the binary log, and to whom?

[1] https://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

[2] https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_log_bin_trust_function_creators

With regard to SUPER and TRIGGER privileges, you need the TRIGGER privilege to create triggers on tables you have rights for.

If you play binlogs, there is the possibility of seeing commands to create triggers on tables you do not have rights for. This is implied from the MySQL Documentation:

  CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as described in Section 19.7, “Binary Logging of Stored Programs”.

With regard to your second point

  trusted not to create stored functions that will cause unsafe events to be written to the binary log.

There are occasions when certain SELECTs on a Master may yield different results on a Slave. For example, let's say you ran this query on a a MASTER

SELECT * FROM tbl WHERE name='...' LIMIT 10;

How could a SLAVE produce different results?

If you have a several hundred INSERTs commands occur on a Master, they get serialized in the Binary Logs. Even if a DB Connection 1 completes an INSERT before DB Connection 2, it is possible for DB Connection 2 to write its event in the Master's Binary Logs before DB Connection 1. This is particularly true in a write-intensive, heavy OLAP environment. This would make a Slave's copy of tbl physically out-of-sync with its Master. Running queries can hide this.

OK, you are probably thinking it's just a SELECT.

SELECTs are not in Binary Logs. INSERTs, DELETEs, and UPDATEs are.

Did you know you could do a query like this ?

DELETE FROM tbl LIMIT 10;

Replicating this can really make tbl out of sync.

Stored Functions can also have this headache if it the code within it is not DETERMINISTIC. This is why log_bin_trust_function_creators was invented. This alleviates mysqld of the responsibility of figuring out if data operations are synchronized in terms of replication. Yes, they fudged their way around downstream replication issues.


Where to learn SQL Server Service Broker? Are there any good resources to learn Service Broker?
I'm looking for a newbie-friendly guide.

Some online articles:

An Introduction to SQL Server Service Broker
Service Broker Tutorials

I myself host a few articles:

Reusing Conversations
Writing Service Broker Procedures
Troubleshooting Conversations

And there are several introduction level practical examples:

Centralized Asynchronous Auditing with Service Broker
Service Broker Foundations Workbench
Using Service Broker instead of Replication
Introducing Distributed Messaging using Service Broker in SQL Server 2005
Building the MSDN Aggregation System


Connecting to Pgpool Apologies for the long post but i'm stumped on getting Pgpool to work for me.

Running Centos7 i have two instances of Postgres running on different ports in master replication mode.

I have successfully installed pgpool-II.

Starting pgpool i get the output :

[root@PLHSISRV035 pgpool-II-96]# pgpool -n
2018-02-07 16:21:54: pid 118373: LOG:  Setting up socket for 0.0.0.0:9999
2018-02-07 16:21:54: pid 118373: LOG:  Setting up socket for :::9999
2018-02-07 16:21:54: pid 118373: LOG:  find_primary_node_repeatedly: waiting     for finding a primary node
2018-02-07 16:21:54: pid 118373: LOG:  find_primary_node: checking backend no 0
2018-02-07 16:21:54: pid 118373: LOG:  find_primary_node: primary node id is 0
2018-02-07 16:21:54: pid 118373: LOG:  pgpool-II successfully started. version     3.7.1 (amefuriboshi)

So from here i should be able to connect using :

psql -l -p 9999 (default settings)

Instead i get the standard not running Postgres error:

psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.9999"?

Can anyone help. ?

thanks,

Simple fix, was to include the database and user flags while connecting.


Exporting certificate without private key I am using the following PowerShell code to create a certificate that can be used as Column Master Key for Always Encrypted feature:

New-SelfSignedCertificate -Subject "AlwaysEncryptedSecurityLevelNormalV001" -CertStoreLocation Cert:LocalMachineMy -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048 -NotAfter (Get-Date).AddYears(100)

Then I export the certificate and import it into the machine where the SQL Server is running. During the export I am disabling the option to export the certificate private key. So, if one try to export it, he/she will get the following:

This is nice because the certificate private key is protected for export.

I am wondering is there any cases where I may need to export only the certificate without the privet key in the context of Always Encrypted feature (as the work of this certificate it to protect the column encryption keys only) ?

Then I export the certificate and import it into the machine where the SQL Server is running.

That defeats the purpose of using Always Encrypted if the server that is running SQL Server also has the certificate to decrypt all the data.

Please, please, please don't do this! Put it on the client machine or a "tools" or "intermediate loading" machine to get the data in and out. Then remove the key and keep it only on the client machines that are locked down.

I'd also keep, in the back of your mind, a solution for when a client machine is lost/stolen/breached (laptop), or a server is compromised. How do you find out, how long do you have to rotate CMKs, do you know how to rotate CMKs, etc.

  I am wondering is there any cases where I may need to export only the certificate without the privet key in the context of Always Encrypted feature (as the work of this certificate it to protect the column encryption keys only) ?

The private key is what keeps everything safe, so no private key then no decryption. I can't think of any times when this would be useful.