index maintenance strategy if few insertion points relative to # of rows In SQL Server 2008 R2, I have a nonclustered covering index on multiple tables with 100M+ rows. The table has a few thousand "insertion points" where all new inserts happen. This means that regardless of fill factor, I'll quickly end up with page splits and fragmentation at every insertion point, and no fragmentation or splits anywhere else in the table.  Unfortunately, queries always include new rows and hence fragmented areas of the index.

what happens when there's a page split but inserts continue sequentially after the split? Is there a way to tell SQL Server to do the split with lots of extra room for subsequent inserts, without wasting space on existing pages with a large fill factor that for most pages will never be filled?
what are good index maintenance strategies to use for indexes like this?
is there a good automated way to identify tables like this where fragmentation is severe but not uniform? These tables don't show up as more than 5% fragmented overall.
are there index schema changes I should be considering?

Here's more info about the problem. The indexes all look like this pattern (simplifying for clarity below) :

CREATE TABLE Foo (
    id int identity(1,1) PRIMARY KEY CLUSTERED,
    foreign_key int,
    log_time datetime,
    ...)
CREATE NONCLUSTERED INDEX on Foo (foreign_key, log_time) INCLUDE (...)

Queries on this table are always in this form:

WHERE log_time > getdate()-70 AND foreign_key IN (select ...)

Other facts:

there are about 5,000 foreign_key values, each with 10,000's of rows for each.
average row size is 55 bytes, meaning around 150 rows per page
the IN filter usually includes 10%-50% of foreign_key values rows and the date filter includes 20%-40% of the rows. The average is about 15% of total rows selected.
the index is a covering index for the queries, so no clustered index access is needed.

A page split is a page split.  You can't control what SQL Server does when it requires a page split.  That is not a variable and configurable setting.  When SQL Server needs to split a page, it'll put roughly half of the data in one page and half in the new page.

If you are having "multiple" insertion points into the index, causing hot spots in the index to undergo many page splits and much fragmentation then there isn't much you can do to alleviate that with the current design.  It sounds like the way your data is dispersed that could be the root of your problem.  If you insert into the middle of an index, you will cause page splits/fragmentation.

There are a couple of things you can do, depending on how predictable your "insertion points" are.  You could utilize table partitioning, and this would allow you to rebuild the indexes on a particular partition (or partitions).  This will keep cold data from receiving the maintenance, and minimize the duration of the rebuild to the hot and fragmented partitions.

Depending on your edition, you could also utilize online index rebuild operations to reduce the availability impact on this maintenance procedure.


Linking Waits to a process/statement I'm starting to take on more of a DBA role and trying to get tooled up with scripts and knowledge to help with this.

I have been trying the sp_blizt procs in a dev environment. A key area I'm trying to focus on is understanding Waits.

My question is when you see a list of Waits how do you relate them back to a proc/query that is causing them?

For eg. When I see asyn_network_io, how can I show which client app is causing this and what is it running at the time to cause this?

My question is when you see a list of Waits how do you relate them back to a proc/query that is causing them?

You can't, generally speaking. You mention in a comment on another answer that your monitoring tool shows you a jump in wait stats. If your monitoring tool can't correlate resource spikes to what was running at the time, you have a bad monitoring tool.

Tools that  make that easy include

Sentry One Performance Advisor
Quest Spotlight

If you're already using one of those and having a hard time, you should schedule a call with a vendor representative to walk you through the product.

You also can't use most scripts to figure out what happened later. You have to catch things while they're happening. Some easy ways to do that (for free):

Logging Activity Using sp_WhoIsActive – Take 2
First Responder Kit Power BI Dashboard

If you're on SQL Server 2017 or higher, and you have Query Store enabled, it will track aggregated waits stats.

It's up to you to keep track of these things, though.

Best of luck!


Query - Pivoting 2 columns into 2 rows I am familiar with PIVOTS but have done only simple ones of one column into a row.

I have this query:

DECLARE @EndDate DATE
SET @EndDate ='20160728' --{?EndDate}

SELECT T1.ItemCode,SUM(T0.IssuedQty) [Litres],SUM(T1.CmpltQty) [Tons]
FROM    WOR1 T0 INNER JOIN
        OWOR T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
        OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE   T0.ItemCode ='FURNACE OIL/0001' AND T1.Type ='S' AND
        (T1.PostDate BETWEEN (SELECT Dateadd(month, Datediff(month, 0, @EndDate), 0)) AND @EndDate)
GROUP BY T1.ItemCode

This returns the results:

What do I need to do is to get the following results but I'm unsure how.

How do I achieve this?

EDIT: The one highlighted as duplicate is different. It is joining 2 queries for additional columns. I just need 1 query transposed to take 2 columns into rows.

You could do something like this I suppose:

DECLARE @EndDate DATE
SET @EndDate ='20160728' --{?EndDate}

SELECT
    A.LineType,
    CASE B.ItemCode WHEN 'FG/RM/001' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/001],
    CASE B.ItemCode WHEN 'FG/RM/002' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/002],
    CASE B.ItemCode WHEN 'FG/RM/003' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/003],
    CASE B.ItemCode WHEN 'FG/RM/004' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/004],
    CASE B.ItemCode WHEN 'FG/RM/005' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/005],
    CASE B.ItemCode WHEN 'FG/RM/006' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/006],
    CASE B.ItemCode WHEN 'FG/RM/007' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/007]
FROM
    (
        SELECT 'Litres' AS LineType
    UNION ALL
        SELECT 'Tons' AS LineType
    ) A
CROSS JOIN
    (
        SELECT T1.ItemCode,SUM(T0.IssuedQty) [Litres],SUM(T1.CmpltQty) [Tons]
        FROM    WOR1 T0 INNER JOIN
                OWOR T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
                OITM T2 ON T1.ItemCode = T2.ItemCode
        WHERE   T0.ItemCode ='FURNACE OIL/0001' AND T1.Type ='S' AND
                (T1.PostDate BETWEEN (SELECT Dateadd(month, Datediff(month, 0, @EndDate), 0)) AND @EndDate)
        GROUP BY T1.ItemCode
    ) B
GROUP BY
    A.LineType


Identify threads with maximum difference in time I have many parallel executing queries in my SQL Server database. I need to find out queries where  the difference in time for the longest thread and the  shortest thread  is high (excluding co-ordinator thread). In fact, I need to find top 10 such queries with highest difference (from the queries that are getting exeuted in the span of 1 hour). How to track this information in SQL Server?

Note: I am using SQL Server 2012.

References

SQL Server 2012’s Information on Parallel Thread Usage - Joe Sack
Parallel Execution Plans – Branches and Threads - Paul White

One thing to note a query execution does not go in parallel for all operators. It is decided per operator cost and your setting for 'Cost Threshold of Parallelism' and 'Max Degree of Parallelism'.

In theory you are looking at operations (operator with in execution plan) that had the highest delta in runtime between threads.

Depending on how much you collect, I suggest you create some indexes to speed things up. Let me know if you have any issues running my code.

/*============================================================================
PerThreadcollection.sql
Written by Taiob M Ali
SqlWorldWide.com

This script will show where queries (certain tasks within execution plan) are running
in parallel and difference in time for the longest thread and the shortest thread is high (excluding co-ordinator thread).

Instruction to run this script
--------------------------------------------------------------------------

--You will have to adjust @howManyTimes Variable values based on your requirements
--You will have to adjust @WaitSec Variable values based on your requirements

============================================================================*/

USE [DbaDB]
GO
--Creating table to hold raw data
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [DbaDB].[dbo].[PerThreadcollection](
    [timestamp] [datetime] NOT NULL,
    [session_id] [smallint] NOT NULL,
    [status] [nvarchar](30) NOT NULL,
    [command] [nvarchar](32) NOT NULL,
    [blocking_session_id] [smallint] NULL,
    [wait_type] [nvarchar](60) NULL,
    [exec_context_id] [int] NULL,
    [task_state] [nvarchar](60) NULL,
    [text] [nvarchar](max) NULL
) ON [PRIMARY]

GO

--Collecting raw data
DECLARE @howManyTimes int=0
DECLARE @WaitSec char(2) = '30'
DECLARE @Delay char(8)
SET @Delay = '00:' + '00:' +@WaitSec

WHILE(@howManyTimes<101)
BEGIN
INSERT INTO [DbaDB].[dbo].[PerThreadcollection]

SELECT
        GETDATE(),
       er.session_id,
       er.status,
       er.command,
       er.blocking_session_id,
       er.wait_type,
       ot.exec_context_id,
       ot.task_state,
       st.text
FROM   sys.dm_exec_requests er
       JOIN sys.dm_os_tasks ot
         ON ( er.session_id = ot.session_id )
       CROSS apply sys.Dm_exec_sql_text(er.sql_handle) st
WHERE  er.session_id IN (SELECT session_id
                         FROM   sys.dm_os_tasks
                         GROUP  BY session_id
                         HAVING Count(exec_context_id) > 1)
WAITFOR DELAY @Delay
SET @howManyTimes = @howManyTimes + 1
END
GO

 ;WITH MinExecution (sessionID, execContextId, queryText, mintime)
 AS
 (
  Select session_id, exec_context_id, [text], MIN(timestamp) AS [MinTime]
  FROM [DbaDB].[dbo].[PerThreadcollection]
  WHERE exec_context_id <>0
  GROUP BY session_id, exec_context_id, [text]
),

MaxExecution (sessionID, execContextId, queryText, maxtime)
 AS
 (
  SELECT session_id, exec_context_id, [text], MAX(timestamp) AS [MaxTime]
  FROM [DbaDB].[dbo].[PerThreadcollection]
  WHERE exec_context_id <>0
  GROUP BY session_id, exec_context_id, [text]
)

