Effect of an index on update statements where update column is not in an index

For a relatively fast modern system the addition of a single index to an OLTP table will probably be virtually undetectable from a performance standpoint for the vast majority of systems.  That said, you should not create needless indexes, and you probably should not create single-column indexes for every column in a table.

You are correct in the assumption that for many queries the presence of useful indexes will result in a very noticeable speed improvement.

Although your question appears to be around performance, there are several other potential issues around adding indexes, including but not limited to:

Time required to create the index may result in blocking while the index is added to the table.  The lock is very short lived, and most likely won't create a big problem.
Index changes result in execution plans being invalidated for any plans that reference the underlying table.  When those execution plans are recompiled, performance may change negatively for some queries.
Index modifications may result in queries returning errors where none were previously returned.  Take the case of a filtered index that was used to return dates contained in a varchar field; if the filter eliminated any rows that weren't dates, and that filter is subsequently changed, queries that relied upon that index may now fail when attempting to convert non-date data.
A new index may cause the order of execution to change resulting in possible deadlocks occurring where non occurred before.


error in Execute SQL Task with parameter

Have you actually tried executing the package or are you just trying to parse the query?

Attempting to parse OLE DB queries with params will fail with the error you've described. Don't parse the query if using OLE DB.

I've created your task and tested. It executes fine, but if you click "Parse query" it returns the same error that you are getting.


how do I insert timzone-pois.sql into MYSQL?

Looking at the manual, for Windows, you need to download the timezones which is a zip file that contains a SQL file. This can be run in workbench.


You're close, but when you introduce two tables, you have to be consistent with the alias.  So
at the declaration of the update statement, you'd want to start with:

UPDATE ua1
SET properties = 'Charlie'

Then create a from clause just as you would with a select statement, declaring your aliases:

FROM UserAttribute ua1
    INNER JOIN UserAttribute ua2
        ON ua2.UserID = ua1.id
WHERE ua1.name = 'Contact';


Is it mandatory to enable --log_slave_updates with MySQL Replication using GTID?

The MySQL Documentation suggests it : See Setting Up Replication Using GTIDs

Keep in mind that MySQL won't even start up with it. Why ? From the Documentation:

  Step 3: Restart both servers with GTIDs enabled.  To enable binary logging with global transaction identifiers, each server must be started with GTID mode, binary logging, slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, you should prevent unwanted or accidental updates from being performed on either server by starting both in read-only mode. This means that both servers must be started with (at least) the options shown in the following invocation of mysqld_safe:

  shell> mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &

Since binary logging is active on both Master and Slave, the Master's binary logs and the Slave's binary logs will both have GTIDs recorded along with the transactions. This makes it possible for auto-positioning a Slave to its Master using the latest GTID in the Slave when starting Replication. One of the key steps clearly says in that same document page:

  Instruct the slave to use the master as the replication data source and to use auto-positioning, and then start the slave.

Therefore, --log-slave-updates must be enabled in order for the auto-positioning mechanism to be active. Even just beyond the mechanism, @jynus points out in his answer to mysql 5.6 gtid replication: is log_slave_updates required? that mysqld won't even bother to start if --log-slave-updates is not specified.


Generate INSERT list but only for non-nullable columns

You can't change the way the UI functions, you'll have to write that kind of code yourself, sorry. But in the simple case, this is easy enough to generate on your own with sys.dm_exec_describe_first_result_set (save this code as a template or snippet):

DECLARE @table nvarchar(261) = N'dbo.YourTableName';

;WITH x AS
(
  SELECT name, system_type_name,
    column_ordinal = ROW_NUMBER() OVER (ORDER BY column_ordinal)
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM ' + @table, N'', 0)
  WHERE is_nullable = 0
    AND is_identity_column = 0
    AND is_computed_column = 0
)
SELECT N'INSERT ' + @table + N'(', N'--', column_ordinal = 0
UNION ALL
SELECT CHAR(13) + CHAR(10) + CASE column_ordinal
  WHEN 1 THEN N'' ELSE N',' END + QUOTENAME(name),
  N'--', column_ordinal
FROM x
UNION ALL
SELECT N'
) VALUES (', N'--', 1000
UNION ALL
SELECT CHAR(13) + CHAR(10) + CASE column_ordinal
  WHEN 1 THEN N'' ELSE N',' END
  + N'<' + name + N', ' +system_type_name + N',>',
  N'--', 1000 + column_ordinal
FROM x
UNION ALL
SELECT N'
);', N'--', 10000
ORDER BY column_ordinal;

This leaves out the identity column and any computed columns, but the view doesn't tell you about other things like not null columns with defaults or rowguidcol columns, which should also stay out of your statement.

The following solution is probably more appropriate in general since you are still using SQL Server 2008 R2 (that view was introduced in SQL Server 2012). It handles rowguidcol and default values but is a lot more verbose because of the work required to generate friendly-looking types.

DECLARE @table nvarchar(261) = N'dbo.YourTableName';

;WITH x AS
(
  SELECT [column] = c.name, [type] = t.name, t.system_type_id,
    t.user_type_id, c.max_length, c.precision, c.scale,
    column_ordinal = ROW_NUMBER() OVER (ORDER BY c.column_id)
  FROM sys.columns AS c
  INNER JOIN sys.types AS t
    ON c.system_type_id = t.system_type_id
   AND t.user_type_id = CASE -- filter out false sysname
     WHEN c.system_type_id = 231 THEN CASE
       WHEN c.max_length = 256 THEN c.user_type_id
       ELSE c.system_type_id END
     ELSE c.user_type_id END
  WHERE [object_id] = OBJECT_ID(@table)
    AND c.is_nullable       = 0
    AND c.is_identity       = 0
    AND c.is_computed       = 0
    AND c.is_rowguidcol     = 0
    AND c.default_object_id = 0
)
SELECT N'INSERT ' + @table + N'(', N'--', column_ordinal = 0
UNION ALL
SELECT CASE column_ordinal WHEN 1 THEN N'' ELSE N',' END
    + QUOTENAME([column]), N'--', column_ordinal
  FROM x
UNION ALL SELECT N') VALUES(', N'--', 1000
UNION ALL
SELECT CASE column_ordinal WHEN 1 THEN N'' ELSE N',' END
    + N'<' + [column] + N',' + [type] + CASE
    WHEN system_type_id IN (106,108)
    THEN N'(' + CONVERT(varchar(11), precision)
       + N',' + CONVERT(varchar(11), scale) + N')'
    WHEN system_type_id IN (59,62)
    THEN N'(' + CONVERT(varchar(11), precision) + N')'
    WHEN system_type_id IN (41,42,43)
    THEN N'(' + CONVERT(varchar(11), scale) + N')'
    WHEN system_type_id IN (167,175,239,231,165,173) AND user_type_id <> 256
    THEN N'(' + CASE max_length WHEN -1 THEN N'max' ELSE
      CONVERT(varchar(11), max_length / CASE
        WHEN system_type_id IN (231,239) THEN 2 ELSE 1 END) END + ')'
    ELSE N''
   END
    + N',>', N'--', 1000 + column_ordinal
  FROM x
UNION ALL SELECT N');', N'--', 10000
ORDER BY column_ordinal;

Note that in both cases I tried to match the output of SSMS exactly with the exception of every single tab and carriage return. I don't think their output makes sense in all aspects, but you can tailor it to your liking.


Selecting Multiple Rows in One Query with Multiple Conditions

You have 2 basic syntactical options for doing this in one query and 2 options on whether to send the values in the query or load them first in a table:

the normal AND / OR (parentheses are redundant here but it's good to use them with OR, just in case the WHERE gets more complicated):

WHERE (  ( x = ? AND y = ? AND z = ? )
      OR ( x = ? AND y = ? AND z = ? )
      ...
      OR ( x = ? AND y = ? AND z = ? )
      )

compact IN with "row constructor":

WHERE (x,y,z) IN ((?,?,?), (?,?,?), ...)

load the triplets in a (temporary) table and JOIN:

CREATE (TEMPORARY) TABLE tmp_table
--- ;

INSERT INTO tmp_table (x,y,z)
VALUES (?,?,?), (?,?,?), ... ;

SELECT t.*
FROM my_table AS t
  JOIN tmp_table AS tmp
  ON  ( t.x = tmp.x AND t.y = tmp.y AND t.z = tmp.z )
 ;

or:

  ON  (t.x, t.y, t.z) = (tmp.x, tmp.y, tmp.z)

The first 2 options are equivalent but they may differ in efficiency, depending on version. This syntax of IN with tuples (row constructors) does not use indexes most effectively in older versions. In 5.7 the optimizer identifies the two syntaxes as equivalent (see MySQL docs: Row Constructor Expression Optimization). Test!

The other choice of using a table may be better when you want to query for a number of parameters / triplets. You can also index the (temp) table. Test!

So, the basic advice is to test in your version/configuration/setup, with the tables having sizes similar to their predicted sizes, and with various number of parameters.

Two more ways that might be worth testing too:

The simple UNION ALL. Since we just want to run multiple identical queries where only the parameters differ. One disadvantage is that the query gets really long and clumsy-looking if the basic query is complex and you have many triplets to check:

SELECT t.* FROM my_table AS t
WHERE ( x = ? AND y = ? AND z = ? ) UNION ALL
SELECT t.* FROM my_table AS t
WHERE ( x = ? AND y = ? AND z = ? ) UNION ALL
---
SELECT t.* FROM my_table AS t
WHERE ( x = ? AND y = ? AND z = ? ) ;

Using a derived table (with UNION ALL) in the JOIN variation. This may use an optimization (that was added in 5.5 or 5.6) that can materialize and index a derived table:

SELECT t.*
FROM my_table AS t
  JOIN
      ( SELECT ? AS x, ? AS y, ? AS z  UNION ALL
        SELECT ?, ?, ?  UNION ALL
        ---
        SELECT ?, ?, ?
      )
    AS tmp
  ON  ( t.x = tmp.x AND t.y = tmp.y AND t.z = tmp.z )
 ;


MariaDB-10.2.2 InnoDB merged from 5.7.14
MariaDB-10.2.35 Merge new release of InnoDB 5.7.32
Lots of miscellany features have been merged at random times.
MySQL 8.0 and MariaDB 10.5 are quite different.  Still, you may not notice any differences unless you reach into rarely-used features.
The two play leapfrog.  For example.
MariaDB had CTEs and Windowing functions in 10.2, long before they were added in MySQL 8.0.
On the other hand, MySQL led the way with JSON and some functions (eg ST_distance_sphere).
Better links:
https://mariadb.com/kb/en/function-differences-between-mariadb-and-mysql/
https://mariadb.com/kb/en/mariadb-vs-mysql-features/
And there maybe more.  Note: it is in MariaDB's best interest to list the differences; MySQL prefers to mostly ignore the existence of MariaDB.
Errors, issues, bugs -- They look at each other's bug list, then add fixes as they see fit.
Syntax - Not much difference (except for CTEs, see above)
Features and behavior - MariaDB moved toward standards more aggressively than MySQL.  For example, in a subquery with an ORDER BY, the ordering became ignored in MariaDB long before in MySQL.  ONLY_FULL_GROUP_BY is another point of difference.  In both of these examples, the "new" version is "more correct", though incompatible.
The Optimizers have diverged.  I deal with performance a lot, but I would say only a few percent of queries show a noticeable difference between the products.  And the difference could be in favor of either product.
If you are just starting, don't worry about which one you pick; you have a long learning curve before you get into the esoteric things I have listed.


MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels?

The semantics of those isolation modes cannot be guaranteed in SBR since the Slave is unlikely to execute the statements of multiple threads in the same order.

More specifically, from the Change Log:

----- 2008-11-14 5.1.30 General Availability -- Bugs Fixed -- -----

With statement-based binary logging format and a transaction isolation level of READ COMMITTED or stricter, InnoDB printed an error because statement-based logging might lead to inconsistency between master and slave databases. However, this error was printed even when binary logging was not enabled (in which case, no such inconsistency can occur). Bug #40360


Does SQL Server Snapshot replication completely copy the data every time or does it issue deltas?

From Books On Line:

  Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

  Using snapshot replication by itself is most appropriate when one or more of the following is true:

  Data changes infrequently.
  It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
  Replicating small volumes of data.
  A large volume of changes occurs over a short period of time.

  Snapshot replication is most appropriate when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended. Given certain types of data, more frequent snapshots may also be appropriate. For example, if a relatively small table is updated at the Publisher during the day, but some latency is acceptable, changes can be delivered nightly as a snapshot.

  Snapshot replication has a lower continuous overhead on the Publisher than transactional replication, because incremental changes are not tracked. However, if the dataset being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to utilize snapshot replication.

Therefore, deltas are not generated, the entire database is snapshotted and replicated.


Best way to speed up requests using date columns

If there is only a small subset of rows with Archive = 0, you could use a filtered index:

CREATE NONCLUSTERED INDEX IX_Archive_Filtered
    ON [schema].[table] ([Date_A], [Date_B])
    INCLUDE ([Archive])
    WHERE ([Archive] = 0)

Note: I've made the index on the two date fields only because I don't know of any other fields in your table - it might make more sense for this to be on a different field

Additional Note: always take care to include the filtering columns in the filtered index keys or included columns, so here for one example of why: Incorrect row estimation given with a filtered index

This keeps the index small (which means it uses less storage, and is quicker to read from), because only the rows where Archive is 0 are included in it.

If a filtered index won't work for you (and there are a number of scenarios where they don't work, see Filtered Indexes and Dynamic SQL for one example), you could try a normal nonclustered index on the archive field OR the date fields.  You would want to create the index on whichever field is most selective - meaning which one will narrow down the results the most.

