The best way to do is -- use coreinfo (utility by sysinternals) as this will give you
a. Logical to Physical Processor Map
b. Logical Processor to Socket Map
c. Logical Processor to NUMA Node Map as below :

Logical to Physical Processor Map:
**----------------------  Physical Processor 0 (Hyperthreaded)
--**--------------------  Physical Processor 1 (Hyperthreaded)
----**------------------  Physical Processor 2 (Hyperthreaded)
------**----------------  Physical Processor 3 (Hyperthreaded)
--------**--------------  Physical Processor 4 (Hyperthreaded)
----------**------------  Physical Processor 5 (Hyperthreaded)
------------**----------  Physical Processor 6 (Hyperthreaded)
--------------**--------  Physical Processor 7 (Hyperthreaded)
----------------**------  Physical Processor 8 (Hyperthreaded)
------------------**----  Physical Processor 9 (Hyperthreaded)
--------------------**--  Physical Processor 10 (Hyperthreaded)
----------------------**  Physical Processor 11 (Hyperthreaded)

Logical Processor to Socket Map:
************------------  Socket 0
------------************  Socket 1

Logical Processor to NUMA Node Map:
************------------  NUMA Node 0
------------************  NUMA Node 1

Now, based on the above info, the Ideal MaxDop setting should be calculated as
a.  It has 12 CPU’s which are hyper threaded giving us 24 CPUs.
b.  It has 2 NUMA node [Node 0 and 1] each having 12 CPU’s with Hyperthreading ON.
c.  Number of sockets are 2 [socket 0 and 1] which are housing 12 CPU’s each.

Considering all above factors, the max degree of Parallelism should be set to 6 which is ideal value for server with above configuration.

So the answer is -- "it depends" on your processor footprint and the NUMA configuration and below table will summarize what I explained above:
8 or less processors    ===> 0 to N (where N= no. of processors)
More than 8 processors  ===> 8
NUMA configured         ===> MAXDOP should not exceed no of CPU’s assigned to each
                                 NUMA node with max value capped to 8
Hyper threading Enabled ===> Should not exceed the number of physical processors.

Edited: Below is a quick and dirty TSQL script to generate Recommendation for MAXDOP setting
/*************************************************************************
Author          :   Kin Shah
Purpose         :   Recommend MaxDop settings for the server instance
Tested RDBMS    :   SQL Server 2008R2

**************************************************************************/
declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int

select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
    ,@HTEnabled = case
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);

select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64
group by parent_node_id
option (recompile);

select @NoOfNUMA = count(distinct parent_node_id)
from sys.dm_os_schedulers -- find NO OF NUMA Nodes
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64

-- Report the recommendations ....
select
    --- 8 or less processors and NO HT enabled
    case
        when @logicalCPUs < 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
                --- 8 or more processors and NO HT enabled
        when @logicalCPUs >= 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : 8'
                --- 8 or more processors and HT enabled and NO NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA = 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
                --- 8 or more processors and HT enabled and NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA > 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
        else ''
        end as Recommendations

EDIT: For future visitors, you can look at test-dbamaxdop powershell function (along with other extremely helpful DBA functions (ALL FREE !!).


Before you can use pgAudit, you need to add it on the postgresql.conf file. You need to uncomment the line (or add pgaudit if you already have another libraries) to the shared_preload_libraries, like this:
shared_preload_libraries='pgaudit'

After this you must start the server and add the pgaudit extension:
CREATE EXTENSION pgaudit

From then on you can configure what is going to be logged either directly on the postgresql.conf file or by set commands. pgAudit can register by session or by oject created, that depends on you.
Only after all of that your set pgaudit.log = 'ddl' will register what you want.


How to control position of startup predicate in the execution plan

Is there any whitepaper or documentation on how SQL Server is placing startup predicates?

No, not that I've ever seen. They don't get written about all that much. The one time I blogged about them, I did a lot of head scratching.

Now, I have an answer -- it's not guaranteed to always work. It's playing some tricks on the optimizer that you can learn more about by watching Query Tuning Mastery: Clash of the Row Goals, a talk by Adam Machanic.

With that out of the way, we can control to some degree where the optimizer decides to stick predicates by using TOP.

Function rewrite 1:

In this one, the WHERE clause appears inside the CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erik ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   SELECT s.*
               FROM   sys.objects AS s
               CROSS APPLY
                      (   SELECT TOP (2147483647) *
                          FROM   dbo.test_fn1(@Par2, ';') AS x
                          WHERE  s.name = x.item
                          AND    @Par1 = 'CASE1' ) AS ca );
GO

Function rewrite 2:

In this one, the WHERE clause appears outside the CROSS APPLY.

CREATE OR ALTER FUNCTION dbo.test_erikx ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (   SELECT s.*
               FROM   sys.objects AS s
               CROSS APPLY
                      (   SELECT TOP (2147483647) *
                          FROM   dbo.test_fn1(@Par2, ';') AS x
                          WHERE  s.name = x.item ) AS ca
               WHERE  @Par1 = 'CASE1' );
GO

You can see the resulting plans here.

In plan 1, the startup expression predicate appears inside by the TOP:

In plan 2, it appears outside.

If you want the Startup Expression Predicate to act as a constant scan, try this:

CREATE OR ALTER FUNCTION dbo.test_erik_filter ( @Par1 VARCHAR(100), @Par2 VARCHAR(100))
RETURNS TABLE
AS
    RETURN (
               SELECT ca.*
               FROM (
                     SELECT TOP (1) 1 AS n
                     WHERE @Par1 = 'CASE1'
                    ) AS x
               CROSS APPLY
                (
               SELECT     s.*
               FROM       sys.objects AS s
               INNER JOIN dbo.test_fn1(@Par2, ';') AS x
                   ON s.name = x.item
               ) AS ca

         );
GO

If we run two different test cases, the plans will be slightly different:

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE2', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO

DECLARE @Par1 VARCHAR(100), @Par2 VARCHAR(100);
SELECT @Par1 = 'CASE1', @Par2 = 'test1;test2';

select * from dbo.test_erik_filter(@Par1, @Par2)

GO

Looking at the live query plans (here are the regular ones), there are a couple early important differences.

No rows come out of this, which means the start up expression isn't true.

Rows will flow out of this one, because it does.

Remember that data flows from right to left in a query plan. Putting the filter further towards the left side of the plan has no advantage for doing less work.

Aaron Bertrand discusses a similar issue with regular filters here:

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

As an aside, if your real-life code doesn't just select from system tables/views, you should consider adding the SCHEMABINDING attribute to it, which could aid in filter placement.

Hope this helps!


SQL Server Linked Server - Timeout Error

It could be a firewall issue.
You have to be able to connect to Server B from Server A. You said that you can connect to Server B from your desktop, but this only proves there is no problem between your desktop and Server B.
For a fair test, you have to log in to Server A and attempt to log in to Server B from Server A.


Query to return fields of distinct values per key

Without knowing anything about the source data, perhaps this would do what you want?

USE Test;
GO
CREATE TABLE GENDER
(
    ORG INT NOT NULL
    , GENDER VARCHAR(1) NOT NULL
);

CREATE TABLE AGE
(
    ORG INT NOT NULL
    , AGE TINYINT
);

CREATE TABLE STATES
(
    ORG INT NOT NULL
    , STATENAME VARCHAR(255)
);

INSERT INTO Gender (ORG, GENDER) VALUES (1, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (1, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'F');

INSERT INTO AGE (ORG, AGE) VALUES (1,27);
INSERT INTO AGE (ORG, AGE) VALUES (1,28);
INSERT INTO AGE (ORG, AGE) VALUES (1,29);
INSERT INTO AGE (ORG, AGE) VALUES (1,30);
INSERT INTO AGE (ORG, AGE) VALUES (2,37);
INSERT INTO AGE (ORG, AGE) VALUES (2,38);
INSERT INTO AGE (ORG, AGE) VALUES (2,39);
INSERT INTO AGE (ORG, AGE) VALUES (2,40);
INSERT INTO AGE (ORG, AGE) VALUES (3, 2);

INSERT INTO STATES (ORG, STATENAME) VALUES (1,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'NM');

CREATE TABLE FACTS
(
    ORG INT NOT NULL
    , GENDER VARCHAR(1) NULL
    , AGE INT NULL
    , STATENAME VARCHAR(255) NULL
);

INSERT INTO FACTS (ORG, GENDER, AGE, STATENAME)
SELECT ORG, GENDER, NULL, NULL
FROM GENDER
GROUP BY ORG, GENDER
UNION ALL
SELECT ORG, NULL, AGE, NULL
FROM AGE
GROUP BY ORG, AGE
UNION ALL
SELECT ORG, NULL, NULL, STATENAME
FROM STATES;

SELECT *
FROM FACTS
ORDER BY ORG;

The results:

This will create a FACTS table that has all the data from several source tables.  As @ypercube and @jon-seigel said, this really doesn't make much sense; perhaps we are missing something compelling about your setup.

If this is not what you were expecting, please provide the source tables, and any other pertinent details.


firebird is very slow compared to sqlite

Unfortunately, this is due to the fact that Firebird has to do a full table scan to get the count. I'm not familiar with how SQLite works, but a quick search seems to indicate that SQLite can use an index to get a count (eg primary key index for count(*) or an index on a column if using count(columnname), though I haven't verified if this is actually correct).

The reason Firebird needs to do a full table scan, is because Firebird is a MVCC (Multi-Version Concurrency Control) database, but record visibility information is not included in its indexes. So, to be able to know that a record is visible to your transaction and therefor should be included in the count, Firebird needs to read the actual record. And because the query is an unconditional count, using an index and then checking each record would be slower than just reading each record.

However, there are some things you can do to improve performance:

Use count(*) instead of count(journal_id).

Using count(*) will just count records, while count(journal_id) will count records where journal_id is not null. Firebird doesn't use the not null state of a primary key, so it checks if that column is not null for each record. On my system, changing to count(*) reduces the query time by around 25%.
Use a larger page size.

Your example database has a page size of 4096, increasing it to a page size of 16384 reduces the execution time your example query (with count(journal_id)) by around 20%, while the count(*) (compared to your original query on page size 4096) is reduced by more than 50%.

However, given the overall poor performance of an unconditional count, the primary advice would be to avoid having to use unconditional count.


Slow checkpoint and 15 second I/O warnings on flash storage

Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints.

