Connection To R2 Named Instance Will preface this by saying I am not at all convinced yet this is not the vendor doing something wrong on their application server.  Just looking for new ideas

Instance X is a named instance of SQL Server 2008 R2.  It houses numerous databases and takes in connections from numerous application servers.  Browser service is running and all connections are made by way of server\instance name.

Moved DB from an old 2K5 instance over to it the other day and vendor claims he cannot connect to the DB.  He says he is just putting in the Server\instance name in his connection string and his connection fails. At this time he cannot/has not provided me with any real error message.

Whenever he tries, we see no evidence of success or failure.  As of right now there is no evidence he is even hitting the named instance.  He can hit a test 2008 R2 default instance though.  So, since the named instance appears to be the difference, I am thinking this has something to do with the port number.  We have not explicitly tried the port number because we need it to work with just the name and vendor swears it should.  However, we can connect with his credentials, via SQLCMD and by setting up a basic ODBC.

Application server is 2003R2 and in my opinion, craped up, but replacing it is not an option.  DBNETLIB.DLL version is 2000.86.3959.0 and native client version is 2005.90.3042.  You would have to go quite a ways back to not be able to hit a named instance.

Am I missing anything overly obvious?  From a customer service perspective would prefer to not just say “Works for everyone else!”.

EDIT 9:44PM Central - Should have noted on original post that the firewall is not on on the application server and we do not filter/block anything internal to the network.

This can be a client error, not a server error. That is, the server responds (as you note) to other clients but not a certain one.
Summary

remove all client aliases
ensure tcp is default and top protocol
ensure servername\instancename only is used

Background
It is possible that there is a "client alias". The client alias will basically "hijack" servername\instancename and overrides port, protocol, instance, server etc. Personally I don't like them.
This can be done explicitly with the "SQL Server Configuration Manager" or by the Control panel ODBC thing. Check and remove it. Alternatively, you can poke around in the registry and remove it under:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

A second option is that port is specified. If I use servername\instancename,1234 then this is effectively servername,1234. Instancename and resolution of instancename via SQL browser are not performed.
A third option is that the default protocol is named pipes not tcp. This can happen if someone has messed around with "SQL Server Configuration Manager" and is liable to show on server migrations when you drop np or such from the new server. However, you also fix this in the registry too.
There are more exotic option such as hard coded HOSTS file or wrong DNS. But generally is the a fault of the SQL Server client configuration.


Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace I have a MySQL 5.5 server running on CentOS 6. The MySQL package is a MySQL-released version (MySQL-server-5.5.28-1.el6.x86_64), not the stock CentOS one. I have moved an existing database over from a single tablespace to innodb_file_per_table. This leaves me with a 150GB ibdata1 file.

The active space isn't a big deal as the rest of the database is quite large. However, I'd like to reduce the size of it for backups. My thinking is that if I can fill the empty space in it with mostly zeros, nulls, or anything else that'll compress well, that that'll cut a lot of space out of the backups.

I've searched around for ways to fill it with zeros and for a related question, filling with random data.

The obvious solution if there isn't a built-in solution, is to use a new table with a large column and insert zeros into it until it's around size of ibdata1. However, I've been unable to find a way to change a table's tablespace when innodb_file_per_table=1.

There is absolutely no way to reduce the size of ibdata1 in-place. As for the already present data pages and index pages in ibdata1 from obsolete tables, you could let mysql overwrite them with transactions over time.

RECOMMENDATION #1

You must migrate all data out and reload with an empty ibdata1.

I have already addressed this in StackOverflow back in Oct 29, 2010 (Steps Outlined).

I have also addressed this in other posts in the DBA StackExchange

Jul 19, 2012 : whether a big (but not used) ibdata1 slow anything down
Aug 29, 2012 : Removing/overwriting sensitive data located in already deleted records in ibdata files
Apr 19, 2012 : MySql - Clean ibdata1 (Steps Outlined)

If you cannot do this in Production at this time, you will have to present this as a case study for the need to do so. Here is what you do:

Setup a Staging DB Server

No initial data
innodb_file_per_table configured

Take mysqldump of Production (use --single-transaction)
Load the mysqldump into Staging

Please note that ibdata1 will not grow dramatically because data and index pages for each InnoDB table will reside in its own personal tablespace file.

For example, the table mydb.mytable will reside in ibdata1. All you will have is /var/lib/mysql/mydb/mytable.frm as an external manifestation of the table.

Once you load the Staging DB Server, the table will look like this on disk:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

Only data dictionary elements of the table are left behind in ibdata1.

That will give you a look at what production will look like when you perform the InnoDB Cleanup.

RECOMMENDATION #2

Once you have performed InnoDB CleanUp in Production, you may be concerned with old data trapped within each individual .ibd file. Cleaning up an .ibd file is much easier.

To cleanup the InnoDB table mydb.mytable, run one of the following:

ALTER TABLE mydb.mytable ENGINE=InnoDB;
OPTIMIZE TABLE mydb.mytable;

That's it. The only difference is that OPTIMIZE TABLE may try to run ANALYZE TABLE under the hood. It may get bypassed for an InnoDB table.

RECOMMENDATION #3

If the goal is to truly stomp over all unused pages in ibdata1, here is something you may want to try out on a Staging Server:

STEP01) Extract all InnoDB tables from ibdata1. (Since you have already done so, I will write this up for the benefit of others):

SQLTORUN="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQLTORUN="${SQLTORUN} FROM information_schema.tables WHERE engine='InnoDB'"
mysql -uroot -p...  -ANe"${SQLTORUN}" > ExtractInnoDB.sql
mysql -uroot -A < ExtractInnoDB.sql

STEP02) Create a large temp table in MyISAM called test.junkman:

CREATE TABLE test.junkman (a INT) ENGINE=MyISAM;
INSERT INTO test.junkman VALUES (0);
INSERT INTO test.junkman VALUES SELECT * FROM test.junkman; ## run this line 30 times

When done, you will generate a 7 GB MyISAM table with no indexes:

mysql> SELECT data_length/power(1024,3) TableSize
    -> FROM information_schema.tables
    -> WHERE table_schema='test'
    -> AND table_name='junkman';
+-----------+
| TableSize |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)

mysql>

This should take about 10-15 minutes to create.

STEP03) Disable innodb_file_per_table

Goto /etc/my.cnf and comment it out

[mysqld]
#innodb_file_per_table

then service mysql restart

STEP04) Create InnoDB table test.junkman2

CREATE TABLE test.junkman2 LIKE test.junkman;
ALTER TABLE test.junkman2 ENGINE=InnoDB;

This will create test.junkman2 inside ibdata1

STEP05) In another SSH session run this watch

watch ls -l /var/lib/mysql/ibdata1

STEP06) Load test.junkman INTO test.junkman2

INSERT INTO test.junkman2 SELECT * FROM test.junkman;

STEP07) Repeat STEP06 until the size of ibdata1 actually changes in the other SSH session

STEP08) Uncomment innodb_file_per_table in /etc/my.cnf to re-enable it

[mysqld]
innodb_file_per_table

STEP09) service mysql restart

STEP10) TRUNCATE TABLE test.junkman2; DROP TABLE test.junkman2;

STEP11) TRUNCATE TABLE test.junkman; DROP TABLE test.junkman;

I've never tried this before (this was just a working theory although the 7GB MyISAM stunt was real). Go give it a try and let me what happens.


Postgres table size doubles are transaction completes I have a table with 50 million rows and 40+ text columns. Size of the table is around 17GB. Inside a transaction, I add another integer column to the table and update all the rows with corresponding values. After the transaction is done, the size of the table doubles to about ~35GB.

VACUUM FULL ANALYZE brings the size of the table back to 17GB, but it takes about 10   hours to complete!
Here are the specs:

24GB RAM, 16 cores, 2.4GHz

postgresql.conf settings -

shared_buffers = 3GB
effective_cache_size = 15GB

All other settings are default.

I see nothing unexpected there except that the VACUUM FULL ANALYZE took that long if you version is 9.0 or higher.

If you want to avoid the extreme bloat when updating every row in a table, update in smaller chunks and run VACUUM (not FULL) between the updates.
If you want to understand why this behaves this way, read up on the MVCC implementation in PostgreSQL.
If you want to eliminate bloat faster in pre-9.0 databases, use CLUSTER.


Explanation for important reserved space I am analysing a database

I didn't understand why reserved space is more important then used space ?

Any explication ? can I get 400 GO ?

Space Reserved is the size you setup using either:

SSMS = Database Properties / Files Database file / Initial Size (MB)
Alter Database [XXX] MODIFY FILE ( NAME = N'XXX', SIZE = ...KB)
Autogrowth changes it as well

You can check these setting using SSMS or this query:

SELECT file_id, name, type_desc, physical_name, size = size/128, max_size = max_size/128
FROM sys.database_files ;

Space Used is the size of your existing data pages in your data file. You can use this query:

SELECT name, groupid, filename,
   Cast(size/128.0 AS DECIMAL(10,2)) AS [Allocated MB],
   Cast(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Used MB],
   Cast([maxsize]/128.0 AS DECIMAL(10,2)) AS [Max Size MB],
   Cast([maxsize]/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space in MB]
FROM sysfiles
ORDER BY groupid DESC


Get Data to connect to the AdventureWorksDW I am trying to connect to a SQL Server 2017 via Visual Studio 2017.What should I be entering in the User name and Password fields?

In SQL Server, databases don't have passwords.  SQL Server uses logins, via either Windows Authentication or SQL Server Authentication.

Try using the same login you use to sign on to your workstation.


Database design for excel-like data The task

I need to store the some values in sequence in the database. The data looks just like a sequence of numbers. Here is an example of the data:

            Val_1   Val_2   Val_3   ...     Val_145
Record 1        6       8       4                 5
Record 2        2       5       6                 3
etc.

Now the thing is that there are a couple of actions that the user has to be able to do:

Insert columns (== e.g. the user needs to enter additional data in between Val_1 and Val_2 columns and inserts 5 columns there). The column headers are sequential though (val_2 always follows val_1, val_3 always follows val_2) thus only the values get shifted to the right - just like in Excel when you insert columns.
Delete column - same as with insert columns - column header always stay the same and values get deleted and values from the right get shifted to the left - again just like in excel.
View random chunks of values. E.g. view values from index 600 to index 1000

The three solutions

I came up with three solutions that can store those values, but every solution has some flaws. Here they are:

Store the index of value in the Value

Tables:

table Record
    id int
table Value
    record_id int
    index int
    value int

Pros: easy to find the needed values by using the index field (where index > 662 and index < 987).
Cons: inserting and deleting value are horrible. If there are 1000 values for a record then inserting one value at index 500 will require 1 insert and 500 updates (to shift values after index 500). Same problem with deleting values.

Store references to previous value and the next value

Tables:

table Record
    id int
table Value
    record_id int
    value int
    next_value_id int
    prev_value_id int

Pros: easy to insert and delete values - inserting a value requires 1 insert and 2 updates (updating refs on adjacent values). We can even remove the prev_value_id to make it even better.
Cons: finding values by index will be horrible. Finding range of values from 500 to 1000 will require us to go though all the values starting at value 1.

Store values as e.g. string in the record table

Tables:

table Record
    id int
    values_str text

Pros: this is just like parsing a text file. Most work is done in code. Feels like a simple and straightforward way to go.
Cons: does not feel right. This may look and feel good, but something about this design smells bad. I have a bad feeling about this.

The question

By know you probably already guessed the question - which way should I go? Is there a more sophisticated way of storing such data? Does any of the solutions I've thought of make any sense?

Here is one approach:

CREATE TABLE columns (
    id       INTEGER,
    ordinal  INTEGER);

CREATE TABLE rows (
    id       INTEGER,
    ordinal  INTEGER);

CREATE TABLE cells (
    rowid     INTEGER,
    columnid  INTEGER,
    value     TEXT);

This way, you will still need to +1/-1 the ordinals behind the new/deleted position, but fortunately you can do it all with a single statement, UPDATE rows SET ordinal = ordinal + 1 WHERE ordinal > 42. Although it updates many rows, the update statement should execute in less than a second.

Benefits:

Updating a rows table with N rows is lighter than updating a cells table with N*M rows.
Explicitly storing the ordinal means fast random access to an ordered subset of the data.


Trying to trace error in Stored Procedure using XEvents Here is the situation:

I have a SQL server ETL application with a stored procedure that is inserting/updating some data into a table.  The table has 1 INT column (it is an identity column and there is no inserting of values into that column by the stored procedure) and the rest of the columns are VARCHAR's.  In the catch block, the code is logging the stored procedure and Error_Message() to a logging table.

The error message that is being logged is:
The conversion of the VARCHAR value '010234567898789' overflowed an int column.

So, I am attempting to find out how to use XEvents to see the exact INSERT/UPDATE statement that the error is really occurring in, but I cannot seem to find the Event that I need to look at to see this.

Can anyone please either point me to a good tutorial or tell me what event(s) that I need to look at to see the INSERT/UPDATE SQL code and the associated error Event?  I know it can be done using Profiler, but I was hoping to be able to use XEvents since it is supposed to be much lighter and this is a production server were the issue is occurring.

Thank you.

You can configure an extended event session with an event:

error_reported: This event gets fired every time that an error happens in the server.

For your particular case you are able to filter events for message_id = 248.

SELECT text FROM sys.messages WHERE message_id = 248

  The conversion of the %ls value '%.*ls' overflowed an int column.

That event contains a field tsql_frame which looks like one below (just an example):

<frame level="2" handle="0x03000500A711127995BB6600B4AA000001000000000000000000000000000000000000000000000000000000" line="83" offsetStart="2634" offsetEnd="3288" />

So, you got sql_handle plus start and end offsets of the statement you are looking for. And getting the statement (values are from the example above):

SELECT
    CASE
        WHEN s.offsetStart > 0 THEN
            CASE s.offsetEnd
               WHEN -1 THEN
                  SUBSTRING(t.TEXT, (s.offsetStart/2) + 1, 2147483647)
               ELSE
                  SUBSTRING(t.TEXT, (s.offsetStart/2) + 1, (s.offsetEnd - s.offsetStart)/2+1)
            END
        ELSE
            CASE s.offsetEnd
               WHEN -1 THEN
                  RTRIM(LTRIM(t.[text]))
               ELSE
                  LEFT(t.TEXT, (s.offsetEnd/2) +1)
            END
        END AS [executing statement]
FROM sys.dm_exec_sql_text(0x03000500A711127995BB6600B4AA000001000000000000000000000000000000000000000000000000000000) t
CROSS JOIN (VALUES (2634, 3288) ) s(offsetStart, offsetEnd)


Why is my Azure SQL Server so slow? We currently have a VM that is very underpowered and are proposing to move to an Azure VM with better specs.  The problem is, the Azure VM is much slower than the original VM even though it is of a higher spec.

The original server is a 2 core VM with 2GB of memory that is also a web server.  It's running Microsoft SQL Server Web Edition 2008 R2 and because this server is used for other things, we have had to limit the maximum server memory in SQL Server to 512MB.

The new server is a 4 core VM with 7GB of memory that is only a database server.  It's running Microsoft SQL Server Standard Edition 2008 R2 and we haven't limited the amount of memory that SQL Server can use.

This is one of two servers set up in a mirrored environment but the database that I am running tests on is not mirrored.  The other databases on this server aren't getting much traffic at the moment (in fact, Activity Monitor shows no activity on the other DBs whilst I was running these tests).

I do realise that an issue with Azure VMs is that the hard drives are a network resource so that would be the source of slow down but it is still slower even when there are 0 physical reads shown in the IO statistics.

I have followed the tuning advice on this page on the Azure VM including striping the disks (two disks per drive) and putting the log and data files on separate drives.

The only things that I haven't done are enable page compression, limit auto-grow on the database and move the SQL server error log and trace file directories to data disks.  I haven't done that on the older server either.

The old server has none of this tuning done and the log and data files are on the same drive which is not striped.

The database on the current server is 65 GB (45 data and 20 log) which was a bit too large to transfer to the new server so I'm testing on a smaller DB (6 data and 13.5 log)

The results on the old server are CPU time = 1311 ms,  elapsed time = 1057 ms. and on the new server are CPU time = 1281 ms,  elapsed time = 2525 ms.  That's just one run but the results are representative of what I am normally seeing.

The new server seems to constantly have an elapsed time significantly longer than the CPU time.  Is that an issue and is there something that I can do to track down what is causing it?

What other steps can I take to find out why this server is going so slowly when it looks like it should be faster than the old server?

For what is worth, I ended changing the VM in Azure from type A to type D and then attaching another disk and moving the TEMPDB to that disk. So my final VM now is a D2 Standard with 7 GB RAM and three data disks, one for the MDF files, another one for LDF files and the new TEMPDB disk.

I gave up trying to understand with the A3 some things you mentioned and simply upgrade the virtual machine. I even went from A2 to A3 and even though I did find some improvements, I ended up changing to a D2 VM.

In the document that you stated, Microsoft recommends a D3 for Enterprise Edition or D2 for Web or Standard Edition, and use of Premium Storage, among other things in the check list at the beginning of the document.


How to select a subset of data from a large ER model? I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from all tables so that I don't break referential integrity and get orphanded records from the database? I mean that if I choose 100 records e.g. From the "product" table, how can I easily get all the data from the referenced tables (and from the tables these tables are referenced)? Is there any tool availble for this purpose or just pure SQL?

Unfortunately there isn't a cookie-cutter way to do this.  You have to start with some base records, write queries that join against them and then repeat.  As you go deeper the queries get more complex as they have to go right back to the root.

If you have the foreign keys then you can automate the generation of the queries, as the joins will just be through the foreign key columns.  It's not as hard as it sounds, but here are a few pointers if you take this approach:

The queries are only going to retrieve the contents of the table at their leaf, so they are fairly straightforward to generate.
Get the data out in a form that will load through bcp.  As a bonus, you can generate bcp control files from the system metadata.
You can probably quite safely just copy all of the reference data, which probably accounts for the majority of your tables anyway.
You may have to start from multiple roots, and you may have to manually add join metadata if you have missing foreign keys.  If your relationships to your leaf tables are not purely hierarchical then you may have to manually edit the metadata for the generator.  In practice this means you probably want to start from the system data dictionary and then make up a configuration file for your generator that works around this.
You may also need to add custom queries in for certain data items if the selection criteria involve more than one path to the record.
Generate or write a script that uses bcp to load the tables to the destination in the right order.

Automation will probably get you 95% of the way with some manual intervention and the scripts aren't all that hard to write.  It's not a 5 minute job, but it's certainly possible.

You could programatically generate SSIS packages to do the load, but I think that generating bcp control files is probably easier.  Another option would be to just structure the scripts so they copy the data from the queries into another shadow database.  This can then be backed-up/restored to wherever you want.

It's not a trivial undertaking but it's certainly not beyond the wit of man.  As a bonus, if you can configure your script so the starting keys can be parameterised, then you can make a generalised utility to copy subsets of your application database.  This will be quite useful for rolling out test environments.


"SQL_NO_CACHE" for innodb buffer pool I'm guessing the answer is no, but I was wondering if there was anyway to tell innodb to not store fetched pages in the buffer pool?

The reason for looking at doing this is check summing tables.  I'd like to minimize the effects of trashing the useful cached data.

Months later I stumbled across the effective solution to what I was seeking in my question.

From the docs itself there are two variables to help prevent the trashing of the buffer pool for large table scans such that would occur during a checksum or mysqldump.

The first is innodb_old_blocks_pct (default 37%).  This the sublist of pages that are eligible for eviction when a new page is inserted.  When a new page is first read it is added to the head of this list.

innodb_old_blocks_time (default 0) defines the number of milliseconds after being added must elapse before an access that cause it to move out of the old list to the head of the new list.  Since the default is 0 the first access puts it to the head of the new list.

