How to design a database with one-to-many and then many-to-many relationship?

This is a many to many relationship. You will have a Customer table and a TruckDriver table.

You will also have a table called Delivery or similar which will manage the relationship between the two.

The delivery table would have the columns

CustomerID (foreign key, referencing the PK in the customer table)
DriverID (foreign key, referencing the PK in the driver table)
Rate

If one customer can recieve an offer from the same driver more than once, it may also be worth adding a column DeliveryID as the Primary Key on the table, otherwise you can use a composite key of CustomerID, DriverID

There may be other information you want to include in your delivery table such as date of offer and a flag as to whether the offer was accepted or not but that is not specified in the question.


Is there a way to do data export from Amazon Web Services RDS for SQL Server to On-Premises SQL Server?

Best is to load the data into a staging database on premise and then incrementally load the data to the main database.

You can use : SQL Database Migration Wizard found on codeplex.

Even though it is for Azure to onpremise sql server and vice versa, it will work for Amazon RDS as well.

Other alternative is to use SSIS as mentioned by @sqlreader.


You cannot run/install SQL Server 2008 or any other production version on Linux OS, this is not allowed or possible, you can only install it on Windows server/client OS.

As per their official Blog, Microsoft announced in March 2016 that is planning to launch SQL Server on Linux: Announcing SQL Server on Linux. The SQL Server version is currently called v.next and will be available on Linux. Tentative date for release is Mid 2017.

There is a public preview version that can be installed/tested now but is not production ready yet: Take a closer look at SQL Server on Linux


Field Terminator Problem in Format Files

After a whole day struggling with Line Feed/Hex code, I finally found that my problem was caused by a missing columns in the format files. The dataset is new to me and I thought it had 21 rows while it actually has 22 rows. That's why sql server kept loading the last "|" from the data file.

Here is a reference for others maybe interested in loading data file from UNIX system with row terminators different from Windows, there are three methods:

use bulk insert and set the row terminator as "0x0a".
use a format file and set the the field terminator of the last field as "\n".
Use a dynamic sql with function Char(10) to insert the line feed(LF) into the command string and then exec() the command string.

Before trying these methods, use a notepad to show all control characters to confirm the control characters used by the text files.

If none of these methods work for you, you may have some problems other than the line feed/row terminator.


For complex date and time functions, use a user defined table-valued function and filter the results from it:

http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx

There are a number of ways to do this, but this is one I prefer.


I need to remove duplicate values SQL Left Join

It is not possible to display in the format you want (without a lot of extra work and an assumption that there are always as many locations as job categories.)

Using GROUP_CONCAT however will allow you to return 1 row per username which contains all the information you seek, regardless of the number of job categories and locations.

SELECT
users.id,
users.username,
GROUP_CONCAT(job.category) AS job_categories,
GROUP_CONCAT(locations.location) AS locations
FROM users
INNER JOIN job ON users.username = job.username
INNER JOIN locations ON job.username = locations.username
GROUP BY users.id

It will return results like:

5   Don   Fashion/Design/Beauty,Eng,Arts/Audio/Video Technology        Mr,Colombo,gm


Failure to log in using just the server name

Check if server name you use resolved to valid IP address: ping SERVERNAME gives you the same IP address as you assumed it has to be


Duplicate entry for key. even though it is not

This is because the maximum possible stored value for the int(11) (32-bit signed integer) Contact column is 2147483647.

MySQL is silently ignoring the fact that the value 9645904409 is larger than this & is "truncating" it down to 2147483647. Multiple values truncated to 2147483647 are causing the unique constraint violation.

If Contact is supposed to be a phone number, I suggest changing the datatype to VARCHAR(20) or similar.


INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query

Thanks to #danblack and dbfiddle.uk in short I determined that I was missing one of the columns in the ON DUPLICATE KEY UPDATE.

Every single column must be defined for ON DUPLICATE KEY UPDATE to work!

I effective had columns id, a, b, c and d in the table. I forgot to define one of the columns (that wasn't id as that is the first half of the requirement).

Won't Work (missing column):

ON DUPLICATE KEY UPDATE
id=VALUES(id),
a=VALUES(a),
c=VALUES(c),
d=VALUES(d);

Will Work (has all table columns):

ON DUPLICATE KEY UPDATE
id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c),
d=VALUES(d);


It has to do with the hierarchy of permissions and using role membership over granting permissions directly to each login. This SQLMag article shows a good image of the hierachy in SQL Server. [In the event the article ever goes dead I have copied the image below.]

You are granting DENY to the role Employees so the HR Administrators being a member of a higher level role should mean they do not directly inherit the DENY. At least that is how I understand it.

The particular section of the article referenced speaks on DENY:

  The third permission statement is DENY. A DENY statement also revokes a permission, but does it in such a way that the principal cannot inherit the permission through role membership. When you deny a permission, that principal doesn't have it, period. Denying permissions can let you get very granular with permissions. A common example is to grant everyone in a department a set of permissions through their membership in a role. But then you can fine tune the scheme by denying permissions either to certain users or to another role that only a subset of users belong to, taking advantage of the ability of users to belong to multiple roles.

I stand corrected

Let me first state I apoligize for not first testing what I was saying as I ended up proving myself wrong. I should have known better.

First lesson to learn: Test everything.

Second lesson to learn: Not all Microsoft Exam questions are perfect. I would suggest sticking with those test engines that Microsoft supports (e.g. MeasureUp.com)

Answer before the details

I will first give you what I discovered, then show you how I tested it. So my finding is that basically option D can be correct if you use REVOKE instead of DENY. This is based on the statement from Bob Beauchemin's whitepaper on SQL Server 2005 Security Best Practices:

  In SQL Server, authorization is accomplished via Data Access Language (DAL) rather than DDL or DML. In addition to the two DAL verbs, GRANT and REVOKE, mandated by the ISO-ANSI standard, SQL Server also contains a DENY DAL verb. DENY differs from REVOKE when a user is a member of more than one database principal. If a user Fred is a member of three database roles A, B, and C and roles A and B are GRANTed permission to a securable, if the permission is REVOKEd from role C, Fred still can access the securable. If the securable is DENYed to role C, Fred cannot access the securable. This makes managing SQL Server similar to managing other parts of the Windows family of operating systems.

So basically REVOKE removes the permissions and basically would be not applying any permissions to the table itself. You are not explicitly denying anyone access to the table. So if you really wanted to deny permissions you would have to remove the HRAdmins group from the Employees roles for it to work correctly.

I do not have access to Azure SQL so I could only test this on a local SQL Server 2014 instance. The script below is what I went through, included comments as I go.

USE [master];
GO
--Create a database to work with
CREATE DATABASE [Database_WorkingWithPermissions];
GO