For instance, again if only a small subset of your rows have an Archive value of 0, you could create this index:

CREATE NONCLUSTERED INDEX IX_Archive_Includes
    ON [schema].[table] ([Archive])
    INCLUDE ([Date_A], [Date_B])

This will let your query seek to the correct rows (where Archive is 0), and then filter easily on Date_A and Date_B.

Additional comments about your query:

you really shouldn't use select * - enumerate each of the fields you actually need in your results to be more efficient, and allow more effective indexing
you shouldn't use nolock if the accuracy of the results is important

see this post for a quick demo of how results can be wrong with nolock: Using NOLOCK? Here’s How You’ll Get the Wrong Query Results
see this post for a lot more detail about using nolock (which is the same as the read uncommitted isolation level): The Read Uncommitted Isolation Level


'Cannot generate SSPI context' is a generic error. It can be caused by many issues, like an outaded password, clock drift, Active Directory access permissions, failure to register an SPN and so on and so forth.

There is no solution to this problem. The only 'solution' is to investigate the cause, as per KB811889 and/or Troubleshooting Kerberos Errors. Applying one solution or another from random Internet resources, w/o understanding the cause, may or may not solve the issue, may or may not cause frustration, may or may not cause irrevocable damage.


Why is "Lookup" colored as a function reserved word in SQL Server?

At first I thought it came from Sybase (which is where SQL Server originated of course), which has a lookup function, but this is PowerBuilder-related. And then I checked SQL Server 2000 and it doesn't light up in pink in Query Analyzer...

...if it was legacy from Sybase I would have expected it to be in the list of color-coded words all along. It's possible, I suppose, that the grammar file was updated and that it was omitted "by mistake" in 2000, but I doubt it. It is much more likely that it is colored because it is listed in the T-SQL Language Service as a future compatibility word, or it was thrown into the Language Service in anticipation of being used. (I am waiting on official confirmation of this, and I will share what I can.)

