Update with CTE & OUTPUT blocks on concurrent calls I was having deadlock issues, so I started using this CTE trick that I read somewhere. No more deadlocks.

But now each client that I add slows down (blocks?) the stored procedure. E.g. a 1-2 second update with 1 client becomes 2-4 seconds for two clients. (This simple metaphor query runs at 0.001 seconds, but gets up to like 0.03 after a handful of clients -- so the issue is independent of my real-world implementation.)

Is it a locking issue? Do I need to wrap it in a (some kind of) transaction?

WITH UpdateView AS (
    SELECT TOP 1 W.*
    FROM [WidgetSandbox].[dbo].[Widgets] W
    INNER JOIN [WidgetSandbox].[dbo].[Sizes] S ON W.SizeId = S.Id
    WHERE W.StatusId = @availableStatusId
    AND W.ColorCode = @colorCode
    ORDER BY S.DiameterInches
)

UPDATE UpdateView
SET StatusId = @soldOutStatusId
OUTPUT INSERTED.Id INTO @outputIds;

SET @singleUpdatedId = (SELECT TOP 1 Id FROM @outputIds);

SELECT * FROM [WidgetSandbox].[dbo].[Widgets]
WHERE Id = @singleUpdatedId;

I wish I had a better idea of what the specific problem is, but as it is I'm just stuck....

DDL, if it helps: https://gist.github.com/RobertBaldini/3740c7bb85eea47d7fe63cb8602ac2d6

Repo: https://github.com/RobertBaldini/WidgetSandbox

Many of the issues you see are being caused by an inefficient execution plan:

Not that the supplied plan and query matches the question, but even so, I'm working with what was provided.

Anyway, you should implement the Name column data type changes (from nvarchar(max)) that I mentioned in your previous question. More importantly, you need to add the indexes I recommended, so the row to update can be found without sorting:

CREATE NONCLUSTERED INDEX IX_dbo_Sizes__DiameterInches
ON dbo.Sizes (DiameterInches);

CREATE NONCLUSTERED INDEX IX_dbo_Widgets__SizeId_ColorCode_StatusId__Name
ON dbo.Widgets (SizeId, ColorCode, StatusId)
INCLUDE (Name);

You can then update the status of the selected item and return the row affected with:

DECLARE
    -- Constant values guessed, replace with the real ones
    @availableStatusId integer = 1,
    @soldOutStatusId integer= 9,
    @colorCode nvarchar(6) = N'Red';

WITH UpdateView AS
(
    SELECT TOP (1)
        W.Id,
        W.Name,
        W.StatusId,
        W.ColorCode,
        W.SizeId
    FROM dbo.Widgets AS W WITH (UPDLOCK, ROWLOCK, READPAST)
    JOIN dbo.Sizes AS S
        ON W.SizeId = S.Id
    WHERE
        W.StatusId = @availableStatusId
        AND W.ColorCode = @colorCode
    ORDER BY
        S.DiameterInches
)
UPDATE UpdateView
SET StatusId = @soldOutStatusId
OUTPUT
    Inserted.Id,
    Inserted.Name,
    Inserted.StatusId,
    Inserted.ColorCode,
    Inserted.SizeId;

Note the UPDLOCK, ROWLOCK, READPAST hints there. These are the standard hints needed for concurrent FIFO access to queue-type tables. See Using Tables as Queues by Remus Rusanu for more information.

The execution plan shape you should get (with no sorts!) is:

As an aside, unless you have very good reasons, your database should not be using SET AUTO_CLOSE ON.


Recommended Data profiling tool for BI developer What recommended data profiling tool to retrieve a lot of information and enable to visulize the data into graphical statistics? The data is coming from the source database.

Your questions are quite broad, so this is difficult to answer definitively.  However, I'll give an overview of the field.

Data profiling is the process of analysing data to understand the semantics of the data and identify data quality issues that may need to be resolved.  Things that data profiling will address include:

Meanings of various reference data tables or coding schemes in the data.  This will almost always be necessary to understand what business logic will be necessary to implement calculations in the ETL processing.
Content of various data items in the source.  This may not be documented sufficiently well that the location or origin is immediately obvious.
Cardinality and relationships of the data.  In some cases foreign keys may not be present in the system, or not a feature of the data source (for example ISAM/VSAM files from older mainframe systems).
Data issues where the data is incorrect and needs to be fixed at source.
Reconciliation processes where the results of prototyping, ETL processing or other work need to be reconciled to the data source or some other control.

Often the data can be profiled by just poking around it with a database query tool such as SSMS and writing SQL queries directly against the source, or a copy of the data loaded into a staging or scratch area.  Desktop tools such as spreadsheets (pivot tables can be useful) or database systems such as MS-Access may also be helpful.

Purpose built data profiling tools such as Pandora X88 are also available, but these tend to be relatively expensive.  Often they are quite a hard sell, even on larger enterprise projects where they represent a tiny fraction of the overall budget.

Often your best approach is to simply copy all your source data into a staging database and poke around it.  This work can also form the basis of your staqging processes later on.


Thousands of After Insert and Update Triggers Causing Performance Issues We have a After Insert and Update trigger on one of our tables.  The trigger basically makes a json payload and enqueues it in a RabbitMQ system.
Today a large insert script was run on the table (over 50,000 inserts).  This scenario had not been tested or accounted for and now we are having performance problems on that database.
We notice that the RabbitMQ is having the records trickle in slowly over a long period of time.  Even though the data has been in place for a while (because it is an After Trigger).
It seems as if the After triggers have been queued up somehow and are very slowly working through the system.
How are the After Trigger events kept track of for execution?  Are they queued somewhere?  Is there a way I can clear them out?

All Triggers fire within the scope of the same Transaction that the INSERT statement that generated them runs in. Therefore if the Transaction of the INSERT statement you executed ran to completion, then so did the After Insert Trigger that fires from that INSERT statement. Here's some straight to the point information and tests that prove this out:

So in short a trigger executes in the context of the calling transaction and a rollback in a trigger will rollback the calling transaction.

So my guess is either your original INSERT is still executing (which can then be aborted and rolled back) or the issue is somewhere between after your Trigger runs and the mechanism you're using to dump the data into RabbitMQ.
You can use sp_WhoIsActive to determine if your INSERT statement is still running and to get the SPID of the process so you can abort it and rollback. To abort you'd have to run KILL 123 (where 123 = the SPID of your INSERT).

Side note, if by 50,000 inserts, you mean 50,000 records in one INSERT statement, then that's small and should be performant. If you actually mean 50,000 separate INSERT statements then that's a different story that can take much longer to complete the INSERT.


Geography data type and Upgrading from 2008 r2 to SQLServer 2014 I am getting a warning about a data type called geography.

Our system has 1 column of data type geography. I am trying to figure out if the upgrade will fail or just if an app may have an issue.

Warning about client side usage of GEOMETRY, GEOGRAPHY and HIERARCHYID

Anybody have an idea? Under Importance of error there is red ball with an X through it, so I assume the upgrade will fail. Is that correct? It says to fix before. I notice SQL Server 2014 has the geography type still, so I do not know what to think.

From export to csv it shows this:

  Database Server
  PreUpgrade
  Warning about client side usage of GEOMETRY, GEOGRAPHY and HIERARCHYID
  Upgrade Advisor detected usage SQL CLR Types
  Database: Metrics
  Column Name: Location

Upgrade should not fail (For this reason anyway).  As stated in the link you provided, this is an application issue and only if it is directly accessing the Microsoft.SqlServer.Types.dll.

The upgrade from 2008r2 to 2014 should work fine.


Joining tables with one to many relationship Hi I have a service ticketing system database (SQL Server 2008) which i want to get the hour elapsed for the tickets. One table contained the Ticket number and the created date/time while the other table contained the status log of each ticket.  My problem is how to get the time elapsed considering the last update from the other table.  Here is a sample table.

Table1
------
Ticket_Number   created
1234            06-12-2014 08:00:00

Table2
------
Ticket_Number        statuschange
1234              06-12-2014 08:30:00
1234              06-12-2014 08:45:00
1234              06-12-2014 10:00:00

You can use a cte to get the latest change and then join it with the ticket table.

DECLARE @Table1 TABLE (Ticket_Number int NOT NULL, created Datetime)
DECLARE @Table2 TABLE (Ticket_Number int NOT NULL, statuschange Datetime)

INSERT INTO @Table1 (Ticket_Number, created)
  VALUES (1234, '2014-12-06 8:00:00'),
         (1235, '2014-12-08 8:00:00'),
         (1236, '2014-12-08 8:00:00');

INSERT INTO @Table2 (Ticket_Number, statuschange)
  VALUES (1234, '2014-12-06 8:30:00'),
         (1234, '2014-12-06 8:45:00'),
         (1234, '2014-12-06 10:00:00'),
         (1235, '2014-12-08 9:30:00'),
         (1235, '2014-12-08 8:45:00'),
         (1235, '2014-12-08 8:22:00');

;WITH cte_lastChange
AS
(
  SELECT  Ticket_Number,
          statuschange,
          ROW_NUMBER() OVER (PARTITION BY Ticket_Number ORDER BY statuschange desc) AS RowNum
  FROM @Table2
--  WHERE Ticket_Number = 1234
)
SELECT  t1.Ticket_Number,
        t1.created AS ticket_started,
        t2.statuschange AS last_change,
        DATEDIFF(MINUTE, t1.created, t2.statuschange) AS differnece_minutes
FROM @Table1 t1
  INNER JOIN cte_lastChange t2 ON t1.Ticket_Number = t2.Ticket_Number
WHERE t2.RowNum = 1

This results in:

Ticket_Number ticket_started          last_change             differnece_minutes
------------- ----------------------- ----------------------- ------------------
1234          2014-12-06 08:00:00.000 2014-12-06 10:00:00.000 120
1235          2014-12-08 08:00:00.000 2014-12-08 09:30:00.000 90

If you need the time between changes you can extend the cte in this way:

;WITH cte_lastChange
AS
(
  SELECT  Ticket_Number,
          statuschange
  FROM @Table2
--  WHERE Ticket_Number = 1234
)
SELECT  t1.Ticket_Number,
        t1.created AS ticket_started,
        t2.statuschange AS last_change,
        DATEDIFF(MINUTE,
                 t1.created,
                 t2.statuschange) AS minutes_since_create,
        DATEDIFF(MINUTE,
                 LEAD(t2.statuschange, 1, t1.created) OVER(PARTITION BY t2.Ticket_Number ORDER BY t2.statuschange desc),
                 t2.statuschange) as minutes_since_last_step
FROM @Table1 t1
  INNER JOIN cte_lastChange t2 ON t1.Ticket_Number = t2.Ticket_Number
ORDER BY t1.Ticket_Number, last_change

That gives you the following output:

