Implicitly (or proxy) convert to Nvarchar on incoming queries from legacy or 3rd party applications We have a 3rd party application of which we don't have the source code. This application executes queries on our database without using the "N" prefix before literal strings. It does this in inserts and where clauses.

So what we need is a way to translate a query like this:

SELECT Whatever FROM Person WHERE Name = 'John';

to this:

SELECT Whatever FROM Person WHERE Name = N'John';

Is there a way to implicitly force this on SQL server (2005 or higher, I guess if an upgrade is needed it would be acceptable).

Otherwise, does anyone know of a 3rd party proxy that would be able to translate the queries? Building one would be costly, but I think it is feasible. Or could we use some other trick like a trigger or something?

I have already checked if the 3rd party application can handle multi-byte characters in the strings that are sent and returned, that is not a problem at all. The only problem is that it's formatting the queries without using N's. (It should have been using parameters but we can't change it anyway).

I guess the implicit conversion causes index scan, if that is the case, You can use plan guides and force index seek without changing the source code.


MySQL '%' hostname wildcard I've used the following to grant all privileges on mydb to myusername:

GRANT ALL PRIVILEGES ON mydb.* to myusername @'%' IDENTIFIED BY 'mypassword';

I thought that the % is a wildcard for all hosts. It worked for a while, but then I had some permission errors when the user tried to access the db from the same machine. I added the following lines:

GRANT ALL PRIVILEGES ON mydb.* to myusername @'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydb.* to myusername @'127.0.0.1' IDENTIFIED BY 'mypassword';

And now the user can access the database.

What does the % stand for? Is there a wildcard for all hosts?

Looking up the documentation, two things to mention:

not sure whether a space is allowed before @ (not declared explicitly, but the user string takes the user_name@host_name form - and since this is a string, one can expect that the space is not allowed)
and, better, if you want to grant privileges to a user on all hosts, you can simply omit the @hostname part completely.


Understanding Stolen and foreign memory usage between 2 app database servers Below is more for my understanding on how to interpret the data from my perfmon counters for 2 database servers with diff app structure but sort of similar workload

  Server A has total of 1 TB RAM with databases of approx 10 TB with
  below metrics via 4 NUMA node config

  Server B has total of 1 TB RAM with databases of approx 16 TB with
  below metrics via 4 NUMA node config

Server B has more MAX memory assigned to it due to size of databases being large out there. Yes i know MAX memory settings can be improved little better, but would plan later if i can understand above numbers better for stolen and foreign memory.

What do those numbers tell me as i see foreign memory seems to be approx 50 GB for one server and stolen being 150 GB on other server where foreign just seems to be 5 GB.

Sorry for my lack of knowledge here, but can someone guide on better way to interpret this data and what possible changes one can think of tweaking here?

Stolen memory refers to memory that is being used for something other than buffer, that could be used for buffer if it weren't being used for something else.  See why SQL Server 2012 still needs the "Stolen Server Memory (KB).  To minimize the amount of stolen memory, one would need to optimize queries so that they use as little memory as possible.  For example, if there is an unnecessary sort, that may use some of this memory.  If it's a reporting server, my guess is you're just going to have to account for some amount of stolen memory.

Foreign memory refers to worker memory that is on a remote NUMA node.  See How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks).  When memory is foreign it takes a bit longer to access it, and the impact depends on your system and the speed of the components.  To minimize, you would want to make sure you have the NUMA configuration and Max Degree of Parallelism set correctly.


Using OLAP-specs to compute selective running totals Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value <> 0, and value is just some number. I want to build a query that gives columns id, sum(value) over all entries with this id, and sum(value) over all such rows having state <> 0.

Of course, I know how to build this using subselects to compute the sums. However, I wonder how to do this elegantly using some OLAP-specification. Since I am not yet used to OLAP-specifications, I am not very certain how to do this.

Surely, something like

