How to Get one Single statement of two table value in SQL

It's not exactly the wanted output but pretty close:

Query for versions 2012+:

; WITH invoices_payments AS
  ( SELECT StatementDate = InvoiceDate, InvoiceNo, TotalDue, TotalPaid = 0.0,
           ID = InvoiceID, Type = 0
    FROM Invoice_TBL
    WHERE ClientNo = 1

    UNION ALL

    SELECT PaymentDate, '', 0.0, TotalAmountPaid,
           ID = PaymentID, Type = 1
    FROM Payment_TBL
    WHERE ClientNo = 1
  )
SELECT StatementDate, InvoiceNo, TotalDue, TotalPaid,
       TotalBalance = SUM(TotalDue - TotalPaid)
                        OVER (ORDER BY StatementDate, Type, ID
                              ROWS UNBOUNDED PRECEDING)
FROM invoices_payments
ORDER BY StatementDate, Type, ID ;

Test at SQLfiddle. I've assumed that you have a primary key (used InvoiceID and PaymentID) in the tables and I used it to make the ordering 100% determinate (so where you have more than one invoices or payments in the same day, you always get the same order in the results.)

For versions before 2012, there are various ways, none as efficient as the OVER (ORDER BY ... ROWS ...) of 2012. You can check this blog post by Aaron Bertrand about various approaches:
Best approaches for running totals – updated for SQL Server 2012. The more efficient (for 2008R2 versions and older) are - as the blog post tests show - the cursor and the CLR approaches.

Following is one way using a self-join:

; WITH invoices_payments AS
  ( SELECT StatementDate = InvoiceDate, InvoiceNo, TotalDue, TotalPaid = 0.0,
           ID = InvoiceID, Type = 0
    FROM Invoice_TBL
    WHERE ClientNo = 1

    UNION ALL

    SELECT PaymentDate, '', 0.0, TotalAmountPaid,
           ID = PaymentID, Type = 1
    FROM Payment_TBL
    WHERE ClientNo = 1
  )
SELECT a.StatementDate, a.InvoiceNo, a.TotalDue, a.TotalPaid,
       TotalBalance = SUM(b.TotalDue - b.TotalPaid)
FROM invoices_payments AS a
  JOIN invoices_payments AS b
    ON b.StatementDate < a.StatementDate
    OR b.StatementDate = a.StatementDate AND b.Type < a.Type
    OR b.StatementDate = a.StatementDate AND b.Type = a.Type AND b.ID <= a.ID
GROUP BY a.StatementDate, a.Type, a.ID, a.InvoiceNo, a.TotalDue, a.TotalPaid
ORDER BY a.StatementDate, a.Type, a.ID ;


SELECT blocks UPDATE in Snapshot Isolation

if @@trancount <> 0 then that SELECT will run with the current transaction isolation level.  Which (unless it already happens to be SNAPSHOT) will require S locks for reading data.

If you want all SELECTS using the default READ COMMITTED isolation level to use row versioning instead of S locks, set READ COMMITTED SNAPSHOT on the database.


Create an EVENT to periodically UPDATE a stats table that stores the "last_ID" to which each of the 70 rankings was last pulled, and the current counts for each.

Each UPDATE on the stats table entries (one per ranking), should join the real table with the entries you are counting, and have the respective criteria, setting stats.count to stats.count+real_tbl.COUNT(*), AND adding WHERE criteria to only consider WHERE id > stats.last_id. You will therefore also be setting stats.last_id to now = MAX(realtbl.id)

Each time the update runs, it will be limited to counting only entries added since the last run, and it will find them by the primary key index (ultra fast).


You can achieve this by creating 3 or 4 tables:

Company Information
Fundamental Entries
Fundamental Data
MarketCap Data (Optional)

Company information will contain information about all the companies, assign a Company ID to each company, which would be later used in to join the financial fields. Use some SCD type( slowly changing dimensions) based on your requirements, fields like Ticker changes often. So carefully assign a company ID.

Unpivot and divide your financial data between Entries and values.

In the Fundamental Entries table generate a unique ID (eg ID_FS_ENT) for each company ID and each financial entry (that would be a combination of Period End, Accounting Standard, Fiscal Period, etc.)

In the financial Data table store the financial value along with the ID_FS_ENT, Field Name (that would be shares outstanding, total fixed assets, earnings etc)

If you are also receiving daily market cap for each company then store it separately. Again use the Company ID, add trading date and value to this table.

Hope this helps!

P.S. I am already managing a similar database with more than 100,000 companies for more that 78 economies and around 100 million financial entries.


Finding code tab's associated schema in MySQL Workbench

Actually, there's no schema associated with a certain code tab. All tabs use the active schema. There's already a feature request for a schema-per-tab feature.

Update: you meant object editors and indeed there's no indication to which schema they belong. Please file a feature request to get this into MySQL Workbench (http://bugs.mysql.com).


Upload multiple files to SQL Server without access to the Server Box

If there are no transformations involved, you could write this process fairly simply in SSIS with a for loop in the control flow that iterates over a file directory, then passes each file name as a variable to a data flow.  The filename for the csv would be bound to that variable, and your destination could similarly be bound to the file name.  Just use bulk mode in your target component and you should get pretty good efficiency.  This works great if the folder is accessible to all users and you add a step to either check and see that files in the directory are processed or move them somewhere else when they've been processed.  The SSIS process could be invoked from the SSIS catalog on the SQL Server directly, or scheduled on a regular basis.

Alternatively, you could use the bcp Utility for a CLI experience.  This is probably a lot simpler in that it doesn't require writing an SSIS process and management of that process, but it requires more knowledge on the end of the user invoking it.  This tool doesn't require the files be on the server: you provide a path to the file as the invoking user, along with credentials to authenticate, and the tool will handle the data movement.


Converting a SQL Server 2000 Database to SQL Server 2016

It is NOT possible to restore a SQL Server 2000 database to SQL Server 2016 without restoring it to 2008 R2 first.

Here is an answer by Aaron Bertrand with details on alternate options to get the data out of the SQL 2000 database https://dba.stackexchange.com/a/99340/60898.


Multiple applications accessing SQL Server

It depends on a few things.

