Should I use a composite or single-column index? I have the following columns in my database table (Medicines).

ID bigint,
MedicineName nvarchar(50),
BrandName nvarchar(50),
MedicineCode nvarchar(20),
and price,quantity.

I am making a stored procedure with this query:

create proc searchmedicine
@name nvarchar(50)=null,@brand nvarchar(50)=null, @code nvarchar(20)=null
as
select * from Medicines
where MedicineName= Case WHEN @name IS NOT NULL THEN @name ELSE MedicineName END
AND BrandName=Case WHEN @brand IS NOT NULL THEN @brand ELSE BrandName END
AND MedicineCode=Case WHEN @code IS NOT NULL THEN @code ELSE MedicineCode END

Now I am confused which non-clustered index is more suitable to help optimize the query: composite or single column?

Using SELECT * is bad practice especially in a stored procedure.  Even though you have a WHERE clause to filter the rows returned, I would explicitly state the columns.

As for indexes you will probably have to do the tuning yourself by looking at the execution plan for each type of index applied.  However I would have a clustered index on ID and 1 non clustered index on MedicineName, BrnadName, MedicineCode since it is them 3 columns you are selecting from.

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineBrands ON Medicines (MedicineName, BrandName, MedicineCode)

I'd then personally include the execution plan and see how your stored procedure performs compared to having the following indexes:

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineCode ON Medicines (MedicineCode)

CREATE NONCLUSTERED INDEX IX_Medicines_MedicineName ON Medicines (MedicineName)

CREATE NONCLUSTERED INDEX IX_Medicines_BrandName ON Medicines (BrandName)


Traditional failover clustering vs. Availability Groups I'm looking into Availability Groups to see if i can benefit from them, but I struggle to understand if I can or not.

Today, we are running a traditional failover cluster with around 150 databases. It is a collection of a lot of different stuff that is in a lot of cases hardly used, but we can't throw it away for a million different reasons. But prolonged downtime is unacceptable. The environment looks like this:

No single database is business critical (but the amount of DBs makes it critical)
Databases ranging from 10 MB to 10 GB
Avg. 30.000 Logical read/s, under 1 Physical read/s
Low CPU usage (below 20%)
Two physical servers in two data centers
Storage is on two VMAX 10k, one in each center, shared volumes distributed with VPLEX, so looing one center will not affect uptime on storage
No need for reporting or anything out of the ordinary
Most DBs are in simple mode, with a nightly full backup, business have no problem accepting the nightly backup as restore point
A rare few databases are running full mode and has 30 min transaction backups

The reason this solution was chosen at the time was licensing and administration reasons. We wanted to cut back on all the single SQL Servers doing nothing but cost us money. And we have removed about 60 SQL Server installations from the environment.

But now there is talk about how we should deal with this in the future, hardware replacement coming up in a year.

And the problem is, as I stated before, I can't quite get my head around if we can benefit from a change, maybe running Availability Groups on virtual machines instead?

So my question is:

What would you think is the best solution to look at given these factors?

What would you think is the best solution to look at given these factors?

AlwaysON is designed to meet business uptime and availability wherein you can afford minimal data loss and downtime. It requires proper planning and implementation.

As per your question, it seems that the databases are not that business critical (business have no problem accepting the nightly backup as restore point).

Based on the information that you provided, your business will be able to survive with Log Shipping as a DR solution.

The benefit is that you dont have to worry about windows cluster and other complexities associated with AlwaysON like DNS, Windows failover cluster, etc. Check the prereq and requirements.

Logshipping is well tested and is the most mature of all the DR technologies that you get from SQL Server. You can adjust the frequency of Transaction log backup,copy and restore as per your needs.


database design for dynamic menu bar I am trying to create a menu bar which will load dynamically on user log in, based on the user roles. Say if the user is an admin he can see all the items listed under the header and if a user is a guest he can see only few items e.g. Item 1.1, Item 1.2, Item 2.2, etc. The menu bar will be something like,

Header
  |_Sub-header_1
  |       |_Item_1.1
  |       |_Item_1.2
  |       |_Item_1.3
  |_Sub-header_2
  |       |_Item_2.1
  |       |_Item_2.2
  |       |_Item_2.3
  |       |_Item_2.4
  |_Sub-header_3
  |       |_Item_3.1
  |       |_Item_3.2
  |       |
  |

So there will be header and there will be 7 sub-headers, in sub-headers there could be 10-20 items. I want a database where I can store these items, sub-headers and header.

1) Please try to make me understand how to design a database for such scenarios?
2) Also I want to create a url out of it, so how do I do that?

If you were using SQL Server, what you are looking for is something like:

USE tempdb;
CREATE TABLE MenuHeaders
(
    MenuHeaderID INT NOT NULL CONSTRAINT PK_MenuHeader
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , MenuText VARCHAR(255)
);
CREATE TABLE MenuSubHeaders
(
    MenuSubHeaderID INT NOT NULL CONSTRAINT PK_MenuSubHeader
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , MenuHeaderID INT NOT NULL CONSTRAINT FK_MenuSubHeader_MenuHeaderID
        FOREIGN KEY REFERENCES MenuHeaders(MenuHeaderID)
    , SubMenuText VARCHAR(255)
);
CREATE TABLE MenuItems
(
    MenuItemID INT NOT NULL CONSTRAINT PK_MenuItems
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , MenuSubHeaderID INT NOT NULL CONSTRAINT FK_MenuItems_MenuSubHeaderID
        FOREIGN KEY REFERENCES MenuSubHeaders(MenuSubHeaderID)
    , MenuItemText VARCHAR(255)
    , MenuURL VARCHAR(2048)
);
CREATE TABLE Users
(
    UserID INT NOT NULL CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , UserName VARCHAR(255)
);
CREATE TABLE UserGroups
(
    UserGroupID INT NOT NULL CONSTRAINT PK_UserGroups
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , UserGroupName VARCHAR(255)
);
CREATE TABLE UsersGroupsXRef
(
    UserID INT NOT NULL CONSTRAINT FK_UserGroupsXRef_UserID
        FOREIGN KEY REFERENCES Users(UserID)
    , UserGroupID INT NOT NULL CONSTRAINT FK_UserGroupsXRef_UserGroupID
        FOREIGN KEY REFERENCES UserGroups(UserGroupID)
    CONSTRAINT PK_UsersGroupsXRef PRIMARY KEY CLUSTERED
);
CREATE TABLE UserGroupsMenusXRef
(
    UserGroupID INT NOT NULL CONSTRAINT FK_UserGroupsMenusXRef_UserGroupID
        FOREIGN KEY REFERENCES UserGroups(UserGroupID)
    , MenuHeaderID INT NOT NULL CONSTRAINT FK_UserGroupsMenusXRef_MenuHeaderID
        FOREIGN KEY REFERENCES MenuHeaders(MenuHeaderID)
);

This allows you to have Menus linked to Sub-Menus, with Menu-Items linked to Sub-Menus.

Users can be members of UserGroups, and UserGroups have associated Menus.

MenuItems have a URL field.


What is the best way to include SQL Server Agent jobs in Source Control? I found that if I script a SQL Server Agent job as 'create to' that the script starts with use msdb- so I assume that jobs are stored in the msdb database?

What is the best way to add SQL Server jobs to a Visual Studio database project? I added the msdb database, to the database solution, but that doesn't seem to have any reference to jobs.

I would like the jobs to be deployed/updated along with a database... it seems like this is not possible?

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


I need to make a structure for a mysql database I have a table that saves users:
Email || Username || age || password
and I need to save the series they have seen and the ones they are watching and as I have read it is not correct to insert two columns with vectors that save the series, how can I do it?

Build another table with 3 columns

the primary key of users (perhaps email?).
what they are watching,
when.

Each user would have any number (0 or more) rows in this table.

Think this way:  When you need a "vector", make a new table.


How to create database level counter (identify field)? I recall that SQL Server (maybe version 2014 but it could be 2016) added the ability to create database level counters (identity field?).  Sort of like an IDENTITY function at the database level.  I don't recall what they called these counters so am having trouble finding them in the help.

What are these counters called and how do I create one?

Note that I am not talking about creating a table with an identity column and rolling a scalar valued function to return it.  I am referring to a build-in feature but I can't recall what it is named.

Sounds like Sequences, brought in with SQL 2012

Info:
https://msdn.microsoft.com/en-us/library/ff878058.aspx
Create Sequence syntax:
https://msdn.microsoft.com/en-us/library/ff878091.aspx
Nice SimpleTalk article on them:
https://www.simple-talk.com/sql/learn-sql-server/sql-server-sequence-basics/


Hard Drive Configuration for my setup I have a fairly large transactional database (100GB) with a lot of users.  The database access is solely via stored procs that make heavy use of temp tables, table variables, cursors and other fun things.

I'm trying it out on a new box in an effort to boost the performance.  The new box has 5 separate physical hard drives.  I am struggling trying to come up with an optimal setup (mostly though ignorance, I am a c# dev normally).  So far, I got the following:

Drive C: OS, SQL install, TempDB log
Drive D: Database data
Drive E: Database log
Drive F: TempDB data
Drive G: Databases indexes

Am I going about the right way?  Any glaring mistakes?

If there is room for more drives, order more drives. If there isn't room for more drives, order more drives... and an external enclosure.

