Difference between all_nodes VS cluster_nodes CouchDB 2.X cluster membership?

Your question has possibly been answered (in part) in the Server Fault Q & A:

BigCouch all_nodes vs cluster_nodes

The official documentation states that when you add a node to the CouchDB cluster, the output of the query _membership will return a list of all_nodes and cluster_nodes. No indication that this information may differ unless you account for:

  to see the name of the node and all the nodes it knows about and are connected too.

...which slightly implies that there may be differences.

The linked Server Fault answer refers to Robert Newson, who is a developer of CouchDB:

  I just heard from Robert Newson on IRC that BigCouch nodes are connected lazily.

I guess that is a quite an authoritative answer in that case.

When you request the _membership page, CouchDB might have connected all the nodes, or it might not have. This would explain why you noted differences in the all_nodes and cluster_nodes listings.

In your specific case where all_nodes contains more information than cluster_nodes the cause is explained in the official documentation:

  all_nodes are all the nodes thats this node knows about.
  cluster_nodes are the nodes that are connected to this node.

You had a situation where the node you were querying was aware of other nodes (all_nodes), but none of them were part of your cluster (clustered_nodes).

It seems like only the developer or the community on Github could answer your question completely. Consider asking your question on Github, or opening an issue on the apache/couchdb Github page.


Does Hasura (GraphQL) support multiple databases?

Nope, It's not supported at the moment. You would have to create as many Hasura instances as the number of databases you have. Then use one as the main endpoint and add the others as remote schemas.


Disabling binary logging when restoring a compressed MySQL dump

I found this answer.

https://geert.vanderkelen.org/2009/disabling-binary-logging-when-restoring-a-mysql-dump/

bash $ (echo "SET SESSION SQL_LOG_BIN=0;"; cat dump.sql) > dump_nobinlog.sql

A better solution would have been the following.
Taken from the comments on the above site.
But as the volume of data is rather large I don't want to spend another few hours waiting for the data to be exported. This also does not include compress of the file at dump time.

$ echo "SET SESSION SQL_LOG_BIN=0;" > dumpfile
$ mysqldump .... >> dumpfile

I have adapted it as follows.

echo "SET SESSION SQL_LOG_BIN=0;" | gzip | zcat - /somewhere/withspace/dump/somelargedb.sql.gz | mysql -u root -p somelargedb


Why would SQL Server remain unresponsive after a slow query completes?

Multi statement table valued functions are well known performance killers. I think the change you introduced had the beneficial side effect of turning your table variable from heap to clustered index. Other than that, I see no other difference between the two implementations.

I have no precise explanation of what could have "freezed" SQL Server, but that unresponsiveness is usually associated with high CPU. Did you take figures of what the CPU looked like during that period?

Another thing maybe worth investigating is the number of cache objects created in tempdb. Paul White has a great post on the subject on his blog. I would expect the numbers to be unchanged between heaps and CIs to be honest.

Another thing worth looking into is the preminent wait class during the "freeze". That could tell a lot about what's going on behind the covers. If you want to collect it over time you can use the Data Collector or a custom script.

BTW, if you managed to turn that function into an Inline Table Valued Function, that would probably help with performance.


How to pass value to ON CONFLICT clause that's not a column?

Not possible, because
There is currently no direct way in Postgres 9.6, because:

In the UPDATE, only the special EXCLUDED row is visible (in addition to the updated row). There is no FROM clause allowed to join in additional tables.

The EXCLUDED row is exactly the state of the would-be inserted row that was rejected by conflict. Exactly the columns of the table to be inserted into. Nothing more.

So there is no room for any additional columns not mentioned in the INSERT in the UPDATE branch of the UPSERT. Future version may allow more. Various additions have been discussed, but not yet implemented. It's a complex matter.
Clean workaround
I suggest this workaround using CTEs.

Provide rows with all columns you need in a free-standing VALUES expression. (CTE data in below example.)

Run INSERT ... ON CONFLICT ... DO UPDATE, but do not actually update any rows (WHERE FALSE). The desired side effect: excluded rows are locked anyway to make it safe for concurrent use.
With just ON CONFLICT ... DO NOTHING, conflicting rows are not locked and fair game for concurrent transactions. If there cannot be concurrent writes to the same rows of the same table, use this cheaper variant.

Run a separate UPDATE as part of the same command, where you can use all columns from the CTE data - and join in arbitrary additional tables, too, if needed.

Demo table:
CREATE TEMP TABLE tbl (tbl_id int PRIMARY KEY, note text);
INSERT INTO tbl VALUES
   (1, 'old1')
 , (2, 'old2');

Demo query:
WITH data (tbl_id, note) AS (
   VALUES
      (int '1', text 'update')   -- explicit type declarations for free standing VALUES
    , (2, 'no update')           -- going to exclude row in WHERE of UPDATE
    , (3, 'insert')              -- not going to INSERT the "note"
   )
, ins AS (
   INSERT INTO tbl AS t (tbl_id) -- "note" not inserted
   SELECT tbl_id FROM data
   ON     CONFLICT (tbl_id) DO UPDATE
   SET    note = NULL
   WHERE  FALSE                  -- never executed, but locks row.
   RETURNING t.tbl_id
   )
UPDATE tbl t
SET    note = d.note             -- now we can!
FROM   data d
LEFT   JOIN ins i USING (tbl_id)
WHERE  i.tbl_id IS NULL
AND    t.tbl_id = d.tbl_id
AND    d.note <> 'no update';    -- just to demonstrate use in WHERE

Result:
TABLE tbl ORDER BY tbl_id;

 tbl_id |  note
--------+--------
      1 | update   -- updated
      2 | old2     -- not updated because of WHERE in UPDATE
      3 |          -- inserted without "note"

Related:

How to include excluded rows in RETURNING from INSERT … ON CONFLICT


In Sql Server what is the difference between VAS and Buffer Pool?

No buffer pool and VAS are not the same thing. In SQL Server direct physical memory access is not allowed any memory request which comes is first mapped to process VAS and then if SQL Server find memory free it would map this VAS address to physical memory and then memory becomes committed.

Bufferpool is physical memory VAS is virtual memory.

VAS

VAS is Total amount of Virtual address space visible to process. Total VAS in windows OS is divided into VAS for OS process and VAS for application. SQL Server runs as an application. For 64 bit system total VAS is 16TB. So now both SQL sever and Windows process gets VAS of 8TB.

Whenever a new process wants to read data or write data into memory  it would reference memory in its VAS region so the new process would see VAS of 8TB and then it would be mapped to physical memory. This physical meory would be your buffer pool

Buffer pool

A buffer is an 8 KB page in memory, the same size as a data or index page you can consider buffer as a frame which holds data and index pages when they are brought from disk to memory.   SQL Server buffer manager manages the task of reading data pages into buffer pool and also writing it to disk. It is a reserved memory store for SQL Server and by default if you do not set value for it it will take as much memory as possible. Buffer pool only allocates memory to requests which requires less than 8 KB pages this feature changed in SQl Server 2012 where buffer pool has no meaning its just consumer. It is easy to allocate small contiguous amount of memory than large contiguous amount( Large contiguous amount might not be free or present to allocate).

32 Bit system

In 32 bit system SQL Server has VAS of 2G. If system is WOW it would have VAS of 4 G. So this becomes limitation for process which requires huge amount of continuous memory and such memory is not satisfied from buffer pool but outside buffer pool.

  "..MultiPage allocation happens in the VAS Area and Single page allocation comes from Buffer Pool..."

This statement is only true for SQL Server 2005 to 2008 R2. Page allocation >8KB is not done by buffer pool. The allocator which does page allocation for buffer pool is single page allocator while allocator which does allocation for memory pages >8KB is multi page allocator and  memory to this is allocated outside of buffer pool.

For SQL Server 2012 there is Any page allocator which does allocation for both pages <=8KB and pages >=8KB


Microsoft Key Storage Provider for Always Encrypted Master Key

How can I remove the "Always-Encrypted-Auto1" entry?

Since there isn't a way in the SSMS GUI and I doubt you want to write your own code to find out, look in %APPDATA%\Microsoft\Crypto\Keys and attempt to figure out based on date which file it may be.


Today I had to grant sysadmin rights for 2 users.

Why?

There should be no Good Reason for anyone (except you and your Team) to have this level of access to any database.  If they're having problems that they cannot resolve for themselves, then they should be working with you to solve them, not working around you and leaving you with little or no chance of fixing anything that they might break.

Always keep the biggest and best tools in the toolbox for yourself.

Trying to Audit users with this level of privilege is pointless - whatever you do, they can undo and, if they do manage to bring the whole instance crashing down, there won't be much Audit logging worth looking at anyway.

Also, bear in mind that it is your job to keep the database running, not their's.  They are only interested in fixing their immediate problem, not necessarily in the overall performance and efficiency (or even continued operation) of the whole database.

At the very least, you should revoke this privilege as soon as practically possible.  if you can, do it tonight before you go home so that they have to request it again tomorrow.  Also, consider raising it to Management as a formal Risk to the Business Continuity Plan.


I would use LIKE for one very good reason. From the documentation here - "This function (FIND_IN_SET) does not work properly if the first argument contains a comma (“,”) character".

You could always use an EXPLAIN  to check on performance - but simply for the reason above, I would not use it.

[EDIT]

You could always use LOCATE(). I strongly recommend that you don't use MySQL's SET datatype. It's non-standard and illogical to say the least.


Is it a good idea to schedule a daily CHECKPOINT?

It's hard to know this for sure given the limited information here, but I don't think you're quite down to root cause yet.

Why did a huge spike in disk activity make you think the root cause was a checkpoint?
Why would the best fix be running a checkpoint manually?
Have you checked into which queries were running at the time, and what their wait stats were?

Checkpoints already happen much more frequently than daily - the default in 2016 is every minute.

So to answer the question:

Adding a daily checkpoint would be a bad idea and ineffective
Consider doing more troubleshooting like checking the server's wait stats
For a followup, include server config and wait stats info in your question (disclaimer: that's my blog post)


Returning Multiple Result-Sets or Single XML Blods

I prefer multiple resultsets because its faster and uses less memory and much easier to deal with.

But everything depends on consumer of the data - for example if it can receive only XML - you have no choice 8-)