--Create the logins (I can't create Windows Groups but should work the same way)
CREATE LOGIN HRAdmin1 WITH PASSWORD = N'Apple123';

CREATE LOGIN Employee1 WITH PASSWORD = N'Apple123';

--Create the users in the database
USE [Database_WorkingWithPermissions];
GO

CREATE USER Employee1 FROM LOGIN Employee1;
CREATE USER HRAdmin1 FROM LOGIN HRAdmin1;
GO

--Create the role
CREATE ROLE Employees

--Create the tables noted in the question
CREATE TABLE [Employees] (employeeID int IDENTITY(1,1), employeeName varchar(15));
CREATE TABLE [SalaryDetails] (employeeID int, salaryAmount decimal(12,3));
CREATE TABLE [OtherTable] (column1 int);

--Grant permissions to the roles according to requirements
GRANT SELECT ON [dbo].[Employees] TO [Employees];
GO
GRANT SELECT ON [dbo].[OtherTable] TO [Employees];
GO
DENY SELECT ON [dbo].[SalaryDetails] TO [Employees];
GO

--Assign membership to the role based on requirements
ALTER ROLE [Employees] ADD MEMBER [Employee1];
GO
ALTER ROLE [Employees] ADD MEMBER [HRAdmin1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [HRAdmin1];
GO

--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

--So HRAdmin1 does not have permissions as it should
-- So they need to be removed from the Employee role
ALTER ROLE [Employees] DROP MEMBER [HRAdmin1]
GO

--Run the test again
--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

--According to a SQL Server 2005 whitepaper Bob Beauchemin wrote on Security Best Practices
-- Revoke should be used
ALTER ROLE [Employees] ADD MEMBER [HRAdmin1]
GO
REVOKE SELECT ON [dbo].[SalaryDetails] TO [Employees];
GO

--Run the test again
--Go through and see what permissions
EXECUTE AS USER = 'HRAdmin1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;

EXECUTE AS USER = 'Employee1'
SELECT * FROM [dbo].[Employees];
SELECT * FROM [dbo].[SalaryDetails];
REVERT;


Is NEWSEQUENTIALID unique within a database for Many tables?

Acording to the documentation from Microsoft.

NEWSEQUENTIALID

  Creates a GUID that is greater than any GUID previously generated by
  this function on a specified computer since Windows was started. After
  restarting Windows, the GUID can start again from a lower range, but
  is still globally unique

I recommend using NEWSEQUENTIALID over NEWID() for any case that that you aren't relying on the built in randomness, mainly due to the fragmentation issues associated with random GUIDs. There is a fantastic article by Kimberly Tripp on some of the pitfalls of using NEWID() on a Primary key.

There are a few situations where NEWSEQUENTIALID may not guarantee  uniqueness as are listed in the documentation.

  The UuidCreateSequential function has hardware dependencies. On SQL
  Server, clusters of sequential values can develop when databases (such
  as contained databases) are moved to other computers. When using
  Always On and on SQL Database, clusters of sequential values can
  develop if the database fails over to a different computer.

However judging by the phrasing of your question it seems like this will be contained on a single database server.

It is also worth noting(not that it applies here) that NEWSEQUENTIALID can be predicted so if privacy of that GUID is a concern this could also be a problem.


Language difference in full text search for numeric strings

Seems that this is the bug: SQL Server 2008 full-text query may return false positives using number with more than 9 digits as search predicate condition.

Unfortunately the resolution does not work for Danish language.


Find the date and time that a data page was modified

If you know the file and page number already:

DECLARE @file INT, @page INT; -- input parameters

SET @file = 1;    -- default, most systems
SET @page = 1681; -- exemplar on my system; you'll need to change

DECLARE @p NVARCHAR(15) = RIGHT(N'000' + CONVERT(NVARCHAR(4), @file), 4)
  + N':' + RIGHT(CONVERT(NVARCHAR(10),CONVERT(VARBINARY(32),@page),1),8);

;WITH x AS
(
  SELECT [Page ID],[Transaction ID]
    FROM ::fn_dblog(NULL,NULL)
    WHERE [Page ID] = @p
)
SELECT MAX([End Time]) FROM ::fn_dblog(NULL,NULL) AS y
INNER JOIN x ON x.[Transaction ID] = y.[Transaction ID]
WHERE Operation = N'LOP_COMMIT_XACT';

If you don't know the page already, you may need to do some up-front investigation using DBCC IND, DBCC PAGE, and various catalog views, especially if you have partitioning enabled, multiple files in the target filegroup, etc.


Oracle: Can I change the oracle's password in a RAC environment?

Yes,

you can change the passwords without any worries.

The reason for that is no cluster configuration file stores information about OS password, hence changing it will not affect already existing user equivalence.

Also, keep in mind that passwords do not need to be identical on both RAC nodes. It is common, but not a must.


Can anyone advise on how to go about recovering the data? If I browse the ibdata1 file I can see most of the data (there appear to be a few rows missing). Are there any tools that can read the ibdata1 file and extract data row by row?

Please don't do this... you may end up corrupting innodb data files.

It is better to follow what the error log says..

  "Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do
  resolve it"

Just follow the steps provided in the link. Once the stack trace issue is resolved you will have the exact cause for the problem and the solution.


Returning 1 Max Value when Some Counts Are Equal in Access

When you join this result set (as a subquery?) back to the main table, why don't you do another GROUP BY and get the MAX(Start)?

Select MaxCount.DOW As DayNumberOfWeek, Max(Date) As LatestDate, MaxCount.Max
From
  (
  SELECT DOW, MAX(Counts) AS Max
  FROM
    (
    SELECT DOW, Start, Count(*) AS Counts
    FROM Outbound
    GROUP BY DOW, Start
    )
  GROUP BY DOW
  ) As MaxCount
  Inner Join
    (
    SELECT DOW, Start, Count(*) As Counts
    FROM Outbound
    GROUP BY DOW, Start
    ) As DailyCount On MaxCount.DOW = DailyCount.DOW And MaxCount.Max = DailyCount.Counts
Group By MaxCount.DOW, MaxCount.Max;

Apologies if I made some syntax errors, I don't have Access handy right now.


Inconsistency in Tables - Mariadb Galera Cluster

pt-table-checksum supports Galera. Try it to see if there are any inconsistencies.


Why is my Postgres database working for a while and then not able to “start server” once restarted?

Okay, I think I've figured it out. Might be overkill but it works:

First thing I did was format and mount my 1TB SSD. Here is a good video for a walkthrough for formatting to ext4 and mounting. The difference between the video is that I've updated the fstab file to check my SSD during bootup or "0 2" at the end of the SSD mount options instead of "0 0".

Secondly, I installed Postgres. Here is a good walkthrough for that. The directions provided in that blog were more than I needed, but a good walkthrough nonetheless. I simply installed Postgres with:

sudo apt install postgresql postgresql-contrib

Third, I followed this walkthrough until the end of step two, but before beginning step 2, I added a symbolic link from /var/lib/postgresql/12/main to /YOUR/MOUNT/POSITION/postgresql/12/main by executing:

ln -s /var/lib/postgresql/12/main /YOUR/MOUNT/POSITION/postgresql/12/main

Lastly, before restarting the postgres server, I used this website to help me better configure my server. Enter your specs and it should give you some useful configuration settings.


In other words, does one ever roll back manually?

No not as such, unless someone is doing testing I have hardly seen people manually doing a rollback. The rollback is specified in stored procedures or transactions in which you either want complete change to be entered or nothing at all.

  As far as I know, the transaction will be automatically rolled back if one of the SQL statements fails, such as an invalid INSERT or UPDATE.

Depending on how transaction is initiated, if it is under explicit begin transaction and commit yes it will rollback completely to its initial state after the query fails, while if the transaction is not under begin transaction and commit it will store the changes done till the query failed and from after the query failed the it would be stopped from making further changes.

A classic case where system initiates a rollback is when a deadlock happens and SQL Server by internal mechanism chooses deadlock victim, kills the transaction and it may or may not rollback depending on whether it was explicit or implicit transaction. Like when normal select statement is killed their is no rollback as such.


Oracle 11g: increasing buffer cache with AMM

You can set the value for db_cache_size, and the value you set for it will be considered the minimum value it can be with AMM enabled.


User cannot create schema in PostgresSQL database

You need to provide at least CREATE permission in schema:

GRANT CREATE ON SCHEMA public TO deploy_user;

or ALL:

GRANT ALL PRIVILEGES ON SCHEMA public TO deploy_user;

See manual at http://www.postgresql.org/docs/9.4/static/sql-grant.html


The best way would be to use tables you already have.
Create two tables -- table-a, table-b
For a test you can even update the same column with the same information
so you don't affect any real data.

For instance
UPDATE table_a set ID = ID where ID = 100;

Open two sessions to the same database.
On one, run

BEGIN TRAN
update table_a set ID=ID where ID = 100;

On two run

BEGIN TRAN
update table_b set ID=ID where ID =100;

Then, copy the update statements to the opposing sessions and run at the same time.
In one,

update table_b set ID=ID where ID =100;

In two

update table_a set ID=ID where ID = 100;

I just tried this now and got on MS-SQL

Msg 1205, Level 13, State 56, Line 1
Transaction (Process ID 23) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


MySQL sort data before using GROUP_CONCAT to concat it together?

You need to get confidence in GROUP_CONCAT with ORDER BY embedded.
Only way to do that is the following
SUGGESTION 01 : Proper Indexing
ALTER TABLE tracks ADD INDEX objectId_posTime_ndx (objectId,posTime);

This will help the query optimizer retrieve your data ordered properly.
SUGGESTION 02 : Display posTime as a Debug Step
SELECT   objectId,
         group_concat(posTime ORDER BY posTime) as trail
FROM     tracks
WHERE    objectId > 0
GROUP BY objectId;

If the posTime is ordered as desired, then you should be fine with
SELECT   objectId,
         group_concat(CONCAT(X, ',', Y, ',', hdg, ',', spd) ORDER BY posTime) as trail
FROM     tracks
WHERE    objectId > 0
group by objectId;


Aggregate count over variable number of json keys

The only one thing which I not found is the function/operator for concatenation of JSON objects, so lets create it:

create function json_concat(in json, in json) returns json as $$
  select
    (
      left($1::text, length($1::text)-1) || ', ' ||
      right($2::text, length($2::text)-1)
    )::json;
$$ immutable language sql;

After this the final query would be:

with t as (
  select band, tag, count(*) as ct
  from people
  group by band, tag
  order by band, tag)

select json_agg(x)
from (
  select json_concat(json_build_object('band', band), json_object_agg(tag, ct)) as x
  from t
  group by band) tt;

and result is

[{"band" : "metallica",  "bass" : 1, "drums" : 1, "guitar" : 2, "vocals" : 1 }, {"band" : "beatles",  "bass" : 1, "drums" : 1, "guitar" : 2 }]


Oracle 11gR2: ORA-01102: cannot mount database in EXCLUSIVE mode

ORA-01102 cannot mount database in EXCLUSIVE mode

  Cause: Some other instance has the database mounted exclusive or shared.
  Action: Shut down the other instance or mount in a compatible mode.

Find whether you have any running Oracle process.

$ ps -ef | grep smon

Kill the process then try to start again.

Since I dont have the procedure which you have followed to perform DB rename and DBID change I can not say whether there is problem with your procedure.

I would recommend to follow Oracle Knowledge Document to perform such task.
How to Change the DBID and the DBNAME by using NID


MySql - Remove duplicate record if another column is empty

I think this is what you want...

I assume your table is called t:

 CREATE TABLE t
 (
     `ID` int PRIMARY KEY,
     `First_Name` varchar(50),
     `Last_Name` varchar(50),
     `City` varchar(50)
 ) ;

 INSERT INTO t
     (`ID`, `First_Name`, `Last_Name`, `City`)
 VALUES
     (1, 'Alan', 'Smith', 'Los Angeles'),
     (2, 'Alan', 'Smith', NULL),
     (3, 'Alan', 'Smith', 'New York'),
     (4, 'Alan', 'Smith', NULL),
     (5, 'Alan', 'Smith', 'Houston')
 ;

This query DELETEs the rows you don't want...

 DELETE
 FROM
     t
 WHERE
     t.id IN
     (
     SELECT
         *
     FROM
         (
         SELECT
             id
         FROM
             t AS t1
         WHERE
             City IS NULL
             AND
             EXISTS
             (
                  SELECT 1
                    FROM t AS t2
                   WHERE     t2.`First_Name` = t1.`First_Name`
                         AND t2.`Last_Name`  = t1.`Last_Name`
                         AND t2.`City` IS NOT NULL
                         AND t2.`ID` <> t1.`ID`
             )
         ) AS s0
     ) ;

 SELECT * FROM t

 ID | First_Name | Last_Name | City
 -: | :--------- | :-------- | :----------
  1 | Alan       | Smith     | Los Angeles
  3 | Alan       | Smith     | New York
  5 | Alan       | Smith     | Houston

dbfiddle here

--

NOTEs:

The need of a subquery within of a subquery is a limitation of MySQL (most SQL databases don't need it). See You can't specify target table 'NAME' for update in FROM clause
I've assumed that you wouldn't want to delete any row if there is not one with a City. I.e.: if you have (15, 'John', 'Doe', NULL) and (16, 'John', 'Doe', NULL) you leave both on the table.


Since it doesn't appear that DimServers references any other tables, you should not have any problems inserting rows into that table.

However, DimDrives has a foreign key reference to DimServers.  When you insert any rows into DimDrives, the value that is in the DimServer_ID column MUST exist in the column ID_DimServer on the DimServers table.

If you attempt to insert a row in the DimDrives table where the column value for DimServer_ID does NOT exist in the DimServers table in the column ID_DimServer, you'll get a foreign key violation error

The INSERT statement conflicted with the FOREIGN KEY constraint


How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow?

Unfortunately this is not a simple matter. There is much black art and experience involved because you are dealing with a few different but strongly coupled matters. First you are trying to decide if your infrastructure needs updating to handle the application1, if the application needs changes to optimise it instead2, some mix of the two3, or if you need to re-architect both in-step4.

1: throwing hardware at the problem
2: better indexing, fixing anti-patterns to improve use of those indexes, and so on
3: when there are optimisation gains to make in the application, but hardware upgrades are still needed to achieve the desired performance
4: switching to a wide scaling model may require changes to both the infrastructure and the code as part of the same grand (re)design

If you have little or no control over the application, you beleive it has been optimised as far as is possible on its own, or you desire to leave it alone as much as possible, then infrastructure changes are all you can work with, in which case there are slightly more hard and fast rules: if you are hitting disk/network a lot for read activity then consider upgrading the amount of RAM, if you are blocking on disk/network IO for write activity then a better performing storage layer may be needed, if your CPU resource is always busy but other resources less so then that is a likely upgrade target though take care to analyse if the load is mostly concurrent or not (if you have small numbers of long-running CPU-busy tasks then faster cores are useful, if you have high concurrency them more cores is probably going to be more helpful though make sure you have enough memory that they won't be starved by IO bottlenecks reading the data they need to process).

Of course even with no control to change the rest of the application you might be able to help it out with index changes.

As a general rule you are not wanting to monitor specific metrics for absolute values, but instead watching key ones for changes over time. These metrics will be a mix of application throughput at the front end (response times generally or for specific actions), throughput server-side (transactions per period, average transaction length, ...) and infrastructure related (CPU utilisation, IO busy/wait times, memory use). Make sure you know your baseline metrics, so you can more immediately see where a bottleneck develops over time due to data growth, over time due to concurrency growth, or suddenly due to a new glut of users or a change in the application.


Postgres - How to prevent locking when parent table is updated whenever an insert/delete occurs in child table

My advice is to use a “deferred constraint trigger” to update the "Products" table whenever "Items" is modified.
Such triggers are AFTER triggers that are not run immediately after the statement, but at the very end of the transaction.
The advantage here is that the rows in "Products" won't be locked any longer than necessary, and your concurrency will be much better.


Good practice for hiding primary keys from a client

Plan A:

Your basic is probably good.

_id could be computed as MD5(CONCAT('salt', id)).  'salt' is some secret string; it's purpose is to make it harder for a hacker to discover your mapping between id and _id.

An MD5 can be stored in CHAR(32) CHARACTER SET ascii or, via UNHEX(...) in BINARY(16) -- 32 or 16 bytes, respectively.

Since that is 32 hex digits, you may have an issue with such a long string.  If so, then you could use some shorter 1-way hash, but that increases the risk of an accidental dup.  At that point, you would need to take advantage of the UNIQUE index, toss the id, and move on to the next.

Plan B:

Create a table with all the numbers from, say, 100,000 to 999,999.  This would give you 900K 6-digit numbers.  Sort them (ORDER BY RAND()) and add an AUTO_INCREMENT id.  Now you have a mapping that is unique, and limited to 6 characters.  (Variants would involve hex or base64 or all letters.  But watch out for "4-letter words" buried in strings.)  (MariaDB has an easy way to build such a "sequence" table.)

Plan C:

When you need a new _id, compute RIGHT(RAND(), 6), then verify that it is not a dup.  (A simple Stored Function can do that task.)  (Caution: When you get close to a million, the function will bog down due to dups.)  6 limits you to 1M numbers; increase the 6 to get more.

Minor bug:  It is possible to get a decimal point; any of these would be safer:

REPLACE(RIGHT(RAND(), 6), '.', '')
FLOOR(1000000*RAND())
100000 + FLOOR(900000*RAND())  -- always 6 digits long


Dispaly Results from left join takes lot of time - MSSQL

Perhaps you see a join elimination? We can't say since you didn't give us any details (like execution plan) and also your query is simplified, so we can't say whether join elimination is possible in the first place. Join elimination is when SQL Server determines that it doesn't have to access a table at all, so it eliminates it from the execution plan. Here's an example using Adventureworks where the is no need to access the referencing table (in a FK relationship) since we didn't pick any columns from it (note the commented column):

SELECT DISTINCT c.CustomerID, c.AccountNumber--, h.Freight
FROM Sales.Customer AS c
LEFT OUTER JOIN Sales.SalesOrderHeader AS h ON c.CustomerID = h.CustomerID

And here is a different example where it doesn't access the referenced table, since we preserve the referencing table and don't pick any columns from the referenced table:

SELECT h.Freight, h.CustomerID, h.CustomerID
FROM Sales.Customer AS c
RIGHT OUTER JOIN Sales.SalesOrderHeader AS h ON c.CustomerID = h.CustomerID


Any ideas what is wrong with this code, Error at or near Insert on a new function

PostgreSQL has several languages for functions, and it is easy to get confused.

PL/pgSQL uses BEGIN / END blocks.

But LANGUAGE sql functions consist only of SQL statements with no blocks ir other procedural elements.

So either change the language to plpgsql and add a semicolon or (better) remove the BEGIN / END block.


Creating partitions on a production database

I want to know how a new partitioned table will accommodate future years?

I'm fairly sure, according to the 5.1 documentation, that you'll have to alter table add partition to handle future years.

  I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name

Are you needing to use the 'old' table after you get this set up? If not, I would follow these steps:

Create partitioned table as you want it, under table name 'mytable_new'
INSERT INTO mytable_new SELECT * FROM mytable
RENAME TABLE mytable TO mytable_old
RENAME TABLE mytable_new TO mytable

This should avoid the need to alter your queries. If your table is extremely write-heavy (I assume it must be if you have the need to partition it!), you will probably want to have a maintenance timeframe where it's offline until the operation is complete.

Hope this helps.


Apply INNER JOIN only if there is a row available otherwise use NULL

If you replace the inner joins with left joins you will get the data that you want. The left join shows the results even with there is not a match on the other side and in those cases shows a null.

SELECT `d0`.`name` AS `name0`, `d1`.`name` AS `name1`, `d2`.`name` AS
       `name2`, `d3`.`name` AS `name3` FROM `my_table_1` AS `d0`
 LEFT JOIN `my_table_1` AS `d1` ON d1.parent_id = d0.value_id
 LEFT JOIN `my_table_1` AS `d2` ON d2.parent_id = d1.value_id
 LEFT JOIN `my_table_1` AS `d3` ON d3.parent_id = d2.value_id
 WHERE (d0.dropdown_id=56)


Where does Postgres store all statistics?

There are two different kinds of statistics, and you are confusing them.  One is about the distributions of values in the table columns and things like that.  These are held in the table pg_catalog.pg_statistic, and the more-user-friendly view pg_stats. This is a more-or-less normal database table. These are gathered by ANALYZE and/or VACUUM, or their "auto" equivalents.

The other kind of statistics records how many updates, inserts, etc. have occured on each table. On a live server, you can see this data using the views pg_stat_user_tables and friends, but there are no real tables underlying these views. These statistics affect the first kind of statistics, because they are used to decide how often ANALYZE and VACUUM are auto-run. These are stored outside the normal database tables, in "pg_stats_tmp" (or whatever "stats_temp_directory" is set to if it is not the default) when the server is running, and moved to pg_stat for cold storage while the server is shut down.  Upon a crash, all this data is discarded. If both of these directories are empty, then perhaps you have turned "track_counts" off, or perhaps you have set "stats_temp_directory" to a non-default value and so are looking in the wrong place. Or maybe there was a crash recently and they have not been rebuilt yet.

  Follow-up question -- doesn't pglogical collect statistics while it is replicating?

If you have "track_counts" or "autovacuum" turned off, that would explain the lack of recent stats.  Or, if you populated the new database extremely quickly, then maybe the automatic analyze just didn't have a chance to do its things yet.  If none of those are the case, then you would have to consider whether there are no stats (of the pg_stats variety) at all, or if they existed but were just not fresh enough.  Running this query might help you figure that out:

select analyze_count,autoanalyze_count from pg_stat_user_tables

But there is a pretty good chance you will never be able to reach a definitive conclusion, unless you are willing to recreate the situation from scratch and poke around in it.


UPSERT - Is there a better alternative to MERGE or @@rowcount?

I think the simple answer to this is no. MERGE was Microsoft's answer to the more convoluted UPSERT logic. And you didn't even list the worst approach:

IF (SELECT COUNT ... ) > 0
    UPDATE
ELSE
    INSERT

I just threw up in my mouth a little typing that, but it's actually the one I see most often.

In any case, if MERGE is not flexible or powerful enough for you, I suggest you submit a feature request to Microsoft at http://connect.microsoft.com/sql/ and explain, thoroughly, your business case. As long as you stick to real advantages of your proposed syntax over MERGE, you've got my vote. If you hang too much on the "error-prone" part, I'm not as likely to buy in. Why? Because you can fat-finger any statement.

That said, I don't think there's anything anyone here can do for you specifically. You should investigate the potential problems with MERGE:

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/


Error 1052 Field is Ambiguous using Inner Join Function

Qualify each column in the SELECT/WHERE and join clauses.

So, for example I've qualified the EventNo column so that it's read from the eventplan table in the SELECT clause - it's ambiguous because it is in both the eventplan and eventrequest tables.

SELECT eventplan.EventNo, DateAuth, COUNT(PlanNo)
FROM eventplan INNER JOIN eventrequest ON eventplan.eventNo = Eventrequest.EventNo
WHERE COUNT(PlanNo) > 1 BETWEEN '2013-12-01' AND '2013-12-31';

You may nave to do the same for DateAuth and PlanNo if they exist in both tables (in this specific query).

It's good practice to always fully qualify column names - can make the query easier to read. You can always use a table alias too to cut down on typing.


Current size of a PostgreSQL table (vs disk space used)

There are two extensions that may be helpful

pgstattuple provides various functions to obtain tuple-level statistics,
pg_freespacemap  provides a means for examining the free space map (FSM).


What does duration measurement in sql server actually measure?

Duration represents the total time from start to finish the request by SQL Server, which represents time to send over network, cpu time, waits, and execution. As Aaron mentioned once the client has received the data SQL Server has finished it's timing, and the client is then processing. This should not be part of the duration.


SQL Server Extended Events - How to filter statements by Table?

The sql_statement_completed event does not have an object_id or object_name field you can use to filter the events you are capturing.

It seems like you are trying to capture all the statements that change the data in one of your tables: you can capture that kind of information using the lock_acquired event and filtering for IX or X mode and the object_id you want to include.

Unfortunately, this will produce a large number of events, of which you probably want just one. You can overcome this problem by capturing the events in a histogram target or by streaming the events with the streaming API.

I blogged about something similar here: Tracking Table Usage and Identifying Unused Objects.

Of course, if you're on Enterprise Edition, you can always use an Audit for this purpose.

Hope this helps


Access Sharing database over Network vs Mysql multiple users

When the own vendor says the following:

  "While Microsoft Jet is consciously (and continually) updated with
  many quality, functional, and performance improvements, it was not
  intended (or architected) for the high-stress performance required by
  24x7 scenarios, ACID transactions, or unlimited users, that is,
  scenarios where there has to be absolute data integrity or very high
  concurrency."

  "When you need unlimited users, 24x7 support, and ACID transactions,
  Microsoft strongly recommends that you use Microsoft SQL Server with
  Internet Information Server (IIS). "

I wouldn't have many expectations for integrity and performance for multiple users over a network.


SQL Server :: how to move country code column to another table?

Is this what you are looking for?
--demo setup
drop table if exists tbl_Countries;
drop table if exists population_by_country_2020;
go
CREATE TABLE tbl_Countries (
  CountryName VARCHAR(17),
  TwoCharCountryCode varchar(20),
  ThreeCharCountryCode VARCHAR(3)
);

INSERT INTO tbl_Countries
  (CountryName, TwoCharCountryCode, ThreeCharCountryCode)
VALUES
  ('Afghanistan', 'AF', 'AFG'),
  ('Aland Islands', 'AX', 'ALA'),
  ('Albania', 'AL', 'ALB'),
  ('Algeria', 'DZ', 'DZA'),
  ('American Samoa', 'AS', 'ASM'),
  ('Andorra', 'AD', 'AND'),
  ('Angola', 'AO', 'AGO');

CREATE TABLE population_by_country_2020 (
  CountryName VARCHAR(19),
  TwoCharCountryCode varchar(2),
  ThreeCharCountryCode VARCHAR(4)
);

INSERT INTO population_by_country_2020
  (CountryName, TwoCharCountryCode, ThreeCharCountryCode)
VALUES
  ('Afghanistan', NULL, NULL),
  ('Albania', NULL, NULL),
  ('Algeria', NULL, NULL),
  ('American Samoa', NULL, NULL),
  ('Andorra', NULL, NULL),
  ('Angola', NULL, NULL),
  ('Anguilla', NULL, NULL);

-- the solution
UPDATE p
SET p.TwoCharCountryCode = c.TwoCharCountryCode
    ,p.ThreeCharCountryCode = c.ThreeCharCountryCode
FROM population_by_country_2020 p
JOIN tbl_Countries c
    ON c.CountryName = p.CountryName

--verfy
SELECT *
FROM population_by_country_2020

|                |                    |                      |
|----------------|--------------------|----------------------|
| CountryName    | TwoCharCountryCode | ThreeCharCountryCode |
| Afghanistan    | AF                 | AFG                  |
| Albania        | AL                 | ALB                  |
| Algeria        | DZ                 | DZA                  |
| American Samoa | AS                 | ASM                  |
| Andorra        | AD                 | AND                  |
| Angola         | AO                 | AGO                  |
| Anguilla       | NULL               | NULL                 |


Can a trigger with an "insert into" statement cause Intext Exclusive locks for small insert operations?

The short answer to your question is - Yes, any insert, even a single row, can cause an Intent-Exclusive lock, but on the higher levels in the lock hierarchy than the actual Exclusive lock that was taken.

The Intent-Exclusive lock indicates that a request has taken an Exclusive lock at a lower level in the Lock Hierarchy (Database (High) -> Table -> Page -> Row (Low)). For example, if you're seeing the IX lock at the table level, it indicates a request has an X lock at the page or row level. If you see the IX lock at the page level, then there is an X lock at the row level. Note that in lock escalation, Row doesn't escalate to Page, instead both Row and Page locks escalate to Table locks.

The Intent Exclusive lock isn't indicating lock escalation per se, it is there to control locks on higher-level resources to ensure the isolation level of all transactions is respected. If your trigger is updating 6 rows then it will most likely only have row or page level Exclusive locks, and the table-level will show an Intent Exclusive lock. This affects other sessions trying to take locks on higher-level resources by indicating lower-level resources are already locked.

Check out this article for a good explanation of the different locks and their compatibility with one another.


Table JOIN and CREATE new table extremely slow

You are doing DDL along with the SELECT

Doing CREATE TABLE AS SELECT mechanically does two commands

CREATE TABLE
INSERT INTO ... SELECT

This will produce locks on both calls and planned

I have written about this behavior in some of my older posts

Mar 23, 2012 : MySQL Locks while CREATE TABLE AS SELECT
Aug 08, 2014 : MySQL consistent nonlocking reads vs. INSERT ... SELECT


get previous 12 month data and if not exist then add zero in mysql

Build a table of all months.
LEFT JOIN to that table.  Viola, the zeros show up.

Well, you might get NULLs instead of zeros.  So use the IFNULL(application, 0) function.

BTW, MariaDB has a nifty way to generate sequences of numbers; see "seq".


Alter stored procedure - linked server not connected

You 'could' temporarily change the linked server 'connection' to point to a Sql instance that is available, but be aware that linked servers do not 'honor' Deferred Name Resolution and any object referenced by the linked server will need to exist on the temporary connection.  Here is someone with a 'similar' problem and some suggestions.


There is obviously no rule, but it's probably a good idea to be consistent. It's also a question of whether these schema changes will be executed by someone perhaps less skilled in running SQL scripts, e.g. if the database and its application is a product you're distributing to others.
While MariaDB supports statements like ALTER TABLE my_table DROP COLUMN IF EXISTS col_name; (see here) , MySQL doesn't have this. You could create a stored procedure to do this, perhaps something like this.
Stored procedures are great for a number of tasks, but this sounds like more work than it's worth. I won't reject the idea completely, as it all depends on your specific use-cases. When migrating big tables you may instead want to use a tool like pt-online-schema-change in Percona Toolkit.


Having a query cache of 45GB is definitely not a good idea. It should not be more than say 128MB, usually much lower numbers are suggested, or even totally disabling it. Query cache of this size makes everything slow because on any update to any table it has to be traversed and entries invalidated, and you can have many entries to invalidate in 45GB.

The crash itself looks like a bug in the server for handling the big cache (signal 11 is a segfault afaik). You can check if it is known and if not then submit a bug report.

But definitely set the cache size well under 1GB, it is better to give that memory to innodb buffer pool.


Execute DDL statements from a query in stored procedure

Use a cursor to loop through the table, then use EXEC sys.sp_executesql for each row.

The following code relies on a temporary table, named #t, which contains various DML statements:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t
(
    cmdOrder int NOT NULL IDENTITY(1,1)
    , cmd nvarchar(max) NOT NULL
);

INSERT INTO #t (cmd)
VALUES (N'SELECT 1')
    , (N'SELECT 2');

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT t.cmd
FROM #t t
ORDER BY t.cmdOrder;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    EXEC sys.sp_executesql @cmd;
    PRINT N'';
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The output looks like:

Compound statements, like IF EXISTS (...) must occur in a single statement.  If you need to run all rows as if they were a single statement, simply concatenate the output from each row, then use sys.sp_executesql on the result, as in:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t
(
    cmdOrder int NOT NULL IDENTITY(1,1)
    , cmd nvarchar(max) NOT NULL
);

INSERT INTO #t (cmd)
VALUES (N'IF (1=2)')
    , (N'PRINT N''yes''')
    , (N'ELSE')
    , (N'PRINT N''no''');

DECLARE @line nvarchar(max);
DECLARE @cmd nvarchar(max) = '';
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT t.cmd
FROM #t t
ORDER BY t.cmdOrder;
OPEN cur;
FETCH NEXT FROM cur INTO @line;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = @cmd + CHAR(13) + CHAR(10) + @line;
    FETCH NEXT FROM cur INTO @line;
END
CLOSE cur;
DEALLOCATE cur;
EXEC sys.sp_executesql @cmd;

The output from the above code:

no

If you are allergic to cursors, you could use a set-based approach, such as:

DECLARE @cmd nvarchar(max) = '';
SELECT @cmd = CASE WHEN @cmd = '' THEN '' ELSE @cmd END + CHAR(13) + CHAR(10) + t.cmd
FROM #t t
ORDER BY t.cmdOrder;
EXEC sys.sp_executesql @cmd;

Understand there is a slight chance that using the set-based approach with string concatenation may cause the commands to be executed in the wrong order, even though there is an ORDER BY clause.  The SQL Server query optimizer can do strange things under the hood if it thinks the query will perform faster.


SSAS error deploying project: Ensure that 'SQL Browser' service is running

i'm not sure to have a good view of what you are doing...
But i can say that SASS service is not included with sql server express. You can only have the reletional database service .\SQLEXPRESS, not the analytics database that is SSAS.
https://docs.microsoft.com/en-us/analysis-services/analysis-services-features-supported-by-the-editions-of-sql-server-2016?view=asallproducts-allversions
Instead of Express edition, download devenloper edition. It's free for not production env and full of features like enterprise edition. So you can test everything. Don't use it in production.
During sql dev setup be sure to select SASS instance too:


SQL Server 2014 Failover cluster - Nodes on different patch levels

SQL Server versions on nodes were different and SQL Server needed to do upgrade or downgrade after failover. This increased failover time.


Snapshot Backups for SQL Server Databases

Cons:

necessitates installing the add-on for Commvault prior to running a restore.  In an emergency, this can add a brutal layer of indirection.
prevents piece-meal-restores of individual file groups, since the entire database must be restored to be consistent.
You need to know how to use Commvault, instead of just the built-in tools.
backup history is obfuscated inside SQL Server, such that you only see a guid as the target of a backup.

Pros:

you may get better compression of the backup file
storage guys feel like they're managing backups (maybe a con, quite frankly)
backup process may be faster.

There are undoubtedly a metric ton of other pros and cons I'm not thinking of.

Personally, as a DBA, I'd way rather use SQL Server native backups.

An exercise that might help you decide on an appropriate methodology might be to get the business folks to thoroughly evaluate their recovery point objective and recovery time objective.

If they can afford to lose 24 hours of data in those databases, probably nightly backups are ok.  If you can't afford to lose more than, say, 15 minutes of data, you'll need 15 minute log backups whenever data is being written to the database as well as full backups.  If the VDI/VSS backup solution doesn't offer log backups, or they can't afford the space for frequent full backups, then you know you cannot use that kind of system.  As for recovery time objective, if they can put up with a week of downtime after a disaster occurs, then you're probably ok to go with a VDI/VSS-based backup system.


Spooling Oracle XML data results in 1 line and missing data?

You're probably hitting the default "long" data type display limit of 80 characters:

SET LONG {80 | n}
Sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.


Just keep the keys. You'll be able for example to locate data in old database much more easily.

But remember to update sequences associated with your keys after migration so they will have a value bigger than the biggest key in database.


Would a NoSQL solution be relevant for a real-time mobile game?

The short answer is that NoSQL, NewSQL, OODB or just plain SQL are all options, but SQL is probably the most viable.  Many NoSQL implementations can be configured for ACID compliance at the cost of performance/scalability, but they will most likely still scale beyond most reasonable SQL implementations.  Another option is to use application sharded SQL to get scalable SQL with minimal effort/cost, and in many cases is very viable. Generally the architecture and testing is more critical than the data store, the most scalable data store may fall flat on its rear with the wrong architecture.

For a startup, getting the product out is often the primary consideration, and SQL is generally the quickest to market due to it being a known entity and experienced developers have the most experience with it.  The biggest problem is usually your available resources, do they have the knowledge and experience with NoSQL? Or do you have the time to test, configure, test again?

pSQL is relatively known, its limitations are known.  NoSQL has 10s of viable options, and 100s in some state of development.  Finding the right one and understanding its limitations is a pretty significant project by itself.  NewSQL is very new, in 3-5 years it may very well be the defacto DB for large scale projects.  Any small shop should probably stay away until it gets more significant production usage by larger companies that have the resources to diagnose and work around the inevitable problems.


Representing N:N relation as a functional dependency in a database design

The question you raise has to do with the definition of first normal form (1NF). Whether the answer directly involves functional dependencies depends in part on the definitions you accept. Wikipedia has a fairly simple article about 1NF.

title                                author    year  category
--
An Introduction to Database Systems  CJ Date   2003  databases, modeling, storage, retrieval

If you look at the column "category" one way, it contains a single value. Depending on your dbms and your design, that value might be the string "databases, modeling, storage, retrieval", or it might be the array "{databases, modeling, storage, retrieval}".

If you look at the column "category" another way, it contains four values. Those values are the four strings "databases", "modeling", "storage", and "retrieval".

In database design, the solution is to use two tables. But I don't think you can decompose the "bad" table by projection (which CJ Date identifies as the decomposition operator), because projection doesn't split the content of a column into multiple rows. (Projection doesn't give you four rows from the single value "databases, modeling, storage, retrieval", which is what you need to do. "Join", the recomposition operator, doesn't yield a single value like "databases, modeling, storage, retrieval", either.)

The inability to decompose by projection suggests that the solution to this problem doesn't have to do with functional dependencies. The resulting table would have three attributes, {title, author, category}, the only key would also be {title, author, category}, and that table would be in 5NF.


You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the subscriber was unreliable. In that case you could simply log ship to the subscriber - you can get pretty close to real time here, though you will have to kick users out temporarily as you restore new logs. If log shipping the entire database is a concern, then you could instead use a trigger to populate another database on the same instance - which only has this table and the required columns populated - and then log ship that database to the other instance:

I have a blog post on making log shipping work much more like a set of readable secondaries:L

Readable Secondaries on a Budget

You can also look at these posts for an approach I've used to do something a lot like replication but with more control, less impact, and none of the troubleshooting nightmares:

Schema Switch-A-Roo
Schema Switch-A-Roo : Part 2

I also talk about some ways to provide read-only copies of the data for reporting at the end of this post:

Bad habits : Putting NOLOCK everywhere


Copying a table in SQL Server database to Oracle database

this can be done if you are using SQL Server Management Studio. The method is as follows:-

Goto the source schema on SQL Server

Right click > Export data

Select source as current schema

Select destination as "Oracle OLE provider"

Select properties, then add the service name into the first box, then username and
password, be sure to click "remember password"

Enter query to get desired results to be migrated

Enter table name, then click the "Edit" button

Alter mappings, change nvarchars to varchar2, and INTEGER to NUMBER

Run

Repeat process for remaining tables, save as jobs if you need to do this again in the future


Linking identifiers across tables in MySQL (Foreign Keys)

Is there a way to tell MySQL that the ContactPhone.ContactID field is referencing the Primary Key of Contact?

Yes, use a foreign key.

  Can there be automatic handling? If the Contact is deleted, then relevant ContactPhone.ContactID should be set to zero.

Yes, add in an action after ON DELETE in the below code.

Here's an example:

create table ContactPhone(
  ContactID int not null,
  PhoneNumber text not null,
  CONSTRAINT `fk_contactid`      -- this is the foreign key
    FOREIGN KEY (`ContactID` )
    REFERENCES `Contact`(`id` )
    ON DELETE <some action>      -- the on delete action goes here
) engine = InnoDB;               -- make sure to use InnoDB if you want FKs to be enforced

Make sure that either InnoDB is specified as the storage engine, or that your version of MySQL defaults to InnoDB.  Other storage engines will parse and silently ignore foreign key constraints.

Here's the grammar for a MySQL foreign key:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION


How to obtain SQL feature list from the command line

The components for SQL Server 2012 are divided by server and then management tools. You can get the server level components by using a PowerShell command like:

Get-Service *SQL*

To get the management tools would require either registry search for the uninstall list or you can query the WMI class win32_product:

get-wmiobject win32_product |
where {$_.Name -match "SQL" -AND $_.vendor -eq "Microsoft Corporation"} |
select name, version


Creating a table from the existing table along with the constraints in oracle

You may try to obtain the source table's DDL using:

select dbms_metadata.get_ddl( 'TABLE', 'SCHEMA_NAME', 'DATABASE_NAME' ) from dual;

Select it into a variable, replace table name to one you need (maybe edit or remove something else) and execute.


Moving SQL Server instances from one datacenter to another

So the challenge here is to keep the server name, database name, and IP the same.

I've done this many times in the past without any issue. I always get another SQL Server built and get everything defined over on it as is currently on the SQL Server I'm migrating from such as linked servers, credentials, proxies, logins with matching sids and password hashes, SQL Agent jobs, and on and on.

Be sure the Server OS on the new SQL Server has a "different" host name before installing SQL Server like ServerNameNew and a different IP Address, get the SQL objects defined or copied over (e.g. first paragraph above), tested and confirmed as best you can.

When it's time for the transition you will:

Change the hostname and IP Address on the original server to
something different
Rename the new SQL Server OS to the original server host name
Assign the same IP Address(es) back to the NIC(s) on the new SQL Server
Change SQL meta data database name

  To rename a computer that hosts a stand-alone instance of SQL Server

  For a renamed computer that hosts a default instance of SQL Server, run the following procedures:

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO

  Restart the instance of SQL Server.

  For a renamed computer that hosts a named instance of SQL Server, run the following procedures:

sp_dropserver <old_name\instancename>;
GO
sp_addserver <new_name\instancename>, local;
GO

  Restart the instance of SQL Server.

  source

If you get the new SQL Server built and define all the objects to match what's defined on the old SQL Server, and then test as much as you can until you feel comfortable with the testing results, make the changes as mentioned and everything should work as expected assuming you do not overlook anything in your test setup.

Important: Have other people help test once you get the DB objects defined, etc. and run through your rounds of DBA testing or whatever. The more eyes and people testing the new setup, the better.

Further Resources

Why are you still using sp_help_revlogin?
sp_help_revlogin (legacy)
SQL Server @@SERVERNAME returning old machine name?


Should fields that are set to Not Null also have a constraint refusing empty strings?

If I'm expecting the value of the field to be not null, shouldn't I also always have a constraint that does not allow for zero length strings or space only strings

Yes.  Yes you should.

It's ANSI standard behavior to enable storing empty strings, but that doesn't mean it's a good idea.  And you especially should not allow both Null and '' in a column.

And note that because strings are compared without trailing spaces,

 check (SomeCol<>'')

suffices for both.


Comparing Tool for Reporting Services Objects

So basically what @saihtam8 said in his comment.

This SO thread might be helpful to you: Where does a published RDL file sit?

Essentially just compare the report BLOB columns (Content? in SQL Server 2008 R2) in the Catalog tables across each server instance.
In SQL Server 2008 R2, it looks like there are DataSets and DataSource tables as well which you can compare.


Are logs in the LDF file being removed after a checkpoint?

If your database is in simple recovery the log might be truncated after a checkpoint.

This does not always occur though for reasons discussed in Factors That Can Delay Log Truncation (the most obvious being an open transaction)

To see the inactive portion of the log you can use the undocumented DBCC log command.

DBCC log(0,1) /*First parameter is database id with 0 as current DB*/

or trace flag 2537 in conjunction with sys.fn_dblog

DBCC TRACEON(2537)

SELECT *
FROM sys.fn_dblog(NULL,NULL)

Of course there is no guarantee that the thing you are looking for will be there. It may be overwritten at any moment.


Causing ORA errors in a program using JDBC. How can I find what's causing them?

Either set tracing on client(jdbc) side. Or you can create system trigger ON SYSTEMERROR see this, but this requires DBA privs.


Troubleshooting a slow performing encrypted stored procedure

Notwithstanding that your question is about how to troubleshoot without decrypting the stored procedure, I would suggest that you do decyrpt the stored procedure so you can do troubleshooting as required, without guessing what's going on.

"Encrypted" stored procedures in SQL Server are actually not encrypted AT ALL.  They are simply obfuscated using a pretty simple method.  This means you can very easily de-obfuscate them and see the source code.  Having said that you might violate the terms of the End-User-License-Agreement included with the vendor's software.

Use the details found in this question about how to de-obfuscate the "encrypted" stored procedure.

If the EULA forbids decrypting the stored proc, the best course of action would be to contact the vendor to have them do the performance troubleshooting.   You do have a maintenance contract, right?


Does view take advantage of table indexes

A view is just a "saved query". The indexes on the base table are still used whenever you access the view.

You don't need to use an indexed view, unless the view contains an expensive logic (aggregations or joins) that you don't want to perform each time you query the view. Please note that even when the view is "materialized", the optimizer is free to access the base tables and their indexes directly and ignore the index on the view altogether.

Also note that keeping the index on the view aligned with the data in the base tables is expensive: every time you write to one of the base tables you have to update the index on the view as well.


Windows cluster failed over, but SQL instances didn't move

Clustering is complex, and there are lots of moving parts (no pun intended). Let me try to break this down into more manageable chunks:

From a terminology perspective, there's your Windows Server Failover Cluster (WSFC), and your SQL Server Failover Cluster Instances (FCI). I try to avoid saying "Cluster" and use these acronyms to avoid ambiguity.

Quorum:
The quorum is the number of votes necessary to transact business on your WSFC. Depending on your WSFC configuration, voters can be nodes (servers), a drive, or a file share. You need more than 50% of your votes in order for the WSFC to be online. If you lose 50% or more of your voters, then the WSFC and all clustered services (including your FCI) will go offline and not come back until you have (or force) quorum.

In your configuration, you have two nodes, and one file share for a total of three votes. Any one of those voters can go offline. When you lost the file share, you still had two nodes online, so your WSFC and all clustered services stayed online.

Cluster Owner/Host Server:
When you say that "Node2 was now specified as the active node by Windows", I suspect you are referring to the "Current Host Server" for the cluster. So what is that?

Your WSFC has a network name and an IP address. That name & IP has to be tied to a machine that is part of your cluster. More specifically, it can be tied to any one machine in your cluster. This is part of your WSFC, but not your FCI.

In your scenario, you have three FCIs on a two-node WSFC. It would be a perfectly valid to have one FCI on Node1, and two FCIs on Node2. And the "Current Host Server" for the WSFC could be either node. SQL Server won't care.

So what happened: As you said, there were no adverse effects on the databases. I'd expect that, because SQL Server isn't tied to that WSFC host server. I don't think I wouldn't have expected the host server to move when the file share failed--but I'd let your Windows guys dig into that more. From a SQL perspective, everything worked as expected.


Where do this Constant Scan and Left Outer Join come from in a trivial SELECT query plan?

The semantics of the two statements are different:

The first does not set the value of the variable if no row is found.
The second always sets the variable, including to null if no row is found.

The Constant Scan produces an empty row (with no columns!) that will result in the variable being updated in case nothing matches from the base table. The left join ensures the empty row survives the join. Variable assignment can be thought of as happening at the root node of the execution plan.
Using SELECT @result
-- Set initial value
DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'};

-- @result does not change
SELECT @result = AccountId
FROM Accounts
WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'};

SELECT @result;

Using SET @result
-- Set initial value
DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'};

-- @result set to null
SET @result =
(
    SELECT AccountId
    FROM Accounts
    WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'}
);

SELECT @result;

Execution plans
No row arrives at the root node, so no assignment occurs.
A row always arrives at the root node, so variable assignment occurs.

The extra Constant Scan and Nested Loops Left Outer Join are nothing to be concerned about. The join in particular is cheap since it is guaranteed to encounter one row on its outer input, and at most one row (in your example) on the inner input.
There are other ways to ensure a row is generated from the subquery to ensure a variable assignment occurs. One is to use a redundant scalar aggregate (no group by clause):
-- Set initial value
DECLARE @result uniqueidentifier = {guid 'FE2CA909-1162-4C6C-A7AC-33B257E28539'};

-- @result set to null
SET @result =
    (
        SELECT MAX(AccountId)
        FROM Accounts
        WHERE AccountId={guid '7AD4D33C-1ED7-4183-B7F3-48C33D666525'}
    );
SELECT @result;

Notice the scalar aggregate produces a row even though it receives no input.
Documentation:

SET @local_variable (Transact-SQL)
SELECT @local_variable (Transact-SQL)

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.
For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.

Further reading:

Fun with Scalar and Vector Aggregates


How to allow in-progress transactions to complete when shutting down PostgreSQL?

I don't know of a way to do this automatically.  You could start a smart shutdown, and then loop through live connections terminating them if they are idle.  This would be a bit fragile, as if your grim reaper script were to lose its connection it could not re-establish it.  A less fragile way would be to edit pg_hba.conf to block all connections except from the grim reaper (instead of starting a smart shutdown), then do the same loop as above.  Once there are no other connections, you can do a regular fast shutdown.

But this might not be necessary because:

  fast will kill all connections whether active or idle, which seems to set me up to risk corrupting an in-progress transaction

There will be no corruption  It will either commit, or will rollback with an error reported to the client.  The only risk is that a poorly written client might not correctly deal with the error, but if that is the case you probably have bigger problems than just expanding your EBS.


What's the best way to migrate a database to a server of a lower SQL version?

BCP by itself may not be sufficient as it won't address non-table objects like stored procedures. To migrate the schema, one option would be to use a Visual Studio database project importing it from the 2016 instance and deploying it to your 2012 instance. Afterwards, two options to import your data rows are BCP and the Import/Export wizard in SSMS.


Which MySQL directories to back up using FTP

If you FTP /etc/my.cnf, read it and look for

datadir
innodb_data_home_dir : If this is not set, ignore this.

For most MySQL installations, this would be /var/lib/mysql


sp_whoisactive keeps returning create procedure statements

Please show us the parameters of the execution of SP_whoIsActive. I remember there was a parameter switched between showing the actual statement or the batch/procedure that contained that statement.

If I'm not wrong:

@get_full_inner_text = 0 -- 1-shows SP/batch, 0-shows statement;

should be the one.
Switch it to 0 and see if it's changing the way the output is shown.


Prevent columns from having same value in record

This is called a "many-to-many" relationship. One person can be a member of multiple teams and a team has multiple members. One way to model this, could be:

create table person
(
   person_id integer primary key,
   name text not null
);

create table team
(
  team_id integer primary key,
  name text not null
);

create table team_member
(
  team_id integer not null references team,
  person_id integer not null references person,
  person_role text not null check (team_role in ('lead', 'member')),

   -- each person can only be assigned to a team once
  primary key (team_id, person_id)
);


MariaDB Slave cause "Copying to tmp table"

Not MariaDB Slave, the Query Cache.

It appears that the first Profile came from a machine where the identical query had already been stored in the Query Cache.  Hence, the query took only a fraction of a millisecond to simply deliver the old resultset to the client.

The second server apparently has not seen the identical query recently, so it had to work on the query (open tables, optimize, use a tmp table, etc).  This took a lot longer.

Addenda

The Query Cache works this way:

If a query is in the QC, and exactly the same query shows up to be run, the resultset is delivered very rapidly from the QC.  However,
When any modification to the tables(s) involved occurs, the all entries in the QC for that table(s) are purged.
Replication is a stream of modifications.

For this reason, then QC is rarely worth turning on for Production servers with a lot of write traffic.

So, I deduce that the some modification (INSERT, UPDATE, etc) occurred to that table, thereby invalidating the entry in the QC before that second copy of the SELECT was presented.

If the stream of changes from Replication had only touched other tables, then the second query would have been just as fast as the first.


Why is a .bak so much smaller than the database it's a backup of?

The space was allocated to the database files, but not used.

You can create a new database, make it 10GB in size, and see the files allocate that amount of space on disk. However, until you put data in the database, the file is essentially empty, and your backup file size will be minimal.

HTH


how to change the file directory of your log and data files and what are the effects

  Can I just change the settings of the instance to point it to the new
  directory, detach the existing databases move the data files and
  attach it back? Please share what would be the best approach in doing
  this.

Below are two scripts I've used for this purpose quite a few times in the past; just adjust accordingly for your environment.

Basically just plug in the new path locations, the data and log file names, and the logical SQL data and log file names, and then run the first script (see comment above second script below).

First Script

Plug in your variable accordingly for your environment.

ALTER DATABASE <DBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE <DBName> SET OFFLINE

ALTER DATABASE <DBName> MODIFY FILE (
    NAME = <LogicalDataFileName1>
    ,Filename = 'T:\MSSQL\Data\<DBName>.mdf'
    );

ALTER DATABASE <DBName> MODIFY FILE (
    NAME = <LogicalDataFileName2>
    ,Filename = 'T:\MSSQL\Data\<DBName1>.ndf'
    );

ALTER DATABASE <DBName> MODIFY FILE (
    NAME = <LogicalLogFileName2>
    ,Filename = 'Y:\MSSQL\Logs\<DBName>.ldf'
    );

Second Script

Before you run the below TSQL, you will need to move the applicable physical data and log files to the new locations as you plugged into the first script.

--Now move the files to the new location where you pointed them to before running the below.

ALTER DATABASE <DBName> SET ONLINE
ALTER DATABASE <DBName> SET MULTI_USER


Window aggregate resulting in slow query on postgres 9.6

Your window function has to sort 10,453,164 rows. Look at cutting that down. Sorting 10.2 million rows in 158 seconds isn't too bad.

Sort (cost=3,047,865.12..3,073,554.61 rows=10,275,796 width=55) (actual time=144,515.353..148,038.268 rows=10,453,164 loops=1)
Sort Key: a.person_id, event_page_2016_8.timestamp_
Sort Method: quicksort Memory: 1863192kB

I also think there is something wrong with your partitioning.

->  Index Only Scan using ep_2016_8_host_ts_a_ses on event_page_2016_8  (cost=0.55..4.57 rows=1 width=42) (actual time=0.016..0.016 rows=0 loops=1)
                        Index Cond: ((location_host = '2016.myhost.ca'::text) AND (timestamp_ >= '2017-02-18 00:00:00'::timestamp without time zone) AND (timestamp_ <= '2017-03-13 00:00:00'::timestamp without time zone))

Why does this query search a table called ep_2016_8_host_ts_a_ses when the timestamp clearly exists in 2017-02, and 2017-03. The query planner is supposed to know better. Look up constraint exclusion

My guess is here that the query can't make use of the partitions index to sort the table. It should be able to -- if all partitions were indexed by timestamp_ I would think it could walk through the indexes in parallel and get just the first rank fairly easily. I could be wrong though. I may have to play with this later. In the mean time, try getting constraint exclusion working and giving it another go.


Grant sysadmin permissions to 'NT AUTHORITY\SYSTEM'

You can get the name of well-known security identifiers (SIDs) using a PowerShell script:

$objSID = New-Object System.Security.Principal.SecurityIdentifier ("S-1-5-18")
$objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
$objUser.Value

Personally I never had to do this from C/C++ code. See the MSDN article about the WINAPI or System.Security namespace for managed code.

There is a compact example in the Stack Overflow Q & A WinAPI LookupAccountSid from an .evt file by Remy Lebeau:

static const DWORD MAX_BUFF_SIZE = 256;

wstring userNameFromSid(SID userSid, wstring computerName)
{
    wchar_t buffName[MAX_BUFF_SIZE];
    DWORD buffNameSize = MAX_BUFF_SIZE;
    wchar_t buffDomain[MAX_BUFF_SIZE];
    DWORD buffDomainSize = MAX_BUFF_SIZE;
    SID_NAME_USE SidType;

    if (LookupAccountSid(!computerName.empty() ? computerName.c_str() : NULL, &userSid, buffName, &buffNameSize, buffDomain, &buffDomainSize, &SidType))
    {
        return buffName;
    }

    /*Here some code to print error in a Message box*/
    return L"";
}

Is there any problem placing NT AUTHORITY\SYSTEM in the SQL Server sysadmin role when installing software from a reliable source? There should be no problem, I hope, but who knows...


How to compare write speed before and after adding index?

I want to know the methodology of testing the writing speed.

I am assuming you want to know if queries are running faster. It can be read, write, or a combination of both.  There are many approaches you can take. It depends on the usage pattern of the table. I am sure I won't be able to list everything, but the following points will give you enough tools to measure the success of your newly created non-clustered index. Despite the method you choose, always be consistent with your test. Test with the same workload and on the same hardware.

You want to confirm if the new index is being used? How can you tell if an index is, being used? by Paul Randal explain how to do that.
If you measure the effectiveness of your non clustered index by using one query (or one store procedure call), you can use some of the methods listed below.

i) Comparing CPU, time, read, write. Measuring SQL Query Performance by Matthew McGiffen explains how to do that.

ii) Adam Machanic originally wrote SQLQueryStress, which is a fantastic tool to test the performance of a single query. How do I best measure the query performance? has more details on that plus talking about statistics parser.

iii) Compare Showplan in SSMS can give some good insight into the pre and post-
performance. Compare execution plans documentation by Microsoft explains how to use it.

If you need to measure a workload (a combination of multiple queries or stored procedures), you will need to do more work.

i)As explained in the comment section by @David Browne, you can use the Experiment Assistant.

