Is there a non-DDL way to preserve back-references when staging data into production? Let's say I've got this hypothetical schema:

Source (OLTP) DB:

Table Orders
------------
OrderID int IDENTITY (PK),
CustomerID int NOT NULL,
OrderAmount decimal NOT NULL

Destination (DSS) DB:

Table Activity
--------------
ActivityID int IDENTITY (PK),
PersonID int NOT NULL,
Amount decimal NOT NULL

Table ActivityOrderImport
--------------------
ActivityID int NOT NULL,
SourceOrderID int NOT NULL

Table CustomerMapping
---------------------
CustomerID int NOT NULL,
PersonID int NOT NULL

Obviously the real deal is considerably more complicated with more transformations.  But assume for the moment that all this ETL does is merge specific transactions ("orders") from an external entity into a DSS that tracks generic "activities".  The links between the external customer and the DSS person are in the CustomerMapping table.

The idea of the "Import" table is to provide some sort of audit trail in case something goes wrong.  We don't have a lot of control over the source system and know it to be a bit on the flaky side.  So it's really important for us to be able to understand the origin of any given activity.

Right now, there's a script that does this with DDL, which looks something like this:

ALTER TABLE Activity
ADD OrderID int NULL

MERGE Activity
USING #StagingOrders
(...)

INSERT ActivityOrderImport (ActivityID, SourceOrderID)
SELECT a.ActivityID, s.OrderID
FROM #StagingOrders s
INNER JOIN Activity a
    ON a.OrderID = s.OrderID

ALTER TABLE Activity
DROP COLUMN OrderID

This works fine, in the sense of not crashing and burning, it just makes me nauseous every time I look at the DDL.

Permanently adding an OrderID column to the Activity table really isn't an option because the data may come from multiple sources, each currently needing their own log table.  Adding a separate column to the main table for all of these would break normalization and quickly turn the production database into a dog's breakfast.
Removing the IDENTITY constraint from the Activity table and using some natural derived key is a better option, but still impractical for the same reason - because every system has a different way of defining it, we'd end up having to use an nvarchar column for the primary key (ick).  Not to mention we'd lose the sequential-ness which is important for a lot of things.

So I've been wondering, is there a better way to do this, one that doesn't involve DDL but still takes into account the heterogeneous nature of the external keys (and thus the need for different tracking tables for each source)?

I'm not necessarily asking for just a script that'll work under the current design.  I realize that the design might have to be modified and I'm open to creating more tables or even separate staging databases; the only things I'm not willing to do at this point are (a) add those tracking columns to the base table and/or (b) remove the IDENTITY field.  It's important to maintain the separation of concerns here and keep the actual activity data separate from the import "log" data.

Any thoughts as to what I could do with this design to achieve all of the objectives here, or have I painted myself into a corner and made that impossible with the current restrictions?

I would suggest adding a (set of) staging tables to the destination or an intermediary that can be better controlled and more stable. Place the tracking info there. Then do all the transformation from the staging to the final destination, either carrying the tracking info with it or discarding it.

There are several ways you can generate a tracking key from multiple systems, as long as they all follow the same algorithm, it doesn't have to be an INT. It could be a 2 char prefix with a sequential number. For that matter it doesn't have to be just one column.


Can I do something like RESTORE DATABASE .? Is it possible to restore a database on a server, using a script on another server?

For example, I have a server A and a server B and I want to restore a database foo on server B from within a script on server A.
I was thinking of something like this: RESTORE DATABASE B.foo FROM DISK ..., but I can't find any information about it.

And I'm looking for something similar for BACKUP DATABASE too.

Another option:

EXEC linkedserver.master.dbo.sp_executesql N'RESTORE DATABASE ...';


MongoDB: how to get the all the embedded data? I have this data:

> db.Category.find().pretty()
{
    "_id" : ObjectId("542450a928db034349fb3bf2"),
    "name" : "A category",
    "products" : [
        {
            "_id" : ObjectId("542450a928db034349fb3bf3"),
            "name" : "Producto de categoria",
            "price" : 11.11
        }
    ]
}

When I run db.Category.find( {}, {products:1} ) I get this:

{ "_id" : ObjectId("542450a928db034349fb3bf2"), "products" : [ { "_id" : ObjectId("542450a928db034349fb3bf3"), "name" : "Producto de categoria", "price" : 11.11 } ] }

I get the data about one product, but how can I get the data about all products?

If you had multiple documents containing products, you could retrieve all as follows by capturing the query cursor and iterating over the set:

replset:PRIMARY> var cur = db.Category.find( {}, {products:1} );
replset:PRIMARY> while (cur.hasNext()) {
... var doc = cur.next();
... printjson(doc);
... }
{
    "_id" : ObjectId("542450a928db034349fb3bf2"),
    "products" : [
        {
            "_id" : ObjectId("542450a928db034349fb3bf3"),
            "name" : "Producto de categoria",
            "price" : 11.11
        }
    ]
}

If you have multiple products within the "products" array then you would iterate over the array contained in each result document.


Environment Varible PATH exceeding recommended length solution? I am attempting to install Oracle Database 11g on a Windows 7 64x laptop.

I get all the way to step 5 of 8 and I get a fail on Environment variable path "exceeds recommended length"...

I have created a C:\ODB directory and have set ORACLE_HOME=C:\ODB

I have shortened it as much as possible.

What is the correct way to install Oracle Database 11g on Windows 7?

I found the exact solution: and your exactly correct Max Vernon.

"Problem causes problems when installing an Oracle 11g database on a machine which already has a lot of different software installed,
On win 7 64bit, go to PATH variable(Right click on my computer>properties>Advanced system Settings>Advanced Tab>click on Envirnment variable>in system variable - look for Path>

When the failure occurs during installation of Oracle 11g, click on cancel, you will need to restart the installation from scratch
On win 7 64bit, go to PATH variable(Right click on my computer>properties>Advanced system Settings>Advanced Tab>click on Envirnment variable>in system variable - look for Path>
Find the Path environment in the System Variables window and edit it, click on it and select all the values within it,
paste these into a word document or something and save it. You might wish to see whats needed and what isnt but thats a different problem !
Clear the Path environment and simply add a single directory such as c:\Seer
Start the installation of Oracle 11g again and sit there and wait while it chunders through the processing.
Return to the Environment Variables and paste the originals back after the new ones put in by the installation "


SQL Server with French_CI_AS Collation date conversion I have a French installation of SQL Server using French_CI_AS Collation. For legacy reasons, a table column stores data as VARCHAR which is later converted to the right type, akin to variant in C++.
Two rows in this column have data as:
2020-10-12 22:54:40

2020-10-13 04:42:10

The second date fails when converting to DATETIME with default options. The application uses ODBC escape sequences. SQL such as COLMVALUE >= {ts'2020-01-01 00:00:00'} fails with the error:

La conversion d'un type de données nvarchar en type de données datetime a créé une valeur hors limites

I did a DBCC useroptions  and the server has date format set to dmy. Why is the 2nd row failing? It looks like it is trying to interpret "13" as the month. If the format is dmy, shouldn't both dates fail?
Also, it is interesting to note that on this server, the following fails:
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
SELECT @datevar;

But this works:
DECLARE @datevar DATETIME2 = '2020-10-13 04:42:10';
SELECT @datevar;

I am aware that DATETIME2 is more accurate and has greater range than DATETIME. However, it seems that even the parsing is different for the two here.

This is not a collation issue. This is a language / locale / culture issue. Your DB (or your login's default language) is set to "French" (most likely) and they use Day then Month formats, not Month then Day, hence your statement is attempting to use month 13.
Try this to reproduce:
SET LANGUAGE French;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
/*
Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line XXXXX
La conversion d'un type de données varchar en type de données datetime a
    créé une valeur hors limites.
*/

GO
PRINT '-----';
GO

SET LANGUAGE English;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
GO

You have two options for changing this:

SET LANGUAGE
This controls not just the date format but also currency symbol, day and month names, etc. This might not be the best choice given that it controls more than just the date format, but it might be easier if it only requires updating the login's default language instead of updating a lot of code to do option # 2.

SET DATEFORMAT
This controls just the date format:
SET LANGUAGE French;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
/*
Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line XXXXX
La conversion d'un type de données varchar en type de données datetime a
    créé une valeur hors limites.
*/

SET DATEFORMAT ymd;
DECLARE @datevar2 DATETIME = '2020-10-13 04:42:10';

SELECT @datevar AS [@datevar], @datevar2 AS [@datevar2];
/*
@datevar    @datevar2
NULL        2020-10-13 04:42:10.000
*/

Regarding your other questions:

If the format is dmy, shouldn't both dates fail?

and

(implied) Why do DATETIME and DATETIME2 appear to parse the same date differently?

All of that is because working with dates is really messy. The documentation for DATEFORMAT states not only that:

datetime and smalldatetime interpretations may not match date, datetime2, or datetimeoffset

but also:

Some character string formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.

While the documentation doesn't tie those two statements together, testing does show that ISO 8601 formatted dates (e.g. YYYY-MM-DD) are "interpreted independently of the DATEFORMAT setting" only for DATE, DATETIME2, and DATETIMEOFFSET. That is why the date of '2020-10-13' respects the value of DATEFORMAT for your DATETIME variable (and hence receives an error for cultures where the day comes before the month, whether separating date parts with "-" or "/"), but is parsed correctly for your DATETIME2 test (and always will regardless of the current culture or value of DATEFORMAT).
P.S. I would not consider a variant / SQL_VARIANT / object, etc to be the "right" type ;-)


drawback of including primary key creation along in the table creation script Sample script:

CREATE TABLE [dbo].[Countries](
    [Id] [nvarchar](2) NOT NULL,
    [Name] [nvarchar](100) NULL
 CONSTRAINT [PK_dbo.Countries] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I was told by a DBA that I should not include the Primary Key  constraint in the table create script but I don't really understand.

Could anyone tell me why and what is the best practice in this situation (for example: create table first and add primary key later in alter table)?

Thanks,

Maybe he wants to load the table with data and then create the index after. That way index fragmentation and statistics would be in good shape.

With that said a good choice of clustering key would keep fragmentation low during the data load. Kimberly Tripp has written a few posts on topic of choosing appropriate clustering keys.


Need help with Regex in SQL Server query editior I have a file with thousand lines of codes with index details like below:

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))
BEGIN
 CREATE UNIQUE INDEX [IX_Test1] ON [Test1]
(
[cd] ASC
)
 WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]
END

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))
BEGIN
 CREATE INDEX [IX_Test2] ON [Test2]
