User already exists error I've created a stored procedure that creates a database, creates a set of tables, copies all of the data from a linked access database into those tables then creates a user and assigns that user to the db_reader, and db_writer roles. When I run this as sysadmin everything works correctly.  When I try to run it as a SQL Server user I have setup it returns an error saying that user already exists.  A look at security on that database does not show the user.

When I check the sid for the server_principles the sid for the user I'm trying set is the same sid I find used for dbo on the database_principles table.

I'm at a loss for why it would set up the user differently depending on the user that is running the stored procedure. Is there anyway around this? I've attemtped to run the sp_changedatabaseowner 'sa' but the user I'm running the stored procedure as doesn't seam to have rights to that even though I've granted execute on it for that user.

Any ideas how to get around this?

The reason for this is that when a database is created by someone who isn't a member of the sysadmin fixed server role their account is setup as the database owner.  Your user will need to be granted the rights to change the database owner to another owner.  However this then creates a new problem, because that user isn't a member of the database owners group any more you'll now need that user to be a member of the security admin fixed server role so that it has the rights to change the permissions within the database.

The better option hear would be to have the customer (or whoever is installing the software) to be a member of the sysadmin fixed server role while the software is being installed, or at least require that they are using a different account than the applications account and that the account has database create and security admin permissions at the instance level.


Oracle query optimization We have a query that runs on a view and its execution time is about 6 min.

SELECT COUNT(*)
FROM someschema.item_total_violations
WHERE channel_id = 122507833
AND item_date >= TIMESTAMP'2015-02-01 00:00:00';

Its not a materialized view so there is no index on the item_date.

The base query of the view is below:

SELECT i.id
    ,i.title
    ,i.body
    ,i.description
    ,i.item_date
    ,i.created_date
    ,i.channel_id
    ,i.community_id
    ,i.person_id
    ,i.message_type
    ,ch.client_id
    ,upper(p.NAME) AS person_name_upper
    ,upper(ch.NAME) AS channel_name_upper
    ,p.NAME AS person_name
    ,ch.NAME AS channel_name
    ,ch.connector_type AS channel_type
    ,cm.NAME AS community_name
    ,cm.network_id
    ,nvl(ct2.cnt, 0) + nvl(ct4.cnt, 0) AS total_violations_count
    ,nvl(ct1.cnt, 0) + nvl(ct3.cnt, 0) AS quarantined_violations_count
    ,nvl(ct5.cnt, 0) AS quarantined_attributes_count
    ,nvl(lh_info.cnt_lh, 0) AS legal_holds_count
    ,nvl(lh_info.cnt_lh_w_no_date, 0) AS legal_holds_count_w_no_date
    ,nvl(lh_info.max_hold_until, to_date('1970-01-01', 'yyyy-mm-dd')) AS legal_hold_max_hold_until
FROM item i
    ,channel ch
    ,person p
    ,community cm
    ,(
        SELECT /*+ NO_MERGE */ pv.item_id
            ,count(*) AS cnt
        FROM policy_violation pv
        WHERE pv.item_id IS NOT NULL
            AND pv.quarantine_status = 'QUARANTINED'
        GROUP BY pv.item_id
        ) ct1
    ,(
        SELECT /*+ NO_MERGE */ pv.item_id
            ,count(*) AS cnt
        FROM policy_violation pv
        WHERE pv.item_id IS NOT NULL
        GROUP BY pv.item_id
        ) ct2
    ,(
        SELECT /*+ NO_MERGE */ aa.item_id AS item_id
            ,COUNT(*) AS cnt
        FROM item_attachment aa
            ,policy_violation pv
        WHERE aa.id = pv.item_attachment_id
            AND pv.quarantine_status = 'QUARANTINED'
        GROUP BY aa.item_id
        ) ct3
    ,(
        SELECT /*+ NO_MERGE */ aa.item_id AS item_id
            ,COUNT(*) AS cnt
        FROM item_attachment aa
            ,policy_violation pv
        WHERE aa.id = pv.item_attachment_id
            AND pv.item_id IS NULL
        GROUP BY aa.item_id
        ) ct4
    ,(
        SELECT /*+ NO_MERGE */ pv.item_id
            ,count(DISTINCT (pv.item_attribute_id)) AS cnt
        FROM policy_violation pv
        WHERE pv.item_id IS NOT NULL
            AND pv.item_attribute_id IS NOT NULL
            AND pv.quarantine_status = 'QUARANTINED'
        GROUP BY pv.item_id
        ) ct5
    ,(
        SELECT lh_rel.item_id AS item_id
            ,count(DISTINCT (lh.id)) AS cnt_lh
            ,max(lh.HOLD_UNTIL) AS max_hold_until
            ,sum(CASE
                    WHEN lh.hold_until IS NULL
                        THEN 1
                    ELSE 0
                    END) AS cnt_lh_w_no_date
        FROM LEGAL_HOLD_RELATION lh_rel
            ,LEGAL_HOLD lh
        WHERE lh.id = lh_rel.legal_hold_id
            AND lh.STATUS = 'A'
        GROUP BY lh_rel.item_id
        ) lh_info
WHERE ch.STATUS = 'A'
    AND i.channel_id = ch.id
    AND i.person_id = p.id
    AND i.community_id = cm.id(+)
    AND i.id = ct1.item_id(+)
    AND i.id = ct2.item_id(+)
    AND i.id = ct3.item_id(+)
    AND i.id = ct4.item_id(+)
    AND i.id = ct5.item_id(+)
    AND i.id = lh_info.item_id(+)

The item table is the biggest and we have tried partitioning the table in chaannel_id but that didn't help our query. I understand that there is no index on item_date in original query and it has effect on performance. Apart from trying out materialized view, is there something that I can improve on the base query of the view?

Why do you use this view at all for this query?

The view collects violations and other stuff, but your query does not care about those at all, just the number of items. The view lists all items regardless of these because of the outer joins, so you basically perform a lot of unnecessary extra work to collect violations and other stuff (the NO_MERGE hint makes this even worse), then you throw it away and count the items based on a totally different criteria.

The query should be something like this:

select
  count(*)
from
  item i
  join channel ch on (i.channel_id = ch.id)
  join person p on (i.person_id = p.id)
where
  ch.status = 'A'
  and i.channel_id = 122507833
  and i.item_date >= timestamp'2015-02-01 00:00:00';

Additionally, you don't need materialized views for indexing a column. Just create the index on the column(s) of the base table(s).

On top of the must-have indexes on primary and foreign keys, another possible candidate is item_date column depending on your data distribution.


Oracle 12c how to purge the FGA audit records before a certain date please I need to know how to purge the FGA audit records before a certain date, I have an Oracle 12c R1 database in which the audit with FGA is implemented, the tables of this are in a tablespace called ts_audit.dbf that It has already had 3 full datafiles and continues to be filled at a dizzying pace due to the intensive use of the database, for this reason I need to purge the oldest records, let's just leave those of the last week.
Thank you very much for your help.

Administering the Audit Trail

begin
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
  (
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    last_archive_time => sysdate - 7
  );
end;
/

begin
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
  (
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    use_last_arch_timestamp => true
  );
end;
/


Problem in Nested Query I have a query like this

SELECT m.title
FROM Movie AS M
WHERE
(SELECT MAX(r.stars)
 FROM rating AS r
WHERE r.mID = M.mID);

I want to show the MAX(r.stars) in my result set, what am I doing wrong?

I don't think you need a sub query here. Does this give you what you need? If so it should be more efficient.

SELECT m.title, MAX(r.stars)
FROM Movie AS m
LEFT OUTER JOIN rating AS r ON r.mID = m.mID;
GROUP BY m.title

Only way it could break that I can see is if you have two movies with the same title and different mID's.


Call python machine learning model from UDF or SP I would like to run records from my database (Postgres), after an update, through a machine learning model. This model resides in a model.py file.

Next to simply porting the python model over to a SP or UDF (I do not know which would be the best to use, if at all).

How might I call this python model and pass the record parameters to the model and return a desired result?

You have basically three options:

Have a program written in Python, that uses PsycoPG, PyGreSQL or some other driver with equivalent functionality to get the data out of PostgreSQL and process it in Python.  Or you could use some toolkit like SQLAlchemy, which works as an Object Relational Mapper.
Write a User Defined Function using PL/Python, which means you actually write UDFs within PostgreSQL using the Python language. If your model works with a certain function model_predict(a, b, c), you can call it in a fashion similar to:

SELECT a, b, c, model_predict(a, b, c) AS prediction
   FROM table
  WHERE <condition>
Export the data from PostgreSQL (with COPY FROM ..., or using any tool such as pgAdmin), into a suitable format (such as CSV), and then import it and process in Python (you could use Pandas read csv, e.g.).


Query takes forever with a NOT EXISTS condition I have two queries, one which has a condition that uc_id and ce_id are in tableUCP , and another which only requires rows which doesn't exist in the tableUCP table.

tableA : ~600k rows
tableB : ~600k rows (exactly equal to TableA)
tableCEM: ~380k rows
tableCE: ~500k rows
tableUCP: ~200k rows
tableND: ~600k rows (exactly equal to TableA)

1st query: (Runs in ~ 20 seconds) (INSERTs 0 rows)

select count(*)
FROM tableA
    JOIN tableB
        ON tableA.user_id = tableB.user_id
        AND tableA.module = tableB.module
    LEFT JOIN (select DISTINCT c_id,module_id from tableCEM where current = TRUE) cem
        ON tableB.module_id = cem.module_id
    LEFT JOIN (select * from tableCE where type = 'module' AND current = TRUE) ce
        ON tableA.module = ce.module
    JOIN (select * from tableUC where current = TRUE) uc
        ON cem.c_id = uc.c_id
        AND tableB.user_id = uc.user_id
    JOIN tableND
        ON uc.uc_id= tableND.uc_id
        AND ce.ce_id = tableND.ce_id
    JOIN (SELECT * FROM tableUCP WHERE current = TRUE) ucp
        ON uc.uc_id = ucp.uc_id
        AND ce.ce_id = ucp.ce_id;

2nd query: (Keep running for hours) (Should INSERT ~600k rows)

select count(*)
FROM tableA
    JOIN tableB
        ON tableA.user_id = tableB.user_id
        AND tableA.module = tableB.module
    LEFT JOIN (select DISTINCT c_id,module_id from tableCEM where current = TRUE) cem
        ON tableB.module_id = cem.module_id
    LEFT JOIN (select * from tableCE where type = 'module' AND current = TRUE) ce
        ON tableA.module = ce.module
    JOIN (select * from tableUC where current = TRUE) uc
        ON cem.c_id = uc.c_id
        AND tableB.user_id = uc.user_id
    JOIN tableND
        ON uc.uc_id = tableND.uc_id
        AND ce.ce_id = tableND.ce_id
    LEFT JOIN (SELECT * FROM tableUCP WHERE current = TRUE) ucp
        ON uc.uc_id = ucp.uc_id
        AND ce.ce_id = ucp.ce_id
    WHERE ucp.uc_id is NULL OR ucp.ce_id is NULL;

