Convert rows into columns using Pivot in SQL I have this temp table named #DynamicDB, with below data.

Date     Actual_Prod    Forecast_Prod   MRB_Parts
01-04-18   1                 2             2
02-04-18   3                 2           NULL
03-04-18   0                 3           NULL
04-04-18   1                 4           NULL
05-04-18   1               NULL          NULL

I want this data to be converted into Column wise, as shown below.

List_Of_Date    01-04-18  02-04-18  03-04-18  04-04-18  05-04-18
Actual_Prod    1        3          0    1     1
Forecast_Prod      2        2          3    4    NULL
MRB_Parts      2       NULL       NULL  NULL     NULL

Using your test data

--Setup Demo Data

drop table if exists #t
Create Table  #T  (
    DateValue Date
    ,Actual_Prod int
    ,Forecast_Prod int
    ,MRB_Parts int
    )

INSERT INTO #T (DateValue,Actual_Prod,Forecast_Prod,MRB_Parts)
VALUES ('01-04-18',1,2,2)
    ,('02-04-18',3,2,null)
    ,('03-04-18',0,3,null)
    ,('04-04-18',1,4,null)
    ,('05-04-18',1,null,null);

;

I used a common table expression to union the following

with UnionedData as
(
Select ''Actual_Prod'' as Col_Label,DateValue,Actual_Prod as DataValue from #T
union all
Select ''ForeCast_Prod'' as Col_Label,DateValue,Forecast_Prod as DataValue from #T
union all
Select ''MRB_Parts'' as Col_Label,DateValue,MRB_Parts as DataValue from #T
)

which results in

| Col_Label     | DateValue  | DataValue |
|---------------|------------|-----------|
| Actual_Prod   | 2018-01-04 | 1         |
| Actual_Prod   | 2018-02-04 | 3         |
| Actual_Prod   | 2018-03-04 | 0         |
| Actual_Prod   | 2018-04-04 | 1         |
| Actual_Prod   | 2018-05-04 | 1         |
| ForeCast_Prod | 2018-01-04 | 2         |
| ForeCast_Prod | 2018-02-04 | 2         |
| ForeCast_Prod | 2018-03-04 | 3         |
| ForeCast_Prod | 2018-04-04 | 4         |
| ForeCast_Prod | 2018-05-04 | NULL      |
| MRB_Parts     | 2018-01-04 | 2         |
| MRB_Parts     | 2018-02-04 | NULL      |
| MRB_Parts     | 2018-03-04 | NULL      |
| MRB_Parts     | 2018-04-04 | NULL      |
| MRB_Parts     | 2018-05-04 | NULL      |

and then utilized that data in a dynamic PIVOT to produce the final result.
I also took the liberty of replacing null values with zeros.

Here is the full solution:

--Setup Demo Data

drop table if exists #t
Create Table  #T  (
    DateValue Date
    ,Actual_Prod int
    ,Forecast_Prod int
    ,MRB_Parts int
    )

INSERT INTO #T (DateValue,Actual_Prod,Forecast_Prod,MRB_Parts)
VALUES ('01-04-18',1,2,2)
    ,('02-04-18',3,2,null)
    ,('03-04-18',0,3,null)
    ,('04-04-18',1,4,null)
    ,('05-04-18',1,null,null);

;

--The actual query
DECLARE @sql AS NVARCHAR(2000);
DECLARE @col AS NVARCHAR(2000);
DECLARE @colCoalesceNull AS NVARCHAR(2000);

--Create dynamic column names for PIVOT
SELECT @col = Coalesce(@col + ', ', '') + QUOTENAME(PvtColumnName)
    ,@colCoalesceNull = Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME(PvtColumnName) + ',0) as ' + QUOTENAME(PvtColumnName)
FROM (
    SELECT DISTINCT CONVERT(VARCHAR(10), [DateValue], 121) AS PvtColumnName
    FROM #T
    ) AS SampleTable
ORDER BY PvtColumnName;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with UnionedData as
(
Select ''Actual_Prod'' as Col_Label,DateValue,Actual_Prod as DataValue from #T
union all
Select ''ForeCast_Prod'' as Col_Label,DateValue,Forecast_Prod as DataValue from #T
union all
Select ''MRB_Parts'' as Col_Label,DateValue,MRB_Parts as DataValue from #T
)
,cte_SampleTable as
(
select
Col_Label
,CONVERT(VARCHAR(10), [DateValue],121)
        AS PvtColumnName
,DataValue
from UnionedData

)
SELECT Col_Label, ' + @colCoalesceNull + 'FROM cte_SampleTable
    PIVOT(Min(DataValue)
    FOR PvtColumnName IN (' + @col + ')) AS PivotSampleTable';
print @sql
EXEC sp_executesql @sql;

| Col_Label     | 2018-01-04 | 2018-02-04 | 2018-03-04 | 2018-04-04 | 2018-05-04 |
|---------------|------------|------------|------------|------------|------------|
| Actual_Prod   | 1          | 3          | 0          | 1          | 1          |
| ForeCast_Prod | 2          | 2          | 3          | 4          | 0          |
| MRB_Parts     | 2          | 0          | 0          | 0          | 0          |


SQL Management Studio Intellisense - Hide global variables I am not sure if this is possible, but when writing a query to select from a table, when I bring up the intellisense drop down with CTRL + Spacebar, it gives me a list of all variables (global, and on the table).

Is there any way to make this only show the variables in the table I am selecting from? Its basically completely useless unless you know the name of the field already, so then what is the point of the intellisense drop down?

Intellisense, however intelligent it may strive (or claim itself) to be, cannot possibly know that you only want columns in that context. Without that piece of information, therefore, the list will include all items that are valid in the given context.

If you want to make it clear that you are only interested in selecting the table columns, you could qualify the column names with the table name and the syntax will restrict the number of available tokens for you.

Consider this:

Same as in your example.

Now look at this:

You can see that the list only has the column names. To make this easier, I would further suggest you use short aliases for tables, like this:


install Galera but mariadb existing on system I try to install galera on ubuntu 14.04 system. It has already got mariadb and it's working. How can I install it?

my command :

sudo apt-get install -y rsync galera-3 mariadb-galera-server

it gives error

Reading package lists... Done
Building dependency tree
Reading state information... Done
rsync is already the newest version.
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 mariadb-galera-server : Depends: mariadb-galera-server-5.5 (= 5.5.46+maria-1~trusty) but it is not going to be installed
E: Unable to correct problems, you have held broken packages.

Try using aptitude instead of apt-get. It is more intelligent. It not only will handle downgrading conflicting packages for you, but will make a series of recommendations asking you which of many possible suggested working scenarios you would like. e.g.

sudo aptitude install rsync galera-3 mariadb-galera-server

If you don't have aptitude on your machine yet, get it with

sudo apt-get install aptitude


Is it better to shrink a database or defragment to improve performance? I'm no DBA hence me visiting here. I do however manage an application which writes to a SQL database. We are experiencing poor performance with the application now. Pages taking forever to load up and unexpected odd behaviour such as pages loading that shouldn't etc.

To improve performance would I be better off defragmenting the database or shrinking it? Why is shrinking so bad? Is it possible to explain that in a couple of lines?

To improve performance would I be better off defragmenting the database or shrinking it?

Shrinking will not improve performance de-fragmenting the indexes should increase the performance, if that is really the reson behind the slowness. Shrinking is going to cause massive logical fragmentation so always bear in mind to avoid it. ONLY shrink when you have freed up large amount of space in SQL Server and you need that space at all cost. For all other reasons avoid shrinking

PS: Before going to shrinking at all I would first suggest you to find the cause of problem and Analyzing SQL Server Performance should get you started.


Too Many Connections I'm using MATLAB to feed the database but after a time I'm getting a too many connections error. I always using opendb and closedb in pair. Is it to fast the feeding? Do I have to wait after I inserted a row?

Connecting to  host = localhost  user = root  password = ***
Uptime: 1591  Threads: 149  Questions: 2956294  Slow queries: 0  Opens: 196391  Flush tables: 1  Open tables: 81  Queries per second avg: 1858.135
Current database is "forwind"
Connection closed, current status:
No connections open
add row
Connecting to  host = localhost  user = root  password = ****
Uptime: 1591  Threads: 150  Questions: 2956402  Slow queries: 0  Opens: 196491  Flush tables: 1  Open tables: 75  Queries per second avg: 1858.203
Current database is "forwind"
Connection closed, current status:
No connections open
add row

MySQL, by default, only allows 151 connections (plus one more for a super user).

In your MATLAB output, you show 149 then 150 threads.  I presume that the majority of those are connecting to the database.  150 MATLAB threads will generate 150 connections.

There are few ways you could try to fix this.

One possibility is to limit the number of threads in MATLAB.  You can do that using the maxNumberCompThreads function.  Note that this is only available in R2007b through R2010a (and it will give you warning as of R2009b).  Alternatively, the -singleCompThread option will work as well, but limits you to one thread.
Try to limit the time that you have a connection open to the absolute shortest interval possible:  Open the connection, process the database work, close the connection.  This may work, depending upon how much other (non-database) work each thread is doing.
Finally, you could increase the number of connections allowed to the database.  You will need to change the max_connections server option.  Just use the command  "SET MAX_CONNECTIONS = 500".


How does a SQL Server instance alias work? I'm trying to understand how a SQL Server alias works, but the documentation is lacking to say the least.
Most blogs/videos/documents I can find state that all you need to do is go to SQL Server Configuration Manager and add an "Alias" under SQL Native Client Configuration / SQL Native Client Configuration (32 Bit), which points to the same port as the instance you want to create an alias for. And that's it, done, nothing else required.
Now unless the SQL Server is in a domain, and allowed to register its own SPNs and only Kerberos is being used (and assuming that an alias gets its SPN automatically registered, which I can't find any documentation to either confirm or refute this statement)... I can't really see how this is enough!
Assuming that either the SQL box is not on the domain, or that SPNs are not registered, if I create an alias called SQLTEST for SQL1 on port 1433 and, from a different computer, try to access SQLTEST bearing in mind the lack of SPNs the first thing the client will do is try to resolve SQLTEST to an IP address - yet there's no DNS record for SQLTEST.
So how does this actually work? What are the true requirements to get aliases working?
How does creating an alias differ from just creating 'A' or 'CName' records in DNS without actually creating an alias in SQL itself?

1) You don't need to define the alias on the SQL Server itself, unless you need a loopback for some reason.

2) The client doing the connecting does need the alias defined on it.  Make sure you define it in both the 32bit and 64bit version of cliconfg to make sure you don't miss anything there.

C:\Windows\system32\cliconfg.exe
c:\WINDOWS\SysWOW64\cliconfg.exe

3) The Alias defined in cliconfg on the client is different from DNS in that you can specify a port instead of just a redirect.  You could combine the two for maximum flexibility.  So you could have cliconfg on the client using a DNS record to point to the correct host, but cliconfg specify a port number.  Think of it as a etc\hosts file that only database drivers talk to.  Effectively:

Application - Connection string to My_SQL_Alias
CLICONFG - My_SQL_Alias == MySQLHost:1564
DNS - MySQLHost == MyRealSQLHostName
FinalConnection == MyRealSQLHostName:1564

4) If part of a domain, you should be able to specify these things through group policy.

WHY USE IT??

I've not found many use cases in my career for setting these up.  Really, if you need to hide the fact that you are connecting to an instance rather than the default instance then that's the only thing that really springs to mind.  But the challenges with spreading this information out prohibits a lot of these use cases.


Archive database on Postgres I have a table with more than 60mio records, where every month I have to add about 2-4 milions. It's not insert only table, I need to read from it and update recently added rows (status changes, etc.). I have spotted some of my SELECT queries begun to ommit indexes because of number of rows to be searched using criterias.
   Therefore I want to archive records older than 6 months. Is there any good way to do this? Only thing I got in mind is manual copying data to archive db and remove at master. Perhaps there are some good practices or mechanisms to do it?

Michal

I recently answered similar question here: database-archive-solutions

You should read about  postgresql table partitioning, if you have good way to split data in parts.


Rename primary key constraint in Teradata Does Teradata have a way to rename a primary key constraint, rather than dropping it and recreating it? I'm using SP_RENAME for SQL Server and ALTER TABLE...RENAME CONSTRAINT for Oracle, but I don't know a corresponding command for Teradata.

no, renaming is not possible in Teradata but as already stated you can drop the constraint and recreate it


How to GRANT SELECT by default to B, on tables created by A? Background
In my PostgreSQL instance (say with one unique database constituted of one table only), I have 2 users:

user_A, who can create and populate my_table,
user_B, who should only be able to read (SELECT) from it.

It happens that, because of an API I do not control, user_A keeps dropping and recreating my_table from scratch.
This causes a reset of user_B privileges on my_table.

Thus, I am forced to

my_db=# GRANT SELECT on TABLE my_table TO user_B;

every time user_A recreates my_table.

Notes:
I know (cf. Grant permissions to user for any new tables created in postgresql) default privileges can only be set for objects created by the user they are granted to (i.e., that would be easier to set default privileges to user_B for tables created by user_B) — but this is not the case here.

Moreover, as mentioned in question linked above, the documentation specifies that "You can change default privileges only for objects that will be created by yourself or by roles that you are a member of".
So I could have

CREATE ROLE super_role;
GRANT super_role TO user_A;
GRANT super_role TO user_B;

and then SET ROLE super_role before user_A creates the table… but I unfortunately have no control over how user_A creates it.

Question
How can I GRANT SELECT by default to user_B on tables created by user_A?

PostgreSQL has default privileges, the perfect tool for your requirement:

ALTER DEFAULT PRIVILEGES FOR ROLE user_a
   GRANT SELECT ON TABLES TO user_b;

Now user_b will automatically get SELECT privileges on all tables created by user_a.


Failed import of date using COPY I'm trying to import a CSV file into a table in Postgres, and it's bombing out on the first date field. The dates are in the format dd/mm/yyyy and there isn't a date in the field in question in the CSV.

As such I'm using the following:

set datestyle = 'ISO,DMY';

\copy companies_house_snapshot
FROM 'BasicCompanyDataAsOneFile-2017-04-01.csv'
WITH DELIMITER ',' CSV HEADER NULL AS '';

Which I thought should be sufficient, the error message is:

ERROR:  invalid input syntax for type date: ""
CONTEXT:  COPY companies_house_snapshot, line 2, column dissolution_date: ""

Where dissolution_date is nullable and in the CSV as ""

I'm using PostgreSQL 9.5.6 on Ubuntu 16.04

Where am I going wrong?

If I use '""' as NULL, I get:

ERROR:  CSV quote character must not appear in the NULL specification

If I use '""' as NULL, I get:

I get these issues all the time. PostgreSQL has made a really massive and stupid mistake from a UI perspective by having two modes

CSV
NON-CSV

CSV according to them is RFC4180. However, it's not all that useful to adhere to a spec if no one else does and RFC4180 is probably the least used spec on the internet. Start using it people.

Anyway to solve these problems the easy method is to create a test table...

CREATE TABLE foo ( x int, y int, z int );
INSERT INTO foo (x,y,z) VALUES (1,null,2);

And then to get to work brute forcing options for both modes. That's why I do anyway because I find it so unintuitive. It's pretty quick though.

COPY foo TO STDOUT NULL AS '""' DELIMITER ',';

That seems to do what you want. You should be able to read it with the same options.

COPY foo FROM '/tmp/test.csv' NULL AS '""' DELIMITER ',';


Fetch data from database from multiple tables matching condition I want to fetch data from the database. I have three tables in my database like listing_master_residential and listing_master_condo and listing_master_commercial. There are three tables and there is one primary key Ml_num in all tables. I want to search the data form one table which matches mls number table.

if(isset($_POST['search'])){
$mls=$_POST['mls_number'];
 $sql = "SELECT * FROM listing_master_residential
 , listing_master_condo
, listing_master_commercial
 INNER JOIN listing_master_residential AS res ON res.Ml_num=Ml_num
 INNER JOIN listing_master_condo AS con ON con.Ml_num=Ml_num
INNER JOIN listing_master_commercial AS com ON com.Ml_num=Ml_num
 WHERE Ml_num='$mls'";
    $result = $wpdb->get_results($sql) or die(mysql_error());
foreach( $result as $row){
echo $row->Lot_code."
";
echo $row->Ml_num."
";
echo $row->Acres;
echo $row->Addr."
";
echo $row->Bath_tot;
echo $row->Br;
echo $row->Br_plus;
}

 }

 }

With the above, I get an error

Column 'Ml_num' in where clause is ambiguous

SELECT
    *
FROM
    listing_master_residential res
    INNER JOIN listing_master_condo con ON res.Ml_num = con.Ml_num
    INNER JOIN listing_master_commercial com ON com.Ml_num = res.Ml_num
WHERE
    res.Ml_num = '$mls'

Also, instead of selecting ALL (*), select only the columns you will be using, this will help your query perform better.


Table Space not being freed up after large DELETEs We offload execution log data from SSRS into our main database for auditing purposes. We keep a year's worth of this data "online" in our main db and have a weekly job that offloads anything over a year to a separate archive db.

In our main db, a year's worth of data is equal to roughly 9 million rows, so in this table there is consistently about 9 million rows. Each week we import about 150K new rows, and delete about 150K old rows.