SELECT sessionId, queryText, (MAX(duration)-MIN(duration)) AS [deltaInSeconds] from
(
SELECT
  mi.sessionID, mi.execContextId,mi.queryText ,DATEDIFF(ss, minTime, maxTime) AS [duration]
FROM MinExecution AS mi
JOIN MaxExecution AS mx
ON mi.sessionID=mx.sessionID
  AND mi.execContextId=mx.execContextId
  AND mi.queryText=mx.queryText) as t
GROUP BY sessionId, queryText
ORDER BY [deltaInSeconds] DESC

GO


Are SQL Server in-place upgrades as ill advised as they used to be? I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade.

I'm currently upgrading my 2008 R2 DEV and TEST systems to SQL Server 2012 and need to use the same hardware. The thought of not having to restore my Reporting services configuration is very attractive and i'm really up against the wall time wise. There is no analysis services involved or anything unusual or non-standard - only the database engine and reporting services are installed.

Has anyone experienced serious issues with in-place upgrades? Or should I reevaluate my position about in-place upgrades?

Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012.

A Longer Answer on SQL Server Upgrades/Migrations

So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said - some of my clients for various reasons have had no choice but to do an in-place and really since SQL Server 2005, in-place upgrades haven't been as bad as they used to be.

Why I Prefer A Migration to an In-Place Upgrade

Easier Rollback - If something goes wrong you can rollback by simple saying "we aborted the upgrade.. Please change connection strings to old server while we resolve this". With an in-place you are fixing it or you are down.
Refresh Hardware - Hardware changes rapidly. You can easily get stuck on hardware that was right for your company 4 years ago but not right for today and the next four years with an in-place upgrade. You will likely have to do a migration at some point anyway for the new hardware.
Feel Better - Sure... This one is subjective, but it feels good knowing you are starting with a new OS installation, a new SQL installation with no cobwebs from the person on the job before you (or you before you knew what you knew today) that could possibly cause you headaches in the future.
New OS - A migration gives you a chance to start with a new OS version if you aren't on the latest and greatest today.
You Can Test It - Ever want to get a set of baselines on a new machine before you install SQL and cloud it up with databases and usage? You can do that now.
Sometimes it is easier to sneak in best practices - Maybe the SQL Server service account was a local admin. Maybe Builtin Administrators is in the SA server role. Maybe things have been sort of hacked together to make it work before. You can fix that all and start fresh.
Free test environment and extra sleep - It is a great benefit to have an environment you can work in ahead of the actual cutover day when you make this new environment live. Doing a migration to a new environment means you can build it during business hours, well ahead of your actual cutover day and test it in many ways ahead of time. You can run full regression testing on all applications and systems for days and have a great peace of mind before you actually do the final set of restores/attaches and cutover all applications and access to the new environment.
You don't have to do it all at once - A very common situation I bump into is an environment that is trying to consolidate to just a few instances. Perhaps one per version, perhaps one per "tier" and version. A lot of these projects have different timelines for various applications and databases based on testing, project plans and vendor certification timeliness. Doing the migration means you can move those databases that are ready, when they are ready and still handle requests for those databases that can't move for one reason or another.

Mind you I am not saying you have to do this as a migration. In-Place works and it works well if you are not planning on the buying new hardware in your budget and can't do that for this upgrade. The support in the upgrade process is so much better than it was in the 6.5 days so you are not putting yourself in a bad position doing this.

If you do plan on doing in-place for dev/test but want to do a migration for production you might consider doing at least one migration before production. This way you can work out your checklist ahead of time and deal with any potential issues that you weren't thinking of.

Attach/Detach vs. Backup/Restore for Migrations

If you decide to go with the migration approach, there is still one more decision you may still have a debate over and that is how you move your database to the new environment. You can either detach your database from the old server and attach it to the new or back it up and restore it there.

I prefer backup/restore. The biggest advantage I hear about detach/attach is that it saves some time. For me backup/restore wins for a few reasons:

Keep the old accessible - This allows you to still have an accessible database on the source server. detach/attach should do the same, but it will require a few steps and there is room for human error with detach/attach that could complicate this.
You are guaranteeing that you have a backup - Instead of just taking a database from a detach and potentially forgetting a backup step, you've made sure that you've taken that backup.
Human error - If you delete the wrong file, forget where you are sending something or otherwise mess up your steps, you risk much by moving the data and log files around for your database. Now you can mitigate this by copying instead of cutting (and if you do detach, you should get out of the cut and paste habit) but you could stil mess up. SQL Server is no longer locking those files and it's just too easy to delete a file accidentally for me to risk it.
It isn't really that slower - Taking a backup and copying it is a bit more time, but it isn't that much that I am willing to pay the extra risk for it. In fact - using full recovery model and log backups, you can take the downtime down even lower for cutovers as described below in "How to Make the Migration Approach Faster"

If you do decide to do the backup/restore - that means your old source database will still be online. I like to bring that database offline after taking the backup. I sometimes go a step further and take the whole SQL instance offline after I've scripted out security, jobs, linked server, certificates, database mail settings, and other instance wide information. This avoids an issue during testing where someone says "Everything looks great!" only to realize a day or two later that they have been talking to the old database on the old server. Taking those databases offline or the whole instance offline allows you to prevent those false positives and the mess they make.

How to Make the Migration Approach Faster

You can minimize the downtime required for the cutover from an old to new environment for a busy production environment with little downtime by utilizing the full recovery model. Basically - stage the environment you are migrating to by restoring the latest full backup, any differential backups and any already taken log backups specifying NORECOVERY - then all you will have to do for the final cut over is restore the log backups that were not yet restored and the final log backup you wish to restore specifying WITH RECOVERY. This way for a large database, the actual cutover downtime window can be drastically minimized by paying for the cost of the full, differential and most log restores ahead of the downtime window. Thanks to Tao for pointing this out in the comments!

How to Make the In-Place Upgrade Safer

A few things you can do to improve your experience and results when choosing the in-place approach.

