Convert C# logic/lambda's to T-SQL I am trying to convert the following logic in C# to a SQL Server stored procedure:

C# logic

// Filter by the search term if the first OR last name is null or empty
// || is an 'OR' operator that won't evaluate the next arguments if the first is true
if (String.IsNullOrEmpty(firstName) || String.IsNullOrEmpty(lastName))
{
        return result.Count(c => c.FirstName == searchString ||
                c.LastName == searchString || c.Brn == searchString);
}

// Filter by the first and last name if they are both present.
// Note: The first name doesn't have to be an exact match, but the last name does
// && is an 'AND' operator that won't evaluate the next arguments if the first is false
return result.Count(c => c.FirstName.StartsWith(firstName) && c.LastName == lastName);

This is what I have so far.

SELECT COUNT(*)
FROM [Services] s
INNER JOIN .[Clients] c ON c.Client_Id = s.Client_Id
INNER JOIN .[AdHoc_Referrals] r ON r.[Referral_Id] = s.[Referral_Id]
WHERE r.[ReferralTypeId] = @referralTypeId
  AND s.[Service_Delivery_Type_Id] IN ({0})
  AND s.[Inital_Service_Assignment_Date] IS NOT NULL
   OR ( -- This clause is meant to match the first lambda expression
       c.[Firstname] = @searchString
        OR c.[Surname] = @searchString
        OR r.[Reference_Number] = @searchString)
  AND (GETDATE() >= s.Inital_Service_Assignment_Date
       AND @visitDate <= DATEADD(dd, 1, COALESCE(s.Actual_Discharge_Date, s.Inital_Service_Assignment_Date))
      )

Below is a literal translation based on your sample query and as such probably isn't the optimal solution:

IF ISNULL(@firstName, N'') = N'' OR ISNULL(@lastName, N'') = N''
    BEGIN
        SELECT  COUNT(*)
        FROM  [Services] s
            INNER JOIN .[Clients] c
                ON c.Client_Id = s.Client_Id
            INNER JOIN .[AdHoc_Referrals] r
                ON r.[Referral_Id] = s.[Referral_Id]
        WHERE r.[ReferralTypeId] = @referralTypeId
            AND s.[Service_Delivery_Type_Id] IN ({0})
            AND s.[Inital_Service_Assignment_Date] IS NOT NULL
            AND (-- This should be 'AND' to filter properly
                c.[Firstname] = @searchString
                OR
                c.[Surname] = @searchString
                OR
                r.[Reference_Number] = @searchString
                )
            AND (GETDATE() >= s.Inital_Service_Assignment_Date
                    AND @visitDate <= DATEADD(dd, 1,
                            COALESCE(s.Actual_Discharge_Date,
                                s.Inital_Service_Assignment_Date))
                )
   END
ELSE
    BEGIN
        SELECT COUNT(*)
        FROM [Services] s
            INNER JOIN .[Clients] c
                ON c.Client_Id = s.Client_Id
            INNER JOIN .[AdHoc_Referrals] r
                ON r.[Referral_Id] = s.[Referral_Id]
        WHERE r.[ReferralTypeId] = @referralTypeId
            AND s.[Service_Delivery_Type_Id] IN ({0})
            AND s.[Inital_Service_Assignment_Date] IS NOT NULL
            AND ( -- Parenthesis not needed, I'm just highlighting the change
                c.[Firstname] LIKE QUOTENAME(RTRIM(@firstName) + N'%')
                AND
                c.[Surname] = @lastName
                )
            AND (GETDATE() >= s.Inital_Service_Assignment_Date
                    AND @visitDate <= DATEADD(dd, 1,
                            COALESCE(s.Actual_Discharge_Date,
                                s.Inital_Service_Assignment_Date))
                )
    END


Update two columns of two tables using inner join I have two tables in which there are two common columns (isActive & group_id)
I want to update the isActive column of two table joining the group_id.

This is the query I tried but it shows error.

UPDATE A set isActive = 0, B.isActive = 0
from OperatorGrouping A
inner join OperatorGroupingSteps B
on A.group_id = B.group_id
where A.group_id = 9

You should break your UPDATE statements into two parts

--Update A
    UPDATE A
      SET
          A.isActive = 0
    FROM OperatorGrouping A
         INNER JOIN OperatorGroupingSteps B ON A.group_id = B.group_id
    WHERE A.group_id = 9;
--Update B
    UPDATE B
      SET
          B.isActive = 0
    FROM OperatorGroupingSteps B
         INNER JOIN OperatorGrouping AB ON B.group_id = A.group_id
    WHERE A.group_id = 9;


possible to reference a jsonb_array_length in a where clause I am just starting to use some of the jsonb functionality in Postgres 9.4 (would love to move to 9.6) . I have the following query:

SELECT
    queryable_type,
    jsonb_array_length(as_data->'users_who_like') as js_users_count
FROM
    queryables
WHERE
    queryable_type = 'Item' and jt_users_count > 1;

but I am unable to access jt_users_count in the where phrase but can like this:

SELECT
    queryable_type,
    jsonb_array_length(as_data->'users_who_like') as js_users_count
FROM
    queryables
WHERE
    queryable_type = 'Item' and jsonb_array_length(as_data->'users_who_like') > 1
ORDER BY
    jsonb_array_length(as_data->'users_who_like') desc;

To be honest, this just seems a bit unusual and possibly wasteful. Is there a way for Postgres to only compute this once and use a reference to jt_users_count?

This is the standard behaviour of SQL. SQL statements are (logically) processed in a certain specified order.

Following that order, the WHERE clause is executed before the SELECT one. That's why the WHERE clause cannot access the value of js_users_count: It has not yet been computed.

That's why your second query works, but your first one doesn't.

On the other hand, it is possible to have js_users_count on the ORDER BY, because that clause is processed after the SELECT:

SELECT
    queryable_type,
    jsonb_array_length(as_data->'users_who_like') as js_users_count
FROM
    queryables
WHERE
    queryable_type = 'Item' and jsonb_array_length(as_data->'users_who_like') > 1
ORDER BY
    js_users_count desc;

Don't be afraid about a certain computation being done twice. It can be either optimized (as some kind of common subexpression elimination optimization) or its impact is completely neglectable in the overall performance of a query.

If you want to really make sure that an expression is not evaluated twice, you can use one (often annoying) feature of PostgreSQL CTE: the fact that they're optimization fences (i.e.: they won't be inlined, or converted to subqueries in the final statement):

WITH q1 AS
(
    SELECT
        queryable_type,
        jsonb_array_length(as_data->'users_who_like') as js_users_count
    FROM
        queryables
    WHERE
        queryable_type = 'Item'
)
SELECT
     *
FROM
    q1
WHERE
    js_users_count > 1
ORDER BY
    js_users_count desc;

This will create a (virtual temporary) table q1 with the already computed values of jsonb_array_length. I would only do this kind of thing if the function used takes a very long time to execute (has a very high cost), and there's evidence that it's actually called twice (you can check with some RAISE DEBUG statements in the function itself, if it's been written by you).

References:

SQL Query Order of Operations
You Can Reference Your Derived SELECT Columns In Your ORDER BY Clause In SQL
SQL Programming: Understanding the Logical Order of Operations in SELECT statements
Order of execution of a Query


debugging PostgreSQL serialization failures I am trying to migrate our PostgreSQL 9.4 database from transaction level READ COMMITTED to either REPEATABLE READ or SERIALIZABLE. In either case, I'm exposed to a new set of errors with the format:

(for both)
ERROR:  could not serialize access due to concurrent update
(just for SERIALIZABLE)
ERROR:  could not serialize access due to read/write dependencies among transactions

After reading the wiki page on SSI and the docs, I thoroughly understand the error conditions which could cause these errors, how to handle them, and even best practices to avoid them.

However, I see no way to determine the data dependency which caused them from any debug output that PostgreSQL could provide, or really any debug information at all. Is there any way to get this information from the database, either by executing additional queries at the time of the rollback or through some logging mechanism?

Having this information would allow me to make application-level changes (locking, different queries, etc.) that would eliminate some of the data races to avoid an excessive number of rollbacks.

It'd be good to have, but I don't think there's really much there at the moment. It's particularly tricky because multiple statements are often involved and the culprit(s) aren't always the current running ones, especially on at-commit failures. Collecting the extra info would be ruinous for performance and memory use, but it'd be a great option to be able to turn on when debugging.

Your best bet is probably to greatly increase your log levels and make sure you have a log_line_prefix including %m, %p, %c:%l and %v:%x so you have the transaction and session information required to identify and reassemble what sessions and their xacts did from the logs.


Rebuild vs Reorganize I currently have a maintenance plan that is reorganizing indexes everyday and I would like to know if you reorganize an index everyday would you ever need to rebuild the index? If so, why?

This should largely be a Google question and might get marked, but I'll add an answer just in case it helps others.  Please see a blog post by the Brent Ozar crew:
http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

First off: ‘Reorganize’ and ‘Rebuild’ are two different operations that each reduce fragmentation in an index. They work differently toward the same end. You don’t need to run both against the same index. (I sometimes find that people are doing both against every index in a maintenance plan. That’s just double the work and NOT double the fun.)

Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).

Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Do you need to reorganize your indexes every day?  Most likely not and you're going to increase disk pressure with higher wear and tear.  A solution like Ola Hallagren's scripts help out often but make sure you don't reindex during business hours and take database size into account.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


Two slaves using the same server-id I have a MySQL master with two slaves. Due to poor configuration both slaves had the same server-id.

My questions are:

Is the slaves' data fully synced with the master or did they miss transactions and I should start a new replication ?
During the past 4 days the master server was getting stuck randomly (3 times) with no apparent reason - is this the cause? if so, why would it get stuck? (By stuck I mean that it was running, no errors in the log, but not accepting connections)
The slaves have been busy for 3 days with a big ALTER that the master long finished, does this have anything to do with it?

