Update same column, same DB with specific records

That wiuld only work when you have fixed rules
Like Doe and Doe_Old, so you can link the records.
IF you can't find a rule for the ON clauseit us impossible and you have to go over every record
Edit: it goes actually easier

CREATE tABLE  Staff (Role_Id  INT, Staff_First_Name VARCHAR(10), Staff_Last_Name VARCHAR(10));
INSERT INTO  Staff VALUES (10, 'Jane' , 'Doe_Old');

INSERT INTO  Staff VALUES (NULL, 'Jane', 'Doe')
GO

SELECT * FROM Staff;
GO

Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old
   null | Jane             | Doe

Update s1
Set Role_Id = s2.Role_Id
FROM Staff s1
JOIN  Staff s2 ON s1.Staff_First_Name = s2.Staff_First_Name
                        AND s2.Staff_Last_Name = CONCAT(s1.Staff_Last_Name,'_Old')
GO

1 rows affected

SELECT * FROM Staff;
GO

Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old
     10 | Jane             | Doe

db<>fiddle here
This Version is only necessary if you need some calculation made in th subquery

CREATE tABLE  Staff (Role_Id  INT, Staff_First_Name VARCHAR(10), Staff_Last_Name VARCHAR(10));
INSERT INTO  Staff VALUES (10, 'Jane' , 'Doe_Old');

INSERT INTO  Staff VALUES (NULL, 'Jane', 'Doe')
GO

SELECT * FROM Staff;
GO

Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old
   null | Jane             | Doe

Update Staff
Set Role_Id = s2.Role_Id
FROM Staff s1
JOIN
(SELECT Role_Id,Staff_First_Name,Staff_Last_Name
        FROM Staff) s2 ON s1.Staff_First_Name = s2.Staff_First_Name
                        AND s2.Staff_Last_Name = CONCAT(s1.Staff_Last_Name,'_Old')
GO

SELECT * FROM Staff;
GO

Role_Id | Staff_First_Name | Staff_Last_Name
------: | :--------------- | :--------------
     10 | Jane             | Doe_Old
     10 | Jane             | Doe

db<>fiddle here


How to log logon logoff and user activity on mySQL server for audit?

If you are willing to upgrade to  MySQL 5.5, there is a way to store the general log as a MyISAM table. I have written about this before in past articles:

Feb 24, 2011 : Audit logins on MySQL database
Jun 30, 2011 : How do I output MySQL logs to syslog?
Feb 11, 2012 : MySQL general log

Since you are using MySQL 5.0.91, you cannot configure the general log to be stored as a MyISAM table. You can only do so as a text file. Nevertheless, there have been other contributors in the DBA StackExchange that have suggested very good ideas for the text file version of the general log in your quest to audit DB Logins for MySQL 5.0. These suggestions may be more what you are looking for:

Aug 26, 2011 : How do I output MySQL logs to syslog? from @JoelHanger
Nov 16, 2011 : Log MySQL DB changing queries and users from @DTest


I'd second Jonathan's comments. There are many resources (to show the DBAs)showing that automated "missing indexes" analysis is very limited and often misleading.

In addition to sp_blitzindex, if you get them to run the following query against each database (after a representative uptime), you can get an idea of which tables are the cause of possibly unnecessary IO due to high number of scans (considering the row counts) and lookups. And you can start to consider the indexes suggested just for those tables, and/or find the offending queries and consider indexing for them.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    , OBJECT_NAME(p.object_id) AS [Table]
    , i.name AS [Index]
    , i.type_desc AS [Index Type]
    --, p.partition_number
    , p.rows AS [Row Count],
       USER_SEEKS,
       USER_SCANS,
       USER_LOOKUPS,
       USER_UPDATES
FROM sys.partitions p
INNER JOIN sys.indexes i ON p.object_id = i.object_id
                         AND p.index_id = i.index_id
left join (select * from SYS.DM_DB_INDEX_USAGE_STATS  where DATABASE_ID = DB_ID(DB_NAME())) b ON b.object_id = i.object_id and b.index_id = i.index_id
WHERE OBJECT_SCHEMA_NAME(p.object_id) != 'sys'


According to MySQL Docs, you can alter a procedure using ALTER PROCEDURE, unless you need to change the procedure's parameters or it body.

  This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE.


SQL Server Login to access tables of three different database from single database

Database ownership chaining will achieve what you are looking for as long as your SP and the tables in the destination DB are owned by users of the same name.

Just alter all participating databases so they are allowed to do ownership chaining:

ALTER DATABASE database1 SET DB_CHAINING ON
ALTER DATABASE database2 SET DB_CHAINING ON
ALTER DATABASE database3 SET DB_CHAINING ON

Additionally, you have to grant the logins executing the SP the right to access the destination database:

USE database2;
EXEC sp_grantdbaccess 'loginname'
USE database3;
EXEC sp_grantdbaccess 'loginname'

Please read about the security implications this has:
http://technet.microsoft.com/en-us/library/ms188676(v=sql.105).aspx

In short, don't use ownership chaining when you can't trust the db_owner of the source database.

For a good in-detail explanation including examples see http://www.mssqltips.com/sqlservertip/1782/understanding-cross-database-ownership-chaining-in-sql-server/


Moving an ibd file temporarily to free up disk space

It would make more sense to me to take a backup, remove the table instead of moving it, perform your optimize and then restore the table you dropped. This would avoid moving tables around unnecessarily and leaving your database in an incoherent state. You would also want to prevent incoming connections from the outside by putting bind-address = 127.0.0.1 in my.cnf and then restarting mysql.

Backup your whole database first with mysqldump

mysqldump
--single-transaction \
--routines \
-u root -p nameofdatabase | gzip -9 > nameofdatabase.gz

--single-transaction is the safest way to backup innodb

--routines backsup stored procedures.

gzip just compresses the backup nicely, you can open it backup with zcat.

1: Backup the table in question

mysqldump
--single-transaction \
-u root -p nameofdatabase nameoftable | gzip -9 > nameoftable.gz

2: Make sure the backup you created of the table has your data with zcat

zcat nameoftable.gz | less

Drop the table

mysql> drop table nameoftable;

3: Perform your maintenance with optimize

4: Restore the table

zcat nameoftable.gz | mysql -u root -p nameofdatabase


How to increase ETL performance in Informatica for Netezza as a source and SQL Server as a target?

My experience with Informatica accessing both SQL Server and Netezza can be summed up to this:

Reading is equally fast (100,000 rows/sec; sometimes twice that speed), provided that the select SQL is simple enough, that is:

no joins;
no group by;
no sort;
where clauses only against the cluster key of the SQL Server table.

In all other cases Netezza will beat SQL Server.
Inserts with PowerCenter are of the array type, and SQL Server can usually receive 2000 to 4000 rows per second.

Inserts into Netezza run at 80,000 to 200,000 rows per second if PowerCenter is not the bottleneck.
Updates/deletes with PowerCenter have been notoriously slow on all databases for years, since the SQL executed is singletons and not an array. Therefore, a session typically drops to ~500 rows per second with SQL Server as a target, and a mere 8 rows per second with Netezza.

Due to that, we have defined staging tables per PowerCenter target in Netezza and write all updates/deletes to those tables while the session executes. These changes are then applied in bulk as a target post SQL.
This scales well since all write operations now run at similar speeds to insert.

The best solution for the notorious writer bottleneck with SQL Server as a target is to spend a lot of time inside PowerCenter comparing source with target and only write whatever difference you detect. That will carry you a long way but not scale.

My final question is, why move data from Netezza to SQLserver? If your business has requirements that truly cannot be met on Netezza, please specify which, perhaps they could be addressed.


sys.dm_sql_referenced_entities fails to provide used columns list when joining with temp table

This is a bug in SQL Server and here is a Connect item to vote for if you want a change.

dm_sql_referenced_entities does not shows columnes when temporary tables are used in statement

Current status:

  We believe it is unlikely that we will address this suggestion, and so
  we are closing it as “won’t fix”.


Return all rows from table1 and corresponding rows from table2 even if null

You only need to move the WHERE conditions (that involve table2) to the joining, ON condition:

SELECT
   tb1.id,
   tb1.model,
   tb1.location,
   tb2.counter
FROM
   table1 tb1 LEFT JOIN table2 tb2
   ON (  tb1.id = tb2.printerid
     AND tb2.year = 2017
     AND tb2.month = 1
      ) ;

The ON condition can be as complex as required, it doesn't necessarily be a simple equality, although that's the most common (the parentheses around the condition are not required either, just to make the code a bit more clear).

It's unclear how you get Printer, Pages, Year, Month columns in the result when you have id, model, location, counter in the SELECT list.


The vendor does offer a forum and a knowledge base of sorts for the tool.  Plus, there is also the manual.  I don't really understand what you mean by 'everyday database admin concepts' with respect to a modelling tool, though.  Could you elaborate on that?

However, asking specific questions on data modelling tools is certainly on-topic for DBA.SE.  If you get to 'How do I do xxx in Erwin?' type of questions then it's fairly likely someone here is familiar with Erwin and can answer the questions.


Can some redundancy in this nested SELECT COUNT query be eliminated?

At least the inner countings are not necessary. You can easily use an EXISTS query, in negative:

SELECT name, continent, population
FROM world x
WHERE NOT EXISTS (
  SELECT 1
  FROM world y
  WHERE y.population > 25000000
  AND x.continent = y.continent
)
ORDER BY continent, population DESC

This is more readable also since it literally says "give me the continents, except if there is a country where population is larger than the limit" and the person reading it doesn't have to spend time wondering what the counts are. Also the DB engine doesn't have to do any counting.

Another way would be to ask for the continents which have smaller countries and exclude those:

SELECT name, continent, population
FROM world x
WHERE continent NOT IN (
  SELECT DISTINCT continent
  FROM world y
  WHERE y.population > 25000000
)
ORDER BY continent, population DESC

This may have better performance since it needs to only once find the continents that don't match and then run the main query as opposed to the previous one checking continents separately.


How to create a normalized database to manage inventory for a small business?

Its a good start.

You probably want to split out the customer table: One for the businesses and one for the people.
On tblCarpets and tblOtherProducts, what about a keyword field? You could do some nifty stuff in Java with that.
Each table could benefit from a create_timestamp, modify_timestamp and, if you've got a way to track users (a tblUsers), created_by and modified_by.
Hide_ind - you might want to hide information from the front end, but keep it on the back end for reporting purposes.
You can get rid of 'tbl' in SQL. Its like saying ATM Machine - the M stands for Machine.
A discounts table, maybe? How would you track price changes?
Do they have their own trucks? How many staff? Do you want to track who sent what where, and how?

