Where to find the size of SQL Server data types? This question has already been asked here but none of the answers was really helpful in finding out the size for example, of a XML variable.

declare @myVariable XML

I have learned from this question:
how to test if XML = '' in sql server?

  Please also note that while it is possible for the internal
  representation of XML data to change between versions, I have tested
  on SQL Server 2008 R2, 2012, and 2014 and the size of an empty XML
  item is consistently 5.

How can I find the size of each SQL Server Data Type?

How can I prove that the size of an XML is 5?

all this started when I was tuning a stored procedure and while using XML as a parameter
improving performance - how to pass a table as parameter in sql server 2005?

How can I prove that the size of an XML is 5?

I believe this question is better stated as: "Where to find the size of variable-length SQL Server data types?"

The issue with variable-length types is that there is no specific size that can be documented anywhere as it all depends on the data currently being stored in a particular variable or column. And the only way to know the current size is to use the DATALENGTH built-in function. For example:

DECLARE @TestXML XML;
SELECT DATALENGTH(@TestXML);

SET @TestXML = N'';
SELECT DATALENGTH(@TestXML);

SET @TestXML = N'g';
SELECT DATALENGTH(@TestXML);
GO

Which returns:

NULL
5
9

Now, the XML datatype does more than just provide convenient access functions for .value, .nodes, .query, and .exist. It is "optimized" in that the physical storage is reduced by creating a dictionary of the tag and attribute names and replacing the text with an ID that can be looked up. So the internal representation of an XML document is not what you see when looking at the XML document.

As you can see below, the NVARCHAR string is 94 bytes due to being UTF-16 (Little Endian) and having 47 characters (as expected). That same value converted to VARCHAR takes up only 47 bytes for those 47 characters (as expected). However, once converted to XML, the size in bytes is 53. How is that? Well, the text of the "row" and "col" tags is recorded only once (in the internal dictionary) and then IDs are used in the internal hierarchy of the document.

DECLARE @TestNVARCHAR NVARCHAR(500);
SET @TestNVARCHAR = N'<row><col>1</col><col>2</col><col>3</col></row>';

SELECT LEN(@TestNVARCHAR) AS [LEN], DATALENGTH(@TestNVARCHAR) AS [DATALENGTH];
-- 47   94

DECLARE @TestVARCHAR VARCHAR(500);
SET @TestVARCHAR = CONVERT(VARCHAR(500), @TestNVARCHAR);

SELECT LEN(@TestVARCHAR) AS [LEN], DATALENGTH(@TestVARCHAR) AS [DATALENGTH];
-- 47   47

DECLARE @TestXMLfromNVARCHAR XML;
SET @TestXMLfromNVARCHAR = CONVERT(XML, @TestNVARCHAR);

SELECT @TestXMLfromNVARCHAR, DATALENGTH(@TestXMLfromNVARCHAR) AS [DATALENGTH];
-- <row><col>1</col><col>2</col><col>3</col></row>  53

This is also why white space and formatting is ignored and the format of the XML value when selected might not be exactly as it was when it was set / inserted:

DECLARE @EmptyTag XML;
SET @EmptyTag = N'<test></test>             <test></test>';
SELECT @EmptyTag;
-- <test /><test />

There are at least two cases, however, where DATALENGTH does not appear to be accurate: SPARSE data and Data Compression. The SPARSE option (introduced in SQL Server 2008) allows fixed-length types to take up 0 bytes when NULL, but then their standard size plus 4 bytes when NOT NULL. However, that doesn't show up correctly here:

DECLARE @TestSPARSE TABLE
(
  RowID INT NOT NULL,
  Col1 BIGINT SPARSE NULL,
  Col2 TINYINT SPARSE NULL
);
INSERT INTO @TestSPARSE ([RowID], [Col1], [Col2]) VALUES (1, 0, 0);
INSERT INTO @TestSPARSE ([RowID], [Col1], [Col2]) VALUES (2, NULL, NULL);

SELECT RowID,
       Col1,
       DATALENGTH([Col1]) AS [BytesCol1],
       Col2,
       DATALENGTH([Col2]) AS [BytesCol2]
FROM   @TestSPARSE;

Returns:

RowID    Col1    BytesCol1    Col2    BytesCol2
1        0       8            0       1
2        NULL    NULL         NULL    NULL

In cases like these you would need to use DBCC PAGE to view the actual row in the actual data page to see what is really happening.

Data Compression behaves the same as the SPARSE option in terms of the effect (or lack thereof) on the value reported by DATALENGTH. This, I suppose, should make sense given that DATALENGTH reports the number of bytes taken up by the datatype (which applies to both variables and columns), but the SPARSE option and Data Compression are storage optimizations (which only apply to columns).


mySQL - Cannot add Foreign Key Constraint I'm trying to add a foreign key constraint but, mySQL is not taking it.  No detailed error, just Cannot add foreign key constraint

Here is my code from an export dump...
thanks for any assistance