Sounds good. Have you collected and cut up the minifilter and storport tracing, yet? If so, what did it show?

  At first glance it looks to be clearly an I/O subsystem error and the SAN admin was to be blamed for it. But recently we changed the SAN to utilize Full Flash but as of today the error still pops up and I have no clue as to why since every metric, whether wait stats or any other metric, that I run to check if SQL server is a possible culprit seems out to return normal.

I want go over two different areas here.

The first is that SQL Server itself doesn't actually do anything with I/O, it posts it to Windows using the typical Windows APIs. Whether it is ReadFile, WriteFile, or the vectored I/O of those it's all up to Windows. SQL Server keeps a list of pending I/O and checks that I/O at various times to get the status if it isn't completed. This is done using, again, the typical Windows asynchronous I/O model. The message is printed when the I/O has been pending and not completed, according to Windows for over 15 seconds as we're using the GetOverlappedResult Windows API to check status. This means, SQL Server doesn't really have a say in the matter, it's what is being returned via Windows.

The second item is that just because it's all flash and 10 Gb fiber doesn't mean something isn't setup or configured incorrectly, that a driver, filter, or other bug or item isn't hit, or that something isn't physically wrong. Just to get an idea:

Windows Config
Windows Drivers such as mutli-pathing being setup and the latest version
Filter Drivers (you know, disk devices, antivirus, backup, etc.)
Hypervisors (if any)
HBA drivers
HBA firmware
HBA configuration
Physical Cabling
Fiber switching
I/O Group connections/SAN/Device
Configuration of SAN/Device

That's all under SQL Server, it's just that SQL Server is the one telling you about it.

  Dbs are in Availability Groups and as and when these events occur we do see role changes and flip overs occurring along with timeouts.

That's really good information to know, although it doesn't necessarily mean it's exactly related. Now, if it only happens when there is a failover, then that would hone in the issue much more and that would sound to me more like the drivers et al. doesn't like throwing a whole lot of mixed I/O at it as a failover typically results in the redo/undo and resync's happening which could be a spike in outstanding I/O.

  Any help in figuring this out would be highly appreciated.

Unless it's a query or set of queries that are pushing high IOPs, which it doesn't sound like as the snapshot for 30 minutes you have was only 737,465 I/O operations which averages to 410 IOPs (not that high, especially if it's flash) looking inside of SQL Server isn't going to help with this issue since SQL Server is the messenger.

You'd want to collect if not already:

Minifilter time spent. This can be done through WPR (XPerf) if you don't have anything else. This can help if the I/O is getting stalled in a filter driver.
Storport trace. This will be the last stop on the way our and the first stop on the way back. Any time between these two readings is time spent outside of Windows... It'll also show you the targets and where the slowness might be on the other end (but isn't always conclusive).

If none of those are helpful in diagnosis or narrowing in the scope of the issue, it may be time to open a ticket with Windows Storage support and have all the data already collected so that you all can start on the same page.


MySQL replication - slave is continuously lagging behind master

The Seconds_Behind_Master is really like viewing the past via time travel.

Think of it this way:

The Sun is 93,000,000 miles away from the Earth
The speed of light is 186,000 miles/sec
Simple division shows that it takes roughly 500 sec (8 min 20 sec) for the Sun's light to reach Earth
When you look at the Sun, you actually don't see the Sun. You see where it was 8 min 20 sec ago.

In like manner, it seems that the Master is processing a lot of queries at the same time.

You look back at the Slave, run SHOW SLAVE STATUS\G and it says 200 for Seconds_Behind_Master. How is that number calculated? Slave's Clock Time (UNIX_TIMESTAMP(NOW()) - TIMESTAMP of the Query when it was completed and recorded in the Master's Binary Log.

There is another metric to look at besides Seconds_Behind_Master. That metric is called Relay_Log_Space. That represents the sum of all bytes for all relay files on the Slave. By default, the largest single relay log is limited to 1GB. If Relay_Log_Space is less than 1GB, this indicates that many long running queries executed on the Master in parallel. Unfortunately, due to the single-threaded nature Replication's SQL thread, queries are executed one behind the other.

For example, suppose you have the following scenario on the Master:

Slow Query log is enabled
20 queries executed in parallel on the Master
Each query took 3 seconds
Each query get recorded in the Master Binary Log with the same timestamp

When the Slave reads those queries from its relay log and processes them one by one

the Slave's Clock will be moving
the TIMESTAMP for each of the 20 queries will be identical
the difference will increasing 3 seconds be completed query
this results in 60 seconds for Seconds_Behind_Master

Concerning the Slow Log, the default for long_query_time is 10 seconds. If all your queries in the relay logs are less than 10 seconds, you will never catch anything in the Slow Query Log.

I have the following recommendations for both Master and Slave servers

RECOMMENDATION #1 : Upgrade to MySQL 5.5. Under MySQL 5.5 and Percona Server 5.1.38+, you can tune InnoDB to access multiple CPUs. I have written past posts about this

Apr 26, 2012 : Is the CPU performance relevant for a database server?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

RECOMMENDATION #2 : Use InnoDB for all tables. InnoDB caches data and indexes in RAM, MyISAM only caches indexes.
RECOMMENDATION #3 : Increase RAM. You must cache more data and indexes on the Slave and Master alike
RECOMMENDATION #4 : Tune all queries. Reducing milliseconds from queries that run hundreds of times goes a long way to reduce Seconds_Behind_Master.

FURTHER TROUBLESHOOTING

If you want to see the queries causing the replciation lag, do the following:

SHOW SLAVE STATUS\G
Get name of relay log from Relay_Log_File
STOP SLAVE;
START SLAVE;
In the OS, cd /var/lib/mysql or wherever the relay logs are written
Dump the relay log to a text file

For example, Let's do SHOW SLAVE STATUS\G

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.51.149
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 1024035856
               Relay_Log_File: relay-bin.000030
                Relay_Log_Pos: 794732078
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: search_cache
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1024035856
              Relay_Log_Space: 794732271
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 106451149

If I run STOP SLAVE; START SLAVE;, the relay log closes and a new one is open. Yet, you want relay-bin.000030.

Dump the contents as follows:

cd /var/lib/mysql
mysqlbinlog relay-bin.000030 > /root/RelayLogQueries.txt
less /root/RelayLogQueries.txt

You can now see the queries the Slave is currently trying to process. You can use those queries as the starting point for tuning.


That is not a large table, SQL-Server should be able to handle that easily assuming you are running on server-grade hardware. If your hardware is severely underpowered then nothing we tell you can solve the problem entirely.

You may have performance issues relating to using GUIDs. Your PK is clustered and is a GUID, this means it is continually moving rows around on insert to keep the clustered index correct. If you are not using newsequentialid for the default value of your GUIDs, then you have a problem right there. Of course joins are faster using int keys but it is probably too late to completely redesign around that.

You may need to experiment with indexes - you want ones that will work with your queries but too many will slow down inserts considerably.

When was the last time you updated statistics or reindexed the table?

You could look at partitioning the table by date. That might speed things up. If you don't have Enterprise Edition and the right hardware to partition, you could archive old records and only use them for reporting by unioning the active records in the main table with the inactive records in the archive table. Then you would have many fewer records to handle for the main bulk of your work.

But clearly what you need most urgently is a good solid DBA who knows how to performance tune. Someone who won't let you make design mistakes in the future and who has the skill and ability to refactor your system to make it work. Performance tuning is not the sort of thing you can learn by asking questions on a board like this. It is a complex subject that takes a good amount of expertise learned over years and by working with many databases. If you have no DBA, then hire one. For later projects, you probably should hire one at design time; databases are hard to refactor and they need to be designed for performance from the start.


Linked servers: Transaction context in use by another session

http://msdn.microsoft.com/en-us/library/ms188716.aspx

  Loopback linked servers cannot be used in a distributed transaction. Trying a distributed query against a loopback linked server from within a distributed transaction causes an error, such as error 3910: "[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session." This restriction does not apply when an INSERT...EXECUTE statement, issued by a connection that does not have multiple active result sets (MARS) enabled, executes against a loopback linked server. Note that the restriction still applies when MARS is enabled on a connection.


How do I change the text in the export format dropdown on SQL Reporting server

Found the answer.

You can use the OverrideName xml tag to rename the display name of an export format. See this Microsoft article.


u can do something like this:
add this to /etc/my.cnf

[mysqld]
expire_logs_days=3

and mysqld will delete them logs for you

if you talking about reduce the size of the table , i would advise this:

1st u need to sort from workbench or phpmyadmin , which tables consumes the most.
2nd maybe u can start plan on how to reduce that 'biggest table'
3rd u think to think or consult with your superior , how long u need to keep the record , some company might wanna keep 3months record only ,then dump and keep somewhere(cloud or Harddisk) , or 1 year or 3 years .
4th before dump , maybe u can create archive table.
eg: your original table 'test' , archive table rename as 'test_archive' then move older records or records that u want to dump there.
5th dump and move to somewhere safer.


What is the difference between start and enable in the Oracle RAC srvctl?

Enable means the service can be used and it will autostart when the cluster is restarted.
Also, you can have a service enabled, but with the current status stoped.
If you change the service to disable, you can't start the service.


Why is SQL Server required to do this task?  This isn't the objective of an RDBMS.  You would have to go through hoops to get SQL Server to find this data.

The quickest/easiest/most natural way to do this?  The Active Directory PowerShell module.  It's a one-liner:

Get-ADGroupMember "administrators" | Select-Object Name, DistinguishedName, ObjectClass

In short, keep logic like this away from SQL Server and put it in the OS where it belongs.


You need to do the unnest in a sub-query:

select d."key", array_agg(distinct x.v)
from data d
  cross join lateral unnest(d."values") as x(v)
group by d."key";

Set returning functions (like unnest()) should in general be used in the from part of a query. But to be able to reference a column from the table you need a lateral join.

from data cross join lateral unnest(...) is an explicit way of writing from data, unnest(...) which also generates a cross join. But I prefer the explicit cross join operator to document that I indeed intended to write a cross join, rather than accidentally.

This will however not preserve the order of the elements.

Online example: https://rextester.com/TVIDB57711


I use this pattern proposed in Exception Handling and Nexted Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end

This not only raises, but also can rollback to savepoint, if possible. This is very convenient in batch processing, the batch is not lost if a row has an error.