The server ID is used for replication to enable master and slave servers to identify themselves, uniquely. While replication may work if two slaves have the same server ID, it could affect performance. Also, having them all unique can provide help in managing and monitoring the MySQL servers participating in the replication. Currently, a MySQL master server allows slaves with the same server-ID. This is a known problem, and could cause slaves to make several smaller relay logs files. Closing and opening files as a result will consequently slow down the replication process.


MySQL difference between wait_timeout and connect_timeout I have a my.cnf file. In it I see wait_timeout as well as connect_timeout. What's the difference between these 2?

Right from the MySQL Documentation

wait_timeout : The number of seconds the server waits for activity on a noninteractive connection before closing it.
connect_timeout : The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake

EXAMPLES

If wait_timeout is 1800 (30 Minutes), the DB Connection will close in 30 minutes if the connection is idle (indicated by Sleep in the command column of the processlist).
If connect_timeout is 10, then mysqld will reject you if you cannot login (authenticate) in 10 seconds.


how do I configure postgresql to find the pg_controldata command I am trying to execute the pg_controldata command, I got the error command not found. Then I checked:

 ls -l /usr/lib/postgresql/9.4/bin/ | grep "control"
-rwxr-xr-x 1 root root   25960 feb  5 18:24 pg_controldata

Well, there is a pg_controldata, then:

Why I got the error command not found?
What kind of configuration do I have to do to my postgresql or my sistem?

Please try an answer with a code or file configuration example.

Your shell PATH environment variable is not pointing to your folder /usr/lib/postgresql/9.4/bin/.

In order to do this, use the command PATH="/usr/lib/postgresql/9.4/bin/:$PATH".

If you want to automate this, you can add this line in your shell config file (ie. ~/.bashrc) with this :
echo 'PATH="/usr/lib/postgresql/9.4/bin/:$PATH"' > ~/.bashrc.


Postgresql: How to pass an array to trigger function in TG_ARGV? I've got some fairly generic Postgres trigger functions that I'd like to re-use across a few different tables. To that end, I'm passing arguments as part of the CREATE TRIGGER statement so they'll be available in the TG_ARGV array, as described in the docs.
This works fine as long as all of my arguments are scalars, but there are a couple of them that really should be represented as arrays.
Here's a simplified example of what I'm trying to do:
CREATE OR REPLACE FUNCTION example_function() RETURNS TRIGGER AS
$$
DECLARE
    special_names text[];
    special_users text[];
BEGIN
  special_names := TG_ARGV[0];
  special_users := TG_ARGV[1];
  IF NEW.name = ANY(special_names) THEN
    NEW.name = 'special';
  END IF;
  IF NEW.user = ANY(special_users) THEN
    NEW.user = 'flag';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_create_flag_special
BEFORE INSERT ON example_table
FOR EACH ROW
EXECUTE PROCEDURE example_function(
    ARRAY['magicname1', 'magicname2']::text[],
    ARRAY['user1', 'user2']::text[]
);

Postgres complains with

ERROR:  syntax error at or near "["
LINE 5:  ARRAY['magicname1', 'magicname2']::text[],

In other contexts, that's valid syntax for an array literal (e.g. SELECT ARRAY['magicname1', 'magicname2']::text[]; works fine). I've also tried using other syntax varieties, like '{"magicname1", "magicname2"}'::text[] but without any success.
Can anyone suggest a good approach for passing an array parameter to a trigger function? Is it just impossible and I should give up and encode/decode via a JSON string or something?

You would have to use the pure string representation of the array.
CREATE TRIGGER on_create_flag_special
BEFORE INSERT ON example_table
FOR EACH ROW
EXECUTE PROCEDURE example_function(
    '{magicname1,magicname2}',
    '{user1,user2}'
);


Two master databases for two separate apps We have two different websites one is hosted in Asia datacenter called it asiadata.com and other site is hosted in Europe datacenter called it europedata.com. Currently we have only one master database server which is hosted in Europe datacenter and it’s responsible for all writes from both websites and to replicate on underlying slaves on Europe and Asia datacenter.Also both apps writes on common database.

We have complains from asiadata.com users about site slow browsing and posting and things are not visible immediately on asiadata.com. After digging we found that database is culprit because it needs to write on Europe datacenter master server first and needs to come back on Asia data center database servers slaves to synchronize data and on Europe datacenter slaves as well. All this process takes time because of geographical distance between Asia and Europe and because of network latency.

Now we decided to resolve this issue by having multiple write servers (masters) each on Asia and Europe datacenter so each site will write data locally and both masters will also be slaves of each other. And each master is responsible to replicate data on it’s own datacenter only on underlying slaves. For conflict resolution we will control it on code level. e.g. to avoid same username and other unique constraint. Hopefully, this will resolve split brain situation and this will make sure neither master broke.

Can someone please let me know if i missed anything on multiple write masters or any scenario on database level which break up replication ? I thought this will be the best way to improve my apps performance but along with that I am afraid to built this solution because MySQL community doesn’t encourages multiple write masters. Also, 90% of the database tables are MyISAM and replication is single-threaded. I am afraid both masters might will not produce the best performance because of table-level locking as both masters will also be slaves at the same time.

Also, is there any automated tool available to resolve conflicts and solve replication issues between both masters ?

Overall, please comment multi master be the best solution or is there any better alternatives.

Thank you.

Thank you for the suggestions.
But still many questions left.

Is there any automated tool available to resolve conflicts and solve replication issues between both masters ?

As long as database data concerns, we have to synchronize both datacenter data to each other (Asia & Europe). We can't restrict data to it's related datacenter only.

MULTIMASTER SETUP
I would recommend a multimaster setup only with the idea that you restrict Asia's data to one master, and Europe's data to the other master. As for the shared databases, make sure auto_increment_increment and auto_increment_offset are in full use. In addition, make sure you only write Asia's data to the shared databases at the Asian DC and likewise with Europe.
STORAGE ENGINE CONSIDERATIONS
If there is no specfic reason to keep MyISAM (such as FULLTEXT indexing or heavy volume reads), I would recommend converting everything to InnoDB. That way, if a server crashes, then crash recovery can bring databases to a consistent state. With MyISAM, tables can be left in a crashed state and repairing those tables may result in the disappearence of one or more rows of data.
HIGH AVAILABILITY
You should strengthened your HA Setup by implementing DRBD. DRBD should be setup as at each DC. What are the benefits?

You maintain a disk-level replica of the mysql data within both data centers
You can have automatic failover within each data center
You only worry about MySQL Replication between DCs

REPLICATION
To minimize data loss with MySQL Replication over geographic distance, I would recommend using smaller binary logs. By default, a binary log/relaylog is 1G (max_binlog_size and max_relay_log_size). You can make these values much smaller. I got this idea from PostgreSQL because WAL files are 16M by default. This creates more log files, but those log files will be closed and complete faster on the acting slave. If you are not using it, you should upgrade to MySQL 5.5 because it has Semisynchronous Replication. It can be tuned to detect heartbeat timeouts with some granularity.
I wrote about some of these concepts in an earlier post back on March 29, 2011.


PLS-00049 bad bind variable when compiling a trigger I'm trying to implement a dedupe trigger to a table.

CREATE OR REPLACE TRIGGER USER.TBL_ACTION_DEDUP
BEFORE INSERT
ON USER.ERRORTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
   IF (:OLD.Identifier = :NEW.Identifier) THEN
      SELECT :OLD.tally + 1 INTO :NEW.tally FROM DUAL;
   END IF;
END TBL_ACTION_DEDUP;
/

Error is:
On line:  2
PLS-00049: bad bind variable 'OLD.IDENTIFIER'

you should remove fragment: "USER.". Properly is:

CREATE OR REPLACE TRIGGER TBL_ACTION_DEDUP
BEFORE INSERT
ON ERRORTABLE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
IF (:OLD.Identifier = :NEW.Identifier) THEN
  SELECT :OLD.tally + 1 INTO :NEW.tally FROM DUAL;
END IF;
END TBL_ACTION_DEDUP;
/


tables for different sources of payments Selling software products. I have a table "licences". Each software licences is created due to a payment. I am creating tables to accept payments from different sources (e.g. manual or PayPal.) I'm having trouble creating the tables for the payments and the foreign keys to see the association between the licence and a its payment and to ensure data integrity.

OPTION 1

My initial design was to have a single payments table and then more details tables for the various payment types. The common columns go in the payments table and the uncommon columns go in the details table.

licences
--------
id
product_id
account_id
payment_id

payments
--------
id
amount
currency
details_table
details_id

paypal_payments
---------------
id
ipn
payer_status
payer_email
...

manual_payments
---------------
id
notes
...

The details_table could have a value "paypal_payments" or "manual_payments" and is an indication of the payment type. With this design, a foreign key on details_id is not easy to make, enforce, or have cascade.

OPTION 2

To rescue the design in option 1, I thought about moving the foreign key to the details tables.

licences
--------
id
product_id
account_id
payment_id

payments
--------
id
amount
currency
details_table

paypal_payments
---------------
id
payment_id
ipn
payer_status
payer_email
...

manual_payments
---------------
id
payment_id
notes
...

This does not seem ideal because a paypal payment and a manual payment could both refer to the same payment which is nonsense.

OPTION 3

I've thought about just pushing everything into one payments table and allowing NULL depending on the source of the payment. A trigger procedure could ensure that certain columns are not NULL if the particular type requires it.

licences
--------
id
product_id
account_id
payment_id

payments
--------
id
amount
currency
type
ipn
payer_status
payer_email
...
notes
...

I know this is the inelegant sledgehammer approach and I don't want to do it.

What to do? I've encountered this issue in many thought experiments and have always been stumped.

Thanks.

I normally go with modified version of (2).

payment_type (payment_type_id, PK(payment_type_id) );
payments(payment_id , payment_type_id, [other_attrubutes],
  PK(payment_id), UNIQUE(payment_id,payment_type_id), FK(payment_type_id)
);

paypal_payment(payment_id , payment_type_id, [paypal_attributes],
 PK(payment_id), FK(payment_id ,
payment_type_id)   REFERENCES payments(payment_type_id,payment_type),
 CHECK  (payment_type_id='paypal')
)
manual_payment(payment_id , payment_type, [manual_attributes],
 PK(payment_id), FK(payment_id ,
payment_type_id)   REFERENCES payments(payment_type_id,payment_type),
 CHECK  (payment_type_id='manual')
)

