datetime2(0) vs datetime2(2) According to the documentation datetime2 (Transact-SQL):

  Storage size
  6 bytes for precisions less than 3.
  7 bytes for precisions 3 and 4.
  All other precisions require 8 bytes.

The size of datetime2(0), datetime2(1), datetime2(2) use the same amount of storage (6 bytes).

Would I be correct in saying that I might as well go with datetime2(2) and gain the benefit of the precision without any additional size costs?

Please note:

This column is indexed with the PK to form a compound clustered index (used for table partitioning)
I do not care about milliseconds

Would datetime2(0) be more cpu efficient when used in a where clause, or when seeking through an index?

This is a massive table, so the smallest optimization will make a big difference.

The size of dateTime2(0), dateTime2(1), dateTime2(2), dateTime2(3) use
  the same amount of storage. (6 Bytes)

  Would I be correct in saying that I might as well go with dateTime2(3)
  and gain the benefit of the precision without any additional size
  costs.

No, you misinterpreted the documentation. Note the doc states storage size 6 bytes for precisions less than 3 (emphasis mine). So a precision equal to 3 will require 7 bytes.

If you don't care about milliseconds, datetime2(0) would be the proper data type and precision.  The best practice is to specify the proper data type and precision based on the data stored as this will inherently provide optimal storage and efficiency. That being said, I would not expect a significant performance impact based on the specified datetime2 precision as long as the storage size is the same but I have not specifically tested that myself.

Application requirements will dictate what must be stored in the database when a greater precision is available in the source. For example, for an order entry time sourced from SYSDATETIME(), users may not want 100 nanosecond precision. Again, choose the data type and precision for new development according to requirements and you will generally get optimal performance without additional thought:

date - you don't need time
smalldatetime - you don't need seconds
datetime2(0) - you don't need fractional seconds
datetime2(1-7) - you need fractional seconds of the specified precision
datetimeoffset(0-7) - you need date and time with time zone awareness
time(0-7) - you need time only (no date) with fractional seconds of the specified precision

Although datetime2 is most appropriate for new development as listed above, one may sometimes need to use datetime (fixed precision 3 with 1/300 fractional seconds accuracy) instead for compatibility with legacy datetime applications, thus avoiding implicit conversions and unexpected comparison behavior, but at the expense of fractional second accuracy and increased storage.

Consider that storing a greater precision than required may also have a development cost. If one stores a time component with fractional seconds when only whole second precision is required, queries will still need to consider the fractional seconds to return the correct results. For example, with an app where the user selects a time range via a UI that only allows for whole seconds, the app code would need to account for the fractional seconds in the end time range value and adjust the user-supplied value accordingly (e.g. WHERE OrderEntryTime BETWEEN '2017-01-11T08:00:00.00.00' AND '2017-01-11T08:59:59.99' or WHERE OrderEntryTime >= '2017-01-11T08:00:00.00' AND OrderEntryTime < '2017-01-11T09:00:00.00'). This will add code complexity.


Retrieve the triggering SQL statement as is I am trying to get the last executed query and log it (as is, a varchar).

For example:

insert into table (firstname,lastname,birthdate) values ('donald','obama','1990-01-01')

create trigger dbo.logqueries
on dbo.users
for update, insert, delete
as
begin

    declare @query nvarchar(400)

    hopefully a simple method to recover the query as is
    without having to re-prepare the query before insert

    insert into queryLog (userId,query) values (@userId,@query)
end

This is the result I got from trying McNets' answer:

DECLARE @TEMP TABLE
(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000))
INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS')
set @query =(SELECT EventInfo FROM @TEMP)

Have a look at MS-Docs about DBCC INPUTBUFFER

  Displays the last statement sent from a client to an instance of Microsoft SQL Server.

Add WITH NO_INFOMSGS just to suppress all informational messages.

Permissions

On SQL Server requires one of the following:

User must be a member of the sysadmin fixed server role.
User must have VIEW SERVER STATE permission.
session_id must be the same as the session ID on which the command is being run.

CREATE TABLE Test (Id int PRIMARY KEY, Val varchar(10));
CREATE TABLE Audit (Id int IDENTITY PRIMARY KEY, EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000));
GO

CREATE TRIGGER AuditTest
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO Audit EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS');
END
GO

INSERT INTO Test VALUES(1, 'John');
GO

2 rows affected

INSERT INTO Test VALUES(2, 'Anna');
GO

2 rows affected

UPDATE Test SET Val='Peter' WHERE Id=1;
GO

2 rows affected

DELETE FROM Test WHERE Id=2;
GO

2 rows affected

SELECT * FROM Audit;
GO

Id | EventType      | Parameters | EventInfo
-: | :------------- | ---------: | :--------------------------------------
 1 | Language Event |          0 | INSERT INTO Test VALUES(1, 'John');
 2 | Language Event |          0 | INSERT INTO Test VALUES(2, 'Anna');
 3 | Language Event |          0 | UPDATE Test SET Val='Peter' WHERE Id=1;
 4 | Language Event |          0 | DELETE FROM Test WHERE Id=2;

As sepupic has pointed out on his comments if you are using an stored procedure, DBCC INPUTBUFFER returns the EXEC sentence instead of the SQL statement.

CREATE PROCEDURE dbo.InsertTest(@Id int, @Val varchar(10))
AS
BEGIN
    INSERT INTO Test VALUES (@Id, @Val);
END
GO

EXEC dbo.InsertTest @Id=5, @Val='Peper';
GO

2 rows affected

Id | EventType      | Parameters | EventInfo
-: | :------------- | ---------: | :---------------------------------------
 1 | Language Event |          0 | INSERT INTO Test VALUES(1, 'John');
 2 | Language Event |          0 | INSERT INTO Test VALUES(2, 'Anna');
 3 | Language Event |          0 | UPDATE Test SET Val='Peter' WHERE Id=1;
 4 | Language Event |          0 | DELETE FROM Test WHERE Id=2;
 5 | Language Event |          0 | EXEC dbo.InsertTest @Id=5, @Val='Peper';

dbfiddle here

Let me suggest you another approach, instead of store SQL commands, you can keep track of all changes of your data on this way:

1- Create a duplicate of your table and add some audit fields like, action, date, user, etc.

CREATE TABLE Test (Id int PRIMARY KEY, Val varchar(10));
CREATE TABLE Audit (Action varchar(10), CreatedAt datetime, [User] nvarchar(128), Id int, Val varchar(10));
GO

2- Lets the trigger to store all new changes.

CREATE TRIGGER AuditTest
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @Action varchar(10);
    IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
        SET @Action = 'UPDATE'
    ELSE IF EXISTS(SELECT 1 FROM inserted)
        SET @Action = 'INSERT'
    ELSE
        SET @Action = 'DELETE';

    IF @Action = 'UPDATE' OR @Action = 'INSERT'
    BEGIN
        INSERT INTO Audit
        SELECT @Action, GETDATE(), SUSER_NAME(), Id, Val
        FROM   inserted;
    END
    ELSE
    BEGIN
        INSERT INTO Audit
        SELECT @Action, GETDATE(), SUSER_NAME(), Id, Val
        FROM   deleted;
    END
END
GO

Now the final result will show something like this:

SELECT * FROM Audit;
GO

Action | CreatedAt           | User                                    | Id | Val
:----- | :------------------ | :-------------------------------------- | -: | :----
INSERT | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  1 | John
INSERT | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  2 | Anna
UPDATE | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  1 | Peter
DELETE | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  2 | Anna
INSERT | 01/08/2017 15:05:03 | fiddle_404da64bd81c4665970c0b39386771b0 |  5 | Peper

And it doesn't matter if you use a direct command, an stored procedure or you're calling it from an external application.

dbfiddle here


Foreign key, key split in two tables I have to record a list of events made of action, reason (optional, may be multiple), user.
I have a master table with all the valid action, and another with all the valid action-reason combination.
All tables have a unique identity (not displayed in the example).

master_actions
action
A1
A2
A3

master_action_reasons
action  reason
A1      NULL
A2      R1
A2      R2
A3      R1
A3      R2
A3      R3

events
action  user   id
A1      John   E1
A3      Lisa   E2
A3      Sue    E3

reasons
event reason
E2    R1
E2    R3
E3    R2

Question: how do I make sure that the combination action-reason is valid, i.e. exists in master_action_reasons? Since the action is in one table, and the reason in another, I cannot use a foreign key.

This is a good case for CHECK constraints using a function. Step one, write a function that does something like (EDIT: Used Event instead of Action sorry!):

CREATE FUNCTION CheckReasonValid(@intReason, @intEvent)
RETURNS bit
AS
BEGIN
   DECLARE @NumRows int
   SELECT @NumRows = COUNT(*) FROM master_action_reason mar
       INNER JOIN Events e ON mar.Action = e.Action
       WHERE mar.Reason = @intReason AND e.Event = @Event
   RETURN @NumRows
END;

Then in your table definition, add the CHECK constraint, something like:

ALTER TABLE Reasons
ADD CONSTRAINT chkActionReasonValid CHECK (dbo.CheckReasonValid(Reason,Event) >= 1 );


How to run SQL Job only on primary replica in AlwaysOn Availability Groups? I have SQL Jobs that need to only on primary replica on AlwaysOn Availability Group. When jobs were originally created ServerA was primary, at later point ServerB became primary so jobs that were on ServerA failed and jobs had to be manually recreated on ServerB to run properly.

What is the way to have jobs run only on Primary replica of AlwaysOn Availability Group?

An easy way to get job to execute only on primary node is to put simple check for the job to verify which node is 'PRIMARY' at the time job is being executed. It can be done with simple sql statement:

DECLARE @ServerName NVARCHAR(256)  = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
        ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'
BEGIN
    --Logic for the job goes here
END

Now just place logic between BEGIN and END keywords.

Just like Aaron pointed out in the comments, a job still has to be created on all servers with the same logic to ensure that it only runs on primary replica and not on the secondaries. There are scripts and tools that can be used to copy jobs from primary replica to the secondary replicas.


How to clear all plans from a single database? I want to clear out the procedure cache for just one database, not the entire SQL Server. Is there a way?

DBCC FLUSHPROCINDB (<db_id>)

This command allows you to specify a particular database id, and then clears all plans from that particular database. Check more information here.

DBCC FREEPROCCACHE

This command removes all cached plans from memory

  It is, of course, recommended that you don’t use these commands on
  your production servers, as it could impact the performance of your
  running applications. Usually, you want to keep plans in cache.


How to select multiple values into an array and loop through? (postgres 9.3) I have simple table for the sake of argument. I have a function that selects ids and loops through them called loop_test. I can select an array of ids and loop through them, causing my changes in a transaction.

CREATE OR REPLACE FUNCTION loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[];
        _id INTEGER;