The only difference between the two is the last JOIN condition.
Why am I getting so different results for the two queries ?

Query Plans for both the queries

I'm sorry I wouldn't be able to share any sample data, but any help or suggestions would be tremendously helpful.

I have also tried the following:

Using NOT EXISTS for both columns individually.
Using NOT EXISTS for both columns together.
Using IN (tableA EXCEPT tableB).

All three keep running without an end.

The OR condition in your WHERE clause is killing the performance. You only need to NULL-check one of the keys you are joining with, not both.

Also, no need to do a SELECT * FROM tableUCP when just SELECT uc_id, ce_id... would be enough.


SQL Server - db_owner user can't see sys schemas these is a user in database XXX_service (it's technical account) and it has set "Server role" - public and and user mapping as dbo_owner for one database.

Programmer sent logs and there are errors like this one:

Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

I think it is necessary to rise rights but i thought db_owner for concrete database >= sysadmin. Am i wrong? Sorry for my bad english, thank you:)

Ok, i resolved it. Sometimes google first, sorryu for post :)

It was necessary allow Ole Automation Procedures:

    sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

And then grant rights to user:

grant exec on schema::sys to [USER]


Complex user interests query I'm trying to create a query that will return a list of results based on a series of conditions. For example - a list of movies based on interests/preferences.

Example Source Data:

+--------+---------------+-------+-------------+--------+
|MovieID | ShortName     | Genre |  Time(Mins) | Action |
+--------+---------------+-------+-------------+--------+
|   1    | LOTR 1        |Fantasy|    208      |   TRUE |
+--------+---------------+-------+-------------+--------+
|   2    | LOTR 2        |Fantasy|    223      |   TRUE |
+--------+---------------+-------+-------------+--------+
|   18   | Harry Potter 1|Fantasy|    174      |   TRUE |
+--------+---------------+-------+-------------+--------+
|   45   | Love Actually |Romance|    145      |  FALSE |
+--------+---------------+-------+-------------+--------+

A separate table contains a limited series of filters directly related to columns of data. For example:

Filter 1: ShortName (Contains) "LOTR" && Time (IsGreaterThan) 220 Minutes
Filter 2: ShortName (Equals) "LOTR 2"
Filter 3: Action (Is) "FALSE" && Time (IsGreaterThan) 60 Minutes
Filter 4: Genre (IsNot) "Fantasy" && Time (IsLessThan) 180 Minutes

  Filter 1 returns: MovieID (1,2)
  Filter 2 returns: MovieID (2)
  Filter 3 returns: MovieID (45)
  Filter 4 returns: MovieID (45)

Ignore the way results are shown above, it's just to exemplify the returned values.
I need the table of results to be returned in 1 execution of the query.
I'm skilled enough in SQL, however due to legacy platform constraints, I have to achieve this without using looping (While/For) functions.
Is there a name for this kind of query (to return a list of values from a table based on a complex filter statement), and can someone provide an example query to help me to understand the concept.
Please let me know in the comments if you require more information to help.

WITH cte AS
(
SELECT *,
       CASE field WHEN 'ShortName'
                  THEN CASE operator WHEN 'Contains'
                                     THEN ShortName LIKE CONCAT('%', criteria, '%')
                                     WHEN 'Equal'
                                     THEN ShortName = criteria
                                     ELSE 0
                                     END
                  WHEN 'Genre'
                  THEN CASE operator WHEN 'Is'
                                     THEN Genre = criteria
                                     WHEN 'IsNot'
                                     THEN Genre != criteria
                                     ELSE 0
                                     END
                  WHEN 'Time_Mins'
                  THEN CASE operator WHEN 'IsGreaterThan'
                                     THEN Time_Mins > criteria
                                     WHEN 'IsLessThan'
                                     THEN Time_Mins < criteria
                                     ELSE 0
                                     END
                  WHEN 'Action'
                  THEN CASE operator WHEN 'Is'
                                     THEN Action = criteria
                                     WHEN 'IsNot'
                                     THEN Action != criteria
                                     ELSE 0
                                     END
                  ELSE 0
                  END test
FROM movies
CROSS JOIN filters
)
SELECT MovieID, ShortName, Genre, Time_Mins, Action, filterid
FROM cte
GROUP BY MovieID, ShortName, Genre, Time_Mins, Action, filterid
HAVING SUM(test=0)=0
ORDER BY MovieID, filterid;

fiddle

Pay attention to formalized filters storing format.

You may add possible operator/criteria values by adding according variants into CASEs.

For example, if you want to add a filtering variant for to select strict time you must add WHEN 'Equal' THEN Time_Mins = criteria to Time_Mins checking CASE.

But do not forget that criteria datatype is STRING! And Action datatype is string too. Use proper data type convertion when default convertion is not obvious.


Can I specify a different disk for a specific MySQL table? I use my home machine as my personal database for my hobby analytics project.  This machine has an SSD for the main drive where MySQL stores its data.  It also has a secondary hard drive that is larger and not an SSD.  I am about to create a table that I fear might be too large to fit on my SSD; is it possible for me to instead store that one table on the larger (but slower) drive?  If it matters, I'm generally using MyISAM tables but could be persuaded to use InnoDB if that would help.

Migrating s single MyISAM table to another disk is only possible in Linux versions, not Windows, of MySQL with the DATA DIRECTORY and INDEX DIRECTORY clauses of ALTER TABLE on a MyISAM table.
However, in Windows, you can manually move the .MYD and .MYI files to where you want.
UPDATE 2012-01-03 22:03 EDT
Interestinging, MySQL 5.5.15 on my Windows 7 machines says symlink support exists:
mysql> show variables like 'have_sym%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_symlink  | YES   |
+---------------+-------+
1 row in set (0.09 sec)

I also discovered that symlinking is possible in Windows:

http://en.wikipedia.org/wiki/NTFS_symbolic_link
http://en.wikipedia.org/wiki/Symbolic_link#Windows_7_.26_Vista_symbolic_link

On my Windows 7 machines at home, the command line utility mklink exists:
C:\Windows\system32>mklink
Creates a symbolic link.

MKLINK [[/D] | [/H] | [/J]] Link Target

        /D      Creates a directory symbolic link.  Default is a file
                symbolic link.
        /H      Creates a hard link instead of a symbolic link.
        /J      Creates a Directory Junction.
        Link    specifies the new symbolic link name.
        Target  specifies the path (relative or absolute) that the new link
                refers to.

C:\Windows\system32>

I AM SHOCKED !!! That means you can experiment with CREATE TABLE ... DATA DIRECTORY='...' INDEX DIRECTORY='...' in Windows.
I just tried this:
use test
drop table if exists data_table;
drop table if exists data_table_sharded;
CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM;
CREATE TABLE data_table_sharded LIKE data_table;
ALTER TABLE data_table_sharded DATA DIRECTORY='C:\DAT' INDEX DIRECTORY='C:\NDX';

I just got this:
mysql> use test
Database changed
mysql> drop table if exists data_table;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists data_table_sharded;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE data_table_sharded LIKE data_table;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE data_table_sharded DATA DIRECTORY='C:\DAT' INDEX DIRECTORY='C:\NDX';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+----------------------------------+
| Level   | Code | Message                          |
+---------+------+----------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
+---------+------+----------------------------------+
2 rows in set (0.00 sec)

mysql>

You still cannot use the DATA DIRECTORY AND INDEX DIRECTORY option in CREATE TABLE or ALTER TABLE.
UPDATE 2012-01-03 22:45 EDT
I ran these commands
use test
drop table if exists data_table;
drop table if exists data_table_sharded;
CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM;
INSERT INTO data_table VALUES (71),(22),(128),(97),(18),(4),(112),(277);
CREATE TABLE data_table_sharded LIKE data_table;

I made two directories

mkdir C:\dat
mkdir C:\ndx

I created those folders and created hard links for data_table_sharded
C:\MySQL\data\test>mklink /H data_table_sharded.MYD C:\dat\data_table_sharded.MYD
Hardlink created for data_table_sharded.MYD <<===>> C:\dat\data_table_sharded.MYD

C:\MySQL\data\test>mklink /H data_table_sharded.MYI C:\ndx\data_table_sharded.MYI
Hardlink created for data_table_sharded.MYI <<===>> C:\ndx\data_table_sharded.MYI

C:\MySQL\data\test>

I went back to MySQL and loaded the data from data_table:
mysql> flush tables;
Query OK, 0 rows affected (0.05 sec)

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

mysql> show create table data_table_sharded\G
*************************** 1. row ***************************
       Table: data_table_sharded