Over time, we see that the number of rows stays pretty consistent, but as of April 1, the table size has started growing (to 5 times it's original size since April 1). On April 1 we added an NVARCHAR(MAX) column to the table.

It would appear that as a result of adding the NVARCHAR(MAX) to the table, the "garbage collection" is never freeing up the unused space in this table when we delete the 150K rows each week.

The table has a clustered index (IDENTITY PRIMARY KEY).

What can I do to free up this space?

The documentation for DBCC CLEANTABLE discusses freeing up space after a column is dropped. Not sure that applies in my situation.

(I have no interest in shrinking my DB or data files - that has nothing to do with my issue - rather it is the allocation of table space that I am trying to free up so I end up with a lot more free space within this physical database file).

There is a memory leak when dealing with heaps (no clustered index).  Multiple deletes/updates causes space to be "lost".  It can easily be fixed by either adding a clustered index or rebuilding the table.

ALTER TABLE tablename REBUILD

However in this case the table is stated as having a clustered index and the same problems don't apply.  Using Occam's Razor I'm going to guess that this is because of the varchar(max) column that was added.  Remember that when you add a varchar column very little space is added to each row until data is actually added to the column.  So in this case my guess is that as the varchar(max) column has been filled in the table size has grown.

Also remember that this table is a running year.  That means that when the column was added presumably it wasn't filled in for any of the old rows.  As the year goes by and old records are dropped off and new records come in a higher and higher percentage of the rows will have that column filled.  Not to mention the possibility of additional information being added over time.  (New comments, additional requirements when putting data in the column, whatever).  This means that the table will grow over time, but should stabilize somewhat.

A very easy way to check the size of a table is to use the system stored procedure sp_spaceused.

 -- Using msdb.dbo.backupset as an example
 exec sp_spaceused 'backupset'

name        rows        reserved    data    index_size  unused
backupset   14793       13072 KB    9112 KB 1640 KB     2320 KB

Note how we are given the number of rows, the total size of the table, how much is used for data and how much for the index.  And in this particular case most importantly how much is unused.

Every table is going to have some unused space, but if you start seeing that number climb then that would be a warning sign.  I've never seen it happen on a non-heap but of course I haven't seen everything either.


Is it possible to create an alias/shortcut/makro/function for a collection of keywords? In Oracle 12c, it's possible to use AUTO_INCREMENT functionality by using the keywords/phrase GENERATED ALWAYS AS IDENTITY. This seems cumbersome/clunky to me, given that in other dialects of SQL that I'm used to (SQLite, MySQL), the same is achieved with the keyword AUTO_INCREMENT or AUTOINCREMENT.

Is it possible to create an alias/shortcut/makro/function to get Oracle to treat AUTO_INCREMENT as meaning the same as GENERATED ALWAYS AS IDENTITY? (I'm not proposing editing the source code as a practical solution. I'd rather submit a feature request.)

You can tell the tool, in this case SQL Developer, to do it for you.

I talk about how to set this up here
 https://www.thatjeffsmith.com/archive/2014/02/auto-replace-in-oracle-sql-developer/

But basically, you define a code template in the preferences with the code you want to be recognized as a template, and then the code you want inserted.

If there is only one match for your template, when you invoke it with ctrl+spacebar, it automatically inserts the code.

If there are multiple matches, it prompts you for what you want.

I built two, one for

auto_increment - long form
autoincrement - short form

You could of course submit an enhancement request for the database so this keyword would be honored, but that's a years long process, assuming it even gets accepted and implemented.


Clearing Log Shipping config Secondary server inaccessible We are using SQL 2012

Our Secondary server in a log shipping configuration went offline never to come back (the VM was permanently deleted before log shipping configuration was updated).

On the Primary server, I am trying to clean up the LS configuration but everything I've tried times out and/or gives me errors about having configuration still existing.

I have done the following:

Deleted the LS jobs from the primary server (Backup and Alert)
Run the Stored Procedure "sp_delete_log_shipping_primary_secondary" with success
Attempted to run the Stored Procedure "sp_delete_log_shipping_primary_database", this fails.

The error with sp_delete_log_shipping_primary_database is as follows:

  Msg 32011, Level 16, State 1, Procedure sp_delete_log_shipping_primary_database, Line 51
  Primary Database <"DB name"> has active log shipping secondary database(s). Drop the secondary database(s) first.

If I try to use the SQL management studio GUI, it tries to get me to log into the Secondary server (no longer exists) and times out/errors when it cannot communicate with it.

How do I get log shipping configuration cleaned up on the Primary server, knowing that the Secondary one doesn't exist anymore?

I have flexibility to do a backup/restore of the database during troubleshooting, if needed.

I had similar error few months ago and solved by this.

USE master;
GO

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database = N'<PrimaryDatabase>'
   ,@secondary_server = N'<SecondaryServer>'
   ,@secondary_database = N'<SecondaryDatabase>'

GO


Should I save "document ID" as a String or Number in MongoDB So I'm importing about a 100,000 documents into mondoDB. MongoDB already generates an Object hash but the data has a documentID that was used to identify records in the previous database.

The IDs look like '45454343','3434', and so on. Should I keep them as String or Numbers? Is there a general rule when making this decision?

One note, somewhat unrelated it's not an object hash, but rather a BSON ObjectID which is somewhat more predictable than a hash.  It's a small point, but it can be an important one.

There are a couple of things which will usually swing you one way or another:

Are they all guaranteed to be base 10 numbers? If so, then you should be save storing them as such, and it will usually be more efficient to do so in terms of things like index size (and usually object size and storage, but that depends on the string).
If there are hex digits or similar in there, or if there is any doubt about the content, then you will want to go with a string instead

If you do go with a string type, be aware of the slight quirks involved with Lexicagraphical ordering which will be used for any sorting and to satisfy range based queries on the field.  Again, these are not usually show stopping problems, but they can cause some interesting results if you are not aware.

Finally, make sure you pick a type and stick to it if this field is going to be indexed.  You are allowed to mix types in a field in MongoDB - having a flexible schema is one of its major strengths, but if you index such a mixed field you will only generally get results for the type you query on.  For example, querying an indexed field using an numeric type when it contains a mix of strings and numbers will only yield results from the number type and vice versa.


Cascade deletes from multiple tables I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating this rating schema for e.g pictures I moved them to separate rating table and only store the rating_id in restaurant.

I know that 1 rating will only be used by 1 other row in the whole database.
How can I cascade a delete to rating when deleting a row in restaurant or picture?

I've been looking around but all I can find is set a REFERENCES, but for that I need to know which table data will be deleted.

I know triggers would do the job, I was simply hoping for something more elegant.

Some clarifications:

REFERENCES is the key word used for a FOREIGN KEY constraint (which allows to cascade DELETE or UPDATE).
Your database design seems to have logical flaws. rating seems like a detail of the main table restaurant. Since you have a 1:1 relationship, you could just include the "rating" columns in the main table. If you need a separate table, you would include a restaurant_id in the rating table rather than the other way round.
Your "rating" columns average, vote_count and vote_sum indicate another table vote and these values are derived aggregates. A MATERIALIZED VIEW would be the typical solution for that. Either as separate rating table or in combination with columns from each main table ...

The clean way would be to have a separate rating table for each main table. Then you can have a FK constraint on each with ON DELETE CASCADE.

If you still need your current design, I have two ideas:

1. Multiple FK columns in one rating table

With FK constraints reversed to point in the right direction, the table could look like this:

CREATE TABLE rating (
  rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
, restaurant_id int UNIQUE REFERENCES restaurant(restaurant_id)
                    ON UPDATE CASCADE ON DELETE CASCADE
, picture_id int UNIQUE REFERENCES picture(picture_id)
                 ON UPDATE CASCADE ON DELETE CASCADE
-- more references to other tables
);

The UNIQUE constraints enforce your 1:1 requirement. Each row in each master table can have at most one row in rating. Also creates very useful indexes automatically.

Alternatively, if you have several main tables, you might want to create partial unique indexes instead to exclude irrelevant rows from each index:

CREATE UNIQUE INDEX rating_restaurant_id ON rating (restaurant_id)
WHERE restaurant_id IS NOT NULL;
-- etc.

To enforce that exactly one FK column per row is NOT NULL:

ALTER TABLE rating ADD CONSTRAINT exactly_one_fk CHECK (
      (restaurant_id IS NOT NULL)::int
    + (picture_id    IS NOT NULL)::int = 1);  -- add more ...

With a couple of master tables, it may look like a lot of wasted storage but it really is not. A bunch of NULL columns hardly cost anything. NULL storage is very cheap:

How do completely empty columns in a large table affect performance?

2. Inheritance

Since your motivation is to avoid repeating this rating schema, inheritance could be a good solution for you. A limitation of table inheritance is that foreign keys of the parent table are not inherited - which turns into an advantage for your case:

CREATE TABLE rating (
  rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
);

CREATE TABLE restaurant_rating (
   restaurant_id int PRIMARY KEY REFERENCES restaurant(restaurant_id)
                                 ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

CREATE TABLE picture_rating (
   picture_id int PRIMARY KEY REFERENCES picture (picture_id)
                              ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

-- more?

Now you have to define the basic schema only once and inherit from it.
You still have a common PK column for all ratings with a single attached sequence.
Each child table adds the ID of the master table as PK and FK, thus enforcing your 1:1 relationship and providing the important index on the column automatically.
You can query the master table to get all ratings at once. If you need to know from which child table each row originates:

SELECT tableoid::regclass::text AS origin, *
FROM   rating;

Get all partition names for a table
Retrieve all records from multiple schemas

You might want to add a rule or trigger to disallow inserts into the master table rating directly.

SQL Fiddle.

Related:

Use triggers on inherited tables to replace foreign keys
Database design - should two projects share the same table?


Approach to Analyzing Database Requirements When presented with requirements for a database driven system, I always find it useful to imagine a world with no computers, where data records were kept in filing cabinets and data was collected by filling in certain forms. Then from that, I proceed to build my table of unnormalized data, then finally get it up to 3NF (if I can).

But the difficulty I often run into in my approach above is, how big should I make my forms? For example, let me use the same scenario from my last question, but extend it further.

An animal charity that aims to house rescued pets to new loving home owners. This charity works slightly differently, it has three types of variations on "Person".

Temporary Homeowner [TH]- Will take care of the pet temporarily until a new permanent homeowner is found.
Permanent Homeowner [PH] - Can view the files of all available pets at the shelter and pick one to adopt permanently.
Charity Sponsor [CS] - Sponsors money to the charity because of the good work they do!

Slightly different information is recorded for each type of person. The [TH] comes and registers himself/herself at the charity to help out, he/she states what type of animal he/she are willing to temporarily look after and how many animals max they're allowed to temporarily house.

The [PH] needs his/her details recorded, so that the charity can give them a call back whenever they're free to see how the poor animal is settling in.

The [CS] also needs his/her details stored in case the charity sends them a newsletter and / or a thank you note for the help and support and informing them how their money is being used to do good.

So in a world before computers, I think in such a case, there would be a "person" registration form, but to highlight my issue, how big should this form be and what information should it capture? Here is what I currently envisage this form to be like (created in word):

I suspect that my approach to getting the unnormalized data into a table would affect how smooth the process of normalization progresses later on.

From the above form, I can bang the whole thing into a table, where each form field would equate to a column of the table, but that would lead to many null values for rows of data, depending on what the person type is [TH][PH] or [CS].

I am looking for some guidance on this, and would greatly appreciate some advice on the best approach to take.

You could have a base table to store the common "person" attributes, and then specialized tables for the more specific fields. Example:

Person
------
  id
  ref_num
  reg_dt
  addrs_line1
  addrs_line2
  postal_code
  phone_num

th_person
--------
  id
  person_id (FK to person.id)
  max_accepted_at_one_time

animals
-------
  id
  accepted_by_th_person (FK to th_person.id)

ph_person
---------
  id
  person_id (FK to person.id)
  animal_ref_num

cs_person
---------
  id
  person_id (FK to person.id)

donations
---------
  id
  date
  amount
  donor_id (FK to cs_person.id)

This might be a good starting point.


Finding stored procedures with missing indexes How to scan for stored proc that need indexes to be created.

Anyone have any idea?  Basically we have stored procedures that run very slowly.  I'm wondering if there is some kind of automated script or app that can scan to indicate if an index needs to be created?

Having not thought a whole lot about this, and answering just the specific question asked, here is a way to identify all the stored procedures that reference tables that have at some point been identified as candidates for additional indexes. Unfortunately this is a very loose relationship ... I don't believe there is any straightforward way to say "this stored procedure queried this table and could have used this suggested index" unless you started parsing the sql text and fuzzy matching against the columns etc. recommended by the missing index DMVs. This is a start toward that process. That said, I kind of agree with Henry, there are plenty of more direct ways to analyze and address performance problems in stored procedures you have identified. Examining the execution plan, for example, should provide some clues much more intuitively than this round-about method you want to pursue. In any case, here is a query to start you off:

SELECT
  [Procedure] = QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id]))
        + '.' + QUOTENAME(OBJECT_NAME(s.[object_id])),
  [Table]     = QUOTENAME(r.referenced_schema_name)
        + '.' + QUOTENAME(r.referenced_entity_name),
  i.equality_columns,
  i.inequality_columns,
  i.included_columns,
  s.execution_count,
  avg_time = s.total_elapsed_time*1.0/s.execution_count
FROM sys.dm_exec_procedure_stats AS s
CROSS APPLY sys.dm_sql_referenced_entities
(
  QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id]))
  + '.' + QUOTENAME(OBJECT_NAME(s.[object_id])), 'OBJECT'
) AS r
INNER JOIN sys.dm_db_missing_index_details AS i
ON i.[object_id] = r.referenced_id
WHERE r.referenced_minor_id = 0;

Note: the DMVs usually only have data since the last restart.


When SQL Server uses Multi Page Allocations What are the consumers of MPA (multi page allocations) in SQL Server?

I know data pages are always 8K. Is there a situation where data/index page make use of MPA? It makes sense that execution plans can use MPA as they can exceed 8 KB.

There is a blog here that suggests use of MPA but it refers to stored procedures (one with 500 parameters). In the attached screenshot I see an execution plan using around 11 MB;  does this use MPA? Is there a way to confirm that memory allocation for the execution plan is using multi page allocation?

My confusion is what actually uses MPA (multi page allocator).  The example in the link I posted shows a complex execution plan which will require contiguous allocation over 8 KB.

An answer below suggests that there are many things like linked server or extended stored procedures that can use MPA. Which for some reason I am not able to agree. For example Extended Stored procedures can be C++ code using HeapAlloc which SQL server has little control over to manage its memory (it has to be at Windows OS level).

Extended stored procedures even in SQL 2012 still consumes memory outside buffer pool so it has to nothing to do with multi page allocation. The same applies to Linked servers especially if you use third party providers (e.g. ORACLE).

In SQL Server 2008 R2 and earlier, the following allocations come from the MemToLeave (MTL) area:

Linked servers
XML documents
Extended stored procedures
sp_OACreate calls
Query plans that exceed 8KB
SQLCLR
Backups that use a large value for MAXTRANSFERSIZE
Using a network packet size over 8192 bytes

Information extracted from this article by Microsoft support engineer Amit Banerjee.

SQL Server 2012 uses the Any Size Page Allocator.

Further information is included in this Microsoft Knowledge Base article.

