What is the best way to include SQL Server Agent jobs in Source Control?

I'm really glad you asked this question.  I had this very same need for SQL Server jobs in a Visual Studio database project and job deployments along with a database.

  I wanted the solution file to contain everything that is needed for the SQL database process I had implemented, all in one place, to Create this solution AND Deploy this solution to any given server (in just a few clicks).  Components in this solution are:

  Database schema
  SSIS package
  SQL Server configurations (master db “Credential” creation; msdb “Proxy” creation; SQL Agent “Job” creation).

  I wanted to keep it all together for two reasons:

  Simplified deployment (SOURCE: Local Dev Instance / TARGET: Server Dev Instance)
  Solution documentation

So I set to work on figuring this out.  Here is what I created and how I sourced this setup.  It works great for the intended purposes.

Overview:

Create the script files with the TSQL code.  I used the CHECK FOR EXISTENCE method to avoid errors where I could possibly be creating something that already exists in the environment.

Reference: How to check if a Credential exists in a SQL Server instance?

Example:

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'MyJobNameInSqlAgent')
    BEGIN
        print 'Yep, found it!!!'

        --Really you need to add the script to create it...
    END

Create a script file that executes the script files.  More details below.
Publish the database project, and BOOM you've just done something awesome.

SSDT Setup:

Pluralsight: "DB Project development in VS" by Robert Cain | Module: Data and Schema Comparisons, Pre and Post Deployment Scripts

  First I reviewed this helpful Pluralsight Video.  I was able to follow
  along, and what I did was...

  A) New Folder.  Create a new folder in the project called
  [ScriptsPostDeployment]

  B) New Script.  Create a new script to the folder called
  [Create_SqlAgentJobs.sql].

ERROR MSG: "You can only have one post-deployment script in visual studio database project"

You will get this error message if you try to create multiple script files with [Build Action="Post Deploy"].

  In total my project needed not only this one script, but it need some other scripts as well-- Create_SqlAgentJobs.sql, Credential_GEORGES-bl0040ep.sql, ProxyAccount_GEORGES-bl0040ep.sql.  The error was discussed and solved in this thread on StackOverFlow: SQL Server project executing multiple script post deploy.

  In total the new folder has 4 scripts-- 1 to run the scripts; 3 scripts that need to be executed.

ssdt-dbproj_DB-ScriptsPostDeployment.png

SSDT Publish:

I ran the Publish file and chose the option to "Generate Script".  I viewed the script and... POST DEPLOYMENT code was automatically inserted to add the SQL AGENT JOB!!  Success!  Below is the script and script execution messages after running the query on the SQL instance in SSMS...

/*
Deployment script for DBA

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;

GO
:setvar DatabaseName "DBA"
:setvar DefaultFilePrefix "DBA"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"
:setvar DefaultLogPath "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END

GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
    DECLARE @rc      int,                       -- return code
            @fn      nvarchar(4000),            -- file name for back up
            @dir     nvarchar(4000)             -- backup directory

    EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @dir output, 'no_output'
    if (@rc = 0) SELECT @dir = @dir + N'\'

    IF (@dir IS NULL)
    BEGIN
        EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @dir output, 'no_output'
        if (@rc = 0) SELECT @dir = @dir + N'\'
    END

    IF (@dir IS NULL)
    BEGIN
        EXEC @rc = [master].[dbo].[xp_instance_regread] N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @dir output, 'no_output'
        if (@rc = 0) SELECT @dir = @dir + N'\Backup\'
    END

    IF (@dir IS NULL)
    BEGIN
        SELECT @dir = N'$(DefaultDataPath)'
    END

    SELECT  @fn = @dir + N'$(DatabaseName)' + N'-' +
            CONVERT(nchar(8), GETDATE(), 112) + N'-' +
            RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(hh, GETDATE()))), 2) +
            RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(mi, getdate()))), 2) +
            RIGHT(N'0' + RTRIM(CONVERT(nchar(2), DATEPART(ss, getdate()))), 2) +
            N'.bak'
            BACKUP DATABASE [$(DatabaseName)] TO DISK = @fn
END
GO
USE [$(DatabaseName)];

GO
PRINT N'SKIPPING THIS STEP (Manual)!!!  Dropping [olap].[UsageStatsLog_GCOP039_bak20190218]...';

GO
--DROP TABLE [olap].[UsageStatsLog_GCOP039_bak20190218];

GO
/*
Post-Deployment Script Template
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.
 Use SQLCMD syntax to include a file in the post-deployment script.
 Example:      :r .\myfile.sql
 Use SQLCMD syntax to reference a variable in the post-deployment script.
 Example:      :setvar TableName MyTable
               SELECT * FROM [$(TableName)]
--------------------------------------------------------------------------------------
*/

/*
REFERENCE
--https://dba.stackexchange.com/questions/202000/what-is-the-best-way-to-include-sql-server-agent-jobs-in-source-control
--
*/

--DESCRIPTION:
--SQL Agent Job created.
--Credential and Proxy Account required setups are preceded in this script
USE master
GO

/*
REFERENCE
--https://dataqueen.unlimitedviz.com/2015/05/creating-a-proxy-user-to-run-an-ssis-package-in-sql-server-agent/
--https://stackoverflow.com/questions/10946718/how-to-check-if-a-credential-exists-in-a-sql-server-instance
*/

IF EXISTS (select * from sys.credentials where name = 'Credential_BL0040EP')
    BEGIN
        PRINT 'The credential [Credential_BL0040EP] already exists...'

    END

IF NOT EXISTS (select * from sys.credentials where name = 'Credential_BL0040EP')
    BEGIN
        -- Create a proxy credential for xp_cmdshell.
        EXEC sp_xp_cmdshell_proxy_account 'GEORGES\bl0040ep', 'EnterThePasswordHere';   --SELECT  * FROM [master].[sys].[credentials]

        -- Grant execute permission on xp_cmdshell to the SQL Server login account.
        GRANT exec ON sys.xp_cmdshell TO [GEORGES\bl0040ep]

        -- Create a credential containing the GEORGES account PowerGEORGES\PowerUser and its password
        CREATE CREDENTIAL Credential_BL0040EP WITH IDENTITY = N'GEORGES\bl0040ep', SECRET = N'EnterThePasswordHere'

        PRINT 'The credential [Credential_BL0040EP] was created!'
    END

USE [msdb]

/*
REFERENCE
--https://dataqueen.unlimitedviz.com/2015/05/creating-a-proxy-user-to-run-an-ssis-package-in-sql-server-agent/
--http://sqldbatask.blogspot.com/2013/10/credentials-and-proxy-creation-script.html?_sm_au_=iVVM80kNTL4rs4Dq
*/

IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = 'Proxy_BL0040EP')
    BEGIN
        PRINT 'The proxy [Proxy_BL0040EP] already exists...'
    END

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = 'Proxy_BL0040EP')
    BEGIN
        -- Create a new proxy called SSISProxy and assign the PowerUser credentail to it
        EXEC msdb.dbo.sp_add_proxy @proxy_name=N'Proxy_BL0040EP',@credential_name=N'Credential_BL0040EP',@enabled=1

        -- Grant SSISProxy access to the "SSIS package execution" subsystem
        EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'Proxy_BL0040EP', @subsystem_id=11

        -- Grant the login testUser the permissions to use SSISProxy
        EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'GEORGES\bl0040ep', @proxy_name=N'Proxy_BL0040EP'

        PRINT 'The proxy [Proxy_BL0040EP] was added!'
    END

USE [msdb]
GO

/*
REFERENCE
--https://stackoverflow.com/questions/136771/sql-server-agent-job-exists-then-drop
*/

USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DatabaseSSASUsageStats')
    BEGIN
        PRINT 'The job [DatabaseSSASUsageStats] already exists...'
    END

IF NOT EXISTS (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'DatabaseSSASUsageStats')
    BEGIN

        /****** Object:  Job [DatabaseSSASUsageStats]    Script Date: 2/21/2019 2:04:38 PM ******/
        BEGIN TRANSACTION
        DECLARE @ReturnCode INT
        SELECT @ReturnCode = 0
        /****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2/21/2019 2:04:38 PM ******/
        IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
        BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

        END

        DECLARE @jobId BINARY(16)
        EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DatabaseSSASUsageStats',
                @enabled=1,
                @notify_level_eventlog=0,
                @notify_level_email=0,
                @notify_level_netsend=0,
                @notify_level_page=0,
                @delete_level=0,
                @description=N'No description available.',
                @category_name=N'[Uncategorized (Local)]',
                @owner_login_name=N'GEORGES\bl0040ep', @job_id = @jobId OUTPUT
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
        /****** Object:  Step [SSASUsageStats]    Script Date: 2/21/2019 2:04:38 PM ******/
        EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSASUsageStats',
                @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'SSIS',
                @command=N'/ISSERVER "\"\SSISDB\IsolatedPackages\SSASUsageStats\SSASUsageStats.dtsx\"" /SERVER GCO07766 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";3 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',
                @database_name=N'master',
                @flags=0,
                @proxy_name=N'Proxy_BL0040EP'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
        EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
        EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DatabaseSSASUsageStats (Hourly_0800-1700)',
                @enabled=0,
                @freq_type=4,
                @freq_interval=1,
                @freq_subday_type=4,
                @freq_subday_interval=5,
                @freq_relative_interval=0,
                @freq_recurrence_factor=0,
                @active_start_date=20190125,
                @active_end_date=20190208,
                @active_start_time=80000,
                @active_end_time=170000,
                @schedule_uid=N'6cd0bef8-9091-4e30-a0c5-5262685aeff0'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
        EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
        COMMIT TRANSACTION
        GOTO EndSave
        QuitWithRollback:
            IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
        EndSave:

    END

GO

GO
PRINT N'Update complete.';

GO


Evaluating most frequent values of columns in a single query

You can use the windowed version of function COUNT(), split by each category with PARTITION BY to get counts and totals without a subquery (note the lack of GROUP BY):