I never ever use return codes in stored procs. Returns codes beg to be ignored. I acknowledge the problem of nested errors as an annoyance, but with SQL Serevr 2012 you have the simple THROW; (no other arguments) to re-raise the original exception.


store two tables into a single table in mySQL

If you want to stream changes from tempA and tempA1 to temp, you can use trigger to do this
create trigger for both tables

Example:

DELIMITER $$
CREATE TRIGGER `insert_to_temp` AFTER INSERT
ON `tempa`
FOR EACH ROW BEGIN
    if new.txt1 is not null THEN
        insert into temp values(new.txt1);
 END IF;
END$$
DELIMITER ;

And do this for tempa1

but if you want to do a batch insert you can run:

insert into temp select * from tempa where txt1 is not null union select * from tempa1 where txt2 is not null


How to determine how much storage to store backups?

I believe in order to get the calculations correct to somewhat close to an appropriate figure would be collecting a data or a trend analysis as mentioned by Kin. There are various methods available to gather such data:

One approach would be analyzing a trend for the size of backup that has happened with its history being retained in msdb backup set from below query:

SELECT
[database_name] AS "Database",
DATEPART(day,[backup_start_date]) AS "Day",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'Databasename'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(dd,[backup_start_date]);

As you mentioned normal full backups, above should be OK, but it could be tedious if you have quite a large no of databases. But i believe you can come up with query on same with help of sp_foreachdb. Also, make calculations based on the factor of retention period for the backups on that drive accordingly.

But the one which i have been using is this excellent article from Chad miller on Database Space Capacity Planning which with help of a Powershell and SQL script might do the trick by gathering the data say for next 15 days or a month to help you in deciding or predicting the Vol space for that particular drive ( in my case i have a dedicated backup drive, so this works good for me)

As said

  You can use the process described in the article to create a
  consolidated space forecasting report, which focuses on a "days
  remaining" metric. In addition, the use of PowerShell to collect data
  and load into a SQL table as demonstrated in this article, provides a
  solution you can easily adapt to many database administration problems


Data from different tables into one table

What you are proposing is a variant of the so-called "Entity-Attribute-Value" or EAV design pattern.

Aaron Bertrand has an excellent article on the advantages and pitfalls of this design over on SQLBlog.org.

Many people trash-talk EAV, however if done correctly it can be the easiest way to manage diverse sets of data, and is in-fact designed particularly well for your scenario.


How do I install PostgreSQL in Debian/Ubuntu?

If you haven't set up the environment variables, then you need to specify the full path to your executables.

What version of Postgresql are you using? You show a link to 8.3 which is many years old. These docs are current: http://www.postgresql.org/docs/current/static/creating-cluster.html

Did you install Postgresql with an up-to-date package, a la http://www.postgresql.org/download/linux/ubuntu/ ?

When installing 9.2 on Ubuntu, I added the following lines to the ~postgres/.bash_profile

export PATH=/usr/lib/postgresql/9.2/bin:$PATH:/usr/sbin
export PGDATA=/var/lib/postgresql/9.2/main
export PGPORT=5432

Where "/usr/lib/postgresql/9.2/bin" is the path to your 9.2 executables. That made the installation a bit smoother for me.


Memory usage increases whenever I run stored procedures

I just want to know why SQL Memory increase whenever runs stored procedures.

Because SQL Server is a messy. It tries to keep everything it ever reads. The idea behind this is that using memory is faster than using disc. Not sure you agree here - the rest of the world does.

  After I stopped and restarted SQL. The memory just has 1 GB.

Because right after a restart obviously SQL Server has nothing read and thus nothing cached.

  Each time I run stored, the memory will raise and now it reach 8GB.

That is still a VERY small server, you know. I have seen machines using hundreds of gb. So what? It is faster than reading the data from disc.

  I don't know why and how to reduce SQL memory.

Why would you ever do that? Set good limits for memory use and then let SQL Server do it's work. After all, accessing cached data is many times faster than even reading it from SSD, you know.

If you are SERIOUSLY memory starved then set a good limit and if that leads to performance problems (particularly when dealing with small amounts like 8gb of memory) just plug in more RAM. RAM is not that expensive. At least as long as you  talk about such small amounts.

So, in short: SQL Server loves caching data under the correct assumption it makes it faster. If you have not set a limit, it will gladly do so up to the memory installed in the machine. It DOES release that, but having multiple servers fighting for memory is not good. If you are not running a dedicated SQL Server and/or are memory starved, set the max memory parameter in the server options to a sensible value.


Oracle Client sessions keep disconnecting

Check if sqlnet_expire_time is set in any of the sqlnet.ora files on the machine that hosts the DB, using some variant of

find / -name sqlnet.ora -exec grep -i sqlnet_expire_time {} \;

If it's not set in any of them then you can quickly rule out dead connection detection.  If it is set then you have DCD enabled and it's likely that which is killing your connections.

You are using three apps - my bet is that you're in one of them for more than the DCD time, so the other apps' connections are being killed, then when you switch to another app you find its connection has gone.  This is because they each have their own connection, and it doesn't matter how busy connection A is - if connection B is idle for the DCD time it will be killed.

If DCD is ruled out then the next place to check will be the firewall.  This needs to be SQL*Net aware; it's not just port 1521 (or whatever you've configured) that needs to be open, but the firewall needs to check the comms for the random port number the server returns to the client and open that as well - if it doesn't do that then the firewall will kill the "unauthorised/unknown" link, thus breaking the connection.


Building a where clause based on user selections

Non-dynamic dynamic where clause:

create table foo (
  gender char(1),
  age smallint,
  blood_type varchar(50)
);

insert into foo values
('m', 40, 'o+'),
('m', 20, 'b-'),
('f', 30, 'b+');

set @gender = 'm';
set @age = 40;
set @blood_type = null;

select
*
from foo
where
  (@gender is null or gender = @gender)
  and (@age is null or age = @age)
  and (@blood_type is null or blood_type = @blood_type)

http://sqlfiddle.com/#!9/c9d888/3


Replicate only specific records of a table

for replicating an specific set of data, you can create a view and replicate it.

here you can see what data you can publish

there is a small detail though:

  All types of replication allow you to replicate views. The view (and
  its accompanying index, if it is an indexed view) can be copied to the
  Subscriber, but the base table must also be replicated.

  For indexed views, transactional replication also allows you to
  replicate the indexed view as a table rather than a view, eliminating
  the need to also replicate the base table. To do this, specify one of
  the "indexed view logbased" options for the @type parameter of
  sp_addarticle (Transact-SQL). For more information about using
  sp_addarticle, see Define an Article.


System SPID running for hours filling up the TempDB Log

There are some newer DMVs designed specifically for troubleshooting what is happening in TEMPDB. Take a look at sys.dm_db_session_space_usage and sys.dm_db_task_space_usage. See this link for a discussion of those DMSVs


MariaDB | Sync Master with Slave in another Datacenter

The Master (in A) does very little work to provide the replication stream to the Slave (in B); don't worry about it.

There will, however, be a slight delay between when the data is available on the Master and when it is usable on the Slave.  But, since you are talking only about Disaster Recovery, I don't need to lecture on "Critical Read".

I think that the replication is opened by the Slave, so the port being opened needs to be on A.  Normally this is the same 3306 used for other clients.  (But your tunneling may lead to something else.)


MySQL: Can't find .ibd files after turning on innodb_file_per_table

The .ibd files will be under the innodb_data_home_dir under a subdirectory, the subdirectory name matching the database name.

Given innodb_data_home_dir=/etc/local/mysql/data and innodb_file_per_table is set:

 SHOW VARIABLES LIKE 'innodb_data_home_dir'
 SHOW VARIABLES LIKE 'innodb_file_per_table'

then when table test.mytable is created, the .ibd file will be located in /etc/local/mysql/data/test/mytable.ibd

(It's possible that the ibdata1 file will grow to accommodate rollback for transactions. When a transaction is run, pre-change copies of blocks get recorded, in case you issue a ROLLBACK, and also for the benefit of concurrent transactions (with an appropriate isolation levels), that need to reference only committed changes, and not any uncommitted changes.)


How to check who has permission to execute a stored procedure?

I would start by checking whether cross database ownership chaining is involved
You should be aware that this can introduce serious security problems
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server
I have had success with the scripts provided by Louis Davidson for working out effective permissions but I don´t know if his ambitions extend to cross database permissions
link for reference
https://www.red-gate.com/simple-talk/blogs/calculating-a-security-principals-complete-effective-rights/
Have you runa trace or extended events session to see the specific place you get the permissions error ?


ERROR 1031 (HY000) at line X: Table storage engine for 'TBL' doesn't have this option

Under MySQL 5.7, ROW_FORMAT=FIXED is no longer an option for the InnoDB Storage Engine.

According to the MySQL 5.7 Documentation for innodb_default_row_format, these are values you can use for the table's row format:

DYNAMIC, COMPACT, and REDUNDANT are allowed as default values
COMPRESSED is not allowed to a default value, but you can explicitly use ROW_FORMAT=COMPRESSED

There is no mention of ROW_FORMAT=FIXED for InnoDB in the MySQL 5.7 Docs.

I wrote an answer to a post about 8 months entitled Does an empty column value occupy same storage space as a filled column value? which explains the difference between COMPACT and REDUNDANT. You should find changing to eitherROW_FORMAT=COMPACT or ROW_FORMAT=REDUNDANT in the dump definition. If you want your VARCHAR columns to behave with the same characteristics as if ROW_FORMAT=FIXED still existed, just change all your VARCHAR columns to CHAR. Then, load the dump.

Please keep in mind that ROW_FORMAT=FIXED really benefits MyISAM (See my old post Which DBMS is good for super-fast reads and a simple data structure?)


Changing Instance Class:

From the documentation "Modifying a DB Instance and Using the Apply Immediately Parameter", changing the instance class and setting "Apply Immediately" to false will result in:

The change being applied during the next maintenance window.
The change causing an outage to occur.

Since the instance is Multi-AZ and you're changing the instance class, your outage will be minimized to the amount of time it takes to failover to the standby replica. That isn't very long; likely worst-case a minute or two. You won't lose data that is already in the DB. Any existing connections will be dropped during failover. You should verify that applications connected to your database can withstand these conditions, or alternatively take them down for maintenance during this time.

Identifying RDS instance storage capacity:

You can identify how much of the RDS storage is currently in use by either:

1) Simply selecting your RDS instance from the RDS web console and viewing the "Storage" bar under the "Monitoring" header.

