Best lock strategy to prevent deadlock on concurrent updates Yesterday I stumbled across a deadlock caused by two apps concurrently updating the same table. The operation would have been harmless in this instance if it weren't for the deadlock. The pattern is as below, which I've shamelessly copied from here.

-- Transaction 1
UPDATE customer SET ... WHERE id = 1
UPDATE customer SET ... WHERE id = 2

-- Transaction 2
UPDATE customer SET ... WHERE id = 2
UPDATE customer SET ... WHERE id = 1

There are countless explainers as to why this deadlocks, and suggestions as to best practices to avoid the pattern in the first place. What I haven't really found is a consistent suggestion as to what locking strategy is best employed for the simple case above, assuming you want or are happy to use explicit table locking to work around it.

The Postgres documentation is generally very good, but I find the explicit locking doc a bit of a head-spin in the way things are described in a sort of circular reference manner.

My first thought, given the docs state "this lock mode will be acquired by any command that modifies data in a table", was that ROW EXCLUSIVE would force a queue. But then in the conflict matrix on the same page it shows ROW EXCLUSIVE does not conflict with itself, so that isn't going to help, is it? So I'm thinking maybe SHARE ROW EXCLUSIVE, but given that is obtained by CREATE and UPDATE table it sounds perhaps heavy handed.

So I guess I'm hoping some long-in-the-tooth Postgres guru can say which lock they'd use here, assuming no complicating factors?

The explicit locking doc says acquire in a consistent order, so perhaps always do the lower id first.

For explicit locking perhaps row level SELECT 1 FROM customer WHERE id IN (1,2) FOR UPDATE.

You should handle the deadlock at the query level, the schematics of a deadlock means its not obvious the the database how it should continue and leaves it for a application designer to implement. Exit and watchdog seems rather extreme.


Working across multiple PostgreSQL database servers So we are writing an app whose schema should reference data which lies currently in an external PostgreSQL instance. We are negotiating being able to put our schema within the external database, but we are evaluating different possibilities.

One option I'm pondering is basing our app on PostgreSQL and use its facilities for accessing external PostgreSQL instances.

What's the status of this?

PG's documentation contains http://www.postgresql.org/docs/9.1/static/sql-createserver.html and http://www.postgresql.org/docs/9.1/static/sql-createforeigntable.html , which allow you to reference tables in an external server. What's their status? Are queries performant (i.e. sends WHERE to the other side)? Can you reasonably join between local and foreign tables?

There's also pgsql_fdw ( http://interdbconnect.sourceforge.net/pgsql_fdw/pgsql_fdw-en.html ), which seems more featureful. Does it offer an improvement on the above? Anyone using it?

Thanks,

Alex

Foreign data wrappers are currently read-only.  This will change in the future, but that's the way it is.  You can also use dblink to access other databases.  As I understand it a big issue will be commit handling.  I would recommend against this approach unless you are trying to aggregate lots of data that is really external.  It is a huge complexity cost and the benefits have to be worth it.

Edit:  Note that foreign tables and foreign servers are how you use foreign data wrappers to map in foreign data to your application.  You can't choose between them.  It's all three or nothing.


Optimal hash technique to index large text Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths.

The upper limit for the insertion rate is tens of billions of rows per year.

For my implementation, the hash never needs to leave the database, but the hashed data must be compared with external data for existence frequently.

In my limited experience with optimization for these purposes, I assume that the best data type for the hash would be bytea.  The alternative is of course a much longer hex string.

Is it correct that bytea is the optimal data type for these hashes?

If bytea is not optimal, what is?

How should my intent be implemented?

Clarification

I'm using the hash of the text per Erwin Brandstetter's recommendation to be able to ensure that large text is unique.  It is my limited understanding that raw binary data is always the most performant especially when compared to strings.

The hash only needs to be compared for existence to preempt uniqueness violations, so the hash happily never needs to leave the database.  Because of libpqxx's incredible design, it appears as if the data can simply be input through a prepared statement and converted with (decode(md5($1::text), 'hex')).  When I become more familiar with bytea insertion via libpqxx 3.1, I'll move it to c++.

For this implementation, collisions are acceptable because data can be reconstructed to conform without breaking the system.

If the maximum throughput is happily reached then it should be expected that the economic resources will be available to accommodate it; therefore, the primary concern will always be performance, so if my understanding of the capabilities of Postgres and its ability for partitioned tables to handle these amounts of rows is accurate, it will hopefully be the right tool for the job for a long while.  Luckily, data older than a few seconds will never change, and it is my understanding that Postgres partitioned tables can make minced meat out of such data.  If not, this will be one of those good problems.

bytea will be optimal for storing the hash.

It'll be transferred in/out of the database as a hex string anyway, unless you use PostgreSQL's binary wire protocol (supported by libpq and partly by PgJDBC) to transfer them.

For best results, store as bytea and have the client application use a PQexecParams call that requests binary results.

Though on re-reading, this is confusing:

  For my implementation, the hash never needs to leave the database, but the hashed data must be compared with external data for existence frequently

Do you mean that the hash isn't transferred for comparison, the original unhashed text data is? If so, the above is irrelevant, as the binary protocol offers no benefits for text-form data.

Also: "tens of billions" of rows is a lot. PostgreSQL has quite a large per-row overhead at 28 bytes, so you're going to be losing a lot of space. Especially once you factor in index overheads too. Is PostgreSQL the right tool for this job?

A final thought: With that many rows, you're getting up into hash-collision territory. Do you care if it's possible - though unlikely - for two different strings to have the same hash, so an incorrect unique violation is reported? If that's a problem then a unique b-tree index on the hash probably isn't the right tool for the job.


Here is one way of getting the result set you want without doing the multiple joins.  It takes a little more setup and uses two pivot operations instead of one, but avoids the multiple joins.

I admit that I had to look it up, but Ken O'Bonn had a great article.  https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/

/** Build up a Table to work with. **/
DECLARE @T TABLE
    (
    ID INT NOT NULL PRIMARY KEY
    , hProp INT NOT NULL
    , iDayOfMonth INT NOT NULL
    , dblTargetPercent DECIMAL(6,4) NOT NULL
    )

INSERT INTO @T
(ID, hProp, iDayOfMonth, dblTargetPercent)
VALUES (117,10,5,0.1400)
        , (118, 10, 10, 0.0500)
        , (119, 10, 15, 0.0100)
        , (120, 10, 20, 0.0100)

/** Create a CTE and give us predictable names to work with for
    date and percentage
    **/
;WITH CTE_Rank AS
    (
    SELECT ID
        , hProp
        , iDayOfMonth
        , dblTargetPercent
        , sDateName = 'iDateTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
        , sPercentName = 'dblPercentTarget' + CAST(DENSE_RANK() OVER (PARTITION BY hPRop ORDER BY iDayOfMonth) AS VARCHAR(10))
    FROM @T
    )
SELECT hProp
    , iDateTarget1 = MAX(iDateTarget1)
    , dblPercentTarget1 = MAX(dblPercentTarget1)
    , iDateTarget2 = MAX(iDateTarget2)
    , dblPercentTarget2 = MAX(dblPercentTarget2)
    , iDateTarget3 = MAX(iDateTarget3)
    , dblPercentTarget3 = MAX(dblPercentTarget3)
    , iDateTarget4 = MAX(iDateTarget4)
    , dblPercentTarget4 = MAX(dblPercentTarget4)
FROM CTE_Rank AS R
    PIVOT(MAX(iDayOfMonth) FOR sDateName IN ([iDateTarget1], [iDateTarget2], [iDateTarget3], [iDateTarget4])) AS DayOfMonthName
    PIVOT(MAX(dblTargetPercent) FOR sPercentName IN (dblPercentTarget1, dblPercentTarget2, dblPercentTarget3, dblPercentTarget4)) AS TargetPercentName
GROUP BY hProp


Select one name per person where spelling variations exist I have a source table that contains Employee IDs and names.  The employee names may be listed multiple times with variations on the exact spelling (see 'source table' for example - note the middle initial is sometimes present).

I want to insert unique values, in alphabetical order, into a new table (see 'target table' for desired results).  I don't care which version of someone's name is inserted into my target table...what I want is one truly distinct value per person in my target table sorted alphabetically by last name.

Source Table:

Emp_ID  Name
123 Jones, John
123 Jones, John P
123 Jones, John P.
456 Lewis, Jerry
456 Lewis, Jerry L
456 Lewis, Jerry L.
789 Hewitt, Jennifer
789 Hewitt, Jennifer L

Target table (desired results):

Emp_ID  Name
789 Hewitt, Jennifer
123 Jones, John
456 Lewis, Jerry L

You can "group by" Emp_ID and use an aggregate function like MIN() or MAX() to get one of the names:

INSERT INTO TargetTable
  (Emp_ID, Name)
SELECT Emp_ID, MIN(Name)
FROM SourceTable
GROUP BY Emp_ID ;

And note that there is no inherent order in a table (actually you can define a clustered index for a table and this affects how the rows are stored on the disk but that is no guarantee for the order of retrieval).

You can get the data from the target table with a query afterwards and if you want them ordered, you can (and should) define the order you like (and a different one if you like, every time you query it):

SELECT Emp_ID, Name
FROM TargetTable
ORDER BY Name ;


How to find DELETE and UPDATE statements that have been executed on a database? I am trying to find out what delete and update statements have been executed on a certain database in a certain period of time.

I came across this following query, but it generates an error message:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
where dest.TEXT like '%Delete%from%'
ORDER BY deqs.last_execution_time DESC

The error message is:

  Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '.'.

If I try:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest
where dest.TEXT like '%Delete%from%'
ORDER BY deqs.last_execution_time DESC

I get another error:

Msg 321, Level 15, State 1, Line 3
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

As far as I understand in my research of the topic it is not an easy task to achieve.

Is this query even the right approach?

SQL VERSION in question:

Microsoft SQL Server Standard Edition (64-bit)
Microsoft SQL Server Management Studio                      10.0.2531.0
Microsoft Analysis Services Client Tools                        10.0.1600.22
Microsoft Data Access Components (MDAC)                     6.1.7600.16385
Microsoft MSXML                     3.0 6.0
Microsoft Internet Explorer                     8.0.7600.16385
Microsoft .NET Framework                        2.0.50727.4984
Operating System                        6.1.7600