Multi-page allocations (MPA) also come from the MTL area, before SQL Server 2012. Whether an allocation made by SQL Server is made via the MPA or single page allocator (SPA) depends purely on the size of the allocation request. If it is 8KB or less, it is allocated via SPA and stolen from the buffer pool. If it is 8KB or more, MPA is used.

  I know data page is always 8K so is there a situation where data/index page make use of MPA?

No, data and index pages are always cached in the buffer pool.

  Is there a way to confirm that memory allocation for the execution plan is using multi page allocation?

Cached plans have a complex structure. Individual allocations within that structure are generally 8KB allocations, but certain queries (generally those that contain long IN lists or many parameters to a stored procedure call) can require an MPA allocation. These are comparatively rare. It is possible to query SQL Server DMVs to determine the split between SPA and MPA for a cached plan.


SCCM 2007 SQL Query Code - Help Needed I am trying to write a report where I grab data showing all server collections with maintenance windows. How I want to do this is to show only those server collections where those computers are also members of my "All Server" collection as well. So as to identify them as servers. I cannot use the OS caption like '%Server%' because some of these computers are also Windows 7, but are being used as a server role.

Here is the code I came up with, but the output is blank. Any help would be very much appreiated. Thank you very much

Select Distinct
    v_Collection.CollectionID as [Collection ID],
    v_Collection.Name as [Collection Name],
    v_ServiceWindow.Description as [Maintenance Window Description],
    v_ServiceWindow.StartTime as [Maintenance Window Start Time],
    v_ServiceWindow.Duration as [Maintenance Window Duration]
From v_Collection Join v_ServiceWindow on
        v_ServiceWindow.CollectionID = v_Collection.CollectionID
Join v_FullCollectionMembership on
        v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID
Where v_FullCollectionMembership.Name In
        (
         Select Distinct v_Collection.CollectionID
         From v_Collection
         Where v_Collection.CollectionID = 'XYZ'
        )
ORDER BY [Collection ID];

Oops, I forgot to put the code I used, sorry about that. Here is the code that worked for me:

  SELECT

v_FullCollectionMembership.Name as [Computername],
v_Collection.Name as [Collections With Maintenance Windows],
v_ServiceWindow.Description as [Maintenance Description],
v_ServiceWindow.StartTime as [Maintenance Window Start Time],
v_ServiceWindow.Duration as [Maintenance Window Duration],
v_GS_OPERATING_SYSTEM.Caption0 as [Operating System]

FROM

v_ServiceWindow
Join
v_Collection on v_Collection.CollectionID = v_ServiceWindow.CollectionID
Join
v_FullCollectionMembership on v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID
Join
v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_FullCollectionMembership.ResourceID
Join
v_R_System on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID

WHERE

(v_GS_OPERATING_SYSTEM.Caption0 Like '%Server%')
or
(v_FullCollectionMembership.Name Like 'WN%')

ORDER BY

[Computername]


total drinks per day column Attempting to select a table with a column where the customers total drinks ordered per day are calculated by the odate and drink number.
EG.
Claude orders 10 drinks total on the 2020-1-12

drinker | odate      | total_drinks
Claude  | 2020-01-12 | 10
Claude  | 2020-01-15 | 3

How do i condense my current statement

Unclear.

What does drink_no signify?
You should not try to maintain a "running count" of how many drinks a person has consumed on a given day.  Instead, simply store that they have consumed another drink and let your queries do the "adding up":

select
  drinker
, odate
, count( odate ) total
from table1
group by drinker, odate
order by drinker, odate ;

+---------+------------+-------+
| drinker | odate      | total |
+---------+------------+-------+
| Claude  | 2020-01-12 |     4 |
| Claude  | 2020-01-15 |     2 |
| Claude  | 2020-01-19 |     1 |
| Claude  | 2020-04-04 |     1 |
| Claude  | 2020-04-05 |     1 |
| Claude  | 2020-04-12 |     1 |
| Claude  | 2020-04-15 |     1 |
| Claude  | 2020-04-16 |     1 |
| Claude  | 2020-04-17 |     1 |
| Claude  | 2020-04-18 |     1 |
| Claude  | 2020-04-19 |     1 |
| Claude  | 2020-04-20 |     1 |
+---------+------------+-------+

Also, please tell me that odate is an actual Date Data Type.

Never store Date values in anything else.  Doing so will make date manipulation far, far more difficult and, potentially, much slower.


I have lots of free memory.  How do I use it to increase performance? I am running Litespeed (with suExec, PHP SAPI), MySQL, DirectAdmin, named on a VPS with a 16-core Xeon CPU and 2GB RAM.

Currently, only 300 MB out of 2048 are used. It might sound a stupid, but how do i make the vps use more RAM?

my.cnf:

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
port                                            = 3306
socket                                          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer                                      = 512M
max_allowed_packet                              = 8M
table_cache                                     = 4096
sort_buffer_size                                = 16M
read_buffer_size                                = 8M
join_buffer_size                                = 2M
read_rnd_buffer_size                    = 32M
myisam_sort_buffer_size                 = 128M
myisam_repair_threads                   = 1
myisam_recover
max_heap_table_size                             = 8M
tmp_table_size                                  = 8M
thread_cache_size                               = 8
thread_concurrency                              = 8
query-cache-type                                = 1
query_cache_limit                               = 4M
query-cache-size                                = 16M

log-slow-queries=/var/log/mysqlslowqueries.log
local-infile = 0
wait_timeout = 10
interactive_timeout = 15
max_connections = 35

character-set-server            = utf8
default-character-set           = utf8

skip-networking
skip-federated
skip-symbolic-links

skip-innodb
[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqld_safe]
open-files-limit = 8192

I actually do need to increase MySQL performance because it's the most cpu-consumming process and there are queries that can't be optimized but take some time to execute.

Any suggestions on how to efficiently use memory to increase performance would be appreciated.

Memory can help you by caching and thus reducing I/O.

However, that won't reduce CPU usage which is your problem. This is an unusual bottleneck, as CPUs are insanely fast for most database work and I/O tends to be the bottleneck.

In your case, it is even more inusual, because you have 16-cores and VPSs tend not to have great I/O performance. First of all, make sure that you are CPU bound. vmstat should help here.If you have high numbers on the wa column, you are probably I/O bound; high numbers on the us column could indicate that you are really CPU bound.

If you are CPU bound, analyze what queries are you executing and why they take long to complete. You are either executing a lot of queries/s or they include complex calculations (i.e. aggregates, functions, etc.). The solution for the former is usually caching on the frontend, which means executing less queries. The latter is solved by simplifying your queries (if possible- you might have queries which are needlessly complex) and calculating stuff once and reusing it (say you have lots of aggregate queries; create a table with the aggregation results and query that instead of running aggregates continuously). The most efficient way to research about this is by logging which queries you are running and analyzing the log- tools exist which do this neatly.

If you are I/O bound, then you can tune memory usage, although the OS cache is often working correctly. Take a look at free:

$ free
             total       used       free     shared    buffers     cached
Mem:       6122892    5903564     219328          0     257020    3119240
-/+ buffers/cache:    2527304    3595588
Swap:     11956220      65980   11890240

The first line of numbers accounts for memory usage including OS caches, the second doesn't; by comparing both you can see how OS caching is working. Also, vmstat will already tell you how much I/O you are performing (bi, bo columns). Often, the key to solving I/O problems is query tuning and indexing; indexing prevents full table scans (i.e. reading the entire table to get a limited set of data, which causes excessive and unnecessary I/O). Again, logging queries is most effective here- running EXPLAIN on the queries will tell you which operations the database is performing to execute the query, which often leads you to understanding inefficiencies in the query (and altering the query to solve them) or finding out about needed indexes.


By what policy are SQL Server 2014 updates installed automatically? Tonight our server decided to install SQL Server 2014 CU7 for SP1. The update appears in the Windows Update history and I found downtime in our logs for it. The previous patch level was CU3.

I was surprised by this because I am not used to Windows or SQL Server automatically installing CU's. I also never witnessed a service pack being installed automatically. What has changed?

According to sqlserverbuilds.blogspot.de the CU7 was released today. It was installed right away after release.

I am not categorically against this but I would like to understand what policies are driving the automatic update choices.

Strangely, tonight was not a "patch Tuesday". No other updates were processed. The server did not reboot, either. I'm certain this was not a manual action.

So far my plan was to keep the server at the CU3 level and upgrade to 2016 eventually. The new CU being installed caused unnecessary downtime and introduced risk which I normally was not willing to take. In particular I do not wish to install patches at a random time.

So what's the automatic update policy and should I do something about that?

You might be using Windows or Microsoft update on the server and that is set to Install updates automatically (recommended).
Windows Server Update Services (WSUS) enables administrators to manage the distribution of updates and hot-fixes released for Microsoft products to computers in a corporate environment.
You should change the setting to either

Check for updates but let me choose whether to download and install them
or

Never check for updates --> This means that you own and are responsible for checking any updates, downloading them , testing and installing them on the box.

A good read - Stop Automatically Updating Production Servers


How to/Where to find MySQL templates for Toad Data Modeler I have recently install Toad Data Modeler 5.3.4 and it only has Oracle data model templates. Where can I find and import the MySQL data templates so that I can create MySQL database models. I have Googled and looked on the Toad World website.

You should use MySQL Workbench for work with MySQL. It is an excellent, free and Open Source tool for working with MySQL databases. As well as that, there isn't a one-to-one correspondence between Oracle and MySQL datatypes. Some samples/examples are available here.

[EDIT] Ooops - just realised that there is Toad for MySQL, so presumably the data types will be appropriate.However, I would just suggest that the best people to make admin tools for a given database are the actual manufacturers of that database - Toad started as a specialised Oracle product and probably still carries some baggage from those origins, whereas MySQL Workbench has always been a specialised MySQL tool.

Having said that, compiling it from source is a tricky business with numerous components and unless you're a guru, I'd use the pre-compiled version. If you are interested, you might like to look at this site which is a kind of MySQL Workbench fanzine. This site is also of interest to those working with MySQL.


Display summary results based on empty value in another column I need some assistance and guidance.

I want to retrieve a summary of results based on values in another column.
For example, I have a column in my data set that shows an audit date. There will be some records which will have an empty audit date, in that case I want to display summary results based on it which where so long as there is a specific date which is empty,a value will be assigned to it based on its grouping. I have made a sample illustration of the problem below:

ORIGINAL TABLE :

Table A
+--------------+--------------+--------------+
| officecode   | Username     |  auditdate   |
+--------------+--------------+--------------+
| ABC          |  ABCTOM      | 02-Dec-2012  |
| ABC          |  ABCPET      | 08-Dec-2012  |
| ABC          |  ABCLEE      | 12-Dec-2012  |
| ABC          |  ABCHET      | 25-Dec-2012  |
| DEF          |  DEFJKT      | 23-Dec-2012  |
| DEF          |  DEFGET      | 12-Dec-2012  |
| DEF          |  DEFHTT      | 18-Dec-2012  |
| DEF          |  DEFEET      | 16-Dec-2012  |
| XYZ          |  XYZOOP      | 16-Dec-2012  |
| XYZ          |  XYZIOO      |              |
| XYZ          |  XYZJMN      | 16-Dec-2012  |
| XYZ          |  XYZGHB      |              |
+--------------+--------------+--------------+

