How to manage concurrencies without locking tables in MySQL?

Concurrency and isolation are the two topics you are dealing with here - the ability for more than one user to use the system simultaneously and for each's work to be unaffected by the others'. RDBMS handle this through isolation levels and locks. It is how they are designed to work; it is what they do. Our job as application architects is to choose settings which meet the requirements and build the system in such a way that minimises the time window in which conflicts can occur.

In this system you want consistency between rows in the Order table and other tables. For this you need locks. This is precisely what locks are for. Don't be scared of them. They are your friend and protect your data. For the reference tables you want to ensure you are taking shared locks by only reading and not writing, and preferably row locks. This way multiple concurrent activities can share the reference data.

Writing the Order will create an exclusive lock. Hopefully on just the new row but maybe on the table. It is therefore important to release that lock quickly by committing the transaction as soon after the insert as possible.

What you do not want is that these locks are held while the agents are typing. That is an eternity in computer terms. By all means validate input as they type but only as single statements, never in a transaction spanning the whole data entry session. Then at the end, when the order is saved, start a transaction, re-read the reference data, insert the Order and commit. On any platform that should complete in milliseconds. The other operators won't even notice it happening. If there is an error or a conflict the agent will get immediate feedback, which they can correct and re-submit.

There is an alternative, which I wouldn't recommend, but list here for interest's sake.  You could architect the system on an asynchronous, message-processing design. When an agent saves data it is written to a single staging table which has one column per data value.  No validation, no normalisation. One single insert and the data's persisted. With 7 operators the chances of there even being two operations in the DB at one time are almost zero. A second process polls this table to read one row at a time. It validates and writes to the normalised schema. The result - success or failure - is recorded. Then the next row in the staging table is processed, and so on. This guarantees that all inserts are fully serialised, at the expense of a significantly more complicated design, no immediate feedback to agents and the need for additional business processes and programming to correct errors. Also this will not protect against activity from other programs connecting to the database. Something has to be reading those orders after all. As I said, not recommended.

Seven agents entering data at the same time is not a big number.  You will be able to construct a system to handle this, I promise.


Perhaps SQL Azure Database Manager? Requires a browser, internet connection, and Silverlight. http://www.mssqltips.com/sqlservertip/2295/getting-started-with-the-sql-azure-database-manager/


There are a number of "features" of merge statements that can lead to issues like this. For a relatively comprehensive list of issues take a look at the following link:

Use Caution with SQL Server's MERGE Statement

I've seen similar issues occurring myself, the solution was either to re-write the code to avoid MERGE altogether or in once case where it was unavoidable, dropping indexes on the target table before the MERGE and then restoring them afterwards resolved the issue.


Explaining what the OR operator does in the following code

Another way you can look at it, which might help you conceptualize, is the OR of an ON clause is like using a UNION ALL with the query repeated and only leveraging one predicate of each OR case like so:
-- Gets the fathers
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON parents.id = children.fatherId
GROUP BY parents.id

UNION ALL

-- Gets the mothers
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON parents.id = children.motherId
GROUP BY parents.id


MySQL Workbench doesn't automatically refresh the tables content

This has nothing to do with MySQL Workbench. If you have an open transaction then no other client than that which owns that transaction can see changes made until everything is committed. I recommend to read a bit about database transactions.


How can I ensure that only one user at a time can call a stored procedure? Can I lock an SP?

If you are appropriately licensed (Advanced Workgroup or Advanced Enterprise edition -- or Performance Management Option, HT to @IanBjorhovde) you can use a DB2 Workload Manager threshold to accomplish something like that. First you create a service class for your procedure:

CREATE SERVICE CLASS sc_report

You then define a workload that will assign the appropriate activities to the service class:

CREATE WORKLOAD sp_workload
  CURRENT CLIENT_APPLNAME('SP_REPORT') ENABLE
  SERVICE CLASS sc_report

Here I'm identifying activities by the value of the client application name, which can be set by calling the system stored procedure WLM_SET_CLIENT_INFO() in the session, e.g.

CALL WLM_SET_CLIENT_INFO(null,null,'SP_REPORT',null,null);
COMMIT;

You can use other parameters, such as the user ID, client IP address etc. to identify the workload -- check the manual for CREATE WORKLOAD. After that you define the threshold:

CREATE THRESHOLD report_thresh
  FOR SERVICE CLASS sc_report ACTIVITIES
  ENFORCEMENT DATABASE ENABLE
  WHEN CONCURRENTDBCOORDACTIVITIES > 1 STOP EXECUTION

which says that, if an activity falls into the previously defined service class sc_report and there is already one such activity in the database, this new incoming activity should be stopped. The application will then receive SQLCODE -4712. Alternatively, you can define the threshold to queue concurrent requests instead of stopping them:

CREATE THRESHOLD report_thresh
  FOR SERVICE CLASS sc_report ACTIVITIES
  ENFORCEMENT DATABASE ENABLE
  WHEN CONCURRENTDBCOORDACTIVITIES > 1
  AND QUEUEDACTIVITIES > 10
  STOP EXECUTION

The above means that up to 10 concurrent activities matching the workload definition will be queued and executed one after another.

You will need to make sure that the workload definition identifies your report stored procedure and nothing else, because everything that falls into the defined service class will be subject to the threshold.


You don't need to GRANT only on a schema. You can get more specific and GRANT specifically to a table, like this:

GRANT ALL ON TABLE xyz.items TO admin_role;

but note that GRANT ALL and REVOKE ALL are very large sledgehammers for building very small spice racks.

Consider reviewing the excellent GRANT documentation which covers this in detail.

For your specific case, you could do something as simple as:

REVOKE CREATE ON SCHEMA public FROM admin_role;

which would remove the right to create new objects in the schema.


Extra column when migrating Sybase databases to single SQL Server database

Turns out SSMA just ignores any extra columns you create after migrating the schema - so I could set the default for the project_id columns before migrating data from each Sybase database and have them populated with those defaults.


sql server transactional - replication - the internal procedures often deadlock with user processes

Partitioning will likely not help, unless the user query in the deadlock can be rewritten in order to let partition elimination kick in.

I would focus on making both operations in the deadlock graph as fast as possible instead. This will involve ad-hoc indexing or rewriting the query in a different form.

If the replication agent is often the deadlock winner, you could consider lowering its deadlock priority, in order to let the user queries run without being killed. However, I would explore this option only as a safety net in case you're not able to reduce deadlocks to a very rare event.


You will probably want the postgis2_94.x86_64 package from the pgdg94 repository, because that is the official RPM package repository for the PostgreSQL project. It also includes a rich ecosystem of tools around PostgreSQL in addition to all of the currently supported PostgreSQL releases according to the Versioning Policy of the PostgreSQL project.


Simple way to use SP_EXECUTESQL for loop through all databases

With sp_ineachdb from here created in master. With this #temp table:

CREATE TABLE #temp
(
    Dbase varchar(100),
    Feature_Name varchar(100),
    feature_id int
);

The code is simply:

DECLARE @cmd nvarchar(max) = N'INSERT #temp
  SELECT db_name(), feature_name, feature_id
    FROM sys.dm_db_persisted_sku_features;';

EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;

Alternatively:

DECLARE @cmd nvarchar(max) = N'SELECT db_name(), feature_name, feature_id
    FROM sys.dm_db_persisted_sku_features;';

INSERT #temp EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;

There isn't really a way to execute a command across multiple databases without using some kind of loop. You could create a view, perhaps, across all the current databases:

USE master;
GO

CREATE VIEW dbo.AllPersistedSkuFeatures
AS
  SELECT 'db1', feature_name, feature_id FROM db1.sys.dm_db_persisted_sku_features
  UNION ALL
  SELECT 'db2', feature_name, feature_id FROM db1.sys.dm_db_persisted_sku_features
  UNION ALL
  ...

But there are multiple problems with this. You have to maintain this view to account for databases that get added, dropped, or renamed. You have to account for cases where any single database can be in any state other than online. And permissions. You could build code to generate the view (or a CTE) at runtime, but that will still require a cursor or a loop.

So if your goal is to simplify your runtime code, no problem. If your goal is to eliminate a cursor or loop at any step in the process, not likely.


How do I figure out how Postgres was installed on my Linux system?

If you are simply looking to start and stop it, and a service or systemctl module doesn't exist on your machine, you can use pg_ctl to start and stop the Postgres server.  You will also need to have the path to the data directory handy (typically, the folder that contains postgresql.conf and pg_hba.conf), and pass that in along with the -D flag:

pg_ctl -D /path/to/data_dir start|stop

If no data directory exists, you will either need further assistance to help you get an existing database cluster up and running, or you can use initdb to create a new database cluster


If you have applied the latest update for SQL Server 2012 - Cumulative Update 2 for Service Pack 3 as at the time of writing - and the issue persists, you should open a support ticket, or report the issue on Connect.

The underlying issue shares some common features with an existing report:

String summary - slow statistics creation on VARBINARY(MAX) column

...which might give you some ideas for a workaround.

For example, there is a small chance that creating statistics on a computed column casting the column to varbinary(200) might work. That cast may also explain why the auto-stats succeeds while you cannot create statistics manually.


Unfortunately, using the update_time column from INFORMATION_SCHEMA.TABLES only works for MyISAM tables. It does not work for InnoDB.

The most effective way to get the last time a table was written is to rely on the OS. You must check the timestamp of the .ibd or .MYD files within a database folder.

I wrote earlier posts about how to do this:

Dec 21, 2011 : Fastest way to check if InnoDB table has changed
Apr 04, 2013 : How to check which tables in DB (MYSQL) updated in last 1 hour / last 1 minute?
Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?
Sep 25, 2014 : want to find out which databases are used in last 30 days or not
Jan 16, 2015 : How can I determine which Innodb table is being written?


SQL Server table with 4,000,000 rows is 40GB

You have 4,000,000 rows and one of the columns averages 8,000 characters (16,000 bytes, I assume).

SELECT CONVERT(bigint,4000000) * /* b */ 16000 / /*kb*/ 1024 / /*mb*/ 1024;
------
61,035

If your stats are accurate, I'd expect this table to be 61 GB. (I wonder if you mean 8,000 bytes, not 8,000 characters, in which case I'd expect > 30 GB.)

Other factors include:

data in other columns I didn't account for in the above calculation
additional indexes
fill factor > 0 and < 100
fragmentation and space still occupied by deleted rows or page splits


Unexpected Seq Scan when doing query against boolean with value NULL

