MySQL Trigger problem with the email address (cause contains the @ sign)

I ran an experiment. I created the following sample data and used your stored procedure:

drop database vito;
create database vito;
use vito
create table users
(id int not null auto_increment,
email varchar(255) not null,
primary key (id));
create table prova
(id int not null auto_increment,
provaemail varchar(255) not null,
primary key (id));
DELIMITER $$
CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
BEGIN
    INSERT INTO prova (provaemail)
    VALUES (NEW.email);
END $$
DELIMITER ;
insert into users (email) values
('redwards@logicworks.net'),
('rolandoedwards@yahoo.com');
SELECT * FROM users;
SELECT * FROM prova;

Here is the result:

mysql> drop database vito;
Query OK, 2 rows affected (0.07 sec)

mysql> create database vito;
Query OK, 1 row affected (0.00 sec)

mysql> use vito
Database changed
mysql> create table users
    -> (id int not null auto_increment,
    -> email varchar(255) not null,
    -> primary key (id));
Query OK, 0 rows affected (0.11 sec)

mysql> create table prova
    -> (id int not null auto_increment,
    -> provaemail varchar(255) not null,
    -> primary key (id));
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER inserisciemail AFTER INSERT ON users FOR EACH ROW
    -> BEGIN
    ->     INSERT INTO prova (provaemail)
    ->     VALUES (NEW.email);
    -> END $$
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;
mysql> insert into users (email) values
    -> ('redwards@logicworks.net'),
    -> ('rolandoedwards@yahoo.com');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM users;
+----+--------------------------+
| id | email                    |
+----+--------------------------+
|  1 | redwards@logicworks.net  |
|  2 | rolandoedwards@yahoo.com |
+----+--------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM prova;
+----+--------------------------+
| id | provaemail               |
+----+--------------------------+
|  1 | redwards@logicworks.net  |
|  2 | rolandoedwards@yahoo.com |
+----+--------------------------+
2 rows in set (0.00 sec)

mysql>

Looks like the trigger works fine to me.

Rhetorical questions:

Is the version of MySQL you are using source compiled or RPM/YUM installed ???
Should you upgrade to the latest version of MySQL ???


Is there a way to merge two databases, same schema, with a lot of foreign key costraints and incremental id into one single database?

Assuming one the same database server:

Step 1:

Take a record of the maximum ids for users and profile_id and assume that greater than this is the value copied (they are auto_increment right?).

SELECT MAX(id) INTO @maxuserid FROM A.user;
SELECT MAX(id) INTO @maxprofileid FROM A.profile_id;

Step 2:

copy the names, and we temporary use the profile_id as the reference to the B.id:

INSERT INTO A.users ( name, profile_id)
SELECT users.name, users.id FROM B.users

Step 3:

Same for profile_id, using user_id as the temporary old id.

INSERT INTO A.profile_id (email, user_id)
SELECT profile_id.email, profile_id.id FROM B.profile_id

Step 4:

Now correct the A.user.profile_id and A.profile_id.user_id base on the mappings that exist in table B:

UPDATE A.user AS auser
       JOIN B.user AS buser,
            ON auser.profile_id = buser.id
       JOIN B.profile_id AS bprofile
            ON bprofile.user_id = buser.id
       JOIN A.profile_id AS aprofile,
            ON aprofile.user_id = bprofile.id
SET
   auser.profile_id = buser.id,
   aprofile.user_id = auser.id
WHERE
   auser.id > @maxuserid
   AND aprofile > @maxprofileid

tip: write the above UPDATE as SELECT to make sure I've got the join criteria correct.


Just as described in documentation:

GRANT <permission> ON <keyspace> TO <user>;

You need to setup authentication/authorization before, and create user/role.


Simple date calculation to show old records

WHERE logon_time <= sysdate - 2;

Or perhaps:

WHERE logon_time <= sysdate - interval '2' DAY;


When was the last time my table was vacuumed?

Yes, it does:

SELECT last_vacuum,
       last_autovacuum
FROM pg_stat_all_tables
WHERE schemaname = 'myschema'
  AND relname = 'mytable';


sync_binlog different for master and slave

Yes, as MySQL replication happens logically (either statements or rows are replicated) most of the options can be different between masters and slaves.

In particular, your configuration makes quite sense in several scenarios- you want your slaves to be faster than the master to avoid replication lag. You sacrifice this way consistency of the binary logs- something you may not want to do on the master (assuming it is a SPOF for you), but allowing the slave to get corrupted/loss file writes if it crashes (or maybe you do not care about that binary log at all on the slave).


Custom Aggregate Postgresql C extension with input as initial condition

If you need to pass an argument to an aggregate function, just do it. That argument does not have to be a table column.

For example:

SELECT myagg(col1, 'initial value') FROM atable;

True, you could also call the function as

SELECT myagg(col1, col2) FROM atable;

but nobody forces you to do that, if you want the second argument to be a constant used for initializing then state.


sql server 2008 r2 DBCC checktable error

One suggestion would be to identify the index that is causing the problem.

select o.name as tablename, i.name as indexname
  from sys.objects o
  join sys.indexes i
    on o.object_id = i.object_id
  where i.index_id = 7
    and o.object_id = 1253579504

and then drop it. If you believe you're going to need this index then I'd script out the DDL before dropping it, drop it, recreate it, and then rerun your DBCC. Since its index_id is 7 I'm going to assume that this table is potentially over-indexed so YMMV with actually recreating it.

If dropping the index doesn't work, then I would always recommend a last-known-good backup to which you can restore. Also, if this has been occurring for a while, the backup will also have the corrupted index since a full backup is a page-by-page copy of the database (+ some transaction log).


You can easily do what @a_horse_with_no_name suggests in his comment.  But there is also an interesting way to do it, using PL/pgSQL as the function language.

This uses a feature of the COPY command, introduced in PostgreSQL 9.3.  It can now take a command as target/source, exactly where you'd use a filename or STDIN/STDOUT in normal cases:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

Obviously, you need a table to put the output, but you can ignore it, if you want.

See a small example:

CREATE TABLE trigger_test (
    tt_id serial PRIMARY KEY,
    command_output text
);

CREATE OR REPLACE FUNCTION trigger_test_execute_command()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $BODY$
BEGIN
    COPY trigger_test (command_output) FROM PROGRAM 'echo 123';
    RETURN NULL;
END;
$BODY$;

CREATE TABLE trigger_test_source (
    s_id integer PRIMARY KEY
);

CREATE TRIGGER tr_trigger_test_execute_command
    AFTER INSERT
    ON trigger_test_source
    FOR EACH STATEMENT
    EXECUTE PROCEDURE trigger_test_execute_command();

INSERT INTO trigger_test_source VALUES (2);

TABLE trigger_test;
 tt_id │ command_output
───────┼────────────────
     1 │ 123

Note: the function needs to run with superuser rights - that is, either do the INSERT as a superuser, or define the function with SECURITY DEFINER.  In any other case, you'll get an error:

ERROR:  must be superuser to COPY to or from an external program
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.


Why "Begin Transaction" before "Insert Query" locks the entire table?

This answer may prove helpful to the original question but is primarily to address inaccurate information in other posts. It also highlights a section of nonsense in BOL.

  And as stated for the INSERT documentation, it will acquire an
  exclusive lock on the table. The only way a SELECT can be made against
  the table is to use NOLOCK or set the isolation level of the
  transaction.

The linked section of BOL states:

  An INSERT statement always acquires an exclusive (X) lock on the table
  it modifies, and holds that lock until the transaction completes. With
  an exclusive (X) lock, no other transactions can modify data; read
  operations can take place only with the use of the NOLOCK hint or read
  uncommitted isolation level. For more information, see Locking in the
  Database Engine.

NB: As of 2014-8-27 BOL has been updated to remove the incorrect statements quoted above.

Thankfully this is not the case. If it were so inserts to a table would occur serially and all readers would be blocked from the entire table until the insert transaction completes. That would make SQL Server as efficient a database server as NTFS. Not very.

Common sense suggests it cannot be so but as Paul Randall points out, "Do yourself a favour, trust no-one". If you can't trust anyone, including BOL, I guess we'll just have to prove it.

Create a database and populate a dummy table with a bunch of rows, noting the DatabaseId returned.

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'LockDemo')
DROP DATABASE [LockDemo]
GO

DECLARE @DataFilePath NVARCHAR(4000)
SELECT
    @DataFilePath = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM
    master.sys.master_files
WHERE
    database_id = 1 AND file_id = 1