I'm assuming this data has at least some value to the business and from your comment we can also infer that you're experiencing performance issues already. So, zero redundancy and single spindle performance isn't going to cut it.

If you absolutely have no other choice than to stick with these 5 drives, I'd probably go for a 4 disk RAID10 with a hot spare. Possibly, maybe, all 5 in RAID5. Separating tempdb and indexes at this sort of scale is pointless.


Why is my planning time consistently slower than execution time? On small tables with 5,000 rows I get:

SELECT category_id FROM widgets WHERE category_id = 1;
Index Only Scan using widgets_category_id on widgets  (cost=0.28..5.20 rows=26 width=8) (actual time=0.083..0.160 rows=70 loops=1)
Index Cond: (category_id = 1)
Heap Fetches: 7
Planning time: 2.758 ms
Execution time: 0.213 ms

and on large tables with 100m rows I get:

SELECT customer_id FROM orders WHERE customer_id = 1;
Index Only Scan using orders_customer_id on orders  (cost=0.58..1372.32 rows=65243 width=8) (actual time=0.155..0.402 rows=158 loops=1)
Index Cond: (customer_id = 1)
Heap Fetches: 109
Planning time: 16.019 ms
Execution time: 0.493 ms

This seems to be a consistent theme when running a bunch of different queries on different tables.

I'm using version 10.4, here are some settings:

shared_buffers is 64GB (25% of total RAM)
default_statistics_target = 100
constraint_exclusion = partition

The only time I see planning times that slow for such simple queries is the first time a table is used in a given connection.  Then it has to read the metadata about the table and its indexes and statistics, perhaps from disk.

Is that is actually a problem?  If so, you could use a connection pooler so you re-use the connections with the metadata already in memory.


Reason of poor performance of ANSI joins in Oracle 9i Please before flagging as duplicate, read the last paragraphs.

In an Oracle 9i database this query runs in 0.18 seconds:

select
    count(*)
from
    dba_synonyms s,dba_objects t
where
    s.TABLE_OWNER = t.OWNER(+) and
    s.TABLE_NAME = t.OBJECT_NAME(+) and
    s.DB_LINK is null and
    t.OWNER is null;

...but this one runs in an appalling 120 seconds!:

select
    count(*)
from
    dba_synonyms s left join dba_objects t
                   on ( s.TABLE_OWNER = t.OWNER and s.TABLE_NAME = t.OBJECT_NAME )
where
    s.DB_LINK is null and
    t.OWNER is null

Notice the only difference is using propietary Oracle join syntax vs ANSI join syntax.

This question is not a duplicate of this one because that other question is about a very complex query involving more than 9 tables, and the only answer points out that the queries are very different besides the syntax usage (mainly the order of the tables).

In my case it is a extremelly simple query, a mere join between two relations with no mayor complications of differences, including the order of tables.

Is this a bug in Oracle 9i?
What is the cause of such dramatic    difference in performance?

Well, after a long time with no answers. I've done some testing.

I ran the same query in 10g and 11g and both versions, the one with ANSI joins and the ones with the WHERE joins run in less than 1 second.

As problems exists only in 9i, the same version the support for ANSI joins where introduced I assume it's a bug in 9i that could or could have not be solved in a patch.

Gladly as I mentioned, from 10g onwards both flavors of joins perform well.


Assistance with part of a join I'm a college student and the database i'm working with is purely fictional but part of it requires me to make a query that is a join.

What I have are 3 tables each with part of the data but also needing to use two of those tables as conditions based off the main table. What I mean is I have an employee table, order table and customer table and the only thing any two of them have in common is the ID of either the employee or the customer is part of the order table. Now what i am trying to do is create a join statement that will get certain information from the employee and customer tables and only those that both the employee and the customer are also on the same line in the order table.

How should i make this type of conditional statement? Any example using the same basic scenario will work I can use that to help me build my own query.

This is what i have right now:

SELECT [Customer/Vendor_Info_local].Name_of_customer,
       [Customer/Vendor_Info_local].[Customer/VendorID],
       Employee_Info_local.Employee_Name,
       Employee_Info_local.EmployeeID
FROM Employee_Info_local
RIGHT JOIN ([Customer/Vendor_Info_local]
RIGHT JOIN Order_Information_local
   ON [Customer/Vendor_Info_local].[Customer/VendorID] = Order_Information_local.[Cusrtomer/VendorID])
   ON Employee_Info_local.EmployeeID = Order_Information_local.EmployeeID;

SELECT C.Name_of_customer,
       C.[Customer/VendorID],
       E.Employee_Name,
       E.EmployeeID
from Order_Information_local as O
JOIN Employee_Info_local as E
JOIN [Customer/Vendor_Info_local] as C
       ON C.[Customer/VendorID] = O.[Cusrtomer/VendorID])
      AND E.EmployeeID = O.EmployeeID;


SQL Web Edition Download location I cannot find the download location for SQL Web Edition.

I can find the Standard / Developer / Express editions, but not the Web edition.

I have tried https://www.microsoft.com/en-us/sql-server/sql-server-downloads,
I have followed all the links on the page, and found various informational resources of SQL Web edition, but not the actual file.

Where/how can I download SQL Web Edition

Web Edition is not available to download at will.

See here.  Scroll half way down to 'SQL Server Price' section.


An alert for Log backup which is less than 2 hours I am looking for a script which will send me an email with the below condition:

Send email when there is no log backup for the last 2 hours.

I actually have generated this output using the below script, but I need my output to have a last full backup date in it.

The script:

DECLARE
  @dblist NVARCHAR(MAX) = N'',
  @c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
  @s SYSNAME = @@SERVERNAME;

SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
  + COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
  + @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
  SELECT
   bs.database_name AS db,
   MAX(bs.backup_finish_date) AS d,
   DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age
-------------^^^^ please don't use lazy shorthand like hh
  FROM    msdb.dbo.backupset AS bs
  WHERE [type] = 'L'
  GROUP BY database_name
  HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -2, GETDATE()))

UNION ALL -- why UNION? By definition there are no duplicates to filter.
          -- in fact you could re-write this without two separate queries at all.

  SELECT
    name AS db,
    NULL AS d,
    9999 AS age
  FROM
    master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
  WHERE name <> N'tempdb' AND NOT EXISTS
  (
    SELECT 1 FROM msdb.dbo.backupset
      WHERE database_name = d.name
  )
) AS x
ORDER BY db;

IF @dblist > N''
BEGIN
  PRINT @dblist;
  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'profile',
    @recipients = 'ABC@ABC.com',
    @subject = N'Databases not backed for last 2 hrs',
    @body = @dblist;
END

The below script will create a SQL Server Agent Job that runs hourly.  The job reports, via database mail, whenever it detects a database that hasn't had a log backup in the past 2 hours.  Since log backups don't apply to databases in the simple recovery model, those databases are excluded.  Also, the model database is excluded - you may want to remove that exclusion if you make frequent modifications to the model database (why would you, though).

The job is schedule to run starting at 6:00 am each day, and stop running at 10:00 pm.  You'll need to modify the @recipients and @profile_name variables in the first couple of lines of the script.

The job is set to notify the SQL Server Agent Operator named 'DBA', so you'll need to set that up in advance.  You can use dbo.sp_add_operator to add an operator via T-SQL.

USE [msdb]
GO
DECLARE @recipients varchar(max) = '<your_email_here>';
DECLARE @profile_name sysname = N'<database_mail_profile_here>';

DECLARE @cmd nvarchar(max) = N'DECLARE @msg nvarchar(max);
SET @msg = N'''';
;WITH src AS
(
SELECT bs.database_name
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.type = ''L'' --only log backups
    AND bs.backup_finish_date > DATEADD(MINUTE, -120, GETDATE())
)
, last_backup AS (
SELECT bs.database_name
    , bs.backup_start_date
    , rn = ROW_NUMBER() OVER (PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC)
FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
WHERE bs.type = ''L'' --only log backups
    AND bs.backup_finish_date <= DATEADD(MINUTE, -120, GETDATE())
)
SELECT @msg = @msg + CASE WHEN @msg = N'''' THEN N'''' ELSE N''

'' END
    + d.name + CASE WHEN lb.backup_start_date IS NOT NULL AND lb.backup_start_date <> N'''' THEN N'' (last log backup: '' + CONVERT(nvarchar(30), lb.backup_start_date, 120) + N'')'' ELSE N'''' END
FROM sys.databases d
    LEFT JOIN last_backup lb ON d.name = lb.database_name AND lb.rn = 1
WHERE d.state_desc = N''ONLINE''
    AND d.recovery_model_desc <> N''SIMPLE'' --simple recovery affords no log backups
    AND d.name <> CONVERT(sysname, N''model'')
    AND NOT EXISTS (
        SELECT 1
        FROM src
        WHERE src.database_name = d.name
        )
ORDER BY d.name;

IF @msg <> N''''
BEGIN
    SET @msg = N''