Backup - Take appropriate backups of all user and system databases of your environment ahead of time and ensure they are good (I'm paranoid.. I actually would restore them someplace first to really know they are good.. May be wasting your time.. But you may thank yourself in the event of a disaster).. Script out any configuration information about the SQL and OS installation at that environment.
Test things well before you start - Verify that you have a good environment and good databases. You should be doing things like looking at the error logs and running DBCC CHECKDB on a regular basis, but before doing an in-place upgrade is a great time to start. Fix any issues ahead of time.
Ensure OS health - Don't just make sure that SQL is healthy, make sure your server is healthy. Any gnarly errors in your system or application error event logs? How is your free space?
Prepare for the worst - I had a blog post series awhile ago that went on the premise that if you aren't preparing for failure - you are actually preparing to fail.. I still believe that. So think through the issues you may have and deal with them accordingly ahead of time. Get yourself in the "failure" mindset and you will think of things that you wouldn't have otherwise.

The Importance of Upgrade or Migration Checklists

If you do decide to do an upgrade (whether in place or migration) you should seriously consider creating a checklist and using this checklist in each environment. You should include a bunch of things in this checklist, not the least of which:

At the Start - Do some things like perform a test upgrade, test your applications on the latest Database Compatibility level and consider running a tool like the SQL Server Upgrade Advisor ahead of time to see what sort of tasks you need to complete before doing the SQL Server upgrade or migration.
Pre Steps - Cleanup, OS tasks, patching ahead of time, preparing applications for the upgrade (clean shutdowns, connection string work), backups, etc.
Upgrade/Migration Steps - Everything you have to do for the upgrade or migration to succeed and in the right order. Installation, changing (or not changing depending on your testing and approach) compatibility mode changes to databases, etc.
Post Migration/Upgrade Steps - Various tests, post new version or new server configuration options, best practice implementation, security changes, etc.
Rollback Steps - All along the way you should have rollback steps and milestones. If you get this far and this happens, what will you do? What are "do a full rollback" criteria? And how do you do that rollback (reverse connection string changes, change back settings, go back to the old version, reinstall if an in place, point back to the old server if a migration, etc.)

And then have the person who will be doing the production upgrade follow the checklist in some environment other than production - especially one that closes resembles production if possible ("South of prod", as I say...) and note any issues or points where they had to divert from the checklist or improvise because of a lack in the checklist. Then get the changes merged in and have fun with your production change.

I can not over stress the importance of testing thoroughly post migration or upgrade and ahead of your migration enough. Making a rollback decision in the midst of an upgrade should be an easy one - especially during a migration. If there is something uncomfortable, rollback and figure it out if you can't troubleshoot it effectively and reliably in the heat of the migration. Once you are live on this new environment and users connect - rollback becomes a difficult task. You can't restore a SQL Server database to an earlier version. That means manual work and data migrations. I always wait a couple weeks to kill the old environment but you should do all you can to avoid needing that old environment by finding all of your issues before your live users ever touch the new environment. Preferably before you even kick off the upgrade/migration.

Quick note about SQL Server Reporting Services migration/upgrade
Migrating an SSRS installation isn't quite a herculean task that many think it is. This technet/books online article is actually quite handy.  One of the most important admonitions in that article is "Back up the encryption keys" especially if you have a lot of saved sensitive information like scheduled report e-mail recipient e-mail addresses, connection information for a multitude of connections, etc. You can ask one of my clients from awhile back how important that is. They know because I messed that step up and spent quite a lot of time modifying report schedules and connection string permissions.


Optimize schema and query for overlapping date ranges query We have a database where we track the price of products from different sellers. If I strip everythin else it looks like this:

I need to make a report where given a date range I have to return a row for every day and the prices for every seller. There will be days that some seller don't have a price for a product and there will be days that a seller will have more than one price for a given product.

I'm doing it by creating a temp table with the date ranges then join them with the sellers and prices for this but once product_price have a couple thousands rows it get VERY slow (about 4'' to return a 200 rows) and I don't know how to optimize it to get it fast enought.

I can't think a workaround for the temp table nor which index should I create.

schema and query here: DB-fiddle

You have only primary keys for tables and indexes related to the FK restrictions but no indexes related to the queries you run.

SELECT qtm.date_i
     , s.name
     , pp.*
  FROM query_time_window   AS qtm
  JOIN seller              AS s   -- some ON condition is strictly recommended
  LEFT JOIN product_price  AS pp  ON pp.seller_id = s.id -- most conditions are moved to WHERE
 WHERE ( pp.product_id = 1 OR pp.product_id IS NULL )
   AND ( pp.created_at <= qtm.date_f   -- I've changed '!a>b' to 'a<=b'
         AND ( pp.cancelled_at IS NULL
               OR pp.cancelled_at >= qtm.date_i) -- I've changed '!a<b' to 'a>=b'
       )
 ORDER BY qtm.date_i, s.id;

Table query_time_window should have an index (date_i, date_f) or (date_f, date_i)
Table product_price should have an additional index (product_id, seller_id, created_at, cancelled_at) - an exact order of the columns depend on the real data. You can try different combinations and choose the fastest one.


Can I disable the redo log for a DELETE query or a whole transaction? I'm working on an existing database, whose structure I can't change. I need to clear (empty) several InnoDB tables (that I can't TRUNCATE due to references in other tables' FK constraints), but I have no interest in any recovery - i.e. if a crash occurs during those operations, I'll clear those tables again on the first occasion.

Can I tell MySQL to not create the redo log about those queries?

I am concerned about speed, as the first thing that can be noted is the time the operation takes. But, space too, I guess. Tables can store 20k-1M rows depending on the specific installation (same schema structure, different usages). I am operating on a bunch of table whose data are related (i.e. products, products_images, products_votes, ...), so FK are not an issue because all data will be deleted consistently without the need of any additional check.

If I understand correctly, the problem is:

We have one or more tables (lets call them "parents") that we want to empty, either with DELETE or TRUNCATE. There are several other tables ("children") that have FOREING KEY constraints which reference the first table(s) or some other "child" and we want to empty those tables as well.

If we DELETE, the operation will succeed, as all the FKs have been defined with ON DELETE CASCADE actions. But it will be a very slow operation.
If we TRUNCATE, the operation fails due to the FKs. It's not allowed to TRUNCATE a table that other tables reference it.

To solve this conundrum, I don't think that messing with the redo log is advisable (if at all possible).

I suggest the more simple:

ALTER the children tables and DROP the offending FOREIGN KEY constraints.
TRUNCATE all the tables.
ALTER the children tables and add the constraints again.

If some reason altering the tables even temporarily is forbidden, you should at least follow MySQL's guidelines about optimizing the redo logs: Optimizing InnoDB Redo Logging


Before Insert Trigger not firing DELIMITER $$
CREATE TRIGGER `merchant_insert` BEFORE INSERT ON `merchant_table` FOR EACH ROW
BEGIN
  INSERT INTO merchant_seq VALUES (NULL);
  SET NEW.merchant_id = CONCAT('MC', LPAD(LAST_INSERT_ID(), 10, '0'));
END $$
DELIMITER ;

INSERT INTO merchant_seq VALUES (NULL);
SET @last_ID = (SELECT MAX(merchant_id) FROM merchant_seq);
SET NEW.merchant_id = CONCAT('MC', LPAD(@last_ID, 10, '0'));


Can IMPLICIT_TRANSACTIONS set on database level? When I have to investigate problems caused by IMPLICIT_TRANSACTIONS ON, can I focus on Client tools setting IMPLICIT_TRANSACTIONS ON or ANSI_DEFAULTS ON or can this be set for the whole database affecting all clients?

I can avoid the problem by changing my standard script header to

SET ANSI_DEFAULTS ON
SET IMPLICIT_TRANSACTIONS  OFF

but at which levels session, database, server can this setting be influenced?

Edit:

I found this in msdn

it seems to be possible to set this on server level.

In the Server Properties dialog (Connections Page) I found the option changed and scripted it:

implicit transactions on:

EXEC sys.sp_configure N'user options', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO

implicit transactions off:

EXEC sys.sp_configure N'user options', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

But executing

exec('dbcc useroptions')

in SSMS didn't reflect the change. The server setting seems to be somehow overwritten for such connections.

Now I have to repose the question,

are there clients which do not overwrite the server setting and can crash when the server setting is changed.

Implicit transactions is a connection setting, it'll be set by the client tools and/or in the scripts only.


SQL Server 2012 copy database failed I want to make a copy of database in the same SqlServer .
so, When i using the Copy Database Wizard, it throw error:
( I did this steps with a test DB and it works fine !!!!)

config:

  sa user

  Method:"Use the SQL Management Object method"

  Chose new name for destination database.

error:

   TITLE: Copy Database Wizard

  The job failed.  Check the event log on the destination server for
  details.

  ------------------------------ BUTTONS:

  OK

in event log :

  System

  Provider

  [ Name]  SQLSERVERAGENT

  EventID 208

  [ Qualifiers]  16384
   Level 3
   Task 3
   Keywords 0x80000000000000

  TimeCreated

  [ SystemTime]  2014-05-07T06:23:11.000000000Z
   EventRecordID 123672
   Channel Application
   Computer Server1
   Security
  EventData

  CDW_Server1_Server1_3     0x666DE807F406D7438C65B09171211D7B
  Failed     2014-05-07 10:52:50     The job failed. The Job was invoked
  by User sa. The last step to run was step 1
  (CDW_Server1_Server1_3_Step).

last lines of log file:

  OnProgress,Server1,NT
  Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects
  Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,Database transfer failed for 1
  database(s). OnProgress,Server1,NT
  Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects
  Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,100,0x,Transfer objects finished
  execution. OnTaskFailed,Server1,NT
  Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects
  Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,(null)
  OnPostExecute,Server1,NT
  Service\SQLSERVERAGENT,Server1_Server1_Transfer Objects
  Task,{066BD090-26F3-45D8-AD60-B207D56D44CE},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,(null) OnWarning,Server1,NT
  Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,-2147381246,0x,SSIS Warning Code
  DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but
  the number of errors raised (1) reached the maximum allowed (1);
  resulting in failure. This occurs when the number of errors reaches
  the number specified in MaximumErrorCount. Change the
  MaximumErrorCount or fix the errors.

  OnPostExecute,Server1,NT
  Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,(null) DiagnosticEx,Server1,NT
  Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,0,0x,104546304
  PackageEnd,Server1,NT
  Service\SQLSERVERAGENT,CDW_Server1_Server1_1,{45A6144C-8DDD-49A6-A6BA-AE81E24826D5},{1CF7B713-F747-45FB-8936-5522651E0C7A},5/7/2014
  10:08:46 AM,5/7/2014 10:08:46 AM,1,0x,End of package execution.

It might be easier to simply backup the database to a .bak and then create a new database from a .bak restore.

BACKUP DATABASE [aaa] TO DISK = N'E:\aaa.bak'

Then check the logical names and locations for the current files in the .bak:

Restore filelistonly from disk ='E:\aaa.bak'

And finally restore the database, renaming the files to make sure you don't overwrite your existing database

restore database Newdatabasename
FROM disk = 'E:\aaa.bak'
WITH replace,
MOVE 'Logical data name' TO 'E:\Newdatabasename.MDF',
MOVE 'Logical log name' TO 'E:\Newdatabasename.LDF',
recovery --force


Scriptable command line access to SQL-Server from linux Does anyone know of a ready made1 command line tool that would allow me to connect from a linux client to a sql-server?

I want to be able to run arbitrary queries, but most of the time I want to be able to take a database dump and then restore it.

This has to be scriptable as it will integrate in an automated build environment.

1FYI: My only other alternative is to write something. I'll probably use perl and DBI

I found tsql and while its main purpose may lie elsewhere it covers my needs.

It is included in the EPEL package. I installed it with this:
rpm -Uvh http://ftp.df.lth.se/pub/fedora-epel/6/x86_64/epel-release-6-7.noarch.rpm

FreeTDS(May 14, 2011)                                    FreeTDS(May 14, 2011)

NAME
       tsql - utility to test FreeTDS connections and queries

SYNOPSIS
       tsql   { -S servername [-I interface] | -H hostname -p port }
              -U username [-P password] [-o options]

       tsql   -C

DESCRIPTION
       tsql  is  a diagnostic tool provided as part of FreeTDS. It uses the TDS protocol directly to con-
       nect to Sybase or Microsoft SQL Servers, and allows the user to issue queries that test the  capa-
       bilities of FreeTDS.

       tsql  is  *not* a replacement for a complete isql, such as sqsh (www.sqsh.org).  It is designed to
       rely on the lowest level FreeTDS library, tdslib, as a way to isolate potential bugs in the proto-
       col implementation.


clustered index got disabled suddenly I have a large database with size of 40 GB and a one of the main tables which have row count of 3763071 rows and name of "XXX" and that table has a clustered index which is the primary key

suddenly that clustered index got disabled and the queries stopped

and i have a huge concurrent access on that table
And i have a full text index which got disabled too on the same table
and when i opened the logs found that the sql got some updates just before that problem. I am using dot net framework 4.5 and entity framework as ORM and sql server 2014 .so i'm asking what could cause that problem to avoid it again??
and one another question i have more than 25 index on that table
so is it bad or good to have that number of indexes which have repeated columns?

I'm sure, by now, you've rebuilt your clustering index and your 25 non-clustered indexes as well.  While it might be difficult to determine why your clustering index got disabled in the first place, you could try implementing one or more server DDL triggers that would fire after an ALTER INDEX command and interrogate the command for the word 'disable' and roll the transaction back - (and wait for someone or some process to complain about that action).

Example here:

--DDL trigger to prevent index disable
USE [master]
GO
alter TRIGGER [xx_TestDisable]
ON ALL SERVER
FOR ALTER_INDEX as

declare @text nvarchar(max)

SET @text = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

if @text like '%disable%'
    rollback
GO

ENABLE TRIGGER [xx_TestDisable] ON ALL SERVER
GO

--Testing to see if the DDL trigger rolls back a disable
CREATE TABLE T (X INT CONSTRAINT IX_Index PRIMARY KEY);

ALTER INDEX IX_Index ON T DISABLE;

SELECT *
FROM T

As far as your 25 indexes, I think I'll borrow a quote that is attributed to Albert Einstein (paraphrased) - “Everything should be made as simple as possible, but no simpler.”

If you 'need' 25 indexes, then you 'need' 25 indexes.

Only you can decide which indexes should be created and maintained, but you should definitely use sys.dm_db_index_usage_stats to see how active your indexes are. Here is a good start.


Compression on a heap The follow is a paragraph from Microsoft Docs:

  New pages allocated in a heap as part of DML operations will not use PAGE compression until the heap is rebuilt. Rebuild the heap by removing and reapplying compression, or by creating and removing a clustered index.

I can't figure out why this is the case. If I have a heap with a specified compression setting, why wouldn't it be applied to a page that belongs to the table?

Thanks

While I don't know the specific internal mechanism(s) that are responsible for the differences, I can say that Heaps are managed (internally) slightly differently than Clustered Indexes (and possibly also Nonclustered Indexes) :

Deleting rows from a Heap such that one or more data pages are empty (no allocated rows) does not necessarily free up that space. You will likely need to either create, and then drop, a Clustered Index on the table, or call ALTER TABLE [TableName] REBUILD; (as of SQL Server 2014 ?). Please see the Microsoft Docs page for DELETE for more details and options.
Inserting individual rows (i.e. not a set-based INSERT) into a Heap does not fill the data pages as fully as it does with Clustered Indexes. Clustered Indexes will fit rows as long as there is space for the row (data and row-overhead) plus the 2-byte overhead of the slot array. Data pages in Heaps, however, don't use the number of bytes left on the page, but instead use a very generalized indicator of how full the page is, and there aren't that many levels that are reported. The levels are something along the lines of: 0%, 20%, 50%, 80%, and 100% full. And it will switch over to 100% while there is still space for another row (and in fact, had those same number of rows been inserted in a set-based operation, then it would have filled the page as much as possible). Of course, just like with the DELETE operations, rebuilding the Heap will pack as many rows as will fit onto the data page.

Now consider the following information, taken from the "When Page Compression Occurs" section of the Microsoft Docs page for Page Compression Implementation:

  ... As data is added to the first data page, data is row-compressed. ... When the page is full, the next row to be added initiates the page compression operation. The whole page is reviewed; ...

Hence, it seems entirely inline with this other Heap behavior that they would require an ALTER TABLE REBUILD, CREATE / DROP of a Clustered Index, or a change in the Data Compression setting (all of which rebuild the heap) before the data pages are written optimally. If Heaps are not fully aware of "whole pages" (until the Heap is rebuilt) and don't know when the page is definitely full, then they wouldn't know when to initiate the page compression operation (when dealing with updates and single-row inserts).

Another technicality that would further limit some Heaps from auto-applying Page Compression (even if they otherwise could) is that applying the compression would require all Nonclustered Indexes for that Heap (if any exist) to be rebuilt. As that linked page for "Data Compression" also states:

  Changing the compression setting of a heap requires all nonclustered indexes on the table to be rebuilt so that they have pointers to the new row locations in the heap.

The "pointers" being referred to are the Row IDs (RIDs), which are a combination of: FileID, PageID, and slot/position on the page. These RIDs are copied into Nonclustered Indexes. Being a precise physical location, they are sometimes faster lookups than traversing a b-tree with the Clustered Index keys. But, one drawback of a physical location is that it can change, and that is the issue here. Clustered Indexes, however, do not suffer from this problem because their Key values are copied into Nonclustered Indexes as the pointer back to the Clustered Index. And Key values remain the same, even when their physical location changes.

Also see:

the "Managing Heaps" section of the Microsoft Docs page for Heaps (Tables without Clustered Indexes):

  To rebuild a heap to reclaim wasted space, create a clustered index on the heap, and then drop that clustered index.

the "Considerations for When You Use Row and Page Compression" section of the Microsoft Docs page for Data Compression:

  When a heap is configured for page-level compression, pages receive page-level compression only in the following ways:

  Data is bulk imported with bulk optimizations enabled.
  Data is inserted using INSERT INTO ... WITH (TABLOCK) syntax and the table does not have a nonclustered index.
  A table is rebuilt by executing the ALTER TABLE ... REBUILD statement with the PAGE compression option.

And the statement quoted in the question.


What is a construct in Oracle? I'm currently studying Oracle Architecture and Database Memory Structures. I'm a newbie in the subject and I just read that a "Shared Pool" is "a data structure that caches various constructs that can be shared among users". So my question is: what is the meaning of a "construct" in this context? Thanks!

In Oracle (and other RDBMSs), when an SQL statement is run for the first time, it is cached and if somebody else wants to run a similar statement, the plan is taken from the cache - or shared pool. This is called soft parsing and is less costly than going to the trouble of redoing it entirely.

So, the term "construct" in this context means an in memory structure (like a "C" struct) which (ideally) allows the database to manage user requests better, like a plan for an already executed query.

Obviously, having a plan (construct) in memory will improve the server's response time - no need to read disks and recheck indexes &c. which take time. This process is an entire science in its own right.

From here, "The shared pool is like a buffer for SQL statements.  Oracle's parsing algorithm ensures that identical SQL statements do not have to be parsed each time they're executed.  The shared pool  is used to store SQL statements, and it includes the following components"...


Why does SQL Server Management Studio crash? When I open SSMS I get this message:

  SSMS - SQL Server Management Studio has encountered a problem and needs to close. We are sorry for the inconvenience.

What can I do?

The issue persists after a reboot and I do not have any add-ins installed.

This is SQL Server 2008 R2 SSMS running on Windows XP.

SSMS was running without errors before on this machine.

SSMS was installed from SQL Server package not alone. Is it recommended to reinstall the package? Or how to install SSMS alone?

The same issue occurs for all users.

To fix the error, you should understand the actual error which is not displayed in the screenshot that you've provided in your question. You can view the actual error by reading the application events within the event viewer when the error occurs.

Start by writing down the timestamp when the error happens.
Open Event Viewer
View the application logs and match the error message with the timestamp when the error occurred.

It's pretty common knowledge that Microsoft displays friendly errors to users when errors occur by default which generally leads the end user to questions that cant be answered, friendly error messages only complicate questions. You need to view the source. Feel free to ask your question again so that a professional can provide you a better professional response from a professionally written question with all the required dependencies.


Why Oracle 19c Database EM Express Webpage is not opening in Microsoft Edge? I have recently installed Oracle Database 19c on my Laptop which has Windows 10 OS. Now, I want to open the localhost:5500/em page in Microsoft Edge browser, but when I try to do so, the webpage does not open and it says localhost refused to connect.
But in the SQLPLUS command prompt I am able to login successfully and execute queries.
Please help me resolving this issue.

I want to open the localhost:5500/em

What makes you think that the web site is being offered on port 5500?
I don't know about the Express version, but full-blown Enterprise Manager is provided by a running, Middleware process.  Is there a Window Service that you need to start (or, for whatever reason, has failed to start?)

... in the SQLPLUS command prompt I am able to login successfully ...

That's because the Database and the Web Site that you use to manage the Database are two completely separate things.


PostgreSQL query with subquery doesn't working as expected I'm trying to make a query that should return records based on a subquery. Here's an example:

  select ... where tablename like ('%subquery_result%');

The subquery is generating the string as expected:

appldb=# select '''%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%''';
   ?column?
---------------
 '%_p2019_09%'
(1 row)

Using the string above, records are returned:

select tablename from pg_tables
where tablename like '%_p2019_09%' limit 2;
      tablename
---------------------
 part_p2019_09_26
 part_p2019_09_29
(2 rows)

But when I use the complete query I have no return:

appldb=# select tablename
from pg_tables
where tablename like ( select '''%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%''' );
 tablename
-----------
(0 rows)

I have already tried to remove parentheses around the subquery. But the query got in error.

Did I miss any steps to ensure where clause to interpret the subquery?

You are including literal single quote marks into the LIKE query (as shown in the output of your first query).  These literal quote marks are not present in the data, so they do not match.

select tablename
from pg_tables
where tablename like ( select '%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%' );

including the select is harmless in this case, but it is not necessary:

select tablename
from pg_tables
where tablename like ( '%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%' );

You don't even need the parentheses around the whole thing, as || binds tighter than LIKE, but I didn't know that until I did the experiment.


ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes I have a databases with latin1 charachter set, I need to change tables character set to utf8, but I get error. the current character set is :

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

the table that I want to change the character set is :

mysql> describe spool;
+------------+---------------------+------+-----+-------------------+----------------+
| Field      | Type                | Null | Key | Default           | Extra          |
+------------+---------------------+------+-----+-------------------+----------------+
| username   | varchar(250)        | NO   | MUL | NULL              |                |
| xml        | text                | NO   |     | NULL              |                |
| seq        | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
| created_at | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+------------+---------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

when I want to change the character set I see bellow error :

mysql> ALTER TABLE spool CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

It's a company functioning database and I don't want to disturb the functionality, so I don't want to change the size of the varchar(250) type, I wonder if there is a solution to change this encoding.

mysql> show create table spool\G;
*************************** 1. row ***************************
       Table: spool
Create Table: CREATE TABLE `spool` (
  `username` varchar(250) NOT NULL,
  `xml` text NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23490775 DEFAULT CHARSET=utf8

Solved :

I solve this problem, by creating new table like this spool. when we want to create a table with utf8mb4 COLLATE utf8mb4_unicode_ci character set we can not use varchar(250) because 250 * 4 = 1000 (4 byte per character in this new encoding). 1000 is more than max key 767 byte for this encoding. so I create a table spoolll with varchar(100) :

CREATE TABLE `spoollll` (
  `username` varchar(100) NOT NULL,
  `xml` text NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23492667 DEFAULT CHARSET=utf8

and now I change the character set :

mysql> ALTER TABLE spoollll CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

I would appreciate if someone comment on it and complete this issue.


query that does functionality of listagg without use of it I want to write a query so that it does functionality of listagg in oracle but I don't want to use it. Actually I don't want any function like lag and lead also. My table is-

Emp_id                          Emp_name                        Department
1                               bradley                             1
2                                david                              1
3                                 will                              2
4                                 john                              2
5                                 bale                              2
6                                 alan                              3
7                                 doug                              3
8                                 hagar                             4
9                                 carla                             4
10                                vashi                             1

I want output like this-

department                               emp_name
1                                        bradley,david, vashi
2                                         will ,john ,bale
3                                          alan, doug
4                                          hagar, carla

I have made a query for this but here I don't want to use sys_connect_by_path. My query is-

SELECT department,
       LTRIM(max(SYS_CONNECT_BY_PATH(emp_name,','))
       KEEP (DENSE_RANK LAST ORDER BY emp_name),',')AS employees
FROM   (SELECT department,
               emp_name,
               ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) AS emp,
               ROW_NUMBER() OVER (PARTITION BY department ORDER BY emp_name) -1 AS prev
        FROM   employee4)
GROUP BY department
CONNECT BY prev = PRIOR emp AND department = PRIOR department
START WITH emp = 1;

Can anyone help me?

You can use a recursive common table expression. I have used a tmp table to enumerate the employees:

with tmp (empname, department, rn) as (
        select empname, department
             , row_number() over (partition by department
                                  order by empname) as rn
        from employees
), cte (empname, department, rn) as (
        select cast(empname as varchar(100)) as empname
             , department, rn
        from tmp
        where rn = 1
        union all
        select rtrim(c.empname) || ', ' || t.empname
             , t.department, c.rn+1
        from tmp t
           , cte c
        where t.department = c.department
          and c.rn + 1 = t.rn
)
select empname, department
from cte c1
where rn = (select max(rn)
            from cte c2
            where c1.department = c2.department)


How can i insert the multiple data in table using the existance condition? I am able to insert the multiple data in table mytest using the below query. But how can i modify the below query so that everytime it checks the first_name and @id_for_test and if its unique, it does allow to insert into mytesttable.

 BEGIN
    DECLARE @id_for_test INT

    SELECT @id_for_test = ixp_tables_id
    FROM   mytables
    WHERE  table_name = 'test123'

    INSERT INTO mytest
    (
        first_name,
        table_id

    )

    VALUES('f1', @id_for_test),
          ('f2', @id_for_test),
          ('f3', @id_for_test),
          ('f4', @id_for_test),
          ('f5', @id_for_test),
          ('f6', @id_for_test)
END

Currently when i run this multiple time, it will insert the data multiple times. Instead i want,  it doesnot allow to insert the duplicate data matching the first_name and table_id.

INSERT dbo.mytest(first_name, table_id)
SELECT fn, ti FROM
(
  VALUES('f1', @id_for_test),
        ('f2', @id_for_test),
        ('f3', @id_for_test),
        ('f4', @id_for_test),
        ('f5', @id_for_test),
        ('f6', @id_for_test)
) AS x(fn, ti)
WHERE NOT EXISTS
(
  SELECT 1 FROM dbo.mytest
    WHERE first_name = x.fn
      AND table_id   = @id_for_test -- or = x.ti
);


What is the best way to store a lot of user-encrypted data? I'm about to develop an application that will mainly store user-encrypted data. Each piece of data being encrypted thanks to an AES key, itself encrypted with the public RSA key of each granted user.

Given that the volume of non-encrypted data is negligible (probably only users login, some dates and foreign keys), I wonder if "standard DBMS" such as PostgreSQL or MySQL would be a good choice.

Indeed, these DBMS are optimized to handle different types of data (text, integers, dates, ...), indexes, process calculus, aggregations and so on.

In my case, the vast majority of the data I will store would be large blocs of text (or maybe binary data). And most of the required calculus will be processed on the client side, after decryption.

Therefore, I won't either be able to parse my data. So even if I had a huge database, it wouldn't be qualified as "Big data". However, I wonder if MongoDb, MemSQL or whatever wouldn't be a more accurate choice. And if not, what would be the best DBMS and the best way to use it for my purpose ?

In other words, I think each DBMS have to make sacrifices in order to be more efficient on most important features. I also think the case detailed above is not that casual. So I assume there is a lot of features I don't need (or I can't use, such as FULLTEXT search for instance). On the other hand, I may need features that are discarded by "standard DBMS".

As a rule of thumb: If your data is well structured, well known (in advance) and of a limited size per entry (no mega BLOBs), relational databases are really good at storing it. Even if you don't use the advanced indexing features.

Managing space, especially empty space in data files, is a very hard problem to solve. Relational databases have been dealing with this for over 20 years now - and it is worth making use of them just for that. In addition to this, relational databases get you the benefits of a long history of performance tuning. They run highly optimised native code so you don't have to struggle with poor Java implementations, bad network stacks, overuse of memcpy, garbage collection, coarse locking and all the other pathologies new products (especially the noSQL stuff) tends to have.

To store encrypted data, use the BINARY data types. MSSQL, MySQL and Postgres all support these types. You may not want to do operations directly on these types (though you CAN do binary manipulation if you want to). Also note that it is relatively easy to implement the encryption/decryption in the database itself  as all of the above support crypto libraries. You will also benefit from indexing on the key/foreign columns so you can find your data fast. A relational database is an excellent key/value store for small value types - SQL Server will easily get you over 1-10M key/value lookups/sec even on a small box - I expect MySQL and PostGres would deliver results in the same ballpark.

It is also easy to find programmers who can query a relational database efficiently. SQL has been around for a very long time and it is an extraordinarily powerful language. MSSQL will even give you automated parallel execution. Some programmers wont "get" it - but if they don't, chances are they also won't grok parallelism or lambda expressions (two crucial skills of a modern coder).

On top of all of this goodness, you also get powerful backup and monitoring tools for all of the standard relational databases.

So basically, unless you have a REALLY good reason to use NoSQL - just use relational databases.


Assigning a Variable Generic NEW Value by Pressing a Postgres Trigger In the trigger body, how can I get a value of NEW by the field name?

This is what I want to do:

some_key = "some_column";

value := NEW[some_key];

You have to use dynamic SQL like this:

EXECUTE format('SELECT $1.%I', some_key) INTO value USING NEW;


Which would be some benefits of storing date ranges instead of single dates? I need to store large number of dates, along with with a price that corresponds to each date. I am considering two options: One is (a) to store a row for each date and price pair; the other is (b) to store a row with date range and price. For example:

Option a. A table that stores individual dates

CREATE TABLE Foo
(
    ID    INT,
    Date  SMALLDATETIME,
    Price DECIMAL
);

Option b. A table that stores date ranges

CREATE TABLE Bar
(
    ID         INT,
    RangeStart SMALLDATETIME,
    RangeEnd   SMALLDATETIME,
    Price      DECIMAL
);

Is option (b) really a good idea? The main reason for this is to decrease number of rows INSERTed. In this case, if a price is the same throught a year I would have, e.g.:

ID     RangeStart    RangeEnd     Price
--     ----------    ----------   -----
 1     2017-01-01    2017-12-31     100

instead of

ID      RangeStart    Price
---     ----------    -----
  1     2017-01-01      100
  2     2017-01-02       100
...........................
365     2017-12-31       100

Is there really a need for this, since there would be an additional overhead of converting date ranges to dates when used at an application program business layer?

I use only one date, the date the price took effect. That price remains in effect until the price changes. So if a price changes rarely, there will be few records; if the price changes often, there will be many records.

create table Prices(
  ID      int  not null,    -- identifies the item that is being priced
  EffDate date not null,    -- when this price took/takes place
  Price   decimal not null, -- the price
  ...,                      -- other item data
  constraint PK_Prices primary key( ID, EffDate )
);

Prices:
ID  EffDate     Price
... ...         ...
17  2016-01-01     90
17  2017-01-01    100
... ...         ...

select  *
from    Prices p
where   p.ID = 17
    and p.EffDate =(
        select  Max( EffDate )
        from    Prices
        where   ID = p.ID
            and EffDate <= Today() );

The query will return the current price, 90, every day it is executed this year. After the New Year, it will return the new current price, 100.

The query above asks the question, "What is the current price of item 17?" To ask the question, "What was/will be the price of item 17 on a particular date?" just replace "Today()" with the date.

For example, to see what the date will be on Jan 1, 2017:

select  *
from    Prices p
where   p.ID = 17
    and p.EffDate =(
        select  Max( EffDate )
        from    Prices
        where   ID = p.ID
            and EffDate <= "01/01/2017" );

Of course, using a variable instead of hard-coding the date would give you a single query which will provide the price at any time in the past, the present or any time in the future.


How to create unique index for month and year column? Does anyone have any idea how do i create unique index for both month and year column?

As in: JAN 2012 should be an unique index and also to aid in searching and retreiving of results by month and year.

Thanks

Assuming you have a column of type DATE or DATETIME2 (or DATETIME), you can define two computed, persisted columns for year and month and put an unique index on those:

ALTER TABLE dbo.YourTable
ADD DateMonth AS MONTH(YourDateColumn) PERSISTED

ALTER TABLE dbo.YourTable
ADD DateYear AS YEAR(YourDateColumn) PERSISTED

CREATE UNIQUE INDEX UIX_YourTable_MonthYear ON dbo.YourTable(DateMonth, DateYear)


Need help designing a query to pull a list of songs based on ranking and the last play date I have a database of a list of songs used during a performance.

The main table, tblSongs, has a Field that represents a Ranking, 1 being worst, 5 being best.  The table is linked to tblEvents which lists every time a song is performed.  tblEvents has fields EventID and Date.  The two tables are linked through tblSongsPlayed (as, obviously, it's a Many-To-Many relationship) which has two fields, SongID and EventID.

The database is in MS Access.

The part that gets complicated for me is to come up with a query that picks the songs as follows:

Show me all 5 star songs that haven't been played in the past two months AND
Show me all the 4 star songs that haven't been played in the past three months AND
Show me all the 3 star songs that haven't been played in the past five months

Wanted to respond that I see your answer and I'll check it out as soon as I can.  Thank you so much!

The reason I have three tables, and maybe I'm getting this wrong here, but you can have one song played on multiple occasions, and multiple songs are played during one event.  So, for example, we play SongID 1 through 5 on date 1, and then song 3 through 6 on date 2, and so forth.  So it seemed to be a many-to-many relationship, a unique song can be played at multiple events.

From what you've shown, each part of the query has unique aspect (only 5* songs, only 4* songs, etc.).

If that's true, I'd build each query on its own, then use UNION ALL to join them together. UNION ALL takes the results of two queries (with the same number and type of columns in the SELECT list), and gives you the results of both as a single result set (add another UNION ALL to do three queries, etc.)

Any sorting necessary must be done last, after the final query, and applies to the full result set.

If separate queries for each rating are advantageous, you could potentially call each query separately.  But especially in Access, UNION ALL queries are more expensive than a single query with a WHERE clause with multiple OR expressions: WHERE (Ranking = 5 AND ...) OR (Ranking = 4 AND ...).

UNION ALL queries are more useful when combing data from "incompatible" sources that cannot be done in a single query.  The Access visual query designer also does not work with UNIONs.


MySQL Replication and Triggers I have stumbled upon an interesting MySQL Error message that I do not really know how to interpret.

The setup:

There are two tables A and B.
When data is written or updated in the table A, then a trigger is writing data to the table B.
Operations happen on a Master database
Data is replicated to a slave server

Now, whenever I decide to update data in table A, then it is updated and the corresponding log message is written to table B. MySQL, however, spawns the following error message:

  Note: #1592 Unsafe statement written to the binary log using statement
  format since BINLOG_FORMAT = STATEMENT. The statement is unsafe
  because it uses a LIMIT clause. This is unsafe because the set of rows
  included cannot be predicted.

  Note: #1592 Unsafe statement written to the binary log using statement
  format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it
  invokes a trigger or a stored function that inserts into an
  AUTO_INCREMENT column. Inserted values cannot be logged correctly.

  Note: #1592 Unsafe statement written to the binary log using statement
  format since BINLOG_FORMAT = STATEMENT. Statements writing to a table
  with an auto-increment column after selecting from another table are
  unsafe because the order in which rows are retrieved determines what
  (if any) rows will be written. This order cannot be predicted and may
  differ on master and the slave.

Nothing happy to the eye. How can I avoid these?

After a lot of playing with the data the answer turned out to be NOT to insert data within a trigger into a table with an auto_increment column. Once I have shifted the id to be generated via the UUID() function all warnings went away.


How to auto assign ordinal ranking in mysql with as minimal queries for new rows? +----+---------+---------+-----------------+
| id | view_id | user_id | ordinal_ranking |
+----+---------+---------+-----------------+
|  1 |      10 |       1 |               1 |
|  2 |      10 |       2 |               1 |
|  3 |      11 |       1 |               2 |
|  4 |      11 |       2 |               2 |
+----+---------+---------+-----------------+

So suppose I want to insert new rows for view_id 12 and for both user_id 1 and 2, these 2 new rows should have ordinal ranking of 3.

How do I do that with minimal number of queries?

Bear in mind, I have many users who may all have different number of views assigned.

Another corollary to the problem is when I need to delete a particular view, say view 10, all the users affected should have the ordinal ranking re-ordered.

How do I do that also with minimal number of queries?

EDIT

A comment was made about the number of views per user. I would like to add that you may safely assume that there is a users table and one column stores the number of views for each user record.

I am also okay if the ordinal ranking is zero-based, ie the ranking is 0, 1, 2, 3, etc

I also prefer not to use stored procedures.

EDIT2:

+----+---------+---------+
| id | name | view_count |
+----+---------+---------+
|  1 |     Sal |       2 |
|  2 |     Sam |       2 |
+----+---------+---------+

Is the users table

the ordinal_ranking merely reflects the personal choice of a user with regards to the views she can view.

E.g. user 1 Sal likes to have view 10 to be ranked higher than view 11.

If you are content with adding and deleting a view_id per user_id, here is what I see

Adding View 12 To User 10

I see three queries

Insert new view into user with BogusRank
Move all rank down below NewRank
Change BogusRank to New Rank for new view

Here is the proposed code

SET @view_id = 12;
SET @user_id = 10;
SET @newrank = 3;
SET @defrank = 999999999;
INSERT INTO userviewranks
(view_id,user_id,ordinal_ranking)
VALUES (@view_id,@user_id,@defrank);
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking + 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking >= @newrank;
UPDATE userviewranks SET ordinal_ranking = @newrank
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking = @defrank;

Deleting View 12 From User 10

I see three queries

Retrieve Row With View to Delete
Move All View Up in Rank
Delete Row with Old View

Here is the proposed code

SET @view_id = 12;
SET @user_id = 10;
SELECT id,ordinal_ranking INTO @id_to_zap,@rank_to_zap FROM userviewranks
WHERE view_id = @view_id AND user_id = @user_id;
UPDATE userviewranks SET ordinal_ranking = ordinal_ranking - 1
WHERE view_id = @view_id AND user_id = @user_id AND ordinal_ranking > @rank_to_zap;
DELETE FROM userviewranks WHERE id = @id_to_zap;


Why would SQL Server automatically remove manually created indexes? What causes Microsoft SQL Server to remove manually created indexes automatically? Every index I create it removes within a few hours.

Here is the code I use to create the indexes. Affected views run much faster.  The index disappears / is removed within a few hours. The affected views run impossibly slow or fail.

CREATE NONCLUSTERED INDEX IX_Tablename_fieldname
ON dbo.Tablename (fieldname)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

SQL Server does not automatically* remove indexes - neither does automatically execute any other DDL statement that alters the structure of your tables, views, functions, indexes, etc.

If your indexes are truly dropped, then some user or application or job is doing it.

*: SQL Server on Azure can automatically remove indexes if the option is enabled. it does so if they are duplicates or have been unused for 6 months or so. Not on version 2008 though or any other desktop version.

To find out who or what, you have various options:

write a DDL trigger to catch the DROP INDEX statements (and even disallow them from deleting your index). See Ken Kim's answer.
SQL Trace
enable Audit
use XEvents (Extended Events)
use SQL Profiler
read the transaction log


Query Optimization required - Subquery using Inner Join Could anybody advise how can I optimize this query below please?

SELECT

IT.[iInternalTransactionID]
, IT.[iInternalAccountID]
, C.[iContractID]
, IT.[dtTransactionDate] AS TransactionDate
, (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName

...

-- ### I would like to optimize this by using inner join ##--
, (SELECT MAX(CPH.[iTermNo])
    FROM [tbl_ContractPaymentHistory] CPH
    WHERE CPH.[iContractID] = C.[iContractID]
    AND CPH.[iInternalAccountID] = @InternalAccountID
    AND CPH.[dtTransactionDate] = IT.[dtTransactionDate])  AS TermNo

, (SELECT (MAX(CPH.[iTermNo])  * MAX(CPH.[cMonthlyAmount]))
    FROM [tbl_ContractPaymentHistory] CPH
    WHERE CPH.[iContractID] = C.[iContractID]
    AND CPH.[iInternalAccountID] = @InternalAccountID
    AND CPH.[dtTransactionDate] = IT.[dtTransactionDate])  AS TotalAmount
-- ### end ### --
...

FROM
    [tbl_InternalTransactions] IT
    INNER JOIN [tbl_Contract] C ON C.[iContractID] = IT.[iContractID]
    INNER JOIN [tbl_Client] CL ON CL.[iClientID] = C.[iClientID]
    INNER JOIN [tbl_FinanceStructure] FS ON FS.[iFinanceStructureID] = C.[iFinanceStructureID]
    INNER JOIN [tbl_InternalAccount] IA ON IT.[iInternalAccountID] = IA.[iInternalAccountID]
    AND IA.[iInternalAccountID] = @InternalAccountID
WHERE
    IT.[dtTransactionDate] >= @FromDate
    AND IT.[dtTransactionDate] <= @ToDate

...

The query plan can be found here: http://pastebin.com/KHPdEzgi

I have tried to replace the subqueries with the following:

INNER JOIN (
SELECT
    MAX([iContractID]) AS [iContractID]
    , MAX([iTermNo]) AS [iTermNo]
    , MAX([cMonthlyAmount]) AS [cMonthlyAmount]
FROM [tbl_ContractPaymentHistory]
WHERE [iInternalAccountID] = @InternalAccountID
    AND [dtTransactionDate] = IT.[dtTransactionDate] -- not able to use this here
    AND [iContractID] = C.[iContractID]          -- not able to use this here
        ) CPH ON CPH.[iContractID] = C.[iContractID]

So I can select [iTermNo] and  [cMonthlyAmount]

However, I am not able to use columns from another tables in the inner join subquery. I am getting the following errors:
The multi-part identifier "IT.dtTransactionDate" could not be bound.
The multi-part identifier "C.[iContractID]" could not be bound.

Help would be much appreciated as the query takes an eternity to load at the moment. Thank you.

You could rewrite with a LEFT JOIN:

SELECT
  IT.[iInternalTransactionID]
, IT.[iInternalAccountID]
, C.[iContractID]
, IT.[dtTransactionDate] AS TransactionDate
, (CL.[sFirstName] + ' ' + CL.[sLastName]) AS ClientName
...
-- ### unchanged up to here ##--
, G.TermNo
, G.TermNo * G.MaxMonthlyAmount AS TotalAmount
-- ### end ### --

FROM
    [tbl_InternalTransactions] IT
    INNER JOIN [tbl_Contract] C ON C.[iContractID] = IT.[iContractID]
    INNER JOIN [tbl_Client] CL ON CL.[iClientID] = C.[iClientID]
    INNER JOIN [tbl_FinanceStructure] FS ON FS.[iFinanceStructureID] = C.[iFinanceStructureID]
    INNER JOIN [tbl_InternalAccount] IA ON IT.[iInternalAccountID] = IA.[iInternalAccountID]
    AND IA.[iInternalAccountID] = @InternalAccountID

--  ### the two subqueries converted to a LEFT JOIN ###
    LEFT JOIN
      ( SELECT CPH.iContractID
             , CPH.dtTransactionDate
             , MAX(CPH.iTermNo) AS TermNo
             , MAX(CPH.cMonthlyAmount) AS MaxMonthlyAmount
        FROM tbl_ContractPaymentHistory CPH
        WHERE CPH.iInternalAccountID = @InternalAccountID
        GROUP BY CPH.iContractID
               , CPH.dtTransactionDate
      ) G
      ON  G.iContractID = C.iContractID
      AND G.dtTransactionDate = IT.dtTransactionDate
-- ### end of changes ###

WHERE
    IT.[dtTransactionDate] >= @FromDate
    AND IT.[dtTransactionDate] <= @ToDate
...

or using OUTER APPLY:

--  ### the two subqueries converted to an OUTER APPLY ###
    OUTER APPLY
      ( SELECT MAX(CPH.iTermNo) AS TermNo
             , MAX(CPH.cMonthlyAmount) AS MaxMonthlyAmount
        FROM tbl_ContractPaymentHistory CPH
        WHERE CPH.iContractID = C.iContractID
          AND CPH.iInternalAccountID = @InternalAccountID
          AND CPH.dtTransactionDate = IT.dtTransactionDate
      ) G
-- ### end of changes ###

These are just rewritings along the lines you tried. Not at all sure if they improves efficiency. They will probably do but the main bottleneck may be elsewhere (indexes).


how to order a field based on two parts of the row value I have a field with contents of the form
22/4,
30/8,
30/5,
30/3

I want it ordered in the form
22/4,
30/3,
30/5,
30/8

with the part after / ordered if the part before / is same!

Please help!!

A way to order such string is

Step1: Create Split Function

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0
BREAK
END
RETURN
END

GO

Step2: split the string into tokens

create table #string
(
field varchar(500)
)

insert into #string
select '22/4, 30/3, 30/5, 30/8,40/9,40/3'

----------------Order by string Tokens returned by function
select *
from #string cross apply dbo.[fnSplit](field,',') atn
order by ltrim(rtrim(atn.Item ))

drop table #string


Multiple Oracle Instances --  is this a good practice? One of my clients has deployed our product's db into a Solaris machine which already has 3 instances of Oracle. So, right now there are 4 instances of Oracle running on the same machine. And now we're experiencing performance issues.

I have no access to other instances or the machine, and all the tools I have are alert.log, AWR, and ADDM. I know there's something related to multiple instances, but I cannot prove it.

So, my question is, have you experienced a similar situation? How should I deal with it?
How can I identify the cause of performance issues related to multiple instances?

Isaac,
preferably we run one instance on one server and implement the different applications as schemas and services in that single datatabase. If the server has enough memory, there won't be a problem, if the applications all behave like good Oracle citizens. As soon as there is an application that does not use bind variables there is a growing pain on the host. The same for applications that keep creating new connections every few seconds, instead of re-using existing connections. Things like auto commit also does not help to improve the situation.
What is the normal behavior of your application?
Does it do large updates?
When does it do a commit?
How much redo is generate/hour?
Does your app use bind variables?
In many cases, the location of the online redolog files is critical. If the app does generate a lot of redo (or competes with an other app that does this), the logwriter will slow down and cause a big performance degradation because the session is waiting for the logfile write to complete.
Give you customer a disk layout where the redo's are on dedicated raid-10 disks and see how much this helps.
Also find out the memory allocation situation. There might be a need for a fair redistribution of available memory.

I hope this helps


Oracle RAC failover not working correctly I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both -vip addresses.

In theory, the new SCAN feature should provide improved functionality over Transparent Application Failover (TAF), which required additional configuration in every client. With SCAN I can just connect to the SCAN address and I should be able to:

Connect to the database and check which instance I am connected to, eg (on SQL*Plus):

select instance_name, host_name from v$instance;

Shut down the instance I am connected to using shutdown abort (in a separate session)
On the same session where I performed step 1, if I re-run the same select statement, it should automatically bounce to the surviving node.

Instead, I am getting:

ORA-03113: end-of-file on communication channel
Process ID: XXXXX
Session ID: YYY Serial number: ZZ

What's going on?

  Note: After step 2, I can exit out of SQL*Plus and reconnect just fine. It just won't work if I try to stay on the same session.

If I modify the tnsnames.ora in the client to something like:

RAC =
  (DESCRIPTION =
    (LOAD_BALANCE = YES)
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
       (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5)
       )
    )
  )

Then it works the way I expect it to. And this is what had to be done prior to 11gR2, but SCAN is supposed to handle all this automatically, so a simple entry like:

RAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Should work the exact same way.

SCAN is supposed to give you connect-time failover, but not run-time failover (like TAF). Where did you get the (mis-)information that SCAN also means TAF?


Tempdb approaching disk max: Is it OK to wait? Today my tempdb pushed up to the edge of the disk allocated. Currently, said disk only has 10 MB left. I'm pretty sure some bad reports run by a colleague earlier in the day pushed up the tempdb to this size.

However, now I've got a tempdb size of 20932 MB and a space available of 20373 MB. So it seems like we've got plenty of space in there to work with. I'd rather wait until the weekend to do a restart and bring it back to a more reasonable size. Am I OK to let this ride, as long as something unusual doesn't happen again?

Working with SQL Server 2012 here. Thanks.

This isn't a big deal -- in fact, it's the way a lot of people handle tempdb in the first place:

Add multiple data files
Pre-size files equally to nearly fill tempdb drive
Set autogrowth to a small increment

If the space you have in there is empty and caused by a rogue report, I wouldn't worry about it, period.

(This does assume you've set up tempdb on a drive by itself, as is generally recommended. If you have other things on the drive that may require the space, you have to decide if there's a risk, and how to handle it).


Azure Data warehouse - User defined function issues Anyone here had luck with creating and using UDFs on Azure data warehouse database? I am in the middle of migrating an on-prem warehouse from SQL Server 2014 to Azure datawarehouse and I ran into an issue with UDFs.

CREATE FUNCTION dbo.fn_GetImpliedRate (@Multiple float, @term int)
RETURNS float
AS
BEGIN
    DECLARE @ImpInt float
    IF(@Term = 1)
        SET @ImpInt = (select [1] from  dbo.ImpliedRate where Multiple = @Multiple);
    IF(@Term = 2)
        SET @ImpInt = (select [2] from  dbo.ImpliedRate where Multiple = @Multiple);
    IF(@Term = 3)
        SET @ImpInt = (select [3] from  dbo.ImpliedRate where Multiple = @Multiple);
    IF(@Term = 4)
        SET @ImpInt = (select [4] from  dbo.ImpliedRate where Multiple = @Multiple);

RETURN @ImpInt

END;
GO

This UDF works perfectly on SQL Server 2014. When I create this on Azure data warehouse, it gets created but it doesn't work when I query it. It returns a NULL. I have verified obvious things like whether the target table exists etc. All check. I looked at CREATE FUNCTION documentation for Azure data warehouse and it has an example UDF that converts int to a decimal. This works flawlessly on Azure DW. The moment I write a simple function that has a select, it fails.  Unfortunately Azure's documentation here is not really helpful and I was wondering if any of you ran into this issue. If yes, how did you resolve?

I just tested another use case and it also doesn't work:

CREATE function [dbo].[fn_GetNumberBusinessDays]
(
    @StartDate datetime,
    @EndDate Datetime
)
returns int
as
begin

DECLARE @NDAYS INT = 0
SELECT @NDAYS =
  ISNULL( (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) - 1 ,0) + 1

 SELECT @NDAYS = @NDAYS - COUNT(*)
 FROM dbo.FedHolidays
 WHERE DateOfHoliday BETWEEN @StartDate AND @EndDate

 RETURN @NDAYS
end
GO

Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse):

  function_body
      Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function.