EXEC ('
CREATE DATABASE [LockDemo] ON  PRIMARY
( NAME = N''LockDemo'', FILENAME = N''' + @DataFilePath + N'LockDemo.mdf' + ''', SIZE = 2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB )
 LOG ON
( NAME = N''LockDemo_log'', FILENAME = N''' + @DataFilePath + N'LockDemo_log.ldf' + ''', SIZE = 1MB , MAXSIZE = UNLIMITED , FILEGROWTH = 1MB )
')

GO

USE [LockDemo]
GO

SELECT DB_ID() AS DatabaseId

CREATE TABLE [dbo].[MyTable]
(
    [id] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , [filler] CHAR(4030) NOT NULL DEFAULT REPLICATE('A', 4030)
)
GO

INSERT MyTable DEFAULT VALUES;
GO 100

Setup a profiler trace that will track lock:acquired and lock:released events, filtering on the DatabaseId from the previous script, setting a path for the file and noting the TraceId returned.

declare @rc int
declare @TraceID int
declare @maxfilesize BIGINT
declare @databaseid INT
DECLARE @tracefile NVARCHAR(4000)

set @maxfilesize = 5
SET @tracefile = N'D:\Temp\LockTrace'
SET @databaseid = 9

exec @rc = sp_trace_create @TraceID output, 0, @tracefile, @maxfilesize, NULL
if (@rc != 0) goto error

declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 24, 32, @on
exec sp_trace_setevent @TraceID, 24, 1, @on
exec sp_trace_setevent @TraceID, 24, 57, @on
exec sp_trace_setevent @TraceID, 24, 3, @on
exec sp_trace_setevent @TraceID, 24, 51, @on
exec sp_trace_setevent @TraceID, 24, 12, @on
exec sp_trace_setevent @TraceID, 60, 32, @on
exec sp_trace_setevent @TraceID, 60, 57, @on
exec sp_trace_setevent @TraceID, 60, 3, @on
exec sp_trace_setevent @TraceID, 60, 51, @on
exec sp_trace_setevent @TraceID, 60, 12, @on
exec sp_trace_setevent @TraceID, 23, 32, @on
exec sp_trace_setevent @TraceID, 23, 1, @on
exec sp_trace_setevent @TraceID, 23, 57, @on
exec sp_trace_setevent @TraceID, 23, 3, @on
exec sp_trace_setevent @TraceID, 23, 51, @on
exec sp_trace_setevent @TraceID, 23, 12, @on

-- DatabaseId filter
exec sp_trace_setfilter @TraceID, 3, 0, 0, @databaseid

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Insert a row and stop the trace:

USE LockDemo
GO
INSERT MyTable DEFAULT VALUES
GO
EXEC sp_trace_setstatus 3, 0
EXEC sp_trace_setstatus 3, 2
GO

Open the trace file and you should find the following:

The sequence of locks taken is:

Intent-Exclusive lock on MyTable
Intent-Exclusive lock on the page 1:211
RangeInsert-NullResource on the clustered index entry for the value being inserted
Exclusive lock on key

The locks are then released in reverse order. At no point has an exclusive lock been acquired on the table.

  But this is just one batch inserting! That's not the same as two,
  three or dozens running in parallel.

Yes it is. SQL Server (and arguably any relational database engine) has no foresight as to what other batches may be running when it processes a statement and/or batch, so the sequence of lock acquisition does not vary.

  What about higher isolation levels e.g. Serializable?

For this particular example exactly the same locks are taken. Don't trust me, try it!


REPLACE(orig,match,replacement) multiple matches in one query

A stored function did the trick. I spent a lot of time playing with RECURSIVE CTE only to find that stored functions have loops. Twas fun.

Anyway. I also have a table 'replaced' with columns 'text' (the original) and 'tokenized' (the updated).

So I run:
UPDATE replaced SET tokenized = tokenize_text(text);

and i have stored function:

DELIMITER $$
DROP FUNCTION IF EXISTS tokenize_text $$
CREATE FUNCTION tokenize_text(the_text varchar(1000)) RETURNS VARCHAR(1000)
    READS SQL DATA
BEGIN

 DECLARE v_finished INTEGER DEFAULT 0;
 DECLARE v_match varchar(1000) DEFAULT "";
 DECLARE v_replacement varchar(1000) DEFAULT "";
 DECLARE v_output varchar(1000) DEFAULT CONCAT(' ',the_text, ' ');

 -- declare cursor for matches
 DEClARE matches_cursor CURSOR FOR
    SELECT text, replacement FROM matches;

 -- declare NOT FOUND handler
 DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET v_finished = TRUE;

 OPEN matches_cursor;

 change_text: LOOP

 FETCH matches_cursor INTO v_match, v_replacement;

 IF v_finished THEN
 LEAVE change_text;
 END IF;

 -- build email list
 SET v_output = REPLACE(v_output, CONCAT(' ',v_match,' '), CONCAT(' ',v_replacement,' '));

 END LOOP change_text;

 CLOSE matches_cursor;
 RETURN v_output;
END$$

DELIMITER ;

To test, I do select * FROM replaced'; and get:

+-----------------------+---------------------------------+
| text                  | tokenized                       |
+-----------------------+---------------------------------+
| i run really fast     |  [who] [action] really [speed]  |
| i run really slow     |  [who] [action] really slow     |
| i shop really fast    |  [who] [action] really [speed]  |
| i shop really quickly |  [who] [action] really [speed]  |
| i shop really slowly  |  [who] [action] really [speed]  |
+-----------------------+---------------------------------+

Which is correct. It turns out I didn't have a match for 'slow', so it doesn't get replaced with [speed] like the other speed-specific adjectives.


SQL or NoSQL for structured clinical data

As usual in similar cases, I would strongly recommend that you look at Open Source solutions - well before the design phase. EVEN if you are doing something truly radical, one can always learn by standing on the shoulders of giants.

I have worked as a student on the OpenEHR project, so this interests me. It's been a while, but I would start by looking here, here and here. GIYF :-).

For a pure database recommendation, I would include the big three of Open Source databases - MySQL, PostgreSQL or Firebird. Find a system you wish to adapt. "Good programmers know what to write. Great ones know what to rewrite (and reuse)" and use the database with that - unless you have a particular requirement that precludes the chosen system's database.

There was a time I would not have recommended MySQL, but I have recently had occasion to work with it, and I was pleasantly surprised - though not having CHECK constraints is still a bit of a shock!

I note with interest in your profile that you are an experienced contributor to the stackexchange lists, so I would ask the question as to why you are choosing MongoDB? I'm not saying your choice is incorrect, I'm just wondering what it is that MongoDB has that you can't get from a "classic" RDBMS?

My understanding is that NoSQL solutions are meant to be for coping with web-scale problems (in big data and real-time web applications), and not running simple medical centres. Even if your system was dealing with the entire population of Chile, it still wouldn't start to be "large" on the web-scale. A further point would be that I can see no valid reason whatsoever to sacrifice consistency with medical data - it might be OK for books on Amazon, but not this sort of information ("Many NoSQL stores compromise consistency" [ibid]).

In any case, I wish you well with the project.


what is the quickest way to check whether a XML column is empty ( @xml = '') in the where clause?

I was intrigued as which one would perform better so I did a test and would like to share the results here.

I create a table to keep put some data in XML and for testing later on

USE MY_TEST_DATABASE
GO

--drop table radhe_01
--GO

CREATE TABLE RADHE_01 ( i int not null identity(1,1) primary key clustered,
                        MYXML XML NOT NULL
                       )
GO

Ops, the MYXML column should be able to accept nulls - need to change it

ALTER TABLE RADHE_01
ALTER COLUMN MYXML XML NULL

I create a procedure in order to create random values to be added to MYXML column

I want to add one million records to that table

CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ''

WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))

IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END

I test the procedure to check it is working nicely

declare @randomString varchar(50)
exec SpGenerateRandomString 30, @randomString output
select @randomString

I insert one million rows into the table.

I kept my XML format as I have in the live procedure.

This XML has 2 values, I forgot about this detail and left the first value there GarmentComposition.

set nocount on
declare @randomString varchar(50)
declare @i int