Generally, col IS NULL is a possible candidate for a (default) b-tree index search. The manual:

  Also, an IS NULL or IS NOT NULL condition on an index column can be
  used with a B-tree index.

To get proof, disable sequential scans (in a test session only!):

SET enable_seqscan = OFF;

I quote the manual here:

  enable_seqscan (boolean)

  Enables or disables the query planner's use of sequential scan plan types.
  It is impossible to suppress sequential scans entirely,
  but turning this variable off discourages the planner from using one
  if there are other methods available. The default is on.

Then try again:

EXPLAIN ANALYZE SELECT * FROM tbl WHERE auto_renew IS NULL;

This will probably result in a bitmap index scan that is slower than a sequential scan on the table.

Reset, or close the session (the setting is session-local).

RESET enable_seqscan;

Indexes on boolean columns are only useful in certain cases. The planner only uses an index if it expects that to be faster. Calculations are based on your cost settings and the statistics gathered by ANALYZE. If a sizable portion of the table matches your condition (around 5% or more, it depends), it is typically faster to do a full table scan instead.

This leaves the rare value in a boolean column as the only useful candidate for a plain index. And it is typically more efficient to create a (more specialized) partial index instead for this - which is cheaper to maintain, smaller, faster and used more readily if query condition match.

If you have lots of queries looking for rows with auto_renew IS NULL and the NULL case is not very common (and / or you need a certain sort order) then this index would help to find / sort these rows quickly:

CREATE INDEX index_tbl_tbl_id_auto_renew_null ON tbl (tbl_id)
WHERE auto_renew IS NULL;

The condition of the partial index has to be repeated in the WHERE clause of a query more or less exactly to make the query planner realize the index is applicable.

The indexed column (tbl_id) is an arbitrary pick. The important part is the WHERE clause. This particular index would be most effective for queries with ORDER BY tbl_id or an additional filter or join on tbl_id. You could make it a multicolumn index. Boolean columns are often more useful in combination with others.

Aside: ORMs are crutches that regularly fail to get the full potential out of your RDBMS.


Errors encountered while Dump/Restoring PostGIS DB from Postgres on EC2 into RDS

I managed to resolve these errors by doing 3 things:

1. Removing all definitions that are already defined in the PostGIS extension from the database dump

All of the function, operator family, and operator class definitions are already included in the new database because I installed PostGIS on the new database. This is also true of the spatial_ref_sys table in the public schema. So I avoided restoring this in my database dump using the following approach:

Create a table of contents list from my dump file with the following command: pg_restore -l -f out.txt backup.dump

Then I edited that out.txt to remove the function definitions. After that file was edited, I imported my dump using the updated out.txt as follows:

pg_restore -v -L out.txt -x -U my-user -d my_db -e --single-transaction ms.pgis-dump

I found out about this approach here: https://stackoverflow.com/questions/9715434/how-to-exclude-pl-pgsql-functions-in-export/31043192#31043192

2. All of my database's tables were created with the postgres admin user, so I migrated them to a regular users.

First I created my new user, then update the owner of my database to the new user:

create role my_user with password 'testuser' login;
grant postgres to my_user;
ALTER DATABASE my_db OWNER TO my_user;

Then I had to migrate all of my tables, views, and sequences to have the new user.

# Migrate tables:
for tbl in `psql -qAt -U my_user -h localhost -p 5432 -d my_db -c "select tablename from pg_tables where schemaname = 'public';" my_db` ; do  psql -U my_user -h localhost -p 5432 -c "alter table \"$tbl\" owner to my_user" -d my_db ; done
# views:
for tbl in `psql -qAt -U my_user -h localhost -p 5432 -d my_db -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';"` ; do  psql -U my_user -h localhost -p 5432 -d my_db -c "alter table \"$tbl\" owner to my_user"; done
# sequences:
for tbl in `psql -qAt -U my_user -h localhost -p 5432 -d my_db -c "select table_name from information_schema.views where table_schema = 'public';"` ; do  psql -U my_user -h localhost -p 5432 -d my_db -c "alter table \"$tbl\" owner to my_user" ; done

I found out about these scripts here:

https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql
https://stackoverflow.com/questions/16959764/reassign-owned-by-for-1-specified-database?noredirect=1&lq=1

3. I had to avoid restoring user privileges:

I was getting error calling REVOKE commands on the existing postgres user. So I added the -x flag to my pg_restore command to prevent restoring privileges to the admin user.

Details were outlined here:

https://stackoverflow.com/questions/37271402/pg-restore-error-role-xxx-does-not-exist


How to know which user update/insert or delete in MYSQL database?

Depends on what version you are.

You can install the audit_plugin:

INSTALL PLUGIN server_audit SONAME 'server_audit';

You can choose two outputs, file or syslog. The output format will be:

20140901 15:19:44,localhost.localdomain,root,localhost,4,133,WRITE,employees,salaries,
20140901 15:19:44,localhost.localdomain,root,localhost,4,133,QUERY,employees,'DELETE FROM salaries LIMIT 100',0

WARNING NOTICE

If you enable the audit_plugin (it's right with all logging processes like SLOW LOW, GENERAL LOG...) be careful to not saturate your disks in term of I/O. Depends of your workload, prefer write these types of logs on dedicated disks to minimize the impact on your MySQL instances. You can also throw them to syslog but you should have the architecture to handle them (ELK for instance).

Max.


Calculating the FileStream Data Columns Length matching used FS Drive used space

Space for each file on the file stream volume is allocated in units of the file system allocation unit (blocksize). When the actual data size is not an exact multiple of the allocation unit size, allocated space is rounded up to the next allocation unit size multiple.

The PowerShell command below will show allocation unit size for each volume on the server:

Get-WmiObject `
    -Query "SELECT Name, Label, Blocksize FROM Win32_Volume WHERE FileSystem='NTFS'" `
    | Select-Object Name, Label, Blocksize;

Using the returned blocksize value from the above command, you can determine the actual space allocated for each file stream file with a query like:

DECLARE @AllocationUnitSize decimal(10,0) = 65536;
SELECT
      CEILING(DATALENGTH(file_stream)/@AllocationUnitSize)*@AllocationUnitSize AS AllocatedSpaceBytes
    , DATALENGTH(file_stream) AS UsedSpaceBytes
FROM dbo.YourFileStreamTable;

The amount of unused allocated space will be significant for small files and a large allocation unit size. For example, consider that an actual file size of 1K will require 64K with that AU size, wasting 63K of storage per file.


Multiple copies (cp) with Oracle ASM asmcmd tool?

Since no platform or version is mentioned, I assume Unix/Linux and 11gR2.

First of all, check Running ASMCMD in Noninteractive Mode. This explains that we can use ASMCMD in a script so whatever we can dream of, we can script. In this case in order to copy files we can use

asmcmd command options

A simple example is shown here Running ASMCMD commands in a script
Change this example slightly to get it to copy all files in a local directory, also using this Using the ASMCMD cp command as input:

ls | while read FILE
do
   asmcmd cp $FILE +data/orcl/datafile/$FILE
done

I am not sure how smart it is to run multiple copies in parallel. It all depends on what your hardware can handle. If you want to run in parallel, run the copies in the background but be a little careful, when running too much in parallel, it will definately cause a slower throughput than running in a sequential way.

For windows it will be slightly different, the loop construct will be different.

I hope this helps.


This is an open-ended interview question that doesn't have a precise answer. The interviewer might expect you to ask additional questions for clarity.
Actual space requirements will depend on what is meant by "processed" (inserted?), data retention (are data ever deleted?), column data types, indexes, storage overhead, use of compression features (including columnstore), plus additional free space for routine maintenance.


Does spreading table data across multiple files (in the filegroup) improve performance?

As noted by the other users this doesn't improve performance.  It does provide two important capabilities.

You can add space and IO capacity to a database without moving it by adding a new disk and adding new files to the filegroup and placing them on the new disk, or by moving some of the existing files to the new disk.

You can restore the database on to a server that doesn't have a volume large enough for the entire database by spreading out the files across multiple disks.

And

because the rows will be spread across the files and so to go from one row to the next row which maybe on another file, wont it take a hit on performance?

No.  SQL Server always reads at least an 8KB page; it never reads a single row from disk.  Whether the "next" page is in the same file or not matters little.  And SQL Server will allocate space to an object using 8-page Extents, so when reading sequentially, you'll typically read at least 64KB from each file before reading from the next.
To support old-fashoned data warehouse configurations built on simple arrays of spinning disks there is a startup parmeter -E that "Increases the number of extents that are allocated for each file in a filegroup.", but on a modern storage solution, it's rarely used even in DW configurations.


Is the Service Broker good for publishing data between servers?

Your best bet is to use Transactional Replication.

T-Rep is meant to be suited in your scenario as you want to replicate a table from server A to server B.

No need to run a store procedure every min (unless you are doing something specific with these SP's which you have not highlighted in your question).

Note: Only when you generate an initial snapshot in transactional replication, there will be locks on the table. So better to set up during maintenance window if your table is very large.

Also, in T-Rep changes are tracked asynchronously and applied asynchronously to the subscriber (destination) server. It has Wizards to allow you to configure T-Rep correctly with no coding efforts.

Service Broker on other hand will track data changes synchronously and apply to the destination server asynchronously. It will require more coding and depending on the amount of data to be transferred, it will be slow.

I have dealt with Service Broker and trust me ... its very hard to debug .. just like a black box vs Replication will show you errors on GUI and is easy to troubleshoot as well.


Inserting to a table when everything is parameterized

// ok, stop, think. this can't be right.

Indeed, there's a better, cleaner, safer, faster way.

You have a DataTable in memory; you can "dump" it as-is to the destination table using a SqlBulkCopy object:

public override void CreateNewOutputRows()
{
    using (var records = new DataTable())
    using (var adapter = new OleDbDataAdapter())
    {
        adapter.Fill(records, Variables.RemoteData);

        var bulk = new SqlBulkCopy(_connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null);
        bulk.BulkCopyTimeout = 0;
        bulk.DestinationTableName = Variables.StagingTableName;
        bulk.WriteToServer(records);

        _transferredRows = records.Rows.Count;
        Output0Buffer.SetEndOfRowset();
    }
}

That fixes the first problem - you indeed don't need to be bothered with individual rows and/or column names and types, even less to build/concatenate an individual INSERT statement for every single row in the data source.

The other problem to solve is to get each iteration to run concurrently. I believe you could extract the body of the Foreach loop container into its own parameterized SSIS package, and replace it with an out-of-process Execute Package Task.


You are on the right way.
Column RETURNCODE in DBA_AUDIT_TRAIL or RETURN_CODE in UNIFIED_AUDIT_TRAIL view.

http://docs.oracle.com/database/121/REFRN/GUID-A9993FAC-12D3-4725-A37D-938CC32D74CC.htm#REFRN23023

  This view is populated only in an Oracle Database where unified
  auditing is not enabled. When unified auditing is enabled in Oracle
  Database, the audit records are populated in the new audit trail and
  can be viewed from UNIFIED_AUDIT_TRAIL.

Oracle documentation is always a very good source of information.


How can I achieve conditional page breaks in SSRS 2016

Another workaround, which seems to work quite nicely - place a parent group of the group you want to selectively break on, then in Group Properties for that, group on a derived expression of either the group field, or a fixed value. And then tick the box for that grouping as per your screenshot.

e.g. Assuming a parameter called "BreakPages", and a grouping on a field called "MyGroupField", you group on:

=iif(Parameters!BreakPages.Value="Y", Fields!MyGroupField.Value, "None")

You don't need to display any fields in this special group, it'll simply use the page break setting as per your requirements..


MongoDB Disk Usage with Capped Collections

You have basically allocated space by defining two capped collections, the oplog and the second capped collection in MyDatabase.

Unless you specify the oplogSize, the oplog will be allocated at 5% of free space on the volume containing the MongoDB data (so I am guessing you had ~170GB free).

The second capped collection size would have been defined by you, and then the _id index would be created and grown in addition to the initial allocation as you added data.  The files also contain the data from the other collections, as you mentioned.

The key thing to remember here, is that capped collections pre-allocate the entire amount specified in advance and never grow or shrink.  Besides the indexes that will be added on user defined capped collections (the oplog has no indexes), the capped collection will remain the same size regardless of how much data you put into it, whether that is 500MB or 500TB - think of it as a fixed-size circular buffer.

If you want to see this in action, try creating another capped collection of, say, 500MB in a different database called foo, you will end up with foo.0 through foo.4 (possibly foo.5 depending on the version) and have 960MB (or 1984MB, again depending on version) of files on disk. That represents the minimum needed to contain a capped collection of that size.


The restored database will have the same file size as it had when the backup was produced.
I sometimes call the files "containers". Same number of containers, and same size. They don't have to be at the same location, though (the MOVE option of the RESTORE command).
I don't know what "initial" you refer to above, but whatever it is, it is probably misleading you - and it doesn't change the facts I mention above. :-)
So if you backup a database which has a 100 GB file, that file will be 100 GB when you restore form that backup. If you see something else, then you are probably in a parallel universe. ;-) OR you need to be extremely specific so we follow you to 100% of what is happening. One thing that can be useful here is RESTORE HEADERONLY and RESTORE FILELISTONLY. The later will tell you what size the files will be when you restore.