Some other fun examples (I complained about a few of these on Connect back in 2008, but it was closed as Won't Fix) of inappropriate highlighting of words that are also not on the list you cite:

Domains lights up in green
Description lights up in blue
Server lights up in blue
Instead lights up in blue
RC2 and RC4 light up in blue

At the time I didn't capture the Lookup or Instead examples, and I'm sure there are some others as well. Though I'm guessing the document you're looking at is not as up to date as it could be either; at the very least, INSTEAD should be on that list since it is a part of T-SQL now (since INSTEAD OF triggers were introduced). I bet there are at least 20 other keywords that have been added for SQL Server 2012 but aren't on that list, too. Quickly scanning there are some notable exclusions that should be there: OFFSET, IIF, FORMAT, etc.

Another example you might find interesting; try putting a word like INSTEAD in a string but on its own line. This runs fine but it doesn't look like it will:

SELECT 'foo
INSTEAD
';

(This one courtesy of a bug filed by @JonSeigel.)

I've probably filed and commented on a couple of dozen other bugs against Management Studio's syntax highlighting; it certainly isn't perfect. I appreciate that you want to know why but we may not ultimately find out. As you can see from a lot of these Connect items, they usually ignore/defer them, or fix them without much explanation.


log_min_duration_statement setting is ignored

You were close. Your last idea is actually the way to go:

log_statement = none
log_min_duration_statement = 10000

Then no statement will be logged, except those running longer than 10 seconds - including the query string itself. Logging may have seemed to stop because 10 seconds is a high threshold. I am using 2 seconds normally, but YMMV.

This related answer on SO has more:
Cannot get log_min_duration_statement to work


Transaction rollback hanging from past two months(MSSQL 2016)

After about 60 days, the rollback process was finally completed. It seems the only way and the only solution you should do is wait, wait, and keep waiting.


Hide column if any value is null or zero via proc sql or view

You can't hide a column dynamically unless you use dynamic SQL. This quickly gets complicated: what if the zeros are in different columns on different rows? What should the output be

If the zeros/nulls are in the same column, then you can "hide" a column by not selecting it.

SELECT
   Kağıt Cinsi, Sarı, Mavi
FROM
   MyTable
WHERE
   (Yeşil IS NULL OR Yeşil = 0)

You may not need the filter here: I added it because the question was unclear


You have a few options depending on what services you use in Couchbase.

The easiest way that I have used (since v4.6) is query via Couchbase's SQL-like N1QL language and the meta().cas value.

For example, CAS value converted to a timestamp and truncated down to seconds works well, though it's largely undocumented and historically not recommended for this use.  Now it represents revision time (more or less, beyond scope here).

A query for documents before today would look something like this (sorry it's not elegant, probably a better way):

SELECT meta().id,
((NOW_MILLIS())/1000 - meta().cas/1000000000 )/86400 as days_before_today
FROM bucket1
WHERE meta().cas/1000000000  <= (NOW_MILLIS())/1000-86400

Note: 86400 seconds in a day, sub in your own timestamp for WHERE clause or change to reflect the range you are interested in.

Obviously if you were able to add a revision timestamp value to the document you'd have more full control over the value.

You could probably also use TTL value settings on the doc though sounds like you do not specifically want to expire documents.


Should a table be split if only a particular column gets updates?

It depends.

Will you be accessing (SELECTing) only the 'static' data frequently?  With complex WHERE clauses?  If so, "vertical partitioning" is beneficial.  It would keep the SELECTs from interfering with the UPDATEs.

Will the JSON columns be large, and the 'static' columns be relatively small?  If so, that adds to the argument for vertical partitioning.

When you are ready to update a JSON field, do you have the PRIMARY KEY in hand?  Searching around for which row to change may interfere with other UPDATEs.

Sorry there is no straightforward answer; "your mileage may vary".

Rephrasing

(This discussion applies to any InnoDB table with "big" fields -- TEXT/BLOB/etc.)

If you usually access the big field (JSON, in your case) --> 1 table
If you often don't look at the big field --> 2 tables
If you usually do a "point query" (ie, you have the id of the row and don't need to search for it) --> 1 table
If you often check lots of rows to find the one (or few) to fetch/update --> 2 tables
But... If most rows are bigger than 8KB (and you are using InnoDB), then, because of the way 'large' columns are stored off-block, there is not much advantage for 2 tables.


How to remove database from explorer after deleting .MDF and .ldf files

Are you attempting this with SSMS? I followed this process using SSMS v18.4:

Take DB offline
Manually delete mdf and ldf files.
Right click on database in SSMS and click 'Delete'.

This worked just fine for me.


Adding an ID column based on two other columns

You just need

SELECT Location,
       Reference,
       Year,
       Int1,
       Int2,
       ROW_NUMBER() OVER (PARTITION BY Year, Reference
                              ORDER BY Int2 DESC) AS [Id]
FROM Value

To give your desired results (online demo).

In the event of tied Year,Reference,Int2 this will arbitrarily assign a sequential numbering between the tied rows.


Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights?

to relink the login to the correct id you will have to run (I assume that the login is appuser, and the databas user appuser):

  alter user appuser with login = appuser ;

From BOL:

To change the name of a user requires ALTER ANY USER on the database. To change the default schema requires ALTER permission on the user. Users can change only their own default schema.
Requires CONTROL permission on the database to remap a user to a login

As you explained that you have the db_owner role on the database, it should work.


Count missing values across columns and join back to original table

SELECT id, date, t1, t2, t3
     CASE WHEN t1 IS NULL THEN 1 ELSE 0 END
     + CASE WHEN t2 IS NULL THEN 1 ELSE 0 END
     + CASE WHEN t3 IS NULL THEN 1 ELSE 0 END AS missing_across_all_cols
   FROM testdata


PostgreSQL update column B with values generated from function that uses values from column A

On @Akina's and @a_horse_with_no_name's advice I downloaded & imported MaxMind's GeoLite2 Country database (in CSV format) using the following:

create table geoip_blocks (
    network cidr,
    geoname_id bigint,
    registered_country_geoname_id bigint,
    represented_country_geoname_id bigint,
    is_anonymous_proxy bool,
    is_satellite_provider bool
);

copy geoip_blocks from 'C:\tmp\GeoLite2-Country-Blocks-IPv4.csv' delimiter ',' csv header;
copy geoip_blocks from 'C:\tmp\GeoLite2-Country-Blocks-IPv6.csv' delimiter ',' csv header;

create index geoip_blocks_network_idx on geoip_blocks using gist (network inet_ops);

create table geoip_locations (
    geoname_id bigint,
    locale_code varchar(2),
    continent_code varchar(2),
    continent_name varchar(255),
    country_iso_code varchar(2),
    country_name varchar(255),
    eu_member bool
);

copy geoip_locations from 'C:\tmp\GeoLite2-Country-Locations-en.csv' delimiter ',' csv header;

then ran an UPDATE to populate my clientip_country column with the appropriate 2 letter ISO country code using the clientip column data:

update
    client_info
set
    clientip_country = country_iso_code
from
    geoip_blocks
    inner join geoip_locations on geoip_blocks.geoname_id = geoip_locations.geoname_id
where
    network >>= client_info.clientip::inet
    and clientip_country is null;

Took a couple of minutes to update just over 100,000 records. Nice.


How to add generated column to db2 table?

Db2 versions for different platforms tend to have different features and statement syntax details, so you should always consult documentation for your exact database version and platform. If you do so, you will see that you can only use a limited selection of expressions for generated columns -- either an identity or a row change timestamp.
Instead of a generated column I suggest you simply create a view with your CASE expression for an extra column.


Yes, this is possible, no body required. How it got this way? Your guess is as good as ours. It is easy to do but we can't possibly know whether this was human error or some kind of truncation from a script or tool.

CREATE PROCEDURE dbo.blat
AS
GO
EXEC dbo.blat;

Output:

  Command(s) completed successfully.

Good to have source control to sort this kind of thing out.

If you're concerned, though, you should check your application and the rest of the procedures to see if anything calls this empty procedure and actually expects it to do anything. Error handling, unit tests, etc. may be passing simply because it isn't throwing an error.


It looks like it only accepts one argument. So try doing this...

DECLARE @UNCSourcePath nvarchar(255) = 'NET USE S: "\\OTHER_SERVER\C$\Program Files"'
EXEC xp_cmdshell @UNCSourcePath


I think this is a many to many relation ship, and the suggested way for that is to have a table for the relation. i.e.:

Division
- DivisionID
- DivisionName

Template
- TemplateID
- TemplateName

division_template
- DivisionID
- TemplateID


Cannot locally import UTF-8 encoded SQL database on Mac

Based on this blog post

http://ilikekillnerds.com/2014/08/fixing-sequel-pro-sql-encoding-error-for-imported-sql-files/

When importing your database dump, you need to select Western (Mac OS Roman) as the encoding format for the file to import without issue.


I think this falls under the category of things whose requirements are so varied and the solutions so complex that there aren't standardized solutions.

Ultimately you need these elements:

A system for recording the recurrence schedule.
A system for generating the recurring events.
A system for storing the events generated.

You probably need to spend a fair amount of time determining your requirements before you start implementing a solution, as getting it wrong up front will cost you tons of time later.

You could take a peek at the Mozilla Lightning project, as they have recurring tasks.


You could simplify your design and make it more powerful by keeping all of the opening times in a single table which includes a date range and a priority code for resolving conflicting records.

It would look something like this:

create table OPENING
( id INT
, entry_id INT
, from_date DATE
, to_date DATE
, priority INT
, weekday_id INT
, start TIME
, end TIME
, pause_start TIME
, pause_end TIME
, editor_id INT
, timestamp TIMESTAMP
)

The differences to note between your model and this one are:

You keep one set of records for each date range that applies instead of just having the current data.
You can keep a date range history of opening hours.  This also allows you to future date ranges which is handy for getting data set up in advance.
You keep regular hours and exceptions in a single table, so you don't need to have conditional logic in retrieving the opening hours.
Exceptions have a date range also, often a range of one day if that is what applies.
All records have a priority level.  Regular records have a low priority, exceptional records have a higher priority.  When reading just select top 1 order by priority descending.


How to calculate the cumulative difference between rows in Postgres?

You can use LAG() function in a query with GROUP BY just like any other query. The only difference is that the columns allowed in the window (OVER) and in the LAG are the ones allowed in SELECT after a GROUP BY:

select
    date,
    session_id,
    sum(upload_usage) as upload,
    sum(download_usage) as download,
    sum(total_usage_on_a_day) as total_as_cumm,
    sum(total_usage_on_a_day)
    - coalesce(lag(sum(total_usage_on_a_day)) over (partition by session_id order by date), 0)
        as expected_difference
 from jiodba.s_crc_zda_mon_conn_usage
 where gpart = '1100043958'
   and zzaccess_ntwk_id = 'FTTH'
 group by session_id, date
 order by session_id, date
 limit 100 ;

I'm not sure why you used max(usage) while you name the column total_usage. I changed it to use sum() instead. If you need to use max(usage) there, pick a more appropriate name for that column.


Delete with join and condition (3 tables) - MySQL

Able to find the answer in another post... Sorry for raising this as duplicate

DELETE `stn` FROM `snote` AS `stn`
LEFT JOIN `bnote` AS `ban` ON `ban`.`bnote_id`=`stn`.`bnote_id`
WHERE `ban`.`note_id`='5' AND `stn`.`bnote_id` = '10'


Use another job to check periodically if your job is running, at which step it is running, and what the value for your timeout is. You can do this by running a stored procedure in your job. I wrote the stored procedure for you, if you have any question just comment :)

-- =============================================
-- Author:      <Wynants Stijn>
-- Create date: <30/03/2016>
-- Description: <GetHangingJob and Restart at step>
-- =============================================
CREATE PROCEDURE dbo.usp_GetHangingJob
    -- Add the parameters for the stored procedure here
    @JobName varchar(1000),
    @TimeOut int   ---In minutes
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @JobState varchar(20)
    DECLARE @JobStep int
    DECLARE @JobStepName varchar(1000)
    SET @JobState = (select top 1
        case when
        case when last_executed_step_date is null then a.run_requested_date else last_executed_step_date end > DATEADD(mi,-@TimeOut,getdate()) THEN 'OK' else 'NOT OK' end as JobState
    from
        msdb.dbo.sysjobs_view j
    inner join
        msdb.dbo.sysjobactivity a
    on
        j.job_id = a.job_id
    where
        a.run_requested_date is not null
    and
        a.stop_execution_date is null
    and
        j.name = @JobName)

    SET @JobStep = (select case when last_executed_step_id is null then 1 else last_executed_step_id + 1 end as CurrentJobStep
    FROM
            msdb.dbo.sysjobs_view j
    inner join
        msdb.dbo.sysjobactivity a
    on
        j.job_id = a.job_id
    where
        a.run_requested_date is not null
    and
        a.stop_execution_date is null
    and
        j.name = @JobName)

    SET @JobStepName = (select top 1 s.step_name from msdb.dbo.sysjobs j inner join msdb.dbo.sysjobsteps s on j.job_id = s.job_id and s.step_id = @JobStep and j.name = @JobName)
    IF @JobState = 'NOT OK'
    BEGIN
        exec msdb.dbo.sp_stop_job @JobName
        WAITFOR DELAY '00:00:02'
        exec msdb.dbo.sp_start_job @JobName,NULL,NULL,NULL,@JobStepName,NULL
    END

END
GO

Enjoy!


First of all: it would be more efficient to store 4 bits of information as separate boolean columns - in every respect: easier to handle, easier to understand and change, easier to index, even smaller storage size! Closely related answer:

Does it make sense to store a couple of Boolean values as array?

To answer your question asked: you can use a partial index like:

CREATE INDEX foo ON tbl (tbl_id)
WHERE bitfield & '0010' = '0010';

& being the bitwise AND operator.
tbl_id being the PK column. (The actual index column hardly matters here.)
In combination with a matching query:

SELECT *
FROM   tbl
WHERE  bitfield & '0010' = '0010';

But your question probably does not reflect your complete situation. There may be better options.


The problem here is that if for example 7 out of 8 database backups
  fail in a job, that job is flagged up as 'failed', but the report will
  show that the last backup time was in that batch of failed backups,
  which it was, as it is selecting the one backup of 8 which succeeded.

To my understanding you want the job to report on the latest full backups on an instance when any of these are not meeting your thresholds.

In that case you could opt to get the the latest FULL backup of all databases, and then get the minimum value of that resultset.

;WITH CTE AS
(
SELECT
sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT  @@SERVERNAME as serverName,
        MIN(LastBackUpTime) as LastBackUpTime
FROM CTE;

If you also need the database name of the database with the oldest successful full backup

;WITH CTE AS
(
SELECT
sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT TOP(1)  @@SERVERNAME as serverName,
        LastBackUpTime as LastBackUpTime,
        DatabaseName
FROM CTE
ORDER BY LastBackUpTime ASC;

I would not use job execution stats, as they can return false positives.

Next to checking if the backup jobs did their job correctly, you should also validate your backups by restoring them somewhere.


How do I select the next row in SELECT FOR UPDATE?

I think you are using the wrong model of concurrency to solve your problem- SELECT... FOR UPDATE (or modifying the transaction isolation level) is used for those cases where you want to manage a row in isolation of other transactions (concurrency), creating a queue, not to coordinate executing different queries/jobs at the same time (parallelism).

The reason for this is that when you run SELECT... FOR UPDATE (and then supposedly UPDATE the row, then finish your transaction), other concurrent queries over the same query do not fail immediately- they get "locked" until the first one finishes and then lock the row again. This would be a pattern needed, for example, for a counter. You could of course add a column "processed", that changes to 1 when it finishes, so the other queries do not run again (I suggested such design on this other answer: https://dba.stackexchange.com/a/98353/30545 )- but you are wasting precious time being locked for finally not doing anything.

They way parallel processing is handled is to generate a unique list of rows in a concurrent way (with a granularity larger than one, to avoid high concurrency; but not too large so there cannot be idle processors), and then process them without worrying about overlaps. If for any reason you need to process rows in perfect order, you will have to read them in a single thread, then send those to different job runners.

If for any reason you want to still use a "try to lock the row, then move on" model, you can use a very low innodb lock timeout and handle the error, or better, GET_LOCK() / RELEASE_LOCK() with the row id and a very low timeout; but I would highly discourage that for the reasons I just said.

Sorry to be a bit vague, but your questions does not show any code to work with.


Does AWS RDS support MEMORY storage engine?

This answer applies to AWS Aurora not straight MySQL RDS.

Just because SHOW ENGINES says the database has the MEMORY storage engine doesn't mean you can actually use it. We have found that in AWS Aurora the SHOW ENGINES; call will tell you that the MEMORY engine is supported. However, if you try to change the engine to MEMORY or create a non-temporary table with MEMORY engine you'll get an InnoDB engine, because Aurora only supports InnoDB. When you run the engine change you'll get a warning that the InnoDB engine has been used.

Here's what I got from AWS support:

  Aurora is designed to provide support for only InnoDB storage engine for regular tables. Engines other than InnoDB are only supported for session-level temporary tables. With default configuration, when you try to create a table using a storage engine other than InnoDB, Aurora will implicitly create the table as InnoDB. Session level temporary tables are not persistent and they will be dropped once the session is completed.


Since MySQL doesn't allow FULL OUTER JOIN this is a bit trickier, but doable taking the week as main key for the left joins.

select distinct s.week, user1, user2, user3 from scorecards s left outer join
(select week, score as user1 from scorecards where user = 1) a
on s.week = a.week left join
(select week, score as user2 from scorecards where user = 2) b
on s.week = b.week left join
(select week, score as user3 from scorecards where user = 3) c
on s.week = c.week
;


SQLPackage resets database recovery model

You're right, as far as I know, that there's nothing in the sqlpackage publish options that would change the recovery model.  That property is set in the dacpac.  If you have access to the source code of the dacpac - the SQL Server Data Tools (SSDT, .sqlproj) project -  make sure that the "Recovery Model" setting is configured to "Simple" rather than "Full."

You can do that in Visual Studio by right-clicking on the project, going to the "Project Settings" tab, and clicking on "Database Settings."  This dialog should pop up:

If you don't have access to the source code, you can unzip the .dacpac file (it's just a compressed folder), and look in "model.xml" which should have the following for simple:

That line (under DataSchemaModel -> Model):

<Property Name="RecoveryMode" Value="1" />

...is omitted if the recovery model is Full.  It's set to "2" for bulk-logged.


Unicode files should have BOM, is the recommended and accepted way, specially for LE:

  In UTF-16, a BOM (U+FEFF) may be placed as the first character of a
  file or character stream to indicate the endianness (byte order) of
  all the 16-bit code units of the file or stream. If the 16-bit units
  are represented in big-endian byte order, this BOM character will
  appear in the sequence of bytes as 0xFE followed by 0xFF. This
  sequence appears as the ISO-8859-1 characters þÿ in a text display
  that expects the text to be ISO-8859-1.
  ...
  "The UTF-16 encoding scheme may or may not begin with a BOM. However, when there is no BOM, and in the absence of a higher-level protocol, the byte order of the UTF-16 encoding scheme is big-endian."

'linux' is a very generic term. You must be using some application for processing, and if the application is not recognizing the BOM it means is a bad application. Ditch it for something better.


If you are still using SQL*Plus variables

variable tmpString VARCHAR2(100);
begin
  myTestProc( :tmpString );
end;
/
print tmpString;

If you want to do it in PL/SQL

SET SERVEROUTPUT ON;
DECLARE
  tmpString VARCHAR2(100);
BEGIN
  myTestProc( tmpString );
  dbms_output.put_line( tmpString );
END;


Select N preceding rows when column match is true

WITH cte AS (
SELECT *,
       SUM(keyPerformanceIndicator) OVER (ORDER BY `date`, `time`
                                          ROWS BETWEEN 10 PRECEDING
                                                   AND CURRENT ROW) is_true_within_10
FROM datatable
)
SELECT * FROM cte WHERE is_true_within_10

If BigQuery does not support implicit convertions then use SUM(CASE keyPerformanceIndicator WHEN TRUE THEN 1 ELSE 0 END) and WHERE is_true_within_10 > 0.


need help building a query to detect duplicates on multiple columns

You can accomplish this with grouping on your store_id and value in a CTE or subquery to get the dupes and then re-joining the dupes to your  catalog_product_entity_varchar table like so:
WITH CTE_Dupes AS
(
     SELECT store_id, value
     FROM catalog_product_entity_varchar
     WHERE attribute_id = 86
     GROUP BY store_id, value
     HAVING COUNT(1) > 1
)

SELECT C.entity_id, C.store_id, C.value
FROM catalog_product_entity_varchar C
INNER JOIN CTE_Dupes D
    ON C.store_id = D.store_id
    AND C.value = D.value
WHERE C.attribute_id = 86


reject queries based on specific client_app_name and nt_username

Yes, you can use a Logon Trigger and call ROLLBACK if you want to deny the logon.

Alternatively if you are on Enterprise Edition you could use these in a resource governor classifier function to route them to a workload group in a resource pool with limited resources.

Note the app_name() is potentially easily settable from the client if they are making adhoc connections, e.g. via SSMS, or can edit the connection string used by your app.


Using TDE and SQL Server 2016 AlwaysOn Availability Groups

From your experience, is it good idea to use TDE in our situation?

No idea, you didn't tell us the attack vectors you are looking to secure. In the rest of my answers, I'll assume the only attack vector is on disk at rest.

  Is it worth?

If you're already running Enterprise Edition (which is required) then there is no direct extra cost. There will obviously admin cost for it in terms of your working hours.

  How well TDE compatible with AlwaysOn AGs?

It's 100% compatible.

  We use EMC SAN for storage and it provides the data encryption at rest on hardware level.

What happens when the backups, data files, etc., are moved off the SAN? What about backups in transit over network?

  I read, for example, you can't use AG GUI to do anything.

In older versions of SSMS, that's correct. This has been fixed for a while.

  Everything has to be done through scripts.

I don't see why that's a bad thing, though.


How to make date intervals from a list of date

I don't have a SQL Server 2005 instance to test against, but this works under SQL Server 2008.  It uses a couple of Common Table Expressions and a Windowing Function

--demo setup
DECLARE @TestData TABLE (
    ColA varchar(10)
    ,ColB datetime
    ,ColC char(1)
    )

insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-02','T');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-03' ,'T');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-04' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-06' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-07' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-01-08' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-02-07' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-02-08' ,'G');
insert into @TestData (ColA,ColB,ColC) values ('Cat','2019-02-09' ,'T');
insert into @TestData (ColA,ColB,ColC) values ('Dog','2019-03-09' ,'T');
;
--solution
WITH mycte1
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY ColA,ColC ORDER BY [ColB]
                ), [ColB]) AS grp
    FROM @TestData
    )