A few updates from the comments - I'd create a few tables like this:

create table items (itemID int, Type varchar(255), item_name varchar(255), keyword nvarchar(1000), starting_length int,
create_timestamp datetime, modify_timestamp datetime)

create table prices (item_id int, price float, create_timestamp datetime, modify_timestamp datetime)

create table inventory (itemID int, current_length int, create_timestamp datetime, modify_timestamp datetime)

and for a keyword search, you might (might, i don't know java) use a stored procedure like this:

create procedure fetch_items_by_keyword (@keyword varchar(255))

as

set @keyword = '%' + isnull(@keyword,'') + '%'

select item_id,item_name, type, keyword
from items
where keyword like @keyword

I'd let everything stew in your brain for a while. sleep on it and start making your changes tomorrow.


How to search Query Store for a specific word or command in a query?

As sp_BlitzErik mentioned in a comment, and as I gathered from my own research, you can find some things in sys.query_store_query_text but not necessarily always a SHRINK.

This query returns 22,497 results:

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%Select%'

This query does not return any, except the second time I run it :)

SELECT  qsqt.*
FROM    sys.query_store_query_text AS qsqt
WHERE qsqt.query_sql_text LIKE '%SHRINK%'

The foundation of the query above comes from a SQL with Bert post.

A coworker has shown me a query that finds the shrink and the time and login of who did it.  So I have what I need for the my specific issue, but that is something of a side step from my question.

My workaround uses the default query trace. It shows multiple recent DBCC Shrinkdatabase ('MyDB', TRUNCATEONLY) that are run against the master database, which does not have query store.

A DBCC SHRINKFILE run in the context of a database with query store enabled does get recorded in the query store.


Technically instance resides in memory and database (physical files) resides on disk. There can be an instance without a database (an example is a started instance right before you create a database). There also can be a database without an instance (example of this is database files residing on disk but instance is not running, or files copied to some other location, something along those lines). Typically you use database by connecting your client to it's instance in order to do some work on data contained within. So an instance, upon startup, associates itself with one or more databases (Firebird, for example - one server process can be used to connect to multiple databases).

Second question: Only in some High-Availability environment, namely RAC on Oracle, where multiple instances are clustered to service the same database. This is transparent to end-users, so practically no, it can't. If you're talking about non-clustered configurations then no - no two instances can use same set of database files, therefore no application can use more than one instance connected to the same set of database files. At least not in RDBMS I know about. However an application can, theoretically, use two instances servicing two distinct copies of the same database, why not. Only I can't see real-world reason to do so.

Third question: already answered. No database can be serviced by two instances unless it's some kind of HA environment.

EDIT about object types

This is what resulted from a query of select distinct object_type from dba_objects to my Ora 10g, it surprised even myself:

CONSUMER GROUP, INDEX PARTITION, SEQUENCE, QUEUE, SCHEDULE, TABLE PARTITION, RULE, PROCEDURE, OPERATOR, LOB PARTITION, WINDOW, LOB, PACKAGE, PACKAGE BODY, LIBRARY, RULE SET,
PROGRAM, TYPE BODY, CONTEXT, TRIGGER, JOB CLASS, UNDEFINED, DIRECTORY, MATERIALIZED VIEW, TABLE, INDEX, SYNONYM, VIEW, FUNCTION, WINDOW GROUP, CLUSTER, TYPE, RESOURCE PLAN, EVALUATION CONTEXT, JOB


How can I get individual rowcounts like SSMS?

The SqlCommand.StatementCompleted event will fire after each statement in a batch, and one of the properties of the event (well, pretty much the only property) is the number of rows affected by the statement that fired the event.

Some notes:

A requirement of getting this info is that you did not specify SET NOCOUNT ON;, or conversely, you did specify SET NOCOUNT OFF;.
All events fire at the completion of each Execute___(), not during the execution.
The StatementCompletedEventArgs.RecordCount includes row counts from SELECT statements, whereas the SqlDataReader.RecordsAffected property only reports row counts from DML statements (INSERT, UPDATE, DELETE, etc).
The StatementCompleted event does not include the individual SQL statement from the batch that fired the event. However, the event handler is sent the sender as an input parameter and this is the SqlCommand of the query batch, and you can see that batch by casting sender to SqlCommand and then looking at the CommandText property (this is shown in the example below).

The documentation is very sparse on this so I have worked up an example that shows this event firing for both ExecuteNonQuery and ExecuteScalar, as well as for both ad hoc queries and stored procedures (i.e. SqlCommand.CommandType of Text vs StoredProcedure):

using System;
using System.Data;
using System.Data.SqlClient;

namespace StatementCompletedFiring
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection _Connection =
                          new SqlConnection("Integrated Security = True;"))
            {
                using (SqlCommand _Command = new SqlCommand(@"
SET NOCOUNT OFF; --  ensures that the 'StatementCompleted' event fires

EXEC('
CREATE PROCEDURE #TestProc
AS
SELECT * FROM sys.objects;

SELECT * FROM sys.tables;
');

SELECT * FROM sys.objects;
", _Connection))
                {

                    _Command.StatementCompleted += _Command_StatementCompleted;

                    try
                    {
                        _Connection.Open();

                        _Command.ExecuteNonQuery();

                        _Command.CommandText = @"
SELECT 123 AS [Bob];

WAITFOR DELAY '00:00:05.000'; --5 second pause to shows when the events fire

SELECT 2 AS [Sally]
UNION ALL
SELECT 5;
";
                        Console.WriteLine("\n\t");
                        Console.WriteLine(_Command.ExecuteScalar().ToString());
                        Console.WriteLine("\n");

                        _Command.CommandType = CommandType.StoredProcedure;
                        _Command.CommandText = "#TestProc";
                        _Command.ExecuteNonQuery();
                    }
                    catch (Exception _Exception)
                    {
                        throw new Exception(_Exception.Message);
                    }
                }
            }
        }

        static void _Command_StatementCompleted(object sender,
                                                StatementCompletedEventArgs e)
        {
            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("\nQuery Batch: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(((SqlCommand)sender).CommandText);

            Console.ForegroundColor = ConsoleColor.Red;
            Console.Write("Row(s) affected: ");
            Console.ForegroundColor = ConsoleColor.White;
            Console.WriteLine(e.RecordCount.ToString() + "\n");

            Console.ResetColor();
        }
    }
}

OUTPUT:

  Query Batch:
  SET NOCOUNT OFF; --  ensures that the
  'StatementCompleted' event fires

  EXEC(' CREATE PROCEDURE #TestProc AS SELECT * FROM sys.objects;

  SELECT * FROM sys.tables; ');

  SELECT * FROM sys.objects;

  Row(s) affected: 453

  Query Batch:
  SELECT 123 AS [Bob];

  WAITFOR DELAY '00:00:05.000'; --5 second pause

  SELECT 2 AS [Sally] UNION ALL SELECT 5;

  Row(s) affected: 1

  Query Batch:
  SELECT 123 AS [Bob];

  WAITFOR DELAY '00:00:05.000'; --5 second pause

  SELECT 2 AS [Sally] UNION ALL SELECT 5;

  Row(s) affected: 2

  123

  Query Batch: #TestProc
  Row(s) affected: 453

  Query Batch: #TestProc
  Row(s) affected: 17


GRANT permissions to all tables, views, procedures in SQL Server 2000

Ugh, 2000.

Assuming all objects are owned by dbo, you can generate a script, e.g.:

SELECT N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type IN (N'U', N'V');

SELECT N'GRANT EXECUTE ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'P';

Now you can copy and paste the output in case you want to exclude any entries or validate. If you just want to blindly execute, you can instead do this, but it relies on the content of any individual set of commands being < 4K:

DECLARE @sql NVARCHAR(4000) = N'';

SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'U';

EXEC sp_executesql @sql;

SET @sql = N'';

SELECT @sql = @sql + N'GRANT INSERT, UPDATE, DELETE, SELECT ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'V';

EXEC sp_executesql @sql;

SET @sql = N'';

SELECT @sql = @sql + N'GRANT EXECUTE ON dbo.' + QUOTENAME(name)
  + N' TO [user];'
FROM sysobjects WHERE type = N'P';

EXEC sp_executesql @sql;


What are the names of SQL Server database statuses 24, 1024 and 65544?

The others are combinations of the documented flags, e.g. 24 is 16 and 8.

This is a method for simulating optional parameters and is used in e.g. C iirc, the numbers 1, 2, 4, 8 and so on are structured like that because they correspond to binary values that combined in any unique way create a unique number. The function that accepts them then use bitmasking to extract each individual number. E.g. if you send the value 6 to a function, then we know that this is a combination of 4 and 2, to find this out using a bit mask we would do:

    0110 // 6
AND 0010 // 2
 == 0010

    0110 // 6
AND 0100 // 4
 == 0100

Both statements would be evaluated to true, thereby we have found out the individual values.

Note: In the real world, we would iterate through all values from 1, 2, 4, 8, 16 and so on to the maximum value of an integer (or whatever datatype the parameter was set to).


2 TB sounds too much for mysqldump. Did you try to restore a database from the dump? How much time does it take?

For large databases I would suggest XtraBackup. Not only it's faster, especially when it comes to restoring but it also supports incremental backups.

Weekly full copies sounds reasonable. For daily backups you have two options:

Incremental copy containing changes since last day.
Incremental copy containing changes since last full copy (sometimes this type is called differential backups). I prefer this one because of smaller restore time and higher chance to successfully restore the database.

If you need to store 13 month of data then you'd probably need to store 13 full copies. In that case storage requirements would be similar.

What you can do is to take full copy at month zero and then take a differential copy every moth. Then assuming 2TB initial size and 6% monthly growth the total storage size would be ~4TB.

Month   Database size, TB   Delta, TB
1   1.00    0.00
2   1.06    0.06
3   1.12    0.06
4   1.19    0.07
5   1.26    0.07
6   1.34    0.08
7   1.42    0.08
8   1.50    0.09
9   1.59    0.09
10  1.69    0.10
11  1.79    0.10
12  1.90    0.11
13  2.01    0.11
    Total delta, TB 1.01


You don't need the recursive CTE, just use generate_series():

You also need to use an outer join in order for the days without inspection to show up as zero:

with days as (
  select day::date
  from generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) day
)
select d.day as date_column,
       count(i.reporting_id) as cnt
from days d
  left join inspection i on i.date = d.day
group by d.day

generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) generates one row for each date between '2013-12-30' and '2013-12-30' + 365. For details on generate_series() see the manual: http://www.postgresql.org/docs/current/static/functions-srf.html

The ::date notation  is just a shorthand for cast(.. as date)