select * from (
  select
    id,
    state,
    sum(value) over (partition by decode(state, 0, 0, 1),
    sum(value) over ()
  from table
)
where state=0

would do the job. My questions:

Is there a better way to fetch the desired result?
Does this query perform better than the classical subselect-solution at all?

(I am using DB2 z/OS. Unfortunately, I do not have permissions to use explain on this installation)

Edit: Sample data and expected results:
 Consider the table

id      subid       state       value
-------------------------------------
 1          1           0           1
 1          1           0           2
 1          2           1           4
 2          1           0           8

The expected result would be a table which, for each ID, sums the values of all entries and the values of all entries where state=0.

 id     sum w/ state=0      total sum
 ------------------------------------
 1                   3              7
 2                   8              8

Given your sample data, I don't think it is necessary to use OLAP functions. Something like:

select id, sum(case when state = 0 then value end) as sum_w_s_0
     , sum(value) as total_sum
from t
group by id

But in the subject, you mention running totals, which usually means a cumulative sum of some kind. Can you add more sample data in case this answer does not achieve what you want?


Oracle insert max from two date I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like

Insert into MYTABLE (DATE_INSERT)
VALUE (MAX(DATE_DEFAULT, DATE_INSERT_VALUE))

where DATE_DEFAULT and DATE_INSERT_VALUE are local variables of type DATE.  The MAX function, however, is an aggregate function so I can't pass two arguments to it.

It sounds like you want the GREATEST function

INSERT INTO my_table( date_column )
 VALUES( GREATEST( date_default, date_insert_value ));


Windows patching on 3 node Always On Availability Group We have a three node Always On Availability Group cluster. We normally patch the passive nodes (N2, N3), fail over from N1 to N2, patch N1, and then fail back to N1. N1 and N2 are sync, N3 is async.

I am in a argument with our colleagues on weather to fail back to node 1 after patching.  My colleagues say we have to do this, so that we make sure Node 1 is functional.  I am of the opinion that we keep SQL Server on N2 so that we avoid another interruption of failing over to N1.

I was wondering if the practice of failing back to N1 to make sure the node is healthy is warranted or just useless?

To be honest there is no definite answer to your question.

If you do the last fallback to N1 you tested it on all available nodes. Within limits, more testing seems to be better to me. But to be honest it might not be absolutely necessary.

Leaving it on N2 will get you the added benefit of using the N2 node for primary workload until the next update.

It might even be feasible to do the fallback to N1 and then do another failover to N2 to test if N1 is running and then have N2 as primary until the next update.

In conclusion: I don't think it is absolutely necessary to the fall back to N1 but if you can afford the time during your maintenance window I would consider just doing it (and maybe even do the failover to N2 afterwards).


What is the impact on a running database when OS date is changed and set to past My SAP back end db is db2. Need to understand the impact on this running database (db2 under HPUX) if I change the OS date and set it to a past date please.

Thanks.

That's an extremeley broad question.  If your DB's tables use date in the primary keys you could run into duplicate problems.  If you query by date range you may have duplicates or holes in your data.  You may even have difficulty saying which row was written before another, depending on your design.  Reproducing any previous state may become impossible e.g. "What was the customer's balance on the 1st January?"  and so forth.

Really, you need to understand your application and DB design deeply, and test, test, test.


How to monitor activity in SSMS only with T-SQL? I use the following statement to monitor activity in SQL Server (This is out of some book)

SELECT
    des.session_id ,
    des.status ,
    des.login_name ,
    des.[HOST_NAME] ,
    der.blocking_session_id ,
    DB_NAME(der.database_id) AS database_name ,
    der.command ,
    des.cpu_time ,
    des.reads ,
    des.writes ,
    dec.last_write ,
    des.[program_name] ,
    der.wait_type ,
    der.wait_time ,
    der.last_wait_type ,
    der.wait_resource ,
    CASE des.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommitted'
        WHEN 2 THEN 'ReadCommitted'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END AS transaction_isolation_level ,
    OBJECT_NAME(dest.objectid, der.database_id) AS OBJECT_NAME ,
    SUBSTRING(dest.text, der.statement_start_offset / 2,
    ( CASE WHEN der.statement_end_offset = -1
    THEN DATALENGTH(dest.text) ELSE der.statement_end_offset
    END - der.statement_start_offset ) / 2 + 1)
    AS [executing statement] ,
    deqp.query_plan
FROM sys.dm_exec_sessions des
LEFT JOIN sys.dm_exec_requests der
ON des.session_id = der.session_id
LEFT JOIN sys.dm_exec_connections dec
ON des.session_id = dec.session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE des.session_id <> @@SPID
ORDER BY 3

Some activities are not shown here: create index, alter table...

How to monitor all activities in SQL Server? (Activity Monitor/Profiler is no option - only plain T-SQL, no sysadmin - only view server state)

Here's a few popular ways to do it:

sp_WhoIsActive

Pretty much the industry standard tool from Adam Machanic. Download it from WhoIsActive.com, install it in the master database (so you can call it from anywhere), and then run it whenever you want to know what's running now. Here's what it looks like:

Queries are sorted from longest-running to shortest. You can click on the sql_text or query_plan to see that stuff (note that the latter is only included when you run it with @get_plans = 1). It'll also show you who's blocking who, what machine the queries are coming from, and much more.

It's free, and it works with all currently supported versions of SQL Server. It's used by thousands of database professionals all over the world. I've used it myself in production all over the place, and never had a problem with it.

sp_BlitzWho

Erik Darling wrote this because he's really into query tuning, and he wanted to see more details than sp_WhoIsActive provides. Starting with SQL Server 2012, Microsoft has added a ton of cool DMV info like memory grant utilization, and Adam hasn't been updating sp_WhoIsActive to leverage it. You can get sp_BlitzWho from the public Github repo or from our First Responder Kit. Here's what it looks like:

At first, it basically looks like sp_WhoIsActive, but here's some of the additional columns it shows:

Parallelism - last_dop, min_dop, max_dop, last_reserved_threads, min_reserved_threads, last_used_threads, and more
Memory grants - last_grant_kb, min_grant_kb, max_grant_kb, last_used_grant_kb, min_used_grant_kb, max_used_grant_kb, last_ideal_grant_kb, and more
Resource Governor - workload_group_name, resource_pool_name

The data can be pretty overwhelming - there's a lot of it - so start with Adam's sp_WhoIsActive, and only graduate to sp_BlitzWho if you need to do live query tuning.

sp_BlitzFirst

I wrote sp_BlitzFirst to go a little deeper and ask, "WHY is the server slow right now?" It takes a snapshot of a bunch of dynamic management objects, waits 5 seconds, takes another snapshot, and compares the difference between the two to figure out what's happening now. You can get it from our site or from the public Github repo. Here's what it looks like:

It gives you a prioritized list of server issues, and URLs to copy/paste into your browser to learn more about each problem. For example, in the screenshot above, the server's slowing down because some meatball is running a backup right now. You can click on the "ClickToSeeDetails" line to see, uh, details:

Backup of StackOverflow database (93GB) is 0.896017% complete,
has been running since Sep 22 2017  5:19AM.
Login: Brent
Over the last 60 days, the full backup usually takes 4 minutes.

This way, you can give this tool to less-experienced folks like your help desk and have them do some initial triage.


Model with variable number of "properties" of different types. I would like to have a table of microscope slides, where on top of the basic id, name,date_created would each slide also have a number of editable "properties".

Imagine one slide having

tumor_type
dye_type
description
date_injected

And some other slide could have for example:

patient_name
treated_since
description

I can't store all these in the slides table because I would like users to be able to chose used properties or add new ones if they wish to. Each property should also have a type, since some are integers, some text and some for example date and time and I would love to render a widget for them to simplify editing these values.

I planned to use MySQL, then I was pointed to use use PostgreSQL and its hstore, but that feels like a borderline experimental feature to me. Any suggestion how should I go about it?

This is what I came up with but it doesn't feel right to me:

Also all this will end up in Django (Python) if it makes any difference.

hstore is ideal for this job, and isn't in any way experimental; it's been a part of PostgreSQL since 8.3 and is way easier to use in 9.1 and above thanks to CREATE EXTENSION.

The only real issue with hstore is that not all client drivers support converting hstore values to native maps or dictionaries, so you might need to get the properties for an entry as a table using the hstore sql function each, effectively converting the hstore to subtable result.

The only real alternatives I see are:

A simple master-detail table in EAV style. That'll work fine, it's just a bit annoying to query, you'll be using a lot of string_agg or array_agg calls and it's lots slower than hstore. This approach is completely portable across database systems.
Storing the properties as xml, which can be easier to parse from some applications and is sort-of indexable using functional indexes on xpath expressions. This approach is somewhat portable across database systems, in that it should work with any SQL/XML database.
Storing the properties as json, which is easy to consume from most applications but is currently non-indexable and pretty opaque to SQL at the moment (expect improvements in future Pg versions). This approach is somewhat PostgreSQL-specific, but you can use a text/memo/clob field to store json in any database.

As for re-using properties that're already defined, that'a a simple matter of maintaining a table of known property names. If you want you can generate it on the fly using SELECT DISTINCT skeys(properties) FROM thetable; queries on the properties hstores, but that'll be quite slow so I'd recommend maintaining a separate table.


adding another table to existing MySQL replication scenario I'm running a simple MySQL replication scenario MASTER  ➙  BLACKHOLE  ➙  SLAVE, replicating only a number of tables via replicate-do-table on BLACKHOLE and SLAVE.
Now I would like to add several more tables (that already exist and filled with data on MASTER) to existing replication, with minimum downtime on MASTER and SLAVE.
I have general understanding of steps to undertake in order to have a consistent snapshot (lock master tables, stop replication, dump&source etc), but this process includes 10+ steps and prone to human mistakes, especially if we set up more slaves in future.

Question: is there an easier option to add new table(s) to replication? Like using

mysqldump --master-data ...

and then somehow start replication of a particular table from a certain binlog position?

Thank you.

Are you using InnoDB? Xtrabackup will allow you to do "hot" or "online" copies of your running master.

Propagate the backup made via xtrabackup to the slave. There is no need to disrupt the relay (blackhole) server, just add the extra tables to be replicated to the relay server config before you "START SLAVE" on your slave.


How do I completely reset ServiceBroker including all messages, queues, triggers, etc? I have a database where I have a ServiceBroker up and running. For unknown reasons we have experienced a lot of problems with missing conversation handlers;

I.e.

The conversation handle "D33EC01C-CEA9-E811-95C5-00155D009F03" is not found.

It is my belief that this has happened when DB has been restarted while conversations have been "open" if that makes any sense.

What I wish to do now is to "reset" the ServiceBroker, remove all conversations, messages, triggers(!) etc.

Unfortunately I haven´t seen any documentation on how to achieve this.

Ideas?

Its cause due to multi-trigger on the table which created by service broker.

Delete all trigger and start broker services or then try.


Why doesn’t a MySQL fulltext search “IN BOOLEAN MODE” fail when referencing a missing column? First, the background on how I discovered this issue. This happened when using MySQL 5.7 and the DB — as well as the related table — are using MyISAM.
I was debugging some code — and related MySQL queries — on a website I manage and found the bottleneck: It turns out a fulltext search was running a MATCH referencing a column that was not a part of the fulltext index for the table with 100,000+ rows. Hooray! I rebuilt the fulltext index adding the missing column, and suddenly a query that took 4.7 seconds to run ran in 0.0007 seconds!
But here is what baffles me: Why didn’t a MySQL fulltext search IN BOOLEAN MODE fail when referencing a missing column? Instead — in this case — it just ran slowly but gave correct results. Why didn’t it just fail? Is this a bug or a feature?
I mean, when I ran the same query in other fulltext modes such as these:

AGAINST ('Happy' IN NATURAL LANGUAGE MODE)
AGAINST ('Happy' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)
AGAINST ('Happy' WITH QUERY EXPANSION)

I immediately got this MySQL error:
#1191 - Can't find FULLTEXT index matching the column list

To me, a failure like that would have been more useful than MySQL seemingly doing a full table search. Instead, the query was simply degraded to being accurate but extremely slow.
The initial fulltext index was built something like this; note the missing MOOD column:
ALTER TABLE `bigmoods`
  DROP INDEX `bigmoods_fulltext_idx`,
  ADD FULLTEXT `bigmoods_fulltext_idx`
  (
    `FIRSTNAME`,
    `LASTNAME`
  );

The query was something like this:
SELECT
  SQL_CALC_FOUND_ROWS
  FIRSTNAME,
  LASTNAME,
  MOOD,
  MATCH (
    `FIRSTNAME`,
    `LASTNAME`,
    `MOOD`
  )
  AGAINST ('Happy' IN BOOLEAN MODE)
  AS search_score
FROM
  bigmoods
WHERE
  MATCH (
    `FIRSTNAME`,
    `LASTNAME`,
    `MOOD`
  )
  AGAINST ('Happy' IN BOOLEAN MODE)
GROUP BY
  LASTNAME
ORDER BY
  LASTNAME ASC
LIMIT 0,100
;

This is documented behaviour:

InnoDB tables require a FULLTEXT index on all columns of the MATCH() expression to perform boolean queries. Boolean queries against a MyISAM search index can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow.

Why this exception exists (which is what you are asking in a comment) is speculation and something to be answered by the original developers, but there is something that you cannot do without this exception:
a boolean search that spans multiple tables.
For a natural search, you could compensate with or over separate matches (one for each table), for a boolean search, it would fall into the range of "much harder" to "impossible", which could explain the asymmetry.
If that was added as a nice feature to the (MyISAM) boolean search, and at least one user relied on it, you can make an argument for not removing it, as the only disadvantage of not forcing an index is that it could be slow if you make the mistake of forgetting the index (which can and will happen with normal indexes too, see 90% of SQL performance questions on Stack Overflow).
MySQL is really not in the business of protecting users from themselves, see e.g. only_full_group_by=0 (that can give you unintended results if you are not careful) or grant (that, before MySQL 8.0, could create a new user without a password if you are not careful).


Is there a way to determine what caused the last growth operation of my database (MDF file)? If I don't already have any logging in place, is there any way to use the DMVs or anything else on SQL Server to determine which query / event caused my database's last grow operation?

You could use the undocumented function sys.fn_dblog filtering by the collumn transaction name like this:

SELECT [Transaction Name]
FROM sys.fn_dblog(NULL,NULL)
WHERE [Transaction Name] IS NOT NULL;

On the query result you should look for SetFileSize

The line before the SetFileSize will show the last event that occured to cause the growth.


Classifieds Database Design I have always worked with CMSs, but I am trying to get into using frameworks like Laravel and Yii.  My main issue is when working with CMSs, I didn't have to think much about the database design since it was done for me.  I have my plan drawn out on paper, but I am not really sure where to go from here....

I am trying to develop a Craigslist clone, but a little more specific.  I have Googled all over for designs, and this is currently what I have.

However, I want certain categories to have specific fields.  Some categories may have fields in common with other categories, but not all categories are the same.

For example:

    - Categories
      - Electronics
        - Title
        - Type
        - Description
        - Year
      - Furniture
        - Title
        - Type
        - Description
        - Brand
      ....

Those are just two examples, but I have a huge list of Categories and the required fields for each category.

My current plan is to load all of these fields into the ad table.  What effect will this have on performance?  At some point there could be 60 fields attached to the ad table, but only 5-10 may be filled at a time, and the others would be empty/NULL.

What is the best way to go about associating images with ads?  I was thinking to just create an assets folder and create subfolders based on the ad id, and upload images to those subfolders of the corresponding ad id. Something like...

    - Public
      - Assets
        - Images
          - 1 (post id = 1)
            - img001.jpg
            - img002.jpg
          ...

What's the best way to set up this kind of database?  Would sticking to MySQL be best for this? What if I want some states to have certain categories but not others?

You need to decide if your categories are hierarchical (one parent) or graphical (multiple parents). For example, are "cell phones" in the category "electronics" as well as "communications"?

What kinds of fields might your categories have? Use Table Inheritance to allow categories that are similar but not identical.

Let's say you go with hierarchical categories. It should look like this:

-- hierarchical relationships usually have a single table with a field with a self-referencing foreign key:
create table category (
  id bigserial primary key,
  name text not null unique,
  parent_id bigint null references category(id) --if I'm null then I am a top-level category
);

create table ad (
  id bigserial primary key,
  subject text not null
  ...
);

-- this pattern is called a junction table:
create table ad_category (
  ad_id bigint references ad(id),
  category_id bigint references category(id),
  primary key (ad_id, category_id)
);

I would suggest Postgres over MySql, since it supports WITH queries, which make hierarchical querying easier.

Regarding images, consider storing them in Amazon S3 / Cloudfront. You would associated them with the ad via the HTML in the ad. For example, in your UI, the user would upload an image, get the URL back, and this would get plugged in to an <img> tag.

Though you could also create a junction table similar to the ad_category table, and remove orphan images from S3 on a schedule:

create table ad_picture (
  ad_id bigint not null references ad(id),
  picture_url text not null,
  unique (ad_id, picture_url)
);


MySql doesn't use extra CPU A server running only a MySql 5.7.30 instance consumes on average around 25% of its CPU, having over 100 threads connected, and of which generally 15-25 are running threads. The CPU in the server has 8 cores / 16 threads, and the load on CPU seems to be distributed over all cores/threads well.
Now a second identical CPU has been added to the server, but we notice that the second CPU does not get any load on any of its cores/threads. Are there any settings I need to adjust, to tell MySql to make use both CPU's?
Here are some settings from the my.ini on the machine:
default-storage-engine=INNODB
log-output=FILE
table_open_cache=2000
thread_cache_size=10
myisam_max_sort_file_size=100G
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
open_files_limit=4161
table_definition_cache=1400
binlog_row_event_max_size=8K
max_connections=301
tmp_table_size=6G
myisam_sort_buffer_size=12G
key_buffer_size=11M
innodb_buffer_pool_size=20G
innodb_log_file_size=2G
innodb_thread_concurrency=33
innodb_open_files=2000
join_buffer_size=4M
max_allowed_packet=32M
sort_buffer_size=512K
event-scheduler=ON
max_heap_table_size=32M
query_cache_type=1

In MySQL one connection uses one CPU core.  Period.  End of discussion.
Well...

InnoDB has some background threads, for example for handling I/O, purging, pruning, etc.
MySQL 8.0.xx has a very limited number of queries for which it will spawn multiple threads.

average around 25% of its CPU,

Yeah, MySQL is very fast at performing well written, well indexed queries.  So, thousands of qps might use only a fraction of a CPU.  CPU is rarely the limiting factor in MySQL systems.  When it is, usually adding a composite index "fixes" it.

distributed over all cores/threads well

That's up to the OS.  Just note that each connection lives in a "thread" (or "process") and it up to the OS to allocate CPU cores to threads.
If you are averaging 25% of one CPU, the OS rarely has a good reason to wake up the other CPU.  Now we are getting into gory details of hardware -- NUMA, affinity, etc, etc.  It may be power consumption, non-uniform memory access, etc.
Even if you get it to use both CPUs, each at about 12.5%, users may not see any improvement in latency.
Multiple threads:

May be blocked waiting on I/O -- which probably has only one way to get to disk
May be blocked on various Mutexes -- At somewhere around 64 threads, this becomes a problem.  Not much of an issue at 25.
May be blocked for transactional integrity -- effectively serializing certain types of actions; hence additional CPUs are useless.

What's the real goal??

More throughput?  Either you have not hit the limit yet, or you are I/O bound.
Lower latency?  The extra CPU, even if used, won't help much.

Either of those may be helped by improving the queries.  Turn on the slowlog; use pt-query-digest; then let's discuss the worst couple of queries.


Dynamic SQL in a pivot table I was watching a video on pivot tables and got to thinking, why can't we just push in a sub query into the IN clause, instead of declaring the expected columns statically? Well, turns out you cant haha, but I wasn't satisfied with that so after some research found that Dynamic SQL will allow you to do something similar. Now, I'm not expert on Dynamic SQL by far, but for the most part I understand what is going on. I am, however, having an issue with a specific query. Here is the query:

DECLARE @SQLString AS NVARCHAR(MAX)
        , @custID AS NVARCHAR(MAX) ;
SELECT @Custid = ISNULL(@custid + ',','') + QUOTENAME(custid)
FROM (SELECT Distinct custid FROM Sales.Orders) AS custid

SET @SQLString = N'
With ptable AS
(
SELECT empid, custid , orderid
FROM sales.Orders
)
SELECT empid, ' + @custID + N'
FROM ptable
PIVOT (COUNT(orderid) For custid IN(' + @custID + N')) AS pivtable
ORDER BY empid'
EXEC @SQLString;

The idea here is to have all of the distinct customer ID's found in the order's table as a separate column to track all employee-customer orders. (This is all for practice and in the name of figuring out how SQL works). This table happens to have 91 unique customers. Here's the problem, it works for the first @CustID in the SELECT statement, however for the second @custid in the IN clause, it only returns the first 16 and a missing ] after the 16. Here is the outputted error message:

  Msg 203, Level 16, State 2, Line 16 The name ' With ptable AS (
  SELECT empid, custid , orderid FROM sales.Orders ) SELECT empid,
  [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[58],[59],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84],[85],[86],[87],[88],[89],[90],[91]
  FROM ptable PIVOT (COUNT(orderid) For custid
  IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16'
  is not a valid identifier.

I am struggling to figure out why it worked for the first select clause then failed on the second one. I know this is a ridiculous example, but I guess my curiosity got the better of me and I just want to know. Any help is appreciated!

You are saying:

EXEC @SQLString;

This is like saying:

EXEC N'SELECT 1;';

Which is probably something that looks like it will work on first glance, but go ahead and try it, it fails. You want:

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT ...;';
EXEC sys.sp_executesql @sql;

I wrote a pretty detailed article on dynamic pivot here:

https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

But as for dynamic SQL, please see:

https://sqlblog.org/2011/09/17/bad-habits-to-kick-using-exec-instead-of-sp_executesql
https://www.mssqltips.com/sqlservertip/3637/protecting-yourself-from-sql-injection-in-sql-server--part-1/
https://www.mssqltips.com/sqlservertip/3638/protecting-yourself-from-sql-injection-in-sql-server--part-2/


Partitioning a table with LOB columns in Oracle 11g we have a fairly small table (~25k rows) that is being heavily written to and most of the rows go away quickly (it's part of a scheduling application which stores information about the jobs in that table).

It's a third party application which does a huge amount of small transactions which result in a high contention on that table.

This is all running on a two node RAC cluser using Oracle 11gR1

We can see in the AWR reports that most of the time is spent waiting for the cluster synchronization.

We would like to (hash) partition that table in order to distribute the contention on the blocks being modified.

However the table also contains a BLOB column which is used by the application to store the state of the job. We also see a high volume of reads and writes to the LOB segment for that column.

My question is: when we partition the table, is there a way to create multiple LOB segments as well? So that there is a one-to-one relation between table partitions (i.e. segments) and LOB segments?

I didn't find anything in the manual, but as the documentation for CREATE TABLE is sooo huge I wouldn't be surprised if I overlooked something.

Yes, it seems you overlooked it indeed:

PARTITION BY HASH(X)
(PARTITION P1
    TABLESPACE T1
    LOB (my_lob_column) STORE AS (TABLESPACE T1_LOB  /* here */ ),
 PARTITION P2
    TABLESPACE ts2
    LOB (my_lob_column) STORE AS (TABLESPACE T2_LOB))
)


Effect of changing data types with row compression enabled The company I work for has a couple of SQL Server databases with tables containing +- 500.000.000 rows. We are running Enterprise editions of SQL Server 2008R2 and 2014.

Big data types

When I look at the used data types in the largest table I see a lot of BIGINT columns. Examining the data in those columns with a script from Thomas Larock and scripting the MIN() and MAX() values myself I concluded that data in those BIGINT columns can easily be fitted in INT or even SMALLINT/TINYINT columns. (I'm aware that some columns could need the range of BIGINT in the future,so I'm not blindly changing all the data types without talking to developers first)

When comparing the possible savings when changing the datatypes it seems the table could be half the size it is currently (not even considering indexes and other tables). These numbers are without any data compression.

ROW Compression

On the large table ROW Compression is enabled. I'm wondering what the actual impact of 'downsizing' the columns datatypes might be, keeping in mind that ROW compression is using only the bytes that are needed. For example, if a value can be stored in 1 byte, storage will take only 1 byte.

Actual question

Would it help to downsize data types, so the ROW compression uses less resources? Or is it save to say 'because ROW compression is enabled there is no difference between BIGINT, INT or SMALLINT data types'?

As the documentation you alredy have linked states, ROW compression uses only the bytes that are needed. Once ROW compression is in use, the CPU cycles used to convert from/to int or bigint are the same: I would not worry about that.

BTW, if you're not sure whether int/bigint has an impact on the database size or not (it hasn't), you can see for yourself with a quick and dirty repro:

USE tempdb;
GO

CREATE TABLE SomeTable (
    SomeColumn bigint
)
GO

ALTER TABLE [dbo].[SomeTable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);

INSERT INTO SomeTable
SELECT TOP 10000000 ROW_NUMBER() OVER (ORDER BY(SELECT NULL))
FROM sys.all_columns AS A1
CROSS JOIN sys.all_columns AS A2;
GO

-- Rebuild the heap, so that pages compact nicely
ALTER TABLE dbo.SomeTable
REBUILD
WITH
(
    MAXDOP = 1,
    ONLINE = OFF,
    FILLFACTOR = 100,
    PAD_INDEX = OFF
);
GO

SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 21009 pages used

ALTER TABLE [dbo].[SomeTable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);
GO

SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 13587 pages used

ALTER TABLE SomeTable ALTER COLUMN SomeColumn int;

-- Rebuild the heap, so that pages compact nicely
ALTER TABLE dbo.SomeTable
REBUILD
WITH
(
    MAXDOP = 1,
    ONLINE = OFF,
    FILLFACTOR = 100,
    PAD_INDEX = OFF
);
GO

SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 13587 pages used (same as bigint)


Real-time deduplication strategy for relational DB I have a messages table with some fields like id, version, timestamp

I'm trying to insert and retrieve messages with real-time deduplication logic, implemented in stored procedures.

On insert, if a message with given id already exists in the table, I invoke a complex deduplication logic (a number of IF/ELSE statements) and then update messages's version, timestamp, etc. Otherwise I insert a new row.
On retrieve, I search for the oldest message and not just select it, but also update a few fields (changing state, setting transient_token, etc)

The problem is, it does not work as fast as I need: ~ 200 rps on insert and the same on retrieve

Separately my stored procedures work good, but when I execute both of them concurrently, they start fighting for locks under load > 30 rps, performance dramatically decreases and deadlocks appear periodically

Are there some best practices to solve my problem? I feel that the whole approach sucks, not my concrete implementation.

May be I should use some temp buffer table for all incoming messages and perform dedup logic afterwords? I would appreciate any ideas.

* I can't use nosql DB in my case (management decision), I need to gain max performance with Mysql/MariaDb

Good news. There is a SQL command usually called MERGE or UPSERT that basically does this.

See this page for more information:
https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

You probably want something like:

INSERT INTO messasges (id,version,timestamp) VALUES (1123,1,1/1/2017)
  ON DUPLICATE KEY UPDATE version=2;


