Will subquery (using IN) run multiple times for update statement (SQL Server 2017)

Will it always update all rows for the ID to DM = 1 or sometime just
  one?

If you don't have NULL values in the ID column then either all DM's for one type of ID != 1 and as a result all ID's that have the same value will be updated or none.

But as said previously, depending on nullability of the ID column you can have suprising results and performance implications.

More on NOT IN and NOT EXISTS here.

An example of spools being added & multiple table accesses as a result of using NOT IN

A cleaner way to write your query:

UPDATE a
SET a.DM = 1
FROM TableA a
WHERE NOT EXISTS
(
SELECT ID FROM TableA  b
WHERE b.DM = 1
AND b.ID = a.id );

Do note that NULL values will be updated as well, you would have to add extra logic such as adding AND a.id IS NOT NULL;.

Resulting cleaner join path:

Example of null values resulting in 0 updates happening:

CREATE TABLE dbo.TableA (id int,DM int);

INSERT INTO dbo.TableA(id,DM)

SELECT TOP 500
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) %200, 1
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

INSERT INTO dbo.TableA(id,DM)

SELECT TOP 500
ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) +200  , 0
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

INSERT INTO dbo.TableA(id,DM)
VALUES(NULL,1),(NULL,0);

UPDATE a
SET DM = 1
FROM TableA a
WHERE a.ID NOT IN (SELECT DISTINCT ID FROM TableA WHERE DM = 1) ;

--> 0 rows affected

UPDATE a
SET a.DM = 1
FROM TableA a
WHERE NOT EXISTS
(
SELECT ID FROM TableA  b
WHERE b.DM = 1
AND b.ID = a.id )
AND a.id IS NOT NULL; --If you do not want to update null id's

--> (500 rows affected)


Capture live stored procedure calls (as per Query Profiler) in SQL Azure

You can use SQL Server Management Studio XEvent Profiler and you may also see Azure Data Studio supports seeing XEvents from cloud and on-premises but SSOS uses the ring buffer target.


Query produces two query plans (at different times). How do I always produce the better query plan?

If you were on 2016 or higher, you could have used Query Store to track your plans and force the good one through Query Store functionality. You can still force the plan,though, easiest is probably sp_create_plan_guide_from_handle: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-create-plan-guide-from-handle-transact-sql?view=sql-server-2017 .

Other options could be to use OPTIMIZE FOR or even RECOMPILE query hints, depending on the circumstances and what suits you best.


What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES?

That column exists for compatibility with other databases.

Interestingly, here is information_schema.tables in PostgreSQL

postgres=# \d information_schema.tables
                       View "information_schema.tables"
            Column            |               Type                | Modifiers
------------------------------+-----------------------------------+-----------
 table_catalog                | information_schema.sql_identifier |
 table_schema                 | information_schema.sql_identifier |
 table_name                   | information_schema.sql_identifier |
 table_type                   | information_schema.character_data |
 self_referencing_column_name | information_schema.sql_identifier |
 reference_generation         | information_schema.character_data |
 user_defined_type_catalog    | information_schema.sql_identifier |
 user_defined_type_schema     | information_schema.sql_identifier |
 user_defined_type_name       | information_schema.sql_identifier |
 is_insertable_into           | information_schema.character_data |
 is_typed                     | information_schema.character_data |
 commit_action                | information_schema.character_data |
View definition:
 SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
            WHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::text
            WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
            ELSE NULL::text
        END::information_schema.character_data AS table_type, NULL::character varying::information_schema.sql_identifier AS self_referencing_column_name, NULL::character varying::information_schema.character_data AS reference_generation, NULL::character varying::information_schema.sql_identifier AS user_defined_type_catalog, NULL::character varying::information_schema.sql_identifier AS user_defined_type_schema, NULL::character varying::information_schema.sql_identifier AS user_defined_type_name,
        CASE
            WHEN c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" AND (EXISTS ( SELECT 1
               FROM pg_rewrite
              WHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '3'::"char" AND pg_rewrite.is_instead)) THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.character_data AS is_insertable_into, 'NO'::character varying::information_schema.character_data AS is_typed,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE'::text
            ELSE NULL::text
        END::information_schema.character_data AS commit_action
   FROM pg_namespace nc, pg_class c
  WHERE c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

In PostgreSQL, the table_catalog field actually means something. In fact, it is part of the SQL-92 standard. The catalog of a database groups related metadata for a database. See, in PostgreSQL, the information_schema is only for the database you are connected to. In MySQL, this concept is blurred and the information_schema contains all metadata for all databases, especially if you have ALL PRIVILEGES on *.* as with root@localhost.

It makes sense that it is there in MySQL but blank. The information_schema in MySQL will collapse for you if your DB user has ALL PRIVILEGES on db.* instead of *.*. In PostgreSQL, the collapse is automatic when you connect to a database schema. So, the table_catalog column is just there for MySQL to be SQL-92 compatible and nothing more.


Does replication with SSL encryption work on MySQL 5.5?

To use SSL for encrypting the transfer of the binary log required during replication, both the master and the slave must support SSL network connections. If either host does not support SSL connections (because it has not been compiled or configured for SSL), replication through an SSL connection is not possible.

Setting up replication using an SSL connection is similar to setting up a server and client using SSL. You must obtain (or create) a suitable security certificate that you can use on the master, and a similar certificate (from the same certificate authority) on each slave.

For more information on setting up a server and client for SSL connectivity refer this link

Please follow the steps mentioned here:

http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html

I was searching in bugs.mysql.com related to MySQL 5.5 but there is nothing reported w.r.t "replication with SSL" for v5.5.
You can mention exact error message here for analysis.


Check which statistics were used in query optimization

When using the original (70) cardinality estimation model, undocumented trace flags 9204 and 9292 can be used to show which statistics were loaded during query optimization, as detailed in my post How to Find the Statistics Used to Compile an Execution Plan.

You can also use undocumented trace flag 8666 as described by Fabiano Amorim in Statistics used in a cached query plan.

For the updated cardinality estimation model (120 and later), undocumented trace flag 2363 shows a lot of useful information, including which statistics were loaded.

New for SQL Server 2017, the statistics objects used to compile and optimize a query are included directly in the execution plan. See SQL Server 2017 Showplan enhancements by Pedro Lopes.


My advice would be to just restore the entire backup. In my current environment, someone accidentally screws up some table once every few months. It just happens. Of course, it always hits our biggest database. 200GB backup file, 1+TB when fully restored. It is just easier to restore a copy somewhere, find the specific table we need, and just update that specific data. The whole process takes about an hour or so. I doubt any other solution would be quicker, worth the extra financial costs, or added complexity.


Deadlock: How to prevent Primary Key Lock in SELECT

Making queries faster is a way to minimize deadlocks, if not avoid them.

INDEX(origin, lastLogin)

should help the first query.

The UPDATE should be helped by

INDEX(uid, chips)

Please provide SHOW CREATE TABLE if you wish to discuss thing further.

How many cron jobs run at midnight?  If more than one, stagger them.  Otherwise the buffalo will stumble over each other.

If you are collecting "yesterday's" data, you should include a stopper -- else it could include a few records from "today":

WHERE lastLogin >= '2017-05-31' - INTERVAL 1 DAY
  AND lastLogin  < '2017-05-31'

With that, it won't matter if you schedule it a few minutes after midnight.

I cringed at --1 and + -1, but I guess they work OK.


Can SSRS used to Update or Modify data tables?

If Madeira Data shows you how to do it, then I think it's safe to say:

It's possible.

It can be made to work, as evidenced by this paragraph from them:

It’s especially good when your organization doesn’t have skilled programmers that can create a pretty-looking graphical user interface (GUI). Or when there are budget and / or time restrictions.

Deciding if this kind of setup will work for you is entirely your decision.


Duplicates when checking for two different column values based on a key that is not unique in table

Based on what you have shared, this should work:

Table and data

CREATE TABLE #Orders (Personid int, proc_code int, time datetime)

INSERT INTO #Orders VALUES (123, 1,  '4/25/2016 00:01:00')
INSERT INTO #Orders VALUES (123, 2,  '4/25/2016 00:01:00')
INSERT INTO #Orders VALUES (123, 2,  '4/25/2016 00:02:00')
INSERT INTO #Orders VALUES (111, 1,  '4/25/2016 00:01:00')
INSERT INTO #Orders VALUES (111, 1,  '4/25/2016 00:03:00')
INSERT INTO #Orders VALUES (111, 2,  '4/25/2016 00:01:00')

Query

select a.PersonId, a.proc_code,
    CASE WHEN b.proc_code = 2 THEN 'Y' ELSE 'N' END AS 'Y/N'
FROM #Orders a
LEFT JOIN #Orders b
    ON a.time = b.time
    AND a.Personid = b.Personid
    AND a.proc_code != b.proc_code
WHERE a.proc_code = 1

DROP TABLE #Orders


Assuming native mode here...

The main thing you are migrating when doing the upgrade to SQL Server 2014 is the reports, subscriptions, and then your data connections. I can't think of a reason why you would need to put this database in replication just for an upgrade. Also, while it probably could be done (and work) it is not a supported method of upgrade from Microsoft to put the SSRS databases in mirroring configuration. So proceed at your own risk.

Unless you are adding/modifying reports or subscriptions often I would not see why you could not just do a quick migration according to documentation. Perform your testing of the new version with your reports, and then at cut-over time just do a backup/restore one last time.


Get members of a given group from N:M relationship

This EXISTS query returns unique users, regardless of whether there can be duplicate entries in auth_user_groups.
SELECT *
FROM   auth_user u
WHERE  EXISTS (
   SELECT 1
   FROM   auth_user_groups
   WHERE  user_id = u.id
   AND    group_id = (SELECT id FROM auth_group WHERE name = 'group1')
   );

Typically fast, too.
Notes
For Postgres - which you seem to be using.
The sequence of index columns matters. You defined UNIQUE(user_id, group_id), which is implemented with a corresponding unique index. For your particular query, an index on (group_id, user_id) would be preferable.
You can either switch the columns of the UNIQUE constraint, or create an additional (optionally unique) index with the columns reversed if you need both (which is the common case). Related:

Is a composite index also good for queries on the first field?

Also be aware that columns included in a UNIQUE constraint can still be NULL. (Unlike a PRIMARY KEY constraint which makes all member columns NOT NULL automatically!) You'd typically want user_id and group_id in auth_user_groups to be NOT NULL as well. Related:

How to implement a many-to-many relationship in PostgreSQL?


Can you time how long an Oracle import takes?

If you just want to time how long it takes, you could call it via a batch file like this:

echo %DATE% %TIME% >> time.log
imp parfile=./file.par etc etc
echo %DATE% %TIME% >> time.log

Once everything has run through, have a look at the time.log file and work out the difference between the 2 values.