select @i =1
while @i < 1000000 begin

      exec SpGenerateRandomString 30, @randomString output
      insert into radhe_01 (myxml) values (N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>' + @randomString  + '</NoteTypeCode></BulletPoint>')
      select @i = @i +1
end

I play with the data, in order to test the results later on,
I will change some rows to NULL and some rows to empty ''

SELECT COUNT(*) FROM
RADHE_01
WHERE I%117=0
--8547

SELECT COUNT(*) FROM
RADHE_01
WHERE I%1217=0
--821

SET NOCOUNT OFF
UPDATE RADHE_01
SET MYXML = ''
WHERE I%117=0
-- 8547 WILL BE EMPTY
UPDATE RADHE_01
SET MYXML = NULL
WHERE I%1217=0
-- 821 WILL BE NULL
SET NOCOUNT ON

--(8547 row(s) affected)

--(821 row(s) affected)

Now doing the test

I have run both queries as you can see on the script below.

set statistics io off
set statistics time off

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

set statistics io on
set statistics time on

--find the number of records that are null or empty - using XML.exists
select t.*
from RADHE_01 t
where t.MYXML.exist('*') = 0 OR t.MYXML IS NULL

set statistics io off
set statistics time off

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

set statistics io on
set statistics time on

--find the number of records that are null or empty - using datalength
select t.*
from RADHE_01 t
where DATALENGTH(t.MYXML) = 5 OR t.MYXML IS NULL

set statistics io oFF
set statistics time oFF

The result of the statistics TIME and statistics IO are as follows:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Table 'RADHE_01'. Scan count 1, logical reads 23902, physical reads 3, read-ahead reads 23445, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2187 ms,  elapsed time = 2646 ms.

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

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Table 'RADHE_01'. Scan count 5, logical reads 24132, physical reads 1, read-ahead reads 23477, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 391 ms,  elapsed time = 1853 ms.

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

This is the execution plan (picture) from sql server

This is the full XML execution plan of the query and picture above.

This is another picture using another tool to compare execution plans.

Conclusion:

Using DATALENGTH on this occasion, for this situation, has been proven quicker.


SELECT (My_Field_List)
FROM My_Table
WHERE My_Timestamp BETWEEN TIMESTAMPADD(HOUR, -1, Alarm_Time)
  AND TIMESTAMPADD(HOUR, 1, Alarm_Time);

Details available in the ever useful documentation here.

Example:

mysql> CREATE TABLE Sample(My_Timestamp TIMESTAMP);
Query OK, 0 rows affected (0.71 sec)

mysql> SELECT * FROM Sample;
+---------------------+
| My_Timestamp        |
+---------------------+
| 1999-01-01 00:00:01 |
| 2010-01-01 00:00:01 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT My_Timestamp FROM Sample WHERE My_Timestamp BETWEEN TIMESTAMPADD(HOUR, -1, '1999-01-01 00:00:01') AND TIMESTAMPADD(HOUR, 1, '1999-01-01 00:00:01');
+---------------------+
| My_Timestamp        |
+---------------------+
| 1999-01-01 00:00:01 |
+---------------------+
1 row in set (0.00 sec)


Selecting data from another table using a foreign key

Joining tables is a fundamental principle of relational databases. In your case, A and B are related with the id column, which means that you can use a syntax similar to this one:

SELECT a.id, a.name, a.num, b.date, b.roll
FROM a
INNER JOIN b ON a.id=b.id;

INNER JOIN means that you'll only see rows where there are matching records in A and B. If you want all the rows in A and matching records in B, you could change INNER JOIN to LEFT JOIN. Conversely, if you want all the records from B and only the matching ones from A, use RIGHT JOIN. Finally, if you need everything from both tables, matching or not, you can use FULL JOIN.


How can I get the host_name who creates a DB (proactive mode) in SQL Server 2008?

That information is available in the default trace (see this answer for more detail), if it hasn't rolled away (the default trace only keeps so much history).

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   LoginName,
   HostName,
   ApplicationName,
   SPID,
   StartTime,
   DatabaseName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 46
AND EventSubClass = 1
AND ObjectID IS NULL
AND ObjectType = 16964
-- AND DatabaseName = N'<database name>';

Other disclaimers:

HostName can't always be collected (e.g. connecting to SQL Server from Visual Studio Code on a Mac leaves HostName NULL).
HostName is easy to spoof.

Going forward, you can do just about anything you want with a DDL trigger, subject to the same disclaimers as above. You can start with something like this:

CREATE DATABASE Watchdog;
GO
USE Watchdog;
GO
CREATE TABLE dbo.CreateDatabaseLog
(
  EventTime datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
  HostName nvarchar(128),
  EventData XML
);
GO

USE master;
GO
CREATE TRIGGER CaptureDatabaseCreation
ON ALL SERVER
FOR CREATE_DATABASE
AS
  INSERT Watchdog.dbo.CreateDatabaseLog(HostName, EventData)
    SELECT HostName, EVENTDATA();
GO

Now, I'll leave parsing DatabaseName out of the XML as an exercise for the reader, mostly because I am late picking up my kids from daycare. :-)


How to maintain Index and Statistics on AWS RDS?

I am running SQL Server RDS maintenance jobs (DBCC CHECKDB, index reorgs/rebuilds, statistics updates) without a problem.  The only limitation I had to work around was that I could not run sp_updatestats, so instead had to generate an 'UPDATE STATISTICS' script for each table like this:

DECLARE @sql NVARCHAR(max) = '';
SELECT @sql = @sql+
'UPDATE STATISTICS ' + '[' + table_name + ']' + ' WITH FULLSCAN;
PRINT ''UPDATING STATISTICS ON ' + table_name +'...'';
'
FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE';

EXEC sp_executesql @statement=@sql;


There are a few causes of this, the top ones are: storage subsystem, bad t-sql, missing/bad indexes.  Paul Randall talks about this on his blog and at the sqlperformance blog.  I like the scripts from Glenn Berry to find missing indexes and also SP_BlitzIndex.

How do you know if you can trust the recommendations?  Well I examine the existing indexes first to see what I have and if they can be altered to accommodate the missing columns.  I also like to look in the plan cache to get an idea of what some of the top queries are on that table to see if it would actually make a difference.  If you have a test/dev instance, you can implement there first which will give you an idea of the size any possibly usage pattern if you can generate a workload.  If you are going right into prod, monitor sys.dm_db_index_usage_stats (check the index reads/writes query in Glenn Berry's DMV queries)and check the reads/writes to ensure it is used.  You could also use SQL Sentry's Plan Explorer, but I think you need the pro version for the indexes.

I'm sure someone has a better way to do it, but this has been working for me so far.


SSMS 2017 Changing SSIS Package Version During Deployment

I'm not surprised but haven't tested it myself.

Project deployment model packages are deployed using ISDeploymentWizard.exe and there is a version associated to each version of SQL Server (and bit-edness).
For example, one laptop here has VS 2013/2015 installed and SSDT for 2012/2014/2016 installed. If I look for that executable, I see

C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\ISDeploymentWizard.exe
C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe
C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe

My new machine only has bits for 2017 and looks like

C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe
C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDeploymentWizard.exe

The other thing to factor into all this is that using a newer version of the SSIS tooling (ISDeploymentWizard, dtexec, dtutil) will happily roll your version forward to match its version. Which is helpful as a consultant to upgrade things but the devil when you want to use new tools against old code.

My guess is that SSMS is defaulting to an ISDeploymentWizard that is not in the 120 folder. Unfortunately, I don't see a parameter in the deployment wizard to address version.

/Silent[+|-]                  Default value:'-' (short form /S)
/SourceType:{File|Server}     Default value:'File' (short form /ST)
/ModelType:{Project|Package}  Default value:'Project' (short form /MT)
/SourcePath:<string>          (short form /SP)
/SourceServer:<string>        (short form /SS)
/Packages:<string>            (short form /PK)
/ProjectPassword:<string>     (short form /PP)
/DestinationServer:<string>   (short form /DS)
/DestinationPath:<string>     (short form /DP)
/SkipSource[+|-]              Default value:'-'
@<file>                       Read response file for more options

Your best, yet icky, bets are to

Keep a copy of SSMS for SQL server 2012 lying about for deployment
Deploy from SSDT 2012
Deploy from the commandline explicitly defining the source exe

The last option is actually that bad. Use the GUI to click through the deployment but on this screen, stop

That highlighted box is selectable/copyable. DO THAT. The command line deploy has been built out for you (excluding the part that says "Command line:")

Thus you'd have something like

"C:\...\1XX\DTS\BINN\ISDeploymentWizard.exe" /SILENT /SourcePath:"C:\MyProject.ispac" /DestinationServer:"localhost" /DestinationPath:"/SSISDB/Folder/Project/MyProject"


Normalization is only one aspect of the database design process.  For starters, I suggest that you do something like ...

[1] Create an ERD (entity relationship diagram) aka logical diagram.  Start on paper - identify the entities that your system will be dealing with.  Find relevant relationships that connect the entities.  Use the correct terminology: entity, attribute, unique identifier, relationship, cardinality, optionality.  AVOID saying/thinking: tables, primary/foreign keys.  Do NOT mix up instances and entities (each entity represents a multitude of instances).  Remove redundant relationships.  Use modelling software* once the paper version looks okay.
* eg the (freely available) datamodeler:  http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

The ERD stage is probably the hardest one to learn and do.  However, if you get it right, you'll have a reliable foundation for the later stages.  Make sure that nothing in your ERD violates 1NF or 2NF or 3NF. A lot of confusion can arise when "implementation details" are coming into the game too early.  A rather good description of the process can be found at: http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html

[2] Draw a relational/physical model - this is one step closer to the implementation.  Entities (of the ERD) become tables, attributes become columns, unique identifiers become primary keys etc.  Apply the correct data types (depending on the system you are using).  Specify the constraints: primary/unique/foreign/check constraints eg tables on the MANY side of a relationship will need an additional foreign key column (which "references" another pk or unique column).  The relational model is like a roadmap for coding queries later on!

Some modelling software is able to generate SQL(DDL) code from a model, or - vice versa - generate a model from code.  If you cannot resist the temptation to write code straight away, generate a model for documentation purposes.

[3] Only if the diagrams are looking good, write the DDL code (SQL: mainly create table ... and alter table ... statements).  Update the models (ERD and relational) if you make any changes to the initial "layout".

[4] Insert a decent amount of test data into each table (there are online test data generators).

[5] Write the queries, views etc as required.


What is the proper way of Adding a Field in a MySQL table with 1.6 million row

ALTER on such a huge table can be slow irrespective of Engine_type

Follow this method to avoid downtime and quicker operation:

Setup slave
Stop replication.
Make ALTER on slave
Let slave catch up the master
swap master and slave, so slave becomes production server with changed structure and minimum downtime

You can also refer this post from MySQL Performance blog:

http://www.mysqlperformanceblog.com/2008/03/27/using-mmm-to-alter-huge-tables/


Additional Select statement within Cursor terminates looping unless Select statement returns a results for all cursor rows

I did some research and found official guidance on this situation. The issue results from the SELECT INTO query as suspected, this is because it is within the scope of the first BEGIN and END statement which has the following HANDLER declared:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

Thus, when the SELECT INTO does not find any rows, finished is set to 1 and this is the condition that causes the loop to cease. Thus, to fix this issue the SELECT INTO must be scoped into its own BEGIN and END statement to allow for another HANDLER to be declared to essentially ignore NOT FOUND conditions. This is what that should look like:

BEGIN

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 0;

         select
            tos.ID
        from toys_on_sale tos
        where tos.TOY_ID = heldToyId INTO existingSaleId;

END;

Now, if the SELECT INTO statement finds no rows it will simple set finished to 0 which does nothing as the loop only terminates if finished is 1. Luckily, our existingSaleId declared variable in the other BEGIN and END statement is still within the scope of this SELECT INTO statement and we can obtain those Ids as expected.


Communication between two database servers

Because both of them are virtual servers (you didn't mention the technology used (ESX/KVM...), you can try this:

Create a small virtual drive that can be mounted on both servers (Linux ? Windows ? - you didn't tell)
Create a resource checking script (ex. http://howtodba.com/linux-cluster-resource-workers-locking-mechanism/)
Run the resource checking script, from each box, each 5 minutes (odd minutes on box 1, even minutes on box2).

** The more info you provide, the better solutions you'll get.


Permission for business logic layer & application

Database engines have no standardized access layer with enough resolution to allow direct use of the database from client apps.

The databases locking mechanism and indexing also have to be taken into account. For example a report query on SQL server or db2 will lock tables and delay all other queries on the same tables.

The purpose of the business layer is to mediate the access to the database and sharp those edges. Some people do that as stored procedures but that makes the application too tied on a specific vendor. I offer no criticism about that. But many others here will.

On my web applications, I put the logic in the server side and access the database directly from the PHP/j2ee webservers. My philosophy is the database has to have the constraints and triggers to ensure the data quality otherwise an rookie developer can break the data model easily.

But yes, mostly the end user will authenticate against the web layer while the servers will authenticate against the database using power users. The database users are focused on which tables, views and functions the server side app can use.


To be less ambiguous you can prepend the alias in the set clause. (Taken from 孔夫子 in the comments, who did not submit an answer.)

update address
set a1.col1 = a2.col1,
a1.col2 = a2.col2,
.....etc
from address a1, address a2
where a1.id = @to and a2.id = @from


Are there disadvantages to using Full-Text Search in SQL Server?

Read this: SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned.


Removing MySQL Orphan Tables in Google's Managed MySQL

Your theory is correct about your cloudSQL since it is a Google managed service.

If you would like to have full access over your mysql database, you can use it through a Compute Engine instance in which you will have super user privileges.

Nevertheless, about your current issue please fill a bug here which is under a private component and you can share if there is a need, sensitive information.


Will an SSRS report on Azure SQL Database work?

You can fetch the data from Azure SQL database but it does not include SSRS so you would need a separate install (Azure VM ?) for that.


Primary Key Type's Effect on Performance in Oracle?

From what I know, it depends on whether your table is IOT (Microsoft calls it clustered table, a table with clustered index)or not . Historically, Oracle IOTs performed worse than its heap tables. That seems to change (I believe with Oracle 10), but there is still prejudice against IOT. So the article you mentioned can be applied to some extent if you use IOT in Oracle, but not for heap tables.
Don't misunderstand me, having a PK constraint supported by index on large varchar2 column has impact on performance, but if it's an Oracle heap table, it's the same as having an index on non-PK varchar2 column.


Managing features for a real estate agency

Best to avoid entity-attribute-value structures if possible as they complicate the model and are unnecessarily fiddly to work with.  It sounds like the fields are going to be the same or substatnailly the same across all the houses, so you're probably just better adding them onto the property record.

The exception being if you really have a genuine 1:M relationship.  An example might be if you wanted to record (say) dimensions and room type (living room, bedroom, bathroom etc.) by room for a variable number of rooms.


Service Broker Connection handshake failed. There is no compatible encryption algorithm

A service broker instance on another server was causing this error. It didn't have a master certificate on the server with error.

I dropped the service broker instance on the other server and the error went away.


Setting up replication for MyISAM tables?

Since everything is MyISAM, here is what you need to do

STEP 01) Configure Master to be a Replication Master

If you do not have server-id defined in /etc/my.cnf, add one

[mysqld]
server-id=1

and service mysql restart

STEP 02) Check Binary Logging on the Master

If binary logging is active on the Master, do this

RESET MASTER;

to zap all binary logs and start fresh.

If binary logging is not active, do this

Add log-bin=mysql-bin under [mysqld] in /etc/my.cnf
service mysql restart

STEP 03) Copy /var/lib/mysql

You could set rsync to perform a brute force byte-by-byte copy of your MyISAM tables from /var/lib/mysql on one server to /var/lib/mysql on another server. You would have perform multiple rsyncs until the last rsync, which is very quick. Then, you would do a full shutdown of MySQL and perform one more rsync. Start mysql on both Master and Slave.

STEP 04) Create Replication User on the Slave

GRANT REPLICATION CLIENT,REPLICATION SLAVE
ON *.* TO replicator@'PrivateIPofSlave' IDENTIFIED BY 'r3pl1cAt0r';

STEP 05) Set up replication