BEGIN
        SELECT ARRAY(SELECT id FROM loop_test) INTO _ids_array;
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET looped = TRUE WHERE id = _id;
        END LOOP;
END;
$$ LANGUAGE plpgsql;

Table:

db=# \d loop_test;
      Table "public.loop_test"
    Column     |  Type   | Modifiers
---------------+---------+-----------
 id            | integer |
 other_id      | integer |
 id_copy       | integer |
 other_id_copy | integer |
 looped        | boolean |

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped
----+----------+---------+---------------+--------
  1 |       10 |         |               |
  6 |       15 |         |               |
  2 |       11 |         |               |
  7 |       16 |         |               |
  3 |       12 |         |               |
  4 |       13 |         |               |
  5 |       14 |         |               |
(7 rows)

When I call select loop_test(), I get the following results:

db=# select * from loop_test;
 id | other_id | id_copy | other_id_copy | looped
----+----------+---------+---------------+--------
  1 |       10 |         |               | t
  6 |       15 |         |               | t
  2 |       11 |         |               | t
  7 |       16 |         |               | t
  3 |       12 |         |               | t
  4 |       13 |         |               | t
  5 |       14 |         |               | t
(7 rows)

I would, however, like to create a function to select both the id and the other_id into an array. I was told about using something like agg_array, but I don't completely understand how that works.

I was imagining something like the following?

CREATE OR REPLACE FUNCTION agg_loop_test() RETURNS void AS $$
DECLARE
        _ids_array INTEGER[][];
        _id INTEGER;
BEGIN
        SELECT AGG_ARRAY(SELECT id, other_id FROM loop_test) INTO _ids_array;
        FOREACH _id IN ARRAY _ids_array
        LOOP
                UPDATE loop_test SET id_copy = _id[0], other_id_copy = _id[1] WHERE id = _id[0];
        END LOOP;
END;
$$ LANGUAGE plpgsql;

A much better way, yet: just update. No loop needed.

UPDATE loop_test
SET    id_copy = id
     , other_id_copy = other_id;
WHERE  id IS NOT NULL;

The WHERE condition is only useful if id can be null and you want a perfect equivalent of what you had.

Loop

If you are just exploring loops - you can assign multiple variables:

CREATE OR REPLACE FUNCTION better_loop_test()
  RETURNS void AS
$func$
DECLARE
   _id int;
   _other_id int;
BEGIN
   -- example makes no sense, just a loop demo
   FOR _id, _other_id IN
      SELECT id, other_id FROM loop_test
   LOOP
      UPDATE loop_test
      SET    id_copy = _id
           , other_id_copy = _other_id
      WHERE id = _id;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

While you just need the two columns of known type, that may be a bit cheaper than fetching whole (possibly big) rows.


`ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave replica of the database and I wanted to take the advantages of innodb_file_per_table during the process. So I set innodb_file_per_table in my slave configuration and imported the dump of the database. To my surprise, it failed with

  ERROR 1114 (HY000) at line 5602: The table 'Sample' is full

The file Sample.ibd is currently about 93GB, with more than 600GB free space available on the partition, so it's not a disk free-space issue. Neither it seems to be hitting any kind of file-system limit (I'm using ext4).

I'd be grateful for any ideas what could be the cause, or what to investigate.

Update: I'm using mysql  Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64).

SELECT @@datadir; -- returns `/home/var/lib/mysql/`
SHOW VARIABLES LIKE '%innodb_data_file_path%'; -- ibdata1:10M:autoextend

df -h /home/var/lib/mysql/
768G   31G  699G   5% /home

FACTS

You said you are using ext4. File size limit is 16TB. Thus, Sample.ibd should not be full.

You said your innodb_data_file_path is ibdata1:10M:autoextend. Thus, the ibdata1 file itself has no cap to its size except from the OS.

Why is this message coming up at all? Notice the message is "The table ... is full", not "The disk ... is full". This table full condition is from a logical standpoint. Think about InnoDB. What interactions are going on ?

My guess is InnoDB is attempting to load 93GB of data as a single transaction. Where would the Table is Full message emanate from? I would look at the ibdata1, not in terms its physical size (which you already ruled out), but in terms of what transaction limits are being reached.

What is inside ibdata1 when innodb_file_per_table is enabled and you load new data into MySQL?

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

My suspicions tell me that the Undo Logs and/or Redo Logs are to blame.

What are these logs? According to the Book

Chapter 10 : "Storage Engines" Page 203 Paragraphs 3,4 say the following:

  The InnoDB engine keeps two types of logs: an undo log and a redo log. The purpose of an undo log is to roll back transactions, as well as to display the older versions of the data for queries running in the transaction isolation level that requires it. The code that handles the undo log can be found in storage/innobase/buf/log/log0log.c.

  The purpose of the redo log is to store the information to be used in crash recovery. It permits the recovery process to re-execute the transactions that may or may not have completed before the crash. After re-executing those transactions, the database is brought to a consistent state. The code dealing with the redo log can be found in storage/innobase/log/log0recv.c.

ANALYSIS

There are 1023 Undo Logs inside ibdata1 (See Rollback Segments and Undo Space). Since the undo logs keep copies of data as they appeared before the reload, all 1023 Undo Logs have reached its limit. From another perspective, all 1023 Undo Logs may be dedicated to the one transaction that loads the Sample table.

BUT WAIT...

You are probably saying "I am loading an empty Sample table". How are Undo Logs involved? Before the Sample table was loaded with 93GB of data, it was empty. Representing every  row that did not exist must take up some housecleaning space in the Undo Logs. Filling up 1023 Undo Logs seems trivial given the amount of data pouring into ibdata1. I am not the first person to suspect this:

From the MySQL 4.1 Documentation, note Posted by Chris Calender on September 4 2009 4:25pm:

  Note that in 5.0 (pre-5.0.85) and in 5.1 (pre-5.1.38), you could receive the "table is full" error for an InnoDB table if InnoDB runs out of undo slots (bug #18828).

Here is the bug report for MySQL 5.0 : http://bugs.mysql.com/bug.php?id=18828

SUGGESTIONS

When you create the mysqldump of the Sample table, please use --no-autocommit

mysqldump --no-autocommit ... mydb Sample > Sample.sql

This will put an explicit COMMIT; after every INSERT. Then, reload the table.

If this does not work (you are not going to like this), do this

mysqldump --no-autocommit --skip-extended-insert ... mydb Sample > Sample.sql

This will make each INSERT have just one row. The mysqldump will be much larger (10+ times bigger) and could take 10 to 100 times longer to reload.

In either case, this will spare the Undo Logs from being inundated.

Give it a Try !!!

UPDATE 2013-06-03 13:05 EDT

ADDITIONAL SUGGESTION

If the InnoDB system table (a.k.a ibdata1) strikes a filesize limit and Undo Logs cannot be used, you could just add another system tablespace (ibdata2).

I just encountered this situation just two days ago. I updated my old post with what I did: See Database Design - Creating Multiple databases to avoid the headache of limit on table size

In essence, you have to change innodb_data_file_path to accommodate a new system tablespace file. Let me explain how:

SCENARIO

On disk (ext3), my client's server had the following:

[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  2 00:15 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  2 00:15 ibdata2

The setting was

innodb_data_file_path=ibdata1:346M;ibdata2:500M:autoextend:max:10240000M

Note that ibdata2 grew to 2196875759616 which is 2145386484M.

I had to embed the filesize of ibdata2 into innodb_data_file_path and add ibdata3

innodb_data_file_path=ibdata1:346M;ibdata2:2196875759616;ibdata3:10M:autoextend

When I restarted mysqld, it worked:

[root@l*****]# ls -l ibd*
-rw-rw---- 1 s-em7-mysql s-em7-mysql     362807296 Jun  3 17:02 ibdata1
-rw-rw---- 1 s-em7-mysql s-em7-mysql 2196875759616 Jun  3 17:02 ibdata2
-rw-rw---- 1 s-em7-mysql s-em7-mysql   32315015168 Jun  3 17:02 ibdata3

In 40 hours, ibdata3 grew to 31G. MySQL was once again working.


ms/write on tempdb data files are dead slow I don't know why writes on my tempdb data files are extremely slow. ms/write on each of the tempdb data files reaches to more than 800 ms!!! that's even after I moved the 8 equally sized (7GB) tempdb data files to separate drive on the HP StoreVirtual 4530 40TB SAS Storage SAN we have. The SQL Server we have is Standard 2008R2. hosted on 8 cores 80GB memory virtual machine. I would like to note that the top ten waits I have on that instance are:

CXPACKET avg:0.0011sec
WRITELOG avg:0.0149sec
PAGEIOLATCH_SH avg: 0.0080sec
IO_COMPLETION avg: 0.0103sec
PREEMPTIVE_OS_PIPEOPS avg: 5.1728sec
ASYNC_IO_COMPLETION avg: 32.6180sec
BACKUPBUFFER avg: 0.0144sec
BACKUPIO avg: 0.0113sec
ASYNC_NETWORK_IO avg: 0.0007sec
PAGEIOLATCH_EX avg: 0.0291sec

Most of the other databases data files average around 30ms per write (some of them reaches more than 100ms.

What to do? what to search?

ASYNC_IO_COMPLETION waits occur when a task is waiting for I/Os to finish.

PREEMPTIVE_OS_PIPEOPS occurs when SQL Server runs code in Windows.  For example, if you run xp_cmdshell, SQL Server spawns a command that is controlled by the Operating System.  While the command is running SQL Server shows PREEMPTIVE_OS_PIPEOPS as the wait since it doesn't know what the OS is actually doing.

The combination of these two waits seems like the operating system is busy.  You mentioned that the server is a virtual machine - if SQL Server itself is not super busy, it's quite likely the physical host server may be very busy servicing other VMs.  Ensure your SQL Server VM has a memory and CPU reservation appropriate for its needs.  Also, if running on VMware, ensure the VM uses the PVSCSI disk controller for the drives where SQL Server data and logs reside.


TNS connection forward There are 3 machines as follows:

Machine A - Remote Exadata DB machine
Machine B - Remote machine from which only I can connect to port 1521 of Machine A
Machine C - Local Machine (To run SQL Developer and can't connect machineA directly)
Machine D  - Bastion Server through which I connect to Machine B

Machine C can access service running in Machine B only using SSH tunnelling.

Is there any way I can access the Exadata DB from Machine A using SQL Developer running in Machine C?

I am trying this way:

ssh MachineD -L 15219:MachineB:15220 -L 15220:MachineA:1521

which is not working.

You tried

ssh MachineD -L 15219:MachineB:15220 -L 15220:MachineA:1521

this did not work but you think in the right direction, you want to join two tunnels.

login to MachineD and make a tunnel from your MachineB port 15219 to MachineB port 15220

ssh -L 15219:MachineD:15220 MachineD

So If you now send something to MachineC port 15219 then your ssh session (from MachineC to MachineD) sends it to MachineD port 15220

if you do a

tnsping  '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = MachineC)(PORT = 15219))(CONNECT_DATA = (SERVICE_NAME = ORCL)))'

you will get an error message

TNS-12541: TNS:no listener

or something similar, if you try to connect with SQL Developer.

Because if you now send something to MachineC port 15219 then your ssh session (from MachineC to MachineD) sends it to MachineD port 15220. And on  MachineD port 15220 there is actually nobody listening.

Now in your ssh session on MachineD execute the following command

ssh -L 15220:MachineA:1521 MachineB

Now you have opened an ssh session from MachineC to MachineB and data sent to MachineD port 15220 is read by this second ssh session and sent to MachineA port 1521. So you have two ssh sessions and the second one extends the first one.

But instead of opening one ssh session and in this ssh session opneing the other one you can do this in one command

ssh -L 15219:MachineD:15220 MachineD ssh -L 15220:MachineA:1521 MachineB

by juxtaposing both commands. If after an ssh command follows another comman this command is executed after login.
To avoid an error message like

Pseudo-terminal will not be allocated because stdin is not a terminal.

you can use the -T option for the second `ssh`` command.