-- create tables
CREATE TABLE `calltarget` (
  `idCallTarget` int(11) NOT NULL,
  `idImportCTBatch` int(11) NOT NULL,
  `idBroker` int(11) NOT NULL,
  `idCallMaker` int(11) NOT NULL,
  `FirstName` varchar(40) DEFAULT NULL,
  `LastName` varchar(40) DEFAULT NULL,
  `StreetAddress` varchar(60) DEFAULT NULL,
  `City` varchar(30) DEFAULT NULL,
  `Province` varchar(10) DEFAULT NULL,
  `PostalCode` varchar(7) DEFAULT NULL,
  `MobilePhone` varchar(13) DEFAULT NULL,
  `HomePhone` varchar(13) DEFAULT NULL,
  `Email` varchar(80) DEFAULT NULL,
  `Broker` varchar(80) DEFAULT NULL,
  `Carrier` varchar(60) DEFAULT NULL,
  `Notes` varchar(250) DEFAULT NULL,
  `Status` int(11) DEFAULT NULL,
  `CUser` int(11) DEFAULT NULL,
  `Created` datetime DEFAULT NULL,
  `MUser` int(11) DEFAULT NULL,
  `Modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

...

CREATE TABLE `importctbatch` (
  `idImportCTBatch` int(11) NOT NULL,
  `idBroker` int(11) NOT NULL,
  `idCallMaker` int(11) DEFAULT NULL,
  `ImpFileName` varchar(80) DEFAULT NULL,
  `ImpDate` datetime DEFAULT NULL,
  `ImpStep` int(11) DEFAULT '0',
  `Created` datetime DEFAULT NULL,
  `CUser` int(11) DEFAULT NULL,
  `Modified` datetime DEFAULT NULL,
  `MUser` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add primary keys
ALTER TABLE `calltarget`
  ADD PRIMARY KEY (`idCallTarget`);

ALTER TABLE `importctbatch`
  ADD PRIMARY KEY (`idImportCTBatch`);

-- set autoincrement
ALTER TABLE `importctbatch`
  MODIFY `idImportCTBatch` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `calltarget`
  MODIFY `idCallTarget` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=182;

-- restrict deletion of importctbatch when calltarget records exist
ALTER TABLE importctbatch ADD
CONSTRAINT `fk_importctbatch_calltarget_idImportCTBatch`
     FOREIGN KEY (`idImportCTBatch`)
     REFERENCES `calltarget` (`idImportCTBatch`)
     ON DELETE RESTRICT;

I think you've got the FK the wrong way round, and really want:

ALTER TABLE  calltarget ADD
CONSTRAINT `fk_importctbatch_calltarget_idImportCTBatch`
FOREIGN KEY (`idImportCTBatch`)
REFERENCES `importctbatch` (`idImportCTBatch`)
ON DELETE RESTRICT;


Is it possible to pipe the result of a mysqldump straight to rsync as the source argument? Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

Conceptually, I was thinking something like:

mysqldump -u root -p database_to_backup > db_backup_file.sql | sudo rsync -avz db_backup_file.sql myuser@mysite.com:/var/www/db_backup_file.sql

I've seen people pipe the result to mysql for their one liner backup solution, but I was curious if it was possible with rsync. You know--- cause rsync is magic :)

Thanks for your time!

It's not clear exactly what you're trying to accomplish, but I'll take a couple of guesses and see how close I get.

If you're wanting to let rsync magically handle the difference between your local dump file and a remote dump file, you could just replace the | with && so that if mysqldump finishes without an error, then rsync will transfer the differences after the backup is complete, and if mysqldump fails, the synch won't happen.

On the other hand, if you're just wanting to transfer the dump file across the network and drop it somewhere other than the local machine, then this is an option:

mysqldump -u root -p database_to_backup | ssh myuser@mysite.com 'cat > /var/www/db_backup_file.sql'

...where /var/www/db_backup_file.sql is a path on the remote machine.

Or if you wanted to save it both locally and remotely at the same time:

mysqldump -u root -p database_to_backup | tee db_backup_file.sql | ssh myuser@mysite.com 'cat > /var/www/db_backup_file.sql'


How to eliminate for loop in a nested aggregate problem I have a parsing problem that I am solving with a FOR LOOP in plpgsql, basically because I can't figure out how to solve it with subqueries and nested aggregates. My question is can the for loop be eliminated and if so how? I am using PostgreSQL version 11.1.

The input (JSON): [{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]

The expected output: {{a="1",b="2"},{c="3",d="4",e=""}}

My plpgsql code:

CREATE OR REPLACE FUNCTION parse(_in JSONB)
RETURNS TEXT LANGUAGE plpgsql STABLE AS $BODY$
DECLARE
    _out TEXT;
    _parts TEXT[];
    _row RECORD;
BEGIN
    FOR _row IN (
        SELECT q1.value, ROW_NUMBER() OVER () AS index
            FROM JSONB_ARRAY_ELEMENTS(_in) q1
    ) LOOP
        _parts[_row.index] := STRING_AGG(CONCAT(q2.key, '=', q2.value::TEXT), ',')
            FROM JSONB_EACH(_row.value) q2;
    END LOOP;

    SELECT CONCAT(
            '{{',
            COALESCE(STRING_AGG(q, '},{'), ''),
            '}}'
        )
        INTO _out
        FROM UNNEST(_parts) q;

    RETURN _out;
END
$BODY$;

Run:

my_db=*# select parse('[{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]');
               parse
------------------------------------
 {{a="1",b="2"},{c="3",d="4",e=""}}
(1 row)

UPDATE: In response to request for additional requirements:

The input is an actual json(b) object, not a string representation.
No key or value will ever have a double quote char in it (not even an escaped one.)
The values are always of type string though they might be empty.
There can be a LOT of elements in the JSON.

For just the shown example, plain string manipulation would do the job:

WITH tbl(j) AS (SELECT json '[{"a":"1","b":"2"},{"c":"3","d":"4","e":""}]')
SELECT '{' || regexp_replace( left(right(j::text, -1), -1)
                            , '"([^"]+)" *:', '\1=', 'g')
    || '}'
FROM  tbl;

If you have more sophisticated keys and values, here is a rewrite of your function. Using a simple SQL function instead:

CREATE OR REPLACE FUNCTION f_parse(_in jsonb)
  RETURNS text LANGUAGE sql STABLE AS
$func$
SELECT '{{' || string_agg(part, '},{') || '}}'
FROM   jsonb_array_elements(_in) q1
CROSS  JOIN LATERAL (
   SELECT string_agg(q2.key || '=' || q2.value::text, ',') AS part
   FROM   jsonb_each(q1.value) q2
   ) q2
$func$;

If you don't trust that the original order of elements is guaranteed, use this safe variant:

CREATE OR REPLACE FUNCTION f_parse_safe(_in jsonb)
  RETURNS text LANGUAGE sql STABLE AS
$func$
SELECT '{{' || string_agg(part, '},{' ORDER BY q1.ordinality) || '}}'
FROM   jsonb_array_elements(_in) WITH ORDINALITY q1
CROSS  JOIN LATERAL (
   SELECT string_agg(q2.key || '=' || q2.value::text, ',' ORDER BY q2.ordinality) AS part
   FROM   jsonb_each(q1.value) WITH ORDINALITY q2
   ) q2
$func$;

See:

How to preserve the original order of elements in an unnested array?

db<>fiddle here


Mysql Incremental Backup and point in time recovery I'm writing a shell script using the concept of binary logs for point in time recovery.

Now, the scenario is such that a full-backup will happen every alternate day and Incremental backup should be done in between.
Suppose full-backup happens on Monday and Wednesday, incremental backup should happen on Tuesday.
For point in time recovery, I'm using mysqlbinlog --starttime --stoptime binary-logs > backup.sql
In the shell script I need to use starttime and log position of the previous full-backup to specify the log files in the statement, how can I retrieve this in a shell script and record the changes done during that time.
Is there anyway I can do this, please help me in this regard.

You can use  https://sourceforge.net/projects/mysqlincrementalbackup/ script. Description from its website:

  A complete incremental backup for MyISAM and InnodB in a mix
  environment for those applications use both of engines simultaneously
  using binary logs and a method that does not affect running database.
  There is no need to stop or lock the database, It does utilize only
  binary logs to extract update queries of databases. This tools uses
  automysqlbackup script as part of solution for its full backup.


Non-product invoice items I am having difficulty representing additional charges in an invoice. I understand how to represent the association between an invoice and individual items in that invoice with the use of a invoice_item table, however, there are charges that I want to include to an invoice which are not orders or products. For example, I might want to include an ad-hoc licence fee or an additional administration fees or similar.
What I have so far looks like this:

invoice
============
PK uid
FK cust_id
date_created
status
...

invoice_line
============
FK invoice_id
FK order_id
...

order
============
PK uid
FK cust_id
date_created
status

order_line
============
FK order_id
FK product_id
actual_price
quantity

product
============
PK uid
name
desc

Now, the above mentioned additional charges cannot really be represented as products here, nor are they associated to any specific order. It should be on a level similar to order, in the sense that there might be a number of those additional charges connected to each invoice, there might in fact be such charges even in cases where there were no orders placed whatsoever within the invoice time period.
What is the recommended way to represent such additional charges in my invoice/invoice_item table?

Typically you would not restrict your invoice_line table to specifically products, instead create an exclusive subtype depending on what type each line is (product, fee, discount, tax, memo, etc.).
CREATE TABLE line_type
(
  line_type_cd  CHAR(1)      NOT NULL
 ,description   VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_line_type PRIMARY KEY (invoice_line_type_cd)
 ,CONSTRAINT AK_line_type UNIQUE (invoice_line_type_cd)
)
;

CREATE TABLE invoice_line
(
  invoice_id    INT           NOT NULL
 ,line_nbr      SMALLINT      NOT NULL
 ,line_type_cd  CHAR(1)       NOT NULL
 ,quantity      DECIMAL(9,2)  NOT NULL
 ,amount        DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT FK_invoice_line_for_invoice FOREIGN KEY (invoice_id) REFERENCES invoice (invoice_id)
 ,CONSTRAINT FK_invoice_line_discriminated_by_line_type FOREIGN KEY (line_type_cd) REFERENCES line_type (line_type_cd)
 ,CONSTRAINT PK_invoice_line PRIMARY KEY (invoice_id, line_nbr)
)
;

CREATE TABLE invoice_product
(
  invoice_id        INT       NOT NULL
 ,product_line_nbr  SMALLINT  NOT NULL
 ,product_id        INT       NOT NULL
 ,order_id          UUID      NOT NULL
 ,CONSTRAINT FK_invoice_line_fulfills_order_line FOREIGN KEY (order_id, product_id) REFERENCES order_line (order_id, product_id)
 ,CONSTRAINT FK_invoice_product_is_invoice_line FOREIGN KEY (invoice_id, product_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, product_line_nbr)
)
;

CREATE TABLE invoice_fee
(
  invoice_id    INT       NOT NULL
 ,fee_line_nbr  SMALLINT  NOT NULL
 ,fee_cd        CHAR(6)   NOT NULL
 ,CONSTRAINT FK_invoice_line_charges_fee FOREIGN KEY (fee_cd) REFERENCES fee (fee_cd)
 ,CONSTRAINT FK_invoice_fee_is_invoice_line FOREIGN KEY (invoice_id, fee_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, fee_line_nbr)
)
;

CREATE TABLE invoice_tax
(
  invoice_id    INT       NOT NULL
 ,tax_line_nbr  SMALLINT  NOT NULL
 ,tax_auth_cd   CHAR(6)   NOT NULL
 ,CONSTRAINT FK_invoice_line_remitted_to_tax_authority FOREIGN KEY (tax_authority_cd) REFERENCES tax_authority (tax_authority_cd)
 ,CONSTRAINT FK_invoice_tax_is_invoice_line FOREIGN KEY (invoice_id, tax_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, tax_line_nbr)
)
;

The last step is to add a function/trigger to ensure the insert into each of the subtype tables corresponds to the appropriate line_type.
An invoice_product table isn't something to be avoided - you need a way to separate concerns/unique attributes while still associating the quantities/amounts to each invoice_line.
A few other observations:
There is a normalization error that will permit invalid order/customer combinations for an invoice.  So you'll need to either:

Restrict an invoice to one order - thus allowing one path to customer (inflexible).
Make customer_id part of the primary key of invoice and order and use the power of the composite key to enforce the relationship (most flexible).

The second option would look like this:
CREATE TABLE invoice
(
  customer_id  INT   NOT NULL
 ,invoice_id   INT   NOT NULL
 /* everything else */
 ,CONSTRAINT FK_invoice_for_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
 ,CONSTRAINT PK_invoice PRIMARY KEY (invoice_id, customer_id)
 ,CONSTRAINT AK_invoice UNIQUE (invoice_id)
)
;

CREATE TABLE order
(
  cust_id         INT          NOT NULL
 ,order_nbr       INT          NOT NULL
 ,cust_order_ref  VARCHAR(20)  NOT NULL
 /* everything else */
 ,CONSTRAINT FK_order_placed_by_customer FOREIGN KEY (cust_id) REFERENCES customer (cust_id)
 ,CONSTRAINT PK_order PRIMARY KEY (cust_id, order_nbr)
 ,CONSTRAINT AK_order UNIQUE (cust_id, cust_order_ref)
)
;

At this point cust_id would migrate to your invoice_line table (and subtypes) and enforce only items will be billed to the customer that were placed by the customer  This also allows you to avoid the ugly UUID data type - much easier to reference a smaller composite key than break everything with a 16-byte row pointer:
CREATE TABLE invoice_line
(
  invoice_id    INT           NOT NULL
 ,cust_id       INT           NOT NULL
 ,line_nbr      SMALLINT      NOT NULL
 ,line_type_cd  CHAR(1)       NOT NULL
 ,quantity      DECIMAL(9,2)  NOT NULL
 ,amount        DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT FK_invoice_line_for_invoice FOREIGN KEY (invoice_id, cust_id) REFERENCES invoice (invoice_id, cust_id)
 ,CONSTRAINT FK_invoice_line_discriminated_by_line_type FOREIGN KEY (line_type_cd) REFERENCES line_type (line_type_cd)
 ,CONSTRAINT PK_invoice_line PRIMARY KEY (invoice_id, cust_id, line_nbr)
)
;

CREATE TABLE invoice_product
(
  invoice_id        INT       NOT NULL
 ,cust_id           INT       NOT NULL
 ,product_line_nbr  SMALLINT  NOT NULL
 ,product_id        INT       NOT NULL
 ,order_nbr         INT       NOT NULL
 ,CONSTRAINT FK_invoice_line_fulfills_order_line FOREIGN KEY (cust_id, order_nbr, product_id) REFERENCES order_line (cust_id, order_nbr, product_id)
 ,CONSTRAINT FK_invoice_product_is_invoice_line FOREIGN KEY (invoice_id, cust_id, product_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, cust_id, product_line_nbr)
)
;

The appropriate choice will be dependent on your other requirements - if customers do not want multiple orders combined on a single invoice, the first option is perfectly acceptable and there are other ways to handle if multiple orders were fulfilled at the same time (you'd generally have a manifest or packing_list entity that could handle this).


Can error 1231 after importing a MySQL database be a compatibility issue? ERROR 1231 (42000): Variable 'character_set_client' can't be set to
  the value of 'NULL' Query OK, 0 rows affected (0.00 sec)

  ERROR 1231 (42000): Variable 'collation_connection' can't be set to
  the value of 'NULL'

The above are the errors that I'm getting after importing my freshly dumped database from Bluehost (done through phpmyadmin, both with quick and with custom export methods).

Info Bluehost versions:
MySQL 5.5.42
PHP 5.4.24
Apache 2.2.31
phpMyAdmin 3.5.8.2

Local setup (no MAMP, XAMP, WAMP or LAMP) versions:
MySQL 5.5.25 (found through command line: mysql which version)
PHP 5.5.27
Apache 2.4.16
phpMyAdmin 4.5.1

Could it be related to incompatibility of versions?
Or do you think the origin of this problem lies elsewhere?
Also, once logged in to MySQL I cannot use mysqldump but if I'm not logged in, I can run this type of command:

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Is that normal?

Anyways, I believe you can see how a newbee I am here so please bear with me.

It has to do with the max_allowed_packet on the MySQL server. It was set to 16M and when I upped it to 64M in my.cnf located in /private/etc/ (on Mac El Capitan) which was a copy of the my-huge.cnf.
After stopping the MySQL server and Apache and restarting them made my site work locally again and the error messages went away.


I can't save Database Diagrams I'm trying to save a database diagram but I'm getting an error. How can I fix this?

  Cannot insert the value NULL into column 'diagram_id', table
  'SOFT.dbo.sysdiagrams'; column does not allow nulls. INSERT fails.
  The statement has been terminated.
  The 'sp_creatediagram' procedure attempted to return a status of NULL, which is not allowed.
  A status of 0 will be returned instead. (.Net SqlClient Data Provider)

Hard to suggest a more specific fix or suggest a cause given that we have no background on how your system got into this state, what you did prior to trying to create the first diagram, if you have successfully created diagrams in the past, or even what version of SQL Server you're using. It sounds like your sysdiagrams table somehow lost the IDENTITY property on the diagram_id column. Do this:

DROP TABLE dbo.sysdiagrams;
GO
CREATE TABLE [dbo].[sysdiagrams]
(
    [name] [sysname] NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] IDENTITY(1,1) PRIMARY KEY,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL,
    CONSTRAINT [UK_principal_name] UNIQUE ([principal_id],[name])
);

GO
EXEC sys.sp_addextendedproperty
  @name=N'microsoft_database_tools_support',
  @value=1 ,
  @level0type=N'SCHEMA',
  @level0name=N'dbo',
  @level1type=N'TABLE',
  @level1name=N'sysdiagrams';
GO

This will of course lose any diagrams you were previously able to save.

(After performing a search it saddened me to see that many people suggest as a fix to make the default for this column 0. This is great after you create one diagram but falls apart for multiple  reasons once you try to create a second one.)


Insert more rows at a time using bcp User is using the bcp command to load data from a Linux command prompt.

As of now it is loading 1000 rows at a time. There are 9970001000 total rows to send to SQL Server.

If he wants to insert more than 1000 at a time, what do we need to do or enable on the SQL Server end?

BCP is a command line utility with switches that are used to control the import process, including the number of rows per batch.  The number of rows is not as important as the number of rows per second that are being imported.

BCP documentation and examples:
https://msdn.microsoft.com/en-CA/library/ms162802.aspx

The switch to control the batch size is:
-b batch_size

The speed of the import is affected by variables including the target table (indexes, heap vs clustered, etc.), the I/O capacity of the server, how busy the server is, the type of logging that is enabled, speed of the network, speed of the process on the client machine, etc.

I suggest you first start looking at the target table and go from there.


How can I reset the index of the rows? I would like to select and insert data from an old table to the new table:

CREATE TABLE test2 LIKE test1
INSERT INTO test2 SELECT timestamp, datenum FROM test1 WHERE datenum>=23 and datenum<=1000000 ON DUPLICATE KEY UPDATE datenum=VALUES(datenum)

Also I have and id column in the test2 and test1, but I would like to start the id from 1. If I use the query above I'm receiving the following error

ERROR 1136: Column count doesn't match value count at row 1

INSERT INTO test2 SELECT * FROM test1 WHERE datenum>=23 and datenum<=1000000 ON DUPLICATE KEY UPDATE datenum=VALUES(datenum)

In this case the id start from 3239.

I believe id is autoincrement field, and you want this value to start with 1 in new table.  You can specify column list in your first query, so it won't fail :

INSERT INTO test2 (`timestamp`, datenum)
SELECT `timestamp`, datenum
FROM test1 WHERE datenum>=23 and datenum<=1000000


Implicit Conversion of VARCHAR Column to NVARCHAR does not cause expected table scan Given the following script, I can see implicit conversion and Data type precendence have a negative impact on a query plan

-- create objects
CREATE DATABASE ConvertTest
GO

USE ConvertTest
GO

CREATE TABLE Person
(
    VarcharId   NVARCHAR(4),
    IntId       INT
)

-- insert data
INSERT INTO Person
SELECT  TOP 1000
        CONVERT(NVARCHAR(4),ROW_NUMBER() OVER (ORDER BY a.object_id)),
        ROW_NUMBER() OVER (ORDER BY a.object_id)
FROM    sys.objects a
        CROSS JOIN sys.objects b

-- create indexes
CREATE INDEX IX_Varchar ON Person
(
    VarcharId,
    IntId
)

CREATE INDEX IX_Int ON Person
(
    IntId,
    VarcharId
)

DECLARE @id NVARCHAR(4) = 100
-- statement 1
SELECT * FROM Person WHERE VarcharId = @id
-- index seek

-- statement 2
SELECT * FROM Person WHERE IntId = @id
-- index seek
GO

DECLARE @id INT = 100

-- statement 3
SELECT * FROM Person WHERE VarcharId = @id
-- index scan

-- statement 4
SELECT * FROM Person WHERE IntId = @id
-- index seek

Query 3 implicitly converts the VarcharId column to int (which has higher precendence) and this causes the predicate to be non SARGable and thus causes a table scan.

However, when I run a similar test, I did not get the results I expected:

-- create objects
CREATE DATABASE ConvertTest2
GO

USE ConvertTest2
GO

CREATE TABLE Ids
(
    NvarId  NVARCHAR(4),
    VarId   VARCHAR(4)
)

-- insert data
INSERT INTO Ids
SELECT  TOP 1000
        CONVERT(NVARCHAR(4),ROW_NUMBER() OVER (ORDER BY a.object_id)),
        CONVERT(VARCHAR(4),ROW_NUMBER() OVER (ORDER BY a.object_id))
FROM    sys.objects a
        CROSS JOIN sys.objects b

-- create indexes
CREATE INDEX IX_NvarId ON Ids
(
    NvarId,
    VarId
)

CREATE INDEX IX_VarId ON Ids
(
    VarId,
    NvarId
)

DECLARE @id NVARCHAR(4) = N'10'
SELECT * FROM Ids WHERE NvarId = @id
SELECT * FROM Ids WHERE VarId = @id
GO

DECLARE @id VARCHAR(4) = '10'
SELECT * FROM Ids WHERE NvarId = @id
SELECT * FROM Ids WHERE VarId = @id

All four queries show an index seek (although query two runs the seek through a nested loop with a computer scalar)

Given that NVARCHAR has a higher data type precendence than VARCHAR, I expected to see the VarId column implicitly converted to a VARCHAR and therefore causing a table scan.

Why / how does datatype precendence bahave differently when converting between varchar / nvarchar types?

Well since my post I have managed to find my answer here.

The behaviour of whether a scan or seek will be performed when performing an implicit conversion from VARCHAR to NVARCHAR on the column in the predicate is dependent on the collation setting.

Older, legacy collation settings will cause an index scan and the newer collation settings will cause the seek


How can I handle delayed records a month or older on a table clustered on time? I have a two part question really for anyone working with data warehousing where they have large fact tables.

Question 1

Lets say you have a table that has 500 million or more records in it that is clustered on time. You are only posting incremental records for the last 24 hours on a daily basis to this table.

How do you handle inserting delayed records that are a month or older to that table? Would you do nothing unless the insert caused a lot of fragmentation or would you attempt to drop the indexes, insert and rebuild?

I do not have enterprise edition available to me for table partitioning.

Question 2

If you have a fact table that is growing large like the above example, would it be wise to split the fact table up into multiple tables or would it be better to look towards adding additional files to the filegroup of said table if table partitioning is not an option?

Thanks in advance. I'm dealing with some large growth and trying to approach it the right way.

For question 2.

I think I will break the large table up into many different tables and then use a view to join them together. This will allow me to have something like table partitioning without the feature of table partitioning as outlined in this article.

https://www.simple-talk.com/sql/sql-tools/sql-server-partitioning-without-enterprise-edition/

View with schemabinding that unions all the tables together. Then I can insert data and select data from this view as if it was the primary fact table. I would only need to ensure all my SELECT queries on this view include the column I chose to partition the tables with to get the full benefit.


Full table scan - high water mark When doing a full-table scan, why does Oracle scan all the rows beneath the high-water mark? Doesn't it know that some of the rows have been deleted and that they shouldn't be scanned?

Oracle (as well as any other RDMS) doesn't scan "rows". It operates with blocks (other vendors may use different terminology , e.g. page) , and it doesn't know which blocks have or don't have rows. Also, sequential read is much faster than random , and it's way cheaper to read more than needed into memory...  A good graphical explanation of HWM : http://chandu208.blogspot.ca/2012/02/high-water-mark-hwm.html


OUTPUT clause with UPDATE FROM In the code below, the OUTPUT Updated.* fails when executed as-is, but removing the FROM in the UPDATE and replacing it with a static value works fine. Is there a different syntax required when using OUTPUT with an UPDATE FROM?

 USE SCRATCH

 CREATE TABLE Tab1(a char(10))
 CREATE TABLE Tab2(a char(10))
 CREATE TABLE Tab3(a char(10))

 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')
 INSERT INTO Tab1(a) values ('a')

 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')
 INSERT INTO Tab2(a) values ('a')

 UPDATE T1
 SET T1.a = T2.a
 FROM Tab1 T1 JOIN Tab2 T2 ON T1.a = T2.a
 OUTPUT Updated.*

The error I get is

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near 'OUTPUT'.

No it's the same syntax

The output still goes immediately after the set.

There is no updated table available though.

UPDATE T1
SET T1.a = T2.a
OUTPUT INSERTED.*,DELETED.*
FROM Tab1 T1 JOIN Tab2 T2 ON T1.a = T2.a


Transaction - Timestamp Ordering. When is aborted transaction restarted (I don't know if this is the correct place to ask, since this is more like a theoretical question).

I have a question about restarting aborted transactions. I don't know when / at which point should restarting of the transaction(s) occur.

Example - using Basic Timestamp Ordering

w11(x), r12(x), r11(z), r13(x), w13(z), r12(z), w13(x), w14(x)

Initial values are:

read_ts(x) = write_ts(x) = read_ts(z) = write_ts(z) = 10

As we can see transaction 12 is aborted because it is trying to read value z, written already by T13 (older, T12 can't read it).

So in this case T12 is aborted and its timestamp is raised (to 15?). My question is when does the aborted transaction(s) restart?
(1) After all the non-aborted transactions are done, or (2) soon as possible (= restarts after next operation w13(x))?

In scenario 1, w14(x) would be done, and after that aborted transaction 12 would start (as T15).

In scenario 2 (soon as possible), transaction 12 (as T15) is restarted immediately after next operation w13(x). After that w14(x) is aborted (14 < 15) and restarted as T16

What is the correct order in this case? What does the restart actually mean? When do we restart an aborted transaction(s)?
I would like to see a practical example.

edit: changed the last operation to w14(x) instead of r14(x)

well, my teacher said that there is no clear answer since the methods varies. Some DBMSystems won't restart aborted transactions at all


how to create backup for update tables structures in existing DB? I have Sql Server DB, I know I can Create thru "Generate Scripts" backup for SP and Functions then I just replace inside the script the "CREATE" with "ALTER", that will update the target DB with the latest Functions And SP but how to do that for Tables structures  and "User Defined Tables" ?

Another tool, apart from those already mentioned is ApexSQL Diff

As Mitch has already mentioned, writing update scripts that will synchronize two databases manually is quite a risky job. You can maybe try to compare by creating scripts for all objects and then use a file compare tool to find the deltas between the two schemas and change it. But for even a bit larger databases I would not recommend this method since it’s quite easy to miss some change

For some object types you can use query like this that will give you objects that are not equal but note that this doesn't cover everything.

select O.name, sm1.definition
from DB1.sys.sql_modules AS sm1
inner join DB2.sys.sql_modules as sm2 on sm1.object_id = sm2.object_id
inner join DB1.sys.all_objects O on sm1.object_id = O.object_id
where sm2.definition <> sm1.definition

Disclaimer: I'm working for ApexSQL as a Technical Support Engineer


pg_dump custom format between postgres versions I was wondering how stable the custom pg_dump format is between Postgres versions.
Can I for example restore a custom format dump created with Postgres 8.x in postgres 9.x?

Background:

I am building a virtual appliance which contains all configuration in a PostgreSQL DB. I have to decide whether I use the SQL format or the custom format for the backup. As the appliance evolves over time (couple of years) I will use newer PostgreSQL versions and still need to be able to restore old backups without a lot of trouble.

In my experience, you can use pg_dump without formatting to backup and restore databases between versions without any issues. This is when using pg_dump to simply generate a SQL file.

example

pg_dump mydb > db.sql

However, when using -Fc as an option

$ pg_dump -Fc mydb > db.dump

You can only restore the dump file with a version of PostgreSQL that's higher i.e. more recent. So if you create the dump file with PostgreSQL 8.1, you can only restore that same file with pg_dump from a 8.1 or more recent version of PostgreSQL.

There may be other elements that must exist on the target server such as plpgsql or postgis, if those same elements exist on the source server.

Here is the documentation from Postgres on pg_dump.

Here is the relative paragraph

  Because pg_dump is used to transfer data to newer versions of
  PostgreSQL, the output of pg_dump can be expected to load into
  PostgreSQL server versions newer than pg_dump's version. pg_dump can
  also dump from PostgreSQL servers older than its own version.
  (Currently, servers back to version 7.0 are supported.) However,
  pg_dump cannot dump from PostgreSQL servers newer than its own major
  version; it will refuse to even try, rather than risk making an
  invalid dump. Also, it is not guaranteed that pg_dump's output can be
  loaded into a server of an older major version — not even if the dump
  was taken from a server of that version. Loading a dump file into an
  older server may require manual editing of the dump file to remove
  syntax not understood by the older server.


SQL Server 2014 SP2 upgrade on Alwayson + FCI I want to upgrade my SQL Server 2014 to SP2 which is involved in SQL Server FCI and Alwayson. Below is my environment details:

    Nodes: SERVER-A, SERVER-B, SERVER-C, SERVER-D
    SQL Server clustered instances : Instance-A, Instance-B, Instance-C
    SQL Server edition and version : SQL Server 2014 EE SP1 on all 3 clustered instances
    Windows server edition and version : Windows server 2012 R2 STD

SQL Server clustered Instance-A runs on SERVER-A and is installed
between SERVER-A and SERVER-B.
SQL Server clustered Instance-B runs on SERVER-B and is installed
between SERVER-A and SERVER-B.
SQL Server clustered Instance-C runs on SERVER-C and is installed
    between SERVER-C and SERVER-D.
SQL Server clustered Instance-A has Alwayson configured for some DBs between Instance-A and Instance-C. Instance-A acts as Primary replica and clustered Instance-C acts as its secondary replica. Alwayson failover mode for DBs is manual.

Please let me know how should I go about patching both my SQL Server clustered instances and any precautions to be taken.

The answer can be found here: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances
I've used this method several times on 2 and 3 node AlwaysOn Availability Group implementations, and it works very well, and is explained in great detail in the above link.


How to view user created table in OracleDB? So I created a table in my OracleDB, however when I use the command to view all tables in my environment, it gives a bunch of other tables, scrolling and filling my command prompt with lot of other tables.
Is there any other command to view just the tables that I created?
This is what I am doing currently
C:\> set ORACLE_SID=ocptech;
C:\> sqlplus / as sysdba
SQL> CREATE TABLE customers
     ( customer_id number(10) NOT NULL,
       customer_name varchar2(50) NOT NULL,
       city varchar2(50)
     );
SQL> SELECT table_name
     FROM user_tables
     ORDER BY table_name;

The above command gives me a lot of tables.
Also can someone also explain me, what these tablespace are while executing the following command
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES

Is there any other command to view just the tables that I created?

If you know the table name just add it in a WHERE clause:
SELECT table_name
     FROM user_tables
     where upper(table_name) = 'MY_NEW_TABLE'
     ORDER BY table_name;

Or perhaps something like this:
select owner,
       object_name
from dba_objects
where object_type = 'TABLE'
  and created_date > trunc(to_date('05-15-2021','dd-mm-yyyy'))
  order by owner,
           object_name;

Also can someone also explain me, what these tablespace are while
executing the following command

SYSTEM is where the data dictionary lives.  Don't touch it. Don't create your own objects in it.
SYSAUX is an auxiliary to SYSTEM.  Treat it the same.
UNDOTBS1 is where oracle writes 'undo' information - information needed to rollback a transaction.  Not only used for actual rollback, but when a query needs a transactionally-consistent view of the data.
TEMP is the 'temp' tablespace.  Used when operations like sorting cannot be completed in memory.  Kind of like Oracle's version of the OS's page file.
USERS - created by oracle by default to be the default tablespace assigned to users.

More information can be found in the Database Concepts manual.
-Expanding original answer to address additional questions:

So where my user created tables are stored, I mean in which
tablespace?

If you did not specify the TS when you created the table, it will be in the default TS for the user that created it.   You can check this with
select username,
       default_tablespace
from dba_users
order by username
;

To check specifically for which TS a table is in:
select owner,
       table_name,
       tablespace_name
from dba_tables
order by owner,
         table_name
;

What if there are 100 of user created tables, how do I view in that
case of course I can't filter by table name in that case

Then remove the WHERE clause, and (if using sqlplus) spool the output to a text file, which you then open with a text editor:
spool myfile.txt
select .....
spool off


Relationship Between Students, Parents and Guardians Assume I use MySQL, and there are two tables, students and parents_guardians table. Table students is used to store students' data. The parents_guardians table is used to store students' parents and  students' guardians.
So either a student can have a father and a mother as his parents, or a student can have an uncle and an aunt as his guardians. To do that, I add one more table as pivot table, which is students_parents_guardians table.
This pivot table contains 3 columns:

students_id
parents_id
guardians_id

The question is:
Is this good design? And how can I do that in Laravel?
So far, I'm unable to do that. What I can do is create 5 table:

students table to store student's data
parents table to store parents' data
students_parents as pivot table to perform relationship between students
and parents.
guardians table to store guardian's data
students_guardians as pivot table to perform relationship bwtween students
and guardians.

I believe this is a bad idea, and I should normalize all of it.

Your design with 5 tables is more normalized than your initial design, and makes more sense.
When you say "pivot table" it sounds like you mean a linking table. Since the relationship between students and parents (and also between students and guardians) is a many-to-many relationship being that a student can have up to two parents, and a parent can have multiple children (students) then this is the proper way to relate the two tables in a normalized form, and good design.
The only thing I think you could do is consolidate the parents and guardians into one Table (assuming they have basically the same attributes) and have a type field to determine if it's a parent or guardian. If there's only two types, you can use a boolean datatype field called IsParent, or with more types you can create an enum table called RelationshipTypes (or something similar) and store the ID in your consolidated Table, for normalization.
The IsParent field would be best to put on the linking table, that way a Guardian can be a parent to one Student but only a Guardian to another Student (who isn't related by blood) in cases of like adoption, etc.
Here's a sqlfiddle to illustrate the above, with some tweaks.


Time sheet Database design with multiple clients My Requirement
I need to design a database that handles multiple time sheet formats for different clients. Some of the fields in TimeSheet are the same for some clients and different for others. Also, each client's time sheet may have unique fields. For example:

Client 1 ==> EmpID, Date, Start Time, End Time,Verifier, Supplier Name ...
Client 2 ==> EmpID, Date, No.Of Hours, Project Code, Project Manager ...
Client 3 ==> EmpID, Date, Time In, Time Out, No of Hours Worked, Verifier...
Client 4 ......................................................
Client .. ......................................................

My Query

How should I design the TimeSheet table to accommodate the existing clients or future clients that may get added (not a major concern at this point)

Should all the fields related to all clients be in one table, TimeSheet?
Or should I have a main table TimeSheet with all the common columns, and then additional tables corresponding to each client, to contain the specific fields for that client?
In case I need to have TimeSheet child tables for a future client who would need to have more than one entry per day specific to project tasks how should be incorporated?
Any tips on good practice?

There is no perfect answer for this only you can decide the best option. And it has a lot to do with tradeoffs, and preferences. How much time you have now vs maintenance in the future.

You could have:

1 table for all customers.
A table(s) for each customer
A database for each customer.

Option 1 is ideal if you hope to reuse and resell to many customers. The downside is that you cant use the db schema to enforce rules per column unless the rule applies to all customers. Which makes enforcing business rules difficult.

Option 2 allows you to use the schema to enforce rules per customer. But the queries and app design become more complex.

Option 3 means you can create a customised solution per customer, but it will require more effort to maintain.

Start by modelling the requirements and see how much commonality there is and what is or is not mandatory.

Understanding business rules and things like one to many relationships will be key to getting it right.

If you dont expect the db to enforce mandatory fields and can handle that in the application personally i would lean towards option 1 with as much re-use as possible.


check if a user-defined type exists before dropping it The sp_droptype procedure can be used to drop a User Defined Type.

So I have a number of such sp_droptype calls in my "clean" script which I invoke using sqsh -i. However it is not possible to conditionally invoke sp_droptype only if the type does in fact exist or to instruct it to be silent if the type doesn't exist.

Is there a way to test if a type exists or not and only invoke sp_droptype if it actually exists? This would result in cleaner output.

This works the same as in SQL Server; use the following style of construct:

IF OBJECT_ID('dbo.MyObject') IS NOT NULL
BEGIN
    EXEC sp_droptype 'dbo.MyObject';
END

OBJECT_ID(...) returns an integer representing the object_id from sysobjects for every object stored in the current database.  It will return NULL if the object does not exist.

Another way of testing for object existence:

IF EXISTS (SELECT 1 FROM systypes st WHERE st.name = 'MyType')
BEGIN
   EXEC sp_droptype 'MyType';
END

I don't have the ability to create a type in ASE at the current time, so I cannot say for certain if OBJECT_ID() will work.


SQL Server 2016 Audit Options Often law or organization policies require an application to track changes to certain table rows in a database. The changed data is often also viewable through some application page. Some common strategies exist to implementing these, such as database triggers, DAO query injects, SQL Server Change Data Capture...

Is there anything new related to this in SQL Server 2016?

Temporal tables are new and in all editions. They add a start and end time to rows and keeps a history of datachanges with some DDL to pull back changes.

It may be possible to stick into existing applications but has some limitations (eg no cascade deletes) and so is much more likely to be put in during design time so that developers don't need to roll their own anymore.


Assessing statement about third normal form Consider the following statements:

A relation R, is in 3NF if every non-prime attribute of R, is fully functionally dependent on some key of R.

State True or False.

The answer given is true. But I think its false.
Example:
Let R(A,B,C) be a relation with functional dependencies {A→B, B→C}.
Here A is the candidate key and both B and C are fully functionally dependent on A since A→B and also A→C right?
But this relation is clearly not in 3NF. Where am I wrong?
As per the definition I found here, the set of attributes X will be fully functionally dependent on the set of attributes Y if the following conditions are satisfied:

X is functionally dependent on Y and
X is not functionally dependent on any subset of Y.

Here C is functionally dependent on A and also C is not functionally dependent on any subset of A. So this would imply that C is fully functionally dependent on A right?

The exercises says:

  A relation R is in 3NF, if every non-prime attribute of R, is fully functionally dependent on some key of R.

As you have noted, that definition is imprecise. Let’s try to correct it:

  A relation R is in 3NF, if every non-prime attribute of R is fully functionally dependent on some (candidate) key and on nothing else.

Of course such definition should also exclude trivial dependencies or derived dependencies in which the left part contains superflous attributes.


Group values: CASE text, ELSE field value I have a ROAD table:

+----+------------+
| ID | ROAD_CLASS |
+----+------------+
|  1 | ARTERIAL A |
|  2 | ARTERIAL B |
|  3 | ARTERIAL B |
|  4 | ARTERIAL C |
|  5 | ARTERIAL C |
|  6 | ARTERIAL C |
|  7 | COLLECTOR  |
|  8 | COLLECTOR  |
|  9 | LOCAL      |
| 10 | LOCAL      |
+----+------------+

The data type for the ROAD_CLASS field is NVARCHAR2.

I would like to create a view that groups all arterial roads in to a single ARTERIAL category, but leaves the other road classes as-is:

+------------+
| ROAD_CLASS |
+------------+
| ARTERIAL   |
| COLLECTOR  |
| LOCAL      |
+------------+

How can I do this?

What I've tried:

I've successfully done part of it:

SELECT
      (CASE
          WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL'
          --ELSE ROAD_CLASS
      END) AS ROAD_CLASS_GROUPED
FROM
    USER.ROAD
GROUP BY
      CASE
          WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL'
      END

+--------------------+
| ROAD_CLASS_GROUPED |
+--------------------+
| null               |
| ARTERIAL           |
+--------------------+

However, instead of the other road classes being output as null, I'd like to get their field values (COLLECTOR and LOCAL).

When I try adding ELSE ROAD_CLASS to the CASE statement (as demonstrated in the SQL comment) I get an ORA-12704: character set mismatch error.

How about ...

select distinct
   (case
       when road_class in('ARTERIAL A','ARTERIAL B','ARTERIAL C') then n'ARTERIAL'
       else road_class
     end ) as ROAD_CLASS_GROUPED
from user.road;

Due to the fact that the datatype of the column road_class is NVARCHAR, we need to write a  N  or  n  in front of the string literal 'ARTERIAL' in order to prevent the error message. (see also: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF00218 )


What to set innodb_buffer_pool and why..? I have 170GB of InnoDB index and Data.

I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB.

So what should be the optimal size of innodb_buffer_pool.

UPDATE

we are going to migrate our this local database to ec2 so will set the RAM according to the current statistics of innodb that's why i need the size of buffer pool so we can have available RAM there.

File Per table is enabled.

I am using Linux machine.

The biggest table you have makes up 16.47% (28/170) of the total data. Even if the table was highly written and highly read, not all 28G of the table is loaded in the buffer pool at one given moment. What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server.

Here is a more granular way to determine innodb_buffer_pool_size for a new DB Server given the dataset currently loaded in the current DB Server's InnoDB Buffer Pool.

Run the following on your current MySQL Instance (server you are migrating from)

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data';  -- IBPDataPages
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS

Run the formula IBPPctFull = IBPDataPages * 100.0 / IBPTotalPages.

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

If IBPPctFull is 95% or more, you should set innodb_buffer_pool_size to 75% of the DB Server's RAM.

If IBPPctFull is less than 95%, run this formula : IBPSize = IPS X IBPDataPages / (1024*1024*1024) X 1.05.

SET @IBPSize = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_page_size'); -- SELECT @IBPSize;
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPSize = concat(ROUND(@IBPSize * @IBPDataPages / (1024*1024*1024) * 1.05, 2), ' GB' );
SELECT @IBPSize;

The number for IBPSize (in GB) is the number that more closely fits your actual working dataset.

Now, if IBPSize is still too big for the biggest Amazon EC2 RAM Config, use 75% of the RAM for the Amazon EC2 DB Server.


Simple query with date type requires explicit conversion to_date of date My question is, I have a table which contains a column called myDateColumn (for example) which is of the type date:

I can confirm desc myTable contains this line:

myDateColumn  NOT NULL     DATE

However, when I try to select all the data that is between certain dates, for example:

select * from myTable
where myDateColumn
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

I get null results no matter what, even though I am sure some data exists within this specific date range.

The fix for it I discovered was to explicitly convert myDateColumn to a date:

select myDateColumn from myTable
where to_date(myDateColumn)
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

which returns the correct results, but it is already a date type!  Why do I need to explicitly convert a date column to date before doing the between logical comparison?

The reason why I ask is because this select/ where will be part of a stored procedure selecting data within a specific date range to be run on a big table partitioned by date, with more than ten million rows, and if I can avoid explictly converting each myDateColumn of each row, then I might be able to save some query time, in theory.

Or is there a more correct way to run this comparison to select data within a date range?

Thank you.

Update:  Leigh suggested in the answer to diagnose the table for something inconsistent with his query.  The result of his query was:

myDateColumn    TO_DATE(myDateColumn)   TO_CHAR(myDateColumn,'DD-MON-YYHH.MI.SSPM') TO_CHAR(myDateColumn,'YYYY')
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011

Indicating that the reason the between clause was not working, is because none of the data would fall in the query interval, since the year was 0011, during the Roman Empire :)

Thanks guys.

All dates in Oracle have a time component.

I'm pretty sure you want all rows between the 13th and the 15th included. However, when you write:

BETWEEN to_date('13-02-2011', 'DD-MM-YYYY') AND TO_DATE('15-02-2011', 'DD-MM-YYYY')

you select only all date up to the 15-02-2011 at midnight. All rows later that day won't be selected.

What is happening is that when you use TO_DATE on a date, it will be converted to VARCHAR2 and then converted back to a date. The net effect will be a truncation to your date format as shown:

SQL> alter session set nls_date_format='dd/mm/yyyy';

Session altered.

SQL> select to_char(to_date(sysdate), 'dd/mm/yyyy hh24:mi:ss') trunc,
  2         to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') not_trunc
  3    from dual;

TRUNC               NOT_TRUNC
------------------- -------------------
23/10/2012 00:00:00 23/10/2012 15:15:32

So your two clauses are not equivalent at all. The second one selects the whole day feb, 15th whereas the first one only selects the first second of the day.

I suggest you use the following constructions when writing date range:

WHERE dat >= to_date('13-02-2011', 'DD-MM-YYYY')
  AND dat < to_date('16-02-2011', 'DD-MM-YYYY')

Also don't use MON or DAY format in your code since they are dependent upon language. Don't use YY since this can be confusing (ever heard of the Y2k bug?). Both formats are ok to display information to your users of course.


What does normalization say about storing data from objects that use a decorator pattern? I am just curious as to what the best practices are for storing the data from objects built using decorator design.  I have a base class and can decorate it with A, B, and/or C, with A B and C having distinctly different properties.  I have read some things and watched some videos on normalization, but am unsure how to apply it to this situation.

The only solutions I can think are:
Having 1 table for the entire thing with null-able properties for all the decorator properties.

Having 4 tables (Base, A, B, C), but put booleans in the base class to keep track of whether or not it has a certain decoration.

Thanks!

Normalization says nothing about the Decorator Pattern, as normalization is much older.

Check out Table Inheritance though, which I think is more along the lines of what you're talking about.

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

http://martinfowler.com/eaaCatalog/classTableInheritance.html


SQL identity column with unique code column This seems a simple question but is it still necessary to include a primary key with identity even if my columns are only a unique code and its description?

CREATE TABLE table1 (
      column1 INT NOT NULL IDENTITY,
      column2 CHAR(4) NOT NULL,
      column3 VARCHAR(10) NULL
      CONSTRAINT pk_id PRIMARY KEY (column1),
      CONSTRAINT uq_column2 UNIQUE (column2)
)

Sample values:

INSERT INTO table1 VALUES ('A', 'aaa')
INSERT INTO table1 VALUES ('B', 'bbb')

If your column2 is immutable then it can serve as primary key. Only if it was to hold long values then you could consider to create a separate primary key field to save space in the tables that have a foreign key constraint with this table.


Finding Conflicting Combinations Using SQL Say I have table called "Everyone_Loves_Relationships" which contains three fields: Boy_Name, Girl_Name, Relationship_Status. The list goes like this:

Boy_Name Girl_Name Relationship_Status
  Tony     Carol            No
  Simon    Julia            Yes
  Frank    Carol            No
  Frank    Lucy             No
  Frank    Lucy             No
  Tony     Lucy             No
  Tony     Samantha         Yes
  Frank    Carol            Yes

Each combination can show multiple times.

You'll notice that Frank and Carol are showing twice, once with a Yes and once with a No.

How do I design a SQL request that checks for combinations of Boy_Name&Girl_Name that have both a Yes and a No?

Thank you!

SELECT Boy_Name, Girl_Name
FROM Everyone_Loves_Relationships
GROUP BY Boy_Name, Girl_Name
HAVING COUNT(DISTINCT Relationship_Status) = 2

This query assumes that none another value (including NULL) is present in Relationship_Status field. If this is not true then replace the condition with

HAVING SUM(DISTINCT CASE WHEN Relationship_Status = 'Yes'
                         THEN 1
                         WHEN Relationship_Status = 'No'
                         THEN 2
                         ELSE 3
                         END) < 3


MySQL seek-method / keyset pagination? I am trying to change the pagination from OFFSET, LIMIT to keyset pagination. My problem is, the resultset consists of several groups (plz) ordered by uid. I added a fiddle, see http://sqlfiddle.com/#!9/62a380/10.
Query to get the results
SELECT
  uid, nick, u.plz, geodb_locations.name
FROM
  user as u
LEFT JOIN
  geodb_locations ON u.plz = geodb_locations.id
WHERE
  u.plz IN(29386,30013,29384,29385,29232,29481,29667,29355,29977,30370,30319,30160,29970,29932,29679,29268,30128,29169,29546,29235,29444,29148,29313,30259)

ORDER BY
  FIELD(u.plz,29386,30013,29384,29385,29232,29481,29667,29355,29977,30370,30319,30160,29970,29932,29679,29268,30128,29169,29546,29235,29444,29148,29313,30259),
  uid DESC

Resultset
uid     nick    plz     name
46208   user21  29386   Graz
46050   user22  29386   Graz
44995   user23  29386   Graz
-----------------------------------
12883   user46  29384   Gratkorn     <-- Pagination with only uid stops here
-----------------------------------
32667   user47  29385   Gratwein
-----------------------------------
33950   user50  29481   Hitzendorf
33926   user51  29481   Hitzendorf
-----------------------------------
16027   user52  29667   Lannach
496     user53  29667   Lannach
-----------------------------------
19971   user55  29977   Pöls
-----------------------------------
37080   user58  30370   Weiz
33844   user59  30370   Weiz

To paginate through the results I use
uid < 46050

Problem: Pagination is not possible for the whole resultset because uid can be higher than the last uid because of several groups within the resultset. So pagination has to be done by more than 1 column!
Next problem: There is no second column for pagination.
WHERE .. IN and ORDER BY FIELD sorting is fixed. These are zip-codes and they are mixed for each different query, so they can't be used for keyset pagination like (plz, uid) < (123, 987654)
So how is it possible to paginate through the resultset? Any ideas? Thanks in advance!
Fiddle
http://sqlfiddle.com/#!9/62a380/10

You effectively have a 2-column ordering.  So, when "remembering where you left off", you must include both columns.  Then, the > becomes more complex.  I discuss that in a couple of places:
Should I store data pre-ordered rather than ordering on the fly?
http://mysql.rjweb.org/doc.php/deletebig#iterating_through_a_compound_key
General discussion of avoiding OFFSET for pagination:  http://mysql.rjweb.org/doc.php/pagination
Granted, the use of FIELD() adds a wrinkle.  But not a big wrinkle.  Simply use the function call instead of a column name in a few places.


Average Time to Load 1.2 million records from Oracle INTO SQL SERVER 2008R2 I have an ssis package which loads 1.2 million records of data from oracle into SQL SERVER 2008R2(with 32 GB RAM) dataware house table once every week. (truncating and loading)

This process is taking 5 hours to complete the load of 1.2 million records.

If any body did the same thing,i wanted to know their average time to complete the load.

I am new to ssis. Please advice me. Is 5 hours an optimum time ??

Note: the destination table is de-normalized and no indexes are there on it because it is in the dataware house.

Time taken would depend on

Row length
Network Bandwidth
Available resources on the source DB server
And finally on data volume, as in number of rows

To put things in perspective - I recently did a similar migration. A table with 40 columns ( a mix on nvarchar and numeric) and 3 million rows of data took 35 minutes, where as another table with 7.5 million rows of data ( again a mix of nvarchar and numeric), but with 104 columns took more than 7 hours.

Conversely, a third table with 18 million rows of data, 6 columns, all numeric took 14 minutes. It is impossible to answer this question with Yes or No, with the information provided.

Raj


encrypting live data in SQL Azure We are using webjob in azure which imports data every 2 hours into the tables I've created in the cloud. As I read it from here, I can encrypt the db very easily using azure TDE feature. But I am assuming I have to enable data encryption everytime I get new imports from webjob? or am I wrong? Does Microsoft offer live data encryption in azure?

TDE, or "transparent database encryption" encrypts all data contained in the encrypted database.  That is, both existing and newly inserted or updated data is encrypted.
There is no requirement to re-encrypt the database once TDE is enabled, assuming the new data is imported into the same TDE encrypted database.
Along with the document you linked to, this document on transparent database encryption contains a wealth of important information about TDE.
TDE, as suggested in its name, is transparent.  That is, running a select statement against data contained in a TDE-encrypted database will not display encrypted data - it will display the unencrypted version of the data.  The encryption protection is for data-at-rest - i.e. If someone steals a copy of the database, they won't be able see any data contained in that database without the certificate used to encrypt the database.  If you need to prevent data being visible in certain columns such as a credit card number, you should look at column encryption.


How to identify unused databases on my sql server I am looking for the best practices or analysis someone would have followed to identify the unused or inactive databases on my SQL Server.

I went through many articles, which say possibly to go for user connections and activating traces to find the DB usage, but implementing those created more confusion.

Kindly help me in understanding if there is a better and safer way I can take my databases offline once identified.

Sorry, SQL Server does not track database use. People have come up with not-so-clever ideas like a logon trigger that checks the default database of the login and/or ORIGINAL_DB_NAME(). But this misses all kinds of things like contained users, people who later issue a USE command, cross-database queries, etc.

My recommendation is SQL Server Audit if you have an appropriate edition (your question is light on details). Stealing from my own post on this topic:

USE master;
GO
CREATE SERVER AUDIT Test_Server_Audit
    TO FILE ( FILEPATH = 'C:\Audits\' );
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = ON);
GO

USE AdventureWorks;
GO
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT ON Person.Address BY PUBLIC)
    WITH (STATE = ON);
GO

SELECT *
    FROM Person.Address;
GO

SELECT *
    FROM fn_get_audit_file('C:\Audits\*', NULL, NULL);
GO

USE AdventureWorks;
GO
ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

If you don't have Enterprise Edition there is a slightly less reliable way to do this, and that is to check the index usage statistics DMV. You'll need to check this often and potentially for a long time to be sure you're not missing anything crucial, as it only contains data since the last restart of SQL Server.

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

;WITH agg AS
(
    SELECT
        last_user_seek,
        last_user_scan,
        last_user_lookup,
        last_user_update
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID()
)
SELECT
    last_read = MAX(last_read),
    last_write = MAX(last_write)
FROM
(
    SELECT last_user_seek, NULL FROM agg
    UNION ALL
    SELECT last_user_scan, NULL FROM agg
    UNION ALL
    SELECT last_user_lookup, NULL FROM agg
    UNION ALL
    SELECT NULL, last_user_update FROM agg
) AS x (last_read, last_write);

(There are slightly cleaner ways to write that syntax, but I'm lazy and borrowing from code I wrote more than six years ago.)

Caveat Emptor

You can use these methods to determine potential candidates this way, but you still can't be sure that someone won't try to use the database tomorrow, at the end of the next business cycle, etc. So please don't just rush to drop databases that don't show up in the audit or the DMV for a few days - this really isn't a good indicator that the database is no longer being used. When you think you've found candidates to drop, please don't drop them. Take a proper, full backup; store it on another machine; then set those databases to OFFLINE:

ALTER DATABASE whatever SET OFFLINE;

Now, if someone's application suddenly breaks, or a developer lost his work, or the CFO can't run her report, you can fix it with a single statement instead of having to restore a backup or simply telling them "tough luck" because you don't even have a backup:

ALTER DATABASE whatever SET ONLINE;


What is Key Preserved Table concept? I read in the Oracle Documentation about key-preserved table in Updating Join Views Section.

However, I didn't find any simple to way understand it.

I hope to receive some simple conceptual details other than the official Oracle Documentation.

The documentation you have already read says it pretty well. To explain further:

  The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views.

Normally an update acts on a single table. To avoid tortuous subqueries in the filter, Oracle allows you to update a view (or subquery) as long as it is still able to easily map the changes you are making onto real underlying rows in a table. This is possible if the set clause only modifies columns in a 'key preserved' table:

  A table is key-preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.

For example:

create table foo( foo_id integer primary key, foo_val integer not null );
create table bar( bar_id integer primary key, bar_val integer not null,
                  foo_id integer not null references foo );

update (select * from foo join bar using(foo_id)) set foo_val=1;
ORA-01779: cannot modify a column which maps to a non key-preserved table

update (select * from foo join bar using(foo_id)) set bar_val=1;
0 rows updated.

the first update fails because Oracle has no way of 1:1 mapping foo_val in the query to foo_val in foo - conversely the second update succeeds because Oracle can 1:1 map each bar_val to  bar_val in bar. The important thing is that foo_id is unique in foo - so for each row in bar, there can only be at most one corresponding row in foo (actually exactly 1 in this example, but the same applies for a nullable foreign key - the point is that there is never more than one row).


General error 1651 after a mysql migration I finished migration from 5.1 to 5.6, and (with the same queries as before) mysql complains right away about:

  General error: 1651 Query cache is disabled; restart the server with
  query_cache_type=1 to enable it

I don't want the query cache enabled (that's an app with lots of writting), and my file my.ini looks like this:

# The MySQL server
[mysqld]
# ... other parameters...

query_cache_type = 0
query_cache_size = 0

Also, on connect, my php connection wrapper always executes

SET SESSION query_cache_type = OFF

What I don't understand is

Why the parameters apparently do not work
Why does mysql even have to complain about a parameter that is disabled when it is the user's choice to do so.

Enlightment needed - Thanks!

I think the SET SESSION query_cache_type = OFF is causing this. In the Query Cache Configuration page it states:

  If you set query_cache_type at server startup (rather than at runtime with a SET statement), only the numeric values are permitted.

And since you are starting the server with cache disabled, there is no need to use that SET anyway.

As to why it throws an error (both with = OFF and = 0), it may have to do with this note in Server System Variables page:

  query_cache_type

  ...

  If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.

My interpretation is that the variable has both Global and Session scope but only if it not set to off at startup.


Imports & exports under storage constraints and preallocations I have a 5GB oracle database that I want to export and then import on my own local database. Easy right?

The point is, I use oracle XE 10g which allows me to use up to 4GB. I then decided to make the export with the option "rows=N" and I would then fill my empty DB with what I need.

My export without any row is 1MB.

I created my user, granted privileges, created a 1GB tablespace and imported my 1MB file.

The problem is that the tables seem to have some pre-allocated space which means that my tablespace is still too small... 1GB space is too small for a 1MB dump, what a joke :/

I can't truncate the database, and I haven't found anything about turning off preallocation during either export or import.

How can I get this done?

It seems that you use the old export (exp) utility and not the datapump export expdp. For expthere is a parameter compress that manages the size of an initial extent of a table that is imported.  The default value is compress=y which means that the initial extent of the table (this is in most cases the space preallocated when creating an empty table) is as large as the space the table currently uses. So you should use the paramter compress=no. Then the initial extent of an imported table is as large as the initial extent of the table in the source database.


Finding exact row in Clustered index leaf page When traversing through Clustered index in Sql Server, the engine will start from Root page and traverse over non leaf nodes until it reaches leaf level page (data page).
Every data page has row offset array where every slot is ordered by clustered key (physical order of data may not be ordered). Slots only have 2byte offset of the row (doesn't contain key value).
My question is:
If I have say table with two int columns (8 byte) i would have around 1000 entries per data page. How does engine locate exact row with index 500 (if that page contains rows with indexes from 1 to 1000). Does it traverse through slot sequentially examining every row or is there any more efficient algorithm which sql server use?

The specifics of how SQL Server does it is not public.

But I recommend you read the vast research material on the subject which is public. Modern B-Tree Techniques is a good start. Many more papers from Goetz Graefe talk about this area, he is well known in the industry.

Interpolation search is a well known technique in B-Trees and there is plenty of material detailing possible approaches (bear in mind that none of the papers describe explicitly how SQL Server does it and there are many ways to skin a cat).


Trying to understand SHOW SLAVE STATUS in MySQL I have a Master-Slave replication setup and it looks like that it is running fine. Below is a result of SHOW SLAVE STATUS command:

show slave STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: *.*.*.*
                  Master_User: repliV1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 10726644
               Relay_Log_File: mysqld-relay-bin.000056
                Relay_Log_Pos: 231871
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: data1
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 10726644
              Relay_Log_Space: 232172
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

I would like to understand further about the Relay_Log_File, Relay_Log_Pos and Relay_Master_Log_File.

My questions are:

Is it true that the relay log file is one which is being read and stored locally for the replication to run ?
What about the relay_master_log_file then; how is it different from the Master_Log_File?
What are both of these values, viz., Read_Master_Log_Pos and Relay_Log_Pos ? Why are they showing up even though the replication is complete and in sync ?
Is it true that these files are in binary format, and hence I cannot view them ?

I want to understand further is the Relay_Log_File, Relay_Log_Pos and Relay_Master_Log_File.

From SHOW SLAVE STATUS\G, get two values

Relay_Log_File : Current relay log accepting new entries during replication
Relay_Log_Pos : Current position of the current relay log accepting new entries during replication
Relay_Master_Log_File : Relay log file containing the last successfully executed SQL statement on the Master that was executed on the Slave.
Master_Log_File : The log on the Master that corresponds to the current relay log file and current relay log position

  The files are in binary so not able to view them right

Yes, you can view the relay logs. Since they have the same structure as a regular binary log, just run the mysqlbinlog program. For example to view the SQL within any of you relay logs, such as the last one, do this:

mysqlbinlog mysqld-relay-bin.000056 > /root/SQLForCurrentRelayLog.txt
less /root/SQLForCurrentRelayLog.txt


Pull Data from one database and insert to another database ( same schema ) I am currently working on moving data from one database to another in PostgreSQL. I am studying python with psycopg2 to connect and run SQL queries in one database. My problem is how can I pull and insert the data that I queried from one database to another. Most of the posts I found here talk about database from local server to a remote server. Like:

How to work with multiple Postgres databases in psycopg2

This suggests to use dblink or FDW. My situation is that both databases are in the same schema.

I am really new to this kind of task. Any suggestions?

Here's sample code in python that I am working with:

conn_p = p.connect("dbname='p_test' user='postgres' password='postgres' host='localhost'")
conn_t = p.connect("dbname='t_mig1' user='postgres' password='postgres' host='localhost'")

cur_p = conn_p.cursor()
cur_t = conn_t.cursor()

cur_t.execute("SELECT CAST(REGEXP_REPLACE(studentnumber, ' ', '') as integer), firstname, middlename, lastname FROM sprofile")
rows = cur_t.fetchall()

for row in rows:
    print "Inserting ", row[0], row[1], row[2], row[3]
    cur_p.execute(""" INSERT INTO "a_recipient" (id, first_name, middle_name, last_name) VALUES ('%s', '%s', '%s', '%s') """ % (row[0], row[1], row[2], row[3]))

cur_p.commit()
cur_pl.close()
cur_t.close()

FDW and dblink have the advantage that they make a direct postgreql to postgresql connection which means that there will be no processing needed in your python code.  here's an example that should work much like your example code, except faster.

conn_p = p.connect("dbname='p_test' user='postgres' password='postgres' host='localhost'")
strt="dbname='t_mig1' user='postgres' password='postgres' host='localhost'"
cur_p = conn_p.cursor();

cur_p.execute("select dblink_connect('tmpdb',%s);",(strt,));
cur_p.execute("""
  insert into "a_recipient" (id, first_name, middle_name, last_name)
  SELECT * FROM dblink('tmpdb',
    $$
    SELECT CAST(REGEXP_REPLACE(studentnumber, '[^0-9]', '','g') as integer), firstname, middlename, lastname FROM sprofile
    $$ ) as x( a int,b text, c text,d text);
""");

cur_p.execute("SELECT dblink_disconnect('tmpdb');");

unfortunately I was not able to test this code. feel free to make any correction needed.
I also changed the regex to remove all non digits.


Query to select cards and transactions I have some trouble with a query definition. My database is about operations in a bank and I want to show the card numbers which were used in transactions at all ATMs.
This is how my tables DDL:
CREATE TABLE Cards(
    card_id INT PRIMARY KEY IDENTITY(1,1),
    number VARCHAR(25),
    CVV CHAR(3),
    bankAccount_id INT REFERENCES BankAccount(account_id)
);
CREATE TABLE Transactions(
    transaction_id INT PRIMARY KEY IDENTITY(1,1),
    ATM_id INT REFERENCES ATM(id),
    card_number INT REFERENCES Cards(card_id),
    sum_money INT,
    transaction_time DATETIME
);

And I tried below query:
SELECT *
FROM Cards C
WHERE C.card_id = ALL (SELECT *
                        FROM Transactions T
                        WHERE T.card_number = C.card_id
                        )

And it doesn't work since I used card_id on both sides. Can somebody help me, please?

You can do something like this:
SELECT T.card_number
FROM Cards AS C
INNER JOIN Transactions AS T
    ON C.card_id = T.card_number
GROUP BY T.card_number -- Grouping up on Card_Number to get the count of unique ATMs each card was used at in the HAVING clause
HAVING COUNT(DISTINCT ATM_id) = (SELECT COUNT(1) FROM ATM) -- Count of distinct ATMs each card was used at needs to equal the total number of ATMs


Changing backup interval? I'm considering changing the SQL Server differential backup interval for simple recovery databases from every 2 hours to every 4 hours with one daily full backup. A four hour data loss is acceptable per SLA. Considering SQL Server will automatically issue checkpoints, log clearing will continue and doesn't appear to be a concern at this point. Before making this change should I monitor data churn or checkpoint occurrences? Thus far I'm thinking this is truly a RPO/SLA concern or issue. Let me know if I'm missing anything.

Thanks in advance!!!

Considering SQL Server will automatically issue checkpoints, log clearing will continue and doesn't appear to be a concern at this point. Before making this change should I monitor data churn or checkpoint occurrences?

If these haven't historically been issues for you, then I think you are fine. It does indeed appear to be purely an RPO/SLA/SLO based issue.

That being said, you didn't mention what precipitated this change, but something to consider from Google's SRE book around SLA/SLOs:

  Using a tighter internal SLO than the SLO advertised to users gives you room to respond to chronic problems before they become visible externally. An SLO buffer also makes it possible to accommodate reimplementations that trade performance for other attributes, such as cost or ease of maintenance, without having to disappoint users.

So if there isn't an active problem necessitating this change, it might be worth keeping the more frequent differential backups in my opinion.


How to convert MySQL Keyword to normal column We thought of upgrading MySQL to new version 5.5.16. While testing the scripts with the new version, I had found one column Maxvalue is a Keyword in latest version.

For this one, we need to change all the application where it is used.
Instead is there any way to make the keyword Maxvalue interpret as a Normal column, so that we will not have to change our application.

Maxvalue is just column name, it is not the maximum value.

And I am looking for some other option apart from backticks: `Maxvalue`.

I can see these choices

don't rename add backticks everywhere
rename the column

Like this

`Maxvalue`


Do you have to run nodetool repair on every node? Do you have to run nodetool repair on every node in a cluster, or do you only need to run it on one node, and from there Cassandra will take care of the rest?

In looking through the documentation, I am having a hard time finding a reference for this, but the short answer is "yes" you need to run nodetool repair on each node in your cluster.  The closest I can find is the documentation on repairing nodes which suggests that you should not run repair on more than one node in your cluster at a time.

You can also run repair with the -pr flag, which limits the repair operation to the first token range that current node is responsible for.  This cuts down on the duplication of work when run on the remaining nodes.


Slow join behaviour with 'or' in predicate I'm faced with a situation which I can't understand and overcome.

In short we have left-join query like:

select from a
left join b on a.key1=b.key1 or a.key1=b.key2

This works VERY slowly, while at the same time both separately:

select from a
left join b on a.key1=b.key1

select from a
left join b on a.key1=b.key2

work very fast.

b.key1 has normal index

b.key2 has normal index

I can't understand the reason for such behavior? Am I missing something very basic in my join strategy or index usage?

Here we go with detailed plans:

WITHOUT OR(TOP_USTR_ADMIN_IP - index name for ustrip column):

SQL> explain plan for
SELECT * FROM top.macs_constraint mc
LEFT JOIN top.top_ustr tu ON  tu.ustrip = mc.IP;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

   Plan hash value: 349751289

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |     1 |    22 |  1296   (1)| 00:00:16 |
|   1 |  SORT AGGREGATE        |                   |     1 |    22 |            |              |
|*  2 |   HASH JOIN RIGHT OUTER|                   |   981K|    20M|  1296   (1)| 00:00:16 |
|*  3 |    INDEX FAST FULL SCAN| TOP_USTR_ADMIN_IP | 23187 |   181K|    62   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | MACS_CONSTRAINT   |   629K|  8601K|  1231   (1)| 00:00:15 |
--------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------

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

   2 - access("TU"."USTRIP"(+)="MC"."IP")
   3 - filter("TU"."USTRIP"(+) IS NOT NULL)

WITH OR:

explain plan for
SELECT count(*) FROM top.macs_constraint mc
LEFT JOIN top.top_ustr tu ON (tu.ustrip = mc.IP or tu.skladIP = mc.IP);

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2704565128

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     1 |    14 |   322M  (1)|999:59:59 |
|   1 |  SORT AGGREGATE      |                 |     1 |    14 |            |            |
|   2 |   NESTED LOOPS OUTER |                 |  1887K|    25M|   322M  (1)|999:59:59 |
|   3 |    TABLE ACCESS FULL | MACS_CONSTRAINT |   629K|  8601K|  1231   (1)| 00:00:15 |
|   4 |    VIEW              |                 |     3 |       |   513   (1)| 00:00:07 |
|*  5 |     TABLE ACCESS FULL| TOP_USTR        |     3 |    72 |   513   (1)| 00:00:07 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------

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

   5 - filter("TU"."SKLADIP" IS NOT NULL AND "TU"."SKLADIP"="MC"."IP" OR
              "TU"."USTRIP" IS NOT NULL AND "TU"."USTRIP"="MC"."IP")

Why does using or in the predicate give nested loops and no index usage? Is it possible to force index usage?

UPDATED:
missattached wrong plan for "without or".fixed

OR is slower because you no longer can perform an index seek, but effectively forces the database engine to look through each leaf node in your index tree.
With a single parameter (no OR), the engine can seek down through the index to the relevant leaf nodes, but when you ask it an OR, it scans the entire range of leaf nodes.

If the performance is too slow to tolerate, do as the comments suggest and try a UNION (no duplicate values) or a UNION ALL (allow duplicate values), or even two separate queries on their own and then join the results together in the code layer receiving the results.


How can I order the results from a DISTINCT ON query I have a table with the equivalent of:

group_id integer REFERENCES <some other table>
item_id integer REFERECNES <some other table>
timestamp date
data text
sort_order text

I want to get the latest data for every item before some date. I.e. the maximum timestamp less than some value. I also want these rows sorted on sort_order.

After som Googling I find that SELECT DISTINCT ON is what I want, so I get something like

  SELECT DISTINCT ON (item_id)
         data
    FROM [this table]
   WHERE group_id = [some value]
     AND timestamp < [some value]
ORDER BY item_id, timestamp DESC

Now, I actually want the data sorted on sort_order, but DISTINCT ON requires the ORDER BY to be listed first.

One solution that comes to mind is to wrap all of this in an outer SELECT that will ORDER BY sort_order. Is that a good efficient solution, or is there a better "correct" way to do this.

(Or, am I in fact not even close...)

Wrapping the query in a derived table is the obvious way to solve this:

SELECT *
FROM
    ( SELECT DISTINCT ON (item_id)
             item_id, data, timestamp, sort_order, ...
        FROM [this table]
       WHERE group_id = [some value]
         AND timestamp < [some value]
    ORDER BY item_id, timestamp DESC
   ) AS t
ORDER BY sort_order ;

If your original query is efficient and doesn't return too many rows, the additional sort will not add much cost to the query.

Besides DISTINCT ON, there are a few other methods that are often more efficient in this type of "greatest-n-pre-group" queries - but efficiency depends on many parameters, like targeted indexes and distribution of values (how many distinct item_id in the table, how many rows per item_id), Postgres version, etc.

Most notable method is using LATERAL subqueries. See these excellent answers by Erwin Brandstetter:

Optimize GROUP BY query to retrieve latest record per user
Optimize groupwise maximum query
Select first row in each GROUP BY group?
Query last N related rows per row
Optimise a LATERAL JOIN query on a big table


SQL Server 2014 SP3 DB emailer TLS 1.2 support I have SQL Server 2014 (SP3-CU4) 12.0.6329.1 running on Windows Server 2012R2 with .NET 3.5.30729.4926 installed

I can't get Database emailer to work with TLS 1.2

I tried everything in the article "TLS 1.2 support for Microsoft SQL Server" to no avail.
https://support.microsoft.com/en-gb/help/3135244/tls-1-2-support-for-microsoft-sql-server

Can you advise please?

This hotfix worked for me:

https://support.microsoft.com/en-gb/help/3154520/support-for-tls-system-default-versions-included-in-the-net-framework

emails from dbmailer are using TLS1.2 now


SQL Server accepting Access queries? I am currently working on migrating a legacy codebase from VB6 and Access to VB.NET and SQL Server.  I have run the Microsoft SQL Server Migration Assistant successfully and populated a SQL server with all of our existing tables from access, and I am currently looking into completely removing access from the equation, but I still need it currently to run the queries so that they can be forwarded to SQL Server.  Is there a way to send queries directly to SQL Server without needing to translate them all, such as some sort of translation plugin or service I can use?

Assuming your queries are against regular Access tables, you should be able to replace the Access tables with Linked Tables to your SQL Server.  The Linked SQL tables have to have the same names and structure to in order for this to work.  I don't have the 2008 version of Access so I can't give exact instructions but look in the menus for something like External Data or Link Tables, then look for ODBC and create a "Link to the data source..." and follow the instructions to setup an ODBC data source to your SQL server, you can Google how to setup an ODBC connection if you need to.

You may need to setup the ODBC connection outside of Access beforehand depending on your security settings.  Keep in mind that if your OS is 64-bit you need to use the 32-bid ODBC administrator console to setup an ODBC data source that Access can use. (Wasted a day figuring out that one.)


Restore without specifying location for each file I am working with a database that has a dynamic filegroup structure. The number of filegroups and filegroup files can and do change.

Is there a way to specify one data and one log folder in a script that will restore all files to those folders?

Is there a way to specify one data and one log folder in a script that will restore all files to those folders?

You just have to use a good existing tool (hint : restoregene). It does take care of multiple filegroups dynamically.

dont put all the filegroups in one folder - as if the drive fails, you loose your entire database. The whole point of having filegroups is to have them on different disks/LUNs.

EDIT: Based on your comment

  The purpose of the restore script is to restore a copy of the production database to QA and CI servers.

PowerShell is the way to go - Start-SqlServerMigration.ps1

Basically, you have to use WITH MOVE option in restore command since the disk and folder layouts wont match from PROD to QA or other servers.


What to consider when setting flush_interval_seconds in SQL Query Store? Most of the settings for Query Store are fairlying straight forward.  But as best as I can tell the one most likely to cause performance issues (waits) and with the least clear documentation is flush_interval_seconds

I have read Erin Stellato's, SQL Server Query Store: Default Settings

  I’d also probably drop  DATA_FLUSH_INTERVAL_SECONDS to something lower than 900 seconds (15 minutes) if my storage can support it.  This setting determines how often Query Store data is flushed to disk.  If it’s every 15 minutes, then I could potentially lose 15 minutes of Query Store data if my server happened to crash before it could be written to disk.

Microsoft's Best Practice with the Query Store does not even mention it.

Microsoft's sys.database_query_store_options (Transact-SQL) says (for on Premisis);

  Defines period for regular flushing of Query Store data to disk. Default value is 900 (15 min).

The above implies (possibly) Query Store data is held in memory until a regular 15 minutes flush occurs then it is pushed/flushed to disk

But the documents for Azure are slightly different. Operating the Query Store in Azure SQL Database

  Specifies maximum period during which captured runtime statistics are kept in memory, before flushing to disk (emphasis is mine)

The Azure description implies Query Store data moves to disk as regularly as normal data, but if it has not moved by the age, then it is forced to disk.

I know that Query Store data is part of the database, I am not clear if logs are updated only when the data is pushed to disk, or in real time.

Some points I am not clear on that could impact the decision. (Maybe some of these should be separate questions?)

Is query store data written to the log?  If so would a restore let us recover any Query Store data, that had not been pushed to disk?
If I have an AlwaysOn AG, do the secondaries get the Query Store data, real time or are they dependent on it actually getting written to disk on the primary? Rephrased if my Query Store data has not been written on the primary for 15 minutes, and the primary gets unplugged will the secondary capture it?
Can a Query Store disk write have wait conflicts with users OLPT data? If so what are they?
What does changing the flush_interval_seconds value really do?

I have this scenario in my mind where, there is a long running query using all the system resources for an hour+ and Query Store hit the 15 Minute flush interval and now it is in conflict with the users application for resources. I can't work out what the scenario really is and how it impacts the user application. How do I justify my decision for the value I set?

Is query store data written to the log? If so would a restore let us recover any Query Store data, that had not been pushed to disk?

Query Store is persisted to disk, so it survives a crash. But Query Store is also Async, it means if the data has not been persisted and a crash happens in the meantime, the data will be lost. Although, if SQL Server must be restart because of a maintenance routine you could force Query Store to be flush using the procedure: sp_query_store_flush_db.

  If I have an AlwaysOn AG, do the secondaries get the Query Store data, real time or are they dependent on it actually getting written to disk on the primary? Rephrased if my Query Store data has not been written on the primary for 15 minutes, and the primary gets unplugged will the secondary capture it?

Secondary replicas get the query store data from PRIMARY ONLY. You cant do force plans,etc on secondary. Since data is written in Async fashion, there will be data loss.

  Can a Query Store disk write have wait conflicts with users OLPT data? If so what are they?

I dont get what you mean by wait conflicts. I have not see any conflicts on a very busy OLTP system handling 60K trans/sec.

  What does changing the flush_interval_seconds value really do?

As per BOL - Defines period for regular flushing of Query Store data to disk. e.g. how often the data from memory is flushed onto disk automatically rather than you running sp_query_store_flush_db manually.


Steps to apply windows updates to sql failover cluster? What are the steps to apply windows updates to a active/passive two node sql failover cluster?  My initial thought was to apply windows updates to passive node/reboot and then failover and repeat process but not sure.  Do I need to stop the db's and sql agent jobs before failover?
sql2014
windows 2012r2

You do not need to stop any jobs or services to manually failover. Please see the Microsoft Docs - Perform a Forced Manual Failover on manually failing over.
These are the general steps you should follow for applying any updates to your failover cluster instances:

Backup all databases.
Failover all SQL cluster roles to Node A.
Install the patches on Node B (passive).
Restart Node B.
Failover all SQL cluster roles to Node B.
Install the patches on Node A (passive).
Restart Node A

More information on the installing updates process can be found in these Microsoft Docs - Upgrade a failover cluster instance.


mysql_upgrade on fresh MySQL/MariaDB installation What I've noticed lately is that whenever I install MariaDB or MySQL on a clean machine, and if I run mysql_upgrade it does the upgrading, so I figured since it's not showing it's already upgraded, I should do it.
So, it's been my practice ever since. Does anyone know anything more about this? Why this isn't done during installation process? Does this mean newer mariadb/MySQL server comes with table from older version?

According to Bogdan Kecman who worked at Oracle:

  mysql_upgrade checks whether the tables are at the latest version, if not - upgrades them
  and then marks them as upgraded. On a fresh install, even though tables are on the latest version, they are not marked. So, mysql_upgrade marks them. It does nothing else. To avoid scanning them
  again when mysql_upgrade runs.

Sorry for lousy translation.