In Sql Server, is there a way to check if a selected group of rows are locked or not?

If I understand the request correctly, the goal is to delete batches of rows, while at the same time, DML operations are occurring on rows throughout the table. The goal is to delete a batch; however, if any underlying rows contained within the range defined by said batch are locked, then we must skip that batch and move to the next batch. We must then return to any batches that were not previously deleted and retry our original delete logic. We must repeat this cycle until all required batches of rows are deleted.

As has been mentioned, it is reasonable to use a READPAST hint and the READ COMMITTED (default) isolation level, in order to skip past ranges that may contain blocked rows. I will go a step further and recommend using the SERIALIZABLE isolation level and nibbling deletes.

SQL Server uses Key-Range locks to protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level...find more here:
https://technet.microsoft.com/en-US/library/ms191272(v=SQL.105).aspx

With nibbling deletes, our goal is to isolate a range of rows and ensure that no changes will occur to those rows while we are deleting them, that is to say, we do not want phantom reads or insertions. The serializable isolation level is meant to solve this problem.

Before I demonstrate my solution, I would like to add that neither am I recommending switching your database's default isolation level to SERIALIZABLE nor am I recommending that my solution is the best. I merely wish to present it and see where we can go from here.

A few house-keeping notes:

The SQL Server version that I am using is Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
My test database is using the FULL recovery model

To begin my experiment, I will set up a test database, a sample table, and I will fill the table with 2,000,000 rows.

USE [master];
GO

SET NOCOUNT ON;

IF DATABASEPROPERTYEX (N'test', N'Version') > 0
BEGIN
    ALTER DATABASE [test] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [test];
END
GO

-- Create the test database
CREATE DATABASE [test];
GO

-- Set the recovery model to FULL
ALTER DATABASE [test] SET RECOVERY FULL;

-- Create a FULL database backup
-- in order to ensure we are in fact using
-- the FULL recovery model
-- I pipe it to dev null for simplicity
BACKUP DATABASE [test]
TO DISK = N'nul';
GO

USE [test];
GO