ssh -L 15219:MachineD:15220 MachineD ssh -T -L 15220:MachineA:1521 MachineB

If you get some

bind: Address already in use
channel_setup_fwd_listener: cannot listen to port: 15220

messages then change the adresses.

e.g. use  15221 instead of 15220. Of course you must replace it on both posisitions:

 ssh -L 15219:MachineD:15221 MachineD ssh -T -L 15221:MachineA:1521 MachineB

I hope this works. It is possible to configure the ssh daemons such that local forwarding is not allowed.

You cann concatenate an arbitrary number of tunnels .

I cannot test the -W option because I get

SSH-2.0-OpenSSH_5.3

Protocol mismatch.


join for between multiple tables without common fields table E : id, title, datetime
table L : id, place, xpos, ypos
table M : id, title, datetime, contact, agenda
table ER : parentid, childid, parenttype, child type

data in ER : e.id, l.id, e, l -> E object linked to L object
             m.id, l.id, m, l -> M object linked to L object

I want to select all objects from table E and M such that they have a connection to object in L (in table ER) and that object has value of xpos>25 (arbitrary)

select * from L where xpos > 5
left outer join ER on ER.childid=L.id
left outer join E on E.id=ER.parentid
left outer join M on M.id=ER.parentid

I feel I'm missing the basic working of joins and am constructing a wrong query (approach here). Can it be combined in one query? Or do I have to use multiple queries, one for E and one for M to get the results from each of them?

Which make of SQL are you using?  I am most familiar with Microsoft SQL Server. Syntactically, you need to move the WHERE after all of the JOIN statements.  (I appended the tablename L to its references for consistency.)

select * from L
left outer join ER on ER.childid=L.id
left outer join E on E.id=ER.parentid
left outer join M on M.id=ER.parentid
where L.xpos > 5

This is legal syntax.  The next question is what results you want.  I think possibly so, although you did not show a sample of results.  Perhaps you can play with the data a bit to decide what you need.

CREATE table E (id INT, title varchar(50), [datetime] DateTime);
CREATE table L (id INT, place varchar(50), xpos float, ypos float);
CREATE table M (id INT, title varchar(50), [datetime] DateTime, contact varchar(50), agenda varchar(50));
CREATE table ER (parentid INT, childid INT, parenttype INT, childtype INT);

INSERT INTO L VALUES (1, 'Boston',123.234, 23.234);
INSERT INTO L VALUES (3, 'Miami', 456.234, 78.234);
INSERT INTO ER VALUES (100,1, 5, 4);
INSERT INTO ER VALUES (200,2, 5, 4);
INSERT INTO ER VALUES (200,3, 5, 4);
INSERT INTO ER VALUES (100,4, 5, 4);
INSERT INTO E VALUES (100, 'Able','2014/01/02');
INSERT INTO E VALUES (2, 'Baker','2014/01/11');
INSERT INTO M VALUES (100, 'Love', '2014/04/04', 'George', 'None');
INSERT INTO M VALUES (200, 'Fury', '2014/05/05', 'Bernard', 'Something big');

SELECT * FROM L
    LEFT OUTER JOIN ER on ER.childid = L.id
    LEFT OUTER JOIN E on E.id= ER.parentid
    LEFT OUTER JOIN M on M.id= ER.parentid
    WHERE L.xpos > 5

DROP TABLE E
DROP TABLE L
DROP TABLE M
DROP TABLE ER


Cancel Rollback and accept data loss I have a SQL instance (2012 Standard Edition) which consumes all CPU resources because of a rollback-process. The instance has been restarted, so the database is currently In Recovery. I cannot see any SPID in Status ROLLBACK with sp_who2 or sp_whoisactive.

The data within that database is not important, but it's currently wasting all my CPU and slowing down the application.

Is there any chance of cancelling the rollback. I am absolutely willing to completely lose the affected database.

Can you please check the error logs, It should tell you the percentage the recovery process is on.

This would give an idea of how long the recovery would take to complete.


Query over text column takes ages to run I'm not used to querying text columns with a large amount of data as I've always used NVARCHAR(MAX) and VARCHAR(MAX) but this is not my Database that I'm querying.

myTable has 305,000 rows.

myDescriptionField is a text column and allows nulls & has loads of data in it.

When I run the following query, it returns a row instantly.

SELECT TOP 1 userid
FROM myTable
WHERE ISNULL(userID,0) > 0
AND TRIM(SUBSTRING(myDescriptionField,1,1)) is not null

However, when I add this line to the where clause, the query just sits for over a minute (not sure how long it actually takes to finish as I always end up stopping the execution).

AND myDescriptionField NOT LIKE ''

I thought it might be because I'm forced to do a not like as it's a text column and also that it may be where a lot of rows have blanks or null so I tried different combinations of the following :-

AND ISNULL(TRIM(SUBSTRING(myDescriptionField,1,1)),'') <> ''

None of this worked.

So now a quick explanation as to what I need to achieve :-

I just need to find out if at least one row has data in the column myDescriptionField and if so, I can move on to the next table.

Is there a quick way of returning just one row once it's hit one?

Not sure if you added another predicate, or if you changed the existing one, but you could try like:

SELECT TOP 1 userid
FROM myTable
WHERE ISNULL(userID,0) > 0
  AND NULLIF(SUBSTRING(myDescriptionField,1,1),'') is not null;


SQL Server 2016 capped at specific CPU usage I have a situation where my SQL server is capped at a constant CPU usage rate (13%)
and only four cores are being utilized.

There is no resource governors or anything like that in place.

This runs on VMWare environment.
SQL Server 2016
Windows 2016

Standard Edition is limited to 4 sockets or 24 cores


SQL Server 2016 Standard Edition Replication I have a single database running on SQL Server 2016 Standard edition in our primary datacenter. I would like to keep a live copy as close to realtime (1-2 minutes) in our disaster recovery datacenter so when the primary goes down we can redirect the traffic to DR. When the primary is back, changes in DR need to sync back to the primary.

From what I've researched peer-peer isn't available in the Standard edition. I found (and lost) a doc that discussed how to do this in Standard edition. It was limited to one database with one primary and one backup, yet I can't figure out what I searched to find it.

Any suggestions on best practice to accomplish this sync?

Replication is not a DR technology in SQL Server, so you should never consider that for DR purposes. In SQL 2016 Standard Edition, you should look at Basic Availability Groups.

Setting up a Basic AG for this database will provide you with DR capabilities and will allow for failover without breaking the flow of data between your replicas.


Select column name dynamically based on rows from another table in SQL server I want to select column name from another table row value in single query.