What is this expression changing process?

Broadly, this is known as standardization or normalization.

Examples of this include (for expressions) Conjunctive Normal Form and Disjunctive Normal Form.

There is no Microsoft documentation I am aware of that lists all the steps of the multiple processes involved. Essentially, SQL Server converts things into a standard format to make things easier for it to work with internally. This occurs at various times, including during parsing, and the normalization stage of query compilation and optimization.

As far as CAST versus CONVERT is concerned, it may simply be that CONVERT was first (and/or because it offers a superset of functionality). You should find that built-in (intrinsic) functions are generally preferred.


Can you set the output configuration at Query time?

No, there is no programmatic way (outside of what you suggested: writing a custom SSMS plug-in) to control SSMS (for the most part). Like most commands, SET commands are executed by SQL Server. The only control you could have locally is a subset of the SQLCMD commands -- :r, :setvar, :connect, and so on -- but none of those allow for configuring the UI / SSMS.

BUT, all hope might not be lost yet. Since you are giving your coworkers T-SQL templates to copy and paste and fill out, and since there are already short-cut keys to toggle between "Results to Grid" and "Results to Text", just add in a few helpful comments to direct them on how to execute the script. Something along the lines of:

/*    INSTRUCTIONS:

1) Control-Shift-M    (to fill out parameter values)
2) Control-T          (for Results To Text)
3) Control-E (or F5)  (to execute query)
4) Control-D          (for Results to Grid)
*/


We do 500 to 3000 inserts per second on a table, but in partitions of 1 month, and are monitoring the last 2 hours of activity. An index on the timestamp does the trick, most queries run less than a few milliseconds on our hardware.

But, we do have fast storage, many GB's of RAM and descent processors. On a single 7200rpm disk, this would be impossible.

Use EXPLAIN ANALYZE to see how your queries are executed and try to find the bottleneck. Storage is the slowest part of your system, and databases love lots of RAM.


How to launch SQL Server Agent job on completion of another one?

Use sp_start_job.

Put that command into a job step as needed. No need to worry about the duration of previous jobs.


How to add in carriage-return/line-feeds without starting a new row in a tabbed txt SQLite import

In general, fields containing newlines should be enclosed in double quotes:

123,test,456
234,"test with
multiple
lines", 567

When using quoted fields, any quote inside the field has to be escaped by another quote (or a \, unix style), and the field itself has to be quoted too:

123,"24"" monitor",456
234,"test with
multiple
lines", 567
345,no quotes needed,678


If you solve it by adding a secondary index then imho MyISAM or InnoDB won't matter much, as both use B-Tree indexes.

Append both additional columns to the index: (col3, col1, col2) or (col3, col2, col1) should behave the same for your query.

The reason to add all the columns is that such index covers your query - all data needed to resolve the query are contained inside the index, so it is not needed to read the actual rows from the table. That means the data you want to fetch are grouped together on disk (the index is ordered/clustered by col3) and no random reads to the "main" table are needed.

If you had index on only (col3) then each row may be stored in a different part of the table (depending on your primary key or order of inserts) and it would randomly jump all over the place, incurring IO penalty.

If you have no primary key, then MyISAM would be probably better, as InnoDB would create internal primary key and the table would take more space than needed.

But there is a possibility to solve it without using any secondary key. Use InnoDB table like

create table t (
   col1 varchar(5),
   col2 varchar(5),
   col3 varchar(32) not null,
   col4 smallint unsigned not null auto_increment,
   primary key(col3, col4),
   key(col4)
) engine = innodb;

That way the table will use InnoDB feature of clustering by primary key - effectively forcing the optimal order and grouping of rows described previously. And it will take just a small bit more space for the additional column (2 bytes only, if you are positive there will always be less than 60k rows) and much smaller secondary key to accomodate the auto_increment behavior instead of effectively having 2 copies of the table as with the other version.

Example: http://sqlfiddle.com/#!9/c36cfc/2


What replica in Availability Group should System Databases be backed up

Since they are independent of each other, backup all system databases, for each individual instance hosting a replica.


How to set indexes on a table with a FILESTREAM ROWGUID?

UPDATE: I found something of potential relevance:
http://msdn.microsoft.com/en-us/library/cc949109%28d=printer%29.aspx

They've got a similar example:

CREATE TABLE DocumentStore (
       DocumentID INT IDENTITY PRIMARY KEY,
       Document VARBINARY (MAX) FILESTREAM NULL,
       DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
              UNIQUE DEFAULT NEWID () ON Data_FG1)
ON DocPartScheme (DocumentID)
FILESTREAM_ON DocFSPartScheme;
GO

With a remark:

  Notice that to use the DocumentID column as the partitioning column, the underlying nonclustered index that enforces the UNIQUE constraint on the DocGUID must be explicitly placed on a filegroup so that the DocumentID column can be the partitioning column. This means that partition switching is only possible if the UNIQUE constraints are disabled before performing the partition switch, as they are unaligned indexes, and then re-enabled afterwards.

I suppose I'll then leave ROWGUID be as it is, a UNIQUE nonclustered index.


MariaDB Full Text Index - How to query old data as well

normally it would be build, when you added the index

But you can OTIMIZE TABLE yourtablename

  The fulltext index is only fully re-organized when an OPTIMIZE TABLE statement is run.


Have you tried setting log_rotation_size as described in http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html#GUC-LOG-ROTATION-SIZE ? That would work if the log is managed directly by postgresql. If you are using syslog then you should look at newsyslog or logrotate.


How to dump only the MySQL database structure?

if you are using MySQLdump and specify  location will be inside the directory you specified for example:

mysqldump  -u root -d  -P port -p password databasename > c:\dumpfile.sql

the dump location will be inside your machine under C drive

but if you did not specify  the directory the dump location will by inside MySQL\bin

mysqldump -u root -d -P port -p password databasename > dumpfile.sql


Where did these dbo._trace_ Stored Procedures come from?

Look at the stored procedure contents.
It they begin like the following example, they could be server side traces someone has created.

Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 100 Create the trace
exec @rc = sp_trace_create @TraceID output, 0, N’E:\demo\demotrace’, @maxfilesize, NULL
if (@rc != 0) goto error --Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 8, @on


You need to check to make sure the user indeed has the ssl field loaded.

Run this query;

SELECT A.* FROM
(SELECT user,host FROM mysql.user WHERE ssl_type IS NOT NULL) A
INNER JOIN
(SELECT user,host FROM mysql.user WHERE ssl_cipher IS NOT NULL) B
USING (user,host);

If nothing comes back, then look the user using

SELECT * FROM
(SELECT user,host FROM mysql.user WHERE ssl_type IS NOT NULL) A
UNION
(SELECT user,host FROM mysql.user WHERE ssl_cipher IS NOT NULL);

If nothing comes back from this second query, that means no user in mysql.user has ssl_type and ssl_cipher filled in properly.

Another thing you can investigate is this:

Did you add this to my.ini ?

[mysqld]
ssl

If you didn't, please do so and run this

C:\> net stop mysql
C:\> net start mysql


How to select distinct for one column and any in another column?

The easiest query to write is for MySQL (with not strict ANSI settings). It uses the non-standard construction:

SELECT key, value
FROM tableX
GROUP BY key ;

In recent version (5.7 and 8.0+) where the strict settings and ONLY_FULL_GROUP_BY are the default, you can use the ANY_VALUE() function, added in 5.7:

SELECT key, ANY_VALUE(value) AS value
FROM tableX
GROUP BY key ;

For other DBMSs, that have window functions (like Postgres, SQL-Server, Oracle, DB2), you can use them like this. The advantage is that you can select other columns in the result as well (besides the key and value) :