,mycte2 as
(
SELECT ColA
    ,min([ColB]) AS [Start]
    ,max([ColB]) AS [End]
    ,ColC
FROM mycte1
GROUP BY ColA,ColC
    ,grp
)
select * from mycte2
ORDER BY ColA, Start

| ColA | Start      | End        | ColC |
|------|------------|------------|------|
| Cat  | 2019-01-02 | 2019-01-03 | T    |
| Cat  | 2019-01-04 | 2019-01-04 | G    |
| Cat  | 2019-01-06 | 2019-01-08 | G    |
| Cat  | 2019-02-07 | 2019-02-08 | G    |
| Cat  | 2019-02-09 | 2019-02-09 | T    |
| Dog  | 2019-03-09 | 2019-03-09 | T    |


Why does my query run faster after I force full table scan?

[...] it goes against the general concept that full table scans are bad.

This has exceptions, like all general concepts. A full table scan can be less expensive than an index scan followed by table access by rowid - sometimes much less expensive.

Remember that:

Index blocks need to be read too, in addition to the table's blocks. If the index is large (a sizable percentage of the table's size), the pressure on the SGA (and associates latches) might be noticeable
If the sort order of the index doesn't match the way the data is stored in the actual table, the number of logical I/Os necessary to fulfill the query could be (potentially much) more than the number of LIOs to do the full table scan. (The index's clustering factor is one of the indicators you can look at to estimate this.)

I find the figures in this article Oracle clustering_factor tips explain the issue pretty well.

Essentially, if scanning via your index makes the data block I/O requests jump all over your table, the overall cost is going to be higher than if you could do large sequential reads. An FTS is more likely to use multi-block direct-path reads, bypassing the SGA entirely, which is also potentially good - no "cache thrashing", less latching.