ii) You can use ostress tool before and after the change. SQL Server stress testing using OStress by Nikhilesh Patel explains how to use the tool


How can I tell WHY an insert on a certain table is slow?

Some things you can look at...

Reduce the batch size from 10000 to something smaller, like 2000 or 1000 (you didn't say how large your row size is).

Try turning on IO Stats to see just how much IO the FK lookups are taking.

What is the waiting caused by when the insert it happening (master.dbo.sysprocesses)?

Lets start here and see where we go.


Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?

There is no such mechanism in PostgreSQL.

However, you can still avoid the excessive effects of such a table change.

The following statement acquires an access exclusive lock on the table for the duration of the statement/transaction:

ALTER TABLE your_table
    ADD COLUMN new_column integer NOT NULL DEFAULT 0;

This statement changes the catalog, then rewrites the whole table so that the new column contains the default value in all rows.  If the table has many rows and being accessed frequently enough, this would cause some temporary problems.

To avoid it, try to hold the exclusive lock for as short as possible:

ALTER TABLE your_table
    ADD COLUMN new_column integer;
ALTER TABLE your_table
    ALTER COLUMN new_column SET DEFAULT 0;

As this is basically only a (actually two) change to the catalog (no data change happens), it will complete pretty fast.  Then depending on your needs and table usage, you can update the new column to the default in one step or in batches, and when finished, set the column to NOT NULL.

Update about a wish coming true: PostgreSQL 11 will have this feature.  See https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/ for more.


sp_MSforeachdb Continue on failure when running DBCC CHECKDB

Since I can't in good conscious provide a solution that is undocumented, unsupported, and likely to skip databases, using my replacement, try adding TRY/CATCH:

EXEC master.dbo.sp_foreachdb
  @command = N'
    BEGIN TRY
      DBCC CHECKDB(?) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    END TRY
    BEGIN CATCH
      PRINT ''? failed.'';
    END CATCH';

Depending on the severity of the error, though, it's possible that it will sever the connection. The above works if you set two databases offline and then add the parameter @state_desc (required since naturally the procedure skips offline databases):

ALTER DATABASE db1 SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db2 SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

EXEC master.dbo.sp_foreachdb
  @command = N'
    BEGIN TRY
      DBCC CHECKDB(?) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    END TRY
    BEGIN CATCH
      PRINT ''? failed.'';
    END CATCH',
  @state_desc = N'OFFLINE';

Output:

db1 failed.
db2 failed.

If TRY/CATCH still fails in your scenario, you might either want to stop what you're doing and deal with that issue directly before proceeding with "all's ok" checks on the other databases or, at least once you've identified a database with an issue, bypass it altogether using the @exclude_list parameter added in the First Responders Kit:

SQL-Server-First-Responder-Kit/sp_foreachdb.sql


MySQL Workbench not showing stored procedures that I have created

I've just upgraded my MySQL server version and this problem is fixed.

It must have been an issue related to using MySQL Workbench with an older version of MySQL server.


Could not complete cursor operation because the set options have changed since the cursor was declared - SQL Server

@Tony You were correct that it is the use db option that messes with the set options (not sure why).
But it is trivial to qualify the relevant queries for master and then run the core of the loop from msdb, negating the requirement to switch the database just to inject the user into that db.

   USE MASTER
   GO
    CREATE LOGIN [login1] with password = 'test12',
    CHECK_POLICY=OFF;
   GO

   USE MSDB
   GO

   declare @ejecutable nvarchar(100)
   declare @database   nvarchar(50)
   declare @DynCommand nvarchar(max)

   declare cr1_database cursor
   local fast_forward
   for
   select name
   from master.sys.server_principals
   where name like 'login1';

   open cr1_database

   fetch next from cr1_database into @ejecutable

   WHILE (@@FETCH_STATUS = 0)

   BEGIN

          SET @DynCommand =N'
         USE ?
          IF (DB_NAME () NOT IN (''DBA''))
         BEGIN

          create user [' + @ejecutable+ ']
          for login [' + @ejecutable+ '];
          exec sp_addrolemember ''db_datareader'', ['+@ejecutable+']
          exec sp_addrolemember ''db_datawriter'', ['+@ejecutable+']
          exec sp_addrolemember ''db_ddladmin'', ['+@ejecutable+']
          exec sp_addsrvrolemember ['+@ejecutable+'] , ''securityadmin''

         END '

      EXEC master..sp_foreachdb
      @command = @DynCommand,
      @user_only = 1

      BEGIN
        BEGIN
         CREATE user [login1] for login [login1]
         EXEC sp_addrolemember @rolename= 'SQLAgentReaderRole', @membername = 'login1'
         EXEC sp_addrolemember @rolename= 'db_datareader', @membername = 'login1'
        END

      END

    FETCH NEXT FROM cr1_database INTO @ejecutable
   END
   CLOSE cr1_database
   DEALLOCATE cr1_database

Which adds the user to all the user dbs + msdb.


How to properly configure SQL Database Mail to send through an Office 365 SMTP server without using an on-prem SMTP server?

It turned out we were using the wrong "Server name" and "Port" all along.
Inputting the correct server and port fixed our issue.
(It really is that simple to setup database mail through O365 :)


Why is Full Recovery model a requirement for an Availability Group?

There is no point in HA if anyhow DR technologies are allowed in simple recovery. Transaction log is a must for point-in-time recovery. The transaction log is what a DR technology uses to replay transactions that are done on Logshipped/mirrored databases, and since a log backup is not possible in simple recovery, you cannot configure HA/DR in simple recovery either. In mirroring, a transaction log block change is sent to a mirror server; in log shipping, a transaction log backup is sent. So, you can see the very basic concept on which Mirroring/Logshipping/AlwayON works is a Transaction log.

The transaction log is the most important part of the database—it's the only place where all changes to the database are guaranteed to be described in the event of a crash. Log records for a single transaction are linked by their LSNs and are almost like a timestamp. This timestamp is internally used to link various transactions and replay it on DR servers.

The main aim of a DR technology is to provide as little data loss as possible, and this can only be achieved if the transaction log is maintained structurally consistent. In simple recovery, transaction logs are truncated the moment the transaction commits, which means the information is not required and the space occupied by the old transaction would now be used to write a new transaction. In that case the old information is lost. If you somehow want to get it, you cannot. But in full recovery you can do it.


Replacing Filegroup Used in Last Partition Slice for RANGE LEFT-based Partition Scheme

Can this last filegroup be changed after the partition scheme is
  initially created or is the only way through creation of a new
  partition scheme and rebuilding all indexes utilizing the old scheme
  into the new scheme?

The last partition of a RANGE LEFT function is a permanent partition that cannot be removed nor can the first partition of a RANGE RIGHT function. This is one of the considerations detailed in my Table Partitioning Best Practices article and why I recommend a more intuitive RANGE RIGHT function for incremental temporal types along with explicit boundaries for data within the expected range.

You don't need to go so far as to rebuild the existing table and indexes to remediate the misnamed filegroup. If you add new partitions using your script  (taking care to ensure that the SPLIT partitions are empty), you'll end up with this scheme:

CREATE PARTITION SCHEME PS_YEAR_LEFT AS PARTITION PF_YEAR_LEFT TO (
      [DATA_1992]
    , [DATA_1993]
    , [DATA_1994]
    , [DATA_1995]
    , [DATA_1996]
    , [DATA_1995]
    );

You could create a new scheme with the last partition on a dummy filegroup (or PRIMARY), create a new aligned table, switch all partitions but the last one (which will be empty if you have no 1997+ data) into the new table, drop the old table, rename, etc. Example below.

CREATE PARTITION SCHEME PS_YEAR_LEFT_NEW AS PARTITION PF_YEAR_LEFT TO (
      [DATA_1992]
    , [DATA_1993]
    , [DATA_1994]
    , [DATA_1995]
    , [DATA_1996]
    , [PRIMARY]
    );
GO
CREATE TABLE dbo.YourTable(
    DateColumn date
) ON PS_YEAR_LEFT(DateColumn);
GO
CREATE TABLE dbo.YourTable_New(
    DateColumn date
) ON PS_YEAR_LEFT_NEW(DateColumn);
GO
DECLARE @Date date = '19921231';
WHILE @Date <= '19961231'
BEGIN
    ALTER TABLE dbo.YourTable
        SWITCH PARTITION $PARTITION.PF_YEAR_LEFT(@Date)
        TO dbo.YourTable_New PARTITION $PARTITION.PF_YEAR_LEFT(@Date);
    SET @Date = DATEADD(year,1,@Date);
END;
GO
--drop original table
--drop original partition scheme
--rename new table, constraints, indexes to original name
--update statistics


What can I do with this, will this process finish? Is there anything I can do?

This is how Ghost Cleanup works it kicks up after every 10 seconds and does the cleanup. The ghost cleanup does not filters out specific pages to cleanup, when ghost cleanup operation starts it takes up 10 pages which are marked as ghosted and cleans them up. This process happens every 10 sec unless scan which run and finds out ghost records come and says that there is no ghost records.

Yep ghost cleanup is single threaded, I/O intensive and also fills up transaction log and you cannot avoid this unless you disable it. To disable this process you can enabled trace flag TF 661 documented in This support article. As a good advise it is  NOT recommended but since you have testing system you can of course try it.

So I believe the testers are doing some big delete operation may be periodically forcing lot of ghost records and this forcing ghost cleanup.

I would suggest you wait till the process completes. Like I said it will pick not more than 10 pages and so depending on amount of pages deleted it may take some time.

Some very good readings on Ghost Cleanup process.

Inside Storage Engine Ghost Cleanup in Depth
Turning Off Ghost Cleanup Tasks for Performance Gains

EDIT:

  My main concern however is that if I disable the Ghost Cleanup task, how then I will clean the rows marked to be deleted?

I would like to quote from the post Turning Off Ghost Cleanup

  If you disable the ghost cleanup task, the space taken up by deleted records will NOT be released for reuse by SQL Server until you do something else to remove it, like rebuilding an index.

One method people sometimes consider is to force ghost cleanup to clean everything by performing a table or index scan (thus queuing all the deleted records up for the ghost cleanup task). Although this is an alternative, it still uses the ghost cleanup task to do the work, and on a very busy system with a very large number of deletes (warning: generalization! :-) it can be much more efficient to remove the deleted-but-not-yet-reclaimed records using index reorganize or index rebuild.


Actually, you could go a little further and take the Author out and having a new table named Authors and you could be more rigid and take publication as well.

Book:

Book_num  Book_titl  Author_id  publication edition

200       physics      1        MACGROW      1ST

201       math         2        MACGROW      2ND

204       new java     3        ALPHA        4TH

Author:

Author_id Author_name

   1      john

   2      martin,tina

   3      korth,chira

Still, remember that going full onto normalization has drawbacks on performance as well.

It all depends on the business and how you are trying to manage the data.


PostgreSQL join subquery can't restrict query

You want to filter out groups of rows rather than individual rows. That is, you want to keep only the groups that have ue.user_id = 1. Therefore use HAVING, rather than WHERE or ON, to add that condition, because HAVING is used for group filtering:

SELECT
    SUM(owe)
FROM (
    SELECT
        (expenses.amount/count(*))
    AS owe
    FROM expenses
    LEFT JOIN user_expenses
    ON (
        expenses.id = user_expenses.expense_id
        )
    WHERE expenses.paid_by != 1
    GROUP BY expenses.id
    HAVING COUNT(ue.user_id = 1 OR NULL) > 0
    )
AS total;


Use the information_schema.columns table

i.e.

select table_name,column_name from information_schema.columns


What platform for a large record based mathematical database?

There are a number of options and please don't limit yourself to my answer here.  In particular you may find array-native databases to be of help.  My answer is going to be specifically about your questions on SQL-based databases.

It sounds to me like this is a question of geospacial information.  SQL-based databases are in fact used in such fields quite well, but this is also a specialist field within databases.

Among the SQL databases in this area, PostgreSQL, with the PostGIS add-on is considered one of the best.  If I were you, this is where I would start.  The primary advantage of SQL is that it preserves flexibility down the road regarding re-use of your data for uses you haven't thought of yet.  Doing this with good geospacial support means that you can calculate distance across a large area without worrying about the specifics of spherical trig.

Of course this only becomes a factor with very large grids.  For smaller grids, where the curvature of the earth can be disregarded, PostgreSQL also has a range of geometric types including points on a coordinate system which can be used.  I mention this because it isn't clear how large of an area is being surveyed and whether one can assume plane geometry or not.

Even so PostGIS may still simplify things by allowing representations and calculations on 3- and 4-dimensional geometric coordinate systems.

Also note that you say your sites are not necessarily square.  In PostgreSQL one thing you can do (either using the geometric types or PostGIS) is define a non-rectangular boundary to each site so you can check to make sure a point is inside the bounds of the site before saving the measurement.

Declarative Language Impacts

This concern I think is overblown.  People can and do write SQL queries as if they are a part of the imperative language of the program they are calling them from.  For most of your queries it won't matter.

What people mean by a declarative language is that within a query, the structure tells the database what information you want, not how to get it.  This is important when you want complex information from the database because basically it means that if you can ask the right question (and your data is valid) you will get the right answer.

The big difference that occurs however is that long SQL queries are easier to debug than long imperative subroutines, simply because one can more quickly narrow down where in the query the malfunction occurs.

How this would work

Chances are if you go this route you'd have a database and a program written in a language of your choice.  The program would send queries to the database and get the answers back.  You can also (in PostgreSQL and many other relational DB's) put your queries inside functions which can then be called by the application, giving more of an imperative or functional interface.  Data would be stored on disk and accessed from a separate piece of software than your program.  You could also connect with another program (from MS Access to pgAdmin) and run queries or generate reports.

