IP Address lookup star schema design I am working on a project in which we need to do IP Address lookups. So for this we have got the datasets and now the next steps is to populate this datasets in to the table. And then have some service setup that will accept IP Address as an input and then lookup the database basis on IP Address. So below is the dataset will look like-

ip_address      country region  city           metro-code   latitude    longitude   postal-code   country-code   region-code    city-code   continent-code  country-code

24.32.116.116   usa       tx    clarksville    623           33.6103    -95.0498    75426            840           44             34918            6            us
65.29.108.232   usa       mi    novi           505           42.4637    -83.4604    48375            840           23             4339             6            us
66.169.99.69    usa       tx    ft worth       623           32.7016    -97.3823    76109            840           44             771              6            us
72.70.100.111   usa       ma    peabody        506           42.5332    -70.9726    1960             840           22             1392             6            us
174.252.116.137 usa       va    oakton         511           38.8892    -77.3222    22124            840           47             3860             6            us
68.55.144.242   usa       md    pasadena       512           39.1276    -76.5125    21122            840           21             4358             6            us
174.252.83.27   usa       pa    lancaster      566           40.0459    -76.3542    17603            840           39             333              6            us
174.78.192.31   usa       ga    warner robins  503           32.5958    -83.6384    31088            840           11             5052             6            us
98.192.146.235  usa       fl    panama city    656           30.1804    -85.5598    32404            840           10             671              6            us
71.192.181.20   usa       ma    springfield    543           42.1187    -72.5483    1109             840           22             967              6            us
76.183.54.227   usa       tx    dallas         623           32.7202    -96.6769    75217            840           44             77               6            us
69.62.143.140   usa       ca    granite bay    862           38.7442    -121.191    95746            840           5              49451            6            us
69.142.76.32    usa       nj    penns grove    504           39.707     -75.4467    8069             840           31             2335             6            us
70.173.54.93    usa       nv    las vegas      839           36.2059    -115.225    89108            840           29             173              6            us
98.242.8.222    usa       ca    fresno         866           36.7968    -119.883    93722            840           5              19               6            us

Problem Statement:-

We are expecting traffic around 100 Millions calls per day for the IP Address lookup.

And this dataset will be worldwide datasets meaning for all the countries. And we are planning to store these datasets in MySql database which is In Memory Database here.

Now my question is should I create only a single table with the above mentioned columns? Or should I normalize the above table into two or three? And do I need to create an index on any columns if any.?

Basically I am trying to know how should I setup the schema for this table so that lookup doesn't take that much time with the traffic we are going to expect. And our service which will be doing lookups should return the response very fast.

I was going through the star schema so If I need to go forward with Star Schema then how can I do that?

Assuming you are using SQL Server, you could adapt this to work:

CREATE TABLE [dbo].[IP2Location](
    [ID] [int] NOT NULL CONSTRAINT PK_IP2Location PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
    [ip] [bigint] NULL,
    [country_code] [varchar](2) NULL,
    [country_name] [varchar](255) NULL,
    [region_name] [varchar](255) NULL,
    [city_name] [varchar](255) NULL,
    [latitude] [float] NULL,
    [longitude] [float] NULL,
    [zip_code] [varchar](255) NULL,
    [time_zone] [varchar](255) NULL,
    [isp] [varchar](255) NULL,
    [domain] [varchar](255) NULL,
    [net_speed] [varchar](255) NULL,
    [idd_code] [varchar](255) NULL,
    [area_code] [varchar](255) NULL,
    [IP2LocationTimeStamp] [timestamp] NOT NULL,
);

/*
    Create a non-clustered index on the numeric IP address to speed lookups
    I indexed the country_code field here as well since our lookups use it.
    I INCLUDED country_name, region_name, and time_zone since that is what we
    typically request in a SELECT statement from this table.
*/
CREATE NONCLUSTERED INDEX [IX_IP2Location_IP2LocationServiceQuery] ON [dbo].[IP2Location]
(
    [ip] ASC,
    [country_code] ASC
)
INCLUDE (   [country_name],
    [region_name],
    [time_zone])
GO

/*
    Create BIGINT versions of the dotted-ip field ip_address
*/
UPDATE IP2Location SET IP =
            (CAST(substring(ip_address,1,charindex('.',ip_address)-1) AS BIGINT) * 0x01000000)
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address))+1, (charindex('.', ip_address, charindex('.', ip_address, charindex('.',ip_address))+1)-charindex('.',ip_address,charindex('.',ip_address)))-1) AS BIGINT) * 0x00010000)
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1, charindex('.', ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1) - (charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address))+1)+1)) as BIGINT) * 0x00000100)
            + (CAST(substring(ip_address, charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address)+1))+1)+1, len(ip_address) - (charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address,charindex('.',ip_address)+1))+1))) as BIGINT)
            );

/*
    Function to return a TVF containing location info about the given numeric IP address
    For example:
        SELECT country_code, country_name, region_name, city_name, latitude, longitude, time_zone
        FROM GetLocationFromIPv4(16777215);
*/
CREATE FUNCTION [dbo].[GetLocationFromIPv4]
(
    @IP bigint
)
RETURNS @IPLoc TABLE
(
    country_code varchar(2)
    , country_name varchar(255)
    , region_name varchar(255)
    , city_name varchar(255)
    , latitude float
    , longitude float
    , time_zone varchar(255)
    , isp varchar(255)
    , domain varchar(255)
    , net_speed varchar(255)
    , idd_code varchar(255)
    , area_code varchar(255)
)
AS
BEGIN
    /*
        TVF to return (typically) a single record from IP2Location as quickly as possible
    */
    INSERT INTO @IPLoc
    SELECT country_code
        , country_name
        , region_name
        , city_name
        , latitude
        , longitude
        , time_zone
        , isp
        , domain
        , net_speed
        , idd_code
        , area_code
    FROM IP2Location
    WHERE ip = @IP;
    RETURN
END
GO

/*
    Function to return a TVF containing location info about the given dotted-decimal IP address
    For example:
        SELECT country_code, country_name, region_name, city_name, latitude, longitude, time_zone
        FROM GetLocationFromIPv4('192.168.0.1');
*/
CREATE FUNCTION [dbo].[GetLocationFromIPv4String]
(
    @IP nvarchar(15)
)
RETURNS @IPLoc TABLE
(
    country_code varchar(2)
    , country_name varchar(255)
    , region_name varchar(255)
    , city_name varchar(255)
    , latitude float
    , longitude float
    , time_zone varchar(255)
    , isp varchar(255)
    , domain varchar(255)
    , net_speed varchar(255)
    , idd_code varchar(255)
    , area_code varchar(255)
)
AS
BEGIN
    /*
        TVF to return (typically) a single record from IP2Location as quickly as possible
    */
    DECLARE @IPInt64 bigint;
    DECLARE @n1 bigint;
    DECLARE @n2 bigint;
    DECLARE @n3 bigint;
    DECLARE @n4 bigint;

    set @n1 = substring(@IP
        ,1
        ,charindex('.',@IP)-1
        );

    set @n2 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP))+1
            , (charindex('.', @IP, charindex('.', @IP, charindex('.',@IP))+1)-charindex('.',@IP,charindex('.',@IP)))-1
        );

    set @n3 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1
            , charindex('.', @IP, charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1) - (charindex('.',@IP,charindex('.',@IP,charindex('.',@IP))+1)+1)
        );

    set @n4 =  substring(
            @IP
            , charindex('.',@IP,charindex('.',@IP,charindex('.',@IP,charindex('.',@IP)+1))+1)+1
            , len(@IP) - (charindex('.',@IP,charindex('.',@IP,charindex('.',@IP,charindex('.',@IP)+1))+1))
        );

    SET @IPInt64 = (@n1 * 0x01000000) + (@n2 * 0x00010000) + (@n3 * 0x00000100) + @n4;

    INSERT INTO @IPLoc
    SELECT TOP(1)country_code
        , country_name
        , region_name
        , city_name
        , latitude
        , longitude
        , time_zone
        , isp
        , domain
        , net_speed
        , idd_code
        , area_code
    FROM IP2Location
    WHERE ip = @IPInt64;
    RETURN
END
GO