CREATE TABLE [dbo].[DynamicFieldsMaster](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [DisplayName] [nvarchar](max) NULL,
    [Type] [nvarchar](max) NULL,
 CONSTRAINT [PK_DynamicFieldsMaster] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Id    Name    DisplayName Type
1 Field1  ID  int
2 Field2  NAME    string
3 Field3  EMAIL   string
4 Field4  ADDRESS textarea

And my Another table is ...

CREATE TABLE [dbo].[DynamicFieldsValue](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NULL,
    [Field1] [int] NULL,
    [Field2] [nvarchar](max) NULL,
    [Field3] [nvarchar](max) NULL,
    [Field4] [nvarchar](max) NULL,
    [Field5] [bit] NULL,
 CONSTRAINT [PK_DynamicFieldsValue] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Id    CustomerID  Field1  Field2      Field3     Field4      Field5
1       1         101     testField2  testField3 testField4   1
2       2         1       tert        erter      terter       1
3       3         7       tertretert  ertert     erterertert  1
4       4         12      ertert      erter      tertr        1
5       5         11      add         jhgjh      jhghjghjghj  1

and i want result like

SELECT *, (SELECT v.Field1(dynamic column name)  FROM DynamicFieldsValue v WHERE CUSTOMERID =1 ) VALUE FROM [dbo].[DynamicFieldsMaster]  DM

For customerid =1 i need to retrive all data from dynamicfieldvalue table

Id  Name    DisplayName Type    VALUE
1   Field1  ID  int             101
2   Field2  NAME    string      testField2
3   Field3  EMAIL   string      testField3
4   Field4  ADDRESS textarea    testField4
5   Field5  GENDER  boolean     1

any one please help me

It's probably not the optimal way to do it and I would probably look at normalizing the database but here's a way to get the data you want.

with cte as (
select 'field1' "name", convert(nvarchar(max),field1) "value" from [dbo].[DynamicFieldsValue] where customerID=1
union
select 'field2' "name", field2 "value" from [dbo].[DynamicFieldsValue] where customerID=1
union
select 'field3' "name", field3 "value" from [dbo].[DynamicFieldsValue] where customerID=1
)
select * from [DynamicFieldsMaster] dm
join cte on dm.name = cte.name


Can you DENY access to a certain schema for a user with dbcreator role? I have a database with two schemas core and dyna.

I am using dacpacs to update the database.
I want the user account to only be able to interact with tables (etc) in the core schema.

This is because the tables (10k+) in the dyna schema are generated dynamically and wont be updated by the dacpac, however the dacpack wants to check every single one to see if it will make an update; and this is killing the deployment performance. (6+ hrs in prod vs 10 min in test)

To be able to deploy dacpacs the user must me in the dbcreator (or sysadmin) role.

We have been using a user with the sysadmin role, for deployment, but we are wondering if:
We can create a user in the dbcreator role, but DENY them access to the dyna schema?

So is it possible to: DENY access to a certain schema for a user with dbcreator role?

DENY access to a certain schema for a user with dbcreator role?

Before I answer this, I want to mention what Daniel eluded in his answer. You do not have a user with dbcreator role, it is a login that is member of dbcreator role.  As dbcreator is a server-level role.

You can have a user denied control (Alter any schema, delete, execute, insert, select update etc) privilege whose mapped login is a member of dbcreator server-level role.

Lets do a demo of your question.

Login with a account with admin privilege. Run this statement.

USE [master]
GO
CREATE LOGIN [testDBcreator] WITH PASSWORD=N'testDBcreator', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [testDBcreator]
GO

CREATE DATABASE [testDBcreator]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'testDBcreator', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\testDBcreator.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'testDBcreator_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\testDBcreator_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

USE [testDBcreator]
GO

CREATE USER [testDBcreator] FOR LOGIN [testDBcreator] WITH DEFAULT_SCHEMA=[dbo]
GO
CREATE SCHEMA schema1
GO
CREATE SCHEMA schema2
GO
CREATE TABLE schema1.table1 (id int)
GO
INSERT INTO schema1.table1
VALUES (1),(2)
GO
CREATE TABLE schema2.table2 (id int)
GO
INSERT INTO schema2.table2
VALUES (3),(4)
GO

USE [testDBcreator]
GO
ALTER ROLE [db_owner] ADD MEMBER [testDBcreator]
GO
DENY CONTROL ON SCHEMA::schema1 TO testDBcreator
GO

Change connection to login with testDBcreator and run this.

USE [testDBcreator]
GO
SELECT * FROM schema1.table1
GO

You will get following error.

  Msg 229, Level 14, State 5, Line 3 The SELECT permission was denied on
  the object 'table1', database 'testDBcreator', schema 'schema1'.

Now run this.

USE [testDBcreator]
GO
SELECT * FROM schema2.table2
GO

You get the result as expected.

I am not sure if you can run your .DACPAC with this login.


How to prevent query execution after exiting the \e editor in psql? Is there an option that can change the "exit external editor -> query runs automatically" behaviour of the psql \e switch ?  (Other than intentionally mangling the query syntax).

Don't add a semicolon (;) and you are fine. I quote the manual about the \e or \edit meta-command here:

The new contents of the query buffer are then re-parsed according to
the normal rules of psql, treating the whole buffer as a single line.
Any complete queries are immediately executed; that is, if the query
buffer contains or ends with a semicolon, everything up to that point
is executed and removed from the query buffer. Whatever remains in the
query buffer is redisplayed. Type semicolon or \g to send it, or
\r to cancel it by clearing the query buffer.

To be clear: only the last statement that is not terminated with a semicolon is not executed. To execute nothing at all, make sure there are no complete statements before the last statement. I.e. no semicolons at all.


Export pump all schemas except Sytem related schemas I have around 111 schemas in one database instance and have to export all the schemas except the system related schemas to a new instance. Is there any way as to exclude system related schemas while using expdp as it would not be easy to write all the schema name in schema paramter of expdp.

As @a_horse_with_no_name mentioned, you can use the exclude parameter to limit the objects to be exported.

It's very simple to use and it supports in/ like... operators.

Usage example:

expdp username/password directory=datapump dumpfile=x.dmp logfile=y.log full=y exclude=schema:"in ('SYS', 'SYSTEM', ..., ...)"


To EAV or not to EAV? I am working on an application which records data from different forms. New forms can (and will be) constructed dynamically by an admin. I am wondering what would be the correct way to save this data in a database? My first guess would be to use EVA, but considering all the bad publicity I am a bit hesitant.

Let's say I have two forms:

#form 1
description = TextField()
score = IntegerField()

#form 2
feedback = TextField()
NOR_score = IntegerField()

There is no way of predicting the number of possible attributes. I am playing around with two ideas, but maybe you'll be able to suggest a much better approach.

Idea I:

unique_id | entity | attribute   | value
1         | form 1 | description | test123
2         | form 1 | score       | 90
3         | form 2 | feedback    | blabla
4         | form 2 | NOR_score   | 5

Idea II:

unique_id | entity | value
1         | form 1 | {"description": "test123", "score":"90"}
2         | form 2 | {"feedback": "blabla", "NOR_score":"5"}

Thanks in advance for any suggestions.

Your example (idea II) looks like it shows how to store the response to the forms. Would you store the form descriptions (created by your admin) in the same fasion, as it:

id | name | value
-----------------------------------------------------------
1  | form1|{"question1":"description", "question2":"score"}

If you go this route, it means you have to store the full description of a form as a very long text string. You won't be able to reuse questions between forms. You won't be able to (easily) query to find which form had a specific question. It will be possible to have more interesting types of form questions such as multiple-choice, but it won't be pretty (and you won't be able to re-use option-sets).

I'm not sure why you think you need an EAV solution. A very simple schema could look like this:

form
----
  id
  name

question
--------
  id
  form_id
  question_text

question_answer
---------------
  id
  user_id
  question_id
  answer_value

Data:

form
----
id | name
-----------
1  | form1

question
--------
id | form_id | querstion_text
-----------------------------
1  | 1       | description
2  | 1       | score

answer
------
id | question_id | answer_text | user_id
----------------------------------------
1  | 1           | test123     | 1
1  | 2           | 90          | 1

Would this not work?


Distinct on two columns, how to rid from 'duplicates' I have a table with messages, that have 'msg_from' and 'msg_to' fields.

when I want to select all current chats and last message from each of them I use the following query:

select distinct on (msg_from, msg_to) array[msg_from, msg_to]
as participants from mockdata_messages
order by msg_from, msg_to DESC

example: https://www.db-fiddle.com/f/n1Bttz4i9Cd5RVA9qCpNkH/1

This works fine in Postgres 12

The problem is that I get 'duplicates' in response. I mean, 'participants' values like: [2, 5] and [5, 2], which obviously refers to the same chat.

I got exactly simillar result when I tried to use 'group by' with two columns.
How can I get arround this problem? What approaches are well to use for storing chat messages in DB?

in this simple example you can use the least and greatest functions to make the distinct on condition match in both cases.

select distinct on ( least(msg_from, msg_to),greatest(msg_from, msg_to))
array[msg_from, msg_to] as participants from mockdata_messages
order by least(msg_from, msg_to),greatest(msg_from, msg_to)


What is a good unique identifier for customer and payment details for this tourism company? Given a tourism company, Air T&T, has  customers.  When a customer checks in to an AirTnT facility (i.e. house-share), AirTnT records the customer's first and last names, address, date of birth, current mobile number, one unique identifier for identity verification, one unique identifier for payment details, and emergency contact number.

I was thinking social security number for identity verification and bank account number for payment details? What are the pros and cons of each-especially for payment details? I know credit card number is bad because of the security threats?

Your question is a business one, not a database one. The 'unique identifier', or your key, should be the key that the business uses to distinguish customers. Some may use a user-name, others may use Email, some may use SSN, some may use retina scan.
The database schema is a MODEL of the business reality, therefore, it should follow the keys that are used in the real world.

It won't help you much to assign a surrogate key, like IDENTITY or GUID, as you still won't be able to distinguish one customer from another, as you can't extract that property from the customer itself, and name is typically not enough. What would you do if a customer walks into the office and there are 4 other customers with the same name?

  Let's say you have 2 customers named John Doe. You insert them into
  the table and assign them surrogate primary keys with ID 34 for one,
  and 237 the other. A customer walks into the store. Says 'hi, I'm John
  Doe and I'm here to buy an item'. You type 'John Doe', and into the
  screen to pull up his account, and you see 2 options - John Doe 34,
  and John Doe 237. You look at the customer and ask "Are you John Doe
  34 or 237?". The customer thinks you're crazy and walks away... The ID
  doesn't identify the ENTITY, it is a relic of thought from 50 years
  ago when magnetic tapes had address pointers...

The question mentions that the data collected is "customer's first and last names, address, date of birth, current mobile number".
If that is all you have to distinguish customers in the 'real world', that is your key.
Perhaps it is just first and last name, perhaps first and last name + address, perhaps just mobile number...

As for payment, again, the question does not provide enough information for you to give an answer. What payment methods are available? Cash? Checks? Credit Cards? Wire Transfers only? Can the customer pay in multiple payments and multiple payment methods?
Again - whatever the BUSINESS uses to distinguish payments, is what your key will be.

HTH


MySQL Cluster RAM requirement From MySQL 5.1, the data doesn't need to be entirely in memory anymore.

I've read that the indexed columns (I think the entire index structure) must still be in memory (MySQL High Availability, 2010, pg. 533, "MySQL Cluster keeps all indexed columns in main memory").

In light of this, what happens if there's NOT enough memory (ie, a huge database (> 100GB, or 1TB), running on servers with low memory configs (2 data nodes, each with 1GB of RAM, for instance))?

MySQL Cluster supports storing non-indexed columns on disk-only with an LRU cache of recently accessed data.  However indexed columns are always held in-memory.

MySQL Cluster preallocates all memory, according to the DataMemory and IndexMemory parameters.  It will not ask the underlying OS for more memory dynamically.

This means that you need to have configured enough memory across your cluster to hold all indexed columns in memory.  If your dataset is large enough that the indexed columns are larger than the available cluster memory, then you cannot load that dataset into the cluster.  At some point you will run out of space, and your inserting transactions will be aborted.

When configuring DataMemory and IndexMemory, it is best to limit yourself to somewhat less than the physical memory in each system.  Some physical memory should be reserved for the OS and other processes.

Theoretically MySQL Cluster can be configured so that it uses virtual memory via a swap device (e.g. more than physical memory), but as the other answers state, this is not a designed-for use case.  Having in-memory structures swapped to disk is usually sub-optimal as in-memory random access patterns result in random access to the disk, resulting in swap thrashing and slowdown across the system.  With MySQL Cluster, the most likely outcome is heartbeat failure and cluster failure due to a swapping data node not responding to signals quickly enough.

To efficiently support larger-than-aggregate-memory indexes, MySQL Cluster would need to support on-disk index formats (perhaps a B tree etc) with caching and access patterns aligned with disk access properties.


BCP code to extract a large amount of data from MS SQL Server I have been using Python to perform a SQL query and retrieve data from the previous date every day since June and load it into a Postgres database which is local. Each date has about 2 million rows and 75 columns or so, so it is fairly large and it takes a while to pull.

Can someone give me an example bcp code which will generate a CSV (comma) file? How much data would I be able to pull at once or can I loop through each calendar date in the SELECT statement? I am hoping this will be a faster way to kind of replicate the tables I am interested in.

My Python code essentially boils down to SELECT * FROM table_name WHERE report_date = {}. The report date is typically yesterday.

Here is my final working result if anyone else every stumbles upon this. The subprocess.run executes the string which contains the BCP query. In the code below, there are a few variables such as the table_name, database name, and query date since I am looping through different tables and databases on different dates.

path = 'C:/raw/'
filename = '{}_{}.csv'.format(database, select_database(database)[table_int])
query = '"SELECT * FROM {}.dbo.{} WHERE row_date = \'{}\'"'.format(database, select_database(database)[table_int], query_date)
command = 'BCP {} queryout \"{}\" -t, -c -S "server_name" -T'.format(query, os.path.join(path, filename))
table_name = 'raw_{}_{}'.format(select_database(database)[table_int], database)
logger.info(command)
subprocess.run(command)

There are plenty of examples on the internet - search for "bcp comma delimited"  Also, refer to the BCP syntax here.

This should get you started:

bcp <YourDatabase>.<YourSchema>.<YourTable> out C:\OutData.dat -o C:\OutData.log -S . -T -c -a 65535' -t, -b100000

BCP - the command
the fully qualified object you're exporting from Sql Server (this could also be a view)
out - the path and file name of the exported data
-o - the output log information about number of rows extracted
-S - the sql server instance name (default here)
-T - use a trusted connection
-c - export the data in character format
-a - packet size of 65535
-t - your column delimiter (comma in this case)
-b - batch size (for performance)


How do I synchronize rows in tables in remote databases? I'm actually developing a synchronization tool in vb.net, I have two database that have on each table records field GUID, this field help to have the same PK on both database. On each record there is also a field called lastUpdated, this field have a milliseconds value, so prevent two user to update the record in the same time. My question is, how I can compare the records of the same table from different db? For example:

ONLINE_DATABASE

TABLE_1

| ID |   GUID                             | NAME  |    LASTUPDATED              |
| 5  | 054ba092-b476-47ed-810b-32868cc95fb| John  | 06-01-2016 17:01:12.472438  |

CLIENT_DATABASE

TABLE_1

| ID |   GUID                             | NAME  |    LASTUPDATED              |
| 9  | 054ba092-b476-47ed-810b-32868cc95fb| Jack  | 06-01-2016 18:01:12.472438  |

How you can see I've update the record from client application, so I need to apply the same change to online database. Now I've a thousand records to check in about ten tables. So my question is, how I can made a system that do this? Actually I tough to read each row with a MySqlCommand reader but I also think that this procedure is slow... Suggest?

NOTICE THAT: the table have the same name in both db

If the GUID is the absolute truth about what is supposed to match, then the GUID must be the PRIMARY KEY.  Get rid of id.

If you want semi-automated "conflict resolution based on time", you want MySQL's NDB Cluster.


Counting unique records across 2 columns and computing a score based on boolean values I have a table that looks like this in Google Big Query:

custID  InteractionDate Purchased?  Sales
1       20150312           F         0
1       20150312           T         200
1       20150314           T         150
2       20150221           T         400
2       20150221           F         0
2       20150221           T         120

..that I need to transform to the following structure:

 custID InteractionDate Success Sales
 1     20150312          0.5    200
 1     20150314           1     150
 2     20150221         0.66    520

..where in this second table the success column is defined as

count(purchase=Yes)/(count(purchase=Yes) + count(purchase=No)),

and sales is just the sum.

I'm quite new to BQ and no-SQL languages. I simply don't know how to do this, I've searched and seen examples that suggest using partition over but I simply haven't seen any example that does just this. Thanks a lot in advance.

This would be very simple in SQL. As far as I can read the BigQuery Query reference, it supports all (GROUP BY clause, COUNT() and SUM() functions, CASE expressions):

SELECT
    custID, InteractionDate,
    1.0 * COUNT(CASE WHEN Purchased = 'T' THEN 1 END) / COUNT(*) AS Success,
    SUM(Sales) AS Sales
FROM
    tableName
GROUP BY
    custID, InteractionDate ;


SMSS will start for one user, but not another? We have a SQL Server 2012 and SMSS 2012 installed.  The C:\ recently ran out of space due to a misconfiguration, and now we have 1 user who cannot start SMSS they receive the error:

"Cannot find one or more components. Please reinstall application"

However when I login with my domain admin account, I can start SMSS perfectly fine.  We are both in the server Administrator group.  I have also "repaired" the SQL Server tools (which includes the SMSS app) on the box with no change in behavior.

Is there a log file somewhere I can check to see why SMSS will not start for this one user?

Perhaps something in their documents\user data profile got corrupted. If you've fixed the space issue, then I suggest running a repair on the application (or installing a newer version - you can manage SQL Server 2012 with SSMS 2014 or newer, and it's free - installation instructions in this answer). However it may still end up being hosed because of that user's profile - if so, as a next step, you could back up their files and re-create their profile (though I do concede that this will force you to lose a lot of their other settings etc).