SELECT key, value
FROM tableX
    ( SELECT key, value,
             ROW_NUMBER() OVER (PARTITION BY key
                                ORDER BY whatever)     --- ORDER BY NULL
               AS rn                                   --- for example
      FROM tableX
    ) tmp
WHERE rn = 1 ;

For older versions of the above and for any other DBMS, a general way that works almost everywhere. One disadvantage is that you cannot select other columns with this approach. Another is that aggregate functions like MIN() and MAX() do not work with some datatypes in some DBMSs (like bit, text, blobs):

SELECT key, MIN(value) AS value
FROM tableX
GROUP BY key ;

PostgreSQL has a special non-standard DISTINCT ON operator that can also be used. The optional ORDER BY is for selecting which row from every group should be selected:

SELECT DISTINCT ON (key) key, value
FROM tableX
-- ORDER BY key, <some_other_expressions> ;


Unable to restore backup taken on one server onto another server

No backup set selected to be restored

As SqlBak Blog  Here the above error comes in two scenario.

  1)You are trying to restore your database to a lower version of SQL
  Server. If you make a database backup on a higher version, let’s say
  SQL Server 2012 and then you try to restore it on SQL Server 2004, you
  will get this error message: No Backupset Selected to be Restored.

  2)The backup file is unreadable or corrupted. You can simply check
  your database backup using the following command:

RESTORE HEADERONLY FROM DISK='D:/DatabaseBackupFileName.bak';

I think as my knowledge it's a incomplete error message.  Because most of the time along with Msg 3013, Level 16, State 1, Line 3, the operating system errors  (like Operating system Error 3,5 etc) or some  error come.

  Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating
  abnormally.

For this error i would recommend to Pinal Dave blog Here,Here and MSDN Blog Here

Even  Remus Rusanu answer also in dbastackstange related to Network drive with same error message Here


SQL Server to MySQL sync, using Workbench, FreeTDS, and iODBC on 10.9

Was trying to solve the same problem and stumbled here...  It's a year late, but better late than never...

OSX uses iODBC as ODBC layer, so FreeTDS must be compiled against iODBC for MySQL Workbench to connect.

Since I struggled with iODBC as well, what I ended up doing was to create a custom "library installation" to run this.

Steps are as follows:

Make sure you have XCode and command line development utilities installed.  You will compile lots of stuff!
Download and compile iODBC (I used 3.52 from www.iodbc.org).  Make sure you install somewhere "isolated" with "--prefix=/usr/local/iodbc" for instance;
Download and compile FreeTDS:

cd to freetds source directory;
type export CFLAGS="-arch i386"
type export LDFLAGS="-arch i386";
run config with ./configure --prefix=/usr/local/freetds --with-iodbc=<path_to_iodbc_in_step_1> --enable-odbc-wide
(MySQL Workbench NEEDS 32-bit archtecture; the exports will ensure that, since Mavericks - my OSX - is 64 bit.)

Download and install ODBC Administrator from Apple (https://support.apple.com/kb/DL895?locale=en_IS - version 1.0 worked for me);
Run ODBC Admin, go to "Driver" tab and create a "TDS" driver; Point "driver file" to /usr/local/freetds/lib/libtdsodbc.so (it should be there after step 3!)
Go to MySQL Workbench Migration Tools and choose as Connection Method "ODBC (FreeTDS)".  In "Driver", type the name you used in step 5 ("TDS") and fill the other info as needed.
"Test Connection" and go have a cup of your favorite drink before moving on! :)

One entire workday "invested", but it did work.

UPDATE

After my cup of coffee, I moved on with my test migration, only to find out my SQL Server connection was NOT actually connecting (correct user, correct password).  Another morning's work and here are the adjustments:

Make sure you compile iODBC to 32-bit architecture.  To do so:

use the same export you will use when compiling FreeTDS (CFLAGS,LDFLAGS);
use --prefix=/usr/local/iODBC32.  Make sure you compile to different directory or you may break your current ODBC install.

Make sure you compile your "32-bit" FreeTDS version pointing to the "right" iODBC with:

./configure --with-iodbc=/usr/local/iODBC32

This should do the trick.

Another piece of advice:

MySQL Migrations will use exec sys.sp_databases to fetch info about the schemas to be migrated.  If you're not using sa to migrate (which you probably shouldn't!), make sure your login has enough privileges on the master database (I myself created a "temporary" clone of sa to migrate); otherwise, MySQL Migrations will have trouble fetching and copying.

All these things followed and I finally completed a successful migration (minor manageable collation problems).

Hope this helps,


PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

If I run them serially, one right after the other, I'm expecting it will require 7 minutes to complete on average. Is this reasonable?

If they use unrelated data sets, then yes.

If they share a data set, and the cache is cold for the first query and the query is mostly I/O bound, then the second one might complete in moments. You need to consider caching effects when dealing with performance analysis and query timing.

  More than that, however, what if I run the two queries concurrently? Two separate connections at the same time.

"It depends".

If they were both using sequential scans of the same table then in PostgreSQL it'd be a huge performance win because of its support for synchronized sequential scans.

If they shared the same indexes then they'd likely benefit from each others' reads in to cache.

If they're independent and touch different data then they might compete for I/O bandwidth, in which case they might take the same amount of time as running sequentially. If the I/O subsystem benefits from concurrency (higher net throughput with more clients) then the total time might be less. If the I/O subsystem handles concurrency poorly then they might take longer than running them sequentially. Or they might not be I/O bound at all, in which case if there's a free CPU for each they could well execute as if the other wasn't running at all.

It depends a great deal on the hardware and system configuration, the data set, and on the queries themselves.

  Now, if I had 1,000 non-trivial queries running concurrently, I have a hunch that it would result in an overall slowdown. In that case, where would the bottleneck likely be? Processor? RAM? Drives?

Yes, that'd very likely slow things down for a number of reasons.

PostgreSQL's own overheads in inter-process coordination, transaction and lock management, buffer management, etc. This can be quite a big cost, and PostgreSQL isn't really designed for high client counts - it works better if you queue work.
Competition for working memory, cache, etc.
OS scheduling overhead as it juggles 1000 competing processes all wanting time slices. Pretty minor these days, modern OSes have fast schedulers.
I/O thrashing. Most I/O systems have a peak performance client count. Sometimes it's 1, i.e. it's best with only one client, but it's often higher. Sometimes performance decreases again above the threshold. Sometimes it just reaches a plateau.


Is it possible to do sums of aggregation that stops aggregating once the condition is reached?

If you don't care what order your rows that contribute to the sum is in:

select * from (
    select ...n sum(amount) over () as sm
    from ...
    where ...
) as x
where sm > 100

If you do care about the order, you should add info on how the cumulative sum is calculated:

select * from (
    select ...n sum(amount) over (order by ...) as sm
    from ...
    where ...
) as x
where sm > 100

You may even sumarise per something like:

select * from (
    select ...n sum(amount) over (partition by ...
                                  order by ...) as sm
    from ...
    where ...
) as x
where sm > 100

However, since you are talking about stopping, when the sum reaches something I suggest what you need is something like:

select * from (
    select ...n sum(amount) over (partition by ...
                                  order by ...) as sm
    from ...
    where ...
) as x
where sm <= 100


Kill all sessions for ONE user in MS SQL

There's not really a reliable way to pin sessions to a specific database (sysprocesses has this, but it relies on an active query's database context, and dm_exec_sessions has authenticating_database_id, but this relies on the connection string, login's default database, etc).

That said, this seems to meet all of your other requirements, attempting to kill all sessions using app_name, connecting as login_name, and who haven't issued a request in the last 5 hours:

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

SELECT @sql += N'KILL ' + CONVERT(VARCHAR(11), session_id) + N';'
FROM sys.dm_exec_sessions
  WHERE [program_name] = N'app_name'
  AND login_name = N'login_name'
  AND last_request_start_time < DATEADD(HOUR, -5, SYSDATETIME());

EXEC sys.sp_executesql @sql;

If all the app does is call that one stored procedure and never does anything else at all, you could perhaps look at DBCC INPUTBUFFER for each spid, but that becomes very tedious very quickly.


Move Complete ORACLE from one system to another

Check OS users and groups: oracle, oinstall, dba.
Check the contents of /etc/oratab and /etc/oraInst.loc files to verify if they point to an expected location.
Check if these files are placed under the same paths:

init.ora, orapwd and similar
listener.ora, tnsnames and similar
control files
data files
redo log files
Oracle scripts such as oraenv (optionally - only if you use them), which are often placed in /usr/local/bin

Check if you retain the same IP.
Check if you retain the same DNS name.

And that's it, you are good to go.


Problems while connecting to a newly installed PostgreSQL 9.2 on same box as 9.1

You need to use

psql -p 5433

instead of

psql -h localhost -p 5433

because the default Ubuntu access control setup has password authentication over TCP/IP but peer authentication over Unix-domain sockets.


Why does SQL Server Management Studio hang (Not Responding) when connecting to Amazon RDS?

Sounds like the connection is successful but SSMS is having trouble loading metadata to populate Object Explorer. I would try opening SSMS without connecting to this server, open an empty query window, disable IntelliSense, and then change that query window's connection.

Also your copy of SSMS is quite old, I would strongly consider applying SP3 + CU1 to your workstation, or moving up to 2014 SP1 or even the new monthly previews. I don't know what build RDS is on, but you should always strive to have SSMS >= the highest version of server you have to manage.


Redundancy of information and algorithm to do lossless decompositions

Does the relation create information redundancy?

Yes, for instance you have the information on the file (name, size, data creation, etc.) repeated for different accesses to the same file.

  What would a decomposition of R without loss of information and without loss of dependency be?

The following decomposition is both in Third Normal Form and in Boyce-Codd Normal Form and is without loss of data and loss of dependencies. It can be obtained by applying both the “analysis algorithm” to produce the BCNF, and the “synthesis algorithm” to produce the 3NF, but note that in general only the latter is guaranteed to avoid any loss of dependencies:

R1 < (D DC HC NF S),
{ D NF → DC
D NF → HC
D NF → S } >

R2 < (A D L NF),
{ D L NF → A } >

R3 < (D DA HA L NF TA),
{ D DA HA L NF → TA } >

Any good database book will describe both the algorithms.


Oracle SQL: Why are column headers missing?

Heading may be disabled in your environment. Enable it (it is enabled by default).

SQL> select banner from v$version;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> show heading
heading OFF
SQL> set heading on
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Or pagesize is set 0:

SQL> show heading pagesize
heading ON
pagesize 14
SQL> set pagesize 0
SQL> select banner from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> set pagesize 14
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production


Oracle 12c Installation in Windows 7 error INS-30131

I found the problem. For reasons I don't understand Oracle needs access to the file system through the administrative network share (i.e. \computer\c$) and I had disabled it.

Things to check to solve this issue:

Remove your computer from workgroups.
Set HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System DWORD value LocalAccountTokenFilterPolicy to 1
Set HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\LanmanServer\Parameters DWORD value AutoShareWks to 1


SQL Server: I need to estimate execution time of a SELECT .. INTO .. statement

On SQL Server 2008 R2, SELECT...INTO queries are not eligible for parallelism (this was introduced in SQL Server 2014).  So the increased CPU count in production will not help your overall runtime unfortunately.

You could re-run your test with a pre-created BACKUP_TABLE, and then use INSERT INTO...SELECT to see how parallelism affects the test.

Regardless of parallelism, since you're copying a 220 GB table, it's likely that your main bottleneck is disk speed.  Specifically writing all of this to the transaction log.  I'd check to make sure that you aren't going to experience file growth events (pre-grow the production log file to whatever the size of the test instance ended up at).

I'd run your test again and measure wait stats to see how much you're hitting WRITELOG waits.  You can use Paul Randal's scripts for that:

Capturing wait statistics for a period of time

Run this multiple times while the insert is running, for 30, 60, or 90 second intervals for example, to get a feel for where the bottlenecks are.

You mentioned that your test was run on a system with no other concurrent activity against this table.  The other thing that might affect your test is if the SELECT INTO query gets blocked by other processes.


PostgreSQL visibility of records changes from other transaction inside the current one

I was assuming that these new records will not be visible to the current transaction, but proved wrong on testing.

That's not the case in the default READ COMMITTED isolation. Changes from committed transactions become visible at the start of the next statement in a transaction. Each statement still has a snapshot, so you can't have rows appear within a statement.

You can use SERIALIZABLE isolation to get that behaviour, but it has a performance impact and can lead to table bloat on fast-changing tables.

See the documentation on transaction isolation to understand what is going on.


Dangers in dropping all tables in a schema with CASCADE

run it in a transaction, you'll get warnings for every cascading drop, ROLLBACK if don't like what you see.


Why is my auto-restore procedure not dropping the databases when I expect it to?

What's probably happening is that another session is connecting to the database after you set it to SINGLE_USER.  Always connect to a database before you set it to SINGLE_USER, or you can't guarantee that you will be the single user.  But you can't drop a database you're connected to, so SINGLE_USER is just not the right state.

While setting the database OFFLINE will work, be aware that dropping an OFFLINE database does not delete the database files.  Then you won't be able to RESTORE because the database files are hanging around.  And you don't have rights to delete database files, by default, and shouldn't really need them.

RESTRICTED_USER is probably the best state to put a database ahead of dropping it.  eg

  RESTRICTED_USER

  Allows for only members of the db_owner fixed database role and
  dbcreator and sysadmin fixed server roles to connect to the database.
  RESTRICTED_USER doesn't limit their number. Disconnect all connections
  to the database using the timeframe specified by the ALTER DATABASE
  statement's termination clause. After the database has transitioned to
  the RESTRICTED_USER state, connection attempts by unqualified users
  are refused.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017

use master
alter database foo set restricted_user with rollback immediate
drop database foo


Why do we have SHRINKDATABASE command in SQL Server

First and foremost lets distinguish between the need for SHRINK and it's well known unintended side effect: possible increase of fragmentation. The later is an implementation side effect. In fact is perfectly possible to design a shrink that would reduce fragmentation (ie. has the same effect as a REORGANIZE) at the cost of significantly longer runtime.

The need for shrink is a perfectly valid one: reduce the size of a database that has grown for whatever reason. Whether is reduce the size after a massive one-time data delete, reverse the effect of a one time operation that caused unexpected size growth (eg. a bad import), migration to a much improved (and much smaller) data model, clean the fallout of some previous bad DBA management, they're all perfectly valid reason for shrinking a database. The cost of shrink (increased fragmentation) must be considered, of course. But consider a case when a badly design DB of 200Gb is reduced to just 20gb of data. Shrinking it to 50Gb size and rebuilding the indexes makes perfect sense. In other words the capability to shrink a database is needed.

What is frowned upon is the abuse of shrink:

regular scheduled shrink
manual shrink for unfounded reason ('I need to free some disk space for ...'), when often the database actually needs that size and will grow back overnight
shrink for small yields (eg. reduce 200Gb by 5Gb)

As for the LOG shrink, the problem is even more direct: LOG can grow even on a well maintained system. Clear examples: transactional replication or DB mirroring deployments that run into problems with the distribution DB or the mirror partner. Until the problem is solved, the log must be retained by the publisher (for TX) or primary (for DBM). When the issue is finally resolved and the pinned log is cleared, you will end up with an unnecessary large log. This can and should be shrinked back to operational size. Shrinking the log is somehow seen as VooDoo ('I tried it and did not do anythin!', 'Hmm, I tried it and it worked now') but is easy once you understand that in order to shrink the log the tail of the log must be free  which implies that, counter intuitively, more log has to be generated (and freed) before the file can be shrunk. Is all explained in How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005


What is the privilege my user requires to be able to grant CONNECT, RESOURCE and CREATE SESSION to other users in Oracle 11g?

If a system privilege is granted with ADMIN OPTION to a user, this user can grant this system privileges to other users, e.g. after

GRANT CREATE SESSION TO SCOTT WITH ADMIN OPTION

Scott is able to grant CREATE TABLE to other users. He is also able to grant CREATE TABLE to other users WITH GRANT OPTION. WITH GRANT OPTION works for roles too.

An object privileges (like a select on a table) must be granted WITH GRANT OPTION to a user to grant it to another user.

GRANT SELECT ON HR.EMPLOYEES TO SCOTT WITH GRANT OPTION

This can be found in the SQL Language Reference


Is IF ELSE better in this stored procedure than a Parameter?

Testing
I changed the inner join with TABLE C to a LEFT JOIN so our data does not get filtered out by the INNER JOIN condition (JOIN  DBO.TABLEC C ON @complicatedQuery=1 when @complicatedQuery = 0 ) as an INNER JOIN ON 1=0 AND SomeOtherCondition returns zero rows.
Unless that is, what you want, but then calling the original or conditional procedure
with @complicatedQuery = 0 will return different results. (Among other remarks)
Test data at the bottom
Procedures
Original procedure example with left join
Create PROCEDURE dbo.ProcOriginal
   @complicatedQuery bit
AS
BEGIN
   SELECT A.VAL FROM
     DBO.TABLEA A
     JOIN  DBO.TABLEB B on A.B_ID =B.ID
     LEFT JOIN  DBO.TABLEC C ON @complicatedQuery=1 and C.A_ID = A.ID
END

Conditional procedure example, also with left join to keep it logically the same
Create PROCEDURE dbo.ProcConditional
       @complicatedQuery bit
    AS
    BEGIN
       IF @complicatedQuery =0
        BEGIN
        SELECT A.VAL FROM
        DBO.TABLEA A
        JOIN  DBO.TABLEB B on A.B_ID =B.ID;
        END
        IF @complicatedQuery =1
        BEGIN
        SELECT A.VAL FROM
            DBO.TABLEA A
            JOIN  DBO.TABLEB B on A.B_ID =B.ID
            LEFT JOIN  DBO.TABLEC C ON C.A_ID = A.ID;
    END
END

Running them with parameter = 0
SET STATISTICS IO, TIME ON;
EXEC dbo.ProcConditional 0
EXEC dbo.ProcOriginal 0

The estimated execution plan's differ the most, the actual query plan's less.

We can see why the estimates are different, based on a part of the estimated execution plan from ProcOriginal :

While in reality, 0 rows are returned.

We can improve this by adding this index:
CREATE INDEX IX_A_ID
ON DBO.TABLEC(A_ID);

Causing no table access

But, the estimated rows are now at 2.5K, even though the variable eliminates the table access.
This can still be a potential problem due to increasingly higher memory grants if the join between A and B returns more results.

The ProcConditional actual execution plan seems ok.

A NC Index on B_ID on TableA could improve both queries.

Running them with parameter = 1
EXEC dbo.ProcConditional 1;
EXEC dbo.ProcOriginal 1;

The only difference here is the extra filter operator with a startup expression predicate as seen earlier @complicatedQuery=1 or 1=1 .

Filter

Added due to
ON @complicatedQuery=1 and C.A_ID = A.ID

All this due to SQL Server not knowing what @complicatedQuery will be at runtime.
You could remove this filter operator by adding an OPTION(RECOMPILE) to the procedure, making a new plan to be created on each execution.

Which is better
I do think that the conditional method would be the better choice between the two.

Another method
Using Dynamic SQL to build the strings depending on @complicatedQuery  and then executing them directly.
CREATE PROCEDURE dbo.ProcDynamic
   @complicatedQuery bit = 0
AS
BEGIN
DECLARE @CMD nvarchar(1000)

    SET @CMD =
    'SELECT A.VAL FROM
    DBO.TABLEA A
    JOIN  DBO.TABLEB B on A.B_ID =B.ID'

    IF @complicatedQuery =1
    BEGIN
    SET @CMD += ' LEFT JOIN  DBO.TABLEC C ON C.A_ID = A.ID;'
    END
    ELSE
    BEGIN
        SET @CMD+= ';'
    END
EXEC SP_EXECUTESQL @CMD
END

When working with dynamic sql, you do have several threats and considerations, but without working with parameters added to the dynamic string you should be ok.
For further reference on dynamic sql, check out this walkthrough by Erland Sommarskog.

Test data
Small A table, bigger B table (1M) and 6x bigger C table
CREATE TABLE DBO.TABLEA(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,B_ID INT, VAL VARCHAR(255));
INSERT INTO DBO.TABLEA(B_ID,VAL)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) , 'val'
FROM MASTER.dbo.spt_values;
--2540

CREATE TABLE DBO.TABLEB(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, VAL VARCHAR(255));

INSERT INTO DBO.TABLEB(VAL)
SELECT top(1000000) 'valB'
FROM MASTER.dbo.spt_values spt1
CROSS APPLY MASTER.dbo.spt_values spt2;
--1000000

CREATE TABLE dbo.TABLEC(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, A_ID  INT, VAL VARCHAR(255))