Could you double check that function is created in DW?


Why do these concurrent MySQL inserts deadlock? I am baffled by this deadlock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
150511 18:42:43
*** (1) TRANSACTION:
TRANSACTION B8972CA4, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 55103359, OS thread handle 0x7f6babefb700, query id 18803072369 db.example.com 127.0.0.1 application update
INSERT INTO users ( created_at, external_record_type, external_id, email ) VALUES ( '2015-05-11 18:42:43', 'person', '212999172', 'user1@example.com' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 1103450 n bits 320 index `index_users_on_external_record_type_and_external_id` of table `users`.`users` trx id B8972CA4 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION B8972CA5, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 55103302, OS thread handle 0x7f6bf2320700, query id 18803072370 db.example.com 127.0.0.1 application update
INSERT INTO users ( created_at, external_record_type, external_id, email ) VALUES ( '2015-05-11 18:42:43', 'person', '212999170', 'user2@example.com' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 18 page no 1103450 n bits 320 index `index_users_on_external_record_type_and_external_id` of table `users`.`users` trx id B8972CA5 lock_mode X locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 18 page no 1103450 n bits 320 index `index_users_on_external_record_type_and_external_id` of table `users`.`users` trx id B8972CA5 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

The schema:

CREATE TABLE users
(id int NOT NULL AUTO_INCREMENT,
 external_id varchar(255) NOT NULL,
 external_record_type varchar(255) NOT NULL,
 email varchar(255) NOT NULL,
 created_at datetime DEFAULT NULL,
 PRIMARY KEY(id),
 UNIQUE KEY index_users_on_external_record_type_and_external_id (external_record_type(8), external_id),
 KEY index_users_on_email (email))

I see that T1 and T2 both want the insert intention gap lock on the same gap, but I don't see why the transactions can't proceed sequentially. Moreover, I don't see why T2 acquires a non-insert-intention lock on the gap before trying for the insert-intention lock.

I'm using MySQL 5.5 of the Percona flavor.

I failed to understand the extent of the transactions.

Before I do the INSERT, I do a write-locking select on the external index value in both transactions. I would have expected T2 to block T1 at that point, but that is not the case, both transactions successfully acquire a lock on the gap. Neither transaction can promote its lock to insert intention; T1 will block when it tries, then T2 will deadlock when it tries, thereby letting T1 succeed.

The behavior I found, and continue to find surprising, is that write locks on index gap values aren't exclusive. While that's certainly a correct way to implement serializability, it carries an attendant risk of deadlock that you would forego if write locks on gap values were exclusive as they are for record values. I wonder if I continue to miss something fundamental.


Implementing a Circular Buffer (Sliding window) in MySQL I intend to store some Java objects in a MySQL database, accompanied by a timestamp. These objects should be kept in a Sliding Window fashion (also known as Circular Buffer), meaning that only the last N items from a specific type should be kept.

My general idea is a an INSERT trigger:

DELETE FROM pixels WHERE type="type_of_new_pixel"
                         AND id NOT IN
                         (SELECT id FROM pixels
                          WHERE type="type_of_new_pixel"
                          ORDER BY timestamp DESC LIMIT N);

Is triggger the right way to go? How do I add the trigger to the table?
In order to increase performance, I would like to activate the trigger every M INSERTs. I will have some extra items in my list, but that's OK. How do I implement this? Will a id % 100 == 0 check do?

Instead of a Trigger, how about a Stored Procedure?
Here is some sample data:
DROP DATABASE IF EXISTS adam_matan;
CREATE DATABASE adam_matan;
use adam_matan
CREATE TABLE pixels
(
   id int not null auto_increment,
   type VARCHAR(30),
   timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
   pixel_data BLOB,
   PRIMARY KEY (id),
   KEY type_timestamp_id_ndx (type,timestamp,id)
);
INSERT INTO pixels (type,timestamp,pixel_data) VALUES
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100));
SELECT * FROM pixels;