The backup or restore was aborted - what could it be? use master
go
ALTER DATABASE Apcore SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE Apcore
FROM DISK=N'C:\Backups\QG-V-SQL-TS$AIFS_DEVELOPMENT_APCore_FULL_20180909_230034.bak'
WITH  FILE = 1,
MOVE N'APCoreDataPrimary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.DEVELOPMENT\MSSQL\DATA\APCoreDataPrimary.mdf',
MOVE N'APCoreData1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.DEVELOPMENT\MSSQL\DATA\APCoreData1.ndf',
MOVE N'APCoreData_nonclusteredIndexes' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.DEVELOPMENT\MSSQL\DATA\APCoreData_nonclusteredIndexes.ndf',
MOVE N'APCoreLog_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.DEVELOPMENT\MSSQL\DATA\APCoreLog_log.ldf',
recovery,  NOUNLOAD,  REPLACE,  STATS = 1

While restoring a database on a developer's machine I got the above error.

The way I have done it is:

First let me say that I did not worry about - permissions, indexes, triggers, synonyms, etc.
I copied the backup file (less than 1 GB) from live to her local machine
did the restore filelestonly which is ok
I had to set the database into single_user mode because there are some services attempting to use it at every second
prepared the script above, and when running it and got that error message.

what could it be?