Can an InnoDB page contain data from different indexes? (In this question, I assume that the data of a table is acťually an index (PRIMARY), so I don't differentiate between table data and column indexes)

I've compacted an "old" InnoDB table (via null ALTER TABLE), and observed the space taken by the indexes, before and after, via the mysql.innodb_index_stats table.

The size decreased (for some indexes, significantly); this lead to a question: can a single InnoDB page contain data from different indexes?

No a page can only hold records from a single index.
see: innodb page header docs
long story short - you have a field PAGE_INDEX_ID which is an identifier of the index the page belongs to.

Regards,
Jony


Can a single PostgreSQL query use multiple cores? In recent versions of PostgreSQL (as of Dec 2013), can we share a query between two or more cores to get a performance boost? Or should we get faster cores?

No, for versions of PostgreSQL prior to v9.6. Please see the PostgreSQL FAQ: How does PostgreSQL use CPU resources?

The PostgreSQL server is process-based (not threaded). Each database session connects to a single PostgreSQL operating system (OS) process. Multiple sessions are automatically spread across all available CPUs by the OS. The OS also uses CPUs to handle disk I/O and run other non-database tasks. Client applications can use threads, each of which connects to a separate database process.
Since version 9.6, portions of some queries can be run in parallel, in separate OS processes, allowing use of multiple CPU cores. Parallel queries are enabled by default in version 10 (max_parallel_workers_per_gather), with additional parallelism expected in future releases.


TSQL - Finding Similar Values I have a static table containing filenames (StaticFilesTbl).

Each day a file is sent in that is stored in DailyFilesTbl.

The filenames in DailyFilesTbl will be different from StaticFilesTbl in that they will contain a date (in any format and in any location) in the filename.

I want to return files from DailyFilesTbl that 'match' - using LIKE - to the files in StaticFilesTbl.

In my code below I try to do this by putting a '%' into the StaticFilename value after an underscore - which works. However, I then have an issue if it is needed after multiple underscores.

Is there a better way to do this?

http://sqlfiddle.com/#!18/a0adfe/1

-- Table that holds static file names
CREATE TABLE StaticFilesTbl
(
    StaticFilename nvarchar(150)
)
INSERT INTO StaticFilesTbl(StaticFilename)
VALUES('SG__123456'),
        ('TFF_x2__x3');

-- update static table to add '%' for searches
UPDATE StaticFilesTbl
SET StaticFilename = STUFF(StaticFilename, CHARINDEX('_', StaticFilename)+1, 0, '%');

-- table that holds files received each day (date is on filename and changes daily)
CREATE TABLE DailyFilesTbl
(
    DailyFilename nvarchar(150)
)
INSERT INTO DailyFilesTbl(DailyFilename)
VALUES('SG_20180731_123456')
        ,('SG_20180730_123456')
        ,('SG_20180730_x2')
        ,('TFF_x2_20180730_x3');

-- query daily table for files
SELECT *
FROM DailyFilesTbl d
INNER JOIN
    StaticFilesTbl s
    ON d.DailyFilename LIKE s.StaticFilename +'%'

you need to remember that in a like statement a _ is ANY single character, and a % is any number (including 0) characters

on your insert for StaticFilesTbl if you use the search strings you wish for in there (and bracket out the underscores) that should i believe get you what you want

INSERT INTO #StaticFilesTbl(StaticFilename)
VALUES('SG[_]%[_]123456'),
        ('TFF[_]x2[_]%[_]x3');

Also you'll nolonger need the + '%' on the end of the LIKE statement as it has everything built in already


whitelist for allowed user queries in postgres Looking for a way to limited a user to a specific set of queries (i.e. a whitelist).

I currently need this as an extra security measure for a client app.

Is there a way to enable a table of "user allowed queries"? Anything that doesn't meet the regex pattern in that table (or however it works) receives a FATAL error.

UPDATE for clarity:

The client app only has 50 or so queries that ever execute, over and over again. So the list of expected queries is known. The only thing that changes between them is some values for id attribute in the WHERE clause.

For instance, I count these two select statements as 1 of the "50" (i.e. it's the same query, just the x value is different):

SELECT a, b, c
FROM table_a
WHERE x = 1;

The next query may be:

SELECT a, b, c
FROM table_a
WHERE x = 2;

What I'd ideally like to enforce is that all queries against the database should include a particular condition in the WHERE clause, i.e. organisation_id = x where the value of x would change depending on the user running the query.. but organisation_id exists in every table, and every query should contain this clause.

Looking for a way to limited a user to a specific set of queries (i.e. a whitelist).

Wrap all of your queries in a plpgsql function that takes a SQL command and executes dynamic SQL.

SELECT * FROM mystupidwrapper('SELECT * FROM foo');

It will have to return a specific type, SETOF jsonb sounds great. You can write mystupidwrapper in perl, or plv8 and it'll even be trusted. If you want to write it in plperl, you can even use SQL::Statement::Structure to do basic SQL Statement processing. That said, SQL is a query language it provides READ permissions on both table and columns but the goal is still a declarative language that provides the ability to query abstractly.

Perhaps you want something more like pg-promise which natively supports a QueryFile


MySQL complains key exists but I can't find it I have a large (in both schema and data) MySQL database with lots of foreign key constrains. Recently I have discovered that some script can not create a table because key with given name already exists.

I traced down the problem to the following:

If I run something like this:

CREATE TABLE `foo` (
  `bar` int UNSIGNED NOT NULL,
  CONSTRAINT `BAZ` FOREIGN KEY (`bar`) REFERENCES `qux` (`bar`) ON DELETE CASCADE ON UPDATE CASCADE
);

I'm getting:

  ERROR 1022 (23000): Can't write; duplicate key in table 'foo'

But if I:

SELECT *
  FROM information_schema.REFERENTIAL_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA = "my_db"
   AND CONSTRAINT_NAME LIKE "BAZ";

I'm getting an empty set.

I have also tried to dump the schema and search for "BAZ" there but found nothing.

Creating a table naming foreign key anything but "BAZ" goes through.

How could it be?

You likely have an index for a foreign key on some other table in your DB, which has the same constraint name and is thus causing a namespace collision.

If you are using all InnoDB tables (as you should in 2017), try this to locate the offending table:

SELECT t.name
  FROM information_schema.innodb_sys_indexes i
  JOIN information_schema.innodb_sys_tables t
 USING (table_id)
  WHERE i.name = 'BAZ';

Or this may work, too:

SELECT *
FROM information_schema.innodb_sys_foreign
WHERE id REGEXP 'BAZ$';


Disabling auto compaction in CouchDB I would like to disable auto compaction on my CouchDB instance - so that I can get a full history of all the database updates.

Is this possible? If so, how do I go about it? Is there a reason why I should not do this?

You can find the description for how to configure automatic compaction in section 3.7 of the documentation. The basic info regarding the configuration of CouchDB can be found in section 3.1.

As I have never turned of automatic compaction and am not sure why you want this, I think you can play around with the settings to achieve it. There is no attribute to set automatic compaction to false.

A word of warning: the basic principle of revisions (_rev) in CouchDB is to keep copies of documents for each revision. That means if you have a document sized 100 kb you will consume n * 100 kb disc space where n is the number of revisions. Please keep that in mind and be aware, that with many documents and many changes, your disk will be full very quickly.


Nested stored procedures with use of transactions I have a question regarding this tsql-code found in this thread regarding "nested transactions" (unfortunately my reputation is not large enough to comment in that thread):
In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?

Here is a procedure with transactions-check and a savepoint if the @@trancount > 0
But I don't see the difference the savepoint makes, when there is a "raiserror-statement" in the catch block of the procedure. Will this not force the calling code to rollback to the beginning of the calling code, thus ignoring the savepoint?

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Insert into table:
           INSERT INTO TABLE1 'Data from usp_my_procedure_name'

        -- Make an error:
           SELECT 1/0

lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end
go

If this procedure is called in a nested way like this

CREATE OuterProc AS
    BEGIN TRY
     BEGIN TRANSACTION

     INSERT INTO TABLE1 'Data from OuterProc code'

     EXEC usp_my_procedure_name

     COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
     ROLLBACK
    END CATCH
END

If I execute the OuterProc, the data 'Data from OuterProc code' will not get inserted.
Then what is the point of the savepoint?

It works if I remove the raiserror-part of the code. Then the 'Data from OuterProc code' gets inserted..

So my question is - what is the point of the savepoint when there is a raiserror-statement in the procedures catch-block?

Let's first get the RAISERRROR() part out of the way, more specifically why does the insert happen when it is removed.

  It works if I remove the raiserror-part of the code. Then the 'Data
  from OuterProc code' gets inserted..

The raise error is defined outside of the TRY block scope

    raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;

And the documentation shows that the error is returned to the caller (OuterProc) when RAISERROR() is ran outside of the try block scope.

  The error is returned to the caller if RAISERROR is run:

  Outside the scope of any TRY block.

Source

As a result, the raiserror returns control to the OuterProc procedure, and as a result of the error happening in the TRY block of the outer procedure it is returned to the CATCH block. And, inside this catch block is a ROLLBACK.

-->  ROLLBACK

If you add this to the CATCH block of the outer proc:

BEGIN CATCH
    declare @error int, @message varchar(4000), @xstate int;
    select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();

raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
 ROLLBACK
END CATCH

The error gets re-raised:

  Msg 50000, Level 16, State 1, Procedure OuterProc, Line 17 [Batch
  Start Line 58] usp_my_procedure_name: 50000: usp_my_procedure_name:
  8134: Divide by zero error encountered.

And the outer proc insert:

 INSERT INTO TABLE1
 VALUES('Data from OuterProc code')

Gets rolled back.

  So my question is - what is the point of the savepoint when there is a
  raiserror-statement in the procedures catch-block

The savepoint is there to not rollback the entire transaction, but due to your use of TRY CATCH in the outer procedure, your outer procedure is also rolled back.

If you don't want the outer transaction to also be rolled back, then you should not use an additional TRY CATCH block around the outer transaction.

You would have to define the procedure like this:

CREATE PROCEDURE OuterProc2 AS

     BEGIN TRANSACTION

     INSERT INTO TABLE1
     VALUES('Data from OuterProc code')

     EXEC usp_my_procedure_name

     COMMIT TRANSACTION

If you then run the procedure:

EXEC OuterProc2;

The inner procedure error is returned to the caller:

  Msg 50000, Level 16, State 1, Procedure usp_my_procedure_name, Line 36
  [Batch Start Line 54] usp_my_procedure_name: 8134: Divide by zero
  error encountered.

But the  Data from OuterProc code string is still inserted.

SELECT *
FROM table1;

Value
Data from OuterProc code

If you do not use a savepoint, then the entire transaction will be rolled back.
As a result, errors will occur when trying to COMMIT the non existing tran.

  Msg 266, Level 16, State 2, Procedure usp_my_procedure_name, Line 0
  [Batch Start Line 53] Transaction count after EXECUTE indicates a
  mismatching number of BEGIN and COMMIT statements. Previous count = 1,
  current count = 0. Msg 3902, Level 16, State 1, Procedure OuterProc2,
  Line 10 [Batch Start Line 53] The COMMIT TRANSACTION request has no
  corresponding BEGIN TRANSACTION.


PowerBI Service Account granting duplicate execute permissions on stored procedure almost constantly I have PowerBi Server (Jan 2019) installed on my server. I can see from the default trace that that the PowerBi Service account NT SERVICE\PowerBIReportServer is granting execute permissions on a stored procedure ExtendEditSessionLifetime to the RSExecRole (within the ReportServer database):

GRANT EXECUTE ON [dbo].[ExtendEditSessionLifetime] TO RSExecRole

This is happening frequently many times during the hour, most hours (around 50 times an hour) there doesn't seem to a pattern that I can see and I can't find any documentation online about what the stored procedure does. I can't see anything obvious in the PowerBI logfiles or the event viewer either.

What could be causing this (or is it intended behaviour?)

I have worked out what is happening here.  tl/dr - There was a missing batch separator between a stored procedure and the associated GRANT statement.
If I run:
SELECT *
FROM sys.sql_modules
WHERE definition LIKE '%GRANT EXECUTE ON \[dbo\].\[ExtendEditSessionLifetime\] TO RSExecRole%' ESCAPE '\'

I can see stored procedures that have that grant statement within them.
This returned one record and the definition field begins
CREATE PROC [dbo].[ExtendEditSessionLifetime]      @EditSessionID varchar(32)......

If I navigate to this stored procedure in the object tree and right click and script it out, I can see text of the stored procedure:
CREATE PROC [dbo].[ExtendEditSessionLifetime]
    -- params
AS
BEGIN
    --code
END

GRANT EXECUTE ON [dbo].[ExtendEditSessionLifetime] TO RSExecRole
GO

I wasn't aware of this but it seems that when you create a stored procedure, you can place a GRANT after the END and it becomes part of the definition (I thought it had to be part of the BEGIN .... END construct (when there is one)
Therefore, every time ExtendEditSessionLifetime is run, it is re-granting permissions to RSExecRole.


Where's my missing Sql Server Express 2005 Database space? Size and space available dont add up to 4G? I recently deleted some data from a table that was not needed and growing to too big a size with this query which provided to me by a support tech...

 declare @mdate datetime
 set @mdate='04/04/2014'
 delete from CacheDiffResults where CacheID in (select CacheID from ComparisonCache
 where TimeStamp < @mdate)
 delete from ComparisonCache where TimeStamp < @mdate
 delete from CacheDiffResults where CacheID not in (select CacheID from ComparisonCache)

so it deleted all data before 04/04/2014. After I ran this query I saw that I gained back about a Gig of space from this table, then I ran a shrink on the database.

When I looked at the properties of the database the figures didn't add up? I'm no DBA (Networks) but I don't understand why the DB size and space left figures add up to 4G which is the size of SQL express 2005 DB?

Have a look at these screen shots...

As you can see 2569.73MB + 43.96MB != 4G

And what are these figures in the bottom picture? These don't add up to 4G either? Confused. Any help appreciated. TIA!

On the question of the size of things, first of all you said that you shrank a database, so that would reduce the DB size of the space that was discarded during the shrink.  John M also provided some clarification.

Regarding the numbers in the bottom picture, they are:

select (2585832 / 1024.0) /* KB from the Reserved in the bottom picture to MB */
       + 43.96 /* MB free */

SpaceAllocated
---------------
2569.186562

So you can see that 2569.19 MB is essentially the same as 2569.73 MB

The other numbers in the bottom picture are showing you how that database is allocated:

reserved        data         index_size  unused
-----------     ----------   ---------   ---------
2585832 KB   =  2315184 KB + 137216 KB + 133432 KB

So it is pretty straight-forward and actually gives you some useful insight into your space utilization.

Additional Notes: Yes, when you shrink a database, it actually compacts the data and then releases the no longer used disk space.  Of course, SQL Server 2005 Express can have many databases that are 4 GB.  (If you update to a more recent version of SQL Server Express, the limit is 10 GB per database, not including the log space.)

Within a database the unused column in the example, defines how much space currently exists in the database file that could be used for adding more data.  However, the database can be expanded to its maximum size or you can set autogrowth to allow it to grow in selected increments. (Use exact growth values, not percentages.)


Can't "Script Database As -> CREATE TO" with SSMS 2008R2 and 2016 I created a SQL Login (Login01), mapped to DB user (User01), member of db_owner database role.
When connected locally with this user in SSMS, i try to script out the database (Script Database As -> CREATE TO) and it works as expected.
When connected from a remote machine (same domain, same LAN), it fails (Could not read metadata, possibly due to insuficient access rights). Only way i found to permit it is to grant "View any definition" to the Login.

I dont understand why it only fails when connecting remotely, is this an expected behaviour?

Edit: As said in comments, the issue here has nothing to do with local/remote connection but is related to the SSMS version. I edited title also so the question/answer is easier to find.

I opened a connect feedback here: https://connect.microsoft.com/SQLServer/feedback/details/2869241
as follow:

"A SQL Login, mapped to a database user member of the db_owner role cannot script out the database with "Script Database As" or "Generate Script" task when using SSMS 2008, 2008R2, 2016.
It gets the following error message "Could not read metadata possibly due to insufficient access rights.
Granting the login "View any definition" solve it but this cannot be a workaround for obvious security reason.

(Tested with SQL2008, SQL2014 and SQL2016 instances installed on VMware virtual machines.)"


MySQL Database Poisoning: How to recover to a known state quickly If you want to avoid database poisoning (ie want to recover back to a point-in-time quickly) which methodology do you prefer?

Let me define data poisoning. You insert some things in your database which totally messes up the internal structure and interdependencies. I know it means that database design probably also needs to be revisited, but the damage is done.

The methods I have in mind are

Somehow setup replication in which the slave is passive and is X hours behind. If I have a failure all I have to do is reset the application and point it to the slave as my new master. I suspect that it is possible.
Do a hot backup of MySQL every few hours and when failure is detected restore to a backup from X hours before. This would mean a downtime for the application since I cannot let the current application keep on running. One could use innobackup or percona for quick backup and recovery steps.
Design the application and database specifically so that newly added data gets nuked (or shelved). This means I store all events/states (I guess this is the most difficult and theorotical solution)

If the first option is possible and it also stores all the relay logs (ie what ever happened on Master gets transfered to Slave at the same instant but is applied in a few hours automagically) then it would be a perfect solution. Perhaps one could setup multiple slaves in a setup to recover from both an outage and data poisoning

You can use the pt-slave-delay tool from Percona Toolkit to keep a replica delayed by the amount of time you choose.


find sold subscriptions from table 1 and store name in table 2, joined by store id. (oracle) I am trying to get the number of sold subscriptions from one table and the store name from another. The tables have a common column containing a store id, however the columns do not have the same name.

Talbe1                  |   Table2

Amount     store_code   |   store_name    store_no

1          nf1ii        |   Jims kiosk    nf1ii
1          nfoi1        |   Henrys kiosk  nfoi1
...        ...          |   ...           ...

I am trying to count the number of subscriptions sold for each store name. How is this doen in Oracle? I am fairly new to SQL in general so please forgive me if the question is phrased strangely.

select
t2.store_name
,t1.store_code
,sum (t1.Amount) -- if Amount can be >1
,count (t1.*)  -- if counting rows is enough and we want to show stores without subscribtions
from Table1 t1
right join Table2 t2 on t1.store_code=t2.store_code
where
t1.subs = 'twin' /* type of subscription */
and t1.date > '01.05.13'
group by t2.store_name, t1.store_code

It is a good practice to use LEFT JOIN rather then RIGHT JOIN, as the later one is harder to imagine and understand.

select
t1.store_name
,t2.store_code
,sum (t2.Amount) -- if Amount can be >1
,count (t2.*)  -- if counting rows is enough and we want to show stores without subscribtions
from Table2 t1
left join Table1 t2 on t1.store_code=t2.store_code
where
t2.subs = 'twin' /* type of subscription */
and t2.date > '01.05.13'
group by t1.store_name, t2.store_code


Installing SQL Server 2000 32-bit on Windows Server 2008 x64? Am I correct in assuming there won't be any issues installing SQL Server 2000 32-bit on a Windows Server 2008 x64 system?

I understand the memory limitations, etc, but that doesn't matter at this point.

I'm not sure why you're installing SQL Server 2000 anywhere in the year 2012, but no, there shouldn't be any technical issue other than the ones you've identified and the fact that it is nowhere near supported.

In any case, there is no 64-bit version of SQL Server 2000 - there is only 32-bit for x86 and 64-bit for IA. I assume your Windows Server 2008 box isn't Itanium.


Is it advisable to have MySQL Master/Slave running on the same physical server, on the same mysqld, sharing the same disk? my Use-Case :

The solution I am developing is very data oriented. I run a cron job every 3 hrs to read lots of records from a file, process them and store them in the database. I also need to generate reports, which I do by querying database.

So, I was thinking of having MySQL replication in Master and slave so that I can do all the inserts / updates on Master and use the slave just to read data to generate reports.

I read this other post on the site and it got me thinking.

Here is my concern: Both my master and slave will be sharing the same resources here as they are on same physical server.

Is it good idea to do so?

I don't think so. You might reduce locking issues (but I'm not sure of that), but you are adding replication overhead without increasing your hardware resources, which means you'll be doing more with the same resources, which probably means decreased performance.


How can I generate a consistent entity graph (say from 10 tables) from a database fetch Suppose I have many tables in a database that need to be joined together to get a single consistent view of the entire entity graph. Am I forced to do this as a single query? I don't care about locks, I just want to ensure the graph is consistent as of the time of the query execution.
My concern is one of performance of joining a large number of entities together in a single query. Is it possible to do this in multiple queries but still realize the same level of consistency as having performed these individual selects as a single query?

Is it possible to do this in multiple queries but still realize the same level of consistency as having performed these individual selects as a single query?

It depends on the RDBMS.
In SQL Server you can use a transaction with SNAPSHOT transaction isolation level to get a consistent view of the database across multiple queries.
The isolation level Oracle calls SERIALIZABLE does the same thing.

My concern is one of performance of joining a large number of entities together in a single query

This is a valid concern because most RDBMS engines only return tabular results from queries. The strategies to generate a single tabular result for a complex object graph can lead to large resultsets and complex queries.  This has always been a problem for ORMs.


Explanation requested for slow DELETE with SQL Server I would like to get some extra insights/reasoning for SQL Server delete behavior. We have a fairly large database of over 1800 GB.

In there are some very shallow tables (only a few integer columns) with many millions of rows. When we delete 10,000s of rows from these shallow tables the delete queries are generally quite fast (at most a handful of seconds).

We also have a table with a field of type image storing images averaging 100 KB. When we delete only a few thousand rows from this table, it takes well over a minute.

Although the difference is clear (much more data size-wise is deleted), I'm am eager to learn more about what happens inside SQL Server. So that I can better understand the latter deletes to be so much slower.

Can anyone please shed some light?


Does Oracle support the VALUES expression construct? PostgreSQL supports the SQL spec's VALUES expression in what the spec calls a <table value constructor>

SELECT *
FROM (VALUES
  (1, 'one'),
  (2, 'two'),
  (3, 'three')
) AS t(num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)

Trying it on dbfiddle, I can't get it to work in Oracle. I keep getting

  ORA-00903: invalid table name

Does Oracle support the VALUES ( expression [, ...] )? If not what is the alternative method of creating a virtual table from row-literals provided in the query?

Does Oracle support the VALUES ( expression [, ...] )? If not what is the alternative method of creating a table from row-literals provided in the query?

The most concise option we've got is with insert all:

insert all
  into t values(1,'one')
  into t values(2,'two')
  into t values(3,'three')
select * from dual

select * from t;

NUM | WORD
--: | :----
  1 | one
  2 | two
  3 | three

dbfiddle here

Note that the syntax is an extension to the standard and allows inserting into multiple tables with a single statement and conditional inserts, as the example shows:

insert all
when 1 = 1 then
  into t values (num, word)
when num > 0 then
  into t values (-num, 'minus ' || word)
select 0 as num, 'zero' as word from dual union all
select 1, 'one'   from dual union all
select 2, 'two'   from dual union all
select 3, 'three' from dual ;

7 rows affected

select * from t;

NUM | WORD
--: | :----------
  0 | zero
  1 | one
  2 | two
  3 | three
 -1 | minus one
 -2 | minus two
 -3 | minus three

dbfiddle here


PG: How to show total count for this query if result is 0 I have a postgres 9.6 table called selected_media, with a column called _type that is set to either book or movie.

I want to select the COUNT of each type, in 1 single row, like this:

25 | 715

To do this, I have this query:

SELECT
  COALESCE(SUM(CASE WHEN _type = 'book'  THEN 1 ELSE 0 END), 0) AS books_count,
  COALESCE(SUM(CASE WHEN _type = 'movie' THEN 1 ELSE 0 END), 0) AS movies_count
FROM selected_media
WHERE subscriber_id = $1

The problem is 2 fold:

I want each column to show the respective total count for that _type only if the sum in that column happens to be 0 (none selected). This is because the UI/UX states "selecting 0 books or movies is the same as selecting all of them".
It needs to be fast, this table will have millions of rows in it

Then do it slightly different, use the (more or less) complementary of COALESCE, which is NULLIF:

SELECT
    NULLIF(books_count, 0) AS books_count,
    NULLIF(movies_count, 0) AS movies_count
FROM
    ( -- Original, slightly simplified query
    SELECT
        SUM(CASE WHEN _type = 'book'  THEN 1 END) AS books_count,
        SUM(CASE WHEN _type = 'movie' THEN 1 END) AS movies_count
    FROM
        selected_media
    WHERE
        subscriber_id = 1234
    ) AS s1 ;

dbfiddle here

If you don't want to have a NULL value when the result is zero, but another one, you can use:

SELECT
    CASE
    WHEN books_count <> 0 THEN books_count
    ELSE (another_value_or_expression_for_books)
    END  AS books_count,
    CASE
    WHEN movies_count <> 0 THEN movies_count
    ELSE (another_value_or_expression_for_movies)
    END  AS movies_count
FROM
    ( -- Original, slightly simplified query
    SELECT
        SUM(CASE WHEN _type = 'book'  THEN 1 END) AS books_count,
        SUM(CASE WHEN _type = 'movie' THEN 1 END) AS movies_count
    FROM
        selected_media
    WHERE
        subscriber_id = 1234
    ) AS s1 ;

You can also use count(case ...) instead of SUM, and, in latest versions of PostgreSQL, you can use a FILTER, instead of CASE:

SELECT
    NULLIF(books_count, 0) AS books_count,
    NULLIF(movies_count, 0) AS movies_count
FROM
    (SELECT
        count(*) FILTER (WHERE _type = 'book') AS books_count,
        count(*) FILTER (WHERE _type = 'movie') AS movies_count
    FROM
        selected_media
    WHERE
        subscriber_id = 1234
    ) AS s1 ;

dbfiddle here

If you need the fastest possible speed, and depending on the number of _types, and the number of elements of each type, you could use some partial indexes:

CREATE INDEX idx_books ON selected_media (subscriber_id) WHERE _type='book' ;
CREATE INDEX idx_movies ON selected_media (subscriber_id) WHERE _type='movie' ;

And then perform your query as two subqueries:

SELECT
    nullif( (select count(*) from selected_media where _type='book'
             and subscriber_id = 1234),
           0) AS count_books,
    nullif( (select count(*) from selected_media where _type='movie'
             and subscriber_id = 1234),
           0) AS count_movies ;

This will give you a query plan doing two index only scans most of the time.

| QUERY PLAN                                                                                |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Result  (cost=164.10..164.12 rows=1 width=16) (actual time=1.491..1.492 rows=1 loops=1)                                                                       |
|   InitPlan 1 (returns $0)                                                                                |
|     ->  Aggregate  (cost=159.94..159.95 rows=1 width=8) (actual time=1.485..1.485 rows=1 loops=1)                                                             |
|           ->  Index Only Scan using idx_books on selected_media  (cost=0.28..150.36 rows=3833 width=0) (actual time=0.016..1.108 rows=3974 loops=1)           |
|                 Index Cond: (subscriber_id = 1234)                                                                                |
|                 Heap Fetches: 3974                                                                                |
|   InitPlan 2 (returns $1)                                                                                |
|     ->  Aggregate  (cost=4.15..4.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)                                                                 |
|           ->  Index Only Scan using idx_movies on selected_media selected_media_1  (cost=0.12..4.14 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1) |
|                 Index Cond: (subscriber_id = 1234)                                                                                |
|                 Heap Fetches: 0                                                                                |
| Planning time: 0.094 ms                                                                                |
| Execution time: 1.514 ms                                                                                |

dbfiddle here

If you have very many _type, or you want your query to work with any possible _type (and not two specifically chosen) your second choice would be to have the following index:

CREATE INDEX idx_subscriber_type ON selected_media (subscriber_id, _type) ;

... then, the previous query will execute using this index

| QUERY PLAN                                                                                |
| :------------------------------------------------------------------------------------------------------------------------------------------------- |
| Result  (cost=1643.07..1643.09 rows=1 width=16) (actual time=0.679..0.680 rows=1 loops=1)                                                          |
|   InitPlan 1 (returns $0)                                                                                |
|     ->  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.670..0.671 rows=1 loops=1)                                                  |
|           ->  Bitmap Heap Scan on selected_media  (cost=29.98..819.19 rows=933 width=0) (actual time=0.170..0.573 rows=992 loops=1)                |
|                 Recheck Cond: ((subscriber_id = 1234) AND (_type = 'book'::text))                                                                  |
|                 Heap Blocks: exact=547                                                                                |
|                 ->  Bitmap Index Scan on idx_subscriber_type  (cost=0.00..29.75 rows=933 width=0) (actual time=0.113..0.113 rows=992 loops=1)      |
|                       Index Cond: ((subscriber_id = 1234) AND (_type = 'book'::text))                                                              |
|   InitPlan 2 (returns $1)                                                                                |
|     ->  Aggregate  (cost=821.53..821.54 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)                                                  |
|           ->  Bitmap Heap Scan on selected_media selected_media_1  (cost=29.98..819.19 rows=933 width=0) (actual time=0.005..0.005 rows=0 loops=1) |
|                 Recheck Cond: ((subscriber_id = 1234) AND (_type = 'movie'::text))                                                                 |
|                 ->  Bitmap Index Scan on idx_subscriber_type  (cost=0.00..29.75 rows=933 width=0) (actual time=0.005..0.005 rows=0 loops=1)        |
|                       Index Cond: ((subscriber_id = 1234) AND (_type = 'movie'::text))                                                             |
| Planning time: 0.077 ms                                                                                |
| Execution time: 0.706 ms                                                                                |