I'm curious about your statement: read hits to bufferpool are around -1300%. Is your bufferpool hit ratio -1300% ? Can you check the hit-ratio with?

  WITH BPMETRICS AS (
    SELECT bp_name,
           pool_data_l_reads + pool_temp_data_l_reads +
           pool_index_l_reads + pool_temp_index_l_reads +
           pool_xda_l_reads + pool_temp_xda_l_reads as logical_reads,
           pool_data_p_reads + pool_temp_data_p_reads +
           pool_index_p_reads + pool_temp_index_p_reads +
           pool_xda_p_reads + pool_temp_xda_p_reads as physical_reads,
           member
    FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
   SELECT
    VARCHAR(bp_name,20) AS bp_name,
    logical_reads,
    physical_reads,
    CASE WHEN logical_reads > 0
     THEN DEC((1 - (FLOAT(physical_reads) / FLOAT(logical_reads))) * 100,7,2)
     ELSE NULL
    END AS HIT_RATIO,
    member
   FROM BPMETRICS;

If it is indeed negative, there are more physical reads than logical reads. Several physical reads per logical read is weird, one possible cause is that the bufferpool is so small that it cant hold the EXTENTSIZE for the tablespace in question.

One other thing that comes to mind is if Db2 failed to start the normal bufferpool and instead uses the default bufferpool - which is very small - for that pagesize.


Convert a DATETIME value using string Timezone Info?

If your time zones are IANA time zones and not Windows time zones, then you should be able to use Matt Johnson's "SQL Server Time Zone Support" project, hosted on GitHub at:

https://github.com/mj1856/SqlServerTimeZoneSupport

It is a pure T-SQL solution.


Tables with the same name in the same DB (very strange bug)

This is the result of a known bug on Azure for MySQL. A fix was deployed last month, but you'll need to restart your server before it takes effect. After restarting, you will see the correct table information.


Foreign key constraint based on conditional column

How can I set up a foreign key constraint that will know which table to be linked to?

Either you can't, or you shouldn't. :)

The standard way to structure this is to have a parent Entity table, and a table-per-child for each of University, Program, and Course. The Entity table optionally contains a denormalized column that specifies the type of entity -- this has a bunch of advantages, so I recommend it. Also, at least in the development stages, I would recommend creating triggers to validate that rows in this structure end up in the right place (i.e., for a given EntityID, there should be only 1 matching row in all the child tables).

Then all you do is create the foreign key to the primary key of Entity.

What I've described looks like this (SQL Server syntax):

CREATE TABLE dbo.EntityType
(
    EntityTypeID tinyint NOT NULL
        PRIMARY KEY
);

CREATE TABLE dbo.Entity
(
    EntityID int NOT NULL IDENTITY
        PRIMARY KEY,
    EntityTypeID tinyint NOT NULL
        FOREIGN KEY REFERENCES dbo.EntityType(EntityTypeID)
);

CREATE TABLE dbo.University
(
    EntityID int NOT NULL
        PRIMARY KEY
        FOREIGN KEY REFERENCES dbo.Entity(EntityID)
);

CREATE TABLE dbo.LearningContext
(
    LearningContextID int NOT NULL IDENTITY
        PRIMARY KEY,
    EntityID int NOT NULL
        FOREIGN KEY REFERENCES dbo.Entity(EntityID)
);

This is the equivalent of subclassing in a database, if you're familiar with client-side programming constructs.

Note how this structure easily allows adding a new derived Entity subtype without changing the existing schema objects.


Can I enforce cascade delete in a delete from statement?

As you probably know, the way to do this is a foreign key whith ON DELETE CASCADE. But you don't want this, so I see 2 options (there may be more as I'm not a SQL Server expert):

Make this in the hard way: write multi-table DELETE statements. As you can see in the documentation, it is very similar to a JOIN.
If you want to do this easy (for example to hide information about database structure to developers), you'll need a BEFORE DELETE trigger. This trigger can delete rows from other tables. I understand that you want this to happen only in certain cases; this is doable by setting (before your query) and checking (in the trigger) SESSION_CONTEXT. Rows will only be deleted if the variable is set. It should be unset at the end of the trigger itself, so developers won't need to take care of this (they could forget). Since the trigger will delete relevant rows before the query is executed, the constraint will not make it fail.
A very similar approach, but maybe cleaner, would be writing a stored procedure.


File permissions for SQL Server 2014 virtual account

Dan,

I'm assuming the account running the agent job is a sysadmin. In this case, the
DEV instance will need to have its' service account or service sid for the DATABASE ENGINE given access to the folder.

SQL Server agent doesn't do the restore, it connects to SQL Server and impersonates the account if non sysadmin and then continues. In this case you're connecting to the database engine and issuing a RESTORE DATABASE command which will be processed by the engine. SQL Agent isn't touching the backups, the engine is.

Edit: Explaining the posted agent output

Executed as user: NT SERVICE\SQLAgent$MyAppDEV.

This is letting you know that the Agent job was executed as the SQLAgent$MyAppDEV local account. This is most likely due to the job owner being sysadmin.

Cannot open backup device 'E:...MyApp_backup_2016_02_24_092912_0136837.bak'.

Here is the actual error, we can't open the file. We don't know why yet.

Operating system error 5(Access is denied.).

This is explaining why we can't open the file, the OS returned error 5. This can be validated by checking the OS return code by executing net helpmsg 5 on a command prompt.

[SQLSTATE 42000] (Error 3201)
RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013).
The step failed.

Some extra notes about what is happening, but this isn't relevant to the actual error or issue.


The task can be solved using dynamic SQL. Something like:

CREATE PROCEDURE assing_value(propertyname TEXT, blueprintid BIGINT)
BEGIN
SELECT CONCAT('UPDATE t_ci SET s39=', column_name, ' WHERE blueprint_id=', blueprintid)
  INTO @sql
  FROM t_core_properties
  WHERE property_name=propertyname;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;
END;

and then

CALL assing_value('Virtual IP Address', 1234);

or the same in plain code:

SET @column := ( SELECT column_name
                 FROM `t_core_properties`
                 WHERE property_name='Virtual IP Address');