2) Viewing the "FreeStorageSpace" RDS cloudwatch metric for your RDS instance.

Further Reading:

RDS Documentation - Viewing DB Instance Metrics


PostgreSQL copy text type column to text[] array type column

I was close, unlike JavaScript and PHP it seems PostgreSQL array key numbers start with 1, not 0.

UPDATE parts SET material_size_temp[1] = material_size;


mongodb moving value from NumberInt to NumberLong

If you are using MongoDB 3.0 + MMAPv1 with noPadding allocation (aka "exact fit"), then the document will have to move if you change from 32-bit ints (4 bytes) to 64-bit longs (8 bytes). With noPadding there is no extra space available to store more bytes, so you should start with NumberLongs to avoid expected document growth.

  The question is, whether mongodb will move the document if the NumberInt changes to NumberLong

You can confirm the outcome via the database profiler. Here's a quick run in the 3.0.7 mongo shell:

// Drop test collection
db.mycollection.drop();

// Create new test collection with noPadding (exact-fit allocation)
db.createCollection("mycollection", { noPadding: true });

// Enabling profiling of all operations
db.setProfilingLevel(2)

// Insert a document with a 32-bit int
db.mycollection.insert({ _id: 'nopadding', value: NumberInt(42) });

// Update document to have a 64-bit int
db.mycollection.update({ _id: 'nopadding' }, { $set: { value: NumberLong(10000000) }});

// Disable profiling
db.setProfilingLevel(0);

// Check if our update operation resulted in a move
db.system.profile.find({op:'update', 'query._id': 'nopadding', moved: true}).count()
1


This is called a cross join.

INSERT dbo.FinalTable(Column1, Column2)
SELECT t1.col, t2.col
  FROM dbo.Table1 AS t1
  CROSS JOIN dbo.Table2 AS t2;


Stand on the shoulders of your predecessors.

  If I use duplicate tables is that a scalable way for a website with millions of entries and much more revisions ?

"Duplicate tables"?  Plan A: one table with all revisions.  Plan B: 2 tables, one with the current value, one with all the previous revisions.  Plan C (really bad):  One table per revision.

  Which of the two database design will be better ?

Once you have gone through the exercise of defining "better", you will be halfway to the answer.

  How to change the schema in json based audits if schema changes ?

This sounds like a different dimension to the problem -- tracking of schema changes.  That, itself, is a very big task.  JSON has no problem adding/dropping extra "fields" as a schema changes.  However if a table is split, then JSON gets trickier.  You would probably need special code bridge the gap.

Bottom Line

Since you seem to be just starting in this endeavor, I suggest you flip a coin to decide which one to use.

But...  Plan on revisiting the decision in 6 months.  By then you will have enough experience with the code to have some feel for whether it will work for the direction you want to take it.

Sure, it will be painful (very painful) to switch after 6 months.  But it will be 'impossible' after 12 months.  You will probably decide to patch the chosen schema instead of switching to the other schema.

Or maybe you will spin off another company to use the other database schema.  A prediction:  You will find that the "grass is not greener" and the second company will fail.


Select row from non-empty node value in xml column using xml query

I have inferred some XML structure from your query but it is best if you supply some sample XML.  Does this work for you?

SELECT *
FROM dbo.Tasks
WHERE [Work details].exist('Root/Row/Completedon/text()') = 1

My full repro script:

CREATE TABLE dbo.Tasks
(
    taskId          INT IDENTITY PRIMARY KEY,
    [Work details]  XML
)
GO

INSERT INTO dbo.Tasks ( [Work details] )
VALUES ( '<Root>
    <Row>
        <Completedon>01/04/2016</Completedon>
    </Row>
</Root>' ),
( '<Root>
    <Row>
        <Completedon></Completedon>
    </Row>
</Root>' )
,
( '<Root>
    <Row>
        <Completedon/>
    </Row>
</Root>' )

GO

-- Original query; returns all records
SELECT [Tasks].*
FROM dbo.[Tasks]
WHERE ( [Work details].exist('for $x in /Root/Row where  ( (empty(xs:date($x/Completedon[1])) or ($x/Completedon[1])="")  )  return $x')>0 )

-- Revised query
SELECT *
FROM dbo.Tasks
WHERE [Work details].exist('Root/Row/Completedon/text()') = 1

And test results:


Mysterious inequality behavior when comparing date strings

What makes you think you are comparing dates?

Actually, you are comparing string literals which - in the absence of a cast context and any explicit cast - default to text:

SELECT '20150526' > '2015-05-26' AS text2text
     , '20150526'::date > '2015-05-26'::date AS date2date;

text2text | date2date
----------+----------
 t        | f

Try:

SELECT pg_typeof(col) FROM (VALUES ('20150527')) t(col);  -- no cast

pg_typeof
---------
text

The result of your comparison of text values depends on your locale, on COLLATION rules to be precise. To see the default COLLATION of your session:

SHOW lc_collate;

COLLATION can be also be set per column or even per expression. To see the actual COLLATION for any expression use the collation for (any) construct:

SELECT collation for (my_column) FROM tbl LIMIT 1;

To test how strings are sorted in your locale:

SELECT *
FROM  (
   VALUES
     ('20150526')
   , ('2015-05-25')
   , ('20150525')
   , ('2015-05-26')
   , ('20150527')
   , ('2015-05-27')
   ) t(col)
ORDER BY col;    -- COLLATE "C"

Compare this to the sort order by pure byte-value by appending COLLATE "C" to ORDER BY. To see the result of your comparison with a different COLLATION:

SELECT '20150526' > '2015-05-26' COLLATE "C" AS text2text_c;


So what instructions did you follow?

Below is an example with your commands, where I did not use sqlnet.ora at all, because it is optional in 19c:

SQL> !mkdir /u01/app/oracle/admin/MIN19_O71/wallet

SQL> alter system set wallet_root='/u01/app/oracle/admin/MIN19_O71/wallet' scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size                  8904776 bytes
Variable Size             297795584 bytes
Database Buffers          759169024 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL> alter system set tde_configuration="keystore_configuration=file" scope=both;

System altered.

SQL> administer key management create keystore identified by "mypassword";

keystore altered.

SQL> administer key management set keystore OPEN identified by "mypassword";

keystore altered.

SQL> !ls -l /u01/app/oracle/admin/MIN19_O71/wallet
total 0
drwxr-x---. 2 oracle oinstall 25 Dec 30 16:01 tde

SQL> !ls -l /u01/app/oracle/admin/MIN19_O71/wallet/tde
total 4
-rw-------. 1 oracle oinstall 2555 Dec 30 16:01 ewallet.p12

SQL> administer key management set key using tag 'master key' identified by "mypassword" with backup using 'masterbackup';

keystore altered.

SQL> !ls -l /u01/app/oracle/admin/MIN19_O71/wallet/tde
total 12
-rw-------. 1 oracle oinstall 2555 Dec 30 16:02 ewallet_2019123015024590_masterbackup.p12
-rw-------. 1 oracle oinstall 4171 Dec 30 16:02 ewallet.p12

SQL>


I'm not sure why you would need this but yes it is possible.

Insert into [dbo].[TimeStamp_Test] default values;

Or

Insert into [dbo].[TimeStamp_Test] values (default);

To do multiple rows you can use merge.


How can I transpose multiple unrelated columns into rows in SQL Server?

I think that this will give you what you are looking for.  You have to hop through a few CTE's to unpivot the data, then add some attribute information and then pivot it back, but the output does match what you provided...
I had to add a CTE_RawData to convert everything to the same data type since the unpivot doesn't like mismatches...
DROP TABLE IF EXISTS #test

--Create the table
CREATE TABLE #test (
PersonId VARCHAR(10),
EyeColor VARCHAR(20),
HairColor VARCHAR(20),
FavoriteTVShow VARCHAR(30),
FavoriteMovie VARCHAR(30),
FavoriteFood VARCHAR(30),
BirthInfo VARCHAR(30)
)

--Populate the table
INSERT INTO #test
VALUES
('A123', 'Brown', 'Black', 'The Price Is Right', NULL, 'Pizza', 'New York City'),
('A444', NULL, 'Brown', 'Friends', 'Elf', 'Steak', 'Seattle')

;WITH CTE_RawData AS
    (
    SELECT PersonID
        , CONVERT(NVARCHAR(30), EyeColor) AS EyeColor
        , CONVERT(NVARCHAR(30), HairColor) AS HairColor
        , CONVERT(NVARCHAR(30), FavoriteTVShow) AS FavoriteTVShow
        , CONVERT(NVARCHAR(30), FavoriteMovie) AS FavoriteMovie
        , CONVERT(NVARCHAR(30), FavoriteFood) AS FavoriteFood
        , CONVERT(NVARCHAR(30), BirthInfo) AS BirthInfo
    FROM #Test AS T
    )
, CTE_UP AS
    (
    SELECT PersonID
        , Attribute
        , [Value]
    FROM CTE_RawData AS T
        UNPIVOT ([Value] FOR Attribute IN (EyeColor, HairColor, FavoriteTVShow, FavoriteMovie, FavoriteFood, BirthInfo)) AS unpvt
    )
, CTE_UPRN AS
    (
    SELECT PersonID
        --, Attribute
        , [Value]
        , AttributeCategory = CASE  WHEN Attribute IN ('EyeColor', 'HairColor') THEN 'ColorInfo'
                                                                WHEN Attribute IN ('FavoriteTVShow', 'FavoriteMovie', 'FavoriteFood') THEN 'FavoriteInfo'
                                                                WHEN Attribute IN ('BirthInfo') THEN 'BirthInfo'
                                                                ELSE Attribute
                                                                END
        , RN = ROW_NUMBER() OVER (PARTITION BY PersonID, CASE   WHEN Attribute IN ('EyeColor', 'HairColor') THEN 'ColorInfo'
                                                                WHEN Attribute IN ('FavoriteTVShow', 'FavoriteMovie', 'FavoriteFood') THEN 'FavoriteInfo'
                                                                WHEN Attribute IN ('BirthInfo') THEN 'BirthInfo'
                                                                ELSE Attribute
                                                                END
                                    ORDER BY PersonID)
    FROM CTE_UP AS U
    )
SELECT PersonID
    , RN
    , ColorInfo
    , FavoriteInfo
    , BirthInfo
FROM CTE_UPRN AS U
    PIVOT (MAX([Value]) FOR AttributeCategory IN (ColorInfo, FavoriteInfo, BirthInfo)) AS pvt
ORDER BY PersonID
    , RN


How to configure MS SQL log on so that it can be used by another program?

Those options equal to:

windows authentication -> use a Windows (or domain) user account to connect to the database. Here you need to ensure that the user that runs the application (or the user that is configured to start the service) has access to the SQL Server (if you installed SQL Server and you're also installing the application, than most likely you already have access);
sql authentication -> create a SQL user that has access to the SQL Server and use it inside the application's connection strings. It is not dependent on the Windows/domain users, it's local only to that SQL installation.

If you're installing a new application that you're not familiar with, and it requests SQL Server access, then I suppose you should use Windows Authentication and check that the SQL Server service is started and accepts connections from that user. In SQL Server Configuration Manager you should check that the main SQL service is started and that it has enabled the main client protocols (TCP-IP and shared memory).


Login failed for user Error: 18456 State: 10

The state 38 error is probably a red herring: focus on the state 10 errors.

While KB Article #925744 says it is for SQL Server 2005, that is simply because of the date it was written. The symptom it causes would affect all versions of SQL Server that have been released since then in similar or identical ways.

Now, if user is a Windows login, perhaps you are somehow trying to authenticate as a Windows login without specifying the domain, or perhaps you are specifying the domain but the domain controller is unreliable or on an unreliable network connection. You should post your actual connection string in the question (fudging our real names/passwords of course).

You should also consider creating a new, separate login with the same permissions, and see if you intermittently get the same error with that other user as well - my guess this is an architectural problem in some way as opposed to a problem with just this user.

Another possibility is if your .net application is using Windows auth and the underlying password has been changed. Seems like a long shot, but check out the article and there is a hotfix available: KB Article #2545850.


The way I usually go about this is to use the procedure sp_WhoIsActive, and log that to a table. This way I don't have to worry about about the result of the procedure being right, cause I'm sure it is.

There are some great articles on the Brent Ozar website about it.

Brent Ozar logging sp_WhoIsActive

Brent Ozar Monitoring activity

Afterwards you can do whatever you want with that data.
You can transfer data from the logging table to the Dxxx.dbo.TDBMSG table, or send mails directly from the WhoIsActive table.


How to enable Oracle Database In-Memory feature in Oracle 11g R2 to reduce report generation time?

a) + b) The In-Memory Column Store option was introduced in version 12.1.0.2, it is not available in 11g.

Changes in Oracle Database 12c Release 1 (12.1.0.2)

You can cache tables in earlier versions as well, but the new In-Memory option is different. The point of the new In-Memory option is it stores tables in memory in columnar format, instead of the default row level format.

c) Logical standy, Goldengate (requires extra licence) or Streams.