Most likely you have defined a command timeout when you connected to your server. Perhaps without thinking about it. Anyhow, the client (SSMS) will cancel the query after the specified amount of time and return that weird looking error of -2 (which wasn't thrown by the server, it is a client generates message). I tested this by connecting with the "Execution time-out" of 1 as in the picture below and then executed the SQL below.

SELECT 'before'
WAITFOR DELAY '00:00:05'
SELECT 'after'

Zero (0) means indefinitely.


Is there any way to use different InnoDB settings for different databases on the same server? From what i know, this is not possible but i'm surprised its not highly requested feature. I'd love to be able to have one db using InnoDB in ACID compliant mode and another db on the same server used for non-critical data that uses InnoDB with more relaxed settings related to disk writes.

Is there a way to achieve this setup?

YOUR QUESTION
The reason there is no way to achieve this has to do with the InnoDB Architecture

There are three(3) things in this which makes different flushing behavior per database impossible

SINGLE BUFFER POOL
SINGLE LOG BUFFER
FUZZY CHECKPOINTING

These three(3) things work together. The log buffer flushes changes from the buffer pool into system tablespace files but there is no way to control how much or how little to flush from the log buffer and buffer pool (in more than one direction).
Look at a more sophisticated RDBMS : Oracle RAC. Scaling out Oracle RAC simply creates a log buffer per RAC Node to distribute writes to one or more servers that have the data files. You could probably unleash CHECKPOINT commands to flush the Log Buffer at a RAC Node. If you could, this would still not be wise (and your scenario impossible) because you cannot control which blocks are written to which RAC Node. Why? Even in a distributed architecture like Oracle RAC, you still have a single Data Dictionary. This is much more so the case with InnoDB when it comes to controlling changes and flushing of dirty pages.
Flushing databases differently puts the InnoDB Storage Engine at risk of losing all data integrity altogether.
ALTERNATIVE SOLUTION #1
Create another MySQL instance on port 3307 (or a different number other than 3306) on the same server. That second instance would have its own Log Buffer and its own Checkpointing. Using a separate my.cnf for the second instance, configure innodb_flush_log_at_trx_commit at your discretion since it will be independent of the first instance's flushing behavior.
ALTERNATIVE SOLUTION #2
Take the source code and implement multiples log buffers (each with a dedicated buffer pool), including a mechanism for tweeking the flushing of commits per log buffer (You'll be a millionaire in 2 years.
Give it a Try !!!


SP for Restoring Database and performing other tasks on it I am creating a stored procedure for our QA dept that will do the following:
Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for replication (three merge publications), and then initialize the snapshots of these three publications.

Don't worry about the replication portion of this, I have it handled so far. What I am wondering is:

What will be the most effective/eficient method of restoring the database? Will it be restoring from a backup (the 2 DB's that they will be restoring from are 1.5gb and 599mb) or would it be another way? If it is not from the .bak files, what other way of restoring do you think would be appropriate?

:: This needs to happen, strictly, with TSQL ::

Thank you,
Wes

Here is the script I used

The only way to really backup a database is to create a backup of the database (using BACKUP DATABASE). All the other methods (scripting database schema and content, copying files around, OS file snapshots) are workarounds, not database backups.

So, as I see it, the best way to have a database copied from one place to another is to restore it from a backup (RESTORE DATABASE). There's no other effective and more efficient way as I see it. That's my 2 cents, anyway.

Now, in order to do all that preparation, I'd make different stored procedures (using dynamic sql to build proper names, paths..etc) that will:

restore databases from a specific location;
prepare specific permissions -> maybe different users..etc;
prepare replication objects;

PS: if it's a possibility, make sure to enable backup compression, as it will really help with size and speed.

PS2: taking into considerations your backup file sizes I wouldn't worry too much about the duration of the operation. Should be fast even on a usual developer box.


could not connect to server: can't connect postgres on the server to postico on my local when I connect postgres on the server to postico on my local I get this error
could not connect to server: Operation timed out
Is the server running on host "http://159.65.65.203/" (92.242.140.2) and accepting
TCP/IP connections on port 5432?

I could not find anywhere postgresql.conf and pg_hba.conf files, so i created the files, as you see in the below image

I found this explanation but still does not solve my issue
Ubuntu does not use the standard pg_ctl for postgreSQL. Instead, it
uses pg_ctlcluster.
That in turn controls the different PostgreSQL clusters. When you do
an install of a new cluster, pg_ctlcluster is smart enough to put
postgresql.conf & pg_hba.conf into separate dirs.
So to be specific, /usr/lib/tmpfiles.d/postgresql.conf is just a tmp
file that you can ingnore, /etc/postgresql/9.4/main/postgresql.conf is
the original version for the 9.4 cluster and
/var/lib/postgresql/9.4/main/postgresql.auto.conf is the actual, live
version of the 9.4 cluster that you need to change to affect the 9.4
cluster. Likewise for the pg_hba.conf.
reference https://www.postgresql.org/message-
id/CANu8FiygR5CdeqRTiE0YM+BQk6aK+qZyw2frH_FWV27J1exong@mail.gmail.com

here how i configured postgresql.conf
port=5432
listen_addresses='*'

here how i configured pg_hba.conf
 host    all             all              0.0.0.0/0                       md5
 host    all             all              ::/0                            md5

when i run
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program
name
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      -
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN
29761/postgres
tcp6       0      0 :::22                   :::*                    LISTEN      -

Clearly 5432 is not open for all connections.
when i make an api call using postman seems to work.
I use mac currently firewall not on, i have my server on digitalocean use Ubuntu 16.04.6 (LTS) x64.
This is the path of postgres files
postgres@borroupapi:~/9.5/main$

when i run  find / -name postgresql.conf i get this
/usr/lib/tmpfiles.d/postgresql.conf

but i cant find the path as logged in postgres user

fixed the issue in postgres 9.5 postgresql.conf and pg_hba.conf files are located in
/etc/postgresql/9.5/main/

not in
/var/lib/postgresql/9.5/main/

you have to login as server root user and run each one of the command and that will give where are the files
find / -name postgresql.conf

find / -name pg_hba.conf

in my instance when i ran the command i got this
/var/lib/postgresql/9.5/main/postgresql.conf
/etc/postgresql/9.5/main/postgresql.conf
/usr/lib/tmpfiles.d/postgresql.conf


SQL Server 2008 R2 - Unable to delete Login I've been using this site for a while and this is the first time I've had to ask a question, but just wanted to say "Thank You" to everyone who has helped me in the past (without knowing they did so).

First things first.. I am trying to delete a user. To locate the user, I execute the following:

USE [MASTER]
SELECT SUSER_SID('henryhr')

Which returns:

0x01050000000000051500000084BC6CEC253AF11EEEFBBA6A240F0034

When I try this:

SELECT *
FROM sys.database_principals
WHERE sid = 0x01050000000000051500000084BC6CEC253AF11EEEFBBA6A240F0034

It returns 0 rows. If I try:

USE [MASTER]
DROP LOGIN henryhr

I get:

  Msg 15151, Level 16, State 1, Line 1
  Cannot drop the login 'henryhr', because it does not exist or you do not have permission.

I have tried creating another user and going through the same process and it works just fine. Any idea how to delete this?

Short intro

To connect to the server you have to have a login to access the server. To access a database you have to be a user in that database. All Logins are member of the server role public which can access the master database.

So to find users to drop within a database you query sys.database_principals and to find logins sys.server_principals. When you drop a login it's not removed from the user list of the databases and the SID is there until you drop the user.

So henryhr exists as a user in the master database but not as a login on the server so try use master; drop user henryhr; to drop the user henryhr from the master database


Shrink database after removing extra data We have a need to fit a database in 4GB in order to use SQL Server Express Edition. I started from a 7GB database, and found a lot of unneeded records, and deleted them. After shrinking, the database size is 4.6GB, and 748 MB is free (according to database properties).

However, when I execute exec sp_spaceused, I have interesting results:

DatabaseName    Database_size   unallocation space
xxxxxx          4726.50 MB      765.42 MB

Reserved        Data          index_size      unused
3899472 KB      1608776 KB    1448400 KB      842296 KB

Any ideas, how can I release at least some of this unused space?
Also I know table, which occupied it.

Update: is it worth to trying to rebuild table indexes?

ALTER INDEX ALL ON Production.Product REBUILD

After index recreation, using this command:

ALTER INDEX ALL ON Production.Product REBUILD

and than I run SHRINK command again - end up with 2.5G database.


SSRS Report by URL with parameter-value AND retain full top menu TL;DR: How can I link to an SSRS (2016) report by URL, with a parameter in the query-string, AND retain (show) the FULL MENU bar (the top "SSRS chrome" that has the Favorites, Browse, Settings, breadcrumb navigation, etc.).

Why: I'm phasing out an old 2008R2 instance by putting a clickable link on the old RDLs to new versions of similar reports in the 2016 instance. When the user has selected/filled-in a parameter value, I obviously want to pass that forward to the new report when possible. I've followed the dox and built the URL like so:

http://ssrsInstance/ReportServer/Pages/ReportViewer.aspx?%2fFolder+Name%2fOrder+Line+Details&rs%3aCommand=Render&OrderNo=123456

or, the non-encoded more readable version:

http://ssrsInstance/ReportServer/Pages/ReportViewer.aspx?/Folder Name/Order Line Details&rs:Command=Render&OrderNo=123456

This works insofar as it shows the report with the parameter values filled-in, but it does NOT show the top level SSRS menu section, i.e. the branding (logo/title), the Settings/Download/Help buttons in the upper-right, and the Favorites & Browse links in the upper left. I WANT this stuff to show up, because it conveys to them that they're in the new reporting system and they can navigate around and find other reports.

What else I've tried: just using the 'standard' URL of the report (as in browsing to it from the menu in the new instance), and just appending the parameter to the end, like so:

http://ssrsInstance/reports/report/Folder%20Name/Order%20Line%20Details&OrderNo=123456

or, for the non-encoded version:

http://ssrsInstance/reports/report/Folder Name/Order Line Details&OrderNo=123456

That gives an "invalid item path" error:

The path of the item '/Folder Name/Order Line Details&OrderNo=123456' is not valid. The full path must be less than 260 characters long; other restrictions apply. If the report server is in native mode, the path must start with slash. (rsInvalidItemPath)

And obviously the other (less favorable) option is to simply use that URL without the parameter, but that forces the user to have to re-do work (especially in the case of more complex parameter sets and multi-valued params), which would suck.

Hope there is a way! Thanks all. :)

The sages of #sqlcommunity (namely @JeffRush) have informed me that this is not possible. It has to do with the two different interfaces --

  "..that is something that only exists when you are accessing
  Reports interface. URL access hits ReportServer [interface]." (emphasis mine)

Hope that helps future readers.


Applying GROUP BY when using multiple CASE WHEN select
 e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id
,CASE WHEN w_id = 1 THEN fee END as value1
,CASE WHEN w_id = 2 THEN fee END as value2
,CASE WHEN w_id not in ('1','2') THEN fee END as value3
from table1

The above code gives me a result like:

e_id | new_w_id | value1 | value2 | value3
 1   |   1      |   100  | null   | null
 1   |   2      | null   |   150  | null
 1   |   0      | null   | null   |    50
 1   |   0      | null   | null   |   150

but I'd like to group by e_id and get result like:

e_id | new_w_id | value1 | value2 | value3
 1   |   1      |   100  | null   | null
 1   |   2      | null   |   150  | null
 1   |   0      | null   | null   |   200

I've tried

group by e_id

and

group by e_id, CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END

but neither of these are grouping my data as I'd like. Is there a solution to this?

I think you just need to aggregate your value columns

create table table1(e_id int, w_id int, fee int)

insert into table1
values(1,1,100),(1,2,150),(1,3,50),(1,4,150),
(2,1,100),(2,1,150),(2,2,50),(2,4,150)

select
 e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END as new_w_id
,SUM(CASE WHEN w_id = 1 THEN fee END) as value1
,SUM(CASE WHEN w_id = 2 THEN fee END) as value2
,SUM(CASE WHEN w_id not in ('1','2') THEN fee END) as value3
from table1
group by e_id
,CASE WHEN w_id in ('1','2') THEN w_id ELSE 0 END


How to compare two last updated entities from two different table Postgresql? I have 2 tables named as price and price_history. price_history table is automatically filled by triggers from price table just before updating the price table. I would like see the change at the new table named as change and fill it by triggers again just before the price table will be updated.
Function:
create or Replace function test()
returns trigger
as
$$
begin

update change set change_= SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1 - SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1 ;
return new;

end;
$$
language plpgsql;

Trigger:
CREATE TRIGGER testtrig
BEFORE UPDATE
ON price
FOR EACH ROW
EXECUTE PROCEDURE test();

I get error while using the querry --> SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1 - SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1 ;

Can you help me please?
Thanks

You are missing some parenthesis around your select queries.
But i don't understand completely what you try to achieve, i think you should at least also transfer the produdct_id, because for now you get a price from a unknown product.
create or Replace function test() returns trigger as $$ begin

update change
set change_= (SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1) - (SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1) ;
return new;

end; $$ language plpgsql;

CREATE TRIGGER testtrig BEFORE UPDATE ON price
FOR EACH ROW
  EXECUTE PROCEDURE test();


mysql run multiple selects on the same table at the same time We have 25 queries, which are simple select statements, that we need to run concurrently on the same table, to test load.

How do I run multiple select statements at the exact same time?
I have tried a bash script, but it runs the selects in order, and not at the same time

The workload needs to open sessions in parallel, instead of executing all queries serially from one bash script.

There is a MySQL tool aimed at just this, please see mysqlslap.

https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html


Oracle Character set identification In the given export(Normal Export) log file i see following:

Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

Is this multi-byte or single byte database char-set.

Can you please explain me a bit

What is difference between multi-byte and single byte character set?
Can we import multibyte into Single byte and vice versa?
How can we identify weather a character set is multi-byte or single byte by looking into its name?

Please provide any other useful information on this topic?

Ok, this basically means:

The National character set of the database you exported from was
AL16UTF16. This is a multi-byte character set and, being the
national character set, would have been used for any NCHAR,
NVARCHAR and NCLOB columns (the N in the name stands for "national").
The "normal" character set of the database you exported from was
WE8ISO8859P1. This is an 8-bit character set (the Western European
ISO variant) and would have been used for any CHAR, VARCHAR and
CLOB columns.

You can see which character sets are being used by a particular database by querying the NLS_DATABASE_PARAMETERS data dictionary view. Your export would have been done from a database with the parameter NLS_CHARACTERSET set to WE8ISO8859P1, and the NLS_NCHAR_CHARACTERSET parameter set to AL16UTF16.

The difference between multi-byte and single-byte character sets is explained in the name. Single-byte characters are stored in a single byte, i.e. 8 bits. Multi-byte characters are stored in one or more (multiple) bytes.

As far as importing multi-byte into single-byte (and vice versa) is concerned, it really depends on the character encodings and whether or not the source character actually exists in the target character set. By way of example, a Japanese multi-byte character is not going to exist in the US7ASCII character set, and you'd end up with a database full of ? signs.

I strongly suggest you read the Oracle Globalization Support Guide, as this goes into the topic of internationalisation in great detail.


Remove Characters & Sum Numeric Values I am using mysql 5.6 and want to remove all characters from json output of db column and after that required sum of numeric values

Input:

  {"across_back":"0","bustd":"0","waiscvvt":"50","hisdp":"50","neckdf":"0","thiddgh":"0"}
  {"shoulder":"0","bustfd":"10","waisdsdt":"40","sdhip":"10","nsdfeck":"0","thigfsdh":"0"}
  {"back":"0","bust":"40","waist":"35","sdfhip":"22","necsdk":"10","thigh":"0"}

Output:

0+0+0+50+0+0 = 50
0+10+40+10+0+0 = 60
0+40+35+22+10+0 107

drop table if exists test;
create table if not exists test(val varchar(255));

drop table if exists log;
create table log(id int auto_increment primary key,
                 val varchar(255),
                 summ int );

insert into test
values
('{"across_back":"0","bustd":"0","waiscvvt":"50","hisdp":"50","neckdf":"0","thiddgh":"0"} '),
('{"shoulder":"0","bustfd":"10","waisdsdt":"40","sdhip":"10","nsdfeck":"0","thigfsdh":"0"} '),
('{"back":"0","bust":"40","waist":"35","sdfhip":"22","necsdk":"10","thigh":"0"}');

drop function if exists my_sum;

DELIMITER @@;

create function my_sum(s text)
returns bigint deterministic
begin
set @sum := 0;
while locate('"', s) do
-- debug 1, remove after debugging
    insert into log(val, summ) select s, @sum;
    set @sum := @sum + substring_index(substring_index(s, '"', 4), '"', -1);
-- debug 2, remove after debugging
    insert into log(val, summ) select s, @sum;
    set s = substring(s FROM 2 + length(substring_index(s, '"', 4)));
-- debug 3, remove after debugging
    insert into log(val, summ) select s, @sum;
end while;
return @sum;
end;
@@;

DELIMITER ;

select val, my_sum(val) from test;

drop table if exists test;
drop function if exists my_sum;

select * from log order by id;
drop table if exists log;


Insert dummy data based on data time found in column while doing data collection, I've come to realize that some of the data are missing and could not be re-collected due to some issue with the program. One of the suggestions given was to replace the missing data with dummy data. An example is given below to better explain my issue:

the data is intended to contain data from 00:00hrs till 00:00 the next day. However, due to the shortcomings of the program, the data could not be retrieved. Is there a way too use the time in the data column to insert in dummy data? Using the data in the red box as an example, the dummy data would fill up from timings, 00:00hrs - 05:00 and 14:30 - 00:00 on the 15 of Aug.

I'm thinking of using CTE to complete the task, however, I'm unsure of how to do it.

WITH RECURSIVE
cte AS ( SELECT MIN(datetimefield) datetimevalue -- or some literal value
         FROM datatable
       UNION ALL
         SELECT datetimevalue + INTERVAL 30 MINUTE
         FROM cte
         WHERE datetimevalue < ( SELECT MAX(datetimefield) -- or some literal value
                                  FROM datatable )
       )
INSERT INTO datatable (datetimefield, valuefield)
SELECT datetimevalue, 'dummy value'
FROM cte
WHERE NOT EXISTS ( SELECT NULL
                   FROM datatable
                   WHERE cte.datetimevalue = datatable.datetimefield )

Of course you must adjust the syntax to your DBMS dialect.


Safe values for InnoDB variables when using FULLTEXT searches I'm using Maria DB, version 10.2.22, where one database column uses FULLTEXT for a broad document searching. However, I've ran into a "Table handler out of memory" on some searches. The table itself is only 4.4 GB. I've read on stackoverflow, that changing some of the InnoDB variables such as:
•   innodb_buffer_pool_size
•   innodb_ft_result_cache_limit
from their default value to say 4 GB could potential solve to the memory issue. My question is three parts, I suppose.

Are there any other variables I should consider changing.

•   innodb_buffer_pool_instances
•   innodb_ft_cache_size
•   innodb_ft_total_cache_size

Because this DB is fairly critical to run, after running the command lines to change the variables would I need to stop and start the MariaDB service to fetch these changes?

If, restarting MariaDB services is needed, can anyone point me to a guide as far as how to safely change these variables?

innodb_buffer_pool_size -- This should be set to about 70% of RAM.  But, if the other settings encroach too much into this, then lower it.  Swapping is bad.
innodb_buffer_pool_instances -- Simply set it to innodb_buffer_pool_size / 1G -- rounded down, but at least 1
innodb_ft_cache_size -- big enough for at least one row of text being indexed via FULLTEXT.  Bigger if practical, but don't squeeze out the buffer_pool
innodb_ft_total_cache_size -- If you are [re]building FULLTEXT in only one thread at a time, this is not relevant.
innodb_ft_result_cache_limit -- This is relevant only to SELECTs that involve a FULLTEXT index.  The max is 4G, but don't set it that large unless you have lots of RAM.  Start with, say, 1% of RAM; if you get an error, then raise it.

The settings are not dynamic, so change the config file and restart mysqld.
How much RAM do you have?  What is a typical and the max size of the column(s) in the fulltext index?


Best approach to run a simple query against all users So, I could easily do this in SQL Server. I created a cursor, but to be even more simple, I just ran SP_MSFOREACHDB with a simple query:

select top 1 * from Database..Table order by Date desc

I created a global temp table, and populated it with the data of the select.

But, How can I do this on oracle?

I tried with cursors, union, and creating some loops but as a SQL Server DBA, I'm pretty bad with PL-SQL.

So, How can I run a simple select over all users? Cursor? Loops? create all selects and use union ?

Thanks.

select * from dba_users

You need the DBA role or similar access to select from this table.
In Oracle 12 the columns available are

USERNAME    VARCHAR2
USER_ID NUMBER
PASSWORD    VARCHAR2
ACCOUNT_STATUS  VARCHAR2
LOCK_DATE   DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE  VARCHAR2
TEMPORARY_TABLESPACE    VARCHAR2
CREATED DATE
PROFILE VARCHAR2
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2
EXTERNAL_NAME   VARCHAR2
PASSWORD_VERSIONS   VARCHAR2
EDITIONS_ENABLED    VARCHAR2
AUTHENTICATION_TYPE VARCHAR2
PROXY_ONLY_CONNECT  VARCHAR2
COMMON  VARCHAR2
LAST_LOGIN  TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED   VARCHAR2


Optimal design for 90mm row clickstream MySQL Question
Which optimizations should I employ to make this DB manageable?  I've read a lot of posts about optimizing big dbs, but I'm pretty new so it's difficult to determine which techniques are suitable for my setup.
DB Info
I loaded about 90 mil rows from flatfile csvs into a mysql database (aws RDS, if that matters).  There are 60 columns and each row is a click on a website.
Datatypes: All datatypes are text because of limitations with the mysqlimport utility (specifically, its treatment of null vs. 0 values in numerical fields, which i could be wrong about)
Each column falls into one of these categories:

Visit info: time, referrer, etc. Many pages log millions of visits (front page, for ex.), some log only a few.

User info:  city/state, etc.  Users log anywhere from 1 to thousands of visits, with the distribution skewing right.

Page info: url, content flags, etc.

IO - The database only has 4 users, so will not have a lot of i/o, mostly just queries to populate dataframes in our python data analysis environment.
indexes - none besides the default generated index.  Candidates for indexing are user ID and page view timestamp (potentially a multi column index?)
Options
I welcome any other ideas, but this is what I've come up w/ with some searching.  Interested to hear which steps are worthwhile and which aren't.

right-size the datatype of each field -- does this offer performance boost? most fields are text or boolean flags.

break table into at least 2 others, e.g., visit info and page info.

Keep it in one table and partition it. From what I've read, I think partitioning on the most frequently queried field is the optimal choice.

OPTIMIZE TABLE is almost always not worth doing.

INDEXes are your friend -- they can (in many cases) make queries run orders of magnitude faster.  Provide a Slow query, plus SHOW CREATE TABLE, and we can help you.  Or study my cookbook.  As that says, picking the 'right' index (perhaps a composite index) requires that you first have some clues of the SELECTs you will be doing.

"Rightsizing" datatypes is a good idea, especially when first creating the tables.  Smaller --> more cacheable --> less I/O --> faster.

Two tables (visit info and page info)?  If they are 1:1, usually 1 table is 'right'; if they are 1:many, then two tables is a must.


How to Update same table on deletion in MYSQL? In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.

Following is query I used to create table:

CREATE TABLE IF NOT EXISTS `Employee` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `designation` varchar(128) NOT NULL,
  `MSSN` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`SSN`),
  CONSTRAINT `FK_Manager_Employee`  FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And I could successfully create my table:

mysql> DESCRIBE Employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| SSN         | varchar(64)  | NO   | PRI | NULL    |       |
| name        | varchar(64)  | YES  |     | NULL    |       |
| designation | varchar(128) | NO   |     | NULL    |       |
| MSSN        | varchar(64)  | YES  | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Then I inserted some tuple in Employee table using following query.

mysql> INSERT INTO Employee VALUES
    -> ("1", "A", "OWNER",  NULL),
    -> ("2", "B", "BOSS",   "1"),
    -> ("3", "C", "WORKER", "2"),
    -> ("4", "D", "BOSS",   "2"),
    -> ("5", "E", "WORKER", "4"),
    -> ("6", "F", "WORKER", "1"),
    -> ("7", "G", "WORKER", "4")
    -> ;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

Now I have following hierarchical relation among the rows in table:

        A
       / \
      B   F
     / \
    C   D
       / \
      G   E

Following is Select statement for table:

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
+-----+------+-------------+------+
7 rows in set (0.00 sec)

Now, I want to impose a constraint like : If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS). e.g. If I delete D then B Become BOSS of G and E.