Run this command on the Slave

CHANGE MASTER TO
MASTER_HOST='PrivateIPofMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3pl1cAt0r',
MASTER_LOG_FILE='XXXX',
MASTER_LOG_POS=YYYY;
START SLAVE;

What is XXXX and YYYY ???

XXXX is the last binary log file at the time you perform final rsync. There is a copy of that file in /var/lib/mysql on the Slave. Just run ls -l /var/lib/mysql/mysql-bin.0* on the Slave and use the last binary log.
YYYY is the position of the last binary log file at the time you perform final rsync. Interestingly, the position is also the filesize of the binary log. Just run ls -l /var/lib/mysql/mysql-bin.0* on the Slave and take the last binary log's filesize to be the position YYYY.

STEP 06) Check replication

Run this

SHOW SLAVE STATUS\G

If Slave_IO_Running is Yes and Slave_SQL_Running is Yes, Congratulations You are Done !!!

I have written similar posts on using rsync like this:

MySQL slave replication reset with no Master Downtime (using MyISAM) (Jul 08, 2011 on ServerFault) with Sample Code !!!
Create a MySQL slave from another slave, but point it at the master (Apr 08, 2011 on ServerFault)
MySQL Replication - Introduce new Slave to replication (Feb 23, 2011)


Mongodb restrict access on collections level

They have added collection-access ACLs, see here: http://docs.mongodb.org/manual/core/collection-level-access-control/


I only want one maximum value but it returns all the rows

It returns many rows, because the MAX-function goes through every group(each customer, as you have grouped the result pr customer), and for each customer, it finds the MAX P.total_amount.
I don't know how your data looks, but if each customer really just have one P.total_amount, it just prints each customers P.total_amount.

What you probably want, is something like this:

SELECT TOP 1
    E.cust_name,
    E.cust_ID,
    MAX(P.total_amount)
FROM    customer E,
    room_booking RB,
    payment P,
    booking_details B
WHERE   P.booking_id    = RB.booking_id
AND     RB.cust_id      = E.cust_id
AND     B.booking_id    = RB.booking_id
AND     B.check_in_date LIKE'%-&MonthInput-2018%'
GROUP BY E.cust_name, E.cust_ID
ORDER BY MAX(P.total_amount) DESC;

What we do here is the same as your original query, but we sort the list by the total amount, and selects just the first row, which must be the customer with the highest value.

If, continuing my assumption, that each customer only have one P.total_amount per month, then the grouping might not even be needed:

SELECT TOP 1
        E.cust_name,
        E.cust_ID,
        P.total_amount
FROM    customer E,
        room_booking RB,
        payment P,
        booking_details B
WHERE   P.booking_id    = RB.booking_id
AND     RB.cust_id      = E.cust_id
AND     B.booking_id    = RB.booking_id
AND     B.check_in_date LIKE'%-&MonthInput-2018%'
ORDER BY P.total_amount DESC;

Note that not all DBMS-systems support the TOP syntax.
SQL Server does. Other systems like Postgres or MYSQL use LIMIT to a similar effect. Some DBMS also support the standard syntax OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY (SQL Server 2012+, Postgres, Oracle 12)

See wikipedia: SELECT (SQL): Limiting result rows


Using SQL Server Reporting Services for monitoring database performance

SSRS (SQL Server Reporting Services) isn't a monitoring tool, it's a reporting tool.

You can absolutely use it to develop your own reports, but you'll have to be collecting the data, first. SSRS will not help you collect or analyze the data, but it will be able to present the data in whatever way you like and develop the reports to behave (such as varying parameters, etc.).


Partitioning increases CPU usage in mariadb

OVERHEAD
What needs to be understood is a partitioned table's physical implementation.
For example, in my old post (Aug 31, 2014 : How does subpartitioning actually work and what physical files are created?), I displayed what a partitioned table looks like:
C:\>cd \MySQL_5.6.15\data\test

C:\MySQL_5.6.15\data\test>dir nums_comp*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.15\data\test

08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp1.ibd
08/31/2014  04:09 PM             8,594 nums_composite.frm
08/31/2014  04:09 PM                96 nums_composite.par
               8 File(s)        598,514 bytes
               0 Dir(s)  686,691,409,920 bytes free

C:\MySQL_5.6.15\data\test>

I have another example in Feb 16, 2015 : MySQL Create Table with Partition Slow on server machine
Each partition is treated as a separate InnoDB table with a distinct tablespace.
What overhead exists ?

Memory Usage for the Information Schema (Adding new tables -- memory usage increases)
Index Stats Separated By Partition (Do I need to index a key that I partition by?)
Open File Handles (In your case, 33 [1 File Handle for Each .ibd file, 1 for .frm])

While these things help explain the overhead, you must still be wondering where the CPU activity is coming from ???
CPU
There will still be some CPU activity based on the open file handles. How ?
The option innodb_open_files places a limit on the number of open file handles against all InnoDB tables through the MySQL Instance.
If table info needs to be examined by monitoring software, more queries make more CPU.
If there are any queries that do not access disk (such as SHOW PROCESSLIST; or SHOW GLOBAL STATUS;) or reads from INFORMATION_SCHEMA, more CPU. (See my old ServerFault post from 2011-08-10 1 billion mysql queries in 24 days? Can something be wrong?)
Opening and closing files handles to access certain tables and read 16K pages into the InnoDB Buffer or marking pages in the InnoDB Buffer Pool as old.


MySQL Partitioning: why it's not taking appropriate partition

The HASH partitioning scheme means MySQL translates your arbitrary numerical value into its own hash value. You have defined 366 partitions. What do you think would happen if your query were:

EXPLAIN PARTITIONS SELECT * FROM temp where PartitionID = 400

?

Your PartitionID cannot mean in this case the real partition's ID/name, since there is no partition 400.

Now, just between the two of us, you might be interested to learn that MySQL's HASHing function is a simple modulus. Thus, 0 maps to partition p0, 1 maps to partition p1, and 400 maps to partition 34 (== 400-366).

Generally speaking you should not be too interested in the identify of the particular partition being used. You should be more interested to know that there is a good balance of partitions.

If the balance doesn't seem right to you, you might consider using the KEY partitioning scheme, where the hashing algorithm is based on a checksum function, which means values are more "randomly" spread as compared to the HASH modulus partitioning scheme.


Microsoft Optimal method for columns into 1 word: XML Raw or Concat

In general, if you truly need to know what will be the fastest for your workload then you need to do your own benchmarking. Take a look at the following query plans:

Which one do you think would be faster if you had to pick one without any additional context? I would pick the bottom one. It simply appears to do less work because there's no nested loop join and there's no UDX operator. To generate the above query plans, put 6.5 million rows into a temp table:

SELECT s1.*
INTO #source_data
FROM master..spt_values s1
CROSS JOIN master..spt_values t2;

This table has six columns with different data types. Some allow nulls and some don't. So it seems to meet your criteria. The top query takes about 3X longer on my machine than the bottom one:

DROP TABLE IF EXISTS #t;

SELECT HASHBYTES('SHA2_256', (SELECT name, number, type, low, high, status FOR XML RAW)) hash_value
INTO #t
FROM #source_data
OPTION (MAXDOP 1);

GO

DROP TABLE IF EXISTS #t;

SELECT HASHBYTES('SHA2_256', CONCAT(name, '|', number, '|', type, '|', low, '|', high, '|', status)) hash_value
INTO #t
FROM #source_data
OPTION (MAXDOP 1);

This seems like a perfectly reasonable result. It will add overhead to convert data to XML and to have a nested loop join. Why do that when you don't have to? In your question you suggest that sometimes the FOR XML RAW technique is faster for your data. If so, there's something about your data or testing methodology that you aren't telling us. My suggestion is to come up with a sharable reproduction that demonstrates that the FOR XML RAW method is significantly faster and to ask a new question. As is we have to guess what's going on, which isn't helpful for anyone.


Postgres how to reference FK to primary composite key?

A foreign key constraint can only reference a unique or primary key constraint.
So if your primary key is defined on (id1, id2) and neither id1 nor id2 are unique, there is no way that you can reference the individual columns in a foreign key.
If, on the other hand, id1 and id2 are unique, then one of them should be the primary key, not the combination of both.
It is not clear what you want to achieve, but I'd suspect that the problem goes away when you normalize the tables.


How to see whether db was hit or not when I run query in my app

Realtime -- I don't think so.

Log -- Turn on the "general log".  (The specifics depend on the version of MySQL you are running.)  That will write all queries to a file or table for later inspection.  Be sure to turn off the General log after you are finished; it could fill up disk rather fast.


"Table of Constants" - is this common practice?

Joe Celko mentions tables of constants in a couple of his books.

He suggests if using a table that a check constraint is added that ensures the table can contain no more than one row.