SET @sql := CONCAT('UPDATE t_ci SET s39=',
                    @column,
                   ' WHERE blueprint_id IN (',
                   ' SELECT id',
                   ' FROM `t_core_blueprints`',
                   ' WHERE NAME=\'VMWare VCenter\')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;


what is the formal statement terminator with db2 sql

You would need to use the -td<statement terminator> syntax. So you would need to format the above as follows:

db2 -td/ -svf db2.sql

This would tell DB2 that your statement terminator is the / character, otherwise, yes the default is the semicolon ;.


How can I pass column to function in sql?

There are much more efficient ways to calculate a simple or grouped median than the one shown in your question:

What is the fastest way to calculate the median?
Best approaches for grouped median

The general winner for 2012 is a method by Peter Larsson. The pattern is:

Simple Median

SELECT
    Median = AVG(1.0 * SQ.YourColumn)
FROM
(
    SELECT NumRows = COUNT_BIG(*)
    FROM dbo.YourTable
    WHERE ColumnName IS NOT NULL
) AS C
CROSS APPLY
(
    SELECT YT.ColumnName
    FROM dbo.YourTable AS YT
    WHERE YT.ColumnName IS NOT NULL
    ORDER BY YT.ColumnName ASC
    OFFSET (C.NumRows - 1) / 2 ROWS
    FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
) AS SQ;

Grouped Median

SELECT
    SQ2.GroupingColumn,
    SQ2.Median
FROM
(
    SELECT
        GroupingColumn,
        NumRows = COUNT_BIG(*)
    FROM dbo.YourTable
    WHERE ColumnName IS NOT NULL
    GROUP BY
        GroupingColumn
) AS C
CROSS APPLY
(
    SELECT
        Median = AVG(1.0 * SQ1.YourColumn)
    FROM
    (
        SELECT YT.ColumnName
        FROM dbo.YourTable AS YT
        WHERE
            YT.GroupingColumn = C.GroupingColumn
            AND YT.ColumnName IS NOT NULL
        ORDER BY
            YT.ColumnName ASC
            OFFSET (C.NumRows - 1) / 2 ROWS
            FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
    ) AS SQ1
) AS SQ2;

To maximize the performance of the OFFSET method above, you may need to add a locking hint (advanced topic). Suitable indexing will also be required, of course.

Code reuse

This is hard to achieve directly with a T-SQL function, since these do not allow the execution of dynamic SQL (assuming you were thinking of passing in the column name).

There are several ways around this, including using a function to generate the dynamic SQL text itself, which can then be executed by the caller. There aren't really enough details in the question to say which approach would be most suitable for you.


How does SQL Server order results when joins are used?

Since you haven't told SQL Server how to order the results, it is free to do so in whatever is the most efficient. In this way it will depend on what is the cheapest to sort, and the columns you select will drive that because it in turn depends on the cheapest index(es) to use to get at the information requested by the query. This can change from execution to execution not just by changing the query text but also by things like data changes, statistics updates, freeproccache, service packs, hotfixes, etc. This is especially volatile with so many tables involved if the underlying data is changing rapidly, as it tends to do in OLTP applications.

If you want a predictable order, why don't you use ORDER BY on the query? As written, the SQL specifies that any 900 rows are acceptable, in any order.

Even though, in a lot of cases, you see the same order over and over again for the same query, there is no guarantee unless you say ORDER BY <something>.


returning the number of columns in a row that are zero in mysql

The easiest way is to sum negated values:

SELECT datetime, (!a) + (!b) + (!c) + (!d) AS zero_count
  FROM table
. . . .

Operator ! returns 1 if operand is 0, 0 if operand is non-zero, and NOT NULL if operand is NULL.

Parenthsis added because adding + have higher precedence over negation.


Is it possible to assign sql logins into a group?

When working with SQL Logins you "group" them by using roles within SQL Server. Roles are created at the database-level but with SQL Server 2012 you will be able to create them at the server-level as well.

Once the role is created you can assign specific permissions to that group and it will be applied to the logins that are member of that role. The roles are localized to the instance you are under and are not visible or accessible to remote instances. You will only be able to add the logins in that instance of SQL Server to the roles.


I found a solution online: http://strictlysql.blogspot.com/2010/07/lock-monitoring-sql-server-2000.html. Modified the script to fulfill my requirement.

Created a table in our monitoring database:

CREATE TABLE MyDBMonitoring
  (
     DatabaseName        varchar(35),
     Time                   datetime,
     SPID                        INT,
     Query              varchar(1000),
     QueryStatus         VARCHAR(35),
     LoginName           VARCHAR(50),
     HostName            VARCHAR(50),
     LastBatch              datetime,
     Command              VARCHAR(50),
     ProgramName          VARCHAR(100),
     OpenTransactions           INT
  )
Modified the script from the link and only selected the fields I need and insert the result into the monitoring table by setting up a SQL Agent Job on the Server. Will provide the result to client in two days.

CREATE TABLE #sp_lock_details
  (
     spid     INT,
     dbid     INT,
     objid    INT,
     indid    INT,
     TYPE     VARCHAR(100),
     resource VARCHAR(1000),
     mode     VARCHAR(10),
     status   VARCHAR(50)
  )

INSERT INTO #sp_lock_details
EXEC Sp_lock

DECLARE @id INT
DECLARE @max INT
DECLARE @spid INT
DECLARE @exec_qry VARCHAR(500)
DECLARE @monitored_dt DATETIME

SET @monitored_dt = Getdate()

CREATE TABLE [#sysproc]
  (
     id             INT IDENTITY(1, 1),
     [spid]         [SMALLINT] NOT NULL,
     [dbname]       NVARCHAR COLLATE sql_latin1_general_cp1_cs_as NULL,
     [cpu]          [INT] NOT NULL,
     [memusage]     [INT] NOT NULL,
     [status]       NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [loginame]     NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [hostname]     NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [lastwaittype] NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [last_batch]   [DATETIME] NOT NULL,
     [cmd]          NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     [program_name] NCHAR COLLATE sql_latin1_general_cp1_cs_as NOT NULL,
     QUERY          VARCHAR(500),
     open_tran      INT
  )
ON [PRIMARY]

CREATE TABLE #dbccinfo
  (
     eventype  VARCHAR(255),
     param     INT,
     eventinfo VARCHAR(255)
  )

INSERT INTO #sysproc
            (spid,
             dbname,
             cpu,
             memusage,
             status,
             loginame,
             hostname,
             lastwaittype,
             last_batch,
             cmd,
             program_name,
             open_tran)
SELECT spid,
       Db_name(MASTER.dbo.sysprocesses.dbid) AS dbname,
       cpu,
       memusage,
       status,
       loginame,
       hostname,
       lastwaittype,
       last_batch,
       cmd,
       program_name,
       open_tran
FROM   MASTER.dbo.sysprocesses
WHERE  spid > 50

SET @max = @@IDENTITY
SET @id = 1

WHILE ( @id <= @max )
  BEGIN
      SELECT @spid = spid
      FROM   #sysproc
      WHERE  id = @id

  SET @exec_qry = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(3), @spid) + ')'

  INSERT INTO #dbccinfo
  EXEC (@exec_qry)

  UPDATE sys
  SET    QUERY = eventinfo
  FROM   #sysproc sys,
         #dbccinfo
  WHERE  @id = id

  TRUNCATE TABLE #dbccinfo

  SET @id = @id + 1

END

INSERT INTO dba.dbo.MyDBAMonitoring
  (
     DatabaseName,
     Time,
     SPID,
     Query,
     QueryStatus,
     LoginName,
     HostName,
     LastBatch,
     Command,
     ProgramName,
     OpenTransactions
  )
  SELECT dbname AS DatabaseName,
       CURRENT_TIMESTAMP AS 'TIME',
       #sysproc.spid AS SPID,
       QUERY,
       #sysproc.status                AS query_status,
       loginame,
       hostname,
       last_batch,
       cmd,
       program_name,
       open_tran
  FROM   #sysproc,
           #sp_lock_details
    WHERE  #sp_lock_details.spid = #sysproc.spid
    and dbname = 'ODS;

DROP TABLE #dbccinfo
DROP TABLE #sysproc
DROP TABLE #sp_lock_details


Oracle 10g: same query, different plans on copies of the same DB

There are other parameters that can influence execution plan, like PGA size, number of CPUs, disk I/O speed, different sample sizes when you are gathering statistics, index modifications, different system load, different block sizes and so on and on.

Just too many to check them all.

You could try sqlTXplain or sql Health Check Oracle's tools to find the root cause of this problem.

Run it for the same query on both databases and compare parameters that you will get(sqlTXplain compare method).


You may need to reset the relay logs. PLEASE DON'T USE RESET SLAVE;

You need to reset the relay logs in such a way that it picks up from the last SQL statement it executed.

Please run the the following:

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='binlog.001403',MASTER_LOG_POS=32820355;
START SLAVE;

If Seconds_Behind_Master stays at zero, CONGRATULATIONS !!!

If Seconds_Behind_Master goes back to 7720 immediately, check the date and time set in the OS on the Master against the the date and time set in the OS on the Slave.

Give it a Try !!!


Query with limit and order by runs too slow

PostgreSQL thinks it cannot use the index defined on (tank_id, dpg DESC NULLS LAST) to satisfy this query without the sort.

If it is just DESC, that is fine.  Or if it was just on (tank_id, dpg), that too would work (it would scan the relevant part of the index backwards).

If you can't change the definition of the index, then making the query match the existing index would probably also work, so:

where tank_id=545 and battles >= 150
order by dpg desc nulls last
limit 50

(which is presumably what you would want anyway?)

To execute your query as originally written with the index you originally had, it would have to execute the query in two parts.  First it would have to visit the NULLs at the end of the tank_id=545 region (because ORDER BY...DESC without further qualification implicitly means NULLS FIRST), and then if it hadn't yet reached the LIMIT would have jump to the beginning of the tank_id=545 region to fulfill the ORDER BY DESC part.

So it would have to be the executor, not just the planner, which was involved.  It would take a lot of annoying code to make that happen, and no one was volunteered to write it.  (Also, it would probably be a rich source of hard-to-find bugs, so even if someone wrote the necessary code, it might not be accepted into the code-base)


Cannot create SSRS subscription for a report using SSAS as datasource

I have blogged about this exact issue. The workaround/solution involves expression-based connection strings which can be a bit painful. But I have prototyped a tool to automate the setup of those connection strings after deployment:

Using EffectiveUserName To Impersonate in SSAS


Best way to store physical stock data in a SQL db

One thing you can do is create a separate table to store the quality and the changes for your stock so you can limit the width of the table. For example you could have a table with the following columns.

Store
SKU
Current Stock
Change In Stock
Change Source (Sales/Restock/Other Change Sources)

When this table is updated it will only include records that had a quantity change so that items that have the same stock level will not update. While you will still get a lot of records the disk usage should be minimized.

Another possibility for performance is to use a partition in the table design so you can more quickly get to the data you need.


SQL Server : KEY lock prevents FK on nearby row, but not SELECT

Your simplified repro simplified the problem beyond the point it repro...

create TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY)

create TABLE dbo.Transfers (
    Id int NOT NULL IDENTITY PRIMARY KEY,
    CreatedByUserId int NOT NULL FOREIGN KEY REFERENCES dbo.Users (Id))

insert into Users default values;
go 5

insert into transfers (CreatedByUserId) values (1), (2), (3), (4);
go

begin tran
insert into Users default values;
insert into transfers (CreatedByUserId) values (6);

-- from separate connection
insert into transfers (CreatedByUserId) values (5);

Since the problem no longer reproes it means your simplified out a critical piece of information. Your symptoms look suspiciously similar to serializable isolation level issues. Perhaps you're using the harmful default TransactionScope constructor?


INSERT and DELETE in single operation (moving from one table to other)

Insert everything into a temp table first. Then your insert and delete are simply everything from the temp table rather than your complicated query. This solves your problem of how to do both halves and is probably going to perform better than running your complicated query multiple times.

I do more MS-SQL than My-SQL so I'll link to another answer on Stack Overflow.

https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table

On MS-SQL it is simply

  SELECT .... into #MyTempTable FROM ... WHERE..


is it possible to reset innodb_row_lock without restart MySQL 5.7

No.
The workaround:

SHOW GLOBAL VARIABLES LIKE 'Innodb_row_lock%';
Run your query/queries
SHOW GLOBAL VARIABLES LIKE 'Innodb_row_lock%';
Programmatically subtract the values.  (Well, perhaps only Innodb_row_lock_waits can really be subtracted.)

It may be possible to use information_schema.GLOBAL_STATUS, but (in some cases) you get hit by the 'Uncertainty principle'.
In my experience, these values are "typical":
Innodb_row_lock_current_waits  -- rarely more than 0
Innodb_row_lock_time  --  non interesting
Innodb_row_lock_time_avg  --  Usually 10-1000ms
Innodb_row_lock_time_max  --  Usually < 50K (ms)
Innodb_row_lock_waits  --  not useful, except in your case of "subtracting"
Where are you hoping to take this inquiry?
If you would like a review of your variables and status:  http://mysql.rjweb.org/doc.php/mysql_analysis#tuning


In-Memory Optimized Error: The code generation directory cannot be created or set up correctly

Is there a way to define where the XTP directory will be created in the CREATE TABLE syntax or elsewhere?

No, there is not. This always points to the default database path. If the path is removed, it will attempt to be created and have security applied to it a few times before returning an error.

This is true as of build: 13.0.4422.0 and older

If you feel that this should be changed to a configurable value - I'd invite you to create a request in Connect.


Table Space not being freed up after large DELETEs

There is a memory leak when dealing with heaps (no clustered index).  Multiple deletes/updates causes space to be "lost".  It can easily be fixed by either adding a clustered index or rebuilding the table.

ALTER TABLE tablename REBUILD

However in this case the table is stated as having a clustered index and the same problems don't apply.  Using Occam's Razor I'm going to guess that this is because of the varchar(max) column that was added.  Remember that when you add a varchar column very little space is added to each row until data is actually added to the column.  So in this case my guess is that as the varchar(max) column has been filled in the table size has grown.

Also remember that this table is a running year.  That means that when the column was added presumably it wasn't filled in for any of the old rows.  As the year goes by and old records are dropped off and new records come in a higher and higher percentage of the rows will have that column filled.  Not to mention the possibility of additional information being added over time.  (New comments, additional requirements when putting data in the column, whatever).  This means that the table will grow over time, but should stabilize somewhat.

A very easy way to check the size of a table is to use the system stored procedure sp_spaceused.

 -- Using msdb.dbo.backupset as an example
 exec sp_spaceused 'backupset'

name        rows        reserved    data    index_size  unused
backupset   14793       13072 KB    9112 KB 1640 KB     2320 KB

Note how we are given the number of rows, the total size of the table, how much is used for data and how much for the index.  And in this particular case most importantly how much is unused.

Every table is going to have some unused space, but if you start seeing that number climb then that would be a warning sign.  I've never seen it happen on a non-heap but of course I haven't seen everything either.


user mapping is droped after swithing from principle server to mirror server

The issue you're running into most likely has to do with your users becoming orphaned on failover. Even though you're using the same logins, the SIDs are different, and don't match up with the users in the database. Basically, you need to recreate the user exactly as it is on server A.

You need to do a CREATE LOGIN with SID = xxx that will create an exact copy of the login on server A. Syntax can be found here. I use sp_help_revlogin to do this; see this knowledgebase article for more information. This will give you a CREATE LOGIN script with the same SID and password to execute on server B.