(
[AdID] ASC , [Date] ASC
)  INCLUDE ( [AID], [LM] )
 WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]
END

I want to format this data like below for comparison purposes:

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test1' AND [object_id] = OBJECT_ID(N'Test1', N'U'))  BEGIN   CREATE UNIQUE INDEX [IX_Test1] ON [Test1]  (  [cd] ASC   )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,  DROP_EXISTING = OFF ) ON [PRIMARY]  END
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WITH (NOLOCK) WHERE name = 'IX_Test2' AND [object_id] = OBJECT_ID(N'Test2', N'U'))  BEGIN   CREATE INDEX [IX_Test2] ON [Test2]  (  [AdID] ASC , [Date] ASC   )  INCLUDE ( [AID], [LM] )    WITH (FILLFACTOR = 90,  IGNORE_DUP_KEY = OFF,  PAD_INDEX = OFF,  ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON,  STATISTICS_NORECOMPUTE = OFF,   DROP_EXISTING = OFF ) ON [PRIMARY]  END

So basically everything between IF and END should come as one line. I tried to do it using REGEX but didn't get it work. If someone could help me in this regard, it would be of great help.

Please let me know if you need any further details.

If you are willing to do this in two step, here is an approach that works, using the Regex option in SSMS's Replace dialog.

Step 1

Delete all line endings, squashing it all into a single line.

Replace: \r\n

With:

Explanation: The \r\n looks for a line ending, which is almost certainly a Carriage Return (\r) followed by a New Line (\n). Replace it with nothing.

Step 2

Replace all occurrences of "END" with "END" plus a new line ending.

Replace: END

With: END\r\n

Explanation: Replace "END" with "END" plus a carriage return plus a new line.

Oddity

Your example above has two spaces after the first "END", and no spaces after the second one. This will cause the results after Step 2 to look something like this:

IF NOT EXISTS...
  IF NOT EXISTS...

If your real code also has that oddity, you can fix that by ensuring that you don't have spaces after "END", with a Step 0 of:

Replace: END (two spaces after "END")

With: END (no spaces after "END")

Tested in SSMS 17.9.1.


mySQL trigger to prevent insert won't work The problem has been discussed before but the answers won't work in my case.

I have a table in my database which contains a numeric column called "value".

I would like to prevent an insert if the new.value is close to the value of a previous insert. So here is the main part of the BEFORE trigger:

first try:

IF EXISTS(SELECT * FROM mytable WHERE ABS(new.value - value) <= 1) THEN
SET new.value = null;
END IF

Result: for one insert, it will throw an error and it will work as expected. However, for multiple inserts at once, the inserts that should be prevented are there, with value 0.

second try:

IF EXISTS(SELECT * FROM mytable WHERE ABS(new.value - value) <= 1) THEN
SIGNAL SQLSTATE '45000';
END IF

Result: again, for one insert, it works as expected. However, for multiple inserts at once, it will also prevent the inserts that should be inserted.

I hope I was clear enough. Thank you in advance!

Change EXISTS( SELECT ... ) to EXISTS( SELECT ... FOR UPDATE ).

(Caveat:  I am guessing that this may solve it.)


Append and add information from rows above the title is not really helpful. Sorry. I better explain with code. I need a query in MySQL for the following problem:

Build schema:

CREATE TABLE AsIs
(
`PN` VARCHAR(10),
`Origin`VARCHAR(10),
`Sub`VARCHAR(10)
);

INSERT INTO AsIs
VALUES ('11111', 'List1', '22222'), ('98765', 'List2', '123456');

I create a table with the column subs:

CREATE TEMPORARY TABLE `Subs` SELECT  `sub` AS `PN` FROM AsIs;

I create a table to union the values from table AsIs and table  subs :

CREATE TEMPORARY TABLE `UnionAsISwithSubs`
SELECT `PN` FROM AsIs UNION SELECT `PN` FROM `subs`;

And finally I create the last table to add the information from the AsIs table:

`CREATE TABLE `FinalList`
SELECT `UnionAsISwithSubs`.`PN`,`AsIs`.`Origin`,`AsIs`.`sub`
FROM `UnionAsISwithSubs`
LEFT JOIN AsIs ON `UnionAsISwithSubs`.`PN` = `AsIs`.`PN;

There are for sure better ways to do this, but my main question is, how can i update the column 'Origin` with the values from the row above. So it should like this:

CREATE TABLE ShouldBe Like AsIs;
INSERT INTO ShouldBe
VALUES('11111', 'List1', '22222'),('98765', 'List2', '123456'),
('22222', 'List1', ''),('123456', 'List2', '');

Many thanks in advance for your help.

I have simulate it with SQL fiddle. Please follow the link:

http://sqlfiddle.com/#!9/dd792/4

You can turn your AsIs table into a row set like the ShouldBe table without any temporary tables, using a single query like this:

SELECT
  PN,
  Origin,
  Sub
FROM
  AsIs

UNION ALL

SELECT
  Sub,
  Origin,
  ''
FROM
  AsIs
;

The first half of the query just lists the current contents of the AsIs table. The second half appends the same rows but this time putting the Sub column at the same position as PN in the first query and specifying an empty string in place of the original Sub.

The column names of the final resulting set are defined by the names that the first SELECT is returning. So, Sub values of the second SELECT will be returned as PN in the output and the empty strings as Sub – that is, for your example the output will look like this:

PN      Origin  Sub
------  -----   ------
11111   List1   22222
98765   List2   123456
22222   List1
123456  List2


set serializable isolation for update query postgresql --9.4 CREATE TABLE xtable(xtableid integer, xcolumn integer)
CREATE TABLE ytable(ytableid integer, ycolumn integer)

CREATE OR REPLACE FUNCTION  foofunction(integer, integer)
RETURNS void AS $$
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE xtable SET xcolumn = $1 WHERE xtableid = (SELECT ycolumn FROM ytable WHERE ytableid = $2);
End $$ LANGUAGE plpgsql;

I want to set SERIALIZABLE isolation for the UPDATE and SELECT query inside but I get an error saying

ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"

How can I use this isolation level in PostgreSQL?  I don't have a live issue, just want to understand how it works.

A function takes part of an existing transaction. To avoid this context error, the transaction mode should be changed before the call to the function. Therefore you must remove the SET TRANSACTION statement function, and then use the code (for example):

Using your example...

function code -

--Atomically, using your example
CREATE OR REPLACE FUNCTION  foofunction(integer, integer)
RETURNS void AS $$
BEGIN
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    UPDATE xtable SET xcolumn = $1 WHERE xtableid = (SELECT ycolumn FROM ytable WHERE ytableid = $2);
End $$ LANGUAGE plpgsql;

calling code to function -

    ...
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    SELECT FROM foofunction (1,1);
COMMIT;
    ...

Or yet:

CREATE OR REPLACE FUNCTION  foofunction(integer, integer)
RETURNS void AS $$
BEGIN
--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    UPDATE xtable SET xcolumn = $1 WHERE xtableid = (SELECT ycolumn FROM ytable WHERE ytableid = $2);
EXCEPTION WHEN others THEN
    ROLLBACK;
RETURN;
    COMMIT;
End $$ LANGUAGE plpgsql;

And

...
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT FROM foofunction (1,1);
...

I hope have contributed!


Created a Materialized View in ORACLE which won't refresh I created a materialized view log along with a materialized view  called update_nboe based on table NBOE_ EMPLOYEES_TEST  using the following code

CREATE MATERIALIZED VIEW LOG ON NBOE_EMPLOYEES_TEST WITH primary key;

CREATE MATERIALIZED VIEW update_nboe
REFRESH FAST ON DEMAND
AS
SELECT E.EMP_ID, E.USERNAME ,E.NAME, E.LOCATION , E.TITLE, E.LOCATION_CODE, E.RS_GROUP
FROM NBOE_EMPLOYEES_TEST E;

Then I updated NBOE_EMPLOYEES_TEST by inserting additional records hoping that the materialized view would update and refresh on-demand after using the following piece of code

exec dbms_mview.refresh('update_nboe',atomic_refresh_test=>TRUE);

However, I see a red cross on my connections panel for the materialized view and it won't refresh either.

Would appreciate some input.

We normally use jobs to automate MV refreshes, so you don't have to do them manually or on demand.

For example, if you want your MV to refresh every 4 hours, then you would submit a job like this.  Connect as the schema owner of the MV.

VARIABLE jobno NUMBER
BEGIN
   DBMS_JOB.SUBMIT
      (job  => :jobno,
      what => 'begin DBMS_MVIEW.REFRESH(''UPDATE_NBOE'',''C''); end;',
      next_date => SYSDATE,
      interval => 'trunc(SYSDATE+1) + 04/24');
   COMMIT;
END;
/
PRINT jobno

The C denotes a complete refresh and you can use F for a fast refresh or check the docs for other possible parameters.  I don't know why you chose atomic_refresh_test; this does not seem to be something you need.

Sometimes the jobs break and you would have to run the job again after refreshing the MV manually.  This you can check from DBA_JOBS.  This is code that checks for broken MV jobs and outputs the commands to fix them. Connect as the owner of the MV then run this code:

set serveroutput on size 1000000
declare
v_mview VARCHAR2(30);
v_run  VARCHAR2(100);
v_job number;
cursor user_mviews_c is
select mview_name from user_mviews ;
cursor user_jobs_c is
select job from user_jobs ;
begin
open user_mviews_c;
loop
   fetch user_mviews_c into v_mview;
   exit when user_mviews_c%notfound;
   v_run := 'exec DBMS_MVIEW.REFRESH ('''||v_mview||''',''C'');';
   dbms_output.put_line(v_run);
end loop;
close user_mviews_c;
open user_jobs_c;
loop
   fetch user_jobs_c into v_job;
   exit when user_jobs_c%notfound;
   v_run := 'exec DBMS_JOB.RUN ('||v_job||');';
   dbms_output.put_line(v_run);
end loop;
close user_jobs_c;
end;
/


Read-only database: Normalize or not for best query performace I have a pandas DataFrame that looks a bit like this:
         id        name       date     col1     col2  total
0 123456748 EXAMPLENAME 2020-09-01 SOMEDATA MOREDATA   5.99
1 123456748 EXAMPLENAME 2020-09-01 SOMEDATA OTHERDATA 25.99

There are 15 columns, the name values are associated with the ID, and the rest is some data relevant for that person. col2 would have about 400 unique values. The database would be about 300,000,000 rows to start with, and then will grow at about 500,000 records per week.
The records in the database will never be updated or deleted, only new ones will be added. Final purpose of the database is to have a web app in which the user can select the ID of the person. The database would retrieve the information, and the website would render a graph and a dataframe. The expected traffic of the website is very low, so I was thinking about using SQLite.
Based on that, I have two questions:

Should I use Relational Databases, like PostgreSQL or SQLite, or should I try MongoDB? I'm interest on the performance of the database to select and retrieve the data; don't care too much about insert time as it won't be done very often (once per week).
Based on performance to query, in case you select Relational Databases, should I have all data in one table or should I split it (normalize it)? I read that normalizing a database when its purpose is only to query and store the data, could lead to worse performance than having it all in one table. However, I do not know much about databases and would prefer an expert opinion, or resources to learn more about the correct implementation and maintenance.

Thanks.

All things being equal, a normalized database will provide better performance for a wide range of queries.  It is a myth that a normalized database will be slow, especially in an analytic environment or with a large number of rows.  Usually if performance suffers, it is because a database has not been properly normalized.
A normalized database also provides for better data integrity which, depending on your industry and whether or not you are storing data related to payments, may be necessary.
However, if you're just looking to dump data, not perform any sort of transforms, always return the exact type of data, and use the same access path you probably don't need to spend as much time normalizing the data to meet your requirements.
But it would still make sense to use a relational engine that features B-Trees (and preferably clustered indexes/index organized tables) to speed data recovery.  Some NoSQL engines support methods to quickly access single records (Hash maps, occasionally B-Trees), but they won't offer the flexibility of a good RDBMS (not SQLite).
Another thing to consider is staying power/support.  Many database engines have existed in a mature form for 30+ years and have existing tools developed to assist with backups/ETL/maintenance, a knowledge base, developers experienced in their use, and some assurance the worst bugs have been worked out (MongoDb was awful in that regard early on).
From comment:

I am storing payments. Why does a normalized database provides better
data integrity?

So it depends on whether you are handling payments in real time versus storing an archive history, but generally a relational database is preferable because:

Transactions can enforce complex operations are performed in a way that keeps the database consistent.
All operations against the database are logged, so changes made between backups are auditable.
With the right primary key definition, uniqueness is guaranteed and all totals/calculations performed will be consistent.
With the right clustered index and/or partitioning scheme, aggregation is very fast.


Need to find a pattern in SQL Data base and then write a query to delete the duplicate "NULL" Value I have read a lots of stuffs regarding this but cannot find solution to this problem. Please some one have a look into it.

I have a unique data-base, which contain Row-number, Year, Practice, office Statistics like details in different columns. Please find the below screen shot of it.

Here is my problem.

I need to search if the practice is unique('A', 'B'), then we don't need to do any change in the SQL Database server. However, if you find a repetitive value like in practice 'C'. Then we need to remove the Whole row from SQL database, if the Q1 , Q2 Q3, Q4 , YTD, and avg value is "NULL".

I want to remove the NULL Value if you have duplicate practice name if Q1 , Q2 Q3, Q4 , YTD, and avg value is "NULL".

Assuming your table name is tab1, this should work.

delete tab1
from tab1 t
join (select practice from tab1
        group by practice
        having count(1)>1) sub
    on t.practice=sub.practice
where q1 is null
    and q2 is null
    and q3 is null
    and q4 is null
    and ytd is null
    and avg is null


LEFT JOIN LATERAL conditions and JSONB I have a JSONB column (jb_column) that may have a key (let's call it schrodinger) or not. When the schrodinger key is present, it's value can either be null::JSONB or a regular JSONB object.

First, a table:

CREATE TABLE my_table
(
    id INTEGER NOT NULL,
    jb_column JSONB
);

The structure I'm trying to extract from the schrodinger column:

CREATE TYPE schrodinger AS
(
    foo NUMERIC,
    bar TEXT
);

And this was my initial approach:

SELECT mt.id, s.*
FROM my_table mt
LEFT JOIN LATERAL jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger') s

But this resulted in the annoying error below:

[22023] ERROR: cannot call populate_composite on a scalar

After a couple of hours trying to debug it, I nailed it down to the null::JSONP values of the schrodinger key. So, my next attempt was to add some criteria to the JOIN, so that PG could ignore those...

SELECT mt.id, s.*
FROM my_table mt
LEFT JOIN LATERAL jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger') s
          ON jsonb_typeof(mt.jp_column -> 'schrodinger') != 'null'

I'd expect this condition to skip the JOIN when both the key is not present as well as when it's there, but null::JSONP... Unfortunately that doesn't seem to be working, since I still get the same error.

I also tried jsonb_typeof(mt.jp_column -> 'schrodinger') = 'object', to no avail.

Thoughts?

edit: online example https://dbfiddle.uk/?rdbms=postgres_11&fiddle=66c90482a33aa458c6a90e1bbaa1ec9d

jsonb_populate_record can't be used like a table as it only returns a scalar values.

I think you want

select id, (jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger')).*
from my_table mt

If the json value doesn't contain the key schrodinger then this will automatically return null

Online example


SSIS vs T-SQL Join Performance, Difference Databases on Same server We are researching the performance difference between SSIS Lookup and T-SQL Join. We want to join two tables; tables are in the same SQL server instance, different databases.

I suspect for small table joins, the difference is minimal or negligible. In this case, our team prefers T-SQL, easier to code/script than writing diagrams. Additionally in the DevOps perspective, we can compile/build scripts in a DB project;  unfortunately, SSIS will not compile T-SQL correctly, I can write 'testabcd' in an SSIS Execute SQL statement, and the project will still build/compile.

However, for a large number of rows which will take longer processing, what is quicker? T-SQL which has indexes and statistics,  or SSIS which is conducted all in memory?

I read these articles with different viewpoints, the team is trying to gain consensus.

https://derekdb.wordpress.com/2012/03/13/ssis-lookup-or-t-sql-join/
http://www.sqlservercentral.com/blogs/jamesserra/2011/08/29/when-to-use-t_2D00_sql-or-ssis-for-etl/

Let's assume, T-SQL Engine and SSIS are provided same hardware: CPU and Memory. Given the same specs, I would like to know performance speed on an internal algorithm perspective.

Given the same specs, would like to know performance

If your data is on a single SQL Server instance, TSQL joins should always be faster than an SSIS lookup.  This is not really close.  A TSQL query will leverage indexes and statistics, and the cost-based query optimizer.  The join will be performed inside the SQL Server instance, where it is not only more efficient, but it has greater memory and disk resources than an SSIS pipeline.

SSIS lookups are primarily intended for destination-side lookups, heterogenous data scenarios, and for sources without a query processing engine (like flat files).

Further, when your data source is a single SQL Server instance, and your destination is a single (possibly different) SQL Server instance, personally I would almost never use any SSIS data flow transformations.  Instead always using source-system SQL for extract-side transformation, and stage-and-merge for destination-side transformation.


I'm trying to use a variable in Oracle PL/SQL, but I'm getting an error. What's wrong here? I come from an SQL Server background and I'm trying to figure out how to use Oracle PL/SQL variables.  I'm trying to declare a variable and then select that variable.  It sounds simple, but I am doing something wrong.  Here's my code:

declare
    num number;
begin
    num := 64;
    select num from dual; --error here
end;
/

This gives me an error message that says: "PLS-00428: an INTO clause is expected in this SELECT statement".

I tried a few other permutations of this but each gives me an error:

declare
    num number;
begin
    num := 64;
end;
select num from dual; --"num" invalid identifier
/

And this one also gives me an error.

declare
    num number;
begin
    num := 64;
end;
/
select num from dual; --"num" invalid identifier

Based on Mihai's comment, I tried putting the declare after the begin, but that threw errors as well:

begin
declare
    num number;
    num := 64; --encountered symbol "=" when expecting ...
end;
/
select num from dual; --"num" invalid identifier

For reference: here's how I would do this in SQL Server.  It may not be clear, but the scope of the @num variable below is the file containing the declaration.  This variable is not global.

declare @num int = 64
select @num

I am doing this is DBArtisan.  Note that this question is similar to this one here, but I'm also asking how to use the variable.  And I'm not interested in bind variables (I don't think).  I want to write one-off queries like this: select * from employee where manager_id = mgrID with the mgrID being a variable that I declare.

I think these answers are all getting a) way too complicated, and b) off-track relative to what user2023861 wants to accomplish:

"I am trying to declare a variable, then select that variable." means two different things in either PL/SQL or in SQLplus, and perhaps two more things in DBArtisan.   To accomplish the objective in SQLplus, use "Substitution variables"...excellent background is found here.

My example, doesn't precisely follow your SQL Server T-SQL example, but it's close and is NOT procedural (read: no anonymous PL/SQL block.)

PL/SQL with Substitution variables:

DEFINE min_salary = 1000
DEFINE mgrID = 7

select *
from employees
where
    salary > &min_salary
    and mgrID = &mgrID
;

All at the SQL-Plus prompt....from my fictional employees table, I got this result:

    SALARY      MGRID
---------- ----------
      2000          7
      1001          7
2 row selected.

If you want to make this semi-procedural, then you use BIND Variables and the declarative syntax of PL/SQL similar to T-SQL. (In my opinion this is a HUGE waste of effort, but is included to help clarify variable types.)

SQLPlus using PL/SQL Bind variables:

 VARIABLE   v_in_min_salary number;
 VARIABLE   v_mgrID         number;    /* vars are defined within SQL+ */
 VARIABLE   v_out_salary    number;
 VARIABLE   v_out_mgrID     number;

BEGIN
    :v_in_min_salary    := 1000;   /* vars are assigned values in PL/SQL */
    :v_mgrID            := 7;

 select salary, mgrID
  into :v_out_salary, :v_out_mgrID
  from employees
  where salary > :v_in_min_salary and mgrID = :v_mgrID
 ;

end;
/

PRINT :v_in_min_salary   /* here the in and out vars are displayed  */
PRINT :v_mgrID
PRINT :v_out_salary
PRINT :v_out_mgrID

Fully-procedural approach using PL/SQL:

set serveroutput on;  /* environment setting within SQLplus to see output */
DECLARE
   v_in_min_salary number   := 1000;
   v_mgrID    number        :=7;
   v_out_salary number;
   v_out_mgrID    number;

BEGIN
  SELECT salary, mgrID
    into :v_out_salary, :v_out_mgrID
    from employees
    where salary > :v_in_min_salary and mgrID = :v_mgrID
  ;
  DBMS_OUTPUT.PUT_LINE('Salary Output is= '||:v_out_salary);
  DBMS_OUTPUT.PUT_LINE('Manager ID = '||:v_out_mgrID);
END;
/

So, when do I use each case?  I use SQLplus Substitution variables 75% of the time when I write SQL scripts that I want to parameterize the input for. Executing a script at the SQLplus prompt allows passing inbound arguments, so my favorite Substitution variables are &1 and &2 as in the following example:

   @get_table_size.sql <schema> <table_name>
        which in practice becomes
   @get_table_size jason employees

and executes SQL code similar to

SELECT extents
  FROM all_tables
  WHERE UPPER(table_owner) = upper('&1')  /* inbound first argument */
  and UPPER(table_name) = upper('&2')     /* inbound 2nd argument   */
;

Notice how the inbound arguments are assigned into substitution variables, then used in the SQL statement.  I never use the middle example, and I always use the 3rd examples (procedural PL/SQL) when writing functions, procs, and packages.


How can I reduce the Tuple Mover's disk I/O impact for mergeouts? I have a cluster with a constant trickle load of new data fed by a stream of large batch COPY statements and also simultaneously support interactive read-queries against the most recently added data, i.e. time series for chart data.

How can I control the Tuple Mover so that it spreads out its work over a longer period of time, to reduce the insane disk I/O it periodically causes which slows down read-query performance to horrible levels.  This data is partitioned on a date column, so every 24 hours precisely (GMT) there is a flurry of disk I/O due to partition mini-ROS to ROS consolidation.  I've set ActivePartitions to 2, long ago, which helped reduce the problem but it's still a significant slowdown while the mergout is hogging the spindles.

It seems like the resource pool settings ought to help, but I can't find a setting that has an effect.  Any suggestions?

What you need to do is make sense of what is your usual load size and set the size of your TM resource pool accordingly.
  Changing the  ActivePartitions to 2 will help you because will stop looking for older partitions to Consolidate.

Answer me this questions :

What is the size of your TM pool ?
How do you do your Trickle loads ? (they go to WOS or ROS)
How many partitions do you have in the table ?
How many projections do you have for that table ? (do you use them
all)
Do you run delete on this data ?(check for replay delete)
Your projection have the default sort ? (all columns or specific
columns ?)

What is the value of MaxMrgOutROSSizeMB and MoveOutSizePct,PurgeMergeoutPercent.

Better off can you post the definition of your TM ?


Performing large amounts of schema updates and transaction log issues We have an issue with growing transaction logs hitting disk size limits

When ever we upgrade our software on a client's site. our upgrade installer runs a number of scripts to add columns to tables and move data around.  once complete it will upgrade the web site that uses the db

The typical db can be of a moderate size 1GB - a few of the tables will contain 10's of millions of rows. nothing that should stress the server too much

We have very little control over our customers back up policy although we recommend full transactional logs backed up every 1hr. we cant guarentee this followed. And we have no control over when the customer runs the upgrade proccess.

Recently one of our scripts added a column to a table that typically has a large numbers of rows, 10 - 20 mil, the column had a default value so had to update all the rows.

For one customer this caused an already large transaction log to grow to the size of the disk. The script failed and at that point our installer stops

I have read a few articles regarding truncating or shrinking the log, running checkpoints , switching to simple mode.

I would like to avoid them needing to do any db maintenance before upgrading as most of them know little about it.

Does any one have a best practice to follow when running these kind of upgrade schema scripts.

The DB is MSSQL

Any help would be much appreciated

=========================== UPDATE 1====================
I will recommend the following steps  for our upgrader

•   Take DB backup before upgrading begins

•   Start of the update scripts set the recovery mode to simple

•   Before running an update that may cause the issue run a ‘checkpoint’ and then shrink the transaction log

•   Run large updates in batches (10000 rows at a time)
o     For new columns with a default value create the column allowing nulls
o     Set the default value in batches
o     Add the default value and not null constraint after
o     Run a checkpoint between each batch so that log does not need to be kept (this will avoid the log growing too large) checkpoints run once a minute by default so may not be needed

•   After all updates have been run set the recovery mode to the original state (full)

•   Backup the database to restart the full log mode

Starting with SQL Server 2012 adding of not NULL column with default values to an existing table is metadata only operation.

From what you wrote it's clear that it's not your case, i.e. or your server is beyond 2012, or one of the following probably is your case:

the newly added column increases the maximum possible row size over
the 8060 bytes limit the column cannot be added online
your server Edition is not Enterprise

(you can find the complete list of restictions here: Online non-NULL with values column add in SQL Server 2012

So how you can resolve the issue?
If you cannot upgrade to at least 2012 or your Edition is not Enterprise, the only thing I can suggest you is to split this one big transaction to many transactions. This will permit you to clear the log (on checkpoint in simple recovery model or on a log backup in full / bulk logged).

So instead of adding not null column first add null column and then update it with your values in batches. So the log will have the possibility to clear between batches.


Remove duplicate records with timestamps I have a table in my DB where I keep track of user data. So I have rows like that:

(row_id,user_id, stat1,stat2,..., statN, timestamp)

Now I want to remove all duplicate records, where user_id and stat columns are the same, but only between other rows with different data. So if I have

(row_id, user_id, stat1, stat2, stat3, timestamp)
(1,1,1,0,1,1000)
(2,1,1,1,1,2000)
(3,1,1,1,1,3000)
(4,1,1,0,1,4000)

It should became

(1,1,1,0,1,1000)
(2,1,1,1,1,2000)
(4,1,1,0,1,4000)

And not

(1,1,1,0,1,1000)
(2,1,1,1,1,2000)

So I want to save record progression but remove meaningless duplicates.

So doing a join on the elements that constitute a duplicate and the last where criteria shows which to delete.

delete t1
from table t1, table t2
where t1.user_id = t2.user_id and t1.stat1=t2.stat1
     and t1.stat2=t2.stat2  and t1.stat3=t2.stat3
and t1.row_id+1=t2.row_id


MySQL - Partially restore binary log I have restored a dump into MySQL.  I also have a binary log, which I want to partially restore, let's say from position 10 - 2500.

How can I do that?

Use the mysqlbinlog utility

mysqlbinlog --help gives the following:

-j, --start-position=#
                  Start reading the binlog at position N. Applies to the
                  first binlog passed on the command line.
--stop-position=#   Stop reading the binlog at position N. Applies to the
                  last binlog passed on the command line.

Suppose your binary log is called mysql-bin.012345. You would do the following for positions 10 - 2500 to attach it to a mysqldump called MySQLData.sql:

Either

mysqlbinlog --start-position=10 --stop-position=2500 mysql-bin.012345 > SQLFromBinLog.sql
cat SQLFromBinLog.sql >> MySQLData.sql

or append directly with

mysqlbinlog --start-position=10 --stop-position=2500 mysql-bin.012345 >> MySQLData.sql

Give it a Try !!!


Sql Agent Jobs executes too slow I have posted a question like this before but is seems that I did not explain the whole story clear. So I made this one to explain it in details. Here's the stored procedure

USE [xxxx]
GO
/****** Object:  StoredProcedure [dbo].[MARK_ACTIVE]    Script Date: 4/20/2015 5:55:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[xxxx]
AS
SET ANSI_NULLS ON
INSERT INTO [123.456.7.890].[DBASE1].[dbo].[TABLE]      (smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX)
SELECT TOP 3 smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX
FROM [098.765.4.321].[[DBASE2].[dbo].[TABLE2] cr
WHERE
NOT EXISTS (SELECT  id,smsfr,smsmsg,smsdt,dbdt,devid,status,status2,IsHEX          FROM [123.456.7.890].[DBASE1].[dbo].[TABLE]  c
  WHERE cr.smsdt = c.smsdt)

and here's my job settings

My General

My Steps

My advance steps

My Scheduler

When I run the stored proc it executes for less than 10 seconds but when i try to call that stored proc using the job this is what happen

it just keep on in progress state but nothing happens, looks like it got stuck in that state.

If you are using Windows Authentication in the linked server setup it will not work using the SQL Server agent even if you have Kerberos correctly setup. You can configure the linked server to use SQL authentication for those connections but I would recommend to create a SSIS package for this as the authentication will be easier to configure.

Can you please post the linked server configuration for further information?


SQL Server 2005 on Windows 7 along with SQL Server 2008 R2 Express edition I've installed SQL Server 2008 R2 Express edition on my Win 7 notebook. Using since an Express edition this doesn't have SQL Server profiler enabled. Thus it's hard for me to debug my SQL Server 2005 applications.

I have a license for SQL Server 2005 (32 bit) and when I tried to install the client tools it gave a "known compatibility" error message. Is it possible to install SQL Server 2005 client tools (for 32 bit) only in the same machine?

Below is list of software on my machine

Windows 7
SQL Server 2008 R2 Express edition
Visual Studio 2010

Can any one advise.

thanks a zillion

I doubt you can use the SQL Server 2005 client tools for SQL Server 2008 R2. But if your main issue is the inability to use profiler, I suggest just running server-side traces instead. You don't get the fancy GUI, but you can still see all the same data.

Running a trace without profiler: SQL Server Performance Statistics Using a Server Side Trace

Note, one thing you won't be able to do with server-side tracing is to replay a trace.


An alternative to querying with multiple JOIN over the same table? I have a Postgresql 11 database.
Let's say I have a table called houses. It should have hundreds of thousands of records.

CREATE TABLE houses (
  pkid serial primary key,
  address varchar(255) NOT NULL,
  rent float NOT NULL
);

Now, my houses have features I want to register in the database. As the list of possible features will be quite long (several dozens) and will evolve over time, as I don't want add  a long list of columns to the table houses and change the table constantly with 'ALTER TABLE', I thought of having a separate table for these features :

CREATE TABLE house_features (
   pkid serial primary key,
   house_pkid integer NOT NULL,
   feature_name varchar(255) NOT NULL,
   feature_value varchar(255)
);
CREATE INDEX ON house_features (feature_name, feature_value);
ALTER TABLE house_features ADD CONSTRAINT features_fk FOREIGN KEY (house_pkid) REFERENCES houses (pkid) ON DELETE CASCADE;

In average, each house record will have 10-20 records in the house_features table.

So far, this seems a simple efficient model : I can add as many different features, controlling the possible values of feature_name and feature_value in the upper layers (the applicative layer and/or the GUI). I don't have to alter the database each time the application evolves and I need a new type of feature.

For the example, let's say I have the following features :

feature_name : 'rooftype' with possible feature_value : 'flat' or 'inclined'
feature_name : 'wallcolors' with possible feature_value : 'white', 'beige', 'blue', 'green', etc.. (15 different possible values)
feature_name : 'has_basement' with possible feature_value : 'True' or 'False'.
feature_name : 'number_of_doors' with possible feature_value any integer coded as a string (so '0', '1', '2', ...).
feature_name : 'floor_surface' with possible feature_value any given float coded as a string (e.g.: '155.2')

Obviously, storing booleans, integers and floats as strings is not very efficient and this is also something I will need to take care of. I was thinking of having a separate house_features_XXX table for each XXX type (string, boolean, float, integer).

But that is not even my problem.

My problem is : how do I search for houses that have certain features ?

For the example, let's say I want to search the houses with a basement, white walls and an inclined rooftype.
I could dynamically create in the application layer a query like :

SELECT sq1.* FROM
( SELECT house_pkid FROM house_features WHERE feature_name = 'has_basement' AND feature_value = 'True' ) AS sq1
JOIN
( SELECT house_pkid FROM house_features WHERE feature_name = 'wallcolors' AND feature_value = 'white' ) AS sq2
ON sq1.house_pkid = sq2.house_pkid
JOIN
( SELECT house_pkid FROM house_features WHERE feature_name = 'rooftype' AND feature_value = 'inclined' ) AS sq3
ON sq1.house_pkid = sq3.house_pkid
;

But that seems not so efficient, especially considering that there may be several dozens of conditions on house_features.

Is there a better way to do this ?

You could try to aggregate the features into a JSON value, then searching for a combination of multiple features is quite easy:

select h.*, hf.features
from houses
  join (
    select house_id, jsonb_object_agg(feature_name, feature_value) as features
    from house_features
    group by house_id
  ) hf on hf.house_pkid = h.pkid
where hf.features @> '{"rooftype": "flat", "has_basement", "true", "wallcolors": "white"}';

Performance can be improved by adding a WHERE clause to the sub-select which repeats the feature names, e.g:

where feature_name in ('rooftype', 'has_basement', 'wallcolors')

or even

where (feature_name, feature_value) in (('rooftype', 'flat') ('has_basement', 'true'), ('wallcolors', 'white'))

The outer condition is still necessary, because the inner where will include houses that don't have all the features.

This also has the advantage (in my eyes) that you only get one row with all the features, rather then one row for each feature.

Unless you remove, add and change features for a house very frequently, storing them as a single JSONB column on the house table  (features) and getting rid of the house_features table, might be an alternative. In that case you could create an index on the column to speed up the search.


how to get estimated subtree cost? If I have a query that returns a query_plan, for instance like this:

    SELECT TOP 1000 st.TEXT
    ,cp.size_in_bytes
    ,cp.plan_handle
    ,QP.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
WHERE cp.objtype = N'Adhoc'
    AND cp.usecounts = 1

Then I can click on a query_plan and hover over the left most icon, where tip-text will list the Estimated Subtree Cost.

Is there a way of getting that Estimated Subtree Cost out as a separate column for my query?

I understand that the number is unit-less, and refers to a particular developers PC some 20 years ago. Even so, I think it might tell me how long the query should take if statistics are not too far out.

I've tried really hard to Google for this info, but even dba.stackexchange.com comes up empty.

I believe you will have to do some XML query work to get that estimated cost.

See if this is what you are looking for:

   ;WITH XMLNAMESPACES
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT TOP 1000 st.text
        ,cp.size_in_bytes
        ,cp.plan_handle
        ,QP.query_plan
        ,n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
    WHERE cp.objtype = N'Adhoc'
        AND cp.usecounts = 1
    OPTION(RECOMPILE);

This will return the cost for individual statements inside of a batch. You might need to work in some grouping if you need the total estimated subtree cost for the entire batch.


Why would you use a managed service account rather than a virtual account in SQL Server 2012? In SQL Server 2012, service accounts are created as virtual accounts (VAs), as described here, as opposed to managed service accounts (MSAs).

The important differences I can see for these, based on the descriptions:

MSAs are domain accounts, VAs are local accounts
MSAs use automagic password management handled by AD, VAs have no passwords
in a Kerberos context, MSAs register SPNs automatically, VAs do not

Are there any other differences?  If Kerberos is not in use, why would a DBA ever prefer an MSA?

UPDATE: Another user has noted a possible contradiction in the MS docs concerning VAs:

  The virtual account is auto-managed, and the virtual account can access the network
  in a domain environment.

versus

  Virtual accounts cannot be authenticated to a remote location. All virtual accounts
  use the permission of machine account. Provision the machine account in the format
  <domain_name>\<computer_name>$.

What is the "machine account"?  How/when/why does it get "provisioned"?  What is the difference between "accessing the network in a domain environment" and "authenticating to a remote location [in a domain environment]"?

Here's the way I see it.

When you use a VA, you impersonate the machine account.

The problem is, that it is easy to make a VA or use an existing one (ex. NT Authority\NETWORKSERVICE). If you grant the machine account access to an instance, an application that is running as a VA will be able to connect to that instance and perform actions.

With a managed account, you will have to provide the credentials for that account to whatever application wants to use them, allowing you more granularity with permissions.


Oracle Database Client 11g R2 Installation Types, which components? I need to know which component is under each installation type!

Instant Client
Administrator
Runtime
Custom (No need as it shows all the components)

ALSO?
does it includes ODP.NET? is it the component

  Oracle Data Provider for NET

if not then I need to have to install (ODAC 112040 Xcopy) after!

The public link below gives an overview of the four installation type options for the 11gR2 client:

http://docs.oracle.com/cd/E11882_01/install.112/e24322/install_overview.htm#LACLI1245

  Instant Client: Enables you to install only the shared libraries required by Oracle Call Interface (OCI), Oracle C++ Call Interface (OCCI), Pro*C, or Java database connectivity (JDBC) OCI applications. This installation type requires much less disk space than the other Oracle Client installation types.
  Administrator: Enables applications to connect to an Oracle Database instance on the local system or on a remote system. It also provides tools that enable you to administer Oracle Database.
  Runtime: Enables applications to connect to an Oracle Database instance on the local system or on a remote system.
  Custom: Enables you to select individual components from the list of Administrator and Runtime components.

A complete list of which individual components are installed by which Installation Type can be found on My Oracle Support, Doc ID [1397346.1].  It also includes the different components that are installed only on Microsoft Windows and not on other platforms.

Because My Oracle Support requires an Oracle account to login and access this information, I do not believe it is allowed to be posted on public forums.  However, if you already have a support account with Oracle, you can go to https://support.oracle.com to login and search for the Document ID posted above to reference the complete list of components included in each Installation Type as you requested.

EDIT:  Also, to answer your second question, yes, "Oracle Data Provider for .NET" is the ODP.NET component.


Postgresql: Streaming Replication - determining wal sender process client port My experience with asynchronous streaming replication has been great so far, both as a HA solution and as a backup strategy. One issue is delaying me from using streaming replication in production - The port used by the wal sender process seems to be determined automatically when I start the streaming replication (I assume between 32768 to 61000). I need to be able to specify the exact port in order to allow access. Does anyone have any information regarding this issue or even a workaround?
Thanks :)

The "wal sender process" is a client during streaming replication.  It connects to the standby servers port (5432?).  In order to allow access, you need to allow traffic from the senders (master) IP, to the standby's IP and port.  A simple firewall rule.  Normally these rules don't bother with the clients port number.


Separate indices for the field I want to get the MAX() and the ones in the WHERE clause My customer's database has a gigantic table (hundreds of millions of records) and a SELECT that, by cumulative use, is eating a huge chunk of processing time. Each exec takes almost a minute in average. (That's what AWR tells me, anyway.)

SELECT
    MAX(fldA)
FROM
    humongoustable
WHERE
    fldB = :B2 AND fldC = :B1

There's only one index involving the above fields, and it's on (fldC, fldA, fldB).

I suspect that isn't helping any, and there should be two separate nonunique indices; one for fldA and another for (fldB, fldC). Am I correct?

The index that will give you the most benefit is one on

fldB, fldC, fldA desc

or on

fldC, fldB, fldA desc

depending on your data. You'll need to check your data and keys to determine which column should go first.

The last column of the index is the column for which the maximum value is being computed. By including it in the index you will avoid table access. And by including the keyword desc it means that Oracle can use the index to calculate the maximum value -- it will be the first entry in the index for which the first two columns match.

In the ideal case, you will have many repeated values in the leading column of your index. In this case you might get some measurable improvement by using index key compression. In this case your create index statement will look something like

create index humongoustable_idx on humongoustable(fldB, fldC, fldA desc) compress 1;

Don't forget to refresh statistics on humongoustable after creating your new index!


script insert SQL Server 2005 from SQL Server 2014 Express I have a database in SQL Server 2014 Express, and I want to import it to SQL Server 2005.

I have generated the scripts to generate the database and to insert the data. All the script generated to be compatible with SQL Server 2005.

I have problems when inserting datetime. I get this error:

  Msg 8115, Level 16, State 8, Line 2
  Arithmetic overflow error converting numeric to data type Numeric.

The datetime exported from SQL Server 2014 is:

CAST(N'2018-10-22 00:00:00.000' AS DateTime)

YYYY-DD-MM works if I edited the query, but it’s a big database

Does anyone have an idea how to solve this?

You can change the client date format when you do the INSERT.

SET DATEFORMAT ymd

Run this as the first line in the script that inserts the data, and this will tell the client to interpret the date correctly as YEAR-MONTH-DATE as it is formatted in the script.


Can I Show Multiple "WHERE" Filtered Results In A Single Query/Output Table? I am using Postgres 9.2.4 on Red Hat 6.
Tables

---------------------------------------
|             Table 1                 |
---------------------------------------
| WorkflowID | DataSet | CreationTime |
---------------------------------------

------------------------------------
|             Table 2              |
------------------------------------
| WorkflowID | Data | CreationTime |
------------------------------------

Description
I'd like to be able to create a query which, for a list of records in Table 1, aggregates the WorkflowID COUNT/SUM(LENGTH(data)) from Table2.  That's easy enough with:
SELECT b.WorkflowID,
       COUNT(b.*),
       SUM(LENGTH(b.data)/1024.)::numeric(10,2) AS size_kb
FROM Table1 a
JOIN Table2 b
  ON (a.WorkflowID = b.WorkflowID)
GROUP BY b.WorkflowID
ORDER BY size_kb DESC;

Now, I'd like to compare these WorkflowID contributions before-and-after a given CreationTime and have it in a single query and output table.  Something like:

----------------------------------------------------------------------------
| WorkflowID | count_before | size_before_kb | count_after | size_after_kb |
----------------------------------------------------------------------------

My Problem
To my less-than-ideal understanding of SQL's mysteries, this looks like some kind of multi-WHERE clause type of query.  Does that even exist, or would I have to run multiple queries and then merge the output?

I think you are looking for conditional aggregation:

SELECT b.WorkflowID,
       count(case when a.creationtime <= timestamp '2015-06-10 14:00:00' then b.worflowid end) as count_before,
       sum(  case when a.creationtime <= timestamp '2015-06-10 14:00:00' then (LENGTH(b.data)/1024.)::numeric(10,2) end) as size_before
       count(case when a.creationtime > timestamp '2015-06-10 14:00:00' then b.worflowid end) as count_after,
       sum(  case when a.creationtime > timestamp '2015-06-10 14:00:00' then (LENGTH(b.data)/1024.)::numeric(10,2) end) as size_after
FROM Table1 a
JOIN Table2 b
  ON (a.WorkflowID = b.WorkflowID)
GROUP BY b.WorkflowID;


How can I set myself as owner of a database ? just using sql As I want to view dependencies of just a couple of tables in my clients database I want to set myself as the owner of the database.

If the database is called MyDatabase and my userName is MyDomain\MyUserName
How do I use the ALTER AUTHORIZATION statement to set myself as Owner ?

Check this links and references:
http://technet.microsoft.com/en-us/library/ms178630.aspx

Example:

EXEC sp_changedbowner 'Albert'

and ALTER AUTHORIZATION

ALTER AUTHORIZATION ON OBJECT::Parts.Sprockets TO MichikoOsada;
GO

http://technet.microsoft.com/en-us/library/ms187359(v=sql.105).aspx


How to see, if a foreign key is enforced? How can I see in IBM DB2, if a foreign key is in enforced Status?
In the systemviews REF_CONSTRAINTS and REFERENTIAL_CONSTRAINTS I see them, but I cannot see, if they are in enforced Status. Is there another System view, where I can see that?

Not sure what version of db2 you are using, but if it's LUW you can use SYSCAT.TABCONST (see column ENFORCED):

select enforced
from syscat.tabconst
where tabschema = ?
  and tabname = ?
  and constname = ?

FWIW, it is better to use the syscat views (vs sysibm), since they are more stable between versions.


My performance_schema.setup_consumers was not properly set.

By default performance_schema.events_statements_history_long is not enabled.

update setup_consumers set enabled="YES" where name="events_statements_history";
update setup_consumers set enabled="YES" where name="events_statements_history_long";


How can MongoDB elect a new primary when a datacenter has failed? I am curious about the following situation when setting up a MongoDB cluster.

2 Datacenters, 2 nodes in each data center for a total of a 4 node replicaset. One of the two datacenters has become unavailable for any reason, thus blacking out the primary and one of the secondaries.

Per fault tolerance section of https://docs.mongodb.com/manual/core/replica-set-architectures/: I need at minimum 3 votes to elect a new primary, but will only have 2 surviving voters.

I am unable to extend into a 3rd datacenter.

How can I setup the cluster to be able to withstand a datacenter failure and continue to write in the surviving datacenter?

You need another replica set member (or arbiter) in a 3rd data center. But if you cannot extend to a 3rd center, then add the 5th member of the replica set to your main data center. Your backup data center will remain with two members.

If backup data center goes down, main center will stay up and replica set operations will continue.

If main center goes down, replica set operations will be read only and you will need to perform manual intervention.

References:

Deploy a Geographically Redundant Replica Set

Replica Sets Distributed Across Two or More Data Centers


SQL Server avoid repeating the collation type multiple times in a query I have a query which selects rows from a source database (DatabaseA), and inserts them into a target database (Database B). The collation type differs between the databases and they cannot be changed. I need to address the collation difference in my query by explicitly specifying the collation for varchar fields.

Currently my query looks like this:

INSERT INTO DatabaseB.dbo.Users(
    Id,
    UserNumber,
    FirstName,
    Surname,
    Address1,
    Address2,
    AddressTown,
    AddressCity
)
SELECT
    Id,
    UserNumber,
    FirstName COLLATE SQL_Latin1_General_CI_AS,
    Surname COLLATE SQL_Latin1_General_CI_AS,
    Address1 COLLATE SQL_Latin1_General_CI_AS,
    Address2 COLLATE SQL_Latin1_General_CI_AS,
    AddressTown COLLATE SQL_Latin1_General_CI_AS,
    AddressCity COLLATE SQL_Latin1_General_CI_AS
FROM DatabaseA.dbo.Users

My question is, can I avoid typing the collation type for every string-based field? Is there a way that I can specify the collation type for the whole query at once? If this is not possible, are there any other shortcuts?

You can once create a view using column definitions like this:

FirstName COLLATE SQL_Latin1_General_CI_AS, ...

Then anytime you'd like to use User tables, you can use this view instead.


How to use SQL Web Service Endpoints for TSQL I'm really having a hard time finding information on how to use SQL Server Web Service (Endpoint for TSQL).  I found some articles on how to create endpoints which seems to be simple enough.  From my understanding, an SQL web Service listens on a specific port for TSQL (TSQL is the one that I want to try).  I haven't been able to find how to call this endpoint with a query or a stored procedure...

Is there something I'm missing? Any article or path I can take to understand how this works would be greatly appreciated.

Any help is greatly appreciated!
Thank you

The MSDN documentation for CREATE ENDPOINT notes the following:

  Native XML Web Services (SOAP/HTTP endpoints) was removed in SQL Server 2012.

So, unless you are using an unsupported version of SQL Server prior to SQL Server 2012, you cannot create an endpoint for http or xml.

If you are considering create a T-SQL endpoint, every SQL Server already has a T-SQL endpoint built in by default and there is absolutely no need to create another one. By default, the T-SQL endpoint listens on port 1433, although that is configurable.


Can somebody help me normalise this database/check my workings I am new to database design/normalisation and functional dependencies. I have started a new database class in school this week and we have been given a worksheet that requires the following:

This is a real simple design, and pets with the same type have the same details just to keep things nice and simple.

show the un-normalised structures
identify the primary keys on these un-normalised structures
specify function dependencies between attributes
bring structures up to 3NF showing workings

These are the tables:

My un-normalised structures are as follows:

person(PersonName, Age, Height, Weight, Pets, Sex)
Pet(petType, petName, Age, Colour, Sex, Alive)

Bold shows the primary keys.

Functional dependencies:

personName -> {Age, Height, Weight, Pets, Sex)
petType -> {petName, Age, Colour, Sex, Alive}

I cant think of any more (if any).

Now bring to 3NF.

I can get it to 1NF (i think)

So now I have:

Person(PersonName, Age, Height, Weight,Sex)
Pet(PetType, PetName, Age, Colour, Sex, Alive)

PersonsPet(PersonName*, petType*)

Then I have no idea where to go from here? That is assuming I have been correct until this point, which I most probably have not.

Can somebody help me and point me in the right direction?

You need a one-to-many table to list the types of pets that a person has, like

create table people_pet_types (
  person_name varchar(...) references people(name),
  pet_type varchar(...),

  primary key (person_name, pet_type)
);

Assuming name is the primary key of People (which it shouldn't be, else you can't have two people with the same name).

This allows a person to have many pets types.

Also, "Age" and "Alive" should probably be derived from date_of_birth and date_of_death (nullable). Otherwise, you need to update the table to re-calculate the age at least every year. You can use a VIEW to calculate the age, and alive values for you.


SSIS Script Task doesn't appear to run when scheduled I have a script task inside SSIS which does not appear to run when the job is triggered from SQL Server Agent.  I say does not appear to run because I don't know if it's the SQL Script task or some task preceding it.

The basic goal of this part of the ETL process is to group all files downloaded and process them one set at a time (5 files per set).

The GetFullSourcePath SQL Task outputs an Object data type (Return Type = full result set).  Inside the Copy Source Files ForEach Loop Container, I convert that to a string using the SQL Script task and then use a File System task to move the file from Inbound to ToDo for processing.

The SQL Script is a very simple C# operation converting an Object data type to a string.  It has 1 input and 1 output variable. However, the output value is never populated which means the next step of the ETL job cannot locate the source file.

    public void Main()
    {
        //Dts.Variables["User::SourceFile"].Value = Dts.Variables["User::FullSourcePath"].ToString();

        object myObjectVar = Dts.Variables["FullSourcePath"].Value;
        string myString = (string)myObjectVar;
        Dts.Variables["SourceFile"].Value = myString;

        Dts.TaskResult = (int)ScriptResults.Success;
    }

When I view the execution report, the script has no errors.
I'm using Visual Studio 2015 Enterprise, SSDT BI Preview (December 2015 release), and SQL Server 2014.  It works fine if I run the package from VS.

The error I get is can't find "somefile.txt". The read/write variable which is also the output variable of the script task is defaulted to "somefile.txt" in the package variables. This made me assume the script task was not working but equally it could be something else. I'll dig a little deeper in the execution logs to be sure.

I've resolved this. I installed the SSDT Feb update and targeted the version to SQL Server 2014, as suggested by Dave. I did have to tweak one script (unrelated package and script task) but then I managed to build and deploy. It's processing happily.
The Pre/Post Execution messages were all clean, but based on the advice on this question, I did analyse them in more detail so I've picked up a bit of knowledge here too!
Thanks for all the feedback, especially the comments from billinkc:

Look at the Execution Log for the package that has run on the server. My guess is that you will eventually discover there is a permissions issue but at the moment, it could be a number of places.
Specifically, what I'd look at is the Pre/Post execute entries in the Execution report. that would indicate whether the Script task ever fired. If there's no pre/post, then look for informational events related to your Foreach Loop Containers.
If a permission issue prevents the FELC from seeing a directory, it does not error out. Instead, it fails gracefully, which trips up 99.999% of the population
If it is a validation issue (somefile.txt doesn't exist when the package begins b/c it determines what the current file is), then you will want to set the DelayValidation property to true on the Connection Manager and any tasks that reference said Connection Manager.


CHECKDB problem - SQL files (mdfs,ldfs etc.) in Azure Files Anybody out there using Azure Files (https://docs.microsoft.com/en-us/azure/storage/files/storage-files-introduction) for SQL data and log file storage? I've got one SQL Server using an AF share in this manner and am having issues running CHECKDB on its databases. Has anyone out there used Azure Files for SQL stuff and, if so, have you run into the following problem?

When attempting to run CHECKDB on master:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked.
See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

When attempting to run CHECKDB on user database:

DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.

DBCC CHECKDB uses a Database Snapshot by default.  Database Snapshots are an NTFS feature.  When storing the database on an SMB share, they aren't available.  Try specifying the TABLOCK option for DBCC CHECKDB.

And Azure Premium Files are intended to support SQL Server database file storage.   In addition to providing shared storage for clusters, they allow you to share storage resources among SQL VMs.  Before the release of Premium Files, Azure Files was not really recommended for SQL Server database storage.

When Azure Shared Disks are GA they may provide a better option for shared storage, but currently the only two options are Storage Spaces Direct, and Azure Premium Files.


Postgres CTE execution guarantees, inlining Following query with non deterministic function yields two same rows - the CTE is computed and re-used.

WITH foo AS (SELECT uuid_generate_v4() AS id)
SELECT id FROM foo
UNION ALL
SELECT id FROM foo

| id
| 741f4f69-416b-4b4c-9226-559527f4a84e
| 741f4f69-416b-4b4c-9226-559527f4a84e

Is this execution guaranteed? Can postgres decide to inline it to

SELECT id FROM (SELECT uuid_generate_v4() AS id) t
UNION ALL
SELECT id FROM (SELECT uuid_generate_v4() AS id) t

| id
| 262f0006-b4d5-440a-a86a-d79cd2684458
| c24b5835-9c5c-4d6f-b649-8e510129015b

which yields two different ids (and thus different result)?

What does the SQL standard specify? Does Postgres provide any additional guarantees? If it's not guaranteed, what is standard way to reuse statement that depends on non deterministic procedures?

Yes, this execution guaranteed . Postgres will not inline it in the way you mentioned.

  A useful property of WITH queries is that they are evaluated only once
  per execution of the parent query, even if they are referred to more
  than once by the parent query or sibling WITH queries. Thus, expensive
  calculations that are needed in multiple places can be placed within a
  WITH query to avoid redundant work. Another possible application is to
  prevent unwanted multiple evaluations of functions with side-effects.
   (from Postgres doc


Add a column with name as a result from a SELECT I'm using PostgreSQL on my project and I have a Spring Boot Application that uses Flyway to create everything on my Database. I need to create a column that its type is based in a row on other table.
E.g.:
ALTER TABLE NAMES ADD COLUMN (SELECT n.name FROM NAMES AS n WHERE n.name = 'Tom') VARCHAR(255) NULL;

Is this possible?
Can I create a function to do the select work then just call it during the Alter Table code?
Context:
If you want to know why I need this, I have lots of clients that uses geometry types and each one has a different EPSG, I need to create the "geom" column based on the client's geometry so, I want to make a select based on my client's name to get its geometry type to then create the column with that type.
EDIT:
I think I got something near to what I need:
CREATE OR REPLACE FUNCTION client_schema.update_geom(my_table text, my_schema text, OUT alter_result boolean)
    LANGUAGE plpgsql
    AS $function$
    declare client_projection varchar(255);
    declare db_name varchar(255);
    begin
        CREATE extension if not EXISTS dblink;
        select split_part(current_database(), '-', 1) into db_name;
        select c.projection_key from client_schema.dblink('dbname=adm-3','SELECT projection_key, context_name FROM "adm-3".public.clients as c ')
        AS c(projection_key text, context_name text) where c.context_name = db_name into client_projection;
        EXECUTE 'ALTER TABLE '
            || esquema::text || '.' || tabela::text
            || ' ALTER COLUMN geom type geometry(Polygon, split_part($1, '':'', 2))'
            INTO alter_result
            USING client_projection;
        END;
    $function$
;

The problem now is that he don't let me update the column's type!
The error:
SQL Error [42601]: ERROR: type modifiers must be simple constants or identifiers

Solved my problem with the following function:
CREATE OR REPLACE FUNCTION client_schema.update_geometry_type(my_table text, my_schema text, OUT update_result boolean)
LANGUAGE plpgsql
AS $function$
   declare new_projection varchar(255);
   declare my_database varchar(255);
   declare old_projection varchar(255);
   begin
   CREATE extension if not EXISTS dblink;
   select split_part(current_database(), '-', 1) into my_database;
   select c.projection_key from client_schema.dblink('dbname=adm-3','SELECT projection_key, client_name FROM "adm-3".public.client as c ')
   AS c(projection_key text, client_name text) where c.client_name = my_database into new_projection;
   select Find_SRID(my_schema, my_table, 'geom') into old_projection ;
   select split_part(new_projection , ':', 2) into new_projection;
   EXECUTE 'ALTER TABLE '
   || my_schema::text || '.' || my_table::text
   || ' ALTER COLUMN geom type geometry(Polygon, ' || new_projection ||') USING ST_Transform(geom, ''EPSG:' || old_projection || ''', ' || new_projection || ')';
   END;