In essence you can think of the RDBMS as a "math engine" which manages your data, and your program interacts with it to do what you need.


Select pseudorandom row from a very small partial index in Postgres?

Shouldn't be a problem, just put the WHERE condition of your partial index into the WHERE condition of your query.

create table foobar as select id, case when random()<0.001 then NULL else random() END as nullable, random() as z from generate_series(1,1000000) f(id);
create index on foobar (id ) where nullable is null;
select * from foobar
   where nullable is null order by random() limit 1;

This does have to read all the indexed rows, so there is only fast it can be.  It also has to "sort" all the rows, but since the sort knows about the LIMIT it is not a full sort and so is not really Nlog(N).

If you are willing to augment your table with a column of random numbers (which I had already done in my example, just for filler at the time), then you can do better.

create table foobar as select id, case when random()<0.001 then NULL else random() END as nullable, random() as z from generate_series(1,50000000) f(id);
create index on foobar (z) where nullable is null;

with rand as (select random()/1.0005 as rand)
select foobar.* from foobar,rand where nullable is null
  and z between rand and rand+ 0.0005
order  by random() limit  1;

You would have to tune the constant 0.0005 with knowledge of how many indexed rows there are.  If you make it too small, there is the possibility that the range will contain not rows and so you get no result (you could then retry it), and if you make it too large you use more time than necessary.