dbfiddle here


Need help designing a database We are designing a system which holds statistical parameters about a particular user. As the users use the system the statistics change and the updated statistics are stored in the database. So for a new user a record is inserted and after that the records are only updated. So I expect this database to be update heavy.

Also when we see the user online we want to query his record from the database so that we can use the stored data to do some characterization.

The fields of the database which are important are

user Identifier to uniquely identify the user.
last accessed time to know when the record was last updated.

If a user is idle for more than 60 days we want to prune the entry from the database. We currently use postgres as our server.

I have been working on how to implement a efficient way to prune the database while keeping update and query performance satisfactory.

Current Design plan:

EDIT: Estimated Number of Records: 100 million.

Set up partition on postgres. Each partition corresponds to one day of usage.
The partitioning criteria would be the date of the last accessed time. The primary key for the partition would be UserId.

If a record is accessed frequently in the same day, it would map to the same partition but across different days i would have to delete the entry from the old partition and move it to the new partition using a update trigger.

A cron job would run daily and delete the old required partition and add the new partitions. A stale entry would probably remain in the partition and truncate table on the required partition would drop all the entries.

The query would be done based on the UserID, if the partition logic is correct we would find the userid in only one of the partitions. So the query would take place across all partitions but find only one record

I need the pruning performance to be good. This is because the table has primary key by User ID. It has no indexes based on time. Since the last accessed time keeps changing it cant be made part of any index. Also there is no logical clustered index field for this table.