$function$
;


ComputerNamePhysicalNetBIOS and "Current Host Server" shows different machines I have a SQL Server 2008 R2 instance on a two node (say names X and Y) Windows server cluster (Windows Server 2008 R2 Enterprise).

Server Manager on both nodes shows 'X' as the 'Current Host Server'.

However, when I run  ServerProperty('ComputerNamePhysicalNetBIOS') it returns 'Y' which should be the current active node.

If I RDP to the SQL's IP, it indeed connects to Y.

Which one is the correct one?
Why does Server Manager shows X as the active node?

Also, ServerProperty('MachineName') returns neither X nor Y but 'Z'. Z is the name of neither computers. What does it represent?

According to BOL for ComputerNamePhysicalNetBIOS (which returns Y as above):

  If the instance of SQL Server is in a failover cluster and you want to obtain the name of the failover clustered instance, use the MachineName property.

Then, why does it return Z?

These machines are not virtualized, they are physical. Does it mean Z is the virtual server name of the cluster? "current host server" seems to refer to the "Active Node in the cluster" (from an online forum). I am not entirely sure what is meant by that with two FCIs and one active on each node.

Windows Server Failover Clustering requires a minimum of two computers (Virtual Machines or Physical Machines).  Lets refer to these as Node01 and Node02.
Part of the install process for the Windows Server Failover Clustering role defines the name of the cluster.  For our purposes let's call that MyCluster.
Once you have created MyCluster, you can add SQL Server instances to the cluster as independent "cluster resource groups".  We'll refer to our SQL Server clustered instance as MyClusterSQL\INSTANCE.
So, to clarify, we have:
╔════════════════════════╦═══════════════════════╗
║          Item          ║         Names         ║
╠════════════════════════╬═══════════════════════╣
║ Computers              ║ Node01 and Node02     ║
║ Cluster Virtual Server ║ MyCluster             ║
║ SQL Server Instance    ║ MyClusterSQL\INSTANCE ║
╚════════════════════════╩═══════════════════════╝
Now, if you run SELECT SERVERPROPERTY('ComputerNamePhysicalNetbios') it will return either Node01 or Node02 depending on which computer is hosting the SQL Server Instance.
SELECT SERVERPROPERTY('MachineName'); will return MyClusterSQL.  The Microsoft Docs for SERVERPROPERTY says this about "MachineName":