CREATE TABLE Constants
(
lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY CHECK (lock = 'X'),
pi FLOAT DEFAULT 3.142592653 NOT NULL,
e FLOAT DEFAULT 2.71828182 NOT NULL,
phi FLOAT DEFAULT 1.6180339887 NOT NULL
);

Or alternatively a view can be used for a similar purpose.

CREATE VIEW Constants
AS
SELECT *
FROM
(VALUES(3.142592653,
        2.71828182,
        1.6180339887)) V(pi,e,phi)

Not something I've used much, if at all, myself but always worth having additional possible techniques to consider.

The specific use case in your question isn't something I would consider a table of constants for though. TBH the hardcoded same "hometown" for everyone just seems a nonsensical requirement.


You could create a PL/SQL script of course, but I think that let a simple query to build the ALTER statements is a better choice:

SELECT 'ALTER USER ' || username || ' PROFILE USR_PROFILE;'
FROM dba_users WHERE username NOT IN (...);

Of course you must be sure of which users you have to include in the WHERE clause.


SSIS Package Error when Scheduled in Job

There is a step-by-step instructions on how another person had fixed it via this link.  Essentially, the set-up best practices weren't followed when setting up the SSIS service and their DBA/admin used the network service.  This caused some permissions not to grant properly during set-up.


Reasonable values for thread_cache_size in relation with Connections

Keep in mind that thread_cache_size controls a "cache".  It is the number of threads (or processes, depending on the OS) to keep open just in case another connection request comes soon.  There is some cost in creating a thread. (This depends on the OS, etc.)

