Microsoft SQL Server 2012 Mirrors Go Into Disconnected

I experienced very similar issue in my previous company. Database miroring was periodically getting suspended with the same error as you are getting. Mirroring never ever reconnected on its own, but usually I was able to resume it using:

ALTER DATABASE <Database Name> SET PARTNER RESUME

After a few weeks we figured out that even though there was enought memory on the server and enough memory was allocated to the instance our workload was very unevenly distributed and sometimes 100% of the memory was used. Since most of the time we had enough memory we were not authorised to purchase more. I ended up increasing buffer pool extension file and placing it on one of the SSDs which happened to have enough empty space to accommodate it. The problem with mirroring never returned again. I cannot guarantee that the same will work for you, I am mainly sharing my experience.


Dealing with subtypes and supertypes in Database design

Not sure I understand all of your questions, but one fairly common model for super/sub-type is to add a classifier attribute to your supertype:

CREATE TABLE super
( xxx_id ... not null primary key
, classifier int not null
,   unique (classifier, xxx_id)
,   check (classifier in (1,2,...))
)

CREATE TABLE sub1
( xxx_id ... not null primary key
, classifier int default 1 not null
,    check (classifier = 1)
,     foreign key (classifier,xxx_id)
          references super (classifier,xxx_id)
)

CREATE TABLE sub2
( xxx_id ... not null primary key
, classifier int default 2 not null
,    check (classifier = 2)
,     foreign key (classifier,xxx_id)
          references super (classifier,xxx_id)
)

Now the classifier in super guarantees that for example 1 can't be located in sub2 (that it exist in any subtype is handled by a transaction).

However, this model is not fully practical if the classifier changes, so this is more of a food for thought answer (but it is too long to be a comment).


How to keep track of functional dependencies when performing a BCNF decomposition

Decomposition and Functional Dependencies

In general, when a relation R(T) with dependencies F is decomposed in two relations R1(T1), R2(T2), the dependencies holding in the two relations cannot be immediately derived from the original set of dependencies F. This is because there could be dependencies implied by F, that is in F+, that hold in the decomposed relations. If we want to know all the dependencies that hold in the decomposed relations, we should consider the projection of the dependencies F over the attributes of the decomposed relations.

Here is the formal definition of projection of a set of dependencies F: Given R, and Ti ⊆ T, the projection of F on Ti is equal to πTi(F) = {X → Y ∈ F+ | X,Y ⊆ Ti }. In other words, one should calculate F+, the closure of F, and then get all the dependencies that have all the attributes in Ti.

But, since the computation of F+ is an exponential task, the computation of a projection of a set of functional dependencies is an exponential task and it is not performed in practice (a part from simple cases with very few attributes and dependencies).

What instead can be decided with a polynomial algorithm is the answer to this question: does the decomposition R1(T1), R2(T2) of R(T) with dependencies F preserves the dependencies? (see J. Ullman, Principles of Database Systems, Computer Science Press, 1983).

By definition, a decomposition preserves the dependencies if the closure of the dependencies projected over the decomposed relations is equal to the closure of the original relation. Formally, a decomposition Ri(Ti) preserves the dependencies if and only if (∪πTi(F))+ = F+.

Why we are interested in FDs of decomposed relations?

When decomposing for a specific reason, for instance to normalize a relation, we are interested in knowing if the decomposition preserves the dependencies (otherwise it is not a good decomposition).

But, while in the synthesis algorithm for the 3NF we are guaranteed that the decomposition alway preserves the dependencies, the same is not true for the BCNF. On the contrary, there are examples of relations that actually cannot be decomposed in BCNF without losing the dependencies (for instance, R(A,B,C), F={AB → C, C → A}).

Summary

When we decompose for BCNF, we should be prepared to lose some dependency (and, for this reason, in practice the 3NF is used).
The analysis algorithm of BCNF requires an exponential task to be performed, since at each step one should check if a decomposed relation is still not in BCNF, and to know this one should know the projection of the dependencies over it (or at least its cover).

A BCNF for your example

In your example, a BCNF obtained with the classical analysis algorithm is the following (starting with a minimal cover of F and producing a decomposition with a cover of the projection of the dependencies):

R2 < (A B H) , { } >

R3 < (A D G) , { D → A, D → G } >

R4 < (B C D E) , { E → D, E → B, C D → B, B D → C, B D → E } >

Note that in this decomposition the two original dependencies:

{ A B → C D
  A B C → D E }

are lost.


From your description, you have a set of files some of which are full backups and some of which are differentials. If it were me, I'd use the various backup metadata interrogation commands inside the dbatools powershell module. Something like:

$bh = Get-DbaBackupInformation -SqlInstance yourRestoreServer -Path directoryForYourBackups;
$bh | Restore-DbaDatabase -SqlInstance yourRestoreServer -OutputScriptOnly;

That first command will go through and do a restore headeronly on all of the files and the second should select the most recent one(s) to do a restore on (which includes matching differential backups to full if that's an option).

If you don't already have the dbatools module installed, check out the installation instructions.


dm_db_index_physical_stats results inconsistent with actual object properties - what may be the reasons?

Before I gave anyone else a chance to answer this, I found the problem with my query.

dm_db_index_physical_stats(), when running in the 'DETAILED' mode, returns one row for each index level. In my case the leaf level of the index indeed has 0.01% fragmentation and is therefore filtered out by the s.avg_fragmentation_in_percent > 25 criteria, leaving only one intermediate level in the result.

Arguably you would only want to look at the leaf level of the index to identify fragmentation, so the query should look like so:

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
       i.name AS IndexName,
       s.index_type_desc,
       s.avg_fragmentation_in_percent,
       s.fragment_count,
       s.page_count,
       s.record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RPT_XXX'), NULL, NULL, 'DETAILED') s
    INNER JOIN
    sys.indexes i ON i.OBJECT_ID = s.OBJECT_ID
                       AND i.index_id = s.index_id
                       AND i.object_id = OBJECT_ID('RPT_XXX')
WHERE
    s.index_level = 0 AND           -- leaf level only
    i.is_hypothetical = N'0' AND    -- exclude hypothetical indexes
    s.page_count > 1000 AND         -- look at large indexes only
    s.avg_fragmentation_in_percent > 25

The criteria i.is_hypothetical = N'0' comes from observing the query generated by the Management Studio in the trace, as suggested by Aaron Bertrand.

Edit: It may also make sense to exclude smaller indexes from this analysis, because their fragmentation a) may not be important and b) may not be significantly improved.


Dynamic Data Masking doesn't affect stored pages and it will work the same way with any transaction isolation level.

from Microsoft documentation(https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15):

  Dynamic data masking is easy to use with existing applications, since
  masking rules are applied in the query results.


Are you allowed to add computed columns in the table or create a materialized view?

You could add a computed column and then index that:

ALTER TABLE <table>
   ADD ntext_as_nvarchar_max AS CAST(ntext_column AS NVARCHAR(MAX))  ;

CREATE INDEX index_name
  ON <table> (<index_columns>) INCLUDE (ntext_as_nvarchar_max) ;

The issue with this approach is that your queries will have to reference the new computed column (ntext_as_nvarchar_max) and not the original column for the index to be used.

Test at SQL-Fiddle


Should I avoid adding a clustered key to a table with high throughput

The main reason I would want a clustered index in this scenario is this line:

  The process for deleting process.Audit records will execute every hour and delete an hours worth of audits from x days ago (typically around 7 days)

When you delete rows from a HEAP, data pages may not be deallocated unless the delete gets a table lock, or you provide a WITH (TABLOCK) hint to the delete query. You can probably imagine what that does to concurrency, though. Not good.

Note that the TABLOCK hint will not have this behavior if you're using RCSI or Snapshot Isolation.

Here's a quick example. Load up a small table:

USE tempdb;
SET NOCOUNT ON;

CREATE TABLE dbo.heap
(
    id INT PRIMARY KEY NONCLUSTERED,
    junk VARCHAR(1000)
);

INSERT dbo.heap (
    id, junk )
SELECT TOP 1000 x.n, REPLICATE('A', x.n % 1000)
FROM   (
           SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
           FROM   sys.messages AS m ) AS x;

Run a sanity check query to figure out how many pages are assigned to the heap, and to the nonclustered PK:

SELECT   OBJECT_NAME(i.object_id) AS table_name,
         i.name AS index_name,
         MAX(a.used_pages) AS leaf_me_alone
FROM     sys.indexes AS i
JOIN     sys.partitions AS p
ON p.object_id = i.object_id
   AND p.index_id = i.index_id
JOIN     sys.allocation_units AS a
ON a.container_id = p.partition_id
WHERE OBJECT_NAME(i.object_id) = 'heap'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY OBJECT_NAME(i.object_id), i.index_id;

Results in this:

table_name  index_name  leaf_me_alone
heap        NULL        74
heap        PK__heap__  7

So, 74 pages in the heap, 7 pages in the NC PK.

Do some singleton deletes to clear out the table:

DECLARE @i INT = 1;

WHILE @i < 1000
    BEGIN

        DELETE h
        FROM  dbo.heap AS h
        WHERE h.id = @i;

        SET @i += 1;

        PRINT @i;

    END;

If you re-run the sanity check query, you'll get the same result.

Worse, if you query the table now, SQL will read ALL OF THOSE BLANK PAGES!

SET STATISTICS TIME, IO ON
SELECT *
FROM   dbo.heap AS h;

  Table 'heap'. Scan count 1, logical reads 67

So now not only is our table artificially large, but SQL now has a bunch of blank pages on disk and in memory and in backups and in DBCC CHECKDB and... well, you get the point.

  We're looking at around 1.5m audit records going through this process every hour

Heh heh heh! No fun.

Other options for getting pages deallocated from the heap are:

TRUNCATE TABLE dbo.heap

Which doesn't work for you, because you need to batch delete data.

ALTER TABLE dbo.heap REBUILD;

Which would be painful for you at that table size, because it will rebuild all nonclustered indexes on the table at the same time.

Will the table re-use pages? Sometimes maybe sorta kinda.

DECLARE @id_max INT = (SELECT MAX(id) FROM dbo.heap AS h);

INSERT dbo.heap (
    id, junk )