The following databases haven''''t had a log backup during the last 2 hour period:

'' + @msg + N''

    '';
    DECLARE @subject nvarchar(1000);
    SET @subject = ''Log Backup Notification from SQL Server: '' + @@SERVERNAME;

    EXEC msdb.dbo.sp_send_dbmail
          @profile_name = ''' + @profile_name + N'''
        , @recipients = ''' + @recipients + N'''
        , @subject = @subject
        , @body = @msg
        , @body_format = ''HTML'';
END
';

DECLARE @ReturnCode int;
SELECT @ReturnCode = 0;

BEGIN TRANSACTION;

BEGIN TRY
    IF NOT EXISTS (
        SELECT sc.name
        FROM msdb.dbo.syscategories sc
        WHERE sc.name = N'Log Notifications' AND category_class=1
        )
    BEGIN
        EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
            , @type = N'LOCAL'
            , @name = N'Log Notifications'
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred adding the Job Category', 1;
    END

    DECLARE @jobId binary(16);

    IF EXISTS (
        SELECT 1
        FROM msdb.dbo.sysjobs sj
        WHERE sj.name = CONVERT(sysname, N'Log Backup Notifier')
        )
    BEGIN
        EXEC msdb.dbo.sp_delete_job @job_name = N'Log Backup Notifier'
            , @delete_history = 0
            , @delete_unused_schedule = 0;
    END

    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Log Backup Notifier'
        , @enabled = 1
        , @notify_level_eventlog = 3
        , @notify_level_email = 2
        , @notify_level_netsend = 0
        , @notify_level_page = 0
        , @delete_level = 0
        , @description = N'No description available.'
        , @category_name = N'Log Notifications'
        , @owner_login_name = N'sa'
        , @notify_email_operator_name = N'DBA'
        , @job_id = @jobId OUTPUT;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred adding the Job', 1;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
        , @step_name=N'Email DBAs if no log backup has occurred in 2 hours'
        , @step_id = 1
        , @cmdexec_success_code = 0
        , @on_success_action = 1
        , @on_success_step_id = 0
        , @on_fail_action = 2
        , @on_fail_step_id = 0
        , @retry_attempts = 0
        , @retry_interval = 0
        , @os_run_priority=0, @subsystem = N'TSQL'
        , @command = @cmd
        , @database_name = N'msdb'
        , @flags = 0;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurrred adding the Job Step', 1;

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
        , @start_step_id = 1;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred setting the job starting step.', 1;

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

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) THROW 50000, N'An error occurred setting the job server to (local)', 1;

    IF EXISTS (
        SELECT 1
        FROM msdb.dbo.sysschedules ss
        WHERE ss.name = N'Log Notification Schedule'
        )
    BEGIN
        EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'Log Notification Schedule';
    END
    DECLARE @start_date int;
    SET @start_date = CONVERT(int, CONVERT(varchar(8), GETDATE(), 112));
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
        , @name = N'Log Notification Schedule'
        , @enabled = 1
        , @freq_type = 4
        , @freq_interval = 1
        , @freq_subday_type = 8
        , @freq_subday_interval = 1
        , @freq_relative_interval = 0
        , @freq_recurrence_factor = 0
        , @active_start_date = @start_date
        , @active_end_date = 99991231
        , @active_start_time = 60000
        , @active_end_time = 215959;

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
        ROLLBACK TRANSACTION;
        THROW
    END
END CATCH
GO


MySQL Replication using GTID I want to create MySQL replication using GTID.

According to MySQL Manual I need the following settings in my.cnf (for the master)

server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
gtid_mode = ON
log-slave-updates
enforce-gtid-consistency

I have also seen this guide, which suggests the following settings in my.cnf (for master):

log-bin = mysql-bin
server-id = 1
relay-log = relay-log-slave
gtid-mode =ON
enforce-gtid-consistency
binlog_format = MIXED
log_slave_updates

Questions:

I have noticed that underscore and dash are being used loosely in the documents, for example when I installed MySQL on my computer, the variable name is log_bin but in MySQL manual they are using log-bin. Are _ and - interchangeable?
In the second tutorial that I have linked they are setting these two values in addition to setting values from MySQL manual: relay-log = relay-log-slave and relay-log = relay-log-slave. What are these two settings? And do I need them?

_ and - are interchangeable in configuration files, however as SQL system variables only _ is supported.

relay-log is a variable. relay-log-slave is just a value string.


Rotate MySQL log files I want to daily rotate MySQL log files on Windows. I want to keep the files, I don't want to use tables instead.

There are 3 log files: general.log slow-queries.log and error.log

I can do it easily with a scheduled task. I saw that it recommended stopping the logs before I copy them to the backup directory.

One problem is that 'error.log' cannot be stopped.
Another problem is that stopping the logs may cause some loss of events, and I want to avoid it.

Is there a safer way (or built-in way) for logs rotation? (on a daily base or according to the file size)?

FLUSH LOGS gracefully does most of what you want.  It stops writing to a log, renames the file and starts a new one.  After that, you can deal with copying/moving/archiving/etc the files.

FLUSH LOGS can be run at any time without damaging anything or losing any log entries.

Caveat:  There are many different log files; FLUSH may not work the same way on each, especially in older versions of MySQL.

For those three logs, in most systems, the general log is the biggest, followed by slow, then error.  FLUSH also rotates the "binlog", but, again, that is "harmless".


date dimension OLAP cube design I have a sales reporting system with incoming data like this

date,editionID,outletId,Measure1,Measure2
2013-01-01,2,532,11,24

etc...

I am new to designing data cubes (for the pentaho BI suite) and when I was building the schema, everything was working fine until I wanted to define date as a date dimension. The pentaho schema designer needs this to be a foreign key reference to a date dimension table in order to use it as a dimension.

My question is, why do I need a separate table? How would I structure it? how would I reference it in the fact table?

Thanks!

Date dimensions are pretty standard in a data warehouse, and are highly recommended by Kimball as most facts tie to a date. Typically, the key is an integer.  It can be a meaningless surrogate key, or it can be a "smart" key where the integer is in the form yyyymmdd; e.g., the key for August 2, 2014 would be 20140802.

Date dimensions provide a set of contiguous dates in multiple formats and allow you to do date calculations once rather than in each query. They make it very easy to do time period comparisons.  You can add other fields that could be analytically relevant such as holidays, indicators of work days, fiscal calendars (where different from standard calendar years).

There are lots of scripts available online to create and populate a date dimension.  Many tools will create the date dimension for you. I'm not sure what you are using for your underlying data source, so here are a couple of examples of date dimensions.  Hopefully you can convert these to the appropriate format for your needs.

T-SQL Date Dim
Oracle Date Dim
MySQL Date Dim
Excel Date Dim


How to update mongodb collection with ssh from a shell script? I am trying to update a mongoDb collection via ssh from a shell script but getting the below error :

  MongoDB shell version: 2.6.4
  connecting to: localhost:27017/b2b_tempdb
  2017-12-05T11:04:51.153+0000 SyntaxError: Unexpected token :

Below is the full command :

ssh -n happy@localhost  " mongo --host localhost:27017  --authenticationDatabase admin b2b_tempdb --eval 'db=db.getSiblingDB( \"b2b_tempdb\" ); db[\"b2b_tempdb.activity.preferences\"].update({},{ $set: {'codeId':\"happy.l2l-09l2l121\"} }, {multi:true})' "

Kindly help me in getting it resolved .
Thanks in advance for the help .

For me this works fine

ssh mongodb.domain.com "mongo --eval='db.getSiblingDB(\"testi\").empty.update({},{\$set:{key:1}},{multi:true})'"

The catch is \ before $ because otherways shell will "expand" that $set "enviroment variable", what of course don't exists.


Simple yet multilingual flashcards - proper database design I'm making a simple flashcards app for personal use.

I've thought about having such table:

CREATE TABLE flashcards (
    translation  VARCHAR(50),
    entry        VARCHAR(50),
    lang         VARCHAR(2),
    displayed    INT DEFAULT 0,
    known        INT DEFAULT 0,
    lastKnown    DATE NULL,

    PRIMARY KEY (translation)
)

Sample data would be:

'périlleux'     ,   'dangerous'  ,  'fr'  ,  ...
'hasardeux'     ,   'dangerous'  ,  'fr'  ,  ...
'mauvais'       ,   'dangerous'  ,  'fr'  ,  ...
'pericoloso'    ,   'dangerous'  ,  'it'  ,  ...
'malfido'       ,   'dangerous'  ,  'it'  ,  ...
'niebezpieczny' ,   'dangerous'  ,  'pl'  ,  ...
'gefährlich'    ,   'dangerous'  ,  'de'  ,  ...
'paisible'      ,   'calm'       ,  'fr'  ,  ...
'tranquille'    ,   'calm'       ,  'fr'  ,  ...

However, I have a feeling that it's not the best way to design this database as it is not aware of the relation between the translations of the same word.

The app will work like this (using speech recognition and speech synthesis):

get entry from database and say it
get all the translations of it for chosen language
wait for me saying the answer or some "I don't know" command
update "scores"
in case I didn't answer correctly say all of the translations (from 2.)

Alternatively (in other mode):

get entry from database and one of its translation in another language and say it (the translation)

I can make it work just fine with multiple query's to the db. But I'm writing here to ask for more of an elegant way to deal with the design. For which I'll be grateful.

This is a good use for a parent-child model.  This can be easily accessed with a join query.  Include the language(s) you want to work with in the where clause of the query.

This model below handles multiple translations between multiple languages.  If you want flashcards for one language as in your example above, remove the lang column from the flashcards table.

CREATE TABLE flashcards (
    entry_id     INT NOT NULL auto_increment,
    entry        VARCHAR(50) NOT NULL,
    lang         VARCHAR(2) NOT NULL,
    PRIMARY KEY (entry_id),
    UNIQUE KEY `flashcard_entry` (entry, lang)
);

CREATE TABLE flashcard_translations (
    entry_id     INT  NOT NULL,
    lang         VARCHAR(2) NOT NULL,
    translation  VARCHAR(50) NOT NULL,
    displayed    INT DEFAULT 0,
    known        INT DEFAULT 0L,
    lastKnown    DATE NULL,
    PRIMARY KEY (entry_id, lang),
    FOREIGN KEY (entry_id) REFERENCES flashcards (entry_id),
);

Depending on your requirements, you may not want the unique key.


Postgresql To Return 0 when Null I am attempting to run a query using MAX() but my issue is that if the field contains a NULL value the data returns nothing.  Updating the table is not an option, so how should this query be re-written to return 0 when NULL?

Sample DDL below - and I hardcoded a WHERE clause that returns nothing just for illustrative purposes, ideally I want to return all usernames and the highest logincount for the user

CREATE TABLE Test
(
 username varchar(50) not null,
 logincount int,
 logindate Date
);

Insert Into Test (username, logincount, logindate)
VALUES
('er11', 1, '2017-01-01'), ('er11', 2, '2017-01-02'), ('ff12', NULL, NULL)
,('jb88', 1, '2017-01-09');

With maxlogincount As
(
    select max(logincount) As "MaxLoginCount"
    ,username
    FROM Test
    GROUP BY username
)
Select
username
,logincount
,logindate
FROM Test t
inner join maxlogincount mlc
ON t.username = mlc.username
And mlc."MaxLoginCount" = t.logincount
WHERE t.username = 'ff12'
Order by t.username asc

Use max(coalesce(logincount, 0)) to avoid NULLs

According to Postgres docs (9.6):

  The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:


Install MariaDB and MySQL in CentOS 6 32bit in same server I'm trying to install MariaDB with MySQL in the same server but got this errors:

Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.i686 0:5.5.32-1 will be installed
--> Processing Dependency: MariaDB-common for package: MariaDB-client-5.5.32-1.i686
---> Package MariaDB-server.i686 0:5.5.32-1 will be obsoleting
---> Package mysql-server.i686 0:5.5.33-1.el6.remi will be obsoleted
--> Running transaction check
---> Package MariaDB-common.i686 0:5.5.32-1 will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-5.5.32-1.i686
--> Running transaction check
---> Package MariaDB-compat.i686 0:5.5.32-1 will be installed
--> Processing Conflict: mysql-5.5.33-1.el6.remi.i686 conflicts MySQL
--> Finished Dependency Resolution
Error: mysql conflicts with MariaDB-server-5.5.32-1.i686
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

I read docs here and here but that doesn't help since those guides are for alone installation I though, how I can install MariaDB and MySQL both in the same server by using yum?

MariaDB is a total drop-in replacement for MySQL - it even uses the same configuration files and folders.

If you're utterly sold on using both simultaneously, then you'd probably be best compiling from source and configuring all the folders manually. I am speaking from Debian derivative basis, so I may be off the mark regarding yum, but generally the package manager versions tend to be a simple replacement.

I would still run the following before taking the plunge, just in case something goes wrong:
mysqldump -u root -p --all-databases > alldbs.sql. Then you can easily restore with mysql -u root -p < alldbs.sql.

Source: I recently attempted to do this myself, and found that there was no feature I used in MySQL that wasn't totally compatible with MariaDB.


SQL Server Reporting Services 2017: Multiple Instances SQL Server Reporting Services are no longer part of the installation media since SQL Server 2017. They are delivered as a separate Installer.

The new Installation Wizard gives no option to install multiple (named) instances of SSRS 2017 on the same machine.

Is that still possible?

From my research, this is currently not implemented in SSRS 2017. I was not able to find any information in an official Deprecated Features document.

Comment from user 'Matt'
Comment from user 'Sai_Kumar'
Twitter-Post from Christopher Finlan, Senior Program Manager, Power BI Report Server & SQL Server Reporting Services at Microsoft.

I found two votes to bring this functionality back to the product:

Support Power BI Report Server multiple instances on a single server
Restore multiple instance support for Reporting services in SQL 2017

Update (May 2019): The feature will not be reimplemented

  AdminChris Finlan (Program Manager, Microsoft Power BI) replied:

  Hi all, We aren’t planning to re-implement multi-instance support as
  we had prior to SSRS 2017. We recommend using virtualization as an
  alternative, and we’re also looking at possibly adding container
  support in the future.


MariaDB optimizing geo IP lookup with Maxmind GeoLite2 database I have the following two tables that I ingest with data from Maxmind's GeoLite2 Country database:
CREATE TABLE `country_location` (
  `geoname_id` int(11) UNSIGNED NOT NULL,
  `locale_code` varchar(255) DEFAULT NULL,
  `continent_code` char(2) DEFAULT NULL,
  `continent_name` varchar(255) DEFAULT NULL,
  `country_iso_code` char(2) DEFAULT NULL,
  `country_name` varchar(255) DEFAULT NULL,
  `is_in_european_union` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`geoname_id`),
  KEY `idx_country_iso_code` (`country_iso_code`),
  KEY `idx_continent_code` (`continent_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `country_blocks` (
  `network` varchar(255) NOT NULL DEFAULT '',
  `network_start_integer` NUMERIC(39, 0) unsigned DEFAULT NULL,
  `network_last_integer` NUMERIC(39, 0) unsigned DEFAULT NULL,
  `geoname_id` int(11) UNSIGNED DEFAULT NULL,
  `registered_country_geoname_id` int(11) DEFAULT NULL,
  `represented_country_geoname_id` int(11) DEFAULT '0',
  `is_anonymous_proxy` tinyint(1) DEFAULT '0',
  `is_satellite_provider` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`network_last_integer`),
  KEY `idx_network_start_integer` (`network_start_integer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I have been previously using the following query:
SELECT l.country_iso_code, l.continent_code
    FROM geo.country_location l
        JOIN geo.country_blocks b
    ON (l.geoname_id=b.geoname_id)
    WHERE network_last_integer >= CONV(HEX(INET6_ATON('1.1.1.1')),16,10)
    ORDER BY network_last_integer LIMIT 1;

This query executes in less than ~1ms which is expected to be fast given the usage of only 1 key.
Unfortunately since MaxMind now adds some additional columns for geoname_id (registered_country_geoname_id, represented_country_geoname_id) there are certain cases (when geoname_id = 0) when the query would produce false positives returning the results of the next network from country_blocks for which the JOIN is triggered. In such cases I need to use registered_country_geoname_id instead of geoname_id (I don't care of represented_country_geoname_id so far).
I am trying to figure out a way of elegantly resolving that issue without any degradation of speed.
I tried the following but unfortunately it takes ~65ms which is quite slow (and explainable since now I am using two keys):
SELECT l.country_iso_code, l.continent_code
    FROM geo.country_location l
        JOIN geo.country_blocks b
        ON (l.geoname_id=b.geoname_id)
        OR (l.geoname_id=b.registered_country_geoname_id)
    WHERE network_last_integer >= CONV(HEX(INET6_ATON('1.1.1.1')),16,10)
    AND network_start_integer <= CONV(HEX(INET6_ATON('1.1.1.1')),16,10);

To optimize this and since selecting with IN with LIMIT from within other queries is not supported with the MariaDB 10.5 I ended up selecting the proper geoname_id first:
SELECT IF(geoname_id = 0, registered_country_geoname_id, geoname_id) as geoname_id
FROM geoip.country_blocks
WHERE network_last_integer >= CONV(HEX(INET6_ATON('1.1.1.1')),16,10)
ORDER BY network_last_integer LIMIT 1;

and then using the result to select within country_location.
This is quite fast since I am still using one key in country_blocks, but is there any more elegant way of achieving this?
~Cheers~

OR is inefficient.  It can be turned into UNION.  An example:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or
Why use INET6_ATON on IPV4 values?
Finding something in a range is poorly optimized, primarily because the Optimizer does not know whether there are overlapping ranges.  One solution is given here:  http://mysql.rjweb.org/doc.php/ipranges


Trying to figure out when a table was dropped? Possible Duplicate:
  Determining how a schema change occurred?

Using SQLServer 2008, I see in my schema a particular table was dropped.  I am trying to figure out when / why etc?  Is there any commands I can run to see when the table was dropped, or anything that could help me out.

Thanks.

Postfactum you not available to see it

If you want to know this in the future - see all about DDL Triggers


Can SQL Server be forced to use a cached plan for executing a SP or SQL script? Can we somehow force SQL Server to use a cached plan for executing a SP or sql query?

We have a parallel plan in prod which estimates data size in GB's and estimates number of rows to rows equal to table cardinality.

Just before the final output , query plan estimates

Estimated Number of Rows: 650970000
Estimated Row Size: 1043 B
Estimated Data Size: 632 GB

The execution plan does not show "Actual Number of rows read" , so have to go with above parameters.

I am unable to get the same plan while executing the SP or the internal query manually so cannot really do a like for like comparison of any performance improvement that I make to the query / SP.

According to these comments

  Can we somehow force SQL Server to use a cached plan for executing a
  SP or sql query?

  I am unable to get the same plan while executing the SP or the
  internal query manually so cannot really do a like for like comparison
  of any performance improvement that I make to the query / SP.

It looks like you only want to run the query with the plan in the cache that has the high row estimates for testing purposes.

Altough you should not implement this in production, you could run it like this:

Also note while you can force the estimated execution plan using below hints, values such as the estimated rowcounts, estimated total subtreecost, ... will be recalculated. Even when forcing the cached plan. What remains the same is the general shape of the plan, such as operators used and indexes touched.

1) Find the estimated plan

You could search for the estimated execution plan in the cache by using the query in this post on Searching the SQL Server Plan Cache

2) Display the plan XML

After getting the plan, display the XML:

3) Replace the apostrophes

Replace all 's with ''s, for example with find and replace:

4) Run the query with the USE PLAN hint

Rerun the query with

OPTION(USE PLAN
N'
Execution plan XML here
');

Make sure that there are no spaces between USE PLAN N' and the execution plan XML.

Correct use:

OPTION(USE PLAN
N'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3223.3">
...

Incorrect use :

OPTION(USE PLAN
N'
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3223.3">
...

You would have to create a temporary stored procedure with the OPTION(USE PLAN N'') or alter the existing stored procedure or use a plan guide to add these hints when using stored procedures.

More information on query hints can be found here.

Looking into why these estimates are so high seems like more of a necessity.
We could help with that, but we would need the (actual) execution plan.

You could upload the plan to PasteThePlan and add it to the question, or ask a new question with the uploaded plan.


How to ensure each user only answers a question once on the db side? I am designing a database for a custom software application. The part I am focused on now is the ability to add custom questions to ask users.

I want to ensure that each user can only answer a question once. In this case, a user is a 'Provider'. How do I ensure each user only answers a question once?

A unique constraint on Answers (questionid, providerid) was needed, as suggested by Balazs Papp.

As explicitly pointed out by Andriy M this should be a composite key.

I had to look up composite keys, as I was unfamiliar. Thanks so much to everyone for helping and responding so quickly.


Truncating MySQL table and insert newly records on daily basis I have my database in which I have to store new data on daily basis fetching from API. The problem is that I have to truncate table everyday and then insert newly fetched records in same table. Records count would be around 10k. It takes 5-10 seconds to insert new records.

So my concern is that if I truncate the table and some users are requesting at a time so he will end up with error because I truncated the table. So can you suggest what should I do to tackle this problem? I am not a DBA so please suggest what should be the better way to do it.

Instead, do

CREATE TABLE new LIKE real;
load new data into `new` by whatever means
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

Notes:

The second step is the only slow step.
The RENAME is atomic.
You can continue to read the table during the process.
Do not use any transactions or table locks.


Integration Services 'All Execution Reports' Loads Forever I'm using SQL Server 2012 to run my Integration Services (SSIS) package by deploying it to the SSIS catalog.

Then an error happened in my packages, so I went to check what it is by opening:

Reports | All Executions | All Messages

However, it never actually opens and loads forever:

And when I tried clicking the refresh button, this error showed up instead:

I don't have any clue what's going on, and I don't remember it ever being like this before, so something (or someone) must have been changing things.

Where should I check on this?

Any advice would be greatly appreciated.

It turns out my SSIS package was so ineffective that it heavily consumes memory, causing the sessionID of SSISDB SUSPENDED (figured it out by using sp_who2)

I kill all the problematic suspended sessions, and the SSIS Execution Report works again!


How can I generate a row_number without using a window function? In PostgreSQL, how do you generate a row number:

WITHOUT a Window Function (like row_number())
WITHOUT a Temp Table
Only using a single SELECT

Here is some sample data to play with,

CREATE TEMP TABLE foo AS
SELECT * FROM ( VALUES
  ('wgates', 'Gates', 'William' ),
  ('wgrant', 'Grant', 'Wallace' ),
  ('jjones', 'Jones', 'John' ),
  ('psmith', 'Smith', 'Paul' )
) AS t(name_id, last_name, first_name);

The desired output would be:

 row_number │ name_id │ last_name │ first_name
────────────┼─────────┼───────────┼────────────
          1 │ wgates  │ Gates     │ William
          2 │ wgrant  │ Grant     │ Wallace
          3 │ jjones  │ Jones     │ John
          4 │ psmith  │ Smith     │ Paul

Some of these methods can get tricky. Please explain your answers. I can also imagine two categories of answers that work:

data with a UNIQUE or PRIMARY KEY (we can still use name_id here)
nothing UNIQUE at all.

All features are on the table for the most recent version of PostgreSQL.

Ultimately, I need a unique key on a table that has no ID so I can update it against a cross-join of itself. I am also asking out of simple curiosity.

If it is performance you are worried about, use row_number without order by to avoid sorting.

row_number() over ()


How can I fix this unexpected Windows Event Log message which appears whenever a database with FILESTREAM is created or restored? We have recently noticed that whenever we create or restore a database with FILESTREAM enabled, a message like the following is added to the System Event Log:

  The description for Event ID 1006 from source RsFx0320 cannot be
  found. Either the component that raises this event is not installed on
  your local computer or the installation is corrupted. You can install
  or repair the component on the local computer.

  If the event originated on another computer, the display information
  had to be saved with the event.

  The following information was included with the event:

  \Device\RsFx0320

  The specified resource type cannot be found in the image file

The Event ID will be 1006 or 1007.

From the "Details" tab of Event Viewer, it's possible to see that the binary data associated with the error message contains the path of the FILESTREAM folder for the database being created/restored.

This occurs on each of our Windows 2012 R2 servers & 64-bit Standard Edition, and on my local machine running Windows 10 Pro & 64-bit Developer Edition. All of the machines are running 12.0.5203, but the issue was certainly occurring before 12.0.5203 was installed.

Unfortunately the Windows logs on the machines have cycled so it's impossible to confidently pick a date when this started.

Some research has revealed that these Event IDs probably relate to the group policy error message codes 1006 and 1007.

I think there are two issues here:

i) The OS is trying to verify file system permissions for a local account via group policy, but the AD controller can't be found.

ii) When i) occurs, error reporting is damaged in such a way as to prevent the proper error message being recorded in the event log.

Regarding ii) RsFx0320.sys is a Microsoft driver installed as part of the OS; I've not been able to find much information about it. I've seen a couple of suggestions that rebooting the system will clear the message but that doesn't seem to be true in this case, since instances of the error on all the servers predate the most recent restart.

Any suggestions on how to fix this/whether it needs fixing? (I don't believe the error is having any impact on the functionality of SQL Server, but it's annoying to have this message clogging up the logs.) Google has so far drawn a blank for me.

It could be two things. If you have Windows 10, Creators update, there is a patch for the SQL Server.

In all other cases this fixed the error for me:

ALTER DATABASE [dbname] SET ANSI_NULL_DEFAULT ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET ANSI_NULLS ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET ANSI_PADDING ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET ANSI_WARNINGS ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET ARITHABORT ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET AUTO_CLOSE OFF WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET CONCAT_NULL_YIELDS_NULL ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET QUOTED_IDENTIFIER ON WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET PAGE_VERIFY NONE  WITH NO_WAIT
GO


Why is this relation in 3NF? I have a relation:

R4 = {{T,U,V}, {T → U, U → T, T → V}}

I know from looking at the answer key that this relation is in BCNF.

I'm going through the process of rigorously determining what normal form the relationship adheres to.  It's clear to me why the relationship is in 1NF and 2NF, and if I assume it's in 3NF, BCNF follows easily.

However, the definition of 3NF states:

  Every non-prime attribute is non-transitively dependent on every candidate key in the table.

But, as far as I can tell, both {T} and {U} are candidate keys of the table, and {V} is thus transitively dependent on {U}.

There is an alternate definition of 3NF available on wikipedia:

  A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:

  X contains A (that is, X → A is trivial functional dependency)
  X is a superkey
  Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each column in A-X is contained in some candidate key)

And the relation is clearly in 3NF by this definition (all of the functional dependencies are covered by "X is a superkey").

So why the discrepancy?  How am I misapplying the definition?  Please don't give me shortcuts that give me the answer in a way I don't want, unless you also help me understand why my application of 3NF (as described) is inaccurate.

The mistake is in your understanding of transitive dependency. From wikipedia: Transitive dependency

  In mathematics, a transitive dependency is a functional dependency which holds by virtue of transitivity. A transitive dependency can occur only in a relation that has three or more attributes. Let A, B, and C designate three distinct attributes (or distinct collections of attributes) in the relation. Suppose all three of the following conditions hold:

1. A → B
2. It is not the case that B → A
3. B → C

  Then the functional dependency A → C (which follows from 1 and 3 by the axiom of transitivity) is a transitive dependency.

In your case though, (2) does not hold:

1. U → T                                  -- correct
2. It is not the case that T → U          -- wrong
3. T → V                                  -- correct

Therefore {V} is NOT transitively dependent on {U}.


Create SQL Script Repository I need to get all my SQL scripts in some kind of script repository. I have searched the web but no luck so far. I thought of creating a repository in a SQL database, my problem so far is to properly manage versioning of the scripts.

We are using to many scripts and everyone is using a different version of a script and is causing skewed data outputs. Has anyone successfully created something like this or is there a free application that can do this?

There is also a way to create SQL Scripts Repository.
Add new template folder

Add script files


Unexpected gaps in IDENTITY column I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script:

CREATE TABLE [dbo].[PONumbers]
(
  [PONumberPK] [int] IDENTITY(1,1) NOT NULL,
  [NewPONo] [bit] NOT NULL,
  [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(),
  CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC)
);

And a stored procedure created using this script:

CREATE PROCEDURE [dbo].[GetPONumber]
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1);
    SELECT SCOPE_IDENTITY() AS PONumber;
END

At the time of creation, this works fine. When the stored procedure runs, it starts at the desired number and increments by 1.

The strange thing is that, if I shut down or hibernate my computer, then the next time the procedure runs, the sequence has advanced by almost 1000.

See results below:

You can see that the number jumped from 8 to 1002!

Why is this happening?
How do I ensure that numbers aren't skipped like that?
All I need is for SQL to generate numbers that are:

a) Guaranteed unique.
b) increment by the desired amount.

I admit I'm not a SQL expert. Do I misunderstand what SCOPE_IDENTITY() does?  Should I be using a different approach? I looked into sequences in SQL 2012+, but Microsoft says that they are not guaranteed to be unique by default.

This is a known and expected issue - the way IDENTITY columns are managed by SQL Server has changed in SQL Server 2012 (some background); by default it will cache 1000 values and if you restart SQL Server, reboot the server, fail over, etc. it will have to throw out those 1000 values, because it won't have a reliable way to know how many of them were actually issued. This is documented here. There is a trace flag that changes this behavior such that every IDENTITY assignment is logged*, preventing those specific gaps (but not gaps from rollbacks or deletes); however, it is important to note that this can be quite costly in terms of performance, so I'm not even going to mention the specific trace flag here.

* (Personally, I think this is a technical problem that could be solved differently, but since I don't write the engine, I can't change that.)

To be clear about how IDENTITY and SEQUENCE work:

Neither is guaranteed to be unique (you need to enforce that at the table level, using a primary key or unique constraint)
Neither is guaranteed to be gapless (any rollback or delete, for example, will produce a gap, this specific problem notwithstanding)

Uniqueness is easy to enforce. Avoiding gaps is not. You need to determine how important it is for you to avoid these gaps (in theory, you should not care about gaps at all, since IDENTITY/SEQUENCE values should be meaningless surrogate keys). If it is very important, then you should not be using either implementation, but rather roll your own serializable sequence generator (see some ideas here, here and here) - just note that it will kill concurrency.

Lots of background on this "problem":

SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry
identity increment is jumping in sql server database
Identity column value suddenly jumps to 1001 in sql server


Difference between a trigger and a transaction I'm implementing movie rating service, so I'm considering about using triggers or transactions.

What I'm considering is inserting movie information into a movie_list and to insert the genre of that movie into movie_genre_list.

In this case, my thinking is to write a trigger to insert the genre when the movie information is inserted.

But, I don't know whether a trigger or a transaction is right for this case. What should I do?

Comparing TRIGGERs and TRANSACTIONs is like comparing apples and oranges! No, wait, it's not, because apples and oranges are similar - they are both fruit and can both be eaten. It's more like chalk and cheese (English expression)!

Transactions are for grouping actions into one unit of work which either succeeds or fails in its entirety!
Triggers are (normally) for performing (procdural/business logic) code when an event (normally an INSERT, UPDATE or DELETE on a particular table) occurs in the database.

TRANSACTIONs.

These units of work are treated in a coherent and reliable way independent of other transactions.

From Wikipedia:

  To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system
  failure, when  execution stops (completely or partially) and many
  operations upon a database  remain uncompleted, with unclear status.
  To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs'
  outcomes are possibly  erroneous.

Note the use of the word "isolation" - this is one of the criteria for the "ACID" test. ACID in this context corresponds to both a play on words in English and a set of properties of database transactions that are considered highly desirable - these are: Atomicicty, Consistency, Isolation and Durablility.

The classic example of this is in banking. Say you want to pay your credit card bill of €100 by transferring money from your current account. So, it's important for both you and the bank that this "transaction" should occur as one single consistent unit of work. Otherwise, either you (money leaves your current account but doesn't get to the CC account), or the bank (your CC get credited but your current account isn't debited) is out of pocket.

So, in SQL, what you would do is:

BEGIN TRANSACTION;
UPDATE current_account ca SET ca.balance = ca.balance - 100 WHERE ca.cust_name = 'Haram';
UPDATE      CC_account cc SET cc.balance = cc.balance + 100 WHERE cc.cust_name = 'Haram';
COMMIT;  -- definitively perform the work.

If any of the steps fail (e.g. the balance in the current_account is insufficient), all of the work of the transaction will be rolled back - thus ensuring consistency.

Final point - if only it were all that simple! :-) There are (at least) four complicating factors:

1) Different isolation levels. This is beyond the scope of this answer - check out the Wiki and use your search engine of choice - there's loads of material out there, and

2) Different implementations by vendors - you'll have to look at your own system(s)' documentation for this, and

3) There are such things as nested TRANSACTIONs and SAVEPOINTs - again, this is getting beyond the scope of this answer - check your documentation, and

4) Some database engines support Transactional DDL (Data Definition Language) and some (notably MySQL) don't.

TRIGGERs.

A database TRIGGER on the other hand is a different animal altogether! A TRIGGERis an action (coded by the developer) which occurs in response to events on a table (again, there are complications which I won't go into here - check the Wiki and/or use your search engine). In the vast majority of cases, the event in question is an INSERT, an UPDATE or a DELETE on a table.

A rather nice analogy can be found here. An action on a database is compared to a row of dominoes - you knock over the first one and rest come tumbling down in a chain reaction. This is similar to a TRIGGER which will cause further events on other tables and so on!

It is best, however, not to take the analogy too far - you want to avoid doing complex multi-table chained updates in so far as is possible - KISS. You can see how circular references (TRIGGER on  A UPDATEs B which in turn UPDATEs A...) might be disastrous!

From here:

You can use triggers to perform the following actions, as well as others that are not found in this list:

  Create an audit trail of activity in the database. For example, you can track updates to the orders table by updating corroborating
  information to an audit table.
  Implement a business rule. For example, you can determine when an order exceeds a customer's credit limit and display a message to that
  effect.
  Derive additional data that is not available within a table or within the database. For example, when an update occurs to the
  quantity column of the items table, you can calculate the
  corresponding adjustment to the total_price column.

(The link also mentions using them to enforce DRI (Declarative Referential Integrity) - I disagree - see end of answer).

A typical (vendor syntax varies considerably here - check your system's documentation) TRIGGER skeleton (example taken from here) would be:

CREATE
  TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE
  ON `database`.`table`
  FOR EACH ROW BEGIN
  -- trigger body
  -- this code is applied to every
  -- inserted/updated/deleted row
END;

and the body might look something like this:

IF NEW.deleted THEN
  SET @changetype = 'DELETE';
ELSE
  SET @changetype = 'NEW';
END IF;
INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);

This trigger is INSERTING a record into a table called audit with the id from the audited table (called blog in this case) and also recording whether the action was a DELETEor an INSERT. You could also have TIMESTAMP logic in there as well to keep track of when the action took place. Furthermore, you could add in user_name logic to audit who INSERTed/DELETEd what and when. The possibilities are endless.

Summary.

So, to sum up, a TRANSACTION groups a set of statements into a logical unit and a TRIGGER permits the inclusion of logic beyond the scope of SQL (complex business rules) in the database itself.

One thing I found tricky at the beginning was the idea that (if no TRANSACTION is specified), the server will set up an implicit transaction on your behalf and COMMIT after each statement.

For example, here is the default behaviour of the MySQL server:

mysql> show variables like '%COMMIT%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| autocommit                              | ON    |

Issue the command

mysql> SET autocommit = OFF;
Query OK, 0 rows affected (0.00 sec)

Now, you can experiment with what happens if you issue several commands in a row and issue a COMMIT and also when you exit without COMMITting.

Better yet, you can experiment with isolation levels

mysql> show variables like '%ISOL%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

and then open different terminal sessions and see what happens to record visibility under different conditions.

Very last word: It looks to me that you are on the beginning of your database journey. Even though I have used MySQL for examples here, I would strongly (strongly) urge you to use PostgreSQL whenever possible - it is a vastly superior database server to MySQL and much more standards compliant to boot! Try and use the command line client whenever possible - can be fiddly at the beginning, but much more powerful and worth it in the long run!

Very very last word ( :-) ) - keep reading and get some good texts and study them! p.s. Congratulations on your first questions on dba.stackexchange!

Oooops - almost forgot to answer the question asked!

You can either (if you have one and only one (main) genre per film do this:

create a genre table and have a FOREIGN KEY on your movie table pointing to the genre_name field (fiddle1),

Or (if you wish to have more than one genre per film - a far more interesting and realistic scenario) do this:

create a genre and movie table as above, but also have a movie_genre joining table (or Associative Entity) between the two (fiddle2).


Unable to change root user unix_socket to mysql_native_password Server version: 10.1.41-MariaDB-0+deb9u1 Debian 9.9

I have installed a LAMP stack on my Debian 9 machine and can not log in with the root user.  From my searching, it seems that it's because the install commonly creates a root user without a password, causing it to default to unix_socket, which foils logging in with that user via terminal.

MariaDB [(none)]> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------+-------------------------------------------+-------------+-----------+
| user       | authentication_string                     | plugin      | host      |
+------------+-------------------------------------------+-------------+-----------+
| root       | *3624F23A73825C0D79BF3591774631513FE76C24 | unix_socket | localhost | | phpmyadmin |                                           |             | localhost |
+------------+-------------------------------------------+-------------+-----------+
2 rows in set (0.00 sec)

I found an article explaining how to resolve this issue by shutting down mysql, starting it with sudo mysqld_safe --skip-grant-tables & so I can log in and then running:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass';

then stopping and starting the daemon.  My problem is that no matter how I format this line, I get:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'user 'root'@'localhost' IDENTIFIED WITH
  mysql_native_password BY 'mypass'' at line 1

I've tried tons of different formatting with backtics, case change and other iterations of the command found on the web but everything fails with the same result.

Could someone tell me how I go about changing this successfully?

As the manual says:

  The ALTER USER statement was introduced in MariaDB 10.2.0.

Since you're at 10.1, you'll need to reset the password using the old, dangerous method:

update mysql.user
 set authentication_string=password('mypass'),
 plugin='mysql_native_password'
where user='root';


Can I use Default Accounts for setting up AOAGs 2014\2016 Can anyone help me on below :

I would like change the SQL Service \ SQL Agent Service to Active Directory Accounts . But before that I would like to understand below :

Can I use the default accounts which SQL Service provides by default

Local Service
Local System
Network Service

If I cannot why I cannot use them  ?

If I can then why should I need Windows Domain Level Service Accounts ?

Why SQL Server Agent Account needs to be added as SysAdmin , is it by default nature of SQL Server Agent Account ?

Can anyone please help me with the background of the above points in brief .

Can I use the default accounts which SQL Service provides by default

Yes you can use that. From Prerequisites and Recommendation for AG

  if the SQL Server service is running as a built-in account, such as Local
  System, Local Service, or Network Service, or a nondomain account, you
  must use certificates for endpoint authentication, and the wizard will
  be unable to create a database mirroring endpoint on the server
  instance. In this case, we recommend that you create the database
  mirroring endpoints manually before you launch the wizard.

The advantage with using domain account is that they are more secured and when you configure availability groups using SSMS GUI the endpoints are created by the SSMS GUI and also granted the connect permission(. As you can note from above that when using built in account you need to create endpoints using certificates manually and grant connect permission.

You also have option of running SQL Server services with different domain account, if you do so you just need to make sure both the logins are created on remote machine in master database.

More over before changing I would suggest you to read Troubleshooting Always ON Configuration( see the accounts section)


Autoincrement vs composite primary key for a big table on innodb I have a had a quite lengthy discussion with Rick James on this this we came out with idea of having composite key to replace the autoincrement pk where the int is limited close 2 billion. My table will reach this limit in few months easily as monthly we are capturing close to few hundred million data. Below is how my table looks like. The key table is the gdata so I composite the primary using 3 fields PRIMARY KEY (alarmTypeID,vehicleID,gDateTime). Then I have another table called alarm table. The link between both is one to many. Meaning one data in gdata can have zero or more alarms related to it. The link between them is vehicleID and gDateTime.

CREATE TABLE `gdata` (
    `alarmTypeID` tinyint(4) NOT NULL DEFAULT '0',
    `fleetID` smallint(11) NOT NULL,
    `fleetGroupID` smallint(11) DEFAULT NULL,
    `fleetSubGroupID` smallint(11) DEFAULT NULL,
    `deviceID` mediumint(11) NOT NULL,
    `vehicleID` mediumint(11) NOT NULL,
    `gDateTime` datetime NOT NULL,
    `insertDateTime` datetime NOT NULL,
    `latitude` float NOT NULL,
    `longitude` float NOT NULL,
    `speed` smallint(11) NOT NULL
     -- (see full text)
) ;
ALTER TABLE `gdata`
    ADD PRIMARY KEY (`alarmTypeID`,`vehicleID`,`gDateTime`),
    ADD KEY `gDateTime` (`gDateTime`),
    ADD KEY `fleetID` (`fleetID`,`vehicleID`,`gDateTime`);
COMMIT;

Here is the alarm table

CREATE TABLE `alarm` (
    `alarmTypeID` tinyint(4) NOT NULL,
    `vehicleID` mediumint(9) NOT NULL,
    `gDateTime` datetime NOT NULL,
    `insertDateTime` datetime NOT NULL,
    `alarmValue` varchar(5) NOT NULL,
    `readWeb` enum('n','y') NOT NULL DEFAULT 'n',
    `readWebDateTime` datetime NOT NULL,
    `readMobile` enum('n','y') NOT NULL DEFAULT 'n',
    `readMobileDateTim` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `alarm`
    ADD PRIMARY KEY (`alarmTypeID`,`vehicleID`,`gDateTime`);
COMMIT;

All looks good but recently I was doing some googling on related topic and found that some discussion https://www.quora.com/Is-it-a-bad-idea-to-have-a-primary-key-on-3-or-more-columns are against composite primary key and would prefer to go with autoincrement mainly for insert purpose. Can some one shed more light on this to maintain composite key of primary or move back to autoincrement?

There is nothing wrong with a composite key. However, you have to take into account how InnoDB stores data.

Quoting the above linked documentation:

  The data in each InnoDB table is divided into pages. The pages that make up each table are arranged in a tree data structure called a B-tree index. Table data and secondary indexes both use this type of structure. The B-tree index that represents an entire table is known as the clustered index, which is organized according to the primary key columns. The nodes of the index data structure contain the values of all the columns in that row (for the clustered index) or the index columns and the primary key columns (for secondary indexes).

That is, InnoDB will store the data according to your PRIMARY KEY. If the data you're inserting has an increasing PK, page fragmentation does not occur. That will happen always with an AUTO_INCREMENT. If you're inserting the data in chronological order (i.e. gDateTime is always monotonically increasing), changing the order of the columns that make out your PK to:

PRIMARY KEY (`gDateTime`, `alarmTypeID`, `vehicleID`)

... will have the same advantages, with regard to not having to "fit a new row in the middle of others" (which means, the B-tree isn't fragmented for every insert).

However: if you reference this table from other (related) tables, you have to store, in the referencing table always the PK (gDateTime, alarmTypeID, vehicleID). This means you're saving every time 7 or 8 bytes of storage. The composite PK would use 2 + 1 + 8 = 11 bytes of information (probably it uses 12 bytes due to alignment); whereas an INT UNSIGNED AUTO_INCREMENT, you'll use only 4 bytes in the referencing table. You're limited to a 2^32 different values for your PK. If you need more than 2^32 values, you'll need BIGINT AUTO_INCREMENT, that gives you 2^64 (and I haven't found yet a practical case where this isn't big enough).

Whether this makes sense or not, depends a lot on your particular scenario.


Am I using the correct data types on SQL Server side to call an Oracle 10g function? When calling an Oracle function with the following in parameter types

NUMBER(7,0)
VARCHAR2(8 BYTE)
VARCHAR2(4 BYTE)
DATE

and return type NUMBER(14,5) from SQL Server 2008R2 via a linked Oracle server, I declared the parameter variables on SQL Server side as follows:

numeric(7,0) -- also tried INT, BIGINT
varchar(8)
varchar(4)
datetime -- also tried DATE

and return variable type numeric(14,5)

exec ( 'BEGIN ? := db.pkg.getPrice(?,?,?,?); END;', @price, @id1, @id2, @curr, @ed ) AT ORASRV

The call fails with the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1".

What am I doing wrong?

To test that function I used the following PL/SQL script and it worked fine (printed 0 which is a correct result for the parameters):

declare
  c varchar2(4);
  ed date;
  res number(14,5);
begin
  c := 'USD';
  ed := to_date('22-MAY-14', 'DD-Mon-YYYY');
  res := pkg.getPrice(214360, '129746', c, ed);
  DBMS_OUTPUT.PUT_LINE(res);
end;

Also created a function just for testing:

create or replace FUNCTION getTest (
    a IN VARCHAR2 -- also tried INTEGER
)
RETURN  VARCHAR2 -- also tried INTEGER
IS
  res VARCHAR2(1); -- also tried INTEGER
BEGIN
  res := a;
  RETURN res;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END;

When I call it from SQL Server as follows

declare @a varchar(1),@b varchar(1) -- also tried INT
select @b = 'a'
exec ( 'BEGIN ? := db.getTest(?); END;', @a, @b) AT ORASRV

I get the following error:

OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1".
Msg 7215, Level 17, State 1, Line 172
Could not execute statement on remote server 'ORASRV'.

In case of INTEGER on Oracle side and INT on SQL Server side the message is just "ORA-06502: PL/SQL: numeric or value error

Your datatypes should be okay, however you need to change your call slightly and add OUTPUT after your @price.  I've tested this against Oracle11g on INTEGER -> INT, VARCHAR2 -> VARCHAR, DATE -> Datetime using your getTest function

exec ( 'BEGIN ? := db.pkg.getPrice(?,?,?,?); END;', @price OUTPUT, @id1, @id2, @curr, @ed ) AT ORASRV


Deadlock best practices We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they happen? I know there is a statement_timeout, lock_timeout and deadlock_timeout but most the places I read state that you don't want to set this in the postgres.conf file.

Just wanted to see if there is a typical/best practice method for this. It looks like setting deadlock_timeout to something like 10 minutes would be sufficient, but I wanted to make sure I wasn't missing some key something as to why that would be a bad idea.

10 minutes is an extremely high setting for deadlock_timeout, which may also explain why you even get the chance to intervene manually. the default is 1 second and the manual advises:

  The default is one second (1s), which is probably about the smallest
  value you would want in practice. On a heavily loaded server you might
  want to raise it. Ideally the setting should exceed your typical
  transaction time, so as to improve the odds that a lock will be
  released before the waiter decides to check for deadlock. Only
  superusers can change this setting.

I don't expect your typical transactions exceed 10 minutes. Try something like 20 seconds if you have long running queries.

The best defense against deadlocks is also documented here:

  The best defense against deadlocks is generally to avoid them by being
  certain that all applications using a database acquire locks on
  multiple objects in a consistent order.

If you do this consequently, there is no chance for a deadlock.


Is there Windows DLL for PDO_IBM driver available for PHP 5.6? On PECL, there is the latest Windows DLL in version 1.3.3. Unfortunately, they didn't provide one for version 1.3.4. I'd like to get the DLL from some reputable source. Otherwise I'll have to compile it from sources myself in which case some hint as to what should be taken care of would be much appreciated.

We solved this problem by using non-object-oriented extension ibm_db2. Fortunately, our code wasn't using many calls or special features so we were able to refactor our code quite quickly, e.g. from:

$stmt = $conn->prepare($sql);

to:

$stmt = db2_prepare($conn, $sql);


Is possible to reuse a already calculatesd column into a query in MySQL? I have a query with complicated column, and I would like to use this column result in another column, for instance:

SELECT ( /* Complex query */ ) as myValue, if( myValue > 10, "OK" , "" ) from table;

but his query returns an error:

Error Code: 1054. Unknown column 'myValue' in 'field list

How can I reuse a already calculated field?

You can use user-defined variable.

SELECT @temp := ( /* Complex query */ ) AS myValue,
       IF ( @temp > 10, "OK" , "" )
FROM table;

Maybe server demands preliminary variable definition. If so,

SELECT @temp := ( /* Complex query */ ) AS myValue,
       IF ( @temp > 10, "OK" , "" )
FROM table, (SELECT @temp := 0) dummy;

The documentation do not specified the order of output values calculations, but in practice it always matched the order they are written.


How do you select the rolling, most recent value for each person for each month in a range where that most recent value is less than 6 months old? I need help writing a SQL query for Redshift (Postgres will do) likely involving WINDOW functions, PARTITIONS, LAST_VALUE and other things above my head.

Users can submit survey responses at any time (usually every few months). After 6 months the score is no longer fresh/relevant.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    user_id       INTEGER UNIQUE,
    user_name     VARCHAR(50)
);
INSERT INTO users VALUES
    (1, 'Steve Wozniak'),
    (2, 'Steve Jobs'),
    (3, 'Tony Ive');

DROP TABLE IF EXISTS responses;
CREATE TABLE responses (
    response_id     INTEGER UNIQUE,
    user_id         INTEGER,
    date            DATE,
    score           INTEGER
);
INSERT INTO responses VALUES
    (1, 1, '2016-08-21', 2),
    (2, 1, '2016-02-04', 8),
    (3, 1, '2016-04-11', 4),
    (4, 1, '2016-06-21', 10),
    (5, 2, '2015-11-04', 9),
    (6, 2, '2015-11-22', 8),
    (7, 2, '2016-07-11', 10),
    (8, 2, '2016-08-15', 2);

I would like to return a recordset grouped by month that contains the rolling, most recent score (LAST_VALUE) for each user up to that month if that response that is no older than 6 months old.

The result set would contain the following rows relating to User #1. NULL values shown for clarity but can be omitted:

+---------+------------+-------+
| User Id | Date       | Score |
+---------+------------+-------+
| 1       | 2015-11-01 | NULL  | <= No score submitted yet
| 1       | 2015-12-01 | NULL  | <= No score submitted yet
| 1       | 2016-01-01 | NULL  | <= No score submitted yet
| 1       | 2016-02-01 | NULL  | <= No score submitted yet
| 1       | 2016-03-01 | 8     |
| 1       | 2016-04-01 | 8     |
| 1       | 2016-05-01 | 4     |
| 1       | 2016-06-01 | 4     |
| 1       | 2016-07-01 | 10    |
| 1       | 2016-08-01 | 10    |
| 1       | 2016-09-01 | 2     |
| 1       | 2016-10-01 | 2     |
+---------+------------+-------+

And for #2:

+---------+------------+-------+
| User Id | Date       | Score |
+---------+------------+-------+
| 2       | 2015-11-01 | NULL  | <= No score submitted yet
| 2       | 2015-12-01 | 8     |
| 2       | 2016-01-01 | 8     |
| 2       | 2016-02-01 | 8     |
| 2       | 2016-03-01 | 8     |
| 2       | 2016-04-01 | 8     |
| 2       | 2016-05-01 | 8     |
| 2       | 2016-06-01 | NULL  | <= 2015-11-22 SCORE OLDER THAN 6 MONTHS
| 2       | 2016-07-01 | NULL  | <= 2015-11-22 SCORE OLDER THAN 6 MONTHS
| 2       | 2016-08-01 | 10    |
| 2       | 2016-09-01 | 2     |
| 2       | 2016-10-01 | 2     |
+---------+------------+-------+

All 12 months (or entire series) should be populated unless all NULL. Possibly use generate_series() in postgres or using a number table in Redshift (https://www.periscopedata.com/blog/generate-series-in-redshift-and-mysql.html)
The NULL values can be omitted (shown for clarity).

Ultimately I'm going to need to reproduce the same grouped by year and week of the year but I can probably extrapolate those queries if I can figure out this.

2015-11-22 is not older than 6 months compared to 2015-05-01 unless you truncate the response date to month also.

> select '2015-11-22'::date + interval '6 months';
+---------------------+
| ?column?            |
|---------------------|
| 2016-05-22 00:00:00 |
+---------------------+

> select date_trunc('month', '2015-11-22'::date) + interval '6 months';
+---------------------------+
| ?column?                  |
|---------------------------|
| 2016-05-01 00:00:00+02:00 |
+---------------------------+

So depending the logic what you consider expired the query changes a bit. I've included both version. In comment the date and active condition as per the example.

Using Lateral join:

with users as (
    -- I assume you have user table so this can be omitted
    -- first and last are used to limit the join
    select user_id, min(date) as first, max(date) as last from responses group by user_id
), boundaries as (
    select
        date_trunc('month', min(date)) as low
        --, date_trunc('month', max(date)) as high
        -- If you want to use the high in generate_series as upper boundary
    from responses
)
select user_id, tick as "date", score
from
    generate_series(
        (select low from boundaries),
        date_trunc('month', current_date + interval '1 month'), interval '1 month'
    ) as tick
join users on tick.tick between users.first and users.last + interval '6 months'
-- # If you omit the users CTE and you have a users table
-- cross join users
left outer join lateral (
    select score from responses
    where users.user_id = responses.user_id
    -- # Proper 6 months calculation
    --and responses.date between tick.tick - interval '6 months' and tick.tick
    -- # As example showed
    and responses.date < tick.tick and date_trunc('month', responses.date) + interval '6 months' > tick.tick
    order by responses.date desc limit 1
) a on true
order by 1, 2;

Using Window functions:

with boundaries as (
    select
        date_trunc('month', min(date)) as low
    from responses
)
select distinct responses.user_id, tick as "date", first_value(score) over (partition by responses.user_id, tick.tick order by responses.date desc)
from
    generate_series(
        (select low from boundaries),
        date_trunc('month', current_date + interval '1 month'), interval '1 month'
    ) as tick
join responses on (
    -- # Proper 6 months calculation
    -- responses.date between tick.tick - interval '6 months' and tick.tick
    -- # As it was in the example
    responses.date < tick.tick and
    date_trunc('month', responses.date) > tick.tick - interval '6 months'
)
order by 1, 2;


How do I send a database to someone else? I am working with a more experienced database admin and he wants me to send him a database that I created.  However, I cannot figure out where the database is stored on disk.  I am using Microsoft SSMS.

Select a.name, b.physical_name
from sys.databases a
join sys.master_files b
    on a.Database_id = b.database_id
Where a.name = 'NAME_OF_YOUR_DATABASE'

This will show you the location on disk.. (can't move it though).

Backup the database:

BACKUP Database NAME_OF_YOUR_DATABASE
To Disk = N'C:\Databases\NAME_OF_YOUR_DATABASE.bak With Compression


Why disable Postgres Autovacuum? I've got a task to work with a 9.6 Postgres DB, and for some reason Autovacuum is disabled.

The database was extremely slow, and after a manual VACUUM it could breathe again.

The point is, why disable Autovaccum? Is there any practical scenario for a small database to have Autovaccum disabled?

There is no good reason to keep autovacuum disabled in a production database, and you will run into problems if you do that.

It may make sense to disable autovacuum during exceptional operations, where you run a manual vacuum anyway and don't want your performance sapped by a background job, but more often than not such trickery is a premature optimization.

I would run VACUUM (FULL) on the database, followed by VACUUM (ANALYZE). That shoud take care of any damage done.

Then enable autovacuum.