For Windows, thread_cache_size should be zero.  (Sorry, I don't have a reference stating that.)

If thread_cache_size / max_connections < 2% or > 15%, it may be poorly tuned.

If Threads_running / thread_cache_size > 2, then the cache may not be big enough.  (This depends on whether the number of connections is stable or fluctuating wildly.)

I suggest that Threads_created / Connections is not a good metric -- What if you have only one Connection/sec or 1000 Connections/sec?  Hence, I prefer this:  If Threads_created / Uptime > 3/second, I suggest that the cache needs increasing.

In a survey of systems, only 10% had Threads_created / Uptime > 0.26 / sec.

MySQL 5.6.8 automatically computes thread_cache_size based on max_connections.

MariaDB 10.2 takes a different approach; it "increases value of thread_cache_size to 32; adds 5 minute timeout before automatically removing threads from thread cache."  This feels like a good algorithm.

Bottom line:  One should rarely have to worry about thread_cache_size.


I don't know if there's a cheaper way to create a guarantee such as you are asking. But Foreign Key constraints are the tool for the job you are wanting to accomplish. It is probably better to use the right tool for the job in the long run. A hack solution might end up being a nightmare in administration and performance down the road.

Here is one possibility to get the constraint in with minimal downtime (logic only)

Create duplicate table structure of table 1 and table 2 (no data)
Create Unique Index on table1, and FK on table 2
Insert columns from table 1 into duplicate table 1, do the same for table 2
After you get all the info ported over, rename table1/table2 to old_table1/old_table2
Rename table1_new/table2_new to table1 and table2


Possible to delete a backup db file while it is being restored?

Yes, you can restore over the net. RESTORE DATABASE .. FROM FILE = '\server\share...'. I.e., an UNC path. Note that the service account for the SQL Server service need permissions on that share.


How do I add/remove tables to a Windows Azure database?

The Manage button is correct. It should bring up a page in a new window/tab that prompts for the user name and password to connect to the database. If that isn't working, there's something wrong with your web browser configuration (does it allow popups from the Azure site?).

Alternatively, the database server can be set up to allow external connections by managing the firewall rules. The interface makes it very easy to poke a hole just for your client computer. You can then connect to the database directly using Management Studio on your local machine. (Don't forget to delete the firewall rule when you're finished.)


Assuming your linked server provider supports it, you should be able to use a 4-part name (your example only used 3-parts)

I'm referencing Transact-SQL Syntax Conventions-Transact-SQL.

server_name .[database_name].[schema_name].object_name

WHERE

  server_name Specifies a linked server name or remote server name.

  database_name Specifies the name of a SQL Server database when the
  object resides in a local instance of SQL Server. When the object is
  in a linked server, database_name specifies an OLE DB catalog.

  schema_name Specifies the name of the schema that contains the object
  if the object is in a SQL Server database. When the object is in a
  linked server, schema_name specifies an OLE DB schema name.

  object_name Refers to the name of the object.


Change language used to parse decimal number

If you can't change the application and the data is hard-coded into the ad hoc queries that way, first of all you should file a bug with the authors, because this is wrong on so many fronts. You can "solve" the problem by using a cumbersome and inefficient workaround, changing the table to have a computed column:

ALTER TABLE dbo.Payment ALTER COLUMN cost varchar(32);
ALTER TABLE dbo.Payment ADD dcost
  AS (CONVERT(decimal(5,2), REPLACE(cost, ',', '.')));

Now your INSERT will work (well, as long as the value can successfully be converted); you just need to update your queries to look at the dcost column instead. If the application also has hard-coded queries that expect a decimal from the cost column, well, it should still be able to do that by pulling the string value. But you really need to tell the application vendor to fix this because it is badly, badly broken.

Originally, when the problem was dumbed down to a local CAST problem, I had written this:

In 2008 R2, I would say, simply, replace the commas with decimals. I don't know that there's a clean way to do this in an existing installation (and I don't know that reinstalling everything, including even the OS, in a specific European locale will solve it).

DECLARE @num varchar(32) = '3,6';
SELECT CONVERT(decimal(5,2), REPLACE(@num, ',', '.'));

Of course, this doesn't help if your stored or incoming data is of the 0.000,00 variety; you'll have to perform multiple replace steps in that case (e.g. REPLACE(REPLACE(@num,'.',''),',','.')). Really ugly if you can't fix the data.

(Also, please always specify the scale and precision when defining decimals. This post is about varchar, but the same principles apply.)

Or, fix the incoming data so that it uses decimals instead of commas.

In 2012 or above, of course, use PARSE() (or TRY_PARSE()), as @Remus explained. Here is an example using TRY_PARSE() with the Spanish culture (es-ES):

SELECT TRY_PARSE('3,6' AS DECIMAL(5,2) USING N'es-ES');

The PARSE() documentation has the list of valid cultures.


What makes for a good index in this scenario?

Ronaldo's comment got me thinking and the more I think about it, I would answer my own question as: if every row of a table needs to be read, then there is no way to order the way that data is stored in order to improve the read performance. In other words, there's no index that would make the update statement in my scenario any faster because every row needs to be read to be updated in that query (regardless of the fact that the query has a filter because the filter doesn't actually return any less rows than everything in the table.)


Are these linked server queries susceptible to SQL injection?

Yes, this is unsafe. Try passing this:

@TableName = 'FakeTableName'';DROP TABLE Foo;PRINT''You have been Pwned'

One way to avoid this is to pass @TableName as a parameter to the dynamic SQL. Like so:

SET @DynamicSQL = '... WHERE TABLE_NAME = @TableName'

And then do this:

INSERT INTO @Receiver EXEC sp_executesql @DynamicSQL, @TableName

From the wording of your question, I am not sure if you are saying that you can't run sp_executesql or that you are not sure about the syntax. If you have to run the statement using only EXEC there is a method that works, but only on a Linked Server. You do do this:

SET @DynamicSQL = '... WHERE TABLE_NAME = ?'

Followed by this:

INSERT INTO @Receiver EXEC (@DynamicSQL, @TableName) AT YourLinkedServer


Do you have a rough idea of the expected max length?

varchar(X) is limited to 255 character before MySQL 5.0.3 and 65k at most with MySQL 5.0.3 link

Text data type should be big enough.

You should also consider an update of your data model as suggested by @oNare


Computing Matching Data Points from Fuzzy Timestamps in Postgresql

In your second approach, with the self join, you could remove duplicates using row_number(),

Partition by l.charttime, order by the time difference and filter for row_number = 1.

I think performance will be horrible, however. Because of the cartesian join this will be an O(size(series 1) x size(series 2)) operation.

Having both l.charttime and r.charttime inside functions may also be causing trouble. Try refactoring to (in pseudo code)

    r.charttime < l.charttime + 3600
and r.charttime > l.charttime - 3600

.. and see how the query plan looks. I presume there's an index on charttime. Without one no approach will be fast. Indeed, two partial indexes, one on Series 1 and one on Series 2 may be even better.


Using Partial String match from one query results in where clause for another SQL Server 2012

You could try using CTE. This will take your first query as base for the CTE and then use it to join on the second query. I have not tested this code but I think it may work:

WITH Answer (Field_ID, Timestamp, Name, Value, ID)
AS
(
SELECT TOP 20 MshiftV1.Field_ID
    , MshiftV1.Timestamp
    , LEFT(MshiftF1.Name,12) AS Name
    , MshiftV1.Value
    , MShiftF1.ID
FROM [BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Values_5Min_RealTime_V6] AS MshiftV1
INNER JOIN [BabyCare].[dbo].[Meanshift_All_Tags_Alarms_Fields_5Min_RealTime_V6] AS MshiftF1
    ON MshiftV1.Field_ID = MshiftF1.ID
WHERE MshiftV1.Quality = 1
    AND MshiftV1.Value NOT LIKE '%INF%'
    AND MshiftV1.Value NOT LIKE '%IND%'
    AND MshiftF1.Name NOT LIKE '%_Splice_%'
    --ORDER BY MshiftV1.Timestamp, ABS(convert (float, MshiftV1.Value)) DESC --Order by not needed in CTE
)
SELECT BSCv1.Timestamp AS "Field Timestamp"
    , BSCV1.Value AS Correlation_Pair
    , BSCV2.Value AS "Percent Change"
    , BSCF1.Name AS "Field Name"
    , BSCF1.ID AS "DEV Name ID"
    , BSCV1.Field_ID AS "Field ID"
    , BSCF2.Name AS "Value Name"
    , BSCV2.Field_ID AS "Value ID"
    , BSCF2.ID AS "V ID"
    , BSCv2.Timestamp AS "Value Timestamp"
FROM [BabyCare].[dbo].[BSC_v3_values] AS BSCV1
INNER JOIN Answer
    ON Answer.Name = LEFT(BSCV1.Value, 12)-- This may be the link you are seeking
        AND DATEADD(MINUTE, -10, Answer.Timestamp) = BSCv1.Timestamp
INNER JOIN [BabyCare].[dbo].[BSC_v3_fields] AS BSCF1
    ON BSCV1.Field_ID = BSCF1.ID
INNER JOIN [BabyCare].[dbo].[BSC_v3_values] AS BSCV2
    ON BSCv1.Timestamp =BSCv2.timestamp
INNER JOIN [BabyCare].[dbo].[BSC_v3_fields] AS BSCF2
    ON BSCV2.Field_ID = BSCF2.ID
        AND BSCV1.Quality = 1
        AND BSCF1.Name LIKE '%Dev_N%'
        AND BSCF2.Name LIKE '%Dev_V%'
        AND RIGHT(SUBSTRING(BSCF1.Name, 5, 5), 3) = RIGHT(SUBSTRING(BSCF2.Name, 5, 5), 3)
        AND BSCv1.Timestamp = BSCv2.Timestamp
WHERE BSCV1.Quality = 1
    AND BSCF1.Name LIKE '%Dev_N%'
    AND BSCF2.Name LIKE '%Dev_V%'
    AND RIGHT(SUBSTRING(BSCF1.Name, 5, 5), 3) = RIGHT(SUBSTRING(BSCF2.Name, 5, 5), 3)
    AND BSCv1.Timestamp = BSCv2.Timestamp
ORDER BY ABS(convert(float,BSCV2.Value)) DESC, BSCv1.Timestamp DESC


I got something finally,

After further reading the DOC and making some test:

Yes, by default, depending on the size of the row, some blobs will be stored in row and other out of row.
Yes, following this you should be able to determinate it:

  LOB values are stored out-of-line when any of the following situations
  apply:

  If you explicitly specify DISABLE STORAGE IN ROW for the LOB storage    clause when you create the table.
  If the size of the LOB is greater than approximately 4000 bytes (4000    minus system control information), regardless of the LOB
  storage    properties for the column.
  If you update a LOB that is stored out-of-line and the resulting LOB    is less than approximately 4000 bytes, it is still stored out-of-line.

  LOB values are stored inline when any of the following conditions
  apply:

  When the size of the LOB stored in the given row is small,    approximately 4000 bytes or less, and you either explicitly specify ENABLE STORAGE IN ROW or the LOB storage clause when you create the table, or when you do not specify this parameter (which is the
  default).
  When the LOB value is NULL (regardless of the LOB storage properties    for the column).

No, this is just the default option, when the blob is too big(arpox 3800) it will be stored out of row


How can I copy data from one MySQL server to another based on a SELECT statement (then delete the data from the original)?

You are looking for pt-archiver, part of the Percona Toolkit. The tool can purge rows from a table; write rows from a table to file; purge rows from a table while copying them to another table (possibly another server)...

It does so in chunks, so like 1,000 rows at a time, so that there is no high load on your server.


RDS or Object DB for 99.99% static data of 25 million rows in one table

You will keep the data 'forever', correct?  That is, there is no 'purging'.

Is there potentially a different entry for each year?  Or multiple entries for each year?

Why not have a single, non-partitioned, InnoDB, table with the PRIMARY KEY starting with the postcode.  Perhaps you want the address and DATETIME as other columns in the PRIMARY KEY.

PRIMARY KEY(postcode, address, as_of)

That would let you get an ordered history of who lived at $pc, $addr:

SELECT * FROM tbl
    WHERE postcode = $pc
      AND address = $addr
    ORDER BY as_of;

It would be very fast (much less than 1 second), regardless of hardware.  (With ENGINE=CSV, you are looking at minutes or hours for any query.)

If you envision other queries, let's see them.


After moving database (backup, restore), I have to re-add user

This is the difference between logins and users and how they relate to each other:

Logins - Instance level principals that allow an entity to connect to the SQL Server instance.  They do not, by their nature, grant any access to databases on the instance.  The exception to this is a login with sysadmin rights can use a database because they are sysadmin, but because of sysadmin level permissions.
Users - Database level principals that allow an entity to connect to a SQL Server database.  Users are associated with logins via SIDs, creating a relationship between the two and allowing a login to connect to the instance and then use the associated user to connect to the database.

What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship.  This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected.  You can fix this with the following SQL:

ALTER USER [foo] WITH LOGIN=[foo]

You can use the following query in the context of your database to check for orphans:

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,'Orhphaned!') [login_name]
    ,sp.type_desc [login_type]
from
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in ('S','U','G')
    and dp.principal_id >4
order by sp.name


How to delete all rows from all tables in postgresql?

Method 1: create a file with a set of sql-statements to truncate each table, then execute those statements from the file.

testdb=# \t
Tuples only is on.
testdb=# \o truncate_all_tables.sql
testdb=# SELECT 'TRUNCATE ' || table_name || ';' FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';
testdb=# \o
testdb=# \t
Tuples only is off.
testdb=# \i truncate_all_tables.sql
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE
TRUNCATE TABLE_NAME CASCADE

Method 2: create a database dump with schema only, recreate the database and restore the dump.

# pg_dump -U postgres -v -Fc -s -f testdb.dump testdb
# dropdb -U postgres testdb
# createdb -U postgres testdb
# pg_restore -U postgres -v -d testdb testdb.dump


SQL Server auto restore only log where not restored before

I assume that your database is in the restoring state and you alredy have restored a full backupset WITH NORECOVERY.

In this case you can inspect the contents of the log backups using RESTORE HEADERONLY: this will return the column FirstLSN, that you can use to compare with MAX(redo_start_lsn) from sys.master_files.
All log backups with FisrtLSN < MAX(redo_start_lsn) can be safely skipped.


How to perform a 3 table join with sparse lookup table

First part returns the Cartesian product of companies and products.

from       companies c
cross join products p

Then for every combination of Company - Product it checks if exists a record in the companies_products table.

select     c.id,
           c.name,
           p.id,
           p.name,
           if(cp.company_id is null, 'status: enabled', 'status: disabled') as status
from       companies c
cross join products p
left join  companies_products cp
on         cp.company_id = c.id
and        cp.product_id = p.id
;

id | name         | id | name      | status
-: | :----------- | -: | :-------- | :---------------
 1 | Test Company |  1 | Product 1 | status: disabled
 1 | Test Company |  2 | Product 2 | status: disabled
 1 | Test Company |  3 | Product 3 | status: enabled

dbfiddle here

Or you can use EXISTS to check if there is a record that match both identifiers.

select     c.id,
           c.name,
           p.id,
           p.name,
           if (exists (select 1
                       from   companies_products cp
                       where  cp.company_id = c.id
                       and    cp.product_id = p.id)
                      ,'status: enabled', 'status: disabled') as status
from       companies c
cross join products p
;

id | name         | id | name      | status
-: | :----------- | -: | :-------- | :---------------
 1 | Test Company |  1 | Product 1 | status: enabled
 1 | Test Company |  2 | Product 2 | status: enabled
 1 | Test Company |  3 | Product 3 | status: disabled

dbfiddle here


You just need to use commas instead of AND:

SELECT payment_method,
       SUM(net_amount),
       DATEADD(MONTH, DATEDIFF(MONTH, 0, transaction_time),0) AS OrderDateByMonth
FROM [Database]
WHERE [criteria]
GROUP BY payment_method,     -- <--- comma here
         DATEADD(MONTH, DATEDIFF(MONTH, 0, transaction_time),0)
ORDER BY payment_method,     -- <--- and here
         OrderDateByMonth ;


I just upgraded PostgreSQL with yum on Amazon Linux, now PostgreSQL won't start. What do I do?

Amazon's packages for PostgreSQL will upgrade from 9.1 to 9.2 as if that was a normal minor update. It isn't; see the PostgreSQL version policy, which makes it clear that the major version is "x.y", eg "9.1", "9.2", etc. This version policy may be weird and perhaps unwise, but it's also widely understood and something you would expect packagers to understand. The documentation for upgrading a PostgreSQL cluster explains this point very clearly.

The Amazon packages don't seem to get that, and just upgrade PostgreSQL. This is a very bad idea. Upgrades can involve downtime and backward-compatibility-breaking changes. C extension functions must be recompiled. A dump and reload may be required if pg_upgrade can't be used with your database. Even if pg_upgrade can be used, downtime can be significant.

See Amazon's severe PostgreSQL packaging blunder by Andrew Dunstan, and this bug report on the AWS forums.

Amazon has had a FAQ entry for this, but it's really the wrong answer. The right answer is "don't upgrade without asking!". Not only that, but doing a pg_upgrade can take some preparation and won't work for all databases. This is really bad advice. (They seem to have silently removed this FAQ entry now, that'll help those customers who're still updating from older versions!)

Nonetheless, pg_upgrade is your only easy option once this has happened. If you can't use it, you'll need to roll back to PostgreSQL 9.1, pg_dump your DBs and pg_dumpall --globals-only as well, then install 9.2 and load the dumps. See upgrading a PostgreSQL cluster


Does a database restore remove existing stored procedures

A restore will completely wipe the target database (if it exists, otherwise created of course).

Any changes to tables, code security and data will be lost in the target database, which will be 100% identical to the source database at the time the backup happened

Yes it will
Yes it will


How to know exactly when my Database / Log files grew?

Here is a script that I use. I don't remember the source, but it is handy, as it will show you the growth events.

DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

-- rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.',@filename);
SET @ec = CHARINDEX('_',@filename)+1;
SET @efn = REVERSE(SUBSTRING(@filename,1,@bc));
SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename)));

-- set filename without rollover number
SET @filename = @bfn + @efn

-- process all trace files
SELECT
  ftg.StartTime
,te.name AS EventName
,DB_NAME(ftg.databaseid) AS DatabaseName
,ftg.Filename
,(ftg.IntegerData*8)/1024.0 AS GrowthMB
,(ftg.duration/1000)AS DurMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg
INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE (ftg.EventClass = 92  -- Date File Auto-grow
    OR ftg.EventClass = 93) -- Log File Auto-grow
ORDER BY ftg.StartTime


Trigger Definer Already Same But User Still Denied

Per documentation on create user, netmasks are only valid on the previous class A,B,C addresses. So masks such as 255.255.240.0 are invalid.
I haven't found a feature request for this feature, however obvious it seems, on https://jira.mariadb.org if you'd like to request one.


pgAdmin 4 version 3 fails to open a second time

“Elephant” icon menu

When pgAdmin is running, an elephant icon menu appears. Look at the upper right corner of the screen. The elephant refers to Slonik, the Postgres mascot.

Choose New pgAdmin Window menu item.