what is relation between sql server back file size and space required for restoring database I am using sql server 2008. I have taken full backup of my database. Its backup file file (.bak) is around 750 MB. Sql Server is installed on c drive which contains 5 GB free space.
When i try to restore my database it get following error :

  Restore failed for Server 'FQA2008'.
  (Microsoft.SqlServer.SmoExtended)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

  ADDITIONAL INFORMATION:

  System.Data.SqlClient.SqlError: There is insufficient free space on
  disk volume 'C:\' to create the database. The database requires
  12304515072 additional free bytes, while only 5931470848 bytes are
  available. (Microsoft.SqlServer.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476

Question: I dont understand that my bak file size is just 750 MB and how does it takes 12 GB of space for restoring database?

A few points to consider - some may apply in your case:

SQL Server backups only contain pages used

From Books Online:

  The backup contains only the actual data in the database and not any
  unused space.

This means that if your database has a lot of free space, the backup might be small.

You can check space usage using sp_spaceused or a view like sys.master_files.

Database restores recreate the data files exactly as they were backed up

Say you have a database with a 10GB data file and a 5GB log file, which you've backed up. When you restore the database, it will recreate these files as they were when they were backed up; it won't change the data layout in any way.

So it seems like your original database had a 12GB file - when restoring this file is being recreated.

Compression can be applied

Depending on your version/edition you can compress backup files (Enterprise only in SQL Server 2008) - this reduces the backup file size so might be misleading.

What can you do?

Easiest answer is to increase the size of your drive or restore to a different location.

Another option would be to look at the size of the original DB and consider if it has been maintained correctly, e.g. it may have a large transaction log file that hasn't been backed up correctly. Questions about appropriate database size and actions might be best suited to dba.stackexchange.com.


How to structure this data? (Many columns currently.) Working in MariaDB, we're trying to figure out how to store this data. All of our other tables have been normalized and are working nicely, but this has me stumped.

We have a number of objects and each object needs to have a state and two colors stored for each layer. The problem is that the upper limit of layers is around a hundred. Each object needs the data stored for each layer, as the objects are definitely not guaranteed to be identical in any way.

ObjID   | L0  |      L0C      | L1  |      L1C      | ... | L99 | L99C
8374629 | mmq | FFFF00,FFFF00 | mqm | 663FBA,34CF09 | ... | mqq | CDCDCD,00CDCD
8374630 | qmq | EE9329,00FF00 | qqm | 00FF52,ACF03D | ... | qqq | FFFF00,00F000
8374631 | mqq | 597842,F98520 | mmm | 05FF10,3726CD | ... | mmm | 05FF10,C0EAFF
8374632 | qqm | FADD03,A6A6A6 | qmm | DDAA11,763276 | ... | qmm | EEEEAA,EE9329
8374649 | mmm | BC1A44,FF00FF | qqq | 000000,FFCFFF | ... | mqm | 2323DD,01CD11

ObjID is always unique, but the rows are purged periodically as the object is no longer needed from the system. Lookup will always be based on ObjID. There will never be any filtering of the other rows (ie, selecting all where L0 is mmm).

The only other idea we've come up with is the idea of using strings to store the data and breaking them apart in the code that's using the data, but I assume that parsing a 1300 character string into a massive array would be both more inefficient and very inelegant. It also wouldn't allow  easy editing of any given layer, which needs to be available by design.

The objID is referenced on another table, but otherwise the data here isn't replicated in any other location. Is this our best option for storing this many unique points? Some two hundred columns just seems like quite a lot.

To normalise this table you must remove the repeating groups and multi-value columns. The resulting table will be

objID
LevelNumber
State
Colour1
Colour2

Using the sample data from your question you would see

ObjID   | Level  | State | Color1 | Color2
8374629 |  0     |  mmq  | FFFF00 | FFFF00
8374629 |  1     |  mqm  | 663FBA | 34CF09
...
8374629 | 99     |  mqq  | CDCDCD | 00CDCD
....
8374649 |  1     |  mmm  | BC1A44 | FF00FF
8374649 |  2     |  qqq  | 000000 | FFCFFF
...
8374649 | 99     |  mqm  | 2323DD | 01CD11

The colors can be normalised into a separate table, too, giving two tables:

State              Color
=====              =====
objID              objID
LevelNumber        LevelNumber
State              ColourNumber
                   ColourValue

A table with several hundred columns is within the capabilities of any DBMS. It may be strange but it is not nesessarily wrong.


What indexes to add on MySQL table I've been trying to optimize my table, but am yet unable to get the desired results.

CREATE TABLE IF NOT EXISTS `puzzles` (
  `puzzleID` int(7) NOT NULL,
  `puzzleMask` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `puzzleScore` int(7) NOT NULL,
  `time` int(11) NOT NULL DEFAULT '0',
  `hour` int(11) NOT NULL DEFAULT '0',
  `ip` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `Iip` int(11) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`puzzleID`, `puzzleMask`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

The combination of ID and Mask must be unique, whereas ID is a random integer and Mask a combination of numbers with a max length of 30. Time is the unix timestamp and hour is "time - (time % 3600)" (the hour in which the row was submitted). ip is the IP address of a server (about 20 servers) and Iip is INET_ATON( ip ).

These 20 servers submit puzzle information to this database. I would like to show live statistics of the submitted information (5-10 records per second).

For that, I use two main queries which need to be as optimal as possible:

Obtain total statistics

SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# GROUP BY hour

Obtain server dependant statistics

SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# AND Iip = INET_ATON("#IPAddress#") GROUP BY hour

My question is, what indexes to use? I've tried many things now, but with the database rapidly increasing the time to trial and error is over...
for example, do I need a seperate index for puzzleScore, since I want the max? or do I need to combine it in another index?

Also, is it true that using a primary key (puzzleID and puzzleMask) in combination with INSERT IGNORE INTO... is better for the performance, than using an id with autoincrement and first perform a SELECT to see if the record exists and than insert?

Thank you

What have you tried?  Are both of these queries running slowly?  Are there other operations which have become a problem?  Since you have a live system, rather than speculating about what might be causing a problem, you can add some profiling to be sure.

Your first query can be answered by an index on ( Hour, PuzzleScore ), in that order.  Your second would require adding Iip; since it should have excellent cardinality, I'd put it first: ( Iip, Hour, PuzzleScore ).  Check with EXPLAIN to confirm whether the indices are actually used; depending on row counts and cardinality, there may be no benefit.

Since you're querying on Hour either way, I'd consider clustering on that field (in conjunction with a unique field or combination of fields to ensure uniqueness, of course).

Caveat: I mostly work in SQL-Server, I'm no expert on MySQL.


How do I create a bounded range type in the SQL Server? I want to create a bible verse table that has one field representing a range of bible verses.  Currently I represent this range as pair of values (VerseBegId and VerseEndId).  I want something like the Sql Server spatial data type

Instead of

CREATE TABLE BibleVerse AS TABLE
(
    VerseBegId int NOT NULL,
    VerseEndId int NOT NULL
)

I want

CREATE TABLE BibleVerse AS TABLE
(
  VerseRange ??? NOT NULL
)

Besides SQLCLR, you could also store both values in either XML or JSON (if you are using SQL Server 2016 or newer) and retrieve them as discrete values. Similarly you could simply convert them to VARCHAR and place them, separated by a comma, in a single column (I don't recommend this at all, but it is functionally similar to the other approaches I just mentioned). The SQLCLR approach makes it easy to get the discrete values back out, and in their native datatypes, but you could just as easily write a few helper UDFs to do the same with those values shoved into XML or JSON.

In all of these cases, while you could accomplish the goal of storing two values in a single column, I don't see what you gain by doing this given that each approach has some amount of overhead yet the usage doesn't seem to justify it. The main reason for creating your own type isn't to merely associated related data points, but to have the ability to act upon them. The internal CLR types not only store the discrete data points, but also offer quite a few methods to do interesting things with those data points. I am not sure what you can do operationally with just beginning and ending numbers that don't have commonly used algorithms for making use of them. It seems that you will be doing more work to get the values back out (and most likely decrease performance while doing so) than you will be gaining by putting them in such a structure.

Unless you can clearly see a guaranteed benefit from doing this, I suggest you keep them as discrete data points. And if my memory serves me correctly, you should be able to get away with using TINYINT, assuming no verse # goes above 255 (and they certainly wouldn't be negative or NULL). Looking here ( New Testament Statistics ) and here ( Old Testament Statistics - New American Bible ) it seems that the highest verse number is 80 for the New Testament (Luke chapter 1), and 176 for the Hebrew Bible — "Old Testament" in Christian terminology — (Pslam 119).


How to perform server-side transaction timeout if COMMIT TRANSACTION will never arrive? How can SQL Server terminate one of its transactions if it exceeds preset timeout?

Imagine the following transaction performed by traveling salesman's SQL client application, which is submitted command-by-command (i.e. not as single batch) and normally takes 2 seconds:

BEGIN TRANSACTION
    INSERT INTO PurchaseOrder <purchase order header>
    INSERT INTO PurchaseOrderLineItem <line item 1>
    INSERT INTO PurchaseOrderLineItem <line item 2>
    INSERT INTO PurchaseOrderLineItem <line item 3>
          :
    INSERT INTO PurchaseOrderLineItem <line item 99>
COMMIT TRANSACTION

Now, if mobile connection permanently drops1 during sending line item 51 (thus dropping the link to SQL server etc.), ABORT command will never arrive from client side. How can SQL Server time out and rollback the transaction when client which opened the transaction is permanently lost? (Condition example: no new command arrived for 30 seconds in that transaction.)

The problem I'm trying to avoid is that all other SQL commands accessing PurchaseOrderLineItem table will wait infinitely for the one described above. How to clean way for other queued commands? (If the client application is unable to submit entire transaction batch at once.)

I can see advices to set client timeout but they are of no help if client is permanently lost.

__

1) other possible accidents leading to problem: application crashes and exits without cleanup, PC goes to blue screen of death, laptop runs out-of-battery, network device stops responding etc...

The underlying OS is capable of detecting all the above mentioned possible disconnects and break the communication channel (TCP, net pipe etc). This will result in the rollback. Don't try to outsmart this.

Of course, one could ask why not

use a reliable queueing communication channel (eg. MSMQ)
submit the entire order in one call rather than 100 calls
commit the order line by line in 'pending' state

Any of the above would work. 30 second transactions would not work.


Optimize Query with Derived Table I have the following query

select
ps.id, ps.title
, IFNULL(s.likes,0) as num_likes
, IFNULL(s.comments,0) as num_comments
, IFNULL(s.ratings,0) as num_ratings
, IFNULL(s.views,0) as num_views
, IFNULL(s.avg_rating,0) as avg_rating

from  ps
left outer join (
    select presiid,
    sum(views) as views,
    sum(likes) as likes,
    sum(ratings) as ratings,
    avg(ratings) as avg_rating,
    sum(downloads) as downloads,
    sum(comments) as comments,
    sum(embeds) as embeds,
    sum(shares) as shares
    from tblstatistics
    group by presiid
) s
on s.presiid = ps.id

where ps.active = 1
order by
datepublished desc
LIMIT 0, 12

Running EXPLAIN on the above shows this

1   PRIMARY ps  ref active  active  1   const   402 Using temporary; Using filesort
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    334
2   DERIVED tblstatistics   ALL NULL    NULL    NULL    NULL    2643    Using temporary; Using filesort

As you can see this is not good....tblstatistics is not using any indexes and is scanning the entire table. How can I optimize this query and make it use indexes? There are over 100,000 rows in the DB on the production DB where this query runs.

Any help will be appreciated....

Your left outer join looks to be condensing your 100,000 rows in tblstatistics into approximately 2,643:

2   DERIVED tblstatistics   ALL NULL    NULL    NULL    NULL    *2643*    Using temporary; Using filesort

and then it places that grouping in into a temporary table. At this point, it applies the aggregate functions (SUM, AVG, etc).

Unfortunately, since you are not limiting your tblstatistics with a WHERE statement, this is probably the best you can do.

Overall, the only limiting you do is on ps.active=1. This is probably a really low cardinality column, and if it's an index, it provides no benefit as MySQL will ignore it anyway.


SQL SERVER 2008 TVF OR CHARINDEX to search column with comma ID    BlahColumn
1       2
2       NULL
3       2,3
4       4,5,6
5       1,2
6       1
7       1

Hello,  I am using the following as part of the where clause and using charindex is not working  correctly to search the above comma delimited column. i am planning to fix this by creating a separate table but for now i need a workaround. I Would really appreciate your help:

WHERE(@Param IS NULL OR
      CHARINDEX(','+@Param+',', ','+CAST(BlahColumn as VARCHAR(255))+',') > 0)))