SELECT
    T.categoryId,

    T.name,
    NameOccurencies = COUNT(T.name) OVER (PARTITION BY T.categoryId, T.name),
    NameTotals = COUNT(T.name) OVER (PARTITION BY T.categoryId),

    T.surname,
    SurnameOccurencies = COUNT(T.surname) OVER (PARTITION BY T.categoryId, T.surname),
    SurnameTotals = COUNT(T.surname) OVER (PARTITION BY T.categoryId)
FROM
    #test AS T

Result:

categoryId  name                NameOccurencies NameTotals  surname             SurnameOccurencies  SurnameTotals
1           John                2               4           Locke               1                   4
1           John Stuart         1               4           Mill                2                   4
1           James               1               4           Mill                2                   4
1           John                2               4           Millar              1                   4
2           Thomas              2               5           Bayes               1                   5
2           Leonhard            1               5           Euler               1                   5
2           Herrmann Amandus    1               5           Schwartz            2                   5
2           Laurent             1               5           Schwartz            2                   5
2           Thomas              2               5           Simpson             1                   5

Then you can use this result to get each percentage simply dividing the occurencies by each total. You can also calculate the best (most frequent) name and surname using a ROW_NUMBER() on this step:

;WITH Totals AS
(
    SELECT
        T.categoryId,

        T.name,
        NameOccurencies = COUNT(T.name) OVER (PARTITION BY T.categoryId, T.name),
        NameTotals = COUNT(T.name) OVER (PARTITION BY T.categoryId),

        T.surname,
        SurnameOccurencies = COUNT(T.surname) OVER (PARTITION BY T.categoryId, T.surname),
        SurnameTotals = COUNT(T.surname) OVER (PARTITION BY T.categoryId)
    FROM
        #test AS T
)
SELECT
    T.categoryId,

    T.name,
    NamePercentage = T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0),
    NameMostFrequentRanking = ROW_NUMBER() OVER (
        PARTITION BY
            T.categoryId
        ORDER BY
            T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0) DESC), -- NamePercentage

    T.surname,
    SurnamePercentage = T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0),
    SurnameMostFrequentRanking = ROW_NUMBER() OVER (
        PARTITION BY
            T.categoryId
        ORDER BY
            T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0) DESC) -- SurnamePercentage
FROM
    Totals AS T

Result:

categoryId  name                NamePercentage  NameMostFrequentRanking surname     SurnamePercentage   SurnameMostFrequentRanking
1           John Stuart         0.250000000000  3                       Mill        0.500000000000      1
1           James               0.250000000000  4                       Mill        0.500000000000      2
1           John                0.500000000000  1                       Millar      0.250000000000      3
1           John                0.500000000000  2                       Locke       0.250000000000      4
2           Herrmann Amandus    0.200000000000  4                       Schwartz    0.400000000000      1
2           Laurent             0.200000000000  5                       Schwartz    0.400000000000      2
2           Thomas              0.400000000000  1                       Simpson     0.200000000000      3
2           Thomas              0.400000000000  2                       Bayes       0.200000000000      4
2           Leonhard            0.200000000000  3                       Euler       0.200000000000      5

Finally, for each available category...

SELECT
    T.categoryId,
    TotalRecords = COUNT(1)
FROM
    #test AS T
GROUP BY
    T.categoryId

we can get the most frequent name and surname with their percentages with a few joins:

;WITH Totals AS
(
    SELECT
        T.categoryId,

        T.name,
        NameOccurencies = COUNT(T.name) OVER (PARTITION BY T.categoryId, T.name),
        NameTotals = COUNT(T.name) OVER (PARTITION BY T.categoryId),

        T.surname,
        SurnameOccurencies = COUNT(T.surname) OVER (PARTITION BY T.categoryId, T.surname),
        SurnameTotals = COUNT(T.surname) OVER (PARTITION BY T.categoryId)
    FROM
        #test AS T
),
MostFrequentRanking AS
(
    SELECT
        T.categoryId,

        T.name,
        NamePercentage = T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0),
        NameMostFrequentRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.categoryId
            ORDER BY
                T.NameOccurencies * 1.0 / NULLIF(T.NameTotals, 0) DESC),

        T.surname,
        SurnamePercentage = T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0),
        SurnameMostFrequentRanking = ROW_NUMBER() OVER (
            PARTITION BY
                T.categoryId
            ORDER BY
                T.SurnameOccurencies * 1.0 / NULLIF(T.SurnameTotals, 0) DESC)
    FROM
        Totals AS T
),
AvailableCategories AS
(
    SELECT
        T.categoryId,
        TotalRecords = COUNT(1)
    FROM
        #test AS T
    GROUP BY
        T.categoryId
)
SELECT
    A.categoryId,
    A.TotalRecords,
    MN.name,
    NamePercentage = CONVERT(DECIMAL(3, 2), MN.NamePercentage),
    MS.surname,
    SurnamePercentage = CONVERT(DECIMAL(3, 2), MS.SurnamePercentage)
FROM
    AvailableCategories AS A
    LEFT JOIN MostFrequentRanking AS MN ON
        A.categoryId = MN.categoryId AND
        MN.NameMostFrequentRanking = 1
    LEFT JOIN MostFrequentRanking AS MS ON
        A.categoryId = MS.categoryId AND
        MS.SurnameMostFrequentRanking = 1

Result:

categoryId  TotalRecords    name    NamePercentage  surname     SurnamePercentage
1           4               John    0.50            Mill        0.50
2           5               Thomas  0.40            Schwartz    0.40

It might be a little big, but you can edit this query with as many new columns you want without adding new SELECT, just repeating the same logic with each new column you want to display and an additional join at the end.

If you have millions of records and query takes long, you might want split each CTE into a temporary table with SELECT ... INTO + CREATE INDEX by categoryId to speed up the process (if you are willing to spend a few resources by creating these tables)


MySQL - Does using an explicit transaction affect query performance?

1: Using autocommit to automatically commit each transaction:

SET autocommit = ON;
UPDATE ...

2: Explicitly turning a single statement into a transaction.  This is 'identical' to #1.

BEGIN;     (aka START TRANSACTION;)
UPDATE ...;
COMMIT;

3: Multiple statements in a transaction:

BEGIN;
UPDATE ...;
UPDATE ...;
COMMIT;

4: Don't use this; some day you will forget to COMMIT:

SET autocommit = OFF;
UPDATE ...;
...
eventually: COMMIT;

5: Not advised.  It can lead to slowdowns due to overflowing the "log":

BEGIN;
a million statements
COMMIT;

Notes:

Each SQL has some small overhead (network transmission; parsing; etc)
Each transaction as a noticeable overhead.  In particular there is disk I/O to make it crash-safe.  Hence #3 is faster than two copies of #1 or #2.
See innodb_flush_log_at_trx_commit for a tradeoff between integrity and speed.

Advice:

First priority: Do what the application needs -- this is, put the appropriate number of statements in each transaction.
Lower priority: Throw together multiple statements when suitable, thereby amortizing the transaction overhead.
In the case of INSERT, it is even better to have a single INSERT statement with multiple rows.  Davit's Comment show a 9x speedup by putting multiple INSERTs in a single transaction.  That is similar to the effect of the single batch insert.
Use ROLLBACK only when appropriate.  It is costly.  This is because InnoDB optimizes for COMMIT and has to do extra effort to undo.
For performance improvements, this whole discussion is pretty far down the checklist.  More important things involve indexing and query formulation.
Multi-thousand-row DELETE and UPDATE have a lot of overhead and stress on the log.  It is better to break them up.


This depends on the MySQL version that you intend to use.
If your view is merely a Join of 10 tables, without any grouping and aggregate calculations, why do you need de-normalization at all?
If your view involves grouping and aggregates, you'll have to test the views performance against the bare queries and against a denormalized design with triggers. There is nothing better than testing with your data and your queries. However, as MySQL Performance Blog article suggests: MySQL VIEW as performance troublemaker, views are more of a way to write compact queries and reuse of code than a performance improvement. Occasionally they even have a negative impact on performance:

It is also very dangerous if you assume MySQL would optimize your VIEWs same way as more advanced database systems would. Same as with subqueries and derived tables MySQL 5.0 will fail and perform very inefficiently in many counts.

But note that this a rather old article (2007) and it certainly doesn't take into account the recent improvements on optimizer, made in MariaDB 5.3 and 5.5 (which can be used as drop-in replacements of MySQL 5.1 and 5.5). This section is relevant to your question:

Optimizations for derived tables and views

No early materialization of derived tables (e.g. subqueries in a FROM clause) and materialized views (EXPLAIN is always instantaneous)
Thanks to Derived Table Merge optimization, mergeable derived tables are now processed like mergeable VIEWs.
Derived Table with Keys optimization gives the optimizer an option to create indexes over materialized derived tables
Fields of merge-able views and derived tables are involved now in all optimizations employing equalities

Another feature that  MariaDB has implemented is VIRTUAL PERSISTENT columns which can be helpful for having certain calculations stored in tables, without using the (evil) triggers.
(Sidenote: one problem in MySQL with triggers is that when mixed with cascading Foreign key constraints, they are not activated by cascading actions MDEV-11472).
MySQL 5.6 also has improvements on the optimizer regarding views and derived tables but I have no idea when a stable release will be out.
To summarize, I think you should carefully consider which version you will implement your database on and if possible test the various design options in the available versions (MySQL 5.1 (and 5.5), MariaDB 5.3 (and 5.5), MySQL 5.6(under development)).


Query Linked Access database on network share via SQL Job Agent

Note: SQL Server Agent itself does not access the share

It depends on how you have mapped linked server security between local and remote credentials. SQL Server Agent running as local system will connect to SQL Server, which then accesses the share based on linked server credentials.

SUSER_SNAME will be different of course in this case to when you run it.

Options:

add a linked server login mapping for "local system"
configure all local credentials to use one remote login
look at SQL Server Agent proxy accounts for the job step (changes SUSER_SNAME)


Why does my database structure and SELECT operations generate NULLs?

There's a problem with your first NOT EXISTS. Let's take a look at your current query:

INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT
    a.Vnr
FROM dzp.accounts_stage a
WHERE NOT EXISTS(
    SELECT id.vnr
    FROM dzp.contractid id
)