SELECT TOP 5000 x.n + @id_max, REPLICATE('A', x.n % 1000)
FROM   (
           SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
           FROM   sys.messages AS m ) AS x;

Sanity check:

table_name  index_name  leaf_me_alone
heap        NULL        400
heap        PK__heap__  20

SELECT * query:

  Table 'heap'. Scan count 1, logical reads 392

Hope this helps!


NEWB Getting to KNow New Database and Understand Where Relationships Are

I'm not a DBA but I've looked at many "new to me" databases.

I don't know if there is a trick to understanding a database.  What has worked for me is just to explore the db and query it to see what it returns.

Many times as a new developer to a project I'm given a task to fix or update something.  I usually look at the table like you did with alert and look at any other id columns that might be in the table (i.e user_id & incident_id).  That starts the bread crumb trail. Then I look at the foriegnkeys to see what they are doing (i.e. cascade delete).  The FK usually point to another table that could also lead to other tables.

If the bread crumb trail of id columns gets bigger than 2 or 3, then I usually try to diagram the db if possible.  Sometimes that helps me to see the tables in a different perspective.

Other than that, it just takes time.  As you get more tasks under your belt with the db you will understand more of it.


You may have been unaware of this, but everything and its grandmother done in MySQL is a question.
In light of this, the real question is : To mysqld, what is a Question ?
According to the MySQL Documentation on Questions:

The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

Anything you issue for the sake of running queries or just checking a status of some kind is a Question. Even checking how many questions in your session is itself a Question. Here is proof: (I will connect to MySQL 5.6.14 for Windows and ask for Questions in my session):
Microsoft Windows [Version 6.2.9200]
(c) 2012 Microsoft Corporation. All rights reserved.
C:\Windows\system32>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 3     |
+---------------+-------+
1 row in set (0.11 sec)

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 4     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 5     |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

This is normal to expect, so there is really nothing to worry about.
YOUR ORIGINAL QUESTIONS

Is someone hacking my database?

No, you are not being hacked.

Is PHPMyAdmin reporting inaccurate information?

PHPMyAdmin asks mysqld Questions all day long. The result is that it just runs up the global count (As seen from SHOW GLOBAL STATUS;). I wrote an answer to a post in ServerFault about 2.5 years ago entitled 1 billion mysql queries in 24 days? Can something be wrong?. I attributed the runaway stats to monitoring then, and I still strongly assert this now.


How can I successfully decrypt column data through the whole database safely?

Take more frequent log backups and/or increase the disk space available for the log files.  Let it run for 48 hours over the weekend.

I don't know everything about your system but increasing CPU is unlikely to help.


TokuDB does not support the repair table command, just like InnoDB, so you can't repair the table in question. If you don't have a backup or slave to pull from the best you can do is select the rows from the readable portion of the table itself. I'd recommend using "select * from table_name where table_pk < ? into ;" and "select * from table_name where table_pk > ? into ;". You'll need to experiment with the ? for each statement, because once you read the damaged part of the file the server will crash.


Trying to avoid bad MySQL database design - can't figure out how to avoid too many columns

Anytime you'd start getting a col1, col2, col3 in a database design, you "typically" want to consider those columns as rows in another table.  (typically, there are always exceptions - doubtful this is the case ;) )

So you have a LEGO_SETs and LEGO_PARTs table already ?

Sounds like you have something like this:

table: LEGO_SET
SET_ID
name
... etc ..
PART_1
QTY_1
PART_2
QTY_2
... etc.

table: LEGO_PART
PART_ID

You have here a many to many relationship. (ie A Lego_Set has many Lego_parts. A Lego_Part may belong to many Lego_sets).

You should have an interim table to help resolve this:
Remove the PART_1, QTY_1, etc. from LEGO_SET table and do this:

table: LEGO_PART_SET
SET_ID
PART_ID
QTY

that's it.

So if you have SET_ID 123, having Lego_parts 333 and 444.
You would have 2 rows in this table:

SET_ID  PART_ID  QTY
123     333       12
123     444       4

Hope that helps.

[edit]
Edited based on comments: Don't use SET table, avoid reserved words.
[/edit]


Query with Variable not estimating right

I have other ideas than a plan guide and I'll post them here for your consideration, but I cannot recommend actually implementing them. There are at least two different ways of bumping up row estimates from tablea. The first involves manually setting statistics and the second involves redirecting the third party app to use a view that you defined instead of accessing the tablea directly.

Both CREATE STATISTICS and UPDATE STATISTICS have a STATS_STREAM option. Here is what Books Online says about it:

  Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

It's completely understandable if that turns you off from using it, but what it allows you to do is to transplant the statistics from one object to another. Suppose that you want to increase cardinality estimates from queries that use tablea by 50X. You could take the data from tablea, duplicate it 50 times, gather stats with FULLSCAN, then update the statistics on tablea using the STATS_STREAM value from the other table along with the NORECOMPUTE option. You probably want to change the ROWCOUNT value as well. This should have a pretty large effect on all queries that reference the table. You could run into problems with other queries, with the underlying data changing in the table, with statistics on indexes not getting updated, and so on. This isn't a good option.

Depending on how the third party application connects to SQL Server, you may be able to create a view with a different schema from tablea and to direct that part of the application to use in the view. The view should have all of the same columns as tablea but should have additional code which inflates the number of rows returned. I didn't try that hard to get this to work because it seems very impractical, but this approach was inspired by Adam Machanic's article about forcing parallel plans:

SELECT ta.*
FROM tablea ta
CROSS JOIN (
    SELECT t.t
    FROM
    (
    VALUES
    (2), (1), (1), (1), (1), (1), (1), (1), (1), (1),
    (1), (1), (1), (1), (1), (1), (1), (1), (1), (1),
    -- lots more values here ...
    (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
   ) t(t)
WHERE t % 2 = 0
) t
OPTION (QUERYTRACEON 8690);

I wasn't really satisfied with that query but didn't spend too much time on it. In that form it does a single scan of the table but the query optimizer overestimates the cardinality estimate by 100X. TF 8690 is there to prevent a pointless table spool, but this probably isn't appropriate in a view or in a larger query. If the same application inserts data into this table you'd need to deal with that somehow. This isn't a good option.

To reiterate, I don't think that you should attempt either of these options. It would be much better to talk to the vendor, or if that doesn't work, to suck it up and try again with a plan guide. I don't have any experience with those so I can't be of help.


error 1045(28000):anonymous user problem

Here is what you are missing

Connecting with user a via a socket is the same as connecting with user a via localhost.
Connecting with a socket will not use TCP/IP

If you are going to explicitly using a hostname, you must use TCP/IP with port 3306 and no socket

mysql -ua -p -h hosta --protocol=tcp -P3306

I have discussed using --protocol=tcp in my earlier posts

Jul 16, 2015 : mysql.sock not generated when starting MySQL server using XAMPP
Jul 13, 2015 : mysql restart issue after move database
Jul 11, 2013 : Rare error lasts only a second "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146)" 146=ECONNREFUSED
Mar 28, 2013 : MySQL - ERROR 1045 (28000): Access denied for user

You need to delete blank users, blank hosts, and blank passwords from mysql.user

DELETE FROM mysql.user WHERE user = '';
DELETE FROM mysql.user WHERE host = '';
DELETE FROM mysql.user WHERE password = '';
FLUSH PRIVILEGES;


SQL Server Express 2008 installation problem

To install SQL Sever properly -- and have all the services running as expected -- you really should be logged in as an administrator.


Efficient schema to handle time interval queries on a huge table?

How many rows are being aggregated on each SELECT?

This should go without saying - ensure you have an index on the datetime column. Depending on what your query is doing you can also try putting a compound index on your datetime and the column you're aggregating to try to only use the index and avoid reading data blocks off the disk.

Please post an EXPLAIN EXTENDED on your SELECT query along with a CREATE TABLE and if you are doing any JOINS then include the CREATE TABLE on those tables too.

If you're using MyISAM switch to InnoDB for your table engine.

If your index is highly fragmented then you may want to consider partitioning your tables by date. If the index lookup isn't part of the problem then this won't help.

If the output of these queries can be cached and subsequent requests would still be valid with that data then cache as much of it as you can.

Also ensure your my.cnf is configured for your usage patterns.

As extra trivia, InnoDB benefits from having it's data files on fast storage and from high core speeds over more cores, especially when aggregation is being done.

[edit] Op posted SQL

You can likely drop a filesort by adding an ORDER BY NULL to the end of this query;

Without knowing how many rows are being aggregated in the query nor the cardinality of the sale_id and created_at columns it's difficult to guess if a compound index gives better performance. You may want to test it.

E.g.; drop separate indexes and create a compound (sale_id, created_at):

ALTER TABLE earnings DROP INDEX index_earnings_on_sale_id, DROP INDEX index_earnings_on_created_at, ADD INDEX index_sale_id_created_at (sale_id, created_at);

In your example you are doing a COUNT(amount) instead of SUM(amount). If your goal is to only count earnings by earner_id and not sum them then you can also try doing the select in an inner query and counting on the outer. This can sometimes improve performance depending on the query.

SELECT earner_id, COUNT(amount) AS total
FROM (
  SELECT earner_id, amount
  FROM earnings
  WHERE sale_id IN (428, 245) AND (created_at BETWEEN '2016-06-01 07:00:00' AND '2016-06-22 06:59:59')
) AS derived
 GROUP BY earner_id ORDER BY NULL


This isn't really a database administration question, but one about your business rules (real or imaginary). If you look at the internets, the most common way to uniquely identify users is by their email addresses or usernames (handles, aliases etc.).
Storing national IDs, which constitute sensitive private information, is a bad idea, as  your application then will need to comply with all sorts of regulations that apply to the processors of SPI.
Allowing users to view other users' data is an even worse idea, as that would be a direct violation of various privacy laws.


Best practices for change management with indexes

I would strongly recommend that you treat your database basically the same way as you treat your application code. You can script your database out to it's component parts and check those into source control and then use the same labels & versions there that you use for your apps.

To get the objects into source control there are a number of tools you can use. Microsoft has a tool that is nicknamed Data Dude. It works with Visual Studio. They're also preparing to release a new tool called SQL Server Database Tools (SSDT), again, working with Visual Studio. My company, Red Gate Software, makes a tool that works with SSMS called SQL Source Control.

In terms of process, I wrote several chapters for the book Red Gate Guide to Team Development. It's available as a free download (or if you want to kill a tree you can purcahse one from Amazon). I go into a lot more details about working with databases in development teams there.


How to increase the amount of memory sql server uses on the server?

This is configurable through SSMS.  However if you have a 32 bit build and are not booting with the /3GB switch the figures shown look suspiciously like the hard limits that will be imposed by the operating system anyway.

You can configure it from SSMS by right-clicking on the database server in the explorer, and you can see the memory usage policy under the 'memory' set of properties.

If you have a 32 bit server you may be able to use AWE to get more than your 2GB or so, although older versions of Std edition Windows (2003R1 and earlier) still restrict you to fairly small memory sizes.  If you have a more recent version of the operating system and a 64 bit build you should be able to use up to 32GB of memory.


Getting multiple queries with "show transaction isolation level" in pg_activity

If they're there; they're there, but they didn't magically pop out of thin air. While I'd love for you to come back and tell us what they were in your case it's usually one of two things,

Connection pooling
ORM or SQL abstraction

Sometimes these guys connect and start empty transactions, or prep a transaction. It's always a result of a bad library. It's excusable if the library is new or new to the database. If the library is years old and doing this, I would suspect they don't have too many eyes on it and look at alternatives.
From the comments,

Postgres doesn't create connections by its own. It's your application that creates those connections and runs those queries. Possibly a connection pool or something similar. – a_horse_with_no_name Sep 10 '13 at 9:03
Are you running a Java application? Using HikariCP, or maybe another connection-pooling option? I'm using HikariCP and seeing a similar thing when I log into psql and do select query from pg_stat_activity;... I was also wondering why this is occurring; my guess is this is a mechanism by which HikariCP keeps connections "fresh". – Chris W. Jan 16 '16 at 19:22

While it's possible it's an attempt at keeping a connection "fresh" the kosher way to do that is something like this
result = PQexec(imp_dbh->conn, "/* DBD::Pg ping test v3.6.0 */");

You may want to file a bug report to use that method instead.


Usually a basebackup with no further description would mean the thing taken by pg_basebackup.  But that didn't exist back in 8.2., so it isn't clear what "a basebackup" means here.  Not that it is very clear anyway, as pg_basebackup has many options.
The article you linked to is for point-in-time recovery.  Obviously a base backup cannot include changes that had not yet happened at the time the backup was taken.  The WAL archive is needed to roll the database forward from the ending time of the backup, to the desired point in time.  If you don't actually want to do a PITR but just restore to the ending time of the basebackup, then you won't need the recovery.conf, and you also need to not do the rm -rf /var/lib/postgresql/10/main/pg_wal/*.  (Although if you did run that command, it wouldn't do any harm anyway as the directory in question was not called 'pg_wal' back then.)  You just start up the database.


Record MultiSubnetFailover value at connection?

I don't believe this configuration setting is sent to the server.  It controls the client's interpretation of a DNS lookup returning multiple IP addresses, and there is no functional need for the server to be informed about it.  And the client doesn't send arbitrary name/value pairs to the server.  The fields that a client sends to the server are dictated by the TDS protocol, and it would require a protocol revision to add new ones.

Also while properly setting MultSubnetFailover at the client continues to be a best-practice, newer SQL Server client libraries have changed their behavior to mitigate the issues arising from not setting MultiSubnetFailover.  See Using Transparent Network IP Resolution.  Newer clients will have only a brief delay in connecting when the first IP returned from DNS is not the active AG Listener, instead of the old behavior of waiting 20-30 seconds before attempting to connect to the second IP.


Can i set Accent insensitive for individual languages?

No, this is not how Collations work. Not only are the various sensitivities (case, accent, width, and Kana) not per Language, you can't even get more than one Language at a time per column or expression. The issue is that multiple languages may share one or more characters. Even if the languages you are concerned about using have entirely distinct characters (e.g. Arabic vs English), you can only specify one locale at a time (even at the OS level and other software -- I've never seen anything allow for multiple locales in a single column / entity / object).

I would suggest picking the Collation that will be used most often as how the column is defined, and then for the other one or two you can create computed columns, defined as simply being the column you are dealing with here -- but with the appropriate Collation -- and defining the Full Text indexes on the various columns. For example:

CREATE TABLE #Temp
(
  ID INT,
  Col1 NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC,
  Col2 AS [Col1] COLLATE Turkish_100_CI_AS_SC [PERSISTED],
  Col3 AS [Col1] COLLATE Arabic_100_CI_AI_SC [PERSISTED]
);

I'm not sure if Full Text Search will require that the computed columns be defined as PERSISTED, so you might want to try with and without it to see not only what is allowed, but also which performs better. This approach will at least make it easy to keep the values in sync as they only truly exist in one place.


sql statement in access that group items and count their sum

I'm guessing the id column is actually not important in your expected result. In which case, you just need to do a grouping and a summation:

select Items, sum(Quantity) as TotalQty
from MyTable
group by Items


Temporary Tables With Nonclustered Indexes Including All Columns

To summarize (and include some information from the comments), you have a process that runs once per day early in the morning that populates several 100k row temp tables with 2 - 8 columns each. This is a broad question but my initial reaction would be to create primary keys on all of the tables. If performance is good enough then I would be satisfied. If performance isn't good enough I would investigate further to find ways to improve the code. Removing the primary keys would technically be an option but in general I would expect to find performance gains by changing the queries that populate the temp tables.

A heap is a table without a clustered index. Note that a primary key does not need to be defined the same way as a clustered index. In fact, you can define a heap with a primary key. As far as I know this isn't a very common thing to do. There are a few advantages in using heaps for your workload:

Inserting the data will not lead to a sort. If you insert 100k rows into a table with a clustered index it is possible that the data will need to be sorted depending on the query that populates the table. All of the columns or just the clustering key could be included in the sort.
As of SQL Server 2014 the insert can run in parallel if you use the SELECT INTO syntax. Note that the query optimizer may choose not to use parallel inserts depending for a variety of reasons including the estimated size of the data, and MAXDOP settings.

In general, I would not worry about a sort of 100k rows, especially if this process runs early in the morning with nothing else. Parallel inserts are unlikely to be a big help as well for such a small amount of data.

There are a few disadvantages of using heaps for this workload:

No data integrity. There might be a bug in your process or corrupt data which could have been caught earlier with a primary key on the temp tables.
The data is not sorted, so any query plan operator which requires sorted data (such as a merge join operator or inserting into a clustered table) will require an explicit sort. Let's say you reference one table in three queries and all three times the query optimizer sorts the data. Why not sort the data on disk at the start to avoid those three sorts in the query plans?

Hard to say more on these points because I don't know anything about your process. It's possible that your queries against the temp tables could benefit from a MERGE JOIN but I doubt it will make a big difference at your volume.

Now let's talk about tables with a clustered index. As stated before, a clustered index does not need to match the primary key but I'll assume the most common default which is they are both the same. That is what you end up with when you define a PRIMARY KEY inline as part of the table definition.

There are a few advantages of tables with clustered indexes for your workload:

Data integrity!
Query plan operators benefit from a sort may already have the data sorted in the correct order. For example, this makes merge joins more attractive, especially if you are getting all of the data from the involved tables.
The query optimizer may be able to pick a more efficient plan if it knows that certain columns are unique. One example of this is SQL Server may know that the joins are not many to many which can lead to more accurate cardinality estimates and costing of join operators.

There are a few disadvantages of tables with clustered indexes for your workload:

Parallel inserts into the temp tables are not available in any version of SQL Server.
Populating the tables may require an explicit sort.

The advantages and disadvantages are mostly the exact opposite of a heap table, which should not be surprising.

The third and four options aren't really worth considering. You can create an index that includes all columns but it will duplicate the data. Just define your primary key the right way and you should have no use for an additional nonclustered index that includes all columns. Note that SQL Server Management Studio will not recommend that you should create a clustered index on a table.

One difference between the clustered index/primary key and the nonclustered index is that the clustered index/primary will not allow NULL values in the key columns and automatically enforces a uniqueness constraint. Of course, it is possible for you to create a nonclustered index with the same restrictions.

Ultimately, all that you can do is test your workload with the different options. So we cannot definitely say which approach will be fastest. With one set of data and queries the heap approach may be fastest. With a different set of data and queries the clustered table approach may be fastest. Do not discount the value of data integrity, but you may have other ways of enforcing data integrity.


Best practice for testing database disaster recovery

If one database successfully restored, it certainly does not mean that the next database has no problems. (E.g. I once, many years ago now, had some disk array problems that resulted in about 20% of the databases on a server manifesting corruption.)

I run a weekly process that RESTOREs and runs DBCC CHECKDB on all databases from over 70 servers. This is done one database at a time and for me takes most of a week. For fully logged databases we also test the latest full backup by restoring all the logs as well.

I have a dedicated machine for doing this, so that there is no conflict with anything happening on the standard landscape of servers.

The truth is that most everything works fine, but occasionally there is a corruption and that is followed as quickly as possible with a resolution.

Statistically you can test fewer databases and feel pretty safe, but checking them all gives you a quicker heads up.


Recovering Disk Space

As far as recovering InnoDB diskspace goes, you only have to migrate data out of ibdata1 and import it back one time if you enable innodb_file_per_table.

Once you do that, any InnoDB table that gets bloated can be shrunk. For example, if you have an InnoDB called mydb.mytable, you can shrink it with:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

or

OPTIMIZE TABLE mydb.mytable;

Restoring Databases

@Craig is totally right on MyISAM.

As for InnoDB, restoring tables individually can be a nightmare. This is the case because tables are either inside ibdata1 (innodb_file_per_table=0) or outside (innodb_file_per_table=1).

For tables outside ibdata1, this is a 24/7 nightmare because the tablespace id of the .ibd file may change if a table is dropped and recreated throughout the course of a business day. If you backup the .ibd with its tablespace id that day, you cannot restore it to it DB Folder by dropping it in. You also cannot do any tricks ALTER TABLE tblname DISCARD TABLESPACE and ALTER TABLE tblname IMPORT TABLESPACE because the tablespace id is hardcoded. You have to write some custom adhoc scripts to pull off such a miracle in conjunction with ALTER TABLE tblname DISCARD TABLESPACE and ALTER TABLE tblname IMPORT TABLESPACE.
For tables inside ibdata1, mysqldump is your friend. 'NUFF SAID...


Linked server connections to Multi-subnet failover cluster

The solution you linked to overcomes the problem with connecting to multi-subnet failover groups by using the MultiSubnetFailover=True connection string setting.

The way that setting deals with the problem is attempting many connections in parallel, rather than one after the other (the default behavior).  This increases the chances that a connection will be made successfully before reaching the connection timeout limit.

If you don't want to use that option (you mentioned not wanting the overhead of configuring ODBC connections on all your cluster servers), another solution would be to increase the (linked) server-level connection timeout setting to a value that's high enough to deal with the serial connection attempts.

The default connection timeout on linked servers is 10 seconds (represented by "0" on the settings screen).  You can increase the timeout to 20 seconds by doing the following:

EXEC master.dbo.sp_serveroption
    @server=N'YourLinkedServerName',
    @optname=N'connect timeout',
    @optvalue=N'20'

If you need to change the connect timeout to something higher because seconds 20 seconds is too low:

EXEC master.dbo.sp_serveroption
    @server=N'repl_distributor',
    @optname=N'connect timeout',
    @optvalue=N'60'

The docs on the default value for "connect timeout" are a little confusing.

sp_serveroption says

  Time-out valuein seconds for connecting to a linked server.

  If 0, use the sp_configure default.

sp_configure doesn't support an option called "connect timeout" but has one called "remote login timeout" (see Server Configuration Options).  The description from here sounds like the thing we're looking for:

  The remote login timeout option specifies the number of seconds to wait before returning from a failed attempt to log in to a remote server.
  ...
  The default value for this option is 10 seconds.

In practice, I just created a linked server locally with the default settings, turned off the target instance, and tried to query it.  Timed out at 10 seconds =)


Variable inside another variable in SQLCMD mode

Is there a way to have variable interpolation like this?

Kinda sorta, in a way. Just not directly.

You need to keep in mind a few things:

The value that you set a variable to via :setvar is a simple, literal value. You can see this by running just the following two lines:

:setvar var1 $(var2)
PRINT '$(var1)';

which returns:

$(var2)

If SQLCMD / SQLCMD-mode attempted to parse the value portion of :setvar in any way, then it would error on $(var2) not being defined.
Variable substitution is allowed in some other SQLCMD commands.

This means that you can, for example, execute a shell command while passing a SQLCMD variable to that command line:

:setvar db_suffix some_suffix
!! echo :setvar other_db [MyDB_$(db_suffix)] > c:\TEMP\setvar.txt

The two lines above will create / overwrite a file, C:\TEMP\setvar.txt, with the text of ":setvar other_db [MyDB_$(db_suffix)]" where $(db_suffix) is replaced with its value of "some_suffix".
SQLCMD commands in a file / script that is imported via the :r command are processed in a second-pass, allowing for variables set in those external files to be reflected in the main script:

:setvar db_suffix some_suffix
!! echo :setvar other_db [MyDB_$(db_suffix)] > c:\TEMP\setvar.txt
:r C:\TEMP\setvar.txt
PRINT 'somethin_somethin: $(other_db)';

Returns:

somethin_somethin: [MyDB_some_suffix]

SQLCMD commands are interpreted per-each batch !! So if you want to change the concatenated value, then you need to separate each creating and reading of the temp file, else the value you get will be the last one to be set since all of the !! and :r commands will be processed before the variable values are substituted in, and then the batch can be submitted to SQL Server to process the T-SQL. For example:

:setvar db_suffix some_suffixes

!! echo :setvar other_db [MyDB_$(db_suffix)] > c:\TEMP\setvar.txt
:r C:\TEMP\setvar.txt
PRINT 'somethin_somethin: $(other_db)';

--GO

!! echo :setvar other_db [NotMyDB_$(db_suffix)22] > c:\TEMP\setvar.txt
:r C:\TEMP\setvar.txt
PRINT 'somethin_somethin2: $(other_db)'

Will return:

somethin_somethin: [NotMyDB_some_suffixes22]
somethin_somethin2: [NotMyDB_some_suffixes22]

BUT, uncomment the --GO and you will get the following:

somethin_somethin: [MyDB_some_suffixes]
somethin_somethin2: [NotMyDB_some_suffixes22]


Import errors importing CSV to SQL Server table

At the end of the import/export wizard you have the option to save your SSIS package (yes that's what the wizard uses behind the scenes).  Save your package to a file then open it in SQL Server Data Tools.  Go into the data flow and open the source.  In the "Error Output" tab you will see an option to ignore failure (for truncation specifically or errors in general).  Modify it for the columns you are worried about.

All that being said you are probably(99.999%) not seeing a truncation because the column is to big to fit in a varchar(max).  What is probably happening is that SSIS "thinks" your column is shorter than it is.  If you go into the connection manager for your CSV file you can modify the "width" of your input columns in the advanced tab.


MySQL replication constantly checking tables

If your database tables are MyISAM, you might have given the server option myisam-recover in your my.cnf, which will turn on automatic MyISAM checking.

  If recovery is enabled, each time mysqld opens a MyISAM table, it checks whether the table is marked as crashed or was not closed properly.


Perl DBI takes 60 seconds to connect to an Oracle database, every single time

Thanks to @ThisSuitIsBlackNot for pointing me to the tracing functionaility in DBI, I was able to track this down to an error with not specifying port.  I knew that I should be pointing at 1521 when setting up other connections in other programs, but DBI didn't require port to function, it was just 60 seconds of waiting for it to give results.  DBI assumes 1526 to be the target port first unless told otherwise, so when targeting a server running on 1521, it was timing out after 60 seconds before trying 1521.

Original:

DBI -> port 1526 -> no response -> wait 60s -> no response -> fail -> port 1521 -> success

Specifying port:

DBI -> port 1521 -> success


The most significant balancing act to consider is whether your need for real-time reporting outweighs the performance hit that both your OLAP and OLTP systems will take from using your OLTP data source directly as fact and dimension tables, and then bouncing ROLAP/HOLAP queries off of them. If the tables are all quite small, and the server isn't under heavy load already, then the penalty is probably negligible. If SSAS queries are going to kick off 500MB+ reads, then that's a problem.

And unless you're doing some kind of high-frequency trading, you probably don't need to have your SSAS database that up to date. It seems like SSAS is more useful for big-picture kind of summaries, and if the past few hours of data aren't included yet, it's not going to make a big difference to those running the reports (ask around to be sure, obviously).

We load our data warehouses and process the cubes each night, and that's typically plenty. We also have a few simple reports built against OLTP tables for viewing more targeted, up-to-the-minute data (stuff that's really more OLTP than OLAP in nature).


Why is the 'AS' table alias keyword required in Visual Studio (Database projects)

I think this is happening because you're qualifying the database name in the query.

So remove **DBName** form the select clause; Visual Studio would then accept it.


Treat database field as a flat file source

My first inclination would be to use a script component (e.g. C#) as source in a Data Flow Task.  The script would just read the column contents, parse the individual records and fields, and send records down the pipeline.  The individual fields (perhaps strongly-typed) can be defined in the output record definition of the designer.

https://msdn.microsoft.com/en-us/library/ms136060.aspx contains an example of parsing a CSV file and processing a SQL query.  You can combine the two examples for your needs.  Below is an example of this. I don't have time to test right now but it should get you started.

public class ScriptMain:
    UserComponent

{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlDataReader sqlReader;

    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.MyADONETConnection;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {

        SqlCommand cmd = new SqlCommand("SELECT CsvContents FROM dbo.CsvImportTable;", sqlConn);
        sqlReader = cmd.ExecuteReader();

    }

    public override void CreateNewOutputRows()
    {

        while (sqlReader.Read())
        {
            {
                string csvContents = sqlReader.GetString(0);
                CreateNewOutputRowsFromCsv(csvContents);
            }
        }

    }

    public void CreateNewOutputRowsFromCsv(string csvContents)
    {

        string nextLine;
        string[] columns;

        char[] delimiters;
        delimiters = ",".ToCharArray();

        nextLine = textReader.ReadLine();
        while (nextLine != null)
        {
            columns = nextLine.Split(delimiters);
            {
                MyAddressOutputBuffer.AddRow();
                MyAddressOutputBuffer.AddressID = columns[0];
                MyAddressOutputBuffer.City = columns[3];
            }
            nextLine = textReader.ReadLine();
        }

    }

    public override void PostExecute()
    {

        sqlReader.Close();

    }

    public override void ReleaseConnections()
    {

        connMgr.ReleaseConnection(sqlConn);

    }

}


I Need Help With My Database Design for Product Management

This is just an "at-a-glance" high-level overview.

you have "ID" as your primary key for all your tables. Despite this being anathema to purists (pace Joe Celko and Fabian Pascal), personally, I have no problem with the use of such surrogate keys. However, I would advise that you rename these fields "product_id", "supplier_id" &c. for two reasons

ultimately it will make your SQL easier to read, and (relatedly)
will greatly help debugging - i.e. if you get an error saying that "... error with field ID = 123456...", you won't know to which table it is referring, whereas with my way, it's obvious.

Your product table is already becoming quite wide. If I were you, I'd split it it up this way.

You have various markets AFAICS (Amazon, FBA, EBay &c.). What happens when you have 47 markets? Your product table will be horrendous! :-). You should move these markets out into separate tables. Have an "amazon" table something like this

CREATE TABLE amazon (amazon_id int PK, product_id int FK, amazon_price... Other Amazon fields &c.).

Do the same for all your markets. Think about using VIEWs to combine this data for a given product later - as well as SQL. Rule of thumb: Tables should be like women, tall and slim not short and fat! :-)

You could also think about doing the same thing for your alternates. What happens if you have more than 1 for a given product? Putting these alternates in a separate table will allow you that flexibility down the road.

CREATE TABLE alternate (alternate_id int PK, product_id int FK, alt_supplier int FK, &c...)

You make use of that MySQL abomination, the ENUM type. Don't! They are evil©, and here's why. Never use it or that other invention of Beelzebub, the SET type.

Firstly, they breach Codd's rules. Secondly, they are non-standard. Imagine, 5 years down the road, your business has taken off and you want to upgrade from MySQL (any change from MySQL is an upgrade IMHO :-) )? Your system will require a complete rewrite rather than just tweaking if you've used ENUMs or SETs. They're a bit like drugs, great at first but coming off them is difficult and painful.

My final two points are my own preferences/biases (take your pick :-) )

you have a mixture of singular and plural table name (products, log_book). Pick one and stick to it - my own preference is for singular (1 exception, orders, for obvious reasons - never use SQL keywords as tablenames).
if you are only starting out on this project, I would strongly urge you to use PostgreSQL instead of MySQL. Many reasons for this:

CHECK CONSTRAINTs
Window (aka analytic) functions
Full support for set operators
CTEs (Common Table Expressions)

Having these built into your RDBMS will save you a lot of time and money in the long run rather than having to implement them yourself later.


SQL Server 2008 R2 installed on a virtual server

IMO, the SQL Server VM should live with the rest of the VMs. If this device goes kaput (the Cybernetics website says nothing about the number of controllers in this model range, so presumably there's only one), you don't want all your eggs in one basket. Hopefully there are other, better protected devices available.

  The SQL Server is more read intensive, with data bulk loaded nightly like a data warehouse and the web app used for looking up and reporting on the data.

You haven't said how big the data warehouse is or what the load time SLA is, but I strongly suspect RAID 5/6 is not going to be appropriate for that, particularly since 7.2k RPM drives are involved. High drive latency combined with RAID 5/6 is two feet in the grave for write performance.

The client can also look at optimizing the bulk loading process to be more efficient. It's likely there's inefficiencies in this area.

  Move backups to a different SAN or network location, in order to free up the F drive for SQL use as well as for better DR.
  Move log files and tempdb data files to the F drive.

Move the backups for DR: Yes, definitely. If there's space available on another device, use it.

Since the storage is all a big pool of RAID 6, the performance characteristics will be the same regardless of which logical volume is used, so I don't see any point in moving things around unless more space is needed.

  I am wanting to suggest the client reconfigure their SAN as follows:

Aside from moving the backups off this SAN, this configuration will probably make things worse:

Using RAID 1 for a single database log file is fine, but as soon as you have more than one log file, that's random access, and performance will nosedive.
Reads from and writes to data files were distributed over the 8 drives before, now the same load will be on 3 drives.

I would recommend using all 8 drives in one pool of RAID 10, which is the only RAID level that makes sense given the workload and the available hardware, and this is only 1 drive less usable space than your proposed configuration. There's just not enough wiggle room to micromanage the physical pools here. The whole thing will need to be redone if the initial sizing is wrong, or if the sizing changes drastically over time. Using one pool gives way more flexibility.

Now having said all of this, depending on how big the data set is, it's usually a really good ROI to buy more RAM so that SQL Server needs to do fewer physical reads in the first place, thus increasing performance. But that won't help with the write workload, of course.


There is one option as I know: one user - one database. Set Initial Size and disable Autogrowth (database options).


Is it always safe to use WITH SCHEMABINDING in a UDF?

In fact it's the other way around. If you check the Best Practices section of the CREATE FUNCTION doc, you'll see that:

If a user-defined function is not created with the SCHEMABINDING
clause, changes that are made to underlying objects can affect the
definition of the function and produce unexpected results when it is
invoked. We recommend that you implement one of the following methods
to ensure that the function does not become outdated because of
changes to its underlying objects:

Specify the WITH SCHEMABINDING clause when you are creating the    function. This ensures that the objects referenced in the function
definition cannot be modified unless the function is also modified.


Cases like this can usually be solved for certain by enabling trace events 10046 (if you have authority to do so), and run tkprof on the resulting trace files:

alter session set events '10046 trace name context forever, level 8';
select /* try one */ -- add your SQL here and invoke it;
select /* try two */ -- add your same SQL here and invoke it;
show parameter user_dump_destination

Invoke it a few times, and then turn tracing off, log off to ensure the trace files get closed:

alter session set events '10046 trace name context off';
exit;

Then find your trace file, and run tkprof on it, assuming your trace file name is ${ORACLE_SID}_ora_39110.trc:

cd $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace
tkprof ${ORACLE_SID}_ora_39110.trc ${ORACLE_SID}_ora_39110.trc.txt
vi ${ORACLE_SID}_ora_39110.trc.txt

The output of tkprof will show you execution statistics (logical reads, physical reads, etc). And you can look at the actual trace file if the tkprof output doesn't help.

If that doesn't help, you can enable 10053 event, and enjoy wading through thousands of lines of output; but sometimes you need to do that.

But first things (well easier things) first; do as others suggested; see if table statistics are up to date, optimizer settings, etc.  If you are using the exact same criteria each execution, could be the optimizer is "on the border" on plans.  I would ensure you have collected system statistics; see the documentation on dbms_stats.gather_system_stats pl/sql package.


Save performance with large update on Index with Included Column

So if your primary access path is by question, then the unique clustered index that makes the most sense will be (QuestionId, EventId).
Adding a second index to EventId may not be useful as the index might not be selective enough and the query engine will decide it's just faster to read the entire table instead of doing a lot of work to read a large portion of it.
Alternately, if you always query based wholly or in part on EventId, the clustered index of (EventId,Questionid) is more suitable, and has the added benefit of making your updates based on EventId require less I/O to complete.
I would not include Value to an additional index, as this will essentially copy the entire table (just clustered on different columns) and your updates will take even longer as Value must be kept in sync across both the clustered index and the additional index.
At a certain point there is no free lunch, and the correct solution is probably choosing the clustered index with the leading column that supports the most use cases, then adding RAM/CPU/faster storage to deal with cases where the entire table (or large chunks of it) must be read or written to.  With 40 million rows and such a narrow table I can't imagine this being something that more RAM wouldn't fix.
Depending on your edition of SQL Server, you can also look into if page compression would reduce the table size significantly, as this would reduce the number of reads/writes to disk (the additional CPU overhead is offset by fewer disk operations).  My guess in your case is it would not, but it's work looking at.

So if I understand correctly (with the current knowledge) your gut
feeling would be Clustered Index on QuestionId,EventId and then a
normal Index on EventId for the batch update?

Only if the primary use is to return specific QuestionIds without regard to EventId. You can try the additional index in EventId, but you may find it isn't being used often (or at all) for updates (or updates still take longer than you'd like), depending on how EventIds are distributed through your data with respect to QuestionId.
You also have to determine what is more important to you overall - select performance or update performance.  If the update is the pain point, (EventId,QuestionId) will undoubtedly be the better choice.  Given the number of unique values for QuestionId, adding an index on that column could be useful for SELECT performance, but that's going to depend on how QuestionId is distributed and how many you are searching for at a time.
Keeping statistics up to date will be crucial in either case.
An extremely simple example (for the sake of completeness):
Let's say we have a DBMS that maintains a clustered index and stores 4 rows per page.  We have a table with a primary key of (QuestionId, EventId) and one additional column, Value.
If we create the clustered index as (QuestionId, EventId), the data in our imaginary DBMS is (roughly speaking) stored like so:
Page | QuestionId | EventId | Value
-----------------------------------
A    | 1          | 2       | ...
A    | 1          | 3       | ...
A    | 1          | 6       | ...
A    | 1          | 7       | ...
B    | 1          | 8       | ...
B    | 1          | 10      | ...
B    | 1          | 11      | ...
B    | 2          | 2       | ...
C    | 3          | 2       | ...
C    | 4          | 1       | ...
C    | 5          | 6       | ...
C    | 5          | 7       | ...
D    | 6          | 1       | ...
D    | 7          | 2       | ...
D    | 7          | 6       | ...
D    | 7          | 8       | ...