Without changing the table, then I think you can't do much better than reading all of the indexed tuples; without being willing to compromise on the quality of the randomness.


multi-master to single-slave replication at table level with PostgreSQL or MySQL

the cleanest solution with PostgreSQL is just to do an initdb for each server you will be replicating from, run it on a different port, and have each server replicating to a server software instance on the slave.

If you need something more complex, you should start looking at Slony, Londiste, or Bucardo.  These are trigger-based replication systems which are far more flexible than the built-in replication systems that ship with the database.  You can build amazingly flexible solutions with these, but there is a lot of design overhead.

The simplest approach is that every table on every master gets writes redirected to a partition for the master and then these partitions get replicated into separate partitions on the slave.

That may be more complicated than ETL, which may be a better option.


SSIS data flow gets stuck reading table with no errors

DanGuzman from the above comments has pointed me in the right direction. The problem was with queries getting suspended in SQL server.
To resolve this issue, I  used SELECT statement with WITH (NOLOCK) option.


SQL unpivoting multiple rows/columns, but keeping the rows grouped together, and in the same order they were selected

This uses the same basic technique as jonearles (+1) (since removed) but eliminates the PIVOT and would require only one function that turns the string into a CASE statement.

SELECT substr(
      MIN (
         CASE WHEN Blank=1 THEN '1 Blank'
              WHEN Error=1 THEN '2 Error'
              WHEN InProgress=1 THEN '3 InProgress'
              WHEN Completed=1 THEN '4 Completed'
         END
         )
   ,3) state_code