So, what's happening here is that the NOT EXISTS that you are using will return true only when the dzp.contractid table is empty. Once it has one row (or more), the NOT EXISTS will return false for every row of your main table, since SELECT id.vnr FROM dzp.contractid has a result set, and you end up inserting duplicated values in your table.

What you need to do is correlate this subquery with the main table:

INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT
    a.Vnr
FROM dzp.accounts_stage a
WHERE NOT EXISTS(
    SELECT id.vnr
    FROM dzp.contractid id
    WHERE id.vnr = a.vnr
);

Also, the CROSS JOIN with the data from the latest import doesn't make much sense to me, and neither the fact that you aren't checking if the data exists already in the accounts table.


Why does DBMS_SESSION.session_trace_enable create multiple files?

This could be for at least 2 possible reasons:

Shared server architecture: SQL trace files belong to server processes, but when you use shared server architecture, your session does not have a single dedicated server process, your session can exist in several different server processes over time.
Parallel processing: when your statements run in parallel, each slave thread is a different process and as SQL trace files belong to processes, you have multiple processes running the same statement, hence the multiple trace files.

You can not force the creation of a single trace file, but you can consolidate these traces into a single file with trcsess, based on your SID, module name, action name, service or client identifier.


Exporting MySQL Database into single SQL File in TOAD

This feature is not available in TOAD for MySQL. See the answer to the question below:

https://stackoverflow.com/questions/4240173/how-do-i-generate-a-script-of-all-database-objects-in-toad-for-mysql

Kind Regards, MB


Converting varbinary data to varchar in SQLServer leads to unexpected results

There are a few of confusions going on in the question, leading to the unexpected results:

The terms VARCHAR and NVARCHAR are being used interchangeably (or so it seems), yet they are quite different. NVARCHAR is a 16-bit encoding – UTF-16 LE (Little Endian) to be exact – and this does not change. VARCHAR is an 8-bit encoding, and the specific 8-bit encoding being used is determined by the Code Page associated with the Collation of the column (we will ignore VARCHAR data in string literals and variables for now since this question is about data stored in a table). If you want to know what Code Page is associated with a particular Collation, you can use the COLLATIONPROPERTY built-in functions:

SELECT COLLATIONPROPERTY(N'Latin1_General_100_CI_AS_SC', 'CodePage') AS [CodePage];
-- 1252

When converting between VARBINARY and either VARCHAR or NVARCHAR you need to be careful to be consistent with that string datatype. You can't convert from VARCHAR to VARBINARY and then take that same VARBINARY and convert it to NVARCHAR.
The Encoding class in .NET represents a particular encoding of text, whether it be 7-bit, 8-bit, 16-bit, 32-bit, or variable (such as UTF-8). In order to get "expected" results, you need to create an encoding that matches what you need to convert to or from, with respect to the byte[] representation. Strings in .NET are always UTF-16 LE (same as NVARCHAR ), and this is what the Unicode encoding in .NET refers to. The byte[] representation of an encoding will be of whatever encoding was created, but the string representation will always be UTF-16 LE. So, which encoding to create depends on what type of data you are dealing with:

NVARCHAR: Use Encoding.Unicode
VARCHAR: Determine the Code Page of the Collation via COLLATIONPROPERTY(N'collation_name', 'CodePage') and then use that int value in Encoding.GetEncoding(CodePageIntValue).

When using the CONVERT built-in function, be careful which "style" number you are using. For example:

SELECT CONVERT(VARBINARY(50), N'bob');
-- 0x62006F006200

Now take that returned VARBINARY value and convert it back into NVARCHAR, using "style" values of 0 (default) and 1 (which is what your CONVERT function is using in the question):

SELECT CONVERT(NVARCHAR(MAX), 0x62006F006200, 0) AS [Style_0],
       CONVERT(NVARCHAR(MAX), 0x62006F006200, 1) AS [Style_1];

Returns:

Style_0        Style_1
bob            0x62006F006200

So, if the following statement from the question is true:

  In the application byte array conversion is done using Encoding.Default.GetString(bytearraydata)

then that would equate to using VARCHAR instead of NVARCHAR, and a "style" value of 0 (or nothing) instead of 1:

CONVERT(VARCHAR(MAX), 0x62006F006200)


I cannot say for sure, never having encountered this exact problem before, but I'd recommend looking into the local security policies. In the standard GPO tree, you can find those under:
"Computer Configuration" -> "Policies" -> "Windows Settings" -> "Security Settings" -> "Local Policies".
Under that, I'd look particularly closely at the "User Rights Assignment" section.

If your server admins won't let you see what GPOs are being applied, you can get a list yourself if you've got administrative access on the server you're having problems with.
Assuming you're running Windows Server 2008 or newer on it, try:

gpresult /H {Your path here, for example C:\gporeport.html}

This will output an HTML report to the file you specified, which details what GPOs are applying, what specific settings are configured and which GPO is controlling which setting.
This might not be a direct answer to your question, but a good starting point to troubleshoot from.
(Specifically, if anything at all is set in the local security policy, try asking your server admins to filter out the specific GPOs that are applying those settings. Narrow it down, rinse and repeat.)


Sql Server And Very Hot CPU % of Total Wait Time

SOS_SCHEDULER_YEILD is not the cause, it's a side effect. It just means that something else is burning the cycles. In your graph, the second is LOGGING; this makes sense considering you are running replication. Based on my prior experience, you are probably experiencing log reader. This is typical; you can reduce your PollingInterval and increase CommitBatchSize

Use ProcessExplorer, sort by highest CPU usage process, must be sqlservr. Right click on sqlsrvr, go to properties, click on threads tab, sort by highest CPU column to see the highest cpu consuming thread. Note the thread id(s) down. Execute the below statement to see what is query is using the most CPU

select r.session_id, st.text, qp.query_plan from sys.dm_os_threads as ot join sys.dm_os_tasks as t on t.worker_address=ot.worker_address join sys.dm_exec_requests as r on t.session_id=r.session_id cross apply sys.dm_exec_sql_text(r.sql_handle) as st cross apply sys.dm_exec_query_plan(r.plan_handle) as qp where os_thread_id=<from process explorer high CPU thread>


If you have to know read status for each notification, use separate table similar to this:

CREATE TABLE notification_read_status (
  notif_id bigint not null references notifications,
  user_id bigint not null references users,
  read_when timestamptz not null default now(),
  primary key (notif_id, user_id)
);

The boolean is not needed here. Existing row means true, missing row means false.

Possibly, if this is a very large scale app, and if you can simplify the model, then you can save some resources:

notification ID will be a monotonic bigint never going down and
store only one the last ID read (which means all previous notifications for given user are also read) - this will be just one field in users table.


SQL Server 2012, triggers missing after restore database

A wild guess - are the triggers based on CLR code? If CLR Integration has not been enabled on the machine you are restoring to then the triggers won't work.

CLR Integration can be enabled with the following code.

exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO


Find Table Variables definitions in Stored Procedures

The LIKE function is not designed to handle the level of complexity that is necessary for this type of string pattern. The % matches 0 or more of anything. Hence, DECLARE @%TABLE matches any code that has "DECLARE @" + anything + "TABLE", where anything can be 1000 lines, or 10 MB worth, of code. Or even just a space. For this you really need Regular Expressions (i.e. RegEx). However, there is no built-in RegEx functionality in SQL Server. However, as long as you are using SQL Server 2005 or newer (and not using Azure SQL Database [not including Managed Instances] or SQL Server 2017 on Amazon RDS), then you can get RegEx functionality via SQLCLR. You can either code these yourself (and there are plenty of examples floating around), or you can download and install SQL#, a SQLCLR library (that I wrote) that has a Free version which includes several RegEx functions.

One thing to keep in mind is that table variables can be declared using a user-defined table type (UDTT) as the definition. In such cases you would not look for the string TABLE but the name of the UDTT. And, to complicate things, UDTTs can be specified with an optional schema name followed by a period. Oh, and, the UDTT name and/or optional schema name may or may not be delimited by square brackets (we will ignore, for the moment, using double-quotes as identifier delimiters).  Oh, AND, if the optional schema name is specified, there can be white-space on either side of the period separator (fo' reelz, yo!). And let us not forget that @, #, and $ are also valid characters to use in identifiers. Fun stuff!!!

The following pattern should cover the vast majority of situations (not 100% of possibilities, but highly unlikely that someone would be doing something outside of what this will match ** ):

DECLARE\s+@[\w@#$]*\s+(?:TABLE\s*\(|(?:\[?\w[\w@#$ ]*\]?\s*\.\s*)?\[?\w[\w@#$ ]*\]?)

The example below uses the SQL# function, RegEx_Matches, and the pattern shown above to find table variable declared either directly as tables or by using a user-defined table type:

-- DROP TYPE dbo.Test_Table;
IF (TYPE_ID(N'dbo.Test_Table') IS NULL)
BEGIN
  CREATE TYPE dbo.[Test_Table] AS TABLE ([ID] INT);
END;

DECLARE @SQL NVARCHAR(MAX) = N'
Declare @a dbo.Test_Table;
Declare
@b dbo.[Test_Table];

Declare @c [dbo].Test_Table;
Declare @d [dbo ].[Test_Table];
Declare @e [dbo]
    .
        [Test_Table];

Declare             @f Test_Table;
Declare @g
[Test_Table];
Declare @h [Test_Table  ]; -- space(s) at the end, not tab(s)

DeclarE @T1     TABLE(col1 int);
DeclarE @T_2 TABLE
(col2 int);
DeclarE
    @$#@        TABLE (
    col3 int);
';

EXEC (@SQL); -- make sure that T-SQL is valid

SELECT * FROM SQL#.RegEx_Matches(@SQL,
N'DECLARE\s+@[\w@#$]*\s+(?:TABLE\s*\(|(?:\[?\w[\w@#$ ]*\]?\s*\.\s*)?\[?\w[\w@#$ ]*\]?)',
                                 1, N'IgnoreCase');

That SELECT returns the following results:

MatchNum   Value                                StartPos   EndPos   Length
1          Declare @a dbo.Test_Table            3          27       25

2          Declare
           @b dbo.[Test_Table]                  31         58       28

3          Declare @c [dbo].Test_Table          64         90       27

4          Declare @d [dbo ].[Test_Table]       94         123      30

5          Declare @e [dbo]
            .
                [Test_Table]                    127        162      36

6          Declare              @f Test_Table   168        191      24

7          Declare @g
           [Test_Table]                         195        218      24

8          Declare @h [Test_Table  ]            222        246      25

9          DeclarE @T1      TABLE(              287        305      19

10         DeclarE @T_2 TABLE
           (                                    318        338      21

11         DeclarE
            @$#@        TABLE (                 351        373      23

You should be able to find everything in your code by doing something along the lines of:

SELECT *
FROM   sys.sql_modules mdl
CROSS APPLY SQL#.RegEx_Matches(mdl.[definition],
  N'DECLARE\s+@[\w@#$]*\s+(?:TABLE\s*\(|(?:\[?\w[\w@#$ ]*\]?\s*\.\s*)?\[?\w[\w@#$ ]*\]?)',
                               1, N'IgnoreCase') tvar;

** Well, ok, this pattern doesn't account for comments placed between the "DECLARE" and variable name, or between the variable name and "TABLE" or UDTT name. But, that is a whole other level of complication that is, for the moment at least, maybe not best handled inside SQL Server, in which case something outside of SQL Server would be your best option. Something like TSqlParser, as Martin suggested in a comment on the question, or maybe SMO can handle this.

DECLARE       -- This
    @Valid    -- is
  /* valid
       /* DECLARE @NotReal TABLE([Col1] TINYINT);
    */
  */  TABLE   -- T-SQL
    ( [col1]  INT );


How can I disconnect clients from MySQL?

You could use "SQL to SQL" method below (just pass in extra connection options to mysql client as needed):

shell> mysql -NBe "SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'some_username';" | mysql -vv

Note: This works with MySQL 5.1 and 5.5. This would have to be implemented differently for older MySQL versions as information_schema does not have the processlist table.

Options used:

-N means that you do not want to get column names back.
-B puts it into batch mode, so that you do not get MySQL's table layout.
-e executes the following statement.
-v controls the verbosity, could be used up to three times.

Explanation of how it works:

First the KILL statements are generated along with IDs.

shell> mysql -NBe "SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'some_username';"

Sample output:

KILL 1061;
KILL 1059;
KILL 1057;

Then those statements are executed.

shell> mysql -NBe "SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'some_username';" | mysql -vv

Sample output:

--------------
KILL 1061
--------------

Query OK, 0 rows affected

--------------
KILL 1059
--------------

Query OK, 0 rows affected

--------------
KILL 1057
--------------

Query OK, 0 rows affected


How is an idle SQL Server connection causing blocking?

If you're comfortable with Data Collector, you could download and install ExtendedTSQLCollector and set up a collection set to capture Blocking and Deadlocking events with XE. I have a blog post that explains how to do that.

In order to collect sleeping SPIDs that cause blocking, you could set up another collection set (or an additional collection item in the same collection set) that captures information about those SPIDs. You can use the query found in this answer. You can also tweak the query to extract information about the objects the sleeping SPID is holding locks on.

With this setup you should easily identify blocking SPIDs. What you will not identify easily is what was the sleeping SPID doing, except for the last command issued. With the information collected you can however set up something more specific like a XE session filtered for application name and/or hostname (or whatever makes sense) using a ring buffer target and dig through the batches it ran before becoming idle.

Good luck!


It looks like what you have done is run rs.initiate() on both the primary and the secondary server.

In this case the outcome is that you have two different replica sets called ittw rather than a single replica set with replication.  The primary knows about both nodes, but the "secondary" you setup is also configured as a primary for a replica set only containing itself.  This definitely won't work.

As per the instructions on Deploying a replica set, you should only run rs.initiate() on the primary, and then use rs.add("hostname:port") to add secondaries to the same replica set.

To fix up your dud secondary to a clean state, you should:

stop the secondary using db.shutdownServer() or appropriate command like service mongodb stop (depending on how you installed MongoDB)
remove the files in the dbpath directory for the secondary
restart the secondary
use the mongo shell to connect to the primary, and rs.add() your secondary

If all is well you should see consistent rs.status() information on the primary and new secondary.


MySQL 5.6 my.ini file is in the path where the data is stored, in my case

C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6

instead of the path where mysql is install

C:\Archivos de programa\MySQL\MySQL Server 5.6

, like older versions.

We have to change C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6 \my.ini file to the changes take effect, and restart the service.

On Windows, MySQL programs read startup options from the following files, in the specified order

More info Using options files

Modifying Registry

As well it is possible to do it in the registry with regedit command

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services

and change binPath

MySQL commands

You can see configuration options through the following command and look up 'Default options' to see the paths where configuration .ini and .cnf are.

> mysqld --verbose --help

And with this other command you can see mysql variables

> mysqladmin variables

Command line

You can also see the default my.ini file that mysql is using by looking at the service properties, in executable path on option --defaults-file.

It is possible to change service through command line
You can use the sc config command to change the path a service points to:

SC CONFIG YourServiceName binPath= "C:\SomeDirectory\YourFile.EXE"

This will update the service called YourServiceName and change the "Path to Executable" entry to C:\SomeDirectory\YourFile.EXE. You will want to restart your service afterwards, which you can do with:

NET STOP YourServiceName & NET START YourServiceName


Would installing 32 bit packages on the existing 64-bit VM be an option to run Oracle 9.2?

It is possible, I had to do it myself once.
BUT: It is not enough to just to install 64 bit packages on any new linux distribution. You need a supported version (that is supported by 9.2).
just hacking the config file did not work out, due to many issues e.g. Kernel versions.

IIRC I ended using an Oracle Linux 4 install.

If you need a more modern and supported OS you might want to give Solaris x86 a try. Usually old software runs with much less issues. Known good installation: Solaris 10 with 9.2


SQL agent jobs are stored in the system msdb database, so backing up that database will do what you need.

In a disaster, you can restore the entire msdb database, or, if necessary, recover individual jobs from a restored copy of msdb:

Serverfault: Recovering a specific job definition from an MSDB backup?

If you want them in a more immediately usable format, you can right-click and script out individual jobs manually, or if you'd like to script out all jobs on the server at once:

Stack Overflow: Automatically create scripts for all SQL Server Jobs


MySQL: Add column to unique index or add new index?

For one particularly heavy select query, I need an index on
  (userid,name,valueclass).

This is a good reason to add an index. It would also be good to examine your query plan. BTW, underscores between words make variables more legible.

  Are there any good reasons why I should NOT just add a column to the
  unique index I already have?

A unique index enforces uniqueness. If you have a need for the two column uniqueness then most likely adding a third column to that will compromise the integrity of your constraint. To assist your heavy select query the best option would be to add an index to (userid,name,valueclass) without the unique constraint.


what is the difference between 'order' and 'order line'?

Generally, an order contains information about the order.  Who placed the order, what time they placed it, the shipping address associated, the billing address, payment method, when it was fulfilled, etc.  It often does not contain any information about what was ordered.

The order line generally contains information about what was ordered, this is done because a single order can have multiple items in it.  So the order line would specify the item ordered, the quantity ordered and the price charged, and there would be one line for each different item ordered.


Getting an aggregate (here: min) for a group of values

Postgres has distinct on that does just what you are looking for:

select distinct on (id) id, value_a, value_b
from t
order by id, value_b;

SQLFiddle here

You haven't specified what you would like to see when the are multiple values of value_b for an id though.


Fault-tolerant storage, a stand-by database (Data Guard in your case), and the database backup are three different solutions to different types of problems. Depending on how much downtime you can afford, you may need to implement all three.


permission denied on mongod.lock, despite it being owned by mongodb

It works after I did:

sudo chmod +r /run/media/minze/ssd
sudo chmod +x /run/media/minze/ssd

I don't understand why.

mongodb.lock is located in /run/media/minze/ssd/mongodb/mongodb.lock

ls -lha gives me (permissions of '.' are important here):

drwxr-xr-x 8 minze   minze   4.0K Dec  3 17:40 .
drwxr-xr-x 3 root    root      60 Dec  3 10:49 ..
drwx------ 2 root    root     16K Oct 15 20:24 lost+found
drwx------ 4 mongodb mongodb 4.0K Dec  3 17:54 mongodb

now


You need a subquery like this for the products_cross , so that Mysql has a temporary table to select the wanted ids

CREATE TABLE IF NOT EXISTS `products_cross` (
  `id` int(6) unsigned NOT NULL,
  `GRPCD` int(10) unsigned NOT NULL,
  `brand` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `products_cross` (`id`, `GRPCD`, `brand`) VALUES
  ('1', '33111', '107'),
  ('2', '33111', '103'),
  ('3', '44111', '105'),
  ('4', '44111', '101');

SELECT * FROM products_cross

id | GRPCD | brand
-: | ----: | ----:
 1 | 33111 |   107
 2 | 33111 |   103
 3 | 44111 |   105
 4 | 44111 |   101

DELETE FROM `products_cross`
WHERE GRPCD NOT IN (SELECT GRPCD FROM (SELECT * FROM products_cross ) pc WHERE brand=107)

SELECT * FROM products_cross

id | GRPCD | brand
-: | ----: | ----:
 1 | 33111 |   107
 2 | 33111 |   103

db<>fiddle here


Implementing a family database using Access 2010

You can certainly create a database such as you describe using Access, if that is what you want to do. I don't see any reason why it would not be feasible. If you want to include other relationships (tribe/clan/family), make those part of your logical data model at the start.

"Perfectly normalized" is a relative term. Normalize according to what you are trying to accomplish. 5th normal form probably isn't going to help in a reporting database where denormalized happens to be what is needed. What you need is a data model that matches what you plan to do with it. Define the entities and relationships in terms of the problems you need to solve. Normalize where needed, and denormalize where needed.

MS Access is OK, I guess, as a front-end GUI builder, if your expectations aren't too great. I've never worked with an Access application that was really well done. A few of them have looked impressive, but they were hard to use. I'm not saying that it is not possible to develop a good one, but I suspect that a lot of people give up before they arrive there.

Whenever I have tried to develop using Access I have run into an endless series of restrictions on what I could do. The SQL dialect itself has many limitations, and the VB programming environment is full of pitfalls. I haven't worked with it now for some years, and I don't know if it has moved on to VB.Net since then, but working with "On Error" in the versions I used was awful. If you are going to do everything in Access then you are probably going to be stuck doing GEDCOM imports and refreshes in VB.

On the other hand there are many things you can do with the forms features, if you know them well. If you don't, you may waste a lot of time trying to do things and failing because you didn't know the particular trick for that task. I say this as somebody that doesn't know all the tricks.

I would like to be able to import my data into a SQL database as well, in order to do queries against it that typical genealogy software does not do. I came across your question while looking to see what might already be out there.

I would be inclined to use SQL Server, because SQL Server development is what I do for a living. If you don't know it well, it can present a steep learning curve. But importing, refreshing, and exporting GEDCOM data is straightforward, if not especially easy, using Integration Services, standard reports can be created using Reporting Services (or even Report Builder), and I might even see possible applications for attribute management using Master Data Services. This is all on top of a powerful database engine, and all of these components are available in the Developer Edition for about the cost of a genealogy software package, if you don't count the time to develop the application, which could be quite a lot.

Personally, I don't have time to develop something like this right now, and I will keep on looking. Good luck.


I'm assuming that the certificate has been created for Server Authentication using your linux server. What you are essentially describing is the same concept that public CAs use to provision their certificates. The CA, or Certificate Authority, is the service or application used to attest to the identity of users, computers, and organizations as well as generate, manage and renew certificates. I would recommend removing the CA service from your SQL Server. You won't need it and if you do use it to create a certificate for the SQL Server, you will be creating a self-signed certificate on your SQL Server, which will leave you open to the risk of a man in the middle attack. It is also possible to encrypt all sessions by simply setting Force Encryption to true, but this will cause the SQL Server to internally generate a self-signed certificate, which is not advised for the same reason.

As long as your SQL Server, linux system and client system can communicate with each other and you use a certificate from the CA on the linux system, what should happen is that the client initiates a session with the SQL Server, which then responds with the request for a secure session and presents a certificate generated from the linux server to the client for validation. Then the client should validate the certificate in two ways, by verifying with the linux system that it is a valid certificate and by using the digital signature to verify that it has not been modified. The client system will also need a way to verify that the certificate can be trusted, which will mean adding it or a root certificate from the linux CA to the trust store of the client.

The basics of enabling SSL on your SQL Server are to import the certificate and private key into the certificate store of the service account running the database engine and then set the Force Encryption property to yes in the properties of the server protocols using SQL Server Configuration Manager. In your case, you may need to convert your pem and key files into a format that Windows Server supports. The question Convert a CERT/PEM certificate to a PFX certificate specifically addresses using OpenSSL to accomplish this.


How do you minimize downtime for an ALTER?

There are two tools you can use to do this.

1) Percona's PT-ONLINE-SCHEMA-CHANGE which can be found here (Percona is a very big hitter in the MySQL world).

and there is also

2) Shlomi Noach's oak-online-alter-table which can be found here. Noach's c.v. is also impressive.