If you move to SQL Server 2012, you'll have the option of using contained databases. Contained databases can store their own logins, meaning the instance doesn't need to have its own set and preventing orphaned users. These are especially good for databases that get moved around a lot and may have users changing somewhat frequently.


SQL Server linked server from on premises to SQL Server instance on Windows in Amazon EC2

When you're using EC2, don't think of it as "a special version of SQL Server" but rather "the same SQL Server I'm used to, running in a special data center." There are no feature restrictions in SQL Server--you're limited only by the infrastructure that you build in the AWS cloud/data center.

You can absolutely create linked servers from on-prem to EC2, and vice-versa. You just have the same challenges as any time you create linked servers across data centers.

Authentication

You mention that AWS & on-Prem are in different domains. This will require that you either have a trust between the domains, or that you use SQL Server authentication.

You've already said you're solving this with SQL Auth, and the configuration of the linked server & auth is a whole separate question, so I'll not dive into those details here.

Networking

Ultimately, your servers need to be able to talk to each other. It sounds like that isn't happening. You mentioned that the error you are seeing is "Login timeout expired." This is a somewhat generic error, but I'm guessing the more verbose version of it is this:

  Login timeout expired.

  A network-related or instance-specific error
  has occurred while establishing a connection to SQL Server. Server is
  not found or not accessible. Check if instance name is correct and if
  SQL Server is configured to allow remote connections.

This full error indicates that your client (in this case, your client is your on-prem instance that is establishing the connection to the AWS instance) cannot reach the server. Presumably, both SQL Instances are online, healthy, and accepting connections. The issue could be a problem resolving the server name, or it could be that a firewall is blocking the communication, or some other network issue.

You will need to establish a (secure!!!) network route between your on-prem server and your EC2 instance.

From your on-prem server, open a PowerShell prompt, and use the Test-NetConnection cmdlet to confirm if your server can communicate with the EC2 instance on the SQL Server port.

In this example, I'm testing a connection to the machine blep.am2.co which is running a SQL Server instance running on the default port 1433:

PS C:\> Test-NetConnection blep.am2.co -port 1433
WARNING: TCP connect to (192.168.86.100 : 1433) failed

ComputerName           : blep.am2.co
RemoteAddress          : 192.168.86.100
RemotePort             : 1433
InterfaceAlias         : Wi-Fi
SourceAddress          : 192.168.86.226
PingSucceeded          : True
PingReplyDetails (RTT) : 20 ms
TcpTestSucceeded       : False

The test shows that the name was correctly resolved to an IP, that a ping test was successful (PingSucceeded: True), but that it was unable to connect on the specified TCP port (TcpTestSucceeded: False).