If partitioning is not done, this would mean i would have to do a full table scan and since last accessed time is not part of the key, it would bring every record to the database eventually and this would replace existing frequently used pages used by our system. I would want to avoid a full scan on the table.

As pointed out, moving between partitions would require a delete and a insert into the other table. Space is not a issue. I can add the partition date as primary key along with the UserID field of the database and modify the query method to take the latest record. The query would be done on the primary keys and hence the computation would only be on the index files. I could avoid the delete and let the entries be in the database with the cron job clearing the table eventually.

But nevertheless insert into another partition would always happen on moving a record across paritions. A single table approach would solve this but then the delete performance would suffer.

Any help or suggestions on this would be appreciated

If you have an index on LastLoggedIn, deleting a few tens of thousands of records should be fast without needing to partition.  I ran a small test on MS SQL:

CREATE TABLE Temp.Temp
(
UserID  INT NOT NULL PRIMARY KEY,
LastLoggedIn    DATETIME2(0) NOT NULL INDEX,
Dummy1  VARCHAR(30),
Dummy2  INT
)

I insert ~57 M dummy records, then selected a LastLoggedIn date such that 35,000 records were at least that old.  Deleting these records took less than a second.  This is on a four-core machine with 128 GB of memory, under light load; your mileage may vary.

Of course, if you run this purge only every few weeks, you may have millions of records to delete rather than thousands (300 K records took seven seconds for me; 1.3 M records took 30 seconds).  But in that scenario, it's part of your monthly maintenance window rather than daily upkeep, and a brief delay should be acceptable.

If you get poor performance with just an index, then sure, consider partitioning.  As others have pointed out, there will be a significant cost to UPDATE operations, a greater overhead than maintaining an index.  You will have many more updates than purges, and the latter can happen during relatively idle periods, so I would worry about the updates more.

If you need always-on uptime, and the delete locks the table for an unacceptable length of time, that might justify using partitioning, but even there I'd look for alternatives.  You could SELECT a list of old users and then delete them one at a time (confirming their LastLoggedIn dates first of course) with a cursor, for example.

I'm an MS SQL guy, so my apologies if any of this is incorrect for PostgreSQL.


Reduce page accesses of query Assume that we have a secondary index for an attribute in a table and
  we want to execute a range query on that attribute. Recommend a
  possible approach to  reduce the number of page accesses
  for this query.

This is from my advanced databases course.

A range query will read from every index page that contains data within the range (plus a couple of non-leaf pages to find the start of the range) so to reduce the number of pages touched you need to make sure there are as many rows squeezed into each page as possible. This can be achieved by simply having the index not cover or include anything except that column.

This is only definitely true if only touching that column though: for queries like SELECT col1 FROM theTable WHERE col1 BETWEEN 4000 AND 9000 or SELECT COUNT(*) FROM theTable WHERE col1 BETWEEN 4000 AND 9000.

When considering anything more complex it is less clear-cut. SELECT col1, col2 FROM theTable WHERE col1 BETWEEN 4000 AND 9000 might be better served in terms of reducing page reads by an index covering col1 and col2 (or one covering col1 and INCLUDEing col2) because otherwise there are extra pages needing to be read to bring in the values from col2.