-- Create our table
IF OBJECT_ID('dbo.tbl','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.tbl;
END;
CREATE TABLE dbo.tbl
(
      c1 BIGINT IDENTITY (1,1) NOT NULL
    , c2 INT NOT NULL
) ON [PRIMARY];
GO

-- Insert 2,000,000 rows
INSERT INTO dbo.tbl
    SELECT TOP 2000
        number
    FROM
        master..spt_values
    ORDER BY
        number
GO 1000

At this point, we will need one or more indexes upon which the locking mechanisms of the SERIALIZABLE isolation level can act.

-- Add a clustered index
CREATE UNIQUE CLUSTERED INDEX CIX_tbl_c1
    ON dbo.tbl (c1);
GO

-- Add a non-clustered index
CREATE NONCLUSTERED INDEX IX_tbl_c2
    ON dbo.tbl (c2);
GO

Now, let us check to see that our 2,000,000 rows were created

SELECT
    COUNT(*)
FROM
    tbl;

So, we have our database, table, indexes, and rows. So, let us set up the experiment for nibbling deletes. First, we must decide how best to create a typical nibbling delete mechanism.

DECLARE
      @BatchSize        INT    = 100
    , @LowestValue      BIGINT = 20000
    , @HighestValue     BIGINT = 20010
    , @DeletedRowsCount BIGINT = 0
    , @RowCount         BIGINT = 1;

SET NOCOUNT ON;
GO

WHILE  @DeletedRowsCount &lt  ( @HighestValue - @LowestValue )
BEGIN

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION

        DELETE
        FROM
            dbo.tbl
        WHERE
            c1 IN (
                    SELECT TOP (@BatchSize)
                        c1
                    FROM
                        dbo.tbl
                    WHERE
                        c1 BETWEEN @LowestValue AND @HighestValue
                    ORDER BY
                        c1
                  );

        SET @RowCount = ROWCOUNT_BIG();

    COMMIT TRANSACTION;

    SET @DeletedRowsCount += @RowCount;
    WAITFOR DELAY '000:00:00.025';
    CHECKPOINT;

END;

As you can see, I placed the explicit transaction inside the while loop. If you would like to limit log flushes, then feel free to place it outside the loop. Furthermore, since we are in the FULL recovery model, you may wish to create transaction log backups more often while running your nibbling delete operations, in order to ensure that your transaction log can be prevented from growing outrageously.

So, I have a couple goals with this setup. First, I want my key-range locks; so, I try to keep the batches as small as possible. I also do not want to impact negatively the concurrency on my "gigantic" table; so, I want to take my locks and leave them as fast as I can. So, I recommend that you make your batch sizes small.

Now, I want to provide a very short example of this deletion routine in action. We must open a new window within SSMS and delete one row from our table. I will do this within an implicit transaction using the default READ COMMITTED isolation level.

DELETE FROM
    dbo.tbl
WHERE
    c1 = 20005;

Was this row actually deleted?

SELECT
    c1
FROM
    dbo.tbl
WHERE
    c1 BETWEEN 20000 AND 20010;

Yes, it was deleted.

Now, in order to see our locks, let us open a new window within SSMS and add a code snippet or two. I am using Adam Mechanic's sp_whoisactive, which can be found here: sp_whoisactive

SELECT
    DB_NAME(resource_database_id) AS DatabaseName
  , resource_type
  , request_mode
FROM
    sys.dm_tran_locks
WHERE
    DB_NAME(resource_database_id) = 'test'
    AND resource_type = 'KEY'
ORDER BY
    request_mode;

-- Our insert
sp_lock 55;

-- Our deletions
sp_lock 52;

-- Our active sessions
sp_whoisactive;

Now, we are ready to begin. In a new SSMS window, let us begin an explicit transaction that will attempt to re-insert the one row that we deleted. At the same time, we will fire off our nibbling delete operation.

The insert code:

BEGIN TRANSACTION

    SET IDENTITY_INSERT dbo.tbl ON;

    INSERT  INTO dbo.tbl
            ( c1 , c2 )
    VALUES
            ( 20005 , 1 );

    SET IDENTITY_INSERT dbo.tbl OFF;

--COMMIT TRANSACTION;

Let us kick off both operations beginning with the insert and followed by our deletes. We can see the key-range locks and exclusive locks.

The insert generated these locks:

The nibbling delete/select is holding these locks:

Our insert is blocking our delete as expected:

Now, let us commit the insert transaction and see what is up.

And as expected, all transactions complete. Now, we must check to see whether the insert was a phantom or whether the delete operation removed it as well.

SELECT
    c1
FROM
    dbo.tbl
WHERE
    c1 BETWEEN 20000 AND 20015;

In fact, the insert was deleted; so, no phantom insert was allowed.

So, in conclusion, I think the true intention of this exercise is not to try and track every single row, page, or table-level lock and try to determine whether an element of a batch is locked and would therefore require our delete operation to wait. That may have been the intent of the questioners; however, that task is herculean and basically impractical if not impossible. The real goal is to ensure that no unwanted phenomena arise once we have isolated the range of our batch with locks of our own and then precede to delete the batch. The SERIALIZABLE isolation level achieves this objective. The key is to keep your nibbles small, your transaction log under control, and eliminate unwanted phenomena.

If you want speed, then don't build gigantically deep tables that cannot be partitioned and therefore be unable to use partition switching for the fastest results. The key to speed is partitioning and parallelism; the key to suffering is nibbles and live-locking.

Please let me know what you think.

I created some further examples of the SERIALIZABLE isolation level in action. They should be available at the links below.

Delete Operation

Insert Operation

Equality Operations - Key-Range Locks on Next Key Values

Equality Operations - Singleton Fetch of Existent Data

Equality Operations - Singleton Fetch of Nonexistent Data

Inequality Operations - Key-Range Locks on Range and Next Key Values


Why SQL server does not have column-level locking?

This feature could exist. It would consume resources and complicate the architecture. So it's a trade-off that had to be made.

For the same reason some databases go in the opposite direction: They lock on the page or table level. MongoDB even has a global write lock for the entire database! This surely makes some physical implementation concerns much easier.

SQL Server's locking model already is quite sophisticated and, in my experience, addresses practical scenarios very well. I'm not sure column level locking would make it a better product (even if the developer time were free for Microsoft).


This depend if you set max_connections under the correct section inside my.cnf. what do you need to check(with examples)

go to MySQL Directory cd /opt/mysql
edit my.cnf using any text editor sudo vi my.cnf
search for [mysqld]
add the following lines directly under it max_connections=300
save and quit
restart the services.

Don't want a downtime?

connect to MySQL using root user mysql -u root -p
SET GLOBAL max_connections=300;
quit


Suitable NoSQL DB design for a fleet of IoT devices

Why NoSQL?

Any RDBMS on modern hardware can easily support your requirements.

Basic Setup

This is meant to be RDBMS agnostic.  You'll want to tweak it to the RDBMS you choose.

create table iot_devices (
  device_id      int identity primary key,
  gis_location   rdbms dependent, -- yes, databases can support longitude/latitude information.
  serial#        varchar(20),
  other_data     what-ever-you-need
);

create table iot_data (
  device_id   int not null references iot_devices (device_id),
  date_time   date  not null,
  temp        number(4,1), -- make sure COMMENT if the units are Celsius, Fahrenheit, or Kelvin
  pH          number(3,1) check ( pH between -14.0 and 14.0)
  -- other data
);

Retrieval

Now, your primary SELECT statement is this:

select *
from iot_data a
where a.device_id = ?
order by a.device_id, a.date_time desc
-- syntax for limit/paging clause is RDBMS specific
;

The question mark is a place holder so that you can BIND a value.  It also prevent SQL Injections.
Need multiple devices?  Look into Array Binding.

From experience, this index should help speed up that specific query:

create index iot_data_ix1 on iot_data (device_id, date_time desc);

Removing Expired Data

Removing "Old data" will cause headaches due to the amount of data that needs to be removed.  With PARTITIONING, you can DROP large segments of data very quickly.

I suggest a range partition on the interval of 1 day and drop the 3rd day's partition every day.
Some RDBMS will need the PARTITION created ahead of time; others can create the PARTITION on-the-fly.

Final

After you have settled on a setup, there are three thing you must do:

Benchmark
Benchmark
Benchmark

With out it, you won't know what the system's limit really is and you won't know if a change to the system has improved/deteriorated the design.


One MySQL table for all data vs Several Different Engines MySQL Tables

Simply, use InnoDB for everything.  MyISAM is going away in the next release.  In almost all cases, InnoDB out-performs MyISAM.  About the only advantage with MyISAM is disk space.

With millions of rows, you will appreciate InnoDB's automatic recovery after any crash.

It is rarely wise to make two tables be 1:1.  Your suggestion for that is based on one table being constant; the other being frequently modified.  But I would suggest that "frequent" is 'many times a second', not 'a few times a day'.

Don't design the schema without sketching out the SELECTs.  You have POINT and SPATIAL, but what will you do with such?

Glancing at the tables...

Use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

Don't use BIGINT (8 bytes) unless INT (4 bytes) won't suffice.  You would need to hire nearly all the adults in the world to exceed what INT UNSIGNED can handle.

JOIN can be pro or con.  But it is rarely a deciding factor.

Use utf8mb4 from the start.  (DESCRIBE omits that bit if info.)

Use AUTO_INCREMENT only if there is not a 'reasonable' column (or combination of columns) that make up a 'natural' PRIMARY KEY.

TINYINT UNSIGNED is limited to 255, not enough for odometer.  MEDIUMINT UNSIGNED goes up to 16M.

Do not PARTITION without a good reason for it.


MySQL binary logging with just one log file?

There are five(5) methods you can do

METHOD #1

Don't run restart. Delete the binlogs between shutdown and startup.

Let's say mysql-bin.* is in /var/lib/mysql. Run the following:

cd /var/lib/mysql
service mysql stop
rm -f mysql-bin.*
service mysql start

After startup, login to mysql and run

SHOW MASTER STATUS;

an you should see mysql-bin.000001

METHOD #2

Create a script to execute RESET MASTER; and have it executed

Step 01 : Add this line to my.cnf under the [mysqld] group header

[mysqld]
init-file=/var/lib/mysql/init.sql

Step 02 : Run the following

echo "RESET MASTER;" > /var/lib/mysql/init.sql
chown mysql:mysql /var/lib/mysql/init.sql

Step 03 : service mysql restart

METHOD #3

Do it by hand. Login to mysql and run

mysql> RESET MASTER;

METHOD #4

Erase binary logs immediately after restart

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
service mysql restart
mysql ${MYSQL_CONN} -e"RESET MASTER"

METHOD #5 (Permanent)

Step 01 : Go to my.cnf and comment out the log-bin option

[mysqld]
#log-bin=mysql-bin

or delete the line that says log-bin=mysql-bin

Step 02 : Run the following

cd /var/lib/mysql
service mysql stop
rm -f mysql-bin.*
service mysql start

Step 03 : Login to mysql and run

mysql> SHOW BINARY LOGS;

The output should be

ERROR 1381 (HY000): You are not using binary logging

GIVE IT A TRY !!!


pg_stat_statements empty with administrator user and unusable for other users


How do I optimize query where searching for 'is not null' on either of two linked tables

Biggest mistake is you are using * in Select.

  Do you require column from all three table in your resultset ?

Once you should try this,

if(@startdate is null)
set @startdate='17530101'

if(@enddate is null)
set @enddate='99991231'

SELECT * FROM dbo.my_first_table a   -- > 1 million records
INNER JOIN dbo.my_second_table b     -- > 1 million records
    on a.id = b.a_id and b.my_column_x IS NOT NULL
LEFT OUTER JOIN dbo.my_third_table c -- > 1 million records
    on a.id = c.a_id and c.my_column_y IS NOT NULL
WHERE
 b.[start_date]  > @startdate
AND     b.[end_date]    < @enddate


How to import SQL file which has no N prefix

Just adding an answer based on the below layout.

CREATE TABLE dbo.words(id NVARCHAR(1000),id2 NVARCHAR(1000),word NVARCHAR(max))

INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');
INSERT into dbo.words (id,word) values ('1','2','فارسی');

**Do replace all for ,' --> ,N'**

will become

CREATE TABLE dbo.words(id NVARCHAR(1000),id2 NVARCHAR(1000),word NVARCHAR(max))

INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');
INSERT into dbo.words (id,word) values ('1',N'2',N'فارسی');

The only downside is you have to change all column definition to NVARCHAR(). But worth a try if there are not much tables. Also try to separate the tables and insert code to a seperate file to work out.

NB:Also regarding the alignment. If the insert layout is aligned we can press
Alt+shift+down arrow will enable a vertical select which is quite useful.


Converting old SQL 2000 Non-Ansi code to SQL 2012 compatible

The best match to your requirement that I can think of is the Query Designer in SSMS.

You can open the designer by right-clicking any table in your database and selecting "edit top 200 rows".

Then, open the SQL pane by clicking the button in the toolbar:

Now you can paste your statement in the SQL pane and use the syntax checker to convert the code:

The converted code goes like this:

As you may have noticed, many things are wrong here:

all formatting is gone
wildcards are expanded. This may actually be a good thing.
Unnamed expressions are named with wonderful random names such as "Expr1".
The syntax checker may rewrite your query in ways that don't match the initial code (see this connect item, for instance)

This method (and any other automated method) is not 100% bulletproof and can be used as a starting point to help you in the process, but it definitely needs manual intervention and extensive tests on the converted code.


I have a year's worth of inventory data, and I want to extract the first and last day of each month's data. What is the best way to do this? Thanks

That is a pretty wide-open description and could use more details if there are complications in the data you are using. But here is a basic idea.

First day of month is easy:  YYYYMM01

Last day of month can easily be calculated by:

   DECLARE @FirstDayOfMonth DATETIME; -- Use DATETIME for older SQL Server versions
   SET @FirstDayOfMonth = '20150601' -- Safest format for dates.
   SELECT DATEADD(DAY,-1,(DATEADD(MONTH,1,@FirstDayOfMonth)));

This trivializes finding the last day of the month.  It is one day before the first day of the next month. No need to calculate leap years, lengths of  months, et cetera.


Referencing PL/SQL variables in FOR loop

The error in you script is that the script now expects a table named tablename, having a column named columnname. In this case you don't know the table and column names so you should use dynamic sql to run this.
Next to that, if possible, forget about LONG and implement lobs instead.

For docu see http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#i13057

sample code slightly modified to fit your needs:

decLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  v_stmt_str      VARCHAR2(200);
  z               long;
  x               number := 0;
  a               number;
  tablename       varchar2(100):= 'dba_views';
  columnname       varchar2(100):= 'text';
BEGIN
  -- Dynamic SQL statement
  v_stmt_str := 'SELECT '||columnname||' from '||tablename;
  -- Open cursor
  OPEN v_emp_cursor FOR v_stmt_str;
  -- Fetch rows from result set one at a time:
  LOOP
      FETCH v_emp_cursor INTO z;
      EXIT WHEN v_emp_cursor%NOTFOUND;
      a:=UTL_RAW.LENGTH (UTL_RAW.CAST_TO_RAW(z));
      x:= x+a;
  END LOOP;
  -- Close cursor:
  CLOSE v_emp_cursor;
  dbms_output.put_line (x);
END;
/


try this

select autori, substring(titolo,1,20) || '...'
from generale where titolo  ~* 'hp-ux' order by titolo;

it truncates your output but doesn't touch the data in the table


how to GROUP BY on specific column and COUNT() the other columns?

I am not so sure what your exact requirement is, but i think you are looking for this..

CREATE TABLE test1(User_ID SMALLINT, Jqci_ID SMALLINT, Ct_ID SMALLINT, Correct BIT)

INSERT INTO test1
SELECT 29, 5, 8, 0 UNION ALL
SELECT 29, 25, 8, 1 UNION ALL
SELECT 29, 26, 8, 0 UNION ALL
SELECT 29, 27, 8, 0 UNION ALL
SELECT 29, 28, 8, 0 UNION ALL
SELECT 29, 34, 8, 1 UNION ALL
SELECT 29, 35, 8, 0 UNION ALL
SELECT 29, 36, 8, 0 UNION ALL
SELECT 29, 37, 8, 0 UNION ALL
SELECT 29, 38, 8, 0 UNION ALL
SELECT 29, 39, 8, 0 UNION ALL
SELECT 29, 41, 8, 1 UNION ALL
SELECT 29, 42, 8, 0 UNION ALL
SELECT 29, 47, 34, 0

SELECT
    Ct_ID,
    SUM(CASE WHEN Correct = 1 THEN 1 ELSE 0 END) AS Correct,
    SUM(CASE WHEN Correct = 0 THEN 1 ELSE 0 END) AS InCorrect
FROM test1
GROUP BY Ct_ID


Oracle 12c Rman Duplicate Archive Log Not Verified

Could you please tell us how you connected to rman?

For example:

rman target sys/asdf@prod auxiliary sys/asdf@dev

The reason for you problem is that the two databases have trouble communicating with each other.

If you use auxiliary / because your rman session is located on the dev server this is not going to work. The reason is pretty simple as follows.

Lets say your rman session was opened on server dev. You use this connection:

rman target sys/asdf@prod auxiliary sys/asdf@dev

The auxiliary database uses the TNSalias "prod" to connect to the target database. The prod database uses the TNSalias "dev" to connect to the auxiliary database. This means you have to configure the tnsnames.ora on both sides to be able to connect to the other database. Since the rman session is on dev in this example you have to configure the TNSalias "dev" on dev as well.

Back to the example with auxiliary /, it is obvious now why it cannot work.

Then there are additional stuff to keep in mind. The auxiliary database needs to be restarted several times. In some cases you loose the connection to the auxiliary database when it shuts down. This means you have to configure the dev database in the SID_LIST of the listener on the dev server. And take care that the password file is present.

Well, this sounds like a lot configuration overhead. If you have to refresh your dev database frequently this is not a big deal. But I personally prefer to clone from backup location because this is much more simple. You can also skip the SPFILE clause when you create it manually. In my experience you need 2 or 3 attempts to work out the correct parameter conversion. If some parameters change on your prod database you might have to adjust this clause again.


Ram consumed by single query in Postgres using SqlAlchemy

You can turn on "log_statement_stats", and look at the 'max resident size' line from the log file (or set client_min_messages TO log;).

Is is a pretty crummy interface, and the number it gives is the high water mark for the connection, so you would have to run each statement in a separate connection while testing it.


Power BI DESKTOP licensing - is it really free to share pbix files?

You are absolutely free to build and distribute .pbix files by file shares or OneDrive, or SharePoint Online.  Eventually this will lead to Power BI Service usage, or at least your team mates will want brand new Surface PCs to run Power BI Desktop.

And in any case you are investing your precious time in learning the technology and spreading its use.


Estimated row size and estimated data size is incorrect

SQL Server just uses a ballpark estimate for varchar that each value will on average be half the column capacity.

So each varchar(4000) will be estimated at 2,000 bytes. You have two of them so that explains the bulk of the 4,496 bytes estimate.

It seems that in reality you could reduce the maximum size of these columns.

This would reduce the estimated row size. Having an accurate estimated row size can help when there are memory consuming operators in the plan for getting an appropriately sized memory grant.


How do I select distinct one column and return the other column data that has been distincted?

Assuming you are using MySQL

Here is how I'd do the SQL:

SELECT
ID,
ITEMNAME,
CONCAT("select tag(",GROUP_CONCAT(DISTINCT size
                                  ORDER BY size DESC
                                  SEPARATOR "/"), ')') AS SIZE
FROM items
GROUP BY ITEMNAME

Results:

| ID |      ITEMNAME |            SIZE |
|----|---------------|-----------------|
|  1 |  Blue T-shirt | select tag(M/L) |
|  3 |   Red T-shirt |   select tag(M) |

SQL Fiddle

Once you have it in PHP you can then iterate it out.


For backup folder :

If you mean the default backup folder as configurable in server properties --> database settings

You can easily change that location to the one you want. If your SSD has only the backup files, then it should be ok to hot swap when your backups are not running. Just change the default location to the new one in the above stated location. A restart of sql server service may be required for the new values to get reflected.

A more easy and managable approach will be to explicitely specify the backup location in your backup scripts (or just use Ola's backup solution).

For LDF (or MDF) files :

You need to use alter database .. modify file to first modify the location in the system tables.

Then offline the database/s and then physically copy the LDF (or MDF) to the new location.

Once above is done, bring the database online.

See this answer for more detail steps by Mike Fal.


Yes, sa can be renamed and disabled since SQL Server 2005. It can't be removed

You can then if you wish, create a dummy sa account with, say, a GUID as password and then disable that or for any software that needs it.

Note that this will let you see the new name of the original sa account

SELECT SUSER_NAME(0x01)


rows are rendered not in serial order as appeared in database

As @a_horse_with_no_name already said: the data may not be stored in the sequential order.

You have some choices:

Use a ORDER BY id[1] clause and force the database to sort the data for you.
Force a CLUSTER[2] on the table to rewrite the data and sort based on a index as you want. I don't recommend that as a solution for your question but it is a alternative. IMPORTANT: CLUSTER command only will physically sort the data on the disk, rewriting the data files based on the index. There is no guarantee of the data will be returned on the same order.

Hope it helps.

References:

https://www.postgresql.org/docs/current/static/queries-order.html
https://www.postgresql.org/docs/current/static/sql-cluster.html


Oracle autotask results of space advisor

This auto task identifies segments that have space available for reclamation and makes recommendations on how to defragment those segments.

You can use DBMS_SPACE.ASA_RECOMMENDATIONS procedure to see the results.

Or you can see the same results in the Oracle Enterprise Manager.

For example:
Go to Advisor Central and Select the Advisory Type as shown in the following screenshot(OEM 11g).


ERROR 1100 (HY000) at line 11084: Table 'search_index' was not locked with LOCK TABLES

My initial guess would be that when the mysqldump file was being created, the option single-transaction was not used.

According to MySQL Documentation on single-transaction

  The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

The fact that you embedded UNLOCK TABLES; into the dump manually shows that neither option was called when the mysqldump was first created. Thus, upon reload, this error reared its ugly head.


Oracle 12: Using compound trigger to update data in same table on each insert/update

There is no need for a compound trigger. Use simply
CREATE OR REPLACE TRIGGER customer_info_trg
    BEFORE UPDATE OR INSERT ON custommer_info
    FOR EACH ROW
BEGIN
    :NEW.last_upd_by := USER;
    :NEW.last_upd_time := SYSTIMESTAMP;
END;


Are you 100% sure that this process is escalating to a tablock? You can verify this with Adam Machanic's sp_whoisactive. Take a snapshot (execute the proc) while the process is running (and presumably blocking). Collect the locks and the query plan - this will give you information needed to fix this issue. Please note what the spids waiting in the insert are waiting on...If they are waiting on latches -we have a page split situation locks its a blocking/locking issue
Remember lock escalation happens when a single statement graps 5K locks on an table or index. (http://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx) I have found that using the paglock hint (so you take fewer coarser locks) can be used to avoid tablock escalation.
Take a look at the query plan collected in step 1. What is the cardinality estimate of the row being insert (i.e. the select statement)... Is it much greater than 5K? If it is you might want to code a loop - so that you are only inserting 2500 rows at a time...


Composite Primary keys and auto increment?

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key.

In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.


Synchronous SQL Server Replication only for specified subscribers

SQL Server supports Transactional, Snapshot and Merge replication. They are inherently asynchronous.

Mirroring on the other hand does support synchronous/asynchronous commit however you can only mirror one database to one other location.

SQL Server doesn't support what you are looking for. I'm not aware of any other tools which would enable this either.

The closest thing to what you are talking about is AlwaysOn, which I believe in SQL Server 2014/2016 supports multiple asynchronous/synchronous replicas.


MySQL Query with good execution plan is very slow due to Sending data operation

Saying "Sending data" merely points out how useless the Profiler often is.

This index on a might change the EXPLAIN (eg, start with a instead of b) and speed up things:

INDEX(`type`, `deleted`)  -- in either order

If you need further discussion, please provide SHOW CREATE TABLE and some clues of about how big each table is.

Why is b used?  Would you get the 'right' answer if you left out

INNER JOIN b ON a.borrowerId = b.id


Once you mark a column unusued, there is no way back, just restore from backup.

Dropping Table Columns

  Removing Unused Columns

  The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action
  allowed on unused columns. It physically removes unused columns from
  the table and reclaims disk space.


SqlAnywhere 12- service started but stopped

If you are still having problems with this, the first place to look is in the Windows Application Events log - you should see the reason (eg manual shutdown requested) why the server was stopped.

You can also get the database engine to write its own console log to file with the

-o <filename>

server start up option - that may give you more detailed information.

It's important to check that your connections aren't specifying the AutoStop connection parameter or the Stop Database After Last Disconnect ODBC setting. This only applies if you are asking the connection to automatically start the database engine if it is not already running - NOT if you are running the database engine as a service.


SQL Server ; working around collation mismatches

You use the collate operator in the join.  Such as:

select <column list>
from dbo.firsttable f
join linkedserver.database.dbo.secondtable d
   on f.name collate database_default = d.name collate database_default

Of course, you can also specify a specific collation, such as: SQL_Latin1_General_CP1_CI_AI


How to use variables with RESTORE FILELISTONLY

I think you have things just a little messed up. How about:

CREATE PROCEDURE dbo.usp_restore
  @backup_path NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    RESTORE FILELISTONLY FROM DISK = @backup_path;
END
GO

Creating the procedure should be completely separate from any individual invocation. So in a different window, or two weeks later, you should be able to do this (again, completely separately from the creation of the stored procedure):

DECLARE @bp NVARCHAR(MAX) = N'Z:\BACKUPS\my_database_backup.bak';

INSERT #filepaths EXEC dbo.usp_restore @backup_path = @bp;

Some other commentary you should look into:

Why you shouldn't use the sp_ prefix
Why you should always use the schema prefix

If you don't want a stored procedure to help with this (why not?), sure you can do this in a much messier way. Assuming you already have a #temp table, #t, created with the right columns and data types:

DECLARE @path NVARCHAR(MAX) = N'Z:\BACKUPS\my_database_backup.bak';

DECLARE @sql NVARCHAR(MAX) = N'RESTORE FILELISTONLY FROM DISK = @path;';

INSERT #t EXEC master.sys.sp_executesql @sql, N'@path NVARCHAR(MAX)', @path;

But still, why not just have a stored procedure ready when you need it, instead of porting around all this bulky code?


Why does a log file grow even when transaction is not commited?

Transaction log records are flushed as log buffers become full, which is why you observed growth before the final COMMIT. The buffer is also flushed when a durable transaction is committed, which guarantees durability.


Default value for CHARSET when creating a table

You can remove Default, it is not mandatory. If you look att the grammar it says:
| [DEFAULT] CHARACTER Set =

Meaning that the table gets this CHARACTER SET. It does not affect other tables.
If no CHARACTER Set is declared it is "inherited" from the database definition. This is the same as:
CHARACTER Set = DEFAULT

I dont know if it is part of the confusion, but there are two different DEFAULT keywords here, with different meaning. The first says what the default CHARACTER Set for this table is, the second one that the default character set for the database should be used.


As @a_horse_with_no_name points out, your connection string is incorrect.  The java jdbc driver needs to know the instance name ( it doesn't play well with the backslash notation ), which will be overridden if you're specifying the port ( 1433 is the default instance port - you might not have to specify the instance name at all if that's what you're trying to connect to ).

Try something along the lines of this instead for your connection string:

  jdbc:jtds:sqlserver://JT__428/j___test_db;instance=J__MS14


How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?

Since you are running MySQL 5.5, you may want to consider configuring InnoDB to access multiple cores

Here are the settings you should be using

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand from the Percona NYC Conference, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

Here are my past posts on MySQL 5.5 and activating multiple cores for InnoDB

Jun 01, 2012 : I've got 16GB of ram, how should I configure MySQL Server?
May 07, 2012 : MySQL Server Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
Mar 16, 2012 : Using multiple cores for single MySQL queries on Debian
Oct 07, 2011 : Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?


MSSQL 2014 "The provided statistics stream is corrupt" Error 9105

The problem may be in the damaged statistics, and probably in the one that was created automatically

To search for problem statistics you can use:

EXEC sp_updatestats

exec will iterate over all the tables and statistics, and if there are problems, it will show which statistics, more precisely the table, is the problem.

To remove auto_created statistics, you can use the following script:

select 'drop statistics [' + OBJECT_NAME (OBJECT_ID) + '].[' + Name + ']'
 from sys.stats
where auto_created = 1
  and OBJECT_NAME (OBJECT_ID) = 'DB PROBLEM TABLE NAME'

The script will generate rows for deleting auto_created statistics for the table "DB PROBLEM TABLE NAME" table

After running the generated lines, rerun

EXEC sp_updatestats

to check for errors.

Not superfluous would be

exec sp_msforeachtable N'UPDATE STATISTICS? WITH FULLSCAN'


How to free up disk space? which logs/directories to clean?

You can change the default retention policy of oracle log and trace files.

Source: https://streetkiter.wordpress.com/2011/04/06/do-you-really-need-one-year-old-logs-and-traces-for-your-oracle-database/

Relevant extract:
There are two parameters: SHORTP_POLICY (default 720 hours) and LONGP_POLICY (default 8720 hours). so SHORTP_POLICY is 30 days while LONGP_POLICY is 365 days.

From the commandline run "adrci". In adrci, use the following commands.

adrci> show homes
....(homes are listed)...
adrci> set home diag/....(the location)
adrci> show control
.... (current settings are listed) ....
adrci> set control (SHORTP_POLICY = 168)
adrci> set control (LONGP_POLICY = 720)

And now the traces are dropped after one week (SHORTP) or one month (LONGP).


Auto increment a value using cursor in SQLServer

I don't think a cursor is good solution to this as it would have to be running 24 / 7 and you would need to check it was running. A better solution would be to have a SQL Agent job that runs at midnight each night. The Agent job would call a stored procedure that checks the IsActive flag, LastUpdate field and NextUpdate field for each employee and then increments the mentioned values for each employee.

The stored procedure could send an email after each run to show you how many records were updated. It could also incorporate error handling to alert you if there was a failure for some reason. This way you can be sure the process is running correctly.


Why is it important to carefully determine and select statistics for the query optimization process?

The type of statistics are cardinalities i.e. a count of the number of rows that have a certain value for a particular attribute.

To take a simple example, say I have a table with one column that is one character long:

create table T(c1 char(1));

This table contains the values A, A, A, B, C, F, K, X, X, Z. The cardinality will then be a count of the occurrences of each value:

A 3; B 1; C 1; F 1; K 1; X 2; Z 1

Now when the optimizer sees the query select * from T where c1 = 'A' it knows to expect three rows returned because the statistics tells it that's how many exist for that value.

In general tables can be large with many, many different key values. Storing complete statistics as I have shown would be impractical. What actually happens is that a statistically believable summary of the actual table is created. (There are many ways to do this; here's one approach; this answer works through the nitty-gritty.) This summary is compact enough to be traversed quickly during optimization to give good-enough results.

The statistics are used by the optimizer to choose between different physical implementations of the query. My single-table query above does not illustrate this well so let's say we have the query

select *
from TableA
inner join TableB
  on TableB.CustId = TableA.CustId
where TableA.SomeColumn = 42;

There are many possible implementations. It could apply the WHERE then do the join, or it could do the join then apply the WHERE. The physical join implementation could be a nested loop, merge or hash join. To read the data from disk it could do table scans, index look-ups or some combination. How is the optimizer to choose between these alternatives?

Most modern DBMS will use a cost-based approach based on the Cascades framework. Each alternative is given a cost (in some arbitrary units that only make sense within the optimizer itself) and the lowest cost option is chosen for execution. These costs are calculated based (amongst other things) on the number of rows expected which, of course, come from the statistics.


Move a Postgres 9.3 database to a new server and update to 9.6 with minimum downtime

You can't do this, because WAL archive can only work with physical copies, while pg_dumpall and restore generates a logical copy.  Also, WAL format is not compatible across major versions, so you couldn't re-play WAL from one version to another anyway.  (You can do something like that with logical replication, but that doesn't exist in version 9.3, and is a rather advanced topic requiring extensive design and testing in the newer versions.)

Is it really necessary to move versions and servers at the same time?  If something goes wrong during such a double migration, you've just created a big diagnostic mess by changing two major things at the same time.

The lowest down-time way to move versions (other than logical replication) is to use pg_upgrade -k.  This requires you to have both old and new versions installed on the same machine simultaneously.

The lowest down-time way to move machines is to set up streaming replication; and wait for it catch up (as much as it can get while the master is still active), then shutdown all user connections to the master, wait for all WAL to replay to the replica, and promote the replica.


Could you post a link to the forum you're citing, I'd like to read the full context of the post.

I am not a lawyer, but legally speaking you don't need a license to use MySQL community edition. They do offer a paid-for enterprise edition. There are some additional tools you get, but I think the big thing people would use that for is support.

I've talked to some people that worked for companies that weren't allowed to use MySQL by veto from their legal dept, because they couldn't comprehend the concept of a license you don't pay for O_o.

MySQL Community Edition flat out says it's free to use and licensed under the GPL.


What is "CSS operation: data query" wait event in an Oracle StatsPack report?

The note you provided is regarding issues with shutdown: Bug 8299200 - Shutdown immediate hangs (Doc ID 8299200.8). Is it the correct doc you wanted to refer?

Have you recently upgraded to 11.2.0.3?

If so, there's this MOSC note that might be helpful, referring to a bug that causes Clusterware Synchronization Services operations to be very slow, leading to the css-related wait events:

Doc ID 1444483.1


Free space within the table can be used to speed up certain kinds of updates on tables, do inserts faster by avoiding the need to extend the table, and make index growth more efficient.

If your table only changes infrequently and you don't mind reindexing it (or dropping & re-creating the index) each time, that's totally fine. A fillfactor of 100 will save a little bit of I/O when scanning the index, and the spare space is no use to you unless you do inserts/updates on the indexed table.


Is it okay to clear SQL Server Buffer every weekend or month end?

It's almost always a good idea to set max server memory setting to some value lower than the total amount of memory available, especially if there are other applications running on the same server (IIS in your case). The actual number depends on total amount of memory and on memory requirements of those other applications.

This setting is important to prevent the various applications/services competing for memory (causing memory starvation), which in turn can cause performance problems.

With 32 GB available, I'd subtract from it 3 GB for the OS, then subtract whatever number is used by IIS and other applications, if any, and set the max server memory to the remainder.

There is no need to periodically clear the SQL Server buffer pools, just the opposite.


mysql import/drop very slow on pyshycal server

It is important to know the load average in your MySQL server, in the VMWare server with Percona DB, the server (i assume) has no working tasks. but in the MySQL server you have some extra Work.
If your MySQL server is on a unix/linux server you can check your load average with the command top-i or simple use up time.

Another think you can check is how many connections do you have in your MySQL and your Connection Usage, the traffic, number of Queries, Query Caché,etc.

I hope this can help you to take a direction of way do you have this problem.s
Please update with more information if you still need more help.


Postgres isn't really using the RAM it could be using

Actually, it probably is.

PostgreSQL relies on the Linux kernel to cache disk blocks. That's what effective_cache_size is about - it's telling PostgreSQL what kind of cache size it can expect the kernel to maintain.

Run:

free -m

You'll probably see almost all RAM in use as buffers/cache. Which is exactly what you want.


How can I view when mysql mixed mode replication is logging a statement using row or statement format?

The table has no index on event_time. Your DELETE must therefore perform a full table scan every time. This has to be a big contributor to the slowness.

SUGGESTION #1

Add an index

ALTER TABLE user_events ADD INDEX (event_time);

SUGGESTION #2

Instead of doing

DELETE FROM `user_events` WHERE `event_time` < CURDATE();

Try loading a temp table and switching it into place

ALTER TABLE user_events RENAME user_events_old;
CREATE TABLE user_events_new LIKE user_events_old;
INSERT INTO user_events_new
    SELECT * FROM user_events_old
    WHERE event_time >= CURDATE() + INTERVAL 0 SECOND
;
ANALYZE TABLE user_events_new;
ALTER TABLE user_events_new RENAME user_events;
DROP TABLE user_events_old;

EPILOGOUE

Suggestion #1 may be all you need. Suggestion #2 is an alternative means of deleting old rows.

YOUR QUESTION

If you have binary logging on the Slave and the Slave is not a Master, disable it.

If you want to see which queries are expanding to ROW rather than STATEMENT, use mysqlbinlog against the binary logs on the Master to see a text representation of all queries. The row-based ones will be a little obfuscated. The statement-based queries will appear as it was executed.


create multi valued variable inside mysql stored procedure

Join table1 and table2. Something like:

SELECT  t2.searchItem
FROM    table2 as t2
JOIN    table1 as t1
    ON t1.tabid = t2.table2_id
WHERE   t2.searchItem LIKE CONCAT(sText , '%')
  AND IFNULL(tabId , '') != '' and t1.table1_id= myId
LIMIT                   15 ;

Bottom line, you don't need the @myCid variable


SQL Server 2012 database in suspect mode after upgrade from Developer to Standard Edition

SQL Server Developer Edition is functionally equal to Enterprise Edition. The error is stating you are using features that only Developer or Enterprise support, such as Table Partitioning in this case. In order to use Standard, you cannot use any Enterprise features.

Check Features Supported by the Editions of SQL Server 2012 for more details.

One way to fix this issue would be to take the following steps:

  Take the database offline.
  Install a new named instance temporarily with Developer or Enterprise edition (database engine only as that is all that is needed
  for this).
  Copy the data and log files from current location to new location.
  On temporary instance, attach database and make sure you point to the copied files in the new location and not the original files. It
  should have no problems and bring the database up.
  Remove any Enterprise features from the link above. Use the following query to see what is used in that database "SELECT feature_name FROM sys.dm_db_persisted_sku_features"
  Detach database from temporary location.
  Rename original database files by appending some word (_orig as an example) to the current name.
  Move files from temporary location to same location as original files (assuming these files have same name as original files).
  Attach database to Standard instance by selecting the file you just copied
  Database should come up with no issues at this point.
  Turn off temporary SQL instance.
  After verifying everything is ok, uninstall the temporary instance.


Primary key for link/junction table with time-dependent data

Assuming that:

every country has at most 1 leader in a given point in time (no overlaps).
there may be gaps in a country's leadership (if you don't want to allow that, change the scond constraint to prevtermend = termstart)
termstart < termend, i.e. every term lasts at least 1 day (if that is not the case, you can alter the constraints below, either by making that <= and/or converting the date columns to datetime.)
you want to enforce all these constraints declaratively.

then I think the following (rather complicated) way will do - by simulating a circular linked list for every country):

    CREATE TABLE country_leader (
      countryid integer unsigned NOT NULL,
      seqno integer NOT NULL,
      leaderid integer unsigned NOT NULL,
      termstart date NOT NULL,
      termend date NOT NULL,
      prevseqno integer NOT NULL,
      prevtermend date NOT NULL,
      PRIMARY KEY (countryid, seqno),
      UNIQUE (countryid, prevseqno),
      UNIQUE (countryid, seqno, termend),
      FOREIGN KEY (countryid)
        REFERENCES country (countryid),
      FOREIGN KEY (leaderid)
        REFERENCES politician (politicianid),
      FOREIGN KEY (countryid, prevseqno, prevtermend)
        REFERENCES country_leader (countryid, seqno, termend),
      CHECK (termstart < termend),
      CHECK (prevtermend <= termstart OR seqno = 0),
      CHECK (prevseqno = seqno - 1 OR seqno = 0)
    );


Query execution involving two servers in SQL server , is really slow

Yes you're right, joins across linked servers is slow. A simple but unflattering solve is to pull the data across from each table on other servers and insert it into a temp table (select a,b,c into #temp from server.database.schema.table) or table variable. Then use that table in your queries. A better way would be to design an ETL to periodically pull these tables onto a single server.


How to enforce a constraint over multiple tables?

This can easily be done by using cascading keys that overlap.  Here is an example using the Oracle Data Modeler (note there is a bug in this tool or a configuration issue as the Provider_Feature table should show each column as PF meaning both PK and FK):

In this example, the PK to Provider Product includes the Product Number provided, and the PK to Feature includes the Feature Number supported for that Product.  The Product Number in Provider_Feature is a FK back to both the Provider Product and the Feature.  The FK constraints thus prevent inserting a Provider Feature for a Product Feature combination not already instantiated as a Product Feature possibility.

This solution assumes that a feature is identified by a Product - that is a feature cannot exist outside the context of a product.  That assumption is likely valid as features, in and of them selves, have no meaning with the context of a product.  However, if you do want to instantiate features that can included on multiple products, then you simply add a Feature table and make the current Feature table a Product_Feature table.

Often database designers assume each table must have its own "identifier."  Nothing could be further from the truth.  Please see Fabian Pascal's excellent blog post on this topic.  Adding a surrogate Key to each table means the natural business rule you are trying to enforce can no longer be enforced declaratively via PK and FK constraints, just as you have found.  If you must have a SK for some reason your only option is to enforce the constraint procedurally using a trigger. This option is much more problematic.  First, you have to write, debug, test and maintain the trigger.  The book Applied Mathematics for Database Professionals provides excellent detail on how difficult it can turn out to be to express constraints using procedural logic in a way that performs adequately.  Secondly, triggers in and of them selves can sometimes be performance inhibitors as they execute serially.

My recommendation is to use the natural declarative approach.


Why 2 Table Scans of Same Table Have Huge Differences in Elapsed Time SQL Server

Logical reads is only an indicator of how much data SQL Server reads from disk (or the buffer pool). There are a bunch of other factors that can contribute to a long-running query.
The most obvious suspect is how much time it takes to move the data from your server to Management Studio and/or the time it takes to render the data in a table. To eliminate those two, try inserting the results in a new temp table:
SELECT a, b INTO #new_table FROM #temp_table;

You can also set
SET STATISTICS TIME ON;

to see the amount of CPU and Elapsed times respectively.


How to rewrite CASE expression to short-circuit evaluation

What is currently happening

When running your query, the table scan, stream agg & compute scalar operators are not evaluated at runtime.

Why is it happening

The apply NL join means that for each row in #Docs, return a row from #Docsitems that matches the predicate. This predicate should be WHERE IDDocs = D.ID

But the compute scalar operator (EXPR1007) next to the select (the actual case statement) is calling the scalar function in the inner side of the NL apply (EXPR1005) only when Doctype <> 1. This as you know cannot happen, and they both return NULL.

Compute scalar between NL & SELECT:

Compute scalar on the inner side of the join:

This all seems to be due to how the CASE statement functions & removing literals. (difference between CASE  2 = 1 vs. CASE WHEN D.DocType <> 1)

Resolving

If you change the query to this:

SELECT
    -- expression
    SumAmount =
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

You should get the execution plan you desire:

Removing the SELonLOJ rule that is changing the plan on the rewrite.

Guessing

Reverting the rule that was applied to get closer to the CASE WHEN query can be done by adding the hint: OPTION( QUERYRULEOFF SELonLOJ   )

SELECT
    -- expression
    SumAmount =
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF SELonLOJ   );

Showing the same situation (minus filter & left join) as the case statement, but the runtime elimination is not occuring.

&

Another rule that seems to get closer to the case when when turning it off is JoinPredNorm

SELECT
    -- expression
    SumAmount =
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF JoinPredNorm   )

With the filtering happening on the #Docs table as expected.


Here is a possible derivation:

 1. A → CD   (given)
 2. C → BE   (given)
 3. CD → BED (by augmentation of 2 with D)
 4. A → BED  (by transitivity of 1 and 3)
 5. B → F    (given)
 6. B → BF   (by augmentation of 5 with B)
 7. BED → B  (by reflexivity)
 8. A → B    (by transitivity of 4 and 7)
 9. A → BF   (by transitivity of 8 and 6)


Querying Table for Index Fragmentation - Multiple Different Results for Same Index

There's a row for each level in the index (add ips.index_level to the output and you will see that there is a row for each level).

Your query should probably simply filter out any row with a low page count (say, 1000), which will eliminate all but level 0 in most cases.


Postgres: turn off formating for xml export?

The psql command \? shows the option to turn off expanded formatting:

Formatting:
...
  \x [on|off|auto]       toggle expanded output (currently off)

Typically it will show table data in one record per line if its "off" or else each field in its own line when switched on, e.g.:

postgres=# \x off
Expanded display is off.
postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | en_IN   | en_IN |

When its switched on:

postgres=# \x on
Expanded display is on.
postgres=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | en_IN
Ctype             | en_IN
Access privileges |
-[ RECORD 2 ]-----+----------------------

With xml output, since everything is in one stream, it shows the record label before printing out the xml.
The difference between /x on and /x off is as follows:

/x on

table_to_xml|<pg_attribute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <attrelid>1255</attrelid>
  <attname>proname</attname>
  <atttypid>19</atttypid>
  <attstattarget>-1</attstattarget>

With /x off the output is:

/x off
<pg_attribute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <attrelid>1255</attrelid>
  <attname>proname</attname>
  <atttypid>19</atttypid>
  <attstattarget>-1</attstattarget>

So before writing to the file you should change the commands issued as follows:

\t on
\x off
\pset format unaligned


Get value of two instances of a secondary table

It looks like you are just trying to pivot some data.

select
    u.Id
  , u.Username
  , [Cards Permission]  =max(case when p.Name='Cards'   then p.Type else null end)
  , [Pages Permission]  =max(case when p.Name='Pages'   then p.Type else null end)
  --, [Players Permission]=max(case when p.Name='Players' then p.Type else null end)
  from user u
    inner join Permission p ON p.UserId = u.Id
  group by u.Id, u.username;

or if we want the name of the type for type integer, something like:

select
    u.Id
  , u.Username
  , [Cards Permission]  =max(case when p.Name='Cards'   then t.Name else null end)
  , [Pages Permission]  =max(case when p.Name='Pages'   then t.Name else null end)
  --, [Players Permission]=max(case when p.Name='Players' then t.Name else null end)
  from user u
    inner join Permission p ON p.UserId = u.Id
    inner join [Type] t ON p.Type = t.Id
  group by u.Id, u.username;

If your nonclustered index on permissions is (Id, UserId, Name), it's not doing you any favors. unique nonclustered (UserId, Name, Type) or nonclustered (UserId, Name) include (Type) would be more useful.


Your proposed project sounds fairly decent. However, the more hands involved the greater complexity security is.

If this was my request, I would ask some of these questions:

Is the data stored currently on multiple data centers or centrally?
Are these sources in the same domain? Or do we need multiple SSL’s for our ETL?
If we choose to rely on external sources to transport this data to a local/network drive, how secure and consistent is this system?
Can the system of question 3 be integrated into SQL Server or another software to copy or pull the data?

Why Network drives are best

For one, durability of the directories is independent of a SAN, disk, or single blade, so the data remains available even during outages.

Because they are on a single mount point, where each drive is additionally mounted, your security is simplified dramatically.
You could give the files to SQL Server by using a single account that only SSIS has privy to, or a Domain Account that your AD manages.

But really, you should have an ETL process developed

You ensure the validity and security of your documents as you go from SOURCE to TARGET.
the process is encapsulated away so that additional sources is a mere plug ‘n play.

Proper ETL involves 3 stages:

Extraction (your primary concern)

this involved programmatic pulling and staging of your source data.
Unless it is absolutely impossible, this process should use as little manual effort as possible.
If you find yourself with a lot of manual effort in this stage, often the data source may not be clean enough for your purposes.

Transformation (inside the database/SSIS package)

This involves all processes that clean and ready the data for loading onto the final target tables.
cleanup usually follows this: Removing immediately bad data rows.  Separating multifaceted values into their own column. Performing transformation steps that changes these values to a pre-final step. Last combinations of said columns that create the final tables OR allow your final loading to be collections of these values in a organized fashion (such as collecting from each sporting Team website details of their teams/history and then aggregating this in a final table)

Loading.  The final, cleaned, and final form for your data.


How to store likes and comments of posts in database?

Doing it the way you suggested is the right way to keep track of likes. A separate table with user_id and post_id both of which are FK and these 2 FK combined can serve as PK

2 columns both of which are integers(or bigint) is not a problem to any RDMBS.

In fact, 2 integer columns should be the least of your concerns. Because integers are one of the most performant datatypes, and occupy very little space if compared to, for example, nvarchar.

I think this SO post might help you as well https://stackoverflow.com/questions/11808680/database-design-table-of-likes


I agree with that person. Here is an implementation.
Basically, add a UNIQUE array column of all chem_ids to table mixtures and keep it current with triggers. Arrays must be sorted consistently, I use the additional module intarray for that and to optimize performance.
For lack of definition I assume frequent multi-row writes, making it a perfect candidate for transition tables introduced with Postgres 10. See:

What is a “transition table" in Postgres?

Important note: Technically, this works in Postgres 10. But while testing I ran into a bug of intarray functions that seemed oddly familiar: empty arrays would not compare equal due to incorrect internal array dimensions. Tom Lane found and fixed this for Postgres 11, but it was not backported to Postgres 10. I strongly advise to use Postgres 11 with this.
Turns out to be another instance of a bug I reported myself earlier. See here and here. Took me a while to reproduce and get the full picture.
This uses a variety of advanced features. Not recommended for beginners.
Code
CREATE TABLE chemicals (
  chem_id serial UNIQUE NOT NULL
, name text PRIMARY KEY
);

CREATE TABLE mixtures (
  mixture_id serial PRIMARY KEY
, chem_ids int[] UNIQUE  -- default NULL !
);

CREATE TABLE mixture_chems (
  mixture_id int REFERENCES mixtures (mixture_id)
, chem_id int    REFERENCES chemicals (chem_id)
);
INSERT trigger
CREATE OR REPLACE FUNCTION trg_mixture_chems_insaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE mixtures AS m
   SET    chem_ids = sort(COALESCE(m.chem_ids, '{}') + n.chem_ids)
   FROM  (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   new_table
      GROUP  BY 1
      ) n
   WHERE m.mixture_id = n.mixture_id;

   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER mixture_chems_insaft
AFTER INSERT ON mixture_chems
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_mixture_chems_insaft();

UPDATE trigger
CREATE OR REPLACE FUNCTION trg_mixture_chems_upaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE mixtures AS m
   SET    chem_ids = sort(COALESCE(m.chem_ids, '{}')
                        - COALESCE(o.chem_ids, '{}')
                        + COALESCE(n.chem_ids, '{}'))
   FROM  (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   new_table
      GROUP  BY 1
      ) n
   FULL  JOIN (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   old_table
      GROUP  BY 1
      ) o USING (mixture_id)
   WHERE m.mixture_id = COALESCE(n.mixture_id, o.mixture_id)
   AND   m.chem_ids IS DISTINCT FROM sort(COALESCE(m.chem_ids, '{}')
                                        - COALESCE(o.chem_ids, '{}')
                                        + COALESCE(n.chem_ids, '{}'));

   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER mixture_chems_upaft
AFTER UPDATE ON mixture_chems
REFERENCING NEW TABLE AS new_table
            OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_mixture_chems_upaft();

DELETE trigger
CREATE OR REPLACE FUNCTION trg_mixture_chems_delaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE mixtures AS m
   SET    chem_ids = m.chem_ids - o.chem_ids  -- assuming this does not upset sort order!
   FROM  (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   old_table
      GROUP  BY 1
      ) o
   WHERE m.mixture_id = o.mixture_id
   AND   m.chem_ids IS DISTINCT FROM (m.chem_ids - o.chem_ids);

   RETURN NULL;
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER mixture_chems_delaft
AFTER DELETE ON mixture_chems
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE trg_mixture_chems_delaft();

db<>fiddle here
This implementation is strict: a mixture with no chemicals (chem_ids = '{}') is just another case that is only allowed once. You may want to allow that multiple times instead. This state is only reached after deleting all existing components, newly inserted row in mixtures start out with chem_ids IS NULL to dodge this UNIQUE constraint.
And you may want to add a PRIMARY KEY constraint to disallow adding the same chemical to a mixture multiple times:
CREATE TABLE mixture_chems (
  mixture_id INTEGER REFERENCES mixtures (mixture_id)
, chem_id INTEGER REFERENCES chemicals (chem_id)
, PRIMARY KEY (mixture_id, chem_id)
);
But my implementation works either way.
Related:

Can PostgreSQL have a uniqueness constraint on array elements?
How to implement a many-to-many relationship in PostgreSQL?


updating millions of rows in multiple tables sql server 2005

Most efficient is a fuzzy term. You're talking about updating about 122 million rows. If you start running naive updates now, it will probably finish before you can create a useful estimate. (I'd expect it to take about half an hour on my development machine. Updating 50 million rows takes 12 minutes.)

Dropping indexes beforehand is fine if you have your DDL under version control, and you can rebuild the indexes more or less automatically. Dropping all constraints should make the updates run faster still, but again I'd do that only if

execution time were critical,
DDL were under version control, and
I could rebuild all the constraints more or less automatically.

Cascading foreign key constraints can be an issue.

Personally, I try to avoid situations where execution time is critical. I don't usually drop primary key and foreign key constraints. And I always rehearse outside the maintenance window.


Does restoring a database in Microsoft SQL Server add objects to Resource Database?

Well, reading the documentation for Resource Database (highlighting mine)

  The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server
  system objects, such as sys.objects, are physically persisted in the
  Resource database, but they logically appear in the sys schema of
  every database. The Resource database does not contain user data or
  user metadata.

Given the above and the fact that it is READ-ONLY, I don't see how restoring a database updates the resource database.


On the Jacob_Archive database you need create an External Data Source that makes reference to the source database.

CREATE EXTERNAL DATA SOURCE RemoteReferenceData
WITH
(
    TYPE=RDBMS,
    LOCATION='myserver.database.windows.net',
    DATABASE_NAME='ReferenceData',
    CREDENTIAL= SqlUser
);

Then create External Tables for all tables on the source database that you want to archive data.

CREATE EXTERNAL TABLE [dbo].[zipcode](
    [zc_id] int NOT NULL,
    [zc_cityname] nvarchar(256) NULL,
    [zc_zipcode] nvarchar(20) NOT NULL,
    [zc_country] nvarchar(5) NOT NULL
)
WITH
(
    DATA_SOURCE = RemoteReferenceData
);

Then extract all the data you want from the remote table using an elastic query. You can create a stored procedure to extract the data you want to archive and put it on a table located on the Jacob_Archive database.

Once you have extracted the information you can run a stored procedure on the source table to remove the archived data. You can create an schedule task to perform/run both stored procedures using Azure Automation.


To sum up the comments:

Like @ypercube hinted, you can do it in a single command, which is cheaper and safer:

ALTER TABLE distributors
  DROP CONSTRAINT zipchk
, ADD  CONSTRAINT zipchk CHECK (length(zipcode) = 6);

ALTER CONSTRAINT in Postgres 9.4 or later (like you found) can only change the "deferability" of a FK constraints. So not what you are looking for. Other than that, there is no "MODIFY (like) command" for constraints (referring to MySQL's MODIFY I assume).

Details in the manual for ALTER TABLE, like @a_horse mentioned.


Checking for multiple identical values in a Posgresql array

Note: this will only work if you make data.id a PRIMARY KEY

SELECT
    data.id
FROM
    data,
    LATERAL (SELECT DISTINCT unnest(values)) no_duplicates
GROUP BY
    data.id
HAVING
    array_length(values, 1) > COUNT(no_duplicates)

Here's an SQL Fiddle.

This works by converting your array into a recordset/table (which I've called "no_duplicates") using unnest(), and removing duplicates using DISTINCT:

LATERAL (SELECT DISTINCT unnest(values)) no_duplicates

Then I GROUP BY the original data table's ID, and compare the length of the new, filtered recordset with the old, unfiltered table. If the original, unfiltered array is bigger, then we removed duplicates so we should select that row:

array_length(values, 1) > COUNT(no_duplicates)


Can’t connect to local PostgresSQL server

Since your local psql session just works, your database is OK. Your other sessions try to connect using network. Things to check here are the listen_addresses and port parameters to be found in your postgres.conf They must match with what the clients specify.
Next to that, your firewalls must allow the access. For your local firewall check your settings Security and Privacy settings. There is a good chance that there is a question asking to allow your postmaster to connect to the network.


sql server right join with condition returns rows that do not match condition

To elaborate a bit on sp_BlitzErik's comments above:

Your RIGHT JOIN says the following:

  I want all rows from d, with the data from any rows from the a-c join where a.id = substring(d.FILENAME,1,15) and d.CARRIERGUID='production'.

If d.CARRIERGUID = 'production', then you still take the row from d - you just exclude any rows from the a-c INNER JOIN that might otherwise match.

If what you really meant was:

  I want all rows from d where d.CARRIERGUID='production', with the data from any rows from the a-c join where a.id = substring(d.FILENAME,1,15).

then you would need to take d.CARRIERGUID='production' out of the RIGHT JOIN ON clause, and make it the WHERE clause for the query as a whole.


How To Set Up Web Synchronisation With IIS 7.5

The answer at this point appears to be quite simple. I was using a different domain account to run the subscription agent. I had to log into my machine with that account and install the security certificate.


Depending on indexing and size of data, you may find performance is better if you use:

SELECT * FROM table WHERE (col = 0 OR col IS NULL)

Or

SELECT * FROM table WHERE col = 0

UNION ALL

SELECT * FROM table WHERE col IS NULL


How to Grant A User Under A Sql Domain Account To Insert and Update Records

Sounds like DOMAIN\GroupName is a group and DOMAIN\user.name is a member of that group. This means that DOMAIN\user.name  has access to SQL Server through that group. You have 2 options:

Create another group, let's say DOMAIN\NewGroupName and grant that group INSERT and UPDATE, then make DOMAIN\user.name a member of that group.
This is not the fastest solution, but it's a clean solution and it will make your life easy long term if you will get more and more users asking for the same rights as DOMAIN\user.name. You will need rights on AD (I assume DOMAIN\NewGroupName is an AD group.)
Just create an individual login for DOMAIN\user.name and grant him access + UPDATE and INSERT to that DB. You find documentation on how to create the login here. After you have created the login, just right click on it and go to properties --> user mapping --> select the desired database --> select db_datawriter --> click ok. You find more about database roles and access here. If you need that user to have access to all DBs on that server, you can look at server roles (listed on the properties page right above user mapping) and you have some nice documentation about that here.


What impact on the index storage size does overprovisioning of varchar types have?

Variable length columns only store the actual data present in the column.  In other words, the defined length of a varchar column has no bearing on the amount of physical storage allocated.

However, querying the data requires a memory allocation that is commonly calculated to be the number of rows expected multiplied by 50% of the defined column size.  So, if you define a column as varchar(500), but only store 50 bytes in the column,  you're wasting 200 bytes of RAM per row in memory.


Left hand item is the "Available Input Columns". That's the data in your pipeline.

The right hand item is the "Available Lookup Columns" - that's the reference data.

When you click the check button, that adds the lookup entry into the pipeline. Thus, you are adding ContactTypeId as a new column and you are replacing ContactTypeDescription.

That's likely not what you want to be doing.

Instead, you want to click and drag the ContactTypeDescription column from the left side to the matching column on the left. You then want to unclick the ContactTypeDescription from the right hand side because, why replace the value with the same thing?

That will tell the SSIS engine that a match is performed by these two things being equal.

Here comes the nuances of the lookup.

Depending on whether you specify a Full Cache (default) or a Partial/None cache, will determine what matching rules are used. Full cache is going to be case sensitive as it uses .NET equality. Partial/None is going to rely on your database rules (generally case insensitive in my world).

The choice on the Connection tab of selecting a table is fine for small data sets but if you don't need LastModifierIdentityId, why have the source system read it from disk, stream it across the network, have SSIS cache that in memory if you're never going to use it? Instead, write a simple SELECT there. SELECT T.ContactTypeId, T.ContactTypeDescription FROM dbo.Table AS T; Now you're only pulling the data you need, the source system can possibly use an index to satisfy that query, there's less traffic over the network and you're not bloating your lookup cache.

Speaking of bloating, the Full Cache, which is generally what you want, does have an impact on the start time of your package. Before it begins executing, all the requested data from the source system must be brought into the local memory before the first bit of data starts moving. That's a great strategy if you're loading a 50 GB file and you need to compare it against 1000 possible codes. On the otherhand, when you're loading trading data and the source is 1000 rows and your reference table is 50 GB, that's a sizeable delay before your package can even start processing. In a situation like that, you might be interested in a partial/none cache.


In MySQL You can try a function

create function modul_id() returns INTEGER DETERMINISTIC NO SQL return @modul_id;
create view v (your select... where modul_id = modul_id()...);
select * from (select @modul_id:=14 modul_id) m, v;

not tested on your query...


There are apparently 2 problems with you constraint:

Msg 1046, Level 15, State 1, Line 244 Subqueries are not allowed in this context. Only scalar expressions are allowed.

What this means is that you can not use a subquery inside a check constraint, you can only validate such constraint row by row. Examples of valid check constraints would be:

CHECK ( x BETWEEN 1 AND 3 )
CHECK ( x > y )

I think sub-queries are allowed according to SQL-standard, but few if any vendors allow it. Apparently SQL-server is not among those that do.

so the culprit for this problem is:

AND REFmusic = ( select IDmusic FROM Catalogue Where censure = 'YES' )

Let's remove that part and try:

ALTER TABLE Historique_ecoute ADD CONSTRAINT majorite_utilisateur
   CHECK (not (DATEDIFF(yyyy, Utilisateurs.Date_naissance, GETDATE()) < 18)

My guess is that this is not allowed either, because GETDATE() is not deterministic, so a row may be valid during insert but invalid a little bit later. A constraint is never allowed to evaluate to False. During a modification of a row, the constraints can be validated, but in your case, the truth value of the constraint can change without any modification being made. What action would be taken when the constraint all the sudden evaluates to False? There is no transaction that can be rolled backed, no user that can be notified.

As a side-note, I don't understand what Utilisateurs.Date_naissance refers to?

Msg 4104, Level 16, State 1, Line 255 The multi-part identifier "Catalogue.annee" could not be bound.

This error I don't understand. I can't locate annee anywhere in the constraint.

I assume that the choice of song is written to some table ( Historique_ecoute ). What you can do is create a trigger that validates the condition

CREATE TRIGGER validate_age ON Historique_ecoute
AFTER INSERT
AS
    IF (DATEDIFF(yyyy, Utilisateurs.Date_naissance, GETDATE()) < 18)
         RAISERROR ('You are not allowed to ...',
         ROLLBACK TRANSACTION;
    END;

I don't know the exact syntax, but you should get the idea. I did copy the trigger stuff from CREATE TRIGGER


There are two distinct conditions you need to be aware of
CONDITION #1 : Fragmentation
It is obvious the table has experienced lots of INSERTs, UPDATEs, and DELETEs (or non-SELECT DML). Even loading data sorted by PRIMARY KEY order will make index pages lopsided, at least 45% sparse.
I have posts about this phenomenon before

Aug 03, 2011 : When should I rebuild indexes?
Nov 26, 2012 : Does the Int type primary key insert order affect query performance in MySQL?
Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions? (I discuss AVL Trees Here)

Thus, data pages shrinking from 8733835264 (8.13G) to 601882624 (574M) and shrinking index pages from 4875419648 (4.54G) to (~443M) is not alarming. This will improve read performance.
CONDITION #2 : InnoDB Row Count
InnoDB is notorious for INFORMATION_SCHEMA.TABLES having spurious counts. MyISAM tables give accurate row counts in the table_rows column of INFORMATION_SCHEMA.TABLES.
InnoDB likes to do dives into the index pages in order to guess the number the rows in the table.
I have discussed this feature before as well

Feb 23, 2013 : How does mysql deal with queries that touches myisam and innodb tables? (See ASPECT #3)
Aug 04, 2011 : Optimizing InnoDB default settings
Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

If you want an accurate count of an InnoDB table, you must bite the bullet and run
SELECT COUNT(1) FROM calls_old;

which will produce the same count every time.
Running this:
SELECT table_rows FROM information_schema.tables
WHERE table_name = 'calls_old';

never gives you the same value each time.
In fact, if you run
SHOW INDEXES FROM calls_old;

look at the cardinality column. It will produce different numbers each time. You would have to lock it down by setting innodb_stats_on_metadata to 0. Even if you did that, the stats would become stale over time through normal non-SELECT DML just like a MyISAM table. Running ANALYZE TABLE calls_old with innodb_stats_on_metadata set to 0 will fix the index statistics.
Having innodb_stats_on_metadata remain at 1 will make ANALYZE TABLE calls_old  completely useless. (See my post From where does the MySQL Query Optimizer read index statistics?).
Since the table is called calls_old, I can surmise that you do not need to adjust any other options since it may be an archive table as the name suggests.
EPILOGUE
The value of Avg_row_length is a good indicator that you should defragment the table. When you see an InnoDB table growing that much, you could just run
ALTER TABLE calls_old ENGINE=InnoDB;

to shrink that table.
Thus, the behavior you are seeing is driven by the two conditions I just discussed.
SUGGESTION
If you want to verify the contents, you should these two queries:

SELECT COUNT(1) FROM calls_old;
CHECKSUM TABLE calls_old;

before and after dumping/importing.


PostgreSQL determine column type when data_type is set to ARRAY

One option is to simply cast udt_name to a regtype:

SELECT column_name, data_type, udt_name::regtype
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name='table_name';

Another option is to use the function format_type(), but that needs the oid of the data type:

select c.attname, pg_catalog.format_type(c.atttypid, NULL) as data_type
from pg_attribute c
  join pg_class t on c.attrelid = t.oid
  join pg_namespace n on t.relnamespace = n.oid
where t.relname = 'table_name'  -- your table name
  and n.nspname = 'public' -- table schema
  and c.attnum >= 0 -- exclude internal columns


Transaction independent writes inside a trigger

One solution is to add a linked server to your own sever.  On the linked server, disable transactions.  Any calls made to the linked server will now run in their own transaction.

Example setup:

-- Add linked server called LOCALHOST
USE [master]
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LOCALHOST')
    EXEC master.dbo.sp_dropserver @server=N'LOCALHOST', @droplogins='droplogins'

EXEC master.dbo.sp_addlinkedserver @server = N'LOCALHOST', @srvproduct=N'SQL Server'
-- Allow procedure calls
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'rpc out', @optvalue=N'true'
-- But do not allow the procedure calls to enlist in our transaction
EXEC master.dbo.sp_serveroption @server=N'LOCALHOST', @optname=N'remote proc transaction promotion', @optvalue=N'false'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LOCALHOST', @locallogin = NULL , @useself = N'true'

-- Create a test procedure
use TestDatabase
if exists (select * from sys.tables where name = 'Log')
    drop table log
if exists (select * from sys.procedures where name = 'WriteLog')
    drop procedure WriteLog
go
create table log (id int identity, message varchar(256))
go
create procedure WriteLog
as
insert Log (message) values ('Hello World!')
go

Test code:

-- Insert through a direct call, and then through a linked server call
begin transaction
exec TestDatabase.dbo.WriteLog
exec [Localhost].TestDatabase.dbo.WriteLog
rollback transaction

select * from Log

The select will show only 1 row.  The rollback has not undone the effefcts of the linked server call.

While a nice trick, this is probably not something you'd want to do inside a production environment.  Personally I'd only allow data modifications through a stored procedure, and add logging in the stored procedure itself, before it starts a transaction (if any.)


What is the proper way to design this database?

You're structuring your data using nested structures. Instead, you should look for relations (tables), with a flat structure... and ask the database to join them when you need content from more than one. Don't be afraid to JOIN them all, this is what RDBMS are for.

You could use the following structure. I've defined it using textual SQL; but you can work out the equivalent ERD if you need to, based on all the foreign keys (REFERENCES or FOREIGN KEY) already defined in the tables.

Table where relevant data for your associates

CREATE TABLE associates
(
    associate_id INTEGER NOT NULL PRIMARY KEY,  /* Probably should be IDENTITY */
    associate_name TEXT NOT NULL,
    other_data TEXT
) ;

Table where all jobs are described:

CREATE TABLE jobs
(
    job_id INTEGER NOT NULL PRIMARY KEY,  /* Probably should be IDENTITY */
    job_description TEXT
) ;

Table where you assign which are the job/s that every associate should perform, and the average_daily_goal:

CREATE TABLE jobs_x_associates
(
    job_id INTEGER NOT NULL REFERENCES jobs(job_id),
    associate_id INTEGER NOT NULL REFERENCES associates(associate_id),
    average_daily_goal DECIMAL(5,2) NOT NULL,

    -- (job_id, associate_id) is the natural primary key
    PRIMARY KEY(job_id, associate_id)
) ;

Table where you store the worked hours that every associate performed of every kind of job s/he is assigned to perform (this is, more-or-less, equivalent to your DayData, but with a column for associate_id, so that it can relate to it):

CREATE TABLE worked_hours
(
    job_id INTEGER NOT NULL REFERENCES jobs(job_id),
    associate_id INTEGER NOT NULL REFERENCES associates(associate_id),
    work_day date NOT NULL,
    worked_hours DECIMAL(5,2) NOT NULL,

    -- The pair (job_id, associate_id) must exist in jobs_x_associates
    -- we do not allow associates to perform jobs they have been not
    -- assigned. Depending on business rules, you can lift this
    -- restriction
    FOREIGN KEY (job_id, associate_id)
        REFERENCES jobs_x_associates(job_id, associate_id),

    -- Only one entry per associate,job and day
    PRIMARY KEY(associate_id, job_id, work_day)
) ;

Sample data for base tables:

INSERT INTO associates
    (associate_id, associate_name, other_data)
VALUES
    (1, 'Alice Cooper', 'nice buy'),
    (2, 'Bob Geldorf', 'difficult to define') ;

INSERT INTO jobs
    (job_id, job_description)
VALUES
    (101, 'Cleaning windows'),
    (102, 'Dish washing') ;

Sample data for jobs assigned and worked hours:

-- What should everyone do?
INSERT INTO jobs_x_associates
    (job_id, associate_id, average_daily_goal)
VALUES
    (101, 1, 5.0), -- Alice should clean windows for avg. 5 h/day
    (101, 2, 3.0), -- Bob should clean windows for avg. 3 h/day
    (102, 1, 1.0), -- Alice should wash dishes for avg. 1 h/day
    (102, 2, 0.5); -- Bob should wash dishes for avg. 0.5 h/day

-- We record what they actually did
INSERT INTO worked_hours
    (job_id, associate_id, work_day, worked_hours)
VALUES
    (101, 1, '2017-07-01', 5.0),
    (101, 2, '2017-07-01', 3.0),
    (102, 1, '2017-07-01', 0.7),
    (102, 2, '2017-07-01', 1.0),

    (101, 1, '2017-07-02', 3.0),
    (101, 2, '2017-07-02', 2.0),
    (102, 1, '2017-07-02', 0.8),
    (102, 2, '2017-07-02', 1.3) ;

With these data, you can perform queries such as:

 SELECT
     work_day, associate_name, job_description,
     average_daily_goal, worked_hours
 FROM
     worked_hours
     JOIN jobs USING(job_id)
     JOIN associates USING (associate_id)
     /* LEFT */ JOIN jobs_x_associates USING (job_id, associate_id)
 ORDER BY
     work_day, associate_name, job_description ;

 work_day   | associate_name | job_description  | average_daily_goal | worked_hours
 :--------- | :------------- | :--------------- | -----------------: | -----------:
 2017-07-01 | Alice Cooper   | Cleaning windows |               5.00 |         5.00
 2017-07-01 | Alice Cooper   | Dish washing     |               1.00 |         0.70
 2017-07-01 | Bob Geldorf    | Cleaning windows |               3.00 |         3.00
 2017-07-01 | Bob Geldorf    | Dish washing     |               0.50 |         1.00
 2017-07-02 | Alice Cooper   | Cleaning windows |               5.00 |         3.00
 2017-07-02 | Alice Cooper   | Dish washing     |               1.00 |         0.80
 2017-07-02 | Bob Geldorf    | Cleaning windows |               3.00 |         2.00
 2017-07-02 | Bob Geldorf    | Dish washing     |               0.50 |         1.30

 SELECT
     associate_name, job_description,
     average_daily_goal,
     CAST(avg(worked_hours) AS DECIMAL(10,2)) AS average_worked_hours
 FROM
     worked_hours
     JOIN jobs USING(job_id)
     JOIN associates USING (associate_id)
     /* LEFT */ JOIN jobs_x_associates USING (job_id, associate_id)
 GROUP BY
     associate_name, job_description, average_daily_goal
 ORDER BY
     associate_name, job_description ;

 associate_name | job_description  | average_daily_goal | average_worked_hours
 :------------- | :--------------- | -----------------: | -------------------:
 Alice Cooper   | Cleaning windows |               5.00 |                 4.00
 Alice Cooper   | Dish washing     |               1.00 |                 0.75
 Bob Geldorf    | Cleaning windows |               3.00 |                 2.50
 Bob Geldorf    | Dish washing     |               0.50 |                 1.15

You can check everything at dbfiddle here

NOTE: If you do allow associates to perform (and record) jobs they were not assigned to do, remove the FOREIGN KEY mentioned before, and change the /* LEFT */ JOIN to actual LEFT JOIN. This will allow the database to still show them, even if the average_daily_goal is N/A (i.e.: NULL).


What is a good strategy for updating lot of rows in postgersql?

The usual way is to import the CSV file into a staging table. Either an unlogged table which you only create once, or a temp table which you  create immediately before the import.
Something along the lines:
create temp table inventory_import (item_id integer primary key, quantity integer);
copy inventory_import from '/path/to/file.csv' ... ;

update inventory i
  set quantity = im.quantity
from inventory_import im
where i.item_id = im.item_id;


How to speed up full text query on a table with 4 million rows? (MariaDB)

How big is the dataset?  SHOW TABLE STATUS.

It sounds like it is much less than innodb_buffer_pool_size, so it will probably reside in RAM once it is loaded.

There is no automatic way to get it loaded.  You could write a script to access every row in the data and every index.  But that is tricky and slow.

On the other hand, you can keep it loaded (at least across graceful shutdowns) by turning ON these:  innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_shutdown.


What's a good SQL Server backup schedule?

As with all things in SQL Server, it depends.

First thing you need to do is make sure you understand what each type of backup does.

Books Online has all the gooey details, but here's my summary.

A FULL backup contains everything within the database.  A DIFFERENTIAL backup is cumlative NOT incremental.  In your example, if your database failed on the 12th, then you'd only need to restore the full backup from the 1st and then the most recent differential on the 12th, then followed by all the transaction log backups upto the failure.  A TRANSACTION LOG backup is only needed for databases using the full or bulk-logged recovery model.  If you're using the simple recovery model then transaction log backups are not needed.

Now that we've cleared that up...Designing a backup schedule really depends on how much data you need to recovery and how fast you need to recover it in the event of a diaster.  I would recommend starting with a full backup each day.  You can always reduce the frequency later.  Remember the differential backup is cumlative since the last full, so depending on the amount change going on in your database the differential could be larger than the full backup after a few days.  If you do a full backup each day, then you may not need to use differentials at all; however you could still do it once a day and schedule it at 12 noon.  The transaction log backup only backs up the log.  The frequency of the log backup will determine how much data you're willing to lose in the event of a failure.  If you run your log backup every 15 minutes, then you would expect to lose up to the last 15 minutes of data that changed.  15 minutes is a good frequency, but every 30 minutes works perfectly for my environment.

As I said earlier, it all depends on your environment.  After you've designed and setup your backup schedule, remember to test it on an alternate server.  Practice restoring your full, diff, and log backups so that you know everything works like you designed it.

Books Online has some good info if you plan use Maintenance Plans, but if you really want flexibility then check out Ola Hallengren's backup scripts.


Best practice for storing large files (+1GB each)

If I understand correctly that you only want to Archive the files produced in case you need the occasional one for referring to later, then cloud file storage would be cheaper and simpler than storing them in a DB.


Selecting row by field and exact list of join id

You can apply conditional aggregation

HAVING sum(case when c.id in (223,224,225) then 1 else -1 end) = 3

This assumes that those ids are unique.


How does MySQL determine the 'index first key' and 'index last key' with indexes?

That is really the job of the MySQL Query Optimiser.  You can determine more information by evaluating the results of EXPLAIN.

You can also manually tweak the Query Optimiser by forcing or hinting an index, or changing the optimizer_search_depth variable which will tell the Query Optimiser how far in the future of each incomplete plan the optimizer should look to evaluate whether it should be expanded.


I have an answer in a previous question that talks about the different forms of partitioning.

Not knowing if you have Database Paritioning Feature (DPF) enabled (since it is a separate license for DB2), your biggest bang for the buck would be to have table partitioning and/or a multidimensional clustering table. MDC's you want to cluster on things with low cardinality, so don't use your date field for that. But you may be able to use other fields used in the query for it.

As for partitioning, you will first need to create your table with partitions defined. (You unfortunately can't partition a non-partitioned table). In your definition, you could define all the partitions you want. After that you can always attach new ones. Once they are defined you can move the data from the old table to the new table, or just reload your fact table (depending on your business case).

Now, here is another thought on defining partitioning. IBM does recommend putting each partition in its own tablespace (for performance and disaster recover reasons). To do this you would have to define the table multiple times each with its partition range and assign it to a tablespace. Then do ALTER TABLE ADD PARITION and attach the table to the "base table". If you are using one day at a time for data, this perhaps could get costly, so maybe you would rather wish to partition quarterly or monthly or something like that.


See if this helps.

SQL> select ltrim(sys_connect_by_path(classificationid, ' \ '), ' \ ') hierarchypath
  2  from classstructure
  3  where parent is not null
  4  connect by prior classstructureid = parent
  5  start with parent is null;

HIERARCHYPATH
--------------------------------------------------------------------------------
FLEET \ LIGHTDUTYVEHICLE
FLEET \ MEDIUMDUTYVEHICLE
ACTIVETRANSPORTATION \ FACILITYWALKWAY
ACTIVETRANSPORTATION \ TRAIL
ACTIVETRANSPORTATION \ SIDEWALK
ACTIVETRANSPORTATION \ SIDEWALK \ SDWRAMP

6 rows selected.

SQL>


MySQL - Why specific query doesn't use cache?

When either table is modified, all entries in the QC are removed (pruned) from the QC for that table.
If the query has too big a resultset, it won't go into the QC.  (Please provide SHOW VARIABLES LIKE 'query_cache*';

Do you have this on wp_postmeta: PRIMARY KEY(post_ID, meta_key) ?  If you can switch to that, performance should improve when the QC is not used.  If you have trouble, let's see SHOW CREATE TABLE wp_postmeta; some WP versions are really lame when it comes to optimizing that schema.

Do you need the GROUP BY?  JOIN...GROUP BY often suffers from the explosion-implosion syndrome that makes such queries slow.  There may be a workaround.

Will you be "paginating"?  If so, use of OFFSET is inefficient.

Getting rid of SQL_CALC_FOUND_ROWS will speed up the query.

That is a confusing query, what is it trying to do?

Index

PRIMARY KEY (meta_id),         -- useless and slows things down
KEY post_id (post_id),         -- inadequate
KEY meta_key (meta_key(191))   -- prefixing is next to useless

Change to this

Change `meta_key` from VARCHAR(255) to VARCHAR(191)
PRIMARY KEY (post_id, meta_key)
no other indexes unless you ever search for meta_key without post_id

Notes:

You probably don't have very long keys, so changing to 191 may be safe.  You could run SELECT MAX(CHAR_LENGTH(meta_key)) FROM .. to see what the current max is.
If you can't shrink to 191, then try to upgrade to 5.6.3 / 5.5.14, which allows you to increase the limit (with some effort), or 5.7.7, which allows it by default.
'Composite' keys are often better than single-column keys, especially in this case.
Having the primary access pattern be the PK makes it faster.
These improvements may not help that particular contorted query, but it will help the table in other ways.

(I have been ranting about WP for years.  I would be happy to advise them.)


How to remove references to performance_schema.user_variables_by_thread in mysql 5.6?

performance_schema.user_variables_by_thread is in MySQL-5.7 but not MySQL-5.6. It cannot be copied as its generated by the server to represent the internal state.
MySQL-5.6 seems to have deprecated Jira support per https://confluence.atlassian.com/adminjiraserver/supported-platforms-938846830.html
MySQL-5.7 reference on user_variables_by_thread


Change SQL Server settings without restarting services

It depends on what settings you changed based on http://msdn.microsoft.com/en-GB/library/ms189631.aspx. Some require a restart (RR), other can be done via RECONFIGURE

There is no trick or workaround. At all.

Edit, now we know it is Authentication mode...

This is automatically deals with the instance name

 EXEC xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'LoginMode',
    REG_DWORD,
    1; -- or 2 for mixed

Restart is still needed