Howto restore Trigger filesystem copy (*.TRG and *.TRN files) in addition to mysqldump?

You shouldn't have to copy .TRN and .TRG files.

Just make sure when you run mysqldump to sue the --triggers option. It is already set by default. You also need the TRIGGER privilege in order to dump triggers.


How to generates VALUES literal expression using a query?

Are you looking for this:
WITH foobar(foo, bar) AS (
  VALUES
  (1::integer,'a'::text),
  (2,'b'),
  (3,'c'),
  (4,'d')
)
SELECT format('(%s, %L),', foo, bar) FROM foobar;

  format
-----------
 (1, 'a'),
 (2, 'b'),
 (3, 'c'),
 (4, 'd'),
(4 rows)


How to find resource intensive SQL operations in Postgresql? High CPU usage spike

The problem was with the indexing of few tables. I was able to identify those tables with Read stats by performing the following query.

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads from pg_stat_all_tables WHERE idx_tup_fetch + seq_tup_read != 0 order by TotalReads desc LIMIT 10;


Heavy performance drop on Progress query, looks like based on string character inside table

It isn't quite clear to me but, as I read the question, you are saying that you have two distinct versions of the code. One with that phrase, another without.
I expect that in the version with that bit of code it is slow because there is no index where field1, field2, and field3 are in consecutive order. In a case like that you will have a table-scan to resolve the query.
To illustrate using the standard sports2000 database:
define variable dbx as int64 no-undo.

find _myconnection no-lock.
find _connect no-lock where _connect-id = _myconn-userid + 1.
find _userio  no-lock where _userio-id = _myconn-userid + 1.

display _connect-name _connect-type _connect-device.

dbx =  _userio-dbaccess.

find first order no-lock
  where order.orderStatus = "shipped"
    and order.shipDate    >= 2/10/1997
//  and can-find( employee where employee.lastName = "parker" )
    and can-find( employee where employee.firstName = "karen" )
  no-error.

find _userio  no-lock where _userio-id = _myconn-userid + 1.
dbx =  _userio-dbaccess - dbx.

display orderNum dbx.

When the CAN-FIND() references employee.lastName (commented out above) you will see that "dbx" = 4. That is to say that your query accessed a total of 4 db blocks (2 index entries and 2 data records). When you run it with employee.firstName it takes 114 db accesses because, while firstName is an indexed field, it is the 2nd component of a compound index and thus cannot be used to bracket the query. Obviously in the tiny sports2000 database this has very little impact on runtime. But the 25x difference in db access operations shows the table-scan in action and hints at the impact that would be felt in a real database.
In OpenEdge 4gl (aka "ABL") queries are optimized at compile time by following rules. There are many rules but the "golden rule" is to have equality matches on leading components of composite indexes. There is a lot of information on the topic shared in the various user groups. A good repository of past presentations can be found at PUG Challenge. The various "pick an index" talks are a good place to get started.
(The OpenEdge SQL engine uses a cost-based optimizer - but we know that this is a 4GL query since the FIND FIRST and CAN-FIND syntax is being used.)
You can see which indexes are being selected by compiling your code with the XREF option. If you see WHOLE-INDEX in that output that is telling you that a table scan is required. (If you do not see that it is not a sign that your query is efficient - just that you didn't trigger that particular warning.)
You can also profile activity at run time using the virtual system tables or by using a tool such as ProTop (if you go with ProTop you probably want to investigate "programmer mode"). Either approach will reveal which indexes are actually being used and how much activity there is. If you carefully control your test environment you can learn quite a lot.


Ideal environment and cost for running a medium size MongoDb server

There really is no "ideal" environment and cost for running MongoDB (or any other database for that matter).  There will be very cheap solutions that will give you enough space, but not enough RAM, there will be middle range options where you have enough RAM most of the time but at busier periods you exceed the memory limitations and the disk is too slow to cope with the increased page fault activity.

As always, it will be a trade of between what you can afford and what is best. In terms of general recommendations:

The cores and space will matter less than your available RAM and whether or not you can keep your working data set (active data plus indexes) in RAM - that is the key to performance.  You won't really be able to tell until you start with real traffic, but if you have decent testing you should be able to estimate it.

I would recommend using MMS to track the stats, it's free and it includes a memory graph that will track your resident memory usage and many other things.

FYI - there is no global read lock and as of 2.2 (release candidate is up for testing as of writing this), the global write lock has been replaced by a database level lock.  Have a look at the relevant concurrency presentations for an in-depth discussion on the 10gen website.

Another thing to make sure of is that you have more than one MongoDB instance, it is highly recommended that you run a replica set (primary, secondary, arbiter minimum) and not a single instance.

Sharding can be used to help scale out horizontally though - that is correct, it allows you to add more resources to your cluster without having to increase the resources available on a particular host.  However, it is not really correct to say that MongoDB runs "best" when sharded - sharding has overheads (you need more servers to run the config DB, mongos processes etc.).

MongoDB runs best when your working set can fit in RAM and your disk subsystems are fast enough to keep up with the amount of data you want to write to disk.  Whether than requires a single replica set or a sharded environment will very much depend on how you use it.


Putting the flag directly in Pets is still normalized. It might not be high-performance, though.

Pulling the five rows into a separate store will undoubedly perform better. Is it normalized? Well, there are a great many normal forms, one of which puts each non-key attribute in its own table. So yes, its normalized. If you put non-key columns in the new table then you risk update anomolies, which means that table is no longer normalized. I'd wear that cost for the simplified run-time.

What other alternatives exist? An index involving the flag column may help at the cost of extra work for all writes. A filtered index or materialised view would be better, if only MySQL supported them.


Replicated MySQL write performance query

This depends on the query and the binary log format.

An UPDATE query that fetches lots of rows and changes very few will in binlog_format=ROW end up as UPDATE table SET x=y WHERE PK=z so will have a lower CPU/IO overhead. If it was using binlog_format=STATEMENT exactly the same query would end up on the slave.

Conversely a UPDATE query that updates a large number of rows, UPDATE table SET status=Fixed WHERE date < NOW() will generate a large number on binary log entries (for each primary key) and might be slower in binlog_format=ROW than statement.

There's a good document on this in the mysql manual

Based on your galera tag, and the known limitations, you'll be using binlog_format=ROW and each table will have a primary key. Take note regarding transaction size so to avoid stalls due to large updates.


Inhibit Sql Server Log Shipping Alerts during certain times

One approach is to use sp_help_log_shipping_alert_job to find the log shipping alert job ID and then use sp_update_job to suspend the job during maintenance. Enable it back when maintenance is done. I don't know whether the job would not immediately send an alert upon being enabled back though.


Double range query on one column uses wrong seek predicate

Running
dbcc traceon (3604,8606)

And then the query with a #temp table the final tree before cost based optimisation looks as follows (with the NE/ <> predicate first and the LT/ < predicate second) - the deviation of the predicate order from the "Input tree" happens from the "Simplified Tree" stage and stays that way in subsequent trees.
*** Tree After Project Normalization ***
        LogOp_Project

            LogOp_Select

                LogOp_Get TBL: #test #test TableID=-1583795211 TableReferenceID=0 IsRow: COL: IsBaseRow1001

                ScaOp_Logical x_lopAnd

                    ScaOp_Comp x_cmpNe

                        ScaOp_Identifier QCOL: [#test].status

                        ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=2)

                    ScaOp_Comp x_cmpLt

                        ScaOp_Identifier QCOL: [#test].status

                        ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=10)

            AncOp_PrjList

                AncOp_PrjEl COL: Expr1003

                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)

With a permanent table the tree looks as follows.
*** Tree After Project Normalization ***
        LogOp_Project

            LogOp_Select

                LogOp_Get TBL: test test TableID=645577338 TableReferenceID=0 IsRow: COL: IsBaseRow1001

                ScaOp_Logical x_lopAnd

                    ScaOp_Comp x_cmpLt

                        ScaOp_Identifier QCOL: [tempdb].[dbo].[test].status

                        ScaOp_Identifier COL: ConstExpr1004

                    ScaOp_Comp x_cmpNe

                        ScaOp_Identifier QCOL: [tempdb].[dbo].[test].status

                        ScaOp_Identifier COL: ConstExpr1005

            AncOp_PrjList

                AncOp_PrjEl COL: Expr1003

                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)