IF @Param = '1,2' then it should return rows 1,3,5,6,7 and if it's something like 3 then it should return row 3.

my real stored procedure looks like this

 (SELECT DISTINCT
         S.ID, S.ServiceDateTime, S.ReasonForFailure, S.ActionTaken, S.VerificationPerformed, S.TSBNumber, S.ISANumber, SS.SystemFullName AS SystemName,
                      U.FullName AS TechnicianName, S.ErrorDescription AS ErrorDescription, S.ClosedDate, S.ServiceRequestClosed,
                      S.ServiceHours, S.ServiceMinutes, S.PartDescription, S.RequestAddParts AS AdditionalPartsRequested, ISNULL(S.Reconciled, 0) AS Reconciled,
                          (SELECT     FullName
                            FROM          dbo.[User] AS U1
                            WHERE      (ID = S.CreatedBy)) AS CreatedBy, S.CreatedDate,
                          (SELECT     FullName
                            FROM          dbo.[User] AS U1
                            WHERE      (ID = S.UpdatedBy)) AS UpdatedBy, S.UpdatedDate
   FROM ServiceEntry AS S INNER JOIN
    dbo.[User] AS U ON U.ID = S.TechnicianID INNER JOIN
                      dbo.System AS SS ON SS.ID = S.SystemID  LEFT OUTER JOIN
                      dbo.ServiceEntryErrorCode AS SEEC ON SEEC.ServiceEntryID = S.ID
   WHERE (S.RunLogEntryID is not null) AND (S.Reconciled=@ReconciledValue) AND (ServiceDateTime between @StartDate and @EndDate)
   and S.ID IN(SELECT SEP.ServiceEntryID FROM ServiceEntryPart SEP inner join Part ON SEP.PartID = Part.ID
   WHERE
  (@ServiceTypes is null or
   CHARINDEX(','+@ServiceTypes+',', ','+CAST(SEP.ServiceTypeIDs as VARCHAR(255))+',') > 0)
   AND
(@ActivePart is null or @ActivePart = Part.Active)

@gbn im using your solution first im trying to just run the query and then will include your solution in my above query:

The following works great,
    declare @param nvarchar(100) = null
    SET @param = '1,4'

SELECT *
FROM
  ServiceEntryPart M
  CROSS APPLY
  dbo.SplitStrings_Moden(M.ServiceTypeIDs, N',') M2
  JOIN dbo.SplitStrings_Moden(@param, N',') P ON M2.Item = P.Item

SO i  include the above query in my stored procedure and do something like this but, what im trying to do is find all service entries which have service parts, and the where clause is optional, so for instance if my @servicetypes param is NULL then I would not search against the parameter but only see if the service entries exist in the service entry parts. if the parameter is not null then i will search against the service type ids. please help, i have tried and not been able to find the correct result

S.ID IN(

SELECT DISTINCT ServiceEntryID
FROM
  ServiceEntryPart M
  CROSS APPLY
  dbo.SplitStrings_Moden(M.ServiceTypeIDs, N',') M2
  JOIN dbo.SplitStrings_Moden(@ServiceTypes, N',') P ON M2.Item = P.Item
  INNER JOIN Part ON M.PartID = Part.ID
  WHERE @ActivePart is null or @ActivePart = Part.Active)

You need a split TVF on both sides to make it a JOIN

SELECT DISTINCT
   M.ID
FROM
   MyTable M
   CROSS APPLY
   dbo.SomeSplitTVF(M.Blahcolumn) M2
   JOIN
   dbo.SomeSplitTVF(@parameter) P ON M2.value = P.value

There are several solutions for "dbo.SomeSplitTVF" on SO, but you could start with this article by our own Aaron Bertrand


Query returned successfully when obvious error should occur I have just installed postgresql and pgadmin on my Windows machine.
The problem is that whatever I enter in query window, it results with message:

  Query returned successfully in XX msec.

Even, when query is obviously incorrect, and should cause an error, no error message is displayed - like on screenshot:

What am I doing wrong?

According to this ticket, the workaround is to set English language for messages in postgresql.conf, e.g.

lc_messages = 'en_GB.UTF-8'

See also this question on SO.


SQL Azure Fragmentation / Database Size I have a database running on SQL Azure that is currently 280mb. It is a test database for a system that we're going to be putting into production soon so the data frequently gets deleted in batches and then recreated.

When I used the "Copy" feature on SQL Azure the new database that it created was only 156mb. When running a query to show how much data each table is using it appears that each table is pretty much half the size it used to be.

I've worked out that this is going to be down to data fragmentation, but my question is what can I do about this? It doesn't appear that Microsoft perform any maintenance on the data itself, and as it's a pay-per-use model I'll end up hitting the 1gb limit when I've not got 1gb of data!

For reference this is the query I ran to show table size:

select sys.objects.name, (reserved_page_count * 8.0 / 1024)
from sys.dm_db_partition_stats, sys.objects
where sys.dm_db_partition_stats.object_id = sys.objects.object_id

Matthew,

I don't have direct experience with SQL Azure but I think same rules apply here as a normal SQL Server instance.

280 mb is a very very small database and the cost of fragmentation is almost 0. Coming to the size of this small database, I don't think you can control that nor should you be worried.

The above is because when SQL Server creates a new table, it doesn't know how big will that table be. Data is stored in pages of 8 kb and an extent is a collection of 8 contiguous pages.
There are 2 types of extents, mixed and uniform extents. Uniform extent means all the 8 pages will be used by the same object (say TableA) and mixed extent means that this extent can be used by as many as 8 different tables. Since SQL Server doesn't know how big the table will be, it will start with single page allocations until the table reaches 3 extents and there after it uses uniform extents. This is the reason, fragmentation is high in small tables and but you shouldn't be worried abt it.

Instead of looking how big is the database, you should look at how much of it is empty. I am NOT sure how this affects the billing though. HTH


postgresql rename a foreignkey after renaming a table I renamed a table, and apparently a foreignkey in it doesn't get changed, and causing problems,
Although when I \d error I get:
"error_test_id_fkey" FOREIGN KEY (test_id) REFERENCES test(id) ON DELETE CASCADE

Which the only relation to error name, is the name (weird)

Anyway - I have no problem deleting the foreignkey and recreating it, but I can't figure out how to do it

If you are using PostgreSQL 9.2 or newer, you can use RENAME CONSTRAINT:

ALTER TABLE name RENAME CONSTRAINT "error_test_id_fkey" TO "the_new_name_fkey";


Architecting table with multiple paths to common parent tables I'm wondering how to architect a database when there are multiple paths (via foreign keys) to a common parent table.

For example I have the following tables:

Table: Students
PK StudentId

Table: Classes
PK: ClassId

Table: ClassStudents
PK: ClassStudentId:
FK: StudentId
FK: ClassId

Table: ClassAssignments
PK: ClassAssignmentId
FK: ClassId
FK: Assignment

Now I'm going to create a new table that store Assignments submissions from each student. What's the best way to structure it such that a student from another class can't get added to an assignment from this class?

TABLE: ClassStudentAssignments
FK: ClassAssignmentId
FK: ClassStudentId

Is there a way to handle this in the database design or would it just have to be done via the business logic?

Thanks in advance for any help.

You are thinking about this incorrectly. An assignment submission from a student not enrolled in the corresponding course, if it happens to get marked, is not fundamentally different from a properly submitted and marked submission from a student who subsequently drops the course. Allow the entry of the mark to exist in the database, by noting that when the final report of assignment marks for each course is generated, the (inner) join will fail for all submissions for students not enrolled in the course at exam time. That is sufficient to properly implement the business logic.


Filemaker Database. How to import to one table then copy information to a different table and populate realted tables I'm using filemaker to create a union membership database, the problem i'm having is that you can't import files into related tables. Basically i'm trying to figure out how I could import an excel spreadsheet containing member information and dues payment information and then populate the correct member and their related table information. I read something I thought might be helpful about magic keys and tried to implement that but i'm wondering if my parent table needs to be my flat import table.

You can import the raw data to a Temporary table.
Loop through each record.
Using Magic key, you can create both parent and related records.


Difference between storing a DBUnit file in version control and using Liquibase for Database Version control? What is the difference between storing a DBUnit database-loadable xml file in version control, and using Liquibase for database version control?

I'm relatively familiar with DBUnit having used it in Matt Raible's Appfuse project, and I've used it for unit testing in projects based on Appfuse.  It never crossed my mind before, but it would seem to me that it could also be used for Database version control.

Now Liquibase appears to be used for version control of a database, but I fail to see how another tool is needed for this when you can just pull database changes using DBUnit, and get your data with it as well (storing that file in version control to facilitate your db vcs needs.)

There must be a reason that both of these tools exist, but I can't understand what the difference in scope between them is, could someone please explain this to me?

From their own FAQ:
"Can I use DbUnit in order to check and execute DDL statements?
    No. DbUnit is not intended to track or test structural DB changes. For this purpose check out liquibase."

So it appears that DbUnit is useful for storing data differences between different versions of a database, while LiquiBase is for schema changes.


MySQL order of execution I am running a very long query described below.
It fetches the next needed action for each account on an automation system.

I.e.

SELECT Account.id,
(IFNULL(**Should send message query**,
    IFNULL(**Should check inbox**, NULL))) as nextTask FROM Account

In reality the string of IFNULL's is around 10, each is quite a complicated subquery.

I want to know if MySQL will compute the values for following IFNULL expression if the first is satisfied. That is, if an account should send a message, it shouldn't bother computing the subquery for Should check inbox

Is this how MySQL works?

What's the difference between this and CASE WHEN's

E.g.

CASE WHEN **Should send message** THEN **Should send message**
    WHEN **Should check inbox** THEN **Should check inbox**
END

I just want to get the CPU usage down for this query.

Breaking down your example, we have:

IFNULL(A,B)

This means: Evaluate A, IF A <> Null return A Else Evaluate and return B

CASE WHEN A THEN A
    WHEN B THEN B
END

Which means: Evaluate A if true then evaluate and return A (since we are using A as condition and return expression above). If B then evaluate and return B.

As such, it is best to use IFNULL if the condition we are testing for null is also the return value. Using case is better if you have a condition to test and a different return value.

Having said all that, your first example seems to be using nested IFNULLs. However, just doing IFNULL(**Should send message query**, **Should check inbox**) may be be enough since it just needs to get one or the other or null.

Finally, Are you running this statement for multiple records? If so, that may be the cause of your high CPU as putting sub-selects in the select clause will cause correlated sub-selects to run for each record (i.e if you are processing 1000 entries with your query, each sub select in the query will run 1000 times). At that point I would say to try to re-write the query to first get all the values for inbox and message (maybe two subqueries in the from clause or put them in a temp table) and then do the ifnull at the end. In the end, as usual check the execution plan of your query and see what's going on .


Predict how much hard drive space a query might cost? Once in a while I run into "No space left on the machine" after running a lengthy query. I also like to have a reasonable estimation, so that the IT team can purchase a correct cloud server. My question is: Given a query, is there a way to predict how much hard drive space it might cost? Is adding the size of involved tables a good enough estimation?

EDIT: Currently I am using Postgres 10. The query in question is about creating a materialized view and do some arithmetic, lag and group by on a single table with a size of around 750 GB.

Query looks like

CREATE MATERIALIZED VIEW foo AS
   WITH change_data AS (
             SELECT (
                      CAST(lagged_data.todays_count AS double precision) /                                      CAST(lagged_data.previous_count AS double precision)
                      - CAST(1 AS double precision)
                    ) / GREATEST(EXTRACT(
                        days from (date_belongs_to - previous_date_belongs_to)
                    ), 1) AS change,
                    lagged_data.date_belongs_to,
                    lagged_data.user_id
               FROM lagged_data
              WHERE lagged_data.previous_count IS NOT NULL
        ),
        lagged_change_data AS (
             SELECT change_data.change,
                    LAG(change_data.change) OVER (
                        PARTITION BY change_data.user_id
                            ORDER BY change_data.date_belongs_to
                    ) AS previous_change,
                    change_data.user_id
               FROM change_data
        ),
        acceleration AS (
              SELECT AVG(ABS(lagged_change_data.change - lagged_change_data.previous_change)) AS fgs,
                     lagged_change_data.user_id
                FROM lagged_change_data
               WHERE lagged_change_data.previous_change IS NOT NULL
            GROUP BY lagged_change_data.user_id
        )

It is hard to make a prediction, because that depends on many factors like the chosen execution plan.

You should first find out where the files are that keep growing.

If they are in the base/12345 subdirectory of the PostgreSQL data directory (the number will be different), it is the materialized view that is being built. That would mean that your disk space is not sufficient to contain the resulting object.
If the growing files are in base/pgsql_tmp, they are temporary files that the query executor creates to cache a large intermediate result.

That might be an indication that you forgot a join condition somewhere - hard to tell, as we only see part of the query.

Since CTEs are materialized before v12, it could also be the intermediate result of a WITH expression. You can try them out one after the other and see if they make things go boom.

Sorry, but it is not possible to give you more than these guidelines.


Innodb using key cache? As Innodb is my default engine, all my tables are innodb. But while monitoring with the help of a tool, I found a constant spike in key_write_requests and key_read_requests. These two variables are myisam variables. Will innodb internally use key cache for any process? Is it possible?

In addition to what Md Haidar Ali Khan said, ...
The "default storage engine" does not mean that you created all the tables in that engine.  You should verify with SHOW TABLE STATUS; for you database(s).  That will say, among other things, what Engine is being used.

innodb_buffer_pool_size, for an InnoDB-only system, should be set to about 70% of available RAM (if RAM is at least 4GB).  And set key_buffer_size to some small value, say, 20M because of system tables that are in MyISAM.  These system tables probably caused the spikes you saw.

More discussion of memory -- including what to do if you are using MyISAM.


How to perform JOIN and only use single record for each instance on 2nd table I am trying to perform a join from table 1.  In table 2 there could be multiple matches on the join, but I only want to use each match once and only once.  How do I do this?  Here is my data set:

Products

Product_Plan_ID      Product_Plan_Creation_Date
===============      =========================
123-3241             1/2/2018
123-3242             1/15/2018
123-3242             1/17/2018
123-3243             1/18/2018
123-3244             2/2/2018
123-3245             2/5/2018

AND...

Product_Delivery

Product_Plan_ID      Product_Plan_Delivery_Date
===============      =====================
123-3242             1/20/2018
123-3242             1/22/2018

Here is code to generate tables and data set...

CREATE TABLE Products
(
Product_Plan_ID varchar(25),
Product_Plan_Creation_Date date
)

create table Product_Delivery
(
Product_Plan_ID varchar(25),
Product_Plan_Delivery_Date date
)

INSERT INTO Products
select '123-3241', '1/2/2018'
union all
select '123-3242', '1/15/2018'
union all
select '123-3242', '1/17/2018'
union all
select '123-3243', '1/18/2018'
union all
select '123-3244', '2/2/2018'
union all
select '123-3245', '2/5/2018'

INSERT INTO Product_Delivery
SELECT '123-3242', '1/20/2018'
UNION ALL
SELECT '123-3242', '1/22/2018'

In my query, I want to join the Products table to Product_Delivery and for each row in Products, I want to join it to Product_Delivery matching on the Product_Plan_ID and where the Product_Plan_Delivery_Date is within 30 days of the Product_Plan_Creation_Date.  The kicker is, if I find a match in Product_Delivery, I only want that match to be used a single time.  The way I have it now, I'm ending up with duplicates on Product_Plan_Delivery_Date 1/20/2018 below.  Here's my query so far:

select Products.Product_Plan_ID, Products.Product_Plan_Creation_Date,
t2.Product_Plan_Delivery_Date from Products
CROSS APPLY (
    SELECT TOP 1 t.Product_Plan_ID, t.Product_Plan_Delivery_Date
    FROM Product_Delivery t
    where t.Product_Plan_Delivery_Date between
    Products.Product_Plan_Creation_Date and dateadd(dd, 30, Products.Product_Plan_Creation_Date)
and Products.Product_Plan_ID = t.Product_Plan_ID
) t2

The problem with the above is I end up with a duplicate match for Product_Plan_ID 123-3242 on Product_Plan_Delivery_Date, 1/20/2018.  How would I write this so I have Product_Plan_ID 123-3242 match first on 1/20/2018, then 2nd on 1/22/2018?

My desired result set would be this:

Product_Plan_ID    Product_Plan_Creation_Date    Product_Plan_Delivery_Date
123-3242           1/15/2018                     1/20/2018
123-3242           1/17/2018                     1/22/2018

However...I'm getting this:

Product_Plan_ID    Product_Plan_Creation_Date    Product_Plan_Delivery_Date
123-3242           1/15/2018                     1/20/2018
123-3242           1/17/2018                     1/20/2018

Thanks in advance.

For future posts, it is better to provide CREATE TABLE statements and INSERT statements. It is also beneficial to add an expected result set. What I guess you are trying to do is to pair Product_Plan_Creation_Date and Product_Plan_Delivery_Date. You can create an ordering number with a WINDOW function:

SELECT Product_Plan_ID, Product_Plan_Creation_Date
     , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Creation_Date) AS RN
FROM Products

and similar for Product_Delivery:

SELECT Product_Plan_ID, Product_Plan_Delivery_Date
     , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Delivery_Date) AS RN