So if I need to perform an operation based on QuestionId, the engine will not have to read more pages than necessary.
However, if I need to perform an operation based on EventId, I will have to read the entire table (clustered index scan), unless I add an additional index, which will look like this (and require four pages):
EventId | QuestionId
--------------------
1       | 4
1       | 6
2       | 1
2       | 2
2       | 3
2       | 7
3       | 1
6       | 1
6       | 5
6       | 7
7       | 1
7       | 5
8       | 1
8       | 7
10      | 1
11      | 1

This index be selective for some EventIds, but in the extreme case (EventId = 2) the whole table would still need to be read, and for some cases (EventId = 6) our optimizer might decide that searching the index and reading the table is more expensive than just reading the entire table.
If we instead cluster on EventId, QuestionId our table looks like this:
Page | EventId | QuestionId | Value
-----------------------------------
A    | 1        | 4         | ...
A    | 1        | 6         | ...
A    | 2        | 1         | ...
A    | 2        | 2         | ...
B    | 2        | 3         | ...
B    | 2        | 7         | ...
B    | 3        | 1         | ...
B    | 6        | 1         | ...
C    | 6        | 5         | ...
C    | 6        | 7         | ...
C    | 7        | 1         | ...
C    | 7        | 5         | ...
D    | 8        | 1         | ...
D    | 8        | 7         | ...
D    | 10       | 1         | ...
D    | 11       | 1         | ...