Check constraints on each detail table ensures that manual table details never go into paypal table, and vice versa. Unique constraint allows detail tables to have a reference to 2 columns.

(3) is also not so bad, but I don't like exclusive arcs - adding new payment type will require changes to payments, and trigger used to enforce that only one of columns is not null.


Check if file exists in step in SQL Need to create step in SQL job which check if file exist because if file exists it needed to be stop but if file not exist to continue with next step.
xp_fileexist not allowed in sql query.

You can use a Powershell command on your job and raise an error if your file is not found so that your job will stop.
Something like:

if (-not (Test-Path $file))
{
    throw [System.IO.FileNotFoundException] "$file not found."
}


parallelism repartitions, ordering, and hash matches I am just starting to delve into query plans and recently learned that "hash matches" are bad.  I know it's rare for something to be so absolute, though, and I want to determine if I have a bad hash match.

My query, which includes two tables, uses parallelism.  It uses clustered index seeks on the two tables then it goes to the step Parallelism (Repartition Streams) with partitioning type of hash after both of these seeks.  For one table, a bitmap is created and then these two are joined (inner join) by hash match.

My understanding is if the data to be joined is ordered, a merge join would be used - and this is preferable.  The clustered indexed seek is ordered.

So... I guess I'm just looking for some information on this situation.  Do hash matches always follow repartitions of streams?  Does that repartition ignore order?  Or should I not be bothered by this "evil" hash match?

Thank you!!

Hash matches are not necessarily bad @ all. Please see Grant Fritchey's awesome (and free!) book: SQL Server Execution Plans


Unable to export 8.0.1 MySQL database using MySQL Workbench 8.0 I'm unable to export my 8.0.1 MySQL database using MySQL Workbench 8.0.  When I go to perform the export via the "Administration" table, with all the default settings (I didn't change anything anywhere, not even Advanced settings) I run into this error:

09:10:26 Dumping account (all tables)
Running: /Applications/MySQLWorkbench.app/Contents/MacOS/mysqldump --defaults-file="/var/folders/m2/y4sdt3ks3_x206vw2gv37pgc0000gp/T/tmptSYqBY/extraparams.cnf"  --user=root --host=account.biblebowl.org --protocol=tcp --port=4488 --default-character-set=utf8 --no-create-info=TRUE --skip-triggers "account"
mysqldump: Couldn't execute '/*!80000 SET SESSION information_schema_stats_expiry=0 */': Unknown system variable 'information_schema_stats_expiry' (1193)

Operation failed with exitcode 2
09:10:26 Export of /Users/me/dumps/Dump20190207.sql has finished with 1 errors

I could downgrade to a previous version Workbench, but I'd prefer to get this one working and I imagine other folks are going to run into this as well.  How can I get around this error and properly export my database?

information_schema_stats_expiry was added in 8.0.3. Use the MySQL Workbench for 8.0.1. I hope you're upgrading to a stable version.


SQL Server data files and RAID I have a server with a RAID1 volume (OS + apps) and a RAID10 volume (database).

The RAID10 volume has 6 physical disks. Should I split the SQL Server database into 6 datafiles (one for each physical disk) or leave it as one single .mdf file?

Edit: to clarify, I am not concerned with the theory of having single or multiple data files, I want to know if the fact that having 6 physical disks in the RAID means that I should have exactly 6 data files, or does the fact that they in an array make this irrelevant?

Thanks

If you have a single RAID10 volume then as far as SQL Server is concerned you have one volume and you can't control how things are stored, splitting things into extra files unnecessarily will likely have detrimental effects as it would on a single disk.

If you wish to try gain performance benefits from segregating data between spindles then you need to split the drives into separate RAID arrays, perhaps four in R10 for data and two in R1 for logs, or three R1s for everything, data and logs spread over the three.

Unfortunately there is no fixed wisdom here, it will vary greatly depending on your application's I/O loads. Splitting data and logs makes little difference for many load patterns (though can make a massive difference for certain write heavy loads) for instance. The only general advice that is applicable without a lot more detail about the application is "bung it all on that volume on one file/data and one/logs and trust the I/O scheduler to be fairly bright".


How to Randomly Select a Date? I am using in a Bash Shell Script this code for retrieving a random date.

Is there a way to do that also in Microsoft SQL Server?

I can imagine that through a single SELECT query and probably by calling a few functions that can be performed easily, somehow.

Well, all you really need to do is decide on your date range, then pick a random number between 0 and the delta in days (+1), then add that many days to the start of the range.

DECLARE @start DATE = '19700101', @end DATE = SYSDATETIME();

SELECT DATEADD
(
  DAY,
  (ABS(CHECKSUM(NEWID())) % (DATEDIFF(DAY, @start, @end)+1)),
  @start
);

There are other methods you can use to achieve randomness, including RAND() and CRYPT_GEN_RANDOM(). In my experience, distribution is not particularly fantastic in any of them, and some will behave differently in a query than others, so they may or may not be adequate depending on your needs. I followed up on a comment from Paul White, but saw similarly poor distribution with the following query (of course quality of distribution is also a factor of the size of the range):

DECLARE @start DATE = '19700101', @end DATE = SYSDATETIME(), @r INT;
SET @r = RAND(CHECKSUM(NEWID())) * (DATEDIFF(DAY, @start, @end)+1);
SELECT DATEADD(DAY, @r, @start);

Documentation:

NEWID()
RAND()
CRYPT_GEN_RANDOM()


T-SQL to find Database name and if Filestream is enabled for specific Database Please help me on below query .

NOTE :  Please excuse my programming skills , the code is of mistakes .

I want the output like Database name in one column and the FileStream enabled or disabled in another column . Just 2 columns I am trying out for . Thanks

To find the Database name and if FileStream is enabled for the Database .

declare @cmd varchar(500)
set @cmd='USE ?
if exists(
 select name, Filestream from sys.database_files where type_desc='FILESTREAM')
 CASE type_desc WHEN 'FILESTREAM' THEN 'Filestream Configured For Datbase' ELSE 'Filestream NOT Configured For Datbase'
PRINT 'Filestream Configured For Datbase'
else
 print 'Filestream Not configured for database'
inner join sys.databases where name not in (''master'', ''tempdb'', ''model'', ''msdb'')')
exec sp_MSforeachdb @cmd

Reference:

Enable and configure FILESTREAM
sys.master_files (Transact-SQL)

I would rephrase you question to find databases that are using FILESTREAM. Because you can only enable FILESTREAM at an instance level in order to use it in any user database.

To find all the files related to FILESTREAM or database name you can use this query.

SELECT db.NAME       AS DBName,
       type_desc     AS FileType,
       physical_name AS Location
FROM   sys.master_files mf
       INNER JOIN sys.databases db
               ON db.database_id = mf.database_id
WHERE  type_desc = 'FILESTREAM'


"Scan interface error" when connecting to a DB2 database restored on a different server I restored a database backup taken on a different server using this script:

FORCE APPLICATIONS ALL;
RESTORE DATABASE MY_DATABASE_NAME
    FROM /backup/db2inst1
    TAKEN AT 20140104051657
    ON /data
    INTO MY_DATABASE_NAME
    WITH 2 BUFFERS
    BUFFER 1024
    PARALLELISM 1
    WITHOUT PROMPTING;
TERMINATE;

From which I receive the message:

 Restore is successful

Then I try to connect:

[db2inst1@host sql]$ db2 CONNECT TO MY_DATABASE_NAME
SQL0901N  The SQL statement or command failed because of a database system
error. (Reason "Scan interface error".)  SQLSTATE=58004

I was able to restore the exact same backup to the server it came from with no problems.  This only occurs when attempting to restore it to a different server.  The second server has a nearly identical setup (both CentOS 6.3, both using db2 10.1 express-C), the only difference being the main server has a user db2inst1 in group db2iadm1 and the second has db2inst1 in group db2inst1.  Any ideas?

Do you know which fix pack both versions were on? I found this APAR list which mentions the error if you move from FP1 back to the GA release of 10.1. It looks from what I'm seeing elsewhere on the web that this can result if you restore from a newer version of DB2 to an older version (possibly even fix pack-wise) such as the link above shows. Check to make sure both are at the same FP level to rule out the problem.


Clustered columnstore index space usage I have a simple table with a clustered columnstore index:

ID INT NOT NULL,
Hash BINARY(20) NOT NULL

This table has some billion rows and according to sp_spaceused, sys.allocation_units and SSMS reports, its size is about 25GB.

My problem is I can't account for all of this space. Querying sys.column_store_row_groups and sys.column_store_segments only gives me about 7,8GB. The index uses no dictionary: primary_dictionary_id and secondary_dictionary_id are -1 for all segments. Querying sys.column_store_dictionaries returns no rows at all.

The tuple mover has done its job and all row groups are in the compressed state. I already tried an ALTER INDEX REORGANIZE just in case.

My only idea for the size difference is some dictionary-like stuff I'm not accounting for. Any ideas on what I might be missing?

I'm running SQL Server 2017 (RTM-CU4).

EDIT 1:

This is the output from sp_spaceused for the table in question:

+--------+------------+-------------+-------------+------------+----------+
|  name  |    rows    |  reserved   |    data     | index_size |  unused  |
+--------+------------+-------------+-------------+------------+----------+
| IdsBin | 1073741824 | 25028112 KB | 25007432 KB | 16 KB      | 20664 KB |
+--------+------------+-------------+-------------+------------+----------+

EDIT 2:

This is a repro script with 1 million rows. It runs in about 1 minute on my machine. warning: it drops and recreates a new database

USE master;
GO
DROP DATABASE IF EXISTS MyDbWeirdTest;
GO
CREATE DATABASE MyDbWeirdTest;
GO
USE MyDbWeirdTest;
GO

CREATE TABLE IdsBin (
    ID INT NOT NULL,
    Hash BINARY(20) NOT NULL
);
CREATE CLUSTERED COLUMNSTORE INDEX ix1 ON IdsBin
GO

