How can I track down what's filling my 'tempdb' transaction log? We have a large and fairly well tuned SQL Server 2016 Enterprise Edition. Given the number of cores we have, our tempdb is currently comprised of 16 2,950 MB files running on a ram disk with a 16 GB transaction log file. All of the files are set to not autogrow and, as a general rule, this has run fine.

We have recently started to get some random "transaction log for tempdb database is full" errors. There's no set time when this is occurring so it is probably some user interaction. Since all interaction is through stored procedures, it's most likely some strange set of parameters or data that's causing the problem but we are unable to track down exactly what might be causing it. Any suggestions that might help us identify the culprit are appreciated.

I have been using the information in How to identify which query is filling up the tempdb transaction log? to track this down. And that gives valuable information but it doesn't give me a way to actually determine what the spurious circumstances are that is causing the transaction log to fill up.

Is there some way to trigger a snapshot or log what is actually causing the issue. Worst case I can write a monitoring program that pings the server every half second and uses variations on those queries to capture anything that is huge and has an open transaction but that still doesn't guarantee that I will actually catch whatever is causing the issue.

You could use a SQL Server Agent Alert to automatically perform some operation whenever the transaction log crosses a percent-used-threshold.

By way of an example, the following automatically emails the results of one of the queries from Aaron Bertrand's answer on a question about how to identify which query is filling up the tempdb transaction log whenever the tempdb transaction log becomes 80 percent full.

USE [msdb]
GO

BEGIN TRANSACTION;

DECLARE @ReturnCode INT;
DECLARE @msg nvarchar(1000);
DECLARE @jobId BINARY(16);
DECLARE @DatabaseName sysname;
DECLARE @DBAEmailAddress nvarchar(100);
DECLARE @JobName sysname;
DECLARE @JobCommand nvarchar(max);
DECLARE @PerformanceCondition nvarchar(512);