INSERT INTO DBO.TABLEC(A_ID,VAL)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), 'val'
FROM MASTER.dbo.spt_values spt1
CROSS APPLY MASTER.dbo.spt_values spt2;
--6451600


Using HASHBYTES() yields different results for nvarchar and a variable

If you want a quoted string to be NVARCHAR (treated as Unicode), you need to prefix it with N.

DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'

SELECT HASHBYTES('SHA1', N'password5baa61e4c9b93f3f0682250b6') AS NVARCHAR_INPUT,
       HASHBYTES('SHA1', @hash) AS VARIABLE_INPUT

This will show matching hashes.

NVARCHAR_INPUT                               |VARIABLE_INPUT
0xCF01AF0DCECF41BA0106A264666544C2590A4660   |0xCF01AF0DCECF41BA0106A264666544C2590A4660

The prefix isn't necessary here because it's declared as NVARCHAR and the string will be converted.

DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'

Though I've run into some problems with that, so I'll usually make it clear:

DECLARE @hash NVARCHAR(MAX) = N'password5baa61e4c9b93f3f0682250b6'

If you want some explanation of why they can be hashed differently, start with this example:

DECLARE @n NVARCHAR(1) = N'N'
DECLARE @v VARCHAR(1) = 'N'

SELECT DATALENGTH(@n), DATALENGTH(@v)


How to prevent a login from 'listing' a table or view definition in PostgreSQL?

The documentation to the rescue:

UPDATE
[...]
For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.

This means, the following would satisfy your requirements, unless alice tries hard enough:
CREATE SCHEMA invisible; -- no privileges granted on this schema to alice or public

CREATE VIEW invisible.iamnothere AS SELECT 'something not very interesting'::text AS data;

GRANT SELECT ON TABLE invisible.iamnothere TO alice;

Now alice will see the following (using psql, and hopefully it is the same in other clients, too):
SELECT * FROM invisible.iamnothere;
              data
────────────────────────────────
 something not very interesting

\dt invisible.*
No matching relations found.

If you are serious
...about preventing your user from seeing other objects, it will be slightly more complicated.  You have to handle the notice in the docs about system tables access.
First, do
REVOKE ALL ON ALL TABLES IN SCHEMA  pg_catalog FROM public, alice;

This, as it suggests, can be disruptive to anyone who is able to browse the schemas through implicitly being a member of public (all roles are members of the latter).  To restore the status quo for them (but not alice), you can do:
CREATE ROLE object_browser;

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO object_browser;

GRANT object_browser TO bob;

After this, bob will be able to list stuff, while poor alice is confined to querying that single view.


Save data to variable and use it in stored procedure

SELECT CASE WHEN T.Date IS NULL THEN M.TEXT ELSE T.TEXT END TEXT
     FROM (SELECT TOP(1) *
              FROM MyTable
          ORDER BY Date DESC) M
LEFT JOIN MyTable T ON T.Date>= @userDate11

This is one of several ways to solve the problem.  The TOP(1) and ORDER BY Date DESC gives you the fallback record.  The original table aliased T is consigned as the optional partner in the LEFT JOIN.  This allows it to return no records, while still retaining the singular record from the derived table aliased M. The CASE statement then tests to see if we actually managed to get anything from T using WHEN T.Date IS NULL - if T is not empty, all the TEXT values will come from T, otherwise we will have just the single TEXT value from M, being the one for the MAX(Date).


MySQL LIKE IN with Input Parameter of stored procedure Comma Separated Values

Use REPLACE(SearchTowns, ',', '|') to change commas to pipes.
Put that result in, say, @regexp.
Construct, via CONCAT a single SELECT with