The second case is quite fragile. Adding a query hint - such as OPTION (RECOMPILE) it reverts to the first case. It seems to retain the initial order of the predicates, as written in the query, in the cases that simple parametrisation is attempted (this is not attempted for the temp table #test).

select 1 from test where  status <> 2 and status < 10 gives the "bad" plan again with the seek on <> 2 and residual on < 10

My supposition is that the index matching code just picks the first seekable predicate available in input tree order in the rare case that there are multiple competing seek predicates for the same column.
Specifically the SelPredNorm transformation rule appears to cause the predicate re-ordering. The following gives the desired plan.
select 1
from #test
where status < 10
and status <> 2
option (queryruleoff SelPredNorm);

As for why this does not kick in (in this case) with auto parameterised queries that question is still not addressed.


Performing wildcard search using the json_contains function in mysql 5.7 and above

You can use JSON_SEARCH with wildcards:

SET @j = '{"MyName": "FirstName LastName"}';
SELECT JSON_SEARCH(@j, 'all', 'FirstName%', NULL, '$.MyName');

Will return the path: "$.MyName"

If you want to filter by this, use an alias and check it in a HAVING clause:

SELECT JSON_SEARCH(`json_col`, 'all', 'FirstName%', NULL, '$.MyName') as `search`
FROM `users`
HAVING `search` IS NOT NULL;

or.. in a WHERE:

SELECT *
FROM `users`
WHERE JSON_SEARCH(`json_col`, 'all', 'FirstName%', NULL, '$.MyName') IS NOT NULL;

https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html


Why full-text-search returns less rows than LIKE

I took the three strings in your question and added it to a table plus three more string with pankt instead of punkt.

The following was executed using MySQL 5.5.12 for Windows

mysql> CREATE TABLE artikel
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     meldungstext MEDIUMTEXT,
    ->     PRIMARY KEY (id),
    ->     FULLTEXT (meldungstext)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO artikel (meldungstext) VALUES
    -> ('Punkten'),('Zwei-Punkte-Vorsprung'),('Treffpunkt'),
    -> ('Pankten'),('Zwei-Pankte-Vorsprung'),('Treffpankt');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

I ran these queries against the table using 3 different approaches

MATCH ... AGAINST
LOCATE as in the LOCATE function
LIKE

Please note the differences

mysql> SELECT id,meldungstext,
    -> COUNT(IF(MATCH (`meldungstext`) AGAINST ('*punkt*' IN BOOLEAN MODE),1,0)) PunktMatch,
    -> IF(LOCATE('punkt',meldungstext)>0,1,0) PunktLocate,
    -> meldungstext  LIKE '%punkt%' PunktLike
    -> FROM `artikel` GROUP BY id,meldungstext;
+----+-----------------------+------------+-------------+-----------+
| id | meldungstext          | PunktMatch | PunktLocate | PunktLike |
+----+-----------------------+------------+-------------+-----------+
|  1 | Punkten               |          1 |           1 |         1 |
|  2 | Zwei-Punkte-Vorsprung |          1 |           1 |         1 |
|  3 | Treffpunkt            |          1 |           1 |         1 |
|  4 | Pankten               |          1 |           0 |         0 |
|  5 | Zwei-Pankte-Vorsprung |          1 |           0 |         0 |
|  6 | Treffpankt            |          1 |           0 |         0 |
+----+-----------------------+------------+-------------+-----------+
6 rows in set (0.01 sec)

mysql>

All the PunktMatch values should bee 3 1's and 3 0's.

Now watch me query them as normal

mysql> SELECT `meldungstext` FROM `artikel`
    -> WHERE MATCH (`meldungstext`) AGAINST ('*punkt*' IN BOOLEAN MODE);
+-----------------------+
| meldungstext          |
+-----------------------+
| Zwei-Punkte-Vorsprung |
| Punkten               |
+-----------------------+
2 rows in set (0.01 sec)

mysql> SELECT `meldungstext` FROM `artikel`
    -> WHERE LOCATE('punkt',meldungstext)>0;
+-----------------------+
| meldungstext          |
+-----------------------+
| Punkten               |
| Zwei-Punkte-Vorsprung |
| Treffpunkt            |
+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT `meldungstext` FROM `artikel`
    -> WHERE `meldungstext` LIKE '%punk%';
+-----------------------+
| meldungstext          |
+-----------------------+
| Punkten               |
| Zwei-Punkte-Vorsprung |
| Treffpunkt            |
+-----------------------+
3 rows in set (0.00 sec)

mysql>

OK using MATCH .. AGAINST with punkt does not work. What about pankt ???

mysql> SELECT `meldungstext` FROM `artikel` WHERE `meldungstext` LIKE '%pankt%';
+-----------------------+
| meldungstext          |
+-----------------------+
| Pankten               |
| Zwei-Pankte-Vorsprung |
| Treffpankt            |
+-----------------------+
3 rows in set (0.00 sec)

mysql>

Let's run my big GROUP BY query against pankt

mysql> SELECT id,meldungstext,
    -> COUNT(IF(MATCH (`meldungstext`) AGAINST ('*pankt*' IN BOOLEAN MODE),1,0)) PanktMatch,
    -> IF(LOCATE('pankt',meldungstext)>0,1,0) PanktLocate,
    -> meldungstext  LIKE '%pankt%' PanktLike
    -> FROM `artikel` GROUP BY id,meldungstext;
+----+-----------------------+------------+-------------+-----------+
| id | meldungstext          | PanktMatch | PanktLocate | PanktLike |
+----+-----------------------+------------+-------------+-----------+
|  1 | Punkten               |          1 |           0 |         0 |
|  2 | Zwei-Punkte-Vorsprung |          1 |           0 |         0 |
|  3 | Treffpunkt            |          1 |           0 |         0 |
|  4 | Pankten               |          1 |           1 |         1 |
|  5 | Zwei-Pankte-Vorsprung |          1 |           1 |         1 |
|  6 | Treffpankt            |          1 |           1 |         1 |
+----+-----------------------+------------+-------------+-----------+
6 rows in set (0.01 sec)

mysql>

This is wrong also because I should see 3 0's and 3 1's for PanktMatch.

I tried something else

mysql> SELECT id,meldungstext, MATCH (`meldungstext`) AGAINST ('+*pankt*' IN BOOLEAN MODE) PanktMatch, IF(LOCATE('pankt',meldungstext)>0,1,0) PanktLocate, meldungstext  LIKE '%pankt%' PanktLike FROM `artikel` GROUP BY id,meldungstext;
+----+-----------------------+------------+-------------+-----------+
| id | meldungstext          | PanktMatch | PanktLocate | PanktLike |
+----+-----------------------+------------+-------------+-----------+
|  1 | Punkten               |          0 |           0 |         0 |
|  2 | Zwei-Punkte-Vorsprung |          0 |           0 |         0 |
|  3 | Treffpunkt            |          0 |           0 |         0 |
|  4 | Pankten               |          1 |           1 |         1 |
|  5 | Zwei-Pankte-Vorsprung |          1 |           1 |         1 |
|  6 | Treffpankt            |          0 |           1 |         1 |
+----+-----------------------+------------+-------------+-----------+
6 rows in set (0.00 sec)

mysql>

I added a plus sign to pankt and I got different results. What 2 and not 3 ???

According to the MySQL Documentation, notice what it says about the wildcard character:

  *

  The asterisk serves as the truncation (or wildcard) operator. Unlike
  the other operators, it should be appended to the word to be affected.
  Words match if they begin with the word preceding the * operator.

  If a word is specified with the truncation operator, it is not
  stripped from a boolean query, even if it is too short (as determined
  from the ft_min_word_len setting) or a stopword. This occurs because
  the word is not seen as too short or a stopword, but as a prefix that
  must be present in the document in the form of a word that begins with
  the prefix. Suppose that ft_min_word_len=4. Then a search for '+word
  +the*' will likely return fewer rows than a search for '+word +the':

  The former query remains as is and requires both word and the* (a word
  starting with the) to be present in the document.

  The latter query is transformed to +word (requiring only word to be
  present). the is both too short and a stopword, and either condition
  is enough to cause it to be ignored.

Based on this, the wildcard character is applicable for the back of tokens and not for the front. In light of this, the output must be correct because 2 of the 3 punkt's start tokens. Same story with pankt. This at least explains why 2 out of 3 and why less rows.


Use array of composite type as function parameter and access it

I answered a similar question on SO some time ago, to suggest the same solution with unnest() like @a_horse did:

Array of composite type as stored procedure input

If books_table has the same row type as your composite type books, you do not need to create the additional type at all, just use the row type of the table:
CREATE TABLE books_table (
  book_id numeric
, row_num numeric
, book_owner text
, book_owner_id text
);

PL/pgSQL function
If you need a plpgsql function for some undeclared reason:
CREATE OR REPLACE FUNCTION update_table_variadic(VARIADIC _books_arr books_table[])
  RETURNS TEXT
  LANGUAGE plpgsql AS
$func$
DECLARE
   b books_table;
BEGIN
   FOREACH b IN ARRAY _books_arr
   LOOP
      INSERT INTO books_table  -- rare case where column list is no improvement
      SELECT b.*;
   END LOOP;

   RETURN 'Saved';

EXCEPTION WHEN others THEN
   RETURN 'Failure';
END
$func$;

Example call with list of row values:
SELECT update_table_variadic('(2,100,Arthur,1)', '(2,50,Zaphod,1)');

Without using VARIADIC, the function call would require a single array parameter.
Either an array literal (optionally with explicit cast):
SELECT update_table('{"(1,100,Arthur,1)","(1,50,Zaphod,1)"}'::books_table[]);

See:

How to call PostgreSQL function with array of rowtype as parameter from Java
How to pass custom type array to Postgres function

Or you can use an array constructor like @a_horse demonstrates.
Array literals are often easier to provide.
Major points:
Use the simpler FOREACH to loop over an array. See:

Postgres - array for loop

Avoid CaMeL-case names in Postgres unless you know what you are doing.
(Optionally) use a VARIADIC parameter to simplify the syntax for the function call. Then you can provide a list of row values. Note the maximum number of function parameters (default 100) if you go with VARIADIC:

Select rows such that names match elements of input array for pgsql function

SQL function
If you don't need to catch the exception, and you also don't need the string 'Saved' / 'Failure' to be returned, simplify:
CREATE OR REPLACE FUNCTION update_table_set(VARIADIC _books_arr books_table[])
  RETURNS void
  LANGUAGE sql AS
$func$
   INSERT INTO books_table
   SELECT * FROM unnest(_books_arr) b;
$func$;

db<>fiddle here
Old sqlfiddle


Permissions to be granted to developers to investigate jobs, queries, and packages

Don't grant ssis_admin if the dev shouldn't be sysadmins:

  Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin.

Source: https://msdn.microsoft.com/en-us/library/ms141053.aspx

Personally I would never let the devs create jobs with their accounts. As soon as they leave the company and someone disables the AD account, all their jobs stop working...

Btw: don't grant server rights directly but with a serverrole.


PostgreSQL JSON data type used as NOSQL, But VIEW as Relational Data Structure

As you have multiple rows inside the JSON column, you need a function that returns a set. This can be done using the json_to_recordset() function:

select j.*
from json_test,
     json_to_recordset(json_data) as j(name text, country text, hobby text, address text, sex text);

Because this is an anonymous record type, you must explicitly define each column. Probably not a big thing if you want to put that into a view. Note that your sample data would not return a value for the Country keys in your JSON data, only for the one named country because the matching between column names and JSON keys is case-sensitive.

If you don't want to create a view, you can shorten the above using a custom type:

create type person_t as (name text, country text, hobby text, address text, sex text);

select j.*
from json_test,
     json_populate_recordset(null::person_t, json_data) as j;

SQLFiddle: http://sqlfiddle.com/#!15/91b85/1


max_wal_size controls the total size of all WAL segments, not the size of each segment.

So with a value of 2GB, you are saying that about 128 of those segments can be created before they get re-used.

The hint is about increasing the total number of files (hence the mentioning of max_wal_size), it's not about the size of each segment (file).

Up to Postgres 10, the size of each segment is fixed to 16MB

Starting with Postgres 11, you can change the size of each segment but only when you initialize a new database cluster (but again: that's not what that hint is about).


Find the value OPTIMIZE FOR UNKNOWN is using

OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector.

If you run DBCC SHOWSTATISTICS, it's the value listed in the "All density" column of the second result set:

In this example, I'm using the StackOverflow demo database. The density vector for the Reputation column is 5.962674E-05.

If you take that value, times the number of rows in the table, 5.962674E-05 * 5277831, you get 314.69985680094. That's the number of rows SQL Server will expect to come back for any given Reputation filter.


PostgreSQL operator uses index but underlying function does not

Supported operators and workaround

The default operator class for GIN indexes on json columns jsonb_ops only supports these operators (per documentaion):

Name        Indexed Data Type   Indexable Operators
...
jsonb_ops   jsonb               ? ?& ?| @>

You can achieve this the other way round: Create a simple IMMUTABLE SQL function using the ? operator, which can be inlined and will use the index just like the operator itself:

CREATE OR REPLACE FUNCTION jb_contains(jsonb, text)
  RETURNS bool AS
'SELECT $1 ? $2' LANGUAGE sql IMMUTABLE;

This works, I tested in Postgres 9.4 ...

Misunderstandings

However, you have been asking the wrong question. There are two basic misconceptions in your question.

The jsonb operator ? cannot be used to search for values. Only for keys or array elements. The manual:

  Description:
  Does the string exist as a top-level key within the JSON value?

You got the wrong operator, the WHERE condition cannot work: WHERE d->'name' ? 'First'
The expression index you have doesn't makes sense either way

CREATE INDEX idx_jsonthings_name ON jsonthings USING GIN ((d->'name'));

The expression d->'name' returns a jsonb value. You would need d->>'name' to get the value as text.
But that would still be pointless. Since the value of the name key is a simple string, a GIN index (while possible) makes no sense to begin with.

Solutions

You don't need the operator ? - so no workaround either.
Here are two ways that would actually work:

Plain GIN index on d and use the "contains" operator @>:

CREATE INDEX idx_jsonthings_d_gin ON jsonthings USING GIN (d);

SELECT d FROM jsonthings WHERE d @> '{"name":"First"}'

You could even use the more specialized operator class jsonb_path_ops. See:

How to get particular object from jsonb array in PostgreSQL?

B-tree expression index on d->>'email' and test with good old =:

CREATE INDEX idx_jsonthings_d_email ON jsonthings ((d->>'email'));

SELECT d FROM jsonthings WHERE d->>'email' = 'First';

The second index would be considerably smaller and the query faster.


How to set max user connection per database in mysql?

You may set a cron every minute to trigger below command and pipe it to a logfile and rotate it every day.

select current_timestamp();

select count(*) sumOfUsers , user,db from information_schema.processlist group by user,db;

After a day you may grep for values more than 90 in sumOfUsers column to see which line and then you may get timestamp above it. Thus you may measure time range and do manual monitoring around that time to see how often it takes the connections almost 100. Also you may add Host  in query to see if any particular application host is doing this pile up of connections.


Nevermind.. I just fixed it... something might have been corrupt in my local profile setting. Renamed the SQL folder in Local and Roaming of App folder and it is working now.

Thanks!


How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

Assuming that Customer_details table is populated you can do lookups on the fly with LOAD DATA INFILE by leveraging session variables and a SET clause

LOAD DATA INFILE '/path/to/order_details.txt'
INTO TABLE order_details
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ord_id, @customer_name) -- use a session variable to store a value read from the file
SET cust_id =
(
  SELECT cust_id
    FROM customer_details
   WHERE customer_name = @customer_name -- use a session variable to get an id
   LIMIT 1 -- make sure that exactly one row is returned
)

Suppose we have a CSV file with the following content

ord_id, customer_name
1,"Customer1"
2,"Customer2"
3,"Customer2"
4,"Customer4"

Let's try import it:

mysql> CREATE TABLE customer_details(`cust_Id` int, `customer_name` varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Customer_details (`cust_Id`, `customer_name`)
    -> VALUES (1, 'Customer1'),(2, 'Customer2'),(3, 'Customer3'),(4, 'Customer4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE order_details (`ord_id` int, `cust_Id` int);
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA INFILE '/tmp/order_details.txt'
    -> INTO TABLE order_details
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (ord_id, @customer_name) -- use a session variable to store a value read from the file
    -> SET cust_id =
    -> (
    ->   SELECT cust_id
    ->     FROM customer_details
    ->    WHERE customer_name = @customer_name -- use a session variable to get an id
    ->    LIMIT 1 -- this is to ensure that exactly one is returned
    -> ) ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM order_details o JOIN customer_details c ON o.cust_id = c.cust_id;
+--------+---------+---------+---------------+
| ord_id | cust_Id | cust_Id | customer_name |
+--------+---------+---------+---------------+
|      1 |       1 |       1 | Customer1     |
|      2 |       2 |       2 | Customer2     |
|      3 |       2 |       2 | Customer2     |
|      4 |       4 |       4 | Customer4     |
+--------+---------+---------+---------------+
4 rows in set (0.00 sec)

There you have it.


Create ORACLE TRIGGER which (INSERT OR UPDATE) in TABLE1 whenever a new INSERT is done in TABLE2

Try something like this. It also accounts for situations in which "id" didn't previously exist. Note: I used "concat_col" as the "concat" column name; @nbk is right - you shouldn't have column names that match pre-defined function names.
CREATE OR REPLACE TRIGGER TRIGGER1
AFTER INSERT ON TABLE1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
   merge into table2
   using (select :new.id l_id from dual) d on (table2.id = d.l_id)
   when matched then
      update set concat_col = concat_col || ' ' || :new.name || '=' || :new.value
   when not matched then
      insert (id, concat_col) values (:new.id, :new.name || '=' || :new.value);
END;
/


Oracle AWS package errors when removing user

Ideally if user exists with same name, it doesn't throw this error. Try overriding the error using

  IMPDP with REMAP_SCHEMA option.


Attempting to backup to remote NAS but access is denied

You mentioned this is a NAS, but this doesn't look like a valid network path:

\Dl-nas-01\LOCAL FILE BACKUP\OLASQL

Try using two slashes at the beginning, like this:

\\Dl-nas-01\LOCAL FILE BACKUP\OLASQL


SQL Server 2014 vulnerabilities reported by Nessus

It looks like Nessus reports these vulnerabilities even when the RC4 is not enabled for any SQL Server endpoint. For SSL 3.0 SQL Server 2014 supports TLS 1.2 as of CU7, while previous versions support TLS 1.0.

I suppose that the Nessus report has to do with the possibility of algorithm downgrade from TLS to RC4. This page describes how to disable RC4 completely.

However, some of my servers are still reported by Nessus with this vulnerability, even if the RC4 cypher is disabled (default for Windows 2012 R2), so I suppose it might be a false positive on Nessus' end.


How to Retrieve Credit Debit and Balance From More Than One Table in SQL Server

I believe this will do what you need. I appreciate you making it easy to write by providing a solid MVCE.

We have 5 queries in UNION ALL to generate our data set, and one outer query to generate the running total. The reason we have 5 queries in the union is that we are using one for the Opening Balance, and the other 4 for each transaction type.

I cheated a bit with the running calculation using ORDER BY Date, Description in the balance. Because we generated the number inside Description. There is surely a cleaner way to do that. If you need to order by a specific type you could add an additional column to the union queries and code them (1, 2, 3, 4, 5) and then order by that column in the outer query.

However, this query does get you to the goal line:

SELECT
  CustomerName,
  [Date],
  Description,
  ABS(Debit) as Debit,
  ABS(Credit) as Credit,
  SUM(CASE WHEN Credit = 0 THEN DEBIT ELSE Credit END) OVER (PARTITION BY CustomerId ORDER BY [Date], Description)  as Balance
FROM

(
    SELECT
      CustomerId,
      CustomerName,
      RegistrationDate as [Date],
      'OPENING BALANCE' as Description,
      OpeningBalance as Debit,
      0 as Credit,
      OpeningBalance as Balance
    FROM Customer

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName,
      InvoiceDate as [Date],
      'Invoice No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, InvoiceDate)) AS VARCHAR(10)) AS Description,
      InvoiceTotal as Debit,
      0 as Credit,
      OpeningBalance
    FROM Customer c
    JOIN Invoice i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName,
      PaymentDate,
      'Payment No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, PaymentDate)) AS VARCHAR(10)) AS Description,
      0,
      -1.0 * PaymentTotal,
      OpeningBalance
    FROM Customer c
    JOIN Payment i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName,
      ReturnInvoiceDate,
      'ReturnInvoice No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, ReturnInvoiceDate)) AS VARCHAR(10)) AS Description,
      0,
      -1.0 * ReturnInvoiceTotal,
      OpeningBalance
    FROM Customer c
    JOIN ReturnInvoice i ON c.CustomerId = i.CustomerId

UNION ALL

    SELECT
      c.CustomerId,
      CustomerName,
      ReturnPaymentDate,
      'Return Payment No. ' + CAST((ROW_NUMBER() OVER (PARTITION BY c.CustomerId ORDER BY c.RegistrationDate, ReturnPaymentDate)) AS VARCHAR(10)) AS Description,
      ReturnPaymentTotal,
      0,
      OpeningBalance
    FROM Customer c
    JOIN ReturnPayment i ON c.CustomerId = i.CustomerId
) u

ORDER BY [Date]

Here is a SQLFiddle


Target server is not communicating(polling) with master SQL Server

Does the target server know that it is a target server? In SSMS, does Agent say that this is a TSX?
I have a feeling that the fact that you are in an MSX environment is stored in the registry instead of a system database.
I just looked on an TSX server, an SQL Server instance named "B". The master server is an instance named "A" (on the same machine, this is a test setup). I have a key named:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.B\SQLServerAgent
In above key, I have an entry named MSXServerName with the name of the master server.
Above is what I would fix first.
After fixing that, I would check the SQLAgent.out file on both MSX and TSX. And possibly resync the TSX server using sp_resync_targetserver. I blogged about this here:
http://sqlblog.karaszi.com/resynchronizing-a-target-server-msx-tsx/


High amount of disk writes during upsert with no changes on table

The DO UPDATE locks the "victim" tuple before it evaluates the WHERE part.  This dirties the page containing the tuple, even if the WHERE ends up short-circuiting the UPDATE.


Access denied for user 'me'@'localhost' (using password: NO)

Now you can probably login by

sudo mysql -u root -p

and entering the password from your ALTER USER statement when prompted.

Once you're in the console again you can reset the password to empty like:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

And it should start to allow you with just sudo mysql as before.


Understanding Set Returning Function (SRF) in the SELECT List

Postgres treats the simple case differently. Multiple columns are treated as composite type (table row), which is only decomposed with SELECT * FROM ..., while a single column of scalar type is treated as just that, no added composite type wrapper. So SELECT my_SRF() produces the same as SELECT * FROM my_SRF() for that simple case. The manual about Table Functions:

Table functions are functions that produce a set of rows, made up of either base data types (scalar types) or composite data types (table rows).

I agree this is confusing, and you are not the first to be confused. (Consider the alternative, though: adding a composite type wrapper around a single column might be even more confusing.)
But not as confusing as what happens used to happen with multiple SRF functions in the SELECT list before Postgres 10. This was fixed for good:

What is the expected behaviour for multiple set-returning functions in select clause?

The standard SQL way is to move SRF functions to the FROM clause. Use a LATERAL join to operate on table columns. The manual suggests:

The LATERAL syntax produces less surprising results when calling
multiple set-returning functions, and should usually be used instead.

What is the difference between LATERAL and a subquery in PostgreSQL?


Unable to call sp_start_job from website only

Can you confirm with a Profiler trace which credentials are attempting to execute sp_start_job? I'm curious if it's the credentials of the client using the browser or the underlying application pool. If the account in question has sysadmin, it should already have the permissions to execute jobs.


How to enforce integrity when joined tables share a foreign key?

Making sure I understand/have correctly elaborated on @ypercubeᵀᴹ's comment/linked answer:

The correct structure should be:

UserGroup(ie. 'Admin')
    ID = 'Admin'
    ApplicationFK = 'App1'
    [PK: ID, ApplicationFK]

JoinTable
    UserGroupFK = 'Admin'
    ApplicationFK = 'App1'
    PermissionFK = 'Approve'
    PermissionValue = 'READ'
    [PK: UserGroupFK, ApplicationFK, PermissionFK]
    [FK -> UserGroupFK+ApplicationFK -> UserGroup]
    [FK -> PermissionFK+ApplicationFK -> Permission]

Permission(ie. 'Approve')
    ID = 'Approve'
    ApplicationFK = 'App1'
    [PK: ID, ApplicationFK]

UserPermission
    UserFK = 'John'
    PermissionFK = 'Approve'
    ApplicationFK = 'App1'
    PermissionValue = 'WRITE'
    [FK -> PermissionFK+ApplicationFK -> Permission]

User
    ID = 'John'


"The attribute cannot be found when processing" error

You should only get that error when a value exists in the fact table that doesn't exist in the dimension table, the error is "attribute key", only dimensions have attributes, facts don't, they have measures and dimension relations, maybe you have a dimension based on your fact table?

Having keys in your dimension that don't exist in the fact table is perfectly fine. Think of it this way: you can have a date dimension containing every date, but some days you haven't sold anything, so the date wouldn't appear in your sales fact while it exists in the dimension table. That's a perfectly reasonable scenario.

If you don't want to use a custom error configuration you will have to solve that in your source data (which would be the best solution anyway).
If you have an ETL process that would be the best point to solve your missing foreign keys but if you don't you can resort to named queries in the dmv where you UNION the missing records with the actual data.

SELECT 'missingkeyvalue' as key, 'explanation' as description
UNION
-- select actual data

The (unknown) dimension member doesn't really tell your users anything useful when they view it in a report, and they often forget to select them in filters.

Copied from my answer here:

If you have missing data in your foreign keys in your fact table (i.e. when your ETL process looks up the surrogate keys in your dimension table but the related row doesn't exist in your dimension) you need to decide based on your user requirements, but the general approach is to convert them to blank, 'unknown, invalid or whatever your requirements are.

If you have blank attribute values you should probably leave it as blank as to not confuse the users.

One thing to keep in mind specific to SSAS is that using error configurations in SSAS to let SSAS handle missing dimension keys has a huge impact on processing performance so you should probably solve that in your ETL process. The (unknown) dimension member is also very confusing for your end users as they don't know what it means when seen on a report and often forget to include it in filters (they have to select the unknown member on every dimension or they will be missing data).

As usual Kimball has a best practice on handling missing data in your dimensional model: Design Tip #128 Selecting Default Values for Nulls


SELECT items that has one or more specific TAGS

For a specific tags list you can simply calculate the amount of tags from this list attached to each item using

SELECT item_id, SUM(tag_id IN (specific_tags_list))
FROM item_has_tag
GROUP BY item_id

To obtain the list of items which have all of the tags in tags list you must compare calculated SUM() with the amount of tags in the list:

SELECT item_id
FROM item_has_tag
GROUP BY item_id
HAVING amount_of_tags_in_list = SUM(tag_id IN (specific_tags_list))

  items that item_has_tag.tag_id = 1 AND item_has_tag.tag_id = 2 AND item_has_tag.tag_id = 3 for example

For this case the query will be

SELECT item_id
FROM item_has_tag
GROUP BY item_id
HAVING 3 = SUM(tag_id IN (1,2,3))

PS. The fields pair (item_id, tag_id) must be defined as unique by index (in any order, maybe primary) in the table item_has_tag structure.

UPDATE

  Your code works very well but I get into trouble when I try to get the
  result from the items list SELECT items.* FROM items, item_has_tag
  GROUP BY item_has_tag.item_id HAVING 3 = SUM(item_has_tag.tag_id IN
  (1,2,3)) Error "SELECT list is not in GROUP BY clause"

Variant 1:

SELECT items.*
FROM items, ( SELECT item_id
              FROM item_has_tag
              GROUP BY item_id
              HAVING 3 = SUM(tag_id IN (1,2,3))
            ) subquery
WHERE items.id = subquery.item_id

Variant 2:

SELECT items.name /* , another fields */
FROM items, item_has_tag
WHERE items.id = item_has_tag.item_id
GROUP BY items.name /* , another fields */
HAVING 3 = SUM(item_has_tag.item_id IN (1,2,3))

Variant 3: disable session ONLY_FULL_GROUP_BY SQL Mode (not recommended).

PS. Variants 1 and 2 can be freely converted to JOIN form.


"New query window" shortcut for Oracle SQL Developer

In Oracle SQL Developer Application

Goto: Tools->SQL Worksheet

OR

ALT+F10


DBCC CheckDB causing high values for resource_semaphore wait type

There is nothing wrong! This is how DBCC CHECKDB works. You can read Jonathan's post explaining this behavior and a workaround if you're on the Enterprise Edition -- DBCC CHECKDB Execution Memory Grants – Not Quite What You Expect

The only strange thing is that usually this happens with servers with larger amount of RAM!

Here is also a really good article on Understanding SQL server memory grant

  Memory grant uses "RESOURCE_SEMAHORE" wait type. If you see significant waits on this wait type, you may have an issue with big queries.

Perhaps, if you see a big IO Queue on your SAN, you have a lot of IO bound queries on top of your memory pressure. Check the PLE and Memory Grants Pending counters on this server. If you PLE is very low and you have lots of pending memory grants, your server might benefit from additional memory. Also look if you need to add any indexes (review missing indexes DMV) -- Are you using SQL's Missing Index DMVs?


WAL archive: FAILED (please make sure WAL shipping is setup)

When running into an error like this, be sure to run barman switch-xlog --force --archive <server_name> as noted in the barman documentation to verify the WAL archiving process.


How to find all participants of a chat thread?

The problem with that though is that for my application I need to order this result set by post date, so the author should always be on top, and UNION ALL messes up the order.

Actually, the order should be preserved as UNION ALL is implemented to just append rows. (Might be revealing to include the offending query in your question.)
Also, you might merge tables post and post_replyinto a single table. Depends.

And if you typically have many rows per user in a chat, use a recursive CTE to get the distinct count of users with best performance using a matching index:

Fast way to count distinct column values (using an index?)


sqlsrv_connect: use Windows authentication?

As stated in the documentation you do not pass the UID and PWD to utilize Windows Authentication. You can see an example here.

From remarks within documentation link:

  If values for the UID and PWD keys are not specified in the optional $connectionInfo parameter, the connection will be attempted using Windows Authentication. For more information about connecting to the server, see How to: Connect Using Windows Authentication and How to: Connect Using SQL Server Authentication.


Improving accurate count mechanism in PostgreSQL

With a large table, you won't be able to avoid I/O, and the query will never be fast. Very fast aggregates over huge amounts of data are not PostgreSQL's strong point. You want a column store for that (PostgreSQL has a column store extension).
If you VACUUM the table, PostgreSQL can avoid looking up the row by consulting the visibility map.
If 11 partitions are static, with only one active, it might be possible to store the total for the static partitions and add to that the count of rows for the active partition. If the user can apply filters, there is no way to get out of actually counting the rows that match. You might need a data warehouse of some sort that summarises the data.


The primary purpose of FOREIGN KEYs is to maintain Referential Integrity (RI). Check out any of the standard textbooks (Connolly, Elmasri, Garcia-Molina or Silberschatz amongst others).

This fundamental concept endures despite users' and programmers' best efforts to produce systems which render the concept null and void - by that I mean that they frequently try and subvert these (reasonable) requirements of a decent database system. See here for further details on that matter.

As explained in the Wiki, RI is

  a property of data which, when satisfied, requires every value of one
  attribute (column) of a relation (table) to exist as a value of
  another attribute (column) in a different (or the same) relation
  (table).

To give a simple example, that means that if you have a purchase order system with an order_header table with fields as follows:

CREATE TABLE order_header
(
  order_id INTEGER,
  client_id INTEGER,
  order_date DATE,
  <...other fields depending on your individual requirements...>
);

You might then have a client table like this

CREATE TABLE client
(
  client_id INTEGER,
  client_name VARCHAR(50),
  client_address VARCHAR(50),
  <.... other fields depending on your requirements...>
);

You would then add a CONSTRAINT to your order_header table with something like this:

ALTER TABLE order_header
    ADD CONSTRAINT fk_header_client
    FOREIGN KEY (client_id)
    REFERENCES client (client_id);
-- the exact syntax may differ per RDBMS.

This is telling the RDBMS that on the client_id column of the order_header table, there is now a constraint such that any client_id in order_header must already exist in the client table.

The referenced FOREIGN KEY columns(s) on the parent table need to have a UNIQUE constraint (or be the PRIMARY KEY).

Otherwise, it wouldn't make much sense - if there were 2 clients with the same client_id, how would the system be able to tell them apart - what address do you send the order to?

Note also that with many systems, you can create the CONSTRAINT in the CREATE TABLE statement.

Now, your system will be unable to create a purchase order for a client which does not exist. This doesn't mean that the wrong client can't be entered into the system, but it does help to ensure that the system is internally consistent.

Many optimizers can also take advantage of some optimizations when there are FOREIGN KEY constraints on the joined tables and produce better (more efficient) and simpler query plans.

Finally, a further advantage of using FOREIGN KEYs is that some systems also can (depending on your RDBMS) automatically create INDEXes on the FOREIGN KEY columns.


There is a CREATE CERTIFICATE permission:

GRANT CREATE CERTIFICATE TO [user_or_role_name];

Here is a simple test:

USE [tempdb];

CREATE USER [t] WITHOUT LOGIN;

EXECUTE AS USER = 't';

PRINT 'Before GRANT:';
CREATE CERTIFICATE [tt] WITH SUBJECT = 'test';

REVERT;

GRANT CREATE CERTIFICATE TO [t];

EXECUTE AS USER = 't';

PRINT 'After GRANT:';
CREATE CERTIFICATE [tt] WITH SUBJECT = 'test';

REVERT;

DROP USER [t];

Which returns (in the "Messages" tab):

  Before GRANT:
  Msg 15247, Level 16, State 1, Line 15
  User does not have permission to perform this action.
  After GRANT:
  Msg 15581, Level 16, State 1, Line 26
  Please create a master key in the database or open the master key in the session before performing this operation.


Is data warehouse monitoring same as database monitoring?

"On what parameters can we monitor data warehouse?".

Exactly the same ones that you use for monitoring an OLTP database. You might want to pay more attention to things like slow query log (MySQL) for a DW, but that should be on the radar for an OLTP system also.

The common "gotchas" may change, but databases all sit on machines with disk, RAM, CPU and network connections. Bottlenecks can and do occur with any/all of these depending on your hardware (notably RAID - most database apps tend to be I/O bound), your application (and its configuration) and your network bandwidth.


Migrating 2+ GB Oracle CLOB column to SQL Server VARCHAR(MAX)

Normally I would recommend using the XML data type to store HTML but it too has the 2GB limit. This then leaves you with two options: FILESTREAM and FILETABLES. Be aware though, that both of these options require some additional configuration within Sql Server and your database before you are able to use them.

File tables are built on-top of FILESTREAM and offer some benefits if you need to access the files using standard Windows API calls. File Tables are generally transparent to the consumer and easier to integrate into your application but lack transactional access. This means that the files can be removed from outside of the Sql Server context which may be undesirable.

FILESTREAM however must be done under a transaction context and therefore prevents anyone (with exception of administrators/people granted explicit permission to the FILESTREAM folder) from removing them outside of Sql Server. They do not have the 2GB limit which will allow you to get around the problem that you are currently facing; however, accessing the data can be a little more complicated. Luckily, the documentation does a pretty decent job of explaining how to perform CRUD operations with FILESTREAM data.

If I had to pick one, based on what you have provided above, I'd go with FILESTREAM as this will offer you better consistency and control over your data.


Display the error message returned form Mysql

From MySQL manual:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, error_string = MESSAGE_TEXT;
    END;

I am just not sure about the use of local vs. user variables. User ones are visible for entire session and written in the @var form and if you need them, try to just adding @ to the var names in that statement.


SQL statement sequential execution for multiple statments

If there are multiple queries in a batch then these queries will be executed in the order they appear in the batch.

After the execution of Query 1 is finished then the update query will be executed.
You can run debugger to see the sequential execution.


What can data cache contain besides pages from physical disk?

There are couple of things here so let me explain

Logical reads: A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache.

Read aheads: I would suggest you to read Reading Pages in SQL Server

  The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.

  The read-ahead mechanism allows the Database Engine to read up to 64
  contiguous pages (512KB) from one file. The read is performed as a
  single scatter-gather read to the appropriate number of (probably
  non-contiguous) buffers in the buffer cache. If any of the pages in
  the range are already present in the buffer cache, the corresponding
  page from the read will be discarded when the read completes. The
  range of pages may also be "trimmed" from either end if the
  corresponding pages are already present in the cache.

Now to your question

  physical_reads are 0 and read-ahead reads are 276 which means that 276 pages of that table which are on the disk drive were in data cache,

No that does not means just the 276 pages it can be much more than 276 actually it is always more than that. Read aheads read *more than required pages *and bring that pages into memory this is done for performance optimization to make sure all the pages are in cache and physical I/O can be avoided. A single read may bring upto 64 pages and this would be seen as one read.From Old BOL document Read-ahead requests are generally 128 pages for each group but can be as many as 1,024 pages when running Microsoft SQL Server Enterprise Edition.

From this Old Technet Article, for enterprise edition Read Ahead  can be up to 1024 KB.

Logical reads are counted for single pages while read aheads are counted as one for chunk of pages.


I created a sequence but I can't alter the increment number after that

The starting value of a sequence can not be altered. Use:

ALTER SEQUENCE NEXT INCREMENT BY 3;


Most likely Service Broker is enabled in your production database. It is enabled by default, it only gets disabled on restore in order to prevent distributed split-brains scenarios.


Database Mirroring error, but everything looks OK

Have you granted the CONNECT permission to the Mirroring Endpoint for the SQL Server service account?

GRANT CONNECT on ENDPOINT::Mirroring_Endpoint TO [domain\user];


Restoring SQL Server database backup file for read-only access - is it possible to skip the ldf file creation?

In brief: NO.

The transaction log file (the .ldf file) is a vital and absolutely necessary part of any SQL Server database. You cannot skip this, you cannot opt to not have it - just doesn't work. SQL Server must have a transaction log stored in a file - no way around that.


What is the preferred method to roll out updates to hundreds of databases

I would adapt your fast solution to something more easily extensible, like PowerShell.

It would be trivial to have PS read a text file for a list of servers to run against, and another file or files for what commands to execute.

You could also add error handling and such like in your SSIS batch, and have it fire asynchronously so it would work on all your servers simultaneously.


Importing from CSV file, is crashing mysql server

Let's do some calculation:

uuid: 36 bytes (Add 1 because it is varchar) ==> 37
uuid_tags_codes: 4 bytes
When indexed, full length of uuid field: 100 bytes

(37+4+100)bytes * 162,281,544 rows = 21.3 G

So, 21G without adding the overhead! Therefore, your first problem is the space.

To answer your questions:

It is more than 6-8G. To ba safe you need ~30G. Single MyISAM table can handle that. I don't recommend it though. MyISAM is not as safe as InnoDB for example.
MySQL is not wrong solution, but I think there are better solution. Since it is kind of look up table, key-value store would do better job. For example, memcache, Redis.
You can improve the structure by making uuid char(36). In fact, this article has even more efficient solution. Also, use the smallest integer type that fits your maximum value of uuid_tags_codes
You may want to load the data first to the table without its indexes, then create the required index.

HTH


Difference between Full backup and Copy-only full backup

The key difference between Full and Copy-only backups is whether or not the LSN (Log Sequence Number), and specifically the DatabaseBackupLSN is updated.

When you take a Full backup, the DatabaseBackupLSN is updated. After taking the full backup, if you take a Differential backup that backup has a DatabaseBackupLSN which matches that of the Full backup, and therefore SQL can link the two together (eg it knows from those LSNs that the diff followed the full).

The problem comes when you have scheduled backups running, so you have an initial Full backup, and then a sequence of Differentials. If you manually take a Full backup it will update the LSN, and then from that point onwards each Differential backup you take via scheduled backup will reference that new LSN not the original one. In the event of needing to restore, you could restore your scheduled Full backup, but when you tried to restore any of the Differential backups taken after the manual job you'd find it would fail as the LSNs no longer match.

With a Copy-only backup it doesn't touch the DatabaseBackupLSN, and as such doesn't break that chain of backups.

There's a good description of the issue, as well as why so many people misunderstand it in Breaking the Backup Chain – REDUX (Or Eating Crow) by Michael K. Campbell, which includes good visual guides like this one:

For a good explanation of the four different LSNs and how they're used, have a look at Understanding SQL Server Log Sequence Numbers for Backups by Simon Liew.

The way to avoid the issue is to not have more than one thing doing standard backups of a database. Any adhoc or secondary backups should be done with the copy-only option, see Copy-Only Backups (SQL Server) for full details, but essentially you use the "Copy Only Backup" option in SSMS, via T-SQL specify WITH COPY_ONLY in the command, or with PowerShell use the -CopyOnly parameter.


is there a statement to check if a specific index's auto stats is disabled?

You can use sys.sp_autostats or sys.stats to get this information.

sys.sp_autostats can also be used to enable or disable automatic updates for a particular statistics object.

For example, using the AdventureWorks sample database:

EXECUTE sys.sp_autostats
    @tblname = N'[Production].[TransactionHistory]',
    @flagc = NULL,
    @indname = NULL;

The AUTOSTATS column shows if the statistics will be automatically refreshed:

SELECT
    S.name,
    S.stats_id,
    S.auto_created,
    S.user_created,
    S.no_recompute
FROM sys.stats AS S
WHERE
    S.[object_id] = OBJECT_ID(N'[Production].[TransactionHistory]', N'U');

The no_recompute column shows if automatic statistics updates will be skipped:

  If I disable auto stats for an index before my reorg jobs, will it prevent blocking issues?

Reorganizing an index does not update statistics. You probably need to clarify your question to explain the exact problem you are encountering.


How to update one database from another?

Without more specifics:

CREATE PROCEDURE dbo.UpdateA
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE A
    SET col1 = B.col1 /* , ... */
    FROM Adb.dbo.commonTable AS A
    INNER JOIN Bdb.dbo.commonTable AS B
    ON A.key_col = B.key_col;

  INSERT Adb.dbo.commonTable(key_col, col1 /* , ... */)
    SELECT key_col, col1 /* , ... */
    FROM Bdb.dbo.commonTable AS B
    WHERE NOT EXISTS (SELECT 1 FROM Adb.dbo.commonTable
      WHERE key_col = B.key_col);

  DELETE A
    FROM Adb.dbo.commonTable AS A
    LEFT OUTER JOIN Bdb.dbo.commonTable AS B
    ON A.key_col = B.key_col
    WHERE B.key_col IS NULL;
END
GO

Now schedule that stored procedure every 15 minutes using a SQL Server Agent job.

Isolation, concurrency, error handling etc. are all additional considerations, but most concerns should be minor if this is the only process that writes to Adb. You might also consider a more concise MERGE command, but the syntax is daunting and it buys you less than you might expect in terms of improving concurrency and eliminating race conditions.

You might also want to consider transactional replication - get rid of this concept of having to do anything manually, and also the idea that data could be up to 15 minutes out of date.


Differences between estimated and actual execution plans

The estimated execution plan is generated based solely on the statistics that SQL Server has - without actually executing the query. The query optimizer is just asked what it would most likely do with this query, based on all the information it has on the query and the data distributions etc.

This works OK, the query doesn't need to run (which could take a long time), but if the statistics are out of date, the plan might be severely skewed.

The actual execution plan is just that - the actual execution plan that was used when actually running the query. This will show you things that might hint at "out-of-date" statistics etc. But to get this, you must run the query - which can take a long time.


How to join same department with multiple staff?

Do you mean you want to left join but then show single departments with the staff concatenated?

SELECT d.departName, GROUP_CONCAT(s.fullName SEPARATOR ' <,> '), GROUP_CONCAT(s.email SEPARATOR ' <,> ')
from staff s
left join department d on s.departCode = d.departCode
left join country c on s.countryCode = c.countryCode
where c.countryCode = 2
group by d.departName


Should I snowflake or duplicate it across my facts?

The "header - detail" pattern is very common the domain of sales transactions.
To answer your question, there are so many factors that will come into play which you've not discussed. For example:

If your DW infrastructure has a great deal of RAM and is on SSD storage, reads in this case are cheap, so it might make sense to denormalize some dimensions in the interest of usability.
What are the use-cases of the data?  In this case I can probably make assumptions - it is sales data. It'll be used for accounting, executive reporting, predictive analysis, customer service, and for just about every possible ad-hoc query you can imagine.

One general principle I use when deciding whether to snowflake a dimension or simply include it's value in the fact table is this:

If the dimension has many attributes which might be useful for reporting (or if there will be report(s) solely on that dimension), I create a dimension for it.

Example: Consider the CUSTOMER dimension. A sales order has a customer, but there are other attributes which belong with the CUSTOMER dimension which you might want to report on, like customer location, customer age/sex/marital status, customer type, customer create date, etc., and many other customer-related attributes. I wouldn't put all of these in a fact table, so in this case I "snowflake" to a customer dimension as there are many more attributes related to CUSTOMER which might be relevant to your sales fact data. There would likely also be reports that solely rely on the CUSTOMER dimension - like a "new customer by month" report. You wouldn't expect this to be in the fact data. The PRODUCT dimension is another I would almost always put in it's own dimension.

If the dimension is a single value with no other useful attributes connected to it, I may consider it for inclusion in a fact table.

Example: We might have an attribute called "Order Source Channel" - which might be a single value describing where the sales order came from e.g. it might have values like eCommerce, Kiosk, Point-of-Sale, Phone-In, etc. It's a single value, and no other related attributes exist for this entity. In this case, I am tempted to leave it in the fact table, rather than create a single-attribute dimension and require my users to do an additional join, etc.

Remember:

The above is a generality, and I don't treat is as a hard-fast rule
Data modeling is as much an art as it is a science. There are many scenarios which can go either way, and only experience will help you decide which way to go.
Usability of your DWH structure should be as much a consideration as is performance. I try to never create a data model that requires my power-users to write SQL queries with 15+ or more joins just to get at sales data. This will lead to someone writing incorrect SQL (it always does). This will sometimes be mistaken as "bad data". This is what you, as a DWH developer, don't want happening.


Turning off auto shrink on all SQL Server databases. Why doesn't this work?

Unfortunately, the procedure sp_MSforeachdb is and will always be unsupported and thus, kind of unreliable.

There's an article on CodeProject that shows how to filter databases when using this DB. But I have the same problem as yours on my local 2008 R2 installation. Filtering doesn't work.

Our own friend, Aaron, wrote a nice article some time ago, about writing a different and better version of sp_MSforeachdb. See his article here. Use that SP and the filtering parameters will do what you need.


How to get rid of "maximum user connections" error ?

The option max_user_connections is a limit imposed, not on the total number of simultaneous connections in the server instance, but on the individual user account.
Let's say the user is called db_user@localhost. You can find out what this user's connection limit is by running this query:
SELECT max_user_connections FROM mysql.user
WHERE user='db_user' AND host='localhost';

If this is a nonzero value, change it back with:
GRANT USAGE ON *.* TO db_user@localhost WITH MAX_USER_CONNECTIONS 0;

or
UPDATE mysql.user SET max_user_connections = 0
WHERE user='db_user' AND host='localhost';
FLUSH PRIVILEGES;

This will cause mysqld to allow the user db_user@localhost to use the global setting max_user_connections as its limit.
Once you get to this point, now check the global setting using
SHOW VARIABLES LIKE 'max_user_connections';

If this is a nonzero value, you need to do two things
THING #1 : Look for the setting in /etc/my.cnf
[mysqld]
max_user_connections = <some number>

comment that line out
THING #2 : Set the value dynamically
SET GLOBAL max_user_connections = 0;

MySQL restart is not required.
CAVEAT
I have discussed this setting in the past

May 24, 2011 : What are user connections - when are the created and destroyed?
Jun 16, 2011 : Would it be sensible to create a MySQL user for each user account of a web application?
Mar 22, 2012 : How can I limit MySQL connections?
Feb 22, 2013 : Mysql : Maximum number of connections on a per user basis - Possible?

Give it a Try !!!


Company supplied data list, with customer additions

For the sake of the customer having a local copy and the supplier having the master copy you could have two tables...
Supplier's Master Copy
blueprints_master
~id ~ name ~ colour ~
~ 1 ~ chair   ~  pink    ~
~ 2 ~ table   ~  blue    ~

Customer Local Copy
blueprints_customer

~id ~ name ~ colour ~ blueprits_master_id ~
~ 1 ~ chair   ~hot pink~  1                                 ~
~ 2 ~ desk   ~ wood   ~   null                            ~

Problems Solved

  A list of blue prints the customer can add, remove, and update
  A link (foreign key) between a customers blueprint and a master blueprint
  I think this is useful in case the customer want's to "change" one of the master blueprints. Internally the system will create a new entry in the _cusomter table rather then changing the _master table, but the customer will still feel like they are doing what they wanted
  A list of blueprints the supplier has absolute control over

Problems Remaining

  Syncing the _customer and _master tables.

unfortunately I do not have any proposed solution to the syncing problem nor can I reference to any other post...


The anti-null brigade would insist you normalize further to create the suggested PhoneNumber table. The practical folk would consider your original design perfectly acceptable. I'm in the latter camp.

Normalize until it hurts, de-normalize until it works.


Sql Server - How to convert Audit log into a table?

Capturing DML operations on individual tables is among the auditing features that are not available in Standard Edition.

If you want to trace DML operations, another possible hook is Extended Events. You won't be able to use the auditing package, but you could use lock acquired events to capture read and write operations.

I blogged about it here: https://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/

Other options are:

Triggers: this is easy to set up, even automatically by generating the trigger code. Downside is the increased write overhead.
App-side logging: If you have control over the application, you can inject the code needed to log accesses to the tables.


Can I use a Percona Xtrabackup backup to restore a single table?

This functionality is currently only supported on Percona Server.

See the official documentation link for more information:
http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/importing_exporting_tables_ibk.html


Standard naming for a credential systems

If I understand your question correctly, the mechanism you are describing is an authentication system.

Basically speaking, this kind of mechanism allows the identification of a certain party (e.g., a person or a machine process) that is attempting to access a particular system. Said party provides a certain set of credentials that are compared with information stored (or retrieved) by the authentication mechanism, and if such set of credentials matches the information stored (or retrieved) by the authentication tool, the involved party receives successful access to the system, otherwise access is denied.

Examples of authentication instruments are the ones created for web applications by means of:

ASP.NET Membership
ASP.NET Identity

If you are interested in authentication processes carried out by concrete database management systems, you might find of help:

The IBM DB2 Authentication Methods For Your Server
The Microsoft SQL Server Logins and Authentication Modes
The Oracle Authentication Methods

Authentication systems are closely related to authorization components which, to put it short, take care of determining whether a specific party has permissions to perform a precise action within a given system.

A platform that assists in the development of both authentication and authorization elements pertaining to different types of application programs is:

The Java Authentication and Authorization Service (JAAS)


Moved Model Database and now my SQL Server doesn't work

Did you follow the steps in the documentation for doing this?

Move System Databases - SQL Server 2012

  To move a system database data or log file as part of a planned
  relocation or scheduled maintenance operation, follow these steps.
  This procedure applies to all system databases except the master and
  Resource databases.

  For each file to be moved, run the following statement.

  ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
  Stop the instance of SQL Server or shut down the system to perform maintenance. For more information, see Start, Stop, Pause, Resume,
  Restart the Database Engine, SQL Server Agent, or SQL Server Browser
  Service.
  Move the file or files to the new location.
  Restart the instance of SQL Server or the server. For more information, see Start, Stop, Pause, Resume, Restart the Database
  Engine, SQL Server Agent, or SQL Server Browser Service.
  Verify the file change by running the following query.

  SELECT name, physical_name AS CurrentLocation, state_desc
  FROM sys.master_files
  WHERE database_id = DB_ID(N'<database_name>');

  If the msdb database is moved and the instance of SQL Server is
  configured for Database Mail, complete these additional steps.

  Verify that Service Broker is enabled for the msdb database by running the following query.

  SELECT is_broker_enabled
  FROM sys.databases
  WHERE name = N'msdb';

  For more information about enabling Service Broker, see ALTER
  DATABASE (Transact-SQL).

  2. Verify that Database Mail is working by sending a test mail.

Critically, note that the command you ran doesn't actually move the physical data files.


CREATE DATABASE AS COPY does not work with Azure

From BOL :

  The AS COPY OF argument does not support the fully qualified unique domain names. In other words, if your server's fully qualified domain name is serverName.database.windows.net, use only serverName during database copy.

So your command should be

CREATE DATABASE Database1_copy AS COPY OF oldserver.olddatabasenamehere;


Azure SQL Databases hit cpu limits too quickly

For questions like these you need to include the query plan and relevant table structure (what keys and indexes are in place) to get a good answer that is specifically relevant. I recommend that you update the question with that detail.

The first thing that springs to mind is the JOIN to Answer - is there a useful index for the QuestionId column that you are joining on? A common error is to assume that defining a foreign key constraint also defines an index (much like defining a unique constraint does) but this is not the case. Most RDBMSs don't because there are occasions where the index is not needed at all so creating one implicitly would be wasteful of space and unnecessarily add I/O to insert & update operations.

If there is no useful index on Answer.QuestionId then that table will be subject to a scan (and probably a sort too) which if the table is small enough to fit in memory will generally be a CPU-bound operation.

If you are seeing this a lot in your DB with the same pattern of query then it may be that your DB devs are working under the assumption that adding a foreign key also defines an index, possibly because they have a mySQL background as IIRC that does create an index implicitly when an FK is defined.


how to add data compression to a primary key of a table?

Is there a way to apply a data compression to this table without dropping the primary key?

Yes you just need to run below command to rebuild the clustered index (in your case it is also PK):

ALTER INDEX [PK__t1__3BD0198E286DEFE4] on TABLE [t1]
REBUILD with (ONLINE = ON, DATA_COMPRESSION = PAGE)

"ONLINE = ON" clause can help greatly reduce locking of table that is being rebuilt


Well, maybe it's not the answer I thought I'd find, but it works perfectly.

I have splitted audit_insert field in another one, audit_insert_datetype, of DATE type. This field has a new index too.

I have changed the query to execute with this field, and I have tried to force the @... variables to be date type (with current_date and date).

The results: the new index is used and the execution time is dramatically reduced.
Maybe it's bad style, but it works as I need.