INTENDED RESULT :

+------------+------------+
| officecode | audit_status
+------------+------------+
| ABC        | Complete   |
| DEF        | Complete   |
| XYZ        | Incomplete |
+------------+------------+

I've done some research and tried pairing group by with a case but i seem to not be getting the results.

SELECT distinct officecode,
CASE WHEN auditdate IS NULL
      THEN 'INCOMPLETE'
     WHEN auditdate IS NOT NULL
      THEN 'COMPLETE'
     END AS AUDIT_STATUS
       FROM A GROUP BY officecode;

COUNT(expr) will count the number of rows where expr is not null, which can be used to figure out which groups have any nulls:

select officecode
     , case when count(*)>count(auditdate) then 'Incomplete'
            else 'Complete' end audit_status
from foo
group by officecode;

OFFICECODE | AUDIT_STATUS
:--------- | :-----------
DEF        | Complete
ABC        | Complete
XYZ        | Incomplete

dbfiddle here

you can get the same result with nvl2 (or decode, etc):

select officecode
     , case when sum(nvl2(auditdate,0,1))>0 then 'Incomplete'
            else 'Complete' end audit_status
from foo
group by officecode;


Not Auto and User Generated Stats in Catalog view of SQL Server While querying

select * from sys.stats

I get this result

In the Books Online it is written

But looking at results I can see that there are also some stats which are not created neither automatically nor by a user. Then how were this stats generated? Or who created them?

Thanks for your time!

Those are the statistics created when index was created. Quoting from BOL Statistics

  The Query Optimizer creates statistics for indexes on tables or views when the index is created. These statistics are created on the key columns of the index. If the index is a filtered index, the Query Optimizer creates filtered statistics on the same subset of rows specified for the filtered index. For more information about filtered indexes, see Create Filtered Indexes and CREATE INDEX (Transact-SQL).


Materialized view taking too much time i create a materialized view fast refresh. it takes almost 45 min to create but it did not refresh in 24 hours. i tried it on with index and without index. i check the log of all table max record in log table is 2 lac. query is as under please suggest what changes needed

CREATE MATERIALIZED VIEW LOG ON a WITH ROWID,   SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON P WITH ROWID,   SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON PG WITH ROWID,  SEQUENCE (COLUMN USED FROM THIS TABLE)
/
CREATE MATERIALIZED VIEW LOG ON PN WITH ROWID, SEQUENCE (COLUMN USED FROM THIS TABLE))
/
CREATE MATERIALIZED VIEW LOG ON AP WITH ROWID
/
CREATE  MATERIALIZED VIEW C_INFO
NOLOGGING
BUILD IMMEDIATE
refresh fast with rowid
on demand
AS
SELECT
   A.ROWID ACTROWID , P.ROWID PREMROWID,
   PG.ROWID PGROWID,AP.ROWID APROWID, PN.ROWID PNROWID,
   ...
FROM     A,    P,   pg,    ap, pn
WHERE
        p.id             =  pg.id (+)
    and pg.columname  (+)=  'Value'
...

Your materialized is not defined with a NEXT clause, therefore it will only refresh when you ask for it explicitely. You can use either DBMS_MVIEW.REFRESH directly or create a refresh group with DBMS_REFRESH.

In order to automate the refresh, you could program a job with DBMS_SCHEDULER or DBMS_JOB (dbms_job is deprecated in 11g).

You could also define your MV with a NEXT clause, for example this will refresh the MV every hour:

CREATE MATERIALIZED VIEW  C_INFO
NOLOGGING
BUILD IMMEDIATE
refresh fast with rowid
on demand
NEXT sysdate + 1/24


Why does querying the right most index of a composite index does not result in full table scan in postgres? Consider the following table