SET @sql = CONCAT('SELECT
  name,
  Town,
  FROM userLocation where Town REGEXP "', @regexp, '"');

PREPARE and EXECUTE the query.

That query should look like:

SELECT
      name,
      Town,
      FROM userLocation where Town REGEXP "orangi|faisal|nazimabad"

(You could manually run that to verify that the correct rows are delivered.)

Also, be sure to have case-folding COLLATION, such as utf8mb4_unicode_520_ci for Town.  (That is indicated by the trailing _ci.)

Code

DELIMITER //
CREATE PROCEDURE `GetUsersByTown`(
    IN `SearchTowns` VARCHAR(200) -- `ORANGI,FAISAL,NAZIMABAD`
)
BEGIN
IF (SearchTowns IS NULL) THEN
    SELECT name, Town
        FROM userLocation;
ELSE
    SET @towns = REPLACE(SearchTowns, ',', '|');
    SELECT name, Town
        FROM userLocation
        WHERE Town REGEXP (@towns);
END IF;
END //
DELIMITER ;


Is it Ok to use GUID as the Foreign Key?

Using a UUID column as a foreign key is fine, and if that is the only candidate key on the parent record recommended/required. The FK must refer to a value that is unique in the referred table, this usually means the primary key though any column (or combination of columns) defined as unique by way of a constraint or index will do. Some suggest that it should be something that is always populated too (i.e. defined as not nullable), otherwise not all the rows in the referred table can be referred to by the FK, though there are many examples where this is not correct.

If the primary key on the referred table is a uniqueidentifier column then unless you have another unique value in that table you should use that as your foreign key target.

The only real reason that people might recommend against UUIDs as foreign keys is space: such values take 16 bytes to store instead of 4 (assuming the alternative is a 32-bit integer) or 8 (for a bigint).

There are arguments against using UUIDs as primary keys (though there are equally valid arguments for too!) especially if your PK is also your clustering key (i.e. the only member in or a significant member of the table's clustered index if it has one), but that is a different discussion.


I'll get to the answer shortly, but first I should say I'm not sure why it is that you are trying to remove a "master role" from a host. Basically, a host is not configured "as master". Slaves just connect to it, which makes it a master.

I'm just curious: is this a security issue or something, that you want to forcibly prevent slaves from connecting to this host?

Anyway, one very easy way to do this is to remove the log-bin parameter, and restart MySQL. This will prevent MySQL from generating binary logs. No binary logs means slaves have nothing to get from this host, hence it cannot be a master.

Otherwise you may try and control that in terms of privileges. I don't think this is a good idea because this slave of yours will normally replicate everything from its master, including the privileges set on the master. So setting the slave to have a different (stricter?) set of privileges means it's not entirely in sync with its master.

Oh, and you may just grant the replication privilege on a pre-defined set of hosts, that would do the trick as well:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.2.101' IDENTIFIED BY '123456';


Identifying the normal form that a certain relation complies with

All attributes are prime, they belong to at least one candidate key.

Therefore, the relation is - trivially - in 3NF.

If you look at the requirements for both 2NF and 3NF, the exceptions start with "every non-prime attribute ...". Since the relation has 0 non-prime attributes, these requirements are trivially covered.

About your analysis, the first parts are OK (finding the candidate keys, that the relation is not in BCNF and that it is in 3NF).

The last part, about 2NF, shows confusion. Check again the 2NF definition and how do you define partial dependency and transitive dependency.


Is it possible to Install two (2) Firebird instances of different names & ports (gds_db port 3050 & fbsvc port 3051) running as service?

Yes,  it is possible and it is covered in documentation and in lot of FAQs like the one at iBase.ru

For the example at my development box I have co-installed FB 2.1.5 Win32 SuperServer (at default port 3050/tcp) and FB 2.5.2 Win64 Super-Classic (at a custom 3064/tcp)

There might be troubles with FB 1.x as it was using registry, but FB 2.x was made isolated and self-dependent.

One option is to download ZIPs and unpack them to different folders.  Then you have to run text window of Windows Command Prompt "As Administrator",  go into "bin"  subfolder and there are all those executables like server itself.  There also is "inst_svc" tool.  Running it with an option like "-?" would show you brief help. Focus on installing main service,  not installing Guardian (only needed on Win98, only shipped for legacy uniformity)  and giving non-default "instance name". Then go outside "bin"  and open "firebird.Conf" with any text editor like notepad. The documentation is within that file how to set non-default TCP port. That's all. Do the same for your another FB folder and you've done it.

Another option is to run two installers.  The 1st one would do all the described above things automatically. The second one would unpack files -  and ask you to do those configuring operations for the second copy manually. Just do it like described above.


This thread answers my question..

When a DML statement is currently being executed in a session the open_tran and @@trancount is always at least 2.

For example

Connection 1

CREATE TABLE T( X INT PRIMARY KEY)

GO

BEGIN TRAN

INSERT INTO T VALUES(1)

WAITFOR DELAY '00:01'

ROLLBACK

Connection 2

INSERT INTO T VALUES(1) /*Blocks on Connection 1*/

Connection 3

(look at blocked connection 2)

SELECT lastwaittype,
       open_tran
FROM   master..sysprocesses
WHERE  spid = <spid of connection 2>

Results

+----------------------------------+-----------+
|           lastwaittype           | open_tran |
+----------------------------------+-----------+
| LCK_M_X                          |         2 |
+----------------------------------+-----------+


Piecemeal restore, filegroup and file extension

File extensions are meaningless to the application.  For example, create a text file and name it textfile.foo and open it in Notepad.  Notepad doesn't care what the filename is--it just opens it because you told it to open it.

SQL Server works the same way.  You can name the files with any extension you want and it doesn't change how SQL Server uses them.  The first data file, which is typically the one with the .mdf file, will always hold the metadata and can never be removed.  The metadata will not be spread out to other files just because you gave them an .mdf file extension.

File extensions are almost always for the humans--so that we can more easily keep track of things.  Accordingly, we use the .mdf extension on the first data file and .ndf on additional data files so that humans will be able to easily tell which file contains the metadata and can never be removed from the database.

I'm sure there are some applications out there that require file extensions to meet certain requirements, but SQL Server isn't one of them, at least not in regard to the file extension on database files.


You might not consider it as "easy", but I've done it a few times via the following method, which had the virtue of being easier than the alternatives of checking every setting in BIDS.

In BIDS you can create a New project and import it from your existing DB1 (and then DB2.)  Now you have Solution folders/files for each DB.  You can use WinDiff to compare the folders and XML files within them and sync them by hand, if you're bold enough and/or the differences are small enough. You have to use some intelligence and intuition which object IDs you may need to copy/change and which to leave alone.

If you can't get that to work, the WinDiff-generated XML differences should at least remind you and/or point you to what has been changed in BIDS and you can make changes to the DB2 project files by hand via the normal BIDS UI.


DB2 9.5 How to get tablespace monitor information?

Use the TBSP_UTILIZATION administrative view - Retrieve table space configuration and utilization information. See the "Essential DB2 Health Check" for a recommendation on how to use it, especially the sections on File System Free Space and DMS Tablespace Free Space.

Example:

select substr(tbsp_name,1,10) "Name", tbsp_utilization_percent "Used%", tbsp_free_pages "FreePages", tbsp_usable_Pages "UsablePages", tbsp_used_pages "UsedPages", tbsp_page_top "HWM", tbsp_auto_resize_Enabled "AutoResize 1=Yes", tbsp_increase_size "Increase Size", tbsp_max_size "MaxSizeBytes -1=ulimited", tbsp_last_resize_failed "ResizeFailed 1=Yes", tbsp_page_size "PgSize Bytes"
from SYSIBMADM.TBSP_UTILIZATION
where tbsp_type = 'DMS';

Specifically, you are after the tbsp_increase_size field from the SYSIBMADM.TBSP_UTILIZATION table, where tbsp_type = 'DMS' as show in the query above. (The query has a few other things that are helpful in it too.)


SQLITE: Multiple foreign key referenced to multiple table cascade delete

You can use on cascade delete

Example

Turn on FK Support

PRAGMA foreign_keys = ON

Create sample data

CREATE TABLE TableA
(
id INTEGER primary key ,
name varchar
);
CREATE TABLE TableC
(
id INTEGER primary key,
name varchar
);
CREATE TABLE TableB
(
id INTEGER primary key,
issues varchar,
tb_aid INTEGER,
tb_cid  INTEGER    ,
CONSTRAINT fk_TBLC
FOREIGN KEY (tb_cid)
REFERENCES TableC(id)
ON DELETE CASCADE,
CONSTRAINT fk_TBLA
FOREIGN KEY (tb_aid)
REFERENCES TableA(id)
ON DELETE CASCADE
);
INSERT INTO TableA(id,name) VALUES(1,'test');
INSERT INTO TableC(id,name) VALUES(1,'test');
INSERT INTO TableB(id,issues,tb_aid,tb_cid) VALUES(1,'test',1,1);

Delete the value from TableA

DELETE FROM TableA where id = 1;

Select from tableB

SELECT * FROM TableB;

Result

  id    issues  tb_aid  tb_cid

DB<>Fiddle

You do have bidirectional FK's in your example, while I do not think it is a good design, you could use something like the DB Fiddle below.

DB<>Fiddle


I would not go with SQL agent running Patch to upgrade sql server. During patching and/or after patching SQL server needs to be restarted along with a reboot of windows machine - depending on the files that are locked during patching.

Alternatives are described in my answer here : https://dba.stackexchange.com/a/105837/8783

Also, dbatools has Update-DbaInstance.ps1 that can perform installation of SQL Server Service Packs and Cumulative Updates on local and remote servers.


How to create a running balance column based on account number?

It seems you need in simple

SELECT *, SUM(debit_balance - credit_balance)
             OVER (PARTITION BY account_no ORDER BY id) balance
FROM acc_account_transactions

fiddle


What will be the initial size of my tempdb data file(s) after restart?

Looks like it's

select name, file_id, size
from sys.master_files
where database_id = 2
and type = 0

(If necessary, the first datafile will be increased to the size of the model database).


reportserver will not join basic availabiltiy group

Found the answer, but still don't understand WHY it works.  if anyone can share any insights it would be appreciated.

Issue with adding a database to an availability group

So, using the answer from the post above did NOT work when doing the join only mode.  I was able to get it to work by doing the below:

fully recovery the DB on node1.  do NOTHING on node2 with the DB so I can use automatic seeding.
Go through automatic seeding, and see that the DB did NOT show up on node2
Run the below queries on node2.  they failed for me for error 41106

ALTER AVAILABILITY GROUP [agname] JOIN;
GO
ALTER AVAILABILITY GROUP [agname] GRANT CREATE ANY DATABASE;
GO
Remove the DB from the AG on Node1, then add it back in with the GUI.

After this the DB showed up on node2 and is in the AG properly. I would love to understand WHY this works if anyone has info.


MySQL performance issues with occasional complete freezes

Rate Per Second = RPS
From the available (very incomplete) global status and global variables posted, please consider the following suggestions to improve performance.
innodb_log_file_size=512M  # from ~ 50M for log rotation reduction from 6 minutes to about 30 minutes.
innodb_log_buffer_size=256M  # from 16M for ~ 30 minutes RAM storage before write required
innodb_buffer-pool_size=40G  # from 12G to reduce innodb_data_reads RPS of 934
thread_cache_size=100  # from 8 to reduce threads_created count and overhead required
tmp_table_size=128M  # from 16M to expand capacity of in RAM tmp tables
max_heap_table_size=128M  # match tmp_table_size and reduce created_tmp_disk_tables RPhr of 1371

Observation, select_scan RPS of 37 indicates additional indexes would reduce table scans.
Use your slow query log and EXPLAIN SELECT xxxxx to assist with finding table and columns missing indexes.
Posting more complete data to pastebin.com would allow more complete analysis and there are many more Global Variables that could enhance speed of instance.


PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?

You can do this with a function that runs in the security context of its owner.

Function that refreshes the view (create it with the user that owns the MV/table):

CREATE OR REPLACE FUNCTION refresh_mvw1()
RETURNS void
SECURITY DEFINER
AS $$
BEGIN
REFRESH MATERIALIZED VIEW mvw1 with data;
RETURN;
END;
$$ LANGUAGE plpgsql;

Grant execute on the function to any user that you want to be able to refresh the view:

-- Users have 'execute' permissions by default on functions!
revoke all on function refresh_mvw1() from public;
grant execute on function refresh_mvw1() to u1;

To refresh:

select refresh_mvw1();

From the Postgres docs:

  SECURITY DEFINER specifies that the function is to be executed with
  the privileges of the user that created it.

Version that supports a parameter:

CREATE OR REPLACE FUNCTION refresh_mv_xxx(table_name text)
RETURNS void
SECURITY DEFINER
AS $$
DECLARE sql text;
BEGIN
sql := 'REFRESH MATERIALIZED VIEW ' || table_name || ' with data';
EXECUTE sql;
RETURN;
END;
$$ LANGUAGE plpgsql;

... but I'm not sure if dynamic SQL will still execute as the definer.


showing locks from all postgres relations

You can only resolve names from the current database. This is because the pg_class table, which is what the ::reglass operator uses to resolve the names, is local to each database.


SQL Server will allow you to create a database user for a domain account, even if the corresponding login does not exist. No error will be returned as long as the user creating the database user has CREATE USER permissions and the domain account is valid.

However, the new user will not be able to authenticate to the instance until a login for that account (or group the account is a member of) is created.


How to get a selection from a join, where there is no result in one of the tables

Here's one option, which uses UNION of those tables. Aggregate (MAX) is used to display changes in both county and address in a single row; otherwise, you'd get two separate rows. This won't work correctly if there are several changes within a single date.

SQL> with t_county (id, county, cdate) as
  2    (select 1, 10, date '2018-08-02' from dual union all
  3     select 2, 20, date '2018-08-05' from dual
  4    ),
  5  t_address (id, address, cdate) as
  6    (select 1, '123 test', date '2018-08-10' from dual union all
  7     select 2, '222 twix', date '2018-08-05' from dual
  8    ),
  9  -- union of those tables
 10  t_union as
 11    (select id, county     , null address, cdate from t_county union all
 12     select id, null county, address     , cdate from t_address
 13    )
 14  select id,
 15    max(county) county,
 16    max(address) address
 17  from t_union
 18  where cdate = to_date('&par_date', 'yyyy-mm-dd')
 19  group by id;
Enter value for par_date: 2018-08-02

        ID     COUNTY ADDRESS
---------- ---------- --------
         1         10

SQL> /
Enter value for par_date: 2018-08-05

        ID     COUNTY ADDRESS
---------- ---------- --------
         2         20 222 twix

SQL>


Alternative to "FOR JSON" in SQL Server 2012?

No. JSON support was new in SQL Server 2016.

If you want to convert data to JSON, you have a few options:

Fetch the rows as you would any other query result, and convert it in the application tier (like C#, Java, etc)
Build T-SQL logic in a stored procedure or function to convert it to JSON yourself (but keep in mind that you won't be able to use "FOR JSON PATH" the exact same way 2016 does)
Build CLR logic to do it (again, won't be compatible with "FOR JSON PATH")


Clustered Index Help, where did i go wrong?

Are there exceptions to the above clustered index rules?

The general guidance for selecting a clustered index is good, but there are sometimes additional considerations to be taken into account. Occasionally, these extra factors can be more important than the general 'rules'.

Your scenario is somewhat "special" in that you have a very wide table, with a set of queries that request a (presumably) unpredictable set of columns, although the query predicates are normally the same.

The original clustered index arrangement was likely expensive for operations that change data, since changing any part of the clustering key means changing all nonclustered indexes as well. In addition, physically moving whole rows is costly in terms of log generation, especially where a page split occurs.

That said, once a clustered index has been modified and split a fair bit, it will contain quite a bit of free space, making future movements less intensive, just as if a sensible fill factor had been set in the first place, and maintained as part of normal index maintenance.

It seems likely your production system had fallen into a sort of equilibrium, where page splitting occurred at a steady, reasonable rate. Nonclustered index maintenance would still be relatively expensive, but it seems that was not a dominant factor.

The crucial advantages of the original indexing arrangement were:

The clustered index (Company, Region, FlagA, FlagB, FlagC) matches the predicate of:

SELECT {unpredictable column list}
FROM table
WHERE Company = 'company'
AND Region = 'region'
AND flagA= 'A'
AND flagB = 'B'
AND flagC = 'C';

...while at the same time providing access to whichever columns are listed in the select list.
Queries of the form:

SELECT *
FROM table
WHERE ID = 1234;

...were adequately supported by the nonclustered primary key. This query always returns a single row, so only a single lookup via the clustered index is required after the row is located in the nonclustered index.

Changing to a clustered index on ID, and a nonclustered index on (Company, Region, FlagA, FlagB, FlagC) makes the second query a little more efficient (eliminating one lookup per query), but makes the first query much less efficient (replacing zero lookups with ~5000).

Moreover, it is very possible the optimizer might choose not to use the nonclustered index at all, estimating that a full scan of the table would be cheaper than the 5000 lookups.

You're probably better off leaving the two main indexes as they are for the time being, while you sort out the 40-odd nonclustered indexes, and analyse the workload for the minimal set of covering nonclustered indexes that would be required. Once that data is available, you will be in a better position to consider fundamental indexing changes.

You may also want to check the existing monitoring and maintenance routines for this table. In many scenarios, the 'rule-breaking' clustered index will be just fine, if the table is given a fill factor that just prevents significant page splitting before the next maintenance window comes around.


One method is to use the MULTI_COLUMN_DATASTORE. Below is an example:

drop table t1 purge;

create table t1 (column1 varchar2(100), column2 varchar2(100));
insert into t1 values ('HELLO', 'WORLD');
insert into t1 values ('SAY', 'HELLO');
insert into t1 values ('SOMETHING', 'ELSE');
insert into t1 values ('WORLD', 'CUP');
commit;

Create the datastore with the desired columns:

begin
ctx_ddl.create_preference('t1_multi_column_datastore', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute('t1_multi_column_datastore', 'columns', 'column1, column2');
end;
/

Create the Text index using this datastore:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore');

Or if you want the index maintained on commit:

create index i1 on t1 (column1) indextype is ctxsys.context
  parameters ('datastore t1_multi_column_datastore sync(on commit)');

Searching for the words HELLO or WORLD:

select * from t1 where contains (column1, 'HELLO or WORLD') > 0;

COLUMN1    COLUMN2
---------- ----------
HELLO      WORLD
SAY        HELLO
WORLD      CUP

Notice that you do not need to specify all columns in the query, but you need to specify an operator between words.


There is a project called MySQL Proxy that is attempting to handle read/write splitting in the proxy layer, but it is not production-ready. You can read more about the issues on this page, paying attention to the known issues section.

For now, as others have noted, you have to handle the routing through your application.


Using  with common_table_expression() and count() over(): we can get a running total for the count.

query:

with cte as (
  select distinct
      TheDate= convert(varchar(10),convert(date,mydatetime),120)
    , TheDay = datepart(day,mydatetime)
    , DailyActivityCount = count(*) over (
          partition by convert(date,mydatetime), activity
          )
    , runningActivityCount = count(*) over (
          partition by activity
          order by convert(date,mydatetime)
          )
    , Activity
  from all_activities
  where activity = 'The door opened'
    and mydatetime >= dateadd(month, datediff(month, 0, getdate()), 0)
    and mydatetime < dateadd(month, datediff(month, 0, getdate())+1, 0)
  )
select
    *
  from cte
  --where runningActivityCount > 12
  order by TheDay

rextester results: http://rextester.com/WOW38735

+------------+--------+--------------------+----------------------+-----------------+
|  TheDate   | TheDay | DailyActivityCount | runningActivityCount |    Activity     |
+------------+--------+--------------------+----------------------+-----------------+
| 2017-01-07 |      7 |                  2 |                    2 | The door opened |
| 2017-01-08 |      8 |                  1 |                    3 | The door opened |
| 2017-01-09 |      9 |                  4 |                    7 | The door opened |
| 2017-01-10 |     10 |                 10 |                   17 | The door opened |
| 2017-01-11 |     11 |                  1 |                   18 | The door opened |
| 2017-01-12 |     12 |                  5 |                   23 | The door opened |
| 2017-01-13 |     13 |                  1 |                   24 | The door opened |
| 2017-01-14 |     14 |                  2 |                   26 | The door opened |
| 2017-01-15 |     15 |                  1 |                   27 | The door opened |
| 2017-01-17 |     17 |                  7 |                   34 | The door opened |
| 2017-01-18 |     18 |                  7 |                   41 | The door opened |
| 2017-01-19 |     19 |                  3 |                   44 | The door opened |
+------------+--------+--------------------+----------------------+-----------------+

update based on question in comment:

Declare @Activity varchar(64) = 'The door opened';
Declare @DailyThreshold int = 2;
Declare @Month varchar(7) =  '2017-01';
Declare @NthDay int = 5; -- for more then 5 days in a month(Y)

;with countCte as (
  select distinct
      TheMonth= convert(varchar(7),convert(date,mydatetime),120)
    , TheDate= convert(varchar(10),convert(date,mydatetime),120)
    , TheDay = datepart(day,mydatetime)
    , Activity
    , DailyActivityCount = count(*) over (
        partition by convert(date,mydatetime), activity
          )
    , runningActivityCount = count(*) over (
        partition by convert(varchar(7),convert(date,mydatetime),120), activity
        order by convert(date,mydatetime)
          )
    , MonthlyActivityCount = count(*) over (
        partition by convert(varchar(7),convert(date,mydatetime),120), activity
          )

  from all_activities
  where activity = @Activity
    and mydatetime >= dateadd(month, datediff(month, 0, getdate()), 0)
    and mydatetime < dateadd(month, datediff(month, 0, getdate())+1, 0)
  )
, cte as (
    select *
      , rn = row_number() over (
          partition by Activity
          order by TheDate
          )
      --,
      from countCte
      where 1=1
        and DailyActivityCount > @DailyThreshold
        and TheMonth = @Month
)
select
    top 1
    *
  from cte
  where rn > @NthDay
  order by TheDay


Cannot Start Oracle Database after Shutdown

$ oerr ora 27137
27137, 00000, "unable to allocate large pages to create a shared memory segment"
// *Cause: Parameter USE_LARGE_PAGES set to only, but large pages were not
//         configured properly
// *Action: Check alert file for more information

Your output shows hugepages were not configured, but the database is configured to use only large pages (use_large_pages=only), hence it can not start.

Configure the required amount of pages by setting the vm.nr_hugepages in /etc/sysctl.conf, the appropriate memlock limit in /etc/security/limits.conf, then reboot and start the database.

Configuring HugePages on Linux

If you do not need hugepages, simply edit the parameter file of the database, and unset the use_large_pages parameter.


If the standby server is promoted to master, will it work without any replica?

Definitely, Your new primary will act like a standalone. If your configuration file is good, you'll even be able to add the old-primary as a new secondary without any restart.

Just be sure your old primary is not running and modifying data or you'll loose some of it!

  If the primary server fails and the standby server becomes the new
  primary, and then the old primary restarts, you must have a mechanism
  for informing the old primary that it is no longer the primary. This
  is sometimes known as STONITH (Shoot The Other Node In The Head),
  which is necessary to avoid situations where both systems think they
  are the primary, which will lead to confusion and ultimately data
  loss.

You may want to read these documentation page :
https://www.postgresql.org/docs/9.1/static/warm-standby-failover.html


You might want to look into using Spark, and if are able to introduce AWS into the architecture, Kinesis Data Firehouse.
Spark: https://medium.com/analytics-vidhya/processing-time-series-data-in-real-time-with-influxdb-and-structured-streaming-d1864154cf8b
Another article on Spark: https://www.influxdata.com/blog/zeppelin-spark-and-influxdb-for-big-data-time-series-scenarios/
Kinesis Data Firehose: https://aws.amazon.com/kinesis/data-firehose/?kinesis-blogs.sort-by=item.additionalFields.createdDate&kinesis-blogs.sort-order=desc
Spark is fairly widely used these days and integrates with InfluxDB, and Kinesis Data Firehose is used for heterogeneously ingesting large amounts of data in real time.


Windows 2000 can't connect to SQL Server 2014

Community wiki answer:

I would try installing the SQL Server 2005 Native Client in the Feature Pack for Microsoft SQL Server 2005 - April 2006.

Please note also that post says Windows 2000 Service Pack 4 is required to be supported.


Existing maintenance plan backup causing 3rd party SQL backup to fail

The database server is being backed up by a third party tool not the maintenance plans.
The following query will show you the backup vendor information

select
 software_vendor_id
 ,name
 ,description
 ,user_name
from msdb.dbo.backupset


First one is how to create database dynamically.

set @db_name:= 'db_name';
set @statment = CONCAT('CREATE DATABASE ', @db_name);
prepare stmt_create FROM @statment;
execute stmt_create;
deallocate PREPARE stmt_create;

Now you want to dump some table into it.

set @db_name="db_name";
set @start_syntax="CREATE TABLE ";
set @table_syntax=".`table_name` (id int(4)) ENGINE = InnoDB;";
set @stat_execute=concat(@start_syntax,@db_name,@table_syntax);
prepare stat_ment from @stat_execute;
execute stat_ment;
deallocate prepare stat_ment;

In this way you can create database and dump the table.


SQL Server Installation - TempDB not created in the directory specified in ConfigurationFile.ini

I can't say specifically why but when reducing the initial size of the TempDB files, they were created in the directory specified in the ConfigurationTile.ini.

I updated the ConfigurationFile to the following:

; The number of Database Engine TempDB files.
SQLTEMPDBFILECOUNT="8"

; Specifies the initial size of a Database Engine TempDB data file in MB.
SQLTEMPDBFILESIZE="256"
; Specifies the automatic growth increment of each Database Engine TempDB data file in MB.
SQLTEMPDBFILEGROWTH="0"

; Specifies the initial size of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILESIZE="256"
; Specifies the automatic growth increment of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILEGROWTH="0"

; Directories for Database Engine TempDB files.
SQLTEMPDBDIR="T:\Data"
; Directory for the Database Engine TempDB log files.
SQLTEMPDBLOGDIR="T:\Log"

; The Database Engine root data directory.
INSTALLSQLDATADIR="O:\Data"
; Default directory for the Database Engine user database logs.
SQLUSERDBLOGDIR="O:\Log"

As an additional setup step, I'll run a script to re-size the tempdb files.


Non-English characters are not imported using SSIS or Visual Studio Deploy

The data types of the columns you are inserting the text into should be set to nvarchar.


SQL Server query on server taking longer than laptop

We had an issue with a select statement using like '%findthis%' was causing slowness issues. One server it'd run fast whereas the other server would be quite slow. We were able to remove one of the % signs to '%findthis' allowing the optimizer to correctly interpret the query--on both servers yielding similar performance results. (%findthis% is simply bad form, but it wasn't our code to change unfortunately).

Long story short, make sure your SQL Server versions are identical. For example, run select @@version and look at the numeric sequences like 10.50.6000.34.

Our versions were almost identical but our test server was running faster than production because it was running a trace flag--and a minor patch. The next patch for SQL Server would have included the flag so I'm told.

Run DBCC TRACESTATUS(-1) on both servers to make sure they're running the same trace flags as well. I forget which trace flag was running in test that made it faster, but the newest patch to production SQL would have enabled it. It's been a while and most people are out for the holidays, or I'd have more specifics on which flag and which patch.