FROM Product_Delivery

Now you can use the ordering number to join these two derived tables:

SELECT A.Product_Plan_ID, A.Product_Plan_Creation_Date
     , B.Product_Plan_Delivery_Date
FROM (
    SELECT Product_Plan_ID, Product_Plan_Creation_Date
         , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                              ORDER BY Product_Plan_Creation_Date) AS RN
    FROM Products
) AS A
JOIN (
    SELECT Product_Plan_ID, Product_Plan_Delivery_Date
         , ROW_NUMBER() OVER (PARTITION BY Product_Plan_ID
                          ORDER BY Product_Plan_Delivery_Date) AS RN
    FROM Product_Delivery
) AS B
    ON A.Product_Plan_ID = B.Product_Plan_ID
    AND A.RN = B.RN


Optimal indexing for SELECT (mysql) What indexes would you recommend for the following select (and why)? Keep in mind there can be tens of millions of rows.

SELECT * FROM mytable WHERE md5 = ? AND created_at >= ? AND created_at <= ? LIMIT 200

Where:

md5 = CHAR(32)
created_at = DATETIME

The question is: what indexes would you add?

SUGGESTION #1
Based on the WHERE clause, I highly recommend a compound index
ALTER TABLE mytable ADD INDEX (md5,created_at);

That way, all parts of the WHERE clause are answered by the index.
SUGGESTION #2
Since you are imposing a limit of 200, collect the 200 keys first, then join the keys
SELECT B.* FROM
(
    SELECT id FROM mytable
    WHERE md5 = ?
    AND created_at >= ?
    AND created_at <= ? LIMIT 200
) A INNER JOIN mytable B USING (id);

I have suggested this technique before in StackOverflow
GIVE IT A TRY !!!


How should I design a re-usable content like table? So I've got a database that holds articles, recipes, and comments in seperate tables, and I'm wanting to allow users to be able to like each of these content pieces (articles, recipes, comments).

I'm wondering whether it's a good idea to create a re-usable likes table which would look something like this:

| content_type | user_id | content_id |
| article      | 1       | 1          |
| article      | 1       | 3          |
| article      | 1       | 4          |
| comment      | 2       | 1          |
| comment      | 3       | 1          |
| recipe       | 2       | 1          |