Here is it executed
mysql> SELECT * FROM pixels;
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
|  1 | type1 | 2012-07-05 00:47:02 | px338      |
|  2 | type2 | 2012-07-05 07:20:24 | px178      |
|  3 | type3 | 2012-07-05 09:03:07 | px337      |
|  4 | type1 | 2012-07-05 06:34:06 | px323      |
|  5 | type2 | 2012-07-05 15:58:22 | px403      |
|  6 | type3 | 2012-07-05 01:00:47 | px267      |
|  7 | type1 | 2012-07-05 16:08:21 | px541      |
|  8 | type2 | 2012-07-05 06:10:06 | px687      |
|  9 | type3 | 2012-07-04 16:35:29 | px994      |
| 10 | type1 | 2012-07-05 16:21:52 | px116      |
| 11 | type2 | 2012-07-05 14:14:51 | px449      |
| 12 | type3 | 2012-07-05 00:03:50 | px307      |
| 13 | type1 | 2012-07-05 13:37:46 | px890      |
| 14 | type2 | 2012-07-05 15:01:37 | px676      |
| 15 | type3 | 2012-07-05 15:29:27 | px334      |
| 16 | type1 | 2012-07-05 11:43:37 | px266      |
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 18 | type3 | 2012-07-04 19:47:46 | px771      |
| 19 | type1 | 2012-07-05 12:26:28 | px619      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 21 | type3 | 2012-07-05 15:03:14 | px575      |
| 22 | type1 | 2012-07-05 04:54:36 | px821      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 24 | type3 | 2012-07-04 22:56:23 | px236      |
+----+-------+---------------------+------------+
24 rows in set (0.00 sec)