Ticket_Number ticket_started      last_change         minutes_create minutes_last_step
------------- ------------------- ------------------- -------------- -----------------
1234          2014-12-06 08:00:00 2014-12-06 08:30:00 30             30
1234          2014-12-06 08:00:00 2014-12-06 08:45:00 45             15
1234          2014-12-06 08:00:00 2014-12-06 10:00:00 120            75
1235          2014-12-08 08:00:00 2014-12-08 08:22:00 22             22
1235          2014-12-08 08:00:00 2014-12-08 08:45:00 45             23
1235          2014-12-08 08:00:00 2014-12-08 09:30:00 90             45

Please note that the LEAD() function is not available for the SQL Server 2008. This function is included from SQL Server 2012 onwards.

Lothar


What the best Configurationfor mysql using 4GB I'm trying to  improve my mysql performance. So I add more memory. But still slow in some part. Im most LEFT JOINs and UPDATE.

My database have 45GB of data.

My current mysql configuration is:

key_buffer_size = 256M
max_allowed_packet = 1024M
table_open_cache = 5000
sort_buffer_size = 4M
net_buffer_length = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M

innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 100

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 256M
write_buffer = 256M

The question is. This is the best configuration ?

MY Serve is running: Linux/Open Suse

4GB of ram

4 cores.

Terrible:  innodb_buffer_pool_size = 4G and 4GB of RAM.  You must not over-allocate RAM; performance suffers terribly.  Change that to 1500M.  (Even 80% is dangerously high for a mere 4GB of RAM.)

key_buffer_size = 256M -- If you are not using MyISAM tables, change this to 20M.

table_open_cache = 5000 -- If you don't have thousands of tables, change this to, say, 1500.

Yes 45GB of data will work with only 1.5G of buffer_pool.  But it may be slow.  Let's see some of the slow queries.  The solution may be as simple as adding a 'composite' index to help a query.

In general, one should not modify my.cnf settings other than the buffer_pool.  Raising values, in any cases, hurts performance.


Create database fails with Microsoft SQL Server Error 5133, Operating syste, error 5 (Access is denied) I just installed SQL server 2012 on Windows 2012 today. After the install, I changed the database settings to create database files in f:\mssql instead of the default. i verified the confired and running values.

Create database fails to create the .mdf and .log files with the following error -
Directory lookup for the file "f:\msql\data\test.mdf' failed with os error 5. (Access is denied).

This error occurs when I run sql server service as domain account. The domain account is an administrator and has full control on volume F.

If I run the sql server service as local system, I am able to create the database with mdf files on f:\ drive.

My sysadmin confirms and I have verified that the domain account has full control on f:

I look forward to your suggestions.

This is now fixed. SQL Server can noow see the additional volumes besides. C:. It required applying this small changes and a server reboot. It turned out to be a VMWare HotAdd bug. Here is direct link to the VM Knowledge base article: http://kb.vmware.com/selfservice/microsites/search.dolanguage=en_US&cmd=displayKC&externalId=1012225


cursor: pin S wait on X in 11g This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution.
Any advice will be greatly appreciated..

DB Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

AWR:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                            3,887          95.0
db file sequential read             207,505         125      1    3.1 User I/O
direct path read                     33,793          69      2    1.7 User I/O
cursor: pin S wait on X                   5          43   8650    1.1 Concurrenc
db file scattered read               34,229          39      1     .9 User I/O

Why does a wait event that occurred 5 times accounts for 1.1% of your database wait time concern you as a potential threat?  Is there some additional information that leads you to believe that this is a threat?  Obviously, something has to be in your top 5 wait events.  Is this event consistently one of the top wait events?  Or did it just appear on one AWR?

The particular wait generally occurs when one session is trying to pin a cursor while another session is parsing it.  Potentially, some of the 95% of the CPU related wait events could be the result of parsing (though you'd want to look at the number of hard parses elsewhere in the AWR).  If you have an OLTP system, you should really only be doing any quantity of hard parses immediately after a reboot-- everything else should be using prepared statements and, at most, doing a soft parse.  If you've got dynamic SQL somewhere in the system, removing that may help though it may also be a rather drastic step to save a few seconds.


mysql Relay log file not found error while changing mysql data directory I want to change data directory of my mysql server running 5.6.28 on centos 5.11. Its a slave to some master server. i have edited the config file ,moved the data contents from old directory to new required directory. When i tried to start the server it failed to start with below error in error log.

/usr/sbin/mysqld: File '/oldpath/to/log/mysql_bin.000220' not found
[ERROR] Failed to open log (file '/oldpath/to/log/mysql_bin.000220', errno 2)

so its still referring old binary log file path. I am not sure why its not referring new binary log file path where the mysql_bin.000220 present and getting failed to start. Any idea why server throwing this error and not starting?

From the manual:

  An absolute path remains unchanged; in such a case, the index must be edited manually to enable the new path or paths to be used. Previous to MySQL 5.6.5, manual intervention was required whenever relocating the binary log or relay log files. (Bug #11745230, Bug #12133)

So if your binlog index was written with the full paths, you need to update it manually or using the mysqlbinlogmove utility.


How can I specify the position for a new column in PostgreSQL? If I have a table with the columns:

id | name | created_date

and would like to add a column, I use:

alter table my_table add column email varchar(255)

Then the column is added after the created_date column.

Is there any way I can specify the position for the new column? e.g. so I can add it after name and get a table like:

id | name | email | created_date

ALTER TABLE ADD COLUMN will only add the new column at the end, as the last one.
In order to create a new column in another position you need to recreate the table and copy the data from the old/current table in this new table.


Compare the hash password value in mysql.user Say I have a password "Foobar1234". In MySQL database (5.6), the hash value is

*59C0D3B7983F06CF32F555AD04BAC66BCF952597

I want to try this outside of MySQL database so the SQL query isn't logged. Is the hash function known? I am using the default settings.

Here is the doc: https://dev.mysql.com/doc/refman/5.6/en/password-hashing.html

This question was explained several times in the DBA StackExchange

Nov 21, 2013 : Why are the passwords in MySQL (5.x) not salted? (by Michael - sqlbot)
Dec 26, 2013 : MYSQL - Unable to change 'old_passwords' variable
Apr 22, 2015 : Why are mysql password hashes internally saved with a star (asterisk)?

I actually learned the formula from a post from PalominoDB (later changed name to Blackbird and then was purchased by Pythian). The post was made Dec 4, 2011 entitled "Hashing Algorithm In MySQL PASSWORD()"

So if you want to execute this function yourself, it would look something like this:

SET @my_new_pass = 'WhateverIWantAsThePassword';
SET @my_new_hash = CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@my_new_pass))))) MySQLPWDHASH;

This would land in the MySQL history ~/.mysql_history but not in the binary logs.


Return Number Not String In Dynamic SQL This is my SQL syntax and (this is only a snippet unfortunately I am stuck running dynamic SQL in this instance) - but my issue is at the very end of my statement when I do the Select @numreturned what is returned is the SQL statement NOT the actual number that is returned when I run the Exec(@numreturned)

What must I alter in order to return the NUMBER instead of the SQL Statement?

Declare @numreturned varchar(max),
@fulllocation varchar(max) = 'nagadishi.mirama.dbo.Humizan'

Set @numreturned = 'Select Count(*) FROM '+@fulllocation+' '
                   +'WHERE ([Nishan] IS NULL) '

Print @numreturned
Exec(@numreturned)

Select @numreturned

You could do this with sp_executesql and assigning the variable to an output parameter

DECLARE @numreturned  INT;
DECLARE @sql NVARCHAR(MAX);
DECLARE @fulllocation NVARCHAR(MAX) = 'nagadishi.mirama.dbo.Humizan';

SET @sql = N'SELECT @numreturned = COUNT(*) FROM '+@fulllocation+' '
                   +N'WHERE ([Nishan] IS NULL)';

EXEC sys.sp_executesql @sql, N'@numreturned INT OUTPUT', @numreturned = @numreturned OUTPUT;
SELECT @numreturned;


Atler Table Alter Column Set Default FALSE: Why my alter table took 10 minutes to finish? I have run the following command and it took about 10 minutes to finish:

ALTER TABLE "bookings" ALTER COLUMN "local_direct_booking" SET DEFAULT FALSE

for a table bookings which had 111.834 rows (about 111K rows).

I don't believe this is a big table.

This is the size of the table:

lvd-am::DATABASE=> SELECT pg_size_pretty( pg_total_relation_size('bookings') );
 pg_size_pretty
----------------
 213 MB
(1 row)

Do you have any hint why this might have taken that long?

There must have been a concurrent long running transaction that used (perhaps read from) the table. Check pg_stat_activity for sessions with a state other than idle.


Oracle standby licence We're planning to buy new Oracle Database Enterprise Edition licence. We have two servers (Live and standby).

Our standby server should be configured as a physical standby, and will be used only in case of disaster.

According to this we should buy licence for both servers.

But do we need to Active Data Guard licence? Is it used buy the physical standby?

Short version: no.

Active Data Guard is an optional feature that allows you to open the standby database in read-only mode while applying logs from the primary. If you need the standby database only in case of disaster, then you don't need to buy this option.


DB2 window function - running total of X for preceding N minutes worth of rows Very new to window functions but think I get the idea.
I'm trying to get a rolling sum of one column in 10 minute intervals (determined from a separate timestamp column)
A working sample might would look like this:
EDIT: fiddle link

Filtered for T2 highlights it better:

There's plenty about the topic like this or this and so on but I'm having trouble finding a solution specific to my requirements.
If I didn't need it by 10 minute intervals it'd be very simple, by 10 rows (current + 9 preceding);
SELECT  ID, TIME, P/L,
    SUM(P/L) OVER (PARTITION BY ID
                     ORDER BY TIME
                     ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) as ROLLING
FROM TABLE

Maybe adding the 10 min constraint is not possible with the window function?
############## EDIT TWO ##############
Ok, so I've figure out the issue and managed to reproduce it in a fiddle
In the real table there are valid duplicate transactions (same ID and TIME), I thought I could pull back microseconds and solve this but unfortunately these aren't stored.
We end up with a weird backwards sum for the dupe's which may not be a huge issue but would rather fix if possible:

########################################


Why does simple ALTER TABLE command take so long on table with full-text index? I have a large (~67 million rows) name-value table that has full-text indexing on the DataValue column.

If I try to run the following command:

ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 NOT NULL;

It runs for 1 hour 10 minutes and still does not complete on a VisitorData table that contains ~67 million rows.

Why is this taking so long and not completing?
What can I do about it?

Here are more particulars about the table:

CREATE TABLE [dbo].[VisitorData](
            [VisitorID] [int] NOT NULL,
            [DataName] [varchar](80) NOT NULL,
            [DataValue] [nvarchar](3800) NOT NULL,
            [EncryptedDataValue] [varbinary](max) NULL,
            [VisitorDataID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_VisitorData_VisitorDataID] PRIMARY KEY CLUSTERED (
            [VisitorDataID] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
CONSTRAINT [UNQ_VisitorData_VisitorId_DataName] UNIQUE NONCLUSTERED (
            [VisitorID] ASC,
            [DataName] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VisitorData]
ADD  CONSTRAINT [UNQ_VisitorData_VisitorDataID] UNIQUE NONCLUSTERED (

[VisitorDataID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VisitorData]
    WITH CHECK ADD
        CONSTRAINT [FK_VisitorData_Visitors] FOREIGN KEY([VisitorID])
        REFERENCES [dbo].[Visitors] ([VisitorID])
GO

ALTER TABLE [dbo].[VisitorData]
    CHECK CONSTRAINT [FK_VisitorData_Visitors] GO

CREATE FULLTEXT CATALOG DBName_VisitorData_Catalog WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT INDEX ON VisitorData ( DataValue Language 1033 )
    KEY INDEX UNQ_VisitorData_VisitorDataID
    ON DBName_VisitorData_Catalog
    WITH CHANGE_TRACKING AUTO
GO

The wait types that are occurring during the ALTER TABLE command are LCK_M_SCH_M (schema modification), as per the query results below:

select * from  sys.dm_os_waiting_tasks

waiting_task_address    session_id exec_context_id wait_duration_ms     wait_type            resource_address       blocking_task_address   blocking_session_id blocking_exec_context_id resource_description
--------------------             ----------     --------------- --------------------              -------------------- ------------------             ---------------------            -------------------        ------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x0000000000B885C8   54               0                   112695                            LCK_M_SCH_M   0x00000000802DF600 0x000000000054E478     25                            0                                         objectlock lockPartition=0 objid=834102012 subresource=FULL dbid=5 id=lock438a02e80 mode=IS associatedObjectId=834102012
0x0000000000B885C8   54               0                   112695                            LCK_M_SCH_M   0x00000000802DF600 0x00000000088AB048    23                            0                                         objectlock lockPartition=0 objid=834102012 subresource=FULL dbid=5 id=lock438a02e80 mode=IS associatedObjectId=834102012

I'm working with production servers that are running SQL Server 2005 SP 2 (soon to be upgraded to 2008 SP2).

The schema change it taking so long because you are assigning a default value to the column during the change and enforcing that with a non-nullable column, and it has to populate the column for 60+ million rows, which is an incredibly expensive operation.  I'm not sure what your application requirements are but an approach that would make the schema change faster is to add it in as a nullable column with no default value and then perform an update in batches to assign 0 as the value for the column.  After your update are done then you can apply another schema change to change the column to non-nullable and assign the default value.


How to use FOR UPDATE with a view that is built using LEFT JOIN? Assuming a simple table, e.g.

\d+ task

      Column       |           Type           |                     Modifiers                     | Storage  |
-------------------+--------------------------+---------------------------------------------------+----------+
 id                | integer                  | not null default nextval('task_id_seq'::regclass) | plain    |
 parent_task_id    | integer                  |                                                   | plain    |
 cinema_id         | integer                  | not null                                          | plain    |
 venue_id          | integer                  |                                                   | plain    |
 movie_id          | integer                  |                                                   | plain    |
 event_id          | integer                  |                                                   | plain    |
 result            | json                     |                                                   | extended |
 context           | json                     |                                                   | extended |
 guide             | json                     |                                                   | extended |
 started_at        | timestamp with time zone |                                                   | plain    |
 ended_at          | timestamp with time zone |                                                   | plain    |
 created_at        | timestamp with time zone | not null default now()                            | plain    |
 updated_at        | timestamp with time zone | not null default now()                            | plain    |
Indexes:
    "task_pkey" PRIMARY KEY, btree (id)
    "public_task_cinema_id1_idx" btree (cinema_id)
    "public_task_event_id4_idx" btree (event_id)
    "public_task_movie_id3_idx" btree (movie_id)
    "public_task_parent_task_id0_idx" btree (parent_task_id)
    "public_task_started_at6_idx" btree (started_at)
    "public_task_venue_id2_idx" btree (venue_id)
Foreign-key constraints:
    "task_parent_task_id_fkey" FOREIGN KEY (parent_task_id) REFERENCES task(id) ON DELETE CASCADE
    "task_cinema_id_fkey" FOREIGN KEY (cinema_id) REFERENCES cinema(id) ON DELETE CASCADE
    "task_event_id_fkey" FOREIGN KEY (event_id) REFERENCES event(id) ON DELETE CASCADE
    "task_movie_id_fkey" FOREIGN KEY (movie_id) REFERENCES movie(id) ON DELETE CASCADE
    "task_venue_id_fkey" FOREIGN KEY (venue_id) REFERENCES venue(id) ON DELETE CASCADE
Referenced by:
    TABLE "task" CONSTRAINT "task_parent_task_id_fkey" FOREIGN KEY (parent_task_id) REFERENCES task(id) ON DELETE CASCADE
Triggers:
    update_task_updated_at BEFORE UPDATE ON task FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column()

Assuming a simple view that selects tasks that either do not have parent task or the parent task is complete:

CREATE VIEW outstanding_task AS
  SELECT
    t1.*
  FROM task t1
  LEFT JOIN task t2 ON t2.id = t1.parent_task_id
  WHERE
    t2.id IS NULL OR
    t2.result IS NOT NULL;

I am attempting to use this view with FOR UPDATE:

SELECT *
FROM outstanding_task
LIMIT 1
FOR UPDATE SKIP LOCKED;

However, in postgres v10.4 this produces an error:

  ERROR: FOR UPDATE cannot be applied to the nullable side of an outer join

How to use FOR UPDATE with a view that is built using LEFT JOIN?

Note 1: The question is specific to views. Without a view I could use FOR UPDATE OF t1.

Untested, as I don't currently have postgres working, but the concept should be clear:

Try using the view in another inner join against the base table, then update the base table.

select *
from task as t
inner join outstanding_tasks as o on t.id=o.id
for update of task


Can I implement a 'gapless' identity column in Oracle? In SQL Server it is so easy to write:

create table #tmp (
    id      integer identity(1,1) primary key,
    message varchar(256)
);

When I try to migrate this to Oracle, I end up with:

CREATE GLOBAL TEMPORARY TABLE tmp(
    id integer primary key,
    message varchar2(256)
) ON COMMIT PRESERVE ROWS;

CREATE SEQUENCE S_TMP_ID START WITH 1;

CREATE OR REPLACE TRIGGER TR_TMP
BEFORE INSERT ON tmp
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT S_TMP_ID.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

OK. I get unique, increasing id values. But, when two sessions are using the same global temporary table at the same time, I get gaps in the sequence of my ideas and they start with some arbitrary value.

Any ideas how to create better identity values for a global temporary table?

The only way to guarantee a gapless series (assuming no deletes) is to serialize - almost always a bad idea

With a sequence or identity you cannot assume your series will be gapless as @a_horse mentions - but it looks like you are assuming that even with gaps, there is some relationship between sequence order and insertion order - this is not true either! A higher sequence number could be inserted before a lower. Therefore trying to join "consecutive" rows is a meaningless concept

example:

create sequence seq;
create table t1(id integer);
create table t2(id integer);

--session 1:
insert into t1 select seq.nextval from dual connect by level<1000000;
commit;

--session 2:
insert into t2 select seq.nextval from dual connect by level<1000000;
commit;

result:

select min(id), max(id) from t1 union all select min(id), max(id) from t2;

MIN(ID)                MAX(ID)
---------------------- ----------------------
1                      1671679
356547                 1999998


Strange behaviour of the float type I have the following table:

+------------+--------+------+-----+---------+----------------+
| Field      | Type   | Null | Key | Default | Extra          |
+------------+--------+------+-----+---------+----------------+
| id         | double | NO   | PRI | NULL    | auto_increment |
| az1        | float  | YES  |     | 989898  |                |
| el1        | float  | YES  |     | 989898  |                |
| az2        | float  | YES  |     | 989898  |                |
| el2        | float  | YES  |     | 989898  |                |
| rfs_angle  | float  | YES  |     | 989898  |                |
| scan_speed | float  | YES  |     | 989898  |                |
+------------+--------+------+-----+---------+----------------+

I inserted this data more than 100times

INSERT INTO lidar_traj_table (az1, el1, az2, el2, rfs_angle, scan_speed)  VALUES (49.100, 0.000,989898,8.000,1.0,1.000)

When I'm trying to select the data I'm receiving nothing:

SELECT * FROM lidar_traj_table WHERE az1=49.1

When I'm selecting smaller data then 49.1 than I get all the rows

SELECT * FROM lidar_traj_table WHERE az1<=49.1

Why am I getting empty result for this:

SELECT * FROM lidar_traj_table WHERE az1=49.1

If you want to store exact values then you should use the DECIMAL data type instead of FLOAT.


Add a years field to a date field? I have the below fields and would like to add the contract_term field to the date field:

Contract_Term: Years
Date: YYYY/MM/DD

For example: 3(contract_term) + 2019/01/01(date) = 2022/01/01

What would be the best way for me to achieve the above result?

Thanks for any help.

It seems like you could use DATEADD pretty easily.

SELECT DATEADD(YEAR, 3, '2019-01-01') AS math

Just substitute in the columns in your table, something like this:

SELECT DATEADD(YEAR, contract_term, contract_date) AS math
FROM dbo.your_table;


SSDT - Exclude certain schema along with unnamed constraint Task:

Automate database deployment (SSDT/dacpac deployment with CI/CD)
The database is a 3rd party database
It also includes our own customized tables/SP/Fn/Views in separate schemas
Should exclude 3rd party objects while deploying the database project(dacpac) to Production
Thanks to Ed Elliott for the AgileSqlClub.DeploymentFilterContributor. Used the dll to filter out the schema successfully.

Problem:

The 3rd party schema objects(Tables) are defined with unnamed constraints(default / primary key) when creating the tables. Example:

CREATE TABLE [3rdParty].[MainTable]
(ID INT IDENTITY(1,1) NOT NULL,
CreateDate DATETIME DEFAULT(GETDATE()))  --There is no name given to default constraint

When I generate the script for deployment using sqlpackage.exe, I see following statements in the generated script.

Generated the script using:

    "C:\Program Files\Microsoft SQL Server\150\DAC\bin\sqlpackage.exe" /action:script /sourcefile:C:\Users\User123\source\repos\DBProject\DBProject\bin\Debug\DBProject.dacpac /TargetConnectionString:"Data Source=MyServer; Initial Catalog=MSSQLDatabase; Trusted_Connection=True" /p:AdditionalDeploymentContributorPaths="C:\Program Files\Microsoft SQL Server\150\DAC\bin\AgileSqlClub.SqlPackageFilter.dll" /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(3rdParty)" /outputpath:"c:\temp\script_AfterDLL.sql"

Script Output:

/*
Deployment script for MyDatabase

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/
...
...
GO
PRINT N'Dropping unnamed constraint on [3rdParty].[MainTable]...';

GO
ALTER TABLE [3rdParty].[MainTable] DROP CONSTRAINT [DF__MainTabl__Crea__59463169];

...
...
...(towards the end of the script)
ALTER TABLE [3rdParty].[MainTable_2] WITH CHECK CHECK CONSTRAINT [fk_518_t_44_t_9];

I cannot alter 3rd party schema due to company restrictions
There are many lines of unnamed constraint and WITH CHECK CHECK constraints generated in the script.

Question:

How can I be able to remove the lines to DROP unnamed Constraint on 3rd party schemas? - Even though the dll excludes 3rd party schema, it still has these unnamed constraints scripted/deployed. Also, it is not Adding them back too !!
How can I be able to skip/remove generating WITH CHECK CHECK CONSTRAINT on 3rd party schemas

Any suggestions will be greatly helpful.

EDIT:

Also, I found another issue. The deployment will not succeed due to Rows were detected. The schema update is terminating because data loss might occur

Output:

/*
The column [3rdParty].[MainTable_1].[Col1] is being dropped, data loss could occur.

The column [3rdParty].[MainTable_1].[Col2] is being dropped, data loss could occur.

The column [3rdParty].[MainTable_1].[Col3] is being dropped, data loss could occur.

The column [3rdParty].[MainTable_1].[Col4] is being dropped, data loss could occur.
*/

IF EXISTS (select top 1 1 from [3rdParty].[MainTable_1])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

GO

You can use utility DacpacTool to remove the schema completely from a dacpac file - I wrote it to do exactly this (among other things). It is quite rough on the edges so far, but it works.

Extract dacpac from target database to MSSQLDatabase.dacpac
Create a file blacklist.txt with one line: <References Name="\[SchemaThatYouWantRemoved]
DacpacTool.exe filter -f MSSQLDatabase.dacpac -b blacklist.txt
DacpacTool.exe filter -f DBProject.dacpac -b blacklist.txt
Script the diff between the two dacpacs. DeploymentFilterContributor is no longer needed: sqlpackage.exe /action:script /sourcefile:DBProject.dacpac /TargetFile:"MSSQLDatabase.dacpac" /outputpath:"c:\temp\script_AfterDLL.sql"


Binary log backups in MySQL I'm running a master-master replication with read-only second master. Now since my binary logs are not getting replicated in my passive master.

Should I need to enable --log-slave-updates? If I enable this, will I be able to take binary log backups from my passive master? If I enable it in passive master, I should not enable the same on active master right?

If I enable --log-slave-updates in my active master also, consider the case of a disaster where in my primary master has a issue, then I will make my passive master the active one and point my application to my new master. Now my new master will be generating binary logs which will be waiting to replicate to my actual master (which is down now). SO what happens when my actual master comes up? Binary of my secondary master gets copied to my primary without any issues?

While enabling --log-slave-updates, should I have attn on any other parameters as well.?

Thanks!

log-slave-updates=true

should be true on both masters. Because if both masters are accepting updates then the other passive master has to be able to update itself.
You can take backups of the bin logs whenever you want.
As to other options, I use the MIXED option for the binglog_format to allow for ROW based or STATEMENT based updates

binlog_format       = MIXED

This is because STATEMENT based replication doesn't cover all updates. But STATEMENT based replication is faster. With MIXED MySQL can switch for you when appropriate.


RMAN-03009: failure of backup command due to ORA-27038 and OSD-04010: I'm running below script in a windows server, this is same script I run across all servers

run {
configure retention policy to recovery window of 7 days;
 backup current controlfile format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_controlfile_%d_%T_%t';
 backup spfile format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_spfile_%d_%T_%t';
 backup incremental level 0 format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_%d_%T_%t' database ;
sql 'alter system archive log current';
backup archivelog all format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_ARC_%d_%T_%t' delete input;
}
crosscheck backup;
report obsolete;
delete noprompt obsolete;
delete noprompt expired backup;
list backup summary;
exit;

But I'm getting below error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/29/2020 11:30:04
ORA-19504: failed to create file "F:\DATABASE_BACKUP\TTTSCM\2020-04-29\FULL_TTTSCM_20200429_1039001396"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

Control file and SPfile are created successfully, and directory is empty prior backup. Thanks.

You must add unicity variable in the backup format name:
 backup incremental level 0 format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_%d_%T_%t_%U' database ;
As your backupset is composed of multiple backup pieces, they must have different names.


How to configure MySQL Router on two application servers? I have three MySQL servers running with group replication and inside a InnoDB Cluster. I have two app servers that will be connecting to the cluster and I need to configure mysql router on both app servers. When I run router on server1 I use the --bootstrap option. When I try to do it on server2 with --bootstrap I get an error "a router instance named has been previously configured on this host". My question is do I need to start mysql router with --bootstrap option? Or since it will be configured on two servers --bootstrap option is not required? Do I need to start mysql router with a personalized conf file?

I managed to resolve the issue and I am posting what resolved it for me in case someone else might run into this dilemma.

The resolution was to run mysqlrouter with the --bootstrap option on both app servers but I was missing the --name parameter (which is a bootstrapping option). The --name parameter gives a symbolic name to the router instance and since I was not passing on that parameter it gave it a blank name or ' '. Like ' ' router instance already existed then when trying to bootstrap on server 2 it was failing because ' ' router instance already exited. The trick here is to give each router instance a different name. All the examples that I found online only show you how to configure the router for a single instance not for a double instance that is why I was making the mistake on not giving it a name.

Anyway, I hope this helps someone.


Alternative to sub query in MS Access I'm looking into the possibilities for writing queries in MS Access, as my client currently uses Access and would be reluctant to move away to SQL Server. I've been using the data macros (which are Access 2010 onward's implementation of triggers) in order to create an audit table of sorts. Which looks something like this:

  Modification_Log
---------------------------------------------------------------------------------------------------------------------------
 ModificationID | TableName | FieldName | RecordID | InitialValue | ModifiedValue | ModifiedDate        | ModifiedEmployee |
       1        | Employee  |  Surname  |    3     |   Bloggs     |   Blaggs      | 16/09/2014 16:26:39 |    andrew        |
       2        | Employee  |  Surname  |    3     |   Blaggs     |   Bloggs      | 16/09/2014 16:27:32 |    stu           |
       28       | Employee  | Firstname |    3     |    Joe       |     Jon       | 17/09/2014 09:15:10 |    andrew        |

Which is updated dynamically when changes are made to a record on my Employee table, which looks something like this:

      Employee
----------------------------------------------------------------
 EmployeeID | PositionID | Title | Firstname | Surname | Email |
       3    |     2      |  Mr   |    Jon    |  Bloggs | none  |
       4    |     1      |  Mr   |   Tyrion  |Lannister| none  |

What I'd like to be able to do is to write a sub query which would use Max() to pull up the most recent ModificationID (an AutoNumber) where the RecordID is 3, and have the main query joined to this listing the employee details along with the most recent "ModifiedEmployee" and "ModifiedDate", giving me a recordset something like:

 EmployeeID | PositionID | Title | Firstname | Surname | Email | ModifiedDate        | ModifiedEmployee |
       3    |     2      |  Mr   |    Jon    |  Bloggs | none  | 17/09/2014 09:15:10 |    andrew        |

I don't think that's too much to ask, but seeing as MS Access doesn't allow sub queries at the moment the way I see it, I'll have to do something programmatically to carry out multiple queries in order to extract the same data, something like:

SELECT Employee.*
FROM Employee
WHERE Employee.EmployeeID = <<Selected Employee>>

Then

SELECT Max(Modification_Log.ModificationID) As NewestModificationID
FROM Employee INNER JOIN Modification_Log ON Employee.EmployeeID = Modification_Log.RecordID
WHERE Employee.EmployeeID = <<Selected Employee>>

Then

SELECT Modification_Log.ModifiedDate, Modification_Log.ModifiedEmployee
FROM Modification_Log
WHERE Modification_Log.ModificationID = <<NewestModificationID>>

Of course; that wouldn't be too difficult to implement but I'm just wondering whether there is a more elegant solution? I'm sure there has got to be as it seems like I'm doing an awful lot of leg work just for this relatively small task.

EDIT: Max Vernon suggested that I should be able to use sub queries via the SQL window (which is what I'd been trying to do anyway) but I'm still struggling to get it working, I've attempted to replicate what I saw a user over on Stack Overflow doing:

SELECT Employee.EmployeeID, Employee.PositionID, Employee.Title, Employee.Firstname, Employee.Surname, Employee.Email,  Modification_Log.ModifiedDate, Modification_Log.ModifiedEmployee
FROM Employee INNER JOIN Modification_Log ON Employee.EmployeeID = Modification_Log.RecordID  INNER JOIN
               (SELECT Modification_Log.RecordID, Max(Modification_Log.ModificationID) As NewestModificationID
               FROM Modification_Log
               GROUP BY Modification_Log.RecordID) As LogMax
ON Employee.EmployeeID = LogMax.RecordID AND Modification_Log.ModificationID = LogMax.NewestModificationID
WHERE Employee.EmployeeID = 3 AND Modification_Log.TableName = 'Employee'

As @Max Vernon suggested, you can create sub queries in MS Access, so perhaps I jumped the gun on that one! I couldn't understand why my sub query wasn't working; I still don't understand fully Why this solution works but the other one didn't.

I followed these steps:

Create sql subquery as a saved query in Access. Test. - Success
In a new query, added the saved query, added my Modification_Log table. Test. - Success
Added the Employee table, linked this. Test. - Success
Added my query criteria. Test. - Success
I finally replaced my reference to the saved query with my sub query. Test. - Success

What I end up with is this:

SELECT Employee.EmployeeID, Employee.PositionID, Employee.Title, Employee.Firstname, Employee.Surname, Employee.Email, Modification_Log.ModifiedDate, Modification_Log.ModifiedEmployee
FROM
         (SELECT Modification_Log.RecordID, Max(Modification_Log.ModificationID) As NewestModificationID
         FROM Modification_Log
         GROUP BY Modification_Log.RecordID) As LogMax
INNER JOIN (Employee INNER JOIN Modification_Log ON Employee.EmployeeID = Modification_Log.RecordID) ON (LogMax.NewestModificationID = Modification_Log.ModificationID) AND (LogMax.RecordID = Employee.EmployeeID)
WHERE Employee.EmployeeID=3 AND Modification_Log.TableName='Employee';

Which is almost identical apart from the fact the sub query immediately follows the FROM clause, I didn't think the order of joins would make a difference but this is MS Access we're talking about.


What does this mean - ~/.my.cnf When I run $ mysqld --verbose --help | less I got information like

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

As per my understanding, At startup, mysql first look for my.cnf within /etc/mysql/my.cnf then within /etc/my.cnf and then finally ~/.my.cnf.

Am I correct?
Which directory does this mean - ~/.my.cnf?
If a variable x has value 100 in /etc/mysql/my.cnf and again the same variable x has value 200 in etc/my.cnf, What value will really apply?
 -

~/.my.cnf is .my.cnf file in your home directory. The meaning of tilde ~ in unix is current user's home directory.

The values from different configurations are overwritten. So /etc/my.cnf will overwrite was what defined in /etc/mysql/my.cnf, and ~/.my.cnf will overwrite what was in previous files.


Does Windows Server File Compression slow Oracle DB performance? I'm starting a new a new contract and I get to troubleshoot their Oracle instance...  I'm more apps and sql, but I'll be trying to figure out their crashes and slow performance.  Go figure.

I can guess at reasons for some slowness... they want me to review their queries and tune where I can... DB and queries... Time for me to brush off those old Oracle DBA books.

I don't get access to Oracle for a bit, but I've got access to the Win 2008 R2 server and MS SQL Server.  I notice that their MS SQL backups are in pretty poor shape, with 987+ backups across 6 or so databases... they were down to 42M of space left on their 173G drive.

Looking at the system over all, I see that they are keeping their DB and databases on compressed drives.  I would think that this would give them a performance hit, but I really don't know.

Would this affect the performance of their Oracle and MS SQL negatively?

Update:
As I look around their system, I see other patterns of Worst Practices:
Multiple applications on one server:  Oracle DB, OracleBI, MS SQL 2008, Oracle WebLogic, Sharepoint...
All are on a single spindle -- 1 TB set in 5 virtual drives.
Almost all applications are in directories with folder/file compression set.
The sizing for the drives is currently a bit tight.  The Oracle "drive" has 4% free space (4.3 G free).  The MSSQL drive had what... 0.02% free before I deleted some old backups...

I will recommend additional drives (which probably won't happen) or to at least re-size the "Drives" and share out some of the 400 GB remaining on C:

The 4.3 GB free on the Oracle drive may not be a critical piece, but I can't tell until I can get Oracle up and running long enough to see what their DBs and conditions are.  So, "performance"-wise, it's a ZERO... at least MSSQL is running.

Thanks for your input... now off to find and digest the Oracle logs.

Indeed it is a bad idea put oracle datafiles in a compressed folder as it uses async random IO.

eg. on http://support.microsoft.com/kb/156932 we read:

  One obstruction to asynchronous operation is NTFS compression. The file system driver will not access compressed files asynchronously; instead all operations are just made synchronous.

If you have enterprise version of Oracle installed you can look into table and index compression provided by Oracle itself.

We can still use NTFS compression for almost everything in the recovery area (archive logs,rman backupsets,...),the dpdump folder and logging files.


Unable to query remote schema object using partial database link name I came across a very interesting situation today and I'm putting this question to the community in hopes that someone can explain why this happened. I haven't been able to find an explanation in Oracle's documentation.

We have a database link configured in our 12c instance, and assume the link is named database.domain.subdomain.com. This link connects to a 10g instance.

In queries that leverage this link, we use the partial link name: select * from table1@database (omitting .domain.*). This works fine.

A complete rebuild of the remote server and instance was necessary due to a hardware failure. When it was brought back up, we encountered a problem querying the table with the same query select * from table1@database however using the full name instead of partial worked fine: select * from table1@database.domain.subdomain.com

Querying other tables using the partial name was fine as well select * from table2@database

I discovered that there was a session open from before the rebuild. After I terminated this session, select * from table1@database resumed working as expected.

My question is, why did was I unable to query that specific object using the partial dblink name?

I had exactly the same problem: migrated Oracle 11.2 database to new installation 12.1 via DataPump. Database links in then 12c only worked when accessed with fully qualified names, but the application had been coded using only their partial names.
The problem was the domain of the new database's Global Name was different - see http://www.dba86.com/docs/oracle/12.1/ADMIN/ds_admin.htm#ADMIN029 (search for 'partial' within the dblinks section).
I created additional dblinks with fully qualified names using new domain name,  by extracting the ddl from the 11.2 database:
SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) FROM dba_db_links a;
 - see excellent hints from
 Alex Lima https://oraclespin.com/2008/03/28/how-to-extract-the-db-links-ddl-with-the-password
and Simon Krenger https://www.krenger.ch/blog/find-password-for-database-link/


Generic end date of quarter, PostgreSQL I'd like to produce the generic quarter end date for a given date.

Ex:If I have 2010-01-01, I would like to return 2010-03-31, and so on.

I can get the quarter number and year:

select to_char(date_trunc('quarter',  current_date)::date, 'yyyy-q');

Which returns 2017-3 since today is 2017-07-14

How do I get the quarter end date nicely?

I can get the answer, but its very ugly:

select to_char(date_trunc('year',  date '2015-01-01'),'yyyy') || '-' ||case
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 1 then '03-31'
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 2 then '06-30'
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 3 then '09-30'
    when (select extract('quarter' from date_trunc('quarter', date '2015-01-01')::date )) = 4 then '12-31'
    else '?'
    end

Which returns 2015-03-31 since I put in 2015-01-01.

Is there a better way?

I think the shortest and most elegant way to solve this issue is to use date_trunc('quarter',d) (which will retrieve the start of the quarter) + 3 months - 1 day, and use the expression to create a FUNCTION:

CREATE FUNCTION end_of_quarter (d date)
RETURNS date AS
$$
SELECT
    CAST(date_trunc('quarter', d)  + interval '3 months' - interval '1 day' AS date)
$$
LANGUAGE SQL IMMUTABLE ;

... and then use it:

SELECT
    d, end_of_quarter(d)
FROM
    (SELECT
        CAST(d0 AS date) AS d
    FROM
        generate_series(date '2017-01-01', date '2017-12-31', interval '6 days') AS s(d0)
    ) AS q ;

d           | end_of_quarter
:---------- | :-------------
2017-01-01  | 2017-03-31
...
2017-03-26  | 2017-03-31
2017-04-01  | 2017-06-30
...
2017-06-18  | 2017-06-30
2017-07-06  | 2017-09-30
2017-07-12  | 2017-09-30
...
2017-09-22  | 2017-09-30
2017-09-28  | 2017-09-30
2017-10-04  | 2017-12-31
2017-10-10  | 2017-12-31
...
2017-12-21  | 2017-12-31
2017-12-27  | 2017-12-31

You can check it at dbfiddle here

You could also shorten a bit your original approach by using the simple version of CASE expression WHEN value THEN ...:

SELECT
    d,
    extract('year' from d) || '-' ||
    /* case expression when value instead of case when expression */
    case extract('quarter' from d)
        when 1 then '03-31'
        when 2 then '06-30'
        when 3 then '09-30'
        else        '12-31'
    end AS end_of_quarter
FROM
    generate_series(date '2017-01-01', date '2017-12-31', interval '6 days') AS s(d) ;

You can check it at dbfiddle here

If you need to do this very often, define a function:

CREATE FUNCTION end_of_quarter (d date)
RETURNS date AS
$$
SELECT
    cast (extract('year' from d) || '-' ||
        case extract('quarter' from d)
            when 1 then '03-31'
            when 2 then '06-30'
            when 3 then '09-30'
            else        '12-31'
        end
    AS date)
$$
LANGUAGE SQL IMMUTABLE ;

dbfiddle here


Point in time Restore failed - Error 3456 'Could not redo log record' I have a database from which all tables got deleted by accident. Unfortunately we do not have a 'perfect' backup and now my problem comes in:

FULL Backup is from yesterday, 18:00
LOG Backup wasn't taken for several days. That backup was performed manually after the delete.
Both files are available to me to perform a point-in-time restore

To restore point-in-time, i restored the FULL-Backup WITH NORECOVERY. Then i tried to apply the Log-Backup. After it reaches 100% in GUI, i get the following error:

LSN-Information of FULL-Backup:

LSN-Information of LOG-Backup:

Am i right that the overlapping LSNs of FULL and LOG-Backup are the problem here? I think it is not accidential that PrevPageLSN from the error matches the FirstLSN of the FULL backup. Ideas how to get the database restored point-in-time?

From the docs:

  As a prerequisite to a point-in-time restore, you must first restore a full database backup whose end point is earlier than your target restore time.

Do you have an older full backup?  Contrary to popular belief, full backups do not break the "log backup chain."  So you should be able to restore an older full backup (that meets this requirement, and then do point in time recovery from the log backup that you took after the delete.

Another possible issue that comes to mind: maybe you have log backups running that you don't know about.  This seems unlikely at this point, but you could check for that by querying msdb.dbo.backupset:

USE [msdb];
GO

SELECT TOP (100)
    [name],
    first_lsn,
    last_lsn,
    checkpoint_lsn,
    backup_start_date,
    backup_finish_date,
    [type],
    [database_name]
FROM dbo.backupset
WHERE [database_name] = 'hydra1'
ORDER BY backup_start_date DESC;

If there any backups with type = 'L' after the full backup other than the one you created, then you'll need to restore those as well.

Since you've confirmed there is no corruption present in the FULL backup, it's possible that there is corruption in the LOG backup you're trying to use.  You could try running CHECKDB on the live database to see of the current log is corrupted.


Can I log which stored procedure issued a delete statement? We have an issue where rows are deleted from a table and we can't figure out where this is happening.  We have a trigger on the table to move the rows to another table, so that we can move the data back to the original table.

There are 20 to 30 stored procedures that delete rows from the table, so I was wondering if there was some way for the delete trigger to know what procedure invoked it.

We're using Sybase 15.

Thanks!

You need to add this implementation in your code base . I donot know If there is any other way in which Sybase itself keeps the track of it .

You can add a column in your table which can keep some identifier to the specific stored procedures and keep the track of the same in your trigger


How to test if a column definition allows nulls? I have a table with roughly 3000 rows in it and I need to modify a column definition to allow nulls. I have a change script that will perform the change but I would like to be able to re-run the script so that the change will only occur if the column definition hasn't already been changed. How do I test a column definition to identify if the column is NULL or NOT NULL?

You can also look at sys.columns.is_nullable ...


Change a field in Database on a Particular Date There is a field in my database named GRADES, what I want to accomplish is that on a particular date I want to update all the grades of a specific value to a new value. For example, let's say 5 weeks from today's date I want to update Grades from I to F.

Thanks in Advance

Note: I cannot do anything to the database like create a new table or a new field  it should be in the same table.

If I were starting from scratch, I might build this logic into a computed column. This may not be the best idea, but I figured I'd throw it out there as an alternative.

CREATE TABLE dbo.Grades
(
    StudentiD BIGINT,
    CourseId BIGINT,
    CourseGrade VARCHAR(2),
    FailDate DATE,
    FinalGrade AS CASE WHEN GETDATE() >= FailDate
                            AND CourseGrade = 'I' THEN 'F'
                       ELSE CourseGrade
                  END
);

When data gets inserted, you can have a default for (and a modifiable by teachers/admins) FailDate.

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 1, 'A', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 2, 'I', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 3, 'B', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 4, 'I', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 5, 'C', GETDATE() )

This allows them to give extensions or permanently get rid of the concept by making the value NULL.

So the query

SELECT *
FROM dbo.Grades AS g

Gives us:

StudentiD   CourseId    CourseGrade FailDate    FinalGrade
1           1           A           2017-05-19  A
1           2           I           2017-05-19  F
1           3           B           2017-05-19  B
1           4           I           2017-05-19  F
1           5           C           2017-05-19  C


Mysql giving error while inserting 120 million rows "The total number of locks exceeds the lock table size" I am inserting approximately 120 million rows in table. Its really slow and giving error "The total number of locks exceeds the lock table size". Can anyone please guide me the quickest process to insert 120 million rows or to remove that error. Insertion table is ISAM.

Here is the query

INSERT INTO brokergenius.market_data  (
  currentPrice,
  SN_ID,
  quantity,
  seatNumbers,
  ZN_ID,
  listingAttributeList,
  listingAttributeCategoryList,
  deliveryTypeList,
  ticketClass,
  dirtyTicketInd,
  splitOption,
  ticketSplit,
  splitVector,
  sellerOwnInd,
  faceValue,
  serviceFee,
  deliveryFee,
  totalCost,
  score,
  seller_price,
  Event_id,
  RN_ID,
  SSN_ID,
  LI_ID ,
  TS_ID
)
SELECT DISTINCT
  currentPrice,
  sectionId,
  quantity,
  seatNumbers,
  zoneId,
  listingAttributeList,
  listingAttributeCategoryList,
  deliveryTypeList,
  ticketClass,
  dirtyTicketInd,
  splitOption,
  ticketSplit,
  splitVector,
  sellerOwnInd,
  faceValue,
  serviceFee,
  deliveryFee,
  totalCost,
  score,
  seller_price,
  event_id,
  0,
  0,
  0,
  0
FROM
    market_snapshot.marketsnapshot;

Quickest way to get around such an error is to split it up into multiple queries.

Option 1:

Take the original query and execute it repeatedly changing LIMIT bounds.
... LIMIT 0, 10000000;
... LIMIT 10000000, 10000000;
... LIMIT 20000000, 10000000;
... LIMIT 30000000, 10000000;
and so on...

Without an ORDER BY, the limit is not 100.00000% guaranteed to be consistent; but as long as the query is not overly complicated this is usually safe.

Option 2: Take the original query and execute it repeatedly. Instead of a shifting window from varying LIMIT clauses, devise a set of WHERE conditions that will discretely partition the data. For example: If you know the source is roughly even divided up by several sectionID values, you could have WHEREs based on those values (or non-overlapping ranges of them.)


Merging two large tables physically in SQL Server 2014 I have a requirement to merge two large tables with redundant data.

Table A and Table B both have more than a billion records, most of the data including columns are redundant except 5 additional columns in Table B.

I would like to merge them physically and delete table B by comparing the data between both table and load the data that doesn't exist in Table A but exist in Table B to Table A.

Ultimate goal is to get rid of Table B and load all data and columns to Table A and then partition table A.

Did anyone had experience doing this? How can I compare the data between two large tables? Can we use Informatica or any native SQL tools? Is it a good approach to merge the tables and partition Table A or first partition Table A and them merge the tables?

Please let me know if you need any details.

going to take a while on 3 billion records but this is a query

select tableB.*
from tableB
left join tableA
on tableA.ID = tableB.ID
where tableA.ID is null


Table Valued UDF vs Stored Proc for passing values to a SELECT statement I work with an application that, in many places, uses stored procedures to return data to the application, kind of like a view, but these procedures take in parameters (which isn't possible in a SQL Server view).

I believe this was done due to it being more efficient for the given queries to have values passed deep into the inner queries, than it was to use a view with a WHERE clause.

In these cases, none of the other features you get by using a stored procedure are required e.g. they are all only select statements... no transactions, it's a single result set, no error handling, no calling to other procedures, etc.

For this specific use case - which is basically a "parameterized view" - are there efficiencies to be gained by using an inline user-defined function that returns a table data type instead of stored procedures for this?

The use case I am dealing with involves very small result sets. A simple example would be where a web app calls a proc with, say, a member id parameter, the proc contains a query with a bunch of joins and subqueries etc. (the parameter is used deep within these) and then send back a list of, say, subscriptions, owned by that user.

Is one method generally better than the other?

The answer partially depends on what you do with the output of the object. Both stored procedures and table-valued functions support inserting into table, but using an inline table-valued function can be significantly more efficient.

Below are a few silly object definitions for the demo. Both of them generate a result set of about 6.4 million rows with a single column.

CREATE PROCEDURE dbo.DUMMY_PROCEDURE (@i BIGINT)
AS
BEGIN
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i <> 0;
END;

GO

CREATE FUNCTION dbo.DUMMY_FUNCTION (@i BIGINT)
returns table
as
return (
    SELECT CAST(NULL AS BIGINT) COL
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    WHERE @i <> 0
);

GO

Next I'll insert the results of the stored procedure into a temp table. In general, this isn't a good thing to do for result sets that aren't tiny. The results are written to a separate internal structure before being inserted into the table. SQL Server uses a cardinality estimate of 1 row for the insert which can also cause performance issues.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 11514 ms,  elapsed time = 10748 ms
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
EXEC dbo.DUMMY_PROCEDURE 1;

The query took about 11 seconds. I uploaded the actual plan here if you want to take a look. Here's a screenshot of the worst part:

Things are much better with the function. The function definition is inlined and I'm eligible for parallel insert in SQL Server 2016. The insert takes about half a second.

DROP TABLE IF EXISTS #TARGET_TABLE;
CREATE TABLE #TARGET_TABLE (ID BIGINT);

-- CPU time = 1562 ms,  elapsed time = 520 ms.
INSERT INTO #TARGET_TABLE WITH (TABLOCK)
SELECT COL
FROM dbo.DUMMY_FUNCTION (1);

The query plan is healthier and much more typical:

There might be other internals differences in addition to that. I couldn't tell you.


Sql FILESTREAM garbage collection issue Updated 3x
I'm using FILESTREAM, but am having an issue with garbage collection.
The FILESTREAM files are not being cleaned up when I set the column to null.

In the event log, there is an enormous number of this error:
Internal FILESTREAM error: failed to access the garbage collection table.

It is error number 5571 on this page: http://msdn.microsoft.com/en-us/library/cc645602.aspx

Using this blog post I've confirmed that a 'tombstone' table exists, but I cannot query it (Invalid object name 'sys.filestream_tombstone_xxxxxxx'.)

I'm not deleting rows, I'm setting the FILESTREAM column to null, as per this msdn article: "When a FILESTREAM field is set to NULL, the BLOB data associated with the field is deleted."

The database is on a local drive.

The database is using Simple Recovery.

I have executed a number of CHECKPOINTs when activity was very low. This seems to have done nothing. The error message seems to indicate the FILESTREAM garbage collector is trying to garbage collect, but cannot access the table to find out what to do.

SQL Server 2008 (10.0.2531.0, SP1, Enterprise Edition

There are no other errors preceding the 5571 errors that I can see in the SQL Server Log or Event Log.

DBCC CHECKDB
There was an error detected. What do I do about this?
DBCC results for 'sys.filestream_tombstone_1819153526'.
Msg 8951, Level 16, State 1, Line 1
Table error: table 'sys.filestream_tombstone_1819153526' (ID 1819153526). Data row does not have a matching index row in the index 'FSTSNCIdx' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (4:4712151:3) identified by (oplsn_fseqno = 62856 and oplsn_bOffset = 82647 and oplsn_slotid = 2) with index values 'file_id = 65537 and rowset_guid = '3F972309-9B0B-4C4F-939A-5618897050B4' and column_guid = '4A143C0D-B877-494E-B1E6-B70B0A834BB6' and oplsn_fseqno = 62856 and oplsn_bOffset = 82647 and oplsn_slotid = 2'.
There are 209624 rows in 3382 pages for object "sys.filestream_tombstone_1819153526".

Questions:

How should permissions be set up for FILESTREAM to work? Other people have access to this server, and this database may have been restored from a backup at some point.

How do I check FILESTREAM (Windows) share/container account permissions and account under which SQL Server runs?

Thanks to @vgv8 for prompting for more info and ideas.

It seems like insufficient info to tell something definitely.  For example, are you deleting through Transact-SQL or through Win32API, etc.

The FILESTREAM data is not deleted immediately from file system because SQL Server transaction logging under full and bulk recovery models permit the crash recovery.

Have you deleted with CHECKPOINT

delete from tablename CHECKPOINT

or tried to execute CHECKPOINT statement or use simple recovery model?

Also, IMO,:

changing of FILESTREAM (re)configuration require restart of SQL Server
FILESTREAM data is restricted to LOCAL only drives
FILESTREAM operations depend on hardware, see Paul Randal's white paper
"deleting or renaming any FILESTREAM files directly through the file system will result in database corruption"

Check Security and Reliability sections of Paul Randal's white paper

Related  discussions containing many subreferences:

SQL Server Filestream - Delete “speed”
How to force the filestream garbage collector to complete its work with the highest priority?

Update:
Have you checked permissions of FILESTREAM (Windows) share/container vs. account under which SQL Server runs  under. It should have local administrator permissions.   It is recommended that no other account be granted permissions on the data container

Update2:
From Paul Randal's white paper:

"The file system access open operations do not wait for any locks. Instead, the open operations fail immediately if they cannot access the data because of transaction isolation. The streaming API calls fail with ERROR_SHARING_VIOLATION if the open operation cannot continue because of isolation violation"
"Antivirus software ... access to the BLOB data in the affected file will be prevented, and to SQL Server the file will appear to have been deleted."
"Note that for a table to have one or more FILESTREAM columns, it must also have a column of the uniqueidentifier data type that has the ROWGUIDCOL attribute. This column must not allow null values and must have either a UNIQUE or PRIMARY KEY single-column constraint"
"anything that can prevent transaction log truncation may also prevent a FILESTREAM file being physically deleted. Some examples are:"
FILESTREAM data containers cannot be nested

Run
DBCC CHECKDB

Update3:
I cannot coach you online. This is Q&A board. I already had been banned many times for infringing the rules on StackExchange having discussions instead of posting question or answer

Run

DBCC CheckDB (QPS8, repair_rebuild)

DBCC CheckDB (QPS8) WITH No_INFOMSGS, ALL_ERRORMSGS

and put output here.

To check/set NTFS permissions: right-click on folder in Windows --> Properties --> Security

To check/set MSSQLServer:  in command line type

SQLServerManager10.msc

navigate to SQL Server Services and double-click corresponding instance of SQL Server

Update 3b:
Probably, you should drop and recreate index or you have further deeper issues and you should restore database from backup or perform database repair permitting loss of data. This is quite separate topic/question.
I do not want to take any responsibility to guide you on it.


Application slows down during Back up The Application is 24/7 up Full back up time is 4 am and back up size is 92 gb. It takes one hour to complete the back up. During those time users complain that the application freezes.

Just wanted to know whether back up freezes or slows down the application momentarily? No other jobs are running that time.

Certainly, running a backup will have an impact on the performance profile of the machine serving the database being backed up.

For a FULL Backup, the operation consists of reading every allocated page in the entire database, and writing those pages to the backup file/device.

Since your users are complaining about the application being slow during the period of time when the backup takes place, I'd say it's obvious that the backup is having an impact.


How is time stored in the FireFox sqlite database "places.sqlite"? How is time stored in the FireFox sqlite database "places.sqlite" ? Looking to modify the stored visit time on a few entries but I can't seem to figure out how to, is it encrypted?

It's not encrypted.

The db-schema is:

CREATE TABLE moz_places (
  id INTEGER PRIMARY KEY,
  url LONGVARCHAR,
  title LONGVARCHAR,
  rev_host LONGVARCHAR,
  visit_count INTEGER DEFAULT 0,
  hidden INTEGER DEFAULT 0 NOT NULL,
  typed INTEGER DEFAULT 0 NOT NULL,
  favicon_id INTEGER,
  frecency INTEGER DEFAULT -1 NOT NULL,
  last_visit_date INTEGER ,
  guid TEXT,
  foreign_count INTEGER DEFAULT 0 NOT NULL
);

It's the field last_visit_date and is saved as Timestamp (Integer in milliseconds)
it means you could divide it by 1 000 000 and than use it as normal timestamp with seconds, when you don't need the precision of milliseconds.


Copying a database that is in Retore status I have a database that needs to stay in restore mode (NonRecovery), I receive a new group of log files that I receive daily that I have to post to it.
How can I copy the database while it is in non recovery mode so I can use it, but leave the original in restore mode.

You cannot use any backup statement to get a copy_only backup since the database is still restoring. At most the only option would be to use STANDBY mode but this would only provide you read access to all the data.

You can get it to STANDBY by using the following command:

RESTORE DATABASE sample_db
WITH STANDBY = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\sample_db_standby.file';

This will put the database in a read only state and you could use SSIS to export the data. You can also use SELECT..INTO for the tables you want to another database. It is odd that SSMS is not going to let you use the Export Data wizard even though the data is accessible in a readable state (just tested trying to use that).

Once you have gotten the data you want out you can put it back in NORECOVERY by issuing this statement:

RESTORE DATABASE sample_db WITH NORECOVERY;


Asked to Not Use Transactions and to Use A Workaround to Simulate One I've been developing T-SQL for several years and am always digging in further, continuing to learn all I can about all aspects of the language. I recently started working at a new company and have received what I think is an odd suggestion regarding transactions. Don't ever use them. Instead, use a workaround that simulates a transaction. This is coming from our DBA who works in one database with a lot of transactions and subsequently, a lot of blocking. The database I primarily work in does not suffer from this issue and I see transactions have been used in the past.

I understand that blocking is expected with transactions as it's in their nature to do so and if you can get away without using one, by all means do it. But I have many occasions where each statement MUST execute successfully. If one fails they all must fail to commit.

I’ve always kept the scope of my transactions as narrow as possible, always used in conjunction with SET XACT_ABORT ON and always within a TRY/CATCH.

Example:

CREATE SCHEMA someschema;
GO

CREATE TABLE someschema.tableA
(id   INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 ColA VARCHAR(10) NOT NULL
);
GO

CREATE TABLE someschema.tableB
(id   INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 ColB VARCHAR(10) NOT NULL
);
GO

CREATE PROCEDURE someschema.ProcedureName @ColA VARCHAR(10),
                                          @ColB VARCHAR(10)
AS
SET NOCOUNT, XACT_ABORT ON;
BEGIN
BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO someschema.tableA(ColA)
    VALUES(@ColA);

    INSERT INTO someschema.tableB(ColB)
    VALUES(@ColB);

--Implement error
    SELECT 1/0

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@trancount > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END;
    THROW;
    RETURN;
END CATCH;
END;
GO

Here is what they suggested that I do.

GO

CREATE PROCEDURE someschema.ProcedureNameNoTransaction @ColA VARCHAR(10),
                                                       @ColB VARCHAR(10)
AS
SET NOCOUNT ON;
BEGIN
BEGIN TRY
    DECLARE @tableAid INT;
    DECLARE @tableBid INT;

    INSERT INTO someschema.tableA(ColA)
    VALUES(@ColA);
    SET @tableAid = SCOPE_IDENTITY();

    INSERT INTO someschema.tableB(ColB)
    VALUES(@ColB);
    SET @tableBid = SCOPE_IDENTITY();

--Implement error
    SELECT 1/0

END TRY
BEGIN CATCH
    DELETE FROM someschema.tableA
    WHERE id = @tableAid;

    DELETE FROM someschema.tableB
    WHERE id = @tableBid;

    THROW;

    RETURN;
END CATCH;
END;
GO

My question to the community is as follows. Does this make sense as a viable workaround for transactions?

My opinion from what I know about transactions and what the solution is proposing is that no, this isn’t a viable solution and introduces many points of failure.

In the suggested workaround, I see four implicit transactions occurring. The two inserts in the try and then two more transactions for the deletes in the catch. It does “undo” the inserts but without rolling back anything so nothing is actually rolled back.

This is a very basic example to demonstrate the concept they are suggesting. Some of the actual stored procedures I have been doing this in make them exhaustively long and difficult to manage because “rolling back” multiple result sets vs two parameter values in this example becomes quite complicated as you could imagine. Since "rolling back" is being done manually now, the opportunity to miss something because real.

Another issue that I think exists is for timeouts or severed connections. Does this still get rolled back? This is my understanding of why SET XACT_ABORT ON should be used so that in these cases, the transaction will roll back.

Thanks for your feedback in advance!

You can not not use transactions in SQL Server (and probably any other proper RDBMS). In the absence of explicit transaction boundaries (begin transaction ... commit) each SQL statement starts a new transaction, which is implicitly committed (or rolled back) after the statement completes (or fails).

Transaction simulation suggested by the person who presents him- or herself as your "DBA" fails to ensure three out of four requisite properties of transaction processing, because it addresses only "soft" errors and is not capable of dealing with "hard" errors, such as network disconnects, power outages, disk failures, and so on.

Atomicity: fail. If a "hard" error occurs somewhere in the middle of your pseudo-transaction, the change will be non-atomic.
Consistency: fail. It follows from the above that your data will be in an inconsistent state following a "hard" error.
Isolation: fail. It is possible that a concurrent pseudo-transaction changes some of the data modified by your pseudo-transaction before yours completes.
Durability: success. Changes you make will be durable, the database server will ensure that; this is the only thing your colleague's approach cannot screw up.

Locks are a widely used and empirically successful method to ensure ACIDity of transactions in all sorts or RDBMSes (this site being an example). I find it very unlikely that a random DBA can come up with a better solution to the concurrency problem than hundreds, possibly thousands of computer scientists and engineers who have been building some interesting database systems over the last, what, 50? 60 years? (I realise this is somewhat fallacious as an "appeal to authority" argument, but I'll stick to it regardless.)

In conclusion, ignore your "DBA"'s advice if you can, fight it if you have the spirit, and come back here with specific concurrency problems if they arise.


SQL Server Point Constructor or version of ST_Point(x,y)? PostGIS provides two point-constructors

ST_MakePoint(x,y,[z,[m]])
ST_Point(x,y)or the compliant version that does not support 3DZ or 4D

MySQL supports Point(x,y)

In SQL Server, Is there anyway to construct a point from two numeric inputs or reals without going from text first?

I do not see a constructor that takes numbers
in the docs on "Create, Construct, and Query geometry Instances".

SQL Server 2012+
Yes, it's not listed there though. You'll find it documented on the docs of geography::Point(x,y,srsid)

The following example uses Point() to create a geography instance.
geography::Point(47.65100, -122.34900, 4326)

Microsoft calls it an "Extended Static Geography Methods"
It's documented in the SqlGeography Class and the Geometry constructor in the SqlGeometry Class


Is there any difference between putting a column alias at the start or the end of the column definition? I've always seen and written my column aliases as

SELECT 1 as ColumnName

but today came across a query that used

SELECT ColumnName = 1

Is there any difference in how these two queries get executed? Or is there a standard among DBAs about which one to use?

Personally I think the 2nd would be easier to read/maintain for longer column definitions (good example here from this article), however I've never seen the 2nd syntax used before today so am wondering if there is some reason I shouldn't be using it.

There is no difference in the underlying functionality of the two types of aliasing (as opposed to =).  What it boils down to is exactly what you mentioned:  Readability and maintainability.

In my opinion the former (<Expression> as <Alias>) is much more readable as it is self explanatory.  When you have SELECT ColumnName = 1 I think it'd be pretty easy to mistake that as setting a variable on those long tired nights.  You might mistake that as SELECT @ColumnName = 1 and that would be completely different functionality.  Therefore, to circumvent any possibility of the query "double look", or even worse...error in understanding/coding, I go with SELECT 1 as ColumnName 100% of the time.

Personal preference, but consistency (for yourself and within your team) is king.  Whatever you find easiest, go with and do it all the time.  There is nothing more frustrating than switching back and forth for somebody troubleshooting/reviewing/maintaining code.

The third unmentioned way is to use <Expression> <Alias>.  In other words, your second way without the as keyword.  I think this is just as bad as the = symbol.  It lacks readability at the gain of what?  Not typing three extra characters (as and a space).  Not worth it.

For exaggeration purposes, take a look at a query like this:

use AdventureWorks2012;
go

select
    [New Name] = Name,
    NewDepId = DepartmentID,
    GroupName as GName,
    ModifiedDate MyModDate
from HumanResources.Department;

Not code that I'd want to review.


Applying indexes on columns used in join condition In the query below,how beneficial it is to use index on columns used in join conditions?

select * from table_a
inner join table_b on
           table_a.col1 = table_b.col1 and
           table_a.col2 = table_b.col2

In above join, is it beneficial to index table table_a(col1,col2) or table table_b(col1,col2) and does order of columns in index impact performance?

If suppose there is an index on table_b(col1,col2). Does it mean we need to change the query as follows?

select * from table_a
    inner join table_b on
               table_b.col1 = table_a.col1 and
               table_b.col2 = table_a.col2

B-tree indexes provide three main benefits:

Seek to a specific value or range of values
Return rows in index order
Smaller row size than the base table (clustered index or heap)

Without useful indexes, the optimizer's main join strategy is hash join, where the join keys from the smaller table are used to build a hash table, then join keys from the larger table are used to probe into that hash table for matches. Hash join requires memory to hold the complete hash table.
With useful indexes, the optimizer may also consider indexed nested loops join or merge join strategies:
Indexed nested loops join
This physical join strategy primarily takes advantage of benefit #1 above. It reads from the smaller table then seeks into an index on the larger table on the join keys. For equi-join, index key order is not significant. Seeking on (col1, col2) is possible whether the index keys are (col1, col2) or (col2, col1).
For your example, an index on (col1, col2) or (col2, col1) for either table would enable an indexed nested loops join strategy. Ideally, the index would be on the larger table.
Merge join (without explicit sorts)
This physical join primarily uses benefit #2 above (index order). The indexes must include all the join columns, in the same key order on both tables. A merge join on (col1, col2) can use indexes on (col1, col2) or (col2, col1), but the key order must be the same for both tables.
Merge join is most efficient when at least one of the inputs is guaranteed unique on the join keys.
For your example, indexes on (col1, col2) or (col2, col1) on both tables would be required. The indexes would need to have the same key order on both, and ideally the index would be unique on those columns for at least one table.

db<>fiddle demo

The textual order of ON clause predicates is not important. The following are exactly equivalent. Which you use is purely a matter of style:
-- I prefer this style
SELECT *
FROM dbo.table_a AS TA
JOIN dbo.table_b AS TB
    ON TB.col1 = TA.col1
    AND TB.col2 = TA.col2;

SELECT *
FROM dbo.table_a AS TA
JOIN dbo.table_b AS TB
    ON TA.col1 = TB.col1
    AND TA.col2 = TB.col2;

More generally, how indexes impact on physical join choice is only part of the equation. Indexes can be useful for other (non-join) predicates in the query, as well as other operations like aggregation, windowing, and final presentation order.
Balancing index usefulness for the whole query, and all queries in the workload, versus the cost of maintaining those indexes is a significant part of the art of database administration and tuning.


Pass additional data in connection for use by PGSQL Trigger I would like to add some auditing triggers to PG, to log all changes to tables.  I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started.

What I would like to do is pass along some additional data automatically, such as the user-id or other key, so that the trigger can link the change back to the application user that made it. The basic flow would go like this:

Get a connection for the current request
Run custom SQL to set a context variable (set conn_context = {userid};)
Read / parse that variable inside the trigger
Write the value to a log table along with the other information

When using MS SQL I was able to execute a statement that set the context for the connection by executing SET CONTEXT_INFO ...data....  I am hoping there is a way to do this for PG; I haven't found one yet, but that may just be bad google-fu.

Old question, but still valid, so here is what I ended up using.
On 9.6 and above, to set the values either of the following will work:

select set_config('app.userid', '3', true)
SET LOCAL "app.userid" = 3; (or '3')

To read the value out,

select current_value('app.userid')

I ended up wrapping the lookup for the userid or other field in a function, to handle the type conversion(s) and the fact that it might not be set:
CREATE OR REPLACE FUNCTION app_get_userid() RETURNS INT AS $$
DECLARE userid integer;
BEGIN
    BEGIN
        userid := current_setting('app.userid');
    EXCEPTION
        WHEN OTHERS THEN userid := NULL;
    END;
    return userid;
END;
$$ LANGUAGE plpgsql;


How can I place "WHERE CLAUSE " to this query in order to filter a results This is mysql query that found on the internet and modified to my needs, but I failed to place where clause in order to filter result.

SELECT
stid,
MAX( CASE WHEN courseid = 'MATH' THEN marks END) MATH ,
MAX( CASE WHEN courseid = 'HIST' THEN marks END) HIST,
MAX( CASE WHEN courseid = 'SCI' THEN marks END) SCI,
MAX( CASE WHEN courseid = 'GEO' THEN marks END) GEO,
MAX( CASE WHEN courseid = 'ENG' THEN marks END) ENG,
MAX( CASE WHEN courseid = 'CIV' THEN marks END) CIV,
MAX( CASE WHEN courseid = 'VS' THEN marks END) VS,
MAX( CASE WHEN courseid = 'KISW' THEN marks END) KISW
FROM finalresults
GROUP BY stid ORDER BY 1"

Between FROM and GROUP BY.

SELECT
stid,
MAX( CASE WHEN courseid = 'MATH' THEN marks END) MATH ,
MAX( CASE WHEN courseid = 'HIST' THEN marks END) HIST,
MAX( CASE WHEN courseid = 'SCI' THEN marks END) SCI,
MAX( CASE WHEN courseid = 'GEO' THEN marks END) GEO,
MAX( CASE WHEN courseid = 'ENG' THEN marks END) ENG,
MAX( CASE WHEN courseid = 'CIV' THEN marks END) CIV,
MAX( CASE WHEN courseid = 'VS' THEN marks END) VS,
MAX( CASE WHEN courseid = 'KISW' THEN marks END) KISW
FROM finalresults
WHERE field=value
GROUP BY stid
ORDER BY 1


Track MongoDB users operations What is the best way to track operations done on my MongoDB instance?
I want to display a graph with the number of user's operations (insert, update, query etc..) done.

I have read about profiling, but is there a better strategy? How does the MMS do that?

The Cloud Manager (née MMS) documentation includes a list of the Database Commands Used by the Monitoring Agent.

The main command to start with is serverStatus, which includes most metrics specific to a mongod instance (as opposed to replica set or sharded cluster information).

You can run this command via a driver and preview the results with the db.serverStatus() helper in the mongo shell.

Available subsections and output may vary depending on the version and configuration of your MongoDB server.

Interesting sections of serverStatus typically include:

opcounters: insert, query, update, delete, command, getmore
opcounters-repl: opcounters that are the result of replicated operations applied on a secondary
connections: current, available, and total created connections
asserts: number and type of assertions that have been raised since the server started

For more information see:

serverStatus command description
Server Status Output


Delete using a composite key I am trying to wrap my head around creating a delete statement for a table with a composite key.

I would like to create something like:

DELETE
FROM
    table_example1
WHERE
    COLUMN1, COLUMN2
    IN
    (SELECT COLUMN1, COLUMN2 FROM table_example2
    WHERE
        COLUMN_DATETIME > @Period);

Keeping in mind only the combination of COLUMN1 and COLUMN2 is unique, each column on its own is not unique.

I can't seem to get my head around how I would actually do this.

Should be something like this:

DELETE A
FROM
    table_example1 AS A
    INNER JOIN table_example2 AS B
    ON A.COLUMN1 =B.COLUMN1
    AND A.COLUMN2 = B.COLUMN2
WHERE
    COLUMN_DATETIME > @Period;

Alternatively:

DELETE FROM A
FROM dbo.table_example1 AS A
WHERE EXISTS
(
    SELECT *
    FROM dbo.table_example2 AS B
    WHERE
        B.COLUMN1 = A.COLUMN1
        AND B.COLUMN2 = A.COLUMN2
        AND B.COLUMN_DATETIME > @Period
);


Database Mirroring and Replication in SQL Server 2008 R2 I have configured the mirroring and replication between 4 servers (A,B,C,D).i.e, Mirroring between A to B and C to D, Replication between A and C. The configuration was a success and I am able test the replication(B to C) when I have failed over the mirroring dbs(A to B). The replication works fine after the mirroring fail over but I am not able to check its status in the Replication monitor. When I am having any insert in a table which is replicated in B, it is reflected back to C..it means the replication is working fine.

Any thought on how I can view the status of replication from mirrored server. I tried adding the publisher in the monitor, but no luck. If I check the snapshot agent status, it says could not retrieve the info, same with the log reader agent status.

Any suggestions on this please.

You will need to add both the principal server and the mirror server to Replication Monitor.  This is covered in Database Mirroring and Replication (SQL Server):

  Add the principal and mirror to Replication Monitor.

Please see Add and Remove Publishers from Replication Monitor to add the mirror server to Replication Monitor.


How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks? I am having a (rather standard) daily maintenance plan created with SQL Server Management Studio  Maintenance Plan Wizard.

AFAIU, the penultimate task "Back Up Database Task" will be executed only on the success of all preceding tasks. Since the constraints (arrows containing tasks) are all the same (taken from default options creation):

and use "Logical AND"

Now if I change in the preceding constraint the "Logical AND" to "Logical OR", the backup task still will fail if one of the preceding tasks fail.
Correct?

How can I make the backup task to run in any case?

UPDATE
After accepting the answers, I am still in doubt about the term "completion":
Does failed task considered to be run to completion in SQL Server maintenance plan terminology?

There are 3 options in Value: Success, Failure and Completion (approximate). Changing the value to Completion for all the preceding precedence constraints should ensure that the backup task is reached regardless of the upstream tasks.

  Execution Results

  The precedence constraint can use the following execution results alone or in combination with an expression.

  Completion requires only that the precedence executable has completed, without regard to outcome, in order for the constrained executable to run.
  Success requires that the precedence executable must complete successfully for the constrained executable to run.
  Failure requires that the precedence executable fail for the constrained executable to run.

Further reading:

https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/
http://technet.microsoft.com/en-us/library/ms141261.aspx
http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/11/things-you-might-not-know-about-precedence-constraints-ssis.aspx
http://blogs.msdn.com/b/mattm/archive/2006/10/30/using-expression-based-constraints.aspx


Silent truncation of last character when using SSIS with Oracle I am using SSIS to export data from an Oracle database. Sometimes when I export a VARCHAR2 column to NVARCHAR the last character gets truncated (silently). It's completely random as far as I can tell. I have tried using different SSIS data types (DT_STR, DT_WSTR and DT_NTEXT) but the problem persists. The source string is always 12 characters long ([0-9A-Z] only), but when I check the destination database some columns are missing the last character.

I use the latest 32bit Oracle Instant Client (11.2) with ODBC and SQL Server 2008 R2. I use ADO .NET for both source and destination. The source character set is AMERICAN_AMERICA.WE8ISO8859P1 and the source and destination columns are declared as VARCHAR2 (12 BYTES) and NVARCHAR(12) respectively.

It looks like the problem was caused by a data flow task where I had two different data flows running in parallel. The same source tables were involved in both data sources but with different destinations. When I broke the task up into two tasks running one after the other, the truncation problem seems to have gone away.