Windows computer name on which the server instance is running.
For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

SELECT @@SERVERNAME; will return MyClusterSQL\INSTANCE.
Server Manager always returns the name of the computer you connect to.  In our case, this will be either Node01 or Node02.  If the SQL Server Clustered Instance is running on Node02, and you connect to it using Remote Desktop to the MyClusterSQL server, Server Manager will show Node02, even though you connected to MyClusterSQL.


Alter table column in production I want to alter a table column from CHAR(2) utf8mb4 to CHAR(2) latin1 (so it will use 2 bytes instead of 8, utf8 is not needed because they are country codes so there are only latin characters).

This table receives about 50 new rows per second.

There are about 125 million rows, total table size 5GB, so the alter will take a wile.

How can I alter the table without locking the writes?

Issue 1 - How to do the ALTER without blocking everything too badly.  Answer:  use pt-online-schema-change.

Issue 2 - Storage for char-like datatypes.

In the past, CHAR(n) always reserved n*c bytes, where c is the maximum size based on the CHARACTER SET: 1 for latin1, 3 for utf8, 4 for utf8mb4.  This was sometimes handy for Engine=MyISAM with FIXED sized rows.

In certain ROW_FORMATs of InnoDB in new enough versions (sorry, I don't have the specifics), CHAR(n) will occupy between n and n*c bytes, depending on the actual characters needed.  With InnoDB, there is no concept of fixed sized rows.

A complex SELECT that needs a temp table (say for ORDER BY), will try to use a MEMORY table for such.  In that case, the length of CHAR(n) and VARCHAR(n) will always be n*c.  This sometimes leads to an inefficiency.  Or it might use MyISAM for the tmp table.  Version 8.0 will be moving toward using InnoDB for tmp tables, so this paragraph will eventually be moot.

Converting CHAR to VARCHAR and/or converting the column from one CHARACTER SET to another requires a heavy-duty ALTER (not INPLACE).  See https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html  That link is for 5.6; there have been changes; pick the page for your version.

VARCHAR has 1 or 2 bytes for the length, plus only the number of bytes needed for the actual characters.  So it is 1+n*c for short max len.


MySQL 5.7 doesn't start after installation on CentOS 6.7 I have just installed MySQL 5.7 on a fresh CentOS 6.7(vm).

When I start the MySQL service with this command:

sudo service mysqld start

It doesn't start and raise this error:

  Initializing MySQL database: 2015-12-07T11:00:51.060114Z 0 [Warning]
  TIMESTAMP with implicit DEFAULT value is deprecated. Please use
  --explicit_defaults_for_timestamp server option (see documentation for more details). 2015-12-07T11:00:51.062305Z 0 [ERROR]
  --initialize specified but the data directory has files in it.
  Aborting. 2015-12-07T11:00:51.062347Z 0 [ERROR] Aborting [FAILED]

Do you know what is the problem?

I faced the same issue myself and I successfully resolved it.
What needs to be done is:
Open the my.conf file in vi editor and add a line under [mysqld]:

explicit_defaults_for_timestamp = 1

This will fix the first issue.
Now check the access rights of the location pointed by datadir field and make sure mysql can access it.
Worst case rename the location it points to. eg:

datadir=/var/lib/mysql1


Automatic database file creation based on database file size in SQL Server 2008 R2 Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. In this database data are logging in every seconds. Here i wants to maintain
the data file size as 4GB. If the size excited the limit then i wants to create one new data file under the same profile. Is there any possible options  available in SQL Server 2008 R2?.

First of all, in SQL Server, there is no automatically confiuration set up to automatically create or add new file to your existing file group when you reach the file size limitation. You have to do it on your own. But you can create script to do it automatically, even though it may not be exactly as you expected.

The logic to split data into different file by your own control is to create a new file group, add new file or add new file to existing file group. But, you have to create new table too.

For example, you have an existing table A. When you create it, you create it on FileGroup1, your FileGroup1 has two files, File1 and File2, you set the File1 and File2 to not autogrow, set them to fixed size 2GB. When you insert data into table A, you can NOT control insert data into which file, File1 or File2, which is controlled by SQL Engine, and because you set them to fixed size, so when both reaches to full, your insert query will throw exception. Certainly you can set them to auto growth. But this will not resolve your issue

What you can do is once table A is getting close to 4GB, create new table B first, after table B create successfully on new file group with new files, take a piece of idle time of table A, rename table A to table A archive and name table B as table A. In this way, the table change is transparent to your application layer.

How to know your table A file size is getting close to 4GB? You can use the following script to get current database file size information, including data file and log file. And you follow certain naming convention to create your FileGroup and Files, then you know what is your table's FileGroup and File name.

select Name,
(convert(float,size)) * (8192.0/1048576) File_Size,
(convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576) MB_Used,
((convert(float,size)) * (8192.0/1048576) - (convert(float,fileproperty(name,'SpaceUsed'))) * (8192.0/1048576)) MB_Free
from sysfiles

You can check your online table file size periodically, certainly set your file to autogrow, in case your periodically check and table switch fail, you will not lose data.
Once you decide it is time to create new table B, you first add new FileGroup and new file to database.

USE master
GO
ALTER DATABASE TEST
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'E:\MSSQL\DATA\TESTdat1.mdf',
    SIZE = 4000MB,
    MAXSIZE = 4000MB,
    FILEGROWTH = 500MB
)
TO FILEGROUP Test1FG1;
GO