If you have a covering index, chances are that's going to beat a full table scan all the time. If not, it's going to depend on what percentage of actual blocks (data + index) will need to be processed (the index's selectivity for that query), and how well they're "physically sorted" with respect to each-other.

As to why the optimizer is picking the "wrong" path for you here: hard to tell. Stale statistics on the index or table could be an issue like always, the estimate calculated base on the LIKE might be off for certain patterns, instance parameters could favor indexes a bit too much, ... If this is the only query that's misbehaving, and your stats are up to date, using a /*+ full */ hint doesn't sound too bad.


After the good work done by Aaron, I found that executing the result from my ReloadJobSchedules column below did the trick. For some reason I couldn't get the schedule to refresh to the correct next run date by manually updating msdb.dbo.sys.sysjobschedules.next_run_date .

SELECT  j.name AS JobName
        ,sch.name AS ScheduleName
        ,'EXECUTE msdb..sp_update_jobschedule @job_name = ''' + j.name + ''', @name = ''' + sch.name + '''' AS ReloadJobSchedules
FROM    msdb.dbo.sysjobschedules s
INNER JOIN
        msdb.dbo.sysschedules sch
        ON  s.schedule_id = sch.schedule_id
INNER JOIN
        msdb.dbo.sysjobs j
        ON s.job_id = j.job_id
WHERE   sch.[enabled] = 1
ORDER BY
        j.name


Suppress database change message in SQLCMD.exe

You can suppress (error) messages with a severity level lower than 10. Add -m10 as  startup option.

to the best of my knowledge, there is no direct way for SQLCMD to suppress that particular message. But perhaps suppressing info-type messages (as per above) will cut it?

Or, of course, use the -d switch as suggested by Dudi.


Pretty good overall, only a few minor changes I would suggest:

quiz_id is redundant in the wp_quiz_participant_answers table, you
can get it via JOINs to either the wp_quiz_questions or
wp_quiz_participants tables
If you have any plans to allow something resembling a login or ability to keep taking quizzes as the same user you should probably break Users into their own table that wp_quiz_participants references. Kinda looks like you might be thinking along those lines with the quiz_participant_userid?

Performance is going to be based around your queries, pay close attention to the performance and add indexing when necessary.


How do I modify a stored procedure in IBM DB2?

I believe you can do so through IBM Data Studio. I would use that tool anyway as that is now the approved IBM tool for interacting with DB2 and Control Center is deprecated.


How to use JOIN and SUM function to Calculate the total of query returned column?

You can use grouping set also

declare @JobRequiredProducts table (jobId int, productId int);
declare @Product table(productId int, productUnitPrice decimal(8,2))

insert into @JobRequiredProducts(jobId,productId)
values(1,4),(1,5),(1,6);

insert into @Product(productId, productUnitPrice)
values(4,175.99),(5 ,100.00),( 6 , 125.00)

SELECT  j.jobId,
        j.productId,
        SUM(p.productUnitPrice) AS 'Total'
FROM    @JobRequiredProducts J
JOIN    @Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
GROUP BY  ROLLUP ((j.jobId, j.productId));

with grouping sets

SELECT  j.jobId,
        j.productId,
        SUM(p.productUnitPrice) AS 'Total'
FROM    @JobRequiredProducts J
JOIN    @Product p
  ON    p.productId = j.productId
WHERE   j.jobId = 1
GROUP BY  GROUPING SETS( (j.jobId, j.productId),());

output:

jobId   productId   Total
1       4           175.99
1       5           100.00
1       6           125.00
NULL    NULL        400.99

dbfiddle here


Query Performance | Update Statistics | Ola Hallengren SPs

Depending on your version of SQL Server, maintenance plans reorganize or rebuild all of the indexes (I believe that starting w/ 2016 you can set thresholds to only process indexes with a certain level of fragmentation).

So, with Ola's scripts, you've got indexes that aren't badly fragmented enough to be rebuilt. While Ola's scripts allow you to update all statistics (depending the parameters you provide), the job may be finishing up faster than it did in the past. Therefore, some other job that makes many changes may not be done at the point when the statistics are being updated, causing enough changes to throw off the validity of the statistics.

(Aside - you noted that you were using @OnlyModifiedStatistics = 'Y'; you might see what happens if you change that to 'N', just as an experiment.)

Alternately, if (for example) you've limited the set of indexes to be considered, Ola's script will put the same limits on the statistics to be updated.

You can set up a job to update statistics using either T-SQL or maintenance plans. That could be run more often than the rebuild job.

UPDATE: I had originally presumed that Ola's script would only update statistics for indexes it had rebuilt/reorganized; it does not look like that's the case. Pulled in info I'd added to the comments.

The suggestion remains the same: have a separate job to update statistics, and run that during the week (daily, if you want to and the job doesn't take too long), and complaints should go down.


Procedure to wholly migrate SQL Server 2005 to a new server

Do not use copy database wizard. Instead use backup/restore. Also, if you want to minimize the downtime then below steps would be sufficient :
Prepwork on the New server :

Create blank databases with the same name as the old server. This is because if any logins or jobs has that database as default, there would be errors.

Script out all the logins using the sp_help_revlogin from the old server to the new server and create them on the new server.

Script out all the jobs. You dont have to Script > CREATE To for each jobs. Instead right click in Object Explorer and script all the jobs in one shot :-) Remember to disable the jobs until you failover to the new server.

Scrip out all Linked servers and recreate them on the destination server. Remember that passwords will not be scripted out.

Once 1-4 is set up then set up either Logshipping or database mirroring (Async mode) from source server to destination server.

On the day of failover

if using logshipping then manually failover to new server.

if using Mirroring then change the mode to synchronous and then initiate a failover to new server.

This way you will do all the prep work before hand and then during the day of failover, the downtime will be minimal.
You can refer to my answer HERE for more steps (they are for migration from old version to higher version, but still will help you).


How can you close or flush a binlog file on a remote server

Flushing the log on the master solves your problem pretty much by accident.  When the master log file rotates, mysqlbinlog closes the current file and opens the next one, which implicitly flushes any buffered data.

You can't trigger mysqlbinlog to flush its log on demand the way you can an actual server, but I've found a way to trigger it to maintain the file in an always flushed state, which seems to be a viable way to improve your results.

When Oracle added the --raw option to mysqlbinlog, which, in conjunction with --read-from-remote-server and --stop-never can be used to capture a real-time backup of the master's binlogs, they overlooked at least a couple of seemingly-obvious things.

In light of the documented purpose of the feature...

  The --raw option can be used to make a backup of a server's binary log. With the --stop-never option, the backup is “live” because mysqlbinlog stays connected to the server.

  https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_raw

...it seems like a distinct oversight that mysqlbinlog does not bother to flush its output stream after each write.

Maybe that's why they put “live” in quotes. :)

My testing confirms that mysqlbinlog, as shipped, leaves dangling data in its buffer that could have been flushed, but this seems like a fairly straightforward problem to fix.

For example, this little change in mysql-5.6.27/client/mysqlbinlog.cc at line 2477:

2477c2477
<         if (my_fwrite(result_file, net->read_pos + 1 , len - 1, MYF(MY_NABP)))
---
>         if (my_fwrite(result_file, net->read_pos + 1 , len - 1, MYF(MY_NABP)) || fflush(result_file))

This change results in the size of the file created by mysqlbinlog continually matching the size returned by SHOW MASTER STATUS;, rather than always seeming to be a few bytes behind.  I've always known of this slight lag, but never looked into it in depth.  Of course, continually flushing the buffer is going to come with a cost, but this seems like a non-negotiable necessity for "backup" purposes.  Note that this is only a flush of the user space buffers, it's not an fsync() or similar, so I don't think it's going to be that different in performance terms... and in any event, it's not going to impact the master.

The logical || is used because my_fwrite() returns 0 on success, so we short-circuit and don't try to flush if that fails, and we error out if either operation fails, since fflush() also returns 0 on success.

This change should significantly improve the behavior you see.

Of course, it's still always possible for you to end up with an incomplete log, since it's still an open file and still being written to... but when testing this fix in a low traffic environment, hot-copying the file results in a usable binlog for me -- while, otherwise, in the same environment, the file end is almost always incomplete when using the stock binary.

For different versions (of 5.6+ only -- this feature wasn't implemented at all before 5.6) the line in the source may be different but it should still be pretty easy to spot, and not far from the line numbers indicated here.

Facebook's fork of MySQL 5.6 features a similar but more sophisticated fix, and a new option --flush-result-file that causes the flushing to be a little less aggressive, but configurable.  However, I found it unnecessarily difficult to compile in its current form, so I developed the little patch above.

As long as you're patching mysqlbinlog, you might find another small tweak useful for solving another design oversight.  The mysqlbinlog utility in the official distribution lacks support for the --compress command line option (although Percona had the good sense to add it to theirs.)

But, it's also easy enough to enable compression.  I just came up with the fix above for this question, but I've been using the fix, below, for quite some time now, since -- again -- it has always seemed to me like an obviously missing feature.

From the 5.6.27 source, it looks like this:

1948c1948
<   if (!mysql_real_connect(mysql, host, user, pass, 0, port, sock, 0))
---
>   if (!mysql_real_connect(mysql, host, user, pass, 0, port, sock, CLIENT_COMPRESS))

This change doesn't enable support for passing the --compress command line option -- it just turns on the client compression protocol on the connection to the master, for any master that supports it (which they essentially all do), thereby reducing the amount of network bandwidth needed to transport the logs -- sometimes by a factor of 10:1 or higher, depending on the specific payload.  It does not change the contents of the generated files -- it just enables compression of the data traversing the network.  On the wire, this has the same effect from the master's perspective as enabling slave_compressed_protocol on the slave server.  (The documentation is ambiguous, but this setting has no effect on the master unless the master is also a slave of another server.)


Based on the comments, this is one possible solution:

select a.NS, Sum(a.MPNS) from
(select ns, MPNS from match_points
union all
select ew, MPEW from match_points) a
group by a.ns

which returns the required results.

SQLFiddle version of the data here - http://www.sqlfiddle.com/#!2/7e473/11


Foreign key constraints with MySQL/InnoDB

"integers are cheaper to compare than characters, because character sets and collations (sorting rules) make character comparisons complicated. Integers are very good when using as an index column." - HottestGuide


How do I view a Microsoft SQL Server Compact Database

After OP's Edit to the question :
Try to use Visual Studio 2010 SP1 + VS Tools for SQL CE 4
-- or --
SQL Server Management Studio 2008 R2 supports for SQL Server Compact 3.5 database. (ATTENTION from SSMS version 2012 and newer, Sql Server Compact databases are no longer supported by SSMS; You must use SSMS 2008 R2 or OLDER)
Note : SQL Server Management Studio cannot open SQL Server Compact 4 files, only SQL Server Compact 3.5 files.
Click View >>Toolbars >>SQL Server Compact Edition Editor as shown.

Additionally, refer to SQL Compact 3rd party tools and
SQL Server Compact Toolbox on Codeplex - It is a Visual Studio add-in and standalone app , for managing all aspects of your SQL Server Compact database files.


Multiple like conditions for same column?


I guess you want something like this:

SELECT value, varname
FROM ja_settings
WHERE varname IN ('clickatel_api_id', 'clickatel_user','clickatel_pass');


Non-programmer using Sequel Ace, how to resolve "Out of sort memory, consider increasing server sort buffer size" error?

see https://forums.mysql.com/read.php?11,366143,376017#msg-376017
how to create a my.cnf file and add
[mysqld]
sort_buffer_size = 1M

And then increase it when it still makes troubles


Ola Hallengren Parameters for Index Rebuild/Reorganize on SharePoint

I'd go with just updating stats.

I think Jeff Moden once said that "Rebuilding indexes is just an incredibly expensive way to update your stats".

Also, running UPDATE STATISTICS gets you both index and columns stats updates.  Rebuilding indexes only does the index itself so the column stats are stale unless they hit the "20% data change + 500 records" criteria to trigger the auto-update.  On large tables, that may be a while and not frequent enough.

SQL2014 and lower - Default sample size will not go parallel, only FULLSCAN will

SQL2016 - Default sample size can also go parallel
(Parallel Statistics Update)

The syntax I usually use (In this case we do FULLSCAN [StatisticsSample=100]):

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@StatisticsSample=100

This way, you get all the benefit of updated statistics (which the optimizer will thank you for) without all the I/O thrashing and logging of physical index structure maintenance.

Additional reading on TF2371, which allows the stats update on large tables to be triggered at lower percentages.  If you're on SQL 2016, this is enabled by default.

https://support.microsoft.com/en-us/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server


AWS MySQL RDS instance becomes unresponsive and getting restarted automatically

We were able to resolve this issue by upgrading the instances to 5.6.34.


It depends on what you are going to do with the data, as it always does.  It's usually dangerous to assume that the data is never going to be used in some other way.  Your question illustrates this nicely.  The original design of "summaries" assumed that weekly summarization would always be the intended use, and now you want daily summarization.

The structure of the summaries table (or view) is what can be called crosstabulation.  Crosstabulation is frequently used in spreadsheets as a convenient way of displaying data that has been summarized along two dimensions.  See the pivot table feature for more details.

Crosstabulated data is not in first normal form.  The folks who invented first normal form were thinking about something when they invented it.  They were thinking about keyed access to all data.  The power of keyed access to all data is central to the simplicity and power of the relational data model. You can look up more detail under first normal form.

Based on this, I would say that one row per line item is the best structure for this data, assuming that no finer granularity can be obtained.  If you want to summarize by day, or by week, or by month or whatever, you can summarize by using a pivot operation in a view.

This will take some computer resources, to be sure.  Computer resources are usually cheap, compared to "we can't do what you want because the data isn't organized that way".

As far as existing code goes, just create views that make the data look the way  it needs to look for the existing code, and tweak the existing code to use the view.  Unless the functionality of the existing code needs to be changed as well.

Edit based on comments:

You probably know more about cross tabulation than you think you do.  Look at the diagram you provided at the top of your question.  That's a cross tabulation.  It tabulates by product down the left side, and by week number across the top.  The cells contain some kind of aggregate statistic, like the total count of bath sheets sold in week 2.

It's probably a summary of invoice data, or something like that.


I have run into this** before, and if I recall correctly, to ensure always getting results with multi-server queries you need to force an empty result set when no rows would otherwise be returned. Meaning, you need an ELSE branch on that IF and within the ELSE you would do something like the following:

SELECT CONVERT(DATETIME, NULL) AS [Col1name],
       CONVERT(DECIMAL(12, 5), NULL) AS [Col2name],
       ...{additional fields}...
WHERE  1 = 0;

This produces an empty result set that has the proper names and datatypes.

OR, and I have not tried this in the past (just thought of it as I was typing this), but you might be able to get away with simply pausing in that ELSE branch such that the primary / intended server is allowed to always return its result set first (which is the real issue here: the first server to respond defines the structure that all other responses must adhere to). Hence the following might work as the only thing in the ELSE:

WAITFOR DELAY '00:00:10'; -- 10 seconds (just needs to be longer than the real query takes)

But I don't remember if having other servers return no results at all caused an error message to be displayed in the "Messages" tab. If that does happen, then the empty result set is definitely the way to go. But if this does work, then this might work better in a general template (like your case seems to be) as it wouldn't require adjusting the forced, empty result set each time it is used.

UPDATE:

The O.P. verified that:

the WAITFOR DELAY did indeed work, and
the replicas did report the error message, but it did not present a problem for the O.P.'s usage

** The situation I ran into was similar, but had nothing to do with either Availability Groups or wanting results from only one server. Our situation was that we had 18 servers of same schema different data and needed to do various maintenance tasks, aggregations across all 18 nodes. There were some stored procedures that, for whatever reason, occasionally didn't return any result set, and whatever that reason was it couldn't be fixed within the stored procedure. So, depending on which node returned first, most of the time everything was fine, but every once in a while the node that sometimes returned no result set returned first. So, I had to do something like dump the results into a temp table and if @@ROWCOUNT of that INSERT...EXEC was 0, then I would select the forced, empty result set.


How to configure mongodb replicaset of 3 nodes in authentication mode

You made things in wrong order.
At start you have "open" replica set. You put authorization on and you have so called "localhost exception" what means that you (still) can connect primary directly without authorization. On that connection you MUST first create "root" (super user) user

use admin
db.createUser(
{
    user: "root",
    pwd: "toor",
    roles: [ "root" ]
})

Now, your "localhost exception" is gone! You must authenticate your self. You can logout and then login again OR just give command

db.auth("root","toor")

And start creating other (normal) users.


How can I determine why a concurrent Postgres index was marked as INVALID?

One simple answer to this is to drop it, run it again, keep the terminal window around (or otherwise log the output) and see what error happens.


My query fails to segment the results as intended. What am I doing wrong?

It's possible to convert the CASE options into rows of a temp table, a JOIN with a less or equal condition to the data table will get the rows.

SELECT CONCAT('Greater than or equal to ',a.val,':', count(e.val))
FROM   (Select 1000 val UNION ALL
        Select 500      UNION ALL
        Select 400      UNION ALL
        Select 300      UNION ALL
        Select 200      UNION ALL
        Select 100      UNION ALL
        Select 10       UNION ALL
        Select 5        UNION ALL
        Select 2        UNION ALL
        Select 1       ) a
       LEFT JOIN e ON e.val >= a.val
GROUP BY a.val
ORDER BY a.val  DESC

SQLFiddle demo

Another way doesn't even need the temp table, but will get the results as columns instead of as rows, if you care about the results and not about the format

SELECT CONCAT('Greater than or equal to 1000: '
             , SUM(Case When val >= 1000 Then 1 Else 0 End)) _1000
     , CONCAT('Greater than or equal to  500: '
            , SUM(Case When val >= 500 Then 1 Else 0 End)) _500
     , CONCAT('Greater than or equal to  400: '
            , SUM(Case When val >= 400 Then 1 Else 0 End)) _400
     , CONCAT('Greater than or equal to  300: '
            , SUM(Case When val >= 300 Then 1 Else 0 End)) _300
     , CONCAT('Greater than or equal to  200: '
            , SUM(Case When val >= 200 Then 1 Else 0 End)) _200
     , CONCAT('Greater than or equal to  100: '
            , SUM(Case When val >= 100 Then 1 Else 0 End)) _100
     , CONCAT('Greater than or equal to   10: '
            , SUM(Case When val >= 10 Then 1 Else 0 End)) _10
     , CONCAT('Greater than or equal to    5: '
            , SUM(Case When val >= 5 Then 1 Else 0 End)) _5
     , CONCAT('Greater than or equal to    2: '
            , SUM(Case When val >= 2 Then 1 Else 0 End)) _2
     , CONCAT('Greater than or equal to    1: '
            , SUM(Case When val >= 1 Then 1 Else 0 End)) _1
FROM   e

SQLFiddle demo


How to change initial extent of an existing partition

Even if its an empty partition, MOVE is the way to do it:

create table t1 (c1 number, c2 varchar2(100))
segment creation immediate
partition by range (c1)
(
  partition p1 values less than (10),
  partition p2 values less than (20)
);

SQL> select partition_name, initial_extent, next_extent
     from user_tab_partitions where table_name = 'T1';

PARTITION_NAME       INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1                          8388608     1048576
P2                          8388608     1048576

Default 8M/1M. Now move the partition:

SQL> alter table t1 move partition p1 storage (initial 65536 next 65536);

Table altered.

SQL> select partition_name, initial_extent, next_extent from
     user_tab_partitions where table_name = 'T1';

PARTITION_NAME       INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1                            65536       65536
P2                          8388608     1048576

Modifying default attributes will have its effect only on partitions created after setting it:

SQL> alter table t1 modify default attributes storage ( initial 1M next 1M );

Table altered.

SQL> alter table t1 add partition p3 values less than (30);

Table altered.

SQL> select partition_name, initial_extent, next_extent from
     user_tab_partitions where table_name = 'T1';

PARTITION_NAME       INITIAL_EXTENT NEXT_EXTENT
-------------------- -------------- -----------
P1                            65536       65536
P2                          8388608     1048576
P3                          1048576     1048576


Is there a performance hit by breaking out tables into different schemas in SQL Server?

No there is no performance hit if the tables are in two different schemas.


As Aaron mentioned, for some reason you need to terminate each statement in Fiddle.

Anyway, for the query, you can use CROSS APPLY to expand the result set. This method scans the base table only once, and for each row applies the subquery, which in this case actually selects two rows:

INSERT INTO userContact(userId, contactType, contactInfo)
  SELECT
    ui.userId,
    c.contactType,
    c.contactInfo
    FROM usersToImport ui
    CROSS APPLY
    (
      SELECT 'email' AS contactType, ui.email AS contactInfo UNION ALL
      SELECT 'telephone', ui.telephone
    ) c;


Why does Cassandra recommend against creating an index on high-cardinality columns?

With a Cassandra index (i.e. a "secondary index", as opposed to primary keys), each node has to query its own local data for responding to a query (see the Cassandra secondary indexexes FAQ).  These index are also built using a background process.  This backgrounding means that the index may return false negatives in terms of hits (or false positives in terms of misses).

This means that in a high-cardinality column, the rate of change (i.e. additions/deletions) from that column can be quite high.  And thus if that rate of change is faster than the updating of the index via the background process, then using an index is "inefficient" (the index is performing more work than is needed by the application, which might often get the wrong answer).

A more efficient approach, in terms of query accuracy, might be to maintain a second table, rather than a secondary index.  Tables, as opposed to indexes, are treated just like any other table.  They are more likely to give your application the query results it expects.  The downside are that maintaining a table as an index, versus a Cassandra "secondary index", are now application constraints (i.e. your application code now has to know to insert/delete rows from that "index" table, and to keep the two tables in sync via application-level "reconciliation").

Hope this helps!


tables are unsorted by nature
so also the joined tables are unsorted
order them if you need it like you want.
So the columns are more complicated and more nested they become more complicated it gets.
Here are all possibilities  with two tables
the result differs, if you use ON Clause or the USING clause
In the USING clause, only RIGHT JOIN gives you another result

CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);

SELECT * FROM t1 NATURAL JOIN t2;

 j |  i |  k
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 INNER JOIN t2 USING (j);

 j |  i |  k
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 JOIN t2 USING (j);

 j |  i |  k
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 LEFT JOIN t2 USING (j);

 j |  i |  k
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 RiGHT JOIN t2 USING (j);

 j |  k |  i
-: | -: | -:
 1 |  1 |  1

SELECT * FROM t1 LEFT JOIN t2 ON (t1.j = t2.j);

 i |  j |  k |  j
-: | -: | -: | -:
 1 |  1 |  1 |  1

SELECT * FROM t1 RIGHT JOIN t2 ON (t1.j = t2.j);

 i |  j |  k |  j
-: | -: | -: | -:
 1 |  1 |  1 |  1

SELECT * FROM t1 INNER JOIN t2 ON (t1.j = t2.j);

 i |  j |  k |  j
-: | -: | -: | -:
 1 |  1 |  1 |  1

db<>fiddle here


Error creating package, Failed to save package file, error 0x80040154 "Class not registered"

I've uninstalled the old version of VS2017 SSDT (15.4.xx) and download the latest SSDT 2017 (15.5.x) from Microsoft then, install it.
It seems to be working now. I can create a new SSIS package. Yehey!


Update a table with data from another table: truncated incorrect double value mysql

This should do it.  Update your table inner join the second table based on ids.
Then we set the email from one table to the other where the ids are the same.  Hope it helps.

UPDATE tbl1
INNER JOIN tbl2 ON tbl2.user_login = tbl1.user_id
SET tbl1.contact_email = tbl2.user_email ;


How can I generate fake data that adheres to the PRIMARY KEY-FOREIGN KEY constraints of my schema?

Basically, chain data-modifying CTEs in PostgreSQL for the purpose. Details very much depend on the undisclosed details of your setup and specific requirements. The meaning of "random" varies wildly.

Minimal demo:

CREATE TABLE fk_tbl(fk_id serial PRIMARY KEY, data int);
CREATE TABLE pk_tbl(pk_id serial PRIMARY KEY, fk_id int REFERENCES fk_tbl, data int);

WITH ins1 AS (
   INSERT INTO fk_tbl(data)
   SELECT trunc(random() * 10000)::int      -- or whatever
   FROM   generate_series(1, 10) g          -- or 1000000 instead of 10?
   RETURNING fk_id
   )
INSERT INTO pk_tbl(fk_id, data)
SELECT fk_id, trunc(random() * 55555)::int  -- or whatever
FROM   ins1
ORDER  BY random();                         -- optional

db<>fiddle here

Related:

Insert data in 3 tables at a time using Postgres
How can I generate a random bytea
Best way to select random rows PostgreSQL
Set random value from set


How to track specific item as part of a kit

Put the Box ID against the primary part, instead of the primary part serial number against the box. To see what's in the box you need to search the Primary Part table for the Box ID. If you move which box a part is in, you just update it once.

You could make a unique index on BoxID as well to ensure that there are never 2 primary parts in the same box.


MySQL - fastest way to ALTER TABLE for InnoDB

One sure way to speed up an ALTER TABLE is to remove unnecessary indexes

Here are the initial steps to load a new version of the table

CREATE TABLE s_relations_new LIKE s_relations;
#
# Drop Duplicate Indexes
#
ALTER TABLE s_relations_new
    DROP INDEX source_persona_index,
    DROP INDEX target_persona_index,
    DROP INDEX target_persona_relation_type_index
;

Please note the following:

I dropped source_persona_index because it is the first column in 4 other indexes

unique_target_persona
unique_target_object
source_and_target_object_index
source_target_persona_index

I dropped target_persona_index because it is the first column in 2 other indexes

target_persona_relation_type_index
target_persona_relation_type_message_id_index

I dropped target_persona_relation_type_index because the first 2 columns are also in target_persona_relation_type_message_id_index

OK That takes care of unnecessary indexes. Are there any indexes that have low cardinality? Here is the way to determine that:

Run the following queries:

SELECT COUNT(DISTINCT sent_at)               FROM s_relations;
SELECT COUNT(DISTINCT message_id)            FROM s_relations;
SELECT COUNT(DISTINCT target_object_id)      FROM s_relations;

According to your question, there are about 80,000,000 rows. As a rule of thumb, the MySQL Query Optimizer will not use an index if the cardinality of the selected columns is greater that 5% of the table row count. In this case, that would be 4,000,000.

If COUNT(DISTINCT sent_at) > 4,000,000

then ALTER TABLE s_relations_new
DROP INDEX sent_at_index;

If COUNT(DISTINCT message_id) > 4,000,000

then ALTER TABLE s_relations_new
DROP INDEX message_id_index;

If COUNT(DISTINCT target_object_id) > 4,000,000

then ALTER TABLE s_relations_new
DROP INDEX target_object_index;

Once the usefulness or uselessness of those indexes have been determined, you can reload the data

#
# Change the Column Name
# Load the Table
#
ALTER TABLE s_relations_new CHANGE sent_at sent_at_new int(11) DEFAULT NULL;
INSERT INTO s_relations_new SELECT * FROM s_relations;

That's it, right? NOPE !!!

If your website has been up this whole time, there may have INSERTs running against s_relations during the loading of s_relations_new. How can you retrieve those missing rows?

Go find the maximum id in s_relations_new and append everything after that ID from s_relations. To assure that the table is frozen and used only for this update, you must have a little downtime for the sake of getting those last rows that were inserted into s_relation_new. Here is what you do:

In the OS, restart mysql so that no one else can log in but root@localhost (disables TCP/IP):

$ service mysql restart --skip-networking

Next, login to mysql and load those last rows:

mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

Then, restart mysql normally

$ service mysql restart

Now, if you cannot take mysql down, you will have to do a bait-and-switch on s_relations. Just login to mysql and do the following:

mysql> ALTER TABLE s_relations RENAME s_relations_old;
mysql> SELECT MAX(id) INTO @maxidnew FROM s_relations_new;
mysql> INSERT INTO s_relations_new SELECT * FROM s_relations_old WHERE id > @maxidnew;
mysql> ALTER TABLE s_relations_new RENAME s_relations;

Give it a Try !!!

CAVEAT : Once you are satisfied with this operation, you can drop the old table at your earliest convenience:

mysql> DROP TABLE s_relations_old;


Using a Linked Server to import data from Excel

You can use the Import wizard to load your data.  The Import/Export wizard actually uses SSIS to do the work.  But if your goal is to avoid learning SSIS, then just do not save the package.

Simple way to import data into SQL Server

That MSSQLtips article walks you through setting up the import and then run the data into your SQL Server destination.

If you want to reuse the package (which is probably a good idea) there is a save option that allows you to save the SSIS package that was internally generated and use it again and again.

However, if the Linked Server approach is the way you want to go, there is a tutorial here:

Excel Import to SQL Server using Linked Servers

This is somewhat more complex to set up, but should work OK.

UPDATE: based on your comment about coordinating two transactions you might find it simpler to import the data into two staging tables.  (You would most likely truncate those tables before each load.) The table Stage01 receives one load and the table Stage02 receives the other load.

Then, now that both data sets are in their own tables, you can join between Stage01 and Stage02 data sets to insert the resolved data into your operational database tables. This would likely simplify the processing of the two data sets.


Using MySQL/PHP PDO, is there a 'simulate' or dry run option?

The best way to do SQL syntax checking without actually writing data is using the engine BLACKHOLE, which is the MySQL equivalent to UNIX /dev/null. A its name suggests, it is an engine that accepts all (but invalid syntax) but stores no data. Great for testing, and incredible inserting performance :-)

Aside from that, people test setups by creating slaves with live data on a separate instance.

Be careful with very dynamic data on InnoDB, and large uncommitted transactions, as that could create a lot of unused gaps on production depending on the configuration (innodb_file_per_table = 0, undo area, etc.) and also performance problems (rollbacks are very costly in InnoDB).


Shared connection pool in SQL Server and prevention of cross-database queries

How Connection Pooling works

The issue with connection pool fragmentation is that each connection pool is bound to the exact text of the Connection String, including the SID of the Windows Login when using a Trusted Connection / Integrated Security. The connection pool works because the connection is exactly the same in every way.

Hence, changing the database after connecting doesn't change anything from a security perspective. The advice to change the database after connecting (which can also be done via the SqlConnection.ChangeDatabase method), is to get around the fact that putting the "Initial Catalog" (or "Database") key/value pair into the Connection String means that those connections aren't using the exact same connection string.

With the above in mind, whether you are using Windows Logins (i.e. Trusted Connection / Integrated Security) or SQL Server Logins (specifying UserID & Password OR using a SqlCredential),

if the web app connects as the same Login for all requests, then as long as you don't specify the database in the ConnectionString (or at least always specify the same database), and only change the database context after making the connection, then you will get the most re-use out of connection pooling since all connections will be able to use the same pool. Else you will get a pool per Database.
if the web app connects with different Logins, then:

in the one-database-per-client model (assuming they are only allowed to connect to their database, hence using a different Login per client), you can either always specify the same database for that Login, or not specify any database in the ConnectionString and only change the database context after making the connection, and you will get a pool per Login (or per Database: they are kinda the same thing in this model).
in the multi-tenant model (assuming the app makes calls to multiple databases), as long as you don't specify the database in the ConnectionString (or at least always specify the same database), and only change the database context after making the connection, then you will get a pool per Login. Of course, if you specify the database in the ConnectionString, then you will get a pool per Login per Database!

What this all means in practical terms

No, you cannot have a single connection pool and "prevent the user from making cross-database queries". The very nature of a connection pool it is that it is the same connection because the connection is not closed. You cannot differentiate permissions when there is nothing to differentiate; the same Login is still logged in, just issuing another command.

Reducing the number of connection pools down to a single pool is at odds with the desire to have different permissions:

If you want to prevent a Login from accessing more than their database, or maybe confined to their database and any "shared" databases, that requires different Logins, and each one will have their own pool. If you go with Contained Databases, you still need to specify either the UserId & Password OR the Initial Catalog in the Connection String, and hence the result will still be one connection pool per each variation.
If you want to have just one connection pool, then you have no choice but to always use the same security credentials (regardless of them being based in Windows or SQL Server). And the same credentials means the same permissions.

The bottom line is, the wording of that MSDN article is a bit confusing / awkward, and you are over-estimating their advice. The issue of Connection Pool Fragmentation really shows up when:

you are changing both the security credentials AND the "Database" / "Initial Catalog" across connections
the app is multi-tenant (i.e. not one database per customer) such that each Login can connect to multiple Databases.

Having multiple Logins that connect to multiple Databases will give you more pools that are less frequently reused. Given that the unused connections linger for "approximately 4-8 minutes" (taken from the MSDN page you linked to, the "Removing Connections" section), this creates the inefficient situation of having too many idle connections.

But, having one Connection Pool per-Login OR per-Database is actually just fine.

Please note, however, that even with one Connection Pool per-Login OR per-Database, you can still experience too many idle connections if you have a lot of clients (e.g. Web Apps, back-end processes, etc.) hitting SQL Server from different servers.


Will auto_increment always grow in master-master setup?

Auto Increment ID is "replicating" to the slaves. Otherwise it would be hell of a job to promote a slave to a new master if the status of auto increment needs to synced. So to answer your question it would start from 8.

If you do not write actively to both master at the same time you don't need to do any auto_increment_* settings trickery. These settings are meant to avoid situation where the two master picks the same ID for new rows inserted concurrently on both masters which would lead to conflicting IDs while applying changes from the relaylog.


Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?

When you say merge table, I assume you mean MERGE (Mrg_MyISAM) storage engine.

Without seeing any table definitions, I can easily say this...

The smartest way would be to have each MyISAM table defined with a very good primary key/unique key where the lead column would be the date. At the very least, create an index that uniquely identifies data with only one partition (one underlying MyISAM table).

Each MyISAM table under the Merge definition would have such an index.

Any other index where the date is not the first column would result in bunches of table scans (NOT PRETTY !!!)

I have two posts I made back in January 2012 on MERGE table usage:

Jan 04, 2012 : get column from too many tables in mysql
Jan 15, 2012 : Separate tables or partition one huge table?

While the Mrg_MyISAM storage engine is still around, most would favor creating a Partitioned Table instead. Notwithstanding, the same rule applies:  create an index that uniquely identifies data with only one partition.


MSSQL Mixed-Mode Users cant connect unless they are in the server sysadmin role

Thanks for all your comments. I know this thread is pretty old, but I have finally resolved the issue.

The public server role had an endpoint called ConfigMgrEndpoint that had no permission to "Connect". I granted the connect privilege and things started to work for non-sa users.

Under Server Objects>Service Broker I find the ConfigMgrEndpoint.

I have to assume that at one point MS Configuration Manager was being implemented.

I'm not sure how to dis-integrate the service broker end point, and I can't really experiment because this database is in production. Our current plan is to simply deploy an instance of mssql 2012 on a fresh server install and port the database over.

Hopefully someone else gets some benefit from this, as there was never any mention about end points in the articles i read investigating this issue!

Thanks again!


Just use a trace template.  Click File, New Trace, and choose the Replay template.  That'll get you everything that happens.


Getting the database from synonym base object

This will work for all three-part names, and all four-part names where the server is the local instance.

SELECT
  name, base_object_name,
  DB_ID(PARSENAME(base_object_name,3)),
  DB_NAME(DB_ID(PARSENAME(base_object_name,3)))
FROM sys.synonyms;

If you get NULL it's either because the synonym is only 2-part (e.g. it references an object in the current database), or the database listed in the synonym no longer exists (it may have been renamed, dropped, etc).

If the four-part name references a remote server, you won't be able to use DB_ID()/DB_NAME(), you'd have to join to the remote server's master.sys.databases - which would involve first parsing out the linked server being used, then constructing some dynamic SQL. Just mentioning that; it doesn't seem to be an issue for your use case.

If you want to limit to 3-part names only, you can add these predicates:

WHERE PARSENAME(base_object_name,3) IS NOT NULL
AND PARSENAME(base_object_name,4) IS NULL;


Can I artificially limit the Columnstore cache size in SQL Server?

If you have an MSDN license you can download and install SQL Server Standard Edition for Dev/Test.

Otherwise you can use an Azure Pay-As-You-Go instance for testing.  See eg

SQL Server 2017 Standard on Windows Server 2016


Comparing two time fields in database with 2 given time fields

Actually, I think you're wrong - there are conflicts for CM-01 and CM-03, based on the query and the data shown.

Your data shows bookings for:

CM-01 on 2017-07-15 starting at 10:30 and ending at 11:00
CM-03 on 2017-07-15 starting at 10:35 and ending at 11:05

(No AM/PM shown, so assuming all times are military (00:00 to 23:59).

You're checking if resources are free between 11:00 and 12:00 on 2017-07-15.

So, CM-03 should trivially be unavailable, as there's a five minute period where the current booking overlaps with the desired (11 - 12) new booking.

Based on the intent of the rules you've laid out, I'd say CM-01 should also be unavailable. Your comparing the starts and ends of the two times inclusively. That would imply that, if something's booked for 10:30 to 11:00, and is wanted for 11:00 to 12:00, then it shouldn't be available, because the end of the existing booking is the same as the start of the new booking. If you want to allow back-to-back booking, you need to tweak the logic.

However, if we look at the query, I do think there is a problem. Let's say there was a booking for CM-02 on 2017-07-15 from 9:00 to 10:00 (with a NULL Status). That should clearly not prevent a new booking from 11:00 to 12:00 from being added.

However, when we look at your query to find IDs that should be ignored (reformatted for readability):

SELECT ResourceID
  FROM Bookings
 WHERE DATE(StartDateTime) = '2017-07-15'
   AND Status IS NULL
   AND (    (   TIME('11:00:00') >= TIME(StartDateTime)
             OR TIME('11:00:00') <= TIME(EndDateTime)
            )
        AND (   TIME('12:00:00') >= TIME(StartDateTime)
             OR TIME('12:00:00') <= TIME(EndDateTime)
            )
       )

and consider our 9-10 booking for CM-02, here's what happens:

DATE(StartDateTime) = '2017-07-15' - This is true.
Status IS NULL - So is this.
Checking the start time for the desired (11-12) booking against the existing (9-10) booking:

TIME('11:00:00') >= TIME(StartDateTime) - "11:00:00" is greater than "9:00:00", so true.
We can skip the second check, as this branch is already true.

Checking the end time for the desired (11-12) booking against the existing (9-10) booking:

TIME('12:00:00') >= TIME(StartDateTime) - "12:00:00" is greater than "9:00:00", so true.
We can skip the second check, as this branch is already true.

So, all is true - and we apparently have a conflict (except we shouldn't).

Feel free to confirm the above by adding such a record to your test data.

I've worked through this before, and there are two things you need to check:

Does time period A start before time period B ends?
Does time period A end after time period B starts?

If both are true, you have an issue.

Note - this does assume your start and end times are in the correct order. If either time period's start time comes before its end time, this won't work.

So, rewrite the query as:

SELECT ResourceID
  FROM Bookings
 WHERE Status IS NULL
   AND CAST('2017-07-15 11:00:00' as datetime) <= EndDateTime
   AND CAST('2017-07-15 12:00:00' as datetime) >= StartDateTime

Let's compare that to the booking I created, and to the bookings you had for the desired 11-12 booking:

CM-02, 2017-07-15 9:00 - 10:00

11:00 (desired) <= 10:00 (existing) = FALSE
12:00 (desired) >= 9:00 (existing) = TRUE
Result - FALSE, so OK to list for desired booking.

CM-03, 2017-07-15 10:35 - 11:05

11:00 (desired) <= 11:05 (existing) = TRUE
12:00 (desired) >= 10:35 (existing) = TRUE
Result - TRUE, so not available for desired booking.

CM-01, 2017-07-15 10:30 - 11:00

11:00 (desired) <= 11:00 (existing) = TRUE
12:00 (desired) >= 10:30 (existing) = TRUE
Result - TRUE, so not available for desired booking.

If you want to allow back-to-back bookings (like for CM-01, 10:30 - 11:00 and 11:00 - 12:00), then change the two comparisons to be < and >, not <= and >=

With that change:

CM-01, 2017-07-15 10:30 - 11:00

11:00 (desired) < 11:00 (existing) = FALSE
12:00 (desired) > 10:30 (existing) = TRUE
Result - FALSE, so OK to list for desired booking.

NOTE: Code is untested.


How to create a correct entity-relationship diagram (ERD) about a video game profits scenario

A question I have is what is the significance of the _EK/_ED columns? I can assume they are separate columns for each person in your party? If that is so, this design would have a lot of NULL values for whenever there isn't 4 people in a hunt, and will outright prevent accounting for a 5 person hunt. I hesitate to suggest a more flexible design until I can better understand what exactly each column is for. I would be happy to expand on what you have if you could post some sample data, such as from a hunt or two, along with a quick explanation of where the information is coming from.

I would also review the datatypes chosen. SMALLINT is fine for key id fields, but it would be best to choose a DATE or DATETIME for your hunt date, instead of varchar. The Paid column has two issues that I see. Including a question mark is not encouraged, instead keep it to alpha-numeric and maybe underscores. If the only two options for that field are Yes/No, then it can be a bit datatype. That way you only store 0 for no, and 1 for yes. That saves a lot of space compared to varchars, and prevents you from having "Yes","YES", and "yes", which are all seen as different depending on your chosen software.


How to correct a primary key field in MS SQL 2014

We would need the table definition for a definite answer. It looks like that when you remove the single apostrophe from the serial number, that that value is then the same as an already existing value. Hence the error message.

It looks like the import file was corrected and imported again, resulting in multiple values containing, for example, values similar to 12345 and '12345.

Solution

I would try and search for the near identical primary keys first and see if you can delete them.

I created a dummy scenario with db<>fiddle to show you how it would work.

Create Table

create table xxx (keynumber nvarchar(10) primary key clustered, comment nvarchar(20))

Insert Pseudo Data

insert into xxx (keynumber,comment)
values
('12345','First Comment'),
('''12345','Second Comment'),
('12346','Third Comment'),
('''12346','Fourth Comment'),
('123457','Fith Comment')

SELECT Psuedo Duplicates

SELECT x1.keynumber, x2.keynumber
FROM [xxx].[dbo].[xxx] as x1
     JOIN [xxx].[dbo].[xxx] as x2
     ON '''' + x1.keynumber = x2.keynumber

Results

keynumber | keynumber
:-------- | :--------
12345     | '12345
12346     | '12346

You can then either deleted the duplicates or rename them (see Alternative)

Once you have cleaned up the duplicates, you will be able to run your UPDATE.... Before that you have some manual labour to do.

Alternative / Renaming

Instead of just removing the apostrophe, add an additional value to the keynumber values that have an apostrophe. E.g. add CHK to the keynumber

UPDATE [xxx].[dbo].[xxx]
SET [xxx].[dbo].[xxx].[keynumber] = replace([xxx].[dbo].[xxx].[keyNumber], '''','CHK')

This way you will be able to see which values have to be checked.

Observations

If the column keynumber would have been an int, then it would have been impossible to enter '12345 into the table.


How about just placing the ADMIN: text in the variable? eg

:setvar DBSrvrInst ADMIN:.\sql2014

:connect $(DBSrvrInst)
GO


SQL ODBC silently truncating numeric data

If the parameter to the ODBC prepared statement is defined as decimal(18,0), that's how it's transmitted to the server.  The conversion is performed by the ODBC driver.  AFAIK the driver is not required by the standard to emit a diagnostic when dropping the fractional part on conversion to an integral type.  Presumably the user knows what he's doing.  ;-)

I think you're mistaken, though.   From what I make of the NUMBER datatype, it's identical to SQL Server's decimal: you specify scale and precision up to a maximum of 38 digits.  How are they incompatible?

If you really can't pass the data as a decimal type, I think I'd pass it as a varchar(30) and use a constraint on the SQL Server table to make sure it conforms.