mysql>

Here is the Stored Procedure
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
BEGIN

  DECLARE KeepPixels INT;

  SET KeepPixels = 5;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);

  DROP TABLE IF EXISTS pixel_window;
  CREATE TEMPORARY TABLE pixel_window
  (id INT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

  SET @sqlstmt= CONCAT('INSERT INTO pixel_window ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt;
  EXECUTE st;
  DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);
  DELETE A.* FROM pixels A LEFT JOIN pixel_window B USING (id)
  WHERE A.type=GivenType AND B.id IS NULL;
  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;
  DROP TABLE IF EXISTS pixel_window;

END $$

DELIMITER ;

Let's INSERT four rows of pixel data into type2
mysql> CALL AddPixel('type2',CONCAT('px',FLOOR(RAND()*899)+100));
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
|  2 | type2 | 2012-07-05 07:20:24 | px178      |
|  5 | type2 | 2012-07-05 15:58:22 | px403      |
|  8 | type2 | 2012-07-05 06:10:06 | px687      |
| 11 | type2 | 2012-07-05 14:14:51 | px449      |
| 14 | type2 | 2012-07-05 15:01:37 | px676      |
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
+----+-------+---------------------+------------+
9 rows in set (0.08 sec)

+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 14 | type2 | 2012-07-05 15:01:37 | px676      |
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
+----+-------+---------------------+------------+
5 rows in set (10.23 sec)

Query OK, 0 rows affected (10.27 sec)

mysql> CALL AddPixel('type2',CONCAT('px',FLOOR(RAND()*899)+100));
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 14 | type2 | 2012-07-05 15:01:37 | px676      |
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
+----+-------+---------------------+------------+
6 rows in set (0.08 sec)

+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
+----+-------+---------------------+------------+
5 rows in set (10.25 sec)

Query OK, 0 rows affected (10.29 sec)

mysql> CALL AddPixel('type2',CONCAT('px',FLOOR(RAND()*899)+100));
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
| 27 | type2 | 2012-07-05 16:32:55 | px293      |
+----+-------+---------------------+------------+
6 rows in set (0.07 sec)

+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
| 27 | type2 | 2012-07-05 16:32:55 | px293      |
+----+-------+---------------------+------------+
5 rows in set (0.15 sec)

Query OK, 0 rows affected (0.19 sec)

mysql> CALL AddPixel('type2',CONCAT('px',FLOOR(RAND()*899)+100));
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
| 27 | type2 | 2012-07-05 16:32:55 | px293      |
| 28 | type2 | 2012-07-05 16:32:56 | px162      |
+----+-------+---------------------+------------+
6 rows in set (0.08 sec)

+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
| 27 | type2 | 2012-07-05 16:32:55 | px293      |
| 28 | type2 | 2012-07-05 16:32:56 | px162      |
+----+-------+---------------------+------------+
5 rows in set (0.16 sec)

Query OK, 0 rows affected (0.19 sec)

mysql>

If you want to keep another number, change the line KeepPixels = 5; to whatever number you need.
Give it a Try !!!
UPDATE 2012-07-05 17:52 EDT
If you want to limit the DELETEs to every 100 INSERTs, here it is:
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN

  DECLARE KeepPixels,DeleteLimit,MaxID INT;

  SET KeepPixels = 5;
  SET DeleteLimit = 100;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;

  DROP TABLE IF EXISTS pixel_window;
  CREATE TEMPORARY TABLE pixel_window
  (id INT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

  SET @sqlstmt= CONCAT('INSERT INTO pixel_window ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);
  DELETE A.* FROM pixels A LEFT JOIN pixel_window B USING (id)
  WHERE A.type=GivenType AND B.id IS NULL;
  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;
  DROP TABLE IF EXISTS pixel_window;

END $$

DELIMITER ;

You can change DeleteLimit to throttle DELETEs at any interval you want.