So you'd have:

content_type:

which would represent either article, recipe, or comment

user_id:

which represents the user who liked the content

content_id:

this would be the primary key of the content piece (article, comment, recipe).

Is it a good idea to do this? There is the potential for millions of records to be inserted into the one table.

The alternative (which I am using right now) is that I have a table for each of the content types (articles, comment, recipe).

The thing is, I might be adding more content types to my database, which means the likes  schema I proposed might be a better idea.

Can anyone point out any cons of using a schema like this?

I think this is probably a good design that you proposed. Just make sure that the table is indexed properly. Consider that every query to the table will probably filter by content_type so that should probably be the leading column in a multi-field index.


Why is MySQL not using the index with the higher cardinality? I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22.  MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' and state = 'y'. Is this normal?  If not, what could be going wrong?

Cardinality

Even with high cardinality, the tipping point used by the MySQL Query Optimizer is either the key distribution or the storage engine.

Back on November 13, 2012, I discussed how lopsided keys can make the Query Optimizer choose different indexes (sometime not choose and index at all) : Must an index cover all selected columns for it to be used for ORDER BY?

In that post, I loaded a table call mf (Male Female) and stored a gender M or F. I inserted 37 Ms and 3Fs. I then ran explain plans on both Male and Female values crossed with MyISAM and InnoDB. Those choices of keys and storage engines yielded some interesting results.

Your Query

What you need to do is examine how distributed (or lopsided) your keys are.

You need to run the following two queries

SELECT IFNULL(source,'Total'),COUNT(1) RowCount
FROM tbl GROUP BY source WITH ROLLUP;

SELECT IFNULL(state,'Total'),COUNT(1) RowCount
FROM tbl GROUP BY state WITH ROLLUP;

This will give you a count of each value along with a total row count.

In my earlier post, I said I use a 5% rule-of-thumb. Look at the rowcounts for each value. If a rowcount for any particular source or state exceed 5% of the table's rowcount, the Query Optimizer throws one index under the bus and tries another index. In rare cases, it may just a do a full table scan.

If any key with a low rowcount does not yield a good index choice, you may need to recompute the index statistics for the MyISAM table (especially if the MyISAM table experienced lots of INSERTs, UPDATEs, and DELETEs, thus making the index statistics stale). Just run this:

ANALYZE TABLE tbl;

or to defragment the MyISAM table and recompute the index statistics, run

OPTIMIZE TABLE tbl;

Give it a Try !!!

UPDATE 2014-04-12 18:06 EDT

I like the answer posted by Alexandros (+1 for you).

I would like to update and expand my explanation by means of his answer.

You mentioned the following cardinalities

state has 22
source has 1122

That being the case, you need to run the following

ALTER TABLE tbl DROP INDEX state;
ALTER TABLE tbl ADD INDEX state_source_index (state,source);

Do not reverse the order of the columns. You should always index lower cardinality columns first.

OK, creating that index will make life better for your query. Or, will it ???

After creating the index, please run this query

SELECT
    IF(ISNULL(state)=1,'Total',
    CONCAT('Total Sources for ',state)) Statistic,RowCount
FROM
(
    SELECT state,source COUNT(1) RowCount
    FROM tbl GROUP BY state,source WITH ROLLUP
) A;

This will give you a count of all (state,source) key combinations in the table and subtotals per state. Any RowCount of any key combination that exceeds 5% of the table will most likely result in a table scan.

UPDATE 2014-04-15 16:43 EDT

Your last comment was

  I am learning a lot from your answer. Thank you. What strategy would you suggest when when the key and multi-column key are lopsided? I don't understand why I should index lower cardinality columns first?

The order of the columns will help depending on the type of query you need.

If you run queries like these

SELECT ... FROM tbl ORDER BY state,source;
SELECT ... FROM tbl WHERE state='NY' ORDER BY source;
SELECT source,COUNT(1) RowCount FROM tbl WHERE state='NY' GROUP BY source;

Then, the index should be (state,source)

If you run queries like these

SELECT ... FROM tbl ORDER BY source,state;
SELECT ... FROM tbl WHERE source='blog' ORDER BY state;
SELECT state,COUNT(1) RowCount FROM tbl WHERE source='blog' GROUP BY state;

Then, the index should be (source,state).

I usually line of columns with lower cardinality first to make index scans a little smoother for queries where the lower cardinality is involved in the ORDER BY or GROUP BY along with other columns.

If you are not comfortable with the index being (state,source), you coudl just make both

ALTER TABLE tbl ADD INDEX state_source_index (state,source);
ALTER TABLE tbl ADD INDEX source_state_index (source,state);

Run your EXPLAIN plans on your queries and see which index get used more often.

Whatever key combination are lopsided and whatever order you have the columns in the compound index, evaluating the range and order of data is faster if columns being used are indexed in groups.

UPDATE 2014-04-16 12:17 EDT

If each (source,state) key combination needs to be ordered by date, you need to incorporate the date column in the index as the last column.

ALTER TABLE tbl ADD INDEX state_source_date_index (state,source,date);
ALTER TABLE tbl ADD INDEX source_state_date_index (source,state,date);

That way the ORDER BY does not have to trigger a sort.


SQL Xml returning empty root tag on empty record set I am working on xml explicit to generate user defined nodes in xml output using Sql Server 2005 Express edition. The below code works fine, just that i couldn't manage to generate a empty  node when no record set exists in the Users table.

CODE:

select 1 as tag, null as parent,

FirstName as [User!1!FirstName!Element],

LastName as [User!1!LastName!Element]

FROM Users

FOR XML EXPLICIT

OUTPUT:

<Users>
<FirstName>Ammy</FirstName>
<LastName>Dammy</LastName>
</Users>
<Users>
<FirstName>Lammy</FirstName>
<LastName>Lolly</LastName>
</Users>

Expected Output on Empty table:

<Users></Users>

Note: I don't like the xsnil approach, all other approaches to solving the problem is welcome

A possible solution to your question is neither elegant or clean (IMHO), but it does satisfy what you are looking for:

SELECT ISNULL(s.lmx, '<Users></Users>')
FROM
    (
        select 1 as tag, null as parent,
        FirstName as [User!1!FirstName!Element],
        LastName as [User!1!LastName!Element]
        FROM Users
        FOR XML EXPLICIT
    ) AS s(lmx)

The output for this on my system is:

<Users></Users>

Please let me know what you think.

Edit:

I took your select statement and made it a subquery.  The subquery is run first and generates the output XML string or NULL.  If the result is NULL, the outer SELECT replaces the NULL-value with an empty Users node (<Users></Users>) else the generated XML string is the result.


Counting unique (distinct) users per day I am new topsql and am interested in counting the number of unique first time users per day.

My table only has two columns- user_id (there can be multiple uses by one user per day) and start_time which is a timestamp that indicates the time of use. The count should only include users whose min(start_time) fell on that day.

Why does the following query not work? Shouldn't it be possible to select distinct on two variables at once?

SELECT COUNT (DISTINCT min(start_time::date), user_id),
       start_time::date as date
FROM mytable
GROUP BY date

produces

  ERROR:  function count(date, integer) does not exist

The output would look like this

        date count
1 2017-11-22    56
2 2017-11-23    73
3 2017-11-24    13
4 2017-11-25    91
5 2017-11-26   107
6 2017-11-27    33...

Any suggestions about how to count distinct min Date and user_id and then group by date in psql would be appreciated.

Because your form is off, what you want is

SELECT count(DISTINCT x)
FROM generate_series(1,10) AS gs(x);

Or, in your case,

SELECT start_time::date, count(DISTINCT user_id)
FROM mytable
GROUP BY start_time::date;


Why doesn't SQL Server rollback the identity_insert state? I have a large script for setting up a new database that does lots of identity inserts on many tables; while I'm working on extending it I quite often have transient errors due to it being a work in progress. If one of these errors is between set identity_insert [table] on and set identity_insert [table] off the script refuses to run a second time with the following error:

  Msg 8107, Level 16, State 1, Line 15
  IDENTITY_INSERT is already ON for table 'x'. Cannot perform SET operation for table 'y'.

This is a pain! To work around it I highlight the 'off' line and run that manually, or kill the connection and reconnect (identity insert is per session I gather).

I have the whole script wrapped in a transaction, with set xact_abort on at the top so my expectation would be for the server to return to the pre-error state.

Why is this not the case? Am I doing anything wrong, and are there any more robust approaches than what I have here?

The following is a minimal test-case to reproduce the behavior. Run it twice and notice the error changes from a type conversion error in the first run to the identity_insert error on subsequent runs:

-- setup test tables for convenience
if not exists (select * from sys.tables where name = 'bill')
begin
    create table bill (id int identity primary key);
end
if not exists (select * from sys.tables where name = 'ben')
begin
    create table ben (id int identity primary key, height int);
end

set xact_abort on -- automatically rollback transaction on error
begin tran

    print 'adding bill records'
    set identity_insert bill on; -- breaks unexpectedly on *second* run
    insert into bill (id) values (1);
    set identity_insert bill off;

    print 'adding ben records'
    set identity_insert ben on;
    select * from ben
    -- insert that intentionally causes error due to type conversion failure
    insert into ben (id, height) values (1, 'not an int');
    set identity_insert ben off; -- never gets run, as expected

commit

Output from first run (expected behaviour):

  adding bill records
  adding ben records
  Msg 245, Level 16, State 1, Line 22
  Conversion failed when converting the varchar value 'not an int' to data type int.

Output from second run (unexpected behaviour):

  adding bill records
  Msg 8107, Level 16, State 1, Line 14
  IDENTITY_INSERT is already ON for table 'data-test.dbo.ben'. Cannot perform SET operation for table 'bill'.

SELECT @@version gives:

  Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

I've tried this both in management studio and in visual studio just to check it's not an odd client bug.

This is a session setting; there is nothing to roll back. If you had issued SET DATEFORMAT DMY; or SET LANGUAGE FRENCH;, would you expect an error to revert you to MDY or english_us? What if you had SET SHOWPLAN on, would you expect a rollback to stop showing you plans on subsequent queries?