FYI, pgAdmin 4 is built as a web app, not a desktop app, even when run locally on your own computer. Note that the internal web server provided by pgAdmin 4 is running continuously, even after you close all windows. Use the Shutdown server menu item, as seen above, if you want to stop that web server.

p.s. I agree, it is very confusing that clicking the icon does not do the same thing (when pgAdmin is already running).


For all business analysis leading to database architecture, I recommend writing rules:

A route has 2 or more stations
A Station can be used by many routes
Stations on a route come in a specific order

The 1st and 2nd rules as you noticed implies a many to many relationship so you concluded rightfully to create routeStations.

The 3rd rule is the interesting one. It implies that an extra column is needed to fit the requirement. Where should it go? We can see that this property depends on Route AND Station. Therefore it should be located in routeStations.

I would add a column to table routeStations called "stationOrder".

+-------------+---------------+---------------
| routeId(fk) | stationId(fk) | StationOrder |
+-------------+---------------+---------------
|     1       |       1       |       3      |
+-------------+---------------+---------------
|     1       |       3       |       1      |
+-------------+---------------+---------------
|     1       |       4       |       2      |
+-------------+---------------+---------------
|     2       |       1       |       1      |
+-------------+---------------+---------------
|     2       |       4       |       2      |
+-------------+---------------+---------------

Then querying becomes easy:

select rs.routeID,s.Name
from routeStations rs
join
Stations s
on rs.stationId=s.StationId
where rs.routeId=1
order by rs.StationOrder;

+-------------+---------------+
| routeId(fk) | stationId(fk) |
+-------------+---------------+
|     1       |       C       |
+-------------+---------------+
|     1       |       D       |
+-------------+---------------+
|     1       |       A       |
+-------------+---------------+

Notes:

I fixed the StationId in RouteStations in my example. You are using the StationName as the Id.
If you don't use a route name, then there's not even a need for routeId since you can get that from routeStations
Even if you would link to the route table, your database optimizer would notice it doesn't need that extra link and simply remove the extra steps.

To develop on note 3, I've built the use case:

This is Oracle 12c Enterprise.

Note that in the execution plan below that table routes isn't used at all. the Cost Base Optimizer (CBO) knows it can get the routeId directly from routeStations's primary key (step 5, INDEX RANGE SCAN on ROUTESTATIONS_PK, Predicate Information 5 - access("RS"."ROUTEID"=1))

--Table ROUTES
create sequence routeId_Seq start with 1 increment by 1 maxvalue 9999999999999 cache 1000;

CREATE TABLE routes
(
  routeId  INTEGER NOT NULL
);

ALTER TABLE routes ADD (
  CONSTRAINT routes_PK
  PRIMARY KEY
  (routeId)
  ENABLE VALIDATE);

insert into routes values (routeId_Seq.nextval);
insert into routes values (routeId_Seq.nextval);
commit;

--TABLE STATIONS
create sequence stationId_seq start with 1 increment by 1 maxvalue 9999999999999 cache 1000;

create table stations(
   stationID INTEGER NOT NULL,
   name varchar(50) NOT NULL
);

ALTER TABLE stations ADD (
  CONSTRAINT stations_PK
  PRIMARY KEY
  (stationId)
  ENABLE VALIDATE);

insert into stations values (stationId_seq.nextval,'A');
insert into stations values (stationId_seq.nextval,'B');
insert into stations values (stationId_seq.nextval,'C');
insert into stations values (stationId_seq.nextval,'D');
commit;
--

--Table ROUTESTATIONS
CREATE TABLE routeStations
(
  routeId       INTEGER NOT NULL,
  stationId     INTEGER NOT NULL,
  stationOrder  INTEGER NOT NULL
);

ALTER TABLE routeStations ADD (
  CONSTRAINT routeStations_PK
  PRIMARY KEY
  (routeId, stationId)
  ENABLE VALIDATE);

ALTER TABLE routeStations ADD (
  FOREIGN KEY (routeId)
  REFERENCES ROUTES (ROUTEID)
  ENABLE VALIDATE,
  FOREIGN KEY (stationId)
  REFERENCES STATIONS (stationId)
  ENABLE VALIDATE);

insert into routeStations values (1,1,3);
insert into routeStations values (1,3,1);
insert into routeStations values (1,4,2);
insert into routeStations values (2,1,1);
insert into routeStations values (2,4,2);
commit;

explain plan for select rs.routeID,s.Name
from ndefontenay.routeStations rs
join
ndefontenay.routes r
on r.routeId=rs.routeId
join ndefontenay.stations s
on rs.stationId=s.stationId
where rs.routeId=1
order by rs.StationOrder;

set linesize 1000
set pages 500
select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2617709240

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     1 |    79 |     1 (100)| 00:00:01 |
|   1 |  SORT ORDER BY                 |                  |     1 |    79 |     1 (100)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                  |       |       |            |          |
|   3 |    NESTED LOOPS                |                  |     1 |    79 |     0   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ROUTESTATIONS    |     1 |    39 |     0   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | ROUTESTATIONS_PK |     1 |       |     0   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | STATIONS_PK      |     1 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID | STATIONS         |     1 |    40 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("RS"."ROUTEID"=1)
   6 - access("RS"."STATIONID"="S"."STATIONID")

Now the fun part, let's add a column name to the route table.
Now there's a column we actually need in "routes".
The CBO uses the index to find the rowID for route 1, then accesses the table (table access by index rowid) and grabs the column "routes.name".

ALTER TABLE ROUTES
 ADD (name  VARCHAR2(50));

update routes set name='Old Town' where routeId=1;
update routes set name='North County' where routeId=2;
commit;

explain plan for select r.name as routeName,s.Name as stationName
from routeStations rs
join
routes r
on r.routeId=rs.routeId
join stations s
on rs.stationId=s.stationId
where rs.routeId=1
order by rs.StationOrder;

set linesize 500
set pages 500
select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3368128430

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |     1 |   119 |     1 (100)| 00:00:01 |
|   1 |  SORT ORDER BY                  |                  |     1 |   119 |     1 (100)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                  |       |       |            |          |
|   3 |    NESTED LOOPS                 |                  |     1 |   119 |     0   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                  |     1 |    79 |     0   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| ROUTES           |     1 |    40 |     0   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | ROUTES_PK        |     1 |       |     0   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| ROUTESTATIONS    |     1 |    39 |     0   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | ROUTESTATIONS_PK |     1 |       |     0   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN           | STATIONS_PK      |     1 |       |     0   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | STATIONS         |     1 |    40 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("R"."ROUTEID"=1)
   8 - access("RS"."ROUTEID"=1)
   9 - access("RS"."STATIONID"="S"."STATIONID")


'Row Chaining/Overflow' in SQL Server when adding column to table?

There are three separate concepts here and I think that it's important to keep them separate.

1. Adding a column to a table

Adding a column to a table is an optimized operation designed to not take a lot of time. Most of the time it will not require all pages in the table to be modified. You can verify by this by measuring the size of the transaction required to do so or by looking at the space used by the table before and after.

Suppose that you add a column to a table with a default value of 1. The table has 100 billion rows. Does it really make sense to modify every data page to write a "1" for every row? That could take a really long time. Instead, you can interpret the data in a way to work around it. If a value for the new row hasn't been written to the page yet then you can assume that it's the default value.

Martin Smith pointed out that this optimization only apply to Enterprise edition. One rule of thumb is that SQL Server needs to modify every page if the new value for the column isn't constant, such as a computed column or a column with a default of NEWID(). There are edge cases which may result in all pages getting modified, such as this one.

2. Updating a column for an existing row

If you update a column it's possible that the full row will no longer fit on the data page. For heaps, the row will be moved to a new page with a pointer. This is known as forwarded records. For clustered indexes some of the rows will be moved to a new page. This is known as a page split and many people have written extensively about them. If a table has so many forwarded records and page splits that you're experiencing some kind of performance problem then that can be cleaned up with a REBUILD. But you probably have better things to worry about.

3. Rows larger than 8k

As of SQL Server 2008 rows can be larger than 8k. Some of the mechanics involved in doing that are explained in the link that you have in the question. Conceptually this really has very little to do with adding a column to a table.


MySQL : Why are there "test" entries in mysql.db?

Please note what MySQL 5.0 Certification Study Guide

say in its bulletpoints on Page 498 Paragraph 6:

  On Unix, MySQL comes with a mysql_secure_installation script that can
  perform several helpful security-related operations on your
  installation. The script has the following capabilities:

  Set a password for the root accounts
  Remove any remotely accessible root accounts.
  Remove the anonymous user accounts. This improves security because
  it prevents the possibility of anyone connecting to the MySQL server
  as root from a remote host. The results is that anyone who wants to
  connect as root must first be able to log in on the server host, which
  provides an additional barrier against attack.
  Remove the test database (If you remove the anonymous accounts, you
  might also want to remove the test database to which they have
  access).

To get rid of those bad entries, run this please:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test';
FLUSH PRIVILEGES;

As @DTest mentioned in his comment to the question, you can also run mysql_secure_installation to this for you.

If an anonymous user can login to MySQL remotely, a simply disk attack can be launched to hurt the mysql installation. Here is an example:

USE test
CREATE TABLE rolando_tb (a int);
INSERT INTO rolando_tb VALUES (1);
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;

Run insert 30 times and you get a 7GB table

Imagine creating several of these tables in the test database
Imagine creating a Stored Procedure in the test database
The possibilities are endless as long as test and test_% exist in mysql.db

The seriousness of securing the mysql installation has not been fully documented by MySQL AB, and I don't think Oracle is interested in doing so today.

UPDATE 2012-02-18 16:45 EDT

It was suggested by @atxdba's comment that just running 'DROP DATABASE test;' should be the preferred method over touching mysql.db. Dropping the database named test simply removes the database that opens a conduit to a potential security hole.

Please take note of this query:

mysql> select user,host,db from mysql.db;
+------+------+---------+
| user | host | db      |
+------+------+---------+
|      | %    | test    |
|      | %    | test\_% |
+------+------+---------+
2 rows in set (0.09 sec)

Based on this, the following databases can be accessed fully by anonymous users:

test
test_db
test_001
test_1
test_data

While the following databases cannot be accessed fully by anonymous users:

testdb
test1
testdata
Test (Test is different from test in Linux-based systems, but it is still problem for MySQL running in Windows)

You will have to remember this subtle rule based on the mysql.db table. If you do not remember this, creating a test database named test or a database name whose first 5 characters is test_ will reopen the same type of security hole.

