How to know if mysql tmpdir is being used I set my MySQL tmpdir setting to use /run/mysqld which is a tmpfs, however while running a query I know copies data to a temp table (which takes 5-6 seconds based on MySQL's INFORMATION_SCHEMA.PROFILE records) I am not seeing any temporary file(s) being written out to that directory when doing an ls -la at the command prompt.

Additionally, the query speed did not change when moving my tmpdir from an on-disk location to an in memory location (tmpfs) which it clearly should have improved.

Question: How do I know for sure that my tmpdir is being used?

In *nix, a file can be creat'd, rm'd, then written to and read from by the same process.  No flavor of ls can see it, since it is already rm'd from the directory, yet the program can see it.  This is a trick that makes sure that the file is not left behind if the program crashes.  MySQL often uses the rm trick.

ls cannot see the rm'd-but-not-closed files, but lsof can see them as (deleted).

df will change because of the space being consumed.

If you show us the SELECT, SHOW CREATE TABLE and EXPLAIN SELECT.  We may be able to give you tips on avoiding the tmp table.  (I assume your real question is about performance?)


SSIS 2012 metadata not refreshing on re-deploy I have three systems: DEV, UAT, PROD, all running SQL 2012 Enterprise.  I have existing packages that hit objects on these systems.  Every time I have to make a change that affects metadata, it's not enough that I update the objects on the target system, then update the SSIS package that points to the system, and then re-deploy the package on the system.  Even though the package will have been updated prior to re-deploy to handle the new metadata, I still have to go onto each system and update the package AGAIN before it will recognize the new metadata.

This behavior is new to me.  In BIDS 2008, a refresh of metadata on one system would persist to any system it was deployed to. Now, if I change the connection manager prior to deploying it, I have to go back and re-do the metadata refresh before I can send it up.  It appears that the metadata per connection setting is retained and is not updated when the package is updated.

An example so I can make this clear: I add a column to a destination table on all three systems.  I update the package while it's pointing to DEV to reflect the change, and then deploy.  So far, so good.  Then I update the connection manager to point to UAT, and deploy the changed package there.  At this point I'd expect that there would not be a required refresh of the metadata, as the schema of the destination table in UAT matches the one in DEV, to which the package was pointed when it was last refreshed.  However, the package acts as if it was not updated, and needs its metadata refreshed while explicitly pointing to that system in order to work.

To try to address this problem, I added an expression for the ServerName property on the Connection Manager to be determined by System::MachineName, thinking that the problem was that changing the Connection Manager on the machine I'm developing on immediately triggered a check of the objects there, even with Delay Validation turned on.  No such luck; it still shows the same behavior.

I can't find much info about this online, to determine if it's expected behavior with SSDT 2012 or something I'm doing incorrectly or a genuine bug.

Anyone have any insight into this issue?

Honestly, you shouldn't be editing the package to point to another environment before deploying it. Please look into package configurations to learn about methods to deploy your packages without editing the package itself.

Likely your connection strings and other settings should just be a variable read from a configuration file.

Since you are using SQL 2012 you should probably be using environments (thanks @MartinSmith)


Need an Oracle query to get the Oracle Home directory for a particular instance I am searching for a query to get the Oracle Home for a particular instance name, say Eagle (if there are more than one instance on the host).

You can do this from a Unix shell, like so:

[oracle@node1 ~]$ grep "^YOURSID:" /etc/oratab | cut -d: -f 2
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@node1 ~]$


Postgres Error for table I want to create this table, but i can’t,

Look this error.

CREATE TABLE ATTR(

    window character varying(64) NOT NULL

);

  ERROR:  syntax error at or near "window"
  LINE 2:     window character varying(64) NOT NULL

window is a reserved word. You can create a column with that name if you enclose it in double quotes:

CREATE TABLE ATTR
(
  "window" character varying(64) NOT NULL
);

More details about identifiers, quoted identifiers and keywords can be found in the manual:
http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

But I highly recommend you find a different name that does not need to be quoted. Using quoted identifiers is more trouble in the long run than they are worth it.


How to persist a lot of boolean fields? I need to persist on Oracle Db the follow check box table :

The simple thing that I can imagine is to create a Table  with 15 boolean fields, but since that the rows and the columns could increase I would to know if there is a better way to persist this table without to provide a table of Number of rows *  Number of Columns.

Thank You in advance for the replies

p.s: the meaning the table is to mean:

is the north Mountainous? true o false?
is the north Hilly? true o false?
is the north Flat? true o false?
is the Middle Mountainous? true o false?
etc.

If you are concerned about the size you can do a binary lookup:

North = 1
Middle = 2
South = 4
Est = 8
Ovest = 16
Mountainous = 32
Hilly = 64
Flat = 128

Of course, then you have to write a function which translates your number into readable results.


how to install SQL 2000 in windows server 2016 OS how to install SQL 2000 in windows server 2016 OS, Is it possible ,please give me a solution.

I would highly recommend that you take the time and effort required to connect your app to a newer version of SQL, preferably one that is officially supported. i.e 2012+. (SQL 2000, 2005 and 2008 and 2008R2 are all officially obsolete.)

If you have issues with deprecated features or connectivity, SQL 2008R2   supports database compatibility back to 2000. It's still obsolete, but is still a much more sophisticated product than 2000.

Edit: Technically Support for 2008 & 2008R2 is still available from Microsoft through their MS Lifecycle Support Site.

In most cases apps are oblivious to which version of SQL they are on as long as they can connect and the features and functions still work.

Even if you can't do a backup and restore, you should be able to create a new db and use SSIS to import the data. If this is a current application I highly recommend you go back to the vendors for assistance and ask/insist they support current db's. Requiring a 17 yr old db to be able to install or run their software is terrible.

If this is a legacy app and no longer supported I suspect you have a much bigger issue looming than just moving a db.

If this is a legacy app and you just want to keep it running but need to replace hardware, I would consider virtualising the server.


Inserting and updating and selecting at thousands of times per second I have a table that gets inserted, updated and selected from thousands of times within one second. I am having deadlock issues though.

The database has 2-5 simultaneous 1000+ row inserts using Linq to Sql.
40 times per second, there is also a select statement from that table, and if a condition is true (95% of the time it is), an update happens with code similar to this:

create procedure AccessFile (@code, @admin) AS

declare @id int, @access datetime, @file string

select @id=Id, @accessed = accessed, @file = file from files where code = @code

IF @admin<> 0 IF @accessed is null begin

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

update files set accessed = getdate() where id = @id

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

end

select @id as Id, @file as File

It seems as though it is the updates conflicting with the inserts that are causing the deadlocks.

The example is a 1 to 1 with the stored procedure, only difference is the names. Assume the 1 and 2, regardless of the stored procedures names.

Change the updates to use WITH (ROWLOCK).  That will reduce the locks from page level to row level.  You can try the same on the inserts as well.


Confused about sys.dm_io_virtual_file_stats num_of_reads/writes column I'm trying to use the sys.dm_io_virtual_file_stats DMF to generate so IO wait information across a number of my servers.  I came across this post by Paul Randall: http://www.sqlskills.com/blogs/paul/survey-tempdb-and-transaction-log-io-latencies/.  In it he asked people to respond with the results of this query:

SELECT
    [database_id],
    [file_id],
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END
FROM
    sys.dm_io_virtual_file_stats (NULL, NULL)
WHERE
    [file_id] = 2 OR [database_id] = 2;
GO

My question is why is he using io_stall_read_ms / num_of_reads instead of io_stall_read_ms / num_of_bytes_read in order to get the average.  I would think ms/byte would be more informative than ms/read.  Which at least in part leads me to believe I don't understand what the num_of_reads/writes columns actually represent.

BOL just says

  Number of reads issued on the file.

which doesn't really help me much.  I'd appreciate any clarification I can get.

The latency of of the I/O system (which the average latency per IOPS is an indicator of) tell you if you are adding more load on the I/O system than it can handle. Because of this, the average latency, when compared with the media you are running on provides a good indicator as to whether I/O is the problem or not.

As a rule of thumb, here are the latencies you should expect if you are not overloaded on the I/O system

Spinning media: Around 5-10ms
SSD: <1ms

If you are seeing latencies higher than that, you know that the I/O system is being asked to do more work than it can handle


Search for X at the beginning of a line within a multi-line string On SQL Server (2008), I have a column which is varchar(max).

I want to query for all rows where the blurb contains a lettered list:

Some sentence is here

a. foo
b. bar
c. baz

Some more text later

Can do this easily in regular expressions, but I do not have access to add regex capabilities to my instance.

So, How can I search using LIKE to find something like this? Presumably I would need to find where the first character of the line (in a multi-line blurb) is a character and 2nd is a dot.

You might have to play with CHAR(13) and CHAR(10) depending on the source of the text, but this might be simply:

WHERE CHAR(10) + col LIKE '%' + CHAR(10) + '[A-Za-z].%'
------^^^^^^^^ in case the text *starts* with a lettered list

Note this will also find:

Some sentence is here

a. foo

Some more text later

If you need to only return rows where there is a list with two or more lines, you may not want to do this in the database, because it would require much more complicated parsing. In fact in that case - and if I couldn't perform the parsing outside the database (or in the database with CLR RegEx), I would probably use an ordered split function.


INSERT of 1 wrong value -> Error; INSERT of 2 wrong values -> Warning! CREATE TABLE `dummy` (
    `a` TINYINT(4) NOT NULL
) ENGINE=MyISAM;

1 wrong value

INSERT INTO `dummy` (`a`) VALUES (NULL);
/* SQL Fehler (1048): Column 'a' cannot be null */
/* Nothing is stored! */

2 wrong values

INSERT INTO `dummy` (`a`) VALUES (NULL), (NULL);
/* Affected rows: 2  Gefundene Zeilen: 0  Warnungen: 2  Dauer von Abfrage: ... sec. */
SHOW WARNINGS LIMIT 5; /* Not sure where this comes from! Maybe HeidiSQL? */
/* Two entries are stored! */

Why is MySQL raising an error with the single value query, but not at the second query with two values?