Any operation based on EventId will only read the necessary portions of the table, and like our first instance, any operation based on QuestionId will require a scan without an additional index.  If we create an index on QuestionId, the index will be:
QuestionId | EventId
--------------------
1          | 2
1          | 3
1          | 6
1          | 7
1          | 8
1          | 10
1          | 11
2          | 2
3          | 2
4          | 1
5          | 6
5          | 7
6          | 1
7          | 2
7          | 6
7          | 8

So as in the first example, this index will be more useful for some questions and less useful for others.  For QuestionId = 1 the optimizer will probably say the cost of reading half the index then looking up half the table isn't worth the cost and will just read the entire table instead of utilizing the index.
If we include Value on the index, we now have to change the table AND the index within the same transaction.  Best case, this doubles the work for any operation.  Worst case this requires the entire table or index (which is just a copy of the table) to be read and possibly locked.
Now it's possible with your actual data that adding an additional index on QuestionId or EventId will provide a lot of benefit.  But it's not going to solve every problem, and it might not be worth the overhead imposed on inserts/updates/deletes.


The query optimizer treats an inline table valued function exactly like a view:

CREATE FUNCTION dbo.InlineUdf(@arg1 int)
RETURNS TABLE
AS
RETURN
(
    ... your query here ...
);

A multi-statement table-valued function is run more like a stored procedure.  They typically have to be executed multiple times, rather than be folded into the main query:

CREATE FUNCTION dbo.MultiStatementUdf (@col1 int)
RETURNS @result TABLE
(
    id int primary key NOT NULL,
    ...
)
AS
BEGIN
   DECLARE @var1 int
   set @var1 = 42

   INSERT @result
   SELECT ...
   RETURN
END;


Convert seconds into HH:MM:SS in Informix

Using the below sql, I managed to give exactly what I am looking for. I used the number 6346 to display the output populating hours, minutes and seconds.
Query used:
select lpad(floor(6346 / 60 / 60),2,'0') || ':' || lpad(mod(6346/60, 60), 2, '0') || ':' || lpad(mod(6346,60), 2, '0') as run_duration_time from systables

Output:
01:45:46

Another example without hours:
select lpad(floor(1000 / 60 / 60),2,'0') || ':' || lpad(mod(1000/60, 60), 2, '0') || ':' || lpad(mod(1000, 60), 2, '0') as run_duration_time

from systables
Output:
00:16:40

Another example without hours and minutes:
select lpad(floor(45 / 60 / 60),2,'0') || ':' || lpad(mod(45/60, 60), 2, '0') || ':' || lpad(mod(45, 60), 2, '0') as run_duration_time

from systables
Output:
00:00:45


Why is multiple independent index always better than concatenated index

Original author here.

The quote is meant as following:

In your first query, just filtering on B and C, what would be better:

Indexing 1: Index B, Index C
Indexing 2: Index (B,C)

Clearly, Indexing 2 is better.

The context was meant to be: if I index all columns, is it better to have one index with all of them, or one separate index for every column. In that case, it is almost always better to have one with all columns.

It's a design advice, not for assessment.


Trying to update a table with a customer sales averaging column - Oracle SQL

In the UPDATE statement you do not calculate the AVG per customer but an AVG on all customers. Put the customer identification in the where clause. The GROUP BY option is not needed since the AVG column is the only column in the SELECT statement (see other reply by @_horse_with_no_name):

UPDATE t1
SET AVG_AMOUNT=(SELECT AVG(INVOICE_TOTAL)
                FROM   t1 a1
                where  t1.customer_ID = a1.customer_ID);


How to make iSQL/FreeTDS behave like a normal client?

I have seen this before. According to the Sybase ASE Documentation:

  Adaptive Server Enterprise implements dynamic SQL using temporary stored procedures. A temporary stored procedure is created when a SQL statement is prepared, and destroyed when that prepared statement is deallocated...[a]s a consequence of this implementation, an application accessing Adaptive Server and using dynamic SQL is subject to the restrictions of Adaptive Server stored procedures.

However, I'm not sure if it's the unixODBC interface, or the isql tool, that is asking the ASE to treat these as stored procedures / dynamic sql.


How to import large sql file in postgres?

psql generally doesn't need much memory when playing large SQL files, since it doesn't buffer the whole file, only one query at a time, or it uses a COPY stream.

The main situation when it may run out of memory is not when importing, but when SELECT'ing a large resultset, especially on 32 bits systems. This situation is generally solved by setting FETCH_COUNT.

On import, the dump would need to contain unusually large rows to cause client-side memory issues. The maximum size of a column is 1Gb, so it's theorically possible that it would cause trouble on import, if the database has that kind of contents.

If the system doesn't have enough RAM, maybe you could just add swap space.


Playing a bit with pg_buffercache, I could get answers to some of your questions.

This is quite obvious, but the results for (5) also show that answer is YES
I am yet to set up a good example for this, for now it is more yes than no :) (See my edit below, the answer is NO.)
Since the planner is who decides whether to use an index or not, we can say YES, it decides caching (but this is more complicated)
The exact details of caching could be derived from the source code, I couldn't find too much on this topic, except this one (see the author's answer, too).  However, I'm pretty sure that this again is far more complicated than a simple yes or no.  (Again, from my edit you can get some idea - since the cache size is limited, those 'sensible' indexes compete for available space.  If they are too many, they will kick each other from cache - so the answer is rather NO.)
As a simple query with pg_buffercache shows, the answer is a definitive YES.  It is worth to note that temporary table data don't get cached here.

EDIT

I've found Jeremiah Peschka's terrific article about table and index storage.  With information from there, I could answer (2) as well.  I set up a small test, so you can check these yourself.

-- we will need two extensions
CREATE EXTENSION pg_buffercache;
CREATE EXTENSION pageinspect;

-- a very simple test table
CREATE TABLE index_cache_test (
      id serial
    , blah text
);

-- I am a bit megalomaniac here, but I will use this for other purposes as well
INSERT INTO index_cache_test
SELECT i, i::text || 'a'
FROM generate_series(1, 1000000) a(i);

-- let's create the index to be cached
CREATE INDEX idx_cache_test ON index_cache_test (id);

-- now we can have a look at what is cached
SELECT c.relname,count(*) AS buffers
FROM
    pg_class c
    INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
    INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;

             relname              | buffers
----------------------------------+---------
 index_cache_test                 |    2747
 pg_statistic_relid_att_inh_index |       4
 pg_operator_oprname_l_r_n_index  |       4
... (others are all pg_something, which are not interesting now)

-- this shows that the whole table is cached and our index is not in use yet

-- now we can check which row is where in our index
-- in the ctid column, the first number shows the page, so
-- all rows starting with the same number are stored in the same page
SELECT * FROM bt_page_items('idx_cache_test', 1);

 itemoffset |  ctid   | itemlen | nulls | vars |          data
------------+---------+---------+-------+------+-------------------------
          1 | (1,164) |      16 | f     | f    | 6f 01 00 00 00 00 00 00
          2 | (0,1)   |      16 | f     | f    | 01 00 00 00 00 00 00 00
          3 | (0,2)   |      16 | f     | f    | 02 00 00 00 00 00 00 00
          4 | (0,3)   |      16 | f     | f    | 03 00 00 00 00 00 00 00
          5 | (0,4)   |      16 | f     | f    | 04 00 00 00 00 00 00 00
          6 | (0,5)   |      16 | f     | f    | 05 00 00 00 00 00 00 00
...
         64 | (0,63)  |      16 | f     | f    | 3f 00 00 00 00 00 00 00
         65 | (0,64)  |      16 | f     | f    | 40 00 00 00 00 00 00 00

-- with the information obtained, we can write a query which is supposed to
-- touch only a single page of the index
EXPLAIN (ANALYZE, BUFFERS)
    SELECT id
    FROM index_cache_test
    WHERE id BETWEEN 10 AND 20 ORDER BY id
;

 Index Scan using idx_test_cache on index_cache_test  (cost=0.00..8.54 rows=9 width=4) (actual time=0.031..0.042 rows=11 loops=1)
   Index Cond: ((id >= 10) AND (id <= 20))
   Buffers: shared hit=4
 Total runtime: 0.094 ms
(4 rows)

-- let's have a look at the cache again (the query remains the same as above)
             relname              | buffers
----------------------------------+---------
 index_cache_test                 |    2747
 idx_test_cache                   |       4
...

-- and compare it to a bigger index scan:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id
    FROM index_cache_test
    WHERE id <= 20000 ORDER BY id
;

 Index Scan using idx_test_cache on index_cache_test  (cost=0.00..666.43 rows=19490 width=4) (actual time=0.072..19.921 rows=20000 loops=1)
   Index Cond: (id <= 20000)
   Buffers: shared hit=4 read=162
 Total runtime: 24.967 ms
(4 rows)

-- this already shows that something was in the cache and further pages were read from disk
-- but to be sure, a final glance at cache contents:

             relname              | buffers
----------------------------------+---------
 index_cache_test                 |    2691
 idx_test_cache                   |      58

-- note that some of the table pages are disappeared
-- but, more importantly, a bigger part of our index is now cached

All in all, this shows that indexes and tables can be cached page by page, therefore the answer for (2) is NO.

And a final one to illustrate temporary tables being non-cached here:

CREATE TEMPORARY TABLE tmp_cache_test AS
SELECT * FROM index_cache_test ORDER BY id FETCH FIRST 20000 ROWS ONLY;

EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM tmp_cache_test ORDER BY id;

-- checking the buffer cache now shows no sign of the temp table


Backup without using SQL Server Management Studio

You can use sqlcmd from the command prompt.

It's default location for SQL 2012 is here :- C:\Program Files\Microsoft SQL Server\110\Tools\Binn

For windows authentication run :- sqlcmd -SSERVERNAME

or for sql authentication run:- sqlcmd -SSERVERNAME -ULogin -PPassword

You can then run a backup sql statement.

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


My question is, with this partial index, wouldn't the database have to look up rows with processed not null (which is not fully indexed, so must be slow) before it gets to the indexed part of the table (which in this case is processed itself!)?

No, an index on IS NOT NULL has no need in itself to visit the table on queries restricted to IS NOT NULL or on queries which can be determined to be not-null. In fact, on queries restricted to the column where the value IS NOT NULL, you could potentially be doing an INDEX ONLY SCAN which I demonstrate here.

CREATE TABLE foo AS
  SELECT x::int, null::int AS bar
  FROM generate_series(1,1e6)
    AS gs(x);
UPDATE foo
  SET bar = 42
  WHERE x = 185093;

CREATE INDEX ON foo (bar)
  WHERE bar IS NOT NULL;

VACUUM ANALYZE foo;

EXPLAIN ANALYZE SELECT * FROM foo WHERE bar = 42;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Index Scan using foo_bar_idx on foo  (cost=0.12..8.14 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)
   Index Cond: (bar = 42)
 Planning time: 0.295 ms
 Execution time: 0.047 ms
(4 rows)


Can an Oracle IDENTITY column be nullable?

You could make the column NULLable, but not make it an IDENTITY column; instead, create a sequence, and an INSERT trigger that sets the ID value from the sequence. Prior to version 12.1, IDENTITY columns weren't available, and using a sequence was the workaround.