Public awards
Oracle Technologist of the Year: Developer award
Oracle ACE
MySQL Community Member of the Year, 2009 award.

Obviously partitioning could also help your speeds, but if you can't afford to partition now, these tools may be of assistance to you.


How to store multiple vendor data in Google Cloud SQL?

The advantage of using a single database is in having a single schema and a single codebase.  If you make a change, then all vendors get the change.  (That can be a disadvantage, too, if they don't like the change).

Apart from that, the disadvantage is if the vendors have significantly different requirements and need different schemas and/or they interpret the data differently.  But if some of them want to have a schema or code change, then you have to manage all the different versions.

It's easy enough to serve up data for a given vendor to a given user by using the right queries.  Unless you want to be spending most of your time managing versions, you probably want to keep them all synchronized with the same schema and code.


Assuming they are running on the same session, no, as you've already said. Are there ways around it? Sure, take it out of that session by plopping the messages in a service broker queue with an activated stored procedure. The only part I'm not quite sure on is if the WITH LOG part of raiserror would be subject to that or not - though you probably don't want to write the results to the errorlog.

It could also be shortened so that each part is its own transaction but that may not be suitable for your needs.


Average Time to Load 1.2 million records from Oracle INTO SQL SERVER 2008R2

Time taken would depend on

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

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

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

Raj


Not valid integer types

If the values in the field are not actually valid integer types (digits and leading - only), a JOIN like @mustaccio suggested would fail with an exception: you couldn't cast to integer at all.

It would have to be the other (even more expensive) way round:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id::varchar = t2.id;

Also, since @mustaccio misleadingly suggested to use int8: Don't. integer equals int4. int8 would be bigint.

You can support that with a functional index:

CREATE INDEX tbl1_taxonomy_id_idx ON tbl1 (cast(taxonomy_id AS varchar));

Valid integer types

If we are dealing with valid integer types, you could just convert your column id to integer - if your setup allows that.

ALTER TABLE tbl2 ALTER COLUMN id TYPE integer USING id::int;

Then your problem is gone for good:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id;

Barring that (if you cannot convert the column for some reason), a functional index would help:

CREATE INDEX tbl2_id_idx ON tbl2 (cast(id AS int));

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id::int;


mapping/translation table design for one to one or multiple to one condition for mapping

May be I'm missing something, but wouldn't simple

create table map (
  group_type varchar(20) not null,
  code_a varchar(20) not null,
  code_b varchar(20) not null
)

do what you want? (group_type, code_a) would be a unique constraint, and the choice of the appropriate index would be dictated by your DBMS abilities.


Multiple disparate functions within stored procedure

In summary, this is poor practice. While not "wrong" in an absolute sense it introduces avoidable risks.
SQL Server optimizes each statement separately. The execution plan for the "everything" stored procedure will not be noticeably different to the combined plans of having each bit in its own stored procedure (SP). There will be a very tiny overhead of evaluating the IFs, but you would never measure it, much less notice it.
It seems they're treating the SP as a plug-in library. SPs are not libraries. They should be treated as methods. Each will have its own clear, distinct purpose and implementation.
How would code review treat application code that had a class with a single mega-method? Would the devs accept this style in the UI or business tier? Likely not. Don't do it in the DB tier either.
There are some run-time implications. The mega-SP has a single plan. Changing any part of the code will cause the whole plan to be re-compiled, stalling all clients wishing to run any part of that plan, even if they're executing a function whose code has not changed. The time to compile a large SP is longer than the time to compile a small one. Plans have to be loaded in toto to be executed, even if only a small fraction of the code path is traversed. So fewer plans can be in memory at any one time, meaning plans get evicted from cache more often, causing re-comiles and client slowness. If the server has more memory than it needs and is never under memory pressure this may never manifest as an actual issue.
Project management becomes more complicated, too. With a single SP lots of changes will affect that one module risking code merge fails. (Your DB code is in source control, right?)
The code is less self-documenting. What does function "1" mean in this SP? Is it these same meaning in that SP? Should every SP have a function "1"?
The parameters must be a superset of all the functions. Now you need even more documentation to say "function 1 uses @a, @c and @f; function 2 uses @a and @g but don't populate @c or year-end crashes." Likely every parameter will be optional losing further built-in documentation and validation.


SQL Server Replication w/o network connection

If you use log shipping, you can do the copy operation of the transaction log backups manually to take them from one server to the other.


In PHP, consider using

error_log($str);

It will write to the web server's log file.

$str should be constructed from diagnostics provided by MySQL or php.  You might consider prefixing the string with some keyword to make the log file more easily searchable.

Alas, this means identifying all places in your code that might have "errors" or "warnings" and adding code to grab the relevant message.  But it does put all the messages into a central location for later study.


What is the best way to store product size and color for an Ecommerce database?

You will need to implement exclusive subtypes to handle the attributes for each product type.  A (very) abbreviated example:
CREATE TABLE ProductType
(
  ProductTypeCd  CHAR(4)      NOT NULL /* Something human readable - SHOE, SHRT, BAG, BALL, etc */
 ,Name           VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_ProductType PRIMARY KEY (ProductTypeCd)
 ,CONSTRAINT AK_ProductType UNIQUE (Name)
)
;

CREATE TABLE Product
(
  ProductId      INT           NOT NULL /* If you have a code to track inventory/items, use that instead */
 ,ProductTypeCd  CHAR(4)       NOT NULL
 ,Name           VARCHAR(50)   NOT NULL
 ,Description    VARCHAR(200)  NOT NULL
 ,Price          DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT FK_Product_Discriminated_By_ProductType FOREIGN KEY (ProductTypeCd) REFERENCES ProductType (ProductTypeCd)
 ,CONSTRAINT PK_Product PRIMARY KEY (ProductId)
 ,CONSTRAINT AK_Product UNIQUE (/* Whatever columns are necessary to prevent duplicates - if you are using a product code/inventory code for your PK this may be unnecessary */)
)
;

In this case ProductTypeCd works as a discriminator to define the subtype of Product, which we would then define like so:
CREATE TABLE ProductShoe
(
  ProductShoeId  INT NOT NULL
 ,GenderCd       CHAR(1)  NOT NULL /* M/W/U, should control through FK reference */
 ,ShoeSizeCd     CHAR(4)  NOT NULL /* 9/9D/9DD,12E, etc., should control through FK reference */
 ,ColorCd        CHAR(3)  NOT NULL /* Something human readable, RED, BLK, YEL, GRN, BLU, etc., again enforce through FK reference */
 ,CONSTRAINT FK_ProductShoe_Is_Product FOREIGN KEY (ProductShoeId) REFERENCES Product (ProductId)
 ,CONSTRAINT PK_ProductShoe PRIMARY KEY (ProductShoeId)
)
;

CREATE TABLE ProductShirt
(
  ProductShirtId   INT      NOT NULL
 ,GenderCd         CHAR(1)  NOT NULL /* Same treatment as for Shoe */
 ,ShirtTypeCd      CHAR(4)  NOT NULL /* TEE, POLO, etc. */
 ,ShitSizeCd       CHAR(4)  NOT NULL /* XS/S/M/L/XL/XXL/etc., again, use FK */
 ,ColorCd          CHAR(3)  NOT NULL /* Same treatment as for Shoe */
 ,CONSTRAINT FK_ProductShirt_Is_Product FOREIGN KEY (ProductShirtId) REFERENCES Product (ProductId)
 ,CONSTRAINT PK_ProductShirt PRIMARY KEY (ProductShirtId)
)
;

There is some care that must be taken to ensure the tables align with the proper ProductType.  Depending on the DB engine this would have to be handled via a trigger instead of a check constraint.  This SO answer handles it in depth: https://stackoverflow.com/a/4898314/13942986
From comments:

@Eniola so currently I am on the planning/design phase and I will be
using a relational sql database thinking of using SqlLite or Mysql

SQLite is not a relational database, MySQL barely so.  Between the two, use MySQL.


Update fails on linked server, Select works

I don't know exactly why that is failing, but there are probably better alternatives.
First is the general best-practice to avoid the propriatary and confusing UPDATE FROM:
Start with a working SELECT and then do
with q as
(
 Select itemcode, UDF_AdditionalBarcode, Id, Additionalbarcode FROM
 openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
 ...[ProductGroup] where IsActive = 1') WEBDB inner
 join LocalDB..Items CI on WEBDB.Id = CI.itemcode where
 nullif(UDF_ADDITIONALBARCODE,'') <> isnull(Additionalbarcode,'')
)
update q set Additionalbarcode = UDF_ADDITIONALBARCODE

But I would go farther and simply spool the OPENQUERY results locally first:
Select *
into #webdb
FROM openquery([remoteserver], ' SELECT Id ,[AdditionalBarcode] FROM
...[ProductGroup] where IsActive = 1')

When working across linked servers, being explicit about how you're moving data back and forth is usually best.


In SQL Server a Latch used to provide brief, exclusive access to a database page.  When multiple sessions need to modify the same page, all but one will enter a "PAGE LATCH (non-I/O) wait".  Your wait stats seem to indicate that all your sessions are lining up to modify a single page.

There are couple of different scenarios where Latch contention can become an issue.  The most common are when all a large number of sessions need to write to the same "hot" page, typically at the end of a clustered index, and latch contention in Tempdb for metadata pages when a large number of sessions create and drop temp tables or table variables.  See  Diagnosing and Resolving Latch Contention on SQL Server for details.

But in the context of a synthetic load test, this Latch contention may represent a defect in the test workload design that is preventing the sessions from running concurrently and accessing all of the server resources.


Add a new collation to a Postgres database

You don't need to create a new DB cluster. You can use CREATE COLLATION in Postgres 9.1 or later. Example in the manual:

  To create a collation from the operating system locale fr_FR.utf8
  (assuming the current database encoding is UTF8):

CREATE COLLATION french (LOCALE = 'fr_FR.utf8');

Be sure to read the chapter Managing Collations in the manual to understand the details. In particular:

  Within any particular database, only collations that use that
  database's encoding are of interest. Other entries in pg_collation are  ignored.

I.e., the collation has to match your database encoding to be available.
But there's more. Read it.


Lost my SQL Server 2005 host, SQL Server 2005 database import into SQL Server 2012?

The absolute best way would be to restore onto 2012 from backups if you have them, but you can also attach the databases from the SAN files onto your 2012 instance.

Once you do that, be sure to run DBCC CHECKDB to make sure all your data is intact.


If you look up the description of error ORA-39324 in the Oracle documentation you will see:

Cause: A dump file was specified for a load operation that had been created using the Data Pump. This type of dump file cannot be processed by external tables using the ORACLE_DATAPUMP access driver.
Action: Access the data in this dump file using the Data Pump.

A dump created by EXPDP can only be read by IMPDP (or dbms_datapump).  Whereas a dump created using writable external tables (using datapump driver) can be re-read only using external table interface (using datapump driver). Expdp does more than dump data (which is the only thing done by writable external tables using datapump driver).

Community Wiki answer originally based on comments by BriteSponge and Raj

Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.


Slow storage writes: how many milliseconds exactly? SQL Server 2008 R2

You can query sys.dm_io_virtual_file_stats to find out the read and write latency.

You can use the script from here or here - this is snip from my project of designing a comprehensive SQL Server health checker (this is something internal for now, but planning to opensource the code soon - as a thank you to sql server community !).

Also, read this blog post by  Erin Stellato - What Virtual Filestats Do, and Do Not, Tell You About I/O Latency. This blog post has a script that you can use as well.


If you are in 80 compatibility mode, you need:

DECLARE @dbid int = DB_ID();

SELECT t.name, i.name, ps.avg_fragmentation_in_percent
  FROM sys.tables AS t         -- why A???
  INNER JOIN sys.indexes AS i  -- why B???
    ON t.[object_id] = i.[object_id]
  CROSS APPLY sys.dm_db_index_physical_stats
    (@dbid,NULL,NULL,0,DEFAULT) AS ps -- why C???
WHERE ps.index_id = i.index_id
AND ps.[object_id] = t.[object_id]
AND i.name IS NOT NULL;

For 100, you need:

SELECT t.name, i.name, ps.avg_fragmentation_in_percent
  FROM sys.tables AS t         -- why A???
  INNER JOIN sys.indexes AS i  -- why B???
    ON t.[object_id] = i.[object_id]
  CROSS APPLY sys.dm_db_index_physical_stats
    (DB_ID(),NULL,NULL,0,DEFAULT) AS ps -- why C???
WHERE ps.index_id = i.index_id
AND ps.[object_id] = t.[object_id]
AND i.name IS NOT NULL;

The function posted in another answer looks nice and convenient, gets the right answer, and it's a clear winner when you measure just estimated cost, but be careful - there are lots of hidden costs in multi-statement TVFs when you actually go ahead and use them (and in this case you can't use an inline TVF for the same reason as the OP is getting an error message with the built-in function). Here is the cost comparison:

(And just to show the cost of individual lookups as well as no filters):

As an aside, please be careful about case (don't upper-case system objects or column names), use sensible aliases, and get in the habit of using statement terminators.


What kind of table structure to use to display product, testing, and test results?

We would need a larger view of what you're doing to be super accurate, but based on your testing example you'll want a basic setup like this:

CREATE TABLE dbo.Products
(
    productCode NVARCHAR(100)
    --Any other columns relevant to describing a product go here
    ,CONSTRAINT PK_Products PRIMARY KEY (productCode)
)
GO
CREATE TABLE dbo.TestTypes
(
    testType NVARCHAR(100)
    ,CONSTRAINT PK_TestTypes PRIMARY KEY (testType)
)
Go
CREATE TABLE dbo.Tests
(
    testID int IDENTITY(1,1)
    ,productCode nvarchar(100)
    ,batchNumber int
    ,testType nvarchar(100)
    ,passed bit
    ,CONSTRAINT PK_Tests PRIMARY KEY (testID)
    ,CONSTRAINT FK_Tests_Products FOREIGN KEY (productCode) REFERENCES dbo.Products (productCode)
    ,CONSTRAINT FK_Tests_TestTypes FOREIGN KEY (testType) REFERENCES dbo.TestTypes (testType)
)

Products will just contain your basic info about a product and I'm assuming your ProductCode is a unique value that can be used to identify a particular product. If that is not true you can slap an IDENTITY column in there to be your primary key.

TestTypes will hold the name of each type of test you want to have a record for, right now it's just a primary keyed name to enforce uniqueness and for use with a FK to constrain your test column's value. You can add any other useful information that may need to be known for a particular test to this table.

Finally, you can record specific Tests for a particular Product and batch into the Tests table. I am somewhat assuming the tests results are a simple pass/fail, if not you can change up the passed column to reflect the results you need to record. So what will happen with this table is that every test for a product gets it's own row with it's result, this allows you to run a simple query on a productCode and a batch to retrieve all the Tests without having extraneous columns that may or may not be filled in.

This also allows you to add new Tests to the system without having to alter your Table structure and if applicable your products don't have empty columns for tests that don't apply to them.


Does my database need to connect to a server?

A schema and a connection are two different things. So, creating a new connection does not mean to create a new schema/database.

You can create a database (or schema) in your server by using MySQL Workbench. After you opened a connection to your server, you will see the SQL IDE, a page with the treeview containing your current schemas, an SQL editor and various other stuff. In the schema tree use the context menu on any node to create the schema:

After that you can create a user with only the necessary rights for that single schema. Use the Administration -> Users and Privileges section for that:

Then assign the user access rights (e.g. for SELECT + DELETE) for that new schema by first allowing access to that new schema:

and finally set the required privileges:

Note: the backslash in the priv list is an escape sequence, because the string there is used as a pattern and the underscore ('_') is used as a placeholder in MySQL. Hence it must be escape to be used literally.


MS SQL running out of Disk Space - can FileGroups be used to free up some space?

While it will work, it's not necessary to move tables to a new filegroup.

If you simply add a file to the database's existing filegroup(s) SQL Server will start using the new file(s).

SQL Server uses a "proportional fill algorithm" when a filegroup has multiple files.  So if you add a new file (perhaps on a different volume) to a filegroup, SQL Server will write new data to that filegroup until it's the same percentage full as the current file.


You need to make SQL join Sale to Customer. There are two ways to approach this:

APPROACH #1

In SQL mode, add the Sale table to the FROM clause

SELECT [Customer].[CUSContact] FROM Customer,Sale
WHERE [Sale].[CUSID] = [Customer].[CUSID];

APPROACH #2

Open the QBE (Query-By-Example) Grid and add the Sale table. The graphical representation of the query should draw a line from the CUSID in the Customer table to the CUSID in the Sale table.

Give it a Try !!!

UPDATE 2014-03-10 14:43 EDT

I just looked at the image again. Perhaps the query should be this instead:

SELECT [Customer].[CUSContact] FROM Customer,Sale
WHERE [Sale].[CUSID] = [Customer].[CUSnum];


Creating a table with parent_id from a flat table

I wrote a php Script that does the job :)

not generic at all and straightforward, but it works

mysql_query('truncate table list');

$cities = array();

$res = mysql_query('select city from temps');

while ($row = mysql_fetch_assoc($res))
    $cities[] = $row['city'];

$counties = array();

$res = mysql_query('select distinct county from temps');

while ($row = mysql_fetch_assoc($res))
    $counties[] = $row['county'];

$states = array();

$res = mysql_query('select distinct state from temps');

while ($row = mysql_fetch_assoc($res))
    $states[] = $row['state'];

//insert germany as root node

mysql_query('insert into list(name,parent_id,type) values("Germany", NULL, "Country")');
$rootid = mysql_insert_id();

foreach ($states as $state) {
    mysql_query("insert into list(name,parent_id, type) values('$state','$rootid','State')");
}

foreach ($counties as $county) {
    //determine state
    $res = mysql_query("select state from temps where county='$county' LIMIT 1");
    $row = mysql_fetch_row($res);
    $state = $row[0];

    $res = mysql_query("select id from list where name='$state' LIMIT 1");
    $row = mysql_fetch_row($res);
    $parent_id = $row[0];

    mysql_query("insert into list(name,parent_id,type) VALUES('$county','$parent_id','County')");
}

foreach ($cities as $city) {
    //determine state
    $res = mysql_query("select county from temps where city='$city' LIMIT 1");
    $row = mysql_fetch_row($res);
    $county = $row[0];

    $res = mysql_query("select id from list where name='$county' LIMIT 1");
    $row = mysql_fetch_row($res);
    $parent_id = $row[0];

    mysql_query("insert into list(name,parent_id,type) VALUES('$city','$parent_id','City')");
}


Enable skip-grant-tables in my.ini and restart MySQL. Now root can login without password so that you can reset your password:

update mysql.user set password=password('newpassword') where user='root';


Since a Knowledge might be a Method, I'm wondering if it would make more sense to store them both in the same table:

KnowledgeAndMethods
--------------------
  ID - unique primary key
  Description
  parent_knowledge - nullable (for methods that are both a Method and a Knowledge, this is the Knowledge that the Method belongs to)
  knowledge_type
  Step
  Step_ID

Based on your example, I think the data would be stored as:

ID Description                  Parent_knowledge  Step      Step ID
-------------------------------------------------------------------
1 "Find Accel. Method"
2 "Find unknown velocity"         1               "Step 1"     S1
3 "Find unknown time"             2               "Step 2"     S2
4 "Apply acceleration formula"    3               "Step 3"     S3
5 "Land rocket ship"

With recursive queries, it could be very easy to get data back from this kind of table.

You could have a KnowledgeSubtype or MethodSubtype column if you want, but I'm not sure if you would gain anything.

Based on comments, it sounds like a many-to-many relationship is wanted. So here's another try at it:

KnowledgeAndMethods
--------------------
  ID - unique primary key
  Description
  parent_knowledge - nullable (for methods that are both a Method and a Knowledge, this is the Knowledge that the Method belongs to)
  knowledge_type

Methods_Steps
  Parent_Knowledge_ID - the ID of the parent Knowledge
  Step
  Step_ID - The ID local within a set of Steps
  Method_ID  - The ID of the Method that is a Knowledge

So now the previous example might look like:

KnowledgeAndMethods:

ID Description
--------------------------------
1 "Find Accel. Method"
2 "Find unknown velocity"
3 "Find unknown time"
4 "Apply acceleration formula"

Methods_Steps:
Knowledge_ID  Step      Step_ID    Method_ID
--------------------------------------------
1             "Step 1"  1          2
1             "Step 2"  2          3
1             "Step 3"  3          4
5             "Step 9"  6          2   <---- Here's an example of "Find Unknown Velocity" being a different "Step" in a diffent "Knowledge"


upload data from on-premises folder to Azure DB as the server admin is SQL authenticated user

You can use Azure SSIS integration with Azure Data Factory as explained here. On this resource you will find how to run packages that use Windows Authentication to connect to on-premises resources like a folder.

Hope this helps.


Is there a way to limit the number of foreign key references for a single table index

I am not sure if this is a very clean solution, but it might work.

Suppose that you have a Leader table and a Member table. Create a third table, LeaderMember. This should have three columns, LeaderID, MemberID, and MemberNumber. LeaderID and MemberID are both foreign keys to the respective tables.

CREATE TYPE for the MemberNumber column, restricting the integer values that it can take, for example from 1 to 8.

Now impose two constraints on the table. First a UNIQUE constraint on MemberID, which ensures that a member can only have one leader. Then a UNIQUE constraint on the combination of columns (LeaderID, MemberNumber). This will mean that each leader can only have as many members as there are distinct values possible for MemberNumber.

I can see a couple of drawbacks with this - first of all inserting a new Member is now a little tricky, as you have to find a value of MemberNumber which has not been used. Bear in mind you have to make a check at this point anyway - to see if the Leader in question already has enough members. Secondly the type for the MemberNumber might suggest that there is some difference between the members which have the different values - that the numbers are meaningful.

However, I think it is a reasonably clean and normalized design.


Two dichotomy views of a table, and means to express this as a stored procudure?

There is a very good reason to create two different views rather than to create a single view and query against the archive flag: indexes.

If you have two views, and the definition of each includes the criteria for determining whether archive is true or false, and the queries are properly written to take advantage of indexes, then they will perform better than if you evaluate whether each row is archived or not, because in the latter case, the server will be forced to evaluate the archive condition for each row and then filter those rows out of the final result based on the WHERE clause outside the view... because the optimizer cannot "see through" the view when you're deriving values.

For a relatively understated example, I have a history table with 151,534 records and an index on event_date.  I want to find records from last June.

mysql> CREATE VIEW get_history_window AS SELECT id, amount, event_date,
       IF(event_date BETWEEN '2012-06-01 00:00:00' AND '2012-06-30 23:59:59',1,0) AS is_in_june
       FROM history;
Query OK, 0 rows affected (0.04 sec)

mysql> EXPLAIN SELECT * FROM get_history_window WHERE is_in_june = 1;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | history | ALL  | NULL          | NULL | NULL    | NULL | 151534 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Oops.  The server has to do a full table scan, and is going to read each of the 150,000+ rows of the table, in order to evaluate the IF() expression so it can know which rows properly satisfy my WHERE.

The optimizer cannot reverse-engineer my query to an extent that it will realize "oh, you just want the rows with event date within a specific range, so I'll just use the index for this."  It has to do the comparison on each row.

However... if my view is more properly defined for the intended purpose:

mysql> CREATE VIEW get_last_june_events AS SELECT id, amount, event_date
       FROM history WHERE event_date BETWEEN '2012-06-01 00:00:00' AND '2012-06-30 23:59:59';
Query OK, 0 rows affected (0.03 sec)

mysql> EXPLAIN SELECT * FROM get_last_june_events;
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | history | range | event_date    | event_date | 4       | NULL | 1830 | Using where |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

Now, thanks to the fact that the index can be used, only 1830 rows are examined, cutting my actual query time dramatically.

Of course, the tables need good indexes and the queries inside the views need to be optimized to take advantage of them... you can easily get this wrong.

If, for example, my view were defined with WHERE YEAR(event_date) = 2012 AND MONTH(event_date) = 6 instead of the way I illustrated above, WHERE event_date BETWEEN '2012-06-01 00:00:00' AND '2012-06-30 23:59:59' then I am right back where I started:

+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | history | ALL  | NULL          | NULL | NULL    | NULL | 151534 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+

The reason for this, of course, is that when you use functions in a WHERE clause, this almost always prevents the optimizer from being able to use indexes on columns used as arguments to the functions.  In the first wrong example, event_date was used as an argument to IF() and here it's used as an argument to YEAR() and MONTH(), so these queries are not optimal, but the one in the middle is.

Assume you wanted to find records more than 6 months old:  By now, hopefully you will see that one of these is right and one of these is wrong:

WHERE DATE_ADD(event_date,INTERVAL 6 MONTH) < NOW();  -- wrong

WHERE event_date < DATE_SUB(NOW(), INTERVAL 6 MONTH); -- right

The expressions are logically equivalent and will produce the same result, but the first one is "wrong" in the sense that uses an indexed column as an argument to a function, preventing the index from being usable, while the second one uses a function to calculate a constant against which the indexed values of event_date can be compared.


How to avoid using Merge query when upserting multiple data using xml parameter?

Whether the source is XML or a TVP does not make a huge difference. The overall operation is essentially:

UPDATE existing rows
INSERT missing rows

You do it in that order because if you INSERT first, then all rows exist to get the UPDATE and you will do repeated work for any rows that were just inserted.

Beyond that there are different ways to accomplish this and various ways to tweak some additional efficiency out of it.

Let's start with the bare minimum. Since extracting the XML is likely to be one of the more expensive parts of this operation (if not the most expensive), we don't want to have to do that twice (as we have two operations to perform). So, we create a temp table and extract the data out of the XML into it:

CREATE TABLE #TempImport
(
  Field1 DataType1,
  Field2 DataType2,
  ...
);

INSERT INTO #TempImport (Field1, Field2, ...)
  SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
         tab.col.value('XQueryForField2', 'DataType') AS [Field2],
         ...
  FROM   @XmlInputParam.nodes('XQuery') tab(col);

From there we do the UPDATE and then the INSERT:

UPDATE tab
SET    tab.Field1 = tmp.Field1,
       tab.Field2 = tmp.Field2,
       ...
FROM   [SchemaName].[TableName] tab
INNER JOIN #TempImport tmp
        ON tmp.IDField = tab.IDField
        ... -- more fields if PK or alternate key is composite

INSERT INTO [SchemaName].[TableName]
  (Field1, Field2, ...)
  SELECT tmp.Field1, tmp.Field2, ...
  FROM   #TempImport tmp
  WHERE  NOT EXISTS (
                       SELECT  *
                       FROM    [SchemaName].[TableName] tab
                       WHERE   tab.IDField = tmp.IDField
                       ... -- more fields if PK or alternate key is composite
                     );

Now that we have the basic operation down, we can do a few things to optimize:

capture @@ROWCOUNT of insert into temp table and compare to @@ROWCOUNT of the UPDATE. If they are the same then we can skip the INSERT
capture the ID values updated via the OUTPUT clause and DELETE those from the temp table. Then the INSERT doesn't need the WHERE NOT EXISTS(...)
IF there are any rows in the incoming data that should not be synced (i.e. neither inserted nor updated), then those records should be removed prior to doing the UPDATE

CREATE TABLE #TempImport
(
  Field1 DataType1,
  Field2 DataType2,
  ...
);

DECLARE @ImportRows INT;
DECLARE @UpdatedIDs TABLE ([IDField] INT NOT NULL);

BEGIN TRY

  INSERT INTO #TempImport (Field1, Field2, ...)
    SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
           tab.col.value('XQueryForField2', 'DataType') AS [Field2],
           ...
    FROM   @XmlInputParam.nodes('XQuery') tab(col);

  SET @ImportRows = @@ROWCOUNT;

  IF (@ImportRows = 0)
  BEGIN
    RAISERROR('Seriously?', 16, 1); -- no rows to import
  END;

  -- optional: test to see if it helps or hurts
  -- ALTER TABLE #TempImport
  --   ADD CONSTRAINT [PK_#TempImport]
  --   PRIMARY KEY CLUSTERED (PKField ASC)
  --   WITH FILLFACTOR = 100;

  -- optional: remove any records that should not be synced
  DELETE tmp
  FROM   #TempImport tmp
  INNER JOIN [SchemaName].[TableName] tab
          ON tab.IDField = tmp.IDField
          ... -- more fields if PK or alternate key is composite
  WHERE  tmp.ModifiedDate < tab.ModifiedDate;

  BEGIN TRAN;

  UPDATE tab
  SET    tab.Field1 = tmp.Field1,
         tab.Field2 = tmp.Field2,
         ...
  OUTPUT INSERTED.IDField
  INTO   @UpdatedIDs ([IDField]) -- capture IDs that are updated
  FROM   [SchemaName].[TableName] tab
  INNER JOIN #TempImport tmp
          ON tmp.IDField = tab.IDField
          ... -- more fields if PK or alternate key is composite

  IF (@@ROWCOUNT < @ImportRows) -- if all rows were updates then skip, else insert remaining
  BEGIN
    -- get rid of rows that were updates, leaving only the ones to insert
    DELETE tmp
    FROM   #TempImport tmp
    INNER JOIN @UpdatedIDs del
            ON del.[IDField] = tmp.[IDField];

    -- OR, rather than the DELETE, maybe add a column to #TempImport for:
    -- [IsUpdate] BIT NOT NULL DEFAULT (0)
    -- Then UPDATE #TempImport SET [IsUpdate] = 1 JOIN @UpdatedIDs ON [IDField]
    -- Then, in below INSERT, add:  WHERE [IsUpdate] = 0

    INSERT INTO [SchemaName].[TableName]
      (Field1, Field2, ...)
      SELECT tmp.Field1, tmp.Field2, ...
      FROM   #TempImport tmp
  END;

  COMMIT TRAN;

END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK;
  END;

  -- THROW; -- if using SQL 2012 or newer, use this and remove the following 3 lines
  DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage, 16, 1);
  RETURN;
END CATCH;

I have used this model several times on Imports / ETLs that either have well over 1000 rows or maybe 500 in a batch out of a total set of 20k - over a million rows. However, I have not tested the performance difference between the DELETE of the updated rows out of the temp table vs just updating the [IsUpdate] field.

Please note regarding the decision to use XML over TVP due to there being, at most, 1000 rows to import at a time (mentioned in the question):

If this is being called a few times here and there, then quite possibly the minor performance gain in TVP might not be worth the additional maintenance cost (needing to drop the proc before changing the User-Defined Table Type, app code changes, etc). But if you are importing 4 million rows, sending 1000 at a time, that is 4000 executions (and 4 million rows of XML to parse no matter how it is broken up), and even a minor performance difference when executed only a few times will add up to a noticeable difference.

That being said, the method as I have described does not change outside of replacing the SELECT FROM @XmlInputParam to be SELECT FROM @TVP. Since TVPs are read-only, you wouldn't be able to delete from them. I guess you could simply add a WHERE NOT EXISTS(SELECT * FROM @UpdateIDs ids WHERE ids.IDField = tmp.IDField) to that final SELECT (tied to the INSERT) instead of the simple WHERE IsUpdate = 0. If you were to use the @UpdateIDs table variable in this manner, then you could even get away with not dumping the incoming rows into the temp table.


What does "slot 80 for LOB data type node does not exist" error in sql server mean?

While normal columns are stored in the actual row record (the structure on disk), LOB columns are stored on a different page, leaving a pointer to that page on the original page.

When you scan a table, all the data pages will be scanned, including the pointers to the LOB pages. If the timing is right, and you're running under READ UNCOMMITTED, you might scan the data page, but when the page containing the LOB data is scanned (by following the pointer in the referring record), it might have been changed/deleted in the meantime.

It could also be caused by corruption, in which case you should be able to reproduce the error again and again. In this case, run DBCC CHECKDB to check for corruption issues. If you can't reproduce it but it happens too often, see if you can't change the isolation level.


How to force SQL server to use row locking for specific update/delete statements

I know you said with (rowlock) did not appear to be working
Are you including a <> for the set as then it will avoid an update lock

update table with (rowlock)
set col1 = 1
where col1 <> 1

with (tablock holdlock) might fix deadlock
as then you only have one update at a time
it is worth a try

Are your transactions updating tables in the same order?


sp_setapprole and creating users / logins

to do it correctly is not very simple, although once setup, your done.

Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation.

Any way to give these server scope permissions by means of impersonating will fail by default.This is by design.

You have 2 options:

Tell SQL Server that you as a DBA trust any user in that database to have privilegde escalation to server wide permission if impersonation is used. You acomplish this by setting the option TRUSTWORTHY to ON
However if you don't fully understand what this option does first read:
SQL Server 2012 Best Practice Whitepaper (Specifically page 18 and 19 about "Database Ownership and Trust")
Use signed stored procedures to accomplish your task.

I'll first give you the example of option 1:

CREATE LOGIN [appRoleProxy] WITH PASSWORD ='1234abcd!@'
GO

EXEC sp_addsrvrolemember    @loginame='appRoleProxy',
                            @rolename='securityadmin'

GO

CREATE DATABASE [Stefaan]
go

ALTER DATABASE [Stefaan] SET TRUSTWORTHY ON
GO

USE [Stefaan]
GO

--add the proxy account to the dbo role since your stored procedure will be running in the context of this account and is also accessing your user database.
ALTER ROLE [db_owner] ADD MEMBER [appRoleProxy]
GO

CREATE APPLICATION ROLE appRole WITH PASSWORD ='Passw0rd!';
GO

CREATE PROCEDURE dbo.usp_testproc
@LoginName nvarchar(128),
@Password nvarchar(128),
@DBName nvarchar(128)
WITH EXECUTE AS 'appRoleProxy' --Impersonate a SQL server login with the proper server level permissions
AS
DECLARE @SQL varchar(max)
BEGIN
    IF NOT EXISTS ( SELECT  name
                    FROM    sys.server_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
                + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
                + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
            EXECUTE(@SQL);
        END

    IF NOT EXISTS ( SELECT  name
                    FROM    sys.database_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
                + @LoginName + ']';
            EXECUTE(@SQL);
        END

    IF EXISTS ( SELECT  name
                FROM    sys.server_principals
                WHERE   name = @LoginName )
        BEGIN
            EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
        END
END
GO

GRANT EXECUTE ON [dbo].[usp_testproc] TO [appRole]
GO

sp_setapprole   @rolename='appRole',
                @password='Passw0rd!'

EXEC usp_testproc 'testlogin','password23@','stefaan'

Now for an example of option 2 I'd like to point out a answer a gave on another question:
Msg 4834 “You do not have permission to use the bulk load statement”


Percent of total: Is there a more efficient way than using a subselect?

with data as
(
  select 'TRANSPORTATION' as asset_type, 2000000000 as cost from dual union all
  select 'ACTIVE TRANSPORTATION' as asset_type, 150000000 as cost from dual union all
  select 'STORMWATER MANAGEMENT' as asset_type, 500000000 as cost from dual
  )
select
  data.asset_type, data.cost,
  trunc(ratio_to_report(data.cost) over () * 100, 1) as percent_of_total
from data;

ASSET_TYPE                  COST PERCENT_OF_TOTAL
--------------------- ---------- ----------------
TRANSPORTATION        2000000000             75.4
ACTIVE TRANSPORTATION  150000000              5.6
STORMWATER MANAGEMENT  500000000             18.8

RATIO_TO_REPORT still works with your updated data:

with data as
(
  select 'TRANSPORTATION' as asset_type, 1000000000 as cost from dual union all
  select 'TRANSPORTATION' as asset_type, 500000000 as cost from dual union all
  select 'TRANSPORTATION' as asset_type, 500000000 as cost from dual union all
  select 'ACTIVE TRANSPORTATION' as asset_type, 100000000 as cost from dual union all
  select 'ACTIVE TRANSPORTATION' as asset_type, 50000000 as cost from dual union all
  select 'STORMWATER MANAGEMENT' as asset_type, 500000000 as cost from dual
  )
select
  data.asset_type, sum(data.cost),
  trunc(ratio_to_report(sum(data.cost)) over () * 100, 1) as percent_of_total
from data
  group by data.asset_type
;

ASSET_TYPE            SUM(DATA.COST) PERCENT_OF_TOTAL
--------------------- -------------- ----------------
TRANSPORTATION            2000000000             75.4
ACTIVE TRANSPORTATION      150000000              5.6
STORMWATER MANAGEMENT      500000000             18.8