Does postgres efficiently re-use space from deleted rows?

VACUUM rewrites the entire block, efficiently packing the remaining rows and leaving a single contiguous block of free space (though this space isn't zeroed and the physical disk file might contain the remnants of deleted rows which of course are in no way visible to the database user).

test schema:

--#psql postgres postgres
select oid from pg_database where datname='postgres';
/*
  oid
-------
 12035
*/
create schema stack;
set search_path=stack;
create table foo(bar text);
insert into stack.foo(bar) values('row 1');
insert into stack.foo(bar) values('row 2');
checkpoint;
select * from foo;
/*
  bar
-------
 row 1
 row 2
*/
select relfilenode
from pg_class c join pg_namespace n on n.oid=c.relnamespace
where nspname='stack' and relname='foo';
/*
 relfilenode
-------------
      446488
*/

physical contents of table backing file:

xxd -a /var/lib/postgresql/9.3/main/base/12035/446488

0000000: 3800 0000 f093 9d83 0000 0000 2000 c01f  8........... ...
0000010: 0020 0420 0000 0000 e09f 3c00 c09f 3c00  . . ......<...<.
0000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
*
0001fc0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001fd0: 0200 0100 0208 1800 0d72 6f77 2032 0000  .........row 2.. <---- this is row 2
0001fe0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001ff0: 0100 0100 0208 1800 0d72 6f77 2031 0000  .........row 1..

delete and vacuum:

delete from foo where bar='row 1';
vacuum stack.foo;
checkpoint;

physical contents of table backing file:

xxd -a /var/lib/postgresql/9.3/main/base/12035/446488

0000000: 3800 0000 e8b5 9e83 0000 0500 2000 e01f  8........... ...
0000010: 0020 0420 0000 0000 0000 0000 e09f 3c00  . . ..........<.
0000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
*
0001fc0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001fd0: 0200 0100 0209 1800 0d72 6f77 2032 0000  .........row 2.. <---- this is free space
0001fe0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001ff0: 0200 0100 0209 1800 0d72 6f77 2032 0000  .........row 2..

insert new row:

insert into stack.foo(bar) values('row 3');
checkpoint;

final physical contents of table backing file:

xxd -a /var/lib/postgresql/9.3/main/base/12035/446488

0000000: 3800 0000 c8ec 9e83 0000 0100 2000 c01f  8........... ...
0000010: 0020 0420 0000 0000 c09f 3c00 e09f 3c00  . . ......<...<.
0000020: 0000 0000 0000 0000 0000 0000 0000 0000  ................
*
0001fc0: 2476 4c14 0000 0000 0000 0000 0000 0000  $vL.............
0001fd0: 0100 0100 0208 1800 0d72 6f77 2033 0000  .........row 3.. <---- this is row 3
0001fe0: 2176 4c14 0000 0000 0000 0000 0000 0000  !vL.............
0001ff0: 0200 0100 0209 1800 0d72 6f77 2032 0000  .........row 2..

clean up:

drop schema stack cascade;


How to reclaim back space after deleting objects on SQL Server?

I assume the table in question is a heap. You can rebuild the table to reclaim the space, or create a clustered index.

ALTER TABLE dbo.table_name REBUILD;

And/or

CREATE CLUSTERED INDEX cix ON dbo.table_name(column_name);

You can also drop the table and re-create it.


Left join multiple tables for millions of rows on multiple columns

@EvanCarroll: Good point, I wanted to add this as question. The tables are not indexed as I don't know which method to use. Question edited. – MERose 16 secs ago

Red flag. Stop your query. Add your indexes. Rerun it. Assuming you have no indexes, add these and then paste the result of \d table for coauthorships and adjacencies`.

CREATE INDEX ON combinations ( source, target, year );
CREATE INDEX ON adjacencies ( source, target, year );
CREATE INDEX ON coauthorships ( source, target, year );
VACUUM ANALYZE combinations;
VACUUM ANALYZE adjacencies;
VACUUM ANALYZE coauthorships;

Also why are you using bigint if you only have millions of rows?


Doing two counts on a single table in a single query

You can use CASE statement for this scenario:

SELECT count(*) AS total_agents,
       SUM(CASE WHEN active = 't' THEN 1 ELSE 0 END) AS active_agents
FROM Agent;

Sample execution with sample data:

CREATE TABLE Agent (Id INT, Active CHAR(10));

INSERT INTO Agent (Active)
VALUES ('a'), ('b'), ('t'), ('c'), ('t'), ('d');

SELECT count(*) AS total_agents,
       SUM(CASE WHEN active = 't' THEN 1 ELSE 0 END) AS active_agents
FROM Agent;


Select only those records that have same multiple values for a particular column if it's existing?

By using ROW_NUMBER() and PARTITION BY first_origin_id, delivery_id then ORDER BY source, you can achieve your expected result.

So the query will be:

SELECT ID, code, first_origin_id, delivery_id, source FROM (
  SELECT ID, code, first_origin_id, delivery_id, source,
  ROW_NUMBER() OVER (PARTITION BY first_origin_id, delivery_id ORDER BY source, ID) AS RowNumber
  FROM pricing_supports
) Q
WHERE Q.RowNumber = 1

Please find the Live Demo


Error creating foreign key from MySQL Workbench

You will get this message if you're trying to add a constraint with a name that's already used somewhere else.

According to this post you can check your constraint like this:

If the table you're trying to create includes a foreign key constraint, and you've provided your own name for that constraint, remember that it must be unique within the database. Run this query to see if that name is in use somewhere:

SELECT
  constraint_name,
  table_name
FROM
  information_schema.table_constraints
WHERE
  constraint_type = 'FOREIGN KEY'
  AND table_schema = DATABASE()
ORDER BY
  constraint_name;


How to write a query which recursively returns all foreign key references to a column?

Use a recursive CTE, it's essentially no different to the examples that involve HumanResources.Employee, except that you're joining across more tables. Instead of EmployeeIDs, you're collecting object_id and column_id combinations.


How do I search to see if a MySQL user exists on the system?

Thanks to @matthewh's answer I managed to compose a query that would work when searching through a large number of users:

use mysql;
select  User, Host from  user where User like 'user%';

Note: <user> is whatever component of the username you need to search for, you may need a % on both sides if you don't know the start of the username, also <> are merely to note the variable, don't include them in an actual query, unless the username includes < or >.


Sql Server Backup - Log shipping vs VM snapshot

The problem with VM snapshots is 1) they aren't the cheapest option.  They generally cost more to store than your database backups.  and 2) they don't protect you from as many things.

To save money, just continue with your Full and Log backups, and keep a cold standby VM that you can start up and restore to in case of a disaster.  Just boot it up every couple of weeks to get patches, and test the restore process.

You can probably get this done with minimal changes to your current setup by perhaps modifying the log shipping jobs and shutting down your secondary VM.  You can bring it up every week and apply all the logs.  Then you're always just a few restores away from a complete database recovery.


Unable to connect to remote SQL Server instance after thorough troubleshooting

Something else you can have a look at is whether the TCP protocol for SQL Server is listening on the correct IP address(es). You can do this from SQL Server Configuration Manager.

Go the same screen where you enabled the TCP/IP protocol, in your case SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for MSSQLSERVER and right-click on TCP/IP. Once the new screen loads, click on the IP Addresses tab, and you will see something similar to this:

The IP that you are connecting to remotely should show up in one of these fields (e.g. IP1, IP2, etc.). To find out what the IP you are using resolves to, run
the following in a cmd session:

    C:\Users\hs>nslookup jxxxxxx.sxxxx
    Server:  UnKnown
    Address:  192.xxx.xxx.xxx

    Non-authoritative answer:
    Name:    jxxxxxx.sxxxx
    Addresses:  192.xxx.xxx.xxx <- This is the address

By default SQL Server listens on all addresses, but in some cases, the default isn't used and addresses need to be explicitly added. Note that if you are using NAT or port-forwarding, you might not see the external IP, in which case you will need to work with your network administrators to make sure the translation or forwarding address is correct.


Cannot Deploy SSIS Package in SQL Server Data Tools 2015 (Destination server name doesn't allow password entry)

You have a lot going on here.

Package vs Project deployment model

Starting with the 2012 release, SSIS projects have the choice between a Package Deployment Model and a Project Deployment Model. Project allows you to create project level artifacts - shared connection managers, project (and package) parameters. The default deployment model for new projects is Project Deployment Model.

The deployable unit of code from a project deployment model is the .ispac file. Your SSIS packages + project parameters + shared connection managers + manifest file get zipped up into a file named MyProject.ispac. The ispac file is deployed to a SQL Server catalog called SSISDB.

The deployable unit of code from package deployment model is the SSIS package itself. You can deploy the .dtsx file to a File System, the SSIS Package Store (also just the file system) or a SQL Server instance. However, the SSIS package will be stored in the MSDB (sysdtspackages90/sysssispackages)

The 2016 release complicates the above matrix in that we can now deploy .dtsx package(s) into the SSISDB. This is to address the incremental deployment story. Behind the scenes, it will generate an ispac and that's what's deployed. But that's only for a 2016 server which won't help your 2012 story.

SSIS Versions

SSIS packages have versions that correspond to the version of SQL Server and tooling you're working with (2005/2008/2008 R2/2012/2014/2016/v.Next). Packages are only* forward compatible so if you make the mistake of opening a 2005 package with the 2014 tooling (or deploy with it) to a 2005 instance guess what, you've upgraded to the current version and deployed to a location that doesn't speak that dialect.

2016 confuses this issue because the tooling now supports targeting (Project -> properties, Configuration Properties -> General: TargetServerVersion)

Deployment

Ispacs are deployed via isdeploymentwizard.exe or you can do it in TSQL.

-- You must be in SQLCMD mode
-- Otherwise, modify the value of $(isPacPath) down below
:setvar isPacPath "C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac"

DECLARE
    @folder_name nvarchar(128) = 'TSQLDeploy'
,   @folder_id bigint = NULL
,   @project_name nvarchar(128) = 'TSQLDeploy'
,   @project_stream varbinary(max)
,   @operation_id bigint = NULL;

-- Read the zip (ispac) data in from the source file
SELECT
    @project_stream = T.stream
FROM
(
    SELECT
        *
    FROM
        OPENROWSET(BULK N'$(isPacPath)', SINGLE_BLOB ) AS B
) AS T (stream);

-- Test for catalog existences
IF NOT EXISTS
(
    SELECT
        CF.name
    FROM
        catalog.folders AS CF
    WHERE
        CF.name = @folder_name
)
BEGIN
    -- Create the folder for our project
    EXECUTE [catalog].[create_folder]
        @folder_name
    ,   @folder_id OUTPUT;
END

-- Actually deploy the project
EXECUTE [catalog].[deploy_project]
    @folder_name
,   @project_name
,   @project_stream
,   @operation_id OUTPUT;

That sounds like what you want because you can be a local SQL User but if memory serves, the above functions do this impersonation jiggery that goes awry if you're not a Windows user.

This application requires one of the components..

With 2005/2008/2008 R2 you could only get SSDT, nee BIDS, with the installation media. This meant you needed Developer Edition at a minimum to build out your SSIS Packages. Starting with 2012, you could download the SSDT tooling directly from MS without having a license for SQL Server. However, you are only licensed for development purposes. The command line tools are not classified as development tooling so dtutil or dtexec will both fail a licensing check and report the above (or a similar error message).

The resolution, as you point out, is to install the Integration Services Service on the machine that requires "more" than just Integration Services development. Be aware that this does count as an installation of SQL Server from a licensing perspective so be sure the person in your organization that handles licensing is aware of machines where you have done this so you don't have an unpleasant surprise come audit time.


Date of last change of an InnoDB table structure

I would say yes because any ALTER TABLE must change the timestamp of the last write to .frm.

To pull the UNIXTIME out, you would do this

ls -l --time-style=+"%s" mysql/data/mydb/mytable.frm | awk '{print $6}'

or to get the date time

ls -l --time-style=+"%Y-%m-%d %H:%M:%S" mysql/data/mydb/mytable.frm | awk '{print $6,$7}'

MySQL does provide an UPDATE_TIME column, but that's for changes to data or indexes.


Yes, using innodb_log_group_home_dir will put your ib_logfile* on the specified location. You can also opt to do the following:

[mysqld]
datadir=/var/lib/mysql
innodb_log_group_home_dir=/iblog
innodb_data_home_dir=/ibdata

So the system tablespace (ibdata1) goes inside /ibdatafiles folder, while the redo log files (ib_logfile*) goes inside /iblogfiles.

# ls -l /ibdata
total 12296
-rw-rw---- 1 mysql mysql 12582912 Jul 17 08:59 ibdata1
# ls -l /iblog
total 98320
-rw-rw---- 1 mysql mysql 50331648 Jul 17 08:59 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jul 17 08:59 ib_logfile1
# ls -l /var/lib/mysql
total 48
-rw-rw---- 1 mysql mysql  56 Jul 17 08:59 auto.cnf
-rw-rw---- 1 mysql mysql   5 Jul 17 08:59 centos1.pid
drwx------ 2 mysql mysql   0 Jul 17 08:59 mysql
-rw-rw---- 1 mysql mysql 151 Jul 17 08:59 mysql-bin.000001
-rw-rw---- 1 mysql mysql  19 Jul 17 08:59 mysql-bin.index
srwxrwxrwx 1 mysql mysql   0 Jul 17 08:59 mysql.sock
drwx------ 2 mysql mysql   0 Jul 17 08:59 performance_schema
drwx------ 2 mysql mysql   0 Jul 17 08:59 test


As mustaccio states, partitioning the data may help, though that might not be practical as a quick solution and you'd still have to optimise such statements a bit.

mysql is said to be particularly inefficient with IN clauses, in this case it may be running that inner query once for every row in mytable which is not going to be efficient. Better but still far from optimal, it may be running the inner query and spooling the results into a temporary table on disk then joining on that.

To avoid IN, you can rearrange actions of the pattern:

DELETE FROM mytable1 WHERE value IN (SELECT key FROM mytable2 WHERE <filtering_condition>)

into

DELETE t1
FROM   mytable1 t1
INNER JOIN
       mytable2 t2
ON     t2.key = ti.id
WHERE  <filtering_condition>

(in your case both mytable1 and mytable2 are the same table, and that works just as well)

I'm not sure how this will react to the counting-in-a-variable syntax you have there though (I'm not a mysql person specifically and that is not something seen in other DBs I do work with regularly).

If you add an integer identity column (I assume your PK here is id, time hence the current integer ID is not unique) then simply checking it against modulo 5 may be an acceptable approximation of "delete 80% evenly" like so:

DELETE t1
FROM   mytable t1
WHERE  t1.time < "2013-08-08 00:00:00"
AND    t1.counter MOD 5 != 0

Adding that column initially will be a time consuming process but maintinaing it afterwards should not be a problem (the DB will generate a number for you on each insert, just make sure you don't include it in the VALUES list of any INSERT operation), but you don't need to use IN or JOIN at all. An index over time, counter (instead of just time) may help performance a bit more. I would be tempted to make counter the primary key and id, time a seperate index (as well as an index over time or time, counter), but that would depend a lot on your other operations on the data.

Of course once you are altering the table structure like this, do give consideration the the partitioning option too. It will be more complicated but may have significant beneficial performance impact elsewhere too.


SQL Server Filetable document properties

It looks like (at this time) the best you are going to be able to do is use the keywords on the property, join them up to the doc and cross your fingers it is enough.

SELECT
    keyword,
    display_term,
    column_id, document_id, property_id
FROM sys.dm_fts_index_keywords_by_property
(
    DB_ID('FileTableDB'),
    OBJECT_ID('FileTableTb')
);

MSDN on that DMV - http://msdn.microsoft.com/en-us/library/ee677646.aspx

Here is a discussion with Bob Beauchemin and I on the topic:
https://twitter.com/bobbeauch/status/275101491084292096

Bob told me about the DMV mentioned above and pretty much confirmed that it is the most likely way.

Here is the Connect ticket Bob created for this topic:
https://connect.microsoft.com/SQLServer/feedback/details/773212/provide-complete-fts-property-values-in-a-dmv-side-table

We also go on to talk about how it might be better to have a preprocessor of sorts to pull the meta info out, store it, (not sure if you normalize format of your docs or not), and then reprocess if modified. Bob suggests perhaps being able to pull this off with a SQLCLR trigger/proc which would be a rather clever solution and get your hands all kinds of dirty.


You're not supplying a correct connection string. If you're using ezconnect, you need to pass the connection string in the format: //host:port/service-name

The expdp command-line should be:

expdp user/pass@//host:port/service-name full=Y VERSION=10.2 directory=m_dump dumpfile=DB10G.dmp logfile=expdpDB10G.log

So, something like:

expdp user/pass@//192.168.10.8:1521:DB10G full=Y VERSION=10.2 directory=m_dump dumpfile=DB10G.dmp logfile=expdpDB10G.log

If your local machine is not configured to use ezconnect, you need to specify the TNS name for the database, like so:

expdp user/pass@DB10G full=Y VERSION=10.2 directory=m_dump dumpfile=DB10G.dmp logfile=expdpDB10G.log

... and ensure there's a relevant TNS entry in your tnsnames.ora file.


how to alter sequence in db2 stored procedure by another user

Effect of DYNAMICRULES bind option on dynamic SQL

You must set the DYNAMICRULES bind option to BIND before the routine creation to make dynamic sql statements of the routine execute with the routine creator authorization. They are executed with authorization of a user who runs the routine otherwise.
You can do it by calling the following statement before the CREATE OR REPLACE PROCEDURE statement in the same session:

CALL SET_ROUTINE_OPTS('DYNAMICRULES BIND')

But you are not able to execute dynamically the following statements with such a bind option as described in Table 2. Definitions of Dynamic SQL Statement Behaviors:

GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET INTEGRITY, and SET EVENT MONITOR STATE

This means, that you are not able to run dynamically ALTER SEQUENCE with an authorization different from the invoker's one.
You may grant ALTER privilege on sequence to your developer to resolve the issue.


How to get useful error information from mysql command?

Quoting of column names and table names (and some other things) is with backtics:

(`key` INT ...

You can change that with a sql_mode, but MySQL users rarely do that.

In the long run, I think you will abandon using shell scripts to run MySQL commands.


Any reasons trigger AFTER INSERT would not fire?

It is possible to disable a trigger. (The user could have disabled it) Also, it is possible that there are other triggers that return an error and rolls back the transaction, wich means the after trigger is not called.


Trigger to drop table only if there are no records

I posted the question as I had just spent 2 days working this out and have found the answer:

    /****** Object:  DdlTrigger [TR_ProtectCriticalTables]    Script Date: 07/12/2015 09:02:05 ******/
    DROP TRIGGER [TR_ProtectCriticalTables] ON DATABASE
    GO

    /****** Object:  DdlTrigger [TR_ProtectCriticalTables]    Script Date: 07/12/2015 09:02:05 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TRIGGER [TR_ProtectCriticalTables]
    ON DATABASE
    FOR
     DROP_TABLE

    AS
    DECLARE @eventData XML,
            @uname NVARCHAR(50),
            @oname NVARCHAR(255),
            @sname nvarchar(255),
            @TABLEName nvarchar(255),
            @otext VARCHAR(MAX),
            @etype NVARCHAR(100),
            @edate DATETIME,
            @sql varchar(max),
            @RowCount bigint
    SET @eventData = eventdata()
    SELECT
            @edate=GETDATE(),
            @uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
            @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
            @otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                    'VARCHAR(MAX)'),
            @etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
            @sname=@eventdata.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME')

    ROLLBACK -- have to undo the drop to be able to get row count

    set @RowCount = (SELECT i.rowcnt
                        FROM sysindexes AS i
                            INNER JOIN sysobjects AS o ON i.id = o.id
                            inner join sys.tables as t on o.id = t.object_id
                            inner join sys.schemas as s on t.schema_id = s.schema_id
                        WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
                        and o.name = @oname
                        and s.name = @sname)
    IF @RowCount>0
    BEGIN
        DECLARE @err varchar(1000)
        SET @err = 'Table ' + @sname +'.' + @oname  + ' has rows and cannot be dropped. Rows: ' + convert(varchar(255),isnull(@RowCount,-1))+ '. '
        RAISERROR (@err, 16, 1)
        return
    END
    else
    begin

        set @sql = '
        disable trigger [TR_ProtectCriticalTables] on database
        drop table [' + @sname + '].[' + @oname + ']'
        exec(@sql)
        set @sql = '
        enable trigger [TR_ProtectCriticalTables] on database'
        exec(@sql)
    exec(@sql)
    end

    GO

    SET ANSI_NULLS OFF
    GO

    SET QUOTED_IDENTIFIER OFF
    GO

    ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE
    GO


MySql - Get all children in JOIN if one child matches a condition

SELECT p.*, c2.*
FROM Parent p
INNER JOIN Child c
    ON c.parent_id = p.id
INNER JOIN Child c2
    ON c.parent_id = c2.parent_id
WHERE
    c.first_name LIKE '%abc%' OR c.last_name LIKE '%abc%'


Postgres: query multiple records using a list of double keys

Well, it all depends on your scenario. If it is a one-off import, you create a junction table and use it for your joins.

CREATE TABLE people_at_companies (
  company_attrib TEXT,  -- whatever type it is
  person_attrib TEXT    -- whatever type it is
)

INSERT INTO people_at_companies VALUES
('some_company_attrib123', 'some_person_attrib456'),
('some_company_attrib789', 'some_person_attribxyz'),
...  -- remember to remove the last comma :)
;

SELECT j.* FROM jobs j
  INNER JOIN people p ON p.id = j.person_id
  INNER JOIN companies c ON c.id = j.company_id
  INNER JOIN people_at_companies pac ON
    c.some_company_attrib = pac.company_attrib
    AND
    p.some_person_attrib = pac.person_attrib
;

Having such junction table might improve performance, and it allows creating indexes.

If the junction data is dynamic in such a way that keeping a junction table in sync is too difficult (a very unusual situation, I’d say), we can avoid the junction table but, at the very least, using VALUES instead of arrays is way simpler:

SELECT j.* FROM jobs j
  INNER JOIN people p ON p.id = j.person_id
  INNER JOIN companies c ON c.id = j.company_id
  INNER JOIN (VALUES
    ('some_company_attrib123', 'some_person_attrib456'),
    ('some_company_attrib789', 'some_person_attribxyz'),
    ...  -- remember to remove the last comma :)
  ) AS pac(company_attrib, person_attrib) ON
    c.some_company_attrib = pac.company_attrib
    AND
    p.some_person_attrib = pac.person_attrib
;

You might have to explicitly cast the first row of the VALUES with the types of companies.some_company_attrib and people.some_person_attrib if PostgreSQL doesn’t figure the correct ones out. E.g.

  ('some_company_attrib123'::TEXT, 'some_person_attrib456'::TEXT)


i made 3 changes to your SP:

initialize exit_loop to FALSE
change the name of each variable by adding "V_" to make them different to the column name:
close the cursor outside the loop

below the end result hopefully that solved your issue:

DELIMITER //
CREATE PROCEDURE `Import_Leave`()
BEGIN
 DECLARE V_AbsenceID INT;
 DECLARE V_PersonID INT;
 DECLARE V_EmpID INT;
 DECLARE V_Start_Date DATETIME;
 DECLARE V_Numdays INT;
 DECLARE V_Category VARCHAR(50);
 DECLARE V_WorkFlow INT;

 DECLARE exit_loop BOOLEAN DEFAULT FALSE ;

 DECLARE absence CURSOR FOR SELECT AbsenceID,PersonID,EmpID,Start_Date,Numdays,Category,Workflow FROM staging_holidays WHERE EmpID is NOT NULL;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

 OPEN absence;

 shoop_da_woop: LOOP
    FETCH absence into V_AbsenceID,V_PersonID,V_EmpID,V_Start_Date,V_Numdays,V_Category,V_Workflow;

    IF exit_loop THEN
        LEAVE shoop_da_woop;
    END IF;
    #Code to process the data goes here, but is never executed asthe cursor is coming back empty
 END LOOP shoop_da_woop;

 CLOSE absence;

 END//
 DELIMITER ;


Getting the referenced object from SQL Trace

Audit Add Role Event is actually auditing the addition of a new role (e.g. CREATE ROLE). To audit adding a new member to an existing role, you want either 108 - Audit Add Login to Server Role Event or 110 - Audit Add Member to DB Role Event, depending on which type of role you actually mean.

Here's an example of each:

Database user added to database role:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT RoleName, TargetUserName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 110
-- AND DatabaseName = N'YourDatabase'
ORDER BY StartTime DESC;

Login added to server role:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT RoleName, TargetLoginName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 108
ORDER BY StartTime DESC;

You can also add other columns from the trace to see what application, host, and login actually performed the action. Combining the two queries above, and adding auditing columns:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
  RoleName, TargetUserName, TargetLoginName, DatabaseName,
  ApplicationName, HostName, LoginName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (108, 110)
AND COALESCE(DatabaseName, N'?') = CASE EventClass
--   WHEN 110 THEN N'YourDatabase' ELSE N'?' END
ORDER BY StartTime DESC;

Now when I do something like this on my system:

USE master;
GO
CREATE LOGIN floob WITH PASSWORD = 'x', CHECK_POLICY = OFF
ALTER SERVER ROLE [dbcreator] ADD MEMBER floob;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER blat WITHOUT LOGIN;
ALTER ROLE db_datareader ADD MEMBER blat;

I can then run the above query, and I get the following results (leaving audit columns out of the output for brevity):

RoleName       TargetUserName  TargetLoginName  DatabaseName  ...audit columns...
-------------  --------------  ---------------  ------------
db_datareader  blat            NULL             splunge
dbcreator      NULL            floob            master

Clean up:

DROP USER blat;
GO
USE master;
GO
DROP DATABASE splunge;
DROP LOGIN floob;

Note that this will only tell you about events that haven't been aged out of the default trace, so if you're trying to find out when someone was added to a role last year, it's unlikely you're going to find it.

Also, as an aside, it's much more efficient to hard-code the event IDs in the query than to go look up the classes every time. You can see how to get the list of available events in any trace here (as well as examples of other information you can get from the default trace).

And finally, your TOP 1 without ORDER BY assumes that the default trace will always come out first. This is not necessarily always going to be true, even if it is what you observe most of the time (see #3).


How does PostgreSQL physically order new records on disk (after a cluster on primary key)?

Rows in postgresql have no fixed order. Not only are records placed where ever there is free space records can also move around. This is because when a row is updated a new version of the row is created in a new location while the old version continues to live in it's old location until it's removed by vacuum.

A CLUSTER operations sorts all rows but does indeed not affect how postgresql add's rows. So the data won't stay sorted. However postgresql keeps statistics among which is the correlation for each column between the order of the rows in the tables and the sorted order of that column. So the planner can still optimize it's plan based on the statistics that tell it the table is still mostly sorted even if some rows have been added after a cluster operation (or have been moved by updates).


You must get execution plan of each slowly query first, and survay required Index.

for change timeout time use below address in SSMS:

Tools\Options\Designers\Trasnaction time-out after

Default value for timeout is 30s and you change this value to about 6000s and then create own index.
The above path is in SQL Server Management Studio 2008 R2


You can split the search parameters and use a join instead. Example

-- Some data to test with
CREATE TABLE testdata ( x varchar(100) not null primary key );
INSERT INTO testdata (x) VALUES ('a'),('c'),('e');

-- This can be a temporary table, add all numbers up to
-- the maximum number of tokens in parameter string
CREATE TABLE positions (pos int not null primary key);
INSERT INTO positions (pos) VALUES (1),(2),(3),(4),(5);

-- Table that hold tokens, this can also be a temporary table
CREATE TABLE tokens (token varchar(100) not null primary key);

-- Split the search string in individual tokens, and store in table
INSERT INTO tokens
SELECT substring_index(
           substring_index(T.tokens, ',', p.pos)
         , ','
         , -1
       ) as token
FROM (SELECT 'a,b,c,d' as tokens) as T -- 'a,b,c,d' is the search string
JOIN positions p
  ON char_length(T.tokens)
    - char_length(replace(T.tokens, ',', ''))
    >= p.pos - 1;

-- Get all testdata that matches search string
SELECT t.*
FROM testdata t
JOIN tokens tt
    ON t.x = tt.token;

x
-
a
c


Add query executed user information to Mysql binlog

Not sure about general log, but this behavior belongs to auditing system.  If you don't insist to write into binlog, both Oracle and Percona offer solutions:

https://dev.mysql.com/doc/refman/5.6/en/audit-log.html
https://www.percona.com/doc/percona-server/5.6/management/audit_log_plugin.html


Redis has "commands" that mutate the various data types it can store. Why? Redis is primarily a cache

Redis is a Key-Value (KV) store, and KV stores generally are well suited for cache however provide additional functionality.

KV stores can provide synchronisation between a variety of stateless front ends which is what this commands provide.


The safest course of action is to stop MySQL on both machines and copy the table files you need from the mysql schema from slave to master, saving copies of the files.  As long as they are MyISAM tables (not InnoDB), this is a legitimate operation.

If you can't shut down the machines, then connect to both of them and run FLUSH TABLES WITH READ LOCK; wait for the prompt to return, and then leave the connections open (to hold the locks) while copying the files.  Then UNLOCK TABLES; to release the global read lock.  MyISAM tables are pretty flexible this way... you need to copy the MYD and MYI files for the tables, together in pairs, and you should probably copy the matching .frm files on the off chance that they aren't identical (though they should be)... just save copies of anything you overwrite, and don't try this with InnoDB tables.

This should restore the procedures; you might need to restart the server for them to be recognized by all threads (but I don't think so).

If changes to tables in mysql normally replicate in your setup, that tends to suggest underlying system trouble ... or someone else with access to the system not quite knowing with they are doing ... or a potential security breach, and, unfortunately, none of these things really jumps out to me as more likely than any other, though my instinct is to be "suspicious of malicious."

Since you do have binary logging enabled, you should review the binlogs from around the period of time the timestamps suggest using mysqlbinlog --verbose --base64-output=decode-rows, since even though the slave didn't change its tables, that doesn't mean there couldn't be something interesting in the binlog that changed something else that you haven't yet discovered, or that did not have the same impact on the slave for any number of other reasons, like replicate-ignore-db.

I would check all of the grant table contents along with everything else in the mysql schema.  I would also reconfigure the servers with log_warnings = 2, which logs access denied errors, and I might even be tempted to turn on the general query log for a while if your system has the I/O capacity to support that without performance penalty.  I would further configure the local machine to forward a copy of its syslog messages to another system with tightly restricted access, and thoroughly audit the firewall... all of this is a little bit alarmist-sounding, but without some other evidence of filesystem problems, that certainly can't be considered the most likely thing to have happened.


I've decided to not use the cnf file, instead I used .yaml in solving this.

Thanks for answering.


No, "recursive" type definitions are not possible in Postgres.
You cannot reference a type in CREATE TYPE that is not registered, yet.
(What would terminate the recursion anyway?)


SQL Job PowerShell task fails same command succeeds as user

cd C:

Get-ChildItem -Path \\FileServer\SQLBackups\SQLServer\ -File -Recurse -Force | Where-Object {($_.LastWriteTime -lt (Get-Date).AddDays(-20))}|Remove-Item

https://www.sqlservercentral.com/forums/topic/powershell-for-network-path
http://www.midnightdba.com/Jen/2013/05/quick-tip-navigating-to-a-unc-within-sqlps/

Adding the cd C: line to my Powershell task in SQL Job allowed UNC references to resolve remote server file directory.

To quote a wise man:
"SQL Server Agent integrates with powershell in very unintuitive ways sometimes"

Thanks for your help, Peter.


MariaDB/MySQL IO limit in test environment

InnoDB only processes in "RAM" -- specifically its "buffer_pool".  InnoDB will persist things to "disk".  So InnoDB will be doing a lot of ram-to-ram copies if you use a RAM-disk.  However, you could save some RAM by decreasing innodb_buffer_pool_size and various other settings.  Caution:  don't lower them too much, or there can be failures.


How do I check how many rows a query will affect before executing it?

If ExperimentID is the primary key or has unique constraint/index, there is no need to limit the number of rows updated since there will never be more than one row updated.  If ExperimentID is not unique by itself, I suggest you add additional criteria to identify the desired row.  The TOP approach you provided will limit the number of rows updated for non-unique values but the one updated will be arbitrary unless your order by additional columns such that the updated row deterministic.

To validate no more than the expected number of rows are modified, one method is to perform the DML in a transaction and rollback if the number of rows is greater than expected.  Below is a parameterized example of this technique:

SET XACT_ABORT ON;

BEGIN TRY

    BEGIN TRAN;

    UPDATE Jobs
    SET JobStatus = @NewJobStatus
    WHERE ExperimentID = @currentID;

    DECLARE @RowCount int = @@ROWCOUNT;

    IF @RowCount > @MaxRowCount
    BEGIN
        RAISERROR ('Attempt to update %d rows, more than the %d row(s) allowed.  Action not performed.', 16, 1, @RowCount, @MaxRowCount);
    END;

    COMMIT;

END TRY
BEGIN CATCH

        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;

END CATCH;


MySQL Join When Record in Joined Table Does Not Exist

A WHERE-condition on the inner table will change the the result to an Inner Join instead. You need to move this condition to the ON:

SELECT
    Confessions.ID, Confessions.WriteTitle, Confessions.WriteArea,
    Confessions.POST_DATE, Votes.Type
FROM Confessions LEFT OUTER JOIN Votes
  ON Confessions.ID = Votes.PostID
 AND Votes.IP_Check = :IP_Check
WHERE ForUser = :ForUser AND Status = :Status

As a rule of thumb: Normally conditions on the Outer table are in WHERE while condition on the Inner table are in ON.


Sounds like a great opportunity to use a webservice to me.

on your customers end, whenever an order is entered, either:

have the site update the customers local database and post a webservice call to your backend with the order details.
have the order inserted only in the customer local database with a sync flag set to unsynced. have a background process poll for such unsynced orders and post them to your web service.

on your end, do the same in reverse;

whenever an order is prepared, post to the originating customers' webservice with the new details so that their data can be updated.

now you need not worry about the database or potentially even the dbms used.


Deferrable constraints are usually wrong. It's a proof that your ORM does something wrong. Usual problems are

commit might fail. This is usally not expexted by various tools and also by developers
commit might last very long. This might be problem with distributed(XA) transactions.
also troubleshooting might be problematic, especially when using Java(JDBC). When commit fails you can hardly say which particular constraint failed the transaction


First, the query you have is not valid. I assume you omitted the GROUP BY a.email when posting the question. You also alias foo as c but then you reference to i.total.
Now for the missing results, it's because you are using (implicit) inner joins. That they are implicit, it doesn't affect results. But when using explicit joins, it's easy to change them from INNER to LEFT join.
So by doing a LEFT JOIN b we keep results from a that have no matching row in b and by doing b LEFT JOIN c we keep results from b that have no matching row in c.
The query finally looks like this (adding COALESCE() so we get zeros and not nulls for the emails that have no account or total):

select a.email,
       coalesce(sum(c.total), 0) as sum_total
from flags as a
  left join accounts as b
    on a.email = b.email              -- join condition
  left join foo as c
    on b.id = c.account_id            -- join condition
group by a.email ;


Is Oracle not suited as the database behind websites like Stackoverflow?

It just means that the developers were more familiar with the Microsoft stack.


I hesitate to recommend this answer to a self described novice, but it seems to fit the case pretty well.

If were working this problem, I would go with a star schema approach. There would be three dimension tables:  Employees, Skills, and Levels.  And there would be one fact table, with foreign keys to all three tables, and no other columns (aka fields).

This particular fact table only conveys a count, and no other measure.

I might go ahead and make the hierarchy of skills a snowflake off of the skills dimension.

If you are new at database design none of this would be familiar to you at all.  When people begin to work with star schemas,  they usually have a few years of database design under their belt.  But just in case you have access to a good tutorial on star schema,  you might want to follow through here.

In addition, if I could fit it into the budget, I would want to buy some sort of business intelligence software that would allow the managers could use to browse this data without learning the table structure.  Failing in that,  I would just try to build in the kind of drill down capabilities the managers need into my app.

I hope all of this isn't mystifying.  Don't worry if it is.  Basically the fact table is capturing a many to many to many relationship,  which probably isn't covered in your learning materials.


Transact-SQL script equivalent to the option: Apply Tracked Changes

There's an interesting discussion about this here:
https://www.sqlservercentral.com/Forums/Topic1480807-2799-1.aspx

and I reckon that you will find everything you need here:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-fulltext-index-transact-sql?view=sql-server-2017

Basically it seems to be something like:

ALTER FULLTEXT INDEX ON [table_name] START UPDATE POPULATION


Idea-1 does not sound good as you have realized yourself, as each video will probably have a different length. You would have numerous rows with empty/null columns.

Idea-2 sounds better and is a more appropriate solution for an SQL database. Also regarding your bonus question, this approach would allow you to create a table with many-to-many relations users<->video_sections, thus you could have the per user, per video section data.

Also, for me, a good fit for this problem seems to be using a NoSQL database (for example MondoDB).
NoSQL is better suited to data with non-concrete structure, and also with NoSQL db it would be easier to scale horizontally in case the data grows exponentially (a concern you have identified in your Idea-2).
Here is a quick example structure that came off the top of my head:

videos: [
    {
        id: 1,
        name: Video1,
        length: 238,
        views: 3125,
        sections: [
            {
                seq_id: 1
                views: 10
            },
            {
                seq_id: 2
                views: 5
            },
            { ... },
            { ... }
        ]

    },
    {
        id: 2,
        name: Video2,
        ...
    }
]

users: [
    {
        id: 1,
        name: User1,
        videos_watched: [
            {
                video_id: 1
                sections_watched: [
                    1, 2, 3, ...
                ]
            },
            {
                video_id: 2
                sections_watched: [
                    1, 2, 3, 4, 5, ...
                ]
            }
        ]

    },
    {
        id: 2,
        name: User2,
        videos_watched: [
            ...
        ]

    },
    ...
]

With NoSQL db and this structure you will be able add sections to each specific video dynamically when needed. As each user goes through a video, section, your application logic will just increment the views for each particular section and add the video/section ID to the list of watched videos/sections for that particular user.