To be fair, the IDENTITY_INSERT case is a little special, since it's the only session setting I can think of off the top of my head that interacts directly with a table. But it's still applicable only to your session. The thinking is probably that you could go on to insert many rows in multiple batches, even after an error has been raised (as long as it hasn't severed the connection).

As an aside, you're using SQL Server 2012, so why are you using SQL Server 7.0 error handling techniques? You could use TRY/CATCH instead of SET XACT_ABORT and then turn the setting off in the CATCH if it's on (which you can check). For a good primer on error handling, see Erland Sommarskog's articles on Error and Transaction Handling in SQL Server:

Part One– Jumpstart Error Handling
Part Two - Commands and Mechanisms
Part Three - Implementation


How can I reliably determine if a given SQL Server instance has the "Lock Pages in Memory" right? The "Lock Pages in Memory" right can be granted to the service account used by SQL Server.  This allows SQL Server to prevent memory being paged to disk.

I've noticed several of our SQL Server machines do not have the local policy configured to allow this right for the service account used by SQL Server.  Since we have a large number of servers, it will be tedious at best to manually check each one using the "Local System Policy" management console.

Is there a T-SQL query, extended stored procedure, or some other method I can use to determine if the server in question has the right or not?

I would like to not use EXEC xp_readerrorlog 0, 1, 'lock memory privilege was not granted'; since it relies on checking the current SQL Server error log; and that error log may not contain the relevant entry assuming the log has been rolled over since the server was last restarted.  I realize I could check older logs by modify the first parameter from 0 to 1, etc, however we only retain 10 error logs, and that may not be enough.  I want a fail-safe way of confirming the setting.

If xp_cmdshell is an option, here is a script making use of whoami:

DECLARE @LockPagesInMemory VARCHAR(255);
SET @LockPagesInMemory = 'UNKNOWN';
DECLARE @Res TABLE
(
    [output] NVARCHAR(255) NULL
);

IF (SELECT value_in_use
    FROM sys.configurations c
    WHERE c.name = 'xp_cmdshell'
    ) = 1
BEGIN
    INSERT INTO @Res
    EXEC xp_cmdshell 'WHOAMI /PRIV';

    IF EXISTS (SELECT *
        FROM @Res
        WHERE [output] LIKE 'SeLockMemoryPrivilege%'
        )
        SET @LockPagesInMemory = 'ENABLED';
    ELSE
        SET @LockPagesInMemory = 'DISABLED';
END

SELECT LockPagesInMemoryEnabled = @LockPagesInMemory;

whoami.exe has the ability to report all the rights held by the account executing whoami.  This, combined with the xp_cmdshell provides a reliable way to determine if the instance of SQL Server has the necessary right to lock pages in memory.

If xp_cmdshell is not enabled, this code returns UNKNOWN.  Assuming xp_cmdshell is enabled, and the right is enabled for the SQL Server account, it returns ENABLED, otherwise it returns DISABLED.


Run SQL Job monthly at day 16. If it's a weekend, run it in the next business day ( dont care if is a holiday) I need to run a job every day 16. But if it is "saturday" or "sunday" it must run in the next business day.
I don't care if it is holidays or not. I just need to run it.
I could do something like this and separete it on steps, and only pass to the next step if it runs with no error:
--Step 1 - Verify if it is 16
IF  day(getdate()) <> 16
    RAISERROR ('Job runs only day 16.',16,1)

--Step 2 - verify if its saturday of sunday
IF  DATEPART(weekday,getdate()) IN ( 7,1 )
    RAISERROR ('Job will run on the next business day.',16,1)

I would create 2 steps with these queries, but this is not going to work since, lets say the next business day is 18, the first step will raise an error.
Is there a way to schedule something like this?

It's not beautiful, but you could wrap all of your conditions into a CASE like so:
DECLARE @IsGood bit;
SET @IsGood = SELECT CASE WHEN DAY(GETDATE()) = 16 AND DATEPART(WEEKDAY, GETDATE()) NOT IN (1, 7) THEN 1
                          WHEN DAY(GETDATE()) IN (17, 18) AND DATEPART(WEEKDAY, GETDATE()) = 2 THEN 1
                          ELSE 0 END;

IF @IsGood <> 1
    RAISERROR ('Nope. Not today.', 16, 1);

Throw that into a scheduled job that runs on the 16th, 17th, and 18th of every month, and you'll be good.


Help on Self-Referential CTE SQL SERVER I have this table below which is has a hierarchy to it where the number of levels vary, for this example I chose a subset with 7 levels.

L_Key   Parent  Level P_Key
-------------------------
1393    NULL    0     2
1399    1393    1     325
4485    1399    2     NULL
4505    4485    3     NULL
5066    4505    4     NULL
6121    5066    5     NULL
5068    6121    6     NULL

My goal is to grab the property of the Parent that isn't NULL. So in this case, this should be returned:

L_Key   Parent  Level P_Key
-------------------------
1393    NULL    0     2
1399    1393    1     325
4485    1399    2     325
4505    4485    3     325
5066    4505    4     325
6121    5066    5     325
5068    6121    6     325

This is what i have tried so far.

;WITH CTE AS (
    SELECT
        m.Functional_Location_Key
        ,m.Parent_Key
        ,m.Level
        ,m.Property_Key
    FROM #MappingPartOne m
    WHERE m.Functional_Location_Key IN (6121,5068,5066,4505,4485,1399,1393)

    UNION ALL

    SELECT
        t.Functional_Location_Key
        ,t.Parent_Key
        ,t.Level
        ,t.Property_Key
    FROM CTE t
    JOIN #MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT
    Functional_Location_Key
    ,Parent_Key
    ,Level
    ,Property_Key
FROM CTE
option (maxrecursion 0)

Except this causes an infinite loop.

Thank you for your help.

I have setup a table variable for your testing

DECLARE @MappingPartOne TABLE (
    Functional_Location_Key int,
    Parent_Key int,
    Level int,
    Property_Key int
)
INSERT INTO @MappingPartOne VALUES (1393,   NULL,   0,  2)
INSERT INTO @MappingPartOne VALUES (1399,   1393,   1,  325)
INSERT INTO @MappingPartOne VALUES (4485,   1399,   2,  NULL)
INSERT INTO @MappingPartOne VALUES (4505,   4485,   3,  NULL)
INSERT INTO @MappingPartOne VALUES (5066,   4505,   4,  NULL)
INSERT INTO @MappingPartOne VALUES (6121,   5066,   5,  NULL)
INSERT INTO @MappingPartOne VALUES (5068,   6121,   6,  NULL)

Your query is running an infinite loop because it always finds more records that can be added. That's because the rows you're adding in the second half of the query (after the UNION ALL) are the exact rows that are in the first half of the query. You won't get an infinite loop if you use the columns from the @MappingPartOne table, not CTE, in the SELECT list. You won't get what you want but you won't get an infinite loop.

If I'm understanding you correctly, when a row's Property_Key is NULL, you want to use the same Property_Key value as used by the row's parent - in other words, the nearest ancestor's Property_Key where that value is not NULL.  For example: if the row with Functional_Location_Key = 4505 had Property_Key = 9978 instead of NULL, its descendants (Functional_Location_Key 5066, 6121, and 5068) would display Property_Key = 9978.

We need to make several changes to your query to accomplish this:

First, as previously noted, the second part of the CTE should output the columns from the @MappingPartOne table, not from CTE. This, however, gives you multiple copies of your rows.
To get a single copy of each of your rows, you need to start the CTE differently. You're initially explicitly providing a list of the rows you want to see in the first part of the CTE. However, then, in the second part, you're bringing in the descendants of each row from the previous iteration. Since you've included rows in the first part that are the descendants of other rows you've included, you get duplicates as you iterate through the rows.

Instead, you need to specify just the ultimate parent rows - rows that aren't descendants of any other rows. That means, the rows (in the present case, row) where Parent_Key is NULL.
Now, the next step is to check if a row's Property_Key is NULL, and (if so) to use the same value as its parent did. It doesn't matter if the parent's Property_Key was also NULL, because then it would be using its parent's Property_Key. During each iteration through our data, we have the current row (from @MappingPartOne) and its parent (from CTE). So, instead of simply using the @MappingPartOne.Property_Key, we check if that's NULL. If it's not, we use it - if it is, we use CTE.Property_Key.

So, this gives us the following query:

WITH CTE AS (
    SELECT
        m.Functional_Location_Key
        ,m.Parent_Key
        ,m.Level
        ,m.Property_Key
    FROM #MappingPartOne m
    WHERE m.Parent_Key IS NULL

    UNION ALL

    SELECT
        b.Functional_Location_Key
        ,b.Parent_Key
        ,b.Level
        ,COALESCE(b.Property_Key, t.Property_Key) as Property_Key
    FROM CTE t
    JOIN #MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT
    Functional_Location_Key
    ,Parent_Key
    ,Level
    ,Property_Key
FROM CTE
;

You can see this in action at this SQLFiddle link.

(If you haven't used it before, COALESCE() works like the ISNULL() function. It has two potential advantages: you can provide more than two arguments, in which case it will return the first non-NULL argument, or NULL if all of them are NULL; and, it's part of the ANSI standard, and thus more likely to be portable to other SQL dialects.)

If you change the WHERE clause criteria in the first part of the CTE to pick a specific row by Functional_Location_Key, then you can pull up any given row and its descendants, ignoring the rest of the hierarchy, in case that's needed.

Per your comments, you seem to want to show the Property_Key from the highest level ancestor with a parent (not from roe 1393, but from row 1399) as the default for all its descendants with a NULL Property_Key.

To do this, we need only need to introduce an additional column to our CTE:

WITH CTE AS (
    SELECT
        m.Functional_Location_Key
        ,m.Parent_Key
        ,m.Level
        ,m.Property_Key
        ,NULL as Top_Property_Key
    FROM MappingPartOne m
    WHERE m.Parent_Key IS NULL

    UNION ALL

    SELECT
        b.Functional_Location_Key
        ,b.Parent_Key
        ,b.Level
        ,COALESCE(b.Property_Key, t.Top_Property_Key) as Property_Key
        ,COALESCE(t.Top_Property_Key, b.Property_Key) as Top_Property_Key
    FROM CTE t
    JOIN MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT
    Functional_Location_Key
    ,Parent_Key
    ,Level
    ,Property_Key
FROM CTE
;

The new column is Top_Property_Key. Initially, we're looking at the top level parent. You've said we don't want to use that as our default, so we set the value for this one row to NULL. In the second part of the CTE, we want to use the established Top_Property_Key as our default if it's not currently NULL, otherwise we want to use the current parent row's Property_Key. Once we have a row where Top_Property_Key is assigned a non-NULL value, it will retain that value for all its children.

Finally, we change the COALESCE for each row's Property_Key to use our new column, t.Top_Property_Key, instead of t.Property_Key (the immediate parent's property key value).

Here's an updated SQLFiddle link with the new functionality.

NOTE: If you decide you want to use the very top parent's Property_Key as the default for all its children, then set Top_Property_Key to Property_Key instead of NULL in the first part of the CTE:

        ,m.Property_Key as Top_Property_Key