/*
    Change the parameters below to suit
*/
SET @DatabaseName = 'tempdb';
SET @DBAEmailAddress = '<email address here>';
SET @JobCommand = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @msg_body nvarchar(max) = N'''';

;WITH s AS
(
    SELECT
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count
          + s.internal_objects_alloc_page_count)
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count
      + s.internal_objects_alloc_page_count) > 0
)
SELECT @msg_body = @msg_body + N''
'' + CONVERT(nvarchar(10), s.session_id) + N''	''
    + N'''' + CONVERT(nvarchar(10), s.[pages]) + N''	''
    + N'''' + COALESCE(t.[text], N'''') + N''	''
    + N'''' + COALESCE(NULLIF(
        SUBSTRING(
            t.[text]
            , r.statement_start_offset / 2
            , CASE WHEN r.statement_end_offset < r.statement_start_offset
                THEN 0
                ELSE( r.statement_end_offset - r.statement_start_offset ) / 2
            END
          )
          , ''''
        )
    , COALESCE(t.[text], N'''')) + N''
''
FROM s
    LEFT OUTER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

SET @msg_body = N''

session_id	pages	text	statement text
'' + @msg_body + N''
'';

EXEC msdb.dbo.sp_send_dbmail @profile_name = N''DBA''
    , @recipients = N''mvernon@mpi.mb.ca''
    , @subject = N''tempdb task space usage''
    , @body_format = N''HTML''
    , @body = @msg_body;
';

SET @ReturnCode = 0;

/*
    Add an operator to receive email alerts
*/
IF NOT EXISTS (
    SELECT 1
    FROM dbo.sysoperators so
    WHERE so.name = N'DBA'
    )
BEGIN
    EXEC msdb.dbo.sp_add_operator @name=N'DBA'
        , @enabled = 1
        , @email_address = @DBAEmailAddress
        , @category_name = N'[Uncategorized]';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Added "DBA" operator.';
    PRINT @msg;
END
ELSE
BEGIN
    SET @msg = N'DBA operator already exists.';
    PRINT @msg;
END

/* Add a job category*/
IF NOT EXISTS (
    SELECT name
    FROM msdb.dbo.syscategories
    WHERE name = N'Reliability'
        AND category_class = 1
    )
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
        , @type = N'LOCAL'
        , @name = N'Reliability';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    PRINT N'Added "Reliability" job category.';
END
ELSE
BEGIN
    SET @msg = N'Job category "Reliability" already exists.';
    PRINT @msg;
END

/*
    Add a job that performs a backup of the target database's transaction log
    This should free up space in the transaction log, assuming nothing else
    is preventing re-use of virtual log files, such as transactional replication,
    Database Mirroring, participation in an Availability Group, or an open
    transaction.
*/
SET @JobName = @DatabaseName + N' - log space usage' ;
IF NOT EXISTS (
    SELECT 1
    FROM dbo.sysjobs sj
    WHERE sj.name = @JobName
    )
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @JobName
        , @enabled = 1
        , @notify_level_eventlog = 3
        , @notify_level_email = 2
        , @notify_level_netsend = 0
        , @notify_level_page = 0
        , @delete_level = 0
        , @description = N'No description available.'
        , @category_name = N'Reliability'
        , @owner_login_name = N'sa'
        , @notify_email_operator_name = N'DBA'
        , @job_id = @jobId OUTPUT;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Added "' + @JobName + '" job.';
    PRINT @msg;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
        , @step_name = N'email space usage report'
        , @step_id = 1
        , @cmdexec_success_code = 0
        , @on_success_action = 1
        , @on_success_step_id = 0
        , @on_fail_action = 2
        , @on_fail_step_id = 0
        , @retry_attempts = 0
        , @retry_interval = 0
        , @os_run_priority = 0
        , @subsystem = N'TSQL'
        , @command = @JobCommand
        , @database_name = @DatabaseName
        , @flags = 0;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Added "email space usage report" job step.';
    PRINT @msg;

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
        , @start_step_id = 1;
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Job is configured to start at step 1.';
    PRINT @msg;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
        , @server_name = N'(local)';

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
END
ELSE
BEGIN
    SET @jobId = (
        SELECT sj.job_id
        FROM dbo.sysjobs sj
        WHERE sj.name = @JobName
        );
    SET @msg = N'"' + @JobName + N'" job already exists.  Using that job''s JobID.';
    PRINT @msg;
END

IF @jobId IS NOT NULL
BEGIN
    /*
        Add an alert to fire the above job whenever the Transaction log crosses 80% full
    */
    SET @JobName = @@SERVERNAME + N' ' + @DatabaseName + N' : 80pct TxLog Alert';
    IF NOT EXISTS (
        SELECT 1
        FROM dbo.sysalerts sa
        WHERE sa.name = @JobName
        )
    BEGIN
        IF SERVERPROPERTY('InstanceName') IS NOT NULL
        BEGIN
            SET @PerformanceCondition = N'MSSQL$' + CONVERT(nvarchar(128), SERVERPROPERTY('InstanceName')) + N':Databases|Percent Log Used|' + @DatabaseName + '|>|80'
        END
        ELSE
        BEGIN
            SET @PerformanceCondition = N'MSSQL:Databases|Percent Log Used|' + @DatabaseName + '|>|80';
        END
        EXEC @ReturnCode = msdb.dbo.sp_add_alert @name = @JobName
                , @message_id = 0
                , @severity = 0
                , @enabled = 1
                , @delay_between_responses = 300
                , @include_event_description_in = 7
                , @category_name = N'[Uncategorized]'
                , @performance_condition = @PerformanceCondition
                , @job_id = @jobId;

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
        SET @msg = N'Added "' + @JobName + '" alert.';
        PRINT @msg;

        EXEC msdb.dbo.sp_add_notification @alert_name = @JobName
            , @operator_name = N'DBA'
            , @notification_method = 1;
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
        SET @msg = N'Added job notification to email the DBA.';
        PRINT @msg;
    END
    ELSE
    BEGIN
        SET @msg = N'Alert already exists.';
        PRINT @msg;
    END
END
ELSE
BEGIN
    SET @msg = @JobName + N' not found.  Aborting.';
    RAISERROR (@msg, 14, 0) WITH NOWAIT;
END

COMMIT TRANSACTION
GOTO EndSave

QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;

EndSave:
GO


Mysql Replication - How it works offline? i have a mysql server on a local machine but the connection is not got for users outside the local network. I want that database to be available on another server wich can handle way more requests and is more stable than the local server.
My question is:
   What is the best way to replicate the data from the local server into the web server?
   I've readed a little about slave servers, are they made for this case?
   If i use a mysql slave server and the internet on the main server goes offline, what happens to the slave?
   If the main server makes some database changes while offline will these changes be written on the slave when the master goes online again?
   Are there any performace loss for using a slave server?

I do not want a backup server, i want a server to redirect ouside queries into it, so the main server wont be affected for huge traffic and etc.

Replication sounds like what you need, as long as the slave server you envision will be used as read-only.

Connecting a slave (replica) server to a master imposes very little load on the master.  If the connection between the two is severed for whatever reason, the slave will continue to answer queries, and normally will reconnect to the master as soon as practical and pick up exactly where it left off.  Certain disruptions can cause you to have to take steps to get replication going again, but ordinarily this is automatically done.

Using a replica this way is common.

I produce several hundred scheduled reports every day, many of which evaluate many millions of rows... and the reports are run against slave servers, to relieve the master of this additional workload.  In some cases, the slave is many thousands of miles away from the master, so, naturally, the connections are periodically lost... but they restart themselves.


Inheritance discriminator column better int or varchar or enum? What is the best practice for storing a discriminator column ? Does it make any difference ?

Any pros/cons of using one over the other ?

I am using MySql with Entity Framework but the question goes beyond the underlying technology i think.

Use always the smallest data types that fits the values it will hold. 'Fit' is a flexible term, depending on thing like:

Can it hold all current values of the table?
Can it hold future values of the table?
Is the data in a normal form? Can I create duplicate values? (think string vs. integer id that is a foreign key to an external table)
Can I insert undesired values? Will I control that at db or application level?
Is it easy to use/compatible with my application? (think ASCII encoding vs. utf8) Some datatypes are not "sql standard" and some frameworks do not recognize them.

Once you have those under consideration, use the smallest data type, as it will reduce memory and disk footprint (potentially reducing IOPS).

An int will take 4 bytes always, and it can store values from -2147483648 to 2147483647 (or from 0 to 4294967295, if using unsigned).
A varchar will take 1 + char_encoding * length (or 2 + char_encoding * length if it is larger than 255). char_encoding can be between 1 and 4 bytes, depending on the character and the encoding. Also, that assumes dynamic storage, fixed row engines like memory will store it with the maximum theoretical size. Usually inefficient for numeric values.
An enum will take 1 byte (or 2, if it holds more than 255 values), but will not allow for values not defined at creation time without an ALTER.

So it is always a trade-off between flexibility and performance.

Inheritance serialization, however, deserves its own discussion.


How to ensure security with SQL Server Restore I'm building a shared hosting SQL Server tool for linux, where users only receive access to their own database, with a SQL Login and SQL Database User that has db_datareader, db_datawriter and db_ddladmin rights to make sure they can not run any destructive actions on the database server (or choose settings we don't want them to).

We want to enable them to restore backups through a script. Obviously we'll run this restore with a sysadmin account to facilitate this for them (as a means of privilege escalation). Now on the Microsoft documentation site here the following notes are made (quoted verbatim)

  We recommend that you do not attach or restore databases from unknown or untrusted sources. These databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

Herein lies my problem. How can I make sure that I do not risk my database server security when allowing end-users (unknown an untrusted sources) to restore arbitrary backup files? Since this process would be automated, there's no time for 'user intervention', but instead perhaps I need to (re)set some safe defaults and/or settings. Does anyone has guidance on what would be my best approach?

I think your best approach would be to supply each user with his or her personal database engine process.
With Linux, consider using Docker or some other container framework which further enhances your security by sandboxing the SQL server process.

One of the problems that may arise from your approach, clifton_h already mentioned some other things to watch for in his comment:
Any user that is member of the "public" Server Role will be able to see at least the names of all databases on the instance when connecting with SQL Server Management Studio - but if you set the securable "View any database" to "Deny", the user won't even be able to browse databases he is allowed to connect to using the Object Explorer (USE [databasename], however, remains possible). This will sure cause some headscratching among your users and desparate calls on your help line :)

At the very minimum do not allow using contained databases (see https://docs.microsoft.com/en-us/sql/relational-databases/databases/security-best-practices-with-contained-databases?view=sql-server-2017), the already mentioned "View any database" permission and the items that are part of the Surface Area Configuration Policy (https://docs.microsoft.com/en-us/sql/relational-databases/security/surface-area-configuration?view=sql-server-2017):


Modelling a database structure for multiple user types and their contact information I'm designing a database that will store users of varying types. Predominantly (but not exclusively) they will be Actors, Directors and Writers. Currently there are just four user types that are of relevance. There is a outside chance that this number may increase, but the probability is low - and in such a case would be by a very small number.

The plan is to have a users table responsible pretty much solely for logging into the site (name, email and password columns plus one or two others such as whether they've been approved, and updated_at), and additional tables for each of the respective user types that each have their own unique set of columns. Only actors, for example, will have an ethnicity column, only Directors would have a bio column, and only Writers would need to provide their location. However, as I've not managed a database of this complexity before, I'm wondering how to organise a couple of aspects:

Firstly, users can be any one, or any combination, of the above types. So I understand I would need something like (for example) a director_user table with director_id and user_id columns. Would this then be sufficient to be able to filter all users by role type and so on?

Secondly, most users will the option of a twitter profile and phone number. And all actors will have to include at least one URL for any of their other online actor profiles; currently there are three that they can include, but this number may increase. Am I right in assuming that a separate table for each of the possible profiles/contact methods is an optimal way to organise data?

According to my interpretation of your description of the relevant business context, you are dealing with a supertype-subtype1 structure where (a) Actor, Director and Writer are entity subtypes of (b) Person, their entity supertype, and (c) said subtypes are not mutually exclusive.
In this way, if you are interested in building a relational database that mirrors such a scenario accurately —and hence are expecting that it functions as such—, your following comment clarifications are quite significant with respect to the previous points, because they have implications at both (1) the conceptual and (2) the logical levels of representation of the database in question:

[…] additional tables for each of the respective user types that each have their own unique set of columns.
[…] there are just four user types that are of relevance. There is a outside chance that this number may increase, but the probability is low - and in such a case would be by a very small number.

I will elaborate on all those aspects and several other critical factors in the sections below.
Business rules
In order to first define the corresponding conceptual schema —which can be used as a subsequent reference so that you can adapt it to make certain that it meets the exact informational requirements—, I have formulated some business rules that are of particular importance:

A Person may carry out one-two-or-three (i.e., one-to-all) Roles2. In other words, a Person may be

an Actor and/or
a Director and/or
a Writer.

A Person may log in via zero-or-one UserProfile.
An Actor provides one-two-or-three URLs3.
An Actor is grouped by one Ethnicity.
An Ethnicity groups zero-one-or-many Actors.
A Writer is based in one Location.
A Location is the base of zero-one-or-more Writers.

Expository IDEF1X diagram
Then, I created the IDEF1X4 diagram shown in Figure 1, which groups all the formulations above along with other rules that appear pertinent:

As demonstrated, the Person supertype (i) has its own box, (ii) possesses the properties or attributes that apply to all the subtypes, and (iii) presents lines that connect it with the boxes of every subtype.
In turn, every subtype (a) appears in its own dedicated box, and (b) holds only its applicable properties. The PRIMARY KEY of the supertype, PersonId, migrates5 to the subtypes with the role names6 ActorId, DirectorId, and WriterId respectively.
Also, I avoided coupling Person with the UserProfile entity type, which permits separating all their contextual implications, associations or relationships, etc. The PersonId property has migrated to UserProfile with the role name UserId.
You state in the question body that

And all actors will have to include at least one URL for any of their other online actor profiles; currently there are three that they can include, but this number may increase.

…so URL is an entity type in its own right, and is directly associated with the Actor subtype in accordance with this quote.
And, in comments, you specify that

[…] an actor will have a headshot (photo), while a writer will not […]

…then, among other features, I included Headshot as a property of the Actor entity type.
As for the Ethnicity  and the Location entity types, they of course may entail more complex organizations (e.g., an Actor may belong to one, two or more different ethnic groups in distinct proportions, and a Writer may be based on a place that requires recording country, administrative region, county, etc.) but it looks like that the needs of your business context are covered successfully with the structures here modelled.
Naturally, you can make as many adjustments as necessary.
Illustrative SQL-DDL logical design
Consequently, based on the IDEF1X diagram shown and described above, I wrote the logical DDL layout that is shown as follows (I have supplied notes as comments that explain some of the characteristics that I esteem particularly important with respect to the tables, columns and constraints declared):
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business needs.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE Person ( -- Represents the supertype.
    PersonId        INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    TwitterProfile  CHAR(30) NOT NULL,
    PhoneNumber     CHAR(30) NOT NULL,
    EmailAddress    CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Person_PK  PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT Person_AK2 UNIQUE (TwitterProfile), -- ALTERNATE KEY.
    CONSTRAINT Person_AK3 UNIQUE (EmailAddress)    -- ALTERNATE KEY.
);

CREATE TABLE Ethnicity ( -- Its rows will serve a “look-up” purpose.
    EthnicityId     INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    Description     CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Ethnicity_PK PRIMARY KEY (EthnicityId),
    CONSTRAINT Ethnicity_AK UNIQUE      (Description)
);

CREATE TABLE Actor ( -- Stands for one of the subtypes.
    ActorId         INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Headshot        CHAR(30) NOT NULL, -- May, e.g., contain a URL indicating the path where the photo file is actually stored.
    EthnicityId     INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Actor_PK            PRIMARY KEY (ActorId),
    CONSTRAINT ActorToPerson_FK    FOREIGN KEY (ActorId)
        REFERENCES Person (PersonId),
    CONSTRAINT ActorToEthnicity_FK FOREIGN KEY (EthnicityId)
        REFERENCES Ethnicity (EthnicityId)
);

CREATE TABLE Director ( -- Denotes one of the subtypes
    DirectorId      INT       NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Bio             CHAR(120) NOT NULL,
    Etcetera        CHAR(30)  NOT NULL,
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Director_PK         PRIMARY KEY (DirectorId),
    CONSTRAINT DirectorToPerson_FK FOREIGN KEY (DirectorId)
        REFERENCES Person (PersonId)
);

CREATE TABLE Country (
    CountryCode     CHAR(2)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Country_PK PRIMARY KEY (CountryCode),
    CONSTRAINT Country_AK UNIQUE      (Name)
);

CREATE TABLE Location ( -- Its rows will serve a “look-up” purpose.
    CountryCode     CHAR(2)  NOT NULL,
    LocationCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Location_PK PRIMARY KEY (CountryCode, LocationCode),
    CONSTRAINT Location_AK UNIQUE      (CountryCode, Name)
);

CREATE TABLE Writer ( -- Represents one of the subtypes.
    WriterId        INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    CountryCode     CHAR(2)  NOT NULL,
    LocationCode    CHAR(3)  NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Writer_PK           PRIMARY KEY (WriterId),
    CONSTRAINT WriterToPerson_FK   FOREIGN KEY (WriterId)
        REFERENCES Person (PersonId),
    CONSTRAINT WriterToLocation_FK FOREIGN KEY (CountryCode, LocationCode)
        REFERENCES Location (CountryCode, LocationCode)
);

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    UserName        CHAR(30) NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK         PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK         UNIQUE      (UserName), -- ALTERNATE KEY.
    CONSTRAINT UserProfileToPerson_FK FOREIGN KEY (UserId)
        REFERENCES Person (PersonId)
);

CREATE TABLE URL (
    ActorId       INT      NOT NULL,
    Address       CHAR(90) NOT NULL,
    Etcetera      CHAR(30) NOT NULL,
    AddedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT URL_PK        PRIMARY KEY (ActorId, Address), -- Composite PRIMARY KEY.
    CONSTRAINT URLtoActor_FK FOREIGN KEY (ActorId)
        REFERENCES Actor (ActorId)
);

This has been tested in this db<>fiddle that runs on MySQL 8.0.
Therefore, (1) every singular aspect of the logical layout above carries a very precise meaning from (2) a singular feature of the business environment of interest7 —in agreement with the spirit of the relational framework by Dr. Edgar Frank Codd—, because:

Each base table represents an individual entity type.

Each column stands for a single property of the respective entity type.

A specific data type is fixed for each column in order to ensure that all the values it contains belong to a particular and properly-delimited set a , be it INT, DATETIME, CHAR, etc (and let us hope that MySQL will finally incorporate DOMAIN support in near-future version).

Multiple constraints are configured (declaratively) in order to guarantee that the assertions in form of rows retained in all the tables comply with the business rules determined at the conceptual level.

Each row is meant to convey well-defined semantics, e.g., a Person row is read:
The Person identified by PersonId r is called by the FirstName s and the LastName t, was born on BirthDate u, has the GenderCode v, tweets on the TwitterProfile w, is reached through PhoneNumber x, is contacted via the EmailAddress y, and was registered on CreatedDateTime z.

Having a layout like this is decidedly favourable, as you can derive new tables (e.g., SELECT operations that gather columns FROM multiple tables with the help of the JOIN clause) that —in succession— carry a very precise meaning too (see the section entitled “Views” below).
It is woth to mention that, with this configuration, (i) a row representing a subtype instance is identified by (ii) the same PRIMARY KEY value that distinguishes the row denoting the complementary supertype occurrence. Thus, it is more than opportune to note that

(a) attaching an extra column to hold system-generated and system-assigned surrogates8 to (b) the tables standing for the subtypes is (c) entirely superfluous.

With this logical design, if new subtypes are defined as relevant in your business context, you would have to declare a new base table, but that happens as well when other kinds of entity types are deemed of significance, so said situation would be, in fact, ordinary.
Views
In order to “fetch”, e.g., all the information that corresponds to an Actor, Director or Writer, you can declare some views (i.e., derived or expressible tables) so that you can SELECT directly from one single resource without having to write the concerning JOINs every time; e.g., with the VIEW declared below, you can obtain the “full” Actor information:
--
CREATE VIEW FullActor AS

    SELECT P.FirstName,
           P.Lastname,
           P.BirthDate,
           P.GenderCode,
           P.TwitterProfile,
           P.PhoneNumber,
           P.EmailAddress,
           A.Headshot,
           E.Name AS Ethnicity
         FROM Person P
         JOIN Actor A
           ON A.ActorId     = P.PersonId
         JOIN Ethnicity E
           ON E.EthnicityId = A.EthnicityId;
--

Of course, you may follow a similar approach in order to retrieve the “full” Director and Writer information:
--
CREATE VIEW FullDirector AS

    SELECT P.FirstName,
           P.Lastname,
           P.BirthDate,
           P.GenderCode,
           P.TwitterProfile,
           P.PhoneNumber,
           P.EmailAddress,
           D.Bio,
           D.Etcetera
         FROM Person P
         JOIN Director D
           ON D.DirectorId = P.PersonId;

--
CREATE VIEW FullWriter AS

    SELECT P.FirstName,
           P.Lastname,
           P.BirthDate,
           P.GenderCode,
           P.TwitterProfile,
           P.PhoneNumber,
           P.EmailAddress,
           L.Name AS Location,
           C.Name AS Country
         FROM Person P
         JOIN Writer W
           ON W.WriterId     = P.PersonId
         JOIN Country C
           ON C.CountryCode  = W.CountryCode
         JOIN Location L
           ON L.LocationCode = W.LocationCode;
--

The DML views here discussed are as well included in this MySQL 8.0 db<>fiddle so that you can see and test them “in action”.

Endnotes
1 In some conceptual modelling techniques, supertype-subtype associations are referred to as superclass-subclass relationships.
2 Although you mention that there exist in fact more Roles that a Person may perform, but the three you revealed are good enough to discuss the scenario exposing several important ramifications.
3 But, as you noted, in the future an Actor might eventually provide one-to-many URLs.
4 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable modelling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is based on (a) the early theoretical works authored by the sole originator of the relational model of data, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
5 The IDEF1X standard defines key migration as “The modeling process of placing the primary key of a parent or generic [i.e., a supertype] entity in its child or category entity [i.e., a subtype] as a foreign key”.
6 In IDEF1X, a role name is a distinctive label assigned to a FK attribute in order to express the meaning that it holds within the scope of its respective entity type.
7 Except, naturally, for the hypothetical conceptual properties (and logical columns) Director.Etcetera and UserProfile.Etcetera, which are merely placeholders that I used to expose the posibility of adding more properties (and columns) that apply to the corresponding conceptual entity type (and logical table).
8 E.g., appending an additional column with the AUTO_INCREMENT attribute to a table of a database “running” on MySQL.


Change user password in PostgreSQL I created a user in PostgreSQL, gave it a password using:

alter user username with password 'new_password';

But when I try to login using phpPgAdmin, it keeps saying use/password incorrect, do I have to do something after alter password, like on MySQL where you have to run flush privileges?

Thanks.

No, no flush is needed.  The response given to the client is intentionally vague, to avoid disclosing information to a presumed attacker.  Look in the database server's log file for a more complete error message.


Returning Result Based on 2 conditions from one Table Problem Statement: I have a table that holds three pieces of information (Number, Id, Type). Let’s call it table A:

╔══════╦════╦══════╗
║Number║ Id ║Type  ║
╠══════╬════╬══════╣
║ 1234 ║1111║Test 1║
║ 1234 ║2222║Test 1║
║ 1234 ║3333║Test 2║
║ 1234 ║4444║Test 2║
╚══════╩════╩══════╝

I have another table that holds 4 pieces of information (Number, Test1, Test2, ID). Let’s call it table B:

╔══════╦═════╦══════╦══════════════════╗
║Number║Test1║Test 2║ ID               ║
╠══════╬═════╬══════╬══════════════════╣
║ 1234 ║1111 ║      ║a1v1b0000006lmMAAQ║
║ 1234 ║2222 ║      ║a1v1b0000006lmNAAQ║
║ 1234 ║     ║3333  ║a1vf1000000qvTYAAY║
║ 1234 ║     ║4444  ║a1vf1000000qvTiAAI║
╚══════╩═════╩══════╩══════════════════╝

What I would like to do is, based on Number and Type from Table A, to search the Table B and return the corresponding ID.

The desired outcome is as follows:

╔══════╦════╦══════╦══════════════════╗
║Number║ ID ║Type  ║ ID               ║
╠══════╬════╬══════╬══════════════════╣
║ 1234 ║1111║Test 1║a1v1b0000006lmMAAQ║
║ 1234 ║2222║Test 1║a1v1b0000006lmNAAQ║
║ 1234 ║3333║Test 2║a1vf1000000qvTYAAY║
║ 1234 ║4444║Test 2║a1vf1000000qvTiAAI║
╚══════╩════╩══════╩══════════════════╝

NB: I have tried to use a decode but reading up on it, I am getting mixed signals whether this is the correct approach.

This is a quick solution that uses union all.

The first query finds the matching rows between Table1 and Table2 where Table1.Type = 'Test 1' and Table1.Id = Table2.[Test 1]

The second query finds the matching rows between Table1 and Table2 where Table1.Type = 'Test 2' and Table1.Id = Table2.[Test 2]

--demo setup
Declare @Table1 table (Number int, ID int, Type varchar(10))
INSERT INTO @Table1
  (Number, Id, Type)
VALUES
  (1234, 1111, 'Test 1'),
  (1234, 2222, 'Test 1'),
  (1234, 3333, 'Test 2'),
  (1234, 4444, 'Test 2');

Declare @Table2 table (Number int, [Test 1] varchar(10), [Test 2] varchar(10), ID varchar(20))
INSERT INTO @Table2
  (Number, [Test 1], [Test 2], ID)
VALUES
  (1234, '1111', '', 'a1v1b0000006lmMAAQ'),
  (1234, '2222', '', 'a1v1b0000006lmNAAQ'),
  (1234, '', '3333', 'a1vf1000000qvTYAAY'),
  (1234, '', '4444', 'a1vf1000000qvTiAAI');

--solution using union all
SELECT t1.Number
    ,t1.ID
    ,t1.Type
    ,t2.ID
FROM @Table1 t1
JOIN @Table2 t2
    ON t1.Type = 'Test 1'
        AND t2.[Test 1] = t1.ID

UNION ALL

SELECT t1.Number
    ,t1.ID
    ,t1.Type
    ,t2.ID
FROM @Table1 t1
JOIN @Table2 t2
    ON t1.Type = 'Test 2'
        AND t2.[Test 2] = t1.ID

| Number | ID   | Type   | ID                 |
|--------|------|--------|--------------------|
| 1234   | 1111 | Test 1 | a1v1b0000006lmMAAQ |
| 1234   | 2222 | Test 1 | a1v1b0000006lmNAAQ |
| 1234   | 3333 | Test 2 | a1vf1000000qvTYAAY |
| 1234   | 4444 | Test 2 | a1vf1000000qvTiAAI |


Deploying SSRS reports on an "Air Gapped" report server without SSDT? Whats the simplest way to deploy multiple reports (.rdl) along with the respective data sources to a machine which cannot be connected to from Visual Studio?

I can only place files at a given location, connect to the report server via remote desktop and execute them. I am not permitted to open firewall ports, or establish any other sort of connectivity between my machine and the report server.

One way is to manually upload the files one at a time, which I wish to avoid.

You could use a RS.exe script. This would allow you to create a script, a batch file and a set of .rdl files.
Then you just call rs.exe with the script you created to upload the files to the report server.

You can find an example here

There is also a tool called rsscripter.exe created Jasper Smith, a SQL Server MVP.
Apparently it is now included in finebuild and the zip is still archived. See this link for the original article.


SQL Server 2014 SP1 CU6 Issue with Multi-Valued Parameters (No Longer Expand) Since upgrading to SQL Server 2014 SP1 CU6 (from CU4) on two different servers we have encountered what appears to be a bug with multi-valued parameters in SSRS where you can no longer drag to expand them. Also you must reload the page before the drop-down will function again.

The issue seems to only affect IE11 and is ok in Chrome and did not occur before updating from CU4. If we downgrade back to CU4 then the issue is resolved again on each server tested.

I have carried out further tests and I can confirm that the drop-downs are also broken in the same way in CU7 and also SP2 so the issue appears to not have been addressed as of yet by Microsoft.

It would appear someone else is asking about the same issue here:

This is what the multi-valued drop-down looked like before:

This is what the multi-valued drop-downs looks like now:

As you can see the option to drag the selection list has gone and some of these are very long and contain long descriptions and it means there is no way to view all the text at once.

Once the drop-down has been dropped-down once you must refresh the page which is a pain as it obviously clears the selection from all parameters.

I cannot see this raised as a KB article anywhere or many posts with people experiencing this which seems surprising.

Thanks
Robin

In case anyone comes across this, I spoke to someone at Microsoft and it turned out this bug was fixed in the next cumulative update although unfortunately it wasn't out until October. These releases which resolve the issue are:

SQL Server 2014 SP1 CU9
SQL Server 2014 SP2 CU2

The release date was around 17/10/2016.

We have since upgraded to the latest version which is 2014 SP2 CU2 released last month and I can confirm the issue was resolved and no longer occurs in any browser.


SQL Server : create dynamically queries to select all related data in DB based on entry table and ID I'm tasked to create a SQL statement which will create (and run?) multiple other queries which will select all related data outgoing from an given ID (uniqueidentifier) and a given table name.

It doesn't have to be that fast, but it should be able to use against every db, given only 1 ID and the table name.

In the DB, there are some 1:n, n:m etc. relations and not every table has a column named Id, some only have foreign-keys and it has to be fully dynamically and generic.

I tried to realize it by myself, but I'm not that good in SQL, I also tried some other scripts which will return the primary-foreign-key-relations but I'm stuck there so I'd thought you guys might help me :)

To be returned, I need (would be best) generated queries which I can run against the db and get all information returned
(Are there other good return possibilities?)

Alternatively I could create this project in C#, but I'm stuck in attempt as well.

I'll appreciate any kind of help :)

It's not entirely clear what the wanted output is but based on the comments that you want a "lightweight db export" and that the solution needs to be dynamic and "to be able to apply it to different dbs and different schemata", I'll try to make an outline of a solution:

Find all FKs in the db in question.
Copy all tables to a new db - but without the FKs and no data.
Copy the "starting rows" from the db to the copy-db. It doesn't have to be 1 row from 1 table but any number of rows from any number of tables.
For each FK, create two statements (one for each direction of the FK) that finds rows in the db that are related to the rows in the copy-db and copies them (counting the copied rows) if they haven't been already copied. This is the dynamic part.
The normal direction of the FK constraints will be strictly needed (if you want a consistent database as a result) but the reverse direction can be decided by the user performing this procedure (see note below*).
Iterate over all these statements.
Repeat the iteration until it produces 0 copying (no rows copied at all, in any step).
If needed, add the FK constraints in the copy-db. This will be an extra check, that the solution worked and created a usable database.
Export the copy-db with a backup or any other method available.

*
Explanation:
  Lets say they choose a few rows from Orders. The FKs to Customers and Stores are necessary to be followed (and the related rows copied over). For OrderDetails you need the related Products. But the reverse FK routes are not all needed. You'll probably want to have for each customer all the Orders, for each order all the OrderDetails and for each product all the ProductDetails.
  But for Products, you'll probably not want all the related OrderDetails.
  Because if you do (follow all FKs in both directions), you'll start with a few or a single customer, find all their orders and the related details, then the products ordered, then all the other orders for these products, then all the other customers for these products, then all the other products that these customers ordered, etc. You'll end up, in many cases, with almost the entire database.


Calculation of two dependent variables in Access Query I'm having problem constructing below two variables in Access Query or Access SQL which are dependent on each other, please kindly help!

a(n)=b(n)+c(n);
b(n)=if(a(n-1)<>0,a(n-1),d(n)).

n refers to the current record while n-1 refers to the previous record.

Here's the sample data:

I've got an answer from my friend below but it works very slow for my actual database.

Anyone has more efficient way doing it?

Table1

| ID n  c  d |
|------------|
| 1  1  1  2 |
| 2  2  0  2 |
| 3  3  0  2 |
| 4  4  1  2 |
| 5  5  2  2 |
| 6  6  3  2 |
| 7  7 -9  2 |

n=ID but n is number variable
sql:

SELECT Table1.n
    ,[b] + [c] AS a
    ,IIf([n] < 2, 2, IIf(DLookUp("[a]", "Query2", "[table1].[n]=" & [table1].[n] - 1) <> 0
         , DLookUp("[a]", "Query2", "[table1].[n]=" & [table1].[n] - 1), [d])) AS b
    ,Table1.c
    ,Table1.d
FROM Table1;


Is sslmode=verify-full enough to connect securely to postgresql? I'm using Amazon's postgresql RDS service to run a few shared development/testing databases. I'm connecting to it via something like:

 psql " dbname=postgres user=myrootuser sslrootcert=rds-combined-ca-bundle.pem sslmode=verify-full host=myhostname.asdfqwer.us-west-2.rds.amazonaws.com"

The password is in a pgpass file which no one else should be able to read.

Is this enough to connect securely to the database? Or are there vulnerabilities that can, say, downgrade the connection to plain text or something that I should be aware of?

(I know it would probably be wise to put the DB behind a VPC/firewall not make it globally accessible, but that's not practical for us at the moment).

If done correctly, it is secure.

The biggest vulnerability would be someone tricking you into removing the sslmode=verify-full parameters from your connect string, or some similar social engineering attack.


Performance of a=0 and b=0 and ... z=0 vs a+b+c+d=0 This is a simple question that I can't seem to find the answer for.
In terms of performance, If I have a WHERE clause such as a=0 and b=0 and ... z=0, Would I gain any performance if I replaced that condition with a+b+...+z=0?
In other words, is there any performance gain by replacing the following
Select *
From MyTable
Where A=0 and B=0 and C=0 and D=0...

With
Select *
From MyTable
Where A+B+C+D=0...

I know it can depend on indexes, but for this purpose, let's just say no indexes exist.  Does the arithmetic operator (+) perform better than an "OR" or "AND" Logical Operator?
I'm under the impression that the addition performs better than multiple conditions with ANDs or ORs.
Test results
On a Table of 4.2 million rows
Returning rows Where A=0 B=0 and C=0 -> 351748 Rows
The addition (A+B+C=0) took 5 seconds while the Logical conditions A=0 and B=0 and C=0 took 11 seconds.
On the other hand
Returning rows Where A<>0 B<>0 or C<>0 -> 3829750 Rows 58 seconds
Returning rows Where F65+ F67+f64<>0 -> 3829750 Rows 57 seconds
For the OR, it seems that there is no significant difference.
I agree with gbn:

If A is -1 and B is 1, A+B=0 but A=0 and B= 0 is false

and with AMtwo:

ABS(A)+ABS(B)+ABS(C)+ABS(D)... Even if you expect only positive values, if the column accepts negative values, you should assume that you might encounter one

The results are very impressive, as I thought, It seems that the addition is much quicker than the logical operators.
A = Float, B = Money and C = Float.  The query used is as shown.  In my case, all are positive numbers.  No indexes.  It is just logical in my mind that addition would be quicker than Logical conditions!

In your question, you detail some tests that you've prepared where you "prove" that the addition option is quicker than comparing the discrete columns.  I suspect your test methodology may be flawed in several ways, as @gbn and @srutzky have alluded to.

First, you need to ensure you're not testing SQL Server Management Studio (or whatever client you're using).  For instance, if you are running a SELECT * from a table with 3 million rows, you're mostly testing SSMS's ability to pull rows from SQL Server and render them on-screen.  You're far better off to use something like SELECT COUNT(1) which negates the need to pull millions of rows across the network, and render them on screen.

Second, you need to be aware of SQL Server's data cache.  Typically, we test the speed of reading data from storage, and processing that data, from a cold-cache (i.e. SQL Server's buffers are empty).  Occasionally, it makes sense to do all your testing with a warm-cache, but you need to approach your testing explicitly with that in mind.

For a cold-cache test, you need to run CHECKPOINT and DBCC DROPCLEANBUFFERS prior to each run of the test.

For the test you've asked about in your question, I created the following test-bed:

IF COALESCE(OBJECT_ID('tempdb..#SomeTest'), 0) <> 0
BEGIN
    DROP TABLE #SomeTest;
END
CREATE TABLE #SomeTest
(
    TestID INT NOT NULL
        PRIMARY KEY
        IDENTITY(1,1)
    , A INT NOT NULL
    , B FLOAT NOT NULL
    , C MONEY NOT NULL
    , D BIGINT NOT NULL
);

INSERT INTO #SomeTest (A, B, C, D)
SELECT o1.object_id, o2.object_id, o3.object_id, o4.object_id
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

SELECT COUNT(1)
FROM #SomeTest;

This returns a count of 260,144,641 on my machine.

To test the "addition" method, I run:

CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE (st.A + st.B + st.C + st.D) = 0;
GO
SET STATISTICS IO, TIME OFF;

The messages tab shows:

  Table '#SomeTest'. Scan count 3, logical reads 1322661, physical reads 0, read-ahead reads 1313877, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 49047 ms,  elapsed time = 173451 ms.

For the "discrete columns" test:

CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE st.A = 0
    AND st.B = 0
    AND st.C = 0
    AND st.D = 0;
GO

SET STATISTICS IO, TIME OFF;

again, from the messages tab:

  Table '#SomeTest'. Scan count 3, logical reads 1322661, physical reads 0, read-ahead reads 1322661, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 8938 ms,  elapsed time = 162581 ms.

From the stats above you can see the second variant, with the discrete columns compared to 0, the elapsed time is about 10 seconds shorter, and the CPU time is about 6 times less.  The long durations in my tests above are mostly a result of reading a lot of rows from disk.  If you drop the number of rows to 3 million, you see the ratios remain about the same but the elapsed times drop noticeably, since the disk I/O has much less of an effect.

With the "Addition" method:

  Table '#SomeTest'. Scan count 3, logical reads 15255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 499 ms,  elapsed time = 256 ms.

With the "discrete columns" method:

  Table '#SomeTest'. Scan count 3, logical reads 15255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 94 ms,  elapsed time = 53 ms.

What will make a really really big difference for this test?  An appropriate index, such as:

CREATE INDEX IX_SomeTest ON #SomeTest(A, B, C, D);

The "addition" method:

  Table '#SomeTest'. Scan count 3, logical reads 14235, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 546 ms,  elapsed time = 314 ms.

The "discrete columns" method:

  Table '#SomeTest'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 0 ms,  elapsed time = 0 ms.

The execution plan for each query (with the above index in-place) is quite telling.

The "addition" method, which must perform a scan of the entire index:

and the "discrete columns" method, which can seek to the first row of the index where the leading index column, A, is zero:


Protect table from wrong data using locks or transactions I have two tables

flats

   id - primary key
   title - text

flats_reservations

   id - primary key
   flat_id - foreign key to flats(id)
   creator_id - foreign key to users(id)
   is_reserved - boolean
   created_at - current_timestamp

If flats_reservations has these rows

id=1, flat_id=12, creator_id=5, is_reserved=1, created_at=2016-10-10 00:00:00
id=2, flat_id=12, creator_id=5, is_reserved=0, created_at=2016-10-10 00:00:05
id=3, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:10

Then only user with id=4 can remove reservation (e.g. insert following row (is_reserved=0)

id=4, flat_id=12, creator_id=4, is_reserved=0, created_at=2016-10-10 00:00:10

No other user can make this. At any point of time only one user can reserve flat.

Can I use mysql transactions to ensure that two users at the same time can not reserve flat?

I want to be sure, that following rows can not be inserted at once

id=5, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:15
id=6, flat_id=12, creator_id=4, is_reserved=1, created_at=2016-10-10 00:00:15

In application I use following queries in transactions

# transaction start
select is_reserved from flat_reservations where flat_id=12 order by created_at desc limit 1;
# if is_reserved == 0 or query returned 0 rows then
insert into `flat_reservations` (`flat_id`, `creator_id`, `is_reserved`) values (12, 4, 1)
# commit

Is there some way to lock table during execution of these two queries and only when flat_id=12?

Users must be able to reserve other flats (e.g., where id is not 12).

If it is not possible then should I lock whole table for writing?

Plan A:  Add FOR UPDATE to the SELECT.

Plan B:  Add UNIQUE(flat_id, creator_id, is_reserved)

If neither of those work, then I am confused by your business logic; please explain further.


Is my entity-relationship diagram about customers and accounts correct? Business domain description

Each customer has a name, a permanent address, and a social security number.
Each customer can have multiple phone numbers, and the same phone number may be shared by multiple customers.
A customer can own multiple accounts, but each account is owned by a single customer.
Each account has an account number, a type (such as saving, checking, etc.), and a balance
The bank issues an account statement for each account and mails it to its account owner every month. As time goes on, there will be multiple statements of the same account.
Each statement has an issued date and a statement ID.  All the
statements of the same account have different statement IDs, but two
different accounts could have statements with the same statement ID.
For example, it is possible that account A has a statement with ID
‘123', while account B has another statement with the same ID '123'.

My entity-relationship representation

I have drawn the following entity-relationship diagram (ERD) to represent that scenario:

Questions

Can Min-Max notation be used in case of any relationships, or, just when there is an indication for that in the description?
Are my many-to-many relationships portrayed correctly here?
Could I properly portray the relationships among Account vs Account Statement vs StatementID?
As per my assumption, Is Account Statement really a weak entity and is Has really a weak relation that is dependent on Statement ID? Is issue-date a weak key?

IMHO you just have to pay attention on the order of cardinalities, and you should note that Issue Date is not a weak key, but it's not a key at all: in fact you could have two account statements of different people issued on the same date, so you need a foreign key (Issue Date plus Number of Account)


inserting the table colomn data in multiple tables colomn.(SQL SERVER) I want to insert one table colomn data into multiple tables colomns using triggers.
For Example: I have one table name 'T1' having multiple colomns like 'C1','C2','C3' and now I want to insert these colomns data in table 'T2','T3','T4' having same colomn names 'C1','C2','C3' by using trigger.

Can anybody please help me out?

Try this

CREATE TRIGGER Trigger_t1
   ON  T1
   FOR INSERT
AS
BEGIN

    SET NOCOUNT ON;

    insert into T2(C1,C2,C3 )
    select  C1,C2,C3
    from    inserted

    insert into T3(C1,C2,C3 )
    select  C1,C2,C3
    from    inserted

    insert into T4(C1,C2,C3 )
    select  C1,C2,C3
    from    inserted

END
GO

DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables.

                                                                                :MSDN

https://msdn.microsoft.com/en-us/library/ms191300.aspx


Convert a numeric column with number eg.(-1222222) to two float column with two decimal places eg (-12222.22) I know there are different variations of this question asked.
But what I am trying to do is that, there is a table with a numeric column (12,0)
that has data looking like

Units
-----
-1222222
0
-19
21123021

What they really mean is this

Units
-----
-12222.22
0.00
-0.19
211230.21

With two decimal places in the last digits of each number.

I am struggling to convert these numbers to a two decimal place float column(Units)

Something like this will do:

with data as
(
select -1222222 as foo
union
select 0
union
select -19
union
select 21123021
)
select cast(cast(foo as decimal)/100 as decimal(10,2))
from data;

You can also use format(foo,'N2') and a million other ways.


SQL Server Express Edition Primary - Secondary Model From Replication Considerations (SQL Server Express) in Books Online, SQL Server Express Edition can only act as subscriber, and only by using Windows Sync Manager.
Can anyone suggest any other methods or third party freeware tools available to produce a Primary - Secondary model like transactional replication between two Express Edition instances?

It must be free.
It must not use triggers.
I cannot build a separate application for this.

I'm searching for freeware or a tool like SymmetricDS, but unfortunately SymmetricDS adds too many triggers. For 20 tables, I had more than 100 triggers in my database. Moreover, those triggers will be considered as malware in concern with security. It does performance degradation too. Still, when there are triggers that track down what are we doing, it will be a security concern, because we can't provide 100% assurance that tracked data will be secured as it's the business logic of SymmetricDS. And moreover, I am not going to use  that product, so talking about it over and over won't solve my problem :(

An answer to Replication from one SQL Server Express to another on Stack Overflow offers some suggestions:

In summary (from the link):

Write an application that transfers "articles" from your "publisher" db to your "subscriber" db(s).

OR

Create a set of views to have a summation of data that you want to be
published. Then create INSTEAD OF triggers on these views (you can't
create an AFTER/FOR trigger on a view) to process that data and
transfer it to your "subscriber"(s).

Triggers On Views? What For? by Michael J Swart has some good information on view triggers.


How to make Swingbench 2.6 and Oracle 18c work together? Swingbench 2.6 supports Oracle 12c and I have been using it for 12c successfully. But I want to use it with Oracle 18c for testing purposes.

In case of using different wizards from swingbench\winbin folder I was getting scripts hanging except oewizard script and could only run CRUD tests with it using SYSTEM user instead of SOE.

So how to fix Swingbench scripts and Oracle configuration for that?

Partially it could be fixed by adding

alter session set "_ORACLE_SCRIPT"=true;

command at the top of these five files:

swingbench/sql/CreUser.sql
swingbench/sql/json_createuser.sql
swingbench/sql/shdg_createuser.sql
swingbench/sql/soedgcreateuser.sql
swingbench/sql/tpcds_createuser.sql

During configuration sys as sysdba Administrator Username is filled by default. And during oewizard script execution Java throw a lot of

  ORA-01950: no privileges on tablespace 'USERS'

exceptions. So I've resolved it by:

$ sqlplus / as sysdba
SQL> grant unlimited tablespace to sys;
SQL> alter user sys quota unlimited on users;


What is wrong in debunking myths about clustered indexes? What is wrong in code. or its results, illustrating that clustered index is evil [1]?
and how to de-debunk, i.e. to return to customary myths and best practices?

[1]
Debunking myths about clustered indexes - part 3 (example script)
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/09/16/377.aspx

Edit by gbn, Jan 2012

The dead link used to have a script that "proved" clustered indexes were bad.

Similar SO question: https://stackoverflow.com/questions/4034076/reasons-not-to-have-a-clustered-index-in-sql-server-2005

That script has a fairly wide, varchar clustered index. And it needs an index rebuild after populating it with random data too: you'll have massive fragmentation.

A good clustered index is narrow, numeric and strictly monotonically increasing: which is why folk use surrogate keys...

A table without a clustered index is called a "heap" because it exactly that: a pile of data lying on disk. And it will stay that way no matter how much you rebuild your NC indexes. Outside of something like staging tables (with a load/truncate usage pattern) there is no reason not to have a clustered primary key.

Edit:  The link does not debunk a clustered index myth, but rather shows how to create an unsuitable clustered index and why index maintenance is important. Parts 1 and 2 mentions bookmark lookups (now key lookups in SQL Server 2005+): a good NC index will be covering so they don't happen.

To learmn about indexes, I suggest Simple Talk's many articles. Like this one


how to grant select access my sql server when I exec the syntax to gain the access I get an error that I cant grant permissions  to my self.
any Ideas please!

It does not make sense to be changing your own rights.   If you already have rights you do not need to grant them. If you do not have the desired rights, you should not be able to grant them to yourself.  (Nor should you be able to deny yourself rights.)

However, it is possible that you apparently should have rights to some object, but you do not.  In that case, check to see if your account was directly denied access to that object or if you are in a group or role that has been denied access.  DENY always overrides a GRANT.

Just to flesh out the limits to changing permissions, there are other rights that cannot be changed, such as:

sa - operates outside the security system, can do everything on the server
dbo - can do everything in the database
entity owner - owns an object or objects and therefore has full rights to them
information_schema - system defined ownership
sys - system defined ownership

More:  If you are an administrator and a group membership is denying you rights, you may be able to modify the group's rights or its membership.  Otherwise, you need someone with GRANT rights to assist you.


Using a join operation with conditionals I have two tables that I'm working with in my database. I am using SQL Server 2014.

One is 'versions', that contains version_id, version_number,
start_date, end_date and server_id.
The other is 'features', which contains feature_id, feature_info and
version_id. version_id is a foreign key to the version_id in
'versions', where it is a PK.

I'm trying to get something like:

"X, Y, Z are in version 1.0.
"....... are in version 1.2.

and then if a version does not have a new feature:

"Version 2.2 has no new features."

Right now, I don't have any versions without features, but I plan on adding some to test this out.

Here is the output I'm currently getting with my code (I don't care about the singular/plural issues, i.e. 'Version 1.7 has email client'):

Version 1.0       has web functionality, Version 1.1       has user interface, Version 1.7       has email client, Version 1.7       has voice control functionality, Version 2.0       has bug fixes.

There happens to be 7 spaces in between commas. This is less important, but would still be nice to be able to fix.

*Also, if I wanted to turn this into a stored procedure, would I just add, 'create procedure x', followed by 'begin' and 'as' after my variable declaration, and then an 'end' at the end of the code?

DECLARE @result varchar(1000)

SET @result = ''

BEGIN TRY

    SELECT @result = @result + 'Version ' + version_number + 'has ' +      feature_info + ', '
    FROM features join versions on versions.version_id = features.version_id
    SELECT substring(@result, 0, len(@result) - 1)

END TRY

BEGIN CATCH
    PRINT 'THere was an error getting feature info'
END CATCH

You can make use of the FOR XML PATH() construct to create the list of features.

For instance:

USE tempdb;

IF OBJECT_ID('dbo.Features') IS NOT NULL
DROP TABLE dbo.Features;
IF OBJECT_ID('dbo.Versions') IS NOT NULL
DROP TABLE dbo.Versions;
GO
CREATE TABLE dbo.Versions
(
    VersionID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , VersionNumber DECIMAL(10,2) NOT NULL
    , StartDate DATETIME NOT NULL
    , EndDate DATETIME NULL
    , ServerID INT NOT NULL
);
CREATE TABLE dbo.Features
(
    FeatureID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , FeatureInfo VARCHAR(250) NOT NULL
    , VersionID INT NOT NULL
        FOREIGN KEY REFERENCES dbo.Versions(VersionID)
);

INSERT INTO dbo.Versions(VersionNumber, StartDate, EndDate, ServerID)
VALUES (1.0,'2016-03-22T00:00:00', '2016-03-23T00:00:00', 1001)
    , (2.0,'2016-03-23T00:00:00', '2016-03-24T00:00:00', 1001)
    , (2.1,'2016-03-24T00:00:00', NULL, 1001);

INSERT INTO dbo.Features (FeatureInfo, VersionID)
VALUES ('blah', 1)
    , ('blee', 1)
    , ('bloh', 2);

;WITH Deets
AS
(
    SELECT VersionNumber = CONVERT(VARCHAR(10), v.VersionNumber, 0)
        , Features = STUFF((SELECT TOP(2147483647) ', ' + f.FeatureInfo
        FROM dbo.Features f
        WHERE f.VersionID = v.VersionID
        ORDER BY f.FeatureID
        FOR XML PATH ('')
        ), 1,2,'')
    FROM dbo.Versions v
)
SELECT 'Version ' + d.VersionNumber + ' has ' +
    CASE WHEN d.Features IS NULL THEN 'no features'
        ELSE d.Features
    END
FROM Deets d;

This returns a nice list, like:

Notice several things from my code above:

I always specify the schema
I use upper case for the SQL keywords
I use Camel Case for column names
The values I specified for the date columns are in ISO format, which makes it easier for SQL Server to understand, and prevents errors.


Mysql Remote IP blocked I have a peculiar issue with some database connections: I've got this (test) script on 2 different servers:

$link = mysql_connect($db_server, $db_user, $db_passwd);
if (!$link) {
    die('Could not connect: ' .mysql_errno().":". mysql_error());
} else {
    echo 'Connected successfully';
}
mysql_close($link);
exit;

On one server, there's no issue at all. On the other server, the connection is refused from time to time, with the following message:

Could not connect: 2002:Operation not permitted

Obviously, both hosts aren't blocked by the db server firewall (but I've explicitly whitelisted the problematic IP address anyway)

Most forums indicate it might be a problem with the lock file, mysql-server not running, or any global issues, but it seems like this specific remote host is blocked, all other websites and remote hosts connect perfectly.

I've attempted to set the global max_connect_errors to 10.000 , but that didn't help either. I do believe it has to do with connection errors from the offending server. It has some slow query issues already, and with the site being visited more often, the issue comes back more frequently. After (exactly) ten minutes, the remote host is unblocked again, and everything works as it should.

I've also tried restarting mysqld, rebooting the server.
But I'm running out of options. Any thoughts on how to prevent this, or at least resolve it when the issue occurs?

Well, it took a couple of years of my life, but I think I've figured it out.

I had to increase two mysql database configuration values (in /etc/mysql/my.ini) :

max_connections (from 100 to 500 , for now)
max_connect_errors (from 10 to 10.000)

After that, I ran flush hosts on the mysql prompt to clear any blocked hosts.

The forum experiencing these issues is generating some heavy database queries (mainly statistics into a table with over 1 million records - not sure why this would be "difficult", but that's another topic).

I assume this causes a temporary lockup and ultimately a lack of allowed connections. This in turn temporarily blacklists the webserver altogether!

After that, the database was accessible via the web interface again!


Postgresql Query getting slow execution time I did the explain analyze for this query, it was giving 30ms, but if the data is more I will get an execution expired; Using PostgreSQL 10

For normal execution: https://explain.depesz.com/s/gSPP

For slow execution:  https://explain.depesz.com/s/bQN2

SELECT inventory_histories.*, order_items.order_id as order_id FROM
"inventory_histories" LEFT JOIN order_items ON (order_items.id =
inventory_histories.reference_id AND inventory_histories.reference_type = 4)
WHERE "inventory_histories"."inventory_id" = 1313 AND
(inventory_histories.location_id = 15) ORDER BY inventory_histories.id DESC
LIMIT 10 OFFSET 0;

Indexes:

"inventory_histories_pkey" PRIMARY KEY, btree (id)
"inventory_histories_created_at_index" btree (created_at)
"inventory_histories_inventory_id_index" btree (inventory_id)
"inventory_histories_location_id_index" btree (location_id)

At the key node, it is expecting 83 rows but finding 46,547.  Unless your statistics are way out of date, that means there is a correlation between inventory_id and location_id.  You could try to CREATE STATISTICS on dependencies between those two columns, but it not really clear what it would do instead if it had correct estimates.  Maybe a hash join to the order_items table, rather than poorly cached nested loop, but that would depend on how big the table is.

A more direct route to address this would be to create an index on (inventory_id, location_id, id DESC).  This would allow it to jump in the index to the correct values of inventory_id and location_id, and walk it in "id" order until it collects 10 rows.  The "DESC" is probably not necessary, you could try without it and see if it works.  You could also add "reference_type" to the index just before "id", but it would only be a minor improvement and might also make the index less general for application to other queries.


Database SQL Script very, very slow (string manipulation over 700k rows) I have table containing 700,000 rows containing a string id field with values such as rec-232276-dup-0 and rec-354240-org. The rec- is constant but the other parts of the id can change.

I want to split this string up so that I have just the integer part and a boolean value depending if the next part is equal to dup or org (1 for dup, 0 for org) which I will insert back into the table.

I wrote the following loop to do this and functionally it works fine but when I run it over the full 700,000 rows it takes an inordinately long time (+12 hours and counting).

What have I done wrong to cause it to take this amount of time? Is it the string manipulation that causes this? What can I do to improve this?

Thanks for your help.

My script is as follows:

select id
into #ControlTable
from [dbo].[original_test_dataset]

declare @TableID varchar(20)

while exists (select * from #ControlTable)
begin

   select @TableID = (select top 1 id
                    from #ControlTable
                    order by id asc)

   declare @duplicate bit
   declare @id_only varchar(10)

   --1. Find id only
   -- Trim off rec-
   set @id_only = REPLACE(@TableID,'rec-','')
   -- Find position of first - and then take everything before it
   set @id_only = LEFT(@id_only,CHARINDEX('-',@id_only,0))
   set @id_only = REPLACE(@id_only,'-','')
   UPDATE original_test_dataset set id_only = @id_only WHERE id = @TableID;

   --2. Find if duplicate
   IF(PATINDEX('%dup%',@TableID) = 0)
        BEGIN
           -- No duplicate so original file
           UPDATE original_test_dataset set duplicate = 0 WHERE id = @TableID;
        END
   ELSE
       BEGIN
           -- Duplicate
           UPDATE original_test_dataset set duplicate = 1 WHERE id = @TableID;
       END

   delete #ControlTable
   where id = @TableID
END

drop table #ControlTable

Added table definition as requested:

CREATE TABLE [dbo].[original_test_dataset](
    [id] [varchar](50) NULL,
    [ FirstName] [varchar](50) NULL,
    [ LastName] [varchar](50) NULL,
    [ Phone1] [varchar](50) NULL,
    [ Phone2] [varchar](50) NULL,
    [ Phone3] [varchar](50) NULL,
    [ No] [varchar](50) NULL,
    [ Road] [varchar](50) NULL,
    [ Village] [varchar](50) NULL,
    [ Town] [varchar](50) NULL,
    [ PC] [varchar](50) NULL,
    [ County] [varchar](50) NULL,
    [ DOB] [varchar](50) NULL,
    [id_only] [varchar](10) NULL,
    [duplicate] [bit] NULL
) ON [PRIMARY]

GO

/****** Object:  Index [PK_ORIGINAL_TEST_DATASET_ID]    Script Date: 03/09/2014  07:47:19 ******/
CREATE CLUSTERED INDEX [PK_ORIGINAL_TEST_DATASET_ID] ON [dbo].[original_test_dataset]
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON   [PRIMARY]
GO

You have a performance question so I recommend you read How to analyse SQL Server performance to begin with.

As about your script: you're doing it as slowly as possible. Row by painfully slow row. Not even a cursor slow, is worse than a cursor slow. What you have here is a single update:

update original_test_dataset
set duplicate= case when PATINDEX('%dup%', id)=0 then 0 else 1 end,
 id_only = REPLACE((LEFT(REPLACE(id,'rec-',''), CHARINDEX('-',REPLACE(id,'rec-',''),0)),'-','');

That's it, nothing else.

What you have in the original script is slow because:

it operates on row by row as opposed to sets. SQL is designed to manipulate sets, not rows.
it perform a lot  of writes in standalone commits resulting in huge flush commit waits. Batch commit should be used.
it performs multiple unindexed end-to-end scans on  #ControlTable (every operation on  #ControlTable is an unindexed end-to-end scan, it should had an index on id, yes #temp tables do need indexes)
it does two updates per row instead of one update of two fields
I can't know whether the access to original_test_dataset is in any way optimized, in lack of an index on id is likely is not.

declare @TableID varchar(20) vs.  [id] [varchar](50) NULL: you're silently truncating data. Basically, your script corrupts the table.


Can I install SQL server 2012 on a same machine with SQL server 2005? I have been using SQL Server 2005 standard for quite a while, and it is working great. Now I need to use SQL Server 2012. Will this effect my existing databases? How much risk is involved in installing SQL Server 2012 on a machine that has SQL Server 2005 running?

Yes, you can install an additional instance of SQL Server 2012 (you'll need a named instance if your 2005 instance is a default), and it can happily co-exist without interfering with your existing 2005 instance. None of your existing databases will be touched, and you will still be able to have both 2005 and 2012 versions of the client tools, too (this isn't true for all side-by-side installs of different versions).

You could also opt for an in-place upgrade; this will keep your named instance and upgrade your databases. Need to be careful with this one though; run at least the 2012 upgrade advisor in case you have 80 compatibility issues, queries with old join syntax, etc. This is not an exhaustive check (it can't check queries constructed by dynamic SQL, and it won't check queries submitted by applications either) but it's a good start.

To learn more about how SQL Server behaves with multiple version I would suggest you to read Work with Multiple Versions and Instances of SQL Server


Changing Database Transaction isolation level to SNAPSHOT for SQL Server replication "distribution" database I have a couple transactional replication publications that make large changes (nearly 1 billion records) to a publication article.  These transactions are captured by the log reader and stored in the distribution database for up to a day before we run the distribution agents to the two subscribers.

In conjunction with these large transactional replication publications, we also have several smaller publications that run daily and sometimes multiple times per day.  What we are seeing is that while the log reader is reading the large transactions and storing them in the distribution database, the smaller, daily publications are being blocked until the log reader completes the write to the distribution database.  Note: these publications use the same distribution database on a single distributor server.

For both scenarios above, the distribution agents are not scheduled but are run on-demand: an SSIS package calls several CMD distrib.exe while passing the parameters for each publication/subscription combination.  There has not been an issue executing these agents.

Since these are two entirely different sets of data, although publishing from the same database, I am considering changing the transaction isolation level of the database to SNAPSHOT to avoid locks and allow concurrent processing of the transactions in the distribution database.

Does anybody have experience doing this?  I've used SNAPSHOT isolation level for another database where we were having deadlocks while accessing the same table but different records and it worked out fine.

If changing the isolation level isn't the way to go, how can I reduce the impact of this large log reader write to the distribution database?

With 1 billion changes, I would be worried about the size of TempDB when the row version is added to everything. Hilary Cotter responded to this same question here - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2edbdc37-c2ed-4c84-ae72-16f89404172b/sql-server-replication-and-enabling-readcommittedsnapshot-on-the-distributor?forum=sqlreplication.

He is definitely the man you want to ask replication related questions to.


How to create month ranges without using a specific date in Postgresql What's the best way to create month ranges without using specific dates?

For example, today is April the 4th and I want to calculate the sales rate of the past 12 months, that I can run every month without updating the date.

I was thinking of working with current_date, but is there a way to create a more efficient query?

select
datetrunc('month',date)
,count(id) filter (where status='sold' and (date between (datetrunc('month','current_date - interval '1 months'))
and datetrunc('month', current_date) - Interval '1 days')) / count(id) filter
(where date between (datetrunc('month','current_date - interval '1 months')) and
datetrunc('month', current_date) - Interval '1 days') as Mar2020_Sales_Rate
,..... as Feb2020_Sales_Rate
,.
,.
,..... as Mar2019_Sales_Rate
from sales
group by 1;

This is the way I chose to get 1st and end of the month, without using current date, but rather the creation date (of an order) in my data. Please suggest any other query that might seem more effective or if you feel there is a flaw in this query:

select distinct creation_date::date,
       (creation_date - Interval '1 months')::date as Minus_1M,
       (creation_date - Interval '2 months')::date as Minus_2M,
       (creation_date - Interval '3 months')::date as Minus_3M,
       extract('day' from creation_date) as Day_of_month,
       date_trunc('month', (creation_date::date - extract('day' from
       creation_date)::int))::date as Beginning_of_month,
       creation_date::date - extract('day' from creation_date)::int as End_of_month
from order_data
order by 1 desc;


Created RDS db-instance Replica successfully, now how to connect it with server I had created AWS RDS, MySQL db-instance successfully with the help of Github Pages Documentation. Thanks a lot 18F/OpenFEC Project.

Now, Instead of Route 53 configuration for network connectivity, I want to configure directly through Godaddy DNS Manager. Because already a subdomain is configured for a production server with RDS db-instance endpoints.

I can create another subdomain at DNS Manager with Replica db-instance's end points. But, how to achieve connection on both db-instance as well as replica-db-instance.

Please guide me, to manage traffic of db query without updating that production app.

What you are talking about is called Read/Write Splitting.   This is not a capability directly provided either by MySQL Server itself, or by RDS for MYSQL. It is an advanced technique for which there are no magic solutions, as I have mentioned elsewhere.

If you are not modifying your application (and assuming your framework does not have such functionality built-in, which is a reasonably safe assumption), you will need to deploy one or more read/write splitting proxy servers to divert each query to the correct target as appropriate.

Examples of software with this capability are MariaDB MaxScale and ProxySQL.


How can I optimize this query and support multiple SKUs? My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any suggestions on indexes would be much appreciated also.

SELECT
    available - (
        SELECT COALESCE(sum(quantity), 0)
        FROM product_locks
        WHERE sku = 'sku1'
    ) - (
        SELECT COALESCE(sum(quantity), 0)
        FROM
            orderlineitems
            INNER JOIN responses_authnets ON responses_authnets.id = orderlineitems.response_id
        WHERE
            sku = 'sku1' AND responses_authnets.salesite_id = 'site_id_1'
    ) AS free,
    available AS total,
    sku,
    on_hold
FROM product_inventories
WHERE sku = 'sku1' AND salesite_id = 'site_id_1';

I have refactored the query to use left joins and IF statement checks for the joins so that you do not have to use nested sub-queries. The group by for sku and site will give the totals by site if needed, but this can be changed to suit your needs.

I am not sure of how the responses_authnets table joins to the orderlineitems for the siteid so this may need additional clarification

SELECT (pi.available - SUM(IF(ISNULL(pl.quantity), 0, pl.quantity)) /* Sums up only those quantities which are not null through the left join */
- SUM (IF(ISNULL(ol.quantity), 0, ol.quantity))) as free, //* SUM up only quantities in the order line items which are not null */
pi.available as total,
pi.sku,
pi.on_hold
FROM product_inventories pi
LEFT JOIN product_locks pl ON pl.sku = pi.sku /* Left join since not all products in the inventory may have locks */
LEFT JOIN orderlineitems ol ON ol.sku = pi.sku
LEFT JOIN responses_authnetsid ra ON ra.id = ol.response_id /* Not sure how the responses come up here, so this join is not clear*
GROUP BY pi.sku, pi.salesite_id


Cost Threshold for Parallelism with Maxdop = 1 Been debating with my company's app vendor regarding parallelism. They have maintained that Maxdop = 1 has significant performance improvements for the app/db but they have provided absolutely no proof of this.

I have tested various DOP/CT settings and found a 28% overall performance improvement from automated testing, when using Maxdop 4 (8 hyperthreaded cores) and CT 150.

The vendor's DBA recently said: "There is an issue with internal processes of MSSQL that are able to use this value even if DOP is ‘1’, there are SPID’s lower than 51 in a MSSQL server that is using it, ghost cleanups and like this."

I have researched this and, despite fairly scant information, Paul Randall at least says that ghost cleanup is always single-threaded, so apparently the vendor's dba is incorrect?

Any thoughts on the effect of CT when Maxdop =1?

To say MAXDOP=1 is always beneficial is totally incorrect statement. MAXDOP value should be set according to SQL Server CPU core configuration followed by a good round of testing. Few applications I know that woks good with MAXDOP 1 is DynamicsAX and Sharepoint.

Yes ghost cleanup is single threaded as mentioned by Paul in this article but just because system process is single threaded IT DOES NOT means that MAXDOP=1 is going to be beneficial for you. This is what I gauge from your question. You can start with cost threshold of parallelism value =50. The value 5 mentioned is utter useless and it was for old days. With systems running on many cores now I recommend to set cost threshold of parallelism value to 50 and MAXDOP value which you can get from link shared below. See how this works for you.

We already have SE thread What is good way to calculate MAXDOP value I suggest you get MAXDOP value from queries mentioned in this link and see if that helps your environment.

Also I am not saying that you completely ignore Vendors recommendation but vendor supplies you application they cannot change SQL Server query engine behavior.


Checkpoints on secondary replica AlwaysOn AG Setup

3 Node Alwayson cluster - 1 sync and 1 async secondary replica - SQL Server 2012

Situation

We are witnessing PageIOLatches when reading from the asynchronous secondary replica. This is mostly caused by the throughput of the SAN that has been throttled. The hosting partner told us that this limitation cannot immediately be alleviated due to hardware constraints.
The primary and synchrounous replica use other SANs with a higher throughput. Although this situation is far from ideal, this is a temporary situation that will be solved soon and not the subject of my question.

When investigating the IO waits we noticed that these occur concurrently with an increase in the number of checkpoint pages/sec.

I was under the impression that checkpoints don’t occur on secondary replica’s in an AG, just as discussed here.

To verify this behaviour, I’ve set up an extended event to monitor the checkpoint events on the asynchronous replica. Just as expected, no checkpoints were captured for this database, nor have I found any checkpoints from other databases that match the pattern.

Next, I’ve created the same extended event on the primary replica and started a perfmon to verify if we could witness the same behaviour. Here we were able to capture (automatic) checkpoints, they happen approx. once per minute. These checkpoints occur simultaneously with the checkpoint pages/sec increase on our secondary (and primary) replica. It seems that checkpoints are being generated on the primary and redone on the secondary replicas. This would mean that checkpoints do occur implicitly on secondary replica's in an AG.

Question

Is my assumption correct that in an AG checkpoints are being generated on the primary replica and redone on all secondary replicas?
And thus, if the database TARGET_RECOVERY_TIME isn't set, the recovery interval setting of the primary replica will dictate the checkpoints on all secondary replicas for these databases.

Is my assumption correct that in an AG checkpoints are being generated on the primary replica and redone on all secondary replicas?

Yes.

A CHECKPOINT is a logged operation, as it's part of SQL Server's recovery strategy.  When the database starts up, it needs to know what modified pages (in the log file) haven't been written to the data file - so it can redo those changes before user queries start hitting the database.

I'm going to snag a screenshot from my blog post that looks at certain types of logging activity:

As you can see, the CHECKPOINT "start" and "end" are written to the transaction log.

A simplified view of the way AGs work is that they redo log entries from the primary replica on all secondaries.

Thus, since CHECKPOINTs are logged, they will be replayed on secondary replicas.

As a side note, John Eisbrener usefully pointed out that, if there are other databases on the secondary replica, those CHECKPOINT operations are also going to be picked up by this Perfmon trace.


Advantages and Disadvantages to using ENUM vs Integer types? Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled.

Is it better for this column's data type to be an int/bool (1 or zero) or to use ENUM with the values being enabled and disabled? What are the advantages or disadvantages?

Let's say instead of just two valid status's, you have 4 or 10 or even more? Do the advantages and disadvantages sway to one side or the other as the number of required values increases?

I found a very bizarre but informative article about 8 reasons why one should not use ENUM.

Even without the article, I know

there is no easy method for adding new values, some techniques are very high risk
numbers should never be used
only use strings (@DTest already mentioned this in his answer)


MySQL query INSERT INTO ... failed - The Table "x" is full I apologize if my question is incomplete or noobish, I have extremely limited knowledge of SQL but the boss wants me to figure this one out.

Environment: Webmin/Virtualmin | MySQL version 5.1.73 on CentOS 6.5

Basically, Im getting an error that the table "Email_Messages" is full. I compare the "Email_Messages" table to other tables in the database and it has a cell count of around 232K but others have cell counts higher: one has 334K.

I still have 8GB free space on disk and googling has told me that there really isnt much of a limit on how big a table can be beyond the OS limitations. It looks like the database in question is only 203mb.

Any ideas on how I can troubleshoot this further/ increase the max table size?

Thanks so much - let me know what other information I can provide.

I don't think the issue is InnoDB table size limit at all.

I have posts about InnoDB Tables having a Table is Full Condition in the InnoDB Plumbing

May 31, 2013 : How to solve "The table ... is full" with "innodb_file_per_table"?
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full

My guess is that your InnoDB Log Files (ib_logfile0, ib_logfile1) are probably too small. (See my second post under CAVEAT #2)

Since you are using MySQL 5.1.73 you will have to increase innodb_log_file_size and innodb_log_buffer_size doing some manual labor. Here is how:

STEP 01 : Add these lines to my.cnf under the [mysqld] group header

[mysqld]
innodb_log_file_size=1G
innodb_log_buffer_size=64M

STEP 02: Login as root@localhost and run this command

SET GLOBAL innodb_fast_shutdown = 0;

STEP 03: Shutdown mysql

service mysql stop

STEP 04: Rename the log files

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.old
mv ib_logfile1 ib_logfile1.old

STEP 05: Start mysql

service mysql start

This will regenerate new log files at 1G each.

Please, give it a try and see if it helps !!!


After manually fail over resume button is disabled and query doesn't resumes As there is no witess available, so when resumes the mirrored database by

ALTER DATABASE <database_name> SET PARTNER RESUME;

  does not resumes the mirroring. Resume button is also disabled. I don't get any error. What is the checkpoint?

Did you also check on the SQL errors? What does it says...?

One thing you can try is to restart your endpoint mirroring on both the primary and secondary instance to see if it helps.

(Stop the endpoint mirroring first)

ALTER ENDPOINT  STATE=STOPPED

(Then start it back up)

ALTER ENDPOINT  STATE=STARTED


UPDATE Query replacing string primary key taking days to resolve I'm working with a DB (not designed by me) that had the primary key set to a 47 character length string, which is causing problems due to the fact that the table has over 20 million rows, and this number increases daily. I'm currently working from my development laptop, which is definitely not as powerful as the server, but I only downloaded part of the DB to test changes/speed of execution.

The pk has 4 parts, 2 are static and so I'm hoping to cut them, one is a semi-unique identifier (aka it should be unique but is not guaranteed to be unique) that could be stored as a bigint, and the other is a UUID-like value that is a pk in another table (with ~30,000 rows) that could be represented as a hex string literal (aka I can store as bytea and use it to output as a string when needed). So, I added serials to both tables, but I need to keep the semi-unique identifier and the reference to the other table as they will be necessary for output purposes, although after this they should be ignorable for joins etc.

So I wrote the following query (tables etc. renamed):

UPDATE bigtable set foreign_key = foreigntable.new_id,
semi_unique_id = (split_part(bigtable.old_id,'.',3))::bigint
FROM foreigntable
WHERE foreigntable.old_id = split_part(bigtable.old_id,'.',2);

I tested this query on 50 rows (AND WHERE bigtable.new_id IN (1,2,3....) and it looked like this would take several days to run.
I thought, maybe it was too much work to do lookups this way, maybe I'll do it in parts, the first query simply to split out the two pieces of information I need, and the second to actually get the information I want from the other table.

So it would look something like this:

UPDATE bigtable SET old_foreign_pk = decode(split_part(old_id,'.',2),'hex'),
semi_unique_id = (split_part(old_id,'.',3))::bigint;

followed by

UPDATE bigtable SET foreign_key = foreigntable.new_id
FROM foreigntable
WHERE foreigntable.old_id = split_part(bigtable.old_id,'.',2);

However, running the query on 50-100 rows (using the method above) seems to suggest it will take 1-2 days to run. That kind of down-time is not really acceptable, can these queries be optimized or is this the cost of having a bad pk for so long?

Edit:

the old pk is stored as character varying and has this form:

log.501ad896c4960b089dd8638f.1344902965419.txt
log.4fe5242a785d0b080de707fa.1344903775142.txt

of which the first number uniquely identifies a row in another table, and the second is theoretically unique but is not guaranteed to be so. so the first is essentially 96 bits stored as a hex string that is a unique identifier (which is why it's only UUID-like), whereas the second is bigger than a integer but smaller than a bigint. the two, when combined do reference files, so they can't be discarded but it's not very efficient in terms of storage.

For the first query it can run fairy fast, but you'd need some temporary functional index for a join:

create index bigtable_old_id_2 on bigtable( split_part(bigtable.old_id,'.',2) );
create index foreigntable_old_id on foreigntable(old_id);

This will allow Potgres to quickly find matching row in bigtable.

You can drop these indexes afterwards if you don't need them anymore.


Config enable sharding collection in MongoDB cluster I'm a new in MongoDB cluster.   I have a question about config enable shading collection in MongoDB:
I have a cluster :
Shard1: replical01/node1:27017, node2:27017
Shard2: replical02/node3:27017, node4:27017
2 config Server on other virtual machine.
Everything worked normally under manual. But I have a question as follows: how can I just shading a collection for only one shard. For example, I used mongos to create a database Test  with 3 collection T1, T2, T3.
Which T1 only located on shard1, T2 only on shard2. T3 is also located on the both shard1 and shard2.
Please help me. Thank you and regards!!

You can enable sharding on collection base, i.e. collection T3 is sharded.
Unsharded collection are created in the Primary Shard, see Sharded and Non-Sharded Collections

Sharded and Non-Sharded Collections
A database can have a mixture of sharded and unsharded collections. Sharded collections are partitioned and distributed across the shards in the cluster. Unsharded collections are stored on a primary shard. Each database has its own primary shard.

Typically the Primary Shard is the least loaded shard at the time when the database was created. When you are not happy with the selected Primary Shard then you can move it with movePrimary command. But you have to move all collections of one database, you cannot move a single collection.
db.adminCommand( { movePrimary: <databaseName>, to: <newPrimaryShard> } )


SQL Server 2016 Install - R Server and R Services Install Failed I just installed SQL Server 2016 Developer and included R Server and R Services to get installed as well. Everything installed correctly except the R Server and R Services. The log file contained this:

2016-07-29T17:07:39 INFO    Command invoked: D:\x64\RSetup.exe /install /component SRS /version 8.0.3 /language 1033 /destdir C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES /logfile C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20160729_125230\RSetup.log /instance MSSQLSERVER
2016-07-29T17:07:39 INFO    Using default cache dir: C:\Users\fs103799\AppData\Local\Temp\
2016-07-29T17:07:39 INFO    Backing up source dir: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\ => C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES_63975911-f850-4ad1-bf7c-21adf0f9ba6d
2016-07-29T17:07:39 ERROR   Error renaming source dir: Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\' is denied.
2016-07-29T17:07:39 INFO    Exiting with code 5

I have no idea why the access to the path was denied. The path exists and there are files there. So the install process was able to store files there, but then couldn't access the folder.

Add the SQLRUsergroup to the LogOnLocally user right assignment. You might have to create the SQLRUsergroup if it does not already exist. Ensure the logon used to execute setup is a local admin on the box.


Anybody using HierarchyId in production? Is it reliable? Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor recommend it, and Paul Nielsen advises against it here.

What is your experience with using HierarchyId in actual production systems?

Which criteria have you used when you were choosing HierarchyId over its alternatives?

I've implemented HierarchyID and found it to provide good performance and easy to use.

I've used it on relatively small datasets (tens of thousands of rows) with hierarchy up to 10 branches deep.

Why use it? The HierarchyID type provides a number of helper methods (such as IsDescendantOf) that make your job easier than rolling your own materialized path.

Paul Nielsen's comment over on StackOverflow is confusing to me - the HierarchyID is a materialized path. I'm more inclined to agree with this comment below his answer.

A better question might be 'why not use it'. It's easy to use, provides a lot of functionality that you'd otherwise be writing for yourself, and performs well (in my limited tests).


What's the name for a table that does not hold "mutable" data? Let's say I have a users table, and each user has a different level of privilege. I have a privileges table with four rows that looks like this:

id | level
---+-----------
1  | viewer
2  | editor
3  | admin
4  | super-admin

In the business context, my users all have a privilege level, 1-4. The users table holds the corresponding user data, and it gets modified when users are modified, added, or dropped, but privileges never really changes, it is just a reference of the four established privilege levels.

Is there a name or term for this type of table?

It could be called an enum, a reference table or a meta-data table.

I prefer to call that kind of table by its real name, i.e. in your case "privileges" or perhaps "PrivilegeLevels".  I don't tend to make distinctions between tables where rows change, and tables where rows don't often change since that tends to needlessly add nomenclature for its own sake.


Need to clean up my Oracle database Requirement is that I need to clean up my database in which my application has extensively tested on, and now I will have to clean up the data and have a database which will be ready to go live. By clean up, I mean the metadata of the tables, users will stay but their data will have to cleaned. What is the fastest and the best method to go about with? Truncate? Any help would be appreciated.

Unless you have a storage snapshot, restore point or backup of the database before the testing started, TRUNCATE is a way to go. You may need to disable FK constraints for that. Another way is to run expdp with CONTENT=METADATA_ONLY, drop the schema, and import the metadata back.