After new FileGroup and new file created, you can create new table B

select *
into table B
from table A
where 1<>1

Certainly, you need to add your indices and constraints. Better to save your table DDL somewhere and get it when you need to create new table.
Last step is to find a right time spot to do table switch. Make sure do it in one transcation.

EXEC sp_rename 'Table A', 'Table A archive'
Exec sp_rename 'Table B', 'Table A'

*The methodolgy above is just one approach, it is definitely not the best approach. *
Other approach:

Control the table and file split in your application layer, once you identify the data write to current table reaches some threshold, create new table, basing on timestamp or some naming convention your application knows.
In store procedure layer to control table and file split, normally your application will call store procedure to read and write data, you can use store procedure to create new table, and let the application read and write from new table.
There are more


how do I check the last time dbreindex or an index defrag was executed Is there a quick tsql I can use to check when was the last time a dbreindex, index defrag and update stats was executed on the database?

Appreciate everyone's quick response.

Try one of the queries below and see if that is what you are looking for:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_system_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'YourDb')
AND OBJECT_ID=OBJECT_ID('YourTable')

select name as index_name,
         stats_date( object_id, index_id) as stats_update_date
from  sys.indexes
where object_id = object_id('YourTable');

Select *

From sys.dm_db_index_usage_stats


phpPgAdmin does not see databases When I go to databases in phpPgAdmin it says that there are 0 databases. Yet, if I run SELECT datname FROM pg_database it displays all databases OK. I would like to be able to 'explore' tables and databases.