FROM m_object mo
JOIN m_form mf ON mo.id = mf.id
WHERE mo.v = :2 AND mf.formlayout_id = :1;

I assume the data looks something like this:

drop table m_object;
create table m_object as
   (select 10 id, 0 blank, 0 error, 0 inprogress, 1 completed, 99 v from dual);
insert into m_object values (11,1,0,0,0,99);
insert into m_object values (12,0,0,0,1,99);
insert into m_object values (13,0,1,1,0,99);

drop table m_form;
create table m_form as (select 10 id, 20 formlayout_id from dual);
insert into m_form values (11,21);
insert into m_form values (12,22);
insert into m_form values (13,20);

Here is what the function might look like if it were done in PL/SQL.  It was just thrown together and is only meant to illustrate building the SQL statement and is not meant to be an example of good coding.

set serveroutput on format wrapped

DECLARE
vPassed Varchar2(500) := 'Blank,Error,InProgress,Completed';

   Function MakeCase(pOriginal In Varchar2) Return Varchar2 Is
      vBuilt     Varchar2(500);
      vWord      Varchar2(100);
      vChar      Char(1);
      vWordCount Number(1) := 1;
   Begin
      For vLoop In 1..Length(pOriginal) Loop
         vChar := substr(pOriginal,vLoop,1);
         If (vChar = ',') Then
            If (vBuilt IS NULL) Then
               vBuilt := 'CASE WHEN ';
            End If;
            vBuilt := vBuilt || vWord || '=1 THEN '''
               || to_char(vWordCount,'FM0') || ' ' || vWord || ''' WHEN ';
            vWord := '';
            vWordCount := vWordCount + 1;
         Else
            vWord := vWord || vChar;
         End If;
      End Loop;
      vBuilt := vBuilt || vWord || '=1 THEN '''
         || to_char(vWordCount,'FM0') || ' ' || vWord || ''' ';
      vBuilt := vBuilt || ' END ';
      Return vBuilt;
   End;

BEGIN
   vPassed := MakeCase(vPassed);
   DBMS_Output.Put_Line(vPassed);

   --Use vPassed here to build SQL statement.
END;
/


Same query same data different server different query plan

Since you are running SP3 in QA and SP1 in production (assuming you are running with Trace Flag 4199) you are missing out on a lot of performance related fixes in your production environment.

My first suggestion would be to test the query and compare the generated plan on servers of the same build number.

Without details of the query and the underlying tables (which may be complex to post if you're talking about views referencing views, joining views) it's hard to tell which issue you are running into exactly, but for example these have been fixed since SP1:

FIX: Suboptimal execution plan is generated when you run a query in SQL Server 2012 or SQL Server 2014
FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2 or in SQL Server 2012
FIX: Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012 or 2014

But the list goes on and on.

Other than that it could be any configuration difference (MaxDOP, memory in the server, activated trace flags) or it could be statistics that are different.

In any case it's hard to compare between builds that are so far apart (SP1 and SP3)

I would suggest running the same build in QA as prod anyway because you want your QA-users to be testing the exact situation as they will be running in prod.