This results in unused sequence values

That's totally normal, and by design. It's also not the only way you can get such gaps. See the note on nextval in the manual.

  Is there any way to reclaim the lost sequence values

No.

  or prevent the failed function from incrementing the sequence?

Don't use a SEQUENCE. Instead, if you must have gapless values, use an ID-generating table via UPDATE ... RETURNING. See gapless sequences for more info.

Note that it's not possible to have concurrent inserts without possible gaps. You get one or the other. So think very hard about whether you really need gapless.

For most applications you should just use a SEQUENCE (SERIAL) and design for the fact that gaps are possible and normal.


One to many relationship query result in a single row and dynamic multiple columns

The query below returns exactly what you described in your question but I doubt this is what you really need.
SET @sql = NULL;
SELECT GROUP_CONCAT(
 CONCAT('(SELECT numbers FROM phones WHERE owner_id = ',id, ' ORDER BY numbers LIMIT 1 OFFSET ',rn - 1,' ) AS numbers', rn)
 ) INTO @sql
FROM (SELECT o.id, p.numbers, @rn := @rn + 1 AS rn
FROM owner o
INNER JOIN phones p ON o.id = p.owner_id
CROSS JOIN (SELECT @rn := 0) r
WHERE o.id=1) phones;
SET @sql = CONCAT('SELECT id, ', @sql, ' FROM owner WHERE id = 1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2f7ec41adaf2acff5ca04ed93276fc3d
Query to return records for all owners:
SET @sql = NULL;
SELECT GROUP_CONCAT(
 CONCAT('(SELECT numbers FROM phones WHERE owner_id = o.id ORDER BY numbers LIMIT 1 OFFSET ',rn - 1,' ) AS numbers', rn)
 ) INTO @sql
FROM (SELECT o1.id, p.numbers, @rn := @rn + 1 AS rn
FROM owner o1
INNER JOIN phones p ON o1.id = p.owner_id
CROSS JOIN (SELECT @rn := 0) r
WHERE o1.id=(SELECT owner_id FROM (SELECT owner_id, COUNT(1) AS cnt FROM phones GROUP BY owner_id) c ORDER BY cnt DESC LIMIT 1)) phones;
SET @sql = CONCAT('SELECT o.id, ', @sql, ' FROM owner o -- WHERE id = 2');

-- SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=0f5f425c11fa45bcc202a3ed116d5033


Deadlock resource when try to drop datafile

This error is produced for various reasons stated below:

  Too high activity - Re-running the job during a less busy period can
  fix this ORA-00060 deadlock error. Poor application design - As noted
  in the trace file:  "The following deadlock is not an ORACLE error. It
  is a deadlock due to user error in the design of an application or
  from issuing incorrect ad-hoc SQL."

  ASSM - This error can happen when the target tablespace is using
  "segment space management auto".  See these notes on deadlocks with
  ASSM.  Beware, ASSM can cause huge deadlocks, because ASSM does not
  perform well under heavy DML loads.

  RAM shortage - In rare cases this ORA-00060 deadlock error can happen
  with a RAM shortage at the Java layer or Oracle layer

One way is to use the trace file to see what is causing the deadlock.

The most common reason for which such deadlock situation occurs is that some resource(s) might be using the datafile that you want to drop.

The best possible solution is to close/terminate all the sessions using the resources on the mentioned datafile and then delete the datafile.

Another solution is by shutting down the database and then making the necessary changes.

There are many ways/queries to find the SID(Session ID) which is causing the lock. Please refer the website for some examples -->http://www.oraclerecipes.com/monitoring/find-blocking-sessions/

Analogy in Windows: It is similar to renaming/deleting a file when in use.
Hope this solves your problem !!


How to return the difference (columns) between two databases or tables

You can use the applications listed in comments, or use SQL Server Data Tools. What I like about SSDT is it is a native SQL Server development environment, works well with source control, and has a really good set of features for database development.

You should be able to install from your SQL Server 2014 media or just download it. You can then use Schema Compare between database projects or deployed databases. In the example image I have compared two dev servers, and after selecting a Procedure that has a detected difference, I can inspect what has changed. You can script out the update and inspect or edit it or simply push the change to the target server. You could also import either into a database project and put it under source control.


RAM usage is increasing in database server with time

SQL Server will try and use all the RAM it can get hold of to reduce execution times on queries.

As you have had it running for a day or two, with the increased amount of queries ran it will be using as much RAM that it has available to it.


Writing a query to COUNT all the services involving a particular

select count(distinct service_id)
from staff
where staff_firstname = 'Gary'
  and staff_surname = 'Crowley';

The distinct is only necessary if your database design allows to assign the same service multiple times to the same staff member.


SQL Server full safety mirror commit overhead less then Time to send?

Database Mirroring Monitor (Status Page):

  Time to send log (estimated)
  Approximate amount of time the principal server instance requires to send the log that is currently in the send queue to the mirror server instance (the send rate). Because the rate of incoming transactions can vary significantly, the time to send log is an estimate. However, the send rate can be useful for roughly estimating the time required for a manual failover.

The time to send measures the time to send all the pending log. It only applies if there is a pending log (eg. the principal has run exposed and mirror is in 'synchronizing' state). Notice that in this state the principal does not wait for commits to be acknowledged by mirror, hence the commit overhead is significantly lower (1-5 ms) than when running in 'synchronized' state (20-40-80ms).


In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key?

In short, no. A primary key by definition requires uniqueness, an index on the primary key field is the database engines route to enforcing this constraint.

From BOL:

  When you specify a PRIMARY KEY constraint for a table, the Database
  Engine enforces data uniqueness by creating a unique index for the
  primary key columns. This index also permits fast access to data when
  the primary key is used in queries. Therefore, the primary keys that
  are chosen must follow the rules for creating unique indexes.


Will MySQL be effective when selecting a row from a table with billions of records?

Let's do a hypothetical answer to a hypothetical question (all of this are ball-park calculations, I do not intend to be very exact, but to have a big picture):

Your table, at the bare minimum, will have a storage requirements of:

50 000 000 000*(8 + 8 + 1 + 1 + 20*3) / 1024^4 ~ 4TB

I do not have into account the TEXT because, even if it can be inline, if it is too big it goes off-page (so funnily enough, it won't be a problem or a performance penalty unless it is selected or modified).

The secondary indexes (I do not have into account the primary key, as the data is clustered around it, but obviously, it will take even more space than just the data) will take at least:

50 000 000 000 * ( (8 + 8) + (8 + 1) + (8 + 1 + 20 *3) ) ~ 4TB

And that is being too optimistic.

As that cannot be realistically into the main memory (largest deployments of traditional SMP architectures can only integrate around 4TB, I do not consider other architectures with its own problems), a single access though primary key may require ~25 theoretical disk hits (double it for secondary keys), which with fast enough disks (a huge RAID of SSDs) and disabling large read-ahead caching to the buffer pool and other tweaks, in a single-thread read-only load: why not?

The problem is that InnoDB was designed assuming that the buffer pool being able to cache at least the most used/upper parts of the primary key, which -assuming you can select any row of the table- would create an excessive amount of traffic between the main memory and disks. With the more and more popular flash drives, some parameters of InnoDB have been tuned to allow faster and faster disk access.

In fact, in reality your biggest problem would be inserting. You probably won't be able to have such a tall table due to the increasing performance penalty of checking for uniqueness on the PK for every row inserted. That is why InnoDB works very well when the working set is mainly on the buffer pool, but not when it goes beyond that. Additionally, structures like the change buffer, transaction log etc. would be easy overloaded on insertion. You can see a big slowdown on the blue line once a threshold is surpassed:

Note: the previous image stops inserting into InnoDB when the table it reaches ~60GB.

In a nutshell, InnoDB is not designed for that kind of operations. Solutions? Splitting the table is usually the number one call- specially because partitions tend not to be access with equal frequency. Compression could help with lower storage requirement, but it may change the bottleneck to the CPU on inserting, and it may make things worse regarding caching.

Probably the best option would be to go for a different engine, which works well for lots of rows that is disk-bound. TokuDB is one of the engines that has been heard a lot lately that tries to cover that market gap, and has some interesting benchmarks.


Is it possible to track the progress of when I drop a the primary key clustered index on a table?

If you are using Enterprise Edition or equivalent, you can drop the primary key ONLINE:
ALTER TABLE Production.TransactionHistory
DROP CONSTRAINT PK_TransactionHistory_TransactionID
WITH (ONLINE = ON);

This will allow you to monitor progress using the Profiler Event Class Progress Report: Online Index Operation or the Extended Event:

progress_report_online_index_operation
Occurs during an online index build process to indicate the progress of the build operation. Each stage of an online index build operation generates an event. Use this event to monitor online index builds.

It provides the following event fields:


SQL Server database tries to restore itself automatically (and gets stuck)

I have a feeling that what you really  have is "recovery pending" and not "restore pending". If you look at the documentation for sys.databases, the state column, you will see that there isn't a state called "restore pending". So, I will assume it is indeed "recovery pending" below. If it is anything else, please let us know, show is what it look like in SSMS Object Explorer as well as the state column from sys.databases.
Recovery pending means that SQL server couldn't do recovery at startup of the database. Note that start of the database can happen outside startup of SQL Server. Having the autoclose database option set to true is one such case.
Anyhow, whenever recovery isn't doable, the database goes into recovery pending. But SQL server will document why, in the errorlog file. So your task is to hunt down these error messages and act on them. My guess is that any of the database files are used by some other process (anti-virus, snapshot thingie, etc). But at a later time, the files are accessible to SQL Server.


Why can't object names start with a number?

Firstly, you need to distinguish between numbers (numeric literals), strings (string literals) and identifiers. '4aii' is a string literal, which can be a value of some "thing", but it does not identify (name) a thing. 4aii or [4aii] would be identifiers (if it were allowed).

The query parser needs to understand the meaning of a token that it is looking at. By allowing names to begin with digits, you by extension allow them to consist of digits exclusively. Then, given select 12345 from mytable, how would you (and the parser) know if 12345 is an integer literal or a name of a column?

However, if you allow identifiers to begin with letters only (or underscore characters), you can unambiguously say if you're looking at an identifier (abc123) or a string literal ('abc123') -- the latter is enclosed in quotation marks.

Square brackets in SQL Server, backticks (`) in MySQL, and double quotes in ANSI SQL-compliant engines, signify identifiers, and you use them for when your identifiers cannot be readily distinguished from other tokens: start with a digit, have spaces or other special characters in them etc. Hence, [4aii] or "4aii" clearly tell the parser that it's dealing with an identifier.

A little dbfiddle demo.


Method to keep track of large number of sets with high cardinality

Optimal table size would be environment specific. I had an almost exact similar environment.  A billing/tracking/logging system was generating 2-3GB of data per day and was already at 1.5TB in 1 table alone.  An ETL process fed data in so that was the only 'write', while thousands of users 'read' it.

It seems like you need to maximize both writes and reads like I did, so questions that comes to mind are what's your budget and what's the concurrency going to be like, as in how many requests per second on the same table so we can figure out if we also need to address locking.  Also, what are your disk arrays currently configured like and do you have any baselines?

Read this first.
Top 10 Best Practices for Building a Large Scale Relational Data Warehouse

Each point is critical for success, at least in the 1.5 TB nightmare I had inherited.  This article hits most of it on the head.  Remember that you can have 'historic' databases mapped by views, and keep the 'live' databases small by purging the to the historic databases often so backups and performance on most recent data is fast.

Concurrency:
Isolation Levels & Snapshot Level Isolation

This basically makes SQL Servers isolation level the same as Oracle.  Reads don't block writes, writes still block reads.  It comes at the expense of (I believe) 14 bytes added to each row and a copy of it stored in TempDB per update.  This means your tempDB is going to be a big huge bottleneck if it isn't optimized for this so watch the metrics carefully.

Writes:

To optimize writes you would want to optimize your transaction log first, IMO set the 'time to recovery' option to a higher number and let the lazywriter update the data when it can.  This would mean throwing lots of IOPS at the tlog for writes and keeping writes in the Tlog drive under 5ms during full load.  The reason we set the time to recover to a higher number is so the transaction log can update the data less, thus being less dependent on your disk arrays.

Record disk sec/writes on all your data/tlog drives.

Partitioning:
Efficiently delete data by using partitioning.  It's a metadata operation.

Reads:

If you have high cardinality you can vastly improve read performance through the transparent compression option in SQL Server, at the expense of writes... and it's a 'enterprise only' option, not sure if you have that coming from Postgres.

Try to keep base table data in memory.  Record disk sec/reads metrics to see how fast your responses are.

Ensure your queries are using partition elimination.  Kim Tripp has a great video of it here.

This is just a 'brief overview' without knowing more about your environment, the link to the data warehouse article is very good and covers a lot of what I wanted to say.  Feel free to give us more details or ask questions.


Specify ICU collations as `ENCODING`, `LC_COLLATE`, and `LC_CTYPE` in Postgres 10

First let's note that at the time of this answer, PostgreSQL 10 in still in beta stage. Some issues about the ICU integration and how it's documented are still under  discussion, and there might be changes before a GA release.

  ➠ How does one specify the ENCODING, LC_COLLATE, and LC_TYPE to use
  the new ICU collations?

It's not possible currently (and probably not documented well enough, or not clear enough in the error message: when it says invalid locale name, it means from the set of locales provided by libc).
This is discussed in this thread on the developers mailing list:
Can ICU be used for a database's default sort order?

  ➠ What ICU collation names you would suggest for English-oriented
  databases? For Western European multi-lingual databases?

It mostly matters if you have specific sort requirements. In this other thread of the mailing-list:
What users can do with custom ICU collations in Postgres
10
some concrete examples are given of ICU collations behaving specially compared to what libc can do.

When not having specific requirements, I think most applications won't care whether they use an ICU collation or a libc en_US collation, except for portability. One of the big points of ICU is to get exactly the same ordering across all operating systems for a given locale, which is really not the case of libc. I guess that the pre-created en-x-icu would be fine for general English, and for West-european languages, I'm not sure why any collation would  be better than und-x-icu.

As a side-note, it's not a good idea to use any other collation than C when you don't really care about the sort rules, because C as a collation will outperform any language-aware collation. And C is portable across operating systems.

  ➠ What are the possible ICU collation names?

Although initdb populates pg_collation with a list of pre-defined collations, a complete list would be subject to combinatorial explosion,
as ICU collation names are formed by assembling tags, as listed here:
http://unicode.org/reports/tr35/tr35-collation.html#Setting_Options

Currently the doc does not mention explicitly that you can do:

CREATE COLLATION mycoll (locale='my-set-of-tags', provider='icu');

but I think work is happening right now to improve that in the doc.

Another issue that might be worth pointing out is that there are multiple ICU major versions out there, and the list of pre-defined collations that initdb instantiates differs significantly between these versions, especially ICU pre-53 versus post-53. This has been put in evidence in another discussion:
Crash report for some ICU-52 (debian8) COLLATE and work_mem values

EDIT: in the next beta or RC, the list of ICU collations created by initdb will change thoroughly , as mentioned in this discussion. The list will made by iterating on ICU locales rather than collations, and as a result the question "What are the possible ICU collation names?" will have to be reconsidered with the new values.


how to set/change the font style in database?

When using SQL, you can use any characters almost: that is, the sysname datatype which is nvarchar(128)

Example (copied Hindi and Urdu text from Wikipedia)

CREATE TABLE मानक (
    اردو int NOT NULL PRIMARY KEY,  --SQL editor reads *only* the column name right to left
    foobar int NULL
    );
SELECT TOP 1 * FROM sys.objects O ORDER BY O.create_date DESC;
DROP TABLE मानक;

If you mean in the GUI, then this should be Windows locale dependent.

If you mean the GUI designers, then frankly I don't know because I use raw SQL.

If you mean the actual SQL language, then it is English

Edit: Under Tools.. Options, you can select fonts. This may be what you want:


Creating temporary table dynamically using condition

Supported?

I know this is tagged as 2008R2. Since that's officially out of support, perhaps an upgrade is in your future. If you end up on a version of SQL Server > 2012, you can use code like this:

CREATE OR ALTER PROCEDURE dbo.dynamic_temp ( @TableName NVARCHAR(128))
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #t ( Id INT );
    DECLARE @sql NVARCHAR(MAX) = N'';

    IF @TableName = N'Users'
        BEGIN
            SET @sql = @sql + N'SELECT TOP 10 * FROM dbo.Users AS u WHERE u.Reputation > @i';
        END;

    IF @TableName = N'Posts'
        BEGIN
            SET @sql = @sql + N'SELECT TOP 10 * FROM dbo.Posts AS p WHERE p.Score > @i';
        END;

    SELECT   column_ordinal, name, system_type_name
    INTO     #dfr
    FROM     sys.dm_exec_describe_first_result_set(@sql, NULL, 0)
    ORDER BY column_ordinal;

    DECLARE @alter NVARCHAR(MAX) = N'ALTER TABLE #t ADD ';

    SET @alter += STUFF((   SELECT   NCHAR(10) + d.name + N' ' + d.system_type_name + N','
                            FROM     #dfr AS d
                            WHERE    d.name <> N'Id'
                            ORDER BY d.column_ordinal
                            FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(4000)'), 1, 1, N'');

    SET @alter = LEFT(@alter, LEN(@alter) - 1);

    EXEC ( @alter );

    INSERT #t
    EXEC sys.sp_executesql @sql, N'@i INT', @i = 10000;

    SELECT *
    FROM   #t;

END;
GO

The idea is to use dm_exec_describe_first_result_set to determine which columns your result produces, along with their datatypes. We can use that to generate a dynamic ALTER TABLE statement to add those columns to a base #temp table created outside the scope of the dynamic SQL.

That makes inserting and selecting data from it pretty easy.

I was blogging about this when I remembered this question. Again, sorry there's nothing as easy for < 2012.


mongodb 2.4.1 instance shutdown without log

It looks like the assertion you are encountering is SERVER-9053: TTL index asserts on 2.4 secondary, which was fixed in 2.4.2. This appears to be a race condition where the TTL monitor thread would incorrectly try to apply TTL deletes on a secondary. The assertion does not appear to be fatal; if it was there should be a "Fatal assertion" logged followed by the  mongod process shutting down.

Unexplained shutdowns without MongoDB log messages are commonly the result of something external to mongod, such as the Out-of-Memory (OOM) Killer on Linux. You can usually find more info on activity such as the OOM Killer by searching your system logs.

Note: MongoDB 2.4.1 was released in April, 2013 and the 2.4 release series reached end of life in March, 2016. I'd strongly recommend upgrading to the final 2.4.14 release and then planning an upgrade to a supported version.


Checkdb giving error for master database

The FAT32 is the reason here. And you can't do a TABLOCK CHECKDB on the Master database. WITH TABLOCK puts the database in read only mode - you can't do that to the Master database because of system operations that required to run and run on the Master database.

You can verify this easily enough by creating a new user Database on the same file system and then trying a CHECKDB, seeing the same error and then doing a CHECKDB on that on with the TABLOCK option. My guess is you get the same error without the option and you do not get the error when specifying the option (as long as you don't have a connection open to the database by accident)

Unfortunately you are sort of stuck from being able to do regular CHECKDBs on Master until you resolve this.

In the meantime you can do this process ( a bit of a pain but it works)

Ensure all services that connect to SQL Server are off/stoped (this includes services like SSIS, SQL VSS writer, etc)
Restart SQL Server in single user mode using the -m startup parameter (described here)
Connect as the single user and then do your CHECKDB with the TABLOCK specified.

I just tried this on my SQL Server 2012 instance and it worked. Only way you can do this.

To me the big question here is - why is your file system FAT32? What OS is this on? Are all of your databases on FAT32? It is possible to be on FAT32, but the recommendation is to be on NTFS volumes. On FAT32 you don't get certain permission benefits that you get on NTFS - leaving some files open for manipulation and resulting problems, and obviously this CHECKDB issue.


Is it OK for the database to be completely overwritten for many times?

No corruption, no loss, no...  (Well, there is always the remote chance of a hardware failure, software bug, typo on your part, etc.)

Here is a 'better' way to completely reload a table:

CREATE TABLE new LIKE real;
load `new` by whatever means
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

That way, the table, real, is always available except for the brief moment when the RENAME is running.


MySQL - best practices/ how to speed up "update, else insert' queries? Insert + delete faster?

Can't comment yet, so posting as answer - you state in one comment that "not sure if there are ever duplicates within a given document anyway" - in that case (and in most other cases too) you should use INSERT ... ON DUPLICATE KEY UPDATE as it does not need to delete the existing row, so update overhead is only seen when the target row actually exists.

Syntax is not as simple as REPLACE INTO, but there are not "weird" side effects (because REPLACE works as DELETE/INSERT pair, it can break on foreign keys or delete referencing rows when you didn't want it to)


Calculate total attendance for each workshop

Something like this would give you the total across all three of those days:

SELECT COUNT(attendance.AttendanceID) as 'Total Attendance'
FROM student
INNER JOIN attendance ON attendance.StudentID = student.StudentID
  AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015')
WHERE student.WorkshopID = '101'

If you wanted the total separately for each of those dates, use this:

SELECT attendance.Date, COUNT(attendance.AttendanceID) as 'Total Attendance'
FROM student
INNER JOIN attendance ON attendance.StudentID = student.StudentID
  AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015')
WHERE student.WorkshopID = '101'
GROUP BY attendance.Date

Try this for overall attendance. Keep in mind I didn't have time to setup the schema, so this is untested:

SELECT wsdata.WorkshopID
  , wsdata.TotalAttendanceDaysPossible
  , adata.TotalAttendanceDaysActual
  , ((adata.TotalAttendanceDaysActual / wsdata.TotalAttendanceDaysPossible) * 100) AS OverallAttendance
FROM (
  SELECT ws1.WorkshopID
    , ws1.NumberOfWeeks AS TotalDays
    , COUNT(s1.StudentID) AS TotalStudents
    , ws1.NumberOfWeeks * COUNT(s1.StudentID) AS TotalAttendanceDaysPossible
  FROM workshop ws1
  INNER JOIN student s1 on ws1.WorkshopID = s1.WorkshopID
  GROUP BY ws1.WorkshopID
    , ws1.NumberOfWeeks
) wsdata
INNER JOIN (
  SELECT s2.WorkshopID
    , COUNT(a.AttendanceID) AS TotalAttendanceDaysActual
  FROM attendance a
  INNER JOIN student s2 ON a.StudentID = s2.StudentID
  GROUP BY s2.WorkshopID
) adata ON wsdata.WorkshopID = adata.WorkshopID
WHERE wsdata.WorkshopID = '101'


Adding a local user account into SQL Server

This has nothing to do with SQL Server, is just basic Windows authentication. In a workgroup you can only authenticate using NTLM mirrored accounts. Mirrored accounts are local accounts that have identical name and and password on different machines.

So to use Windows authentication between a process running on machine A and a process running on machine B, in a workgroup, you must:

create a local user A\foo on A
create a mirrored user B\foo, on B, that has the same password as A\foo
run the client process as A\foo on A
grant permissions to B\foo on B

I strongly suggest you deploy a proper domain and use AD instead.


This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY

WITH STANDBY is only supported when both SQL Servers are the same version.

You can only use WITH NORECOVERY

An alternative if you need read access to the destination databases, is to use replication.


Why is SQL Server Express creating twenty users on my system?

Do you have installed the R- and Machine Learning services? According to the documentation below those are generated to run external scripts of those services in an isolated environment. So I would say they are normal if you install those components.

Those are part of the Advanced Services Variant of the SQL Express Edition.

From the documentation: Modify the user account pool for SQL Server machine learning

  By default, the user account pool contains 20 user accounts. In most cases, 20 is more than adequate to support machine learning tasks, but you can change the number of accounts.

  In a default instance, the individual accounts are named MSSQLSERVER01 through MSSQLSERVER20.
  For a named instance, the individual accounts are named after the instance name: for example, MyInstanceName01 through MyInstanceName20.


Can I fail the current transaction from within a trigger?

IF @@ROWCOUNT > 0
BEGIN
   ROLLBACK TRANSACTION;
END
ELSE
BEGIN
  ... continue ...
END


Why would sp_helprolemember show different MemberSID for same member?

dbo is a user in a DB. When a user is created (whatever is name would be), it gets its own unique SID. If you later create a second DB and add a new user with a similar name (or not), it will get another unique SID. Both user on DB1 and DB2 have nothing in common and SID will be different.

  This was based on the unedited question about sp_helpsrvrolemember...

Look at the view sys.server_principals which is mentionned in the text below from the technet link:

When a SQL Server login is created, it is assigned an ID and a SID. These are visible in the sys.server_principals catalog view as principal_id and SID. The ID (principal_id) identifies the login as a securable within the server. It is assigned by SQL Server when the login is created. When a login is deleted, its ID number is recycled. The SID identifies the security context of the login and is unique within the server instance. The source of the SID depends on how the login is created. If the login is created from a Windows user or group, it is given the Windows SID of the source principal; the Windows SID is unique within the domain. If the SQL Server login is created from a certificate or asymmetric key, it is assigned a SID derived from the SHA-1 hash of the public key. If the login is created as a legacy-style SQL Server login that requires a password, the server will generate a SID.

https://technet.microsoft.com/en-us/library/ms403629%28v=sql.105%29.aspx


SQL Server Data File/Log File poor performance

Points:

RAID 5 is a poorly performing configuration for database files especially for writes, and
Partitioning is even worse.

Yes, it works and is reliable, but it's definitely a low-performance disk configuration that I would only use myself for development or the most light-weight of apps.  The standard configuration for the physical host of a SQL Server are separate physical volumes for the system and data disks using RAID 1+0.

I will note @Spörri's recommendation also: first, check to see if the RAID array is degraded because of a failed disk.


Indexes to add:

Table: candidates
  (job_id, ref_type)

Table: job_views
  (job_id, views)

Table: autopost_jobs
  (job_id, posts)

Then run again the queries and post the updated EXPLAIN


Is there a service broker target for an extended event session

There looks to be a service_broker target in SQL Server 2017 but I can't find any documentation on it. This target is visible with the latest SSMS GUI and this query:

    SELECT *
    FROM sys.dm_xe_objects
    WHERE
        object_type='target'
        AND name = N'service_broker';

Without documentation, I experimented using the script below but could not capture any events and saw no SB activity. The execution_count in sys.dm_xe_session_targets increments so it seems the plumbing is there. It could be an issue with my script or the target might not be fully implemented by SQL Server.

I'll see if I can find more info about the service broker target and report back here.

/*
DROP EVENT SESSION [service_broker_target_test] ON SERVER
DROP SERVICE InitiatorService;
DROP SERVICE TargetService;
DROP CONTRACT ExtendedEventMessageContract;
DROP MESSAGE TYPE ExtendedEventMessageType;
DROP QUEUE InitiatorQueue;
DROP QUEUE TargetQueue;
*/

CREATE QUEUE InitiatorQueue;
CREATE QUEUE TargetQueue;
CREATE MESSAGE TYPE ExtendedEventMessageType VALIDATION = NONE;
CREATE CONTRACT ExtendedEventMessageContract (
      ExtendedEventMessageType SENT BY ANY
    );
CREATE SERVICE InitiatorService
    ON QUEUE dbo.InitiatorQueue (ExtendedEventMessageContract);
CREATE SERVICE TargetService
    ON QUEUE dbo.TargetQueue (ExtendedEventMessageContract);
GO

CREATE EVENT SESSION service_broker_target_test ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1))
ADD TARGET sqlserver.service_broker(SET database_id=(14)
    ,initiator_service=N'InitiatorService'
    ,message_type=N'ExtendedEventMessageType'
    ,service_contract=N'ExtendedEventMessageContract'
    ,target_service=N'TargetService')
WITH (
    MAX_MEMORY=4096 KB
    ,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
    ,MAX_DISPATCH_LATENCY=30 SECONDS
    ,MAX_EVENT_SIZE=0 KB
    ,MEMORY_PARTITION_MODE=NONE
    ,TRACK_CAUSALITY=OFF,
    STARTUP_STATE=OFF);
GO

ALTER EVENT SESSION service_broker_target_test ON SERVER
    STATE = START;
GO

SELECT * FROM sys.conversation_endpoints;
SELECT * FROM dbo.InitiatorQueue;
SELECT * FROM dbo.TargetQueue;
SELECT * FROM sys.dm_xe_session_targets
WHERE target_name = 'service_broker';
GO


How to show the column names of a table?

select column_name from information_schema.columns where table_name='table'


`cube` query performance issues with ordering

Sorting works on values the query returns.

Here you have an index on embedding column but you are sorting on the result of embedding <-> cube('(0.08470847,...,0.06106149)'), which is not indexed.

So first retrieve the required result with the help of sub-query, then perform sorting.

select id,EDistance
from
(
select id, embedding <-> cube('(0.08470847,...,0.06106149)') EDistance
from documents
where embedding <-> cube('(0.08470847,...,0.06106149)') < 0.25
limit 25
) t
order by EDistance ASC

Thanks!


SQL Server - backup restore - lose indexes?

There is no setting that allows indexes to be omitted from the backups or restores.  Such a feature would be nice as it would result in much smaller backups, but Microsoft states that there are many hurdles to adding such a feature (see here).

It's most probable that the indexes did not exist when the backup completed.  There may be an ETL job that removes indexes before a data load, and then creates the indexes again after the load is complete.  Look for other types of activity that occur on the server leading up to the backup window.  If it were such an ETL job doing this, for example, some loads may be taking so long that they aren't complete when the backup ends.  So you'd have a timing issue where sometimes the backup has the indexes, and sometimes it doesn't.

It's also possible, but I would expect you'd know if this is the case, that the backup was actually created on another server that had a copy of the database, and the backup job drops indexes first to make the backup smaller.  This would not be a hit or miss situation as you've reported.

You can also read  here  for another way to accomplish this using filegroups, but again, this wouldn't be hit or miss as you've reported.


You could try this

WITH cte AS (
    SELECT dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
    FROM   dim_product dp
    INNER JOIN  fact_sales fs ON dp.product_code = fs.product_code
    GROUP BY dp.product_name
)
SELECT *
FROM (
    SELECT TOP 1 product_name, SumOfunits_sold
    FROM cte
    ORDER BY SumOfunits_sold DESC
) a
UNION
SELECT *
FROM (
    SELECT TOP 1 product_name, SumOfunits_sold
    FROM cte
    ORDER BY SumOfunits_sold
) b


trigger or check constraint insert values based upon existence of others

If you query the table you're inserting into inside a trigger on that same table, you are likely to get "ORA-04091: table name is mutating, trigger/function may not see it." error. Check constraints cannot contain subqueries. Therefore your best option is to encapsulate logic for inserting data into a stored procedure and use that procedure instead of direct inserts.

I can assist you with writing the procedure, however, it seems to me that you have a logical flaw in your data model. Your example data shows that my_id is not a unique key for the table but rather that many rows may share the same value for my_id. So if one row with my_id = 1 has field_2 set to some value and for some other row field_2 is null, what is to be done? Don't you have a primary key in your table?

Anyways, if you want literally what you wrote in your post, you could do with something like

create or replace procedure my_practice_table_insert(p_my_id in number, p_field_2 in varchar2, p_field_3 in varchar2)
is
    l_cnt number;
begin
    if p_field_2 is null then
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is not null;
    else
        select count(*) into l_cnt from my_practice_table where my_id = p_my_id and field_2 is null;
    end if;

    if l_cnt = 0 then
        insert into my_practice_table(my_id, field_2, field_3) values(p_my_id, p_field_2, p_field_3);
    end if;
end;

I didn't actually try out the procedure so it may have slight errors, but that's the general form it could take.


Does adding an auto_incrementing number to a MySQL partitioned table that is not part of the primary key, cause consistency issues?

Rarely does an app need the constraint about an AUTO_INCREMENT being UNIQUE.  Uniqueness is not guaranteed.

Sometimes it is best to turn

PRIMARY KEY(id),  -- (id is auto_increment)
INDEX(foo)

Into

PRIMARY KEY(foo, id),
INDEX(id)

The purpose is to cluster the rows based on foo because most of the access is done with WHERE foo = constant.

The INDEX(id) is all that AUTO_INCREMENT requires.  It does not require uniqueness; it does not require PRIMARY KEY.

I added id to the PK on the assumption that foo is not, itself, unique.  The combination foo, id is unique (assuming you are not playing games with id), thereby satisfying the uniqueness requirement of PRIMARY KEY.

Back to your question -- Sure, go ahead and add the id.  It will require

INDEX(id)  or  INDEX(id, ...)

Implementation insight...  When a table is opened, the auto_inc value is initialized by doing SELECT MAX(id) FROM tbl.  If that table is partitioned, the code already knows how to check MAX(id) from each partition; then it can easily get the overall MAX.

MySQL 8.0 has a slight variation on that -- it saves the auto_inc value somewhere else so that after a server restart, you will not reuse any ids that got lost in what might have been a crash that necessitated rollbacks.  DELETEing the last id just before a graceful shutdown is another issue covered.

BTW -- See if you can use the above trick instead of partitioning.  I have found very few valid cases for using partitioning.  Provide more details on your case; I can probably spot whether it will help or not.

Ref:  http://mysql.rjweb.org/doc.php/partitionmaint


Does InnoDB ever release unused disk space?

InnoDB tablespace size for individual tables (.ibd) can be reduced with OPTIMIZE and ALTER TABLE ... ENGINE=InnoDB;, but the system tablespace file ibdata1 can be never be shrunk.
The source of ibdata1's growth in in the undo logs.
I have written posts about this since July 2013. I first learned about this phenomenon from Percona's post Reasons for run-away main Innodb Tablespace.
Suggestion #1
If you mysqldump the data into another server running mysql in a newly installed instance, you could setup replication to it, then failover and trash the old master.
Suggestion #2
If you do not have another server, you'll have stop the application, mysqldump the data, drop all DBs, shutdown mysql, delete ibdata1, start mysql (which recreates ibdata1, reload the mysqldump). See my very old post Howto: Clean a mysql InnoDB storage engine?


Actually... no.

The query cache in MySQL, as I discussed at length in another answer, is an optimization that does only one thing: it returns the exact same result set in response to the exact same query, if the previous result was cached and has not been removed from the cache due to invalidation (by changes to the data in the underlying tables) or pruning to make room for more recent queries.

The query cache is checked before the query reaches the parser, whenever a query comes in starting with the characters "SEL" (case-insensitive).

If the query, byte-for-byte, is not identical to one already cached, the query will not be served from the cache.  If anything changes, even a single space in the query, then that's a different query according to the query cache.

If a query with LIMIT X,Y or the equivalentLIMIT ... OFFSET ... is cached, then only the actual rows returned are stored in the query cache, and only the identical query with the same limit and offset will fetch those same rows from the cache again.


As Jon kindly suggested, I changed the format of the image to .png and the issue is resolved on the spot.


Overriding a row FORMAT_STRING with a column FORMAT_STRING in MDX

After doing a bit more research, I found that the SOLVE_ORDER member property can get SSAS to use the format string from the MTD vs. Prev. MTD % member.

The following code worked for me:

MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD %] AS
  [ClosedDate].[Date].[MTD vs. Prev. MTD] / [ClosedDate].[Date].[MTD],
  FORMAT_STRING='Percent', SOLVE_ORDER=1


initdb initializes the whole server data directory and every file will be in the same disk. With tablespaces you can distribute objects onto different disks when needed. The reasons usually are performance related. For example, you may want to have some rarely used tables on slower disks and high throughput tables on super fast disks.

Another benefit is to store indexes on separate disks so they have separate I/O from the data.

Naturally also if the volume on which the cluster is on runs out of space, tablespaces can be used to allow the databases to grow to other disks. This requires the tables to be moves to the new tablespace, it will not happen automatically.


Dog slow query, needs optimizing, please help

Here's a modification you could make... (caveat - I'm a SQL Server person):

SELECT DISTINCT
  test_user_info_test.ID
FROM
  test_user_info_test,
  left outer join test_user_saver_test
    on test_user_info_test.id = test_user_filter_test.USERID
    and symbol = 'x'
    AND INCLUDE ='1'
  left outer test_user_filter_test
    on test_user_info_test.id = test_user_saver_test.USERID
    and symbol = 'x'
    AND INCLUDE = '1'
WHERE
  test_user_info_test.ACTIVE = '1'
  AND
  (
  (test_user_info_test.BALANCE >= '1' and test_user_filter_test.USERID is not null)
  or
  (test_user_info_test.HAS_IT = '1' AND test_user_info_test.EXPIRE > '1337500000' and test_user_saver_test.USERID is not null)
  )

Also add indexes on test_user_info_test.id, test_user_info_test.id, test_user_saver_test.USERID.

Those are the quick things that jump out at me...


Using fulltext search to find a sequence of digits

FTS is language specific; it can help you to find stuff in SQL Server which is specific to a language rather than treating it just like any other string in the databases.

5088 is a base 10 number, I don’t think FTS will be of much help here. I think you’re only option is to use the LIKE operator.

To learn more about why your query is running slow with like operator read Sargability: Why %string% Is Slow article by Brent Ozar.

Alternatively, if you always look for a specific value in the column in question, you can make use of the Computed columns, something like...

Test Table

CREATE TABLE #Test (ID INT , Value VARCHAR(100))
GO

INSERT INTO #Test VALUES (1 , 'ABC'), (2 , '') , (3 , 'EFG')
                       , (4 , 'A123BC'), (1 , 'AB123C') , (1 , 'ABC123')
GO

Let's say you are only interested in the rows where it has a value of 123 and this the only value you always check for, then you can add another column (Computed) to the table, something like...

ALTER TABLE #Test
ADD IsValid  AS (CASE WHEN Value LIKE '%123%' THEN 1 ELSE 0 END)
GO

And probably index the column too, something like....

CREATE INDEX NIX_IsValid_Record
ON #Test (IsValid)

Now your query for records with 123 string should be...

Select * from #Test Where isValid = 1

Even though you are looking for records with a string 123 anywhere in there but you will benefit from the Index on isValid column.


How to find user who Drop any thing without auditing applied on database

Please try it:

Select * from user_audit_object

And for restore dropped table , you should know , that drop did without purge command.
If he/she used purge you can just restore your object just with dump or flashback and if he/she didn't use purge you can use this select * from dba_recyclebin where orginal_name=<object_name>, if the select has result you can restore that and otherwise just falshback or restore backup


DELETE statement execution cancellation in SSMS

Assuming it is a single statement, the operation will either succeed or fail as a whole - there is no way some of the rows will get deleted and some will remain. (And if the delete succeeds, as @Shanky suggested above, in simple recovery there is not much you will be able to do to recover the data aside from reverting to a prior backup.)

That said, do not get impatient and try to kill the user forcefully, or take the database offline, or shut down the service, or disconnect the network cable, or disconnect the power cord. You will need to wait for the rollback to complete - even if it's blocking other users - to be sure your data will be intact. Note that a rollback operation can take longer than the initial operation because a rollback is generally single-threaded, but the delete might have gone parallel.

In the future, you should issue a BEGIN TRANSACTION; prior to running ad hoc / uncontrolled / cowboy DML against a production or otherwise critical system. This way no matter when you realize your mistake, you can wait for the operation to complete and then issue a ROLLBACK TRANSACTION; instead of madly and repeatedly slamming the stop button in SSMS. This will save you from deleting data that you can't easily recover, but there's a caveat the other way - if you did the right thing, you need to remember to COMMIT TRANSACTION;, since blocking the entire table while you're on lunch, home for the evening or on vacation is probably also an employment-terminating event.


Microsoft SQL server 2008 and linked servers

Using loop back linked servers isn't the greatest idea.  This can lead to some interesting performance problems once you start doing joins between local tables and "remote" tables as you are going through a linked server so suddenly instead of doing a normal join you may end up transferring the entire table across the linked server (which means possibly reading it off of the disk), stuffing it into tempdb and joining to a hash table which doesn't have any indexes on it.

Don't setup linked servers unless you actually need them.  You can get some very powerful servers these days for not much money (I'm currently working on a server which has 32 cores and 256 Gigs of RAM) so scaling a server up isn't all that hard.  If the application actually needs to be scaled out across multiple servers you'll need someone who is used to doing very complex tuning to help out to make sure that you aren't shooting yourself in the foot in the process (which isn't hard to do).

Once you do get to needing linked servers, there's going to be so much stuff that needs to be done to move the database and test everything going through and fixing the code isn't going to seem all that bad.


Combine two event tables into a single timeline

Use a FULL [OUTER] JOIN, combined with two rounds of window functions:
SELECT ts
     , min(foo) OVER (PARTITION BY foo_grp) AS foo
     , min(bar) OVER (PARTITION BY bar_grp) AS bar
FROM (
   SELECT ts, f.foo, b.bar
        , count(f.foo) OVER (ORDER BY ts) AS foo_grp
        , count(b.bar) OVER (ORDER BY ts) AS bar_grp
   FROM   foo f
   FULL   JOIN bar b USING (ts)
   ) sub;

Since count() does not count NULL values it conveniently only increases with every non-null value, thereby forming groups that will share the same value. In the outer SELECT, min() (or max()) likewise ignores NULL values, thereby picking the one non-null value per group. Voilá.
Related FULL JOIN case:

Add up conditional counts on multiple columns of the same table

It's one of those cases where a procedural solution might just be faster, since it can get the job done in a single scan. Like this plpgsql function:
CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column
DECLARE
   last_foo text;
   last_bar text;
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
      ORDER  BY 1
   LOOP
      IF foo IS NULL THEN foo := last_foo;
      ELSE                last_foo := foo;
      END IF;

      IF bar IS NULL THEN bar := last_bar;
      ELSE                last_bar := bar;
      END IF;

      RETURN NEXT;
   END LOOP;
END
$func$;

Call:
SELECT * FROM f_merge_foobar();

db<>fiddle here, demonstrating both.
 Old sqlfiddle.
Related answer explaining the #variable_conflict use_column:

Naming conflict between function parameter and result of JOIN with USING clause


Oracle Text CTXCAT Domain Index, Transactional, and Before Insert/Update Trigger

Aha!  I've found the answer.  Talk about an edge case.

First, I found this post from 2007, where someone says:

  ...the AFTER trigger for synchronizing the CTXCAT index on [column] is
  not firing (since my update statement does not include the indexed
  column).

  ...

  Oracle, would it not be better to generate the CTXCAT
  trigger to examine the :old and :new values in the indexed column,
  rather than using a check on (if updating([column]))?

Over a year later, someone replied:

  I fixed this issue by adding additional UPDATING (first_name and
  last_name) conditions in the DR$table_name trigger.

  Here is part of the modified trigger.

  if (inserting or updating('LAST_NAME_FIRST') or updating('FIRST_NAME')
  or updating('LAST_NAME')) then reindex := TRUE;

Hm, so the CTXCAT index uses a trigger to know when it needs to update the index for a particular entry.  All I need to do is tweak the trigger and recompile it so it does what I want.

To get the content of the trigger:

SELECT text
  FROM   user_source
  WHERE  name = 'DR$NAMES_IDXTC'
  AND    type = 'TRIGGER'
  ORDER  BY line;

I copied this into Sublime, prettified it, and found this (simplified):

TRIGGER "TEST"."DR$NAMES_IDXTC" AFTER
INSERT
OR
UPDATE ON "TEST"."NAMES"
FOR EACH ROW DECLARE REINDEX boolean := FALSE;

BEGIN

IF (inserting
    OR updating('COMPOUND_NAME')
    OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME") THEN REINDEX := TRUE;
END IF;

...

END;

You can see that the 12c Oracle Text version's AFTER trigger does actually compare the :new and :old values of the indexed column to see if it needs to update--not the case back in 2008.

So...if I'm updating the :new value in my BEFORE trigger, that should be reflected in the AFTER trigger, and the comparison would kick off an update to the index.  What gives?

Well, here are the two SQL statements I was using:

update test.names set
  first_name = 'Skye',
  last_name = 'Fillingim'
  where ... ;

update test.names set
  first_name = null,
  last_name = null
  where ... ;

(Slightly different than what I said in my question, I apologize.)

The effect here is that, each time I used one of these statements, either the :old.compound_name or :new.compound_name would be null.  So when we get to this condition:

OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"

We are doing an equality comparison against a null, which evaluates to UNKNOWN.  Hence, no index update.

This is actually an extreme edge case, because you have to be indirectly modifying :new.column via a trigger, and either :new or :old must be null.  I would never have discovered it it I hadn't used those exact SQL statements.

So, we have a slight update to the trigger:

IF (inserting
    OR updating('COMPOUND_NAME')
    OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"
    OR (:new."COMPOUND_NAME" IS NULL AND :old."COMPOUND_NAME" IS NOT NULL)
    OR (:new."COMPOUND_NAME" IS NOT NULL AND :old."COMPOUND_NAME" IS NULL)
    ) THEN REINDEX := TRUE;

And then just stick CREATE OR REPLACE in front of the old trigger, recompile it, and everything works perfectly.


Dropping indexes doesn't seem to release space to the OS. Can it?

The space should be freed as soon as the index is dropped.  Possible explanations for what you show above are:

The index is in a tablespace on a different filesystem from the one
you're checking.
The OS/filesystem has some lag in providing up-to-date free space
information.
Something else is eating the free space as soon as it becomes available.
Something is holding open index files; lsof might be useful here.

If you run this query, you can see a little information about the index, including the filesystem number and the number used for filenames making up the index.

select oid::regclass, reltuples, relpages, reltablespace, relfilenode
  from pg_class
  where oid = 'mcdata_2011_07_host_idx'::regclass;

Look for files with filenames based on relfilenode both before and after dropping the index.


Microsoft SQL linked server to Oracle data not updating

I feel really stupid for not thinking about this earlier. I'm so used to SQL Management Studio not requiring changes to be committed. I also thought that the changes were being committed on oracle, because my select statements reflected the changes made.

I found the solution to my problem was upon changing data in Oracle SQL Developer I needed to explicitly

commit;

Now the updated data is available through my linked server.


Does mysqld --ndb-cluster need to be run on MySQL Cluster data nodes?

There are three types of nodes

SQL
Storage (Data)
Management

According to MySQL Clustering, page 7 paragraphs 3,4 say:

  Storage Nodes

  Stroage nodes store the fragments of data that make up the tables held in a cluster and the early work in processing queries. Storage nodes require a large amount of RAM and relatively high-performance machines. The bulk of the processing is done on the storage nodes., and these are the nodes that an administrator spends most of his or her time tweaking. They are completetly controlled the the management node via the configuration file and management client.

  SQL Nodes

  SQL nodes, which run on standard MySQL servers, are the nodes that
  applications can connect to, In small simple clsuters, these nodes are
  often run on the same physical servers as the storage nodes.
  Essentially, SQL nodes provides the "face" to the cluster and operate
  exactly as standard MySQL servers. They then connect to the storage
  nodes behind, which do the early processing on the queries and return the
  results for final processing at the SQL nodes. The amount of
  processing done on the SQL and storage nodes changes form query to
  query.

Based on this, you could run mysqld on a Storage Node if the dataset is small. You are not obligated to do so. It is preferable if you had a separate Storage Node and a separate SQL node. If you have separate Storage and SQL nodes, then do not run mysqld on the Storage Node.


Trying to fit indexes in memory: partitioning vs compression or neither?

Indexes and data are stored in pages. Buffer pool holds the "hottest" pages meaning that if you only access one quarter of your table frequently mostly the "hot" part of your table is going to be the buffer pool because an LRU list manages which page(s) should be evicted at any given time.

Partitioning is a good alternative which also helps data cleanup (simply dropping a partition) but yes as you said it comes with some restriction.

If you have auto increment primary key you can also set up ranges on that. Since you have quite good estimation on how many rows you have inserted / day it should be easy to calculate ranges which translates nicely into date ranges.

Experiment with the setup and see if it does improve the performance.

InnoDB compresses on page level but there are many gotchas there:

1) you should check first if your dataset is compressible enough to have benefit. InnoDB user KEY_BLOCK_SIZE which means if it can compress below this than it will otherwise won't.

2) To minimize disk IO both compressed and uncompressed version of the page is stored in the buffer pool meaning that with assuming at least 50% compressability you sacrifice 33% of your buffer pool on the altar of compression which may become an issue. This can be improved with better compression rates. For example with key_block_size=4 only 20% but that means every page has to be minimum 1:4 compressable.

From http://dev.mysql.com/doc/refman/5.5/en/innodb-compression-internals.html:

  Thus, at any given time, the buffer pool might contain both the
  compressed and uncompressed forms of the page, or only the compressed
  form of the page, or neither.

A good explanation of the scenario is available in this answer: https://serverfault.com/questions/358444/setting-mysql-innodb-compression-key-block-size

3) Table compression can also lead to serious mutex contention issues. For more details: https://www.percona.com/blog/2011/05/20/innodb-compression-woes/

Therefore I wouldn't go for compression unless you have big varchar columns that you want to have indexed. You can compress either in application level or on the filesystem both has their own benefits and both are much more efficient than InnoDB compression.


How can I pass parameters to an "SQL Script" from isql

Looking at your use case, it looks as if you want a stored procedure, so here's one I wrote for you:

CREATE PROC the_script
(
   @param1 int = null,
   @param2 int = null
)
AS
BEGIN
   select
      col_1,
      col_2,
      etc
   from
      table
   where
      cond1 > @param1 and
      cond2 < @param2
END

Now, being in isql, you can run it:

exec the_script 900, 20

Regards.


Conflicting 'LIKE' conditions in MySQL CASE statement

You have no chance,  besides change the metallooc text first  and also do a substing on it

Schema (MySQL v8.0)

CREATE TABLE Option1 (
  `Text1` VARCHAR(18),
  `num` INTEGER
);

INSERT INTO Option1
  (`Text1`, `num`)
VALUES
  ('Red Metallic (999)', '0'),
  ('Blue (745)', '0');

Query #1

SELECT
CASE
WHEN `text1` LIKE '%Metallic%' THEN SUBSTRING_INDEX(REPLACE (`text1`,'Metallic','M'),' (', 1)
WHEN `text1` LIKE '%(%)%' THEN SUBSTRING_INDEX(`text1`,' (', 1)

ELSE `text1`
END AS `Option1`
FROM Option1;

| Option1 |
| ------- |
| Red M   |
| Blue    |

View on DB Fiddle


How can I skip rows that I've already imported?

2008 supports except.  Import the excel data into a different table--mytemptable--then

INSERT INTO mymaintable
SELECT * from mytemptable
EXCEPT
SELECT * from mymaintable;

Then you can get rid of mytemptable (or whatever you want to call it).


How to setup Linked Server connect with different Domain Account with Windows Authentication

No can do, I'm afraid. For a local Win login, you can point to a remote SQL login (including the password), or you can ask for impersonation/delegation meaning that the same win account/login will be used on the other SQL Server.
You can't point to a different win account/login on the remote server,


Get physical disk id and drive letters from management studio?

You can use glennberry scripts..

 SELECT DISTINCT vs.volume_mount_point,
vs.file_system_type,
    vs.logical_volume_name,
 CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
    CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)],
    CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %]
    FROM sys.master_files AS f WITH (NOLOCK)
    CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE);

Below is the sample output,is this what you need..


Restoring corrupt SQL Server 2005 database

Restoring a backup runs in three phases.

creating the files and zeroing them. (Log files are always zeroed, data files depend on a server setting)
reading the pages and writing them to their place in the new files
rolling forward (committed transactions that did not hit a checkpoint yet) and then back (uncommitted transactions).

Only phase 2 is measured by the percent counter. If you are restoring a very large log backup, the 3rd phase can actually take significantly longer than the 2nd phase all the while the percent done counter is sitting at 100%.

Every full database backup always contains a portion of the log. If you have logical inconsistencies within that piece of the backup file, SQL Server would not find out until it is in the 3rd phase. That's why you are seeing 100%.

The best resource for dealing with SQL Server corruption is sqlskills.com. More likely then not however you will find that you can't get to the data with build-in methods. If the problem lies indeed in the log section, you might be able to use an external vendor tool that can read backups like red-gate SQL data compare. There are also tools out there that can help with data recovery from corrupt databases like apexsql recover, but I am not sure if any of those can actually read a backup.


MySQL: How to set the default string using another field by string-interpolation?

This is possible in MySQL 8.0 because they support expressions in DEFAULT clauses now. But it won't work with interpolation like you show. It works like any other MySQL string expression:
CREATE TABLE Account (
 AccountID varchar(25),
 About varchar(300) not null default (CONCAT('My id is ', AccountID)),
 Pagelink varchar(255) not null unique
);

Example:
mysql> INSERT INTO Account SET AccountID = '8675309', Pagelink = '<link>';
Query OK, 1 row affected (0.00 sec)

mysql> select * from Account;
+-----------+------------------+----------+
| AccountID | About            | Pagelink |
+-----------+------------------+----------+
| 8675309   | My id is 8675309 | <link>   |
+-----------+------------------+----------+

In earlier versions of MySQL, defaults can only be a literal value, or NULL, or CURRENT_TIMESTAMP, but not an expression.


Insert values into new columns using values from old columns without locking table

You cannot avoid locking. Keep your transactions short and the number of rows locked reasonable by performing the update in batches, e.g. by primary key range. That reduces the danger of deadlocks.


SQL Server 2019 standard edition CDC with SSIS standard edition

You should research DMS solutions. AWS DMS, Azure DMS, and HVR DMS are the three I'm familiar with. They use CDC, are not just for migrations, and are very good for on-going replication of data where you don't have primary keys on every table or you are going from SQL to something else. HVR is probably your best bet of these three since you're going from SQL to Postgres but I know there are additional DMS products out there. Here's a link to get you started:
https://www.hvr-software.com/platforms/postgresql/


Designing An ACL Based Permission System

I did something like this a long time ago.  Yes it is possible.  No it isn't too hard on the technical side.  I don't think you will have problems in the areas you are asking about.  Really this is pretty basic stuff once you figure out how acls are supposed to behave.

The issue you will run into has to do with consistently enforcing the acls.  It is very easy to forget it once in a call somewhere and you suddenly have the acls not doing what they are supposed to. Oops, security issue.

If you are on PostgreSQL, you could probably use SQL roles and views to enforce this properly on select statements, and so forth.  On MySQL I think this is likely to be more difficult.  You will probably want to have some consistent way of adding clauses to your queries so that you are able to ensure they are enforced all the time.  Do this programmatically. Do not try to do it manually unless you want security holes.


Unable to access SSRS web URL outside Azure VM

Your URL is wrong and also you can configure the firewall. Try below URL.  Configure port as describe below if you have not already.

http://jarvissqlvm.cloudapp.net/ReportServer/PageTo Connect to Remote Report Manager

Here are more detail steps.

Reference:

https://web.archive.org/web/20140523110804/http://msdn.microsoft.com/en-us/library/azure/jj992719.aspx

  If you want to connect to Report Manager on the virtual machine from a
  remote computer, create a new virtual machine TCP Endpoint. By
  default, the report server listens for HTTP requests on port 80. If
  you configure the report server URLs to use a different port, you must
  specify that port number in the following instructions. Create an
  Endpoint for the Virtual Machine of TCP Port 80. For more information
  see, the Virtual Machine Endpoints and Firewall Ports section in this
  document.

  Open port 80 in the virtual machine’s firewall.

  Browse to report manager using Azure Virtual Machine DNS Name as the
  server name in the URL. For example:

  Report manager:http://uebi.cloudapp.net/reportserver

  Report server:http://uebi.cloudapp.net/reports

  Configure a Firewall for Report Server Access
  (http://technet.microsoft.com/en-us/library/bb934283.aspx).


Oracle DB 11gR2 netca installation failing

Answer from below thread solved my problem

https://forums.oracle.com/forums/thread.jspa?threadID=2208254

apparently '=' is not needed in responsefile argument in netca cmd

/u01/app/oracle/product/11.2.0/db_1/bin/netca /silent /responsefile /oracle_binaries/database/response/netca.rsp


Database "warm up" is a real thing... but then again, people making excuses for under-performing systems is also a real thing.  An important take-away here is that once a server is warmed up, it doesn't generally spontaneously cool back down again.

Warm-up is needed because the best performance of a database is achieved when its various caches are populated with the most relevant data, since reading from memory is generally substantially faster than reading from disk.

InnoDB has the InnoDB buffer pool.  MyISAM has the key cache and the OS cache.  Then there's also the MySQL query cache.  MySQL has the open table cache.  Some or all of these structures contribute to the ideal performance of a server.  When a server is handling traffic that is characteristic of its workload, it tends to have the most frequently used -- and therefore presumably the most relevant data -- already cached.

Take the load off of an active MySQL server and you will not see it start closing tables and evicting data from its caches.  If you leave it running, then come back to it tomorrow, you should find that it's still as warm as you left it.

The need for a warm-up is not typically something that is a relevant consideration needed unless the server process is restarted.  Percona Server has a feature that can pre-warm a server's InnoDB buffer pool on restart, by storing the pointers to the pages that were in-memory before shutdown and reloading those pages again into the pool on startup.

However, you can also undo a well-warmed system to some extent by taking a full backup from the client-side of the server, such as with mysqldump and most graphical tools, but not backup utilities that understand native tablespaces, like innobackupex (if my understanding of how that utility works is correct), since a large portion of the data being backed up may be data that is older or irrelevant to the primary workload but may still cause the eviction of other data from the various buffers and caches as it is loaded in, on its way back out for the backup.

The logic in the question you referenced is that reading from the tables and their indexes brings them into memory -- warms things up -- before the "real" queries jump in to start doing their work.

Generally speaking, though... a single query, run once, will have warmed up whatever structures were needed to execute that particular query.  If the same query, executed again immediately, is still slow, then warm-up is not the issue.  Indexes are not reloaded every time you use them, unless there is competition for space in the buffer and cache structures.


Database size limit in Sybase Express edition

For anyone coming across this Q&A ... the maximums for the ASE Express Edition were bumped up in early 2017.

Of interest:

max memory: unlimited
max db size: 50GB (this is actually the max size of all db devices combined)
max number of engines: 4

See ASE version comparison matrix for more details.


What's the difference between quiesce the instance and enable restricted session?

In quiesce restricted, all running sessions complete their current task, then at the point that they become INACTIVE in v$session, they cannot execute any more SQL until the system is unquiesced. In enable restricted session no new connections can be made, but you will need to kill existing sessions or they will simply continue. Quiescing is much less impactful, as your users might only notice the database pauses then resumes. Restricted session I use most often in combination with startup force if I absolutely need to perform some maintenance that cannot be done online, then I disable it and do another startup force and reconnect the apps.


Error on DDL statement never invokes CATCH

Why isn't it caught? Because TRY / CATCH doesn't catch all types of errors. But you can get it to catch the non-system-critical stuff by wrapping it in an EXEC(), such as:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY

The error in the ALTER will fail out to the EXEC, which in turn will return to the TRY / CATCH block reporting a simple, catchable error.

For more detail, the MSDN page for TRY...CATCH states:

  The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  Compile errors, such as syntax errors, that prevent a batch from running.
  Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

That second bullet point, about object name resolution errors, is what is causing the ALTER statement to fail and not be eligible to get handled by the CATCH block.

EDIT:
Regarding your statement about "DDL statements are implicit COMMIT": I am not sure what you mean by this exactly, but all (well, nearly all) queries are transactions by themselves. Meaning, single-query batches have no use for explicit BEGIN TRAN / COMMIT / ROLLBACK. If the statement fails it automatically rolls back, and if it succeeds then it automatically commits. You would only use the BEGIN TRAN / COMMIT / ROLLBACK construct if you want to conditionally rollback for some reason. It is still a good idea to use the TRY...CATCH structure to properly handle errors, but you gain nothing (at least with the code posted in the question) with the explicit transaction handling. There is absolutely no difference between the posted code and the following:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY
BEGIN CATCH
   SELECT 'I''m in Catch';
END CATCH;


Is there any option to kill the particular process automatically if it is consuming a particular value of cpu?

No, there isn't. But you could consider setting statement_timeout, which will kill any query running longer than a certain time.


Extended event duration Millisecond or Microsecond?

It is a mix of Millisecond, Microsecond and some are unknown.  Easiest way to find I used below tsql code to determine which is what.

SELECT p.name package_name,
       o.name event_name,
       c.name event_field,
       DurationUnit= CASE
                         WHEN c.description LIKE '%milli%'
                         THEN SUBSTRING(c.description, CHARINDEX('milli', c.description),12)
                         WHEN c.description LIKE '%micro%'
                         THEN SUBSTRING(c.description, CHARINDEX('micro', c.description),12)
                         ELSE NULL
                     END,
       c.type_name field_type,
       c.column_type column_type
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_packages p ON o.package_guid = p.guid
JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
WHERE o.object_type = 'event'
AND c.name ='duration'

Here is the result set from a SQL 2016 SP1 server. I only posting part of it. Look at the DurationUnit column and you can see 3 different values.

For NULL ones I find the best option is to run (if possible with start and end time) and find what the unit is.

I ran the same query against vnext SQL Server and confirmed we will carry the same confusion to the future.


How can I allow a user to query a view but not the underlying table in MySQL?

I figured it out myself.

First, I created an account that ONLY had permissions to certain columns (not all).  I then created a view for those columns and used the user as the definer.

Then, I created another test account and ONLY gave them permissions to that view.

So the test user could not see the underlying table but only the view and columns.

Works great.


Migrating Standalone named instance to AlwaysON

Yes, its very much possible and I completely concur with your idea as starting with new server and do all the required and once all set-up, get a short window of down-time. Take database backup and restore it on the new server. Once sanity and basic testing is completed successfully, switch off the old server or rename it with some other name and allocate that name to new server.

Create 2 new instances. (SERVER2\NAME and SERVER3\NAME)
Create a Listener called SERVER1_NEW.
Once everything is really for the cut-over, just rename the SERVER1
to SERVER1_old and then rename SERVER1_NEW to SERVER1.

You may read wonderful article on how to do set up of windows always on availability group by Priyanka Devre as below:

https://www.sqlshack.com/configuring-a-sql-server-alwayson-high-availability-group/

Hope above helps.


How to perform bulk delete and release free space?

I suggest you take a step back and look at the overall picture:
You remove some data, and by next month you have added more data and yet again remove old data.
Let us play with some numbers:
Say you have 100 GB data file at end of month. You remove 50 GB. So you shrink the file and gain 50 GB disk. What usage do you have for these 50 GB? These 50 GB will be used over the month, so at end of next month the file is again 100 GB. You have temporarily gained 50 GB disk space, but you can't use this disk space - if you do you end up in a situation where the database is full when it need to grow.
And on top of that you have the downsides of shrinking.

It takes time.

It fragments you indexes. Sure, index fragmentation isn't as bad as
when we had spinning disks but it still come with some cost (mainly
read-ahead read crap into memory and you pollute memory with stuff
you don't need, pushing useful stuff out of memory). I've elaborated
and did some measurements regarding fragmentation here. So, frag
doesn't hurt that much, but even say 20% or 10% slowdown can be relevant - so why
fragment the indexes when it doesn't buy you anything?

And, of course, if you are in full recovery, the data that was moved by the shrink is now logged, requiring a large ldf file and also making the following log backup huge.

And all the other downsides of doing shrink - which has been elaborated in many places (mine is found here).

Bottom line is that you should consider these 100 GB as a part of the cost for this application/database and get financial approval for this. :-)


SQL Server 2008 RC2 Edit transaction Log

Okay, there is a reason why tools that can do this are expensive. It's by no way easy to do. However this blog post can help you.

How to create undo update statements

Since you have found the transaction, you can filter the result by using your transaction id.


Unable to drop database user without login

Check for any blocking SPIDs when you have issued the DROP USER XXX command.

Then kill.


The rows column of EXPLAIN output is an estimate of the rows to be examined. Thus, value of this column and number of rows returned on executing the command can be different.


Displaying the order of the columns of a table

column_id is a reasonable proxy for the column ordinal, since it is impossible to insert a column between two existing columns in SQL Server without dropping and recreating the table.

As the documentation states, column_id values may not be sequential if you drop a column from a table.

You can also make use of the COLUMNPROPERTY() function to return the actual ordinal for each column.

Consider a quick example:

IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
DROP TABLE dbo.t;
CREATE TABLE dbo.t
(
    c1 int
    , c2 int
    , c3 int
    , c4 int
);

ALTER TABLE dbo.t DROP COLUMN c1;
ALTER TABLE dbo.t ADD c5 int;
ALTER TABLE dbo.t ALTER COLUMN c2 char(3);

SELECT o.name
    , c.name
    , c.column_id
    , ordinal = COLUMNPROPERTY(c.object_id, c.name, 'ordinal')
FROM sys.columns c
    INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = N't'

The output looks like:

╔══════╦══════╦═══════════╦═════════╗
║ name ║ name ║ column_id ║ ordinal ║
╠══════╬══════╬═══════════╬═════════╣
║ t    ║ c2   ║         2 ║       1 ║
║ t    ║ c3   ║         3 ║       2 ║
║ t    ║ c4   ║         4 ║       3 ║
║ t    ║ c5   ║         5 ║       4 ║
╚══════╩══════╩═══════════╩═════════╝


Normalisation talks about the atomicity of attributes within a relation and the dependency of attributes on candidate keys. Since there is no mention of non-key attributes in the question it is not possible to comment on the normalisation or otherwise of this design.

That said, I would like to comment on the semantics of this design with regards to the business requirement to move accounts between organisations. In this design you cannot move an account to another organisation. Primary keys, by definition, distinguish one item from another. The account's key includes the organisation id. Therefore an account for one organisation is a different thing to an account with a different organisation, even if the two accounts have the same values in all attributes.

By extension, moving an account would not cause Invoice to be updated. Rather, new rows would be created in Invoice, with values copied from the existing (Organisation, Account, Invoice) rows.

The old Invoice rows could be deleted or not, it doesn't matter to the argument. Although an insert followed by a delete looks to an outside observer like an update, we are talking about primary keys here, which define the existence and distinguishability of objects. If I were to replace your Ford by a Ferrari while your eyes were closed would you, on opening them, think "Wow, my car has spontaneously morphed!" No, one thing has gone and another, functionally similar thing arrived.

If the problem domain requires accounts to be movable yet still recognised as one continuing object you could

A) key the account off account id solely, and have organisation id as a foreign key called "current owner"

B) introduce a new table called "account transfer" with attributes
    From owner
    From account
    To owner
    To account
    Start date
    End date


When doing an update on a set of records, how do i get previous updates to show on current row to update in UDF

(You edited your question, so now I'm editing my answer)

Stop using a UDF for it. Do it all in one hit like this:

WITH Numbered AS
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum
  FROM theTable
)
UPDATE Numbered SET UID = UID + '_' + CAST(DupeNum AS VARCHAR(10))
WHERE DupeNum > 1;

By doing it all at once, you will avoid the issues you have, because it works out what they should all become and applies them in a single transaction.

Also - put an underscore or something in. If you have UIDs like D, D, D2, then it's better to end up with D, D_2, D1 than D, D2, D2. Use a character that isn't used in regular UIDs. And ideally, store what it was originally somewhere, because if you get more Ds coming through, you want to be able to tell that you really have a third duplicate, not just a second one.

A further edit to clarify:

A scalar UDF is a procedure. It has BEGIN and END, and runs row by row. When this runs repeatedly in a larger query, it runs within the context of the transaction, and therefore sees the impact of earlier work within the transaction.

If you MUST use the UDF, create a temporary table containing the rows that you want to update to, and then perform an update by joining this temporary table. While you populate the temporary table you're not making any changes to the table, so every UDF call will be unaffected by those changes. Then you can do it without having a UDF in your actual update statement.

Yet another edit;

What does this do?

WITH Numbered AS
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum
  FROM theTable
)
UPDATE Numbered SET UID = dbo.UIDFIX(UID)
WHERE DupeNum > 1;

And more...

How about:

--First get the new values
WITH Numbered AS
( SELECT *,
  ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum
  FROM theTable
)
SELECT *, dbo.UIDFIX(UID) AS NEWUID
INTO #NewVersion
FROM Numbered
WHERE DupeNum > 1;

--Now update them all
UPDATE theTable
SET UID = n.NEWUID
FROM #NewVersion AS n
WHERE n.PK = theTable.PK;


Is using the rename database feature in PHPMyAdmin safe?

According to this stack overflow answer , there might be data loss. The same is confirmed via MySQL official document here. Please correct me if something is missing from my point of understanding.


Is not in (a,b,c) faster than where != a, b, c

I don't think there will be a noticeable difference. Query optimization has very little to do with the syntax of your query and a lot to do with the RDBMS query optimizer. The optimizer pulls apart your queries and optimizes it as it sees fit.


1. To dump only table structures

a. dump

mysqldump -d -u root -p"password" --all-databases > /tmp/dumpfile.sql

b. restore

mysql -u root -p "password" "dbname" < /tmp/dumpfile.sql

2. To dump only data not structure

a. dump

mysqldump -uroot -p"password" --no-create-info "Db" "TableName"> /tmp/dumpfile.sql

b. restore

mysql -u root -p password "dbname" < /tmp/dumpfile.sql

3. To dump inserts only for specific Columns

a.dump

mysqldump -t -uroot -p"pawword" "Db" "TableName" --where =”Columnname in (1,2)” > /tmp/dumpfile.sql

b. Restore

mysql -u root -p password "dbname" < /tmp/dumpfile


In MySQL replication, why aren't queries run on the slave when there was no USE statement on master?

Please provide the relevant my.cnf entries.

If you have

binlog_do_db = B
-- Note:  That implies ignoring other dbs

and you do

USE A;  -- either explicitly, or due to the connection parameters
UPDATE B.tbl ...;

That UPDATE will be ignored.


Is >58 hours to create an index normal? Should I cancel and try again?

What is the value of sort_buffer_size?  200M might be reasonable on your machine.  (A sort might be involved in the index creation.)

Keep in mind that it will rebuild all the indexes.  Still 58 hours is unreasonable.

What is the value of innodb_buffer_pool_size?  It may be using that.  It should be about 11G for that size machine, assuming you are using only InnoDB.


If you right click on the availability group listener and click properties you can change the IP address from dhcp to a static ip.

As for where the data is stored prior to transfer, it is stored in the transaction log.