CREATE TYPE tBin AS TABLE (
    ID INT,
    Hash BINARY(20)
);
GO

CREATE OR ALTER PROCEDURE pBin (
    @ids AS dbo.tBin READONLY
)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.IdsBin
    SELECT ID, Hash FROM @ids;
END;
GO

SET NOCOUNT ON;
DECLARE @i INT = 1, @t INT = 1;
DECLARE @tvp dbo.tBin;

WHILE @t <= 1000000
BEGIN
    DELETE @tvp;
    BEGIN TRAN;
    WHILE @i <= 1000
    BEGIN
        INSERT @tvp VALUES (@t, HASHBYTES('SHA1', CAST(@t AS BINARY(4))));
        SET @i = @i + 1;
        SET @t = @t + 1;
    END;

    EXEC pBin @tvp;
    COMMIT;
    SET @i = 1;
END;
GO

ALTER INDEX ix1 on IdsBin REBUILD;
GO

For this repro, sp_spaceused shows:

+--------+----------------------+----------+----------+------------+--------+
|  name  |         rows         | reserved |   data   | index_size | unused |
+--------+----------------------+----------+----------+------------+--------+
| IdsBin | 1000000              | 22728 KB | 22640 KB | 0 KB       | 88 KB  |
+--------+----------------------+----------+----------+------------+--------+

sys.column_store_row_groups:

+-----------+----------+------------------+--------------+---------------------+-------+-------------------+------------+--------------+---------------+
| object_id | index_id | partition_number | row_group_id | delta_store_hobt_id | state | state_description | total_rows | deleted_rows | size_in_bytes |
+-----------+----------+------------------+--------------+---------------------+-------+-------------------+------------+--------------+---------------+
| 901578250 |        1 |                1 |            0 | NULL                |     3 | COMPRESSED        |    1000000 |            0 |       5896938 |
+-----------+----------+------------------+--------------+---------------------+-------+-------------------+------------+--------------+---------------+

So sp_spaceused gives me about 22MB and sys.allocation_units (not shown) agrees. But no columnstore DMVs seems to agree on that number and they say the index is less than 6MB in size.