Should I manually VACUUM my PostgreSQL database if autovacuum is turned on?

I agree with ETL that there is no short answer. Size is not the only thing that matters - we run quite large PostgreSQL OLTP Databases (with some tables > 100.000.000 rows) under heavy load and currently we rely on autovacuum only.

Yet, two things seem important to me:

There seems to be a consensus, that autovacuum should never be switched off, unless you have a very well defined workload on your database and you know exactly what you are doing. But, naturally, you could do additional VACUUM and/or ANALYZE runs.
Before considering additional VACUUM runs, I would check how autovacuum keeps up. You can check whether any tables are beyond the autovacuum threshold by querying pg_stat_user_tables and pg_class. I posted such a query on another thread, that might be of interest: Aggressive Autovacuum on PostgreSQL.

Unfortunately, it is not as easy (i.e. not possible at the moment) to do a similar check for autoanalyze thresholds. However, autoanalyze kicks in long before autovacuum by default and it is much cheaper. So, basically if your database can keep up with autovacuum, it will probably be fine with autoanalyze too. The last autoanalyze dates can also be queried from pg_stat_user_tables.

Some parts of the (most excellent) PostgreSQL documentation, that I found helpful:

Routine Vacuuming
The Statistics Collector


mysqld got signal 11 - it's my fault but how do I fix it?

After a bunch more googling, etc., I finally came upon the solution: http://notesonit.blogspot.hk/2013/05/innodb-unable-to-lock-ibdata1-error-11.html


As developer, not a full-time DBA, I use Postgres for some of my work. But it is not my focus. I found Postgres very confusing and frustrating when getting started. After a long career using 4D, I was an pro in relational database design and normalization, but a newbie with SQL and "black-box" database engines. Below is a list of the resources that helped me.

I look at Postgres not as a product to be mastered such as a new IDE or email client app. I see it more as a computer system unto itself, to be explored bit by bit. Some areas I may never touch (PostGIS, geo data), while others are vital to me (UUID data type, XML data type, date-time data types).

➥ So I see "learning Postgres" not as a goal to be accomplished but as an on-going process.

The more I learn about Postgres, the more impressed and awed I become. It took some investment of time and effort, but now I ♥ 🐘 (the Blue Elephant).

Tips

Smart Defaults

When in doubt, go with Postgres’ defaults.

UTF-8

One exception to the 'smart defaults' tip: Almost always best to specify UTF-8 as the character encoding. Do so when installing Postgres, as this defines the default when you later create databases. When you do create databases, verify that UTF-8 is the encoding is in use.

Security

Postgres is designed for security as a priority. By default you can only connect from a process/app running locally on the same machine. You must change the security configuration to connect from across a local network or internet.

Installers

To get started you must install Postgres. The main 3 ways to do that:

Compile from source.
Download easy-to-use installers built and provided by the EnterpriseDB as a courtesy to the community. They provide installers for their commercial enhanced version of Postgres as well as plain-vanilla free-of-cost Postgres for Mac OS X, Linux, and Windows.
For Mac OS X users, Postgres.app is an alternative, a double-clickable Mac app.

On some hosted servers such as Heroku or Amazon, the host may offer to spin up Postgres on your behalf. So no installation required in that case.

Regarding uninstalling:

Uninstall Postgres 10 on macOS – EnterpriseDB installer

Client-Server

Postgres is a black-box, hiding your data inside. To access, you must use a utility tool to make a connection to the Postgres daemon process. For getting started, use either of these tools usually included with your Postgres installation.

pgAdmin for GUI app access
psql for command-line access

Virtual Machines

When playing around or experimenting with Postgres, I suggest using a virtual machine so you can later throw it away. Postgres is a pretty heavy installation, creating a new Unix-level user. Entirely removing Postgres can be a bit tricky.

Local

I use Parallels 9, 10, and 11 for virtual machines running Mac OS X Mountain Lion or El Capitan to install various versions of Postgres 9.*. On a Mac, besides Parallels, you might be able to use VMware Fusion or Oracle VirtualBox.

Cloud

Another route is a cloud server. Renting by the hour can be quite inexpensive, perhaps in the range of a penny (USD $0.01) or nickel or dime an hour. You should be able to delete your virtual machine when done or when you want to start from scratch with a fresh one. Examples include DigitalOcean, Heroku, and Amazon.

In-Person Resources

PUGs

Find a PUG. Not a dog, a Postgres User Group. Search MeetUp.com and search this list.

Attend every meeting. Let discussions wash over you even when you cannot understand it all. Postgres has many features, add-ons, related products. Postgres has much depth. So don't expect to master Postgres, just keep looking for ways to use it.

If you cannot find one near you, start one.

Experts

Learn the names of some of the Postgres experts who are generous about sharing their knowledge. Pay special attention when they speak. For example, Craig Ringer on this page. Other examples, Josh Berkus, Bruce Momjian, David E. Wheeler, Selena Deckelmann.

Events

pgDay

You can find one-day "pgDay" events as well as multi-day Postgres conferences all over the world. For example, France, Prague, India, Finland, and Singapore. See this list.

Multi-Day Conferences

In North America the multi-day conferences include:

PGCon in Ottawa.
pgOpen in Chicago.

Other Events

You can often find a Postgres booth at other geek events as well.

For example, visit me while volunteering at the SeaPUG booth next April at LinuxFest Northwest 2016 in Bellingham WA, USA (outside Seattle). Postgres consulting companies have had booths in the past.

Online Resources

Postgres Weekly

Read Postgres Weekly, a collection of select Postgres-related articles and news. Curated by an expert, Craig Kerstiens. Published weekly, thus the name.

Mailing Lists

Subscribe to the Postgres mailing lists.

I find it enlightening to skim through the weekly PostgreSQL Weekly News email delineating that week's work including a list of patches submitted. Posted on the Announce list. All the nitty-gritty details are beyond my comprehension, but nevertheless I become more and more familiar. I learn a bit about the many different features inh Postgres. And I learn how seriously the team takes their work, with an emphasis on solid quality.

Planet Postgres, blog portal

Planet Postgres is a consolidator of Postgres-related blogs.


How to convert DB2 9.5 to DB2 10.5 online (Linux)

You cannot migrate the database online.

You can use InfoSphere DR or any other replication software for migration with minimal downtime, however, if your application itself does not support transparent connection failover you will still experience a brief outage.

The process in brief would look like so:

Install DB2 10.5.
Create a new database in a DB2 10.5 instance, using db2look.
Install and configure replication software.
Perform a full refresh from the old (V9.5) database to the new one.
Continue mirroring until the new database catches up to the old one.
Initiate application failover.
Stop replication once the application has fully failed over.


Postgresql update slow on a relative small/medium table

Instead of updating hundreds of row at once, which will always take sometime with that much rows
make an UPDATE TRIGGER, which automatically updates the timestamp for every row, whrn an update occurs.
CREATE OR REPLACE FUNCTION update_changetimestamp_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.last_updated = now();
   RETURN NEW;
END;

AND
$$ language 'plpgsql';
CREATE TRIGGER update_det_price_list_timestamp BEFORE UPDATE
ON ab FOR EACH ROW EXECUTE PROCEDURE
update_changetimestamp_column();


How do I create a global MongoDB Admin account to see all Dbs?

You created the user correctly, but you missed a parameter when connecting to mongod.

By default, the authentication process assumes that the user is stored in the database you connect to, which in your case simply isn't true.

You need to specify the autheticationDatabase option:

mongo yourMongodIpAddress:27000/targetDb \
--authenticationDatabase admin -u DBASupport -p yourPw


While operations on the data in an unlogged table are unlogged the creation of the unlogged table is actually logged as this is an operation on the system catalog. Thus normal recovery would happen. So depending on how far it actually was it would complete creation of the table or discard the action during recovery.


In SQL Server 2012 or later, I would implement this using sequence objects.

For SQL Server 2008 R2, my replacement for that missing feature is Sequence Tables. In this case, there would be a key in the master sequence table for each year, for example:

CREATE TABLE dbo.SequenceTable
(
    sequence_name   nvarchar(20) NOT NULL,
    next_value      integer NOT NULL,

    CONSTRAINT [PK dbo.SequenceTable sequence_name]
    PRIMARY KEY CLUSTERED (sequence_name),
);
GO
INSERT dbo.SequenceTable
    (sequence_name, next_value)
VALUES
    (N'PermitIDs for 2016', 1),
    (N'PermitIDs for 2017', 1),
    (N'PermitIDs for 2018', 1);

The standard allocation stored procedure to robustly allocate a key or range of keys from a sequence is:

CREATE PROCEDURE dbo.Allocate_TSQL

    @SequenceName       nvarchar(20),   -- The name of the sequence to allocate keys from
    @RangeSize          integer = 1,    -- The number of keys to allocate
    @FirstAllocated     integer OUTPUT  -- The first key allocated (output)

AS
BEGIN

    SET XACT_ABORT ON;  -- Most errors will abort the batch
    SET NOCOUNT ON;     -- Supress 'x row(s) affected' messages
    SET ROWCOUNT 0;     -- Reset rowcount

    -- Validate the range size requested
    IF (@RangeSize IS NULL OR @RangeSize < 1)
    BEGIN
        RAISERROR('@RangeSize must be a positive integer (supplied value = %i)', 16, 1, @RangeSize);
        RETURN  999;
    END;

    -- Initialize the output parameter
    SET @FirstAllocated = NULL;

    -- Update the row associated with @SequenceName, returning the
    -- current value, and then incrementing it by @RangeSize
    UPDATE dbo.SequenceTable WITH (READCOMMITTEDLOCK)
    SET @FirstAllocated = next_value,
        next_value = next_value + @RangeSize
    WHERE sequence_name = @SequenceName;

    -- If @Allocated has a non-NULL value, we know we successfully updated a row
    RETURN CASE WHEN (@FirstAllocated IS NOT NULL) THEN 0 ELSE -999 END;
END;

Then, given a simplified version of the table in the question:

CREATE TABLE dbo.ImportPermits
(
    ImportPermitID integer IDENTITY (1, 1)
        CONSTRAINT [PK dbo.ImportPermits ImportPermitID]
        PRIMARY KEY CLUSTERED,
    ImportPermitNo nchar(12) NULL,
    LodgementDate datetime NULL
);

We can assign sequence numbers per year using the following trigger:

CREATE TRIGGER ImportPermitsImportPermitNo
ON dbo.ImportPermits
AFTER INSERT AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;   -- Return immediately if no rows affected
    SET XACT_ABORT, NOCOUNT ON; -- Most errors abort the batch; no row count messages
    SET ROWCOUNT 0;             -- Ensure all rows are visible (local to trigger)

    DECLARE
        @rc integer,
        @FirstAllocated integer,
        @RowCount integer,
        @Years integer,
        @SeqName nvarchar(20);

    -- Count rows and distinct lodgement years in the insert set
    SELECT
        @RowCount = COUNT(*),
        @Years = COUNT(DISTINCT(YEAR(INS.LodgementDate))),
        @SeqName = N'PermitIDs for ' +
            CONVERT(nchar(4), MIN(YEAR(INS.LodgementDate)))
    FROM Inserted AS INS;

    -- Check for multiple lodgement years (not implemented)
    IF @Years > 1
    BEGIN
        RAISERROR('Multiple LodgementDate years are not supported.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- Allocate the range of sequence numbers we will need
    EXECUTE @rc = dbo.Allocate_TSQL
        @SequenceName = @SeqName,
        @RangeSize = @RowCount,
        @FirstAllocated = @FirstAllocated OUTPUT;

    IF @rc <> 0
    BEGIN
        RAISERROR('Sequence allocation failed.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- Assign ImportPermitNo values using the sequence numbers allocated
    WITH Sequenced AS
    (
        SELECT
            IP.ImportPermitNo,
            IP.LodgementDate,
            Seq =
                @FirstAllocated - 1 +
                ROW_NUMBER() OVER (
                    ORDER BY IP.LodgementDate ASC)
        FROM Inserted AS INS
        JOIN dbo.ImportPermits AS IP
            ON IP.ImportPermitID = INS.ImportPermitID
    )
    UPDATE Sequenced
    SET Sequenced.ImportPermitNo =
        N'IP' +
        CONVERT(nchar(4), YEAR(Sequenced.LodgementDate)) +
        RIGHT(N'000000' + CONVERT(nvarchar(11), Sequenced.Seq), 6);
END;

For brevity, the trigger is limited to inserting row(s) from a single year only, but it is not difficult to extend the logic.

Demo: db<>fiddle


You add the column, in both parts of the recursive CTE.

Starting nodes have the ID as startingNode id.

The descendants have as starting node id, the starting node id of their parent:

WITH ret AS
  (
    SELECT id AS idStartingNode, o.*
    FROM    osoba o
    --WHERE   ID = 1
    UNION ALL
    SELECT r.idStartingNode, o.*
    FROM    osoba o INNER JOIN
            ret r ON o.idNd = r.ID
  ) ;


Effective duplication of DELETE ... LIMIT in Pg

It's easy with window functions. Say you want to delete the last @N rows when ordered by some_column. You assign a ROW_NUMBER() with a CTE or a derived table and then DELETE:

WITH del AS
  ( SELECT pk,
           ROW_NUMBER() OVER (ORDER BY some_column DESC) AS rn
    FROM theTable
    WHERE <conditions>
  )
DELETE FROM theTable
USING del
WHERE theTable.pk = del.pk         --- the PRIMARY KEY of the table
  AND del.rn <= @N ;               --- your parameter here

or without the USING, in a more standard way:

WITH del AS
  ( SELECT pk,
           ROW_NUMBER() OVER (ORDER BY some_column DESC) AS rn
    FROM theTable
    WHERE <conditions>
  )
DELETE FROM theTable
WHERE pk IN
      ( SELECT pk FROM del WHERE rn <= @N ) ;

Test at SQLFiddle.com


What is the best way to apply production changes (DML & DDL) in an environment with thousands of databases?

In very high level, I would suggest two points (which I always follow)

Automation: highly recommend to use PowerShell to do the various change implementation steps.
Rollback plan: always have a rollback plan in place, but rollback can be in different ways, such as backup & restore, creating a snapshot, recreating an object using pre-implementation version etc.

But in my experience, some very tricky work is security related issue, such as dropping a view and recreating it, all the previously granted permissions are lost, usually, this is not known until users start to complain.

Anyway, your sql environment and your business requirements will play key roles in your Change Management strategy.


Crow's Foot notation and identifying -vs- non-identifying relationships

Information Engineering methodology placed a big "I" on the relationship to show the foreign key was part of the primary key:


Dockerized Sysbench could not connect to Mysql

With the help of mustaccio I've resolved the issue by following instructions in this answer:

Removed a comment sign before the next string in /etc/my.cnf:

default_authentication_plugin=mysql_native_password
Connected as root:

mysql -u root -pMyPassword+1
Executed the next query:

ALTER USER 'sbtest'@'%' IDENTIFIED WITH mysql_native_password BY 'MyPassword+1';

After following these instructions Sysbench successfully connected to Mysql 8 and executed all preparation tasks successfully.


Implementation of JDBC DataSource in MySQL Connector/J 8

The driver has com.mysql.cj.jdbc.MysqlDataSource (there is also a ConnectionPoolDataSource and an XADataSource implementation). The actual implementation of MysqlDataSource uses the JDBC driver directly, similar to what DriverManager would do.
The MySQL Connector/J hardly mentions data sources. In fact, the only reference I could find to MysqlDataSource was in Changes in the Connector/J API. I'm not sure if it is a conscious choice by the MySQL documentation team, but it usually makes little sense to use MysqlDataSource directly in your code (it doesn't provide connection pooling), and MysqlConnectionPoolDataSource and MysqlXADataSource are not for direct usage nor are they a connection pool (instead they serve as a factory for connections for a Java EE data source that provides connection pooling and/or distributed transactions).
A lot ways of using JDBC involves using third-party data sources providing a connection pool (eg HikariCP, DBCP, c3p0), and those usually use DriverManager directly (though some also allow you to provide a data source implementation), or through a data source of a JavaEE/JakartaEE application server. It is usually better to use those than to rely on the data source implementation of a driver.


Assuming that the temporary tables that you are referencing exists, then your problem is session scope. Temporary tables marked with a single # are visible to the session that they are created in, they are also known as local temporary tables.

The problem that you have is that when you execute a piece of dynamic sql it runs in a new session - hence that session cannot see the temporary tables that you have created.

To fix the problem you could do one of 3 things:

Create the temporary table within your dynamic sql
Use a global temporary table using ## as these are visible across all sessions
Re-design your code to insert into a table variable and return that as an output parameter to sp_executesql

I hope this helps you.


MYSQL: What is the benefit of compound indexes, over separate ones?

If you know that you frequently query on a combination of fields, a combined index means "you only have to search one B-tree."  Otherwise, the server would have to search n individual B-trees and then combine the results.

(N.B.:  A "B-tree" is a data structure commonly used by database servers to create indexes ...)

(You can use the EXPLAIN command to quickly see how the server plans to do this ... and to see that it does have to do this.)


What are some key configuration settings for using an InnoDb table for session storage?

You could experiment with innodb_flush_log_at_trx_commit

According to the MySQL Documentation on innodb_flush_log_at_trx_commit

  If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is
  written out to the log file once per second and the flush to disk
  operation is performed on the log file, but nothing is done at a
  transaction commit. When the value is 1 (the default), the log buffer
  is written out to the log file at each transaction commit and the
  flush to disk operation is performed on the log file. When the value
  is 2, the log buffer is written out to the file at each commit, but
  the flush to disk operation is not performed on it. However, the
  flushing on the log file takes place once per second also when the
  value is 2. Note that the once-per-second flushing is not 100%
  guaranteed to happen every second, due to process scheduling issues.

  The default value of 1 is required for full ACID compliance. You can
  achieve better performance by setting the value different from 1, but
  then you can lose up to one second worth of transactions in a crash.
  With a value of 0, any mysqld process crash can erase the last second
  of transactions. With a value of 2, only an operating system crash or
  a power outage can erase the last second of transactions. InnoDB's
  crash recovery works regardless of the value.

  For the greatest possible durability and consistency in a replication
  setup using InnoDB with transactions, use
  innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master
  server my.cnf file.

  Caution

  Many operating systems and some disk hardware fool the flush-to-disk
  operation. They may tell mysqld that the flush has taken place, even
  though it has not. Then the durability of transactions is not
  guaranteed even with the setting 1, and in the worst case a power
  outage can even corrupt the InnoDB database. Using a battery-backed
  disk cache in the SCSI disk controller or in the disk itself speeds up
  file flushes, and makes the operation safer. You can also try using
  the Unix command hdparm to disable the caching of disk writes in
  hardware caches, or use some other command specific to the hardware
  vendor.

Based on this, values other than 1 put InnoDB at risk of losing 1 second's worth of transactions, or a transaction commit's worth of data.

As far as innodb_flush_method, the default is best. I wrote an earlier post to describe the effects of tweeking it.

As for running multiple instances of MySQL, it's OK as along as you have enough memory for the OS and enough memory of DB Connections for the multiple instances.


How to install Oracle 10g Express and 11g Enterprise on same machine

It appears that you don't need to worry too much.

Install XE first.  You don't get to choose the database name (XE) or the port (1521).
Next, install 11g to the default app folder.
Modify the TNSNAMES.ora files for both databases so they know about each other.  Test connections and create database links and you are good to go.


Rebuild the indexes on the table to a filegroup instead of a partition schema.

Do so in the following order:

CREATE INDEX  ON [MyFileGroup] WITH DROP EXISTING on the cluster index
CREATE INDEX  ON [MyFileGroup] WITH DROP EXISTING on all non clustered indexes (one at a time)

If you can take some downtime, consider scripting and dropping the non-clustered indexes first, rebuild the cluster index and then reapply the non-clustered indexes (targeted at the filegroup, not the partition schema of course)

This is faster than merging the partitions together because it can run at high concurrency using the SQL Server index build engine.

With this trick, the schema remains in place, no need to move it all to another table. Depending on your SQL Server edition, you may even be able to do this online.