I had to set the following line of the phpPgAdmin config file (which was duplicate in my config) to

$conf['owned_only'] = false;


How to hint many-to-many join in SQL Server? I have 3 "large" tables that join on a pair of columns (both ints).

Table1 has ~200 million rows
Table2 has ~1.5 million rows
Table3 has ~6 million rows

Each table has a clustered index on Key1, Key2, and then one more column. Key1 has low cardinality and is very skewed. It is always referenced in the WHERE clause. Key2 is never mentioned in the WHERE clause. Each join is many-to-many.

The problem is with cardinality estimation. The output estimation of each join gets smaller instead of larger. This results in final estimates of low hundreds when the actual result is well into the millions.

Is there any way for me to clue the CE into making better estimates?

SELECT 1
FROM Table1 t1
     JOIN Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

Solutions I have tried:

Creating multi-column stats on Key1, Key2
Creating tons of filtered stats on Key1 (This helps quite a bit, but I end up with thousands of user-created stats in the database.)

Masked execution plan (sorry for the bad masking)

In the case I'm looking at, the result has 9 million rows. The new CE estimates 180 rows; the legacy CE estimates 6100 rows.

Here's a reproducible example:

DROP TABLE IF EXISTS #Table1, #Table2, #Table3;
CREATE TABLE #Table1 (Key1 INT NOT NULL, Key2 INT NOT NULL, T1Key3 INT NOT NULL, CONSTRAINT pk_t1 PRIMARY KEY CLUSTERED (Key1, Key2, T1Key3));
CREATE TABLE #Table2 (Key1 INT NOT NULL, Key2 INT NOT NULL, T2Key3 INT NOT NULL, CONSTRAINT pk_t2 PRIMARY KEY CLUSTERED (Key1, Key2, T2Key3));
CREATE TABLE #Table3 (Key1 INT NOT NULL, Key2 INT NOT NULL, T3Key3 INT NOT NULL, CONSTRAINT pk_t3 PRIMARY KEY CLUSTERED (Key1, Key2, T3Key3));