SQL version. Will the new application run on the same version of SQL as the existing application?
Security. Are you comfortable having the same people possess server-wide permissions to both applications?
Performance. Are you comfortable with a runaway query in one application possibly affecting the performance of the other application?
Maintenance. Are you comfortable with the idea of any maintenance that requires a server reboot or a SQL service restart taking both applications out of commission at the same time?
Vendor support. Do the vendors of each application support having them co-located on a server with other applications? (Just because the vendor doesn't like it, doesn't mean you can't make it work. But if you don't follow the vendor's requirements, you run the risk of them refusing to help you when something goes wrong.)


Azure SQL Database in different locations

Based on your scenario Data Sync is the best fit. It's the only Azure PaaS solution for Data Synchronizaion.

Transactional replication is supported for on-premise to Azure. If sync in this direction is enough for you, you can use replication. See Transactional Replication to Azure SQL DB now in public preview


How long does it take to do RESTORE WITH NORECOVERY

it also took less than a minute to say it was successful, but then the database remained in the Restoring... state

It surely did, you're now supposed to restore an arbitrary number of transaction logs, all WITH NORECOVERY, and the last one WITH RECOVERY.

See Understanding How Restore and Recovery of Backups Work in SQL Server and Restoring full and differential database backups.


JDBC Driver that Neo4J provides are for Cypher queries ( which are select only). There are no inserts , updates or delete. You will have to adopt some other means like .. exporting rdbms stuff to csv and importing csv to Neo4J.


I tried running what you have and noticed one small error:

where @Title = '%Production%'

So if @Title ever actually = '%Production%' it will update the entire table.  Until then it's going to update 0 rows.  My guess is what you are trying to do is something like this:

WHERE @Title LIKE '%Production%'
  AND Title = @Title

And on further inspection I don't think you want this either

SET @Department = 'Production'

You probably mean

SET Department = 'Production'

Which also means you don't need to be pulling Department at all in your cursor.  Last but not least, since you are updating the table that the cursor is pulling from I would use a READ ONLY cursor.  This does however write the cursor results to tempdb so it may not be such a good idea if you have a large table.

Last comment: Cursors are not always bad.  But they should only be used where batch processing is not possible.  Say executing a stored procedure with multiple inputs.  I think it's great that you are being proactive and practicing them however.


Select from one table where range is held in columns belonging to another table

In ANSI SQL and SQL Server you would just use an INNER JOIN. I don't know whether Oracle supports this, but I'd be surprised if it doesn't... No DDL, so typing off the top of my head:

SELECT *
FROM table_b AS b
INNER JOIN table_a AS a ON b.DivisionNo BETWEEN a.start_division AND a.end_division


Why does this stored proc cause a clustered index scan, but seek when using OPTION RECOMPILE?

b.ID = @ID OR @ID = 0

The optimizer has to produce a plan with an index scan, because the plan is cached and reused.

On a subsequent execution, the parameter @ID might be zero. An index seek is of no value in that case, because there is no ID value to seek to. Other times, there will be a non-zero value provided for @ID, but the cached plan has to work correctly for all possible parameter values.

When OPTION (RECOMPILE) is used, the Parameter Embedding Optimization (PEO) means the current value for @ID is used in place of the parameter on each execution, and no plan is cached.

Say @ID is 1234. After PEO, the optimizer sees:

b.ID = 1234 OR 1234 = 0

That is simplified by the contradiction detection logic to:

b.ID = 1234

...which enables a seek on ID.

For further reading please see my article Parameter Sniffing, Embedding, and the RECOMPILE Options.


mariadb: setting AUTO_INCREMENT using SELECT

I think best would be to remove the value of auto_increment from import script/dump - let it start at 1, then insert all the rows with fixed ID and let InnoDB do the work:

"If you insert a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value." - from manual

If this is not possible, then you can use a variable in the ALTER. You might do that using prepared statement:

SET @query = CONCAT('ALTER TABLE accounts AUTO_INCREMENT = ', @autoinc, ';');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


Views that inherit a table and syntax in PostgreSQL

Views cannot inherit.

Only tables can.

Aside: the query for CREATE VIEW does not need parentheses. And your whole SELECT is needlessly convoluted and buggy. Untangled and without the invalid INHERITS clause:

CREATE OR REPLACE VIEW stipa_test.stipa_view AS
SELECT aar.gid AS aar_gid, aar.geom AS aar_geom
     , aar_korr.gid AS aar_korr_gid, aar_korr.geom AS aar_korr_geom
FROM   stipa_test.stipa_type_aar  aar
JOIN   stipa_test.stipa_type_aar_korr aar_korr
                   ON ST_Within(aar.geom, ST_Buffer(aar_korr.geom, 3))
-- and 'aar_korr.dato' > 'dato.aar'  -- nonsense, always FALSE


Sql server AOAG license on readable secondary

You have to cover the readable secondary with proper license. So you have to count the cores on the secondary.
Count the cores assigned by the virtualizer (private cloud) or physical total cores (on-premise install).


Quite large (400k) mysql database design for multiple users

Use (word_id, user_id) table to store solved combinations, that way you won't store the NULLs. How many words will an average user solve? 5-20 maybe? 400k * 20 = 8M rows, thats nothing (the two column table is quite compact).

Do not even try to alter the table on each new user, that would stop working really quickly.

I suggest InnoDB for anything new unless there is a specific reason against it.


Can we create Fail over clustering for SQL Server 2012 standard edition on amazon EC2?

Yes you can deploy that scenario to EC2 just as you would if it was VMWare environment. However with AWS I would highly suggest you follow the guidelines on how to properly set this up from Amazon.

  AWS provides a comprehensive set of services and tools for deploying Microsoft Windows workloads on its reliable and secure cloud infrastructure, such as Microsoft Windows Server Failover Clusters and SQL Server AlwaysOn Availability Groups. WSFC clusters and AlwaysOn Availability Groups, together with Active Directory Domain Services (AD DS) and Domain Name Server (DNS) functionality, provide the underpinnings for many enterprise-class Microsoft technology-based solutions including Microsoft SharePoint and .NET applications.

https://aws.amazon.com/windows/resources/whitepapers/alwayson/


Breaking changes between 2008R2 RTM going to 2008R2 SP3?

Consider this as answer

SP3 is new and thoroughly tested its highly unlikely it would create any issue but again I would recommend you not to proceed without any testing and that too when Cluster is involved. Also asking whether anybody faced issue will lead you in problem, what if somebody writes 'Yes I have done and its working fine' yes they are correct it worked in there environment but I am sure your and his environment are not same and after applying SP if something stops working who would you catch other than Microsoft.

I have applied SP3 in my environment and every thing is going good as of now. But if you ask my plan of action I have now deployed in DEV then I will deploy in UAT and then after a month, because couple of SSIS packages run monthly, I would apply in production.

Please create an environment and test first believe me it will save you lot of hassle.

If still you want to proceed

1.. Backup system and user databases. Use TSQL backup command [This link is external to TechNet Wiki. It will open in a new window.] or SQL Server Management studio GUI [This link is external to TechNet Wiki. It will open in a new window.] to backup system and user databases. Since you backup system and user databases you don't need to backup jobs, SSIS packages, mail profiles, linked servers  and logins as all of these would be included in MSDB and Master database backups.

If you use analysis services backup Analysis Service configuration files, databases and repositories. Backup files present at location
C:\Program Files\Microsoft SQL Server\MSAS11.\OLAP\Data\
C:\Program Files\Microsoft SQL Server\MSAS11.\OLAP\Config\ directory.
Make sure enough space is available on drive where resource/system databases are present. Resource database [This link is external to TechNet Wiki. It will open in a new window.] is utilized during service pack upgrade. The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.
Service pack upgrade would require a downtime of application. Make sure application does not access database during service pack upgrade. Make sure you stop all jobs and activities on database before proceeding. you don't need to shutdown database services to apply service pack.
Make sure you are administrator on the system and always run the service pack executable with administrator privileges.

Please do read This BOL


Do Log Backups depend on Differential Backups?

Differential backups do not truncate the transaction log, so, there's no relation between Diff and Log Backups and both could co-exist in parallel without affecting each other.

This is correct understanding. In full recovery, ONLY a transaction log backup will  allow log truncation.

I have always thought that since Full/Diff backups do not affect the chain log, Differential backups were mostly meant to shorten recovery time by reducing the number of log backups to be restored.

Yes, this is correct as well. Infact, when a log shipping is broken (e.g. some process takes a NON COPY_ONLY log backup), the fastest way to rebuild log shipping is to take a differential backup and restore it to secondary and then take a log backup to establish the log chain.

there's indeed a strong dependency between transaction logs and differential backups. In fact, I wouldn't be able to restore everything If I had lost the latest Differential backup

NO. This is not 100% correct. Diff and t-log backups are independent.
Diff will allow you to skip the log backups that you will need to restore. But to do a Point-in-time restore, you will need a log backup (with STOP AT command to do a point-in-time restore).
For more details, refer to my answer - Log backup vs differential backup if latter is broken
sp_RestoreGene will automatically construct the right sequence for you.

What is the reason behind DIFF backup being required to allow log restores in the Restore Sequence #2?

This is the minimal repo that proves that you do not need a diff backup to allow log restores in the restore sequence.


Well this was fun. Usually I control language strings in the application layer rather than the database, but this was a good opportunity to learn how to use WITH RECURSIVE.
First let's define the tables that will be used: locale and dictionary:
CREATE TABLE locale (
    code           varchar(6)     NOT NULL    ,
    description    varchar(64)    NOT NULL    ,
    fallback       varchar(6)         NULL    ,
    PRIMARY KEY (code)
);

CREATE TABLE dictionary (
    id             serial         NOT NULL    ,
    key            varchar(64)    NOT NULL    ,
    value          varchar(128)   NOT NULL    ,
    locale_code    varchar(6)     NOT NULL    ,
    PRIMARY KEY (id)
);

There should be no surprises here. Locale's code can be 6 characters in length, though it will generally be either 2 or 5. If the fallback is NULL, then the record is considered "top level". This will allow you to have multiple top levels if you so choose, as you will see in the examples below.
Next, let's populate these tables:
INSERT INTO locale ("code", "description", "fallback")
VALUES ('en-us', 'English (US)', NULL), ('en-gb', 'English (UK)', 'en-us'), ('en-ca', 'English (Canadian)', 'en-gb'),
       ('fr-fr', 'French (France)', 'en-us'), ('fr-be', 'French (Belgium)', 'fr-fr'), ('fr-ca', 'French (Quebec)', 'fr-fr'),
       ('nl-nl', 'Dutch', 'en-us'), ('nl-be', 'Dutch (Belgium)', 'nl-nl'), ('ja-jp', 'Japanese', 'en-us'),
       ('es-es', 'Spanish', NULL), ('es-ar', 'Spanish (Argentina)', 'es-es'), ('es-bo', 'Spanish (Bolivia)', 'es-es'),
       ('es-cl', 'Spanish (Chile)', 'es-bo'), ('es-sv', 'Spanish (El Salvador)', 'es-cl'), ('es-mx', 'Spanish (Mexico)', 'es-cl');

INSERT INTO dictionary (key, value, locale_code)
VALUES ('yes_button', 'Yes', 'en-us'), ('yes_button', 'Very Well', 'en-gb'), ('yes_button', 'Sorry, Yes', 'en-ca'),
       ('yes_button', 'Oui', 'fr-fr'), ('yes_button', 'C''est exact', 'fr-ca'),
       ('yes_button', 'Ja', 'nl-nl'), ('yes_button', 'はい', 'ja-jp'),
       ('yes_button', 'Sí', 'es-es'), ('yes_button', 'Absolutamente', 'es-bo'), ('yes_button', 'Por favor', 'es-sv');

Note:These translations are not at all accurate, but instead intended to show how the final query works and how you might tweak it.
And now the SQL Query:
WITH RECURSIVE c AS (SELECT 1 as level, code, fallback FROM locale WHERE code = 'nl-be'
                      UNION ALL
                     SELECT c.level + 1 as level, lo.code, lo.fallback
                       FROM locale lo JOIN c ON c.fallback = lo.code)
SELECT c.level, c.code, dic.key, dic.value
  FROM c LEFT OUTER JOIN dictionary dic ON c.code = dic.locale_code WHERE dic.key = 'yes_button'
 ORDER BY c.level;

The target language you seek is in WHERE code = 'nl-be', and the dictionary key is within the OUTER JOIN with WHERE dic.key = 'yes_button'.
If you run this, you will receive the following results:
level | code  | key        | value
----- | ----- | ---------- | -----
2     | nl-nl | yes_button | Ja
3     | en-us | yes_button | Yes

Because there is no value for nl-be, the next value is returned. If there is no value for that one, en-us will be returned.
You can see this in action with Spanish as well. Change the language to es-sv and results will come back for three options.
level | code  | key        | value
----- | ----- | ---------- | -----
1     | es-sv | yes_button | Por favor
3     | es-bo | yes_button | Absolutamente
4     | es-es | yes_button | Sí

Note that there is no en-us in the output, nor is there any record for "level" 2. This is because es-es does not have a fallback, and there is no matching dictionary entry for es-cl, so it is excluded.
If this works for you, remember to set LIMIT 1 at the end of the SQL query so that you are given just the most relevant value.

Big hat-tip to Laurenz Albe for his many, many examples of how to write RECURSIVE queries across Stack Exchange. I learned something new and (hopefully) solved a problem today.


I'm sure that such approach is feasible. Technically Grid 11.2.0.4 is fully compatible with RDBMS 11.2.0.3. You just need to be accurate in some parameters.
I believe that in a new 11.2.0.4 grid you won't use very specific "compatible.rdbms" or "compatible.asm" attributes' value ('11.2.0.4.0', e.g.) which might conflict with 11.2.0.3 database. Ideally you should use the same default '11.2.0.0.0' value in target GI.

You can clone databases from old to new GI, copy specific tablespaces if your schemas are compatible with transport_tablespaces approach. You can use logical dump created with datapump.
It might be very useful to use dbms_file_transfer package to copy datafiles (and dumpfiles created by datapump as well) directly from source ASM to target ASM diskgroups.

Good luck.


Dump the buffer pool to disk so it can be loaded after a restart

No, there is no way to restore the buffer pool. Some will suggest pre-filling it by selecting all rows from all tables, but that is just silly given how rare it is for systems to use all rows from all tables (among other reasons that it wouldn't do what such an action is perceived to do).

I'm not even sure that it makes sense conceptually that this should be a thing to do. What is the actual gain here? Saving the time it takes to read those data pages into memory from disk? If the pages in the buffer pool were saved to a file to be reloaded upon start-up, it would still take that same (or nearly same) amount of time to load those data pages into the buffer pool, just from a different file. Or, perhaps you don't want the application pages' initial response times to reflect that the data pages had to be loaded? How long does your system take to load pages? One person waiting an extra 1 second for that particular result set (cuz after that, those pages are in memory for subsequent queries, right?) is hardly noticeable, nor is it something that they will remember when subsequent page loads take less time. But if this is a web-based application, then can it be said that any lag is the fault of the DB and not of the app server or some segment of the route between the client computer and the app server?

Don't worry about initial load times of data pages. Any areas for improvement of initial load times are going to be in better data modeling and index structure (i.e. making sure that reading pages from disk is as efficient as possible). Instead, worry about areas that drag on performance for repeated requests once the data has been loaded.

Besides, who is to say that you want all of the pages that were in the buffer pool? How many ad hoc support queries were performed recently that are still in the cache that likely won't get looked at again for 6 months or so? Just let SQL Server take care of managing the cache as the pages that get loaded when users start hitting the system after the restart will necessarily be the pages that should be there.

Also, given that SQL Server can evict pages from the cache whenever wants (if it feels that more memory is needed for other items) then you might load a bunch of pages that get evicted a moment later.


Once you have taken steps to backup the other database that contains the real data, you could also backup the views from your views only database, by scripting them out using SQL Server Management Studio

From SQL Server Management Studio
Via Object Explorer

Expand the database you wish to backup views for
Right click on the database and select Tasks -> Generate Scripts
Choose "Select specific database objects"
Check views or just the views you want to backup

Select Next
Choose Save to new query window

Choose next and then next again
Click on finish and the view or views you selected will be scripted to a new window


I'm not experienced with RedGate's monitoring tools, but SSMS has an Availability Group Dashboard out-of-the-box that I've always found particularly helpful with basic metrics like this. Specifically the metric in the dashboard that would be helpful to you is called the Estimated Recovery Time (seconds):

Indicates the time in seconds it takes to redo the catch-up time. The catch-up time is the time it will take for the secondary replica to catch up with the primary replica. This value is hidden by default.

I also found the Redo Queue Size (KB):

Indicates the number of log records in the log files of the secondary replica that have not yet been redone. This value is hidden by default.

And the Redo Rate (KB/sec) particularly helpful in monitoring the health of my secondary replicas:

Indicates the rate in KB per second at which the log records are being redone. This value is hidden by default.

If you want to access this information more natively so you can collect the metrics over time, the above Availability Group Dashboard just leverages the following DMVs:

sys.dm_hadr_database_replica_states
sys.availability_databases_cluster
sys.availability_groups
sys.availability_replicas

You can find more information on querying the DMVs for this information in SQLPerformance's Monitoring Availability Group Replica Synchronization. This is the query from the article (with one additional calculated column to get you the Estimated Recovery Time):
SELECT
    ar.replica_server_name,
    adc.database_name,
    ag.name AS ag_name,
    drs.is_local,
    drs.is_primary_replica,
    drs.synchronization_state_desc,
    drs.is_commit_participant,
    drs.synchronization_health_desc,
    drs.recovery_lsn,
    drs.truncation_lsn,
    drs.last_sent_lsn,
    drs.last_sent_time,
    drs.last_received_lsn,
    drs.last_received_time,
    drs.last_hardened_lsn,
    drs.last_hardened_time,
    drs.last_redone_lsn,
    drs.last_redone_time,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate,
    drs.redo_queue_size / see.redo_rate AS EstimatedRecoveryTime -- Additional helpful calculated column
    drs.filestream_send_rate,
    drs.end_of_log_lsn,
    drs.last_commit_lsn,
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc
    ON drs.group_id = adc.group_id AND
    drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar
    ON drs.group_id = ar.group_id AND
    drs.replica_id = ar.replica_id
ORDER BY
    ag.name,
    ar.replica_server_name,
    adc.database_name;

You can create a SQL job to routinely log this information to a table so you have a historical comparison to achieve your goals with. (This is essentially what RedGate's Performance Monitor is likely doing under the hood.)
The above article also briefly mentions a third way to monitor these metrics via Perfmon Counters.
And finally a third and equally effective way to log your Availability Groups health metrics is via the built-in Extended Events.


DB Structure: one table to hold all domain values?

That design structure is commonly referred to as entity-attribute-value, or EAV.  Some people hate it, others see the inherent beauty in its design.  There is nothing wrong with the design, as long as it's carefully thought out, and implemented properly.

Some of the more common "problems" people associate with EAV are:

Good chance for poor performance
non-intuitive design leading to difficult application maintenance
virtually impossible to reliably enforce referential integrity

Good references abound:

https://softwareengineering.stackexchange.com/questions/93124/eav-is-it-really-bad-in-all-scenarios
Attribute and Value tables on SQL - Is this a good practice?

Porting a database from EAV structure to a more typical relational design will be very involved.  Any and all code that touches the data will likely need to be changed.  You could use triggers on the existing tables to allow you to slowly port code from the EAV model into a relational model, or possibly you could add a series of views over the re-modeled data to support reporting or other read-only aspects of the client application once you convert to a relational model.


Your usage of setval() is not what one usually expects.  It is used for resetting the sequence.  Feeding new values to IDs and other stuff is done by nextval().  Its function is described so:

  nextval()   Advance sequence and return new value

Important to note that this is done so that two concurrent transactions would never get the same number when calling nextval().  Also, it is not really suitable for producing gapless series - the reason is already cited in your question.


Use a CASE statement to check if RecordNumber = 0 | 2147483647.

Try this version:

select   Content->>'Group' as "Group",
         min(case when Content->>'RecordNumber' = '0'
                  then Content->>'When'
                  else null
             end ) "Start",
         max(case when Content->>'RecordNumber' = '2147483647'
                  then Content->>'When'
                  else null
             end ) "Stop",
         count(*) as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

The result:

| Group | Start      | Stop       | Count |
|-------|------------|------------|-------|
| 0     | 1490280300 | 1490280500 | 3     |
| 1     | 1490280600 | 1490280700 | 2     |
| 2     | 1490280900 | NULL       | 2     |

As Evan Carroll pointed out, you can also take advantage of the FILTER clause. Have a look at 4.2.7 Aggregate expressions in the Postgres docs.

select   Content->>'Group' as "Group",
         min(Content->>'When') filter (where Content->>'RecordNumber' = '0') "Start",
         max(Content->>'When') filter (where Content->>'RecordNumber' = '2147483647') "Stop",
         count(*)  as "Count"
from     DocumentStore
group by Content->>'Group'
order by Content->>'Group'
;

Check it here: http://rextester.com/ADYG46620


How to update the time of each row and check if its updated or not

Maybe you need in
CREATE TRIGGER tr_bu_check_gap
BEFORE UPDATE
ON {tablename}
FOR EACH ROW
SET NEW.Status = CASE WHEN DATEDIFF(NEW.Time, OLD.Time) > 1
                      THEN 'not expected'
                      ELSE 'expected'
                      END;

?
PS. This is solution for MySQL only. For SQL Server it can be similar, but syntax adaptation needed.


How do you grant execute permission for a single stored procedure?

GRANT EXECUTE ON dbo.procname TO username;


Stored procedures, binlog and SUPER privilege

You can't avoid it. The restriction applies also to stored procedures. The only alternative would be to have the SUPER privilege, but I don't think it's possible in this case.

Moreover, you have to declare your function as DETERMINISTIC, telling explicitly that you're not going to modify data:

CREATE FUNCTION GetRootZoneId (zoneId INT) RETURNS INT DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE _id, _parentId INT;
    SELECT zoneId INTO _parentId;

    my_loop: LOOP
        SELECT id, parentId
        INTO _id,_parentId
        FROM Zone
        WHERE id = _parentId;

        IF _parentId IS NULL THEN
            LEAVE my_loop;
        END IF;
    END LOOP my_loop;

    RETURN _id;
END;


If it's a table of 3 columns, you're probably overthinking this.  Strict 3NF is all well and good, but when you're talking about possibly having two tables just to follow it precisely, it's time to step away from the table, nobody's getting hurt here...

The intent of 3NF is that each table should contain unique keys, and all of the columns should describe those keys.  Placing data about a person and that person's workplace in one table doesn't make sense - you're really describing two things, so you should separate them.

In this case, it sounds like (and it's not totally clear, but I'm guessing) account and idNumber are both effectively keys.  If they're both unique in the table, with a 1:1 relationship, then they're both keys, and even if one of them is the primary key, they both serve that purpose.  So why not keep them in the same table?  You're not describing two different things here, are you?  It sounds like you're describing the same thing in two different ways.

Now, the fact that you have two unique IDs for the same records begs the question as to why.  Are these IDs from two different groups of [other] tables?  If that's the case, then you may need to have an account-idnumber only relationship stored in a table in order to more easily convert between them.  If that's not the case, do you really need both of these columns at all?


Password column from sys.user$ is null in 18c, why?

Check the SPARE4 column.
Quoting from https://www.experts-exchange.com/articles/855/How-Oracle-Stores-Passwords.html

11g introduces new security and the ability to support case sensitive
passwords.
First, DBA_USERS no longer exposes the PASSWORD value, instead it will
be NULL and you will have to go to SYS.USER$ to find the hash value.
However, if your PASSWORD_VERSIONS does not contain 10g then
SYS.USER$.PASSWORD will not be reliable.  The reason is the hashing
algorithm populating that field doesn't support case sensitivity.  So,
regardless of the sensitivity setting and regardless of the upper or
lower case of your password the hash will be the same.  If your
PASSWORD_VERSIONS is 11g only then you will need to look in the
SYS.USER$.SPARE4 column and you will see a much larger hex number.
This is because Oracle has switched to the SHA-1
(http://en.wikipedia.org/wiki/SHA-1) algorithm.


Log File Size near 100% - How to find Active Transaction

You can use the following DMV to view any transactions that might be causing your log file to be bloated: sys.dm_tran_database_transactions.

Both your queries are limited to your current connection, meaning they give you no information about other connections made to the SQL server.

@@Trancount;
XACT_STATE


Extent Allocation Mechanism in SQL Server 2017

In SQL server 2016 and later, if we create a new table or index, will it be allocated a uniform extent or a mixed extent initially?

If the database was created in SQL Server 2016+ then by default the uniform extent allocation is on by default and thus a uniform extent will be allocated even if just a single page is needed (extents are 8 contiguous pages starting from database page 0).

If, however, the database was created in an older version <= SQL Server 2014 then by default the uniform extent allocation database setting did not exist and thus a single page from a shared extent was used. In <= 2014 there was a trace flag that would override this TF1118 setting but it would be for the entire instance and not just a single database. SQL Server 2016 took away the trace flag (which is no a NOP) and made the setting per database with a default of ON.


DB server VM's CPU configuration - 1x2Ghz or 2x1Ghz - which is better

My question is a bit different / specific in a way I'm choosing between just one core with higher speed or two with lower speed. So the scale is far different.

Technically, more cores is always better if either the software is multithreaded, or multi-process (workers). As far as the hardware is concerned

More cores means more instructions are processed at the same time.

including instruction fetching and decoding, execution, memory access (cached, or out to bus), writes. You can read more about this on Instruction pipelining
you have a different pipeline for each core.
each core can with a full pipeline do more than one thing at a time. Modern Intel CPUs post-Sandy Bridge have a 14 stage pipeline.

Fewer cores, regardless of speed, mean fewer multi-stage pipelines to fill and fewer things can be done in parallel.

As far as the software side

Threads and processes can be managed by the kernel.
Most databases will run at the very least the WAL-log on another thread then the execution/query engine
Software that is multithreaded, will do very poorly on a single-threaded CPU.

For these reasons, the answer from the hardware and software side is clearly in favor of more cores (so long as your use case is provided for by the operating system and application as subject to Amdahl's law). Microsoft though isn't free, and brings to the table expensive licensing that needs to be factored into the total cost of ownership. This can tie your hands, but plays into the value of the product. As far as licensing requirements, check out this answer by Max.


Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL)

As it sounds, you cannot solve this with one constraint, but nothing prevents you from defining more of them.  I believe something like this should work:

CREATE TABLE due (
    ...,
    d_invoice_id REFERENCES invoice (i_id),
    d_loan_id REFERENCES loan (l_id),
    ...,
    CHECK (   (d_invoice_id IS NULL AND d_loan_id IS NOT NULL)
           OR (d_invoice_id IS NOT NULL AND d_loan_id IS NULL))
);

A column that has a foreign key defined on does not have to be NOT NULL, unlike the column it references.  Apart from that, you may define checks of arbitrary complexity - it is advisable to keep complexity at its necessary minimum, though.


Assuming that this question pertains to Microsoft SQL Server:

Identifying the most appropriate solution requires knowing more about:

What is the source of this string
What is the goal of splitting it and returning each value as a separate column?

That being said, the way to accomplish this in T-SQL is to use Dynamic SQL to SELECT a series of literal values that are the result of the input string being split. For example:

DECLARE @String NVARCHAR(MAX) = N'factor1|factor2|factor3';

DECLARE @SQL NVARCHAR(MAX) = 'SELECT ';

SELECT @SQL += N''''
             + factors.SplitVal
             + N''' AS [Var'
             + CONVERT(NVARCHAR(50), factors.SplitNum)
             + N'], '
FROM   SQL#.String_Split(@String, N'|', 1) factors;

SET @SQL = STUFF(@SQL, LEN(@SQL), 1, N';');

--PRINT @SQL;

EXEC (@SQL);

You can use whatever string splitter you prefer, though SQLCLR-based splitters are the best choice. The one I used is from the SQL# library of SQLCLR functions (which I wrote, but String_Split is in the Free version).

This can easily be wrapped in a stored procedure as they can dynamically create their result set structure at runtime. On the other hand, it cannot be used in a TVF (T-SQL or SQLCLR) as those are required to have static result set structures.

Also, this code just returns a single row. The sample result set in the Question has an ID field which implies multiple rows. It is not clear, at this point, how that would even be passed in. But, if there are multiple rows then this gets a little more complicated:

The code presented here can be adapted to return additional rows of static values, combined into a single result set via UNION ALL between them
The code presented here can be adapted to instead dynamically create a local temp table containing the appropriate number of fields, then for each row, create an INSERT statement instead of the current SELECT to populate the temp table, and finally do a SELECT * FROM #tmp;. All of this would be done within the Dynamic SQL, even the final SELECT, so that the temp table can be kept as a local temp table, thereby allowing multiple processes to run at the same time (which would not work if using a global temp table).
Depending on where the input data is coming from and how it is being used, it is possible that a Table-Valued Parameter (TVP) would be best.
If this data already exists, as shown, in a table, then SQLCLR is an option as a stored procedure can be created to split a string into any number of fields and return each row as it is read from the table (i.e. streaming the data back). This is more efficient than T-SQL which needs to place all rows either in memory (in the Dynamic SQL local variable) or on disk (in a temp table). There is a stored procedure in the SQL# library (noted above) called String_SplitResultIntoFields that handles this particular scenario quite nicely and efficiently. This proc, however, is only available in the Full version (i.e. not free), but the benefit is that it already exists.


Backing up the entire database to a single file would be much slower. You can consider below points

Stripe your backups across different spindles on a SAN.
Use backup compression (costs you more CPU) but it results in fewer overall I/O operations during backup and restore operations.
you can use the trace flags 3605 and 3213 to find out the default BUFFERCOUNT value used in your backup and then tune accordingly.
Set your max memory away from default as when you increase BUFFERCOUNT and MAXTRANSFERSIZE, they require additional memory.
For your 4TB database, assuming you have different filegroups for putting recent and historical data, you can mark the historical data as READ_ONLY and then just back it up once. You should perform regular backups of the read/write filegroups.
Use BACKUP ... WITH CHECKSUM  as it places a checksum on every page in the backup file which helps in detection of corrupt pages in the backup file when doing restores.
Best is to backup locally on the server (provided you have enough disk space) and then transfer the backup file to a remote backup location -- or -- if you are backing up directly to a network share, then best is to use Multiple NICs and/or just use differnt logical subnets in the network.

Below is from the Whitepaper -  A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabytes Database over the Network :

As a side note, its more important to test your restore strategy as a backup is ONLY GOOD if it can be restored without any issues. Enabling Instant File Initialization will considerably cut down the restore time.

Be careful when you are striping your backups as there are chances that a stripe might get corrupted or might become missing (deleted) due to machine or human error :-)

References :

Consider efficient backup strategies
Backup More Than 1GB per Second Using SQL2008 Backup Compression
How to increase SQL Database Full Backup speed using compression and Solid State Disks


You are much better off running the ALTER TABLE like this
ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

What could be the benefit ? Defining a column whose default value is NULL will not attempt to increase the size of the row. This is true for VARCHAR. Honestly, I'm not sure this is true for numeric. (INT, TINYINT, etc). You need to test this (I mentioned testing this 2 years ago (Does InnoDB allow adding columns to a table with concurrent READ/WRITE of rows?)). Consequently, there should be no downtime.
The downside to this would be changing your software to deal with a NULL numeric column.
If you concerned about foreign keys, turn them off in your session beforehand
SET foreign_key_checks = 0;
ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

If you have triggers and can live without it for the duration of the load, drop the trigger, do the ALTER TABLE, and recreate the trigger.
CAVEAT: Make sure you are using the latest version of MySQL 5.6 (at least 5.6.23) to avoid foreign key issues with ALTER TABLE.
Please note that I did not mention using third party tools. This is just straight up MySQL.
GIVE IT A TRY !!!


You can get the same results with just one join and pivoting .

SELECT c.domain, c.period, h.nameserver,
MAX(CASE WHEN co.id=c.owner_id THEN co.name END) AS owner,
MAX(CASE WHEN co.id=c.admin_id THEN co.name END) AS admin,
MAX(CASE WHEN co.id=c.tech_id THEN co.name END) AS tech,
MAX(CASE WHEN co.id=c.billing_id THEN co.name END)  AS billing
FROM carts c
LEFT JOIN contacts co ON co.id IN (c.owner_id , c.admin_id,c.tech_id,c.billing_id)

LEFT JOIN HOSTS h ON c.host_id = h.id
WHERE c.user_id =1
GROUP BY c.domain,c.period,h.nameserver

However, check execution plan for both versions, sometimes multiple joins may be faster than such approach.


How do I merge two rows only if there is the TagWidgetIds are same but the tagValues are different?

You can first insert the results from the stored procedure into a temporary table (see https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table), and then simply query that table with your conditions.  Let's say that you put the results in a temporary table called #procData.  In that case, your final query would look something like:

SELECT <columns_you_want>
FROM #procData p1
JOIN #procData p2
    ON p2.TagWidgetId = p1.TagWidgetId
    -- NOTE: if your columns are nullable, adjust this logic to account for that
    AND p2.TagValues <> p1.TagValues
    AND p2.TagIndex <> p1.TagIndex
    AND p2.ValueIndex <> p1.ValueIndex


No connections to mongodb replset member post outage

By "B" again working do you mean the connections to be distributed evenly when B comes back? Do you see new connections also going to C when B has come back?

I believe MongoS does not do dynamic load balancing w.r.t. to the existing state of connections. However if you create new connections, MongoS may appropriately create connections on B. Do you not see this behaviour?


You need to first retrieve the whole tree of comments using a recursive common table expression. Then you can count the number of children for each root node:

with recursive all_comments as (
   select id, parent_id, id as root_id
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id
   from comment c
     join all_comments p on c.parent_id = p.id
)
select root_id, count(*) as comment_count
from all_comments
group by root_id;

SQLFiddle: http://sqlfiddle.com/#!15/158ea/15

If you don't want to get the count per "thread" but for each node, you can use something like this:

with recursive all_comments as (
   select id, parent_id, id as root_id, 1 as level
   from comment
   where parent_id is null
   union all
   select c.id, c.parent_id, p.root_id, p.level + 1
   from comment c
     join all_comments p on c.parent_id = p.id
)
select ac.id,
       ac.parent_id,
       ac.root_id,
       count(*) over (partition by ac.root_id) - count(*) over (partition by ac.root_id order by level) as child_count
from all_comments ac
order by ac.id;

SQLFiddle: http://sqlfiddle.com/#!15/158ea/17

The above is using window functions to calculate the total number or nodes for a root node and the number of nodes in each level:

count(*) over (partition by ac.root_id) counts the number of children (including the root) for each root node.

count(*) over (partition by ac.root_id order by level) counts the number of nodes up to the current's node level. The difference between the total count and the ones "till now" is the number of child nodes.

Btw: comment is a SQL keyword, you shouldn't use it as a table name.


First, if it ain't broke, don't fix it. If no-one is complaining about the speed of the system then leave it alone. Spend time and money on other things.

Second, no, I don't think it is possible to over-normalise. That is, I think it is possible to spend more time on the normalisation process than your application might need, but unless you've spent tens and tens and tens of hours doing so I don't think the time is really wasted. The time getting to 3rd normal form is most certainly never wasted.

In my (admittedly limited) experience, the problem is usually under-normalisation, otherwise known as "laziness". Database designers often claim it is for speed purposes, throwing the term "denormalised" about like a holy mantra, but if you investigate closely, all they've actually done is half a job.

Denormalisation is not "stopping before you reach a certain normal form"; it is "reaching a certain form (usually 3NF or BCNF) and then undoing some of that normalisation for well-documented, well-benchmarked reasons."

Links

Tony Davis, The myth of over-normalization.

Louis Davidson, Can you over-normalize?.


Increment a column in table based on another table

For MySQL only.

If the id field in the table1 is primary key or has unique constraint, the solution can consist of 2 queries:

INSERT IGNORE INTO table1 (name, id, overdue_days)
SELECT name, id, 0
FROM table2;

UPDATE table1
LEFT JOIN table2 ON table1.id=table2.id
SET table1.overdue_days = CASE WHEN table2.id IS NULL
                               THEN 0
                               ELSE table1.overdue_days+1
                          END;

If record present in table1 only, it is not affected by Q1 and overdue_days is set to 0 by Q2.
If record present in both tables, it is not affected by Q1 and overdue_days is incremented by Q2.
If record present in table2 only, it is inserted by Q1 with overdue_days = 0, and then is incremented by Q2.

For PostgreSQL.

  PostgreSQl is not my favorite DBMS, some mistakes possible

INSERT INTO table1 (name, id, overdue_days)
SELECT name, id, 1
FROM table2
ON CONFLICT DO UPDATE SET overdue_days = overdue_days+1;

UPDATE table1
SET overdue_days = 0
WHERE NOT EXISTS (SELECT 1
                  FROM table2
                  WHERE table1.id = table2.id);

If record present in table1 only, it is not affected by Q1 and overdue_days is set to 0 by Q2.
If record present in both tables, it's overdue_days is incremented by Q1 and is not affected by Q2.
If record present in table2 only, it is inserted into table1 by Q1 with overdue_days = 1, which is not affected by Q2.


insert is not working as expected while doing partitioning

Well, with the checks you use for your child tables, it is obvious why such an UPDATE runs into problems.  Adding one to id_ changes it from even to odd or back - the new row does not match the check anymore.

The first trigger is not enough to circumvent this as it does not remove the old row from the child table.  You need a mechanism that does exactly this - delete the old row version and readd the new to either t1 or the matching child.  This trigger has to be defined on t1_odd and t2_even.


This is due to MySQL Master and Slave version mismatch

The message below is clear, and confirms the master/slaves run different versions. The variable SERVER_UUID is not supported with MySQL 5.5 but is supported with MySQL 5.6

2015-06-29 11:01:01 8026 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.41-0ubuntu0.12.04.1-log), even though it is on the slave (version: 5.6.22-log), Error_code: 1193

Please have a look at Link1 & Link2


I had the same problem, tried to change the sql_mode but didn't work, then changed the explicit_defaults_for_timestamp to 0 and worked!!!, but I encourage you to read the following to understand the issue:
http://openbedrock.blogspot.mx/2015/04/what-happened-to-mysql-timestamp-columns.html
and
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html


Graphical user interface to change data capture on Oracle

Duplicate post in StackOverflow.

Oracle 11.1 Documentation Warning: "Oracle Change Data Capture will be de-supported in a future release of Oracle Database and will be replaced with Oracle GoldenGate. Therefore, Oracle strongly recommends that you use Oracle GoldenGate for new applications."

Attunity, the company I work for, offers REPLICATE, a sleek GUI alternative.

hth,
Hein.


`select OBJECT_ID(N'X')` takes "forever"

OBJECT_ID() (and several other metadata functions) have to observe locks taken against objects, and wait for them to be released. I generally recommend against their use because, unless you actually want to wait for the lock to release (which is totally valid, but uncommon), you end up getting blocked. This is what I suspect happened here, though there is no way to prove it now.

I blogged about this here:

Bad habits : Using (certain) metadata "helper" functions

The workaround is, instead of using the functions, pull the values from the catalog views. It's more code, but it will obey isolation semantics, unlike the functions, which won't. So instead of:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT OBJECT_ID(N'dbo.foo');
-- actually won't obey isolation level

You say:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- will obey isolation level
SELECT o.[object_id]
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE s.name = N'dbo'
  AND o.name = N'foo';

Again, it's more code, but you've found the price of convenience here. It's a shame schema name isn't exposed as a computed column; I wrote a tip about creating your own views to get around this and other complexities here:

Making SQL Server metadata queries easier with these new Views


Where does it make sense to store Indexes for partitioned tables?

Putting an index on a partition scheme is exactly the same as putting a table on one. All it means in terms of storage is that the storage for the index is partitioned according to the partitioning column that you select. The partitions in a partition scheme still end up pointing to filegroups eventually, so you can still point the partition scheme at filegroups that are on your separate drives.

So the pros and cons are pretty much exactly the same as for table partitioning, and the query optimiser will make very similar choices in terms of partitioned .v. not. However, from my own experience, I can say that partitioning makes a lot of sense if you are regularly making queries that can be fully satisfied with the data from a single partition - and the sample applies to indexes.


How do I recover my database after losing a tablespace with indexes?

How much improvement are you expecting from the SSD? It is surprising how little physical I/O actually happens on busy indexes and I don't think I've ever solved an actual performance problem with physical I/O devices.

I don't know enough about PostgresSQL to comment on buffering or recoverability and the like but I will say that choosing performance over recoverability is probably not a good practice.

I never want to find out that I have found a previously unknown recovery bug when my data is sitting in a tape on a shelf. I learned the DBA craft in the days before RAID - every night was a sleepless night. Always, always, always use RAID's.

If a user is complaining about performance, tell them you need a dozen SSD's to put in a 10 disk RAID5 array with hot failover. He'll leave with his head spinning but he might come back with a purchase order.

Good luck.


Why does an UPDATE against a table with an INSTEAD OF UPDATE trigger appear to do a clustered index insert, as well as a clustered index update?

An INSTEAD OF trigger stores a copy of the rows that would be affected in a hidden work table*. This is the Clustered Index Insert you see. The trigger body reads from this work table* and any data changes in the trigger use the 'normal' operator (Clustered Index Update in your example).

*The query processor internally renames the work table when constructing the user-visible form of the execution plan. When writing to it, it is renamed to the target base table, when reading, it is renamed as inserted or deleted more or less as people would expect to see in a trigger.

For more details, see my article Interesting things about INSTEAD OF triggers.


Does Management Studio let me change the format of new script names?

You can't.

The SSMS tab is named after the open file, not the contents of the window/file.
So if you have not saved it, it has no name to assign


When should you split a database into 2-3 databases for performance reasons?

Size is not really a reason to do a database split.  If there is slow query performance or other factors like that, then maybe.  But keep in mind, splitting a database using the same hardware and storage will provide very few gains.  There is also the possibility of partitioning the data which will accomplish something similar.

The problem is that this was devised as a solution that it may not fit.  I have multi-terabyte databases that respond faster than 100 GB databases because it is tuned and indexed within an inch of it's life.  Replacing hardware will often cover up poor database and query design.  An immediate fix would be to add more RAM.  8 GB would be the minimum I would want to see in a SQL server, but given the size of the database I would suggest a minimum of 128 GB.

Ultimately we need more information on what performance issues you are experiencing in order to guide you to a more complete solution.


Add row after every rows of a table from select statement

Your question says two tables but your example shows three.  I'm going to take table_3 as the association table between table_1 and table_2, showing data which actually exists.

To get all possible combinations of two tables you need a CROSS JOIN. The number of rows returned is the product of the number or rows in each table. Be careful as this can get very large.  With two rows in each table you get four rows from a cross join.  With one thousand rows in each you get one million rows returned:

table_1 CROSS JOIN table_2

That will give you all possible combinations.  To include your actual values you will need a LEFT JOIN from that to table_3:

table_2 AS t2 CROSS JOIN table_1 AS t1
  LEFT JOIN table_3 AS t3
    ON  t3.name = t2.name
    AND t3.type = t1.type

If you want the extra "party" rows, you'll need one more subquery, combined with UNION to the previous one and all wrapped in an extra derived table to have the results in the wanted ordering. The final query will be something like:

SELECT
    result, value
FROM
  ( SELECT
        name, name AS result, NULL AS value, 0 AS ordering
    FROM
        table_2

    UNION ALL

    SELECT
        t2.name, '  - ' || t1.type, COALESCE(t3.value, 0), 1
    FROM
        table_2 AS t2 CROSS JOIN table_1 AS t1
          LEFT JOIN table_3 AS t3
            ON  t3.name = t2.name
            AND t3.type = t1.type
  ) AS x
ORDER BY
    name, ordering, result ;


Login failed for user Error: 18456, Severity: 14, State: 11

States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached.

Refer to to the below link for more information on Error states.

https://sqlblog.org/2011/01/14/troubleshooting-error-18456


Counting number of visitors who did NOT log in

Do it with a LEFT JOIN

SELECT Count(DISTINCT accessid) AS c
FROM   accesslog a
LEFT JOIN
(SELECT accessid
 FROM   accesslog
 WHERE  DATE = '2014-01-21'
 AND userid > 0
 AND is_robot = 0
 AND is_admin = 0
 GROUP BY accessid)x
ON a.accessid=x.accessid
WHERE  a.date = '2014-01-21'
   AND a.userid = 0
   AND a.is_robot = 0
   AND a.is_admin = 0
   AND a.url = 'www.example.com/'
   AND x.accessid IS NULL


Can't understand difference in fulltext results - contains, contains with wildcard, freetext

The difference is caused by noise word "W". By default SQL Server uses stoplist to exclude some short or frequently used words like "and", "does", "could". You can see the full list using such query:
select * from sys.fulltext_system_stopwords where language_id = 1033

And you can disable the stoplist for your full text index if you want.
Now let's take a look at your queries:
SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W*"')

this one looks for all rows which contain words starting with "W" and these words shouldn't be in your stoplist.

SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W"')

this query returns nothing because you are looking for a stop word.

SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917"')

this query just ignores the W and looks for "917".

SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917*"')

This is the most interesting case. According to SQL Server documentation:

When <prefix_term> is a phrase, each word contained in the phrase is
considered to be a separate prefix. Therefore, a query specifying a
prefix term of "local wine*" matches any rows with the text of "local
winery", "locally wined and dined", and so on.

It means actually you are looking for phrases which have two words in a row. The first one is like "W*" (but it shouldn't be in stoplist) and the second one is "917*".


How to transfer database from SQL Server to DB2?

Migrating between platforms is not always an easy task. Besides the changes in schema and SQL code, the data itself may not be an exact fit between platforms. For example, a TIMESTAMP in SQL Server is not the same as a TIMESTAMP in Oracle.

Microsoft offers the SQL Server Migration Assistant, but that is for getting data into SQL Server, not for exporting to other platforms.

The only company I know that offers tools and knowledge for migrating from SQL Server to DB2 is Ispirer. I've never used their tools however, but you may want to start with them.

HTH


SQL Server placement of master database files vs resource database files

Totally agree with what George has mentioned, just trying to shed some more light to make it clear.

For SQL Server 2005

You were allowed to move resource database. If you read SQL Server 2005 Master Database Document it says

  The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location.

But this caused problem during service pack upgrade when users started moving it along with master database. The issue came while applying SP/CU which eventually failed because resource database was moved.

For SQL Server 2008 and above.

This was then fixed by MS and they changed the whole thing such that "user is not allowed to change resource database location" and this is why it was moved to
<drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binnfolder along with other binaries, people normally do not touch contents of BINN folder. It's specifically in BINN folder because it does not contains any USER data only information related to SQL Server.

Yes there was some discrepancy in BOL documents which was later fixed. I wrote an article on this please read Should We Move Resource Database

Moral: Do not move resource database


SELECT C.Name_of_customer,
       C.[Customer/VendorID],
       E.Employee_Name,
       E.EmployeeID
from Order_Information_local as O
JOIN Employee_Info_local as E
JOIN [Customer/Vendor_Info_local] as C
       ON C.[Customer/VendorID] = O.[Cusrtomer/VendorID])
      AND E.EmployeeID = O.EmployeeID;


How to Extract SubString between two positions

The SUBSTRING function has regex support, so the following would work:

SELECT SUBSTRING(emailbody FROM 'Delayed (.*?) for Carrier') FROM notification

The so-called capturing group () is to make sure you capture only the shipment ID and not the entire string "Delayed 12345 for Carrier". The ? makes the capture non-greedy, to make sure the first 'for Carrier' is matched.


DB backups for production to DR without creating jobs in prod

I suggest you use Ola Hallengren's maintenance solutions stored procedures to accomplish what you are doing.

These stored procedures will allow you to backup via simple SQL scripts. You could then create a Job from the SQL Server Agent to run those backups once or on a scheduled basis.

Simple Example from the website:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

This process will allow you to save your backups to a remote storage or locally.

You could also run this same process using sqlcmd to connect remotely to the server you are trying to backup

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

And MyScript.sql would contain the backup script from above.

As long as the machine running the script is in same domain as the instance being backed up, there is no need to specify the user and password. This assumes that your domain login has the necessary server permissions to run a backup.

You must install the maintenance stored procedures before using them to backup, so please be sure to carefully read all instructions at this page.


mongoaudit: Security through obscurity and how to limit connections from a specific subnet?

I don't wish to hide my version number. In my opinion this is Security through obscurity

Hiding version details is not security through obscurity unless that is the main security measure you rely on. Exposed version details can allow an attacker to easily identify known security issues rather than having to probe for them. Forcing an attacker to test for known exploits may be useful for an Intrusion Detection System to detect and handle obvious malicious attempt signatures.

I would consider this a low risk if you are following other best practices (eg. limiting network exposure, actively applying security updates, monitoring for intrusion attempts). Putting a reverse proxy in front of your MongoDB deployment may also add unwelcome operational complexity for server discovery and failover.

  Howto whitelist networks? I wish to do that, but only know about net.bindIp. This only say on which virtual NIC mongod listens. Are there options to restrict a subnet?

As you've noted, MongoDB's bindIP configuration only determines which NIC IP addresses the server listens to (not the client IPs that are allowed to connect). To limit network exposure you need configure solutions at the operating system and/or network infrastructure level (eg. firewalls or VPNs).

The current version of mongoaudit includes a link to some suggested resources depending on where your MongoDB deployment is hosted: How to Configure Allowed Hosts, Firewall, Whitelisting and Blacklisting in MongoDB.

For more information, also see Hardening Network Infrastructure and the Security Checklist in the MongoDB documentation.


How to handle deadlock issue sql server 2008?

The simplest way to reduce deadlocks - proper indexing. Make sure, that client's read requests not involving table/clustered index scans - if any - this is the open gates to deadlocks

And make sure, that your index has its allow_page_lock and allow_row_lock options ON


Well, apparently this is it:

SELECT
    *,
    SUM(amount) OVER (
        ORDER BY tx_date DESC
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS running_balance
FROM transactions
ORDER BY tx_date DESC;

Output:

 id |      memo       | amount |  tx_date   | running_balance
----+-----------------+--------+------------+-----------------
  4 | Wages           |  75000 | 2015-08-31 |           77000
  2 | Eating out      |  -1000 | 2015-08-19 |            2000
  3 | Gas             |  -7000 | 2015-08-19 |            3000
  1 | Initial balance |  10000 | 2015-08-17 |           10000


How would I improve my "algorithm" for figuring out my average spending on food per month?

I would first calculate the sum per month, then calculate the average from that:
select avg(amount) as avg_amount_per_month
from (
  SELECT date_trunc('month', "purchase time"), SUM("amount in EUR") as amount
  FROM personal."food purchases"
  WHERE "purchase time" >= now() - INTERVAL '3 months'
  group by date_trunc('month', "purchase time")
) t
;

The inner query will return the amount for each month of the chose interval (yielding three rows), and the outer query will then calculate the average from that.


Is there a way to configure a distributed environment with mongodb?

Nowadays, master - slave system is not used anymore. You create replica set (RS) where you have one PRIMARY and at least one SECONDARY (plus arbiter), but minimum two SECONDARY are preferred.

In the RS, write is happening ALWAYS to current PRIMARY and preferred read is also from PRIMARY, but you can configure reading happening from SECONDARY too, but.. Information is not guaranteed to be up-to-date (until SECONDARY has replicated it OR write concern is so strict that OK is not returned until all nodes have it; what is not recommended).


They are essentially the same thing. database_xml_deadlock_report was introduced to provide a method for retrieving deadlocks for Azure SQL Databases by scoping the deadlock event to the victim database and allowing users to create database scoped extended events sessions.

See this link for more information. Basically, the XML Report event field should return the same information and that is where the important data is. The other three fields (database_name, server_name and deadlock_cycle_id) are obtainable from within the XML report or from the global fields.

You can basically use either one.


MYSQL Indexes when looking up on alternating sets of columns

You already have indices on both colums with the unique constraint. No need for new ones.

I hope you have a good reason avoiding a foreign key. I hope you pay attention to keep your data consistent...

btw: parent_id in your join query is ambiguous.


how do I limit the characters in selected row and con-cat with '...'

You can use MySQL's IF function

select
    IF(LENGTH(comment) > 50,CONCAT(LEFT(comment,50),'...'),comment) comment
from mip where active = 1;

or

select
    IF(LENGTH(comment) > 50,CONCAT(SUBSTR(comment,1,50),'...'),comment) comment
from mip where active = 1;


Oracle RAC with single node Data Guard physical standby

There aren't any special considerations that need to be taken into account when setting up a RAC->No RAC Primary->Standby configuration.

In fact, there's a White Paper written by Oracle which explains the setup.

The Oracle documentation is here.


Returning rows where all non-NULL values are equal

The most compact syntax I can find is:

SELECT *
FROM @T AS t
WHERE EXISTS
(
    SELECT ISNULL(ISNULL(a, b), c)
    INTERSECT
    SELECT ISNULL(ISNULL(b, c), a)
    INTERSECT
    SELECT ISNULL(ISNULL(c, a), b)
);

Based on an idea from one of my old blog posts that describes how to use INTERSECT and EXCEPT to replace comparisons like a <> b OR (a IS NULL AND b IS NULL) with NOT EXISTS (a INTERSECT b).


Whats the reason behind this Error ?

It is in the error message,

  "UPDATETEXT is not allowed because ...  or  tracked by Change Data
  Capture.

You cannot use it on columns tracked by CDC.

Testing

Create table & insert sample data

CREATE TABLE dbo.tablea
(id int identity(1,1) PRIMARY KEY NOT NULL, UpdateTextTest TEXT)

INSERT INTO Dbo.tablea(UpdateTextTest)
VALUES('bBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbB')

Test updatetext without CDC

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
FROM dbo.tablea

UPDATETEXT   tablea.UpdateTextTest  @ptrval 8 1 'c';

This runs fine

Result

id  UpdateTextTest
1   bBbBbBbBcBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbB

The 9th value was changed to 'C'.

Enable CDC on the db and table

use my_test
EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'tablea',
@role_name     = NULL ,
@filegroup_name = N'PRIMARY',
@supports_net_changes = 1;
GO

Try to run the same query

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
FROM dbo.tablea

UPDATETEXT   tablea.UpdateTextTest  @ptrval 8 1 'c';

The same error

Msg 7137, Level 16, State 1, Line 41
UPDATETEXT is not allowed because the column is being processed by a concurrent snapshot or is being replicated to a non-SQL Server Subscriber or Published in a publication allowing Data Transformation Services (DTS) or tracked by Change Data Capture.

By the way, updatetext might be removed in future versions, and TEXT,NTEXT and IMAGE datatypes are also deprecated

  This feature is in maintenance mode and may be removed in a future
  version of Microsoft SQL Server. Avoid using this feature in new
  development work, and plan to modify applications that currently use
  this feature. Use the large-value data types and the .WRITE clause of
  the UPDATE statement instead.

Source

What could you do?

I would advise to try and change the column to either nvarchar(max), varchar(max) or varbinary(max) depending on the deprecated datatype used, and then using .write .

Testing with text:

UPDATE tablea
SET UpdateTextTest .WRITE (N'c',9,1)

Msg 258, Level 15, State 1, Line 44
Cannot call methods on text.

As it is now, I cannot use .Write since the datatype is text.

Changing the datatype and testing the query again, with CDC enabled.
    Alter table tablea
    alter column UpdateTextTest varchar(MAX)

UPDATE tablea
SET UpdateTextTest .WRITE (N'c',9,1)

Result:

id  UpdateTextTest
1   bBbBbBbBccbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbBbB

the 10th value was also changed to 'c'.

Extra info

Readtext works with cdc

DECLARE @ptrval varbinary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
   FROM tablea pr

READTEXT tablea.UpdateTextTest @ptrval 1 1;

Result:

 UpdateTextTest
B

Writetext does not work with CDC

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(UpdateTextTest)
FROM tablea pr
WRITETEXT tablea.UpdateTextTest @ptrval 'DDDDDD';

Error:

Msg 7138, Level 16, State 1, Line 92
The WRITETEXT statement is not allowed because the column is being replicated with Data Transformation Services (DTS) or tracked by Change Data Capture.


How do you structure a database that allows for groups to be formed/added by users and also be a part of like facebook?

You need three tables - User, Interest and an associative table for the many-to-many relation between them (a User can have many Interests and an Interest will have many Users), call it UserInterestLink or something. User and Interest will have autoincrement int fields as a surrogate primary key, UserInterestLink will have FKs to both User and Interest, like so:

|=================================================|
| TABLE: User                                     |
|=================================================|
| Name       | Type                               |
|=================================================|
| UserID     | int, autoincrement, PK             |
| << Other User fields here >>                    |
|=================================================|

|=================================================|
| TABLE: Interest                                 |
|=================================================|
| Name       | Type                               |
|=================================================|
| InterestID | int, autoincrement, PK             |
| << Other Interest fields here >>                |
|=================================================|

|=================================================|
| TABLE: UserInterestLink                         |
|=================================================|
| Name       | Type                               |
|=================================================|
| InterestID | int, FK to Interest.InterestID     |
| UserID     | int, FK to User.UserID             |
|=================================================|

I didn't mention the PK on UserInterestLink, for efficiency it would be a compound PK on (UserInterestLink.InterestID, UserInterestLink.UserID), but there's also an argument to be made for a seperate surrogate primary key (which I personally think is a waste of space, unless you need to allow for multiple UserInterestLinks between one User and one Interest). For a purely associative table in a many-to-many relationship, though, the compound primary key should work fine.

Edit: The complicated part of this isn't in the relational design, it'll be in the application code, because you'll need best-match/partial matching in order to show users the groups that most closely match their interests (rather than having accidental splinter groups all over the place). And if you want to out-Facebook Facebook, you'll need a smoother and better user experience than they offer.


What index(es) should I use for three columns join?

You should benchmark it using autotrace (http://betteratoracle.com/posts/10-using-autotrace) for example.

If you expect millions of rows to be returned, then I do not think indexing will help at all. Simple full table scan and hash joining is the most efficient way of executing this kind of query (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968).

Putting the least selective column first and using key compression can decrease the index size and reduce IO and CPU consumption  (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8806017524595).

The following index should be quite sufficient if you fetch only few rows:

create unique index test1 on example(extype,studentid,exid) compress 1;


User creation:

select dbms_metadata.get_ddl( 'USER', 'PHIL' ) from dual;

Default role:

select dbms_metadata.get_granted_ddl( 'DEFAULT_ROLE', 'PHIL' ) from dual;

System grants:

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'PHIL' ) from  dual;

Object grants:

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'PHIL' ) from dual;

Role grants:

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'PHIL' ) from dual;

Quotas:

select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;

If any of the above have no output, you'll get an exception that looks something like this:

SQL> select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'PHIL' ) from dual;
ERROR:
ORA-31608: specified object of type TABLESPACE_QUOTA not found
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5991
ORA-06512: at line 1

no rows selected

SQL>

Then do a search and replace on the output to change the username.


MySQL 5.5 to 5.6 Upgrade. Will data remain the same?

As noted, mysql_upgrade will change the system tables to include any new columns required.

MySQL 5.6 includes support for microseconds in DATETIME and TIMESTAMP, and as such uses a different format on disk for storage.  Conversion to the new format does not happen as part of mysql_upgrade, but will happen on  ALTER TABLE or OPTIMIZE TABLE and in which case you will not be able to start MySQL 5.5 and use this data directory.


limit keyword support in DB2 latest version

There was no change that I am aware of in Fixpack 5 to the requirement that the registry variable  DB2_COMPATIBILITY_VECTOR must be set to MYS for the ... LIMIT x OFFSET y functionality to work.  (Unsetting it in FP5 still results in an error if you try to use LIMIT / OFFSET).

You may want to look to see if DB2_COMPATIBILITY_VECTOR is set in the global registry.

Notice that running db2set alone does not show DB2_COMPATIBILITY_VECTOR:

$ db2set
DB2COMM=TCPIP
DB2AUTOSTART=YES

While running db2set -all does, since it's set at the global level:

$ db2set -all
[i] DB2COMM=TCPIP
[i] DB2AUTOSTART=YES
[g] DB2_COMPATIBILITY_VECTOR=MYS
[g] DB2SYSTEM=host1
[g] DB2INSTDEF=db2inst1


Check existence with EXISTS outperform COUNT! ... Not?

I've often read when one had to check existence of a row should always be done with EXISTS instead of with a COUNT.

It's very rare for anything to always be true, especially when it comes to databases. There are any number of ways to express the same semantic in SQL. If there is a useful rule of thumb, it might be to write queries using the most natural syntax available (and, yes, that is subjective) and only consider rewrites if the query plan or performance you get is unacceptable.

For what it's worth, my own take on the issue is that existence queries are most naturally expressed using EXISTS. It has also been my experience that EXISTS tends to optimize better than the OUTER JOIN reject NULL alternative. Using COUNT(*) and filtering on =0 is another alternative, that happens to have some support in the SQL Server query optimizer, but I have personally found this to be unreliable in more complex queries. In any case, EXISTS just seems much more natural (to me) than either of those alternatives.

  I was wondering if there was a unheralded flaw with EXISTS that gave perfectly sense to the measurements I've done

Your particular example is interesting, because it highlights the way the optimizer deals with subqueries in CASE expressions (and EXISTS tests in particular).

Subqueries in CASE expressions

Consider the following (perfectly legal) query:

DECLARE @Base AS TABLE (a integer NULL);
DECLARE @When AS TABLE (b integer NULL);
DECLARE @Then AS TABLE (c integer NULL);
DECLARE @Else AS TABLE (d integer NULL);

SELECT
    CASE
        WHEN (SELECT W.b FROM @When AS W) = 1
            THEN (SELECT T.c FROM @Then AS T)
        ELSE (SELECT E.d FROM @Else AS E)
    END
FROM @Base AS B;

The semantics of CASE are that WHEN/ELSE clauses are generally evaluated in textual order. In the query above, it would be incorrect for SQL Server to return an error if the ELSE subquery returned more than one row, if the WHEN clause was satisfied. To respect these semantics, the optimizer produces a plan that uses pass-through predicates:

The inner side of the nested loop joins are only evaluated when the pass-through predicate returns false. The overall effect is that CASE expressions are tested in order, and subqueries are only evaluated if no previous expression was satisfied.

CASE expressions with an EXISTS subquery

Where a CASE subquery uses EXISTS, the logical existence test is implemented as a semi-join, but rows that would normally be rejected by the semi-join have to be retained in case a later clause needs them. Rows flowing through this special kind of semi-join acquire a flag to indicate if the semi-join found a match or not. This flag is known as the probe column.

The details of the implementation is that the logical subquery is replaced by a correlated join ('apply') with a probe column. The work is performed by a simplification rule in the query optimizer called RemoveSubqInPrj (remove subquery in projection). We can see the details using trace flag 8606:

SELECT
    T1.ID,
    CASE
        WHEN EXISTS
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
        ) THEN 1
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Part of the input tree showing the EXISTS test is shown below:

ScaOp_Exists
    LogOp_Project
        LogOp_Select
            LogOp_Get TBL: #T2
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier [T2].ID
                ScaOp_Identifier [T1].ID

This is transformed by RemoveSubqInPrj to a structure headed by:

LogOp_Apply (x_jtLeftSemi probe PROBE:COL: Expr1008)

This is the left semi-join apply with probe described previously. This initial transformation is the only one available in SQL Server query optimizers to date, and compilation will simply fail if this transformation is disabled.

One of the possible execution plan shapes for this query is a direct implementation of that logical structure:

The final Compute Scalar evaluates the result of the CASE expression using the probe column value:

The basic shape of the plan tree is preserved when the optimize considers other physical join types for the semi join. Only merge join supports a probe column, so a hash semi join, though logically possible, is not considered:

Notice the merge outputs an expression labelled Expr1008 (that the name is the same as before is a coincidence) though no definition for it appears on any operator in the plan. This is just the probe column again. As before, the final Compute Scalar uses this probe value to evaluate the CASE.

The problem is that the optimizer doesn't fully explore alternatives that only become worthwhile with merge (or hash) semi join. In the nested loops plan, there is no advantage to checking if rows in T2 match the range on every iteration. With a merge or hash plan, this could be a useful optimization.

If we add a matching BETWEEN predicate to T2 in the query, all that happens is that this check is performed for each row as a residual on the merge semi join (tough to spot in the execution plan, but it is there):