Create Table: CREATE TABLE `data_table_sharded` (
  `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from data_table_sharded;
+-----+
| a   |
+-----+
|   4 |
|  18 |
|  22 |
|  71 |
|  97 |
| 112 |
| 128 |
| 277 |
+-----+
8 rows in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql>

So, it can be done. Just use mklink to create hard links instead of symlinks. WOW !!!
I learned something about MySQL for Windows. I doubt if Oracle will implement the DATA DIRECTORY and INDEX DIRECTORY options since the default storage engine is now InnoDB.
Notwithstanding, you create the empty table, move the .MYD and .MYI to different folders from the OS, create hard links, run FLUSH TABLES; and INSERT your data.
Give it a Try, and let me know how it goes...


Service Broker Connection handshake failed. There is no compatible encryption algorithm My target server is not receiving the message and the following error shows in the target servers error logs. Service Broker login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'

I am using service broker transport security - certificate based authentication.
My master db keys and certs are set, permissions are granted to broker_endpoint.
My user db keys and certs are set, permissions are granted to services.

My Endpoints on each server are configured for certificates, have permission to the master db cert, and all my databases are authenticated by the same user.

I am also receiving an error in Sql Trace, on target server: Certificate Not Found.

The problem here is that the service that it is documenting on the same row, in sql trace, is for a different service broker configured on the source server that I didn't reference in my conversations.

When I view sys.certificates, on both servers,  all certs are tied to the proper issuer_name.

Any help is appreciated.

Thank you

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.


State of the art self healing and self protection in DBMS At the moment I'm writing a paper and I need some input about the state of the art of self healing and self protection in DBMSs. Does somebody read some interesting articles or books about this topics in the last few month? Doesnt matter which DBMS you have read about.

Regards

SQL Server has Automatic Page Repair During a Database Mirroring Session in 2008+. Is this the sort of thing you're looking for?

A database mirroring partner tries to automatically recover from
corrupted pages on the mirror database by resolving certain types of
errors that prevent reading a data page. The partner that is unable to
read a page requests a fresh copy from the other partner. If this
request succeeds, the unreadable page is replaced by the copy, which
usually resolves the error.

I guess you could include the repair mechanisms of DBCC CHECKDB under the self-healing banner and Page Checksums under self-protection. Clustering, mirroring and the new HADRON features in SQL Server 2012 are arguably relevant topics also.


What is the fastest method to migrate large tables from Oracle to SQL Server? We have a table with 169 million rows in a Oracle 10g database, we need to move this table to a MS SQL Server 2012 database.

Describe on the table:

aaa NOT NULL NUMBER(38)
bbb NOT NULL NUMBER(38)
ccc NOT NULL DATE
ddd NOT NULL VARCHAR2(8)
eee NOT NULL DATE
fff NOT NULL NUMBER(3)
ggg NUMBER(38)
hhh DATE
iii VARCHAR2(255)
jjj VARCHAR2(255)
kkk VARCHAR2(4000)

We have tried SSMA (SQL Server Migration Assistant) - takes over 300 hours.
We have tried SSIS (SQL Server Integration Services)- takes over 52 hours.
These options are not practical

I was wondering if 'bcp' is a good option, if yes, what is the syntax for such a data pull? Can we directly pull the data from the Oracle database?

BCP would be a good choice. Export your data to a text file and then import it. To do this follow these steps:

Create your new table in sql server
Insert 1 row of data into this new table
Use BCP to get an export from this table containing this 1 row
This is necessary so that you can create an export from Oracle that matches this. Example:

  bcp yourdatabase.dbo.yourtable out yourtable.dat -T -c

Check the format in yourtable.dat
Generate an output file from Oracle that matches the format of yourtable.dat
Use bcp again to load the data file that was created by oracle into sql server. Example:

  bcp yourdatabase.dbo.yourtable in yourtable.dat -T -c

Try with 5000 rows. Get it working properly then time it and load. BCP will be fast.


Post installation of SSIS for existing SQL Server instance with different build versions I would like to install SSIS into existing SQL Server 2014 Fail-over cluster instance with latest cumulative update 7 which was applied. I know how to do it but I want to know if consistency of versions between SSIS (from installation disc) and SQL Server 2014 (already updated) will be kept at the same latest server version (latest version is 12.0.2560).

In to detail, I mean I have latest CU7 update applied but I want to install SSIS from image where is first RTM version release 12.0.2000 but currently I have 12.0.2560 CU7 Server version which is newer. Will be there conflict of versions or SSIS has no impact which version of SQL Server is actually installed? I can also download image of SQL Server 2014 with SP1, but build version is higher than actual CU7 (SP1 is 12.0.4100 build version and CU7 with fixes 12.0.2560 build version). Reason why I don't want to install SP1 even when build version is higher has less fixed bugs than actual CU7.

So in result does post-installation of SSIS downgrade SQL Server version to which installation media contains or it will be self-upgraded to actual server installed version?

I have answer for this question from member of Microsoft Technet called Lydia Zhang:

  ...SSIS will inherit the patch level that your installation media contains, which has no impact on the patch level of your existing SQL Server 2014 failover cluster instance...
  https://social.technet.microsoft.com/Forums/en-US/83f5eef4-bf7a-439e-8e6c-a3124434b12d/post-installation-of-ssis-for-existing-mssql-instance-with-different-build-versions?forum=sqlsetupandupgrade

So my question is resolved.


Can combine five mysql statements into just one statement? Can I combine all the five statements into just one statement?

update mytable set myfield1 = replace(myfield1, '…' ,'-') ;
update mytable set myfield1 = replace(myfield1, '‖' ,'|') ;
update mytable set myfield1 = replace(myfield1, '”' , '"') ;
update mytable set myfield1 = replace(myfield1, '‘' , '`') ;
update mytable set myfield1 = replace(myfield1, '〃' ,'"') ;

update mytable set myfield1 =
replace(replace(replace(replace(replace(myfield1, '…' ,'-'),
'?' ,'|'),'”' , '"'),'‘' , '`'),'?' ,'"');


MySQL slave database won't start I have a MySQL master slave configuration and the slave is not working anymore. When trying to stop and start again the slave db, I am getting this error:

140530 14:21:23 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
140530 14:21:23 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position; the first event 'mysqliv-bin.000142' at 184658851, the last event read from './mysqliv-bin.000142' at 4, the last byte read from './mysqliv-bin.000142' at 4. ( server_errno=1236)
140530 14:21:23 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysqliv-bin.000142' at 184658851, the last event read from './mysqliv-bin.000142' at 4, the last byte read from './mysqliv-bin.000142' at 4.', Error_code: 1236

Any idea what can be the problem here?

Unknown system variable 'binlog_checksum'

That's because your master is running MySQL 5.5 (or earlier), and the slave is running MySQL 5.6. The binlog_checksum global variable was introduced in 5.6, and the slave is trying to find out if that feature is enabled on the master. But the variable doesn't exist on your master. This shouldn't cause any data loss, and the warning will go away once you upgrade your master.

  Client requested master to start replication from impossible position

Sounds like your master crashed before it could write a final "Rotate to next binlog" event at the end of a binlog file. So the slave doesn't know to advance to the next binlog.

You can manually change the slave to read from the next binlog:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqliv-bin.000143', MASTER_LOG_POS=4;
mysql> START SLAVE;

But first, double-check that mysqliv-bin.000143 exists on the master with SHOW BINARY LOGS.

The following blog explains in more detail:
http://www.skysql.com/blogs/adam-donnison/client-requested-master-start-replication-impossible-position


Efficiently storing irregular/repeating intervals (think calendar/events) I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing.

Right now I'm storing them in a postgres database, but I have a feeling it doesn't scale well.

The current layout is:

ID - MESSAGE - DATE - TIME

The DATE and TIME field holds the time and date for when the message should be send for processing. This doesn't scale well, as if a message needs to be send the first monday every month, it would take up 12x as much space.

Problem is that I can't seem to find another way to represent when a message should be send for processing? Ideally I'd love to be able to represent each and every date in a single row.

We were also disucssing using Redis, but quickly decided not too, as we would need the database for the webfrontend.

Anyone have any idea how to optimize the message storage? How to represent when a message should be send for processing?

I am also open for any other suggestions on how to tackle this.

Always look for standards that support your requirements. What is a standard that supports recurring calendar events? ICalendar RRULEs:

http://en.wikipedia.org/wiki/ICalendar

http://www.kanzaki.com/docs/ical/rrule.html

You can either store the rule as plain text and parse it as needed, or use a database schema for them:

SQL Schema:
https://stackoverflow.com/questions/1054201/ical-field-list-for-database-schema-based-on-ical-standard/1397019#1397019

Perl:
http://search.cpan.org/~rfrankel/iCal-Parser-1.16/lib/iCal/Parser.pm

PostgreSQL-specific:
http://svn.expressolivre.org/contrib/davical/dba/rrule_functions-8.1.sql
Performance-wise, you can use Materialized Views to calculate events, say one month in advance and one month prior (if required):

https://stackoverflow.com/questions/4239871/when-building-a-calendar-app-should-i-store-dates-or-recurrence-rules-in-my-dat/10151804#10151804

Additional information : http://www.vertabelo.com/blog/technical-articles/again-and-again-managing-recurring-events-in-a-data-model
This article explains each and every aspect of repeated events. Please read it before coming to any conclusions.


Multiple Inserts vs. Bulk Insert From Disk for hydrating SQL Server reference tables When doing database design, I often use reference/support tables, as we all do. Each time I start up a new project there are inevitably tables which:

Have a predefined set of values.
Will never change, and likely never encounter new records (or very infrequently).

A perfect example of this might be a Country table,.

create table Country
(
  CountryKey  int         not null identity
 ,CountryName varchar(64) not null
 ,IsoNumber   int         not null
 ,Iso3        varchar(8)  not null
 ,constraint pk_country primary key clustered (CountryKey)
);

I have used  a number of different approaches to "hydrate" these kinds of tables, primarily:

A hydration SQL script with insert statements.

insert into Country (CountryName, IsoNumber ...) values ('Canada', ...)

A hydration SQL script, reading from disk, using bulk insert.

bulk insert MyDatabase.dbo.Country from ...

And in extreme cases a programmatic script (f#, python).

My preferred approach is the first if all tables only contain a few records. If the tables are beyond this limit, I typically like a script reading from CSV files. I elect to use CSV in that it's both compact and human-readable.

How is everyone else handling this situation?

  I realize that this is a somewhat opinionated question. But I figured it was worth asking, since it will inevitably have concrete answers with technical rationale.

Separate Files

Keep CREATE TABLE, INSERT, GRANT scripts separately.  Data MIGHT change "per project" (eg 'Oui' vs 'YES').  Additionally, this seems to be a common design for most projects I've seen/worked on.

INSERT vs Other

Bulk operations (f#,python,CSV) are faster than multiple single-row INSERT statements.

I would definitely use INSERT for a few rows.  The "cutoff point" between INSERT and Code/CSV would be a personal decision.

Usage of Code

I would only consider using code (f#,python) if and only if the data is Calculated based on input parameter(s).  (eg a DAY_DIMENSION table that loads n days at a time)

CSV Scripts

For sizable data, you'd want to use a CSV Script for speed

The script that loads the CSV (or other format) file should use the Filename as a parameter. All utilities (eg SQL*Loader) do this; you just need to filter the parameter up to the Shell Script level.
This allows you to load a different (project specific/updated revision) file at-will.

Make sure you note if globbing (eg LoadMyData.sh data_0[1-3]*.csv) is allowed.

Dumps

One of the easiest ways to port tables is to use Dumps (as Dave has suggested). (eg Oracle's expdp/impdp)

You should still have the DDL statements available so that you can recreate the tables (and dump) as necessary.


Oracle - ORA-01654: unable to extend index in tablespace I referred to the below related question, but it was not helpful in my case:

ORA-01654: unable to extend index <name of the index> by 128 in tablespace <name of the Index tablespace>

I have 2 indexes in the same tablespace that I need to increase the size of.

I have 13 dbf files associated with that tablespace.

How can I make sure I increase the size of correct dbf file to fix the above error?

This error code, [ORA-01654], means the server has failed to allocate an extent of the required number of blocks for an index segment in the specified tablespace. As you have said you have got 13 datafiles in your tablespace, you are using small file tablespace. In order to get rid of this error, you can simply add data file using ALTER TABLESPACE ADD DATAFILE command to the specified tablespace.

First of all, you need to identify in which data file or files your index is being stored. Your index might have expanded into multiple data files. To identify the data files you can use the following SQL statement.

select a.owner, a.index_name, b.file_name
from dba_indexes a, dba_data_files b
where a.tablespace_name=b.tablespace_name and a.index_name='Your index name'


Will the transaction log shrink automagically in SQL Server? When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate automagically at some time point? Or do I have to truncate/shrink it manually?

It will truncate automatically but that is very different to shrink. Truncation reclaims log space for re-use, shrinking physically reduces the file size to release space back to the OS. If your log has grown to its current size its likely that it will grow again if you shrink it.

I'd suggest getting a handle on what typical and maximum log usage is for your system. The query below (not mine, boosted from Glen Berrys DMV scripts) could be run manually or you could capture the output to a table via an agent job. If you log it to a table for a week or so you'll get a picture of typical usage and more importantly, when a process is causing the log to grow beyond what you expect.

SELECT
     db.[name] AS [Database Name]
   , db.recovery_model_desc AS [Recovery Model]
   , db.log_reuse_wait_desc AS [Log Reuse Wait Description]
   , ls.cntr_value AS [Log Size (KB)]
   , lu.cntr_value AS [Log Used (KB)]
   , CAST(
        CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
        AS DECIMAL(18,2)
     ) * 100 AS [Log Used %]
   , db.[compatibility_level] AS [DB Compatibility Level]
   , db.page_verify_option_desc AS [Page Verify Option]
   , db.is_auto_create_stats_on, db.is_auto_update_stats_on
   , db.is_auto_update_stats_async_on, db.is_parameterization_forced
   , db.snapshot_isolation_state_desc, db.is_read_committed_snapshot_on
FROM sys.databases AS db
   INNER JOIN sys.dm_os_performance_counters AS lu
     ON db.name = lu.instance_name
   INNER JOIN sys.dm_os_performance_counters AS ls
     ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
   AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
   AND ls.cntr_value > 0
OPTION (RECOMPILE);

Transaction Log Truncation describes both the when and why log truncation occurs.

  If log records were never deleted from the transaction log, it would
  eventually fill all the disk space that is available to the physical
  log files. Log truncation automatically frees space in the logical log
  for reuse by the transaction log.

Factors That Can Delay Log Truncation is a useful reference for understanding why your log may fail to truncate and therefore grow larger than expected.


SQL Server AlwaysOn and FailoverCluster We have one project which has Failover Cluster with two nodes and one standalone server in AlwaysON. For example:

Project 1

Node1 (ip:192.168.100.101)
Node2 (ip:192.168.100.102) in Failover Cluster with clustered IP: 192.168.100.104
Node3 (ip:192.168.100.103). Clustered IP:(192.168.100.104) and it's AlwaysON IP: 192.168.100.105

And now we have another project as follows (Cluster IP:192.168.200.105):

Project 2

Node1 (ip:192.168.200.101)
Node2 (ip:192.168.200.102)

We want to add third Node3 (ip:192.168.200.103) into Project2 cluster and create AlwaysON. But don't want change Cluster IP. In Project1 we have two WFCI. Can we create with one WFCI and Clustered IP?

A node can only be in one cluster at a time.  If Node3 is in CLUSTER-1, you have to remove it in order to add it to CLUSTER-2.

If you want to use Node3 with availability groups in both projects, then all five servers will need to be in the same cluster.

There are other options that would allow you to use Node3 for DR purposes without having all 5 nodes in one cluster.  For instance, you could perhaps use log shipping to replicate the databases on CLUSTER-2 to Node3.  More detail on the requirements would be needed for someone to be able to suggest what would be best.


Error 1215: Can't find how to solve it I'm currently building a database and adding my foreign keys constraint on phpMyAdmin but I'm struggling with one of them.
Indeed, I've looked on the internet for many possible solutions but any of them was working for me.

The fact is that I'm trying to add a foreign key contraint in my CUSTOM_TRANSLATION table on the AUTHOR, referring to the NAME attribute in my USER table with a CASCADE effect ON UPDATE and ON DELETE.
Type matches and my indexes are set, I still get a #1215 error (cannot add foreign key constraint).

I didn't pasted the whole code because I don't think it's really usefull, just pasted the interesting part, I guess.

Thank you for your time and help, fellas :)

CREATE TABLE `USER` (
  `ID` tinyint(3) UNSIGNED ZEROFILL NOT NULL,
  `NAME` varchar(20) NOT NULL,
  `MAIL` varchar(50) NOT NULL,
  `PASSWORD` varchar(150) NOT NULL,
  `RANK` enum('Admin','Translator','ViewerSDTV','ViewerCustomer','Customer','TranslatorRestricted') NOT NULL,
  `SOCIETY` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `CUSTOM_TRANSLATION` (
  `ID` smallint(5) UNSIGNED ZEROFILL NOT NULL,
  `TRANSLATION_ID` smallint(5) UNSIGNED ZEROFILL NOT NULL,
  `MESSAGE_ID` smallint(5) UNSIGNED ZEROFILL NOT NULL,
  `TRANSLATED_MESSAGE` varchar(80) NOT NULL,
  `VERSION` decimal(3,2) UNSIGNED ZEROFILL NOT NULL,
  `AUTHOR` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `USER`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `MAIL` (`MAIL`),
  ADD KEY `NAME_LINK` (`NAME`(10)) USING BTREE;

Even though Mysql InnoDB allows to reference non-unique column or set of columns, it's very bad practice, and not recommended especially with CASCADE option :

  Additionally, MySQL requires that the referenced columns be indexed for performance reasons. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.
  (https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html)

The right way is to have a reference to USER.ID , not to USER.NAME.

However, adding and index on USER.NAME will let you create a constraint you want :

CREATE INDEX IDX_NAME ON USER(NAME)

Your code would work as well if you removed size while creating index:

ALTER TABLE `USER`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `MAIL` (`MAIL`),
  ADD KEY `NAME_LINK` (`NAME`) USING BTREE;
   --NOT `NAME_LINK` (`NAME`(10))  !!
  ALTER TABLE CUSTOM_TRANSLATION add constraint fk1 foreign key (AUTHOR) references user(name);


Find the violated index on ORA-00001 (dup_val_on_index) I have a table with several indexes. In my application code there could be instances where any of these indexes could be violated, depending on user input.

I would like to log exactly which index was violated when my PL/SQL code stumbles upon an ORA-00001.

Is there a way to know which index violation caused Oracle to raise the dup_val_on_index?

AskTom has already discussed this.

This could be achieved in following manner:

BEGIN
   -- code containing INSERT operation
EXCEPTION
   WHEN dup_val_on_index THEN
      Trace_SYS.Message(substr(sqlerrm, instr(sqlerrm, '(')+1,
                                        instr( sqlerrm, ')') - instr( sqlerrm,'(') -1 ));
END;


Difference of write authorization on file system with mySQL On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this:

SELECT html
FROM mytable
WHERE lang = 'en' and id='KEY01'
INTO OUTFILE 'key01_en.html'

By doing this, the file will be saved in the /var/lib/mysql/mydbschema/ by default.

Now, I want the file to be saved in my user let say /home/userone/dump_html folder

If I say this (I launch the mysql command from the shell, and logged as "userone":

SELECT html
FROM mytable
WHERE lang = 'en' and id='KEY01'
INTO OUTFILE '/home/userone/dumphtml/key01_en.html'

It makes an error that the process does not have enough rights to write there.

But now still from the mysql> prompt, if I make:

SELECT html
FROM mytable
WHERE lang = 'en' and id='KEY01'
INTO OUTFILE '/tmp/key01_en.html'
;

-- and then
system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html
;

It works without any issue.

It looks strange to me the SQL cannot write on my disk but the system command can. Any idea why?
(The discussion started on this point from this answer on SO)

Your problem is that MySQL cannot write to your home directory, as by default it will run as the MySQL user, or as some other user you defined when you installed and configured it, if not using the default rpm or other install package. Users are not, by default, able to write to another user's home directory as it is each account's private space.

/tmp is by default writable by all users. As such MySQL can write there. Depending on user groups and umask on your system you may be able to read that output as your user.

If you don't want to use /tmp you could create another directory where your user and MySQL can write and make sure your user and MySQL are members of the same group.


Get a truly RANDOM row from a PostgreSQL table quickly I always used to do:

SELECT column FROM table ORDER BY random() LIMIT 1;

For large tables, this was unbearably, impossibly slow, to the point of being useless in practice. That's why I started hunting for more efficient methods. People recommended:

SELECT column FROM table TABLESAMPLE BERNOULLI(1) LIMIT 1;

While fast, it also provides worthless randomness. It appears to always pick the same damn records, so this is also worthless.

I've also tried:

SELECT column FROM table TABLESAMPLE BERNOULLI(100) LIMIT 1;

It gives even worse randomness. It picks the same few records every time. This is completely worthless. I need actual randomness.

Why is it apparently so difficult to just pick a random record? Why does it have to grab EVERY record and then sort them (in the first case)? And why do the "TABLESAMPLE" versions just grab the same stupid records all the time? Why aren't they random whatsoever? Who would ever want to use this "BERNOULLI" stuff when it just picks the same few records over and over? I can't believe I'm still, after all these years, asking about grabbing a random record... it's one of the most basic possible queries.

What is the actual command to use for grabbing a random record from a table in PG which isn't so slow that it takes several full seconds for a decent-sized table?

Interesting question - which has many possibilities/permutations (this answer has been extensively revised).

Basically, this problem can be divided into two main streams.

A single random record
Multiple random records (not in the question - see reference and discussion at bottom)

Having researched this, I believe that the fastest solution to the single record problem is via the tsm_system_rows extension to PostgreSQL provided by Evan Carroll's answer.

If you're using a binary distribution, I'm not sure, but I think that the contrib modules (of which tsm_system_rows is one) are available by default - at least they were for the EnterpriseDB Windows version I used for my Windows testing (see below). My main testing was done on 12.1 compiled from source on Linux (make world and make install-world).

The reason why I feel that it is best for the single record use case is that the only problem mentioned concerning this extension is that:

  Like the built-in SYSTEM sampling method, SYSTEM_ROWS performs
  block-level sampling, so that the sample is not completely random but
  may be subject to clustering effects, especially if only a small
  number of rows are requested.

however, since you are only interested in selecting 1 row, the block-level clustering effect should not be an issue. This article from 2ndQuadrant shows why this shouldn't be a problem for a sample of one record! It is a major problem for small subsets (see end of post) - OR if you wish to generate a large sample of random records from one large table (again, see the discussion of tsm_system_rows and tsm_system_time below).

Then I created and populated a table like this:

CREATE TABLE rand AS SELECT generate_series(1, 100000000) AS seq, MD5(random()::text);

So, I now have a table with 100,000,000 (100 million) records. Then I added a PRIMARY KEY:

ALTER TABLE rand ADD PRIMARY KEY (seq);

So, now to SELECT random records:

SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);

Notice that I have used a slightly modified command so that I could "see" the randomness - I also set the \timing command so that I could get empirical measurements.

I used the LENGTH() function so that I could readily perceive the size of the PRIMARY KEY integer being returned. Here is a sample of records returned:

test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 970749.61 | bf18719016ff4f5d16ed54c5f4679e20
(1 row)

Time: 30.606 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 512101.21 | d27fbeea30b79d3e4eacdfea7a62b8ac
(1 row)

Time: 0.556 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 666476.41 | c7c0c34d59229bdc42d91d0d4d9d1403
(1 row)

Time: 0.650 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column? |               md5
--------+----------+----------------------------------
      5 | 49152.01 | 0a2ff4da00a2b81697e7e465bd67d85c
(1 row)

Time: 0.593 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column? |               md5
--------+----------+----------------------------------
      5 | 18061.21 | ee46adc96a6f8264a5c6614f8463667d
(1 row)

Time: 0.616 ms
test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 691962.01 | 4bac0d051490c47716f860f8afb8b24a
(1 row)

Time: 0.743 ms

So, as you can see, the LENGTH() function returns 6 most of the time - this is to be expected as most records will be between 10,000,000 and 100,000,000, but there are a couple which show a value of 5 (also have seen values of 3 & 4 - data not shown).

Now, notice the timings. The first is 30 milliseconds (ms) but the rest are sub millisecond (approx. 0.6 - 0.7ms). Most of the random samples are returned in this sub-millisecond range, but, there are results returned in 25 - 30 ms (1 in 3 or 4 on average).

From time to time, this multi-millisecond result can occur twice or even three times in a row, but, as I said, the majority of results (approx. 66 - 75%) are sub-millisecond. None of the response times for my solution that I have seen has been in excess of 75ms.

During my research I also discovered the tsm_system_time extension which is similar to tsm_system_rows. Now, I also benchmarked this extension as follows:

SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_TIME(0.001) LIMIT 1;

Note that the time quantum is 1/1000th of a millisecond which is a microsecond - if any number lower than this is entered, no records are returned. However, interestingly, even this tiny quantum always returns 120 rows.

Quite why it's 120 is a bit above my pay grade - the PostgreSQL page size is 8192 (the default)

test=# SELECT current_setting('block_size');
 current_setting
-----------------
 8192
(1 row)

and the file system block size is 4096

[pol@UNKNOWN inst]$blockdev --getbsz /dev/mapper/fedora_localhost--live-home
4096

A record should be (1 INTEGER (4 bytes) + 1 UUID (16 bytes)) (= 20 bytes) + the index on the seq field (size?). 4096/120 = 34.1333... - I hardly think that each index entry for this table takes 14 bytes - so where the 120 comes from, I'm not sure.

I'm not quite sure if the LIMIT clause will always return the first tuple of the page or block - thereby introducing an element of non-randomness into the equation.

The performance of the tsm_system_time query is identical (AFAICS - data not shown) to that of the tsm_system_rows extension. The same caveat about not being sure whether there is an element of non-randomness introduced by how these extensions choose their first record also applies to the tsm_system_rows queries. See discussion and bench-testing of the (so-called) randomness of these two methods below.

With respect to performance, just for reference, I'm using a Dell Studio 1557 with a 1TB HDD (spinning rust) and 8GB of DDR3 RAM running Fedora 31). This is a 10 year old machine!

I also did the same thing on a machine (Packard Bell, EasyNote TM - also 10 years old, 8GB DDR3 RAM running Windows 2019 Server) that I have with an SSD (SSD not top of the range by any means!) and the response times are typically (strangely enough) a bit higher (~ 1.3 ms), but there are  fewer spikes and the values of these are lower (~ 5 - 7 ms).

There could well be a lot of stuff running in the background with 2019 Server - but if you have a modern laptop with a decent SSD, there's no reason that you can't expect sub-millisecond response times as a matter of course!

All tests were run using PostgreSQL 12.1.

To check out the true "randomness" of both methods, I created the following table:

CREATE TABLE rand_samp
(
  seq INT,
  md5 TEXT
);

and then ran (3 times each):

DO
$$
DECLARE
  i RECORD;
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO rand_samp (seq, md5)
    SELECT seq, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
  END LOOP;
END;
$$
;

and also using (in the inner loop of the above function)

SELECT seq, md5 FROM rand TABLESAMPLE SYSTEM_TIME(0.001) LIMIT 1;

Then after each run, I queried my rand_samp table:

SELECT
  seq, COUNT(seq)
FROM rand_samp
GROUP BY seq
HAVING COUNT(seq) > 1;

And got the following counts:

For TABLESAMPLE SYSTEM_ROWS, I got 258, 63, 44 dupes, all with a count of 2. For TABLESAMPLE SYSTEM_TIME, I got 46, 54 and 62, again all with a count of 2.

Now, my stats are a bit rusty, but from a random sample of a table of 100M records,from a sample of 10,000, (1 ten-thousandth of the number of records in the rand table), I'd expect a couple of duplicates - maybe from time to time, but nothing like the numbers I obtained. Furthermore, if there was true randomness, I'd expect (a small number of) 3's and 4's also.

I ran two tests with 100,000 runs using TABLESAMPLE SYSTEM_ROWS and obtained 5540 dupes (~ 200 with 3 dupes and 6 with 4 dupes) on the first run, and 5465 dupes on the second (~ 200 with 3 and 6 with 4). The most interesting query was this however:

SELECT COUNT(s.seq)
FROM rand_samp s
WHERE s.seq IN (SELECT sb.seq FROM rand_samp_bis sb);

where I compare dupes in both runs of 100,000 with respect to each other - the answer is a whopping 11,250 (> 10%) are the same - which for a sample of 1 thousandth (1/1000) is WAY to much to be down to chance!

Results 100,000 runs for SYSTEM_TIME - 5467 dupes, 215 with 3, and 9 with 4 on the first group, 5472, 210 (3) and 12 (4) with the second. The number of matching records is 11,328 (again > 10%).

There's clearly (a LOT of) non-random behaviour going on. I'll leave it to the OP to decide if the speed/random trade-off is worth it or not!

Benchmark of other answers.

I decided to benchmark the other proposed solutions - using my 100 million record table from above. I ran all tests 5 times - ignoring any outliers at the beginning of any series of tests to eliminate cache/whatever effects. All the outlier values were higher than those reported below.

I'm using the machine with the HDD - will test with the SSD machine later. The .mmm reported means milliseconds - not significant for any answer but my own.

Daniel Vérité's answer:

SELECT * FROM
  (SELECT seq FROM rand TABLESAMPLE BERNOULLI(1)) AS s
 ORDER BY RANDOM() LIMIT 1;

Ran 5 times - all times were over a minute - typically 01:00.mmm (1 at 01:05.mmm).

Typical run:

test=# SELECT * FROM
  (SELECT seq FROM rand TABLESAMPLE BERNOULLI(1)) AS s
 ORDER BY RANDOM() LIMIT 1;
   seq
---------
 9529212
(1 row)

Time: 60789.988 ms (01:00.790)

Swav's answer:

SELECT md5 FROM rand OFFSET (
    SELECT floor(random() * (SELECT count(seq) from rand))::int
) limit 1;

Ran 5 times - all times were over a minute - from 01:03 to 01:29

Typical run:

test=# SELECT md5 FROM rand OFFSET (
    SELECT floor(random() * (SELECT count(seq) from rand))::int
) limit 1;
               md5
----------------------------------
 8004dfdfbaa9ac94243c33e9753e1f77
(1 row)

Time: 68558.096 ms (01:08.558)

Colin 't Hart's answer:

select * from rand where seq >= (
  select random()*(max(seq)-min(seq)) + min(seq) from rand
)
order by seq
limit 1;

Ran 5 times - times varied between 00:06.mmm and 00:14.mmm (Best of the Rest!)

Typical run:

test=# select * from rand where seq >= (
  select random()*(max(seq)-min(seq)) + min(seq) from rand
)
order by seq
limit 1;
   seq    |               md5
----------+----------------------------------
 29277339 | 2b27c594f65659c832f8a609c8cf8e78
(1 row)

Time: 6944.771 ms (00:06.945)

Colin 't Hart's 2nd answer (adapted by me):

WITH min_max AS MATERIALIZED -- or NOT, doesn't appear to make a difference
(
  SELECT MIN(seq) AS min_s, MAX(seq) AS max_s, (MAX(seq) - MIN(seq)) - MIN(seq) AS diff_s
  FROM rand
),
other  AS MATERIALIZED
(
  SELECT FLOOR(RANDOM() * (SELECT diff_s FROM min_max))::INT AS seq_val
)
SELECT seq, md5
FROM rand
WHERE seq = (SELECT seq_val FROM other);

Response time is between ~ 30 - 45ms with the odd outlier on either side of those times - it can even drop to 1.xxx ms from time to time. All I can really say is that it appears to be more consistent than either of the SYSTEM_TIME and SYSTEM_ROWS methods.

There is a major problem with this method however. If the underlying field that one is choosing for randomness is sparse, then this method won't return a value all of the time - this may or may not be acceptable to the OP? You can do something like (end of query):

SELECT seq, md5
FROM rand
WHERE seq >= (SELECT seq_val FROM other)
LIMIT 1;

(note >= and LIMIT 1). This can be very efficient, (1.xxx ms), but seems to vary more than just the seq =... formulation - but once the cache appears to be warmed up, it regularly gives response times of ~ 1.5ms.

Another advantage of this solution is that it doesn't require any special extensions which, depending on the context (consultants not being allowed install "special" tools, DBA rules...) may not be available.

One really WEIRD thing about the above solution is that if the ::INT CAST is removed, the query takes ~ 1 minute. This happens even though the FLOOR function should return an INTEGER. I only discovered that this was an issue by running EXPLAIN (ANALYZE BUFFERS).

With ::INT

   CTE other
     ->  Result  (cost=0.02..0.04 rows=1 width=4) (actual time=38.906..38.907 rows=1 loops=1)
           Buffers: shared hit=1 read=9
           InitPlan 4 (returns $3)
             ->  CTE Scan on min_max  (cost=0.00..0.02 rows=1 width=4) (actual time=38.900..38.902 rows=1 loops=1)
                   Buffers: shared hit=1 read=9
   InitPlan 6 (returns $5)
     ->  CTE Scan on other  (cost=0.00..0.02 rows=1 width=4) (actual time=38.909..38.910 rows=1 loops=1)
           Buffers: shared hit=1 read=9
 Planning Time: 0.329 ms
 Execution Time: 68.449 ms
(31 rows)

Time: 99.708 ms
test=#

Without ::INT

   CTE other
     ->  Result  (cost=0.02..0.04 rows=1 width=8) (actual time=0.082..0.082 rows=1 loops=1)
           Buffers: shared hit=10
           InitPlan 4 (returns $3)
             ->  CTE Scan on min_max  (cost=0.00..0.02 rows=1 width=4) (actual time=0.076..0.077 rows=1 loops=1)
                   Buffers: shared hit=10
   InitPlan 6 (returns $5)
     ->  CTE Scan on other  (cost=0.00..0.02 rows=1 width=8) (actual time=0.085..0.085 rows=1 loops=1)
           Buffers: shared hit=10
   ->  Parallel Seq Scan on rand  (cost=0.00..1458334.00 rows=208333 width=37) (actual time=52644.672..60025.906 rows=0 loops=3)
         Filter: ((seq)::double precision = $5)
         Rows Removed by Filter: 33333333
         Buffers: shared hit=14469 read=818865
 Planning Time: 0.378 ms
 Execution Time: 60259.401 ms
(37 rows)

Time: 60289.827 ms (01:00.290)
test=#

Note the (without ::INT)

   ->  Parallel Seq Scan on rand  (cost=0.00..1458334.00 rows=208333 width=37) (actual time=52644.672..60025.906 rows=0 loops=3)
         Filter: ((seq)::double precision = $5)

Parallel Seq Scan (with a high cost), filter on (seq)::double

(WHY double??).

And

Buffers: shared hit=14469 read=818865

compared to (with ::INT)

Buffers: shared hit=1 read=9

Finally, my own answer again (same machine, time & cache):

(this is now redundant in the light of the benchmarking performed above).

Ran my own benchmark again 15 times - typically times were sub-millisecond with the occasional (approx. 1 in 3/4) run taking approx. 25 milliseconds.

Typical run:

test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(1);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 401578.81 | 30ff7ecfedea088dab75932f0b1ea872
(1 row)

Time: 0.708 ms

So, it would appear that my solution's worst times are ~ 200 times faster than the fastest of the rest of the pack's answers (Colin 't Hart).

My analysis is that there is no perfect solution, but the best one appears to be the adaptation of Colin 't Hart's solution.

Finally, a GRAPHIC demonstration of the problem associated with using this solution for more than one record is shown below - taking a sample of 25 records (performed several times - typical run shown).

The tsm_system_rows method will produce 25 sequential records. This may be suitable for certain purposes where the fact that the random sample is a number of sequential records isn't a problem, but it's definitely worth keeping in mind.

test=# SELECT LENGTH((seq/100)::TEXT), seq/100::FLOAT, md5 FROM rand TABLESAMPLE SYSTEM_ROWS(25);
 length | ?column?  |               md5
--------+-----------+----------------------------------
      6 | 763140.01 | 7e84b36ab30d3d2038ebd832c241b54d
      6 | 763140.02 | a976e258f6047df18e8ed0559ff48c36
--
--    SEQUENTIAL values of seq!
--
      6 | 763140.23 | ad4f1b4362187d6a300aaa9aaef30170
      6 | 763140.24 | 0c7fcc4f07d27fbcece68b2503f28d88
      6 | 763140.25 | 64d4507b18b5481a724d8a5bb6ac59c8
(25 rows)

Time: 29.348 ms

A similar state of affairs pertains in the case of the SYSTEM_TIME method. As mentioned above, even with a minimum time of 1μs, it gives 120 records. Just as with SYSTEM_ROWS, these give sequential values of the PRIMARY KEY.

test=# SELECT seq, md5 FROM rand TABLESAMPLE SYSTEM_TIME(0.001);

returns:

   seq    |               md5
----------+----------------------------------
 42392881 | e92f15cba600f0c7aa16db98c0183828
 42392882 | 93db51ea870e15202144d11810c8f40c
 42392883 | 7357bf0cf1fa23ab726e642832bb87b0
 42392884 | 1f5ce45fb17c8ba19b391f9b9c835242
 42392885 | f9922b502d4fd9ee84a904ac44d4e560
 ...
 ...  115 sequential values snipped for brevity!

Our sister site, StackOverflow, treated this very issue here. Good answers are provided by (yet again) Erwin Brandstetter here and Evan Carroll here. That whole thread is worth reading in detail - since there are different definitions of random (monotonically increasing/decreasing, Pseudorandom number generators...) and sampling (with or without replacement...).


Event Management System I'm working on a "Event Management System" Online Software Project where the system requires the following:

Viewers can sign up for their user account
Events can be created by the admin from the admin panel
Each event requires that the student has a set of attributes
The required attribute for an event may or may not exist in the
user's details table
When the user wants to apply for participation in an event, the
system will prompt for the missing attributes
Once the user fills in the missing attributes, the system will
accept an application for the event, from the user.

To solve this one I created the following tables.

users (user_id [PK], username, password, user_level)
userdetails (user_id [FK], name, address, attr1, attr2, attr3, ... , attrN)
event (event_id [PK], event_start, event_end, event_title)
eventdetails (event_id [FK], ... )

How do I relate the eventdetails and the userdetails, such that the eventdetails

requires a tuple of n attributes from the userdetails table?

I was thinking something like:

The system will add n colums to the userdetails  field as soon as a new event is

created by the admin.

Am i thinking in the proper way?

I think you want a lookup table that shows the requirements for a particular event. In this case, generally I would also have attr1-3 in a separate table called 'Attribute' and a user_attribute table to assign attributes to a particular user_detail record. Then, you could assign a particular event to require n attributes through the lookup table-

event_attribute (event_attribute_id [PK], event_id [FK], attribute_id [FK],...).


Migrating system and user databases to a new server by moving the LUN I have to replace a server. The system and user databases all reside on the E: drive which is a SAN LUN. The method they want to employ is to remove the LUN from the current server and present it to the new server.

My concerns are that SQL Server is installed on the C: drive which is local storage and will not move with the LUN. I will need to install SQL Server on the new server, and when I do the system databases will be created on the new server.

My questions are:

I know the user databases will need to be detached before moving the LUN, but what about detaching master, model, and msdb?
Assuming the system databases including master can be detached, after the LUN is associated with the new server, how would you handle the master, model, and msdb files on E: with the LUN?

My thoughts were maybe choosing to install the system dbs on C on the new server and then after the LUN moves associate SQL Server with the location of the system databases on E and delete the ones on C.

I also thought about restoring the master db from a backup. How would you handle this?

This is SQL Server 2008 R2 SP2 10.52.4000 Enterprise non clustered.

You will not be able to detatch system databases. If they are not open to properly migrating the server level objects to another server, then I would opt for restoring system databases over trying to replace them. Ensure you are on the same, exact build number or master will not restore. You will also likely have to update the server name in master, if your new server does not have the same hostname.

I would also insiste on a backup of every user databae before that LUN is moved anywhere. (Just in case.)


Appropriate Action for Dead Locks / Blocking I've setup my databases so that anytime a procedure is blocked for more than 45 seconds the database notifies the DBA email.  Is it bad practice to setup a way to auto kill the process that is doing the blocking?  I'm assuming yes; however, waiting until an off hours DBA can get to a computer and fix the change seams problematic as well.  Is there a better way to handle the blocking processes?

To piggy back off @AaronBertrand, killing a process could cause transaction inconsistency and that would mean you are throwing out ACID compliance.  Why not first record all queries that take over 45 seconds and then change your code design based off that?

There's a lot you can do to resolve locking/blocking issues from changing the isolation levels such as READ UNCOMMITED or using snapshot isolation (snapshot isolation is preferred as there's lots of caveats with READ UNCOMMITED, but it won't help in the case of writers blocking writers).

check out:

MCM Snapshot Isolation Intro Video

Good Start On Troubleshooting Locking And Blocking


format of mysql query log What is the format of the mysql query log? In particular, for lines like:

133 Query     commit
133 Query     rollback

What does '133' stand for? And is it true that each line represents a round trip communication to the database (i.e., they are not batched)?

In the MySQL General Query Log, the fields are these:

yymmdd hh:mm:ss thread_id command_type query_body

The timestamp only appears each time it changes.

In your example "133" would be the thread id of the connected client, as shown in SHOW PROCESSLIST;.

Each line represents a query issued, but not necessarily a round-trip from the client to the database, because queries issued within stored procedures and stored functions are also logged, and a proc or function, while running, could issue many queries in response to a single query from a client.

Also, the MySQL C-API, which is the library underlying some languages' connectors to MySQL, supports multiple statement execution, which would be another case where multiple queries don't necessarily represent multiple round-trips since multiple queries can be send to the database as a "batch"... but that's only a factor if this capability is used by the calling application.

The general log can also be directed to write to the mysql.general_log table, which is easier to parse and analyze than the file-based log, but the same general observations, above, apply when using that format.

The slow query log includes more information and so provides a more informative log for understanding how the server is performing, and can be used as a log of all queries by configuring MySQL to interpret all queries as meeting the "slow" criteria.  This is done by setting long_query_time to '0' after enabling the slow query log.


What is the maximum number of parameters I can pass using the SQL IN clause in SQL server 2008 and SQL server 2000? My program lets people pick items from a list to help run a query. To return the minimum amount of data, I've had to use the IN clause.

I'm pretty sure there's a limit to how many items I can put in the IN clause of one SELECT statement, but I don't know exactly what the limit is. For my case, I need to know the limit for SQL server 2000 and SQL server 2008.

Thanks!

There isn't a quantity limit on the number of items in the IN clause.  The only limit is that the entire batch cannot be more than the batch size maximum.  In SQL 2008, this is 65,536 * Network Packet Size. In SQL 2000, I'm not sure if the limit was smaller than that.

This link describes these limits for 2008


Interbase SQL Grouping and displaying I have a database with different data I need to make a query to select a group with summed quantities.

Two tables: Positions and Documents.

There can be many positions in one document both are connected by unique_id.

The Documents have two different statuses: In and Out

I need to see the result of these two status on one row with a column for each case, but instead my query gives me two rows. How do I do it?

My query doesn't give me this result:

Select   documents.code
        ,positions.fullname
        ,case when documents.status='In' then Sum(positions.quantity) else 0 end as DocIn
        ,case when documents.status='Out' then Sum(positions.quantity) else 0 end as DocOut
from positions
    inner join documents
        on documents.unique_id=positions.unique_id
group by documents.code, positions.fullname, documents.status

You are getting two rows per document because you are grouping by documents.status. You need to use SUM() slightly differently and then remove documents.status from the GROUP BY clause:

Select   documents.code
        ,positions.fullname
        ,SUM(case when documents.status='In' THEN positions.quantity ELSE 0 END) AS DocIn
        ,SUM(case when documents.status='Out' THEN positions.quantity ELSE 0 END) AS DocOut
from positions
inner join documents
 on documents.unique_id=positions.unique_id
 group by documents.code, positions.fullname


SQL Server Linux(Ubuntu) Connection Options In general SQL Server Connection Protocols allows three different connection protocols:

  TCP/IP
  Named Pipes
  Shared Memory

in linux by default installation we can connect to the SQL Server using TCP/IP, the question is is there any options for named pipes or shared memory or UNIX sockets?

Also, it seems there is no option to enable or disable such features in Linux.

TCP/IP:

sqlcmd –S tcp:ServerName –U sa –P

Named Pipes:

sqlcmd –S np:\\ServerName\pipe\sql\query –U sa –P

Shared Memory:

sqlcmd –S lpc:ServerName –U sa –P

Shared Memory and Named Pipes protocols are only available with SQL Server on Windows.

Note the documentation links above call out "SQL Server (Windows only)" in the APPLIES TO header.


How can I speed SQLPS connection when I don't have WMI permissions at the server When connecting SQLPS to a SQL Server, it will try to run some WMI service status queries. In an environment where one does not have permissions for those, PowerShell issues a bunch of warnings, and it's VERY slow to connect.

It's possible to suppress the warning messages with $WarningPreference = 'SilentlyContinue', but is there any way to disable the actual checks, in order to speed performance?

Example:

PS SQLSERVER:\> set-location \sql\myserver\default
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on '<servername>' failed with the following error: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Scenario: SQL Agent job using a PowerShell job step takes 30+ seconds just to spin up and connect to the local SQL Server, before doing any work.

Apparentely, after some testing...there is a way to speed up SQLPS load time (tremendously) and prevent the WMI warnings (if you don't need the SQLAS module loaded.

How I tested this on my laptop, was simply by shutting down the WMI service: Stop-Service winmgmt -Force. I then proceeded to edit the module files for SQLPS for my SQL Server 2012 local instance.

As soon as I load the module initially, I get the same warning messages you show in your question. The one that causes the specific problem is the SqlPsPostScript.PS1 file. This file is a nested module based on the module manifest, so everytime you load SQLPS this script is going to be executed.

This SqlPsPostScript.PS1 file is also why when the module is loaded it changes your location in your prompt. The first line of the file is Set-Location SQLSERVER:. [If you comment out that line it can also speed up the load time for the module a good bit.]

The lines that are causing the WMI warnings are from the two lines of code that will load the SQLAS module. Which if you look at the documentation for AS PowerShell in BOL it states it is loaded with SQLPS here. It also goes to mention when configuring remote administration for AS that in step 3 WMI Service has to be running. Which if you don't have access to WMI equates to the service not appear as running.

So in the end, as long as you don't need Analysis Services PowerShell module you can edit the SqlPsPostScript.PS1 file, found under:

C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\PowerShell\Modules\SQLPS\

And comment out the following lines:

$m = Get-Module -ListAvailable | where {$_.Name -eq "SQLASCmdlets"}
if($m -ne $null) { Import-Module $m -Global }

Once I did that and then saved the file, I no longer received the warnings when loading SQLPS.


How to sort rows after union I have two tables that I want to combine.

I need to be able to show all DelDates that had changes from the original (record 1). Record 1 as the first Record and Record 0 (table 2) always being the last record.

Result should be:

I am new to SQL and with my research, it seems I need to use UNION to join both tables and to join the new table (from Union) by itself.  When I tried doing that, it created duplicate rows and columns.

You are correct - if you have two datasets with identical columns then UNION is the correct way to append the second dataset onto the first. Moreover I would suggest you use UNION ALL. UNION by itself guarantees unique values in the output. Usually this requires a sort and further processing to detect and eliminate duplicates. In your case I do not think you have duplicates so UNION ALL would be appropriate.

The real problem seems to be the sort order. You want the combined dataset to be sorted by Record but with 0 as the last item. The rows exist in two blocks: the zero rows and the other rows. If we assign a "block number" to each they can be sorted accordingly. Clearly this cannot be achieved by changing Record itself so another column must be introduced. This new artificial column need not be returned form the query.

You don't mention which DBMS you use. I have SQL Server to hand; I'll use it. For future questions please do not include screen shots of the data. I makes it much too hard to reproduce your situation. Instead include in the quesiton the CREATEs and INSERTs required to reproduce the test cases and also what queries you've tried and why the don't work. See mcve. I've written the minimum to demonstrate the proposed solution.

drop table if exists Table1;
drop table if exists Table2;
go

create table Table1(Name varchar(9), Record int); -- other columns omitted for simplicity
create table Table2(Name varchar(9), Record int);
go

insert Table1 values ('Joy', 1), ('Joy', 2);
insert Table2 values ('Joy', 0);
go

-- I've written this as a Common Table Expression (CTE). A sub-query would work just as well
;with Data as
(
select
    Name, Record, BlockNumber = 2   -- BlockNumber for Table2 is greater than that for Table1
from Table2  -- the order of queries within the CTE does not affect the output

union all

select
    Name, Record, BlockNumber = 1
from Table1
)
select
    Name, Record -- note that BlockNumber is not returned
from Data
order by
    Name,           -- the leading column of the sort so one person's rows all appear together
    BlockNumber,    -- will put Table2's row after Table1's.
    Record;         -- since all Table1's rows are in the same block they'll sort by Record


How to give user access permission to dbo and single custom schema? So I created the following users which owns single schema:

userA -> owns -> schemaA
userB -> owns -> schemaB
userC -> owns -> schemaC

The userA should be able to access dbo & schemaA schemas, but should not be able to access (i.e select/update/insert/delete...) schemaB data!

May be I can do something as below:

DENY SELECT ON schema::[schemaB] TO [userA]
DENY DELETE ON schema::[schemaB] TO [userA]
DENY UPDATE ON schema::[schemaB] TO [userA]
DENY INSERT ON schema::[schemaB] TO [userA]

GRANT SELECT ON schema::[dbo] TO [userA]
GRANT DELETE ON schema::[dbo] TO [userA]
GRANT UPDATE ON schema::[dbo] TO [userA]
GRANT INSERT ON schema::[dbo] TO [userA]

However, is there any better approach for doing this, so the userA can only access dbo and schemaA, and not schemaB & schemaC? And same vice-versa for userB & userC!

When you create your users userA, userB, userC, they have no permissions at all
(unless you grant some permission to public database role, but by default no permission is granted to public)

So they cannot access nothing, even dbo schema, so you have no need to revoke/deny any permission.

Then you create schemaA, schemaB and schemaC (there is no need to make any particular user the owner of these schemas) and grant the permissions to your users as follows:

GRANT CONTROL ON schema::[dbo] TO [userA];
GRANT CONTROL ON schema::[schemaA] TO [userA];

GRANT CONTROL ON schema::[dbo] TO [userB];
GRANT CONTROL ON schema::[schemaB] TO [userB];

GRANT CONTROL ON schema::[dbo] TO [userC];
GRANT CONTROL ON schema::[schemaC] TO [userC];

Of course you can do the same thing granting the permissions to Roles, not only to individual users


Should I use MongoDB or a NoSQL solution at all for this? I apologize if this belongs in web applications or something.

I'm creating a web application that is sort of a similar concept to github. Basically I plan to allow someone to create a design of something (lets say a recipe for a cake for instance) and they can add new ingredients (or from already existing, which will have attached calories, etc.). But I want to add in that revision control aspect, new versions, branches etc.. Also plan to allow cloning of a design.

That's a basic idea. But would those main functions of a revision-control-esque setup beg for nosql? Am I crazy and jumping into a hip looking db too fast?

Let me know if more details are necessary.

You certainly could do this but if revision control is what your after for documents use something like subversion or git.  In general storing documents that do not depend on transactions and you are not storing sensitive information that requires immediate atomicy and consitency like financial transactions then a nosql solution may fit your needs and be appropriate.

To the best of my knowledge you'd have to be rolling your own in the revision diffs if that's something you're really looking for whether from a nosql or traditional sql solution.

Regardless, if you're working on something trivial and want to play around with newer technologies that you're unfamiliar with that's great, go for it.

At the same time, if this is for a commercial client and you aren't familiar with mongo, couch, etc it's probabaly better to stick with what you know and leave the sandbox for play time until you're ready.


What is stored in a transaction log file? What does the transaction log file store? Is it the blocks of transactions to be executed, is it the snapshot of records before beginning the execution of a transaction or is it just the statements found in a transaction block?

The transaction log stores every transaction and the changes made by those transactions to the database. This answer gets very complicated when going into detail but generally it stores the following.

Modification Operations
Log sequence numbers (LSN)
Row data. Before/After/Partial/Meta-data, depending on the operation.
Reserved space for rollbacks

It does not store queries or t-sql statements. Please refer to this overview by Microsoft.

https://msdn.microsoft.com/en-gb/library/ms190925.aspx

The specific format is not fully documented and is subject to change betwen versions.


Oracle DB 11gR2 netca installation failing I am getting below error when I run netca from command line

cmd: /u01/app/oracle/product/11.2.0/db_1/bin/netca /silent /responsefile=/oracle_binaries/database/response/netca.rsp

[main] [ 2013-05-17 10:52:47.975 IST ] [CmdlineArgs.parseLogParam:290]  Parsing /log argument...
[main] [ 2013-05-17 10:52:47.976 IST ] [CmdlineArgs.parseLogParam:345]  Returning Log File = null
java.lang.ArrayIndexOutOfBoundsException: 2
        at oracle.net.ca.CmdlineArgs.parseArgs(CmdlineArgs.java:416)
        at oracle.net.ca.InitialSetup.<init>(NetCA.java:3969)
        at oracle.net.ca.NetCA.main(NetCA.java:405)

Answer from below thread solved my problem

https://forums.oracle.com/forums/thread.jspa?threadID=2208254

apparently '=' is not needed in responsefile argument in netca cmd

/u01/app/oracle/product/11.2.0/db_1/bin/netca /silent /responsefile /oracle_binaries/database/response/netca.rsp


Initialize replication sql server from backup of many databases I want to know how I can initialize a subscription from a backup of a publication database. I have three diferent databases that will point to the same subscriber. For the first time there is no problem but the second and the third one I have problems. How can I initialize the suscription without lossing the data from the first time and the data from the other two db integrate to the first?

You can't do that.  Initializing from a backup is only appropriate when you want to overwrite the subscriber database.  In your scenario you should probably use regular snapshot initialization, so each subscription will create and load its own tables.


Move MySQL Database to Amazon EC2 Servers I have a website which has a 5 GB big mysql database.

I am thinking if it is a good idea to move only the database to amazon, all front-end etc. (PHP) will remain.

I've red already a lot about my Idea and I am not sure whether it is a good idea. Some people say its not a good idea to move a database file to EC2, I will need ECB for a relational Database file like MySQL.

And the amazon FAQ etc. are filled with so much informations I can hardly decide what is important and whats not.

Someone can comment on this? That would be helpful.

P.S.: I am not the owner the main person is to busy atm. and asked me to do the research.

Thanks! I hope I am not off-topic. If so I am sorry, provide me then the right StackExchange where to post such a question.

There is nothing intrinsically wrong with moving a database to AWS EC2.  IN the end, they're just servers, but they're set up in a very specific way.

You have to be fully aware of the limitations of the platform before you start, so those documents that you don't know where to start?  Read them all.

You've got to be aware that there are different ways of working when you start using AWS.  Some fundamental differences between having a server in your server room that you are in complete control of vs a server in AWS' data centre somewhere that somebody else is in control of.

Learn about RDS, learn about different storage types, learn about EC2.  Make sure you understand the costs before you put anything in there.

Use the free year's worth of free instances for all it's worth.  Test, ensure that what you want to do is possible.


Data modeling membership and profiles Anyone have an idea on how to solve this issue.

Im modeling my membership and user profile DB and run into one issue I have to solve.
I have this layout of tables.

Membership (where all basic info is stored, like password etc.)
UserProfile (where all extra info is stored, like name etc.)

In my application I will have two user groups (Parent/Kid), and both of them have the need of registering different types of profile data. Some are the same, but some is different.

How should I solve this? Should I just add all fields to the UserProfile table, and settle with the fact that some fields will be redundant.

Or should I create a new table for each user group and link them to the UserProfile?

First, you should never ever store user passwords! Read the following two articles before continuing:
http://blog.moertel.com/posts/2006-12-15-never-store-passwords-in-a-database.html
http://www.codinghorror.com/blog/2007/09/youre-probably-storing-passwords-incorrectly.html

Your user profile table design depends on extensibility and how common the user profile types are. In object oriented design you ould probably create a base class and derive one each for parents and kids. That's not possible in SQL though.

In general the best approach is to create a single table with columns for all profile types in this case. NULL values in columns are a common feature and cost next to nothing in terms of used table space. You can get away with multiple tables, but at the least it will make your queries very awkward, because you would need three tables: One for the common columns, and one for each profile. If you want to do a generic SELECT on the membership with added profile data, you have to join all three tables on it while with a single table you only have to join one.


limiting rows in joins So I have this problem with one of my queries.

The query is meant to get data from table patients and join the table patient_nurse, then table nurses to it. The patients table is as follows

id | name | added
----------------------
1  | John | 2014-03-30
2  | Doe  | 2014-03-31

The patient_nurse table looks like this

id | patient_id | nurse_id | shift     | added
---------------------------------------------------
1  | 1          | 2        | MORNING   | 2014-03-30
2  | 1          | 3        | EVENING   | 2014-03-30
3  | 2          | 2        | AFTERNOON | 2014-03-31
4  | 2          | 1        | MORNING   | 2014-03-31
5  | 2          | 3        | EVENING   | 2014-03-31
6  | 2          | 4        | EVENING   | 2014-03-31

The nurses table looks like this

id | name
----------
1  | Jane
2  | Joe
3  | Will
4  | Harry

How should the query be if I want to get the following information?:

patient_name | nurse_primary | nurse_morning | nurse_afternoon | nurse_evening
------------------------------------------------------------------------------
John         | NULL          | Joe           | NULL            | Will
Doe          | NULL          | Jane          | Joe             | Will

As you can see, I just need 1 nurse data for 1 shift on a patient (why nurse Harry is left out), which is the nurse inserted first (lower id value).

Try this:

select
 patients.name AS patient_name
,NULL As nurse_primary
,m.`name` As nurse_morning
,a.name As nurse_afternoon
,e.name As nurse_evening
FROM
patients left join
 (SELECT patient_id, n.`name` AS `name`
  FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id
  where pn.shift='MORNING' AND
  pn.id = (SELECT MIN(id) FROM patient_nurse pn1
        WHERE pn1.patient_id = pn.patient_id AND pn1.shift='MORNING')) AS m
ON m.patient_id = patients.id
 left join
 (SELECT patient_id, n.`name` AS `name`
  FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id
  where pn.shift='AFTERNOON' AND
  pn.id = (SELECT MIN(id) FROM patient_nurse pn1
        WHERE pn1.patient_id = pn.patient_id AND pn1.shift='AFTERNOON')) AS a
ON a.patient_id = patients.id
 left join
 (SELECT patient_id, n.`name` AS `name`
  FROM patient_nurse pn INNER JOIN nurses n ON pn.nurse_id = n.id
  where pn.shift='EVENING' AND
  pn.id = (SELECT MIN(id) FROM patient_nurse pn1
        WHERE pn1.patient_id = pn.patient_id AND pn1.shift='EVENING')) AS e
ON e.patient_id = patients.id

SQL Fiddle


Adding Calculated Columns together to create a Total I am creating a tracking sheet, based off of multiple tables. I then use a CASE statement to determine the progress of each section. I would like to then take the results of these 3 calculated col and create a Total ?

Select Case
     ....
End as [Val1],
Select Case
     ....
End as [Val2],
(Val1 + Val2) as [Total]
From Table

Currently the Addition line is not working since the Val1 and Val2 are 'Invalid Column Names'

How can I fix this ?

SELECT
    d.DataSourceID as [DataSource],
    c.Name as [Custodian Name],
    et.Name as [Source Type],
    ed1.QCBarcode as [ED1 QC Barcode],
    ed1code.Name as [ED1 Status],
Case
    When ed1code.Name like '01%' then 14
    When ed1code.Name like '02%' then 28
    When ed1code.Name like '03%' then 42
    When ed1code.Name like '04%' then 56
    When ed1code.Name like '05%' then 71
    When ed1code.Name like '06%' then 85
    When ed1code.Name like '07%' then 100
    When ed1code.Name like '08%' then 0
End  as [ed1Calc],
ed3.ProcessingCase as [ED3 LAW Case Name],
ed3code.Name as [ED3 Status],
Case
    When ed3code.Name like '01%' then 50
    When ed3code.Name like '02%' then 100
    When ed3code.Name like '03%' then 0
    When ed3code.Name like '04%' then 0
End  as [ed3Calc],
ed4.ED4ID as [ED 4 Reference],
ed4code.Name as [ED4 Status],
Case
    When ed4code.Name like '01%' then 34
    When ed4code.Name like '02%' then 66
    When ed4code.Name like '03%' then 100
End  as [ed4Calc],
 ( (ed1Calc + ed3Calc + ed4Calc)/3 ) as [Overall Status]
/*Basic  layout */
FROM [EDDSDBO].[DataSources] d
JOIN  [EDDSDBO].[Custodian] c ON d.Custodian = c.ArtifactID
JOIN [EDDSDBO].[EvidenceType] et ON d.EvidenceType= et.ArtifactID
JOIN [EDDSDBO].[f4868106f4868107] fed1 on fed1.f4868107ArtifactID = d.ArtifactID
JOIN [EDDSDBO].[ED1PreProcessingRecord] ed1 ON fed1.f4868106ArtifactID = ed1.ArtifactID
JOIN  [EDDSDBO].[ED3LawLoadingRecord] ed3 ON ed1.ArtifactID = ed3.ED1Reference
JOIN [EDDSDBO].[ED4LawRelativityMigrationRecord] ed4 ON ed3.ArtifactID =    ed4.ED3Reference
 /*status*/
--ed1
JOIN [EDDSDBO].[ZCodeArtifact_1000129] ed1Status  ON ed1.ArtifactID =  ed1Status.AssociatedArtifactID
JOIN  [EDDSDBO].[Code] ed1code ON ed1Status.CodeArtifactID = ed1code.ArtifactID

--ed3
JOIN [EDDSDBO].[ZCodeArtifact_1000156] ed3Status  ON ed3.ArtifactID = ed3Status.AssociatedArtifactID
JOIN  [EDDSDBO].[Code] ed3code ON ed3Status.CodeArtifactID = ed3code.ArtifactID
--ed4
JOIN [EDDSDBO].[ZCodeArtifact_1000161] ed4Status ON ed4.ArtifactID = ed4Status.AssociatedArtifactID
JOIN  [EDDSDBO].[Code] ed4code ON ed4Status.CodeArtifactID = ed4code.ArtifactID

You don't specify any specific RDBMS.

A method that should work in all/most would be to use a derived table.

SELECT Val1,
       Val2,
       Val1 + Val2 AS Total
FROM
(
Select Case
     ....
End as [Val1],
Select Case
     ....
End as [Val2]
From Table
) d /*<-- arbitrary derived table alias*/

Probably you are on SQL Server (from the square brackets) in which case you could also use

SELECT Val1,
       Val2,
       Val1 + Val2 AS Total
FROM   TABLE
       CROSS APPLY (VALUES(Case ....End,Case ....End)) V( Val1, Val2)

depending on the exact expression behind Val1 and Val2 (e.g. you can't use this and get correct results if they reference window functions).


Oracle Data Pump Import with running advanced replication In our system we have a running advanced replication between two databases(11g release 2) and want to use pump import to import a backup on one site. However the imported data is not replicated to the other site. All following changes get replicated as usual.

Edit for calification: Replication is active and both sites have the same data. I then import something into the primary site. These imported changes are NOT replicated. The second site still has the "pre-import" data.
When something in the primary site changes after the import, these changes get still replicated. So the replication itself is still active.
The pump import seems not to trigger the replication of the imported data.

I could not find anything about this in the oracle documentation. Is it even supported or is there something missing from our import and export commands?

expdp <connection data> dumpfile='xyz.dmp' SCHEMAS=XYZ EXCLUDE=USER NOLOGFILE=Y REUSE_DUMPFILES=Y

impdp <connection data> DUMPFILE='xyz.dmp' NOLOGFILE=y IGNORE=y INCLUDE=table_data TABLE_EXISTS_ACTION=skip

Basically Advanced Replication works with materialized views, materialized view logs and triggers.
To replicate data from one side to another, you need to have INSERT, UPDATE or DELETE statements to make the replication mechanism recognize that there is new/changed data. When you import data with direct path method you skip all this stuff. This means you need conventional path method to import data.

DataPump chooses the method by itself on a set of conditions. These conditions can be found here: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL804

I'm not 100% sure which method is being used in your case but i doubt that it is conventional path.

Oracle does not tell how to force conventional path method but you could try to import the data with the old imp utility. When the export is done with DIRECT=N the imp should be done in conventional method.

Maybe someone else has experiences with Advanced Replication and DataPump. My experiences are limited since Oracle pushes Golden Gate as the future replication technology.


Can SQL Server Bulk Copy cause timeouts when querying the table In SQL Server 2008 R2, can using BulkCopy to upload thousands of rows of data cause problems for queries taking place on the same table?

Specifically could it cause the queries to time out?

The table being queried has millions of rows.

It definitely could.

It requires locks just like any other insert operation. If enough locks are taken it will escalate to a full table lock (assuming the table allows it). Any insert operation like this would block anything else that was trying to read the data, unless NOLOCK was specified for those queries (which I am not recommending here).

You can actually set up a trace to see what types of locks are being taken if you are interested. Just set up a Profiler trace that gets the "Locks: Acquired" and "Locks: Escalation", and then do a bulk insert.

Word of warning, Profiler can cause a hit to performance, so I would either run the trace on a dev server, or if you have to run it in production, export the script and execute it as a server-side trace.