For that I also written a Trigger as follows:

mysql>  DELIMITER $$
mysql>        CREATE
    ->        TRIGGER `Employee_before_delete` BEFORE DELETE
    ->          ON `Employee`
    ->          FOR EACH ROW BEGIN
    ->          UPDATE Employee
    ->          SET MSSN=old.MSSN
    ->          WHERE MSSN=old.MSSN;
    ->        END$$
Query OK, 0 rows affected (0.07 sec)

mysql>        DELIMITER ;

But When I perform some deletion:

mysql> DELETE FROM Employee WHERE SSN='4';
ERROR 1442 (HY000): Can't update table 'Employee' in stored function/trigger
because it is already used by statement which invoked this stored
function/trigger.

I learn here that this trigger is not possible because In MySQL triggers can't manipulate the table they are assigned to.

Is there some other possible way to do this? Can some one suggest me other method ? A suggestion would be enough but should be efficient.

EDIT: Is it possible using Nested Query?
I also asked it here.

The easiest way to delete D is to do the following:

Have B inherit G and E, putting them on the same level as D.
Delete D

First, I'll load your sample data

mysql> DROP DATABASE IF EXISTS grijesh;
Query OK, 1 row affected (0.06 sec)

mysql> CREATE DATABASE grijesh;
Query OK, 1 row affected (0.00 sec)