So in short set innodb_old_blocks_time to a nonzero value, say 500, and then a table scan will basically just cause the old sublist to continually get cycled through.  Pages getting read for other purposes will remain in the new list allowing your DB to remain warmed with real application relevant data!


Why do 59% of my Execution Plans have CONVERT_IMPLICIT warnings? Yes, you read correctly, 59%.  Some of these are undoubtedly programmer error where they are comparing an INTEGER with a VARCHAR value, but of the ones I've surveyed (out of the 5139 execution plans with the problem) the only conversion has been a varchar to varchar comparison, like this ...

ScalarString="CONVERT_IMPLICIT(varchar(250),[CAMMS].[Entity].[t_PersonName].[LastName] as [pn].[LastName]+', '+[CAMMS].[Entity].[t_PersonName].[FirstName] as [pn].[FirstName]+' '+[CAMMS].[Entity].[t_PersonName].[MiddleName] as [pn].[MiddleName],0)"

This part of the stored proc is concatenating name parts.  The varchar(250) reference above appears to come from the fact that the Full-Name value is being inserted into a table with a column of type varchar(250)

CREATE TABLE #TmpMatter (
    ObjectType      varchar(50),
    ObjectID        int,
    MatterEntityID int,
    CaseNumber      varchar(100) null,
    Title           varchar(50),
    DefendantName   varchar(250) null ...

I checked COLLATION on both my database and on TempDB ... they are the same.  Checked COLLATION on all text columns and that is correct.  I have 12 columns whose ANSI PADDING is OFF but these CONVERT_IMPLICIT errors are showing up only on columns with it ON, so I'm not seeing any mismatch there.

One post said they fixed this problem by addressing the ANSI PADDING issue, but that can't be it ... unless my temp tables are being created with PADDING = OFF.  But TempDB (and all system dbs) have PADDING = ON.

I'm stuck on this one.  Anyone have any ideas?

Not every implicit conversion is a problem that needs to be fixed. Is this contributing to any actual performance issue? How do you know this?

The reason an implicit conversion comes into play here is not because of data type or collation but rather because of precision. SQL Server will come up with a guess that this expression:

LastName + ', ' + FirstName + ' ' + MiddleInitial

Will need to be:

varchar
(
  datalength(LastName)
  + 2
  + datalength(FirstName)
  + 1
  + datalength(MiddleInitial)
)

You can prove this by selecting a row from Person.Person in AdventureWorks into a new #temp table using the same expression:

Output (note that in AdventureWorks these are nvarchar, so every character counts as two. So adding up ', ' and ' ' adds 6 characters to the string:

Your conversion is happening the other way around - your expression, which is:

varchar
(
   50
   + 2
   + 50
   + 1
   + 100
)
= 203

The implicit conversion (which is totally unnecessary and effectively does nothing) is making your expression (203 characters) conform to the column (250 characters). Again, this has nothing to do with type. Not all conversions involve varchar/nvarchar.

If you want to get rid of the warning, you can wrap the expression in an explicit convert(varchar(250), or add a computed column in the source table that does the same, but that won't really change anything about how the code works or how fast it executes.

Finally, you should probably consider using nvarchar for proper names. You may not think you need it today, but you probably will at some point. Conversion is typically more painful later.


What are safe characters to use in SQL Server Full Text Search? I've been playing with full text search on SQL Server 2012. I must say my attempts to target special characters in search queries were futile - SS does not understand them. What's more, sometimes the presence of special characters breaks the query entirely and SS reports an error. I haven't been able to understand when and how and what characters are okay and when they break things. I haven't found any way to target special characters in search short of escaping/replacing them with some other characters. I've also stumbled onto this article http://searchsqlserver.techtarget.com/tip/Using-full-text-search-for-symbols-in-SQL-Server which implies some special characters work like they do in regular expressions. I haven't been able to confirm this hypothesis in practice - escaping as suggested in the article does not work and those characters do not function as regex commands either.

Can anybody point me to a concise [working] explanation of how to deal with FTS and special characters? I haven't found anything useful on the web. I also have read through questions on SO, they were equally unhelpful.

It is difficult to answer the question specifically without a good description of what you are trying to achieve and why, but it appears that Full-Text Search may not be a good fit for you based on your need to search for characters that are either reserved for a special purpose within the full-text engine, treated as delimiters by word-breakers, or present as a stopword in the default system stoplist.

The Full-Text Search feature is primarily aimed at performing linguistic searches on words and phrases using the rules of a particular language like English, German, or French, for example. Searching for programming keywords like $partition in T-SQL will probably not work as you would hope because $partition is not a word or phrase in any of the available 50 (spoken) languages. If there were a T-SQL language option, things would be different.

It might be possible use a custom dictionary of 'new words' to meet your requirements, but this is not something I have directly experimented with myself. I think that's about as helpful an answer as I can offer without knowing more about your needs.


Slow Query Postgres 9.2 I've got the following query:

SELECT
            j.id,
             concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client,
            c.email AS client_email,
            concat(bc.company,' ', bc.name_first, ' ', bc.name_last) AS bclient,
            j.title,
            j.refnum,
            j.job_number,
            j.time_job,
            j.time_arrival,
            j.address,
            j.suburb,
            j.city,
            j.stpr,
            j.postcode,
            j.priority,
            j.status_label_id,
            j.description,
            u.name_first AS staff_first,
            u.name_last AS staff_last,
            fl.serialised_data,
            fl.gtime,
            j.created_date
        FROM
            public.ja_status AS s
        JOIN
            public.ja_jobs AS j
        ON
            j.status_label_id = s.id
            AND NOT j.deleted
            AND j.templated = false
             AND ((((j.time_job <= 1461240000) AND (j.time_arrival >= 1460462400))))
        JOIN
            public.ja_customers AS c
        ON
            c.id = j.customerid
        LEFT JOIN
            public.ja_customers AS bc
        ON
            bc.id = j.bill_customer
        JOIN
            public.ja_feedlog AS fl
        ON
            fl.jobid = j.id
            AND fl.clientid = 19233
            AND fl.log_type IN (5,101,6,102)
            AND (
                fl.log_type IN (5,101)
                OR (
                    fl.description LIKE '%status_change%'
                    AND fl.log_type IN (6,102)
                )
            )

        LEFT JOIN
            public.ja_mobiusers AS u
        ON
            u.id = fl.requestorid
        ORDER BY
            j.id, gtime
        LIMIT
            50000

Explain analyze: http://explain.depesz.com/s/25Wj

How can I improve it? The total time is absolutely huge!

It seems the major problem is on ja_feedlog table:

                                  ->  Bitmap Heap Scan on "ja_feedlog" "fl"  (cost=230474.49..397722.58 rows=33256 width=952)

  (actual time=307.690..290532.037 rows=323605 loops=1)

Table ja_feedlog:

CREATE TABLE public.ja_feedlog
(
  id integer NOT NULL DEFAULT "nextval"('"ja_feedlog_id_seq"'::"regclass"),
  clientid bigint,
  mobiuserid bigint,
  customerid bigint,
  invoiceid bigint,
  description character varying(1024),
  gtime bigint,
  jobid bigint,
  log_type smallint,
  serialised_data "text",
  push_status smallint DEFAULT 0,
  requestorid bigint,
  the_geom "geometry",
  admin_read smallint NOT NULL DEFAULT 0,
  visitid bigint,
  CONSTRAINT pk_feedlog PRIMARY KEY ("id")
)

It looks like the join between ja_feedlog and ja_jobs is the culprit (it appears to be taking most of the time doing a filtered indexed scan on ja_jobs for each ja_feedlog resulting row). In cases like this where there are two joined heavily filtered large tables, I find it useful identifying which set of filter conditions will render the least rows on one of the tables, and separate that/those one/s into an indexed temporary table(s) for joining later.

I have assumed that the table ja_jobs, with those filter conditions, will produce a reasonably small rowset in a reasonably small time. If that is the case, you may want to try the following:

CREATE TEMP TABLE myjobs AS
  SELECT *
  FROM public.ja_jobs
  WHERE NOT j.deleted AND NOT j.templated AND ((((j.time_job <= 1461240000) AND (j.time_arrival >= 1460462400))));

CREATE UNIQUE INDEX ON myjobs (id);

SELECT      j.id,
             concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client,
            c.email AS client_email,
            concat(bc.company,' ', bc.name_first, ' ', bc.name_last) AS bclient,
            j.title,
            j.refnum,
            j.job_number,
            j.time_job,
            j.time_arrival,
            j.address,
            j.suburb,
            j.city,
            j.stpr,
            j.postcode,
            j.priority,
            j.status_label_id,
            j.description,
            u.name_first AS staff_first,
            u.name_last AS staff_last,
            fl.serialised_data,
            fl.gtime,
            j.created_date

FROM public.ja_feedlog AS fl
JOIN myjobs AS j ON (j.id = fl.jobid)
FROM public.ja_status AS s ON (j.status_label_id = s.id)
JOIN public.ja_customers AS c ON (c.id = j.customerid)
LEFT JOIN public.ja_customers AS bc ON (bc.id = j.bill_customer)
LEFT JOIN public.ja_mobiusers AS u ON (u.id = fl.requestorid)

WHERE fl.clientid = 19233 AND fl.log_type IN (5,101,6,102) AND (fl.log_type IN (5,101) OR (fl.description LIKE '%status_change%' AND fl.log_type IN (6,102)))

ORDER BY j.id, gtime
LIMIT 50000

Also, for inner joins, it is clearer if filter conditions that are not strictly part of the joining condition are specified in the where clause rather than the join, for clarity and for making debugging easier. Finally, even if it doesn't affect the final plan, for readability and clarity I place in the FROM whatever table it will be the "carrier" table, i.e. the one that will take the biggest filtering effort, and "hang" from it all the dependent ones, like small master tables, since I find it easier commenting these out for debugging.


Does the sql server compare strings by hash values? If two columns are in the same collation, it would make sense for big strings just to use a hash value in order to compare them and only if they are equal to compare them characterwise. Does the sql server proceed this way?
My guess would be rather not, because that would mean storing hash values that are usable for comparison within the same collation and that would use additional space. However Microsoft states

For single-byte encoding character sets such as Latin, the storage size is n bytes + 2 bytes

which negates the storage of any hash values.

If the query optimizer decides that the execution plan should use a hash join this is exactly what it does. (It can be used in other places too - such as aggregation).
re the quote that you give - I think this relates to a varchar(n) column, so the storage is the number of characters you want to store in bytes plus a 2 byte overhead to manage the variable length.
It´s also quite common to add some sort of hashing or checksum programmatically, for example to represent a long strings checksum using an integer as a computed column, then add an index and search for a checksum match (plus a test to eliminate any false matches caused by a collision).


Copy whole database from SQL Server to SQL Server using linked server I am using SQL Server 2014 Management Studio installed in my system. And using linked server I have connected to a linked server.

Now here I need to copy whole database from linked server to my local SQL Server using linked server itself.

I am using a linked server instead of backup/restore because using linked server I can copy remote database.

Tool used: Microsoft SQL Server 2014 Management Studio.

Backup/restore, the Copy Database Wizard, and Log Shipping are all potentially better choices than linked server copies.

See the following documentation:

Back Up and Restore of SQL Server Databases
Use the Copy Database Wizard
About Log Shipping (SQL Server)


Foreign Keys with ON DELETE SET NULL are still deleted when TRUNCATE CASCADE is called on the foreign table in Postgres Maybe I am missing something here:

CREATE TABLE public.example_table (
    id integer UNIQUE
);

CREATE TABLE public.foreign_table (
    id integer,
    example_table_id integer,
    CONSTRAINT fk_example_table_id
    FOREIGN KEY (example_table_id)
    REFERENCES public.example_table (id)
    ON DELETE SET NULL
);

INSERT INTO public.example_table (id) VALUES
    (1);

INSERT INTO public.foreign_table (id, example_table_id) VALUES
    (1, 1),
    (2, null);

If I run TRUNCATE CASCADE, both tables are wiped which is not what I expected would happen.

TRUNCATE example_table CASCADE;

SELECT COUNT(*) FROM public.foreign_table;

0

What I would expect to happen would be that foreign_table would alter to:

(1, null)
(2, null)

Am I not understanding what SET NULL is supposed to accomplish?

Is there a way to use TRUNCATE CASCADE without having it delete from the other table? I use Laravel where I can call Model::truncate(); and it will automatically truncate the table and reset my indexes, I was hoping I could call this on the example_table and have it reset all the rows in foreign_table to null instead of just deleting the whole table.

Thanks for your help.

If I understand the documentation properly:

https://www.postgresql.org/docs/current/sql-truncate.html

TRUNCATE CASCADE truncates every table that has a foreign key relationship to the table in common, regardless of what action is specified for the foreign key. Example:

create table parent
( x int not null primary key);

create table child
( y int not null primary key
, x int not null references parent(x)
                 on delete restrict
                 on update restrict
);

insert into parent (x) values (1),(2),(3);
insert into child (y,x) values (1,1),(3,2);

truncate parent cascade;

select * from child;

There are no results to be displayed.

Is there something in particular that prevents you from:

delete from parent;

?


which option is better for not slow down the performance of queries? I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the question is which option is best:

A. Record each payment made, this means customers have multiple records.

B. Have only one record per customer and an attribute in the database where there concatenate previous payments, so it's a string that has previous payments.

Note: The DB will have many customers that's why I'm worried about performance with this situation, especially when viewing the information on the DB.

I had never done such a system and therefore would not know how much will be affected the performance of the DB having one of these two configurations and a large number of records.

if there is a better solution is welcome

I would use a system where each payment is recorded separately

Typically, you'd want to keep the "balance" consistent over time and this requires them to be recorded individually.

the ability to link payments
record cancellations separately

I would never concatenate previous payments into one opaque string.

I also wouldn't worry about performance yet: this is premature optimisation. If you do expect 100s of millions of rows, there are other techniques to improved performance. Otherwise, concentrate on data integrity and correctness


Separate differential backup from one restored baseline I work on a team that customises software, part of this customisation is stored as records in a database, part is customisations to the schema itself and there are often some specific records in the database too.

I'd like to create a baseline version of the database and back it up normally. Then for each customer I restore the baseline database under the customer's name and customise it as usual. I'd then like to do a differential backup of the database from the baseline point to the current point and shelve the differential backup for that customer, I expect about 0.1-0.2% changed data. When I need to do more work for that customer I restore the baseline backup and their differential backup, do the work then backup again, replacing the shelved baseline backup.

This is similar to Differential databases (not backups) but using backups.

Can I have different differential backups that can be restored against a single baseline?

Can I do a new differential backup from the same starting point or would I need to do incremental backups.

Any problems or experiences with this sort of approach?

The question is a little ambiguous, but either of the two ways I see to interpret it would work:
Starting point: A differential backup captures all changes from the last full backup (the 'differential base').
Both scenarios below start by creating the baseline database and taking a full (not copy only) backup of it.
Scenario 1 - Continuing differentials

Restore the baseline
Restore the latest differential for customer X (if any)
Make changes for customer X
Perform a new differential backup for customer X

You need to keep the full baseline and the latest differential for each customer in this scenario.
Scenario 2 - Separate full backups

Restore the latest full backup for customer X (or the baseline if a new customer)
Make changes for customer X
Perform a new full backup for customer X

You need to keep the baseline (for new customers) and the latest full backup for each customer in this scenario.

In scenario 1, the size of the differentials will grow larger over time, but never larger than the size of the current database for each customer.
Restoring for each customer requires two steps: baseline, then customer-specific single latest differential. Previous differentials can be discarded if necessary.
Scenario 2 will require more storage space than scenario 1, as each customer gets a full copy of their current database.
Restoring for each customer is a simple one-step process. Previous per-customer full backups may be discarded.

As usr mentioned in a comment, one drawback to either set up is it makes it more difficult to apply a change to all customers.
As always, a known good current database validated by DBCC CHECKDB with the appropriate options is needed, and the only valid backup is one you have tested to restore successfully (and pass the checkdb tests). It may be a good idea to keep the historical backups, at least for a time, in a case a customer ever wants to revert.


sql server 2008 email notifications I have set up a profile and operator within SQL Server 2008. I also have an operator that is a group in MS Outlook.

When I use the single user address as the notification address when a job completes, the email is sent out (so profiles and mail is working). When i use the group address (its exactly the same format as the single user one - abc@abc.co.uk), nothing is sent out.

Any suggestions?

trobbie

Have you created a contact group in Outlook or a Distribution group in Exchange? (I'm assuming you are using Exchange?)

The Outlook group is just a container that lets you send emails to many people from your personal machine.  The SQL Server is trying to send to a group that exists only on your Outlook and not on the Exchange server which is why single email recipients work but not the group.

I would suggest setting up, or asking your email admin to setup a new SQL support distribution group on your mail server.

or

You can send to multiple single email addresses by separating the address of the recipient with a ;


Master-Master replication MariaDB failover resync I'm working on a project to replicate two sites running MariaDB 5.5 version. Replication works flawless until a host restarts, losing connection still works after reconnecting. When the node restarts if i manually enter the new master status to both servers they resume and replicate all data which were non replicated. Is there a way to auto-resume and resync the replication whenever something like this happens? Nodes are in a production environment so downtime with no recovery isn't allowed. Thanks in advance

I would recommend to upgrade to MariaDB 10.0 and use Global Transaction IDs. Replication isn't crash safe in 5.5, so you might encounter issues such as the one you've described above.


Why "Begin Transaction" before "Insert Query" locks the entire table? I am using SQL Server 2005 Express.

In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire table and all concurrent connections showed a hung display till the time this INSERT finished.

Why does the entire table get locked and how do I overcome this problem in SQL Server 2005 Express?

Edited

Query is as below:

INSERT INTO <table2> SELECT * FROM <table1> WHERE table1.workCompleted = 'NO'

This answer may prove helpful to the original question but is primarily to address inaccurate information in other posts. It also highlights a section of nonsense in BOL.

  And as stated for the INSERT documentation, it will acquire an
  exclusive lock on the table. The only way a SELECT can be made against
  the table is to use NOLOCK or set the isolation level of the
  transaction.

The linked section of BOL states:

  An INSERT statement always acquires an exclusive (X) lock on the table
  it modifies, and holds that lock until the transaction completes. With
  an exclusive (X) lock, no other transactions can modify data; read
  operations can take place only with the use of the NOLOCK hint or read
  uncommitted isolation level. For more information, see Locking in the
  Database Engine.

NB: As of 2014-8-27 BOL has been updated to remove the incorrect statements quoted above.

Thankfully this is not the case. If it were so inserts to a table would occur serially and all readers would be blocked from the entire table until the insert transaction completes. That would make SQL Server as efficient a database server as NTFS. Not very.

Common sense suggests it cannot be so but as Paul Randall points out, "Do yourself a favour, trust no-one". If you can't trust anyone, including BOL, I guess we'll just have to prove it.

Create a database and populate a dummy table with a bunch of rows, noting the DatabaseId returned.

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'LockDemo')
DROP DATABASE [LockDemo]
GO

DECLARE @DataFilePath NVARCHAR(4000)
SELECT
    @DataFilePath = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM
    master.sys.master_files
WHERE
    database_id = 1 AND file_id = 1

EXEC ('
CREATE DATABASE [LockDemo] ON  PRIMARY
( NAME = N''LockDemo'', FILENAME = N''' + @DataFilePath + N'LockDemo.mdf' + ''', SIZE = 2MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2MB )
 LOG ON
( NAME = N''LockDemo_log'', FILENAME = N''' + @DataFilePath + N'LockDemo_log.ldf' + ''', SIZE = 1MB , MAXSIZE = UNLIMITED , FILEGROWTH = 1MB )
')

GO

USE [LockDemo]
GO

SELECT DB_ID() AS DatabaseId

CREATE TABLE [dbo].[MyTable]
(
    [id] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , [filler] CHAR(4030) NOT NULL DEFAULT REPLICATE('A', 4030)
)
GO

INSERT MyTable DEFAULT VALUES;
GO 100

Setup a profiler trace that will track lock:acquired and lock:released events, filtering on the DatabaseId from the previous script, setting a path for the file and noting the TraceId returned.

declare @rc int
declare @TraceID int
declare @maxfilesize BIGINT
declare @databaseid INT
DECLARE @tracefile NVARCHAR(4000)

set @maxfilesize = 5
SET @tracefile = N'D:\Temp\LockTrace'
SET @databaseid = 9

exec @rc = sp_trace_create @TraceID output, 0, @tracefile, @maxfilesize, NULL
if (@rc != 0) goto error

declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 24, 32, @on
exec sp_trace_setevent @TraceID, 24, 1, @on
exec sp_trace_setevent @TraceID, 24, 57, @on
exec sp_trace_setevent @TraceID, 24, 3, @on
exec sp_trace_setevent @TraceID, 24, 51, @on
exec sp_trace_setevent @TraceID, 24, 12, @on
exec sp_trace_setevent @TraceID, 60, 32, @on
exec sp_trace_setevent @TraceID, 60, 57, @on
exec sp_trace_setevent @TraceID, 60, 3, @on
exec sp_trace_setevent @TraceID, 60, 51, @on
exec sp_trace_setevent @TraceID, 60, 12, @on
exec sp_trace_setevent @TraceID, 23, 32, @on
exec sp_trace_setevent @TraceID, 23, 1, @on
exec sp_trace_setevent @TraceID, 23, 57, @on
exec sp_trace_setevent @TraceID, 23, 3, @on
exec sp_trace_setevent @TraceID, 23, 51, @on
exec sp_trace_setevent @TraceID, 23, 12, @on

-- DatabaseId filter
exec sp_trace_setfilter @TraceID, 3, 0, 0, @databaseid

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Insert a row and stop the trace:

USE LockDemo
GO
INSERT MyTable DEFAULT VALUES
GO
EXEC sp_trace_setstatus 3, 0
EXEC sp_trace_setstatus 3, 2
GO

Open the trace file and you should find the following:

The sequence of locks taken is:

Intent-Exclusive lock on MyTable
Intent-Exclusive lock on the page 1:211
RangeInsert-NullResource on the clustered index entry for the value being inserted
Exclusive lock on key

The locks are then released in reverse order. At no point has an exclusive lock been acquired on the table.

  But this is just one batch inserting! That's not the same as two,
  three or dozens running in parallel.

Yes it is. SQL Server (and arguably any relational database engine) has no foresight as to what other batches may be running when it processes a statement and/or batch, so the sequence of lock acquisition does not vary.

  What about higher isolation levels e.g. Serializable?

For this particular example exactly the same locks are taken. Don't trust me, try it!


In a MySQL Master/Slave replication, what would happen if I write to the master table and in slave table is drop? In a MySQL Master/Slave replication, what would happen if I write to the master table and in slave table is drop ?

If the Slaves do not find 'DROP TABLE' command in the Master's binary logs, it cannot run the DROP TABLE either DROP TABLE command has not been run on Master.


How can I get a value of a column to 2 decimal points? As you can see from the columns, i wanted the output to be 2 decimal place as shown on EndOfYearForecast. Any way around it?

You can use the ROUND() function

SELECT
CONCAT("£",ROUND(SUM(cost),2)) AS EndOfYearForecast,
CONCAT("£",ROUND(SUM(cost/12),2)) AS MonthlyAverage,
CONCAT("£",ROUND(SUM(cost/53),2)) AS WeeklyAverage
FROM yearlyAnalysis;

You can also use the FORMAT() function if you want commas in the display

SELECT
CONCAT("£",FORMAT(SUM(cost),2)) AS EndOfYearForecast,
CONCAT("£",FORMAT(SUM(cost/12),2)) AS MonthlyAverage,
CONCAT("£",FORMAT(SUM(cost/53),2)) AS WeeklyAverage
FROM yearlyAnalysis;

EXAMPLE

mysql> SET @NUM=1234.56789; SELECT ROUND(@NUM,2) RoundNum,FORMAT(@NUM,2) FormatNum;
Query OK, 0 rows affected (0.00 sec)

+----------+-----------+
| RoundNum | FormatNum |
+----------+-----------+
|  1234.57 | 1,234.57  |
+----------+-----------+
1 row in set (0.00 sec)

mysql>


Partitioning an existing table into multiple filegroups I am working on a SQL Server 2016 SP2 Standard edition server, with an in-house application that is 24/7 used in multiple locations across multiple countries. The current application has one database per Customer (company using the application and central server). Eventually, there will be hundreds of Customers with databases in this system.

There is also one "Central" database, which includes all archival data from all of the individual DBs. A customer Id value is used to keep all of the different data separated.

To speed access to archival data, and to simplify maintenance, the current plan is to partition this Central DB, with the appropriate by-customer tables partitioned into one partition per customer, each on a separate physical file in a separate DB partition.

However, this DB and the relevant tables are already in use in an unpartitioned way. I am researching what needs to be done to split things out into the desired data structures.

Example: There is a table (Orders) that includes CustomerID. The desire is to split this existing table so there is one Customer per Filegroup/file based on the CustomeId. The partitioning function, partitioning scheme, and other miscellany is simple enough to figure out. However, the only examples I can find for splitting a table across filegroups involve creating a new table. The documentation doesn't explicitly state that ONLY a newly created table can do this in partitioning, and some older articles I have read (SQL 2005 and 2008) say that you can't do this.

So, the final question: Other than by creating a duplicate table with the correct partitioning, migrating the data, and renaming the old and new tables, is there any way to partition an existing table into multiple different new filegroups / physical files?

Directions to examples for SQL 2016 (or 2012 if it can be done there or later) will be accepted as well.

TIA

Create partition functions and partition schemes on required file groups. Then just rebuild/create the clustered index of required table on related partition scheme. It will involve data movement and take time. You can also use ONLINE = ON for keeping the table available.


Does the EXIST clause only return distinct rows by its nature? I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database.

The first query uses an EXISTS clause with a subquery:

SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
WHERE EXISTS
(SELECT *
    FROM HumanResources.Department AS d
    JOIN HumanResources.EmployeeDepartmentHistory AS edh
       ON d.DepartmentID = edh.DepartmentID
    WHERE e.BusinessEntityID = edh.BusinessEntityID
    AND d.Name LIKE 'P%')

The second query only uses JOINs:

SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
JOIN HumanResources.employeeDepartmentHistory AS edh
    ON P.businessentityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
    ON edh.DepartmentID = d.DepartmentID
WHERE d.name LIKE 'P%'
AND E.BusinessEntityID = EDH.BusinessEntityID

The first query returns 198 rows, while the second query returns 199.

I then ran both queries against each other with an EXCEPT clause to see which row was disappearing:

SELECT  p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
JOIN HumanResources.employeeDepartmentHistory AS edh
    ON P.businessentityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
    ON edh.DepartmentID = d.DepartmentID
WHERE d.name LIKE 'P%'
AND E.BusinessEntityID = EDH.BusinessEntityID

EXCEPT

SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
WHERE EXISTS
(SELECT *
    FROM HumanResources.Department AS d
    JOIN HumanResources.EmployeeDepartmentHistory AS edh
       ON d.DepartmentID = edh.DepartmentID
    WHERE e.BusinessEntityID = edh.BusinessEntityID
    AND d.Name LIKE 'P%')

Surprisingly no rows were returned. But then I added a DISTINCT clause to my query with JOINs, and then both queries were finally returning the same result sets.

Am I correct in assuming that to have logically equivalent queries of this nature, I would have to add a distinct clause to my query that only uses JOINs?

What is the benefit of using subqueries like this? To me, JOINs make much more sense, and are easier to understand as to what is going on.

EXISTS don't actually return any rows.  They check for the existence and then move on.

Your problem is probably a duplicate caused by the JOINs to the second two tables.  Try running this:

SELECT p.FirstName, p.LastName, e.JobTitle, COUNT(1) AS Cnt
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID
JOIN HumanResources.employeeDepartmentHistory AS edh
    ON P.businessentityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
    ON edh.DepartmentID = d.DepartmentID
WHERE d.name LIKE 'P%'
AND E.BusinessEntityID = EDH.BusinessEntityID
GROUP BY p.FirstName, p.LastName, e.JobTitle
HAVING COUNT(1) > 1

I'm guessing you will get back one row with a count of 2.

The reason you aren't seeing anything in the EXCEPT is because all of the rows do exist in both places.  You just have a duplicate in the second query.

The reason for the duplicate is because either the joins with HumanResources.employeeDepartmentHistory and HumanResources.Department.  Basically the HumanResources.employeeDepartmentHistory table is used to map one employee to two departments.

This is one of the major differences in using a JOIN vs EXISTS for the type of logic you are trying to use.


SQL Server Assertion Error Does anyone have a solution to this problem?

  SQL Server Assertion: File: , line=10850 Failed Assertion = 'GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

Actions that are already taken:

DBCC Checkdb no_infomsgs - reported no problems.
DBCC CHECKDB, report 0 errors, 0 consistency errors.

Environment:

Windows Server 2003 R2 Enterprise Edition 64Bit SP2
Microsoft SQL Server Standard Edition (64-bit) Version 9.00.3042.00

This sounds like a known issue with SQL Server 2005.  This page from MS details both a patch if you can upgrade, and a workaround if you cannot.


Can't connect to new instance of SQL Server I was trying to upgrade to SQL Server 2014 from SQL Server 2012, but I kept getting a failure on one of the pre-installation checks saying that I needed to have SP1 or later installed for SQL Server 2005. Not sure why it was asking this as I'm pretty sure I never had 2005 installed. I went ahead and found the latest SP for 2005 and installed it but still I was getting the same failure on the pre-install check.

So I decided to install a new instance of 2014 along with cumulative update #5. I named the new instance 'MSSQLSERVER14' to tell it apart from the 'MSSQLSERVER' instance that was already installed for SQL Server 2012. So in my Services I basically have two instances of each service of Sql Server. One for 2012 and one for 2014. I disabled all the 2012 services so only the 2014 are running. However, when I try to connect through Sql server management studio it tells me it cannot connect because of a network-related or instance-specific error.

I've been using Sql Server 2012 with Management Studio for that last couple of years with no problems, so I'm 100% this is caused by the switching to 2014. Anyone know how I can solve this issue?

You have installed a new instance of SQL Server, so you need to change the way you connect to that new instance.

Previously, you had a default instance, so connecting using any of these in the Server name: box would work fine:

localhost
.
127.0.0.1
<any_local_IP>
<your_machine_name>
<your_FQDN>

Now that you have installed a new instance of SQL Server, you need to reference that instance name as part of the connection string, so any of the above need to become the following:

localhost\MSSQLSERVER14
.\MSSQLSERVER14
127.0.0.1\MSSQLSERVER14
<any_local_IP>\MSSQLSERVER14
<your_machine_name>\MSSQLSERVER14
<your_FQDN>\MSSQLSERVER14


How do I bypass the typing of `GO` in `sqlcmd`? sqlcmd has this most annoying this of having to type GO after every command, how can I get around this?

SELECT 1 AS "foo"
2> GO
foo
-----------
          1
(1 rows affected)

Can I make this like psql...

test=# SELECT 1;
 ?column?
----------
        1
(1 row)

Reference: https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility

With -q you can execute multiple query, as you do not get your prompt back until you hit ctrl+c.

  -q" cmdline query " Executes a query when sqlcmd starts, but does not exit sqlcmd when the query has finished running.
  Multiple-semicolon-delimited queries can be executed. Use quotation
  marks around the query, as shown in the following example.

This is in my workstation.

 of -q.

  -Q" cmdline query " Executes a query when sqlcmd starts and then immediately exits sqlcmd. Multiple-semicolon-delimited queries can be
  executed. Use quotation marks around the query, as shown in the
  following example.


How can I search SQL Server for maintenance plans that execute specific tasks? Sometimes certain SQL Server maintenance plan tasks can be problematic, like Shrink Database, Rebuild Index, Update Statistics, etc. I'd like to search for the use of one or more of these tasks on an instance of SQL server, for further review of whether they are necessary.

Is there a way to query for maintenance plans that perform one or more specific tasks?

(Not limited to the tasks that I mentioned.)

Yes. This can be done by using a combination of the system SSIS tables and XQuery.

Note: In SQL 2012, this will only work for Maintenance Plans created within SSMS. If you used Data Tools to deploy a package that performs maintenance via the project deployment model, this will not work.

SQL Server saves maintenance plans as SSIS packages, hence the need for the system SSIS tables (sysssispackages and sysssispackagefolders).
The package-level data where we'll find the references to the tasks you're searching for will be found in the column sysssispackages.packagedata.

XQuery

If you are not familiar with XQuery, you can start here.

Find the task ExecutionType(s) for the Task(s)

The first thing that you'll need to find is the ExecutionType of the task(s) you're looking for.

Find an existing maintenance plan with the task(s) in question or build a new maintenance plan.
Remember the name(s) of the maintenance plans you're looking for.
Query msdb..sysssispackages for the plan name(s).

Example query to retrieve the package XML based on the maintenance plan name:

 SELECT
    name PlanName
    ,createdate PlanCreated
    ,CAST(CAST(pack.[packagedata] AS varbinary(max)) AS XML) PackageDataXml
 FROM msdb.dbo.sysssispackages pack
 WHERE name = 'RebuildIndexes'

Click on the results in the PackageDataXml column which will open the raw XML in another query tab.
In the newly opened XML tab, search for the string "ExecutableType". (Nothing fancy required here - just good old-fashioned CTRL+F.)

We do this because each task is saved as a DTS:Executable node, and each DTS:Executable node has a DTS:ExecutableType. The Executable type is what we can use in our final query to search for the specific tasks.
You'll find some ExecutableTypes for Executable nodes that don't relate to our quest. Keep searching until you find the relevant Executable node(s).
You'll know a good one when you see it, because the value for DTS:ExecutableType will have a useful descriptive name.
Only grab the first portion of the ExecutableType, which is usually the part of the string before the comma.

Search for the maintenance plans using the ExecutableType(s)

The following query uses XQuery and the ExecutableType values that were found in the step above to search in all maintenance plans.

There are commented out lines that would allow for filtering for plans associated with active jobs and/or active schedules.
Credit goes to StuartLC who helped me with the XQuery portion of this.
Has only been tested on 2012 and 2008R2.

/*
  Description: Search SSISPackages w/ xQuery for specific
  Maintenance Plan tasks. In this example:

    --> Index Rebuild
    --> Shrink Database
    --> Update Statistics

Known compatible versions: 2008R2 & 2012

    --> No 2008 or 2005 instances to try it out on. :'(
*/

-- Build a temp table to retrieve the list of plans
-- that use the tasks we're searching for.

IF OBJECT_ID('tempdb..#BadPlans') IS NOT NULL
  DROP TABLE #BadPlans;

CREATE TABLE #BadPlans
(
  PlanName nvarchar(128)
  ,PlanCreated datetime
  ,PlanFolder nvarchar(128)
  ,TaskDescription varchar(50)
  ,TaskExecutableType varchar(200)
);

;WITH XMLNAMESPACES
(
  'www.microsoft.com/SqlServer/Dts' AS DTS
  ,'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
INSERT INTO #BadPlans

--******2012 Results******

SELECT
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(@DTS:Description)[1]', 'varchar(50)')
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)')
    AS TaskExecutableType
FROM
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY
-- Version-specific XPath for 2012. Look at this singular-plural-singular-plural-singular madness.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executables/DTS:Executable/DTS:Executables/DTS:Executable')
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%'

UNION ALL

--******2008R2 Results******

SELECT
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(DTS:Property[@DTS:Name="ObjectName"])[1]', 'varchar(50)')
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)')
    AS TaskExecutableType
FROM
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY
-- Version-specific XPath for 2008R2. Not as much madness here.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executable/DTS:Executable')
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)')
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%';

SELECT * FROM #BadPlans;


LOAD DATA INFILE blocks on concurrent use against MyISAM tables I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks – a system lock, in MySQL profiling information – until the previous is finished. This happens independent of loading into the same/different table, or even a different database. InnoDB does not have the same problems, however, MyISAM seems better suited for a pure logging-table.

Can concurrent LOAD DATA INFILE operations be done with MyISAM engine on MySQL? Am I missing some crucial settings (DISABLE KEYS is automatic against an empty database)? Tested on MySQL 5.1.63 and 5.5.14.

I expected loading using LOAD DATA INFILE to different tables concurrently to be performant, however, performance is instead completely destroyed.

To replicate my scenario:

SQL

CREATE DATABASE test;
CREATE TABLE `log_isam` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `num` int(11) DEFAULT NULL,
  `surname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE log_isam ADD INDEX num(num);

CREATE TABLE `log_isam2` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `num` int(11) DEFAULT NULL,
  `surname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE log_isam2 ADD INDEX num(num);

LOAD DATA log.txt file contents

https://gist.github.com/4245602

Shell script

MYSQL_USER=root
MYSQL_PASS=PASS
time mysql -u$MYSQL_USER -p$MYSQL_PASS -e \
    "LOAD DATA INFILE '/tmp/log.txt' \
     INTO TABLE log_isam FIELDS TERMINATED BY ','" test & \
time mysql -u$MYSQL_USER -p$MYSQL_PASS -e \
    "LOAD DATA INFILE '/tmp/log.txt' \
     INTO TABLE log_isam2 FIELDS TERMINATED BY ','" test

Concurrent LOAD DATA INFILE on MyISAM tables are limited by mutex congestion due a global Key Cache. To alleviate lock congestion, MyISAM tables can be set to have their own Key Cache.

SET GLOBAL isam_cache.key_buffer_size=8*1024;
SET GLOBAL isam_cache_two.key_buffer_size=8*1024;
CACHE INDEX log_isam IN isam_cache;
CACHE INDEX log_isam2 IN isam_cache_two;


Is a relational database with a dynamic number of tables a good design? I have a use case where I wish to create a table for each entity to which the underlying application that owns this entity will publish records.
This table has a fixed structure, so if there are 5 such entities in my system, there will be 5 different tables with the same schema.
The schema is generic with one of the columns in the schema as JSON for flexibility. I do not expect queries based on the fields in the JSON. I expect the following queries on each entity:

On the auto-increment id primary key column with LIMIT and OFFSET where I need to read X rows from the record with id Y.
On the creation date column with LIMIT X.

I expect thousands of such entities to be created on the fly so in turn there will be thousands of tables in the database.
In future when one of these entities have fulfilled their purpose, the table would be simply deleted.
I expect most of these tables to have not more than 100 rows while there will be a few with at least 1M rows as time goes by.
This design makes data easy to query as my application can determine the table name from the entity name.
Is this a bad design?
Is there a limit to the number of tables in a database in RDBMS (the above design is with Postgresql 11 in mind) keeping performance in mind?
Should I use any different datastore to achieve this other than RDBMS? Any suggestions?

Is this a bad design?

Yes.  See comment by LowlyDBA.

Is there a limit to the number of tables in a database in RDBMS ...

Each RDBMS has a different maximum number of tables.  There is a Q&A for this is on Stack Overflow.

Should I use any different datastore to achieve this other than RDBMS?

No. RDBMS should be fine if you design your schema correctly.

Any suggestions?

From your description, a single table with Entity_Name as part of the Primary Key should work. A table with 100M rows is not a problem for today's databases.
create table data_for_entity (
  entity_name     varchar(50),
  entity_id       int,
  entity_value    text, -- or whatever PostgreSQL uses for JSON
  constraint data_for_entity_pk Primary Key (entity_name, entity_id)
);


SQL Query Optimizer - how does cost vary with resource availability When the query optimizer calculates the cost of an execution plan, can you expect the plan to differ depending on the amount of resource available to it?

Take the following scenario:

You have a VMware virtual machine running SQL Server 2008 R2 with 32GB RAM, 8 CPU cores hooked up to a SAN. There are 7 VMs sharing the host box.
You find CPU usage is peaking at 130% regularly, so you move most of the VMs off the host, leaving only 3 of the original 7 VMs including the SQL Server box.
You immediately find that actual performance is slower now that more CPU headroom is available to it. You can see that CPU utilization on the guest is going much higher (because more CPU resource is available for use) than the 30% it could muster before. Despite this, users of the application see a marked slowdown in performance.

My interpretation of why a SQL VM performs slower when more CPU resource is available to it is that the procedure cache contains plans calculated under/optimized for an environment with less CPU availability. When the extra resource becomes available, the procedure cache will provide sub-optimal plans which may perform less well.

Does this make sense? We had this exact scenario a couple of weeks back and had lots of complaints about performance as soon as we stopped the CPU thrashing on the host. I ran dbcc freeprocache and thereafter performance seemed to improve - either that or the users got used to it.

Thoughts?
Thanks

First, some background information

So far (until SQL Server 2014) the query optimiser will only take a few things into account:

The number of CPU cores available on the system
The current amount of memory in use

CPU cores: The core count assigned to SQL Server from the affinity mask determines how efficient it can possibly be to create a parallel plan. For example, on a system with 4 cores, you can expect a 4x speedup when switching to a parallel plan, whereas a system with 2 cores only get a doubling. Because of this, the core count can affect the choice of plan. Unfortunately, not every query can scale linearly with the core count, yet the optimiser will believe it does. This means that on machines with more than around 12-16 cores, getting more parallelism will actually SLOW the query down. The speed of the CPU is not taken into account, only the number of cores.

Memory Available: When the plan is made, the amount of memory available is taken into account. This determines strategies like hash joins, sort space and other memory intensive operations. Mis-estimation here is very dangerous and can lead to poor performance. Especially if you over estimate the memory available for a hash join and have to spill into tempdb.

Your specific case

Without measurements of your system it is difficult, if not impossible, to know exactly what happened in your scenario. There are too many variables that potentially changed at the same time. It may simply be that something else has changed in the environment. Any diagnosis is pure guesswork and real DBA work is a science, not an arty exercise in guessing.

You would need to collect the following to get knowledge.

The query plans before/after
Wait stats
Exact machine configuration (both for the host and the VM in the virtualised scenario)


How to calculate total time taken I want to calculate total driving time excluding time between Pause and Resume Driving using sql query.

Here is the sample data:

Create Table Tracker
(LocationID int,
RequestID int,
TrackingDatetime datetime,
Status varchar(30));
go

Insert into Tracker
values
(1, 22, '2019-06-17 14:10:00', 'Stop'),
(2, 22, '2019-06-17 14:08:00', 'Driving'),
(3, 22, '2019-06-17 14:06:00', 'Driving'),
(4, 22, '2019-06-17 14:04:00', 'Resume'),
(5, 22, '2019-06-17 14:02:00', 'Pause'),
(6, 22, '2019-06-17 14:00:00', 'Driving'),
(7, 22, '2019-06-17 13:58:00', 'Driving'),
(8, 22, '2019-06-17 13:56:00', 'Start');

Grid view of sample data:

This is how you should have mention in the question:

Create Table Tracker
(LocationID int,
RequestID int,
TrackingDatetime datetime,
Status varchar(30));
go

Insert into Tracker
values
(1, 22, '2019-06-17 14:10:00', 'Stop'),
(2, 22, '2019-06-17 14:08:00', 'Driving'),
(3, 22, '2019-06-17 14:06:00', 'Driving'),
(4, 22, '2019-06-17 14:04:00', 'Resume'),
(5, 22, '2019-06-17 14:02:00', 'Pause'),
(6, 22, '2019-06-17 14:00:00', 'Driving'),
(7, 22, '2019-06-17 13:58:00', 'Driving'),
(8, 22, '2019-06-17 13:56:00', 'Start');

Following query that you can start with for desired result (for test..):

WITH T as (
            select  LocationID,
                    RequestID,
                    TrackingDatetime,
                    CASE    WHEN Status = 'Resume'
                            Then TrackingDatetime
                            Else LEAD(TrackingDatetime) OVER (PARTITION BY RequestID ORDER BY TrackingDatetime desc)
                    END as PrevTrackingTime,
                    Status,

                    ISNULL(DATEDIFF(minute,
                                (CASE   WHEN Status = 'Resume'  Then TrackingDatetime Else LEAD(TrackingDatetime) OVER (PARTITION BY RequestID ORDER BY TrackingDatetime desc) END),
                                 TrackingDatetime
                            ), 0) as Duration_Minutes
            from Tracker
            )
select *, SUM (Duration_Minutes) over (order by TrackingDatetime) as Duration_Cumm
from T


How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week? I am  a total newb, and I couldn't find a good way to do this anywhere.  I have a database table that contains statistics that are recorded at various time throughout the week.  The reporting week starts on Thursday.  The table contains a datestamp column (date) that stores when the data was recorded.

I need to pull the data for a given week (reporting weeks start on Thurs).  I have written the following query:

   SELECT *
FROM `table`
WHERE 1 = CASE
  WHEN WEEKDAY(NOW()) = 0 THEN DATEDIFF(NOW(),`date`) BETWEEN -2 AND 4
  WHEN WEEKDAY(NOW()) = 1 THEN DATEDIFF(NOW(),`date`) BETWEEN -1 AND 5
  WHEN WEEKDAY(NOW()) = 2 THEN DATEDIFF(NOW(),`date`) BETWEEN -0 AND 6
  WHEN WEEKDAY(NOW()) = 3 THEN DATEDIFF(NOW(),`date`) BETWEEN -6 AND 0
  WHEN WEEKDAY(NOW()) = 4 THEN DATEDIFF(NOW(),`date`) BETWEEN -5 AND 1
  WHEN WEEKDAY(NOW()) = 5 THEN DATEDIFF(NOW(),`date`) BETWEEN -4 AND 2
  WHEN WEEKDAY(NOW()) = 6 THEN DATEDIFF(NOW(),`date`) BETWEEN -3 AND 3
END

This appears to work on initial testing.  But I am not sure it is the best way to go about it.  I don't know much about MySQL performance, but there will be over a hundred thousand records to filter.  Will this query be real slow due to the number of conditions checked?

The NOW() function is used when pulling the most current report- however, it some cases I will need to do reports for other weeks- so I would substitute another date into the place.

Also, doing it this way requires re-writing the query if the reporting week changes- say the starting day changes to Wednesday.

I can't use the WEEK() function because you can only start a week on Sun or Mon with it.

Any ideas to improve this query are much appreciated!

Other Notes: Currently using MariaDB 5.3.

Here is a query I wrote up to give you the most recent thursday and the ending wednesday

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

Here is an example for today, 2011-09-21

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2011-09-15 00:00:00 | 2011-09-21 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Just replace the NOW() function calls with whatever datetime you like and you will have the week starting Thursday all the time for the give datetime you choose.

Here is another example using the specific date '2011-01-01'

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE('2011-01-01') - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE('2011-01-01') dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2010-12-30 00:00:00 | 2011-01-05 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Your query of table referencing today would resemble something like this:

SELECT * from `table`,
(SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A) M
WHERE `date` >= thu_beg
AND `date` <= wed_end;

Give it a Try !!!

UPDATE 2011-09-22 16:27 EDT

This was my proposed query for marking Thu-Wed.

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

How about other weeks ???

(SELECT SUBSTR('6012345',wkndx,1) does the week starting Mon ending Sun
(SELECT SUBSTR('5601234',wkndx,1) does the week starting Tue ending Mon
(SELECT SUBSTR('4560123',wkndx,1) does the week starting Wed ending Tue
(SELECT SUBSTR('3456012',wkndx,1) does the week starting Thu ending Wed
(SELECT SUBSTR('2345601',wkndx,1) does the week starting Fri ending Thu
(SELECT SUBSTR('1234560',wkndx,1) does the week starting Sat ending Fri
(SELECT SUBSTR('0123456',wkndx,1) does the week starting Sun ending Sat


Can we use MariaDB mysqldump CLI to take dump from MySQL Community version? MySQL server version:

  mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))

MariaDB mysqldump CLI version:

  mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)

I am using this mysqldump version to take dump from the mentioned mysqld version. Data is around 25 gb.

I am getting below error while importing the dump,

  ERROR 3105 (HY000) at line 82: The value specified for generated
  column 'column_name' in table 'table_name' is not allowed.

Source and destination mysql server is same version.

Command used to dump,

mysqldump -h mysql -P 3306 -uroot -px xxxxxxx --databases db_name

Total number of characters in Line 82 is around 80k, line starts with,

  INSERT INTO table_name (ID, XXXXX, XXXXX, XXXXX,
  XXXXX, XXXXX, XXXXX, XXXXX, XXXXX,
  XXXXX, xxxxx, xxxxx, XXXXX) VALUES
  (5541,1,4852,'2018-11-12 09:30:25.306','{\"id\": 1108

Output from show create table, for the column throwing error:

  column_name varchar(45) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS
  AS (json_unquote(json_extract(OPTIONS,'$.type'))) VIRTUAL,

Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?

MariaDB JSON functions are different from MySQL ones.

Manually create the equivalent table in MariaDB-10.3.
remove the DROP TABLE {tablename} /CREATE TABLE {tablename} from your mysqldump output.
import the edited mysqldump file.

If there are a lot of these generated columns, suggest doing a mysqldump --no-data and getting the syntax of all the tables correct first.


Select columns from 4 tables First thing, I am totally new to SQL. I saw some tutorials, videos, tried searching but didn't find what I needed. I know it has asked before, but I couldn't find it.

What I need to find is basically like:

Select
    Company.Name, Programmer.Name, Manager.Name, CEO.Name
where
    Company.City = Programmer.City = Manager.City = CEO.City

like select all of them who are from London.

EDIT: Tried doing this with 2 tables only, code as below, but I'm getting an error:

SELECT Company.Name
FROM Company Cm
INNER JOIN Programmer Pg ON Cm.City = Pg.City
WHERE Cm.City = 'London'

  The multi-part identifier "Company.Name" could not be bound.

If you are attempting you return all rows from the four tables where the City is the same, you should be able to use a UNION ALL to get the results:

select name, 'Company' as Source
from company
where city = 'London'
union all
select name, 'programmer' as Source
from programmer
where city = 'London'
union all
select name, 'manager' as Source
from manager
where city = 'London'
union all
select name, 'ceo' as Source
from ceo
where city = 'London'

I included a field that identifies what table the record is coming from.  This is not necessary for the query and can be removed.

Just as a side note, the error you were getting in your query is because you created an alias for the Company table but when selecting the Company.name in the SELECT list you are not using the alias.  You would need to use:

SELECT Cm.Name
FROM Company Cm
INNER JOIN Programmer Pg
   ON Cm.City = Pg.City
WHERE Cm.City = 'London'

If you want to JOIN the tables then you could use something similar to this:

SELECT Cm.Name
FROM Company Cm
INNER JOIN Programmer Pg
   ON Cm.City = Pg.City
INNER JOIN Manager M
   ON Cm.City = M.City
INNER JOIN CEO C
   ON Cm.City = C.City
WHERE Cm.City = 'London'


Insert/Update Stored Procedure My database model is quite simple. I have the following tables: Monitor, Computer, and Monitor_Computer_Map as my mapping table for a many-to-many relationship between the other two tables.

I have a script that will run on end users' computers as the user. (This is why I chose a stored procedure in the first place. Is there a better suggestion?). I need to insert data about the user's computer into the Computer table and data about the attached monitors into the Monitor table. What I would like to do is map the monitors to the computers in the mapping table. Is it possible to do all this in a stored procedure?

p.s I'm new to working with databases and I may be going about this all wrong.

EDIT:
With the help of @Michael Green i was able to come up with the script below. However, I still can't get it working. Currently I'm reading up on how variables are set and used in SQL so I'm slowly getting it. Also, I'm using a Powershell script to do the inserting which I'm pretty sure I have on lock.

Because my database schema is so simple I'll just post it here.
Note: All timestamps are set in the default values.

-- My Table Parameter Value (TVP)
CREATE TYPE ComputerTableType AS TABLE
(Name nvarchar(15),
IPAddress nvarchar(15))

-- My Stored Procedure (SP)
CREATE PROCEDURE sp_InsertUpdateSerialInventory
@Serial nvarchar(50),
@TVPComputer ComputerTableType READONLY

AS
DECLARE @TVPComputer TABLE (Computers int)
INSERT INTO dbo.Computers (Name, IPAddress)
OUTPUT INSERTED.Id
INTO @TVPComputer
VALUES (@TVPComputer);
INSERT INTO dbo.Monitors(Serial)
OUTPUT INSERTED.Id
INTO @Serial
VALUES (@Serial);

EDIT #2:
Still learning...
So I've come to the conclusion i don't actually need a Table Parameter Value(TPM) for my Computers table. I only have two fields which isn't a big deal In my opinion. So what I've done is add each parameter to the top of my Stored Procedure and continue with following @Michael Green's suggestion. I can't seem to get the Table Variable working correctly. Below is my updated code. Still not working! I keep getting a "Must declare the scalar variable". I found some other topics on this error on Stack Exchange but nothing that resolves my issue. It looks like a scope issue or something?

CREATE PROCEDURE sp_InsertUpdateSerialInventory
@Serial nvarchar(50),
@Name nvarchar(15),
@IPAddress nvarchar(15)

AS
BEGIN

    DECLARE @MonitorId TABLE (Id int);
    INSERT INTO dbo.Monitors (Serial)
    OUTPUT inserted.Id INTO @MonitorId(Id)
    VALUES (@Serial)

    DECLARE @ComputerId TABLE (Id int);
    INSERT INTO dbo.Computers (Name, IPAddress)
    OUTPUT inserted.Id INTO @ComputerId(Id)
    VALUES (@Name, @IPAddress);

    INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
    VALUES (@MonitorId, @ComputerId)
END

EDIT #3 - Working!
I can't thank you enough @Michael Green for your help. Your answer took me on quite a learning journey which ultimately brought me to a resolution. Below is my working code!

CREATE PROCEDURE InsertUpdateSerialInventory --Fixed Name. That link was a good read thank you!
@Name nvarchar(15),
@IPAddress nvarchar(15),
@Serial MonitorTableType READONLY

AS
BEGIN

    DECLARE @MonitorId TABLE (MId int);

    INSERT INTO dbo.Monitors (Serial)
    OUTPUT inserted.Id INTO @MonitorId(MId)
    SELECT Serial
    FROM @Serial;

    DECLARE @ComputerId TABLE (CId int);

    INSERT INTO dbo.Computers (Name, IPAddress)
    OUTPUT inserted.Id INTO @ComputerId(CId)
    VALUES (@Name, @IPAddress);

    INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
    SELECT m.MId, c.CId
    FROM @MonitorId AS m CROSS JOIN @ComputerId AS c;
END

One execution of your script will produce one computer name and a list of one or more monitors. You can pass these to a SQL Server stored procedure, you just have to get the parameter types correct.

The computer name is simplest. With just this the SP definition would look like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100)
as
    ...

I use varchar(100) as an example.  You use whatever's right for you. The list of monitors is more difficult because it may contain many values.  For this you have to use a table-valued parameter and for that you will have to define a type:

create type MonitorList AS TABLE (
    MonitorName varchar(100)
    );
GO

Now the SP looks like this:

create procedure dbo.MyProcedureName
    @ComputerName varchar(100),
    @Monitors     MonitorList    READONLY
as
    ...

Now to the body of the SP. If you use the names as the primary key in tables Computer and Monitor the INSERT statements are straightforward.  Assuming you have primary keys and foreign keys defined -- you should -- you have to insert in the correct sequence to respect these key definitions i.e. into Computer and Monitor and only then into the mapping table.

insert dbo.Computer (ComputerName)
values (@ComputerName);

The type we defined, and variables created off it, behave just like any other table:

insert dbo.Monitor (MonitorName)
select MonitorName
from @Monitors;

insert Monitor_Computer_Map (ComputerName, MonitorName)
select @ComputerName, MonitorName
from @Monitors;

If your tables have surrogate keys that use IDENTITY, however, you have more work to do.  You have to capture these identity values as they are generated in the parent tables and use them in the mapping table.  This can be done with the OUPUT clause:

declare @ComputerID table ( ComputerID int);

insert dbo.Computer (ComputerName)
output INSERTED.ID
into @ComputerID
values (@ComputerName);

Do the same thing for monitors and use the local table variables to populate the mapping table.

Of course you want to have appropriate validation, duplicate checking and error handling in the body of the SP, too.

You don't say what scripting language you use.  The documentation for it will tell you how to declare and populate stored procedure parameters for SQL Server.

Response to OP's EDIT #2:

First, a few tips.  Please post the full error message; it helps immensely with debugging.  Second, if you're using SSMS you can double-click an error and it will highlight the code in error.  Third, get in the habit of closing your statements with a semicolon.  It is not required yet but it will be soon.

If all computers have exactly one monitor then the TVP is not needed.  You are correct.  How many developers have only one monitor these days?  I've seen finance traders' stations with eight.  In these cases you do want a TVP.  Please, please, please do not be tempted to write @Serial1, @Serial2, @Serial3 ....

Your code will throw the error Must declare the scalar variable "@MonitorId". This is because of your third INSERT statement:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
VALUES (@MonitorId, @ComputerId);

When you use the INSERT..VALUES syntax SQL Server demands that there can be only one value per variable.  You have provided table valued variables which could (potentially) hold many values.  What you need is the INSERT..SELECT syntax.  Yes, I know @ComputerID only has one row but it is a table and it could have many rows.  What you need is:

INSERT INTO dbo.Monitor_Computers (Monitor_Computer_Monitor, Monitor_Computer_Computer)
SELECT m.Id, c.Id
from @ComputerId AS c
cross join @MonitorId AS m;


Security risk with xp_cmdshell In one of our production servers, we need to enable xp_cmdshell for some app functionality.

With our current security measures, it is disabled in all instances and needs to be enabled for just one specific SQL Server.

How bad is it to enable xp_cmdshell?
Is it really a big thing to worry about?
Any specific measure that can be taken to enable it and still limit its use?

Our application team has created a trigger which kicks off another invoice process and they need xp_cmdshell for that.

According to the Microsoft Docs for xp_cmdshell:

xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

Thus, if you attempt to execute xp_cmdshell as a non-sysadmin user,  assuming you have not configured the proxy account, it will fail.  This means only sysadmins can run it.  Do you trust your sysadmins?
Once xp_cmdshell is enabled, the shell it spawns has the same security as the account used to run SQL Server itself (or the proxy account, if configured).  If that account has full permissions on the local machine, then the shell created by xp_cmdshell will have full permissions on the local machine.  Same thing if the SQL Server Service Account is a domain admin (which I would never suggest).  Following the principle of least-privilege should mean SQL Server has the minimum rights it needs to function, and xp_cmdshell will have those same set of rights.
If you configure xp_cmdshell to use the proxy account, then the shell spawned by it will have the rights of that account, which can be locked down.  However, any sysadmin can modify the proxy account settings, so ensuring you limit who has sysadmin rights is extremely important.
You've mentioned that your application team is wanting to use xp_cmdshell inside a trigger.  This is going to really limit the performance of your system, and you really should re-think that design.  If, for instance, xp_cmdshell returns an error, the insert into your table will fail.  If the call to xp_cmdshell takes several seconds to complete, the rows affected by the insert will be locked during that time, and may cause other sessions to be blocked.
As @DanGuzman pointed out, you should instead use a service broker queue to provide an asynchronous guaranteed message delivery mechanism that won't interrupt the insertion of rows into the affected table.  Service Broker queues can be consumed by apps outside of SQL Server in a responsive, reliable, and scalable manner.