SELECT
    T1.ID,
    CASE
        WHEN EXISTS
        (
            SELECT 1
            FROM #T2 AS T2
            WHERE T2.ID = T1.ID
            AND T2.ID BETWEEN 5000 AND 7000 -- New
        ) THEN 1
    ELSE 0
    END AS DoesExist
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

We would hope that the BETWEEN predicate would instead be pushed down to T2 resulting in a seek. Normally, the optimizer would consider doing this (even without the extra predicate in the query). It recognizes implied predicates (BETWEEN on T1 and the join predicate between T1 and T2 together imply the BETWEEN on T2) without them being present in the original query text. Unfortunately, the apply-probe pattern means this is not explored.

There are ways to write the query to produce seeks on both inputs to a merge semi join. One way involves writing the query in quite an unnatural way (defeating the reason I generally prefer EXISTS):

WITH T2 AS
(
    SELECT TOP (9223372036854775807) *
    FROM #T2 AS T2
    WHERE ID BETWEEN 5000 AND 7000
)
SELECT
    T1.ID,
    DoesExist =
        CASE
            WHEN EXISTS
            (
                SELECT * FROM T2
                WHERE T2.ID = T1.ID
            ) THEN 1 ELSE 0 END
FROM #T1 AS T1
WHERE T1.ID BETWEEN 5000 AND 7000;