mysql> USE grijesh
Database changed
mysql> CREATE TABLE IF NOT EXISTS `Employee` (
    ->   `SSN` varchar(64) NOT NULL,
    ->   `name` varchar(64) DEFAULT NULL,
    ->   `designation` varchar(128) NOT NULL,
    ->   `MSSN` varchar(64) DEFAULT NULL,
    ->   PRIMARY KEY (`SSN`),
    ->   CONSTRAINT `FK_Manager_Employee`  FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO Employee VALUES
    -> ("1", "A", "OWNER",  NULL),
    -> ("2", "B", "BOSS",   "1"),
    -> ("3", "C", "WORKER", "2"),
    -> ("4", "D", "BOSS",   "2"),
    -> ("5", "E", "WORKER", "4"),
    -> ("6", "F", "WORKER", "1"),
    -> ("7", "G", "WORKER", "4");
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
+-----+------+-------------+------+
7 rows in set (0.00 sec)

mysql>

View all current relationships

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | D    | BOSS        |
| D    | BOSS        | E    | WORKER      |
| D    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
6 rows in set (0.00 sec)

mysql>

With this UPDATE JOIN query

UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
SET B.MSSN = A.MSSN WHERE A.SSN = '4';

let's elevate G and E to the same level as D and show relationships

mysql> UPDATE `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> SET B.MSSN = A.MSSN WHERE A.SSN = '4';
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | D    | BOSS        |
| B    | BOSS        | E    | WORKER      |
| B    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
6 rows in set (0.00 sec)

mysql>

At this point, D is nobody's boss.

Now, delete it and show relationships:

mysql> DELETE FROM `Employee` WHERE SSN = '4';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT
    ->     A.name,A.designation,
    ->     B.name,B.designation
    -> FROM
    ->     `Employee` A INNER JOIN `Employee` B ON A.SSN= B.MSSN
    -> ORDER BY A.SSN,B.SSN
    -> ;
+------+-------------+------+-------------+
| name | designation | name | designation |
+------+-------------+------+-------------+
| A    | OWNER       | B    | BOSS        |
| A    | OWNER       | F    | WORKER      |
| B    | BOSS        | C    | WORKER      |
| B    | BOSS        | E    | WORKER      |
| B    | BOSS        | G    | WORKER      |
+------+-------------+------+-------------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Please notice that you do not need a Trigger


How do long string field names affect MongoDB database size? I am using MongoDB and the names of my fields are strings with 10-20 characters. A typical document consists of 30.000 columns filled mostly with floats, like 1.2, 10.5, 2.55. It's size is 1MB.

Do the long string field names affect the size of the MongoDB database ?

This is covered in the developer FAQ, some relevant excerpts:

  MongoDB stores all field names in every document. For most documents,
  this represents a small fraction of the space used by a document;
  however, for small documents the field names may represent a
  proportionally large amount of space

And, just a note on indexes:

  Shorter field names do not reduce the size of indexes, because indexes
  have a predefined structure

So, yes, reducing your field name size will make storage more efficient, though it will have no impact on index sizes.  Whether the saving you will make is worth it (versus loss of descriptiveness) will be up to you.  As an approximation, you will probably save something like 16 bytes per field if you drop to 2 character field names from 20 (for example) and that should mean that the document size will be reduced by more than 40% (~400k).

Here's an easy way to estimate this using the MongoDB shell:

$ ./mongo --nodb
MongoDB shell version: 3.0.2
> testObject = {"12345678901234567890" : 1.23324}
{ "12345678901234567890" : 1.23324 }
> Object.bsonsize(testObject)
35
> testObject = {"1" : 1.23324}
{ "1" : 1.23324 }
> Object.bsonsize(testObject)
16
> testObject = {"12" : 1.23324}
{ "12" : 1.23324 }
> Object.bsonsize(testObject)
17

The Object.bsonsize method will give you an approximate size of any document in bytes but does not include padding, indexes etc. that would actually be used when storing a document in the database. Hence, these are all very approximate numbers - I would recommend testing with actual data to get a more definitive example.


Insert 4 tinyint values into varbinary(4) I'm working on a function to convert the string representation of an IP into its varbinary(4/16) form.

Right now, I have this:

@stringIP = '192.168.0.3'
select
    CAST(PARSENAME(@stringIP, 4) as tinyint),
    CAST(PARSENAME(@stringIP, 3) as tinyint),
    CAST(PARSENAME(@stringIP, 2) as tinyint),
    CAST(PARSENAME(@stringIP, 1) as tinyint)

This breaks up my IPv4 Address very well; but I need to figure out how to insert it into a varbinary(4) so I can insert the data to my table. I cannot find ANY TSQL syntax to combine these four tinyints into a varbinary(4) -- I can only find C#/VB examples using paramterized SQL. This is fine and good, but I'm trying to create an SQL Function do use in an INSTEAD OF INSERT trigger.

@stringIP = '192.168.0.3'
select
    cast(CAST(PARSENAME(@stringIP, 4) as tinyint) as varbinary(1)) +
    cast(CAST(PARSENAME(@stringIP, 3) as tinyint) as varbinary(1)) +
    cast(CAST(PARSENAME(@stringIP, 2) as tinyint) as varbinary(1)) +
    cast(CAST(PARSENAME(@stringIP, 1) as tinyint) as varbinary(1))


How Does SQL Server Choose Indexes to Use I've recently begun working on indexing the various views in our DB. Often, some of these tables are only used for joins, and rarely are they used in where or order by statements. What this means, is that we are creating multiple indexes (or one large relatively less efficient index) which are all keyed on the primary key. I should clarify that there are no where or order by containing anything from this table and there are no key-lookups in the views I am having issues with.

If SQL has multiple indexes that are all keyed on tbl.ID, how does it make the choice to use one index over the other? I find that it can vary between views even if the data pulled from the index will be the same. Generally, there is not a huge gain/loss in efficiency in choosing one index over the other; leading me to believe that it chooses a 'good enough' index and moves on (since there are many keyed off of the same column).

Further, should creating multiple indexes with the same key be avoided for this reason?

The specific problem i am currently dealing with has the view choosing an index like this:

CREATE NONCLUSTERED INDEX [index1]
ON a.tbl ([ID])
INCLUDE (number,name,year, ...)

Where there are 14 columns in the include (it's unfortunate, but required based on our current query structure)

Over the more efficient index:

CREATE NONCLUSTERED INDEX [index2]
ON a.tbl ([ID])
INCLUDE (number,name,year, ...)

Where there are only 5 columns in the include.

Both indexes have the information that the view needs, but one is obviously much smaller and more efficient to use. The larger index needs to exist to cover a massive view that calls all of the columns in it (roughly half of the table).

It is probably worth mentioning that I could choose to create one massive index as above, which covers almost every view slightly less efficiently than these smaller indexes do. From my testing, it seems that adding these additional indexes does not add a significant cost to updating the tables; and so I chose to create them for the small performance gains. In general, they are used as they are needed, but in certain cases, the optimizer chooses a less efficient option for no apparent reason.

In my limited experimentation:

When the query has to perform a scan, SQL Server does the work to find the narrowest index every time.
When the query is expected to perform a seek, it doesn't care about index width, it just uses the last covering index created.

This kind of makes sense. If you're going to add rules to search for the perfect index in either of these cases, you should do it when it's going to save you the most. On a seek the effort spent in finding a better index when it won't really matter in terms of savings for all rows as opposed to per row is far less likely to be worth it. "Last in" is maybe intentional, with the assumption that the last covering index you created is probably the best one you've created, or it may just be arbitrary and coincidental (like the order of columns in a missing index recommendation).

Proof (well, sort of proof)

My test setup was pretty simple:

CREATE TABLE dbo.t1
(
  id int IDENTITY(1,1) PRIMARY KEY,
  sn1 sysname, tn1 sysname, cn1 sysname, typ1 sysname,
  sn2 sysname, tn2 sysname, cn2 sysname, typ2 sysname,
  sn3 sysname, tn3 sysname, cn3 sysname, typ3 sysname,
  sn4 sysname, tn4 sysname, cn4 sysname, typ4 sysname
);
GO
SET NOCOUNT ON;
GO
INSERT dbo.t1
(
  sn1,tn1,cn1,typ1,sn2,tn2,cn2,typ2,
  sn3,tn3,cn3,typ3,sn4,tn4,cn4,typ4
)
SELECT s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name
FROM sys.schemas AS s
CROSS JOIN sys.objects AS t
INNER JOIN sys.all_columns AS c
ON t.[object_id] = c.[object_id]
INNER JOIN sys.types AS typ
ON c.user_type_id = typ.user_type_id;
GO

CREATE VIEW dbo.v1
AS
  SELECT id,sn1,tn1
  FROM dbo.t1;
GO

This put 13,260 rows into my table (your results will vary). Then I repeatedly created the same three indexes, in different order:

-- widest first
CREATE INDEX ix_wide ON dbo.t1(sn1)
  INCLUDE(tn1,cn1,typ1,sn2,tn2,cn2,typ2,sn3,tn3,cn3,typ3,sn4,tn4,cn4,typ4);
GO
CREATE INDEX ix_mid ON dbo.t1(sn1) INCLUDE(tn1,cn1,sn2,tn2,cn2,typ2,sn3,tn3);
GO
CREATE INDEX ix_small ON dbo.t1(sn1) INCLUDE(tn1,cn1);
GO

-- widest last = ix_small then ix_mid then ix_wide
-- middle1 = ix_mid then ix_wide then ix_small
-- middle2 = ix_small then ix_wide then ix_mid

Then in each of those four cases I ran these two queries and investigated the plans:

DBCC FREEPROCCACHE;
GO
SELECT id,sn1,tn1 FROM dbo.v1; -- scan
GO
SELECT id,sn1,tn1 FROM dbo.v1 WHERE sn1 LIKE N'q%'; -- seek

Results:

         widest first  widest last  middle1   middle2
-------  ------------  -----------  --------  --------
   scan    ix_small      ix_small   ix_small  ix_small
   seek    ix_small      ix_wide    ix_small  ix_mid

The scan always chose the narrowest index. The seek always chose the index that was created last (since all cover). I didn't extend the tests to also include a non-covering index in the mix, because I don't suspect it will change this outcome.

Morals (well, sort of morals)

There are two morals here:

Create your widest indexes first if you want your narrowest indexes to be used in seeks when they can.
It's probably a narrow set of use cases where this optimization is worth it. Such as seeks that return a lot of rows and the wider covering index has very large columns that aren't in the narrower index. In these cases it might be worth explicitly specifying the index with a hint (and all the caveats that involves) rather than relying on the behavior I've observed here.

Probably some internals and heuristics I'm missing here that Paul will clarify. Also I tried to enable trace flag 302 to output index selection information, but this doesn't seem to work on modern versions of SQL Server.