The most secure way around having to remember these things is to run these lines after an initial installation:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;

then any database with any name can have a proper authentication setup. You can still run these two lines anytime.

UPDATE 2012-02-24 15:20 EDT

To openly demonstrate the danger of having anonymous users in mysql.db,
I would like to create a user that has only the usage privilege.

I will be using MySQL 5.5.12 on my Desktop

First, look at the mysql.db

mysql> select user,host,db from mysql.db;
+------+------+---------+
| user | host | db      |
+------+------+---------+
|      | %    | test    |
|      | %    | test\_% |
+------+------+---------+
2 rows in set (0.05 sec)

mysql>

According to this, any anonymous Joe can reach these databases.

I'll create a database test_mysqldb

mysql> create database test_mysqldb;
Query OK, 1 row affected (0.00 sec)

mysql> use test_mysqldb
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>

Let's create an plain vanilla user called vanilla@localhost (no password)

mysql> CREATE USER vanilla@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR vanilla@localhost;
+---------------------------------------------+
| Grants for vanilla@localhost                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'vanilla'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql>

Next, from the DOS Command Line, let's connect to the mysql schema

C:\>mysql -uvanilla -Dmysql
ERROR 1044 (42000): Access denied for user 'vanilla'@'localhost' to database 'mysql'

C:\>

OK great. That's what I expected.

Next, from the DOS Command Line, let's connect to the test_mysqldb schema, create a table, and load it with numbers

C:\>mysql -uvanilla -Dtest_mysqldb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE rolando_tb (a bigint unsigned);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO rolando_tb VALUES (1);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM rolando_tb;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
+------+
16 rows in set (0.00 sec)

mysql> SELECT database();
+--------------+
| database()   |
+--------------+
| test_mysqldb |
+--------------+
1 row in set (0.00 sec)

mysql>

Did you see that? A user with USAGE privilege can create a table in a test database and fill it with data. This a clear and present danger. This is why I strongly recommend deleteing those test entries out of mysql.db to deter anonymous users from reaching test databases or accessing newly created test databases (via making a subfolder under the default datadir).

As a reminder, this is how you do it:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;

UPDATE 2013-09-14 20:05 EDT

To demonstrate that the DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user=''; actually works, I ran this on MySQL 5.6.13 today:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select db,user,host from mysql.db where LEFT(db,4)='test';
+---------+------+------+
| db      | user | host |
+---------+------+------+
| test    |      | %    |
| test\_% |      | %    |
+---------+------+------+
2 rows in set (0.43 sec)

mysql> delete from mysql.db where LEFT(db,4)='test';
Query OK, 2 rows affected (0.04 sec)

mysql> select db,user,host from mysql.db2 where LEFT(db,4)='test';
Empty set (0.00 sec)

mysql>

Just as a public service announcement, please run

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;

or just run mysql-secure-installation and put this potential danger to bed.


How can I keep a result set without keeping the transaction open?

When a cursor is defined at the SQL level with DECLARE, there is an option WITH HOLD that makes it continue to exist after commiting the current transaction. Quoting the doc:

  WITH HOLD specifies that the cursor can continue to be used after the
  transaction that created it successfully commits

On the other hand, a refcursor opened by a plpgsql function is closed at the end of the transaction. Quoting the plpgsql doc:

  All portals are implicitly closed at transaction end. Therefore a
  refcursor value is usable to reference an open cursor only until the
  end of the transaction.

To create a cursor in a plpgsql function that may be used outside of its "parent" transaction, it's just a matter of syntax. You want the SQL implementation of a cursor, not the plpgsql variant. For this, EXECUTE must be used.

As an example, here's the skeleton of a function similar to yours, but using SQL-level cursors that outlive the transaction:

CREATE FUNCTION dyncursor(name text) RETURNS VOID AS
$$
DECLARE
  query text;
BEGIN
  query='SELECT 1 as col1, 2 as col2'; -- sample query
  EXECUTE 'DECLARE ' || quote_ident(name) || ' CURSOR WITH HOLD FOR ' || query;
END
$$ language plpgsql;

Demo:

test=> begin;
test=> select dyncursor('foo');
 dyncursor
-----------

(1 row)
test=> commit;
test=> fetch all from foo;
 col1 | col2
------+------
    1 |    2
(1 row)
test=> close foo;
CLOSE CURSOR


Question about Dumps in Oracle (REMAP_SCHEMA)

Even if not told, OldUser can be easily found as:

IMPDP System/Password@TNS
dumpfile=ThefileName.dmp
directory=dumpdir
sqlfile=ThefileName.sql
include=user

This does not import anything, just creates ThefileName.sql under dumpdir with the SQL statements used for creating OldUser, including the username as well of course. Using that information, they will know what to specify in REMAP_SCHEMA=OldUser:NewUser.


BCP export more rows per batch to file (queryout)

-b batch_size Specifies the number of rows per batch of imported data. [BOL]

The parameter changes import batch size only. I'm not aware of anyway to change the export row count reporting.


SQLAlchemy connection error with Postgres

Disclaimer - I'm not a huge sys admin buff, so my information may not be correct..  I also realize that the error says "host" - but I don't think that means it has to be a host entry in pg_hba.conf

When you connect to localhost, it uses the loopback address 127.0.0.1 (unless that configuration on your computer has been tampered with) to connect to your system, which counts as a "HOST" connection..

When you connect to "myComputer" (which happens to be your computer name), I'm pretty sure your OS is smart enough to say "oh hey! that's me!" and doesn't even bother resolving an ip address .. it just does a direct connection. This would be a "LOCAL" connection for postgres..

Try adding local all all ::1/128 trust to your pg_hba.conf file..

Then either restart the server, or connect as a superuser and issue select pg_reload_conf() (preferred method).

  UPDATE: I tried instead of using the name of the machine, using it's static ipv4 IP address, which worked. The problem has something to do with the ipv6 connection.

That still works because it's still using a HOST connection (instead of a LOCAL one).  Try what I suggest and see if you still get an error when connecting to the computer hostname.


Not an Oracle/SQL*Plus problem. The default size of commandline window screen buffer is 300 (lines), and the maximum is 9999 (lines), so it is unable to display 157998 rows.

Better spool the output to a file:

SQL> spool output.txt
SQL> select ...
SQL> spool off

After that you can view the result in the file output.txt


Create menu from multiple meals relationship

You'll need a menus table as you've specified (albeit with a user_id field), so menus(menu_id, user_id, name)

I would then include a linker table to join a meal to a menu, seeing as a menu can have multiple meals and a meal can belong to multiple menus meal_menu(menu_id, meal_id)

EDIT

As Colin 't Hart suggests, for the second part of the question I would also have the chef and user share a common table (probably called users) as they're sharing fields. You could then have a roles table that links each user to a role (chef, diner etc.)

That way, there's only one link from any type of user to a menu.


Optimizing SELECT query consistently showing up in slow-log

The plan is not using the index on (which_photo_id) but the PK (id) index instead, so it has to read a very large part of the index (and all of it if there are less than 25 rows that match the filter). This takes about 4.4 seconds in the specific execution (and finds the 25 rows after reading and rejecting 19M rows):

-> Index Scan Backward using links_photocomment_pkey on links_photocomment
   (cost=0.57..2,819,246.22 rows=7,195 width=41)
   (actual time=555.830..4,929.154 rows=25 loops=1)

   Filter: (which_photo_id = 3115087)
   Rows Removed by Filter: 19100179

I would try these:

replacing the index on (which_photo_id) with an index on (which_photo_id, id).

rewrite the INNER join to a LEFT join (there is a FOREIGN KEY constraint that ensures that the two queries will produce the same results.)

rewrite with a subquery (derived table or CTE) moving the WHERE filter inside) in order to first get the 25 ids (hopefully with an index only scan) and then join the other 2 tables.

Query (with derived table):
SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM
       (
         SELECT id
         FROM links_photocomment
         WHERE which_photo_id = 3115087
         ORDER BY id DESC
         LIMIT 25
       ) AS lim
       INNER JOIN "links_photocomment"
           ON ( "links_photocomment"."id" = lim.id )
       LEFT OUTER JOIN "auth_user"
           ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
           ON ( "auth_user"."id" = "links_userprofile"."user_id" )
ORDER  BY lim.id DESC
LIMIT  25;

Query (with CTE):
WITH lim AS
       (
         SELECT id
         FROM links_photocomment
         WHERE which_photo_id = 3115087
         ORDER BY id DESC
         LIMIT 25
       )
SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM
       lim
       INNER JOIN "links_photocomment"
           ON ( "links_photocomment"."id" = lim.id )
       LEFT OUTER JOIN "auth_user"
           ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
           ON ( "auth_user"."id" = "links_userprofile"."user_id" )
ORDER  BY lim.id DESC
LIMIT  25;


argument 'and' incorrect, sum values of attributes

You should use a comparison operator like > < = >= <=

create table t (id int);
select case when (select count(*) from t) then 1 else 0 end

  ERROR:  argument of CASE/WHEN must be type boolean, not type bigint
  LINE 1: select case when (select count(*) from t) then 1 else 0 end

select case when (select count(*) from t) > 0 then 1 else 0 end
                                          ^^^

| case |
| ---: |
|    0 |

db<>fiddle here


Show that functional dependency pseudotransivity holds using closure test

The definition of Pseudotransitivity given above is equivalent to say the following.

From:

(1) A1 A2 ... An -> B1 B2 ... Bm
(2) B1 B2 ... Bm E1 E2 ... Ej -> D

we can derive:

(3) A1 A2 ... An E1 E2 ... Ej -> D

Here is a possible simple proof.

Let’s try to compute the closure of {A1 A2 ... An E1 E2 ... Ej} from the first two functional dependencies. If this closure contains D then we have proved the hypothesis.

{A1 A2 ... An E1 E2 ... Ej}+ = A1 A2 ... An E1 E2 ... Ej    (starting point)
{A1 A2 ... An E1 E2 ... Ej}+ = A1 A2 ... An B1 B2 ... Bm E1 E2 ... Ej (by using 1)
{A1 A2 ... An E1 E2 ... Ej}+ = A1 A2 ... An B1 B2 ... Bm D E1 E2 ... Ej (by using 2)
No other derivation is possible.

So, since D is included in the closure of {A1 A2 ... An E1 E2 ... Ej}, we have shown that A1 A2 ... An E1 E2 ... Ej -> D.