I wouldn't be happy writing that query in a production environment, it's just to demonstrate that the desired plan shape is possible. If the real query you need to write uses CASE in this particular way, and performance suffers by there not being a seek on the probe side of a merge semi-join, you might consider writing the query using different syntax that produces the right results and a more efficient execution plan.


As far as I understand, the answer is no, you can't use XtraDB in MariaDB 10.2. From the Changes & Improvements in MariaDB 10.2 page:

  XtraDB in 10.2 is not up to date with the latest features of InnoDB and cannot be used. As the InnoDB on disk format is identical to XtraDB's this will not cause any problems when upgrading to MariaDB 10.2. See Why does MariaDB 10.2 use InnoDB instead of XtraDB?


How to specify the primary key number space for a postgres table?

If you want to happen it automatically (I mean, without the application doing that), you have to create a sequence:

CREATE SEQUENCE some_table_pk_seq START 50;

Then change the column to use this as a default:

ALTER TABLE some_table
ALTER COLUMN id SET DEFAULT nextval('some_table_pk_seq'::regclass);

And that's it.  Remember that the default will be used only if you don't pass a value to the given column in your INSERT statements.


Given the information in the question I would start out with something like:

CREATE TABLE players
( player char(8) not null primary key
, additional attributes );

CREATE TABLE games
( game_id int not null primary key
, additional attributes );

