SQL Server 2012 memory consumption outside the buffer pool

Max server memory controls buffer pool and all page size allocations, but still does not control things like direct Windows allocations (linked servers, sp_OA, XPs), memory required for threads/thread stacks, etc.

You can probably expect this to be higher on NUMA (though I'm not sure 20 GB is normal); the point is, you can't expect max server memory to fully control the memory used by an instance of SQL Server. If you want the whole instance (not just buffer pool, plan caches, and CLR) to use no more than 64GB, you should set max server memory to something lower.

Some potential ideas for tracking this down (I will normalize everything to MB):

performance counters

See if anything jumps out here as excessively large:

SELECT counter_name, instance_name, mb = cntr_value/1024.0
  FROM sys.dm_os_performance_counters
  WHERE (counter_name = N'Cursor memory usage' and instance_name <> N'_Total')
  OR (instance_name = N'' AND counter_name IN
       (N'Connection Memory (KB)', N'Granted Workspace Memory (KB)',
        N'Lock Memory (KB)', N'Optimizer Memory (KB)', N'Stolen Server Memory (KB)',
        N'Log Pool Memory (KB)', N'Free Memory (KB)')
  ) ORDER BY mb DESC;

top 20 clerks

You've already done this, but for completeness:

SELECT TOP (21) [type] = COALESCE([type],'Total'),
  mb = SUM(pages_kb/1024.0)
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((type),())
ORDER BY mb DESC;

thread stack size

First, make sure this is zero, and not some custom number (if it is not 0, find out why, and fix it):

SELECT value_in_use
  FROM sys.configurations
  WHERE name = N'max worker threads';

But you can also see how much memory is being taken up by thread stacks using:

SELECT stack_size_in_bytes/1024.0/1024
  FROM sys.dm_os_sys_info;

3rd party modules loaded

SELECT base_address, description, name
  FROM sys.dm_os_loaded_modules
  WHERE company NOT LIKE N'Microsoft%';

-- you can probably trace down memory usage using the base_address

memory-related DMVs

You may also be able to spot something out of the ordinary looking at these DMVs:

SELECT * FROM sys.dm_os_sys_memory;
SELECT * FROM sys.dm_os_memory_nodes WHERE memory_node_id <> 64;

This article was written before SQL Server 2012, so some column names and calculations may have to be adjusted, but may give some other avenues to try as well:

Troubleshooting SQL Server Memory

Some good background in another article on that site too:

SQL Server 2012 Memory

Some good info about the types of things that use memory outside of max server memory (but no good data about how to collect the actual usage):

MTL Consumers


Grant View Server State - is it possible for a none SA user to have in Azure SQL?

GRANT VIEW SERVER STATE is not supported in Azure SQL Database.

GRANT Server Permissions (Transact-SQL)

In order to use sys.dm_os_schedulers you need:

  On SQL Database Premium Tiers, requires the VIEW DATABASE STATE
  permission in the database. On SQL Database Standard and Basic Tiers,
  requires the Server admin or an Azure Active Directory admin account.

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-schedulers-transact-sql

While testing this I also could not grant VIEW DATABASE STATE to a user in master database using SA account.

I grant VIEW DATABASE STATE to the user database using SA account.  Then run SELECT * FROM sys.dm_os_schedulers in the context of user database. I was able to get result set.


Rather than calculating the distance between two points on the fly, you can narrow your selection by approximation.
Since we know that one degree of latitude is a nearly-constant 111km, a search for locations within 100km should rule out points greater than one degree of latitude distant.
Longitude is more complex because of the latitudinal delta (~111km per degree at the equator to 0 at the poles), but there are ways to approximate it. One method would be to simply calculate, in advance, the distance between degrees of longitude for each whole degree of latitude and store the result in a table. That table could be joined on the search target table using a rounded value for the input latitude.
Movable Type has a well-known, informative page dedicated to great circle navigation and related calculations.
Note: I realize this doesn't answer the actual question, but it does address the underlying need for a performant method of lat-long distance calculation, which I hope is helpful.


Before doing any attempts for recovery make sure to have backup of data files.

" tried to move the entire data dir."

  make sure my.ini has correct paths.

Look at error that points at innodb_data_file_path.

  Do confirm your ibdata file(s) are at right location.


Giving a session-local temporary name to a schema

I don't think you can. If you check the documentation on ALTER SCHEMA (PostgreSQL 8.4), there is not such a thing as ALTER SCHEMA TEMPORARILY or anything similar.
What you could actually do (per session), is change your search_path setting. Citing the documentation:

search_path (string)
This variable specifies the order in which schemas are searched when an object (table, data type, function, etc.) is referenced by a simple name with no schema component. When there are objects of identical names in different schemas, the one found first in the search path is used. An object that is not in any of the schemas in the search path can only be referenced by specifying its containing schema with a qualified (dotted) name.

Depending on your specific use-case (the need for a temporary change of schema name wasn't mentioned), this might be helpful.


Only one non-nullable value across 3 columns

That's not what indexes are for, you need a check constraint for that,

For the "at most one can be not null" you can use a little trick: casting a boolean expression to an integer yields 0 or 1. If you add up the values for a is not null condition, the rule "at most one can be not null" translates to "the sum of those must always be 1":

alter table contacts add constraint only_one_not_null
   check (   (a is not null)::int
           + (b is not null)::int
           + (c is not null)::int = 1);

The above can be simplified by using the num_nonnulls() function:

alter table contacts add constraint only_one_not_null
   check (num_nonnulls(a,b,c) = 1);

This also covers the "not all three can be null" requirement.


Should I create a new table with redundant data for optimization reasons?

Not really enough information to give a good recommendation.

A few hundred queries a day is not much at all for Postgres, generally speaking.

As for queries, keep in mind that Postgres caches data in memory. As mentioned, do some study to learn about this caching and how to tune settings for Postgres. By default the settings are quite conservative, to favor erring in the side of safety and not overburdening the host server machine.

Adding a row every second means 86,400 per day, and 31.5 million per year. That is a serious number of rows, but many people handle such data with Postgres.

The key to fast queries here is in the indexing (assuming the RAM buffers issue is tuned). You've not described enough detail to help. If you are simply indexing a TIMESTAMP WITH TIME ZONE column, you may have no performance issues at all. Remember that with suitable hardware, tuned Postgres settings, and appropriate indexing, both your data and the relevant portions of your index will all be cached in RAM and very fast.

If you do decide to cache data in a redundant table, know that you can turn off the write-ahead log (WAL) on that table for faster performance as you do not care about its integrity.

Try some tests, running on hardware similar to your production system. Load up fake data to simulate real loads. Verify behavior with the EXPLAIN feature to make sure your queries act as you expect.

Another approach for performance is to have a background process perform the query and generate results every so many seconds (whatever your users’ tolerance for staleness of results). When user request arrives, your app uses the already determined result rather than performing a fresh query. This increases the burden on your database. And most of those queries are for nought given you have so few user requests per day. But the benefit is fastest possible response to user request. For Java, see ScheduledExecutorService.

Another option is to cache the data in your app rather than in the database if it easily fits in RAM. Java for example offers many kinds of collections and queues for such caching. This especially makes sense if the app gathering the data is also the app responding to the hundreds of requests per day. Use the database as history, but keep the recent data in memory. Keep three caches, one for each of your recent 1 minute, 5 minutes, and 15 minutes, so no queries needed at all, data ready to go at all times.


$ ps -ef | grep pmon | grep -v grep
oracle   23955     1  0 Jan16 ?        00:00:33 asm_pmon_+ASM
oracle   27366     1  0 Jan16 ?        00:00:31 ora_pmon_S112

$ ps -ef | grep pmon | grep -v grep | awk '{ print $8 }' | cut -d '_' -f3
+ASM
S112

$ ps -e -o command | grep pmon | grep -v grep | cut -d '_' -f3
+ASM
S112


migrate SQL Server geography data to PostGIS geometry data

I figured it out.  I used a query for the SQL Server source that converts to POINT text using:

  geometry::STGeomFromWKB(myPointColumn.STAsBinary(), myPointColumn.STSrid).STAsText()

That was all it took.  Now whether the point will be represented in PostGIS the way I intend it to I can't say at this point, but I'm hoping so.


Can oracle dbca template be used cross versions

The information in templates includes database options, initialization parameters, and storage attributes. I use scripts instead, but from what I can see in $ORACLE_HOME/assistants/dbca/templates they are XML files which should translate to the creation of your CREATE DATABASE statement as well as init.ora which hasn't changed between 12.1 and 12.2 apart from a few deprecated parameters.

I would imagine it's not downward compatible to 11g, but there's no harm in trying?


Alternatives to AdeptSQL Diff (for MS SQL Server 2012)

ApexSQL Diff supports SQL Server 2012.
Haven't tested it on SQL Server 2012, but it works fine on 2008


Mongo authentication user can't drop database

No, it is not currently possible (in version 2.4) to create such a user/permission.

Permissions on a specific database grant permissions on entities contained in that database, but not on the database itself.  Cluster level permission is needed to create or drop databases.

Reference: http://docs.mongodb.org/manual/reference/user-privileges/#database-administration-roles


What exactly is INTERVAL 1 HOUR checking?

Since the WHERE clause says NOW(), the expression STAMPFOO > NOW() - INTERVAL 1 HOUR makes this a dynamic query. It will return all records whose timestamp is greater than one hour ago, down to the very second.

If you run this at 2017-02-02 12:15:27, your query will return all FOO values  from 2017-02-02 11:15:27.


SQL Server 2008 simple recovery mode issue

If you change the recovery model to SIMPLE to back to either FULL or BULK_RECOVERY you make all your transaction log backups useless.  The recovery model shouldn't every be changed regularly.

Shrinking the database is simply fragmenting all the objects within the database tables.

You should stop doing both of these things.


Bad estimates when sorting, good estimates without sort

why an ORDER BY can influence the estimates of an index seek.
Can somebody explain this to me?

This is a top values query.  The ORDER BY specifies which 1000 rows the query will return.  Without the ORDER BY SQL Server is free to return any 1000.  So once SQL Server has 1000 rows from Schema1.Object2 that qualify in the WHERE clause, it can stop.  The LEFT JOIN can't reduce the number of rows.
Specifically, the no-ORDER BY plan has a row goal when it reads Object2:
    <RelOp AvgRowSize="698" EstimateCPU="0.814711" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"
EstimateRows="1181.27" EstimateRowsWithoutRowGoal="194907"
LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="3.89162">

The ORDER BY plan does not.  It will need to sort all filtered rows before figuring out which 1000 to return.
<RelOp AvgRowSize="51" EstimateCPU="0.214555" EstimateIO="1.3572" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"
EstimateRows="194907"
EstimatedRowsRead="194907" LogicalOp="Index Seek" NodeId="17" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="1.57175" TableCardinality="42781400">


The most error-proof approach is to restore all databases -- the entire server -- to a different machine, by loading a backup (dump) file and playing back the appropriate logs, beginning at the binlog coordinates where the dump began... and then using mysqldump to extract the one table of interest and copy it to the desired destination.

In any event, you do have to restore something from a backup, first -- which I assume you understand, but have not mentioned.  Playing back the binary logs allows you to capture the events that occurred between the restored backup and the end of the log.

Playing back a single table's row events is, to my knowledge, not supported.  But, even restoring a single database (schema) this way is a complex operation.

The --database or -d option for mysqlbinlog can only definitively identify the desired database with sufficient accuracy when the binlog_format on the server that created the log entries was set to ROW, or, in the case where the binlogs were generated by a replica with log_slave_updates enabled, at least one of the servers in the cascade, including the last one, had its binlog_format set to ROW, because this coerces the replication events into ROW format, which is a one-way process.

If the events were not logged in ROW format, then playing back the binary logs can't be guaranteed to work correctly without all of the other data on the server also being present.  It is also not possible to be certain that the --database or -d option will catch all of the statements, if they were not logged in ROW format, because of interactions with the USE statement and other intricacies of how MySQL interprets replication events.

This does not mean that mysqlbinlog is broken, only that it's an advanced tool that can only be used within the constraints of the way replication works.

The binary log format was designed for replicating changes from one server to another, and only contains enough information to do that when the two servers in question begin with the same data set -- hence the reference to restoring from a backup.

Also, playing back a binary log into your production system is really not something you would ever want to do unless your production system had been damaged so badly that you had no choice but to completely reload it from a backup and wanted to play-forward through the available logs to minimize the amount of lost data.


Since BigQuery support MIN/MAX aggregation function on time fields You can use this SQL:

SELECT user_id, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM `data`
GROUP BY user_id
ORDER BY user_id ASC

And this the sql with some test data you can play with

WITH `data` AS (
SELECT 1 AS user_id, 'a' as unique_id, timestamp('2019-01-20 5:10:05') as start_time, timestamp('2019-01-20 5:10:20') as end_time UNION ALL
SELECT 1 AS user_id, 'b' as unique_id, timestamp('2019-01-20 5:10:11') as start_time, timestamp('2019-01-20 5:15:31') as end_time
)

SELECT user_id, MIN(start_time) AS start_time, MAX(end_time) AS end_time
FROM `data`
GROUP BY user_id
ORDER BY user_id ASC

Which produce the following desired result:


How to atomically replace table data in PostgreSQL

Right, the TRUNCATE TABLE command you are performing "... acquires an ACCESS EXCLUSIVE lock on each table it operates on", so in the first SQL block you posted, any other clients attempting to access the table after that time will be blocked until your INSERT finishes and you COMMIT.

You can use the same workaround as in your MySQL-specific code; Postgres supports roughly the same syntax and will have similar locking behavior. To wit:

BEGIN;
-- You probably want to make sure that no one else is
-- INSERT / UPDATE / DELETE'ing from the original table, otherwise
-- those changes may be lost during this switchover process. One way
-- to do that would be via:
-- LOCK TABLE "table" IN SHARE ROW EXCLUSIVE mode;
CREATE TABLE "table_new" (LIKE "table");
INSERT INTO "table_new" ...;

-- The ALTER TABLE ... RENAME TO command takes an Access Exclusive lock on "table",
-- but these final few statements should be fast.
ALTER TABLE "table" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table";
DROP TABLE "table_old";

COMMIT;

Extra bonus: Postgres actually supports transactional DDL, unlike MySQL, so in case you need to ROLLBACK the above transaction, you may safely do so.


Querying 500 million records in Oracle view takes forever

select /*+ full(su) no_index(su) parallel(su 8) */
  distinct ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO
from
  t_vz_service_utilization su
;

There. Now you can use the lookup table on this, something like this.

select /*+ no_merge(T0base) leading(T0base) */
  T1.LOOKUP_VALUE as ORIG_SYS,
  T2.LOOKUP_VALUE as DEST_SYS,
  T4.LOOKUP_VALUE as OPERATION_ACCESSED,
  T7.LOOKUP_VALUE as SERVICE_NAMESPACE
from
  (select /*+ full(su) no_index(su) parallel(su 8) */
      distinct ORIG_SYS_NO, DEST_SYS_NO, OPERATION_NO, NAMESPACE_NO
    from
      t_vz_service_utilization su) T0base
  left join t_vz_lookup T1
    on (T0base.ORIG_SYS_NO = T1.SNO )
  left join t_vz_lookup T2
    on (T0base.DEST_SYS_NO = T2.SNO )
  left join t_vz_lookup T4
    on (T0base.OPERATION_NO = T4.SNO )
  left join t_vz_lookup T7
    on (T0base.NAMESPACE_NO = T7.SNO )
;

Of course this could be far from optimal, but you gave us nothing else than the query. We do not know your indexes (which columns, b-tree/bitmap?), constraints (FK/UK/PK), table structure (width, row migration/chained rows), init parameters (star transformation enabled?), table/index statistics, not even a plan...


No, Management Studio does not have this native functionality in any version from 2005 -> 2016 CTP 3.2. You may want to check out 3rd party add-ins, like Mladen Prajdić's SSMS Tools Pack or Red-Gate's SQL Prompt, both of which have the capability to format a block of code, including automatically upper-casing keywords. However I don't know of any tool that will do this automatically as you type.

For me, I have been doing it so long, it just comes naturally, and not pressing Shift would actually slow me down. When I get someone else's code where the keywords are lower case, I highlight either the whole block or the offending sections, and hit Ctrl+Shift+U. If I have to highlight the whole section, I then go in to the object / column names and use IntelliSense to case them correctly (I have a real hang-up about ensuring that all keywords are upper case and all entity names match the metadata exactly, because you never know when your code will be deployed to a case sensitive collation).


ENABLE KEYS on a large table is crashing mysql

When I asked Question 4: (During the ENABLE KEYS phase, do you see Repair by keycache in SHOW PROCESSLIST?) this is what happens when there is no more room for sorting. You most likely ran out of room if mysqld attempted to switch to Repair by keycache.

If you are stuck with this 2GB Server for testing, try one or more of the following:

Lower bulk_insert_buffer_size to 64M
mysqldump the original data using --skip-extended-insert and reload with it. It will produce one INSERT per row. It will be slower to reload (about 2-3 timer longer) but at least memory will be spared collateral damage
Abandon doing DISABLE KEYS and ENABLE KEYS


BULK INSERT getting "Cannot bulk load because ... Access is denied" on a file I have access to

The problem is that:

You are logging in using Windows Authentication: this means that SQL Server will attempt to impersonate your Windows / Domain account for the file system access.
You have logged in directly to a work station, and then connected remotely to SQL Server: this means that you connected to the server running SQL Server indirectly, by passing your authentication token from your workstation to the server running SQL Server.
You have not enabled delegation for SQL Server: this means that you are stuck with the default behavior of not being allowed to pass that token beyond 1 step from the direct login.
You are trying to read a file that exists on a remote resource: this means that it is more than 1 step beyond where you directly logged in.

This is why you can access the file from your workstation just fine. And it is why using xp_cmdshell also works since the service account is logged in directly to the server running SQL Server, and the remote resource is just 1 step beyond.

I suspect that if you were to remote desktop directly into the server running SQL Server, run SSMS on that server, and then execute the BULK INSERT, that it would succeed. Or, it should also work if you set up a SQL Server Login, grant the Login the "bulk admin" permission, then EXECUTE AS Login = 'JustReadTheDangFileAlready';, and then do the BULK INSERT. When executing BULK INSERT / OPENROWSET (BULK...) as a SQL Server Login, there is no Windows account to impersonate, so the file system access is done using the service account, which will be just like xp_cmdshell.

You have the following choices:

Enable Delegation for SQL Server (can't remember if it's for the service or the account, in which case it might be your Windows account that needs it, but there are guides on how to set this up, which includes setting up the SPNs). I believe this is the preferred / recommended choice. This allows the process to forward the authentication tokens to resources further-removed from the direct login.
Use a SQL Server Login for this process. I can't believe that I'm suggesting this, but if you had a SQL Server Login that was in the "bulk admin" fixed server role OR had the correct "bulk admin" permission, then you could create the associated User for that Login (in an appropriate DB), and place the BULK INSERT in a stored procedure that was created using WITH EXECUTE AS 'JustReadTheDangFileAlready'. Then you could execute that proc which should mask that the request was coming from a Windows account.
Open up the permissions on that remote file (and maybe also the folder?). You could give "read" permission to "Everyone" (and maybe also "read" or some other permission on the folder).
Delete this question and use this "error" as justification for needing an expensive SAN that will be connected via HBA card and appear as a local drive 🙃. Just tell the CFO: "It's really the only way. The internet said so."


What column/s do I need an index for my queries?

Most of your answers are here.  But here are some notes:

= is easier to index than > (or other inequalities).  For that reason, you list of cases is much amplified.
Using OFFSET (under the guise of LIMIT {start}. ...) requires scanning all the skipped rows.  So, see if you can "remember where you left off" as in here.
Please use fake names instead of {...}; even a, b would be easier to read and parse.
WHERE a op 3 -- usually can use INDEX(a)
WHERE a = 3 ORDER BY b -- use INDEX(a,b), but
WHERE a > 3 ORDER BY b -- won't get past the "range"; that is, INDEX(a,b) will work, but the b part will not be used.
I don't think HAVING ever factors into indexing.
IN ( constants ) is somewhere between = and > for index-ability:
WHERE a IN (constants) AND b > 8 might use both parts of INDEX(a,b), but
WHERE a IN (constants) ORDER BY b cannot use both parts of INDEX(a,b)
If {columns} is a short enough list, then consider a "covering" index.  (See my first link.)
ORDER BY:  ASC vs DESC usually does not matter; but
ORDER BY a ASC, b DESC cannot use INDEX(..., a, b) because the directions are different.  (Version 8.0 has a solution; I assume you are not there yet.)
OR is rarely optimizable -- that is it usually defies index usage.
Your case #6 has 4 sub-cases for operators; see my link.  Hint: The order of ANDs does not matter, but the order of columns in a composite index does.

Etc, etc.

One more thing... If you have most of those cases hitting the same table, it becomes tricky to devise the optimal set of indexes.  Note

INDEX(a) is redundant if you have INDEX(a,b)
There is a limit of 64 indexes on a table, but it is unwise to have more than, say, a dozen.

If you want more specifics, (and my blog does not suffice), please provide specific schema and queries.

"Case 2 is most popular"

If id is the PRIMARY KEY and if it is in date order:

WHERE a = const
  AND id <= $left_off
ORDER BY id DESC
LIMIT 11

INDEX(a, id)

WHERE a > const
  AND id <= $left_off
ORDER BY id DESC
LIMIT 11

(PK suffices)

Notes:

By switching from date to id (if possible), the "left off" logic becomes much simpler.
LIMIT 11 - 10 to show, 1 to see if there is any more.  (This makes the UI more friendly, rather than linking to an empty next page.)
See the second link for more discussion.


ORA-12516: when accessing another database via link

Most probably you are reaching the maximum connections allowed. Make sure that old sessions are being closed properly and try increasing the processes and sessions parameters.


Not only must all memory-optimized data be loaded into memory before any tables are available in the entire database, but because non changes to indexes on memory-optimized tables are logged, all indexes must be recreated on all memory-optimized tables as well.
That's why it's critical to have fast storage, and spread your containers across multiple volumes.
http://nedotter.com/archive/2016/09/in-memory-oltp-the-moving-target-that-is-rto/
http://nedotter.com/archive/2016/02/backup-and-recovery-for-sql-server-databases-that-contain-durable-memory-optimized-data/


Your question is not very clear on this point but I'm assuming you want to find overlaps with a proposed, new booking.

Let the values for the proposed booking be held in the variables @StartDate, @EndDate and @RoomID.  The SQL will be

select
    RoomID,
    StartDate,
    EndDate
from TheBookingTable as t
where t.RoomID =  @RoomID
and T.StartDate <= @EndDate
and T.EndDate >= @StartDate;

This query will return all current bookings which overlap your proposed booking, or an empty set if none exist.


Dataset parameters are not defined there.

To define the "value" of the parameter, you have to edit the dataset itself. That's where you will specify what value you want your query to run with; you can use a static value, or a report parameter.

If you use a report parameter as the value for your dataset, the dataset will not execute until the report parameter value is defined by the user. This will make the report behave like, "the values of parameter 2 only become available after selecting parameter 1".

Hope it helps.


SQL Server Table keeps big after schema change

Your rows fit in page, even with 100 spaces in Comments. Updating the Comments to ltrim(rtrim)) will make absolutely no difference in space used, since the rows will be left in place in their respective pages (ie. absolutely no page gets freed, you just get more space available on each page). To reclaim the lost space you should rebuild the table:

ALTER TABLE ... REBUILD;

Not that 3.7Gb is big by any stretch...


SAP AG (Often just called SAP) is a company which has a product called SAP ERP (Enterprise Resource Planning).  Since Oracle also makes ERP software they are indeed competitors.  In fact they are the top two ERP vendors.

SAP can be used with quite a few different database systems, but according to one source I found Oracle is dominant comprising around 80% of SAP systems.  Oracle recently won a law suit against SAP for intellectual-property theft. (http://news.cnet.com/8301-1001_3-20029601-92.html#ixzz1CWy7UiKR)

Here is some SAP on Oracle documentation.

It does seem like a strange scenario for a product to depend so heavily on a competitors product, but it is not unheard of.  To some extent this pattern exists for Oracle with their database product running on Microsoft Windows servers.


Unable to begin a Distributed transaction

Map a specific instance of SQL Server to a specific instance of MSDTC?

To map a SQL Server instance to an instance of MSDTC, open an administrative command prompt and use the msdtc.exe command.

The following command is used to create a mapping between an instance of SQL Server and an instance of MSDTC.

msdtc -tmMappingSet -name <MappingName> -service <SQLServerServiceName> ^
-ClusterResourceName <MSDTCResourceName>

https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/15/msdtc-recommendations-on-sql-failover-cluster/


From here:

  "varchar fields were set to utf8_bin and needed to be changed to
  utf8_general_ci"


Lost data during PostgreSQL minor version upgrade

Indeed, you should not have uninstalled it and re-installed it, as PostgreSQL initdb starts a new database store from scratch, hence empty.

BTW it may happen that your data is still there, but in another old directory. See for example this article (https://keita.blog/2016/01/09/homebrew-and-postgresql-9-5/) that shows an upgrade between 9.4 and 9.5 but it may apply in the same way in your case.

If you look at the PostgreSQL formula you can see:

def post_install
    (var/"log").mkpath
    (var/name).mkpath
    unless File.exist? "#{var}/#{name}/PG_VERSION"
      system "#{bin}/initdb", "#{var}/#{name}"
    end
end

so initdb should be called only if directory is not already populated, so in your case the database just got initialized in a new directory, empty, while your data is still in another directory.

But things have changed recently.

Homebrew 1.5, released on January 19th 2018 has this in its changelog:

  brew postgresql-upgrade-database is a new command to simplify upgrading PostgreSQL databases between major versions.

It is a wrapper around PostgreSQL pg_upgrade tool, that can be used to upgrade a database in place, hence not loosing anything, including between major versions.

This article (https://olivierlacan.com/posts/migrating-homebrew-postgres-to-a-new-version/) can give you a lot of information on how to upgrade PostgreSQL, and what happens when you use brew upgrade.


ISNULL - available only in SQL Server. Allows for the testing and replacement of a NULL value with one another one.

COALESCE - ANSI standard. Allows for the testing and the replacement of a NULL value with the first non-null value in a variable length set of arguments. It is important to note that data type precendence factors into this

-- Conversion failed when converting the varchar value 'a' to data type int
SELECT COALESCE(CAST(NULL AS varchar(10)), 'a', 1)
-- Returns 1
SELECT COALESCE(CAST(NULL AS varchar(10)), 1, 'a')

In the above example, 'a' is the first non-null value but character data has lower precedence than integer.

Another consideration between ISNULL vs COALESCE is the resultant of ISNULL is determined to be NOT NULL while the outcome of a COALESCE call is NULLable. See JRJ's post ISNULL() <> COALESCE() While that might seem a trivial thing, the query optimizer can make different plans based on the nullability of a column.

You can easily test the nullability of your isnull/coalesce/case expressions by running it through the dmo  sys.dm_exec_describe_first_result_set

-- these all evaluate to not nullable (is_nullable = 0) because I'm not clever enough
DECLARE @sql nvarchar(4000) = N'
SELECT ISNULL(NULL, 1) AS I
, COALESCE(NULL, 1) AS C
, CASE WHEN NULL = NULL THEN NULL ELSE 1 END AS C1
'
SELECT
    DMO.*
FROM
    sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS DMO

CASE - Also an ANSI standard scalar function. I'd look at using CASE over the previous two when I have a test that can't be expressed in a simple scalar but that's a pretty weak answer, I admit.


I found nothing in the pgAdmin documentation, but the source code reveals the query behind these entries (added for Postgres 9.2+):
It boils down to:
SELECT temp_files AS "Temporary files"
     , temp_bytes AS "Size of temporary files"
FROM   pg_stat_database db;

And the Postgres manual has details for pg_stat_database:

tmp_files bigint  Number of temporary files created by queries in
this database. All temporary files are counted, regardless of why the
temporary file was created (e.g., sorting or hashing), and regardless
of the log_temp_files setting.
temp_bytes bigint     Total amount of data written to temporary files
by queries in this database. All temporary files are counted,
regardless of why the temporary file was created, and regardless of
the log_temp_files setting.

Note that these values do not contribute to the size of your database. But they indicate that your setting for work_mem may be too low, so that many sort operations spill to disk (which is very slow as compared to just RAM).
Related:

Optimize simple query using ORDER BY date and text

To actually compact the size of your database:

VACUUM returning disk space to operating system

To measure size:

Measure the size of a PostgreSQL table row

Aside: WAL (Write Ahead Log) would be equivalent in Postgres for the transaction log in SQL Server. Nice explanation in this related answer on SO:

Why do SQL databases use a write-ahead log over a command log?


Fastest way to move a database from one server to another

If you are changing versions, DO NOT MOVE THE mysql SCHEMA.

Why should you not move the mysql folder? It has to do with the authentication privileges.

The number of columns in mysql.user is different from version to version

If you run desc mysql.user

You will see 31 rows for MySQL 4.1
You will see 37 rows for MySQL 5.0
You will see 39 rows for MySQL 5.1
You will see 42 rows for MySQL 5.5

I wrote about this before

Cannot GRANT privileges as root
Mysql users deleted

It is OK to move everything else. On the new machine that has MySQL 5.5.24, do this:

mv /var/lib/mysql /var/lib/mysql/mysql55
mkdir /var/lib/mysql
<scp or rsync /var/lib/mysql of MySQL 5.1.41 over to /var/lib.mysql of MySQL 5.5.24>
rm -f /var/lib/mysql/mysql/*
cp /var/lib/mysql/mysql55/* /var/lib/mysql/mysql/*
chown -R mysql:mysql /var/lib/mysql
service mysql start

So, the question remains:

How do you move the User Privileges in the old MySQL 5.1.41 to MySQL 5.5.24 ???

There are two ways to do this starting on the MySQL 5.1.41 machine:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Either way, move MySQLUserGrants.sql over to the MySQL 5.5.24 machine and execute the script

I wrote about this before : importing myisam 5.0 database into a 5.5 innodb server


insert trigger in sql server to linked oracle

The answer is a "simple" trigger as I've outlined below for you.  I didn't have enough information to name it properly, but hopefully you will be able to fill in the gaps.  This will fire AFTER the insert operation on the SQL side, then insert the relevant records to the Oracle linked server (assuming it's setup correctly).
However, a few words of advice from someone whose environment does this kind of thing a lot (using triggers to shove data from A to B).
Depending on what kind of client is accessing this, it will escalate to a distributed transaction, which may or may not work for all clients.  If it doesn't work then you will need to change this to using a cursor (or find some way to make distributed transactions work for your environment).  But don't test with just management studio, use the application(s) in your environment as well.
There is no way to catch an error in here (not even in a try/catch block).  If the insert into Oracle fails, then the transaction that wrote the data to TableA will also fail.  This may or may not be what you want to happen, but it is what will happen and there is nothing you can do to stop it.
Finally, if a delay is acceptable and you only need it on INSERTED data, then might I suggest using an agent job to run periodically, query the Oracle side for the highest value id, then send over everything in TableA that is greater than that id.
CREATE OR ALTER TRIGGER trg_TableA ON dbo.TableA
AFTER INSERT
AS
BEGIN

    INSERT INTO OracleLinkedServerName.SchemaName.TableB
        (id, [name])
    SELECT I.id, I.[name]
    FROM INSERTED AS I

END


Local package won't compile when remote package recompiles. (Oracle)

It is obviously a bug, so should go to Oracle support.
As a workaround, are you able to switch that call on the 11gR2 to dynamic SQL to avoid the hard-coded dependency.

You might be able to do that through a wrapper package on the 11g side.
Rather than

11g package calls 10g package

it goes

11g package calls 11g wrapper and 11g wrapper (dynamically) calls 10g package.


It is the amount of WAL that would need to be replayed to bring one backup up to the next-in-time (previous in the order listed) backup.  If you delete the first backup, it is the amount of WAL that would also be deleted, as being no longer useful.

They are different sized because your database has seen different amounts of activity between your backups.


Determining Functional and Transitive dependencies from Relational Model (with no sample data)

You are being asked to engage in reverse engineering.  Reverse engineering is often done in the case of legacy systems, in order to find out what goals the original designers might have been pursuing.  It's really an iffy proposition, nevertheless.  You best handle is the foreign key constraints.  These reveal the FDs, presumably.  Also, you can presume that every non key value is dependent on the primary key of the table it is in.

In regular (forward) engineering, you don't discover the FDs by studying the design.  You study the subject matter, and uncover the FDs there.  This can be time consuming, and people driven by a tight schedule may accuse you of "analysis paralysis".  Only once you've discovered the FDs, among other subject matter features, and you've decided whether or not to pursue a normalized design, do you proceed to compose the tables and the constraints.

Good luck.  You're going to need it.


How save psql `ERROR` output to the same file as the DDL commands?

I want help with how write the command line so I can save the output of the restore to a file.txt. So the create/insert/alter on the backup aren't relevant. And the image is just as reference, I also include the message as text just in case.

If you're asking how do you put the error to the "result file" too, you need to redirect STDERR as well as STDOUT or direct STDERR to STDOUT with 2>&1

psql.exe -U  sde sde < c:\backup\sde_04_06_2018.backup > c:\backup\result.txt 2>&1

Internally you have three "standard" streams: input, output, and error.

input you're directing that to c:\backup\sde_04_06_2018.backup
output you're directing output to c:\backup\result.txt
You need to also redirect error.


postgresql being slow on count distinct for dates

There is a trick with distinct to get it fast using index, that you can try.
It involves creating a function looking like that:

CREATE OR REPLACE FUNCTION small_distinct(IN tablename character varying, IN fieldname character varying, IN sample anyelement DEFAULT '1800-01-01'::date)
  RETURNS SETOF anyelement AS
$BODY$
BEGIN
   EXECUTE 'SELECT '||fieldName||' FROM '||tableName||' ORDER BY '||fieldName
      ||' LIMIT 1'  INTO result;
   WHILE result IS NOT NULL LOOP
      RETURN NEXT;
      EXECUTE 'SELECT '||fieldName||' FROM '||tableName
         ||' WHERE '||fieldName||' > $1 ORDER BY ' || fieldName || ' LIMIT 1'
         INTO result USING result;
   END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Then create an index on the column you want to count distinct, and select small_distinct('yourtable', 'yaddate'); should return you the distinct values you want, without the need to read the table.

Try it, be beware, I'm not sure it will work right out of the box, as I quickly adapted it from a varchar function.


Is it possible to have a foreign key that relates to information_schema

No.

information_schema contains views. You can't have a foreign key to a view.

You can't set a foreign key to point at the underlying pg_catalog.pg_attribute table that defines columns, either, because foreign keys to system catalogs are not supported.


Improve performance on concurrent UPDATEs for a timestamp column in Postgres

Would a different kind of column be faster? For example an integer

No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway.

  Is there some way to not lock the column?

It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating.

There is no way to prevent the row level lock. It exists because without it behaviour and ordering concurrent updates would be undefined. We don't like undefined behaviour in RDBMSs.

It only blocks concurrent updates of the same row anyway.

  Any other tips to improve this?

Not with the detail provided. There's likely a better way to do what you're trying to do, but it'll probably involve taking a few steps back and looking for a different strategy for solving the underlying problem.

In the specific case of cache invalidation I think you might want to look into LISTEN and NOTIFY. Again though, there just isn't enough info here to go on.


Good tool for making MySQL queries graphically?

You can try Visio. Here's a related SO post.

Here's a how-to about making the connection.


Want to create partitions of a MySQL table and change the charset of new partitions

Partitions have in their syntax the option to have different engines for every partition, but this is not (yet?) supported.

Having different character sets for different partitions, however, may not make sense as all of them should have a common data structure. It is also not supported. Please understand that the charset is a property of a field, not of a table, and that the only reason you can configure it for a table is to set a "default".

If you want to create partitions for archiving, that is a good call, as it makes easier to delete old ones. Be aware of MySQL partitioning restrictions.

If you want to change the charset of a field in an online fashion, I recommend you to use any online alter table tool (I think that MySQL 5.6 does not allow for online charset alters, but I may be wrong) like Facebook's, Percona's or Shlomi's.

As an addendum, if at some point you need to mix different charsets on a field (I have needed it in the past as an intermediate step for having invalid charsets), you could set the type of the string to binary type instead of text (BLOB instead of TEXT, VARBINARY instead of VARCHARetc.). Binary strings do not have encoding information.


Is it OK to create hundreds of databases in SQL Azure versus one big one and run the risk of deadlocking

Since no one else has answered, I'll share some opinions and do some hand-waving.

As long as you aren't locking common resources, or are locking resources in the same order, you shouldn't have problems with deadlocks.

I'd look at separate tables before separate databases. Each additional database will definitely cost you more but additional tables won't necessarily cost you more. You might need to use more than 1 database becuase of the sheer volume of data you will store or because of the rate at which you need to store your burst traffic. If you can manage it, I think that a table-level granularlity will be more flexible and possibly a good deal cheaper than starting with a database-level granularity.

The problem with putting each device's data into it's own tables is that it makes the reporting hard since all of the table names will be different.

I presume that you have some way of detecting when you get a "failure resend" of data. You don't want to put the same value in a table twice and I i'm sure that the devices can fail (local power failure?) in ways that have nothing to do with whether or not earlier values where properly stored.

WAG: Assuming each "value" is 4 bytes, I calculated about 11.5 MB of collected data per device, per day. (This ignores all kinds of stuff, like device identifiers and timestamps, but I think it is OK as a rough estimate.) So, with "thousands" of sites, we are looking at tens of GB, per day. You don't mention any kind of lifetime on that data. The largest Azure database currently maxes out at 150 GB. You could fill those up pretty quickly.

Getting anything to happen in a web browser in a short period of time is iffy. When you are reading from (possibly multiple) databases with GBs of data, continuously inserting lots of new data into the tables you are reading from and interacting with web servers across the open internet, "real time" is wishful thinking. IMO. "Fast enough" is the usual goal.

If you can't keep all of the data you need in a single report in one SQL Azure database, it's a problem. There are no linked servers or distributed views (at this point). There is no simple way to aggregate accross many Azure databases. You'd have to pull all of the data to a central location and report from there. I'd guess that the aggregated data would be too large to store in a single SQL Azure database, so you'd have to go to on-premise or maybe EC2. A data mart or warehouse with a star-schema structure would be the classic answer there, but that takes significant processing time and that means no "real time". Also, that's potentially a lot more data transfer from Azure to wherever it goes, and that will cost you.

I wouldn't commit to this strategy without a pilot program first. The first thing to do would be to build a single instance (can it handle 400 sensor values a second? (Is that a series of rows, a big denormalized row, an XML document or something else? The format of the incoming data will affect how fast the data can be stored. Can you do bulk inserts, or does it have to be row-by-row?) How about 4,000 sensor values a second? It's possible that an single SQL Azure instance might not be able to store that much that quickly.) and see how it handles insertions at your expected rates and see how the reporting might work. And I'd talk to Microsoft too. Just dealing with the billing for hundreds or thousands of seperate databases might be quirky.

I don't know if this is applicable to you, but have you looked at Microsoft's "Stream Insight" product? It seems to be aimed at situations like yours. Caveat: I've never used it.

The marketting blurb:
Effectively analyze large amounts of event data streaming in from multiple sources. Derive insights from critical information in near real time by using Microsoft StreamInsight. Monitor, analyze, and act on data in motion and make informed decisions almost instantaneously

While doing some quickly googling, I noticed a blog posting which states that StreamInsight available on SQL Azure as a CTP last year. it might be ready for prime time by now.

Good luck, it sounds like an interesting project.


How to implement 'temporal database' to keep Sales records truthful in POS?

This problem is essentially that of slowly changing dimensions. Typically you would not allow editing such records in place; instead you create a new version of it (if your user is stupid enough not to realize that a desk is a different product), while the old sales record still references the previous version.


mysql_* functions

officially deprecated as of PHP 5.5 (released June 2013).
removed entirely as of PHP 7.0 (released December 2015)

So switch to
mysqli or pdo and use always prepared statements with parameters because of sql injections

see api comparison


Return all rows with a certain value in jsonb dictionary in Postgres

You can use jsonb_each_text (or json_each_text) function which expands outer most json object into key - value record. Here is the example query;

SELECT * FROM
(
    SELECT
        t.id,
        (jsonb_each_text(t.dicts)).*
    FROM
        my_table t
) denormalized_values
WHERE
    value = 'walking'

And the result of the query above will be;

| id |      key |   value |
|----|----------|---------|
|  1 | exercise | walking |
|  3 |  hobbies | walking |

<<fiddle>>


Restore database in workbench from a Linux file

Short answer - Yes, you can restore from MySQL Workbench.

Explanation:

Both MySQL Workbench and command line are clients. I hope you know about the MySQL client-server architecture. MySQL Workbench is GUI(Graphical User Interface) client and Command line(MySQL client) is a CLI(Command Line Interface) client tool.

When you have a backup file on the Linux machine data directory, it is very easy to restore using a command line. The syntax for CLI restoration is:

mysql -u root -p dbname < dbbackup.sql

If you prefer to use MySQL workbench. Create a new connection on the MySQL dashboard by giving hostname (Linux server hostname), MySQL username and password. After created new connection, you will see Import/Restore data on the right-hand side of the window. Click on that option and it will provide you an interface to choose database name and backup file with complete path.

My personal choice would be command line interface. I hope this will help you!!!.


Is very bad for lots of registers... if you need accessing a file, need typing db.collection.find({"hours.hour":"05", "hours.file.name":"name"}). And you hours arrays will grow  very fast...

You can having a struct like this,

{
    _id   : {date:"2014-02-13",hour: "00"}, files: [
                     {name:"something.csv", date_added : ISODate("2014-04-04T08:59:34.268Z")},
                     {...},
                     {...}]
 }

Having a documents with a array having little data and where you find very fast...


Find all tables without foreign keys in a database

SwapnilBhate's answer will return tables that are referenced by a foreign key. Adding a further LEFT JOIN will exclude these:

SELECT tbl.name
FROM sys.tables AS tbl
    LEFT JOIN sys.foreign_key_columns AS fKey
        ON tbl.object_id = fKey.parent_object_id
    LEFT JOIN sys.foreign_key_columns AS rKey
        ON tbl.object_id = rKey.referenced_object_id
WHERE fKey.parent_object_id IS NULL
    AND rKey.referenced_object_id IS NULL;


Restart your database and that may clear up your issue.  Any more relevant advice will require alert logs or trace files from the database.


Query store data maintenance background tasks

For the query store to be consuming a lot of IO I would say, by my experience, that there is a lot of change in the data calling methods.

Are you aware of any reporting with new filters?
Any major change is Store Procedures parameters that might cause parameter sniffing
Any other change to an Application that consumes data in the sense of querying with parameter changes?

I'm asking since these situations create new execution plans and those create data in the Query Store, causing the IO.


You need to use INDEX_STATS view to determine fragmentation.

Firstly, You need to populate this view with an individual index by using :

SQL> analyze index <your_index> validate structure;

and then look at these values ratio and height :

select round((del_lf_rows/lf_rows)*100,2) ratio, height, lf_blks, lf_rows
  from index_stats;

If ratio is more than %20

or height is more than or equal to 4

or  lf_rows is lower than lf_blks,

then you need to rebuild your index against fragmentation.

The blevel column of dba_indexes table is my main point of interest. This gives the number of branch levels (including the root node) for a b-tree index, and the column height is closely related to blevel.

If you have access to MOSC, search for the blevel keyword, especially the subject Index Health. The Oracle Community was also suggesting rebuilding indexes for indexes have blevel more than or equal to 4.


PostgreSQL not working, pgAdmin always says server not listening

Since you installed multiple versions of PostgreSQL, there is a good chance your cluster is running on a different port than 5432. Additional clusters automatically use the next free port unless instructed otherwise. Like 5433, 5434 etc.

Check your setting in postgresql.conf to find out and connect to the proper port. postgresql.conf is usually installed in your data directory, which in turn is defined in pg_env.bat, usually located here

C:\Program Files\PostgreSQL\<version>\pg_env.bat

Default location of the data directory:

C:\Program Files\PostgreSQL\<version>\data\postgresql.conf

Where <version> is 9.1 or 9.2 in your case.
Base directory varies according to your version of Windows.

Obviously initdb fails to create a database cluster. Read the chapter in the manual to get a grip on initdb. Maybe a problem with missing privileges?

Here is a closely related question with a solution.


Computed column 'IsWeekend' in table  cannot be persisted because the column is non-deterministic

You can do a datediff between 1900-01-01, that just happens to be a monday, and check if modulo 7 is a 5 or a 6.

create table D
(
  DateRecorded date,
  IsWeekend as cast(case when datediff(day, 0, DateRecorded) % 7 in (5, 6)
                      then 1
                      else 0
                    end as bit) persisted
)

BTW, you don't need to persist the computed column in order to use it in an index.


only the second, can you be more specific?

For second view test

CREATE VIEW vs_attrib
AS
SELECT MIN(iditem) OVER (PARTITION BY name) iditem, attributes
FROM tb_products

Index tb_products (name, iditem) may increase view's performance.


Migration script fires 'The data definition language (DDL) command cannot be executed at the Subscriber'

We found traces of a replication that had been activated in the past in a specific table.

Replication was not fully removed from this table.

We also notice that the script works in different database name (a database with the same structure).

Here is the history:

We restored database from DATABASE_A (with traces of replacation) to DATABASE_B
We run the stript on DATABASE_B and it works
We run the script on DATABASE_A and it fails

Our DBA found traces of replication in DATABASE_A and in order to clean up the replication traces, he had run:

sp_removedbreplication 'DATABASE_A'

I am not sure if there is something configured on system tables in SQL Server that are related to the name DATABASE_A. We are going to investigate this issue deeper to find dependencies or what is causing the error.


Import a sample database into SQL Server on AWS RDS free tier

Use this version of NW instead. Its very light (2.6 MB in disk) and you can download the scripted version.
Should be just fine.


What is the performance difference between IN (42) and id = 42 in MySQL?

Whether the queries are equivalent or not is up to the MySQL Query Optimizer. Why ?
Back n Mar ,13 2013 I wrote an answer to this post: Is there an execution difference between a JOIN condition and a WHERE condition?
In that post I described exactly how JOINs are executed. The following is taken from my post which quotes from page 172 of Understanding MySQL Internals:

Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
For each table, decide whether a table scan is better that reading on a key. If there are  a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
Determine the order in which tables should be joined when more than one table is present in the query.
Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
Eliminate unused tables from the join.
Determine whether keys can be used for ORDER BY and GROUP BY.
Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
Merge views (expand the view reference as a macro)

On that same page, it says the following:

In MySQL optimizer terminology, every query is a set of joins. The term join is used here more broadly than in SQL commands. A query on only one table is a degenerate join. While we normally do not think of reading records from one table as a join, the same structures and algorithms used with conventional joins work perfectly to resolve the query with only one table.

From the aforementioned information, JOIN behavior will execute the same say regardless of a query having multiple tables or event just one table.
YOUR ORIGINAL QUESTION
Under the hood, MySQL will evaluate the two queries the same way. If you want better query performance, you have to take the bull by the horns. You should do all you can to the table so that MySQL join behavior goes as smooth as possible.

Add the needed indexes
Increase session level buffers (sort_buffer_size, join_buffer_size)
Take advantage of Storage Engine mechanisms for tuning data and indexes
Refactor the query

If you look at dimitar's answer, now it spells out a case where MySQL's join behavior is put to the test. Instead of betting on two horses you own (your queries) to see who runs better, invest time into getting a faster horse if such a horse exists.
From ditimar's post, you have these

SELECT * FROM table WHERE id IN  (42,43,44,45);
SELECT * FROM table WHERE id = 42 or id = 43 or id = 44 or id = 45;

Here is yet another one I suggest for the sake of example
SELECT A.* FROM table A INNER JOIN
(SELECT 42 id UNION SELECT 43 UNION SELECT 44 UNION SELECT 45) B
USING (id);

and another
SELECT * FROM table WHERE id = 42
UNION
SELECT * FROM table WHERE id = 43
UNION
SELECT * FROM table WHERE id = 44
UNION
SELECT * FROM table WHERE id = 45;

I can make up other possibilities, but the main idea here is to try to write good queries the first time. When your amount of data grows, your best queries may suffer due to key distribution and stale index stats which may require optimizing tables or even rewriting queries to suit bigger data.


How do I set permissions for Package Body

Anything else in addition to EXECUTE? When granting DEBUG, you implicitly "get" EXECUTE on the PACKAGE BODY (I replaced EABINTEG with U2 in your query):

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2;

User created.

SQL> grant create session to u1, u2;

Grant succeeded.

SQL> create package u1.p1 as
  2  procedure pr1;
  3  end;
  4  /

Package created.

SQL> create or replace package body u1.p1 as
  2  procedure pr1 as
  3    begin
  4      null;
  5    end;
  6  end;
  7  /

Package body created.

SQL> grant execute on u1.p1 to u2;

Grant succeeded.

SQL> conn u2/u2
Connected.

SQL> @yourquery

OWNER NAME  TYPE                GRANTS
----- ----- ------------------- ------------------------------
U1    P1    PACKAGE             EXECUTE

Seems ok so far.

SQL> conn u1/u1
Connected.
SQL> grant debug on u1.p1 to u2;

Grant succeeded.

SQL> conn u2/u2
Connected.

SQL> @yourquery

OWNER NAME  TYPE                GRANTS
----- ----- ------------------- ------------------------------
U1    P1    PACKAGE             DEBUG, EXECUTE
U1    P1    PACKAGE BODY        DEBUG, EXECUTE

If you revoke DEBUG now, the PACKAGE BODY row disappears.

SQL> conn u1/u1
Connected.
SQL> revoke debug on u1.p1 from u2;

Revoke succeeded.

SQL> conn u2/u2
Connected.
SQL> @yourquery

OWNER NAME  TYPE                GRANTS
----- ----- ------------------- ------------------------------
U1    P1    PACKAGE             EXECUTE


Deleting rows from child table without deleting from parent

As you can see that is not the case, when  the tables are not linked in anyway

CREATE tABLE earthquakes_ovr ("datetime" timestamp)

INSERT INTO earthquakes_ovr VALUES (NOW() - INTERVAL '6 DAYS')

INSERT INTO earthquakes_ovr VALUES (now() - INTERVAL'4 days')

CREATE UNLOGGED TABLE  earthquakes AS SELECT * FROM earthquakes_ovr

    CREATE OR REPLACE FUNCTION earthquakes_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO earthquakes VALUES (NEW.*);
        DELETE FROM ONLY earthquakes WHERE datetime < (now() - '2 days'::interval);
        RETURN NEW;
    END;
    $$
    LANGUAGE plpgsql;

    CREATE TRIGGER earthquakes_trigger
            BEFORE INSERT ON earthquakes_ovr
            FOR EACH ROW EXECUTE PROCEDURE earthquakes_trigger();

INSERT INTO earthquakes_ovr VALUES (now())

SELECT * FROM earthquakes_ovr

| datetime                   |
| :------------------------- |
| 2021-04-18 00:11:15.191671 |
| 2021-04-20 00:11:15.203608 |
| 2021-04-24 00:11:15.267616 |

SELECT * FROM earthquakes

| datetime                   |
| :------------------------- |
| 2021-04-24 00:11:15.267616 |

db<>fiddle here


Full text index for a column that stores json which contains guid - Is this a good idea, space-wise?

... a full text index for a column whose values are json ... usually contain Guid (or UUID) values.
Is it a good idea to build a full text index for this column?

I would say "No".
A full text index makes all of the words in the field available for searching.  I don't think that's what you want.  You want to be able to search for those Guid/Uuid values and, possibly, specific Request Types.
My preference would be to pull these values out into separate fields in their own right and index them.
Failing that, use a JSON-aware index type, which will be better, but still slower than the separate fields.


It's not the same advisory lock.  Your log mentions a different table oid (off by 10) for the first process as the second.  This looks like a classic deadlock situation where two processes are seeking the same locks just in a different order.  The third number in the first is 12864385 while the third number in the second is 12864395.

So my suspicion is that both processes are trying to create the same partitions, just in a different order and so they deadlock.

One thing I have found helpful in puzzling situations like this is to go through and compare one digit at a time all the way through the series of numbers because with advisory locks it is way too easy to miss one digit subtly off like this.


Generate ER Diagram with view permissions

I used SchemaSpy and it worked flawlessly.


What is a good practice for storing unstructured tables as a field in mongodb

The decision about what to put in the document is pretty much determined by how the data is used by the application.

The data that is used together as users documents is a good candidate to be pre-joined or embedded.

One of the limitations of this approach is the size of the document. It should be a maximum of 16 MB.

Another approach is to split data between multiple collections.

One of the limitations of this approach is that there is no constraint in MongoDB, so there are no foreign key constraints as well.

The database does not guarantee consistency of the data. Is it up to you as a programmer to take care that your data has no orphans.

Data from multiple collections could be joined by applying the lookup operator. But, a collection is a separate file on disk, so seeking on multiple collections means seeking from multiple files, and that is, as you are probably guessing, slow.

Generally speaking, embedded data is the preferable approach.


How to grant a user UNSAFE ASSEMBLY permission

Only users mapped to a login that's a member of the sysadmin fixed server role can create and alter an assembly with an UNSAFE permission set defined. Therefore, to achieve your desired result you would have to add that respective login to the sysadmin fixed server role.

BOL reference on CREATE ASSEMBLY:

  Specifying UNSAFE enables the code in the assembly complete freedom to perform operations in the SQL Server process space that can potentially compromise the robustness of SQL Server. UNSAFE assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE permissions should be granted only to highly trusted assemblies. Only members of the sysadmin fixed server role can create and alter UNSAFE assemblies.


Can't connect to EC2 Postgresql instance unless I change the default port 5432

Doing an nmap over the EC2 instance:

nmap -Pnv -p 5432 <public-ip>

revealed that my ISP is intentionally blocking the port 5432(third-world country FTW...):

Host is up.
PORT     STATE    SERVICE
5432/tcp filtered postgresql

It was never a problem with EC2 nor Postgresql in first place.


No. If it starts to swap, it means that MongoDB needs more RAM than the system has available. Limiting the RAM used would make the situation worse.

MongoDB uses only up to a certain limit (95% of physical RAM, iirc), which might lead to the wrong assumption that there is free RAM available and it must be a problem caused by MongoDB. Quite the contrary: MongoDB keeps the system usable for administrative tasks.

Solution: increase the physical RAM (scale up) or start sharding (scale out).


I would run DBCC CheckDB and see what corruption you have in the database.  It sounds like it is disk corruption.  If that is the case you will likely have to restore from backup.

After you restore from backup you can compare the two tables in question.

You can also try BCP'ing the bad table out but if it is a i/o disk error it will likely die if the corruption is in the heap.


Correct Code Page or Collation in SQL Server for Portuguese characters

You're not going to get the correct result until you tell BULK INSERT the code page of your .csv file. You tried ACP, RAW and OEM but you did not try the correct one. Which one is the correct one you ask? That would be something only you can answer, since you have the file and you (should) know what code page was originally created as! Perhaps  MS-DOS Portuguese?

the other alternative is to create the .csv file itself as an Unicode file, but that goes back to your source of the .csv file and depends how that file is created (you bcp out, you have it from a friend, was always used by the original program author, etc etc).

  the file is an Unicode one (UTF8 without BOM)

SQL Server does not support UTF-8 files for bulk insert. You must save your file as UCS-2LE or as ASCII with codepage 860. You can use libiconv to do the conversion if you cannot save the file again in the proper encoding.


SQL Server 2014: Using gMSA for xp_cmdshell Proxy Account?

However, it expects a password.

Correct, the password is encrypted and stored in the master database. When the account needs to be used, the password is decrypted and passed in.

  Is it possible to use a gMSA as the proxy account for xp_cmdshell in SQL Server 2014?

I don't believe so, and part of the reason why I included the quote about the password above.

The API calls used to do this expect a plain text password to be given. Since one does not exist for the gMSA in this instance the password would be blank. GMSA's do have passwords, but they aren't available in plain text and are base don generated values that rotate every so often, specified at creation time.

The code that does this expects a plain text password and does not check to see if the account is a gMSA or not, thus does not use any special API to retrieve the account password for storage.

My best guess would be, if we traced this all the way back to the calls you'd actually be receiving error 1326: The user name or password is incorrect.

Now What?

If you have very strong feelings that this would be helpful or that others would also like to use it, I'd invite you to create a Connect Item. If you do create one, please update your answer with a link to the item.


Forcing a LIMIT to MySQL Queries using ProxySQL, QueryRewrite, or some other method

Forget it.  There are ways to write "simple" queries, even with a small LIMIT, that can  take hours to run.

How, if you switch to MariaDB-5.5.21 (or later), there is a "LIMIT ROWS EXAMINED" that would be relatively effective in stopping runaway queries.

An example of where LIMIT does not help much:

SELECT ...
    FROM big_table
    WHERE lots_of_rows_kept
    GROUP BY one_column
    ORDER BY another_column
    LIMIT 1

It will

fetch lots of rows.
GROUP BY, possibly involving a sort of all the rows.
Sort again, this time for the ORDER BY.
Deliver one row.

That is, lots of time and effort went into steps 1,2,3; the final LIMIT had very little impact on the overall time.

Check out Proxy servers -- some of them might have a feature wherein they kill any process running longer than X seconds.

Query Rewrite  My old thoughts on Query Rewrite:  http://mysql.rjweb.org/doc.php/queryrewrite

What's the worst?

So, someone writes a long-running query.  It will hog some resources and slow down other queries, but most likely won't kill the system in any way.  Hopefully, he will be embarrassed and try harder next time.

Anecdotes

I have dealt with several time-series applications.  The first thing I do is build a web site to provide likely information.  And, behind the scenes, I build Summary tables so that queries against them are better (sometimes 10x) than against the raw (Fact) table.

The web pages present the data in an easier to read manner than the non-programmers can get via clumsy SQL.  And I can test them to see that they won't harm the system.  When I am finished, I have no fear of people pounding on my web pages that in turn hit a billion-row dataset.

Yes, I build in LIMITs in various places -- after all, who wants to scroll through a million-row web page (should it ever finish rendering)?  I even give them the ability to change the limit from the sane default I provide.  But they rarely do.

And I listen to their requests.  I try to quickly build whatever they ask for.  (This keeps them from demanding direct SQL access.)

Summary Tables are the key to success.  They don't want item by item results; they want sums/averages by day (or week or hour or ...)

It is easier for me to write the SQL than to explain the nuances of the table.

And, yes, there is always normalization, so JOINs are required.


How to get the free temporary memory available in a server

There is no such a thing in sql server as temporary memory. Temp tables and table variables created in tempdb and may use shared buffer pool of sql server as any other objects do

But

if you still insist on knowing everything about server memory use the following script:

set nocount on
declare @PI sysname
declare @PI_MemoryManager   sysname
declare @TotalServerMemoryKB            numeric
declare @TargetServerMemoryKB           numeric
declare @MemoryConnectionsKB            numeric
declare @MemoryLocksKB                  numeric
declare @MemoryOptimizerKB              numeric
declare @MemoryMinKB                    numeric
declare @MemoryMaxKB                    numeric
declare @MemorySortHashKB               numeric
declare @PhysicalRAMKB                  numeric
declare @qs_xp_msver table(
    [idx] [int] NULL,
    [name] [sysname] NOT NULL,
    [internal_value] [int] NULL,
    [char_value] [sysname] NULL
)
insert into @qs_xp_msver (idx, name, internal_value, char_value)
exec    master.dbo.xp_msver 'PhysicalMemory'
select  @PI = case when convert(sysname,ServerProperty('ServerName')) like '%\%' then 'MSSQL$' + @@servicename else 'SQLServer' end
set     @PI_MemoryManager   = @PI + ':Memory Manager'
select  @TotalServerMemoryKB    = sum(case when lower(p.counter_name) = 'total server memory (kb)'      then convert(numeric,cntr_value) else 0 end)
    ,   @TargetServerMemoryKB   = sum(case when lower(p.counter_name) = 'target server memory (kb)'     then convert(numeric,cntr_value) else 0 end)
    ,   @MemoryConnectionsKB    = sum(case when lower(p.counter_name) = 'connection memory (kb)'        then convert(numeric,cntr_value) else 0 end)
    ,   @MemoryLocksKB          = sum(case when lower(p.counter_name) = 'lock memory (kb)'              then convert(numeric,cntr_value) else 0 end)
    ,   @MemoryOptimizerKB      = sum(case when lower(p.counter_name) = 'optimizer memory (kb)'         then convert(numeric,cntr_value) else 0 end)
    ,   @MemorySortHashKB       = sum(case when lower(p.counter_name) = 'granted workspace memory (kb)' then convert(numeric,cntr_value) else 0 end)
from sys.dm_os_performance_counters p
where object_name  = @PI_MemoryManager
and lower(counter_name) in
    (
        'total server memory (kb)'
    ,   'target server memory (kb)'
    ,   'connection memory (kb)'
    ,   'lock memory (kb)'
    ,   'optimizer memory (kb)'
    ,   'granted workspace memory (kb)'
    )
and instance_name <> '_Total'
select  @MemoryMinKB        = sum(case when name like 'min%'        then convert(numeric,value_in_use) * 1024 else 0 end)
,       @MemoryMaxKB        = sum(case when name like 'max%'        then convert(numeric,value_in_use) * 1024 else 0 end)
from
    sys.configurations with (readpast)
where
    name in ('min server memory (MB)', 'max server memory (MB)')
declare @PhysicalRAM    numeric
select  @PhysicalRAM = ceiling(convert(numeric,
            substring(char_value, charindex('(', char_value) + 1, charindex(')', char_value) - 1 - charindex('(', char_value) ) )
            / (1.0))
from    @qs_xp_msver
if (@PhysicalRAM is null)
    set @PhysicalRAMKB = 0
else if (@PhysicalRAM < 0)
    set @PhysicalRAMKB = (convert(numeric,4096.0)*1024*1024 + @PhysicalRAM) /1024
else
    set @PhysicalRAMKB = @PhysicalRAM/1024
if (@PhysicalRAMKB < @MemoryMaxKB and @PhysicalRAMKB > 0 )
    set @MemoryMaxKB = @PhysicalRAMKB
set nocount off
select
        @TotalServerMemoryKB            as CurrentMemoryKB
    ,   @MemoryMinKB                    as MemoryMinKB
    ,   @MemoryMaxKB                    as MemoryMaxKB
    ,   @MemoryConnectionsKB            as MemoryConnectionsKB
    ,   @MemoryLocksKB                  as MemoryLocksKB
    ,   @MemoryOptimizerKB              as MemoryOptimizerKB
    ,   @MemorySortHashKB               as MemorySortHashKB
    ,   @PhysicalRAMKB                  as PhysicalRAMKB
    ,   @TotalServerMemoryKB            as TotalServerMemoryKB
    ,   @TargetServerMemoryKB           as TargetServerMemoryKB


SSIS cannot deploy package to SQL server - An error occurred during decryption 15466

i believe you have to encrypt it again using the ssisdb original password in this way:
open master key decryption by password = 'xxxxx' –‘Password used when creating SSISDB’
Alter Master Key Add encryption by Service Master Key


How do I check if PostGIS is installed in my PostgreSQL?

In psql CLI use \dx  to display all installed extensions in the current database or with SQL:

select * from pg_extension


Install Oracle Weblogic: Unsupported major.minor version 52.0

"openJDK" is not listed within the Supported Configuration documents.
https://www.oracle.com/middleware/technologies/fusion-certification.html

Use Oracle JDK.


File creation is the main part of the sluggishness, especially on Windows.  So, decreasing the number of files is key to speeding up your process.

Each MyISAM table needs 3 files.  Each InnoDB table needs 1 or 2 files.  Switch to InnoDB if not already using it.

Each PARTITION is a "table", so it counts as above.  More than about 50 partitions is inefficient, and this is one of the reasons.

Having more than a thousand tables is usually (not always) a sign of a poorly designed schema.

VIEWs, TRIGGERs, Stored routines, etc each take at least one file.

Parallel loading may or may not help; it depends on whether the OS can create multiple files in parallel.


How can we optimise this OR and ORDER BY query?

I've been reading dba stackexchange for a while now, and these kind of questions come up often, and they always kind of frustrate me.

People often seem to go straight to explain plans, and or looking for special indexes like they're magic bullets - but they're really not.

What you should get into doing (everybody) is thinking these problems through from a logical standpoint - if you, as a human, wanted to search through ~10 million lines of data to achieve this, how would you best organise the data to help you do this? That's all an index is, basically, is an ordering of the data. The computer does the work a bit faster than you, that's all.

By all means use those other tools and experimentation to help you with your understanding and refinement of yours skills, but don't use it as the primary go-to. That's a crutch.

Maybe I'm a bit of a idealist, and anyone who is more than a couple of years younger than me just isn't used to manually dealing with these kind of tasks, you just type them in to google - but think of how you might use a phonebook*

you don't open it at page 1, find Andrew Aardvark, ask youself "is this Walter Williams?" . no... next
line 2 - Arthur Abraham - is that Walter Williams? ... no.
line 3 - Alex Acer - Walter Williams? no
And so on.

No, you use an algorithm.

these were big fat books, made of paper, with everyone's phone number in. We had them in the 20th century.

So anyway, here's how I always approach your problem.

[1] Continue thinking of this problem like a phonebook.

[2] What columns could I sort the book by to help me search the data? - you're right in that it's those columns affecting the selection - columns in the WHERE clause, and columns in the ORDER BY

[3] So systematically... column by column

[4] mc.IsBarred - I didn't get as far reading your notes (IsBarred is mostly 0) before I disounted this. The name gave me the clue - this is presumably effectively a binary flag - Low cardinality data is generally not a good sorting column

Think of the phonebook. If we sorted the phonebook not by name, but by hair colour - even if we knew Walter Williams had red hair, and could home straight in on the 10% of the book containing just those guys (me included) it'd still take a while to search them one by one.

[5] mc.AllocatedCount or mc.MaxUses - no. The problem here comes from your comparing one column against the other. There's no ordering you could apply to the list by either column, or one then the other, which could zero you in quickly on to a small subset of data.

[6] mc.BatchID - this doesn' appear to be an unreasonable candidate. Although your code doeesn't actually show exactly how many values you're selecting from, or the cardinality of the data.

If you could search through just those sections of the phonebook relating to batch 446, and then those for batch 561, etc. That would certainly narrow the amount of searching you did - but you would still have to search through and compare every single row in that set, and then find the one which matches the rest of the clause, AND has the earliest date.

OK, we'll pencil it that column as a possible...

[7] mc.ExpiryDate - appears in both your where clause AND your order by ...

interesting...

If you sorted your phonebook by expiry date, you could turn to the page corresponding to 18:44 on 2018-11-13, and find the first record. If it matched all of your where criteria - you would have to look no further. Since it is by definition the earliest expiring date after that date - you know this is the record you're looking for.

If it's not, you have to look at the second row, then rinse and repeat. AS soon as you find a matching row, you know it's got the earliest expiry date and can stop.

This is the only potential indexing which can find you your single row in one attempt.

mc.ExpiryDate IS YOUR CANDIDATE FOR INDEXING

I have a few caveats.

[A] I'm not overly familiar with MySQL. Exactly how the query engine will interpret your code telling it to ORDER BY and then LIMIT, I don't know for sure. As a human I can think this through as detailed above - but whether MySQL will attempt to fetch all matching rows before finding earliest I can't say. I sesiously doubt it'd be that silly, but have no firm evidence.

[B] If you're adding 60,000 rows of data to the table in a week - that's 60,000 operations which will run marginally slower each time, as they have to maintain an extra index. Think through the implications of that.

There are always thade-offs with these things...

[C] If your where clause still filters out many many rows, the query may still have to search through many records. It depends a lot on the actual data. But that's where your own knowledge is key in this kind of situation. Use that!


How to call the unix Sendmail from oracle

Why do you want to use sendmail?  Is it just because it is simpler than UTL_SMTP?  You should really look into UTL_MAIL.

See this: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL

To send mail from an Oracle db, you might:

Use UTL_TCP, way too low-level, old school, not recommended.
Call Unix Sendmail (external program) using DBMS Scheduler. Kind of complex -- you need to setup credentials to run OS processes. Anyone who has tried this knows it's not trivial.  Technically possible, but why put yourself through the pain. I personally wouldn't do this.
Use UTL_SMTP, better than UTL_TCP, was the best approach pre-10g.
Custom mail proc that you write, which makes use of UTL_SMTP.  Okay for 9i, but for 10g+ you'd just be reinventing the wheel, so use UTL_MAIL instead.
Use UTL_MAIL, simplest approach on 10g/11g/12c
If you are really bent on using sendmail and would be open to an asychronous/batch solution, consider creating a DIRECTORY that Oracle can write text files in MIME format into (via UTL_FILE), then set up a cron job that runs every 5 minutes which processes these files through sendmail and deletes them if successful.


Can combine five mysql statements into just one statement?

update mytable set myfield1 =
replace(replace(replace(replace(replace(myfield1, '…' ,'-'),
'?' ,'|'),'”' , '"'),'‘' , '`'),'?' ,'"');


sys.sp_reset_connection. Why theres a lot of them ?

sp_reset_connection is an artifact of using Connection Pooling. And what you think you are seeing is not what is actually happening.

Connection Pooling is a feature of the client library more than of SQL Server. The client provides most of the functionality, while SQL Server itself mainly provides sp_reset_connection. When using Connection Pooling, the client driver does not actually close the physical connection to SQL Server when the app code calls the "close connection" method. Instead, the driver keeps the connection open and will link it to the next "open connection" request that uses the exact same connection string (and from the same Windows account, if using a Trusted Connection / Integrated Security). Now, because the connection was never closed, SQL Server knows only that the client is keeping the connection open, but not why. And because the connection is remaining open, the session still exists with whatever resources and settings were in place as of the last command / batch executed. This is why temporary tables created in the main scope (i.e. not in a stored procedure) will continue to exist (for details, please see: Sessions, Temporary Objects, and the Afterlife), and open transaction will stay open.

SQL Server doesn't know that connection pooling is being used until the next command / batch is submitted, at which point it executes sp_reset_connection. It is sp_reset_connection that cleans up the prior state of the session so that it will be just like a brand new session, which is what the client code is expecting (with a few minor items that do not get cleaned up / reset).

Of course, that description is based on the newer drivers. As the quote in the question states, older drivers sent a separate request for sp_reset_connection. The fact that you are seeing sessions that have no currently executing request (i.e. CPU is NULL) is expected when using Connection Pooling. What is not typical is seeing sp_reset_connection via DBCC INPUTBUFFER. This is not typical since the newer drivers request sp_reset_connection upon submitting the first batch / command upon reconnecting, so you would never see sp_reset_connection since it will never be the last thing to execute (though you can see it using SQL Server Profiler or Extended Events). But, if you are seeing it using DBCC INPUTBUFFER (which will continue to show the last command / batch executed, even after the batch completes), then that would appear to match the behavior of the older drivers (assuming that the older drives, because they are not sending a flag along with the next command / batch, are requesting sp_reset_connection upon "close connection" requests).

Either way, sp_reset_connection is not currently running. And the reason that the requests are all for the master DB is most likely do to master being the default Database for the Login(s) being used.


First add a row_number() value to the mix, because I'm guessing you can't rely on ID to have no gaps.

WITH NumberedRows AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Number, Name ORDER BY SubNumber) AS RowNum
  FROM [Table]
)

--now compare RowNum to SubNumber. When the difference changes, there's been a gap in the sequence of SubNumber values.

, Diffs AS (
  SELECT *, SubNumber - RowNum AS TheDiff
  FROM NumberedRows
)
, Ranges AS (
  SELECT Number, Name,
    MIN(SubNumber) AS StartRange,
    MAX(SubNumber) AS EndRange
  FROM Diffs
  GROUP BY Number, Name, TheDiff
)
, RangeStrings AS (
  SELECT Number, Name,
    CASE WHEN StartRange = EndRange
         THEN CAST(StartRange AS VARCHAR(10))
         ELSE CAST(StartRange AS VARCHAR(10)) + '-' + CAST(EndRange AS VARCHAR(10))
    END AS RangeString
  FROM Ranges
)

--And then just do your concatenation as you have already solved. :)

SELECT ...
FROM RangeStrings;


Alternative to CLUSTER without table lock

If it's just about table bloat, VACUUM FULL is the tool to do it. (VACUUM only shrinks the physical file if it can opportunistically truncate it at the end.) However, VACUUM FULL also takes an exclusive lock on the table, just like CLUSTER.

To do the same without exclusive lock pg_repack is the tool you are looking for. It's unfortunate that Amazon does not seem to allow it.

VACUUM returning disk space to operating system

Manual solutions introduce all sorts of corner cases and race conditions. You would need to specify your table and possible access patterns exactly before we can discuss the best solution.

Your current solution is no good either way. You do the sort operation after deleting all rows (locking them), which will not shorten the downtime (the duration of the lock) for SELECT queries.

If you have no concurrent write access and no depending objects, this might be better:

BEGIN;

CREATE TEMP TABLE tmp_target AS TABLE target ORDER BY field1, field2 DESC;

TRUNCATE target;
INSERT INTO target TABLE tmp_target;

-- DROP TABLE tmp_target;  -- optional; dropped at end of session automatically

COMMIT;

Sort in the background before taking the lock.
Strictly speaking, INSERT without ORDER BY is free to write rows in any physical order. Practically, though, it will copy the current physical order of the temp table with a plain SELECT * FROM (or TABLE for short).
Use TRUNCATE instead of DELETE, which is faster for big tables. Be aware of some implications like TRUNCATE does not work with FK constraints. Read the manual for full coverage.

You may want to drop existing indexes right before TRUNCATE and recreate after INSERT to make this faster.

Related:

Best way to populate a new column in a large table?
Why wouldn't VACUUM ANALYZE clear all dead tuples?
Configuring PostgreSQL for read performance


If there can be more than one author / editor / translator per book - like it is in real life and like your relational design suggests, then the existing answer with plain LEFT JOINs will produce incorrect results. If any book could have at most one person for each of the roles, you could radically simplify your design: no junction tables needed, just a foreign key column in the table Books.

You need to aggregate authors, translators and editors. You could do that after joining all rows (and producing mulitple resulting rows), but it should be more efficient to aggregate in the subqueries and then join to the Books table

SELECT B.BookTitle, BA.Authors, BT.Translators, BE.Editors
FROM   tblBook B
LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Authors
    FROM    tblBookAuthorJunction J
    JOIN    tblPerson P ON J.AuthorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BA ON BA.BookID = B.ID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Translators
    FROM    tblBookTranslatorJunction J
    JOIN    tblPerson P ON J.TranslatorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BT ON BT.BookID = B.ID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Editors
    FROM    tblBookEditorJunction J
    JOIN    tblPerson P ON J.EditorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BE ON BE.BookID = B.ID
WHERE  B.ID = 123

In most RDBMS it will be faster if you add WHERE BookID = 123 to the subqueries, too.


Cardinality showing the nature of relationships among tables

The code you've presented satisfies the requirements.

If we think about querying the tables starting from each "side", we can see this. (Assume the supplied parameters are suitably valid.)

Return all instructors in a given department:

SELECT i.*
    FROM department d
    INNER JOIN instructors i ON i.dept_id = d.dept_id
    WHERE d.dept_id = @departmentId;

We specify a value for the primary key of department, which restricts rows from that table to just 1 row. When we join to instructors, we need to find all instructors with that dept_id, which could be all of them, none of them, or somewhere in between. For one department, we can find many (or, more accurately, zero-to-many) instructors. (Note: yes, the query can be simplified; I'm just trying to demonstrate a point.)

Return all departments for a given instructor:

SELECT d.*
    FROM instructors i
    INNER JOIN department d ON d.dept_id = i.dept_id
    WHERE i.inst_id = @instructorId;

Again, because we specify a unique value for the primary key, this restricts the rows returned from instructors to just 1 row. In that single row, there is exactly 1 dept_id. Because dept_id is also unique in department, when joining to that table, we can only get back a single department. For one instructor, we will find exactly one department.

If you follow this process for all the relationships you create, it's easy to figure out if the requirements/design specs are met or not. You'll start to see these more easily (without writing queries) once you get more experience under your belt.


Compare Validate TSQL Data Warehouse to SSAS

If you wish to run MDX within SSMS then I believe OPENROWSET is your only option. Read about it carefully as there are some permissions/configurations that you must change to enable it, and they may affect your security.

If you do not have to use SSMS then you could use any programming language that can call the database drivers which communicate with SQL Server and OLAP, such as ADOMD and ADODB. You mention C# which would be perfect - use Microsoft.AnalysisServices.AdomdClient namespace. Or even a simple VBScript file could work if you want a quick and simple solution on Windows, just for short-term testing.


Intermittent Login Issues with Windows Authentication

SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. No authority could be contacted for authentication.

If you cut up the HRESULT listed, as the error indicates, it maps to:
SEC_E_NO_AUTHENTICATING_AUTHORITY

FAILURE (1), FACILITY_SSPI (0x9), Code 0x311

  Failure Information: Failure Reason: An Error occured during Logon. Status: 0xC000005E Sub Status: 0x0

If you cut up the HRESULT listed in this one, it maps to: STATUS_NO_LOGON_SERVERS

Both of these error are fairly indicative that the domain controllers and infrastructure around them should be investigated. Additionally if it's a single server having the issue, look at recent patches or group policy items that have been pushed, recently, especially due to OU changes or membership changes. If others on the same network are having issues, network hardware or improper domain controller resources might be an issue.


How to handle versions of an Entity in Database Design

You could keep them all in the same table, simply with a column to track version numbers:

documents
---------
  id (part of PK)
  version_number (part of PK)
  title
  file
  creator_id (FK to users.id)
  version_date (the date that this version is created - not necessary, but good to have)
  file

Your application will need to be capable of handling updating the version number of a document when a new record is added but it is a new version of an existing document. You also need to ensure that any document metadata (such as Title) changes are propagated to all related documents. You could do this by allowing document ID to not be unique, and the primary key is a combination of document ID and version number. Otherwise, it could be impossible to tell which record to update.

Another way would be to have a stub for document metadata and a separate table for all versions:

documents
---------
  id (PK)
  title
  creator (FK to users.id)
  (other metadata)

document_versions
-----------------
  id (PK)
  document_id (FK to documents.id)
  document_version_number
  file
  (other fields)

The second option is a slightly more complicated structure, but will give you better referential integrity because all version must be linked to a document stub by a foreign key.

Personally, I'd go with the second version.


Capturing datetime of change in SQL Server CDC

Remember that CDC uses a log reader agent to populate the change table. Why is that important? By that mechanism, rows show up in the change tables asynchronously to the changes made in the base tables.

There are actually 3 different time points that can be recorded, in reverse chronological order:

The time the change was delivered to the change table (which is what you're recording).
The time the transaction that contained the change committed (using cdc.lsn_time_mapping).
The time with which you manually populate a column in the base table (using a default constraint, a trigger, etc.).

So the first thing is to be clear on what you want to record. Usually we would care about either #2 or #3.

If the LSN mapping mechanism (#2) isn't performing well enough for you, the only supported alternative is to add a column to the base table and populate it yourself (#3).

With regards to changing the internal tables, as a matter of policy, I think it's best to avoid hacking around with internals when there are supported alternatives. The very last thing you want is an important production system going down, needing to call Product Support, and being denied service because of something like this. Never mind the issues of it potentially breaking things (upgrades), or being broken because it was unexpected (turn CDC off, then on again, as mentioned in the other answer).


SQL server permissions during installation

I will say up front that I do not know why your company would have policies that lock down the OS so much that a Microsoft product cannot be installed and provided the appropriate permissions during an installation. I would think they might be doing that backwards. I supported the military as a contractor for many years and that included secured networks, we did not lock down the OS until after all software was installed on the server. We at most wanted to ensure the software installed itself appropriately without any issue.

Outside of that, it is going to depend on what permissions you are not allowing to be set. If it is being restricted on what OS level permissions are set within the local group policy that are going to prevent the services from starting the installation will likely fail.

Shorting SQL Server services the documented permissions required to operate might affect your supportability with Microsoft. Just something to keep in mind.

Edit/Update

There is no in-depth doc that is going to go through what all the installation process does on a server. You can weed through the installation log under the default directory here: C:\program files\Microsoft SQL Server\110\Setup Bootstrap\Log. The 110 would be based on which version of SQL Server you are working with: 100 = SQL Server 2008/R2; 110 = SQL Server 2012; 120 = SQL Serve 2014.

I don't know anyone has gone through and determined the outcome of each little affect of an OS level permission not existing. All that is needed to understand is that permissions assigned according to documentation show what is required for a supported installation of SQL Server. You are basically going to experience sporadic issues based on what you need to do. Every permissions has a purpose, you just may not have reached or hit that purpose as of yet.


solved it by my own. Not optimized, but helpful for me. I insert my calculations into temp table, and add there a column with current date. On a script start I'm checking if table is older then 30mins, and if so, I drop it and crwate again.


MySQL INSERT INTO SELECT vs BULK INSERTs performance

You have to go with the BULK INSERT.
WHY NOT INSERT INTO TABLE2 SELECT * FROM TABLE1 ???
Running INSERT INTO TABLE2 SELECT * FROM TABLE1 requires a single transaction.
Imaging how populated an undo log will be to perform a single rollback.
If that transaction fails and rolls back, you create lots of table fragmentation.
Why BULK INSERT manually ???
This takes a lot of pressure off the InnoDB Storage Engine for holding large undo information.
EXAMPLE : mysqldump
Have you ever noticed when reloading a mysqldump, hundreds or thousands of rows at a time are being inserted ? If you grep a mysqldump like this:
grep "^INSERT" dump.sql

You will see many lines with INSERTs. Each INSERT is an extended insert by default. That allows 100's of rows to be inserted per INSERT command. So, the principle you already suggested of BULK INSERT 10K rows at a time is perfectly acceptable.


How can I find the stored binary value of a DOUBLE value in MySQL?

A (very) poor man's strategy is to simply:

create a table with a known float value
perform a hex dump of the table ibd file
test against a hypothesis of how the value is stored

I've tested against the IEEE754 format value, but couldn't identify the value.

I've created the table adding a string value in the same record as the float, and I've actually found that the float storage format is actually IEEE754, but it's little endian.