Run this in the context of the master database as you must be running it under the context of a user database set to SQL Server 2000 compatibility level.

The following should work fine

USE master;

SELECT deqs.last_execution_time AS [Time],
       dest.TEXT                AS [Query]
FROM   sys.dm_exec_query_stats AS deqs
       CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE  dest.text LIKE '%Delete%from%'
ORDER  BY deqs.last_execution_time DESC


Determining Functional and Transitive dependencies from Relational Model (with no sample data) I have a relational model which I've converted from an EER diagram...
I've been asked to find the functional and transitive dependencies but how does one do that when there's no sample data to start from?

My relational model is as follows:

Product(productID, name, manufacturer, description, categoryID, supplierID, qtyDescription, unitPrice, status, availableQty, reOrderLevel, maxDiscount)
Primary Key productID
Foreign Key categoryID references Category(categoryID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key supplierID references Supplier(supplierID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Category(categoryID, name, description)
Primary Key categoryID
Alternate Key name

Quotation(quoteID, quoteDate, empID, quoteValidity, supplierID, description, productID, qty, unitPrice)
Primary Key quoteID, productID
Foreign Key empID references Employee(empID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key productID references Product(productID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key supplierID references Supplier(supplierID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Supplier(supplierID, name, address, phoneNo, faxNo, contactPerson)
Primary Key supplierID

SupplierOrder(supplierOrdID, orderDate, quoteID, description, productID, qty, unitPrice, orderTotal, status, orderReceiveDate, orderPaymentDate, PaymentRefNo)
Primary Key supplierOrdID
Alternate Key PaymentRefNo
Foreign Key quoteID references Quotation(quoteID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key productID references Product(productID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Employee(empID, name, gender, positionID, phoneNo, homeAddress, homePhone, dateOfBirth)
Primary Key empID
Foreign Key positionID references Position(positionID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Position(positionID, title, hourlyRate)
Primary Key positionID

Payslip(PayslipID, empID, startOfPayPeriod, endOfPayPeriod, noOfDaysWorked, hourlyRate, totalPay)
Primary Key PayslipID
Foreign Key empID references Employee(empID)
    ON UPDATE CASCADE, ON DELETE CASCADE

CustomerOrder(customerOrdID, empID, customerID, orderDate, productID, qty, unitPrice, discountGiven, overDueFees, cancellationFees, totalAmtDue, billingDate, dueDate, status, description)
Primary Key customerOrdID
Foreign Key empID references Employee(empID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key customerID references Customer(customerID)
    ON UPDATE CASCADE, ON DELETE CASCADE
Foreign Key productID references Product(productID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Customer(customerID, companyName, address, phoneNo, faxNo, emailAddress, contactPerson, gender,
maxAvailableCredit)
Primary Key customerID

CustomerPayment(paymentRefNo, customerOrderID, paymentDate, amtPaid)
Primary Key paymentRefNo
Foreign Key customerOrderID references CustomerOrder(customerOrdID)
    ON UPDATE CASCADE, ON DELETE CASCADE

Any assistance would be greatly appreciated... I just need an understanding of whether it is possible, and if so, how? A few examples from the model would be great, and I can determine the rest through application of the theory behind the examples.

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

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

Good luck.  You're going to need it.


Difference between functional dependency and join dependency By definition, a functional dependency is defined as

  Let X and Y be subsets of attributes of a relation R. A functional dependency X → Y holds over R if and only if for any instance r of R,
  whenever two tuples t1 and t2 of r agree on the attributes X, they
  also agree on the attributes Y. That is,

  t1.X = t2.X ⇒ t1.Y = t2.Y

A join dependency, meanwhile,

  A table T is subject to a join dependency if T can always be recreated by joining multiple tables each having a subset of the attributes of T.

I could be wrong, but I was wondering if there are any relation between the concept of functional dependency and join dependency.

Join Dependencies can be considered a generalization of Multivalued Dependencies, following the fact that a Multivalued Dependency X →→ Y in a relation R can be seen as another way of writing a binary Join Dependency ⨝[XY, X(U − Y)], where U is the set of all the attributes of the relation R.

For instance, in a relation describing employees together with their salary history as well as other attributes R(empNo, salary, year, name, role), we can say that there is a MultivaluedDependency empNo →→ salary, year or, equivalenty that there is a Join Dependency ⨝[{empNo, salary, year}, {empNo, name, role}] (note that this makes clear that in R holds also the “dual” Multivalued Dependency empNo →→ name, role).

You are asking which relations there are between Functional Dependencies and Join Dependencies. The question is reasonable, since there is a relation between Functional Dependencies and Multivalued Dependencies.

In Chapter 8 of Abiteboul, S. “Foundations of Databases.” Reading, Mass: Addison-Wesley, 1995. you can find an answer which is also proved:

  Proposition 8.3.3. Let U be a set of attributes, {X, Y, Z} be a partition of U, and Σ be a set of Functional Dependencies over U. Then Σ ⊨ ⨝[XY, XZ] if and only if either Σ ⊨ X → Y or Σ ⊨ X → Z.

In other words, if a Functional Dependency X → Y holds in a relation schema R, then this implies that the binary Join Dependency ⨝[XY, XZ] holds, and viceversa. Note that this is equivalent to say that you can perform always a lossless decomposition of a relation R{X,Y,Z} with a functional dependency X → Y in R1{XY}, R2{XZ}.

So, for instance, in the previous example, since empNo → name, the Join Dependency ⨝[{empNo, name}, {empNo, salary, year, role}] holds and {R1(empNo, name), R2(empNo, salary, year, role)} is a lossless decomposition.


Is there a difference between granting SELECT to a user and adding them to the db_datareader role? I was asked to grant SELECT, INSERT and UPDATE to a given user for all tables in a database.  I ended up adding the user to db_datareader and granting them INSERT and UPDATE at the database level.

But that got me thinking, what is the difference (if any) between granting a user SELECT permission at a database level, or adding them to the db_datareader role?  And is there any best practice one way or the other?

Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map.  You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx

The chart shows that db_datareader role is identical to GRANT SELECT ON [database].  So it is still fine to use, but the recommendation is to move away from those roles to the more granular commands. Some of the other fixed database roles are less clearly defined for most people.  Using the explicit commands results in greater clarity when reporting rights.

Obviously you know how to grant finer grained permissions. I am trying to break loose from the old roles whenever possible, but db_owner (for example) is a hard habit to break.


Will Changing the Max Memory Setting Invalidate Query Plans? I recently lowered the max memory from the default (unlimited) down to 20 GB. Does this erase the oldest queries in the plan cache?

Per Microsoft documentation changing the max server memory will clear the plan cache, as will changing:

access check cache bucket count
access check cache quota
clr enabled
cost threshold for parallelism
cross db ownership chaining
index create memory
max degree of parallelism
max server memory
max text repl size
max worker threads
min memory per query
min server memory
query governor cost limit
query wait
remote query timeout
user options


Using if statement outside of a stored procedure or function I want to call a query to update a mysql table but my query contains an if statement

set @id = 0;
set @the_number = 0;
set @the_message = 0;
set @selected_message = 0;

SELECT id, dest_msisdn, text_message INTO @id, @the_number, @the_message FROM    incoming_sms where service_id = 6015592000101762 AND job_status = 0 limit 1;

if(@the_message LIKE '%Bank%')then

select 'h';

/* Update Statement Here*/
else

select 'nothing to see here';

end if;

This is the error that i get.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if(@the_message LIKE '%Bank%')then

select 'h'' at line 1

In the mysql manual found here for if http://dev.mysql.com/doc/refman/5.0/en/if.html
it explains The IF statement for stored programs implements a basic conditional construct....

Are If statements usable outside stored procedures?.

Use the IF() function

mysql> set @the_message = 'Rolando Bank of America';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(@the_message LIKE '%Bank%','h','nothing here to see') result;
+--------+
| result |
+--------+
| h      |
+--------+
1 row in set (0.00 sec)

mysql> set @the_message = 'Rolando Bk of America';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT IF(@the_message LIKE '%Bank%','h','nothing here to see') result;
+---------------------+
| result              |
+---------------------+
| nothing here to see |
+---------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!


SQL Server 2012 - password for services during silent installation I am a database developer and I have a task to prepare silent installation of SQL Server 2012 using configuration file.
I have a problem with passwords for server services like: RSSVCACCOUNT="NT Service\ReportServer"

During normal installation with user interface there is no need to specify passwords for services. I assume that setup generates random passwords or they are handled by Windows virtual accounts.

However when I use configuration file then setup expect to provide a password either in the file or in the command prompt.

My question is why do I have to provide a password with silent installation while it is no needed during normal installation.
Is there a way to silently install SQL Server without specifying passwords for services?

Just to preserve this as an answer for future readers:

I have seen this type of failure before. Somehow something still knows that that managed account had been created already and therefore now requires a password.

Try your install script against a newly setup VM.


Can I use a Percona Xtrabackup backup to restore a single table? I've been playing around with the Xtrabackup tool for a month now, and I got to admit : it is really powerful !

I'm still testing it to backup database servers between 5 and 150 GB and the backup/restoration time are impressive. So I'm trying to push the use of this tool to my manager, saying the only inconvenient we could have is the backup archive size (which is 3x/4x time larger than with mysqldump) and on the other part we gain faster times on backup/restoration, no lock during the backup process and the ability to stream backups to servers.

He was quite impressed by the capability of the tool, but the most important part for him in a backup/restore process is to be able to restore a specific table of a database quickly. As for what I know about this tool, I'm still thinking that this is not possible with Xtrabackup right now. Am I right?

We're using the following softwares :

Ubuntu 12.04
MySQL Server 5.5.32
Xtrabackup 2.1.3-608.precise

PS: I've seen some solutions to restore a specific table from a backup created with Xtrabackup, but it always require Percona Server.

If it is not possible to do with Xtrabackup, do someone have another solution to this problematic?

This functionality is currently only supported on Percona Server.

See the official documentation link for more information:
http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/importing_exporting_tables_ibk.html


Use string functions and arithmetic calculations in SQL query or by programming language? I need heavy arithmetic and string functions for processing the data captured from mysql database. Which is preferred: mysql functions or programming language (e.g., PHP)?

$result = $mysqli->query("SELECT A, B, C FROM table1");
while($row = $result->fetch_assoc() )
{
$a = str_replace("X", "Y", $row['A']);
$b = ($row['B']/$row['C')*1000;
}

OR

$result = $mysqli->query("SELECT REPLACE(A, 'X', 'Y'), ((B/C)*1000) AS B FROM table1");
while($row = $result->fetch_assoc() )
{
$a = $row['A'];
$b = $row['B'];
}

This just a simple example with no significant difference, but when having heavy calculations, which one is preferred?

If you're going to bring the same amount of rows from the database anyway, then you had better perform your mathematical functions in your application, not in your database. Math calculations in SQL are notoriously slow.

Also note that this takes computation from a single database server into possibly multiple PHP front ends. Sure, you might also spread the load over multiple MySQL replication servers; generally speaking, you will have more PHP front ends than MySQL back ends.


How to disable case sensitivity in SQL Server 2012? I tried to update collation_name in sys.databases. I am trying to change 'SQL_Latin1_General_CP1_CS_AS' to 'SQL_Latin1_General_CP1_CI_AS' in order to disable case sensitivity in table.

update d set d.collation_name='SQL_Latin1_General_CP1_CI_AS'
--SELECT name, collation_name
FROM sys.databases d
WHERE name = 'db_name'

However, I'm getting this error:

  Msg 259, Level 16, State 1, Line 1
  Ad hoc updates to system catalogs are not allowed.

First, as you have now seen, you cannot directly alter meta-data in the system views. However, you could change the setting for a particular database using ALTER DATABASE:

ALTER DATABASE { database_name  | CURRENT }
  COLLATE collation_name;

Please note that the option to use the CURRENT keyword was introduced in SQL Server 2012.

OR, if you only want to change how a particular field in a particular table behaves for all queries (well, all that do not override the fields collation with the COLLATE clause -- see notes below), then you can do that by changing the collation for just that one field:

ALTER TABLE [TableName]
  ALTER COLUMN [ColumnName] NVARCHAR(10)
  COLLATE Latin1_General_100_CI_AS
  NOT NULL;

Just be sure to specify the exact same datatype and NULL vs NOT NULL setting that the field currently has!!

Now, a few notes about what you are trying to accomplish:

The collations starting with SQL_ are deprecated. You should use the Windows Collations. In this case, you would use Latin1_General_100_CI_AS. The _100 series are the newest versions and were introduced in SQL Server 2008.
Changing the default collation of a database does not change the collation for any string fields in any existing tables. It only affects operations involving string literals and variables that do not also involve a field in a table that would already have a collation specified. For more details about the ramifications of changing a database's collation when tables already exist (with example code showing what the true effects are, including potential errors if joining existing and new tables on string fields), please see my answer here: Latin1_General_BIN performance impact when changing the database default collation
Any string operation, whether it involves literals, variables, or fields in tables, can have the default collation overridden by using the COLLATE clause. For example:

SELECT *
FROM   dbo.SomeTable st
WHERE  st.SomeField = @StoredProcedureParameter COLLATE Latin1_General_100_CI_AS;

If you are only concerned about some (not all) queries on some (not all) tables, then this is a great place to start.
The collation actually used for a string operation is determined by looking at the various levels of where to specify a collation. Please see Collation Precedence for more info.


Mysql master/master replication, what is the problems I tried to set up Master <-> Master replication on production and some people they tell me that is not a good option.

I want to know why is not a good option and what is the problem that may find? :)

Mysql version: 5.7.23

mysql config:

auto-increment-increment = 2
# 1 Node
auto-increment-offset = 1
# 2 Node
auto-increment-offset = 2

The autoincrement% settings you mentioned take care of AUTO_INCREMENT values, but do not handle other UNIQUE KEYs.  Keep in mind that if a dup key error occurs, replication is stopped until a human intervenes.

Depending on how the code (router, proxy, etc) picks which Master to write to, you may have the "critical read" problem.  This is where, say, someone posts something, then goes to another web page and expects to see the results of it.  But the read goes to another machine and the results have not [yet] arrived because of replication delay.

Automatic failover is a big challenge.  It is probably best left to be manual.  If the network dies, you could have the "split brain" problem.  In this case it is unsafe to failover because machine can't see the other.  If they both blindly thing the other is dead, and take control, you could get data conflicts that are 'impossible' to fix.

Some of the issues are avoided by designating one of the Master as active and having the other be readonly.

After all, every write to a Master is also performed on every Slave (including the other Master in a dual-Master setup).  That is, there is essentially no "write scaling" by having dual-Master.  There is some "read scaling".

What is your goal of picking M-M?  Scaling?  HA?  Disaster protection?  Facilitate certain schema changes?  Other?  Are the two Masters located next to each other?  On the same power source?

One of my points is that M-M provides limited benefits.  And you may have to do other things to really achieve them.  (Separate data centers, careful routing of queries to servers, etc.)

A number of the issues are lessened if you go to one of the "cluster solutions" (Galera or Group Replication).  They require 3 servers, preferably in 3 geographic locations.  But they handle split brain, automatic failover, and some other issues.


PAGEIOLATCH wait type I'm trying to get a better understanding of how serious this wait type is. I'm using sp_WhoIsActive stored procedure. Our DBAs don't seem to know much about this wait type. But I often see it when I run the stored procedure. Most of them are PAGEIOLATCH_SH but some are PAGEIOLATCH_EX. When they occur, the average wait time seems to be in the 20-25 millisecond range.

Questions:

1) Is this more of an indication of a problem with I/O as opposed to memory?

2) Is there a guideline to how often someone could expect to see this before it's considered a real problem?

The Microsoft definition of this wait type is:

  Occurs when a task is waiting on a latch for a buffer that is in an
  I/O request. The latch request is in Shared mode. Long waits may
  indicate problems with the disk subsystem.

As mentioned above, excessive PAGEIOLATCH_SH wait types don’t mean necessarily that the I/O subsystem is the root cause. It can often be some other reason, such as: bad index management, memory pressure, synchronous mirroring and AlwaysOn AG, logical/physical drive misconception, network issues/network latency, overloaded I/O subsystem by another processes that are producing the high I/O activity.

You may want to try some of the following to resolve having excessive PAGEIOLATCH_SH wait type values:

Keep in mind that in case of high safety Mirroring or synchronous-commit availability in AlwaysOn AG, increased/excessive PAGEIOLATCH_SH can be expected
Check your SQL Server queries and indexes as very often this could be found as a root cause of the excessive PAGEIOLATCH_SH wait types
Check the memory pressure before jumping into any I/O subsystem troubleshooting

If more details on this wait type are needed, including real-word situations that are causing this wait type values to be excessive, take a look at the Handling excessive SQL Server PAGEIOLATCH_SH wait types article.


error "--secure-file-priv option" when save selection to csv I try to save a very large select (more than 70 milion rows into csv file) on Win 2012 R2 server

I execute query as root , but I think there is still some problem with privileges

select *
INTO OUTFILE 'D:\my_folder\my_file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from my_table
where
...

Error

  Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Thank you in advance for any help !

I am using MySQL Server 8.0 on Windows 10. I tried to load data from csv file using the following commands:

LOAD DATA INFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\World_cup_dataset.csv'
INTO TABLE trial
FIELDS TERMINATED BY ',';

When I run above commands, I got

  "ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"

I tried many methods to solve the problem. Finally, I changed the '\' characters in the file path to '/' characters.

I mean my new file path became C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/World_cup_dataset.csv and my problem were fixed. If you look at my.ini file located in the MySQL Server folder, the path of secure-file-priv is "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads". So, this solution makes sense.


Definition of ROWS_READ? This may seems like a stupid question, but I cant seem to find a good answer for it. I have a pretty good intuitive understanding of what ROWS_READ in for example  sysibmadm.snapdyn_sql means, and what conclusions can be drawn from this metric. However, I'm not sure exactly what this means, and I can't find any information in the documentation. Looking at:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.mon.doc/doc/r0001317.html

it says: The number of rows read from the table.

My question is due to observations that sometimes ROWS_READ does not increase eventhough NUM_EXECUTION does. At the same time there are situations where ROWS_READ increases along with NUM_EXECUTIONS eventhough data definitely fits in bufferpool.

Are ROWS_READ physical reads (from disk to bufferpool) or logical reads (in bufferpool) or orthogonal to physical/logical? If the metric is measured in the bufferpool, how can the first phenomena be explained? Invalidated package (something wrong with counters in package cache), a sudden change to index only access or something else?

Firstly, rows_read are logical. The metric shows the number of rows the engine had to retrieve (from the buffer pool if available) and evaluate against SARGable predicates, if any. It is in contrast with the rows_selected metric that shows how many rows matched all query predicates and were therefore returned to the application.

If your query does not use any indexes, rows_read will in general be equal to the total number of rows in all tables that are involved. If an index is used, rows_read will be reduced to the number of rows that can be found matching whatever predicates by the index scan.

Subsequently, if a unique index scan does not find any matching key values, rows_read for that particular table will be 0. Other optimization techniques, such as the use of referential integrity and check constraints or partition elimination, might also allow the engine to satisfy the query without actually evaluating any rows. For example, if you have an int_column integer check (int_column > 0) and a query with where int_column < 0, the engine doesn't need to read any rows to satisfy that query.

The ratio of rows_read to rows_selected is one of the general measures of efficiency of your SQL queries; the lower it is, the better: it means the server does not waste time reading rows that do not match  query predicates.


Excessive log throttling on Azure SQL on business critical tier We moved from the Biz Critical vCore Gen4_8 tier to Gen5_8 over the holidays. We had to move because our log growth is constantly pushing us into the 1 TB limit of Gen4.  Not only are our queries taking twice as long in Gen5, but we are seeing a lot of log rate throttles as shown here:

Why are there three different log rate throttles - what is the difference between them?  If you remove the non-important CXCONSUMER wait, almost half of our waits are from the 3 throttles.

I can reproduce the log rate throttles with nothing else running in our pool with a simple "SELECT TOP 20000000 * INTO newtable FROM oldtable" query.  We are selecting from a very narrow table (4 int and 2 float columns) that has a CCI and no fragmentation.

We contacted support and they responded with "its because of poor indexing or a very inefficient query plan". Obviously, there is nothing wrong with this query. It runs in 1:50 on Gen5 consistently, 49 secs in Gen4, and 20 secs on my old laptop.

When I run this query, the DMVs show the I/O for this query as: total_logical_writes=150258, total_physical_reads=44080.

Given that it takes 110 seconds to run, can we then say that each write and read is a single I/O which would make our average IOPS about 1766 ((150258+44080)/110)?   If not, is there a good way to measure how many IOPS a query hits?  I bring this up because the DB limit for Gen5_8 shows "Target IOPS" = 20000, but I think we are well below that.  Also is "target" an actual limit or an estimate of the throughput we could expect?

We never had throttling under Gen4 and are paying $50k a year for this service tier and our app is barely usable now - any suggestions/thoughts on where we go from here would be much appreciated!

After a few weeks with the Azure product team we have great answers to these questions.

The main bottleneck in Azure for us is Log I/O.  Each logical processor has 6 MB/s throughput. So, on Gen4_8, we have 16 logical processors and get 96 MB/s log I/O throughput. On Gen5_8, there are 8 processors so the throughput is 48 MB/S.

The reason for these limits is that if they allowed higher log I/O throughput they wouldn't be able to guarantee that the log shipping job that runs every 5 minutes would be able to capture all the transactions that occurred.

There is also a major issue with using MAXDOP on Azure right now.  By adding MAXDOP 1 to the SELECT INTO statements and our parallel inserts, our performance improved tremendously.  Do NOT use MAXDOP 8 or higher on Gen4 or Gen5! I tested on Gen5_16 and MAXDOP 8 adds a 10 fold increase to run times.   On Gen4_8, our production pool, our parallel insert for loading 1 billion records goes from 40 minutes with MAXDOP 1 to 3.5 hours with MAXDOP 8 (yes, which means we aren't really doing a parallel insert).


SQLServer - Detect who is connecting via an AG listener We have multiple generations of applications connecting to SQL Server, some of which we know [their connection libraries] do not support using the MultiSubnetFailover=True connection parameter. Fine.

For the apps that "say" they are capable of using the AG listener, is there any way to verify this from the SQL side once they are connected? Unfortunately, our role (DBAs) does not grant us (nor would we want) the liberty of validating all kinds of application strings to ensure they configured it correctly. These older generation applications have historically used the literal server name, but we're trying to migrate all over to listeners.

First post; please let me know what info I might be missing from my question. Thanks!

[…] is there any way to verify this from the SQL side once they are connected?

Unfortunately there is not, most of the client side connection string settings are not persisted or sent across the wire as they are specific to the connection library - one such is the MultiSubnetFailover setting.

  These older generation applications have historically used the literal server name, but we're trying to migrate all over to listeners.

I'll point you to a post I made a while ago for someone that had a very similar issue and they wanted a way to track it down. It was very helpful for them, it may or may not be helpful for you but it's the closest you're going to get without asking to see the connection string of the application. The very minimum it'll show you if they are using the listener and which one. It gives some extra information so you can narrow down which client on the remote (note that this data can be spoofed, as I show).


Queries running parallel with default maxdop I need to understand below behavior of sql server queries

System has 4 CPUs with hyper thread ratio of 20 making 80 logical processors count on my sql server instance.

Currently MAXDOP is 0 and ctop is 5

This is a read only instance acting as stand by for LS where all reporting is done.

Lot of queries coming in seems to show running with degree of parallelism 64.

Questions:
.1. why degree of parallelism is not 80 when no of logical processors is 80?

Is my understanding correct that if there are 6 slow queries running over an hour all showing degree of parallelism 64 would be expected to slow as they might be hitting same threads and thus showing as cx packet waits. Hence I believe changing MAXDOP to say 16 or 8 by testing should give them their own threads to complete?

Thanks

The simple answer is that 64 is the DOP limit when zero is specified:

  To enable the server to determine the maximum degree of parallelism,
  set this option to 0, the default value. Setting maximum degree of
  parallelism to 0 allows SQL Server to use all the available processors
  up to 64 processors.

A single query can use more than 64 cores if you set that specific number (i.e. not zero):

  Set the value to a number from 1 to 32,767 to specify the maximum number of processor cores that can be used by a single query execution

See also SQL Server MAX DOP Beyond 64 – Is That Possible?

As for how SQL Server will allocate threads, this blog post is the closest I've seen to official documentation:

SQL Server Parallel Query Placement Decision Logic


Constraint based rules engine I am evaluating software systems which configure products that tout constraint based versus rules based engines.  Can someone explain (with maybe an example or two) to a non-expert what the difference is?  My guess is that a constraint based engine is rules defining relationships between objects, and rules based engines are more like the WHERE clause of a SQL language query.

Constraints will basically allow you to enforce data integrity - limiting the values that may be inserted into a column and there by enforcing business rules. The database engine (SQL Server) will automatically enforce integrity of a database based on the constraints defined
e.g. NOT NULL, CHECK constraints, UNIQUE constraints,  PRIMARY KEY constraints, FOREIGN KEY Constraints, etc.

For Rule Engine,

  A rules engine is a schedule based data validity application that typically runs as a meta-layer on top of an OLTP application. It fires a set of queries (defined as rules) which determine whether the underlying data elements comply with a specific rule’s definition. The compliance to the rule is then recorded indicating a pass or fail.

I will just point you to some Microsoft articles and other useful links (as I am not a pro in this area).

Building a Rule Engine with SQL Server
Choosing a Rules Engine Design
Creating a Rules Engine


Unable to delete a SQL Server 2012 publication I've set up a transactional replication for two databases, let's call them A & B.

Replication for the database A worked just fine and I was able to delete it afterwards without any issues.

Replication for the database B would error out and after checking the logs I found (Could not obtain information about Windows NT group/user 'DOMAIN\someaccount', error code 0x5.).

I was going to start fresh but now I am not even able to delete it. I am trying to remove a publication from the Publisher/Distributor (both using code and via Management Studio) and getting the same error.

What concerns me is database A had no issues whatsoever, and also the fact that DOMAIN\someaccount does not appear to own any objects or be referenced within the database at all.

Please help!

Looks like you have an object owned by a user that the service account can't validate.  you can figure out what object it is, and change the owner of the object to sa.  If it's the database, run this:

exec sp_changedbowner 'sa'


How can I prefer a specific MongoDB replica set member be elected as primary? I have a replica set with 5 members. replica-1 is a primary and other replicas are secondary:

replica-1 (primary)
replica-2 (secondary)
replica-3 (secondary)
replica-4 (secondary)
replica-5 (secondary)

If the primary replica (replica-1) goes down one of the secondaries will switch to primary. After fixing any issues I will bring up replica-1. Then I need replica-1 to become the primary again.

How do I achieve this? I have tried using the following command but any of the secondaries may be elected:

rs.stepDown()

How can I force replica-1 to be primary?

You can influence the outcome of elections by adjusting the priority for specific members. The default priority of 1 can be changed to any floating point value between 0 and 1000 (where 0 indicates a non-electable member).

For your use case, you might want to set your preferred primary to have a priority of 10. The priority value doesn't have any special significance aside from numerical ordering for all member priorities. For example: priority values of 1.01, 10, or 1000 are all higher than the default priority of 1.

Note: using priorities may result in extra elections to ensure a preferred member becomes primary when eligible. Typically it is best to think about replica set members as peers with similar resources so that any member can take on the role of primary. Unless there is a strong reason to have a specific member as primary (for example, a replica set distributed across multiple data centres), I would avoid using priorities or consider having a few preferred members with equal priorities in order to minimise elections.

An example sequence with replica-1 having a higher priority (10) than all other members with default priority (1):

replica-1 is current primary.
replica-1 is shutdown in order to be upgraded.
replica-2 is elected as the new primary.
replica-1 is restarted and resumes syncing in order to catch up with the current primary.
replica-1 catches up with the current primary and triggers an election so it can become primary.  replica-2 will drop all current connections when it steps down to a secondary.

Setting replica-1 and  replica-2 to the same priority would avoid the extra election.


Does a read apply locks when run outside of a transaction? If implicit_transactions = off and I execute a SELECT query outside of an explicit transaction, will any locks be applied or do locks only make sense as part of a transaction?

The way SQL Server handles locks is based on the isolation level that you are using and the state of the transaction. The default isolation level for SQL Server is READ COMMITTED. So if you perform a SELECT command without an explicit transaction, then the dynamic locking will decide the lock granularity.

For READ COMMITTED isolation level, the SHARED locks acquired on the index keys/RID (row identifier) will be released after the scan/seek has completed.

Refer to Locking in SQL Server and
Isolation Levels


Database Normalization - Can one field point to another? This may sound like a dumb question, but I am a software developer new to database design... So the concept makes sense to me, but maybe this doesn't carry--

Is it possible for a field in a record in Table B to simply point to a field in a record in Table A?

Example: If I have two tables... one containing a list of all employees and another containing a history of those employees coming and going from the office.

Employees
    ID
    First Name
    Last Name

History
    Event ID
    Event Time
    Event Type (Entry/Exit)
    EmployeeID

I can establish a relationship between History.EmployeeID and Employees.ID with a foreign key, but that duplicates data. So then History.EmployeeID and Employees.ID would contain the same ID number which would be stored in memory twice. So if I went into the database and changed John Smith's employee ID, I would need to write a script or something to scour the database and do a find/replace for that ID.

What I want is for Employees.ID to contain the real ID and History.EmployeeID to simply contain a pointer to Employees.ID. That way, if I updated John Smith's ID, the change would be centralized to one field in one table.

Is this possible?

Thanks

Even if SQL were to support a "pointer" like you describe, the pointer itself would take some memory. Ironically, the pointer might even be larger than the integer value it points to.

You tagged this question with MySQL, so I'll answer with respect to MySQL: there is no support for this pointer concept. In a relational database, columns contain values, not pointers.

Constraints are part of the table's metadata, and serve to make sure the value in the foreign key column does not get out of sync with the key column it references. Either it block changes, or duplicates changes if you declare the foreign key constraint with cascading effects.

Other implementations of RDBMS might have this feature, but not MySQL.


schema shows through standard client connection but not through ODBC connection? Hoping you can help!
The Gist
When I log into an Oracle 11g DB using SQL Developer or DBVisualizer, I can see schemas and execute queries against them.
However, when I use the same credentials to connect via an ODBC connection in Crystal Reports (2008), those schemas do not show up as being available to me.
Supporting Information

The ODBC connection will ultimately be created on Windows Server 2008
I'm currently testing the ODBC connection / setup on Win7 Enterprise x64
We are connecting to the DB via LDAP, not tnsnames (no port information specified -- only the SID and user/pass)
I can login to the database just fine in DBVisualizer and run a query against it
The ODBC connection tests successfully after logging in with the username and password
The connection to ODBC is successfully made by Crystal Reports; it enumerates certain system users, etc. -- just not the users I need access to (and that I have access to via a standard client)
Our DB is managed separately by a team of DBAs, who aren't the easiest folks to reach. I can, however, get questions answered most likely.

Help?
Any thoughts on where I could start or what the issue might be? Thanks!

As I suspected this may have been, it actually appears to be an issue in Crystal Reports 2008 on the client side.

Though I was unable to see the schema enumerated in the list of schemas, after selecting "Update" to link the two, I WAS able to select the various fields necessary and generate the report from the new datasource.

So, end result -- it doesn't show up, but it's there.


How to import the data of postgres 8.x to postgres 9.x My OS is windows 7 x64. I was use postgres 8.4, and all databases are in postgres/8.4/data. Yesterday, I reinstalled my os system, but I found I can't install postgres 8.4 anymore (there is always an error when installing), so I installed postgres 9.1.

But I don't know how to reuse the data of postgres 8.x. I tried to copy the data from postgres/8.4/data to postgres/9.1/data, but the postgres service won't start.

Is there any way to reuse that. Since I have a lot of data in postgres/8.4/data, I can't loose them.

What you need is a dump / restore. However, for this to work, you need the old PostgreSQL 8.4 server running. The new version 9.1 is not binary compatible and cannot use the files in postgres/8.4/data directly.

If you have no backup and cannot run PostgreSQL 8.4 on your machine any more, you are out of luck. Why is it that you cannot install PostgreSQL 8.4 any more? You'll have to, if you want your data.

On a side note:
It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantage of any enhancements that may have been made in these programs.


How to set selinux permission for non-standard mysql bin log path? By default, mysql bin log works in folder /var/log/mysql. but moving to any other share such /mylogs/mysql gives permission error.

Below is the exact message:

usr/sbin/mysqld: File '/mylogs/mysql/mysql-bin.index' not found (Errcode: 13 - Permission denied)

how to fix this without disabling selinux?

There is similar problem in the link below but solution is not acceptable as I cannot disable SELinux.
mysql bin-log.index not found

To set mysql selinux on a directory you can:

semanage fcontext -a -t mysqld_db_t "/yourdirectory(/.*)?"

then

restorecon -Rv /yourdirectory

Also check the mysql user has permissions on your directory:

ls -l

Set permissions if needed:

chown -R mysql:mysql /yourdirectory


How to add additional hard drive(s) to existing database I have a database that is close to filling the hard drive that it lives on.  I'd like to expand the database on to another hard drive to deal with this.

I see there's a Files option in the database properties under SSMS that I could add to but I don't really want to go adding files without knowing what comes next.  Do I need to add a new entry to Filegroups and then add to Files?  Is the PRIMARY type only supposed to be used for the first file?  If not then what is the difference between making one primary and another secondary?  Once I add a file, does the DB engine automatically decide where to put data or do I have to go into my tables and specify this in some way?

Sorry if this is a duplicate.  I'd be surprised if it isn't but I couldn't find another question that had these answers.

Do I need to add a new entry to Filegroups and then add to Files?

You can do one of two things here:

Add a new filegroup and at least one new data file
Add a new data file to an existing filegroup (PRIMARY in this case)

  Is the PRIMARY type only supposed to be used for the first file?

No, the PRIMARY filegroup can contain multiple data files.

  Once I add a file, does the DB engine automatically decide where to put data or do I have to go into my tables and specify this in some way?

Within the filegroup, SQL Server will manage the data dispersion across the data files through a proportional fill algorithm.  Therefore, you will not need to manually administer where the data goes within a filegroup.  But, if you create an additional filegroup with one or more data files, and you want data to live on that filegroup then you will need to explicitly state that (unless you set the new filegroup as the default filegroup).

For instance:

create table dbo.YourNewTable
(
    col1 int not null,
    col2 int not null
    -- so on and so forth
) on YourNewFilegroup;
go

To find out what your default filegroup currently is:

select
    name as default_filegroup_name
from sys.filegroups
where is_default = 1;

To change the default filegroup:

alter database YourDatabase
modify filegroup YourNewFilegroup
default;
go


Insert incoming data (and pk from 2nd table) into 1st table if true boolean value from 2nd table This question is further to my previous question answered very well by @ypercube here

I have been trying to work out the easiest way to ask the question without ambiguity, here I go...

I have data arriving from a sensor that contains a unique serial number and a data value. The sensor configuration along with its unique serial number is in a table called device which also contains other settings such as enabled or disabled.

I want to store the incoming data value into another table named datain along with the primary key from the device table.

I am currently doing this with several individual MySQL statements and wish to improve the efficiency by reducing it to one MySQL statement.

Steps to complete are:

Retrieve the primary key and boolean enabled state for supplied sensor serial number from device table
Test if primary key exists and boolean enabled value is true, if either is no, then just exit.
If primary key exists and boolean enabled is true, then store the incoming data value and the primary key (identified by the above test) into another table named datain

Maybe below explains a little better what I am trying to do. I know this is not valid MySQL, just an example of what want.

`INSERT INTO mydb.datain(sensorpk,sensorvalue) values (pk from sensor serial in device table, 25.4) WHERE mydb.device.pk =  pk from sensor serial in device table AND mydb.device.enabled = TRUE;`

Maybe the following SQL code will help you to find a solution.  The comments "-- step1, -- step 2" etc refer to your question. Dbfiddle here , also tested with MySQL 5.5.

create table device (
  serialno int primary key
, dataval int
, state tinyint
);
-- use tinyint instead of bool 0 = false, non-zero = true
-- https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

insert into device (serialno, dataval, state)
values
 (1,11,1),(2,22,0),(3,33,1),(4,44,0)
,(5,55,1),(6,66,0),(7,77,1),(8,88,0);

create table datain (
  id int auto_increment primary key
, device_serialno int references device(serialno)
, datain int
);

"Steps to complete" ... NOTICE: "step 3" contains both the SELECT and INSERT, as required (as one statement).  Steps 1 and 2 are just for "illustration".

-- step 1
select serialno, state from device;

-- find all devices and states (CASE - just to make the output more "readable")
select serialno
, state
, case
    when state = 0 then 'disabled'
    else 'enabled'
  end state_
from device;

-- "supplied sensor serial number"
set @id_ = 5;

-- step 2
select serialno, state
from device
where serialno = @id_
  and state <> 0 ;

-- step 3
insert into datain (device_serialno, datain)
select serialno, dataval
from device
where serialno = @id_
  and state <> 0 ;

If you wanted to insert data from all enabled devices, you could maybe use something like ...

insert into datain (device_serialno, datain)
select serialno, dataval
from device
where state <> 0 ;


Missing data for one schema in pg_dump I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like

pg_dump -h localhost -d db -U postgres -Fc -f file.dump

When I restored a backup I got the unexpected result that for one of the schemas the contents were missing entirely, only the tables were there, but empty. This affects all tables in that schema. The other schemas contained all the expected data when restoring from this backup.

To confirm that the data isn't in the backup I used the following command to create a readable text version of the binary database dump:

pg_restore mydb.dump > mydb.sql

I looked for the entry that is supposed to fill in the rows and found something like this:

--
-- Data for Name: some_table; Type: TABLE DATA; Schema: schema1; Owner: postgres
--

COPY schema1.some_table (column1, column2, column3, column4) FROM stdin;
\.

There was no data inserted at all with the COPY command here, and this entry looks similar for every table in this particular schema. When I look at the other schemas, there are the individual rows to be inserted in the COPY command as I would expect.

The schemas all have the same tables, but there is one difference that is possibly connected to the source of the problem. The schema with the missing data in the backup is owned by postgres, while all other schemas are owned by other roles. Each schema is supposed to be owned by a specific role, one role per schema. That the problematic schema is owned by postgres means there was an earlier bug or issue that caused the wrong ownership, but my understanding is that as I'm making the backup as the postgres superuser, the wrong ownership alone can't explain the missing data in the backup.

I'm now looking to figure out what happened here, and why the data is missing. Right now I don't have any explanation of how just this specific data could be missing in the database dump, when the dump itself is successful.

Is the way I'm transforming the binary dump valid, and do my observations on the plaintext dump mean that there is no data for this table in the dump?
Is there any way the ownership could affect the data contained in the dump when performing pg_dump as a role with SUPERUSER privilege?
What are the possible explanations for the missing contents of only one specific schema when dumping a whole database?

If you don't have the required permissions, pg_dump will give you an error message rather than an empty COPY statement. Moreover, permissions do not apply to superusers.

You should verify what the database user used by pg_dump sees when selecting from the original table.

The only explanations I can think of are:

Somebody deleted all rows from the table.
You have experienced data corruption that somehow rendered the table's file empty of zeroed out.

Try the following to find the file:

SELECT oid FROM pg_database
WHERE datname = 'db';

SELECT relfilenode FROM pg_class
WHERE oid = 'schema1.some_table'::regclass;

If the numbers returned are 1234 and 5678, examine the file base/1234/5678 in the data directory: How big is it, what does it contain?


Clustered index on a column with low selectivity I have a table with a lot of columns. Two lookup functions are performed: 1) lookup by using column name (this happens rarely, maybe twice a season), and 2) lookup by column date. The column name has a very high selectivity measure (almost all rows are distinct), whereas the date column has very low selectivity measure (only several distinct columns over a table size of 300,000 records)

I need to optimize the performance by creating an index. I wanted to create a clustered index on the date column, but it is my understanding that doing so will even slow down the performance of my queries given the low selectivity measure.

What should I do - Clustered index on date, or just clustered index on name (or no indexes at all) ? The name lookup happens rarely, but date lookup queries run all of the time.
Thanks a lot!

I'm using SQL Server 2012.

Low Selectivity

Here is a good quote from SqlServerCentral:

  In general, a nonclustered index should be selective. That is, the
  values in the column should be fairly unique and queries that filter
  on it should return small portions of the table.

  The reason for this is that key/RID lookups are expensive operations
  and if a nonclustered index is to be used to evaluate a query it needs
  to be covering or sufficiently selective that the costs of the lookups
  aren’t deemed to be too high.

  If SQL considers the index (or the subset of the index keys that the
  query would be seeking on) insufficiently selective then it is very
  likely that the index will be ignored and the query executed as a
  clustered index (table) scan.

  It is important to note that this does not just apply to the leading
  column. There are scenarios where a very unselective column can be
  used as the leading column, with the other columns in the index making
  it selective enough to be used.

Couple of things to consider when indexing:

When To Avoid Indexing

Indexes should not be used on small tables.
Tables that have frequent, large batch update or insert operations.
Indexes should not be used on columns that contain a high number of
NULL values.
Columns that are frequently manipulated should not be indexed.
When you already have a lot of indexes (do not over index a table)
Avoid Nonclustered, Unfiltered Indexes on Columns that have few Distinct Values (Low Cardinality)

Effectiveness of Indexing

I'm going to quote another stack exchange post:

  There are a few concepts and terms that are important to understand
  when dealing with indexes. Seeks, scans, and lookups are some of the
  ways that indexes will be utilized through select statements.
  Selectivity of key columns is integral to determining how effective an
  index can be.

  A clustered index's key columns are
  called a clustering key. This is how records are made unique in the
  context of a clustered index. All nonclustered indexes will include
  the clustered key by default, in order to perform lookups when
  necessary. All indexes will be inserted to, updated to, or deleted
  from for every respective DML statement. That having been said, it is
  best to balance performance gains in select statements against
  performance hits in insert, delete, and update statements.

  In order to determine how effective an index is, you must determine
  the selectivity of your index keys. Selectivity can be defined as a
  percentage of distinct records to total records. If I have a [person]
  table with 100 total records and the [first_name] column contains 90
  distinct values, we can say that the [first_name] column is 90%
  selective. The higher the selectivity, the more efficient the index
  key. Keeping selectivity in mind, it is best to put your most
  selective columns first in your index key. Using my previous [person]
  example, what if we had a [last_name] column that was 95% selective?
  We would want to create an index with [last_name], [first_name] as the
  index key.

Personally

I index on how the table is being used. This includes the holistic view of all queries on the tables as well what queries are not taking advantage of indexes. As Jeremiah Peshka said in the same thread, if the percentage of missing indexes is high, then an index on how it's being used is likely needed.

Key Takeaway

The SQL query engine loves highly selective key columns. Index on how a table is being used. Keep It Simple Stupid (KISS).


How to Count Rows in a Separate Table Using a Foreign Key I am in school to become a software engineer, and I am taking my first class on SQL. We usually use MsSQL, but the schools VMWare instance is down, so I have an older version of Oracle on my computer (Oracle 11g Express). I am trying to take my customer table, and orderInfo table to get the count of each customers # of orders. I have it working if I use the orderTable, but then it would not get customers that have 0 orders. Basically, if the customer is not in the orderInfo table, they won't have an order count. Please See the table structure below and my SQL code.

Customer Table

Order Info Table

This is my current SQL code and the output.

SELECT CUSTOMER_ID, COUNT(ORDERINFO.ORDER_ID) AS TotalOrders
FROM ORDERINFO
WHERE CUSTOMER_ID = CUSTOMER_ID
GROUP BY CUSTOMER_ID;

Output:

Thank you so much for the help in advance!

A join of some type between the two tables is needed - and then group by customers.customer_id. Then to get results for customers with no orders, one way would be to use a LEFT join:

SELECT
    C.CUSTOMER_ID,
    COUNT(O.ORDER_ID) AS TotalOrders
FROM
        CUSTOMERS  C
    LEFT JOIN
        ORDERINFO  O
    ON
        O.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY
    C.CUSTOMER_ID ;


SQL to choose a price between two points in time Given a time-series of datetime price changes and another disparate time-series of N events, how do I query the last known price at the time of each event without running N queries? Essentially, I need to write some kind of CROSS-JOIN BETWEEN query that matches the most recent known price.

For example, price change history:

Changed At (time) | Price (money)
                1 |    10
                5 |    20
               10 |    30
               20 |    40

Events:

Event Time | Nearest Matched Price (from above)
         0 | n/a
         3 | 10
         6 | 20
        10 | 30
        15 | 30

The real-world use-case is that I tracked a time series of stock movements with costs and prices, but neglected to store costs alongside my invoice lines. My stock movements usually occurred right before the time of invoice.

I have a similar calendaring report query that reports sales across calendar days/weeks/months, but only works because the CROSS JOIN condition (> now && < next-day) will only ever return one row. Pardon the noisy query (will clean it up):

SELECT [t3].[FirstDateOfWeek] AS [Date], [t3].[value] AS [Total], [t3].[value2] AS [Count]
FROM (
    SELECT SUM([t0].[Total]) AS [value], COUNT(*) AS [value2], [t2].[FirstDateOfWeek]
    FROM [dbo].[vw_Invoices] AS [t0]
    LEFT OUTER JOIN [dbo].[Cases] AS [t1] ON ([t1].[CaseId]) = [t0].[CaseId]
    CROSS JOIN [dbo].[Calendar] AS [t2]
    WHERE ([t0].[Date] >= [t2].[CalendarDate]) AND ([t0].[Date] < [t2].[NextDayDateTime]) AND ([t0].[Date] >= @p0) AND ([t0].[Date] < @p1) AND (NOT ([t0].[IsVoided] = 1))
    GROUP BY [t2].[FirstDateOfWeek]
    ) AS [t3]
ORDER BY [t3].[FirstDateOfWeek]

Is this even possible in SQL, perhaps with a "running total" query grouped by extracted hour?

Quick-and-dirty, not tested on a live instance.

For each row of Event data we need one row from Price - the one which happened most recently, but before the Event's timestamp. TSQL supports the top 1 .. order by notation. By embedding the Price lookup as a sub-query in a SELECT list it will be executed once per row in Event. Predicating Price on Event's values will ensure the most recent is returned. Something like this:

select
    e.event_id,
    e.event_time,
    ( select top 1     -- return one value
        p.price
      from Prices as p
      where p.price_time <= e.event_time -- ensure the price change happened at or before the event
      order by p.price_time desc  -- top ensure "top 1" picks the price with the gretest i.e. most recent, timestamp
    ) as price
from Events as e;

The sub-query will run once per row in Events, so performance may be compromised for very large sets. Make sure there are indexes on Price.price_time.


Powershell Format Table Combine Values I have an awesome script that gives vital data for running worker processes. What I'm attempting to do is to get these three values into a table so that they can be displayed all pretty-like. I previously used a .net extension to accomplish this but I've add problems with compatibility so I'd like to use strictly Powershell here. Here is my script:

$myprocess = "w3w*"
$filter = "*\process(" + $myprocess + ")*"
$pids = get-counter -listset process | get-counter -maxsamples 1 | select -expandproperty countersamples | where {$_.path -like "*\id process" -and     $_.path -like $filter} | select cookedvalue | ForEach {$_.cookedvalue}
$wsp = get-counter -listset process | get-counter -maxsamples 1 | select -expandproperty countersamples | where {$_.path -like "*\working set" -and $_.path -like $filter} | select cookedvalue | ForEach {$_.cookedvalue}
$w3wpn = $pids | foreach {
$filter = "processid='" + $_ + "'"
$wmip = gwmi Win32_Process -filter $filter
if($wmip.CommandLine -match "-ap `"(.+)`"") {
$appName = $matches[1] -replace '([^^"].)".*', '$1'
Write-Output $appName
}}

'WP,IP,Memory',$w3wpn,$pids,$wsp | ConvertFrom-CSV | Format-Table -Auto

and here is the output I'm looking for:

WP                  Process ID Mem (KB)
--                  ---------- --------
MyAppPool                 4816 76,435,456
AnotherAppPool           16924 205,369,344
DefaultAppPool           34508 233,574,400

Figured it out!

The key here was piping it out to an array successfully. I've had a lot of drawbacks here but you've got to work thru them.

$pids = get-counter -listset process | get-counter -maxsamples 1 | select -expandproperty countersamples | where {$_.path -like "*\id process" -and       $_.path -like $filter} | select cookedvalue | ForEach {$_.cookedvalue}
function GetMemoryUsage  { ...code here... }
function GetAppID { ...code here.... }
$myresults = @()
$x = foreach ($ids in $pids) {
 $thisapp = GetAppID $ids
 $thismem = GetMemoryUsage $ids
 $robj = New-Object System.Object
 $robj | Add-Member -type NoteProperty -name WP -value $thisapp
 $robj | Add-Member -type NoteProperty -name "Process ID" -value $ids
 $robj | Add-Member -type NoteProperty -name Memory -value $thismem

 $myresults += $robj
}
$myresults | ft -auto

The great thing abou this method is that you can pipe in an infinite number of variables and as long as you can relate these to the array then you're in business.


pgAdmin4 or pgAdmin3? I've seen a lot of hate directed to pgAdmin4, but honestly I can't see why. It worked out of the box from a docker image, connected right away to the database and perfectly managed every maintenance and query I've made.

Is there a good reason to keep using pgAdmin3?

Which database client to use is generally personal preference, and sometimes based on which client makes certain tasks the easiest to do.

That said, pgAdmin3 doesn't connect cleanly to PG10+.  In that regard you will need to switch entirely to pgAdmin4 at some point.  If now is a convenient time then skip pgAdmin3.

There are some obvious and non-obvious UX differences between the two, some features easier in pgAdmin3, some easier in pgAdmin4, but ultimately some users are just more comfortable with pgAdmin3.  How long you want to allow your fellow users/admins to use it is always dependent on your own use case.  I personally configured pgAdmin4 to use a browser that I don't normally use and that gives me a seperate location on my task bar to find my various query windows. This, and opening new query windows in new browser tabs have made pgAdmin4 more friendly for my own personal use.

While discussing database clients, however, don't forget about psql.  The more I use it, the more I go to it first.  The -E command line option is a great learning tool for deciphering how the pg_catalog tables work and the \gexec internal command is extremely useful for as is \watch.


Granting permission to the service account I am currently granting permissions to a service account on a database. I want to ensure if the granting db_datareader and db_datawriter database roles is the same as my grant command.

Adding service account to db_datareader and db_datawriter database roles.

EXEC sp_addrolemember N'DB_DATAREADER', N'INT\svc-w-corerefdata-de';
EXEC sp_addrolemember N'DB_DATAWRITER', N'INT\svc-w-corerefdata-de';

Creating a role, granting permissions to it and adding the service account to the role.

IF DATABASE_PRINCIPAL_ID('AlterPermCoreRefData') IS NULL
    BEGIN
        CREATE ROLE [AlterPermCoreRefData] AUTHORIZATION [dbo]
    END

GRANT EXECUTE, ALTER, SELECT, DELETE, INSERT, UPDATE ON database::[CoreReferenceStaging] TO [AlterPermCoreRefData]

EXEC sp_addrolemember 'AlterPermCoreRefData', 'INT\svc-w-corerefdata-de'

Have a look at the fixed db roles in SQL server:

In your first example (db_datareader and db_datawriter), the service account can only read and modify data in the tables.  With these 2 roles, the service account cannot modify the tables or perform and DDL.  Nor does it have EXECUTE permissions.

In your second example, you've given all the permissions above but you've added EXECUTE and ALTER.  This allows that account to execute SPs and also modify objects (structurally, or otherwise) within the DB.

So, to answer your question, no, they are not the same.  The second example has more permissions and can do more.


How to rename a PostgreSQL cluster? I googled and found this old web page, which said just to change the name of the directory.  I tried it but it didn't work:

$ pg_lsclusters
Version Cluster   Port Status Owner    Data directory                     Log file
8.4     main      5432 down   postgres /var/lib/postgresql/8.4/main       /var/log/postgresql/postgresql-8.4-main.log
9.1     main      50420 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log$ pg_ctlcluster 9.1 main stop

$ pwd
/var/lib/postgresql/9.1

$ ls
main

$ mv main oldmain

$ pg_ctlcluster 9.1 oldmain start
Error: specified cluster does not exist

PostgreSQL 9.1.4, Ubuntu 11.10

Update: version 166 of the postgresl-common package added pg_renamecluster. See Ilya's new answer about that.

In older versions (including Postgres 9.1.4 on Ubuntu 11.10) the name of a cluster is bound to the name of the data directory in the "Multi-Version/Multi-Cluster PostgreSQL architecture" introduced by Martin Pitt. This is shipped with Debian packages (Debian, Ubuntu and related distributions). For a detailed description of the architecture, see:

/usr/share/doc/postgresql-common/README.Debian.gz

Or browse the file online here.

Other installations do not have all of these features, so some people may be confused when you talk about cluster names.

Just renaming the data directory does not work, because you have to change the directory in/etc/postgresql/<version>/<cluster> accordingly.

And there are a couple of settings in your postgresql.conf, that have to be adjusted, too. Run a grep in the directory to see:

postgres@db:/etc/postgresql/9.1/main$ grep 'main' ./*


Creating an SSIS package that uses a stored procedure We're trying to make an SSIS package where it'll launch a stored procedure and capture the contents in a flat file. This will have to run every night, and the new file should overwrite the existing file.

This wouldn't normally be a problem, as we just plug in the query and it runs, but this time everything was complicated enough that we chose to approach it with a stored procedure employing temporary tables. How can I go about using this in a DTS package? I tried going the normal route with the Wizard and then plugging in EXEC BlahBlah.dbo... It did not care for that:

  The Statement could not be parsed. Additional information: Invalid
  object name '#DestinyDistHS'. (Microsoft SQL Server Native Client
  10.0)

Can anyone guide me in the right direction here?

Thanks.

If you are calling the temp table from a different task try this

https://stackoverflow.com/questions/5631010/how-to-create-a-temporary-table-in-ssis-control-flow-task-and-then-use-it-in-dat


Calculate path to node in a tree The following is part of a bigger system. The tree data table and temporary table cannot be changed easily so I need to work with those. The tree structure is more complex (deeper and wider) but the following are illustrative of the problem I am trying to solve.

I have a simple tree structure:

AAA
  - aaa
      - 111
      - 222

that is represented in data as:

Id  ParentId  Text
------------------
1   NULL      AAA
2   1         aaa
3   2         111
4   2         222

for a common function there is a temporary table where I want to determine which records need to be used to ultimately build the path to a particular item:

Id  ParentId  Text  UseRecord
-----------------------------
1   NULL      AAA   False
2   1         aaa   False
3   2         111   False
4   2         222   False

So for instance if I wanted to find which records are necessary to build the tree to item "222" after updating the temporary table it would look like:

Id  ParentId  Text  UseRecord
-----------------------------
1   NULL      AAA   True
2   1         aaa   True
3   2         111   False
4   2         222   True

What is the SQL statement I need to achieve the above result assuming it updates UseRecord and its only input is the Id value of 4 (ie "222"'s record).

This has to be really simple and fast, but for the life of me I cannot figure it out. All pointers appreciated.

You can use a recursive CTE to fetch Id at parent' levels:

DECLARE @Id int = 4;

WITH cte(level, Id, ParentId) AS(
    SELECT 0, Id, ParentId FROM @data WHERE id = @Id
    UNION ALL
    SELECT c.level+1, d.Id, d.ParentId FROM cte c
    INNER JOIN @data d ON c.ParentId = d.Id
)
SELECT Id, Level FROM cte
;

Output

With @Id = 4, This query returns the path with several Ids:

Id  Level
4   0
2   1
1   2

It only returns Ids on the path from the root up to the Id store in @Id. This is probably all is needed. I didn't try to update the @data table.

However this can easily be done by replacing the SELECT by an UPDATE:

WITH ...
...
UPDATE @data SET [UseRecord] = 1
WHERE Id IN (SELECT Id FROM cte)
;

SELECT * FROM @data;

Notes

It is uneasy to say whether it will be efficient and fast. There are not enough information and details in the question (data model, number of rows, quantity of data, indexes, etc.).

It is worth mentioning that it is probably a bad idea to update the temp table and it may also be a bad idea to use a temp table in the first place.

Perhaps the same result can be achieved with a single query (SELECT with cte) without creating a temp table. It could avoid tons of IO (read and write the temp table X times). The above query is a starting point anyhow.

Data used

DECLARE @data TABLE(Id int, ParentId int, [text] varchar(5));
INSERT INTO @data(Id, ParentId, [text]) VALUES
    (1, NULL, 'AAA')
    , (2, 1, 'aaa')
    , (3, 2, '111')
    , (4, 2, '222');

Replace @data by your temp table name in the query.


when do lock actually happen in a transaction? Let's say we have a transaction A:

BEGIN TRAN A;
//--------------------< time a
 SELECT productid, unitprice
 FROM Production.Products
 WHERE productid = 2;
//--------------------< time b
 UPDATE Production.Products
 SET unitprice += 1.00
 WHERE productid = 3;
COMMIT TRAN A;

We know that for the transaction A (under default Read Committed isolation level), a share lock (for productid 2) is needed and a exclusive lock(for productid 3) is needed.

My question is, for the exclusive lock for productid 3, when did the lock happen? At the beginning of the transaction, at time a or at time b when the transaction actually starts to do the update?

You can see for yourself using DMVs. In SSMS open two query tabs. Paste this into one:

select * from sys.dm_tran_active_transactions
select * from sys.dm_tran_locks

Paste your statements into the other.

Run my queries. This will show background activity on the server which can be ignored.

Run just your BEGIN TRAN A; statement and re-run the DMV queries. You'll see there's a new transaction with name=A but no further locks.

Now hightlight just your select statement and run it and also the DMV queries again. Nothing has changed! What happened to the S locks? Well, running under the default READ COMMITTED isolation level the locks are release as soon as the DB engine has finished processing the particular row. See section "SQL Server Locking Read Committed" here. So the S locks were taken while the query was execution but had been released by the time the DMVs were examined again. (You can see the locks and releases happening using extended events. There are queries in this answer which you can modify to see this for yourself.)

We can make the locks remain to the end of the transaction using the HOLDLOCK table hint. Change your SELECT to ..FROM Production.Products WITH(HOLDLOCK).. and re-run the SELECT and the DMV queries. You will see shared locks on the table (and other objects).

Run just the UPDATE statement. The shared (S) locks have become exclusive (X) locks.  Because these locks are protecting altered data they are always held until the end of the transaction - no additional hints are required.

Running your COMMIT will release the locks and remove the transaction.

For your original question - when was the exclusive lock taken on row with productid 3 - the answer is, when the query processor came to that particular row as it was working through the query plan generated for that update statement.


Azure SQL Database "Login failed for user" in application, but works fine in SSMS I wanted to try out the contained database users feature on Azure SQL Database V12, but I'm having a problem authenticating that seems odd to me.

I created a database called Classifier.  I added my IP to the firewall rules so I could connect to the Azure db server from SSMS on my workstation.  Once I was able to get connected via SSMS for administration, I tried adding a user with a password to the database, like this:

CREATE USER classifier WITH PASSWORD='thepassword'

I also added this user to the data writer and reader roles:

exec sp_addrolemember 'db_datawriter', 'classifier'
exec sp_addrolemember 'db_datareader', 'classifier'

After this, I'm able to connect to the database with these credentials from SSMS:

But this is where things go awry: I've tried several different connection string incantations and can't seem to get connected in a web app I'm working on.  It didn't work in the Azure environment, so I'm running on localhost with a connection string to the Azure database, and it just won't connect.  Here's the connection string I'm using at the moment:

<add name="Classifier" connectionString="Data Source=xxxxxxx.database.secure.windows.net;Initial Catalog=Classifier;User ID=classifier;Password=xxxxxxxxxxxxx;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient"/>

I've tried resetting the password (via SSMS) for the user and then updating the connection string; I also double-checked the password by copying it right out of this connection string and into the connect dialog in SSMS to make sure I didn't have a typo of some kind there.

I enabled auditing in the Azure db server hoping to get some details as to why it's failing, but all I get is this:

And this is where I'm stuck.  Most of what I've been able to find by way of documentation or blogs indicate that the thing to do is look at SQL Server logs to see what the real error state is which would indicate more narrowly the nature of the failure, but since I'm dealing with Azure there's no way to do that (as far as I know).

What could cause the application to fail where SSMS (and LinqPad and Visual Studio Server Explorer, incidentally) succeeds?

We found that with contained databases / contained users you must specify:

GRANT CONNECT TO [YOUR_USER]

Otherwise CONNECT seems to be revoked by default. Once we made the above change, we could access the database.


Basics of creating a table and refreshing it via a mysql event My apologies in advance for my display of ignorance.  Is there a way for the event_body of an event to take the following:

DROP TABLE IF EXISTS compdist1.comp_CL1;

CREATE TABLE compdist1.comp_CL1 AS SELECT * FROM compdist1.prices_CL_analysis;

I am discovering that only one line is being picked up. (the top one).  Is there a way to make it do both steps? Basically, drop the table and repopulate it from the view prices_CL_analysis.

Wrap both statements in BEGIN and END, like:

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END

Taken from a quick perusal of the MySQL Create Event page.

So, your code might become:

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'blah'
    DO
      BEGIN
          DROP TABLE IF EXISTS compdist1.comp_CL1;
          CREATE TABLE compdist1.comp_CL1
          AS
          SELECT *
          FROM compdist1.prices_CL_analysis;
      END

Also redefine the MySQL delimiter temporarily before the CREATE EVENT … to a different symbol and use the declared symbol at the end of the definition so that MySQL can pass the entire CREATE EVENT statement to the server. Redefine it back to ; after the definition, like this:

DELIMITER $$

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'blah'
    DO
      BEGIN
          …
      END$$

DELIMITER ;

This is needed when passing compound statements. Since the event body contains more than one statement, you event definition now qualifies as a compound statement. More information at the MySQL Defining Stored Programs page.


Extracting data from Excel files How to extract data from spreadsheets or Excel to database using terminal in Ubuntu?

I'm using MySQL in Ubuntu and now I have 3 different Excel files using which I need to create a database in SQL.

Can someone help me with how can I extract that Excel data to MySQL?

The simplest way is to create a formula in the additional column.
This formula has to make a correct INSERT statement. E.g.:

="INSERT INTO MYTABLE (field1, field2) VALUES ('" & A1 & "', '" & B1 & "');"

Then fill in the whole column with this formula
(A1 and B1 become A2 and B2, A3 and B3, etc.)

Select the whole column and copy to clipboard,
then activate the terminal and insert from clipboard.


Get failed SQL Server agent job? How to get a list of failed job run last night? I only find the following powershell script. What's the SQL equivalence?

dir $psPath\Jobs | % { $_.EnumHistory() } | ? { $_.RunStatus -ne 1 }

Here you go. From: http://www.toadworld.com/platforms/sql-server/w/wiki/10351.sql-server-agent-job-query-samples.aspx

SET  NOCOUNT ON

DECLARE @MaxLength   INT
SET @MaxLength   = 50

DECLARE @xp_results TABLE (
                       job_id uniqueidentifier NOT NULL,
                       last_run_date nvarchar (20) NOT NULL,
                       last_run_time nvarchar (20) NOT NULL,
                       next_run_date nvarchar (20) NOT NULL,
                       next_run_time nvarchar (20) NOT NULL,
                       next_run_schedule_id INT NOT NULL,
                       requested_to_run INT NOT NULL,
                       request_source INT NOT NULL,
                       request_source_id sysname
                             COLLATE database_default NULL,
                       running INT NOT NULL,
                       current_step INT NOT NULL,
                       current_retry_attempt INT NOT NULL,
                       job_state INT NOT NULL
                    )

DECLARE @job_owner   sysname

DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()

INSERT INTO @xp_results
   EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

UPDATE @xp_results
   SET last_run_time    = right ('000000' + last_run_time, 6),
       next_run_time    = right ('000000' + next_run_time, 6)

SELECT j.name AS JobName,
       j.enabled AS Enabled,
       CASE x.running
          WHEN 1
          THEN
             'Running'
          ELSE
             CASE h.run_status
                WHEN 2 THEN 'Inactive'
                WHEN 4 THEN 'Inactive'
                ELSE 'Completed'
             END
       END
          AS CurrentStatus,
       coalesce (x.current_step, 0) AS CurrentStepNbr,
       CASE
          WHEN x.last_run_date > 0
          THEN
             convert (datetime,
                        substring (x.last_run_date, 1, 4)
                      + '-'
                      + substring (x.last_run_date, 5, 2)
                      + '-'
                      + substring (x.last_run_date, 7, 2)
                      + ' '
                      + substring (x.last_run_time, 1, 2)
                      + ':'
                      + substring (x.last_run_time, 3, 2)
                      + ':'
                      + substring (x.last_run_time, 5, 2)
                      + '.000',
                      121
             )
          ELSE
             NULL
       END
          AS LastRunTime,
       CASE h.run_status
          WHEN 0 THEN 'Fail'
          WHEN 1 THEN 'Success'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Cancel'
          WHEN 4 THEN 'In progress'
       END
          AS LastRunOutcome,
       CASE
          WHEN h.run_duration > 0
          THEN
               (h.run_duration / 1000000) * (3600 * 24)
             + (h.run_duration / 10000 % 100) * 3600
             + (h.run_duration / 100 % 100) * 60
             + (h.run_duration % 100)
          ELSE
             NULL
       END
          AS LastRunDuration
  FROM          @xp_results x
             LEFT JOIN
                msdb.dbo.sysjobs j
             ON x.job_id = j.job_id
          LEFT OUTER JOIN
             msdb.dbo.syscategories c
          ON j.category_id = c.category_id
       LEFT OUTER JOIN
          msdb.dbo.sysjobhistory h
       ON     x.job_id = h.job_id
          AND x.last_run_date = h.run_date
          AND x.last_run_time = h.run_time
          AND h.step_id = 0


What happens when you delete a Clustered index and Non Clustered Index once created What happens when we dropped a Cluster index and Non Clustered Index once they are created?

What will happen to the data at the leaf level according to Clustered Index ?

From MSDN:

  When a clustered index is dropped, the data rows that were stored in
  the leaf level of the clustered index are stored in an unordered table
  (heap). Dropping a clustered index can take time because in addition
  to dropping the clustered index, all nonclustered indexes on the table
  must be rebuilt to replace the clustered index keys with row pointers
  to the heap. When you drop all indexes on a table, drop the
  nonclustered indexes first and the clustered index last. That way, no
  indexes have to be rebuilt.

https://technet.microsoft.com/en-us/library/ms190691(v=sql.105).aspx

As for the nonclustered index drops, they aren't DELETING any data, it's a metadata operation and the index pages are simply deallocated. Think about TRUNCATE versus DELETE, TRUNCATE just deallocates the pages and you're done.  Dropping a nonclustered index is very similar, hence why dropping one is lightning fast when you do it (unless you are blocked).


SQL Server - Combining Bulk Insert with Manual value I have a Comma separated CSV file containing ID, FirstName, and LastName. I am trying to insert it into my existing table, however the table has few more column which the CSV file is missing thus I am wondering if it's possible to insert the CSV file combined with manual value mapping ?

Below is how my table looks like:

And this is how my CSV file looks like:

As you can see, the CSV file only contains the IdentityId column of the Table (is's called Id in the file) and Email and other fields are missing, therefore I am wondering if I can manually provide them or I need to insert the bulk item first, and then write a command to update all of them?

A possible solution is to use OPENROWSET

Assuming a csv file (emp.txt):

User1,100,Dep1
User2,1000,Dep2

Assuming a format file (format_file.xml):

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="Salary" xsi:type="SQLDECIMAL"/>
  <COLUMN SOURCE="3" NAME="DepartmentName" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

--demo setup
drop table if exists employees;
drop table if exists departments;
go
CREATE TABLE Employees (
    NAME NVARCHAR(20)
    ,Salary DECIMAL
    ,DepartmentID INT
    )
GO

CREATE TABLE Departments (
    DepartmentID INT
    ,DepartmentName NVARCHAR(20)
    )
GO

INSERT INTO Departments
VALUES (1,'Dep1')

INSERT INTO Departments
VALUES (2,'Dep2')

--solution
--Insert data into Employees table
INSERT INTO Employees
SELECT NAME
    ,Salary
    ,DepartmentID
FROM OPENROWSET(BULK 'C:\testdata\emp.txt', FORMATFILE = 'C:\testdata\format_file.xml') emp
JOIN Departments dep ON (emp.DepartmentName = dep.DepartmentName)

--verify
select * from Employees

| NAME  | Salary | DepartmentID |
|-------|--------|--------------|
| User1 | 100    | 1            |
| User2 | 1000   | 2            |


Connecting MySQL to Visual Studio 2017 For the life of me, I cannot get the MySQL for Visual Force connector to work in VS. I just want to be able to add a MySQL database as a database connection in VS so I can build an integration.

I have the community version of VS 2017. I have followed the instructions listed on the MySQL website - I have tried the MySQL Installer as well as the standalone download.

My first problems began because the MySQL installer would not recognize the installation of Visual Studio (even though I ALSO have 2013 installed!). So I uninstalled Visual Studio, the MySQL ODBC and .NET connectors, and I tried reinstalling them over and over again. Each time I would attempt to install the MySQL for Visual Studio connector, the installer would say that Visual Studio was not installed on my computer.

So then I installed the connector by itself, BEFORE installing the ODBC or NET connectors and I got it to install. I still could not get the MySQL connector to appear when I tried to connect a new database to VS, though.

Frustrated, I decided to create a User DSN (as opposed to a System DSN) and connect that way. It seemed to work, and I was able to see the connector in VS. However, now when I try to connect, I get "ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application".

I looked up the error, and it appears that there is a mismatch between the x86 and x64 drivers. The problem is that I cannot find a x64 driver for MySQL for VS.

I'm at the end of my rope. I just want to build out a package that will allow me to pull data from the MySQL and push to a SQL server. Is there a better way to do all of this, or am I on the right track? I would appreciate any assistance.

Please use Visual Studio Community Installer and install .Net Development.  After this, you will be able to install MySql for Visual Studio 1.2.7.