CREATE TABLE player_games (match?)
( player char(8) not null
     references players (player)
, game_id int not null
     references games (game_id)
, participant_no smallint not null
,    constraint ... check (participant_no between 1 and 4)
,    primary key (player, game_id)
,    unique (game_id, participant_no) )

CREATE TABLE results
( game_id int not null
, player char(8) not null
, score ... not null
,     foreign key (game_id, player)
      references player_games (game_id, player)

Example of queries that can easily be answered

Which games has a player participated in?

SELECT game_id
FROM player_games
WHERE player = ?

JOIN with games if you need more info from each game

Which players participated in a game?

SELECT player
FROM player_games
WHERE game_id = ?

JOIN with players if you need more info from each player

Order the players from game X according to there score:

SELECT player, score
FROM results
WHERE game_id = X
ORDER BY score


SQL storage sizing - How to get statistics of what data is being accessed

If the crux of the question is, "How do I get correct data to size my storage subsystem?" and you want to it fairly repeatedly across many servers, use windows performance counters.

Here is the list I would minimally have:

Logical Disk(*)\Current Disk Queue Length
Logical Disk(*)\Disk Read Bytes/Sec
Logical Disk(*)\Disk Reads/Sec
Logical Disk(*)\Disk Write Bytes/Sec
Logical Disk(*)\Disk Writes/Sec
Logical Disk(*)\Disk Transfers/Sec
Logical Disk(*)\Avg. Disk Sec/Read
Logical Disk(*)\Avg. Disk Sec/Write
Logical Disk(*)\Avg. Disk Sec/Transfer

The Avg. Disk Sec/XXX is going to give you the current milliseconds per operations (read/write). This is important to know so when you move to new storage you meet or exceed the current setup. It's also important as we don't want extremely high numbers here as that will manifest itself in other ways, making it "feel" like SQL Server is slow.

The Disk XXX/Sec is going to give you the number of operations (IOPS) with the Transfers being total IOPS (or Reads + Writes). This is also going to give you an IO profile of your server, knowing whether you're heavy read or write so that any caches could be tuned appropriately or more cache bought per storage unit.

The Disk XXX Bytes/Sec is going to give you an understanding of the size of the IO happening for reads/writes/total. This, in association with the Disk XXX/Sec should give you a better IO profile. Are you doing many small IOs, many large, a mixture, etc. It'll help you decide how to carve up LUNs, shares, etc. It'll also help you understanding what you'll need to do to any caches or cache sizes that may be in the mix.

  I know I'm using 30k IOPS. And the storage solution can provide that. BUT the proposed storage solution can achieve that using Fast VP / Fast Cache. That will actually mean that it's able to deliver 30k for some data (the hot one). But not for everything. I need to know what set of data requires those 30k IOPS. Because it's random data all the day long, I know the storage won't be able to cache or move that data to the fastest drives in order to obtain 30k. And I will end up with issues.

You have no control over what users end up wanting, and in some cases (insert) the data doesn't even exist yet. If you're having issues with storage at that level you'll want to go back and ask your storage admins to not be tiered. If you want to know every page that is touched per database, period, you'll want to create an extended events session and ask for a few TBs worth of storage to handle the crazy large amount of data you'll be doing. IMHO you're aiming at an unknown moving target that isn't feasible at all. Talk to your storage team.


Update expiring Mongo cluster SSL certificate

If you CA don't change, just cert expiration time, you should not have any problems to change cert one node at the time (rolling upgrade).

But...
Basically following this procedure, so you "downgrade" you nodes ssl level allowSSL and doing change of certificate one node at time. After that you can bring ssl level back to requireSSL after everybody have new certs.


Here's what we did in LedgerSMB:

Entity table storing basic container info on individuals and businesses
location table storing address information.
Right now we have a join table between address and entity, but in future versions that will probably be merged with the address table.
Tables 'person' and 'company' which extend entity for that purpose.

That basic structure would work on MySQL too.  On PostgreSQL we do some magic with partial unique indexes to make sure that billing addresses are unique per entity, but contact addresses are not.  This would not be possible to enforce in MySQL as easily though it could be done with creative design.


MySQL client out of memory - Linked Table MsSQL to MySQL

Do the DELETE in chunks.  More discussion .


What are thresholds in SQL Server 2014 "big table join to small table" cardinality estimation optimization?

The whitepaper does not define "large" for any examples, it uses the terms "large" and "small" to help explain the math the new CE is doing compared to the legacy CE.

The section you referenced shows a join predicate that contains a mix of equality and inequality predicates. The new CE will look at the rowcounts for the tables and determine which one is "large", and use that for the estimate. The legacy CE didn't look at rowcounts, it would just multiply the selectivity of each predicate.

HTH


Ola Hallengren DataBaseBackup to URL results in un-equal file sizes causing IO Device Error

All Ola's procedure does is to call the BACKUP command. I.e., the fact that you are using Ola's procedure is irrelevant. If you want to determine this with 100% certainty, then just pick up the backup command executed (should be in the CommandLog table methinks, or worst case using a trace) and execute this directly (possibly using an Agent job).
My guess is that you are unfortunate combined with using backup compression. I.e., some backup thread just happen to run into data that doesn't compress well, and because of that the size of that file becomes larger than the other thread's files.
You can verify this by turning off compression, and see that you get a much more even file size. I understand that this might not be practical because of the database size, but perhaps you have a smaller database with same symptom for which you can do this test? I've asked around and we'll see if I can get a confirmation on my theory.
I tried this myself and did indeed see a bigger file size variation with compression.
FWIW, in 7.0 (when striping was introduced) the backup thread pushed as much data into the destination (file or tape) as the destination could consume. This gave best backup throughput, but uneven file size if one tape device (or file destination) happened to be much slower than the other. The algorithm changed in 2000 to spread the data evenly across the backup files. My assumption is that this determination of how the data is to be spread is done before compression.
SQL Server pre-allocates the backup file for performance reasons. When you use compression it has to guess what size you end up with. You can end up in a situation where it tries to pre-allocate more than what it would eventually need, causing the backup to fail. Trace flag 3042 changes this. It causes SQL Server to allocate storage on-the-fly. I very much doubt this will change anything for you, but thought I'd mention this so you can be aware of what the trace flag does and don't get your hopes up too much in case you read about the trace flag and decide to test it.