Is it related to SHOW WARNINGS LIMIT 5; But where does this line come from and why is it changing the error type from Error to Warning?

MySQL V5.6.31

It is way MySQL is designed. As per the documentation:

  If you try to store NULL into a column that doesn't take NULL values, an error occurs for single-row INSERT statements. For multiple-row INSERT statements or for INSERT INTO ... SELECT statements, MySQL Server stores the implicit default value for the column data type. In general, this is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. ....

I did a test to see the contents of the table (Notice that I got the warnings!):

mysql> CREATE TABLE `dummy` (
    ->     `a` TINYINT(4) NOT NULL
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `dummy` (`a`) VALUES (NULL);
ERROR 1048 (23000): Column 'a' cannot be null

mysql> INSERT INTO `dummy` (`a`) VALUES (NULL), (NULL);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+---------------------------+
| Level   | Code | Message                   |
+---------+------+---------------------------+
| Warning | 1048 | Column 'a' cannot be null |
| Warning | 1048 | Column 'a' cannot be null |
+---------+------+---------------------------+
2 rows in set (0.00 sec)

mysql> select * from dummy;
+---+
| a |
+---+
| 0 |
| 0 |
+---+
2 rows in set (0.00 sec)


How should I best handle a rapidly growing database? I have a database that I need to maintain.
Sadly, the setup and use of that database I can't change, much (thanks to some internal politics).
It's running on SQL Server 2008r2.
Its only been live for 5 days and has grown from 20GB to upwards of 120GB in that time. (essentially most of the data gets deleted and then imported, but like I say I can't control that side of things)
I would love to run nightly jobs to shrink the database and reorganise the indexes, but I know that's a long way from best practices and could lead to more problems than I've already got!
QUESTIONS

What's the best way to handle a database that's rapidly increasing in size?
Should I be looking at moving the file group around to keep the physical size on disk down?
Is there any way to stop the server running out of space within a month?

I don't recommend to shrink your database files unless you are completely sure that the space will not ever be needed again.  Why shrink every night only for it to grow every day?  You're going to run into the pain points of shrinking your data files, and you're forcing yourself to run into performance impact when the database files need to grow during the day.

If it ramped up to 120 GB because of initial space requirements, is it safe to say that the database will not be larger than that (obviously with some buffer)?  Size your database appropriately.

Otherwise, if the database will continuously grow, and you don't have any input on the data growth that happens (in other words, if you can't archive data off of the database) then you will simply need to ensure that there is ample drive space for the required growths.

Do you know which database files have been growing?  There is different behavior that will cause data files to grow, as opposed to transaction logs to grow as well as maintaining their size.  If the transaction log is the main consumer of the space because of heavily logged transactions, you can consider more frequent transaction log backups to ensure the log is reused more often (there are some stops here, as open transactions, so this might not be possible across the board).

More information surrounding what is growing and the scenario is needed to get more specific, but I don't recommend scheduled shrinking of your database files.


PostgreSQL pg_basebackup from several port number I have one slave server which used as a replication server (let's called it slave1) from several master servers.

The `slave1' server is set up to receive replication from several postgresql server, and I set it at multiple ports. The settings is like this:

Master1 port 5432 replicated to slave1 port 5432
Master2 port 5432 replicated to slave1 port 5433
etc.

Those server above (master1, master2, & slave1) are hosted at cloud.

all server using Postgresql-11 on Ubuntu 18.04.1 LTS

Is it possible to replicate my slave1 to my on premise server at office (called slave2), so that all databases on slave1 on all port is replicated to my slave2 on single port (port 5432 which is the default port of postgresql) ??

Using physical replication, no, that isn't possible.  You would have to use the new-ish logical replication feature to consolidate the data from multiple clusters to one cluster.


Time out errors - MS access front end with SQL backend First of all I don't know anything about SQL, so I apologise if I make no sense.

I have created a MS access database and our IT area migrated the tables to SQL server 2008 and linked the tables to the front end.

The database works fine, until the load increases (around 1000 users), then whenever trying to submit a record the user gets the following error:

Error 3155 ODBC--insert on a linked table 'tblMain' failed.
[Microsoft][ODBC SQL Server Driver]Query timeout expired (#0)

That error occurs for about 90% of users, basically making it unusable. At around 4pm when most user leave for the day, the database begins to work again without issue.

The IT area say everything is fine on their end, there are no limits set to users or locks, and the issue is with the front end and the code being too complex. But there is no way that's it, because the code behind the submit button is as simple as it gets.

DoCmd.GoToRecord , , acNewRec

I don't know what to do as IT are being difficult, I just need to get a better understanding so I can get them to look into it further. So any advice appreciated.

Oh, and I got sneaky access to SQL, and if I go to database properties is says number of users = 8. This doesn't change if I open the database. Does that mean it's limited to 8 people?

Many thanks,
Jeremy.

The Timeout Expired error you're seeing is very likely due to how Microsoft Access fetches rows from SQL Server.  If, for instance, the form in question uses data-binding to populate controls on the form with data from SQL Server, and there is no filter on the form that selects a SINGLE row of data, Access will keep the results open, and "page" through the rows one single row at a time.  This will prevent others from updating any of the rows in the table.

For a system used by 1,000 users, you should never ever use Access as a front-end, as tempting as that may be.  Use Access  (it you must) as a rapid design prototyping tool, then build out the real solution using Visual Studio or some other enterprise-level development software.


Powershell to dump database schema to text file I want to be able to dump a database schema to a sql text file using Powershell. I want this because our DBAs want to be able to do a diff on the file that is committed to SVN. They won't execute it if they can't see what it contains.

I've found many scripts that backup to a binary .bak file, but I can't find any that dump as a sql text file.

How do you do this in Powershell?

Use the dbatools module.

From your svn you need to build your solution. With those dacpacs you can use the following command.

Publish-DbaDacPackage -SqlInstance sql2017 -Database WideWorldImporters -Path C:\temp\sql2016-WideWorldImporters.dacpac -PublishXml C:\temp\sql2016-WideWorldImporters-publish.xml -GenerateDeploymentReport -ScriptOnly

Reference:
https://docs.dbatools.io/#Publish-DbaDacPackage


SQL server calculated field value Not sure how to start describing this so I get straight to it.

I have a SQL Server 2012 database with a sample table Table_1 with the below (simplified) design:

id: int (PK, ID, NOT NULL)
name: varchar(100) (NOT NULL)
label: varchar(100) (NOT NULL)
description: varchar(max) (NOT NULL)

on my front end form user is asked for a name and description only.

What I want to achieve from the database is to, at the point of creating new record, get the value of the name field, convert it to lowercase and replace any spaces with underscores, and then insert the resulting string into the label column.

For example if user provided name "Sample Product", I want the label to be "sample_product".

How can I achieve this and what is the best approach: triggers or the calculated field property can do this?

Thanks in advance for any help or pointers.

In my experience is better a calculated field than a trigger because a trigger could generate performance issues in the future. Try this piece of code that uses a calculated field:

CREATE TABLE [dbo].[Table_1](
    [id] [int] NOT NULL,
    [name] [varchar](100) NOT NULL,
    [label]  AS (lower(replace([name],' ','_'))),
    [description] [varchar](max) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO [dbo].[Table_1]
([id]
,[name]
,[description])
VALUES
(1
,'Sample Product'
,'Sample Product Description')

GO


Efficient pagination for big tables Using PostgreSQL 10.5. I'm trying to create a pagination system where the user can go back and forth between various of results.

In an attempt to not use OFFSET, I pass the id from the last row in the previous page in a parameter called p (prevId). I then select the first three rows whose id is higher than the number passed in the p parameter. (as described in this article)

For example, if the id for the last row in the previous page was 5, I'd select the first 3 rows with an id is higher than 5:

SELECT
  id,
  firstname,
  lastname
FROM
  people
WHERE
  firstname = 'John'
  AND id > 5
ORDER BY
  ID ASC
LIMIT
  3;

This works great and the timing isn't very bad either:

Limit  (cost=0.00..3.37 rows=3 width=17) (actual time=0.046..0.117 rows=3 loops=1)
   ->  Seq Scan on people  (cost=0.00..4494.15 rows=4000 width=17) (actual time=0.044..0.114 rows=3 loops=1)
         Filter: ((id > 5) AND (firstname = 'John'::text))
         Rows Removed by Filter: 384
 Planning time: 0.148 ms
 Execution time: 0.147 ms

Although, if the user, on the other hand, would like to return to the previous page, things look a bit different:

First, I'd pass the id for the first row and then put minus sign in front of it to indicate that I should select the rows with an id that's less than (a positive) p parameter. Namely, if the id for the first row is 6, the p parameter would be -6. Similarly, my query would look like the following:

SELECT
  *
FROM
  (
    SELECT
      id,
      firstname,
      lastname
    FROM
      people
    WHERE
      firstname = 'John'
      AND id < 6
    ORDER BY
      id DESC
    LIMIT
      3
  ) as d
ORDER BY
  id ASC;

In the query above, I first select the last 3 rows with an id that's less than 6 and then reverse them in order to present them in the same way as the first query described in the beginning.

This works as it should, but since the database is going through almost all my rows, the performance is suffering:

Sort  (cost=4252.75..4252.76 rows=1 width=17) (actual time=194.464..194.464 rows=0 loops=1)
   Sort Key: people.id
   Sort Method: quicksort  Memory: 25kB
   ->  Limit  (cost=4252.73..4252.73 rows=1 width=17) (actual time=194.460..194.460 rows=0 loops=1)
         ->  Sort  (cost=4252.73..4252.73 rows=1 width=17) (actual time=194.459..194.459 rows=0 loops=1)
               Sort Key: people.id DESC
               Sort Method: quicksort  Memory: 25kB
               ->  Gather  (cost=1000.00..4252.72 rows=1 width=17) (actual time=194.448..212.010 rows=0 loops=1)
                     Workers Planned: 1
                     Workers Launched: 1
                     ->  Parallel Seq Scan on people  (cost=0.00..3252.62 rows=1 width=17) (actual time=18.132..18.132 rows=0 loops=2)
                           Filter: ((id < 13) AND (firstname = 'John'::text))
                           Rows Removed by Filter: 100505
Planning time: 0.116 ms
Execution time: 212.057 ms

With this being said, I appreciate that you have taken the time to read this far and my question is, how can I make the pagination more efficient?

The key to performance is a matching multicolumn index of the form:

CREATE UNIQUE INDEX ON people (firstname, id);

UNIQUE, since sort order can be ambiguous without it, and you can get arbitrary results from peers.

A UNIQUE or PRIMARY KEY constraint serves as well.

While the first column is checked for equality like in your example (or sorted in the same direction as the query), this index is good for paging up and down, though it's a bit better for paging up.

With the index in place (and after running ANALYZE on the table), you won't see sequential scans any more (unless your table is small). The database is not "going through almost all your rows" any more.

Read the fine presentation by Markus Winand you are linking to.

If you want paging across more than one firstname, use ROW values. Example for paging down:

SELECT *
FROM  (
   SELECT id, firstname, lastname
   FROM   people
   WHERE  (firstname, id) < ('John', 6)  -- ROW values
   ORDER  BY firstname DESC, id DESC
   LIMIT  3
   ) d
ORDER BY firstname, id;

Related:

Optimize query with OFFSET on large table

If the SELECT list only adds lastname like in your example, you might try to add that column to the index to get index-only scans out of it:

CREATE UNIQUE INDEX ON people (firstname, id, lastname);

Index expressions in this order.

The upcoming Postgres 11 allows indexes to INCLUDE columns, which results in smaller index size, better performance and is applicable in more situations. Like:

CREATE UNIQUE INDEX ON people (firstname, id) INCLUDE (lastname);


SSRS Reports Blank on Zebra Printer I need assistance getting labels to print from SSRS 2014 to a Zebra GK420D direct thermal label printer. The labels show up when running the report but when I print them to the label printer, all that comes out is a blank page. If I export to a PDF and then print, it works fine, but this isn't a viable option for our end users.

I've set the printer to the correct label size as well as changing the page size in the report to match and I've changed the printer settings to "Always use driver settings" and to print directly to the printer (bypassing the spooler) which were the two recommendations I found thanks to googling. Any assistance is greatly appreciated.

The issue appears to have been limited to printing from report builder. When I print from SSRS directly, the labels print out as intended. Not sure what the problem is with Report Builder but I'm content the problem has been fixed.


How do you verify that all InnoDB tables are in good shape? Without using a script, Is there a way to get mysql to check all the tables at once to make sure they're not corrupted?

innodb is very very very unlikely to have any corruption caused by the storage engine.  (corruption caused by applications isn't something mysql can help with).
Upon each start up it will check it's log files and automatically recover any from any partially completed transactions in the event of a crash.
That being said it is still very possible for a replication slave to become out of synch.  For that reason, or even if you just want to be uber paranoid you can use the tool pt-table-checksum
This will sometimes intelligently checksum your live tables in chunks so performing checks doesn't require downtime (either through explicitly taking the server down or having tables locked for a checksum table; query).  These numbers as a one off don't really tell you much.  As with any checksum you need something to compare it against to have any value.  If you run periodic checksums you can compare against previous values to alert you of unexpected changes.
Now, obvious you will be making changes in a database so this isn't a catchall.  This could be a good method for checking archival tables were data is not updated once it is written.
Note:  I said it "sometimes intelligently" does checks in chunks.  You might run into issues if you only have a PK/unique key that is multi column.


Creating a Trigger in MySQL that stores stock levels once a sale takes place I have a basic database for a retail store with 4 tables, one of which stores Order info, one that stores Product info and one that stores Supplier info.

I  am trying to run a query that will display total sum of stock sold figure (in dollars) with a GROUP BY supplier with ROLL UP. I have a feeling that SUM should be in the code but I can't find where to put it, so far I have this:

SELECT ORDERS.OrderPrice, PRODUCT.SupplierID, SUPPLIER.SupplierName
FROM ((ORDERS
INNER JOIN PRODUCT ON ORDERS.ProductID = PRODUCT.ProductID)
INNER JOIN SUPPLIER ON PRODUCT.SupplierID = SUPPLIER.SupplierID)
GROUP BY SupplierName WITH ROLLUP;

The database can be viewed on DB Fiddle here:

https://www.db-fiddle.com/f/qS515ShyFGxgj4h6Ga6HA3/1#&togetherjs=aNgE8G0yMh

Any ideas? Thanks

You must use SUM function with orderPrice

SELECT SUM(ORDERS.OrderPrice), SUPPLIER.SupplierName FROM ((ORDERS INNER JOIN PRODUCT ON ORDERS.ProductID = PRODUCT.ProductID) INNER JOIN SUPPLIER ON PRODUCT.SupplierID = SUPPLIER.SupplierID) GROUP BY SupplierName with ROLLUP


sys.dm_exec_procedure_stats reset I want to get stats from the sys.dm_exec_procedure_stats view.

When I check the records from months ago, I see that after a point of time its stats reset daily, but there have been no server resets.

It seems there is a job running that makes this view reset. Can anyone help me with this problem? What kind of procedures will reset these views?

From the docs

  The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

This dmv is therefore reset for each stored procedure recompile, which can happen, for example, after index and statistics maintenance


How can I get a list of column names and types from a resultset? Say I get a resultset back from the following query:

select *
from sys.database_files;

For any given resultset, I want to be able to query the column names and types so I can then create tables to store the results.

What is a good way of performing this in T-SQL?

Way late to the party, but just thought I'd mention a metadata enhancement in SQL Server Denali that will make it much easier - not only to inspect the output of a query without running it (not quite the same behavior as SET FMTONLY ON, which many apps use today), but also to build target tables dynamically (without all the parsing and case work involved with pulling metadata from sys.columns). Here is a quick example - notice you never have to actually run the query to figure out the shape of its resultset:

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM sys.database_files';

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

There are some limitations, of course. If you have a query with multiple statements, the phrase "first_result_set" in the name should give a clue that you will only receive information about the first statement that returns data - it will not stop at a leading SET NOCOUNT ON; for example. There are also going to be issues if you are referencing > 3-part names either directly or by trying to follow a view or synonym. But for the majority of use cases this will be a welcome simplification.

I blogged a lot more details back in December, also describing some of the other metadata enhancements:

https://sqlblog.org/2010/12/20/sql-server-v-next-denali-metadata-enhancements


How in Oracle can I export package that will have package and body? I'm using putty. How in Oracle can I export package and its body to a file in present directory using spool?

Thanks

Use SYS.DBMS_METADATA.GET_DDL to get a CLOB with the code:

declare
  myPackageSpec clob;
  myPackageBody clob;
BEGIN
    select dbms_metadata.get_ddl('PACKAGE_BODY', 'PACKAGE_NAME', 'SCHEMA')
       into myPackageBody
      FROM DUAL;
    select dbms_metadata.get_ddl('PACKAGE_SPEC', 'PACKAGE_NAME', 'SCHEMA')
      into myPackageSpec
    FROM DUAL;

  dbms_output.put_line(myPackageSpec);
  dbms_output.put_line(myPackageBody);
end;

You could also use expdp from command line and then impdpwith sqlfile

expdp scott/tiger@db10g schemas=SCOTT INCLUDE=PACKAGE SQLFILE=scott.sql directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
impdp scott/tiger@db10g SQLFILE=scott.sql directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log


Risk of dropping users in postgres Is there any risk in DROPPING users in postgres?
We have bunch of users in our database that no longer work at the company.
I was wondering what (if any) risk there was in dropping those users?

The only risk is that you might drop a user that you still need.
Other than that, PostgreSQL tracks dependencies between users and other objects and won't let you drop users that have permissions or own objects in the database.


Optimizing PostgreSQL query with multiple subqueries I have a rather simple question (I think): I have one query with 2 window functions: COUNT() and AVG(). I also have 10 subqueries, each with a window function (COUNT() or AVG()).
Testing in pgadmin, this query works fast. The problem is: I'm currently testing against only 100 rows.
Here is the query:
SELECT
    COUNT(a.review_id)::int AS total_reviews,
    COALESCE(AVG(a.score), 0)::int AS average_score,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 1)::int AS total_1,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 2)::int AS total_2,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 3)::int AS total_3,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 4)::int AS total_4,
    (SElECT COUNT(review_id) FROM data.users_pro_reviews WHERE user_id = $1 AND score = 5)::int AS total_5,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW()), 0)::int as today,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '1 DAY'), 0)::int as yesterday,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '1 WEEK'), 0)::int as one_week_ago,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '1 MONTH'), 0)::int as one_month_ago,
    COALESCE((SELECT AVG(score) FROM data.users_pro_reviews WHERE user_id = $1 AND timestamp_reviewed < NOW() - INTERVAL '6 MONTH'), 0)::int as six_montsh_ago
FROM
    data.users_pro_reviews a
WHERE
    a.user_id = $1

How can this query be optimized? Using JOINS? And how should I use window functions in JOINs?

It's probably more performant to write it this way instead of querying the same table multiple times:
SELECT
    COUNT(a.review_id)::int AS total_reviews,
    COALESCE(AVG(a.score), 0)::int AS average_score,
    SUM(CASE WHEN score = 1 THEN 1 ELSE 0 END)::int AS total_1,
    SUM(CASE WHEN score = 2 THEN 1 ELSE 0 END)::int AS total_2,
    SUM(CASE WHEN score = 3 THEN 1 ELSE 0 END)::int AS total_3,
    SUM(CASE WHEN score = 4 THEN 1 ELSE 0 END)::int AS total_4,
    SUM(CASE WHEN score = 5 THEN 1 ELSE 0 END)::int AS total_5
/* ...similar syntax for your AVG calculations */
FROM
    data.users_pro_reviews a
WHERE
    a.user_id = $1

Using the SUM function with a well crafted CASE statement becomes logically equivalent to how you were previously trying to COUNT things. You can also implement similar logic for the AVG function (which I'll probably update this answer with an example for).
As per @LaurenzAlbe, this is another example you can use leveraging the FILTER keyword:
SELECT
    COUNT(a.review_id)::int AS total_reviews,
    COALESCE(AVG(a.score), 0)::int AS average_score,
    COUNT(*) FILTER (WHERE score = 1)::int AS total_1,
    COUNT(*) FILTER (WHERE score = 2)::int AS total_2,
    COUNT(*) FILTER (WHERE score = 3)::int AS total_3,
    COUNT(*) FILTER (WHERE score = 4)::int AS total_4,
    COUNT(*) FILTER (WHERE score = 5)::int AS total_5
/* ...similar syntax for your AVG calculations */
FROM
    data.users_pro_reviews a
WHERE
    a.user_id = $1

I do have to agree this syntax feels a lot cleaner.


How to remove partial duplicates from a table I have a database that contains some erroneous results that are essentially but not technically duplicates. The structure is like this:

id_page (PK), id_site, label, create_date

There should only be one entry for each site for each label but it turns out that I have some duplicates for site and label, where id_page and create_date are different. I would like to remove the rows with the lowest create_date.

I think I have come up with a solution for this but I'd appreciate feedback.

WITH duplicates as (
   SELECT id_page, id_site, count(id_site) over (partition by id_site) as ct,
    min("create_date") over (partition by id_site) as dt
   from pages
   where label = '2018-12-15'
   )
DELETE from pages
where id_page in (
   select p.id_page
   from duplicates as d
   join pages as p on (p.id_page = d.id_page
                   and p.create_date = d.dt)
WHERE ct = 2
);

WITH duplicates as
(
    SELECT id_page, id_site,
           row_number() over (partition by id_site order by id_site, create_date) rn
    FROM   pages
    WHERE  label = '2018-12-15'
)
DELETE FROM pages
WHERE  id_page IN (SELECT id_page
                   FROM   duplicates
                   WHERE  rn > 1);


Conditional Query selection on the basis of paarameters I have two sql:

1st:

Select emp.id from Employee
where '&BI'=I
UNION ALL
Select emp.id from Employee
where '&BI'<>I

2nd:same as first one but addition of few more tables and more union all

(
Select emp.id,sal.salary from Employee emp, Salary sal
where '&BI'=I AND emp.id=100 AND '&BI' Is not null
UNION ALL
Select emp.id,sal.salary from Employee emp, Salary sal
where '&BI'<>I AND emp.id=100 AND '&BI' Is not null
)
UNION ALL
(
Select emp.id,sal.salary from Employee emp, Salary sal
where '&BI'=I AND emp.id=sal.id AND '&BI' Is not null
UNION ALL
Select emp.id,sal.salary from Employee emp, Salary sal
where '&BI'<>I AND emp.id=sal.id AND '&BI' Is not null
)

want to combine both 1st and 2nd in to one select statement and run it dynamically.
Please help!!

I'm not sure you know what you are doing.

Two things about query #1: it is invalid, and it is useless.

What is emp.? There's no table alias emp there ... It might be a function id that belongs to package named emp, but I somehow doubt it. You probably meant something like

select emp.id from employee emp
where ...

If that's so, it just doesn't make sense. The way you wrote it, it is equal to

select id from employee

because the first SELECT returns values values "equal" to something, while the second one returns values "different" from that something, and you then UNION ALL them. That's the whole contents of the EMPLOYEE table.

The first part of query #2 is wrong as well; you're performing Cartesian product of employee and salary tables (and then restrict the result with WHERE clause). What is

and '&BI' is not null

condition used for? Did you mean

and I is not null

instead?

Also, what is I? The way you put it, it is a column in one of those tables (probably employee, according to query #1). No problem if that's so, it's just that it is somewhat unusual name for a column.

Now, what do you mean by "combining" those two queries? How? You can't UNION them because their column list doesn't match. What would you combine, then?

Finally, how do you run anything dynamically? What do you mean by saying that?

Please, provide a test case (create table & insert into sample data), and - using that data - explain the result you'd want to get and rules that should make that happen.


Which of these build numbers will tell me what version of SQL Server is installed on a server? The main Microsoft SQL Server 2008 R2 (64-bit) doesn't contain a version number in Programs and Features in Microsoft Windows Server 2008 R2:

To me it almost looks like I have SQL Server 2008 R2 RTM installed on the server since the version numbers match some of the other components listed, notably Microsoft SQL Server 2008 R2 Policies.

My goal is to install SP2 but when I tried last night I got an error message concerning an unexpected version number.

Is there a way to use sqlcmd to find out what version it is?

Yes, you can query the database to find out the version via sqlcmd.

The actual SQL you'll want to use is just SELECT @@VERSION but to do it via sqlcmd, you can use:

sqlcmd -S[server] -U[user] -P[password] -q "SELECT @@VERSION"

Also check the MS sqlcmd reference for further info on flags you can use with sqlcmd.


How to design a database that tracks sports player's positions I'm building a database for a sports (ultimate) tracking program. Basically, the "raw data" for the program will be the positions and IDs for all the players on the field at one time. But the way I'm currently doing it seems to me to be overly complicated.

The main table, games, has columns for  7-10 player IDs on each team, matched against the IDs contained in the players table. It also has a column for each player's position on the field. But this creates a table with over 28-40 columns!

I'm fairly new to designing my own database, so I could be missing something basic, but is there a simpler way to do this?

(For what I want to do with the data, I do need to have a record of each player's position at each timestamp)

Clarification: 'position' means physical location on the field, not the name of the player's position

Sounds like your design isn't quite right. You probably need it like this (I imagine this is for football?):

Team Table - TeamID, TeamName...

Player Table - TeamID, PlayerName, PlayerPreferredPosition...

Position Table - PositionID, PositionName, PositionType (backs/mid/forward/Goalkeep etc)

Game Table - GameID, GameLocation, HomeTeam (FK Team), AwayTeam (FK Team), MatchStartTime...

GamePlayPosition - GameID (FK Game), PlayerID (FK Player), PositionID (FK Position), TimeStart, TimeEnd

That way you can figure out what players are playing at what position in what match at any time.

EDIT: If you want to use a grid and indicate exactly the time that player is spending there, I would suggest the following change, but what is above is still correct.

To the GamePlayPosition table add a surrogate key - GamePlayPositionID, use as your primary key. Then add another table:

ExactPlayPosition - GamePlayPositionID (FK), XPosition (small int), YPosition (small int), GameTime (datetime? Possibly an integer representing the seconds elapsed in the game time, easier to compare relative position game to game without doing datetime calculations if done with gametime).

The reason you have another table like this is because it will have a HELL of a lot of data, you want it as small as possible in terms of row width. You will be adding probably 3600 (# seconds in match) multiplied by 10 (# players in match) = 36k records per match, minimum if you don't include breaks. If tracking both teams of players, say 75k records per match, say over 50 matches... you get the idea.


SQLCMD cannot connect my named instance I am using SQL Server 2017 based on Windows server 2016. I am trying to connect my named instance, but I got the error message as below screenshot:

So I tried to reinstall ODBC Driver 13. But I got the error as follow:

Even after I uninstalled ODBC Driver 17, I still got the same error message.
Any thought?

Thanks in advanced!

You must specify the server name with -S (capital letter) instead of -s (lowercase).

From the documentation:

   -s col_separator
   -S [protocol:]server[instance_name][,port]


How to take backup of schema postgresql? Hi I am currently using postgresql and deleting some of the tables for testing purpose and I want to take backup of schema. can any one let me know how to do that ?

You export only schema without data using pg_dump in psql [terminal]:
pg_dump -U postgres -s databasename > backup.sql

or with Pgadmin4

Go to Tool -> Backup and select only schema in the Dump option.


Read-ahead reads and SQL-Variant fields I have two tables containing exactly the same data. Both table have bigint primary key identity column, 60 columns and 300 000 rows. The difference is that all of the columns of the second table have sql-variant type.

I am creating temporary table and importing the data from the two tables in it. When data is extracted from the sql-variant columns it is cast to the corresponding SQL type.

The data extraction from the first table is performed for 1 sec and from the second table for 6 secs.

Basically, the differences in the executions are in the estimations:

and in the read-ahead reads count:

I am wondering why the SQL Server cannot load in advance the data readed from the sql-variant fields (there are almost no read-ahead reads).

Also, the storage size of the tables is almost the same:

Why the SQL Server thinks it should read 67 GB?

The column types are:

16 x BIGINT
8  x DECIMAL(9,2)
36 x NVARCHAR(100)

The dbcc dropcleanbuffers command is used each time before data extraction and population.

In order to test you can download the sample data files from here. Then,

Execute Tech05_01_TableDefinitions.sql
Execute 3 times the Tech05_02_TablePupulation.sql
Open the Tech05_03_TestingInsertionInTempTable.sql file and excute one time like this:

DECLARE @TableStructured SYSNAME = '[dbo].[NormalDataTypes_60Cols]';
DECLARE @TableFromWhichDataIsCoppied SYSNAME = '[dbo].[SQLVariant01_60Cols]';

and one time like this:

DECLARE @TableStructured SYSNAME = '[dbo].[NormalDataTypes_60Cols]';
DECLARE @TableFromWhichDataIsCoppied SYSNAME = '[dbo].[NormalDataTypes_60Cols]';

There are a few issues with the Tech05_03_TestingInsertionInTempTable.sql file. It uses a function called [dbo].[ConcatenateWithOrder] which isn't defined. I think I understood your intention and adjusted it to produce a few insert statements.

To answer your first question, it is not true that SQL Server cannot use read-ahead reads to get sql_variant data. If I run DBCC DROPCLEANBUFFERS; immediately before the insert I get the following output from statistics io:

  Table 'SQLVariant01_60Cols'. Scan count 1, logical reads 66311, physical reads 3, read-ahead reads 66307, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

To answer your second question about the estimated row size in the plan, SQL Server does not look at the space used by the table and divide by the number of rows. Instead the query plan contains an estimation based on the datatypes in the table. For more details read this post by Martin Smith.

SQL Server estimates your table to have a row size of 235 KB. I can get close to that:

  240484.0 = 0.5 * (8016 * 60 + 8)

That's a little off from 235 KB but I don't know how the space estimate for sql_variants are calculated exactly. But you can see that you are likely to get overestimates when using a datatype that can store many different datatypes. For the record I don't think that estimate has anything to do with the performance issue that you observed.

To answer the question that I think you wanted to ask (why is the query with sql_variant so much slower than other one?), I tested it a bit and there appears to be a large CPU overhead when selecting from the sql_variant table. I selected the rows instead of inserting them (discarding them before they reached the client) and found a significant difference in cpu time. The sql_variant query had a CPU time of 3151 ms and a total elapsed time of 3268 ms. The other query had a CPU time of 686 ms and a total elapsed time of 744 ms. The difference in CPU time almost fully explains the difference in run time.

It doesn't seem unreasonable for there to be an overhead when working with sql_variant columns, right? I don't know of any tricks to avoid paying the overhead. If you feel that your data model needs sql_variant columns I can only recommend testing to make sure that you can get good enough performance.


Dynamic query execution error I'm trying to execute the following dynamic query code:

  declare @sql nvarchar(max)
  set @sql = 'DECLARE @DatePartitionFunction nvarchar(max) = N''CREATE PARTITION FUNCTION DatePartitionFunction (datetime)
                                             AS RANGE RIGHT FOR VALUES ('';
    DECLARE @incremental_date nvarchar(150);

    select @incremental_date = '+CONVERT(VARCHAR(24),GETDATE(),112)+'
    declare @counter int = 0;
    WHILE @counter < 3
    BEGIN
        SET @DatePartitionFunction += '''' + CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112) + '''' + N'', '';
        SET @counter += 1;
    END

    SET @DatePartitionFunction += '''' +  CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112)  + '''' + N'');'';
    print (@DatePartitionFunction)
    end;'

exec (@sql);

However, it returns an error stating incorrect syntax.

When I execute the same code outside the dynamic query context, it returns output. Stated below is the same code outside the dynamic query context

        DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime)
                                                         AS RANGE RIGHT FOR VALUES (';
        DECLARE @incremental_date nvarchar(150);

        select @incremental_date = CONVERT(VARCHAR(24),GETDATE(),112)
        declare @counter int = 0;
        WHILE @counter < 3
        BEGIN
            SET @DatePartitionFunction += '''' + CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112) + '''' + N', ';
            SET @counter += 1;
            END

        SET @DatePartitionFunction += '''' +  CONVERT(VARCHAR(24),DATEADD(DD, @counter, @incremental_date),112)  + '''' + N');';
        print (@DatePartitionFunction);

It produces the desired output text as imparted below:

CREATE PARTITION FUNCTION DatePartitionFunction (datetime)
 AS RANGE RIGHT FOR VALUES ('20150923', '20150924', '20150925', '20150926');

This is what i want the console to output in case of the aforementioned dynamic query, however I'm being met with errors.
Help!

The error you are receiving is because you have a trailing end; in your dynamic SQL version that is out of place (and doesn't exist in the non-dynamic version that works):

end;' --> should be just '

Your dynamic SQL version is missing the required number of single quotes (so when you fix 1., you'll still have dates that look like integers).
This seems like a tidier approach, mostly because it avoids a lot of the single-quote-spaghetti, looping constructs, repetitive conversions, and changing the number of partitions requires only one change in the TOP() argument:

DECLARE @sql NVARCHAR(MAX) = N'CREATE PARTITION FUNCTION
   DatePartitionFunction (datetime)
   AS RANGE RIGHT FOR VALUES (';
;WITH c(rn) AS
(
  SELECT TOP (4) CHAR(39) +
    CONVERT(CHAR(8), DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1,
    GETDATE()), 112)  + CHAR(39)
  FROM sys.all_columns
)
SELECT @sql += STUFF((SELECT ',' + rn FROM c
  ORDER BY rn FOR XML PATH,
  TYPE).value(N'.[1]',N'varchar(max)'),1,1,'');
SET @sql += N');';

PRINT @sql;

Why you would then need to nest that in dynamic SQL, I'm not sure, but you could:

DECLARE @sql NVARCHAR(MAX) = N'
   DECLARE @pf NVARCHAR(MAX) = N''CREATE PARTITION FUNCTION
   DatePartitionFunction (datetime)
   AS RANGE RIGHT FOR VALUES ('';

;WITH c(rn) AS
(
  SELECT TOP (4) CHAR(39) +
    CONVERT(CHAR(8), DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1,
    GETDATE()), 112)  + CHAR(39)
  FROM sys.all_columns
)
SELECT @pf += STUFF((SELECT '','' + rn FROM c
  ORDER BY rn FOR XML PATH,
  TYPE).value(N''.[1]'',N''varchar(max)''),1,1,'''');
SET @pf += N'');'';

PRINT @pf;';

EXEC sys.sp_executesql @sql;

Please avoid lazy shorthand like DD. It takes an extra microsecond to type DAY, but the benefit is well worth it: it makes the code that much clearer and more self-documenting, and avoids fostering that shortcut for other dateparts, which can change the meaning and, therefore, the results (like y and w).


Profling for Index Maintenance I am wanting to perform some index maintenance, in particular we have a poor performing query against a particular table.  Is it a sensible strategy to profile on RPC:Completed, SP:StmtCompleted and SQL:BatchCompleted but filtering only on that table?

The idea being I can focus my indexing maintenance on that table and work out what the best indexes will be?

Joe Changs ExecStats provides a nice GUI for analysing which queries touch each index.

It can be downloaded from: http://www.qdpma.com/JoeChang.html

Or write Joe directly for the latest version. The tool uses sys.dm_exec_query_stats so it is very lightweight.


Exclude Duplicates With Certain Value I'm been beating my head up against a wall with this all day.

I have a SQL Server table called Accounts

CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL)

    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson')
    GO
 INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0)
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall')
    GO
 INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0)
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson')
    GO
    INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole')
    GO

I'm trying to eliminate the duplicate rows with 0 as the FirstName and LastName.

The non duplicate rows with zeros I want to keep. I want to eliminate only the rows that contain zeros that have a duplicate IDNum.

I can't add an a primary key column.

My first approach was to find the duplicates, put in table variable, filter the zero rows then union that back with the table excluding those ID numbers. Then I thought it has to be a better way. Any help?

End Result

If I understand your question correctly, how about something like this:

DROP TABLE IF EXISTS dbo.Accounts
go
CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL)

INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole')
GO

;WITH _dups
AS (
    SELECT IDNum
        ,count(*) cnt
    FROM accounts
    GROUP BY IDNum
    HAVING count(*) > 1
    )
--now join _cte to the real table to delete the one's with zeros in First and Last name
DELETE a
FROM accounts a
JOIN _dups d
    ON d.idnum = a.idnum
        AND a.firstname = '0'
        AND a.lastname = '0'

SELECT *
FROM accounts

OR

DROP TABLE IF EXISTS dbo.Accounts
go
CREATE TABLE [dbo].[Accounts](
    [IDNum] [INT] NULL,
    [FirstName] [VARCHAR](50) NULL,
    [LastName] [VARCHAR](50) NULL)

INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (123, N'Joe', N'McDaniels')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (124, N'Mary', N'Cain')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (125, N'John', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (126, N'Sue', N'Bird')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (127, N'Michael', N'Bennett')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, N'Larry ', N'Jackson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (128, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (129, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, N'Tanya', N'Hall')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (130, 0, 0)
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (131, N'0', N'0')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (132, N'Joe', N'Johnson')
GO
INSERT [dbo].[Accounts] ([IDNum], [FirstName], [LastName]) VALUES (133, N'Stephen', N'Cole')
GO

DELETE a
FROM accounts a
WHERE FirstName = '0'
    AND LastName = '0'
    AND EXISTS (
        SELECT *
        FROM accounts
        WHERE IDNum = a.IDNum
            AND FirstName <> '0'
            AND LastName <> '0'
        )

SELECT *
FROM accounts

Both of these solutions produce the results you're looking for.

| IDNum | FirstName | LastName  |
|-------|-----------|-----------|
| 123   | Joe       | McDaniels |
| 124   | Mary      | Cain      |
| 125   | John      | Johnson   |
| 126   | Sue       | Bird      |
| 127   | Michael   | Bennett   |
| 128   | Larry     | Jackson   |
| 129   | 0         | 0         |
| 130   | Tanya     | Hall      |
| 131   | 0         | 0         |
| 132   | Joe       | Johnson   |
| 133   | Stephen   | Cole      |

UPDATE 8/2/18 - Excluding records instead of deleting them.

Based on your comment

What about excluding those records from the resultset? So don't delete them just return the others., see if this gives you what you want.

;with ExcludeTheseRecords as
(
select a.*
FROM accounts a
WHERE FirstName = '0'
    AND LastName = '0'
    AND EXISTS (
        SELECT *
        FROM accounts
        WHERE IDNum = a.IDNum
            AND FirstName <> '0'
            AND LastName <> '0'
        )
)

SELECT * from accounts
except
select * from ExcludeTheseRecords


Unable to delete a record in Mysql Let's say I have table name Company with two columns cname, location.

In the table I am having two records with

cname   |location

Asmetric |Dallas

Sophore |Washington

on this table when I perform a  delete operation

delete company where cname='Sophore';

I am getting an error

Error 1064:You have an error in sql syntax check the manual that corresponds to your sql server version

whereas i had expected the complete record to be deleted , is the syntax wrong or it's incomplete.Any suggestions welcome

Your error output is strange, it should be:

ERROR 1064 (42000): 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 'where cname='Sophore'' at line 1

Which tell you exactly where the error is- you forgot the FROM clause on DELETE:

DELETE FROM company WHERE cname='Sophore';


Reducing column size and data type in mysql I have a mysql table adv(id(TEXT),index(TEXT) ) which I need to change it as follows

ALTER TABLE adv
  MODIFY COLUMN id VARCHAR(80),
  MODIFY COLUMN index INT(11);

will this preserve all the data in the above columns assuming that they are less then 80 chars for id and a number for index?

Yes, it will. When there's no load on the table, I would suggest following procedure though. Not only can you double-check if everything is okay, it probably is also faster.

CREATE TABLE new_adv LIKE adv;

ALTER TABLE new_adv
  MODIFY COLUMN id VARCHAR(80),
  MODIFY COLUMN index INT(11);

INSERT INTO new_adv SELECT * FROM adv;

RENAME adv TO old_adv, new_adv TO adv;
/*when everything is okay...*/
DROP TABLE old_adv;

And by the way, it's a good idea not to use keywords like index for column names. You might want to change that, too. And if it's just for the benefit of not having to use backticks all the time when you use this column.


How can a session be blocked by an session that is idle? I'm trying to identify blocking sessions using the following query:

SELECT
  v1.sid esid,  v1.blocking_session bsid,  v1.process,  v1.wait_class,  v1.event,  v1.sql_id,  v1.seconds_in_wait waitss,  v2.blocking_session bsid2,  v2.process,
  v2.sql_id,  v2.wait_class,  v2.event
from
  v$session v1, v$session v2
where
  v1.blocking_session is not null and v1.blocking_session = v2.sid
order by
  waitss desc

Here is sample output:

ESID    BSID    PROCESS WAIT_CLASS  EVENT                           SQL_ID          WAITSS  BSID2   PROCESS_1 SQL_ID_1 WAIT_CLASS_1 EVENT_1
2707    2313    8526    Application enq: TX - row lock contention   05v90pbavkptk   11      (null)  28152     (null)   Idle         SQL*Net message from client

Which indicates that the blocking session is idle. Maybe I'm missing something but I don't understand how an idle session that is not executing any statement can be blocking anything.

It would appear the idle session has a lock on at least one record required by the blocked query.  The user has already executed a query that acquired a lock.  Normally the lock will be held until they commit or rollback the transaction.

I've run into this a few times when a developer went for lunch while in the middle of updating or inserting data.  The lock was removed shortly after they returned from lunch.

If their session times out or you kill their session, the transaction should rollback and release the lock.

If you can determine what location they are connected from you may be able to contact them and request they commit or rollback their transaction.


Changing number of rows when importing table I'm currently importing a table with about 17 million rows in MySQL. What catches my attention is, the number of row seems to be fluctuate.

I used the command show table status while my importing process was running. One time, I got 1.5 million of rows, then I ran that command again and got lower than 1.5 million. How is that possible?

The answer is innodb only estimates number of rows by command show table status. Please refer to http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html for further explanation.


SQL Profiler Audit Logout duration 18446744073709548 I'm running traces on our SQL Server 2014 box and see something disturbing. The duration column of the Audit Logout event is normally the time in microseconds a connection has been alive to the server and can get quite large due to connection pooling, but what does it mean when it's 18446744073709548?

I cant help to think this number is special. A bit like the value of maxint in .NET etc. I've googled this number and it returns various stuff. A few of them are about SQL server and the audit logout event, so I'm not the only one seeing this, but I cant find an explanation about what it means.

Anyone? (preferably someone who has participated in the development of SQL server ;))

br
Frederic

I currently see the same duration for Audit logout, among other similar values. For the same SPID, I would expect this number to increase monotonously, but that is not the case. A few subsequent values:

0
3
6
2616
0
49530
196
18446744073709548
18446744073709545
18446744073709541
18446744073709548

(duplicate values omitted)

I did not arrive here by searching for that number, so presumably, these numbers occur frequently.

If it were a duration in ms, this would correspond to a duration of over 584942 years. The only machine I have heard of running for that long is Deep Thought, but that machine is definitely not hosting my SQL Server databases.

My assumption is that somehow, the durations of individual batches are subtracted from the cumulative duration, causing an unsigned long to wrap around.
2^64 = 18446744073709551616, which is very close to 1000 times the numbers we encounter. So, internally, the duration is probably stored in µs.

So, this seems a bug, but I do not think it will learn you anything about the performance of your batches.

I am running SQL Server 12.0 and profiler v17.9.1.


Validate Primary Key and Index Selection I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this level of table design so advice would be greatly appreciated.

The table will be queried mainly on looking for a specific transaction (TrxDate, StoreNo, RegNo and TrxNo) or summarising data by TrxDate/ StoreNo.

Do my indecies look correct? Should the UX_POS_eCommTrx_TrxDate_StoreNo_RegNo_TrxNo_SeqNo index be a primary key? Is it worth adding another index just to cover looking for specific transactions (same a UX but without SeqNo)?

This is going to be a key table as the database grows and I really want to avoid having to change things round once the table has grown significantly.

CREATE TABLE sfs.tbl_POS_eCommTrx
(TrxDate int NOT NULL,
    StoreNo smallint NOT NULL,
    RegNo smallint NOT NULL,
    TrxNo smallint NOT NULL,
    SeqNo tinyint NOT NULL,
    SKN int NOT NULL,
    Pcs smallint NOT NULL,
    Retail decimal(38,4) NOT NULL,
    CorrTend numeric(1,0) NOT NULL,
    OrderID int,
    UID int NOT NULL identity(1,1),
    DateStamp datetime Default(getdate())
)

CREATE UNIQUE INDEX UX_POS_eCommTrx_TrxDate_StoreNo_RegNo_TrxNo_SeqNo
    ON sfs.tbl_POS_eCommTrx (TrxDate,StoreNo,RegNo,TrxNo,SeqNo)

CREATE INDEX IX_POS_eCommTrx_TrxDate_StoreNo
    ON sfs.tbl_POS_eCommTrx (TrxDate,StoreNo)

Thanks in advance.

I wouldn't get too hung up on estimating the correct indexes ahead of time.  Make the ones you think you'll need then watch the missing index DMV's after you get some data in the table.

In my experience, it's pretty difficult to correctly estimate the indexes that will have the biggest impact until you start actually querying the data, unless you know EXACTLY the queries you will use.  If you DO know exactly the queries you will be using, post them so we can help you.

You will also want to view the estimated and actual execution plans for any queries you do make, looking for table scans with an eye to converting them into index seeks.


Why do I have to backup a transaction log twice for it to shrink? This question was attempted twice on experts-exchange with no real answer.

I know that I should not shrink the log file - that is not the question.

If you feel like answering that one should not do it- that has been confirmed many times yes and it is not the goal of this question.

Setting to Simple backup is also not an option.

It has been bugging me for years - the fact that I do now know why it should be backed up twice.

I recently saw the first log file that was able to shrink every time on the first attempt - it was unbelievable. I asked the guy about it and all I could make out was "reorganize before releasing" - I do not know what he was referring to. I am not able to contact him again.

I know this from the existing posts on E-E:
"Shrinking removes inactive parts of the log.  In order to be inactive, the log must be truncated which occurs as part of log backup.

However, transaction log is made up of many virtual log files that are used in a round robin fashion.  Only virtual logs at the end of the file can be pruned so if SQL server is currently using the last virtual log, nothing can be removed.  The old fix in sql 7 was to run a script that ran enough dummy transactions to fill up the log and wrap the pointer around to the first virtual log (you can still use this method if you prefer).
http://support.microsoft.com/kb/256650/EN-US

DBCC now does this for you but still requires the extra step of truncating the log again so the inactive space can be deleted"

From another user:
"All I had to do was discontinue the automated optimization and integrity checks the last Admin had left on the database maintenance plans."

SQL Server's transaction log files are made up of Virtual Log Files (VLFs). When you shrink a log file, it will only release unused VLFs at the end of the file. There is always going to be at least one VLF that is being used for current activity.

Consider this simplified example. You have a Log file that is made up of 6 equally sized VLFs. For simplicity, let's say 1 VLF = 1MB, so the total log file is 6MB. You want to shrink it to 3MB. You perform a transaction log backup, which makes some VLF (we'll pick 4 at random) active, and the rest available for reuse:

1____2____3____4_XX_5____6____

Now you perform a shrink with a target size of 3MB. Shrink can only lop off the unused VLFs at the end, but cannot go all the way down to the target size of 3MB:

1____2____3____4_XX_

You can't shrink any further, because VLF 4 is in use, and that's just how things work. You need VLF 4 to be available for reuse before it can be truncated.  So you take a second transaction log backup--VLF 4 gets backed up, and VLF 1 becomes the active VLF:

1_XX_2____3____4____

Now, you can perform a second shrink, and get the transaction log down to the target size of 3MB:

1_XX_2____3____


PostgreSQL - tuning multiple clusters on server I usually use http://pgtune.leopard.in.ua/ for tuning postgresql parameters depending on hardware resources.

But now I want to tune two clusters on same server.

How should I tune PostgreSQL configuration in this case?

For the sake of simplicity let say both clusters would be under similar load.

Now with this setup:

DB Version: 10
OS Type: Linux
DB Type: Online transaction processing systems
RAM: 2 GB
Number of CPUs: 2
Data Storage: SSD

pgtune.leopard recommends this configuration:

max_connections = 300
shared_buffers = 512MB
effective_cache_size = 1536MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1747kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 2
max_parallel_workers_per_gather = 1
max_parallel_workers = 2

So do I need to "slice" parameters in half or generally just use suggested configuration for each cluster? Or maybe it is recommended to use some different strategy here?

You should probably cut max_connections by a lot.  I don't know why it would recommend such a high setting in the first place.  I'd also cut shared_buffers down to the default (128MB) or maybe lower, as PostgreSQL runs on top of the OS file cache and with two separate instances it would probably be best to let the OS decide how to use the available memory, both between instances and for other uses.

I'd leave effective_cache_size and effective_io_concurrency alone.  Those are short term issues, and postgresql makes no effort to partition them between connections even within an instance.  It is possible that two connections on separate instances want to make use of those "facilities" at the exact same time and interfere with each other, but it is also possible that two connections on the same instance would.  So the mere fact that they are being shared is not a reason to lower them.  These setting don't do much for transaction processing anyway, they are mostly important for large analytics-type queries.


SET STATISTICS IO- worktable/workfile I am executiong query, that produces plan:

Statistics IO:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 128, logical reads 5952, physical reads 576, read-ahead reads 6080, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table1'. Scan count 9, logical reads 90450, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, Some questions

1. Why Statistics IO show higher reads than Profiler?.

As to KB314648 it is OK if if Profiler reports higher numbers than Statistics IO. But Profiler shows  92283 reads at  described query, same execution. Does that mean that Profiler does not count workfile/worktable reads?

2. What is difference between "Worktable" and "Workfile"

I have found that:

work files could be used to store temporary results for hash joins and hash aggregates.
work tables could be used to store temporary results for query spool, lob variables, XML variables, and cursors.

Is there some physical difference between them?

3. Why there is "Worktable" in this particular case?

Why do i have worktable at all, if there is 0 logical reads? Its included in statistics IO just because there might be need for it (in case of bad estimates)?

Description found on technet seems vague..

4. What "Physical reads" on Workfile means?

Does that mean that there was not enough memory granted to query, so data had to be written to disk while query executes (yellow warning on Hash Match)?
Can i assume that whenever I see worktable/workfile in Statistics IO with physical reads, there has been not enough memory granted for query and some intermediate results of query had to bee written to tempdb disk?
And whenever i see only logical reads, RAM is used?

5. One "workfile" means exactly one table that is used for one purpose?

If there is more that one workfile/worktable then i cannot know to what operation it is used?

1. Why Statistics IO show higher reads than Profiler?

No idea, sorry. There are often differences because they are measuring different things as alluded to in the knowledge base article. There is no additional documentation on this that I am aware of. You might be able to infer some things through detailed testing, but there's really no guarantee whatever you find will remain consistent across versions and builds. The intended behaviour is inconsistent enough, before accounting for possible bugs.

2. What is difference between "Worktable" and "Workfile"

Both are internal objects; otherwise it is pretty much what the names suggest: A work table has a table-like structure, a work file is file-like. The detailed structure is not visible, though some broad features can be discerned by inspecting their methods and following their execution paths with a debugger.

3. Why there is "Worktable" in this particular case?

A work table is always needed for a (row mode) hashing operation. It is used internally in distributing the input into hash partitions (an overloaded term, not related to table partitioning) and to keep track of status. I have never seen a hashing work table report anything other than zero in statistics output, but then I've never really looked into it.

4. What "Physical reads" on Workfile means?

Work files are part of the mechanism employed when a hash partition spills. Though not documented, the physical and read-ahead reads occur when the execution engine retrieves spilled hash partitions from the work files.

5. One "workfile" means exactly one table that is used for one purpose?

As I recall, there may be multiple instances. There is no way I know of to relate a particular STATISTICS IO line to a particular object or plan node. This is a long-standing limitation. There may be a change to this in SQL Server 2016, though I have not tested to see if it applies to internal temporary objects.

Ultimately, there's not that much useful to be learned from STATISTICS IO output for work files and work tables beyond that which you can learn by looking at other information in a post-execution plan (or via DMVs, Extended Events etc.). I apologise for the incomplete answer, but it's the best I can offer off the top of my head.


Is my SQL Server patched? How can I tell whether my SQL Server instances are patched? Is there native functionality that will identify whether there are patches available for my server? Heck, do I even have version data available to me?

To answer in reverse order

What version am I on?

There are two different queries that I use to identify my SQL Server level.

The first is @@version. The nice thing about this is that it brings back OS level information as well. The challenge with it is that it's a big dump of text and you have to parse that to find the relevant bits.

e.g.

SELECT @@version;

/*
Microsoft SQL Server 2014 - 12.0.4416.0 (X64)
    Jun 11 2015 19:18:41
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
*/

My preference is to use ServerProperties as I can individually identify the elements.

SELECT
    SERVERPROPERTY('productversion') AS ProductVersion
,   SERVERPROPERTY ('productlevel') AS ProductLevel
,   SERVERPROPERTY ('edition') AS Edition;

/*
ProductVersion  ProductLevel    Edition
12.0.4416.0     SP1             Developer Edition (64-bit)
*/

Is there native functionality to check for patches?

Nothing that I'm aware of. If the host OS has Windows Update service running and someone has specified "Include patches for other MS products" then you might get patches installed for SQL Server.

But, if you're a professional, you're probably not interested in waiting for a patch to be listed in WSUS or you'd like to validate that the patch works for your environment. That's going to involve reading knowledge base (KB) articles and testing.

How can I tell if my SQL Server instances are patched?

Lacking anything built into the product, I have been referencing sqlserverbuilds.blogspot.com. But there are plenty of other sites out there attempting to offer the same consolidated list of patches, such as:

http://sqlserverupdates.com/
http://blogs.sqlsentry.com/category/sql-server-builds/

I have taken the data from that blog and converted it to a View, dbo.PatchLevel. That view exposes all the patches. This view is truncated to only cover 2016 and 2014, otherwise I blow the character limit for answers.

CREATE VIEW dbo.PatchLevel
AS
-- data from http://sqlserverbuilds.blogspot.com
WITH SRC(Build,[File version],[KB / Description],[Release Date], SimpleVersion) AS
(
    SELECT
        CASE LEN(D.Build) - LEN(REPLACE(D.Build, '.', ''))
            WHEN 3 THEN REPLACE(D.Build, '.00.', '.0.')
            WHEN 2 THEN REPLACE(D.Build, '.00.', '.0.') + '.0'
        END AS Build
    ,   D.FileVersion
    ,   D.KB
    ,   CAST(REPLACE(D.ReleaseDate, ' *new', '') AS date) AS ReleaseDate
    ,   CAST(LEFT(D.Build, 4) AS decimal(4,2))
    FROM
    (
        VALUES
            ('13.00.500.53','2016.130.500.53','Microsoft SQL Server 2016 Community Technology Preview 2.3 (CTP2.3)','August 28, 2015 *new')
        ,   ('13.00.407.1','2016.130.407.1','Microsoft SQL Server 2016 Community Technology Preview 2.2 (CTP2.2)','July 23, 2015')
        ,   ('13.00.400.91','2016.130.400.91','Microsoft SQL Server 2016 Community Technology Preview 2.2 (CTP2.2) [withdrawn]','July 22, 2015')
        ,   ('13.00.300.44','2016.130.300.444','Microsoft SQL Server 2016 Community Technology Preview 2.1 (CTP2.1)','June 24, 2015')
        ,   ('13.00.200.172','2016.130.200.172','Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2)','May 27, 2015')
        ,   ('12.00.4427','2014.120.4427.24','3094221 Cumulative update package 3 (CU3) for SQL Server 2014 Service Pack 1','October 21, 2015 *new')
        ,   ('12.00.4422','2014.120.4422.0','3075950 Cumulative update package 2 (CU2) for SQL Server 2014 Service Pack 1','August 17, 2015')
        ,   ('12.00.4416','2014.120.4416.0','3067839 Cumulative update package 1 (CU1) for SQL Server 2014 Service Pack 1','June 22, 2015')
        ,   ('12.00.4213','2014.120.4213.0','MS15-058: Description of the nonsecurity update for SQL Server 2014 Service Pack 1 GDR: July 14, 2015','July 14, 2015')
        ,   ('12.00.4100','2014.120.4100.1','SQL Server 2014 Service Pack 1 (SP1)','May 14, 2015')
        ,   ('12.00.4050','2014.120.4050.0','SQL Server 2014 Service Pack 1 (SP1) [withdrawn]','April 15, 2015')
        ,   ('12.00.2556','2014.120.2556.4','3094220 Cumulative update package 10 (CU10) for SQL Server 2014','October 20, 2015 *new')
        ,   ('12.00.2553','2014.120.2553.0','3075949 Cumulative update package 9 (CU9) for SQL Server 2014','August 17, 2015')
        ,   ('12.00.2548','2014.120.2548.0','MS15-058: Description of the security update for SQL Server 2014 QFE: July 14, 2015','July 14, 2015')
        ,   ('12.00.2546','2014.120.2546.0','3067836 Cumulative update package 8 (CU8) for SQL Server 2014','June 22, 2015')
        ,   ('12.00.2506','2014.120.2506.0','3063054 Update enables Premium Storage support for Data files on Azure Storage and resolves backup failures','May 19, 2015')
        ,   ('12.00.2505','2014.120.2505.0','3052167 FIX: Error 1205 when you execute parallel query that contains outer join operators in SQL Server 2014','May 19, 2015')
        ,   ('12.00.2504','2014.120.2504.0','2999809 FIX: Poor performance when a query contains table joins in SQL Server 2014','May 5, 2015')
        ,   ('12.00.2504','2014.120.2504.0','3058512 FIX: Unpivot Transformation task changes null to zero or empty strings in SSIS 2014','May 5, 2015')
        ,   ('12.00.2495','2014.120.2495.0','3046038 Cumulative update package 7 (CU7) for SQL Server 2014','April 23, 2015')
        ,   ('12.00.2488','2014.120.2488.0','3048751 FIX: Deadlock cannot be resolved automatically when you run a SELECT query that can result in a parallel batch-mode scan','April 1, 2015')
        ,   ('12.00.2485','2014.120.2485.0','3043788 An on-demand hotfix update package is available for SQL Server 2014','March 16, 2015')
        ,   ('12.00.2480','2014.120.2480.0','3031047 Cumulative update package 6 (CU6) for SQL Server 2014','February 16, 2015')
        ,   ('12.00.2474','2014.120.2474.0','3034679 FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING','May 15, 2015')
        ,   ('12.00.2472','2014.120.2472.0','3032087 FIX: Cannot show requested dialog after you connect to the latest SQL Database Update V12 (preview) with SQL Server 2014','January 28, 2015')
        ,   ('12.00.2464','2014.120.2464.0','3024815 Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014','January 5, 2015')
        ,   ('12.00.2456','2014.120.2456.0','3011055 Cumulative update package 5 (CU5) for SQL Server 2014','December 18, 2014')
        ,   ('12.00.2436','2014.120.2436.0','3014867 FIX: "Remote hardening failure" exception cannot be caught and a potential data loss when you use SQL Server 2014','November 27, 2014')
        ,   ('12.00.2430','2014.120.2430.0','2999197 Cumulative update package 4 (CU4) for SQL Server 2014','October 21, 2014')
        ,   ('12.00.2423','2014.120.2423.0','3007050 FIX: RTDATA_LIST waits when you run natively stored procedures that encounter expected failures in SQL Server 2014','October 22, 2014')
        ,   ('12.00.2405','2014.120.2405.0','2999809 FIX: Poor performance when a query contains table joins in SQL Server 2014','September 25, 2014')
        ,   ('12.00.2402','2014.120.2402.0','2984923 Cumulative update package 3 (CU3) for SQL Server 2014','August 18, 2014')
        ,   ('12.00.2381','2014.120.2381.0','2977316 MS14-044: Description of the security update for SQL Server 2014 (QFE)','August 12, 2014')
        ,   ('12.00.2370','2014.120.2370.0','2967546 Cumulative update package 2 (CU2) for SQL Server 2014','June 27, 2014')
        ,   ('12.00.2342','2014.120.2342.0','2931693 Cumulative update package 1 (CU1) for SQL Server 2014','April 21, 2014')
        ,   ('12.00.2269','2014.120.2269.0','3045324 MS15-058: Description of the security update for SQL Server 2014 GDR: July 14, 2015','July 14, 2015')
        ,   ('12.00.2254','2014.120.2254.0','2977315 MS14-044: Description of the security update for SQL Server 2014 (GDR)','August 12, 2014')
        ,   ('12.00.2000','2014.120.2000.8','SQL Server 2014 RTM','April 1, 2014')
        ,   ('12.00.1524','2014.120.1524.0','Microsoft SQL Server 2014 Community Technology Preview 2 (CTP2)','October 15, 2013')
    ) D(Build,FileVersion,KB,ReleaseDate)
)
SELECT
    SRC.Build
,   SRC.[File version]
,   SRC.[KB / Description]
,   SRC.[Release Date]
,   SRC.SimpleVersion
FROM
    SRC;
GO

I have a second view, yes nested view - bad me, dbo.MyPatchLevel which uses the version data from above to compare to my current instance and generate output to let me know just how bad it is.

CREATE VIEW dbo.MyPatchLevel
AS
WITH MostRecentBuild AS
(
    SELECT
        SRC.Build
    ,   SRC.[Release Date]
    ,   SRC.SimpleVersion
    ,   SRC.[KB / Description]
    FROM
        dbo.PatchLevel AS SRC
    WHERE
        SRC.[Release Date] =
    (
        SELECT
            MAX(SRCI.[Release Date])
        FROM
            dbo.PatchLevel AS SRCI
        WHERE
            SRCI.SimpleVersion = SRC.SimpleVersion
    )
    AND
        SRC.Build =
    (
        SELECT
            MAX(SRCI.Build)
        FROM
            dbo.PatchLevel AS SRCI
        WHERE
            SRCI.SimpleVersion = SRC.SimpleVersion
            AND SRCI.[Release Date] = SRC.[Release Date]
    )

)
, MyVersion AS
(
    SELECT
        SRC.Build
    ,   SRC.[File version]
    ,   MRB.[KB / Description]
    ,   SRC.[Release Date]
    ,   D.ProductVersion
    ,   D.ProductLevel
    ,   D.Edition
    ,   D.Version
    ,   SRC.SimpleVersion
    ,   MRB.Build AS MostRecentBuild
    ,   MRB.[Release Date] AS MostRecentReleaseDate
    FROM
        dbo.PatchLevel AS SRC
        INNER JOIN
        (
            SELECT
                SERVERPROPERTY('productversion') AS ProductVersion
            ,   SERVERPROPERTY ('productlevel') AS ProductLevel
            ,   SERVERPROPERTY ('edition') AS Edition
            ,   @@VERSION AS Version
        )D
        ON D.ProductVersion  = SRC.Build
        INNER JOIN
            MostRecentBuild AS MRB
            ON MRB.SimpleVersion = SRC.SimpleVersion
)
SELECT
    MV.ProductVersion AS CurrentVersion
,   MV.MostRecentBuild
,   MV.[Release Date]
,   MV.MostRecentReleaseDate
,   DATEDIFF(DAY, MV.[Release Date], MV.MostRecentReleaseDate) AS DaysSincePatchAvailable
,   MV.[File version]
,   MV.ProductLevel
,   MV.[KB / Description] AS [Most Recent KB / Description]
,   MV.Edition
,   MV.Version
,   MV.SimpleVersion
,   CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS sysname) AS Server
,   CAST(SERVERPROPERTY('InstanceName') AS sysname) AS Instance
,   CAST(SERVERPROPERTY('ServerName') AS sysname)  AS ServerName
FROM
    MyVersion AS MV;
GO

I'd use a query like

SELECT
    MPL.CurrentVersion
,   MPL.MostRecentBuild
,   MPL.[Release Date]
,   MPL.MostRecentReleaseDate
,   MPL.DaysSincePatchAvailable
,   MPL.[Most Recent KB / Description]
FROM
    dbo.MyPatchLevel AS MPL;

Results

CurrentVersion  MostRecentBuild  Release Date  MostRecentReleaseDate  DaysSincePatchAvailable  Most Recent KB / Description
12.0.4416.0     12.0.4422.0      2015-06-22    2015-08-17             56                       3075950 Cumulative update package 2 (CU2) for SQL Server 2014 Service Pack 1

Armed with these query results, I know whether I'm patched up to date, how long it's been since I've patched and what the KB is to go read up on the current patch state.

Scripts are also hosted on github


Active-active Cluster In MySQL I'm planning to go for a 3-node active-active MySQL cluster which will not have more 100 databases. Many experts here have already told there are issues with MySQL 5.6 and 5.7 where circular replication happens which is very vulnerable due to increase in failures, in what way Percona XtraDB provides edge to MySQL cluster? Percona doesn't do the circular way? Just trying to understand. The application which i'm going to use is not very much resource intenseful.
What is the best option for 3 node active-active cluster?

You appear to be missing the distinction between "Percona XtraDB" and "Percona XtraDB Cluster," also known as PXC.  XtraDB is Percona's compatible drop-in replacement rewrite of the InnoDB storage engine, which is included in Percona Server and MariaDB.

Percona XtraDB Cluster and MariaDB Galera Cluster both use XtraDB as their storage engine, but the important difference from standard MySQL replication is that they both use the Galera Replication Provider, which provides true synchronous replication among all the nodes.  You can also use Galera with MySQL.  Oracle may not mention that, since it wasn't their idea.

MySQL's built-in asynchronous replication can be configured for circular replication (and is included in Percona and MariaDB) but it has no mechanism for handling conflicts.  If queries make conflicting changes on different servers, your data will be inconsistent and replication will stop.  Galera resolves this by requiring all nodes to concur on each commit.  It's replication mechanism is fundamentally different than what is built in.

There are no issues with circular replication that are specific to MySQL 5.6 and 5.7 as your question implies.  These issues apply to all versions of MySQL, Percona, and MariaDB when standard replication is used in a circular configuration, if you allow writes to be done to more than one of the masters.

See http://galeracluster.com


Inserting rows into a truncated table will increase the size? Today I came across this question. Everyone knows truncating table won't reduce the actual size of DB, in my scenario I have 5 tables on a DB which almost occupied 50 GB, so if I truncate all the tables in it and then start inserting new rows across tables will it increase the size or it still the same 50 GB, what I am trying to do is reuse the same tables- I don't need the old data.

If I truncate all the tables in it and then start inserting new rows across tables will it increase the size or it still the same 50 GB?

It depends on how much free space was in the database before you started truncating, and how quickly you start inserting new rows.

As an optimization, a truncate on a large table (more than 128 extents) uses deferred drop. Within the truncate transaction, the allocation units associated with the table are simply marked for deallocation. After commit, the actual deallocation work is performed separately in batches by the system. As a result the space released by the truncate is gradually made available for reuse.

See Dropping and Rebuilding Large Objects

If you truncate all the tables in one transaction, then immediately start rapidly inserting rows to a database that had very little space remaining, the chances are quite good that the database would grow, at least for a short time until the deferred drops started to catch up.


How to prevent a record from being affected by the ORDER BY RAND statement in MYSQL I have a series of problems all which I can't put in the title but I will try to explain them as best as I can. I am building an online exam platform with PHP and MySQL.

Problem 1: Prevent a record from being randomized.
So I have a series of MCQ questions with each question having 4 options. I have these questions and answers randomized for each student so no two students have the same pattern of questions. I am using the ORDER BY RAND() command to archeive this. My problem now comes for questions like this...Example:
***Question: Which of the following is an input device?
A) Monitor
B) Speaker
C) Printer
D) None of the above.

My problem is when the system randomizes these answers the option "D" (None of the above) could change to A or B or C which makes the answer invalid since there are answers below it. So is there a workaround which I can use to prevent randomization for option D only while A, B and C are randomized?

Problem 2: Displaying n questions at a time.
For MCQ questions where the answers of one question is to be used in the calculation of the next. So let's say I have an MCQ test of questions and question 1 to 5 are related....how can I make the system display these five questions at once instead of 1 question per screen.
Again how can I let the user be the one to choosr how many questions are displayed at a time...maybe the user wants n questions displayed at the time such that the value of n is the number of questions to be displayed for each screen and a third option where all the questions are displayed at once.

You need to add some flags (or something) to the data.

"None of the above" needs a flag saying it must be last.

Two questions that must be consecutive -- Indicate on the first that a particular question must immediately follow it.

Once you get "business logic" like this, you will probably find that the handling of the flags and the randomization may as well be done in client software, not SQL.

A database is a repository for information.  It is not intended as a programming engine.  There are simple programming things built into the database (ORDER BY date, ORDER BY rand, filter out stuff; etc), but I suggest that your requirements have crossed the boundary into "gotta do it in a real programming language".