-- Table1
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2),
     DataSize (Key1, NumberOfRows)
     AS (SELECT 1, 2000 UNION
         SELECT 2, 10000 UNION
         SELECT 3, 25000 UNION
         SELECT 4, 50000 UNION
         SELECT 5, 200000)
INSERT INTO #Table1
SELECT Key1
     , Key2 = ROW_NUMBER() OVER (PARTITION BY Key1, T1Key3 ORDER BY Number)
     , T1Key3
FROM DataSize
     CROSS APPLY (SELECT TOP(NumberOfRows)
                         Number
                       , T1Key3 = Number%(Key1*Key1) + 1
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smaller number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table2
SELECT DISTINCT
       Key1
     , Key2
     , T2Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1*10)
                         T2Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smallest number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table3
SELECT DISTINCT
       Key1
     , Key2
     , T3Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1)
                         T3Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;

DROP TABLE IF EXISTS #a;
SELECT col = 1
INTO #a
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
WHERE t1.Key1 = 1;

DROP TABLE IF EXISTS #b;
SELECT col = 1
INTO #b
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN #Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

Just to be clear, the optimizer already knows that it's a many-to-many join. If you force merge joins and look at an estimated plan you can see a property for the join operator which tells you if the join could be many-to-many. The problem that you need to solve here is bumping up the cardinality estimates, presumably so you get a more efficient query plan for the part of the query that you left out.

The first thing that I would try is putting the results of the join from Object3 and Object5 into a temp table. For the plan that you posted it's just a single column on 51393 rows, so it should hardly take up any space in tempdb. You can gather full stats on the temp table and that alone might be enough to get a sufficient accurate final cardinality estimate. Gathering full stats on Object1 may help as well. Cardinality estimates often get worse as you traverse from a plan from right to left.

If that doesn't work you can try the ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint if you don't already have it enabled at the database or server level. Microsoft locks plan-affecting performance fixes for SQL Server 2016 behind that setting. Some of them relate to cardinality estimates, so perhaps you'll get lucky and one of the fixes will help with your query. You can also try using the legacy cardinality estimator with a FORCE_LEGACY_CARDINALITY_ESTIMATION query hint. Certain data sets may get better estimates with the legacy CE.

As a last resort you can manually increase the cardinality estimate by whatever factor you like using Adam Machanic's MANY() function. I talk about it in another answer but it looks like the link is dead. If you're interested I can try to dig something up.


Altering table Foreign Keys produces an error (errno:150) (MySQL) This is the first database I set up so I apologize if my problem is something obvious.

I'm playing around with my table design and tried to create some link tables with FKs to avoid redundant data. When I try to sync my EER model with the DB I get an error which makes no sense to me.

Executing SQL script in server
ERROR: Error 1005: Can't create table 'jonijz1_Test.#sql-d7d_1291db' (errno: 150)

ALTER TABLE `jonijz1_Test`.`UserGroupLink`
ADD CONSTRAINT `fk_UserGroupLink_UserGroup`
  FOREIGN KEY (`UserGroupID`)
  REFERENCES `jonijz1_Test`.`UserGroup` (`UserGroupID`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_UserGroupLink_Users1`
  FOREIGN KEY (`UserID`)
  REFERENCES `jonijz1_Test`.`Users` (`UserID`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION

SQL script execution finished: statements: 5 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

What am I doing wrong?
And on a sidenote: would a design like this make any sense or am I approaching this wrong as well?

While you seem to have fixed the issue, I will quickly explain why it happened in case anyone finding this will want to understand where the problem was.

When setting foreign keys, the Primary Keys Columns must be of exact same type and attributes. E.g. If you have unsigned attribute on one primary key, you must have it on another. If you have INT data type on one column, then another column must also be INT (NOT TINYINT, MEDIUMINT etc.).

As you have only one ID set to unsigned, I would go and set it to all IDs. As it is usually good idea to have unsigned attribute on primary keys (if you do not use negative IDs), I would have changed all IDs to have unsigned attribute, as it will improve your query performance.

Also, take a look at what values you can get with various integers (when they are unsigned). WHat you set your lenght  to - does not matter:

Integer Type    Max Value
TINYINT         255
SMALLINT        65535
MEDIUMINT       16777215
INT             4294967295
BIGINT          18446744073709551615


trigger_file doesn't work I set streaming replication. Replication works just fine but it doesn't care about trigger_file. I have nothing fancy there except all what many of tutorials advices:

recovery.conf:

standby_mode = on
primary_conninfo = 'host=69.69.69.69 port=5432 user=repl password=some_pass_here'
trigger_file = '/var/lib/postgresql/9.3/main/failover_trigger'

I've checked that the postgres user has access to this file, I've tried to change the location to /tmp, set file owner as postgres or root and nothing helped.

Any ideas ? Thanks in advance.

As a starting point to troubleshoot this, you may check what's read from recovery.conf with log_min_messages set to debug2 in postgresql.conf on the slave.

On server start, the trigger file should be shown in the log within a set of entries like this:

 DEBUG:  standby_mode = 'on'
 DEBUG:  primary_conninfo = 'host=69.69.69.69 port=5432 user=repl password=some_pass_here'
 DEBUG:  trigger_file = '/var/lib/postgresql/9.3/main/failover_trigger'
 LOG:  entering standby mode

If the trigger_file entry doesn't show up, the most plausible explanation would be that you're editing a recovery.conf at a wrong location.

If on the other hand it's found at startup, when later creating the trigger file to fail over, this entry should appear:

 LOG:  trigger file found: /var/lib/postgresql/9.3/main/failover_trigger