If you are selecting all, or most of, the columns from the table, and your database engine supports clustered indexes[1] or similar[2], then that will be the best way to reduce page accesses for that sort of query. This is an instance where you will deviate from the default practise of having your primary key as the clustering key, as for obvious reasons you can only have one clustered index. If clustering is not supported, or you want the one clustered index on the table for something else, then again you'd need to use a wide covering index or an index that INCLUDEs all the other columns. Though in this latter case be careful you are not wasting space, and affecting write performance, with a wide index that isn't really needed.

[1] directly called a clustered index in SQL Server, what Oracle calls an "index organized table" is the same as a table with a clustered index in SQL Server

[2] postgres allows you to reorganize a table index order with the CLUSTER command


How to write a query that counts rows by date? I have a database named emp1. There is a table named tx_feeder. I want to get the count of rows from that table.

I have used the following query to get the data of single day,

select count (*) from tx_feeder
where datum between (select sysdate -31 from dual) and (select sysdate -1 from dual);

SQL> 3455890

It will give the count for the whole month. But instead of getting the count of the entire month as a whole, I want to get the output like following way, total count of each day in a month from a single SQL query.

     sysdate -31 = 11190
     sysdate -30 = 13390
     sysdate -29 = 65790
     sysdate -28 = 44390
     ........
     sysdate -1 = 14590

That SQL dialect is from Oracle but this answer will be for MySQL.

If datum is DATE

select count (*),datum date from tx_feeder
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by datum;

If datum is DATETIME or TIMESTAMP

select count (*),DATE(datum) date from tx_feeder
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by DATE(datum);

If you are looking for a grand total to be included, do WITH ROLLUP

select count (*),datum date from tx_feeder
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by datum WITH ROLLUP;

or

select count (*),DATE(datum) date from tx_feeder
where datum between (CURDATE() - INTERVAL 31 DAY)
and (CURDATE() - INTERVAL 1 DAY)
group by DATE(datum) WITH ROLLUP;


2NF and 3NF How to do it Given E(ABCDE) ABC are candidate keys

Normalize into 2NF and 3NF

As far as 2NF is concerned the solution is quite easy:

E1(BD), E2(DE), E3(ABC)

But with regard to 3NF I think I'm wrong if I say that nothing should be done.

Maybe the 3NF schema is:

E1(ABC), E2(BD)

Is it correct?

Thanks a lot

So according to @OliverAsmus the 3NF result should be:

E1(ABC), E2(BD)

But if what I wrote is correct, am I right in thinking that the 3NF (in this particular case) doesn't preserve all the attributes?
E doesn't depend upon any key so I got rid of it...

Is that correct? Thanks

You're on the right track.  Look closely at your 2NF result - this is where the 3NF result will come from.  Your next task would be to take the 2NF result and eliminate the fields that do not depend on the key.

There are numerous sources for how and why to do this, but I prefer Microsoft's explanation the best:

http://support.microsoft.com/kb/283878


Do I need to disable logins while upgrading SQL Server? I need to do an in-place upgrade for one of our critical servers: SQL 2012 to SQL 2016. This server has 200+ logins.

Is it advisable to DISABLE these logins for the upgrade? Or, SQL Server will take care of it.

I don't want anyone to login and screw-up something during the upgrade.

SQL Server certainly doesn't require you to disable the logins in order to upgrade, but it is going to terminate any sessions that are connected because it will stop the SQL Server service.  If you are going to install a service pack, and then a CU, for example, this will result in stopping, starting, stopping, and starting the SQL Server service.  So if you are concerned about users logging in and being disappointed because their query didn't finish, then yes, you might want to disable their logins first.

You can generate a T-SQL script to do this by running a query similar to the following:

SELECT 'ALTER LOGIN [' + name + '] DISABLE'
FROM sys.server_principals WHERE TYPE = 'S'
    AND  [name] NOT IN ('sa', '##MS_PolicyTsqlExecutionLogin##',
       '##MS_PolicyEventProcessingLogin##')

You may need to add some more logins to the NOT IN list--just be sure that you don't end up disabling the login(s) you need to administer the system!

A similar script to enable the logins can be generated by replacing DISABLE in the script above with ENABLE.

If there is a poorly designed application using a database on the server, it's possible that a user could start something that doesn't get properly finished before their session is terminated by the service shutdown.  This could happen if something that should be done in one transaction is instead being done in multiple transactions.  If the first transaction succeeds, but one or more of the related transactions fail, then you might have something half-baked.  Typically this is not something to be concerned about, but just including this because it's certainly possible.


Update Postgres Table with Array of JSON I am using Postgres  9.5, and I am trying to figure out how to update a postgres table using an array of JSON. I want each object in the array to coorespond to a new row, with each key cooresponding to a column, and each value is the data to be inserted into the column. I am trying to do so with a function. Here the the data format:

[
    { col1: a, col2: 5, col3: 1, col4: one},
    { col1: b, col2: 6, col3: 2, col4: two},
    { col1: c, col2: 7, col3: 3, col4: three},
    { col1: d, col2: 8, col3: 4, col4: four},
]

Here is my expected output:

 col1   (varchar)| col2 (integer) |   col3 (integer)   |   col4 (varchar)
-----------------+----------------+--------------------+------------------
    a            |  5             |     1              |    one
    b            |  6             |     2              |    two
    c            |  7             |     3              |    three
    d            |  8             |     4              |    four

Is there a built in postgres JSON function or operator that can do this for me? Or do I have to loop through the array and pull out each value, and pass it as an input? I know function below is wrong, but my goal is for the function to act similar to the following:

CREATE OR REPLACE FUNCTION UPDATE_TABLE_FUNC (
arrayOfValues TEXT[]
)
RETURN VOID AS $$
BEGIN
UPDATE table SET (col1, col2, col3, col4) = ($1)
END;
$$ LANGUAGE plpgsql;