sys.column_store_segments and sys.column_store_row_groups store some of the metadata information about the ColumnStore data, but I believe that ends up representing the compressed size. There are LOB structures that are allocated as well, and you can see the uncompressed size in the allocation unit / partition DMVs (and if you get to the pages somehow with DBCC PAGE, I bet you would see that they're relatively empty). In other words, sys.column_store_row_groups shows you how much data is stored on those pages, but doesn't add in the free space on those pages (which still take up space in the data file and in memory, like a fragmented index or an index with a really low fill factor).

I ran your repro and here is what I saw:

SELECT
  a.[type_desc],
  p.[rows],
  a.total_pages, reserved_kb = a.total_pages * 8,
  a.used_pages,  data_kb     = a.used_pages  * 8
FROM sys.allocation_units AS a
INNER JOIN sys.partitions AS p
   ON a.container_id = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID(N'dbo.IdsBin');

Results:

Other than unused, which I didn't bother calculating, the results match up with sp_spaceused:

And you can confirm these numbers in sys.dm_db_partition_stats as well:

SELECT
  lob_reserved_page_count, reserved_kb = lob_reserved_page_count * 8,
  lob_used_page_count,     data_kb     = lob_used_page_count     * 8
FROM sys.dm_db_partition_stats
WHERE [object_id] = OBJECT_ID(N'dbo.IdsBin');

Results:

You mentioned this but I just wanted to call out explicitly that sp_spaceused and allocation_units reflect the page count, regardless of how full or empty any page might be. The row groups DMV is reflecting just the actual data. The documentation states (emphasis mine):

  Size in bytes of all the data in this row group (not including metadata or shared dictionaries)

Whereas sys.dm_db_partition_stats, for example, explicitly states pages throughout, not data, although I would argue that they should specify that each LOB is an 8K page here:

  Total number of LOBs used to store and manage columnstore index in the partition.

Which number you want to trust, well, that's up to you.

As an aside, Niko Neugebauer talked about the fact that only the compressed size of the dictionaries is exposed in the columnstore DMVs here, and raised a feedback item about it. Seems to me there is other information that could be exposed in the columnstore DMVs too.


Users in SQL Server cluster I have created a 2012 WFC and SQL Server 2014 is clustered on it. The cluster works as expected but have a question on Users:

I added a windows domain group to the cluster as sysadmin.

When the user tries to log into SQL on the active server the users rights are not right. They can’t see anything. If the same user logs in from the no active server they have full admin rights, if they log in from their local machine they have the proper sysadmin rights.

Anyone have a clue as to what might be happening?

From a terminology perspective, there is a Windows Server Failover Cluster (WSFC), and a SQL Server Failover Cluster Instances (FCI). There's also an Availability Group (AG). AGs and FCIs are built on top of a WSFC. AGs and FCIs can be combined or separate. I try to avoid saying "Cluster" and use these acronyms to avoid ambiguity.

From your description, it sounds like you have an AG. An AG is a database-level of HA/DR. Anything and everything within the database is kept in sync between the replicas on your AG. For a visual, look at things in Management Studio, and use Object Explorer to drill-down to see what is contained within the database, and what is outside the database.

The sysadmin role is a server role, so therefore that permission is not kept in sync between replicas. Additionally, the server login is also outside the database, and not automatically synced. You will need to create logins and grant server-level permissions separately on every instance that is part of your AG.

Logins, server-level permissions, linked servers, SQL Server Agent Jobs, backup history, (among other things) are all stored in the master and msdb system databases, which are not part of the AG, and therefore not kept in sync between instances. You'll need to keep this in mind when you're building a solution using Availability Groups.


Allows to select on view, while hiding its definition I need to create a user in PostgreSQL with the least privileges possible. So, the user would only be allowed to do queries on specific tables and views. That user should not be allowed to get any additional info, especially not see the view definitions. How can this be done?

You cannot hide the view definition from the user without modifying the database metadata in a way that will break things. But it is no problem to show the user the view definition.

What is more important is to define the view with security_barrier = true so that the user cannot use functions with side effects to get at the underlying tables.

If you want to hide the query used to extract the data, your best choice might be to write a C function that produces the data. If the user has no access to the database server, that should be safe.


No such column error jdbc Suppose I have a table:

SERVICE={SID,NAME,PORT}

and with the help of a Java program, I want to select the SID of the known port with the help of a SQL query:

Select SID from SERVICE where PORT=port

But when I write the code snippet:

String sql="Select SID from SERVICE where PORT=port;";

where port is an integer variable but on execution I get the following error:

No such column port".How to get rid of this error?

It is generally a terrible idea to construct SQL queries the way you currently do, as it opens the door to all sorts of SQL injection attacks. To do this properly, you'll have to use Prepared Statements instead. This will also resolve all sorts of escaping issues that you're evidently having at the moment.

String[] port = new String[] {"3306"};
    PreparedStatement statement = connection.prepareStatement("String sql="Select SID from SERVICE where PORT= ?");

    for (String name: names) {
        statement.setString(1, port);
        ResultSet resultSet = statement.executeQuery();
        ...
        ...
        statement.clearParameters();
    }


Get if field has max value and display value I need to display Y or N when displaying rows in a report based on the max value for them on their version. If they have multiple versions all of them will display 'N' but the max (last version) will display 'Y'.
I have tried with case when and max but always I'm getting 'Y'
case when version = (select max(version) from t_proj_f where version = t.version) then 'Y' else 'N' end Is_last_version

Here is fiddle in case anybody can take a look
http://sqlfiddle.com/#!4/813501/4
I'm new on oracle sql any help is appreciated.

I think this will give you want you want:
 select project_id,
        version,
        stop,
        t_id,
        status,
        mp,
        case when rowno = 1 then 'Y' else 'N' end is_last_version
        from (
              select project_id,
                     version,
                     stop,
                     t_id,
                     status,
                     mp,
                     row_number() over (partition by projecT_id order by version desc)
                        as rowno
              from t_proj_f t
              order by project_id)


Nonclustered index Seek Question about Index usage:

If I create a nonclustered index like

IX_LastName_Age

Where it is created by LastName ASC and Age ASC, and there is clustered index on ID field.

Now if I run a query

Select Age from dbo.user where Age > 10

Would the execution plan use "IX_LastName_Age" or will it scan from Clustered Index?

If the table has more columns than the 3 described, it would be more efficient to scan the IX_LastName_Age index.  If the table is just those 3 columns, it may scan either the clustered index, or the IX_LastName_Age index, each would essentially be equivalent.  In no case could there be a seek given the info in this scenario.


SQL Server 2005 merge replication failure We have a customer who, when trying to sync a client (subscriber) to the server (publisher,) receives the following error:

2012-11-30 21:03:33.334 Percent Complete: 18
2012-11-30 21:03:33.334 Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.
2012-11-30 21:03:33.334 OLE DB Distributor 'EnvisionSQL\SQL2005': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-11-30 21:03:33.506 Percent Complete: 0
2012-11-30 21:03:33.506 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.
2012-11-30 21:03:33.521 Percent Complete: 0
2012-11-30 21:03:33.521 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Article 'TB_CORE_DAILY' failed data validation (rowcount only). Rowcount actual: 80367, expected: 0.
2012-11-30 21:03:33.537 Percent Complete: 0
2012-11-30 21:03:33.537 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Article 'TB_CORE_INSPECTION_VIOL' failed data validation (rowcount only). Rowcount actual: 19451, expected: 0.

The Server is 2005 and the local database engine is 2005 express.

What could be causing the mismatch of actual/expected row counts?

I'm trying to do more research on this so I thought I would re-ignite this discussion to see if anyone else has some input.

I understand how rowcount validation works, but why would I receive an expected value of 0 on a table that has known data?

Check if you have any conflicts between the subscriber and the publisher and try to resolve these conflicts.


Why is SELECT USER_NAME() returning my windows credentials and not the database user mapped to a windows security group that I am in? I am trying to implement Row Level Security and have mapped an AD Security Group to a user in my test Database. When trying to select data from a table that I have granted access to I am not getting my intended result. I have added select USER_NAME() to see what is being checked on my predicate function and I get the Windows login and not the database user.

If a Windows principal accessed the database by way of membership in a group, CURRENT_USER will return the name of the Windows principal instead of the group name.
https://docs.microsoft.com/en-us/sql/t-sql/functions/current-user-transact-sql?view=sql-server-ver15


ODBC Microsoft Access Driver: Not enough space on temporary disk I am running spatial modelling software that uses an Access database to store data needed for the computation. Each model run, data is loaded into the model from a Microsoft Access 2010 database using an SQL query and the Jet Database Engine 4.0. The connection between the software and the database is made with ODBC.

I need to run the model for thousands of locations, but after tens of model runs I get the error: “[ODBC Microsoft Access Driver] Not enough space on temporary disk”. After restarting the model, the computation runs ok for the same query and data set, but I quickly get the error again. The amount of data that is loaded differs per model run, with a maximum of ~30.000 records. When loading in only small datasets of max. 1000 records, the error does not occur.

The following did not work:

increasing the MaxLocksPerFile in the windows registry
increasing the MaxBufferSize
Compacting and repairing the .mdb file
there is enough space on my temporary disk

Interestingly, I did not have any problems with the same model on my old system (32-bit Windows XP). Only on this computer (64-bit Windows 7, 16 Gb RAM) the error occurs. The modelling software is 32-bit, and the ODBC connection is set with odbcad32.exe in “c:\Windows\SysWOW64\” directory. It seems there is a memory leak in the Windows7 version, as the same model and data works fine on an older Windows XP computer with less memory.
Any ideas are greatly appreciated!

This is due to the fact that MS Access tables have a limit of 2GB - this might be the issue ! The temp table fills up to 2 gb and then throws this error.
 As a suggestion - brake your query into some sub-query with their own where predicates and finally join just the results


How are indexes represented in an ERD? What do indexes look like in an Entity Relationship Diagram?

I've googled it and I'm unsure what the standard look of an index is in an ERD.  And I'm talking about the crowsfoot diagram that includes all of the fields.

They don't tend to be shown in ERDs. An ERD focuses on the Entities and their Relationships, but an index is a copy of the data from one (or potentially more) of the entities, created to assist in the execution of queries. While it's possible that an index could be unique and therefore contribute to the database design, they are not typically shown on ERDs.

...which is a shame, because in my experience, a database design should involve careful acknowledgement of the indexes, as they can make-or-break the effectiveness of a database system.


Which is better? A join or a SELECT IN? I have a set of ids that I extract using a join of 2 tables.
Now I need some information from 2 other tables. What would normally be the best approach?
So a SELECT IN using the set of ids I already have, against each of the other 2 tables OR
Should I try to join these tables to the first 2 I originally do to get the set of IDs?
Is there a standard practice?

Without more information about the size of data, what relevant indexes exist and the number of likely matched that will be in the "IN" list, "it depends" is the best answer you will get. Also I'm assuming you are meaning SELECT <something> FROM <somewhere> WHERE <column> IN <set> - if not then my answer will be less relevant. Giving sample queries in your questions will help improve the answers you get in response.

As a general rule though an IN clause with static values can not be parametrised so you will end up using ad-hoc SQL which is generally best avoided for a number of reasons.

If you are using SELECT within the IN clause and you are checking against a column in the inner select that is a primary key or otherwise declared UNIQUE then you will often find that the query planner will end up doing the same thing as the simple join (certainly this is often the case with MSSQL) so at that point it is a question of style/preference. I don't know if mySQL would see this equivalence or not: to test this use the EXPLAIN method to see what it decides is possible+appropriate.

Do remember though that the results of SELECT stuff FROM here WHERE here.property IN (SELECT match FROM there) could be quite different from SELECT stuff FROM here JOIN there ON there.match = here.property - the above comments are only relevant to circumstances where the two are going to be equivalent. The former will return at most one row for each found in here even if there are several rows in there where there.match = here.property where in the latter case one copy of the row from here will be returned for each match found in there. Where there is a difference in possible output the decision is made for you: use the one that gives the right result! As another general rule (for which there are exceptions, as always with general rules!) do try avoid using the JOIN method with a DISTINCT modifier where the IN variant would work.


Large MySQL database dump/restore Got a quite interesting task: Have to dump mysql databases from AWS and restore on a private cloud. Databases are quite big: 160GB, 120GB and 21GB each db.sql dump - all 3 in the same aurora rds instance, instance snapshot in AWS have 700GB. MySQL version 5.6, using innodb.
At moment it's been 20 hours sience restoration is running, my estimations are that its going to finish in 4 - 6 hours, so the complete restore will be about 24 hours and that is way too much downtime for production environment.
My question: How to tune mysql in order to speed up restoration?
Thank you in Advance.

P.S
Dump was done in 3 and a half hours.

Have you established what you are bottlenecked on in your import? If you are CPU bottlenecked, there is a very limited amount you can do other than put CPUs with faster single-thread performance into your private cloud instance.

A few things that will help:

innodb_doublewrite=0
innodb_flush_logs_on_trx_commit=0
innodb_io_capacity=<approximate amount of IOPS your storage can provide when using 16KB random writes>
innodb_log_file_size=1G
innodb_log_files_in_group=24 # you will want to change this later!
performance_schema=0
slow_query_log=0
general_log=0

Note: Make sure you remove all of these options after your import is complete, they are dangerous and not sensible for regular runtime operation! These are equivalent of making your race car go faster through weight saving that comes from removing the roll cage and seat belts.

Enabled disk write cache on your import machine's virtual disk, if the driver supports such a thing.

If you can get enough RAM during the import, once your ib_logfile* files are created, stop mysqld, put those files and ibdata1 on tmpfs, and symlink them in. When you are done, stop mysqld and move them back.

If ZFS is an option for you, put your /var/lib/mysql on ZFS on a volume with the following settings:

compression=lz4
logbias=throughput
recordsize=16k
sync=disabled
xattr=sa

This will approximately double your write throughput compared to other file systems, due to compression and sync=disabled functionality. Make sure you set sync=standard after the import is complete.

If you combine all of the above and you are disk I/O limited, you will see a performance improvement in the multiples. If you are CPU limited, the above will give you double-figure % points of improvement.


Optimize large table design First, I am a complete novice when it comes to databases, but have been given the task to speed up queries on a large set of data.  (Hundreds of millions of records)  The current implementation is a very simple data warehouse that was created long ago in Oracle.  The existing table has no primary key but each record is unique.  The table is indexed by the first two columns listed below.

The data itself is fairly simple:

device – there are multiple devices with a unique number indicator
data generation time – each device generates a set of data multiple times a day at different, random times.  Each data set covers multiple days. Sample times for individual data points in the set can be up to every second.  For times prior to the data generation time, these are measured results.  For times after the data generation time, these are the device’s prediction of what the data will be.  Queries are often pulled for a full day that compare measured verses predicted data for a given device (e.g. how well did the device predict future needs)
Date/Time of the data points
Data point 1
Data point 2

.
.
.
Data point 23

The major types of queries are:

Give me the latest data generated by a device
Give me all the data for a device for a given day (as previously described above.)
Give me the data generation times for a device on a given day

My idea to speed up queries would be to split the table up into two tables as follows:

MetaData Table (each of the first 3 will be indexed)

device
data generation time
day – This would be a new, indexed data point
Primary Key – a number with the device number, data generation time (141230073205 – for 2014 Dec 30 07:32:05), and day (150102 – for 2015 Jan 02)

Data Points Table (There will be 10s of thousands of these for each entry in the MetaData table above)

Foreign Key – that points to the Primary Key in the above MetaData table for which this particular point is valid
Date/Time of the data points
Data point 1
Data point 2
.
.
.
Data point 23

So, long story short (too late!):

Is this a valid approach to speeding up queries?
Is there a better way to organize the data?
What other things can I do to cut down on the query times?
Any sqlplus coding tips would also be greatly appreciated.

I was able to split the table up as described above.  Since I did not know the data generation time a priori, the approach above did give a 6 to 10 times speed up on queries.


MongoDB automatic failover between 2 data centers If I deploy a MongoDb replica set across only 2 data centers, if one entire data center fails, can there be automatic failover to the other data center?

Consider the situation where 1 data center has a primary, secondary and an arbiter, and the second data center has 2 secondary members. Essentially, a 5 member replica set.

If data center 2 goes down, there will still be 3 members in the replica set in data center 1. That is great.

In reverse, if data center 2 remains up and data center 1 goes down, there will only be 2 members left out of the 5 member replica set. That is not enough to have either secondary vote themselves to become primary.

Is that correct? If not, what am I doing wrong.

If this isn't possible, please let me know so I can explore other options.

Adding a third data center to house the arbiter is not an option.

First, you are correct. If the replica set doesn't have a quorum, it will relegate to secondary state.

The solution is pretty easy: put two data bearing nodes in each of the two datacenters and an arbiter on a cheapo VM in a third datacenter. Hell, a free AWS instance should do the trick. It is very unlikely that you'll loose two of three datacenters at the same time.

From my experience, everything else will fire back on you.


PGPool II - Failover when current master node dies I have a 2 node high-availability postgresql 9.5 cluster. I'm using streaming replication, PGPool II (3.5.4 [ekieboshi]), and a virtual (or floating) secondary IP all hosted on aws to accomplish this.

I've configured the PGPool Watchdog and it is running well but only seems to be executing a failover script on the failed node when the postgresql service instance dies.

I'd like to be able to execute a failover script on the slave node when the master becomes unreachable. Is there any hook for this in PGPool II? If not, is this not really even a good idea? Thanks in advance for your help.

Sounds like you might have incorrect values for your failover_command value. And Typically the failover script is run on pgpool server, not the database node.

failover_command = '/etc/pgpool-II/failover.sh %d %H %P /tmp/postgresql.trigger.failover [hostname of failover pgpool server]'

This configuration above is passing the new master candidate and an argument to my failover script which is executed on pgpool but ultimately promotes the master candidate (which is a hot standby) to a master using postgresql.


Difference between Clustered Index and Unique Clustered Index? I got difference between clustered index and unique index here. But what is the exact difference between clustered index and unique-clustered index?

AFAIK creating clustered index can be done for a column having unique values and if value is duplicate then clustered index cannot be set. Same is the case with unique-clustered index.

So i want to know the difference between them.

You can create a UNIQUE CLUSTERED INDEX only on a column (or combination of columns) that contains no duplicate data. The fact that you create the index with the UNIQUE keyword means that you are now constraining users from adding non unique data. (in the key column(s) of the clustered index)

A CLUSTERED INDEX can be created on a column (or combination of columns) that contains duplicate data. So in this case, the clustered index is not constraining you from adding non unique key data.

However, since a clustered index key is the row identifier, it needs to be able to uniquely identify a row, even if it's created on a column containing duplicate entries. Because of this, if you create a CLUSTERED INDEX for every row that is a duplicate, SQL Server adds a 4 byte uniquifier to the row, so that the index in fact becomes unique. This is all done under the hood.


What flavor of SQL is used inside Microsoft Access? I don't have much Access experience, and I took on a new Access project. I can use SQL to query the Access databases.

What SQL specification/flavor is used inside Access? Is it the same as SQL Server?

For full details read the docs: Microsoft Access SQL reference

MS Access definitely is not the same as SQL Server. The overall syntax is similar as any flavor of SQL, but it doesn't have a lot of features that were added since SQL Server 2005.

It doesn't even have some pretty basic features, like:

  DISTINCT aggregate function references. For example, Microsoft Access SQL does not allow SUM(DISTINCT columnname).
  The LIMIT TO nn ROWS clause used to limit the number of rows returned by a query. You can use only the WHERE clause to limit the
  scope of a query.

On the other hand, it has enhanced features, like:

  The TRANSFORM statement, which provides support for crosstab queries.
  Additional aggregate functions, such as StDev and VarP.
  The PARAMETERS declaration for defining parameter queries.

Window functions that were added since SQL Server 2005+, such as ROW_NUMBER, OVER clause don't exist as well.

Set of built-in functions for string or date/time manipulation are similar, but they are not the same.


Will Re-Seeding an Identity column back to 0 cause page splits? Simple table with an identity table as a clustered primary key. We unexpectedly hit the max value for the column, so I requested our DBA re-seed the table back to 0 to give us some time to examine updating the column. The table is also purged based on time, so we have a substantial gap in Id's to use up before we collide with anything. Since this is a high insert table, the DBA brought up the potential of page splits since we are no longer inserting at the end of the table.

My question is, how will SQL Server behave when the value of the identity column wraps around? Will inserting into gaps cause a performance issue? I want to consider maybe creating a sequence that will automatically cycle rather that up the id column to bigint.

My question is, how will SQL Server behave when the value of the identity column wraps around?

Assuming the index is 100% full the first few inserts at the new key value will cause one or two page splits, where half the rows on the full page are copied to the new page, and then the new row will be inserted into one of the half-full pages.

However after that you resume low-cost inserts and page allocations.  When the page fills up a new, empty page is allocated and inserted into the doubly-linked list of leaf pages.  No rows are copied to the new page, and the new row is written to the new page.

The only difference between this and an end-of-index insert is the presence of a next page after the new page.  And the next page must be updated with a reverse page pointer to the new page.

See this post for details: Good Page Splits and Sequential GUID Key Generation

  Will inserting into gaps cause a performance issue?

No.

As SQL Continues inserting into the "middle" of the index, when the target page for an insert is full, SQL will always allocate a new page.  It won't put the row on the next existing page, even if that page is not full.  The non-leaf levels of the clustered index store the starting value for each leaf-level page, so SQL can't insert the new row onto the next existing page without rewriting the non-leaf level of the index.

You can see this behavior easily by forcing page locks and looking at the lock footprint of your inserts (or less easily with DBCC PAGE). In the example below a clustered index table that can fit exactly 4 rows on a page take a series of mid-index inserts.

  Assume every page can hold 4 rows. You have full page existing rows
  with ascending key values from 10-20, and now you want to insert key
  values 1-9.

use master
drop database ps_test
go
create database ps_test
go
use ps_test
go
drop table if exists ps_test
set nocount on

go

--8,060=4*(4+2011)
create table ps_test(id int primary key, data char(2011) not null default '')

go
DBCC TRACEON(3604)
go

insert into ps_test(id) values (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21)--,22

go

dbcc ind('ps_test','ps_test',1)
--three data pages
/*

PageFID PagePID         PageType
------- ------- / /     --------
1       336             10
1       328             1
1       329             2
1       330             1
1       331             1
*/

insert into ps_test(id) values (1)

dbcc ind('ps_test','ps_test',1)
--four data pages 332 has been inserted between 328 and 330
/*

PageFID PagePID        PageType PrevPagePID NextPagePID
------- ---------/ /   -------- ----------- -----------
1       336            10       0           0
1       328            1        0           332
1       329            2        0           0
1       330            1        332         331
1       331            1        330         0
1       332            1        328         330
*/

dbcc page(ps_test,1,328,3)
-- 328 has rows 1,10 and 4048 bytes free
-- the page is split
/*
PAGE: (1:328)
...
pminlen = 2019                      m_slotCnt = 2                       m_freeCnt = 4048
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 10

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

dbcc page(ps_test,1,332,3)
--page 332 has 11,12,13 and 2024 bytes free
/*

PAGE: (1:332)
...
pminlen = 2019                      m_slotCnt = 3                       m_freeCnt = 2024
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 11
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 12
...
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 13
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/

insert into ps_test(id) values (2)
insert into ps_test(id) values (3)

dbcc ind('ps_test','ps_test',1)
--no page split.
/*

PageFID PagePID     PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- -------- ---------- ----------- ----------- ----------- -----------
1       336         10       NULL       0           0           0           0
1       328         1        0          1           332         0           0
1       329         2        1          0           0           0           0
1       330         1        0          1           331         1           332
1       331         1        0          0           0           1           330
1       332         1        0          1           330         1           328

the new rows went on page 328, which now has

pminlen = 2019                      m_slotCnt = 4                       m_freeCnt = 2024
*/

So now we have page 328 is full and the next page 332 has only 3 rows.  But the lowest value on 332 is 11.  So when we insert the value 4, does go on 332, which will cause another bad page split when 5 is inserted?

insert into ps_test(id) values (4)
dbcc ind('ps_test','ps_test',1)

/*
A new page 333 has been inserted in the list between 328 and 332

PageFID PagePID     PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- -------- ---------- ----------- ----------- ----------- -----------
1       336         10       NULL       0           0           0           0
1       328         1        0          1           333         0           0
1       329         2        1          0           0           0           0
1       330         1        0          1           331         1           332
1       331         1        0          0           0           1           330
1       332         1        0          1           330         1           333
1       333         1        0          1           332         1           328

*/

dbcc page(ps_test,1,333,3)

/*

PAGE: (1:333)
...
pminlen = 2019                      m_slotCnt = 1                       m_freeCnt = 6072
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 10
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

So another "bad page split", but this one only moved the row with id=10.  And leaves page 328 full with values 1,2,3,4

dbcc page(ps_test,1,328,3)
/*
PAGE: (1:328)
...
pminlen = 2019                      m_slotCnt = 4                       m_freeCnt = 0
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1
...
Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 2
...
Slot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 3
...
Slot 3 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 4
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

Now all the larger key values are on seperate pages, and from here on out there are no more bad page splits.  The next insert requires a new page, and only the new row is on the new page:

    insert into ps_test(id) values (5)
    dbcc ind('ps_test','ps_test',1)

/*

PageFID PagePID      PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- -----------  -------- ---------- ----------- ----------- ----------- -----------
1       336          10       NULL       0           0           0           0
1       328          1        0          1           334         0           0
1       329          2        1          0           0           0           0
1       330          1        0          1           331         1           332
1       331          1        0          0           0           1           330
1       332          1        0          1           330         1           333
1       333          1        0          1           332         1           334
1       334          1        0          1           333         1           328

*/

The new page 334, has only the last-inserted row.

 dbcc page(ps_test,1,334,3)
/*
PAGE: (1:334)
...
pminlen = 2019                      m_slotCnt = 1                       m_freeCnt = 6072
...
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 5
...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
id = 5

*/

So after a couple of initial "bad page splits" that required moving rows, subsequently new pages are allocated and spliced into the middle of doubly-linked list of leaf pages.  The new pages are allocated when a new row wont fit on the previous page and no rows are migrated to the new page.


Is there a way to get SQL Server capcity constraints dynamically I have some code which creates database tables dynamically, and I want to ensure that people don't try to do things that result in me trying to create a table with more columns than what SQL Server allows.

According to this article, the limit for the max number of columns per (nonwide) table is 1024.

However, this can obviously change in the future, so I don't want to hardcode it in my app.

Is there a way to get this value dynamically using a SQL query (e.g. by reading from some system table or proc)?

No there's nothing built in that is exposed to us.

Your app would need to maintain some sort of configuration table itself.

You can't even try and parse it out of sys.messages as it isn't hardcoded into the text for message_id=1702.

  CREATE TABLE failed because column '%.*ls' in table '%.*ls' exceeds
  the maximum of %d columns.

Your app could of course try and dynamically discover the limit by simply trying CREATE TABLE statements with ever greater amounts of columns but that is not a serious suggestion.


Azure SQL Warehouse - Data Ingestion - Convert a huge fixed width (with commas) file to delimited I am not even sure if I am framing this question right, but I will try - I have a bunch of huge text files generated from an Oracle export on a Linux system. Each file is about 30 GB in size, and I have about 50 of them.

The goal is to export this data to Azure SQL Data Warehouse. BCP in this case is not the right approach keeping in mind the size of the data, so I had to use Polybase.

After converting from ASCII to UTF8 encoding, I ran into an issue while querying the external tables. Polybase doesn't play well with the fixed width text file with a line break in each line.

The text file looks something like this:

101,102,103,104,105,106,107
108,108,109,110,111,112,113
114,115,116,117,118,119,120
121,122,123

--nothing here, just a blank line

201,202,203,204,205,206,207
208,209,210,211,212,213,214
215,216,217

Polybase tries to process from 101 through 107 and errors out complaining there were not enough columns in this file to process.

Here is what I think is happening: The fixed width and line breaks are making it treat the line break as a row delimiter.

How do I convert this file to look like below:

101,102,103,104,105,106,107,108,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123{CR}{LF}
201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217{CR}{LF}

EDIT: Here is the sample data from a file. I opened it in git bash on a windows VM.

These files are supposed have 167 columns with , as the column separator. The problem is, since each row spawns multiple lines, its difficult to process them from Polybase external tables.

Polybase is powerful but not really that sophisticated so will not be able to deal with this weird format.  You have three options as I see it:

Correct the file format at source.  Instead of having weird mix of fixed-width and delimited file format, use a standard file format, such as .csv.  This format you have where the column delimiter is either carriage return or comma I think is genuinely strange.  Are there any tools that can read this easily?  Is this a common format where you work?
Import the file specifying one of the delimiters into one row, then shred it based on the other delimiter.  I started trying this with your sample data but did not get very far.  Do different rows really have different numbers of columns?  In your sample data, row 1 has 24 columns and row 2 has 17 columns.    Please provide a small sample file eg via gist that accurately represents your data.
Write a highly customised import routine.  The point of using standard data interchange formats like csv, tab-delimted, pipe-delimited, XML, JSON etc is that you should not have to write a highly custom routine every time you want to import some data.  However, this could be an option, if you cannot change your file at source, or import it in stages.  I've been working with Azure Data Lake Analytics (ADLA) and U-SQL recently, and that might be able to do this.

Please try and answer my questions above and provide a sample file and I will try and help.


INT or TIMESTAMP for the change time column in a logging table? We're having an argument over what to use for storing change date in a new log table for our site.

One side says, use INT for the timestamp, storing the UNIX timestamp. We can convert the datetime query in PHP to a UNIX integer and query based on that (WHERE log_date BETWEEN [convert 12/25/2012 to timestamp] AND [convert 12/31/2012 to timestamp]).

The other side says, use TIMESTAMP for this; we can then use the native tools to query. (WHERE log_date BETWEEN "2012-12-25" AND "2012-12-31").

Since this is a logging table, we're not concerned about the 1970-2038 range of TIMESTAMP.

Which makes more sense?

I have done it both ways. I prefer using TIMESTAMP because it makes adhoc queries easier. If you only use a GUI to query the logs, then use whatever that programmer wants.

This one boils down to a style question because there isn't much difference in the functionality or performance.

ed


MySQL: execute an "UPDATE" only if an specific row exists I want to execute an UPDATE clause only in the case a specific row exists. Specifically, if a row in gallery_image exists with a specified position, I want to update all rows with a position higher than that value, flipping the sign of the position value.

So this is my try:

IF SELECT * FROM gallery_image WHERE position = 'X'
UPDATE gallery_image SET position = position * -1

but I get an error.

EDIT: After the first comment after my question I have tried this:

update gallery_image if ( EXISTS ( select * from gallery_image position = 8 )) set position = position * -1

but it says

  An alias was found before. (near "(" at position 24)"

The IF statement can only be used inside Stored Routines.  This explains the syntax error.

Are you trying to change all rows in the table if a certain row exists?

It's a bit clumsy, but here is a purely SQL way:

SET @x := EXISTS ( SELECT * FROM gallery_image WHERE position = 8 );
UPDATE gallery_image
    SET position = - position
    WHERE @x;

(It is not normal to change all rows of a table.  What is the application doing?  Can it be achieved without such a massive query?)

If you only want to change the one row with 8:

UPDATE gallery_image
    SET position = - position
    WHERE position = 8;


Limits of applock in MS SQL Server Microsoft SQL Server offers an arbitrary application-defined locking mechanism through the sp-getapplock command and related commands.

The key, or resource_name, is specified as a string, a varchar 255. But the documentation also mentions that the key/name is hashed.

So that leads me to wonder:

What kind of hashing function is used?
How well-distributed are the results of that hashing function?
What kind of input should I use to widen the distribution of hash result, to minimize chance of coincidental collision?
What is the theoretical and practical limits on the number of applocks I can have active?
What resources are used by applocks, such as amount of memory or limits on memory?

I am implementing a pessimistic locking mechanism for the rows of many tables for a special-needs app. As my applock key/name I plan to use a number representing each table combined with an ID number stored in each row. An example key/name might be something like mydb_myschema_table0000000142_row0000241738. I may have many of these active at a time, so I need to know about these limits.

I didn't see official documentation answering your questions, but some of these things you can yourself (on a testing/dev server first!). Here's what I found:

Unless you are hoping to use a very large number of concurrent locks, you likely will not have any problems. On SQL Server 2017 CTP2.1, I was able to acquire 1MM concurrent locks in about 26 seconds. There were no collisions, and this appeared to use ~286MB of memory.

The SQL Server documentation specifies a maximum of 2,147,483,647 locks for 32 bit and "Limited only by memory" for 64 bit (which I assume you're using).

Test script

Below is a script that acquires 1MM locks and does basic monitoring of the elapsed time, memory consumed, collisions encountered.

BEGIN TRAN;

DECLARE @result INT,
    @i INT = 1,
    @locks_attempted INT = 1000000,
    @locks_acquired INT,
    @ts DATETIME2 = SYSDATETIME();

WHILE (@i <= @locks_attempted)
BEGIN
    DECLARE @Resource NVARCHAR(255) =
        'LongStringSoThatTheFirst32CharactersAreIdentical_' + CAST(@i AS VARCHAR)
    EXEC @result = sp_getapplock @Resource = @Resource, @LockMode = 'Exclusive';
    IF (@result <> 0) THROW 50000, 'Failed to acquire lock', 0
    SET @i = @i + 1;
END

DECLARE @elapsed_ms INT = DATEDIFF(MILLISECOND, @ts, SYSDATETIME());

SELECT @locks_acquired = COUNT(*)
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
    AND resource_type = 'APPLICATION';

-- Acquired 1000000 of 1000000 locks in 26351 milliseconds
RAISERROR('Acquired %i of %i locks in %i milliseconds', 0, 1,
    @locks_acquired, @locks_attempted, @elapsed_ms);

-- ~286MB of memory for OBJECTSTORE_LOCK_MANAGER
SELECT *
FROM sys.dm_os_memory_clerks
WHERE type = 'OBJECTSTORE_LOCK_MANAGER';

ROLLBACK;

What about collisions?

I didn't observe collisions in any of my trials, but based on the documentation for sys.dm_tran_locks, it does appear that a 32 bit hash is used. With such a short hash, the odds of a collision should be fairly high (see chart below). However, even with 10MM concurrent locks, I did not achieve a collision. This might indicate that the hash is more complex under the hood, and that the resource_description column of sys.dm_tran_locks simply shows the first 32 bits of the hash (similar to the way it shows only the first 32 characters of the @Resource parameter). Or it might indicate that I was lucky.

If you are going to use this strategy, you should check to see if you get any collisions on your actual data. If you do, you could try to put unique elements (e.g., the schema_id, object_id, and your rowid) at the beginning of the @Resource. (It's not fully clear, but the documentation suggests that the first 32 characters of @Resource might be combined with the hash in order to uniquely identify the lock.)

(Image from Preshing on Programming)

Why do you want to do this?

It seems unusual to use sp_getapplock so heavily for something that seems similar the type of row-level locking that SQL Server provides on its own. I'm assuming you've carefully considered other approaches, but if you haven't yet done so, you might want to think about other solutions that don't involve sp_getapplock.


Get the data from the table in the form of fieldname and fieldvalue in SQL Server Is it possible to get the data and column name as a key-value pattern like the table tblemplyee shown below?

UNPIVOT can help you with this.

Try something like

SELECT employeenumber, fieldname,  fieldvalue
FROM tblmployee e
UNPIVOT(
    FieldValue
    FOR FieldName IN (Gender, JoinDate, MobileNo)
) U

Hope that helps


SQL Server 2008 R2 DETA Error: Can not get minimal database information in allotted time I have a 18,6 GB SQL Server 2008 R2 profiler trace file. Trace file recorded at tuning profile. When I submit this trace file to database engine tuning advisor I get this error:

  Error: Can not get minimal database information in allotted time

How can I solve this ?

Windows Server 2008 R2, SQL Server 2008 R2
24 GB ram , 16 cores server

From the comments I see you removed the default time limit like I mentioned but still the analysis didn't happen in enough time or ever complete as I feared.

18GB is definitely the largest trace I have ever heard of attempting to be analyzed by the Database Engine Tuning Advisor. I suggest you break your trc file up into smaller sizes. Either by looking at the data a bit more filtered or for a smaller window of time while still maintaining enough of a workload to capture both data modification and selects.

Alternatively -

I haven't been a big fan of the DETA and sometimes the results, even with a properly sized workload file, fly in the face of reality (most index  suggestions are very wide covering index in a lot of cases, redundant indexes in overlapping column coverage at times, etc.) I would suggest a "Top 10" style tuning exercise may be a better approach. Find the worst performing queries by duration and/or reads and work on tuning them through query tuning and index tuning/design best practices.

Edited: Added a couple more tips based on comment from OP

I would also look to set an end time. DETA isn't supposed to be a "done in 5 minutes" deal. It is trying to be incredibly thorough and look at your database as deeply as it can and analyze the workload as long as it can to come up with the recommendations. Have you tried setting an end time for the analysis?

I'd check out this link on MSDN for some other tips - https://blogs.technet.com/themes/blogs/generic/post.aspx?WeblogApp=sql_server_isv&y=2011&m=04&d=08&WeblogPostName=fundamentals-running-database-engine-tuning-advisor-and-selecting-indexes&GroupKeys=

Bottom line though - This will be a slower operation. The larger the workload file and more complex the database, the slower it will be. If you are running on the same system that other work is going on then you are each competing for resources.


Filter rows by range dates and only get the rows in a range of hours I want you to filter the rows between a date range but I only want you to return the rows of a time range of each of the days included in the previous date range.

For example, you would like to filter out all the rows between March 1 and March 3, 2020 and also only those between 14:00 and 18:00.

So far I can filter between two dates or between two hours but it doesn't work when I put the two things together. I don't get any records.

and BEGIN_INTERVAL_TIME BETWEEN to_date('19/04/2020', 'DD/MM/YYYY') AND to_date('20/04/2020','DD/MM/YYYY')
and BEGIN_INTERVAL_TIME BETWEEN to_date('15:00:00', 'HH24:MI:SS') AND to_date('16:00:00','HH24:MI:SS')

Regards,


MySQL backup breakes a table every time I've a MySQL Server 5.1.58-1ubuntu1 installed on an Ubuntu 12.04 LTS.

Every night we do a backup of all the databases with this script:

rm -rf /var/backup/mysql/ *
for i in $(echo 'SHOW DATABASES;' | mysql -u root -p'password' |grep -v '^Database$'); do
mysqldump \
-u root -p'password' \
-Q -c -C --add-drop-table --add-locks --quick --lock-tables \
$i > /var/backup/mysql/$i.sql;
tar -cpzf /var/backup/mysql/$i.sql.tar.gz /var/backup/mysql/$i.sql;
rm -rf /var/backup/mysql/$i.sql
done;

Every time the backup is executed a particular MySQL table is broken (the MYD and MYI files are deleted, while the FRM remains).

We have many databases and tables, only this one is broken.

Any idea?

This might seem too obvious but:

rm -rf /var/backup/mysql/ *

Space between / and * means there's two seperate deletes. So you're deleting the contents of /var/back/mysql/ and the contents of *. i.e. Everything in the current location. Could that be doing it?


PostgreSQL migrate data from one table to another (which inherits the first one) I have a table "company" and another named "school" ("school" table inherits "company").
Most of the data is community generated and sometimes a cleaning is needed.
I would like to be able to "transfer" a row from the company table to the school table without modifying its ID (because there could be some foreign keys linked to it).

Is there a clean way to do this ?

Thanks for your help.

You can use this method (assuming both tables have same columns in same order):

with T as (delete from only company where id=? returning *)
    insert into school select * from T


How to insert multiple records using XML in SQL server 2008 i use SQL server 2008 in my web application back end. Apparently i iterate through all the records from the C# code whenever there is a multiple insertion scenario. i have never tried the multple insertion using XML. And i think after reading many blogs about XML manipulation using SQL server 2008 the process is pretty tideous ..
So my question is..

Is Insertion via XML much efficient than the traditional insertion?
Is the a generic way in which i can serialize a class in C# --> Manipulate XML in SQL -->Insert also Read data as XML--> Deserialize XML to usual object?

The whole "inserting multiple records with XML" has pretty much been superseded by table parameters in SQL Server 2008.

And mentioned in Erland Erland Sommarskog's Arrays and Lists in SQL Server 2008: the definitive article on this subject.

.net has better XML handling than the SQLXML implementation too.


Running Total: To store or compute Say I have a product table with the following columns:

product
--------
product_id
name

And I have another table to record the transaction (IN/OUT) for each product

product_transaction
--------------------
transaction_id
transaction_date
product_id
type
quantity

I want to have a page that lists that products with its details including the current quantity. At the same time, I also want a page that lists all transactions within a day (or a range of dates) that also include the running total for the current transaction.

My question is, shall I store the running total in the product_transaction table and the current quantity in the product table. Or is it quick enough to just compute them on the fly?

Like many things, the answer to this question is "it depends". In this case, what it depends on is how many transactions each product will get.

With low to moderate volumes of transactions, it will be very fast to compute the running total on the fly and you don't have to write lots of code to compute, store and maintain the totals.

Index your table!

Make sure you have an index on (or starting with) production_transaction.product_id so that you can quickly retrieve all transactions for a given product.
An index on the combination of product_id, transaction_date and a query specifying product_id and a date range (or >= today) will give you a day's transactions for a given product, and with good performance.

Test!

Be sure to test this in your Test / Q&A environment! Generate representative transaction loads, but also much higher loads to see how it will perform in the expected and unexpected cases.

Even at high transaction levels, you will have to weigh up the performance associated with updating and storing the running totals vs how often they are queried.


Sync tables from Host1 to Host2 with over 50 million rows? I apologize first for using Sync in the title, that may be the wrong term. I am using MySQL, but can switch to Postgress or other open source platform.

I have a service that when people subscribe to we receive a lot of data from them, around ~60,000 rows that gets split up across multiple tables using a http endpoint processed by php. After the initial sync, we only receive incremental updates.

Looking at server load, we became concerned that it would become an issue as we added more and more customers and so setup a separate server that would handle all the http requests, parse the data and place it in the right tables. As load increased, we could then section off customers to other identical servers.

The question is what should I do to effectively replicate the data on those finished tables over to the production server (same network)? I could setup triggers, but that would result in tens of thousands of individual connection requests for each insert.

If I setup were to setup mirroring / master-slave, I want it to ignore all existing data because I wouldn't want it to try comparing things that have already been sent on a regular basis since that could grow to 50 million rows quickly.

It would be more like :

SELECT @rowcount:=COUNT(*) FROM production.database.table1;
INSERT INTO production.table1 SELECT * FROM workhorse.database.table1 where `id` >@rowcount;

Something this simple could be run once a minute or so I would think and cut down on individual connections. I realize this is kind of messy since COUNT and ID aren't locked together, but it gets the idea across. Also, I don't know a way to run the query on a remote server.

Are there any tools/commands that would make quick work of this?

Master-Slave (and various other forms of replication) provide keeping two (or more) hosts in sync continually.  No ETL process needed.

Splitting the client processes and MySQL into separate servers is a good first step.

Putting different customers on different "shards" provides 'infinite' scalability in that dimension.  You would need some form of proxy or home-grown directing to get from a client to the necessary server.

Ingesting 60K rows is pretty trivial unless, of course, the data format is always something different.  At that point, I might contract a programmer who knows Perl (or other tool) to crank out ingestion scripts.  (Years ago, I did such with news feeds.)

Does 50M rows at 60K per customer mean you have 1000 customers?

I would suggest you use sharding do deal with excessive write traffic and disk space.  And use Slaves for excessive read traffic.

It is a never-ending pain to try to perfect "sync" scripts.


SELECT query with referenced subqueries How does one reference the currently-grouped by field in a subquery?

Consider a table with the following data:

Client    Status    Size
   1       A         500
   1       B        1500
   2       A        2000
   2       B         800

I am trying to get the following data in a single query:

Client     A      B
   1      500   1500
   2     2000    800

I've been playing with variations on this, with absolutely no success:

SELECT
  t.Client,
  (
    SELECT A FROM table WHERE Client=t.Client
  ) as A,
  (
    SELECT B FROM table WHERE Client=t.Client
  ) as B
FROM table t
GROUP BY t.Client;

The status for each Client is not repeated, that is, each client should have only one row with a given status. For purpose of this question, we can assume that there is exactly one entry for each status.

Note that there are about one hundred thousand rows: about 20 thousand Clients and five status per client. There exist other fields as well (such as date of status update and the user who performed the update) so changing the table schema to simply Client, A, B is not feasible.

How can I reference the grouped field in the subqueries?

This works, there may be something more elegant:

 insert
 select 1 as client, 'A' as [status], 500 as 'Size'
 into #temp
 insert into #temp
 select 1 as client, 'B' as [status], 1500 as 'Size'
  insert into #temp
 select 2 as client, 'A' as [status], 2000 as 'Size'
 insert into #temp
 select 2 as client, 'B' as [status], 800 as 'Size'

 select t.client, ta.size as 'A', tb.size as 'B'
 from #temp as t
 INNER JOIN (select client, sum(size) as size from #temp where status = 'A' group by client) as ta on t.client = ta.client
 INNER JOIN (select client, sum(size) as size from #temp where status = 'B' group by client) as tb on t.client = tb.client
 group by t.client, ta.size, tb.size


Postgres JOIN conditions vs WHERE conditions Postgres newbie here.

I'm wondering if this query is optimized or not? I tried to JOIN ON only the values that are 100% necessary and leaving all the dynamic conditions in the WHERE clause. See below.

SELECT *
    FROM
      myapp_employees
    JOIN myapp_users ON
      myapp_users.user_id=myapp_employees.user_id
    JOIN myapp_contacts_assoc ON
      myapp_contacts_assoc.user_id=myapp_users.user_id
    JOIN myapp_contacts ON
      myapp_contacts.contact_id=myapp_contacts_assoc.contact_id
    WHERE
      myapp_contacts.value='test@gmail.com' AND
      myapp_contacts.type=(1)::INT2 AND
      myapp_contacts.is_primary=(1)::INT2 AND
      myapp_contacts.expired_at IS NULL AND
      myapp_employees.status=(1)::INT2 AND
      myapp_users.status=(1)::INT2
    LIMIT 1;

Note: For context, this proc is checking to see if a user is also an employee (elevated privs/different user type).

Anyways, is this the right way to go? Should the JOIN ON contain more statements like checking for expired_at IS NULL, for example? Why or why doesn't this make sense?

Logically, it makes no difference at all whether you place conditions in the join clause of an INNER JOIN or the WHERE clause of the same SELECT. The effect is the same.

(Not the case for OUTER JOIN!)

While operating with default settings it also makes no difference for the query plan or performance. Postgres is free to rearrange joins and JOIN & WHERE conditions in its quest for the best query plan - as long as the number of tables is not greater than the join_collapse_limit (default 8). Details:

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT

For readability and maintainability it makes sense to place conditions that connect tables in the respective JOIN clause and general conditions in the WHERE clause.

Your query looks just fine. I would use table aliases to cut back the noise, though.

Minor detail:

int2 '1' or even 1::int2 are more sensible than (1)::INT2. And while comparing to a value of well defined numeric data type, a plain numerical constant 1 is good enough, too.