CREATE TABLE public.my_table
(
    my_id text COLLATE pg_catalog."default" NOT NULL,
    my_second_id text COLLATE pg_catalog."default" NOT NULL,

    CONSTRAINT my_table_pkey PRIMARY KEY (my_id, my_second_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.my_table
    OWNER to my_db;

Querying the following

explain select * from my_table
where my_id = ''

explain select * from my_table
where my_second_id = ''

Have the following result

Index Scan using my_table_pkey on my_table  (cost=0.56..178699.70 rows=268 width=917)

Index Cond: (my_second_id = ''::text)

Index Scan using my_table_pkey on my_table  (cost=0.56..3544.14 rows=1142 width=917)

Index Cond: (my_id = ''::text)

I expect the second query that uses the right most index to do a full table scan, however I see index scan instead

Does postgres support sub index scan in composite index or am I missing something?

I can't replicate this without going to extreme contortions, like setting enable_seqscan, enable_bitmapscan, and enable_indexonlyscan all to off.  What version are you using, what are you non-default settings, and how did you populate the data (as shown, you would get an empty table to query)?

But anyway, what it is doing here is a full index scan, using the index as a skinny table (although in your case, not much skinnier than the table itself), and applying the condition as an in-index filter.  Unfortunately, the displayed plan does not distinguish between the "Index Cond:" is being used for jump-to selectivity, versus just as an in-index filter.  But you can see the cost estimate for the query that uses it as an in-index filter is far higher (50 fold) than the one where it uses jump-to selectivity.


How to display one name in three tables many-to-many relationship? I have three tables (student's_info, subjects and marks):

This is student's_info:

----------------
|s_id | s_name |
----------------
| 1   | Liza   |
| 2   | Jake   |
----------------

This is subjects:

------------------
|sub_id | subject|
------------------
| 1   | Word     |
| 2   | Windows  |
| 3   | Excel    |
------------------

and this is marks:

-----------------------------------
|id | s_id |sub_id| n1  | n2 | n3 |
-----------------------------------
| 1 |  1   |  1   |  50 | 20 | 10 |
| 2 |  1   |  2   |  44 | 33 | 23 |
| 3 |  1   |  3   |  90 | 44 | 31 |
| 4 |  2   |  1   |  32 | 12 | 11 |
| 5 |  2   |  2   |  56 | 28 | 32 |
| 6 |  2   |  3   |  66 | 33 | 44 |
-----------------------------------

I have this query to select data:

SELECT students_info.*, marks.*, subjects.subject
  FROM subjects
         RIGHT JOIN (students_info
                       RIGHT JOIN marks ON students_info.s_id = marks.s_id
                    ) ON subjects.sub_id = marks.sub_id";

The query result is:

Liza  Word     50 20 10
Liza  Windows  44 33 23
Liza  Excel    90 44 31
Jake  Word     32 12 11
Jake  Windows  56 28 21
Jake  Excel    66 33 44

My question is: Why is the sutdent's name repeated? How can I get blank out the student's name if it's the same as in the previous row?

This is normal behavior in any database. The value "Liza" shows on each line because it's the value from student's_info.s_name for the rows being shown.

You want to suppress the information to improve the presentation of the data (i.e., the way it looks). This is why Michael Kutz calls it a "Display Tier" problem.

The data being returned from the query is exactly correct; you want to modify it when you go to display it to an end user. Generally, it's recommended that this sort of thing be handled by the application that's going to deliver the data to said end user.

There are ways to manipulate the result set to not display the student's name if it's the same as it was on the previous row, if for some reason you cannot do it in the application. However, they result in a more complicated query.

I've put together a query that seems to work, with the sample data at least. You can see it in action at this DB-Fiddle. Note that I've tweaked your names slightly (the apostrophe/single quote character is almost always used to mean something in SQL, so I'd avoid it in an object name).

The fiddle shows the results - as you desired, the first row for each student shows their name, and the following rows for the same student leave the name blank.

Here's the query:

SELECT s_name_display, sub_name, n1, n2, n3
  FROM (SELECT if(@last_s_name = s_name, '', s_name) as s_name_display
              ,@last_s_name := s_name as name_set_to
              ,sub_name
              ,n1,n2,n3
              ,@row_number := @row_number + 1 as sort_id
          FROM (
                SELECT student.s_name
                      ,subject.sub_name
                      ,marks.n1, marks.n2, marks.n3
                  FROM subject
                         RIGHT JOIN (student
                                       RIGHT JOIN marks ON student.s_id = marks.s_id
                                    ) ON subject.sub_id = marks.sub_id
                 ORDER BY student.s_name, subject.sub_name
                ) base
       ) sq
 ORDER BY sort_id
;

The subquery named base is your original query. We sort the data in the desired order here.

The subquery sq uses base with two variables. One, @last_s_name, holds the s_name from the previous row. @row_number is used to set a row number - we want to be certain that the final output is shown in the same order as what we have here, since row order is critical (the first appearance of each student name has to come before the other rows with that student's name).

We determine the name to display by comparing the student name from the previous row with the student name in the current row. If they're the same, we return an empty string as s_display_name; otherwise, we return the actual s_name.

Finally, we select just the columns we want from the sq subquery, and make sure they're sorted correctly in the outermost query.

Like I said - this is much more complicated than your original query. For that matter, I can't guarantee that this will scale - merely that it works fine with your sample data.


How can I query for existing database snapshots in SQL server? I'm trying to create a t-sql query that can determine whether or not a given database has any database snapshots that were created from it.

For example, if I were to create a snapshot like this:

CREATE DATABASE [DatabaseA_Snapshot] ON
(NAME=DatabaseA, FileName='<whatever>')
AS SNAPSHOT OF [DatabaseA]

Is there a way I can query for the existence of that snapshot again later? I can see that it shows up in sys.databases, but I couldn't find any information that would help me determine that it's a database snapshot that was created from DatabaseA.

SQL Server Management Studio's Object Explorer places it under the 'Database Snapshots' folder so there's obviously some way to differentiate these from regular databases.

A snapshot database will have an entry in sys.databases.source_database_id, so you can use something like the following to check for a current snapshot. Similarly, you could use the same check to DROP an existing snapshot before creating new.

IF NOT EXISTS(
    SELECT
        *
    FROM
        sys.databases
    WHERE
        name = 'DatabaseA_Snapshot'
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot]
    ON (NAME=DatabaseA, FileName='<whatever>')
    AS SNAPSHOT OF [DatabaseA]
END


Create a User and Password for ADO.net and SQL SERVER Authentication I am trying to create a sample application  in asp .net mvc with sql server authentication.

I want to create a user with password in sql server so that i can provide it in connection string in asp .net mvc web.config.
My question is how to create a user in sql server 2012 and later login using that user and password,and create databases and tables for my entity framework in .net

What are the steps to create a user and password??

Please go through below links :

https://stackoverflow.com/questions/16386610/how-do-i-create-a-new-user-in-sql-server-2012-that-i-can-use-in-a-connection-str

https://msdn.microsoft.com/en-in/library/aa337562.aspx

https://stackoverflow.com/questions/1601186/sql-server-script-to-create-a-new-user


Does my ERD account for my business logic As part of my new and upcoming project, part of the business logic involves sending each and every one of my customers (assume they number in their 1000s) multiple (possibly customized) emails.

Each of these emails will have a:

Subject
To Field (can take list of emails)
CC Field (can take list of emails)
BCC Field (can take list of emails)
Email Body

The Email body will be HTML, and will have in it placeholders where my code can inject values (e.g. a placeholder for Order No, where the code will then inject the Order No for that customer).

If the Customer is new, he/she will receive an extra email (making the total three), if on the other hand they are an old customer, he/she will only receive two emails.

Certain customers might wish some different information displayed on their emails, so my database design need to account for this. E.g. Customer John Smith might want his emails to also list the 'Item Color', where as Emma Watson is not too fussed about that detail, and does not want that listed in the emails.

If customers have not explicitly mentioned that they want customized emails sent, then a Standard Template email is sent.

I believe this is a Many to Many relationship between the Customers and the Emails, so my ERD is very simple! After examining my business logic above, have I missed anything in the ERD below?

Thank You.

UPDATE: ERD updated AGAIN after Joel Brown's input.

There are a few issues with your ERD.

A. Your ERD does not account for these business requirements:

  Certain customers might wish some different information displayed on
  their emails, so my database design need to account for this. E.g.
  Customer John Smith might want his emails to also list the 'Item
  Color', where as Emma Watson is not too fussed about that detail, and
  does not want that listed in the emails.

  If customers have not explicitly mentioned that they want customized
  emails sent, then a Standard Template email is sent.

You need to add a location to maintain your customer email preferences.  The model for that depends on what the rules are for recording the preferences.  Your question isn't explicit enough on this point.

B. You might also want to include an EMAIL_TEMPLATE table with a foreign key from EMAIL to this new table.  That will allow you to record which template was used for each email.

C. You aren't clear about what your m:n relationship is recording.  Are these columns: EMAIL.TO, EMAIL.CC, EMAIL.BCC foreign keys?  If so, are they single or multivalued?  (i.e. can you have multiple email addresses in any or all of these fields?)  If they are single valued foreign keys, then you can do away with the many-to-many and make them each a many-to-one from EMAIL to CUSTOMER (so you will have three 1:m relationships)  If they are multi-valued foreign keys, then you need to create an explicit intersection table instead of using a simple m:n relationship and you will want to have an ADDRESSEE_TYPE column ("TO", "CC", "BCC") as part of the composite key.

EDIT: Entity Relationship Diagram

This would be a logical data model that could support your business rules:

The TEMPLATE_FIELD_TYPE table is optional.  You could use it if you have a standard list of fields that can be added to a template.  If you don't want to lock down this list or reuse fields then you can skip this table and just use TEMPLATE_FIELD by itself.


eCommerce product table design - MySQL I am designing database for an web application, I have designed the product and specification details database as follows,

create table pr_size
(
    size_id             int not null auto_increment,
    value               varchar(256) not null default 'No Size Specified',
    is_active           bit(1) default 1,
    primary key (size_id)
);

create table pr_color
(
    color_id            int not null auto_increment,
    color_value         varchar(256) not null default 'No color specified',
    is_active           bit(1) default 1,
    primary key(color_id)
);

create table pr_measurement_unit
(
    mes_unit_id         int not null auto_increment,
    unit                varchar(10),
    is_active           bit(1) default 1,
    primary key (mes_unit_id)
);

create table pr_dimension
(
    dimension_id        int not null auto_increment,
    mes_unit_id         int not null default 0,
    dimension_value     char(10) not null default 'No Value',
    is_active           bit(1) default 1,
    primary key (dimension_id),
    foreign key (mes_unit_id) references pr_measurement_unit (mes_unit_id)
);

create table pr_product_features
(
   product_features_id  int not null auto_increment,
   brand_id             int not null default 0,
   color_id             int not null default 0,
   size_id              int not null default 0,
   dimension_id         int not null default 0,
   is_active            bit(1)  default 1,
   primary key (product_features_id),
   foreign key (brand_id)   references pr_brands(brand_id),
   foreign key (color_id)   references pr_color(color_id),
   foreign key (size_id)    references pr_size(size_id),
   foreign key (dimension_id)   references pr_dimension(dimension_id)
);

create table product
(
   product_id           int not null auto_increment,
   product_name         varchar(256) not null default 'No product name specified',
   ........
   ........
   is_active            bit(1) default 1,
   primary key (product_id)
);

create table pr_product_feature_applicability
(
   product_feature_applicability_id int(11) NOT NULL AUTO_INCREMENT,
   product_id int(11) NOT NULL DEFAULT '0',
   product_feature_id int(11) NOT NULL DEFAULT '0',
   dated_from datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   dated_thru datetime NOT NULL DEFAULT '9999-12-30 00:00:00',
   is_active bit(1) NOT NULL DEFAULT b'1',
   primary key (product_feature_applicability_id),
   key product_id (product_id),
   key pr_product_feature_idx (product_feature_id),
   constraint pr_product_feature FOREIGN KEY (product_feature_id) REFERENCES           pr_product_features (product_features_id) ON DELETE CASCADE ON UPDATE CASCADE,
   constraint pr_product_feature_applicability_ibfk_1 FOREIGN KEY (product_id)  REFERENCES product (Product_id)
);

This design seems good to me (I understood this design from some internet materials), but I am facing some issues.

Consider the following table values,

In front end we are confused because,

For a size there are option for multiple colors and brands.
For a color there are option for multiple sizes and brands.

Developers do understand the logic but it is confusing to write sql query to insert values to the pr_product_feature_applicability table. Since while we choosing multiple colors there are different possible ids for a single size, how to insert multiple color id's in a pr_product_feature_applicability table with a single insert statement.

SQL is not my strong point, but this was an interesting question with no answers for such a long period of time.

I would use HTML arrays to set the extra detail:

<input type="text" name="model"> //say AA-1501
<input type="text" name="color[]"> //say red
<input type="text" name="color[]"> //say green
<input type="text" name="color[]"> //say black

<input type="text" name="size[]"> //say 15
<input type="text" name="size[]"> //say 16
<input type="text" name="size[]"> //say 17

On the back end in PHP this will come across as:

<?php
$model = 'AA-1501';
$color = array(red,green,black);
$size  = array(15,16,17);

Then use your SQL to insert the correct data into the fields you want.

PHP implode, array_walk will help you a bit :)

I understand this won't help you now, but I'm sure someone might find it useful.


"Table of Constants" - is this common practice? College SQL class, using the book "SQL Fundamentals" by John J. Patrick. In the third chapter, he talks about using a "table of constants" to add columns to a select statement, where all rows have the same value.

For example, if you have table "characters", as so:

first_name  last_name  dept_code
----------- ---------- -------------------
Fred        Flintstone ROCKS
Barney      Rubble     ROCKS
Wilma       Flintstone FACEPALMING_AT_FRED

and you want a SELECT that adds a column "hometown" with value "BEDROCK" to all rows, he recommends making a second table in the database, "temp", with

hometown
--------
BEDROCK

and then doing

SELECT first_name, last_name, dept_code, hometown FROM characters, temp

The idea is that this avoids putting string constants in the SELECT statement, and that if you've got a lot of SELECTs that need the same constants, it's easier to update one table than fifty queries.

The thing is, I've been working with SQL databases for the last fifteen years, and I have never seen this construction. Is it something totally common that I've just missed, or is it something that I can erase from my memory after this assignment is over?

Joe Celko mentions tables of constants in a couple of his books.

He suggests if using a table that a check constraint is added that ensures the table can contain no more than one row.

CREATE TABLE Constants
(
lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY CHECK (lock = 'X'),
pi FLOAT DEFAULT 3.142592653 NOT NULL,
e FLOAT DEFAULT 2.71828182 NOT NULL,
phi FLOAT DEFAULT 1.6180339887 NOT NULL
);

Or alternatively a view can be used for a similar purpose.

CREATE VIEW Constants
AS
SELECT *
FROM
(VALUES(3.142592653,
        2.71828182,
        1.6180339887)) V(pi,e,phi)

Not something I've used much, if at all, myself but always worth having additional possible techniques to consider.

The specific use case in your question isn't something I would consider a table of constants for though. TBH the hardcoded same "hometown" for everyone just seems a nonsensical requirement.


Custom Sequences with SQL Server What is the best method to generate a Custom Auto-Generated Sequence with SQL Server?

Here is my requirement:

My application is hosted on 10 independent servers which are not
interconnected.
Each instance should generate a sequence number, and all these data
will be merged to all databases using SQL Server's merge replication.
The sequence generated should be unique across all the servers.

To achieve this, I have created a function to prefix a server id to the sequence number and used this as a computed column. But when I add data, I am getting the following exception:

  "Maximum stored procedure, function, trigger, or view nesting level
  exceeded (limit 32)."

Here is my table and function. Kindly suggest a better method.

create table Customers
(
    CID int identity not null primary key,
    CustomerName varchar(50)
)

ALTER function [dbo].[NextCustomerNumber]()
returns bigint
as
begin
    declare @lastval bigint
    declare @serverId int
    declare @newval bigint
    set @lastval = (select max(CustomerNumber) from Customers)
    set @serverId= 1 -- Server ID pulls from some settings
    set @newval=CONVERT(bigint, (CAST(@serverId as varchar(2))+CONVERT( varchar(10),(RIGHT(@lastval,(LEN(@lastval)-LEN(@serverId)))+1))))
    return @newval

end

Alter table Customers add CustomerNumber as dbo.NextCustomerNumber()

Why don't you just use an actual SEQUENCE which was introduced to SQL Server with 2012?

The following approach overloads the BIGINT value where the leading 10 digits represent the server IP address after being converted to a BIGINT (via the method outlined in this blog post by SQLDenis) and the right-most 10 digits represent the CustomerID local to the instance.  A BIGINT can have up to a maximum of 19 digits, but because the leftmost digit will never be higher than 4 to start the sequence this allows for far more customer numbers than the max value of an INT allows for (the comments in the code explain it in a little more detail).

The only requirement for this approach is that this solution only be deployed to, at most, one instance per server (because IP won't be unique across instances hosted on the same server).  As stated above, this approach still allows up to the INT maximum of 2,147,483,647 customers per instance in your solution.  Because the CID column in the tables is defined as a INT in your question, I'm assuming this is acceptable.  It's still about as fast of an approach as you'll find anywhere else.

-- This sequence will have a dynamic starting number **PER SERVER** based on IP
-- Max value for BigINT is 9,223,372,036,854,775,807
-- Max value for INT is                2,147,483,647
-- Max IP (255.255.255.255) to BIGINT: 4,294,967,295
-- IP to padded BIGINT:    4,294,967,295
--                       *             1,000,000,000
--                       ---------------------------
-- Max Starting Value =    4,294,967,295,000,000,000

-- Variables to capture server id (IP) and generate sequence DDL statement
DECLARE @serverid BIGINT, @sequenceDSQL VARCHAR(4000)

-- Convert IP to BIGINT
SELECT TOP(1) @serverid =
        (CONVERT(bigint, PARSENAME(local_net_address,1)) +
         CONVERT(bigint, PARSENAME(local_net_address,2)) * 256 +
         CONVERT(bigint, PARSENAME(local_net_address,3)) * 65536 +
         CONVERT(bigint, PARSENAME(local_net_address,4)) * 16777216)
FROM sys.dm_exec_connections AS c
WHERE c.local_net_address IS NOT NULL;

-- Shift Server IP value to left of the BIGINT value
SET @serverid = @serverid * 1000000000

-- Generate Dynamic SQL DDL for CREATE SEQUENCE Statement
SET @sequenceDSQL = '
CREATE SEQUENCE dbo.NextCustomerNumber
AS BIGINT
    START WITH ' + CAST(@serverid AS VARCHAR(19)) + '
    INCREMENT BY 1
    NO MAXVALUE
    NO CACHE -- Or switch to CACHE if you''re seeing performance issues on inserts'

-- Create the Sequence
EXEC(@sequenceDSQL)

-- Create the Table
create table dbo.Customers
(
    CID int identity not null primary key,
    CustomerName varchar(50),
    CustomerNumber bigint DEFAULT (NEXT VALUE FOR dbo.NextCustomerNumber)
)
GO

If you want to also factor in the port number for the @serverid value, you're going to reach a limit of what a BIGINT can hold, so I would suggest coming up with some other unique identifier for the server other than IP/port.  However, my code should show a general approach that you can adjust as needed to solve your problem.


How do I keep a 'running total' of purchases? Say I have an append-only table that records purchases at a store:
create table purchases(
    customer_id int,
    amount numeric(10, 2) -- (in dollars)
);

There's a lot of other queries that want to know the total dollars spent by a customer, so I have a view like this:
create view total_amount_purchased_view
as
    select customer_id,
           sum(amount) as total_amount_purchased
      from purchases
  group by customer_id;

The problem is, the purchases table can be very large (much larger than the number of customers), so queries using the view are slow. I can use a materialized view instead, but I would have to refresh the entire view every single purchase (because postgresql does not support single-row refresh of materialized views, you have to recalculate the entire thing. Not sure about other db's).
So, naturally, this leads me to thinking that I should have a table called total_amount_purchased containing the same rows as the view above. I would keep it up-to-date with an insert trigger on the purchases table, which would update a single row.
-- initialize
create table total_amount_purchased_table
as
    select customer_id, sum(amount) as total_amount_purchased
      from purchases
  group by customer_id;

-- keep it up to date
create or replace function fn_update_total_purchase() returns trigger as $$
begin
    update total_amount_purchased_table
       set total_amount_purchased = total_amount_purchased_table + new.amount
     where customer_id = new.customer_id;
    -- (OR, insert a new row for a new customer, code not shown)
    return new;
end; $$ language plpgsql;

create trigger tr_update_total_purchase
after insert on purchases for each row
    execute procedure fn_update_total_purchase();

Question 1: Is there a better solution to this?
Question 2: If this is an ok solution, is there any way to enforce that the total_amount_purchased is correct? (The definition of 'Correct' being that it is equal to sum(amount) of purchases? Can I restricts that the only the trigger tr_update_total_purchase can make changes to total_amount_purchased_table?

If the numbers have to be accurate, the trigger solution is the best one.
To prevent the application from messing with the summary table directly, have that table owned by a different user than the one the application uses to connect. Grant only the SELECT privilege to the application user.


How to Prevent Timeslot Overlap SQL newbie here. I currently have a 2-table database-"Customers" and "Bookings". This question concerns the "Booking" table, which has 3 attributes this question is about:

BookDate
BookStart
BookEnd

I'm trying to prevent the timeslots from overlapping. To do so, I've managed to bind the 3 attributes together into a composite key, which prevents identical entries from being entered. However, if 2 records like so are entered:

24/05/18 10:00am 10:15am

24/05/18 10:00am 10:30am

The records are accepted into the system. How do I prevent this? I'm trying to use data validation field right now (Help with preventing double bookings with variable lengths), but I'm not quite sure how to link 2 attributes together within it.

Thanks in advance!

You can only achieve such validation at the database level (when not using forms and VBA) using a CHECK constraint

ALTER TABLE Bookings
ADD CONSTRAINT NoDoubleBookings CHECK(
       NOT EXISTS(
             SELECT 1
             FROM Bookings b1, Bookings b2
             WHERE b1.BookDate + b1.BookStart < b2.BookDate + b2.BookStart
             AND b1.BookDate + b1.BookEnd > b2.BookDate + b2.BookStart
       )
)

More details on adding check constraints to Access databases can be found here


Index on a persisted computed column not seekable I have table, called Address, that has a persisted computed column called Hashkey. The column is deterministic but not precise.  It has a unique index on it that is not seekable. If I run this query, returning the primary key:

SELECT @ADDRESSID= ISNULL(AddressId,0)
FROM dbo.[Address]
WHERE HashKey = @HashKey

I get this plan:

If I force the index I get this even worse plan:

If I try and force both the index and a seek, I get an error:

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

Is this just because it's not precise?  I thought that didn't matter if it was persisted?

Is there a way to make this index seekable without making this a non-computed column?

Does anyone have any links to information on this?

I can't post the actual table creation, but here is a test table that has the same issue:

drop TABLE [dbo].[Test]

CREATE TABLE [dbo].[Test]
  (
     [test]        [VARCHAR](100) NULL,
     [TestGeocode] [geography] NULL,
     [Hashkey] AS CAST(
                        ( hashbytes
                            ('SHA',
                                ( RIGHT(REPLICATE(' ', (100)) + isnull([test], ''), ( 100 )) )
                                + RIGHT(REPLICATE(' ', (100)) + isnull([TestGeocode].[ToString](), ''), ( 100 ))
                            )
                        ) AS BINARY(20)
                      ) PERSISTED
    CONSTRAINT [UK_Test_HashKey] UNIQUE NONCLUSTERED([Hashkey])
  )
GO
DECLARE @Hashkey BINARY(20)

SELECT [Hashkey]
FROM   [dbo].[Test] WITH (FORCESEEK) /*Query processor could not produce a query plan*/
WHERE  [Hashkey] = @Hashkey

The problem seems to be related to the fact that [TestGeocode].[ToString]() returns a max datatype (nvarchar(max)).

I also encounter the issue with this simpler version (changing the definition of c1 to varchar(8000) or using COALESCE instead of ISNULL resolves it)

DROP TABLE dbo.Test

CREATE TABLE dbo.Test
  (
     c1        VARCHAR(
                          MAX    --Fails
                        --  8000 --Works fine
                          ) NULL,
     comp1 AS CAST(ISNULL(c1, 'ABC') AS VARCHAR(100))
    CONSTRAINT UK_Test_comp1 UNIQUE NONCLUSTERED(comp1)
  )

GO

DECLARE @comp1 VARCHAR(100)

SELECT comp1
FROM   dbo.Test WITH (FORCESEEK)
WHERE  comp1 = @comp1
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8606);

Computed column references get expanded out to the underlying definition then matched back to the column later. This allows computed columns to be matched without referencing them by name at all and also allows simplification to operate on the underlying definitions.

ISNULL returns the datatype of the first parameter (VARCHAR(MAX) in my example). The return type of COALESCE will be VARCHAR(MAX) here too but it seems to be evaluated differently in a way that avoids the problem.

In the cases where the query succeeds the trace flag output includes the following

ScaOp_Convert varchar(max) collate 49160,Null,Var,Trim,ML=65535

    ScaOp_Const TI(varchar collate 49160,Var,Trim,ML=3)
                      XVAR(varchar,Owned,Value=Len,Data = (3,ABC))

Where it fails this is replaced by

ScaOp_Identifier COL: ConstExpr1003

I speculate that in the cases where it fails the (implicit) CAST('ABC' AS VARCHAR(MAX)) is just done once and this is evaluated as a runtime constant (more information). However the reference to this runtime constant label, instead of the actual string literal value itself, prevents it from matching the computed column definition.

This rewrite avoids the issue in your query

CREATE TABLE [dbo].[Test]
  (
     [test]        [VARCHAR](100) NULL,
     [TestGeocode] [geography] NULL,
     [Hashkey] AS CAST(
                        ( hashbytes
                            ('SHA',
                                ( RIGHT(SPACE(100) + isnull([test], ''), 100) )
                                + RIGHT(SPACE(100) + isnull(CAST(RIGHT([TestGeocode].[ToString](),100) AS VARCHAR(100)), ''),100)
                            )
                        ) AS BINARY(20)
                      ) PERSISTED
    CONSTRAINT [UK_Test_HashKey] UNIQUE NONCLUSTERED([Hashkey])
  )


Should you OPTIMIZE FOR default values in a dynamic search query? I have a dynamic search query that looks like this:

@CustID             INTEGER         = -2,
@Name               NVARCHAR        = ''

--more parameters with default values...

Select x, y, z FROM MyTable
WHERE
(@custid = -2  OR CustomerID = @CustID) AND
(Name = ''     OR Name = @Name) AND
--more dynamic search conditions....

We've recently been having a lot of trouble with parameter sniffing and it causes major slowdowns, and even timeouts.

1) I first tried setting all my parameters to local variables, at that fixed the parameters sniffing, but the default query (the query with all default parameter values) was taking 5 seconds when before it would take 1 second. No more timeout level executions though.

2) Next I tried using OPTION (OPTIMIZE FOR (@CustID UNKNOWN, @Name UNKNOWN, ...)), but that essentially does the same thing as using local variables.

3) Last I tried still using OPTION (OPTIMIZE..., but instead of UNKNOWN, I used the default value of each parameter: OPTION (OPTIMIZE FOR (@CustID = -2, @Name = '', ...)). This kept the default search fast (still about 1 second, and it seems all other searches with other values still execute fast.

Is it a good or known practice to to this (#3)?

I would dynamically construct the query such that each combination of search values results in a different plan.

If you are using a stored procedure to do this, it might look something like this.

Do this in the tempdb:

USE tempdb;

Create a demo table:

CREATE TABLE dbo.DatabaseList
(
    ServerName sysname NOT NULL,
    DatabaseName sysname NOT NULL,
    CreateDate datetime NOT NULL,
    CONSTRAINT PK_DatabaseList PRIMARY KEY CLUSTERED
    (
        ServerName ASC,
        DatabaseName ASC,
        CreateDate ASC
    )
) ON [PRIMARY];

Create a stored proc:

IF OBJECT_ID(N'dbo.SearchDatabaseList', N'P') IS NOT NULL
DROP PROCEDURE dbo.SearchDatabaseList;
GO
CREATE PROCEDURE dbo.SearchDatabaseList
(
    @ServerName sysname = NULL
    , @DatabaseName sysname = NULL
    , @CreateDate datetime = NULL
    , @Debug bit = 0
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @where nvarchar(max);
    DECLARE @connector nvarchar(max);
    DECLARE @statement nvarchar(max);
    DECLARE @vars nvarchar(max);

    SET @vars = N'
    @ServerName sysname
    , @DatabaseName sysname
    , @CreateDate datetime
    ';

    SET @statement = N'SELECT *
FROM dbo.DatabaseList dl
';

    SET @connector = '';
    IF @ServerName IS NOT NULL
        OR @DatabaseName IS NOT NULL
        OR @CreateDate IS NOT NULL
    BEGIN
        SET @where = N'WHERE ';
    END
    ELSE
    BEGIN
        SET @where = N'';
    END
    IF @ServerName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + N'ServerName LIKE @ServerName';
        SET @connector = N'
    AND '
    END
    IF @DatabaseName IS NOT NULL
    BEGIN
        SET @where = @where + @connector + N'DatabaseName LIKE @DatabaseName';
        SET @connector = N'
    AND '
    END
    IF @CreateDate IS NOT NULL
    BEGIN
        SET @where = @where + @connector + N'CreateDate >= @CreateDate';
        SET @connector = N'
    AND '
    END
    SET @statement = @statement + @where;
    IF @Debug = 1
    BEGIN
        PRINT @statement
        PRINT N'';
    END
    ELSE
    BEGIN
        EXEC sys.sp_executesql @statement, @vars
            , @DatabaseName = @DatabaseName
            , @ServerName = @ServerName
            , @CreateDate = @CreateDate;
    END
END
GO

Demo searches using the stored proc:

EXEC dbo.SearchDatabaseList @Debug = 1, @ServerName = N'SomeServer';

Running the above generates this query (running with @Debug = 0 will actually execute the query):

SELECT *
FROM dbo.DatabaseList dl
WHERE ServerName LIKE @ServerName

more examples...

EXEC dbo.SearchDatabaseList @Debug = 1, @DatabaseName = N'SomeDatabase';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE DatabaseName LIKE @DatabaseName

Just by create date:

EXEC dbo.SearchDatabaseList @Debug = 1, @CreateDate = N'2017-06-19T00:00:00';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE CreateDate >= @CreateDate

Search server and database:

EXEC dbo.SearchDatabaseList @Debug = 1, @ServerName = N'SomeServer'
    , @DatabaseName = N'SomeDatabase';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE ServerName LIKE @ServerName
    AND DatabaseName LIKE @DatabaseName

Search server, database, and create-date:

EXEC dbo.SearchDatabaseList @Debug = 1, @ServerName = N'SomeServer'
    , @DatabaseName = N'SomeDatabase'
    , @CreateDate = N'2017-06-19T00:00:00';

output:

SELECT *
FROM dbo.DatabaseList dl
WHERE ServerName LIKE @ServerName
    AND DatabaseName LIKE @DatabaseName
    AND CreateDate >= @CreateDate

This results in clean target queries that only contain the relevant where clauses, thereby allowing the SQL Server Query Optimizer to pick a plan that is relevant to the query, and generate multiple plans that can also be cached.

This approach could easily be replicated in C#, or whatever client you're using to submit the query.


Can you prevent more than X number of rows being updated in SQL Server? Is there a way to configure SQL Server to limit the number of rows than an update statement can modify?  Say I wanted the limit to be 30,000 rows and someone fired an update that would modify 45,000 rows, they would receive an error or some other preventive message.

A trigger would be how I would handle this.  This will slow down all your update statements as SQL now has a bunch of extra work to do, but it'll handle your requirement.

CREATE TRIGGER Something on dbo.SomeTable
FOR UPDATE
AS
BEGIN
IF (select count(*) from inserted) > 30000
BEGIN
   RAISERROR('To many rows being processed at one.  Reduce and try again.', 16, 1)
   ROLLBACK
END
END

Personally I wouldn't like the idea of the instead of trigger and reapplying the update with a smaller row set as this gets into tricky areas of half completed operations now which could get very messy, quickly.


Changing a materialized view column's length after a change in the base table? We changed the underlying table column size (length). So the materialized view which is built on that table throws an error now since the MV column still has the old size.

This is an Oracle 11gR2 database.

How can I change the MV column's length to match the table's new column size without dropping the MV and recreating it?

You have to drop and recreate.

If you look at the documentation here, you can see that only modification of the storage clauses are allowed.


Is there any rule of thumb to optimize sql queries like this this is my first question here. Although I've been helped out from this forum over a hundred times.
I was having difficulties in optimizing sql query. It takes hours to execute. Record set is also large enough. The query was not written by me. So just to find the bottle neck I tried removing conditional clauses but that doesn't make any difference. Indexing the ID's done.

Can any sql guru here could throw some light on it. Is there any fine tuning room left in the query below? The server hosting the database in DB2. I'm not too pro in sql.
Thanks as always.
Regards, Nuh

This is the query:

SELECT
HEALTH_INSURANCE.RISK_DETAIL_ID ,
POLICY_RISK_COVER.RISK_COVER_ID ,
HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO ,
RD_POLICY_END_NO_IDX ,
HEALTH_INSURANCE.RD_POLICY_ID ,
HEALTH_INSURANCE.RD_LEVEL1_ID ,
HEALTH_INSURANCE.RD_SUM_INSURED_AMT_LC ,
HEALTH_INSURANCE.RD_PREMIUM_AMT_LC ,
POLICY_RISK_COVER.PREMIUM_AMOUNT_FC ,
POLICY_RISK_COVER.SUM_INSURED_AMT_FC ,
HEALTH_INSURANCE.RD_REC_TYPE ,
HEALTH_INSURANCE.RD_EFFECT_FROM_DT ,
HEALTH_INSURANCE.RD_EFFECT_TO_DT ,
HEALTH_INSURANCE.RD_END_EFFECT_FROM_DT ,
HEALTH_INSURANCE.SEX_MAS_CD ,
HEALTH_INSURANCE.MARITAL_STATUS_CD ,
HEALTH_INSURANCE.EMP_CATG ,
HEALTH_INSURANCE.NO_OF_DEPENDENTS ,
CAST((
CASE
    WHEN HEALTH_INSURANCE.AUTHORITY_LETTER_NO IS NULL
    THEN HEALTH_INSURANCE.EMP_AL_NO
    ELSE HEALTH_INSURANCE.AUTHORITY_LETTER_NO
END) AS INT) AS EMP_AL_NO ,
HEALTH_INSURANCE.DOB ,
HEALTH_INSURANCE.EFF_DATE ,
HEALTH_INSURANCE.EFF_DATE2 ,
HEALTH_INSURANCE.NAME ,
CAST((SUBSTR(HEALTH_INSURANCE.RELATIONSHIP_CD, 5,2)) AS INT) AS
RELATIONSHIP_CD_S ,
HEALTH_INSURANCE.RELATIONSHIP_CD ,
HEALTH_INSURANCE.DESIGNATION ,
HEALTH_INSURANCE.BRANCH ,
HEALTH_INSURANCE.BANK_ACCOUNT ,
HEALTH_INSURANCE.BANK_BRANCH_NAME ,
HEALTH_INSURANCE.PRE_EXISTING_AILMENT ,
HEALTH_INSURANCE.AUTHORITY_LETTER_NO ,
HEALTH_INSURANCE.AGE ,
HEALTH_INSURANCE.REGION ,
HEALTH_INSURANCE.CNIC ,
HEALTH_INSURANCE.CO_CODE ,
HEALTH_INSURANCE.EMP_LOCATION ,
HEALTH_INSURANCE.SUB_LOCATION ,
CATEGORY_LIMIT_HEADER.CLH_SYSTEM_NO ,
CATEGORY_LIMIT_HEADER.CTH_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_POL_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_END_NO_IDX ,
CATEGORY_LIMIT_HEADER.CTH_END_SR_NO ,
CATEGORY_LIMIT_HEADER.CTH_CATEGORY ,
CATEGORY_LIMIT_DETAIL.CLD_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLDH_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_CD ,
CATEGORY_LIMIT_DETAIL.CLD_END_IDX ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_DESC ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_TYPE_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_REL ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_FROM ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_RB_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_LIMIT_FC ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_PREM_FC
FROM
DB2ADMIN.HEALTH_INSURANCE AS HEALTH_INSURANCE
    INNER JOIN DB2ADMIN.POLICY_RISK_COVER AS POLICY_RISK_COVER
    ON HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO = POLICY_RISK_COVER.
    RC_POLICY_SYSTEM_NO AND
    TRIM(RD_LEVEL1_ID) = TRIM(RC_LEVEL1_ID)
        INNER JOIN DB2ADMIN.CATEGORY_LIMIT_HEADER AS CATEGORY_LIMIT_HEADER
        ON HEALTH_INSURANCE.RD_POLICY_ID = CATEGORY_LIMIT_HEADER.
        CTH_POL_SYS_ID AND
        HEALTH_INSURANCE.EMP_CATG = CATEGORY_LIMIT_HEADER.CTH_CATEGORY
            INNER JOIN DB2ADMIN.CATEGORY_LIMIT_DETAIL AS
            CATEGORY_LIMIT_DETAIL
            ON CATEGORY_LIMIT_HEADER.CTH_SYS_ID = CATEGORY_LIMIT_DETAIL.
            CLDH_SYS_ID AND
            POLICY_RISK_COVER.RISK_COVER_CD = CATEGORY_LIMIT_DETAIL.
            CLD_COVER_CD AND
            HEALTH_INSURANCE.RELATIONSHIP_CD = CATEGORY_LIMIT_DETAIL.
            CLD_CLM_REL
WHERE
COALESCE(HEALTH_INSURANCE.AGE, 1) BETWEEN CATEGORY_LIMIT_DETAIL.
CLD_CLM_AGE_FROM AND
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO

Some rules of thumb that might be relevant to you . . .

Never try to tune a query without a representative EXPLAIN plan. Representative means to get an EXPLAIN plan either from the production database or from a dev/test database that have nearly the same number of rows and nearly the same distribution of values, ideally running on similar hardware. Different numbers of rows and different distribution of values often means different execution plans.
Make sure statistics are up to date.
Index (or verify indexes) on every column used in a JOIN clause and every column used in a WHERE clause. I think every dbms implements primary key constraints and foreign key constraints with indexes. There used to be some that wouldn't let you drop such indexes, and one or two that would let you. Since I deal with a lot of different dbms, I don't bother trying to remember which is which. I just look it up when I need to know. So just look it up.
One multi-column index might perform better than three single-column indexes. Think about consolidating indexes.
Write sargable WHERE clauses. Your expression COALESCE(HEALTH_INSURANCE.AGE, 1) BETWEEN one_column AND another_column has to evaluate the COALESCE() function for every row--it can't use an index. If it's possible, replace the NULLs with the literal integer 1, and make that column non-nullable. (This isn't always practical, but non-sargable WHERE clauses are often a performance killer, especially if other considerations provoke a full table scan.)