If you know the desired column names (be it the same or different from the keys in the JSON structure, you can use json[b]_to_recordset():

SELECT * FROM jsonb_to_recordset('[
    { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
    { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
    { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
    { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
]'::jsonb) AS t (col1 text, col2 integer, col3 integer, col4 text);

 col1 │ col2 │ col3 │ col4
──────┼──────┼──────┼───────
 a    │    1 │    1 │ one
 b    │    2 │    2 │ two
 c    │    3 │    3 │ three
 d    │    4 │    4 │ four

As the documentation tells us,

  Note: In json_populate_record, json_populate_recordset, json_to_record and json_to_recordset, type coercion from the JSON is "best effort" and may not result in desired values for some types. JSON keys are matched to identical column names in the target row type. JSON fields that do not appear in the target row type will be omitted from the output, and target columns that do not match any JSON field will simply be NULL.

If you already have a table to work with, json_populate_recordset() is an even better solution:

CREATE TABLE inputtable (col1 text, col2 integer, col3 integer, col4 text);

SELECT * FROM jsonb_populate_recordset(NULL::yourtable, '[
    { "col1": "a", "col2": 1, "col3": 1, "col4": "one"},
    { "col1": "b", "col2": 2, "col3": 2, "col4": "two"},
    { "col1": "c", "col2": 3, "col3": 3, "col4": "three"},
    { "col1": "d", "col2": 4, "col3": 4, "col4": "four"}
]'::jsonb);

 col1 │ col2 │ col3 │ col4
──────┼──────┼──────┼───────
 a    │    1 │    1 │ one
 b    │    2 │    2 │ two
 c    │    3 │    3 │ three
 d    │    4 │    4 │ four

Now updating the table itself may be done like this:

WITH source AS (SELECT * FROM jsonb_populate_recordset [...])
UPDATE yourtable
   SET col1 = s.col1, col2 = s.col2
  FROM source AS s
 WHERE col3 = s.col3;

In case it seems slow, it might make sense to not use the CTE, but a subquery in the FROM clause instead.


What is the difference between *data dictionary* and *data dump of SEDE*? A meta post (https://meta.stackexchange.com/q/2677/714660) gives the "anatomy" of the data dump from SEDE that includes the structure of the public data dump(tables, columns, data types, etc) and Entity Relationship Diagram.

The stuff above looks similar to data dictionary.

So, what is the difference between data dictionary and data dump of SEDE?

Well, the data dump is the data itself, i.e. the questions, answers, comments, user information etc. of the Stack Exchange sites. Note that on Stack Exchange, 'data dump' usually refers to something else than SEDE: a quarterly dump of the data published in XML format. It contains more or less the same information as SEDE (which is the reason the schema Q&A is shared between them), but in a different format, and it isn't updated as often as SEDE is (SEDE is updated once a week, on Sunday morning).

There is no formal data dictionary for the entirety of SEDE; most of the schema documentation can only be found in the Database schema documentation for the public data dump and SEDE and isn't available as a database/repository/API itself. The schema is rather small and schemas of this size don't benefit as much from a data dictionary as large-scale applications like ERP software does.


Why does information_schema have "YES" and "NO" character strings rather than booleans? I was taken by surprise to find that the is_nullable column for this query is typed as character varying (3) rather than boolean:

select
    column_name,
    data_type,
    is_nullable
from
    information_schema.columns
where
    table_schema = 'public';

I noticed that some other column use "YES"/"NO" as well. What is the rationale for this? My initial thought was that perhaps other values besides "YES" and "NO" are possible, justifying something other than a boolean, but I didn't see any examples of this in my current database.

INFORMATION_SCHEMA

Using psql you can see the schema with \d information_schema.columns, or you can look it up in the docs.

                               View "information_schema.columns"
          Column          |                Type                | Collation | Nullable | Default
--------------------------+------------------------------------+-----------+----------+---------
... stuff cropped..
 is_nullable              | information_schema.yes_or_no       |           |          |

SQL Spec

Looking up information_schema.columns.is_nullable in the spec we can see it's the spec's fault -- as is often the case with stuff. You can't just erase 9,000 years of legacy. Especially with something that is supposed to provide a dependable  interface. From the SQL 2011 spec,

IS_NULLABLE INFORMATION_SCHEMA.YES_OR_NO
  CONSTRAINT COLUMNS_IS_NULLABLE_NOT_NULL NOT NULL,

And you can see the definition of INFORMATION_SCHEMA.YES_OR_NO defined in the spec too,

CREATE DOMAIN YES_OR_NO AS
  CHARACTER VARYING (3)
  CHARACTER SET SQL_IDENTIFIER
    CONSTRAINT YES_OR_NO_CHECK
    CHECK (VALUE IN ( 'YES', 'NO' ) );

GRANT USAGE ON DOMAIN YES_OR_NO
  TO PUBLIC WITH GRANT OPTION;

PostgreSQL's RDBMs-specific catalog

If you don't need the generic standardized interface though, you can usually find something more sane in pg_catalog. In this case you want pg_catalog.pg_attribute. Let's check it out \d pg_catalog.pg_attribute,

              Table "pg_catalog.pg_attribute"
    Column     |   Type    | Collation | Nullable | Default
---------------+-----------+-----------+----------+---------
 attnotnull    | boolean   |           | not null |

There is your boolean.

PostgreSQL Docs

The PostgreSQL docs on Information Schema: Data Types actually address this too,

  A character string domain that contains either YES or NO. This is used to represent Boolean (true/false) data in the information schema. (The information schema was invented before the type boolean was added to the SQL standard, so this convention is necessary to keep the information schema backward compatible.)


Simplify SQL Server trigger logic I have an Images table where that table has these basic columns —

ImageId (int)
VotesUp (int)
VotesDown (int)

Each image can get upvotes (up , down , retract vote)

The upvotes table has :

ImageId (int)
UserId (int)
Score (int )   [-1,0,+1]

So if —

A user inserts +1 score then  ( if not exists) I add a new row to upvotes table & update the value in the Images table ( for quick fetching)
A user changes its score from +1 to 0 then decrease votes up
A user changes its score from +1 to -1 then decrease votes up and increase
votes down
A user changes its score from 0 to +1 then increase votes up
A user changes its score from 0 to -1 then increase votes down
A user changes its score from -1 to 0 then decrease votes down
A user changes its score from -1 to +1 then decrease votes down  and increase votes up

It's a pretty simple logic.

Here is the trigger on the upvotes table :

alter TRIGGER [dbo].[UpvotesChanged]
ON [dbo].[Upvotes]
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS( SELECT 1  FROM   DELETED ) --update
BEGIN

UPDATE imgs
SET    VotesUp = CASE

                    WHEN deleted.Score = 1 AND INSERTED.score =0      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 0 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    ELSE ISNULL(VotesUp, 0)
                END
                ,
    VotesDown = CASE

                    WHEN deleted.Score = 0 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesDown, 0) -1
                    WHEN deleted.Score = -1 AND INSERTED.score =0      THEN ISNULL(VotesDown, 0) -1
                    ELSE ISNULL(VotesDown, 0)
                END
FROM   Images imgs
    JOIN DELETED
        ON  imgs.ImageId = deleted.ImageId
        JOIN INSERTED ON imgs.ImageId = INSERTED.ImageId

END
ELSE
--insert
BEGIN
UPDATE imgs
SET    VotesUp = CASE
                    WHEN INSERTED.Score = 1
                    THEN ISNULL(VotesUp, 0) + 1
                    ELSE VotesUp
                END,
    VotesDown = CASE
                    WHEN INSERTED.Score = -1
                        THEN ISNULL(VotesDown, 0) + 1
                    ELSE VotesDown
                END
FROM   Images imgs
    JOIN INSERTED
        ON  imgs.ImageId = INSERTED.ImageId
END
END

It's working as expected.

So where is the problem ?

Question :

I think this trigger can be simplified without all these if's/cases . I just can't see the simplification.

Is it possible to simplify this trigger?

Instead of using a trigger, I would use an indexed view.
That way, the two views of the data are automatically kept synchronized by SQL Server, without writing any trigger code. You also don't have to worry about subtle race conditions and other potential concurrency issues.
Indexed view
CREATE TABLE dbo.Upvotes
(
    ImageId integer NOT NULL,
    UserId integer NOT NULL,
    Score smallint NOT NULL,

        CONSTRAINT ValidScore
        CHECK (Score IN (-1, 0, +1)),

        CONSTRAINT PK_dbo_Upvotes__ImageId_UserId
        PRIMARY KEY (ImageId, UserId)
);
GO
CREATE VIEW dbo.Images
WITH SCHEMABINDING
AS
SELECT
    U.ImageId,
    VotesUp = SUM(CASE WHEN U.Score = +1 THEN 1 ELSE 0 END),
    VotesDown = SUM(CASE WHEN U.Score = -1 THEN 1 ELSE 0 END),
    NumRows = COUNT_BIG(*)
FROM dbo.Upvotes AS U
GROUP BY
    U.ImageId;
GO
CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.Images (ImageId);

The query in the view is essentially a pivot. It is not written using PIVOT syntax, since that is not allowed in a SQL Server indexed view.
For Enterprise/Developer Edition, the optimizer will make a cost-based decision to use the view's index(es), or expand the view and access the base tables. You can force using the view indexes with a NOEXPAND hint.
In other Editions you must use the NOEXPAND hint to avoid expanding the view. There are other reasons to prefer using NOEXPAND hints with indexed views.
Example
SELECT
    I.ImageId,
    I.VotesUp,
    I.VotesDown
FROM dbo.Images AS I WITH (NOEXPAND);


Extract only Date from the string I have one field in database and I am getting the multiple dates in the same field. I want to extract multiple dates from the string. Please help me on this
String like:

"7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul  7/3/2019 1:34 AM - Request assigned to   7/3/2019 3:22 AM - PO123456 created"

dbfiddle

DECLARE @string VARCHAR(MAX) = '7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created';

WITH abc(i,x,y) AS (
  SELECT
    1 as i,
    @string as X,
    @string as Y
  UNION ALL
  SELECT
    i+1,
    SUBSTRING(Y,1,CHARINDEX(' ',Y)),
    SUBSTRING(Y,CHARINDEX(' ',Y)+1,1024)
  from abc
  where X<>''
  )
select TRY_CAST(CONCAT(a1.X, a2.X , a3.X) as DATETIME) ValidDate
from abc a1
inner join abc a2 on a2.i=a1.i+1
inner join abc a3 on a3.i=a1.i+2
where not TRY_CAST(CONCAT(a1.X, a2.X , a3.X) as DATETIME) is null;

output:

ValidDate
03/07/2019 01:20:00
03/07/2019 01:34:00
03/07/2019 03:22:00

Using LAG, or LEAD, example with dbfiddle does not seem to be less expensive than the INNER JOIN's. Results show almost equal times..

Below the example with LAG:

DECLARE @string VARCHAR(MAX) = '7/3/2019 1:20 AM - Request submitted by Ekaterina Rahul 7/3/2019 1:34 AM - Request assigned to 7/3/2019 3:22 AM - PO123456 created';

WITH abc(i,x,y) AS (
  SELECT
    1 as i,
    @string as X,
    @string as Y
  UNION ALL
  SELECT
    i+1,
    SUBSTRING(Y,1,CHARINDEX(' ',Y)),
    SUBSTRING(Y,CHARINDEX(' ',Y)+1,1024)
  from abc
  where X<>''
  )
SELECT * FROM (
    SELECT
        TRY_CAST(concat(lag(a1.x,2) over (order by a1.i), lag(a1.x,1) over (order by a1.i), a1.x) as DATETIME) ValidDate
    from abc a1
    ) x
where not x.ValidDate is null;


How to restore SQL Server database to a specific day? Suppose I have an SQL Server 2008 which is installed on windows 2008. There is a db MyDb under the instance. MyDb is have full backup every night. Because of storage space on hard driver, only 7 days backup kept. the log on SQL Server is never truncated. Now the log file even is larger than 100GB. Then I am requested to restore the db to a day one month ago. how can I do that? Also can I shrink or truncate log file much smaller?

>I am requested to restore the db to a day one month ago. how can I do that?

you can't as you only have 7 days of backups due to disk space shortage. If you are/were moving the backups away and storing them for X amount of days then you would be able to go back that X amount of days to recover your data.

also, your database recovery model plays an important role in whether you can do a point-in-time restore or not - provided you are taking transaction log backups in full or bulk-logged recovery mode.

>Also can I shrink or truncate log file much smaller?

yes you can do it, but with some caution.

shrinking database is not a good idea unless you are sure that it won't grow to that point again.

refer to I Need to Shrink My Database - I just freed a lot of space for more details as it is covered is a nice and precise way.


database and table design for billions of rows of data basically i got 2 tables: header, details tables.

CREATE TABLE `header` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `RECORD_DATE` datetime DEFAULT NULL,
 `TICKER_ID` int(11) DEFAULT NULL,
 `CURR_TIMESTAMP` datetime DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `detail` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `HEADER_ID` int(11) DEFAULT NULL,
 `BROKER_ID` int(11) DEFAULT NULL,
 `AMOUNT` decimal(26,0) DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

There're about 4800 ticker need to be update everyday, ticker_id (A00001, B00032...) and each ticker_id contain numbers of record everyday and i store in detail table

it works fine at the beginning, after years, header become 2.4 million row and detail table got 250 millions row, its take an hour to with simple select,

SELECT h.ticker_id, h.record_date , d.broker_id, d.broker_id, d.amount
FROM DETAIL
INNER JOIN herder h
    ON h.id = d.header_id
where h.ticker = 'A00001'

so i create a 'link' table to join reference them together.

CREATE TABLE `linkA00001` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `HEADER_ID` int(11) DEFAULT NULL,
 `DETAIL_ID` int(11) DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

and the result become

SELECT h.ticker_id, h.record_date , d.broker_id, d.broker_id, d.amount
FROM DETAIL
INNER JOIN linkA00001 l
INNER JOIN herder h
    ON h.id = l.header_id and d.id = l.detail_id

its stunning fast, from hour to few seconds, but this approach solve 1 ticker selection only, so i create 4800 link table for better performance, its do solve the selection problem, but i using java spring for my application, building over 4800 entity require 10 minutes to start the application and very hard to maintain the link table.

so my question is:

this approach works?
if not, how can i improve or just redesign this?
according to this link, its require timestamp but i never insert at detail table, is it good practices add back?

Thank you very much!

To improve the query:

SELECT h.ticker_id, h.record_date , d.broker_id, d.broker_id, d.amount
FROM DETAIL
INNER JOIN header h
    ON h.id = d.header_id
WHERE
    h.ticker_id = 'A00001'

(ignoring that ticket_id is an INT in the table);

The aspect of the query that will limit the results most is the value of h.ticket_id. To find this quickly there need to be an index on this in table header.

 CREATE INDEX ticket ON header (TICKER_ID)

The database then need to find the HEADER_ID quickly in the detail table:

 CREATE INDEX header ON detail (HEADER_ID)

What is unclear in the question here is why detail has an auto_increment primary key. If this isn't used in any queries, and if HEADER_ID,BROKER_ID is unique, then perhaps those are a better primary key for the table and as an alternate to CREATE INDEX header above:

ALTER TABLE detail
DROP ID,
DROP PRIMARY KEY,
ADD PRIMARY KEY (HEADER_ID, BROKER_ID);

This will also speed up the query as primary keys are quicker to search than secondary keys (where non-index values like d.amount are also needed).


Migrate a SQL Server instance from one server to another I have a database for my website running on AWS.  My web admin has given me access to the database, and I'd like to host and manage it on my own.  How do I migrate the SQL database from AWS to my own SQL server?

The database is 17.5mb, so quite small. I am assuming you can RDP onto the server in AWS, or have the ability to RDP? If so, then simply take a backup of the database, copy the backup down to your local machine, and then restore it locally.


sql producing duplicate column id on join? Is there any way to only show one id column which is used to join on other tables?
This query produces two id columns used to join each other as primary and foreign key. I only want it to repeat the id column once.
SELECT *
FROM products
JOIN productimg ON products.id = productimg.id
WHERE products.id IN (1, 2, 3)

Query results:
id    name     cid  stk prc    id       url
 1  product1    1   10  3000    1   /static/images/dresses/td1.jpg
 2  product2    2   10  2500    2   /static/images/dresses/td2.jpg
 3  product3    2   4   4000    3   /static/images/dresses/td3.jpg

SELECT products.*, productimg.url
  FROM products JOIN productimg
  ON products.id = productimg.id
  WHERE products.id IN (1, 2, 3)


Need advice on combining tables or leaving as separate tables I tried to find a related question similar to mine, but couldn't. I apologize if this is a repeat.
I have an odd situation and just need advice. I have a company calendar that shows events as well as employee time off (PTO). These are in an event table.
Events Table
id
user_id
text
start_date
end_date

PTO Table
id
user_id
text
reason
length
start_date
end_date
type
status
overall_status

Here's where it gets a bit messy. There are a few identical columns in both tables, such as user_id, start_date, and end_date. If a user submits a request to change the PTO, I would need the make the changes in two tables.
My question is, is it better to combine the tables or update the related Event when a change is made? OR, is it better to find a way to pull data from two tables and combine them into one variable for display on the calendar.
Hope that's not confusing.

My advice is allowing PTO to be a sub-type of event.
If Event is a super-type it will have an additional field: type.
If type=PTO, then there should be an item in the PTO table, with the event_id to link them.
You can read about super-type/sub-type relationships on your favorite database information website, but a sub-type minimizes data duplication, and keeps similar data in one place. So, all events have a start and end date, but not all have a reason like vacations do.
Once the tables have been changed, you could add the start and end date from each PTO row to Event, and add the reason and other PTO data to the new, smaller PTO table. Then you could drop the PTO table once all the data has been moved.


Trying to run lines on a timer One of the things I need to do on my job is run lines of SQL code.  I need to do each line individually.  Which involves highlighting one line, executing it, then doing the next.  Sometimes it is up to 50 lines, so I'm trying to find an easier way.

Not giving specifics, basically each line reads

UPDATE que SET status = 0 WHERE doc = '001'; EXECUTE [things] '001'

UPDATE que SET status = 0 WHERE doc = '002'; EXECUTE [things] '002'

UPDATE que SET status = 0 WHERE doc = '003'; EXECUTE [things] '003'

etc etc

Is there some kind of command that will do each line one after another?  preferably with like a 10 second timer inbetween?

You can add WAITFOR DELAY between executions.

UPDATE que SET status = 0 WHERE doc = '001'; EXECUTE [things] '001';
WAITFOR DELAY '00:00:10';
UPDATE que SET status = 0 WHERE doc = '002'; EXECUTE [things] '002';
WAITFOR DELAY '00:00:10';
UPDATE que SET status = 0 WHERE doc = '003'; EXECUTE [things] '003';
WAITFOR DELAY '00:00:10';

Might be a good idea to add some (error) logging as to know where it failed if an error occured.

If you don't want to write 50 extra lines, you could use search and replace in ssms (CTRL+F) and replace 'UPDATE' with 'WAITFOR DELAY '00:00:10';  UPDATE'

Make sure you are on current document and then press replace all:

Result:


how to get avg() size by day using two decimal places does this simple query result in grouping the attachments by day and giving me the total number of daily attachments as well as the average size?

the only table i am querying is attachmentdetail (email attachments)

[attachmentdetail]
    AttachmentId     int
    Name             varchar
    Size             int
    IsInline     bit
    InsertedDatetime datetime

select
    count(*) as [Tally],
    avg( cast(size as decimal)/1024/1024) as avgSize,
    DateAdd(dd, DateDiff(dd, 0, InsertedDatetime), 0) As [date_only]
from attachmentdetail
where isinline <>1
group by DateAdd(dd, DateDiff(dd, 0, InsertedDatetime), 0)
order by [date_only] desc

also how to i keep the values i get from my avg() to 2 decimal places?

You should specify precision and scale when casting to a decimal, and perform that cast last. This blog post explains why you never want to leave out the size declaration for char/varchar/nchar/nvarchar, but similar arguments hold for decimal and a few other numeric types, too.

Also, on 2008+, CONVERT(DATE is a slightly more efficient (and easier to read) method of stripping time (see this post and this follow-up).

Here is how I would write your query:

SELECT
  [date_only] = CONVERT(DATE, InsertedDateTime),
  [Tally] = COUNT(*),
  avgSize = CONVERT(DECIMAL(10, 2), AVG(size/1024.0/1024))
FROM dbo.attachmentdetail
WHERE isinline <> 1
GROUP BY CONVERT(DATE, InsertedDateTime)
ORDER BY [date_only] DESC;

(Also, use schema prefixes and statement terminators. Always.)


Fastest way to find (rare) rows with a null value among millions of rows? If I have an addresses table with ~ 4 million rows in a Postgres database. Is there a way to optimize the following query so that I can find the few rows where address_id is NULL? Sometimes I need to write these queries to evaluate the state of the database. I'm pretty new to optimizing SQL, I'm used to working with much smaller databases.

SELECT address_id FROM addresses WHERE address_id IS NULL;

There is a very fast way, create a partial index:

CREATE INDEX foo_idx ON addresses (address_id)  -- arbitrary pick
WHERE address_id IS NULL;

The indexed column is largely irrelevant. Use any small column(s) that might be relevant to your queries. The important point is that only the few rows with address_id IS NULL end up in the index.

The index incurs a (very small) cost on write performance, obviously.

Related:

Very large btree index with few rows (openstreetmap gis data)


Same queries, different servers, different exec plans, stats are up to date I've got the same query running on two different servers, and getting vastly different performances.  I have updated all of the stats on dependent objects and it has not fixed the issue.  I'm lost on where to turn to next.

Here's what the plans look like, the top beign the dex execution (the one that works), and the bottom being the test execution (the slow one).

edit:
here's what the sql looks like:

ALTER procedure [ardb].[NewProjects]
    -- Add the parameters for the stored procedure here
as
begin
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    set NOCOUNT on ;

    -- Insert statements for procedure here

;with b as
(
    select distinct
        coalesce(a.STUDY_NUMBER,b.STUDY_NUMBER, c.study_number) as StudyNumber
    from
    (
        SELECT     SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
        FROM         appdb.PROD.dbo.PA01201 AS PA01201_1
        where PAPROJNUMBER>'0'
            and PAPROJNUMBER not like '%[a-z]%'
    ) a
        full outer join
        (
            SELECT     SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
            FROM        appdb.MO1.dbo.PA01201 AS PA01201_1
            where PAPROJNUMBER>'0'
                and PAPROJNUMBER not like '%[a-z]%'
        ) b
            on a.STUDY_NUMBER=b.STUDY_NUMBER
        full outer join
        (
            SELECT     STUDY_NUMBER
            FROM         appdb.ptwdb.dbo.tblARCompletedStudies
            where STUDY_NUMBER>'0'
        ) c
            on a.STUDY_NUMBER=c.study_number
                and b.STUDY_NUMBER=c.study_number
)

select
    snStudyNumber as ProjectNumber,
    c.clName as CompanyName,
    q.quQuoteNumber as QuoteNumber,
    q.quQuoteID as QuoteID,
    q.quDateWon as DateWon,
    s.snPTWCompletionDate as PTWCompletionDate
from CDB.cdb.Quotes q
    inner join CDB.cdb.LineItems l
        on q.PK_quID=l.FK_quID
    inner join CDB.cdb.StudyNumbers s
        on l.FK_snID=s.PK_snId
    inner join cdb.cdb.Clients c
        on q.FK_clID=c.PK_clID
    left outer join ardb.projects p
        on s.snStudyNumber=p.prProjectNumber
    left outer join b
        on s.snStudyNumber=b.studynumber
where q.quDateWon>''
    and s.snPTWCompletionDate >''
    and p.PK_prID is null
    and l.liCreationDate>'12/31/06'
    and b.studynumber is null
union all

select CAST(ProjectNumber AS int) as ProjectNumber,
    null as CompanyName,
    null as QuoteNumber,
    null as QuoteID,
    null as DateWon,
    null as PTWCompletionDate
from history.ProjectsToProcess a
    left outer join ardb.projects p
        on a.ProjectNumber=p.prProjectNumber
where p.PK_prID is null

order by ProjectNumber desc

end

xml plans: here

In the slow plan, the plan sub-tree that contains all the remote queries is executed 2928 times.  It would have been 3632 times (the number of rows on the outer input to the nested loops join that has the remote query sub-tree on its inner side), but the Distinct Sort is able to rewind (reuse) the prior iteration's results on a number of occasions.

In the fast plan, the join is a hash join rather than nested loops, so the problematic sub-tree is only executed once.  As usual, the cause is likely the small differences in cardinality estimation (visible in the plan as estimated row counts) and derived histograms and distribution statistics (not so visible).  This variation is probably due to slightly different samples taken to build statistics, or statistics built at different times between dev and test.

To quickly validate this, you could add OPTION (HASH JOIN) to the query.  There is a small risk that the optimizer will complain that it cannot produce a plan with that hint.  I haven't analysed the plan deeply enough to predict that, and this suggestion is just for demonstration purposes.  It is not my suggested solution.

A better fix is to break the query into sections.  Refactor the remote query sub-tree processing into a separate query, and store the 6737 rows it produces in a temporary table.  You will probably want to use a #temporary table rather than a table variable, since you will get auto-generated statistics, and you will have a wider range of indexing possibilities, should that prove to be useful.

Queries with many joins and remote queries have a very large number of possible arrangements.  By breaking the query up sensibly, and providing statistics (and perhaps indexes) you are giving the optimizer better information and a smaller search space to explore.  Your chances of a good plan go up commensurately.


PostgreSQL: How do I stop PostgreSQL from generating logs for PL/pgSQL functions? I've defined some PL/pgSQL functions which get invoked from the client (e.g. psql, but could be JDBC/Hibernate as well), which is connected using a non super user role. e.g. SELECT chk_pwd('(some.user,example.com)', 'P@ssW0rd'); I've also set log_statement = 'none' in the postgresql.conf of my server. That stops logging of all SQL statements. However, the signatures and invocations of the PL/pgSQL functions are still being logged, leaking sensitive data into the log files.
How do I prevent this?
On further investigation, it looks like the RAISE LOG statement in my function is causing this additional information to be printed. So, although it is just a log message, it seems to be getting treated as an error. Please correct me if I'm mistaken, but from reading the manual, there does not seem to be a way to suppress these additional messages while only printing the message from the RAISE LOG statement alone. An option like log_log_verbosity would have been useful.
2021-02-27 17:42:58.483 IST [13008] myrole@test_db CONTEXT:  PL/pgSQL function chk_pwd(email_t,character varying) line 10 at RAISE
2021-02-27 17:42:58.483 IST [13008] myrole@test_db STATEMENT:  SELECT chk_pwd('(some.user,example.com)', 'P@ssW0rd');

Finally, as per postgresql.conf, the definition of log_min_error_statement considers log at a higher error level than error, which is quite unintutive.I ended up resolving the situation by using RAISE NOTICE instead of RAISE LOG. However, the verbosity of NOTICE is controlled by log_error_verbosity. So, it continues to show the CONTEXT, though thankfully not the STATEMENT, thus preventing the leakage of sensitive values from the function parameters.
Strictly speaking, I would not expect log_error_verbosity to be applicable to NOTICE, since it is not an error. Verbosity for NOTICE should be controlled by log_notice_verbosity (or log_non_error_verbosity), but there is no such setting available.

The presence of CONTEXT depends on log_error_verbosity.
Another setting you might want to tune down is log_min_error_statement.
From the documentation:
log_min_error_statement (enum)

Controls which SQL statements that cause an error condition are
recorded in the server log. The current SQL statement is included in
the log entry for any message of the specified severity or higher.
Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE,
WARNING, ERROR, LOG, FATAL, and PANIC. The default is ERROR, which
means statements causing errors, log messages, fatal errors, or panics
will be logged. To effectively turn off logging of failing statements,
set this parameter to PANIC. Only superusers can change this setting.

log_error_verbosity (enum)

Controls the amount of detail written in the server log for each
message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE,
each adding more fields to displayed messages. TERSE excludes the
logging of DETAIL, HINT, QUERY, and CONTEXT error information. VERBOSE
output includes the SQLSTATE error code (see also Appendix A) and the
source code file name, function name, and line number that generated
the error. Only superusers can change this setting.


Security per database in SQL Azure Pool I am new to SQL Azure and need to plan on moving some of our SQL Server Databases.

We have a SQL Server Azure Pool which can take up to 50 databases.

I need to restrict certain users to specific databases inside the pool.

E.g. User1 can only Access Database Northwind -thus when he connects with SSMS to the Azure Pool, this is the only database he can see (he can't see the other 49)

Is setting up this possible - or is it a case that if you have access to the Instance (the whole pool) - then you will have access to all the DBs inside that pool?

This would mean that we would need to move each database to its own instance based on security?
It will also have the implication that whatever SQL username/password is used in the applications web.config, that user in the config will have access to all the databases in the SQL instance (servername)

I am looking at the SQL Azure training on Pluralsight and seeing that there are database level firewalls based on IP - in this case we have multiple devs - each with same IP - but specific devs must only have access to specific databases. Thus one IP rule per database on the instance will not work since username is not evaluated.

You can restrict user per database.  I will explain here using sql authentication and you can do the same if you have AD set up.

I have 3 database and 'taiobmdjamshed' is the server admin and have access to all the databases.

I create security1 login with db_owner privilege to security1 database and a user at master database.

CREATE LOGIN security1
    WITH PASSWORD = 'S<<MM12345!@@!'
GO

Create a user in master database.

CREATE USER security1
    FOR LOGIN security1
    WITH DEFAULT_SCHEMA = dbo
GO

Make a user in security1 database and make it a member of db_owner role.

CREATE USER security1
    FOR LOGIN security1
    WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'security1'
GO

If connecting to the server using security1 login and try to access security2 database this is what will happen because security1 login does not have a corresponding user or privilege in security2 database.

This link explains more in details.


Compare two tables and find dates present in one and missing in another I have a month table tbl_month having only one column cal_dates which is list of dates for a month. And another table tbl_employee is having employee_code and attendance_date and it will have entry of employee code and the particular date which he submits the attendance. I need to find only the absent dates of each employee.

 TBL_MONTH
 -------------
ID CAL_DATES
1   2017/01/01
2   2017/01/02
3   2017/01/03
--  --
--  --
30  2017/01/30

 TBL_EMPLOYEES
 -------------
EMP_ID ATTENDANCE_DATE
001   2017/01/01
001   2017/01/02
002   2017/01/01

Here employee 001 have not entered attendance for '2017/01/03' and employee 002 have not entered attendance for '2017/01/02' and '2017/01/03'. I want to fetch this missed dates alone. I'm using below query, I missed something here.

DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)
select @PivotColumns=COALESCE(@PivotColumns + ',','') + convert(varchar(20),CALENDAR_DATE,120)
FROM (SELECT  CAL_DATES FROM [dbo]. [TBL_MONTH]) AS PivotExample
select @PivotColumns

SET @SQLQuery = 'select B.EMP_ID,'+@PivotColumns+'
                from TBL_MONTH A
                RIGHT OUTER JOIN
                TBL_EMPLOYEE B
                WHERE A.CAL_DATES = B.ATTENDANCE_DATE
                PIVOT( max(A.CAL_DATES)
                FOR B.ATTENDANCE_DATES IN
                ('+@PivotColumns+')
                ) AS P';

SELECT @SQLQuery

As far as you have not provided an expected result I've used this:

emp_id | 01/01/2017 | 02/01/2017 | 03/01/2017 | 04/01/2017 | 05/01/2017 | 06/01/2017 | 07/01/2017
:----- | ---------: | ---------: | ---------: | ---------: | ---------: | ---------: | ---------:
001    |          1 |          1 |          1 |          1 |          0 |          1 |          1
002    |          1 |          1 |          0 |          0 |          1 |          1 |          1
003    |          1 |          0 |          1 |          1 |          1 |          1 |          1

dbfiddle here

Where 1 means attendance and 0 not attendance.

I've used next script to dynamically generate the pivot table.

DECLARE   @SQLQuery AS NVARCHAR(MAX) = '';
DECLARE   @PivotColumns AS NVARCHAR(MAX) = null;

select  @PivotColumns = COALESCE(@PivotColumns + ',','')  + '[' + convert(varchar(20),cal_dates,103) + ']'
FROM    (select cal_dates from tbl_month) m;

SELECT @SQLQuery =
'with allDates as
 (
 select      te.emp_id, tm.cal_dates
 from        tbl_month tm
 cross apply (select distinct emp_id
              from tbl_employees) te
 )
 select    emp_id,' + @PivotColumns +
 'from      (select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates,
                       case when te.attendance_date is null then 0 else 1 end as Assist
                       from      allDates
                       left join tbl_employees te
                       on        te.emp_id = allDates.emp_id
                       and       te.attendance_date = allDates.cal_dates) source
PIVOT
(sum(Assist) FOR cal_dates IN (' + @PivotColumns + ')) piv'

exec (@SQLQuery)

First CTE generates all possible dates using tbl_month, you should set some filter (date between MinDate and MaxDate) or it will use the whole calendar table.

with allDates as
(
select      te.emp_id, tm.cal_dates
from        tbl_month tm
cross apply (select distinct emp_id
             from tbl_employees) te
)

Data source

Using a LEFT JOIN with all possible dates, I've set a mark (0/1) depending on employees attendance.

select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates,
          case when te.attendance_date is null then 0 else 1 end as Assist
from      allDates
left join tbl_employees te
on        te.emp_id = allDates.emp_id
and       te.attendance_date = allDates.cal_dates

Pivot columns

Keep in mind I've used CONVERT(varchar(20),cal_dates,103), as a result you get a date format as 'DD/MM/YYYY'. If you want to change it, remember to change both, pivot columns generator and data source select.

No attendance only

If you want to fetch 'Non attendance' only, you can filter data source on this way:

select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates,
          case when te.attendance_date is null then 0 else 1 end as Assist
from      allDates
left join tbl_employees te
on        te.emp_id = allDates.emp_id
and       te.attendance_date = allDates.cal_dates
and       te.attendance_date is null

Change dates by employess

Once you have defined the dynamic query, you can easily change final output, and interchange dates by employees.

DECLARE   @SQLQuery AS NVARCHAR(MAX) = '';
DECLARE   @PivotColumns AS NVARCHAR(MAX) = null;

select  @PivotColumns = COALESCE(@PivotColumns + ',','')  + '[' + emp_id + ']'
FROM    (select distinct emp_id from tbl_employees) m;

SELECT @SQLQuery =
'with allDates as
 (
 select      te.emp_id, tm.cal_dates
 from        tbl_month tm
 cross apply (select distinct emp_id
              from tbl_employees) te
 )
 select    cal_dates,' + @PivotColumns +
 'from      (select    allDates.emp_id, convert(varchar(20), allDates.cal_dates, 103) as cal_dates,
                       case when te.attendance_date is null then 0 else 1 end as Assist
             from      allDates
             left join tbl_employees te
             on        te.emp_id = allDates.emp_id
             and       te.attendance_date = allDates.cal_dates) source
  PIVOT
  (sum(Assist) FOR emp_id IN (' + @PivotColumns + ')) piv'

exec (@SQLQuery)

This is the result:

cal_dates  | 001 | 002 | 003
:--------- | --: | --: | --:
01/01/2017 |   1 |   1 |   1
02/01/2017 |   1 |   1 |   0
03/01/2017 |   1 |   0 |   1
04/01/2017 |   1 |   0 |   1
05/01/2017 |   0 |   1 |   1
06/01/2017 |   1 |   1 |   1
07/01/2017 |   1 |   1 |   1

dbfiddle here


Set SA to weak Password in SQL Server 2012 Hi I'm migrating a database to SQL Server 2012, and I have a problem.

Let me start by saying the application (ISV) has horrible security! So try not to cringe too much.

The app logs in with sa authentication.
The sa password is hard-coded into the app.

So the sa password on the 2012 instance must match the app login. However, it does not meet the 2012 password requirements.

Is there a way to override this functionality?

Try

 ALTER LOGIN sa
 WITH PASSWORD = 'password',
  CHECK_POLICY = OFF

(I feel dirty now)

In order to mitigate some of the potential horrors this allows, I'd recommend renaming the built-in sa account to something else, then creating a new login called sa with the rights the app actually requires.