A successful ping indicates that there is a successful route to the server (that's good!); a failed ping could indicate that the server is not accessible at all (it is also possible to disable ping responses, so a failed ping is valuable data, but depending on your network/security configuration may not be conclusive).

The TCP Test failure indicates that the specific connection on port 1433 is failing. The combination of a successful ping, and failure on port 1433 indicates that the most likely issue is a firewall blocking the connection. You would need to check all firewalls along the network path between servers to identify which one is blocking the traffic. At minimum, you would likely need to check:

Outgoing connections on the Windows firewall for your on-prem server
Outgoing connections on the firewall for your on-prem data center
Incoming connections on your AWS network
Incoming connections on the Windows firewall for your EC2 server

Once you resolve the networking connectivity, and the Test-NetConnection is successful on your SQL Server port, you will be able to successfully create your linked server.

⚠Warning⚠

If you aren't familiar with networking & network security, I would suggest proceeding carefully when making any configuration changes to allow your on-prem & AWS servers to communicate. Misconfiguring the connections could create a vulnerability where either you allow network connections from unauthorized sources, or that you transmit data unencrypted across the internet, where it could be intercepted.


How to check if Alter view/stored proc script has run successfully

Check sys.objects for the row that has the right value in name and see if the modify_date column is newer than when your script started.

SELECT SYSDATETIME() AS ScriptStart
INTO #scriptstart;
GO
ALTER VIEW ...
GO
ALTER PROC ...
GO
SELECT name
FROM sys.objects
WHERE modify_date > (SELECT ScriptStart FROM #scriptstart);

This will list all the objects that have changed since your script started. You could join to sys.schemas if you care enough... :)


Change postgres default behavior for connection to database when dbname not supplied

According to the same Postgres documentation

The following environment variables can be used to select default connection parameter values, which will be used by PQconnectdb, PQsetdbLogin and PQsetdb if no value is directly specified by the calling code[...]

PGDATABASE behaves the same as the dbname connection parameter.

If you want psql to fail if the database name is not explicitly given on the command line, set the variable PGDATABASE to point to a non-existent database, e.g. export PGDATABASE=" " or export PGDATABASE="".
As to your question:

whether or not the default behavior can be modified to not connect to a database named after the user if no database name is supplied either through an environment variable, or the psql command line utility.

we can find a definitive answer in the libpq source code:
/*
 * If database name was not given, default it to equal user name
 */
if (conn->dbName == NULL || conn->dbName[0] == '\0')
{
    if (conn->dbName)
        free(conn->dbName);
    conn->dbName = strdup(conn->pguser);
    if (!conn->dbName)
        goto oom_error;
}

The only way to modify the default behaviour is to edit the code fragment shown above and recompile the library.


Does mysqlhotcopy  block SELECT statements from different clients?

The documentation is not as clear as it should be, but I would say no, mysqlhotcopy does not block SELECT queries.
Here is why
REASON #1 : MySQL Docs on 'mysqlhotcopy'

mysqlhotcopy is a Perl script that was originally written and
contributed by Tim Bunce. It uses FLUSH TABLES, LOCK TABLES, and cp or
scp to make a database backup. It is a fast way to make a backup of
the database or single tables, but it can be run only on the same
machine where the database directories are located. mysqlhotcopy works
only for backing up MyISAM and ARCHIVE tables. It runs on Unix and
NetWare.
To use mysqlhotcopy, you must have read access to the files for the
tables that you are backing up, the SELECT privilege for those tables,
the RELOAD privilege (to be able to execute FLUSH TABLES), and the
LOCK TABLES privilege (to be able to lock the tables).

REASON #2 : MySQL Docs on 'Lock Tables' Restrictions

• If you are using tables for a nontransactional storage engine, you
must use LOCK TABLES if you want to ensure that no other session
modifies the tables between a SELECT and an UPDATE. The example shown
here requires LOCK TABLES to execute safely:  LOCK TABLES trans READ,
customer WRITE; SELECT SUM(value) FROM trans WHERE
customer_id=some_id; UPDATE customer   SET
total_value=sum_from_previous_statement   WHERE customer_id=some_id;
UNLOCK TABLES;   Without LOCK TABLES, it is possible that another
session might insert a new row in the trans table between execution of
the SELECT and UPDATE statements.

In summary, since mysqlhotcopy launches LOCK TABLES, SELECT queries are evidently still allowed.


Problem joining log-shipped secondaries in STANDBY to AlwaysOn group

When restoring WITH STANDBY SQL Server has to undo uncommitted transactions (the relevant data pages get written to the undo file that you specify). The NORECOVERY option does not care about the state of the log, or performing any potential rollbacks, that would happen if the database were recovered. Because the two leave the DB in different states you would have to reapply the final transaction log WITH NORECOVERY in order to be in a good state. There's an excellent write-up on this at https://askmesql.blogspot.com/2011/01/log-shipping-norecovery-vs-standby-mode.html

As for your monitoring software, I would recommend putting in a temporary exclusion for certain databases, or even instances while getting the AG setup so that you do not get those spurious alerts.


What is the performance impact of indexing a frequently updated column?

Surely READ UNCOMMITED is a recipe for disaster? It's not called "Dirty Read" for nothing!

Everything I've ever read about Oracle says so. In particular, Tom Kyte's "Oracle Database Architecture" which flat out states that Oracle refuses to provide that level and that is a good thing!

I saw the MySQL tag on this post, but this is universally applicable to databases in general IMHO.

Of course indexes slow down INSERTs and UPDATEs. Also, naturally, they will speed up SELECTs.

You can't know in advance performance will be affected for either case except by extensive experience and/or testing. What to do about this is a business decision, not a technical one.

Your question cannot be reasonably answered without far more information. Your UPDATE queries? Your SELECT queries? Your transaction rates? Your disk setup? How are your iostat results? Your entire sar statistics? Is your system humming or do you experience bottlenecks/slowdowns?

How is your budget? How much of a delay in reporting can the business tolerate? My first instinct in your case is to go for an offline data-warehousing solution with a 24 hour lag (DW built overnight - using some form of replication).

This is from my experience of how OLAP reports can cripple a running OLTP system. However, I cannot say for sure without far more information.


SQL Server Setup failure - Cached MSI files are missing

Did you find the tool that automates this ?
https://docs.microsoft.com/en-us/archive/blogs/psssql/sql-setup-toolsuite-introduction-1-fixmissingmsi


Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters

You can try the following calculation:

Openings:=
CALCULATE( SUM( 'Requisition Counts'[NumberofOpeningsQT] )
         , CALCULATETABLE( LASTNONBLANK( 'Requisition Counts'[SnapshotDateKEY] , 1 )
                         , ALL( 'Requisition Status' )
                         )
         )

The important part is in setting the correct filter context for the outer CALCULATE.

With CALCULATETABLE, we define the specific rows which we want the outer CALCULATE to consider: the last non-blank snapshot date for all requisitions (or for a specific requisition, considering the context of each requisition on a pivot table), regardless of any filters in the requisition status, which is achieved using the ALL function.

The measure doesn't work exactly as you want; you say that you would expect the total for Approved to be 2. Using the 2013-11059 requisition from the Power Pivot model as an example, since there is 1 Approved status in 2013-07-01 and another in 2013-07-02, it just considers the last status for the total, resulting in 1.


PostgreSQL transaction locked database table: "idle in transaction"

What you are seeing are transactions which are running, holding locks, and not doing anything.  What you need to do is trace the transactions back to their source and find out what they are doing.  They may be other admins who have logged in and forgot to log out.

In general it is usually a bad thing to hold transactions open and idle for a long time due to this sort of locking problem.


Demystification of SQL Server optimization process

I will try to reply to your questions:
1. It seems that the MEMO structure contains only final variants of the query plan or variants that were later rewritten into the final one. Is there a way to find "unsuccessful/unpromising" query plans?
No, sadly there is no way to do that. @Ronaldo pasted a nice link in the comment. My suggestion is to use the Include Live Query Statistics

and try to find out if you see different query plan. Use top 10, top 1000, or * and you will see that different query plans will be proposed. You can also use query hint and force your query plan to a different pattern. Basically "do your own discarded query plan"
2. The operators in MEMO do not contain a reference to SQL parts. For example, LogOp_Get operator does not contain a reference to a specific Table.
Use QUERYTRACEON 8605, I can see a reference to the table:

3. The transformation rules do not contain a precise reference to MEMO operators, therefore, we can not be sure which operators were transformed by the transformation rule
I don't see any GetToIdxScan - Get -> IdxScan in the query you provided. My suggestion is to use Use QUERYTRACEON 8605, or QUERYTRACEON 8606, there should be a reference there.
EDIT:
So "... is it possible to see more information about the candidate plans in SQL Server."
The reply is no, because there are no other candidate query plan. In fact, is a common misconception that SQL Server returns you The Best query plan. SQL Server simply cannot calculate for you all possible solutions: that would take... I don't know... minutes...? hours...? Calculating every single solution is unfeasible.
But if you want to investigate why your query plan choose that pattern you can use:

SET SHOWPLAN_ALL ON : and SQL Server will return you a tree of the logic of every single calculation of your query plan

DBCC SHOW_STATISTICS('A', 'PK_A') : which will show you the statistics about a target table and constraint. I created a key to show you the results, naturally you will see more info if your table is queried more often

USE HINT('force_legacy_cardinality_estimation') : will allow you to use the former cardinality estimation, so you can check if you query plan might have been faster with the legacy cardinality estimation .


Java-Hibernate-Oracle: Can a fluctuating DB connection duplicate insert statement?

An interrupted client connection can lead up to a scenario where the insert statement executes multiple times. It is possible for the client to miss the commit message. This scenario is documented in the manual: "A crash occurring after a successful write to the log can prevent the success message from returning to the client. In this case the client cannot tell whether or not the transaction committed." If the client is designed to retry in such a scenario, then the insert statement will run twice.

Oracle 12c feature "Transaction Guard" is designed to protect against such a scenario. Transaction Guard guarantees "At-Most-Once Execution".

http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_trans_idemp_guard.htm#ADFNS8000


What is the purpose of table partitioning

When to use many schemas?

In PostgreSQL databases of multi-tenant setup, it is not unusual to see a separate schema for every tenant.  This serves mostly to separate data of the different tenants, as schemas give a very convenient way of setting security/access boundaries.  This means, if I used such a setup, customer A would not have access to Customer B's data.  If necessary, it is easy to set up overarching roles that can work across the different schemas (doing maintenance, producing reports, and so on).

In these scenarios, one might have data that is common across all tenants.  I would store these in a common(ly accessible) schema, to prevent unnecessary duplications, thus making maintenance a bit easier.

Addressing one of your concerns, doing cross-schema queries is easy when you know which schemas to go for.  If not, it might be tricky.  However, maintenance in such cases might need some (self-built) infrastructure (as a script to roll out table changes on all tables named your_table one by one) - but when it's already in place, the single tasks are not so complicated.

Do schemas help with performance?

On the other hand, having 350+ schemas for 10M rows is definitely not about performance.  With 10M (not too wide) rows on a decent machine one would not expect serious performance issues in the first place.  If they still appear, there are other means than partitioning that one should try first (meaning different indexing strategies, usually).  A meaningful partitioning scheme for such data is very hard to come up with: 350+ partitions are just that much a pain in the ass as 350+ schemas.  Not to mention that the per-partition data volume will be small (like 30k rows), and in certain cases planning the queries would take a hit - especially, when you try to have multiple tables partitioned the same way.


Can I re-replicate a transactional publication? Should I?

Yes, replication can be setup as per requirement. kindly follow below steps to configure replication :

Configure transactional replication between primary and secondary server. Make sure initialization of objeccts are required to again setup replication.
kindly follow below article to setup replication again between secondary and reporting server.

https://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/
3. Make sure to add appropriate indexes on replicated objects to avoid latency. check procedure_stats for more detail.

I hope this would help to setup.


MYSQL - Using simple inventory number convention as Primary Key

You can use the one-column approach for simplicity.  However, what happens when a code is changed?  Will you have to update all related tables to have the new code?

Using an integer ID is the best choice for a primary key because it's small and efficient (strings have to be reduced/hashed to an integer before they are used as an index).  Also, updating a code will happen in only one place and be reflected in all related tables automatically.

So I would recommend your second option for the sake of speed and to avoid a cascading update.


Why do ASC and DESC come *after* a CASE clause?

Why do ASC and DESC come after a CASE clause?
Why doesn't the CASE statement contain the ASC or DESC (if you try to do that you get a syntax error)?

Because a CASE expression (it's neither a clause nor a statement) can have a value/expression as a result, not code words like ASC or FROM. The syntax provided in CASE expression documentation is:

Searched CASE expression:
CASE
     WHEN Boolean_expression
       THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

It might resolve to:

ROW_NUMBER() OVER (ORDER BY
    DESC,
    AggregateId ASC
) AS RowNumber

No, it cannot. Due to the implied ELSE NULL part that all CASE expressions have as a default, if you don't provide it.


DB2 - how to call a stored procedure that returns a result set in another user defined table function

What you want to accomplish is certainly possible by using a pipelined table function,
but it isn't necessarily a wise thing to do. For any non-trivial result set row-based
processing is always significantly slower than set-based processing. In addition to that,
by hiding your data access logic in a table function you deprive the query optimizer
of any information about the statistics and other physical characteristics of the underlying
tables and indexes. As a result, performance of such a chimera will lie anywhere  between
suboptimal and unacceptable.

Consider rethinking your approach; in most cases you should be able to implement your data retrieval and manipulation logic in a single query, may be wrapped in a view for convenience.

If you, however, insist on shooting yourself and those who come after you in your
collective feet, here's how you might go about doing that.

CREATE OR REPLACE PROCEDURE inner
LANGUAGE SQL
DYNAMIC RESULT SETS 1
READS SQL DATA -- (1)
BEGIN
DECLARE c1 CURSOR
WITH RETURN -- (2)
FOR SELECT tabschema, tabname FROM syscat.tables;
OPEN c1;
END

Footnotes:

The READS SQL DATA here is important; a table function cannot have side effects, and so
shouldn't the stored procedure it calls. We enforce this contract at compilation time.
Make sure Db2 knows we want data in the caller, otherwise it will optimize it away.

Then the function itself:

CREATE OR REPLACE FUNCTION smitf()
RETURNS TABLE (
    out_schema VARCHAR(128), out_name VARCHAR(128)
)
READS SQL DATA
BEGIN
DECLARE has_rows INT DEFAULT 1;
DECLARE schema_name VARCHAR(128);
DECLARE table_name VARCHAR(128);
DECLARE res1 RESULT_SET_LOCATOR VARYING;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET has_rows = 0;
CALL inner();
ASSOCIATE RESULT SET LOCATORS (res1) WITH PROCEDURE inner;
ALLOCATE cur1 CURSOR FOR RESULT SET res1;
FETCH cur1 INTO schema_name, table_name;
WHILE has_rows > 0 DO -- some bogus processing
    IF table_name NOT LIKE 'A%' THEN
        PIPE (LOWER(schema_name), LOWER(table_name)); -- magic is here
    END IF;
    FETCH cur1 INTO schema_name, table_name;
END WHILE;
RETURN;
END

and call it:

SELECT * FROM TABLE (smitf() CARDINALITY 100) WHERE ...

The CARDINALITY clause lets you tell the optimizer how many rows you expect the function to return, but it may not take this information into account, and even if it does, it may not help.


Linked server in SQL Server 2005 using NT authentication impersonation

If it was possible to connect to a SQL Server without setting up a security context on that server, that would be a serious security hole.  So, there must be a security context.

If you can ride on some existing rights that are already defined, then fine, but likely any existing security context was not tailored for your need. And since you want a specific account it is further complicated.

Perhaps you could have a SQL Agent job created on the RemoteServer that would periodically PUSH the data to a staging table on your LocalServer.  (This would also allow the RemoteServer administrators to control what happens, just in case that is an issue.)

Then you would grant rights on your LocalServer to allow the RemoteServer's job to insert data into the staging table on your LocalServer.

Note: When SQL Agent runs a job using a credential it is not impersonating the account, but should be viewed as logging in using the credential's account name and password.


In almost ALL cases, YES, use a view. Then you can have multiple joined tables, as may be required, whereto you would not have to give any access


What are the differences between Cognos and SPSS modeler products?

The differences between Cognos and Modeler are very large.  Cognos is mainly a summary and reporting tool.  It does not do any statistical analysis to speak of (except when you use the part of SPSS Statistics that is optionally integrated into one of the Studios).  Modeler is a full data mining tool with many modeling capabilities.  Modeler can read and write Cognos data.


What is the fastest way to clone/copy oracle databases on the same machine?

I prefer to use the RMAN clone method. The overall method is to start with the PROD database, and then a DEV database that is in a NOMOUNT state with all datafiles/controlfiles/tempfiles/archivelogs removed:

oracle> rman target sys@prod CATALOG rman/x@rman_catdb

RMAN> list backup;

  1    1399    53289602077 15-DEC-11 53289629964 15-DEC-11
  1    1400    53289629964 15-DEC-11 53289657925 15-DEC-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
88      Full    20.52M     DISK        00:00:00     15-DEC-11
        BP Key: 88   Status: AVAILABLE  Compressed: NO  Tag: TAG20111215T133438
        Piece Name: /u01/backups/prod/PROD_20111215_c-4198857860-20111215-02
  SPFILE Included: Modification time: 15-DEC-11
  SPFILE db_unique_name: PROD
  Control File Included: Ckp SCN: 53295424420   Ckp time: 15-DEC-11

target database Password:
connected to target database: PROD (DBID=4198857860)

RMAN> connect auxiliary /

connected to auxiliary database: DEV (not mounted)

RMAN> run {
2> set until scn 53289602077;
3> duplicate target database to dev;
4> }

This gives me a block-for-block recreation of the production level database, and depending on how many threads you give RMAN, can be completed pretty quickly.

From there, I have a set of environment specific scripts I run that change passwords, null out email addresses, etc in the DEV database to configure it the way it needs to be.


Why pad instead of trim when comparing strings?

It is no problem to trim the strings before comparing them.

The SQL standard does not describe the implementation, it describes the semantics of the comparison. It just finds it simpler to describe the behavior using padding.

You are free to implement the comparison any way you want as long as the behavior is identical.


I've created an SSIS package that connects to multiple servers and runs a query and copies that data to a central SQL server that I report against.   Just took a while to get the query to contain all the information I needed but the SSIS package is pretty straight forward, so may be an easier option than txt/xml


Sync & Async Synchronisation on SQL Server 2012

Is it possible to set up async synchronisation (high-performance) on MS SQL Server 2012 globally

Yes, with AlwaysOn Availability Groups having commit mode set to asynchronous commit for a replica.  That'll be by-database, so you'll have to group the databases into the AG that you want to see that happen with.

  but somehow force synchronous replication for one table between nodes

It really depends on your definition of "synchronous", but in the truest of definitions then no, you can't have synchronous commit for individual tables.  It'd have to be by database/availability group.


Are there any disadvantages to partitioning on financial year?

Answering your questions above:

In RANGE COLUMNS (datetime) key word COLUMNS is unnecessary. It is used to specify a list of columns, where as you have only 1 column. The difference exists: for RANGE (datetime_column) you can specify partition boundaries in datetime format, whereas for RANGE TO_DAYS(datetime_column) partition boundaries should be effectively in date format.
Partitioning on datetime is OK and it is very often.

Please refer to MySQL Manual, Chapter 18. Partitioning.

If the only reason for you using partitioning is structuring data, then may I suggest you looking at MERGE Storage Engine. It allows to create a table consisting of other tables. When queries to this overarching table have data only in some tables, then only those tables are used. It is easier to set up and maintain, than partitioning.


SSL connection to AWS RDS Postgresql instance from windows client

If you are using PGAdmin, when you create a new connection there is a tab called SSL[1]. There you can input your pem file.

If you are using psql, put your pemfile on %APPDATA%\postgresql\ directory. See more details at the documentation[2].

Hope it helps.

References:

https://www.pgadmin.org/docs/dev/connect.html?highlight=ssl
https://www.postgresql.org/docs/9.2/static/libpq-ssl.html


Locate the psql binary. (In a terminal, run locate psql | grep /bin, and make note of the path. (In my case, it's /opt/local/lib/postgresql90/bin/, as it was installed using MacPorts.)

Then, edit the .bash_profile file in your home folder (e.g. mate -w ~/.bash_profile assuming you've textmate), and add the needed line so it's in your path, e.g.:

export PATH=/opt/local/lib/postgresql90/bin/:$PATH

After having saved the file, read the file (. ~/.bash_profile) or open a new terminal, and type psql.


MySQL - maximum of sum over different months with ties over multiple years

My attempt to solve this is as follows. I would appreciate any advice on how this query could be improved:

SELECT
  t3.c_year AS "Year",
  t3.c_month AS "Month",
  t3.il_mc AS  "Tumour count",
  t4.ill_nat AS "Type" FROM
(
  SELECT c_year, c_month, il_mc FROM
  (
    SELECT
    c_year,
    c_month,
    MAX(month_count) AS il_mc
  FROM
    (
      SELECT nature_of_illness as illness,
        EXTRACT(YEAR  FROM created_at) AS c_year,
        EXTRACT(MONTH FROM created_at) AS c_month,
        COUNT(EXTRACT(MONTH FROM created_at)) AS month_count
      FROM illness
      GROUP BY illness, c_year, c_month
      ORDER BY c_year, c_month
    ) AS t1
  GROUP BY c_year, c_month
  ) AS t2
) AS t3
JOIN
(
SELECT
  EXTRACT(YEAR FROM created_at) AS t_year,
  EXTRACT(MONTH FROM created_at) AS t_month,
  nature_of_illness AS ill_nat,
  COUNT(nature_of_illness) AS ill_cnt
FROM illness
GROUP BY t_year, t_month, nature_of_illness
ORDER BY t_year, t_month, nature_of_illness
) AS t4
ON t3.c_year = t4.t_year
AND t3.c_month = t4.t_month
AND t3.il_mc = t4.ill_cnt

And it does give the correct result, as can be seen in the fiddle here!


SQL Server replication over internet error

So, I resolved the problem by creating an alias whith the (expected) name: SERVEUR\SERVER, protocol: TCP/IP, port: 1433, server: MyPublicIP\SERVER

sql replication over internet is working very well.


How would I model a product to have multiple image URLs and have only one as the default?

Yes, your idea can work. Add an is_primary boolean column to images:

ALTER TABLE images
ADD is_primary boolean NOT NULL;

and enforce the "No more than one primary picture per product" rule with a partial index:

CREATE UNIQUE INDEX images_primary_image_constraint
ON images (product_id)
WHERE is_primary;

The above index will ensure uniqueness of product_id among rows where is_primary is set. If you try to specify a second primary image for some product, the uniqueness will immediately be violated and the operation will fail.


How to avoid the SORT operation in SQL query that contains ORDER_BY condition?

ORDER BY is not that expensive by itself. The problem here is that it is used together with OFFSET. So postgresql does sorting of all data before it can take needed 10 rows.

You are probably implementing paging using ORDER BY + OFFSET. This is a known postgresql limitation and there is postgresql way to handle this.


Need Expert suggestion for choosing best DB scalability

Will you be INSERTing one row per click?  Or just incrementing a counter?

INSERTing or UPDATEing as many as 100K rows in one second requires some special planning.  First, plan on gathering a bunch of rows, then batching the insert or update; do not try to insert/update one click at a time.  (More on this in a minute.)

Build and maintain "summary tables" so that the queries will not have such a significant impact the system.  (More to come.)

I would suggest a Galera Cluster (MariaDB 10 / PXC / Mysql+Galera) to give you some scaling, HA, etc.  Since you have a large read requirement, you might need multiple slaves hanging off each cluster node.

Backups could be handled by taking a slave offline.  (There are other options; I don't see it as much of a problem.)

You have not explained the 85% reads enough for me to finish this discussion; I will focus on the writes and some of the reads.

Use SSDs of sufficient size.  Have sufficient network capacity.

Have enough client machines -- possibly more than MySQL servers; the number depends on several things.  The design should easily grow by adding clients, so I am not too worried about how many clients until the system is being prototyped.

The best high-speed ingestion I know about is here .  It allows multiple clients to insert into a temp table, ping-pong to another table while doing the INSERTs (or UPDATEs).  If all you are doing is counting hits, this design takes a load off the main table because it would do some of the tallying in the tmp table.  Also, you would probably use INSERT ... ON DUPLICATE KEY UPDATE ... SELECT ... to move the data from the temp to real table.  (Caveat:  The blog was written with a single Master in mind; I need to do some research to tailor it to Galera, especially if this is happening on all 3 nodes.  Initial thoughts suggest that only minor tweaks are needed.)

Normalization can, and should, be done from the tmp table.  (The blog discusses that.)

With Summary table(s), the 85% estimate for reads may drop significantly.  The idea is to create and maintain tables that are subtotals over an hour or day.  Then the queries actually hit the summary tables and run a lot faster than against the real table.  (Sometimes 10x faster.)  The summary table(s) may be denormalized for extra convenience/speed.  The Summary table updates are likely to be IODKU from the tmp table.

I need to see more details about the schema and the tentative inserts/updates and selects.  This entire discussion will probably expand beyond what can/should be handled in a free forum.

Reference: Summary table blog


Best way to store a list of numbers where individual numbers can be deleted

look up put with conditional expression attribute_not_exists so you will only put the item if it does not exist.  delete single item only by parturition key and range key, not all of them. also the primary key combination must be unique anyways so you can't have duplicates.


Access ('07 -'10) form is not showing entered records

Try setting it to continuous forms instead of single.

Also, if it's set to single form you should be able to navigate thorough all the records, one at time, with the navigation buttons in the lower left of the form.


How and where do I upload a mysql database?

If you have downloaded the database as an SQL file then you can just import it using the command prompt and the mysql command.

Do it in these simple steps:

Make sure the SQL file is unpacked as a text file with SQL commands inside
Verify you're able to login to MySQL using the command prompt. You can simply type # mysql -p -u username database_name. You'll be asked for password and, if correct, you'll be accepted to your database.
If you haven't created any new database to unpack your backup file in you can do it now (inside the MySQL command prompt) by typing create database MyNewDatabase;
Now that you know you can login to the database you can "feed" the downloaded SQL file by changing the command in this way: # mysql -p -u username database_name < /tmp/download/db_backup.sql. Make sure you're not inside the MySQL command prompt but out in the normal command prompt when typing this command. You can simply type exit or quit to leave the MySQL command prompt.
The database will then be imported into your local database. If it's a big database running this last command might take quite some time.

Some things to think of:

If you have a new installation and haven't added any users to MySQL then root is the username to use. But it's always recommended to add a separate user account in MySQL that you can use from scripts ect.
In my examples above I specified a database name. But if your backup script starts with use someotherdb; then someotherdb will be used instead of the database name you specify in the mysql command. To get around this, either open up the SQL backup file and remove the first line with use, or, when you create your backup, untick the box saying Include using (or something similar).
This is just how to install the database. You talked about "web site" also and that's a different thing. However, you need to update your website scripts to point to the new local database that you've created now.


want to find out which databases are used in last 30 days or not

Unfortunately, the INFORMATION_SCHEMA and SHOW STATUS are of no value for InnoDB tables.

I am also sure you don't have time to gore through and interpret servers logs.

Here is the fastest way: Check the timestamp of the most recently accessed file inside every database.

All you need is the most recent file written in each of the 120 databases. This is true for both MyISAM and InnoDB.

I wrote a crazy post back on Dec 21, 2011 (  Fastest way to check if InnoDB table has changed) to check the timestamp of any individual InnoDB table.

You should write a bash script to cd into every folder under /var/lib/mysql, find the most recent file, and check the timestamp. Just subtract the most recent timestamp from UNIX_TIMESTAMP(NOW()).

If the difference is less than 2592000 (That's the number of seconds in 30 days), that database has been updated in the last 30 days.
If the difference is greater than 2592000, that database is over 30 days old.

Here is that script

#!/bin/bash

DATADIR=/var/lib/mysql

AGE_DAYS=30
(( AGE_SECS = AGE_DAYS * 86400 ))

RIGHTNOW=`date +%s`

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"

SQL="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DB_CSV=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
DBLIST=`echo "${DB_CSV}" | sed 's/,/ /g'`

OLDDBLIST=""
SPC=""
for DB in `echo "${DBLIST}"`
do
    DBFOLDER=${DATADIR}/${DB}
    cd ${DBFOLDER}
    FILECOUNT=`ls -lt *.[fiM][rbY][mdDI] | wc -l`
    if [ ${FILECOUNT} -gt 0 ]
    then
        FILENAME=`ls -lt *.[fiM][rbY][mdDI] | head -1 | awk '{print $9}'`
        FILETIME=`ls -l --time-style="+%s" ${FILENAME} | awk '{print $6}'`
        (( TIMEDIFF = RIGHTNOW - FILETIME ))
        if [ ${AGE_SECS} -gt ${TIMEDIFF} ]
        then
            OLDDBLIST="${OLDDBLIST}${SPC}${DB}"
            SPC=" "
        fi
    fi
done

echo ${OLDDBLIST}

CAVEAT : @Jynus wrote a post about using the audit_plugin to determine that info.

If you are using Percona Server, check this forum.


Looking for SQL Server 2000 DTS Components

Fair warning, this is tricky.  I get it working about 9/10 times these days.

FYI First install the 2005 backward compatibility components, then the DTS components.

I believe both are located here: https://www.microsoft.com/en-us/download/details.aspx?id=24793 but no guarantees.


This is called as MySQL Master-Master replication.
You can follow these links which can be helpful to you:

1. How to setup Master-Master replication

2. How to setup Master-Master replication

Another way to implement multi-master replication, including circular replication is using MySQL Cluster.

3. MySQL Cluster replication - Multi Master


DBeaver Error 1146 (42S02) on SQL database restore- Table 'acumatica.AUNotification' doesn't exist

The create table may not have used quotes around the table name, while a reference to it does.

Unless lower_case_table_names=1 is set upon database creation, the case of the tablenames has to be respected when quoted.


PostgreSQL: Valid variable names for functions

This is implicitly stated in the introduction to PL/pgSQL.

  Identifiers are implicitly converted to lower case unless double-quoted, just as they are in ordinary SQL commands.

So a PL/pgSQL variable has to comply with the rules for SQL identifiers


Since you quoted SQL Developer in your question -

View > DBA

Add your connection.

Go to Storage, Tablespaces.

Right-click, 'Create New'

Fill in the details - and I agree with Gerard Pile's comment to your question - you don't need to worry about what you're worrying about. Just pick a directory, file name, and intial file size, and how you want it to grow, with an optional max file size.

You could theoretically override the database's DB_BLOCK_SIZE on the 'Properties' page, but that's generally not necessary.

If you want to see the SQL that's generated by the dialog, click on the 'DDL' page of the popup, otherwise, just click 'Ok.'


Broken NTFS Permissions for MSSQL$SQLEXPRESS

This account is a service SID.

The full name is NT SERVICE\MSSQL$SQLEXPRESS.


Duplicate data in a table given table name

I like to use rownum. Using a CTE to get the current maximum PK value, you can easily double-up your data.

Example below.

Test table:

create table yourperftest
( pk number,
  col1 number,
  col2 number,
  col3 number
);

Test data:

insert into yourperftest values (1,1,1,1);
insert into yourperftest values (2,2,2,2);
insert into yourperftest values (3,3,3,3);
insert into yourperftest values (4,4,4,4);
insert into yourperftest values (5,5,5,5);

Query to "double up" the table:

insert into yourperftest
with maxvalue as (
  select max(pk) as maxvalue
  from yourperftest
)
select rownum+maxvalue, col1, col2, col3
from yourperftest,maxvalue;

I'd probably use an /*+APPEND*/ hint too, plus make sure you gather stats on the table afterwards.

Having said that, stats play a big part in testing - you want to make sure that your data/performance test environment is as close to production as possible. Going forward, the stats are the key to this. Stats that are different between test and production will cause different query plans to be picked by the optimiser, which may make your performance testing invalid when production is going tits-up.

EDIT:

Since you ask about doing this for more than 1 column, here's another example:

 insert into yourperftest
with maxvalue as (
  select max(pk) as maxvalue
  from yourperftest
), col1tab as (
   select max(col1) as col1maxvalue
   from yourperftest
)
select rownum+maxvalue, col1maxvalue+rownum, col2, col3
from yourperftest,maxvalue,col1tab ;

CTEs are brilliant.


InnoDB for MySQL 5.1 exhibits single-threaded behavior because the number of read and write I/O threads are fixed at 4 each. I was just reminded that versions of MySQL before 5.1.38 has only one thread for read and one for write. Therefore, scaling up hardware will not improve performance at all.

You need to upgrade to MySQL 5.5/5.6 because there are settings that will enable InnoDB to engage multiple CPUs and multiple cores (such as innodb_read_io_threads and innodb_write_io_threads).

Sep 20, 2012 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?
May 26, 2011 : About single threaded versus multithreaded databases performance

CAVEAT : You must tune MySQL 5.5/5.6 properly because left unconfigured MySQL 5.1 is faster than 5.5/5.6 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

Give it a Try !!!


Row Compression in AlwaysOn Availability Groups

Compression is not listed as a restriction in the documentation at Prerequisites, Restrictions, and Recommendations for Always On availability groups, so that is the official reference that indicates it is supported with availability groups.  There are no special considerations for configuration, and it does not matter whether synchronous or asynchronous replication is used.

Row and page level compression will have no direct impact on availability groups as far as functionality is concerned, but there will be a small increase in CPU utilization on the primary server to compress the data when written, and uncompress when read, so it's possible that extra resource usage could impact availability group replication as it also requires CPU.  In general, though, if your system has adequate CPU this will be of no concern.  Additionally, if the secondary is used for reading, additional CPU will be used to uncompress the data.  Again, the CPU required to do this is quite minimal and will only be of concern if there are other factors causing the CPU utilization to be excessively high.

The replication process is simply copying transactions from the transaction log to the secondary servers (to oversimplify it a bit), so it really doesn't know or care about what data is actually in that transaction.  When the log record gets to the secondary, it is placed in the transaction log there and committed to the database just as if the transaction had originated locally.

If the replication process had to understand the contents of every transaction that is replicated, whether the data is compressed, encrypted, or whatever, it would be far slower.


PostgreSQL "at time zone" construct does not use index

I found a way to "cheat" which uses the index, and also has the comfort of timezone conversion.

I basically extend the date range of selection to be sure to include enough values whatever timezone the user has, and then use a wrapper select which filters the result a second time and performs the timezone conversion:

select * from (
    select
    date_trunc('minute', datetime at time zone 'UTC' at time zone 'Europe/Luxembourg') as datetime,
    round(avg(last_value), 1) as last_value
    from impulse2
    where account = 1
    and sensor = 2
    and datetime between '2016-09-07 00:00' and '2016-09-09 23:59' -- uses index
    group by 1
    order by 1
) as r
where
r.datetime at time zone 'UTC' at time zone 'Europe/Luxembourg' between '2016-09-08 00:00:00' and '2016-09-08 00:20:00'


How to recover data from corrupted SQL Server database?

I’d try the procedure described below or third party tool such as ApexSQL Recover if that doesn’t work and database is severely corrupted.

There might be other options out there. This is something that worked for me once but there are no guarantees that it will work at all times.

Stop SQL Server instance -> Copy MDF and LDF files to another location -> Delete original MDF and LDF files -> Start SQL Server instance again -> Create new database with exact same name and file names -> Stop SQL Server -> overwrite newly created MDF and LDF.

After this your database should be back online. If it is then go ahead and put it into EMERGANCY mode and SINGLE USER mode.

Finally go ahead and execute DBCC CHECKDB like this

DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

If you can get to this and execute last command successfully you should be good. If not then your only options are restoring from backup and/or using 3rd party tools.


Query runs slowly when a non-indexed column is added to the WHERE clause

You're getting a secondary predicate on the key lookup operation. That's going to slow stuff down. It's not doing a full scan, but remember that even seeks are just limited scans. So the key lookup is still doing what it did before, but now it's also adding a filtering step because of that added index.

You could look at exploring a covering index, however, because you have a SELECT *, covering means all columns, effectively creating a second clustered index. There'll be some sacrifice of performance on data modification queries and on disk space (depending on where you are on Azure SQL Database, that could bump you to another tier).

Instead, I'd start with experimenting with your existing nonclustered index. See if adding this new column to the key of that index helps at all. Keep it as the second column, other wise you're changing your histogram which could cause other issues.

You're going to have to experiment to see for sure what works best in your situation.


Hostname configuration for oracle12c on OL7 VM

Simply add the following to /etc/hosts with your actual IP address:

192.168.0.215   OracleLinux7VM


How can I find and clean up unused database files?

There are two PowerShell functions and a PowerShell script will help automate the process of finding unused SQL database files and alerting you to their presence so that you can clean them up.

First Invoke-SQL function:

Save the code for the Invoke-SQL function that I have posted as an answer to "How do you run a SQL Server query from PowerShell?" into a file named Invoke-SQL.ps1.

Second Get-SQLDatabaseFilesNotUsed function:

Save the following function into a file named Get-SQLDatabaseFilesNotUsed.ps1.

function Get-SQLDatabaseFilesNotUsed {
    param (
        [parameter(Mandatory = $true)][string]$SQLServerName,
        [switch]$IncludeEqual
    )

        $SQLCommand = @"
set nocount on
SELECT DB_NAME([database_id])AS [Database Name],
        [file_id], name, physical_name, type_desc, state_desc,
        CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
"@

    $Results = Invoke-Sql -datasource $SQLServerName -database master -SQLCommand $SQLCommand
    $SQLDatabaseFilesInUse = $Results | select @{Name="fullname";Expression={$_.physical_Name -replace "\\\\","\" <#SQL server allows \\ in paths and just uses it like \#> }}

    $SQLDatabaseAndLogFilesOnDisk = invoke-command -ComputerName $SQLServerName -ScriptBlock {
        $Filesystems = get-psdrive -PSProvider FileSystem
        Foreach ($FileSystem in $Filesystems) {
            Get-ChildItem $FileSystem.Root -Recurse -include *.mdf,*.ldf,*.ndf -File -ErrorAction SilentlyContinue | select fullname
        }
    }

    $SQLDatabaseAndLogFilesOnDisk = $SQLDatabaseAndLogFilesOnDisk |
    Where fullname -notlike "C:\windows\winsxs*" |
    Where fullname -notlike "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\*" |
    select fullname

    Compare-Object -ReferenceObject $SQLDatabaseFilesInUse -DifferenceObject $SQLDatabaseAndLogFilesOnDisk -Property FullName -IncludeEqual:$IncludeEqual | FT -AutoSize

}

One thing to note, if you have additional database files that you want to keep on disk and you want to exclude them from this check just add another Where fullname –notlike “<Path where you have files you don’t want checked>” | to the others you see above.

Third Test-SQLDatabaseFilesNotUsed script:

Save the following code into a Test-SQLDatabaseFilesNotUsed.ps1 script file.

. c:\scripts\Invoke-SQL.ps1
. c:\scripts\Get-SQLDatabaseFilesNotUsed.ps1

$OutputMessage = ""

$FromAddress = "scheduledtasks@YourDomain.com"
$ToAddress = "DBA@YourDomain.com"
$Subject = "SQL Server has data or log files that are not being used"
$SMTPServer = "MailServer.YourDomain.com"

$OutputMessage = Get-SQLDatabaseFilesNotUsed -SQLServerName <YourSQLServerNameGoesHere>

if ($OutputMessage)
{
    Send-MailMessage -From $FromAddress -to $ToAddress -subject $Subject -SmtpServer $SMTPServer -Body ($OutputMessage | FT -autosize | out-string -Width 200)
}

Be sure to substitute the name of your SQL server for  in the code above.

Now save all of these in a C:\scripts directory and create a scheduled task with whatever schedule you like (I have ours run once per week) that executes:

Powershell.exe -noprofile -file c:\scripts\Test-SQLDatabaseFilesNotUsed.ps1

This has saved me +100GB already and from this point forward you won’t have to worry about having space used up by database files from databases removed from SQL server long ago.


There is no feature to do this but it can be done with a slightly hack method of making a sleeping connection and killing it when the event occurs.

The client submits a long running sleep query:

SELECT /* WAITING_FOR_EVENT */ SLEEP(9999);

The client will now sit waiting for the query to return.

Now to signal the event, you just kill the thread. This could be set off by another client, a trigger, a scheduled event, etc.

SELECT * FROM information_schema.processlist WHERE `INFO` LIKE '%WAITING_FOR_EVENT%';

This gives the process id that is running the sleep query. Then simply kill it with the KILL command.

The client will see it was interupted and now knows the event has been triggered.

You can have multiple listeners using this method but cannot pass any data back to the listening client, only signal an event has occurred.


Why does SQL Server need permissions to access data and log files?

The SQL Server service account needs to have read/write access to the folders where the data and log files exist. Because it needs to read and write.

By default the service account has access only to SQL Server's own folder structure. This has nothing to do with what access you have.

Your options are:

Grant explicit read/write access to your desired folder/tree
Change the service account to be you
Put the database files in their proper location for that instance

I prefer the latter so that you don't have active database files scattered all over your machine.


SELECT t1.name,
       COALESCE(t2.tag, t3.tag) tag,
       t4.something
FROM tableA t1
LEFT JOIN tableB t2 ON t1.id = t2.TableA_Id
JOIN tableB t3 ON t3.TableA_Id = 0
JOIN tableC t4 ON t1.id = t4.TableA_Id

TableB must contain only one row with TableB.TableA_Id = 0. If not then use
SELECT t1.name,
       COALESCE(t2.tag, t3.tag) tag,
       t4.something
FROM tableA t1
LEFT JOIN tableB t2 ON t1.id = t2.TableA_Id
CROSS JOIN ( SELECT *
             FROM tableB
             WHERE TableA_Id = 0
             -- ORDER BY something, for example, by `Id`
             LIMIT 1 ) t3
JOIN tableC t4 ON t1.id = t4.TableA_Id

fiddle


First, some comment on the existing query:

Every expression that is not in the GROUP BY list should not appear in the SELECT list, unless it is inside an aggregate function (SUM(), COUNT(), etc). So that SELECT task should probably be changed to SELECT DATE(datetask) AS date_of_task.
It's not good to use BETWEEN .. AND .. for ranges of datetime and timestamp columns. The condition WHERE datetask BETWEEN '2015-10-01' AND '2015-10-31' will seemingly gather all rows of this October. Except that almost all the rows of the 31st of October will escape!

The reason is that the condition is equivalent to WHERE datetask BETWEEN '2015-10-01 00:00:00' AND '2015-10-31 00:00:00' and all rows with datetime values that are not in midnight exactly (00:00:00) will fail the condition and will not be counted in the query.

Even if you use something like WHERE datetask BETWEEN '2015-10-01 00:00:00' AND '2015-10-31 23:59:59', you have a few drawbacks: It's more complicated to write. It will work but only if the precision of the dtateime/timestamp is 1 second. If the column has millisecond or microsecond precision (this is allowed in 5.6+ versions), you'll have to change the condition to have ... AND '2015-10-31 23:59:59.999999'.

My suggestion is to use the WHERE column >=? AND column < (? + INTERVAL 1 DAY) pattern. It's guaranteed to work on all DATE, DATETIME and TIMESTAMP types of arbitrary precision.

Now for the multiple counts, there are several methods. Perhaps the easiest to write is:

SELECT DATE(datetask) AS date_of_task,
       COUNT(CASE WHEN status = 1 THEN 1 END) AS status_1,
       COUNT(CASE WHEN status = 2 THEN 1 END) AS status_2,
       COUNT(CASE WHEN status = 3 THEN 1 END) AS status_3
FROM tasks
WHERE datetask >= ? AND datetask < (? + INTERVAL 1 DAY)
  AND taskstatus = ?
GROUP BY DATE(datetask)
ORDER BY date_of_task ASC ;

which  can be simplified a bit, to:

SELECT DATE(datetask) AS date_of_task,
       SUM(status = 1) AS status_1,
       SUM(status = 2) AS status_2,
       SUM(status = 3) AS status_3
FROM tasks
WHERE datetask >= ? AND datetask < (? + INTERVAL 1 DAY)
  AND taskstatus = ?
GROUP BY DATE(datetask) ASC ;    -- combine GROUP BY and ORDER BY


Select rows where smaller row does not exist

Oracle supports LAG so you should be able to do something like.

SELECT T.ID,
       T.NAME
FROM
(
SELECT ID,
       NAME,
       LAG(ID) OVER (ORDER BY ID) AS PREVID
FROM YourTable
) T
WHERE ID > PREVID + 1;

To compare the Id with the previous one and only return ones with a gap greater than 1.

(Untested as SQL Fiddle is currently returning errors when I try and create an Oracle Fiddle)


SQL Server - Must be first statement in query batch - what and why?

From http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

  Rules for Using Batches

  The following rules apply to using batches:

  CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE
  SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined
  with other statements in a batch. The CREATE statement must start the
  batch. All other statements that follow in that batch will be
  interpreted as part of the definition of the first CREATE statement.
  A table cannot be changed and then the new columns referenced in the
  same batch.
  If an EXECUTE statement is the first statement in a batch,
  the EXECUTE keyword is not required. The EXECUTE keyword is required
  if the EXECUTE statement is not the first statement in the batch.


SQL> create table t1 (c1 number);

Table created.

SQL> insert into t1 values(1);

1 row created.

SQL> insert into t1 values(0);

1 row created.

SQL> commit;

Commit complete.

SQL> select decode(c1, 1, 'TRUE', 0, 'FALSE') from t1;

DECOD
-----
TRUE
FALSE


Store both geometry and geography or store one and calculate the other?

To elaborate a little more, the database will be populated with pre-existing data that contains both British National Grid (EPSG:27700) (OSGR) and converted WGS84 (EPSG:4326) values, and there will be a regular update of that data from external sources. This would argue for two columns, because I don't need to do the calculation.

You can always reproject with ST_Transform (though you may have minor rounding errors). I would convert and store in WGS84 explicitly and reproject to EPSG:27700 when you need those advantages. The geography features are really useful, and it makes life massively simpler to have them.

Not to mention, you can indexes to cover that conversion too if you frequently query on it.


how to use a different tmp dir for INTO OUTFILE select with MySQL?

Assuming you have the FILE privilege, you should be able to specify a location other than the /tmp location. For example:

SELECT .. INTO OUTFILE '/home/foo/myfile.csv'

The other option, if you have access to the command line, is to use the -e switch:

> mysql -e "SELECT * FROM foo ORDER BY bar" > '/path/to/file'


Query to match exact column or partial data of the column: SQL Server / Azure

As @MarkSinkinson suggested you probably want to bite the bullet and move to the geography datatype.  Sometimes a little short-term pain will save you a lifetime of misery.

Options 1 and 3 clearly can't work for the reasons you mentioned.
Option 2 doesn't work either unless you want a search for India to return results for Indiana too.
Option 4 shows the most promise assuming from1, from2, from3 -> city, region, country, and your input search terms follow the same pattern (city, region, country).  If they must be a random list search terms then you may run into problems assigning weights for locations like Lima (city), Lima (region/province), Peru (country) because Lima matches two categories.

As I suggested option 4 will probably be the most fruitful, given the caveats I listed for option 4 and the recommendation to abandon text matches in favor of the geography type. The following query isn't sexy but it should be close to your original requirements.

declare @city nvarchar(50) = N'Bangalore', @region nvarchar(50) = N'Karnataka', @country nvarchar(50) = N'India';

;with [City_CTE] AS
(
    select
        <table PK> -- Unique match guaranteed
        , 1 as [present] -- Convenient summing later via the isnull function
    from <tablename>
    where
        [city] = @city
)
,  [Region_CTE] AS
(
    select
        <table PK>
        , 1 as [present]
    from <tablename>
    where
        [region] = @region
)
, [Country_CTE] AS
(
    select
        <table PK>
        , 1 as [present]
    from <tablename>
    where
        [Country] = @country
)
select
    *  -- Select the columns you need
    , isnull([ci].[present], 0) + isnull([re].[present], 0) + isnull([co].[present], 0) as [MatchWeight]
from <tablename> [t]
    left join [City_CTE] [ci] on [ci].<table PK> = [t].<table PK>
    left join [Region_CTE] [re] on [re].<table PK> = [t].<table PK>
    left join [Country_CTE] [co] on [co].<table PK> = [t].<table PK>
order by
    [MatchWeight] desc


Understanding the differences in the storage space used by two tables

Based on the information given, one explanation is that the size of the data being stored in the nvarchar(max) column used to be much larger than it is now.

~12 million historical rows averaging 3.88 KB per row
~24 million live rows averaging 0.23 KB per row

You can double check this by using the dynamic management function sys.dm_db_index_physical_stats on each of the two tables, specifically looking at the "avg_record_size_in_bytes" column.  Something like this:

SELECT
    DB_NAME(ips.database_id),
    OBJECT_NAME(ips.[object_id]),
    ips.index_id,
    ips.index_type_desc,
    ips.index_level,
    ips.alloc_unit_type_desc,
    ips.page_count,
    ips.record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
    DB_ID(N'YourDatabaseName'),
    OBJECT_ID(N'CIC_DESLUNGHE'),
    NULL,
    NULL ,
    'DETAILED'
) ips;

And

SELECT
    DB_NAME(ips.database_id),
    OBJECT_NAME(ips.[object_id]),
    ips.index_id,
    ips.index_type_desc,
    ips.index_level,
    ips.alloc_unit_type_desc,
    ips.page_count,
    ips.record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
    DB_ID(N'YourDatabaseName'),
    OBJECT_ID(N'CIC_DESLUNGHE_STORICI'),
    NULL,
    NULL ,
    'DETAILED'
) ips;

Compare the results of the two queries and see if there are any surprising discrepancies.

I left other filters off the function call because this will also show nonclustered indexes.  You didn't mention how you arrived at the numbers in your question, but if they include NC indexes on the tables, perhaps those are contributing to the size difference.


COALESCE can take a whole list of values to check for NULL where ISNULL takes one check value and would require nesting to check more values.

SELECT
COALESCE(col1, col2, col3, col4, col5, 0) AS value_returned
FROM myTable

vs

SELECT
ISNULL(col1, ISNULL(col2, ISNULL(col3, ISNULL(col4, 0)))) AS value_returned
FROM myTable

Since your case is using float values, there should not be a difference in results from calculations.  The only benefit I can really see if code readability and minimizing the amount of nesting needed.