nvarchar(max) converting to varchar and table optimization I am working with a table that has all character types set to nvarchar some of them are nvarchar(max). We are converting all these to varchar and specifying a character width based upon the actual usage in production. The production data uses a range of 2 characters up to 900 characters of actual used width for any given column. We are going to add padding of 10% when applicable.

-- Insert statements for procedure here
UPDATE Listings WITH (ROWLOCK)
SET [SubType] = 'S'
WHERE @idSettings = idSettings AND
    (@idRetsClass = 0 OR idRetsClass = @idRetsClass)
    AND (@idRetsSetting = 0 OR idRetsSetting = @idRetsSetting)
    AND IsNew = 1 AND ([SubType] LIKE '%Single Family Home%' OR [SubType] LIKE '%Modular%' OR [SubType] LIKE '%Mobile Home%'
    OR [SubType] LIKE '% Story%' OR [SubType] = '' OR [SubType] = 'residential - S' OR [SubType] = '1 House on Lot' OR [SubType] = '2 Houses on Lot'
    OR [SubType] = 'Detached' OR [SubType] LIKE '%single family%' OR [SubType] = 'ranch' OR [SubType] = 'Semi-Detached' OR [SubType] = 'single' OR [SubType] = 'one family' OR [SubType] = 'Residential'
    OR [SubType] = 'Ranch Type' OR [SubType] = '2 or More Stories' OR [SubType] = 'Cape Cod' OR [SubType] = 'Split Level' OR [SubType] = 'Bi-Level' OR [SubType] = 'Detached Single'
    OR [SubType] = 'Single-Family Homes' OR [SubType] = 'house' OR [SubType] = 'detached housing'  OR [SubType] = 'det')

A large overhaul of this table that consists literally 140 (nvarchar) columns, 11 being MAX. I am dropping 30 indexes and recreating them afterwards.

My question is in what situations is varchar(max) preferred?

Only when you expect to have 4k or more characters?

What should I learn and prepare for when doing this?

Will this improve performance when a clustered index update that affects the clustered key has to update the all non-clustered indexes?

We are having update procedures timing out that are using 75% to 95% of the query execution plan & displayed plan for an a clustered index update.

Link to the actual execution plan

{ This is a little lengthy, perhaps, but your actual problem(s) cannot be solved by looking at execution plans. There are two main issues, and both are architectural. }

Distractions

Let's start with the items that are not your major problem areas. These are things that should be looked into as it definitely helps improve performance to use the datatypes that you need and not just a general, one-size-fits-most datatype. There is a very good reason why the different datatypes exist, and if storing 100 characters in NVARCHAR(MAX) had no negative impact on queries (or any other aspect of the system), then everything would be stored as NVARCHAR(MAX). However, cleaning up these areas won't lead to true scalamability.

to MAX, or not to MAX

  I am working with a table that has all character types set to nvarchar some of them are nvarchar(max).

Ok. This is not necessarily a bad thing, though most often there is at least one field that is of a numeric type as an ID. However, there are certainly valid cases for the scenario described so far. And there is nothing inherently bad about MAX fields since they will store the data on the data page (i.e. in row) if the data can fit there. And in that situation it should perform as well as a non-MAX value of that same datatype. But yes, a bunch of MAX type fields is a sign of sloppiness in the data modeling and is far more likely to have most (or all) of that MAX data stored in separate data pages (i.e. off row) that need an extra lookup, hence less efficient.

VARCHAR vs NVARCHAR

  We are converting all these to varchar...

Ok, but why exactly (yes, I know that info and comments that follow this statement add clarity, but I am going in order to preserve the conversational aspect for a reason). Each datatype has its place. VARCHAR is 1 byte per character and can represent 256 characters (most of the time) as defined on a single code page. While character values 0 - 127 are the same between code pages, character values between 128 and 255 can change:

;WITH chars ([SampleCharacters]) AS
(
  SELECT CHAR(42) + ' '   -- *
       + CHAR(65) + ' '   -- A
       + CHAR(126) + ' '  --
   -------------------------------
       + CHAR(128) + ' '  -- €
       + CHAR(149) + ' '  -- •
       + CHAR(165) + ' '  -- ¥, Y, ?
       + CHAR(183) + ' '  -- ·, ?
       + CHAR(229) + ' '  -- å, a, ?
)
SELECT chr.SampleCharacters COLLATE SQL_Latin1_General_CP1_CI_AS AS [SQL_Latin1_General_CP1_CI_AS],
       chr.SampleCharacters COLLATE SQL_Latin1_General_CP1255_CI_AS AS [SQL_Latin1_General_CP1255_CI_AS],
       chr.SampleCharacters COLLATE Thai_CI_AS_KS_WS AS [Thai_CI_AS_KS_WS],
       chr.SampleCharacters COLLATE Yakut_100_CS_AS_KS AS [Yakut_100_CS_AS_KS],
       chr.SampleCharacters COLLATE Albanian_CS_AI AS [Albanian_CS_AI]
FROM   chars chr;

Please note that it is possible for VARCHAR data to take up 2 bytes per character and represent more than 256 characters. For more information on Double-Byte Characters Sets, please see the following answer: Storing Japanese characters in a table .

NVARCHAR is stored as UTF-16 (Little Endian) and is either 2 or 4 bytes per character, which can represent the full Unicode spectrum. So, if your data will need to ever store more characters than can be represented by a single code page, then switching to VARCHAR won't truly help you.

Prior to converting to VARCHAR, you need to make sure that you are not storing any Unicode characters. Try the following query to see if there are any rows that cannot be converted to VARCHAR without losing data:

SELECT tbl.PKfield, tbl.SubType
FROM   dbo.[Listings] tbl
WHERE  tbl.SubType <> CONVERT(NVARCHAR(MAX), CONVERT(VARCHAR(MAX), tbl.SubType))

To clarify how NVARCHAR works: the max length of an NVARCHAR field is the number of 2-byte characters. Hence, NVARCHAR(50), will allow for a maximum of 100 bytes. How many characters will fit into that 100 bytes depends on how many 4 byte characters there are: none will allow you to fit in all 50 characters, all characters being 4-bytes will only fit 25 characters, and many combinations between.

Another thing to consider regarding space taken up by VARCHAR vs NVARCHAR: starting in SQL Server 2008 (Enterprise and Developer editions only!) you can enable Row or Page compression on Tables, Indexes, and Indexed Views. For situations where much of the data within an NVARCHAR field can actually fit within VARCHAR without any data loss, compression will allow for characters that do fit into VARCHAR to be stored as 1 byte. And only characters that require either 2 or 4 bytes will take up that space. This should remove one of the larger reasons that people often choose to stick with VARCHAR. For more info on Compression, please see the MSDN page for Creating Compressed Tables and Indexes. Please note that data in MAX datatypes that is being stored off-row is not compressible.

Real Areas of Concern

The following areas should be addressed if you want this table to be truly scalable.

Problemo Numero Uno

  ...and specifying a character width based upon the actual usage in production. The production data uses a range of 2 characters up to 900 characters of actual used width for any given column. We are going to add padding of 10% when applicable.

Uh, what? Have you added all of those values up? Given how many MAX fields you have, it is possible that 1 or more of those fields has 900 characters, and even though that should equate to 1800 bytes, the value stored on the main data page is just 24 bytes (not always 24 as the size varies related to several factors). And that could be why there are so many MAX fields: they couldn't fit in another NVARCHAR(100) (taking up to 200 bytes), but they did have room for 24 bytes.

If the goal is to improve performance, then converting the full strings to codes is, on some levels, a step in the right direction. You are drastically reducing the size of each row which is more efficient for the buffer pool and disk I/O. And shorter strings take less time to compare. That's good, but not awesome.

If the goal is to dramastically improve performance, then converting to codes is the wrong step in the right direction. It still relies upon string-based scans (with 30 indexes and 140 columns, there should be a lot of scans, unless most of the fields are not used for filtering), and I assume that those will be case-insensitives scans at that, which are less efficient than if they were case-sensitive or binary (i.e. using a case-sensitive, or binary, collation).

Additionally, converting to string-based codes ultimately misses the point of how to properly optimize a transactional system. Are these codes going to be entered in on a search form? Having people use 'S' for [SubType] is far less meaningful than searching on 'Single Family'.

There is a way to retain your full descriptive text while both reducing the space used and greatly speeding up queries: create a lookup table. You should have a table named [SubType] that stores each of the descriptive terms distinctly and has a [SubTypeID] for each one. If the data is part of the system (i.e. an enum), then the [SubTypeID] field should not be an IDENTITY field as the data should get populated via a release script. If the values are entered by end users, then the [SubTypeID] field should be an IDENTITY. In both situations:

[SubTypeID] is the Primary Key.
Most likely use INT for [SubTypeID].
If the data is internal / system data, and you know that the max number of distinct values will always be under 40k, then you might could get away with SMALLINT. If you start numbering at 1 (either manually or via IDENTITY seed), then you get a max of 32,768. But, if you start at the lowest value, -32,768, then you get the full 65,535 values to use.
If you are using Enterprise Edition, then enable Row or Page Compression
Descriptive text field can be called either [SubType] (same as the table name), or maybe [SubTypeDescription]
UNIQUE INDEX on [SubTypeDescription]. Keep in mind that indexes have a max size of 900 bytes. If the max length of this data in Production is 900 characters, and if you do need NVARCHAR, then this might work with compression enabled, OR use VARCHAR only if you definitely do NOT need to store Unicode characters, ELSE enforce uniqueness via an AFTER INSERT, UPDATE trigger.
[Listings] table has [SubTypeID] field.
[SubTypeID] field in [Listings] table is Foreign Key, referencing [SubType].[SubTypeID].
Queries can now JOIN the [SubType] and [Listings] tables and search on the full text of [SubTypeDescription] (case-insensitive, even, same as current functionality), while using that ID to perform a very efficient search on the indexed FK field in [Listings].

This approach can (and should) be applied to other fields in this table (and other tables) that behave similarly.

Problemo Numero Dos

  A large overhaul of this table that consists literally 140 (nvarchar) columns, 11 being MAX. I am dropping 30 indexes and recreating them afterwards.

If this is a transactional system and not a data warehouse, then I would say that (again, generally), 140 columns is too much to handle efficiently. I highly doubt that all 140 fields are used at the same time and/or have the same use cases. The fact that 11 are MAX is irrelevant if they need to contain more than 4000 characters. BUT, having 30 indexes on a transactional table is again a bit unwieldy (as you are clearly seeing).

Is there a technical reason why the table needs to have all 140 fields? Can those fields be split into a few smaller groups? Consider the following:

Find the "core" (most important / frequently used) fields and put them into the "main" table, named [Listing] (I prefer to keep with singular words so that the ID field can easily be just TableName + "ID").
[Listing] table has this PK: [ListingID] INT IDENTITY(1, 1) NOT NULL CONSTRAINT [PK_Listing] PRIMARY KEY
"secondary" tables are named as [Listing{GroupName}] (e.g. [ListingPropertyAttribute] -- "attributes" as in: NumberOfBedrooms, NumberOfBathrooms, etc).
[ListingPropertyAttribute] table has this PK: [ListingID] INT NOT NULL CONSTRAINT [PK_ListingPropertyAttribute] PRIMARY KEY, CONSTRAINT [FK_ListingPropertyAttribute_Listing] FOREIGN KEY REFERENCES([Listing].[ListingID])

notice no IDENTITY here
notice PK name is the same between "core" and "secondary" tables
notice PK and FK to "core" table is the same field

"core" [Listing] table gets both [CreatedDate] and [LastModifiedDate] fields
"secondary" tables only get [LastModifiedDate] field. The assumption is that all secondary tables get their rows populated at the same time as the "core" table (i.e. all rows should always be represented across all "secondary" tables). Hence the [CreatedDate] value in the "core" [Listing] table would always be the same across all "secondary" tables, on a per-row basis, so no need to duplicate it across the "secondary" tables. But they can each be updated at different times.

This structure does increase the number of JOINs that many queries will need, though one or more Views can be created to encapsulate the more frequently used JOINs, for coding convenience. But on the plus side:

when it comes to DML statements, there should be much less contention since the "core" table should get most of the updates.
most updates will take less time since they are modifying smaller rows.
index maintenance on each of the new tables (both "core" and "secondary" tables) should be faster, at least on a per-table basis.

Recap

The current model is designed to be inefficient, and it seems to be fulfilling that design goal (i.e. it be slow). If you want the system to be fast, then the data model needs to be designed to be efficient, not merely less-inefficient.


sysadmin security error creating Reporting Services cache refresh plan in Report Manager On SQL Server 2012 (SP1) Reporting Services (native), when creating a cache refresh plan in Report Manager, I receive the error:

Only members of sysadmin role are allowed to update or delete jobs owned by a different login.

This is unexpected because:

Isn't it creating a job, not updating or deleting an existing one?
The owner_sid of all the existing jobs in the "Reporting Services" category is that of NT Service\ReportServer. (Not sure how these were ever successfully created, somebody else must have created them.)
NT Service\ReportServer is in the SQLAgentOperatorRole in msdb.
The service is indeed running as NT Service\ReportServer.

I can "fix" this by giving NT Service\ReportServer the sysadmin (thus verifying the login being used is really NT Service\ReportServer). However, that level of access shouldn't be necessary given the facts above.

If you are on CU7 or CU8, you may be hitting the issue mentioned this KB article since cache refresh plans also make use of SQL Agent. If so, updating to the newest CU should fix it


Keeping an eye on contention In SQL Server, I am using the following command to see blocked sessions:

select * from sys.sysprocesses where blocked>0

This is super. The problem is I need to see what block sessions there were in the last 30 minutes.  This isn't easy because, the blocked sessions can be come unblocked.can go a so they won't be returned by :

select * from sys.sysprocesses where blocked>0

Any ideas?

Thanks

Adam Machanic wrote the everuseful sp_whoisactive that can handle the task. A description of the procedure can be found at SP_WhoIsActive: Get detailed information about the sessions running on your SQL Server system


How 'Select * into' works into background? Today I came through the weird issue for which I need your help. Basically, I am copying a table(temp_a) into table (temp_b) using below query.

select * into temp_b
from temp_a
where cast(date_from as date)>='2010-01-01'
and cast(date_from as date)<'2018-01-01'

Temp_a table Structure and sample data:
id int primary key,
name  varchar not null,
date_from datetime,
update_time getdate()

Temp_A
ID           name         date_from         update_time
-------------------------------------------------------
1            A            2010-01-01        2010-01-01
2            B            2011-02-02        2011-02-02
3            C            2012-02-02        2012-02-02
4            D            2013-09-09        2013-09-09
5            E            2014-08-06        2014-08-06

But above query results duplicate records in temp_b table.

Temp_B
ID           name         date_from         update_time
-------------------------------------------------------
1            A            2010-01-01        2010-01-01
1            A            2010-01-01        2010-01-01
2            B            2011-02-02        2011-02-02
3            C            2012-02-02        2012-02-02
3            C            2012-02-02        2012-02-02
4            D            2013-09-09        2013-09-09
5            E            2014-08-06        2014-08-06

Can someone please elaborate what would be the reason for duplicate records in destination table when there is no duplicate in the source table.

Or

How "Select * into" works in the background?

You didn't include the exact query to reproduce the issue (it doesn't happen every time), so here's a few different ways it'll happen:

Using WITH (NOLOCK) - this hint in your query can cause records to be read twice, to be skipped altogether, and for your query to fail outright. (The same thing can happen with the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED hint.)

An error in the SELECT query - for example, if you're joining multiple tables together to get the data for the INSERT, and you have an error in the join (or a legitimate join that produces multiple rows of results), you can get duplicates.

Concurrency - say that while you're working, someone else is inserting and removing records from the source table. They might have temporarily had duplicates in the source table, and when you go to look later, they're gone. (I've hit issues where people would insert a new version of a row, then delete the old version of a row, so some selects out of that table would see duplicate rows. They couldn't implement unique constraints in the database because of that code pattern.)

To get an exact answer for your exact situation, post your exact code, or code that reproduces the issue you're having, and we'd be glad to dig deeper. And welcome to Stack!


How to determine the biggest float value (in terms of used bytes) with SQL in Oracle? I would like to know how to check out what is the biggest float value in terms of used bytes in a specific column.

Oracle DB Version is 10.2.

VSIZE

  VSIZE returns the number of bytes in the internal representation of
  expr.

select max(vsize(mycolumn)) from mytable;


SQL Server 2008 R2 high Buffer I/O causes Am trying to troubleshoot performance on a SQL Server that we have for our document management server and today performance has hit an all time low.

Looking at the Management Data Warehouse (MDW) I setup recently, I can see a huge spike in the Buffer I/O.  The combined SQL wait time is in the region of 1500-2000 ms which is considerably higher than normal (around 500ms).  The large proportion of this increase is Buffer I/O.

Not being a DBA and doing this out of necessity, I am massively out of my depth.

Is there any way to tell why there has been a large increase in Buffer I/O?  Is this likely due to a query pulling large amounts of data or incorrect config of SQL Server memory limits?  Is there anything specific (or general for that matter) I should be looking at or DMVs I can query to help troubleshoot?

The server is SQL Server 2008 R2. It is a VM running two vCPUs and 8GB RAM with the disks hosted on a separate array on a SAN.

The graphs above were all generated using SQL Server Management Data Warehouse.

If you look carefully at the waits graph, the coloured bars actually indicate lock waits, not I/O waits.

Also, the I/O graph shows increased system disk usage, not SQL Server disk usage.

Given that the memory graph is approaching your system memory size as the system utilization increases over time, I believe your instance is overcommitted for the current max server memory setting (or the max server memory setting isn't set at all), and one or more of the SQL Server memory pools are swapping to disk.

The solution is to lower the max server memory setting (or set it to an appropriate value if it isn't set). Please see my answer here for a more in-depth answer from a similar scenario, and my blog post:

Is my SQL Server's memory over-committed?


How to add an additional table over two one to many relations tables? I have two tables TableA and TableB which have a relation of one to many

Later I am told that there must be an additional table that links the initial tables with additional information. To carry this out using the entity relationship model, I have in mind these two proposals:

Which of the two options would be the most recommended?
For this third table can you define some type of cardinality, with respect to the two tables already related?

If the relationship between TableA and TableB is meant to be one-to-many then you don't need a third table (TableC).
However, if TableA to TableB are meant to have a many-to-many relationship, then you would need a third table (TableC).  In this case you would have foreign keys in TableC to both TableA and TableB.  Normally these two foreign keys in TableC would have a unique constraint (and probably index) applied to the combination of these two keys (a compound key).
Often this compound key is also used as the primary key of TableC.  This doesn't have to be the case. It's possible for TableC to have it's own single column unique key.  It depends on the nature of the data you're storing in TableC.


Multiple Table Pivot I have a few tables that need to be converted into a single new table.  I'm positive (un)pivot is the solution to the problem.  However, I'm uncertain what the best way is to unpivot data from multiple tables into a single table with a single query.

The tables look roughly like this:

Sales:
Pk, Year, Week1, Week2, Week3,...Week52

Quantity:
Pk, Year, Week1, Week2, Week3,...Week52

Orders:
Pk, Year, Week1, Week2, Week3,...Week52

... And so on

What I want is a table in the format

Aggregate table: Pk, Year_Week, Sales, Quantity, Orders

I've already learned from a previous question (Single year column and multiple weeks columns into single year/week column with PIVOT) how to do this for a single table.  Which looks roughly like this:

SELECT Pk, Sales FROM
    (SELECT Pk, Wk1, Wk2, ..., Wk52
        FROM Sales_Table) source
    UNPIVOT
    (
         FOR Sales IN Wk1, Wk2, ..., Wk52
    ) upt

How can I do this for multiple tables in one query?

I don't have data on what links your tables so you might have to do a very little change to the ON clauses, but here it is:

Creating tables with data

CREATE TABLE #sales (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #sales VALUES (1,2012,9,5,3,4);
INSERT INTO #sales VALUES (2,2013,7,2,6,3);
INSERT INTO #sales VALUES (3,2014,6,5,6,1);
GO
CREATE TABLE #quantity (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #quantity VALUES (1,2012,14,54,13,49);
INSERT INTO #quantity VALUES (2,2013,8,12,17,31);
INSERT INTO #quantity VALUES (3,2014,16,25,25,3);
GO
CREATE TABLE #orders (PK int, Year int, Wk1 int, Wk2 int, Wk3 int, Wk4 int);
GO
INSERT INTO #orders VALUES (1,2012,2,5,3,9);
INSERT INTO #orders VALUES (2,2013,1,2,7,1);
INSERT INTO #orders VALUES (3,2014,1,5,5,2);
GO

And the query:

SELECT PK, CAST(Year AS CHAR(4)) + '_' + Year_Week, Sales, Quantity, Orders
FROM
    (SELECT s.PK, s.Year
        , s.Wk1, s.Wk2, s.Wk3, s.Wk4
        , q.Wk1 AS qWk1, q.Wk2 AS qWk2, q.Wk3 AS qWk3, q.Wk4 AS qWk4
        , o.Wk1 AS oWk1, o.Wk2 AS oWk2, o.Wk3 AS oWk3, o.Wk4 AS oWk4
    FROM #sales s
    INNER JOIN #quantity q ON s.PK = q.PK AND s.YEAR = q.YEAR
    INNER JOIN #orders o ON s.PK = o.PK AND s.YEAR = o.YEAR) p
UNPIVOT
    (Sales FOR Year_Week IN
        (Wk1, Wk2, Wk3, Wk4)
    )AS unpvt_s
UNPIVOT
    (Quantity FOR Year_Week_q IN
        (qWk1, qWk2, qWk3, qWk4)
    )AS unpvt_q
UNPIVOT
    (Orders FOR Year_Week_o IN
        (oWk1, oWk2, oWk3, oWk4)
    )AS unpvt_o
WHERE 'q'+Year_Week = Year_Week_q
    AND 'o'+Year_Week = Year_Week_o;
GO


How do I properly perform a MySQL bake-off? I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better understand the methodology behind setting up a proper performance test. I planned on using sysbench to run my actual test, but I'm open to anything.

What steps should I be taking to
ensure that the test results in a
apples-to-apples comparison and that
only the RDBMS is the variant?
Where do I get started?
How do I evaluate results?
What advice can you give me?

Here is the first thing I thought about

Setup 3 DB servers (identical HW/OS configs) each installed with

MySQL
Percona
MariaDB

Get a Fourth Server installed with MONyog (eval version lasts 30 days)
Register the 3 DB servers in MONyog
Use the Performance Metrics Charts in MONyog or Set Up Your Own Charts in MONyog
Use SysBench  against all 3 DB Servers at your discretion

This is just a basic outline. Believe me, you can use Monyog right out of the box. Check out my testimonial at MonYog's Website (Look for my name on the page). I eventaully convinced my company to purchase the Utlimate Version outright after my testimonial, which they watched with me as it happened.

Caveat : Do no get Monyog until you have the 3 DB servers and Sysbench Ready to get the best 30-day usage of MonYog.

UPDATE 2011-06-20 13:18 EDT

If you keep the InnoDB Buffer Pool Small (default for innodb_buffer_pool_size is 8M) and keep the binary logs disabled (do no include log-bin in my.cnf), then these metrics should be measured

Innodb_buffer_pool_pages_flushed
Innodb_buffer_pool_write_requests
Innodb_row_lock_time_avg
Innodb_os_log_fsyncs
Queries, Questions
SELECTs ((Com_select + Qcache_hits) / Questions)
INSERTs ((Com_insert + Com_replace) / Questions)
UPDATEs (Com_update / Questions)
DELETEs (Com_delete / Questions)

Many of these are default graphs in MONyog's Monitors/Advisors Section, and more graphs can be created to check other desired MySQL metrics. MONyog also lets you export the graphs as CSV data.

UPDATE 2011-10-12 14:17 EDT

Percona performed a bake-off among multiple releases of MySQL

MySQL 4.1
MySQL 5.0
MySQL 5.1 (with built-in InnoDB)
MySQL 5.1 with InnoDB-plugin
MySQL 5.5
MySQL 5.6

All tests were performed with MySQL unconfigured. The results?

MySQL 4.1 performs the best single-threaded
MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 InnoDB built, 5.5 and 5.6

What do I get out of this? Your must configure MySQL 5.5/5.6 to get multicore enhancements engaged.


put the result of a query into a variable and WITH statement I need to put the result of a query into a variable.
Just a query, works as successful
DECLARE @count INT = (

SELECT count (*)
FROM [AdventureWorks].[Person].[Address]
);

select @count;

But if I need to use the WITH statement in a query, then I get a syntax error
DECLARE @count INT = (
   WITH person_address (id)
   as (
    SELECT AddressID
    FROM [AdventureWorks].[Person].[Address]
   )
   SELECT count (*)
   FROM person_address
);

select @count;

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

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

How do I put the query value into a variable if the WITH clause is used in the SQL statement?

Assign the variable in the last query.
DECLARE @count INT;

WITH person_address (id)
as (
    SELECT AddressID
    FROM [AdventureWorks].[Person].[Address]
   )
   SELECT @count = count (*)
   FROM person_address;

select @count;

create table t (id int);

insert into t values
(1),(1),(2),(1),(2);

declare @cnt int;

with ct as
(
    select id from t
)
select
  @cnt = count(*)
from
  ct
where id = 1;

select @cnt;

| (No column name) |
| ---------------: |
|                3 |

db<>fiddle here


How much will using a varchar(15) PK affect my tables performance? I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. Sadly, these timestamps are not always guaranteed to be unique. There is also a 2-6 (thus the VARCHAR) character hex ID which, when appended to the timestamp, is unique. I tested this out with ~400k records and just doing a SELECT * on the table takes over 15 seconds.

Before I go completely redesigning my table in some drastic way, I want to be sure that using this PK (as opposed to an auto-incrementing INT) is where my performance hit is. I have never really worked on a table using something other than a regular INT PK (I am a developer, not a DBA).

I am using InnoDB engine and a few FK relations to some small tables. MySQL admin is showing the data length of the table at ~150MB, and the index length at 21MB with 380k rows.

As I said, I'm a developer, not a DBA, but in my current situation I don't really have one I can bring in. I did some Googling but found a pretty wide array of answers that often delved into topics that just raised more questions for me. I'm hoping someone here can give a concise answer or at least point me to some more resources.

EDIT: Changed the column to CHAR(14) and removed one large TEXT column that was somewhat superfluous. This seems to have improved the time a good deal and took the table size down to about 80MB, but I'm still looking for suggestions.

I don't know that the PK is your issue. To me, doing a request for all 400k rows, 15 seconds doesn't sound too bad. PKs and indexes really come into the equation when you're trying to filter the set (using WHERE conditionals).


Using IF in T-SQL weakens or breaks execution plan caching? It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance.  I'm trying to find some confirmation or validate this assertion.  I'm using SQL Server 2005 and 2008.

The assertion is that with the following batch:-

IF @parameter = 0
 BEGIN
  SELECT ... something
 END

ELSE
 BEGIN
  SELECT ... something else
 END

SQL Server cannot re-use the execution plan generated because the next execution may need a different branch.  This implies that SQL Server will eliminate one branch entirely from execution plan on the basis that for the current execution it can already determine which branch is needed.  Is this really true?

In addition what happens in this case:-

IF EXISTS (SELECT ....)
 BEGIN
  SELECT ... something
 END

ELSE
 BEGIN
  SELECT ... something else
 END

where it's not possible to determine in advance which branch will be executed?

SQL Server optimises the process of compilation of the query plan for the stored procedure by ignoring the conditional branches inside the stored procedure.  The plan will be generated based on the parameters used for the first execution, this will cause problems if the parameters are different for the branches.

I would place the SQL for each of the branches into their own stored procedure, so that the plan generated is based on the actual usage of parameters for that branch.


High number of page reads and PLE counter, but only when tracing with Profiler A newly developed and deployed functionality on our production server started running slowly. Although prior tested in a development environment, it was not tested at a workload scale similar to prod.
Anyway, the issue I'm facing is that a stored procedure runs in about 13-14 seconds, with a high number of page reads being generated by a single query, which also takes an average of 11 seconds.
While tracing this query in prod, with Profiler, I captured that it does about 975.000 reads and takes about 11.7 CPU time, 258 writes.
While testing this same query on a different reporting database, which has 99% of the same data as prod, the query runs in less than 1 second, with a total of about 14.000 page reads (stats taken with SET STATISTICS IO ON).
After this I took my chances and tested this problematic query in prod, to see if I can replicate the 900K page reads there.
But, I couldn't, although I ran the stored procedure with the same parameters as the original query that generated 900K reads, I could only get the same number of 14.000 page reads (using STATISTICS IO) as in the reporting database.
Monitoring this query over time I could see that it didn't always generated the same number of page reads / sec and that occasionally it generated some "spikes" in number of pages read. Below is a graph of how the # of pages read (metric taken from Profiler trace) evolves through time.

Because of this, I thought of BCHR (buffer cache hit ratio) and PLE, assuming that because of this behaviour my BCHR and/or PLE counters would be very low, as I thought that the pages the query reads are taken out of cache frequently, thus having to be read again from disk (which generated the high number of page reads / spikes).
Taking this to the monitoring tool, SQL Spotlight, to confirm my hypothesis I noticed that the PLE counter is 1d 01:03:44. Which seems to be an amazing feat, but I'm not sure this is real.
Looking at other metrics, during the highest load time of the system, the number of logical pages read per second was very high (in my opinion, although I don't know if this is normal):

~500.000 logical page reads/sec
~750 physical page reads/sec

So, I'm confused at this point, what could generate a very high number of page reads, periodically, but not have an effect on PLE.
Any suggestions on what I can try next? Or how can I identify the cause of why the query is running much better from SSMS directly in Prod than the metrics captured by Profiler?
Update:
Pasted the execution plans of both queries:
Production:

Reporting:

UPDATE (based on @PaulWhite's suggestions):
PROD Paste the plan LINK
REPORTING Paste the plan LINK

Unfortunately, as is the case with other counters, the definition of 'reads' is not identical across the board. If a plan has calls to UDFs, statistics IO may under-report them (or hide them completely) while profiler still displays them.

Regarding the PLE counter, if the server has more than one (physical) NUMA nodes, it is important to use the ones under 'SQL Server: Buffer Node'. The PLE figure under 'SQL Server: Buffer Mgr' is an average of the Node PLEs, and can be hiding horrors sometimes (I've seen PLE of 400 on one node and 7000 on the other, with SQL supposedly using both nodes).


(SQL Server /SSIS) Managing IS Catalog permissions on a shared server? I'm hoping for some ideas on how you and your team manage this. I'm supporting a shared SQL Server that will have several different departments deploying their SSIS packages via the IS Catalog. I would like users to be able to have some level of access to their packages, without that colliding with/seeing the packages from other groups. I envision users being able to:

Go to 'Integration Services Catalogs' -> SSISDB -> And have read-only access to the folders containing the projects and environments they have deployed. Possibly full access to deploy new ISPAC to that folder, but only for folders they are granted permissions to.
See the IS catalog execution reports for only their SSIS packages.
Query the SSISDB database for only information (executions, package names, parameters) related to their packages.

How do you do this in a shared environment, where more granular permissions are needed?

You probably want to add Users in the msdb database to the db_ssisltduser role as per the Microsoft Docs on Integration Services Roles. Under the Read and Write Actions section for the db_ssisltduser role it says the following permissions will be applied:

Enumerate own packages.
Enumerate all packages.
View own packages.
Execute own packages.
Export own packages.   Import packages.
Delete own packages.
Change own package roles.

You can also make a custom role that encompasses some of these permissions and/or other permissions as needed too.


Postgresql trigger update whole table after delete I have a table "measurements" which looks like this (with empty cells in the last col)

ser_nr | meas_ser | meas_value | last_meas
-------+----------+------------+-----------
  A1   |    1     |    12      |  (False)
  A1   |    2     |    5.5     |  (False)
  A1   |    3     |    5.12    |  (False)
  A1   |    4     |    5.01    |  (True)
  B1   |    1     |    2       |  (False)
  B1   |    2     |    2.03    |  (True)

http://sqlfiddle.com/#!15/bcd6b (Link for create table etc.)

I need this to query "select ... where last_meas=True"

I wrote a trigger for "after insert" and this works for the newly inserted rows. But what can I do to update the whole table?

For example: I want to delete the A1 row with meas_value "5.01". There has to be a function which executes for the whole remaining table and updates the last_meas column (in this example the third row should be True now). I can trigger a function using "after delete for each row" but using this method i can only access all deleted rows.

I found a workaround by generating a view using

SELECT * FROM measurements AS m LEFT OUTER JOIN (SELECT m.ser_nr,
    max(m.meas_ser) AS meas_ser, TRUE AS last_meas FROM measurements AS
    m GROUP BY m.ser_nr ) AS last_ms USING (ser_nr, meas_ser);

but this only generates "True" and not "False".

Is there a more easy way doing this?

You can use this simpler / cheaper query for your view or CTE or simply a subquery:

SELECT ser_nr, meas_ser, meas_value
     , row_number() OVER (PARTITION BY ser_nr ORDER BY meas_ser DESC) = 1 AS last_meas
FROM   measurements;

SQL Fiddle.

There are no corner case problems with duplicate or NULL values here while you have that PK constraint you added in the fiddle:

ALTER TABLE measurements ADD CONSTRAINT measurements_pk PRIMARY KEY (ser_nr,meas_ser);

The associated index will also help to make it fast if you only need a small selection of ser_no from a big table.

And yes, this dynamic approach has so much less potential for headache (consistency in the face of concurrent write access!) than trying to keep rows in your base table current.


No-SQL Database Design for restaurant app I've been working with SQL databases all my life and I am now trying to get into the NoSQL world. I am a looking for a piece of advice on whats the best practice in order to achieve this.

Suppose I have a table that contains all the meals on the menu and another with the the waiters information. I want to create a third table for the orders. What is the best practice to archive this using a NoSQL database?

In traditional SQL I would have made a table called Orders with an ID and foreign key pointing the waiter id. And I would have created another table relating meals id with order id.

What is the recommend approach to do this in a NoSQL database?

You must have into consideration there will be a "client" running in the kitchen showing the orders.

If it's any relevant I will use parse.com as backend.

Greetings!

As NoSQL databases typically do not support joins, they expect you to store the data in de-normalized form. (So, throw the codd normalization rule book away. Just kidding). In this case, the thrid table better have the values duplicated something like (orderid, ordertime, [itemnames], waitername, orderserviced yes/no, servicetime, ...). itemnames can be a list type which is supported by many NoSQL databases. Not all but some databases support secondary index queries. You can query for all the orders where orderserviced=no to get all the open orders.

You may still do what is similar to joins at application level if one of the table is small and can be cached by the application. You can do a hash look up of the id and get the actual value.

In general, you should be conscious that NoSQL databases come with a limited functionality. But they give more flexibility in-terms of the schema and generally tuned for high-volume applications and clustering/HA in mind.


Truncating Table Partitions Containing Data and Global Indexes I have to truncate a partition (392,000 records) of a table with 20.000.000 records.

At the moment, I'm making a truncate table and rebuilding indexes. But the table has 37 indexes and it takes 20 minutes to rebuild them.

Taking a look at the Oracle Docs it gives me three options:

1- (This is what I'm doing)

ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

2-

DELETE FROM sales PARTITION (dec98);
ALTER TABLE sales TRUNCATE PARTITION dec98;

3-

ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE INDEXES;

So I have two questions:

1- Which of these options would be the faster in my case?
2- What is the meaning (in option 2) of deleting and then truncating the partition?

Thanks in advance!

Are the 37 indexes you have on the table global indexes?
Option 1:
Where you truncate and then rebuild
This method is most appropriate if the partition that you are truncating has at least 10% of the total data in the table.  But rebuilding these 37 indexes is going to take time.
Option 2:
Where you delete and truncate the partition
The delete statement here updates any global indexes, fires triggers and generates redo and undo logs. To avoid the generation of logs and make this faster, Just before the deletion, you may alter the partition as
ALTER TABLE sales MODIFY PARTITION dec98 NOLOGGING;

and then proceed with deletion and truncation.  But for partitions containing a large percentage of the total data in table, this will still be slower
Option 3:
If you have a global index on the partition, UPDATE INDEXES will rebuild this index as well. Local Indexes are truncated automatically while partition truncate .
My suggestion here would be use Option 2 with NOLOGGING; as below,
ALTER TABLE sales MODIFY PARTITION dec98 NOLOGGING;
DELETE FROM sales PARTITION (dec98);
ALTER TABLE sales TRUNCATE PARTITION dec98 UPDATE INDEXES;


SQLPlus Padding in Query Results I have several Oracle servers that exhibit a behavior I do not understand.  I have identified a two node RAC in which one node exhibits the behavior, but the other node does not.  RAC is not involved in the issue, but it dramatically reduces the differences between the servers experiencing the different behavior.

Why is the following output different?
Node 2 (expected behavior):

SQL> select 'a' from dual;

'
-
a

SQL>  select 'abc' from dual;

'AB
---
abc

Node 1 (unexpected behavior):

SQL> select 'a' from dual;

'A'
---
a

SQL>  select 'abc' from dual;

'ABC'
---------
abc

These nodes are connecting to different instances of the same database.  I have done a show all on both and verified that the sqlplus settings are identical.

I found this just before posting.  The difference is the linux environment setting for NLS_LANG.  It was set to American_America.UTF8 on the system it added padding to.  Unsetting this on one and setting this on the other swapped where the behavior.


SQL Server 2005 error while starting service I am getting error while starting the service & errorlog is:

2013-04-04 14:50:49.14 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
    Oct 14 2005 00:33:37
    Copyright (c) 1988-2005 Microsoft Corporation
    Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2013-04-04 14:50:49.14 Server      (c) 2005 Microsoft Corporation.
2013-04-04 14:50:49.14 Server      All rights reserved.
2013-04-04 14:50:49.14 Server      Server process ID is 312.
2013-04-04 14:50:49.14 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2013-04-04 14:50:49.14 Server      This instance of SQL Server last reported using a process ID of 3072 at 4/4/2013 2:32:28 PM (local) 4/4/2013 9:02:28 AM (UTC). This is an informational message only; no user action is required.
2013-04-04 14:50:49.14 Server      Registry startup parameters:
2013-04-04 14:50:49.14 Server        -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2013-04-04 14:50:49.14 Server        -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2013-04-04 14:50:49.14 Server        -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2013-04-04 14:50:49.18 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2013-04-04 14:50:49.18 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2013-04-04 14:50:49.84 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2013-04-04 14:50:49.85 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2013-04-04 14:50:49.90 spid3s      Starting up database 'master'.
2013-04-04 14:50:50.60 spid3s      SQL Trace ID 1 was started by login "sa".
2013-04-04 14:50:50.64 spid3s      Starting up database 'mssqlsystemresource'.
2013-04-04 14:50:50.82 spid8s      Starting up database 'model'.
2013-04-04 14:50:50.82 spid3s      Server name is 'COMPAQ-FB238749\SQLEXPRESS'. This is an informational message only. No user action is required.
2013-04-04 14:50:50.82 spid3s      Starting up database 'msdb'.
2013-04-04 14:50:50.90 Server      Error: 17190, Severity: 16, State: 1.
2013-04-04 14:50:50.90 Server      FallBack certificate initialization failed with error code: 1.
2013-04-04 14:50:50.90 Server      Warning:Encryption is not available, could not find a valid certificate to load.
2013-04-04 14:50:50.93 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2013-04-04 14:50:50.93 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2013-04-04 14:50:50.98 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2013-04-04 14:50:51.00 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2013-04-04 14:50:51.03 spid8s      Error: 9003, Severity: 20, State: 1.
2013-04-04 14:50:51.03 spid8s      The log scan number (100:384:1) passed to log scan in database 'model' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
2013-04-04 14:50:51.03 spid8s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

It looks like your [model] database has become corrupted in some or another way, in particular, SQL is reporting that the log scan failed.

If you had a customised model database, you should probably have a backup of it too.

You would need your installation media to recreate the system databases, and thereafter (if you have one) restore the latest backup of your [model] database.

You can achieve this by running the following command line :

start /wait <CD or DVD Drive>\setup.exe /qn INSTANCENAME=<Instance> REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=<NewStrongPassword>

This will set you up with an instance without your user databases and all default settings.  You can then proceed with the restore of your [master], [msdb], and [model], and also the rest of your databases.


Mysql 5.5.25 must be restarted to connect We have MySQL v5.5.25 running on Windows Server 2008 R2. Occasionally people are unable to connect, and the only way we have found to fix this is to restart the mysql service. This happens every 2-3 days.

Anyone have a clue on this? We have spent hours trying to fix it changing various values in the configuration, but nothing has worked.

Please keep in mind what the MySQL Documentation says about max_connect_errors

If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it.

MySQL 5.5's default value for max_connect_errors is 10.
I have two suggestions on resetting the connection error count errors besides restarting mysql:
SUGGESTION #1
Create a one line SQL script (such as C:\FlushHosts.sql)
Setup a scheduled task to execute mysql -uroot < C:\FlushHosts.sql every hour
SUGGESTION #2
Goto my.ini and add the following line
[mysqld]
max_connect_errors=18446744073709547520

Then, login to mysql client and run this:
mysql> SET GLOBAL max_connect_errors = 18446744073709547520;

After setting max_connect_errors this high, you should never get connection errors. Otherwise, you must running mysql in some parallel universe.
Give it a Try !!!


What does 'fully determines' mean in the context of postgres functional dependency statistics? I am following the example of how to use 'create statistics' in postgresql from this page.

CREATE TABLE tbl (
                     col1 int,
                     col2 int
);

INSERT INTO tbl SELECT i/10000, i/100000
FROM generate_series (1,10000000) s(i);

CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl;
ANALYZE tbl;

SELECT stxname, stxkeys, stxdependencies
FROM pg_statistic_ext
WHERE stxname = 's1';

returns:

stxname,stxkeys,stxdependencies
s1,1 2,{"1 => 2": 1.000000}

However, I'm confused by this line

  Looking at this, we can see that Postgres realizes that col1 fully
  determines col2 and therefore has a coefficient of 1 to capture that
  information.

In the example there are many records in col2 that are repeated. So how does col1 'fully determine' col2? What does this mean?

It means that if col1 is identical for two rows, then col2 will also be identical.

In other words, if the functional dependency were perfect, you could remove col2 from the table and use a lookup table that maps col1 to col2. But don't forget that these are only statistical data from a sample of the rows, so even if you see a dependency of 1 in the extended statistics, that is no proof that it is really always so (only very often).


COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query I'd like to do the following in one query using MySQL:

grab a row that has a parent_id of 0
grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0

How can I accomplish this in one query? Please let me know if you need more information, I will gladly be as assistive as I can. I'm not an expert on creating questions so please tell me what more information you need.

Here's an example of what I'm doing now:

select id from messages where parent_id=0

and then

select count(id) from messages where parent_id={{previously_chosen_id}}

How do I get a one shot query? Something like...

select id, count(records where parent_id=the id we just asked for)

Or, is there a better way to handle this? You see, currently I have to run a ton of queries to find the counts, when I'd rather do it in one shot.

This is easily achievable with an in-line subquery :

select  m.id,
        (select count(*) from messages where parent_id= m.id ) as ChildCount
from messages m
where m.parent_id = 0

Note that no group by is needed because a sub-query is used.


Create view to know missing records between two tables I have two tables, Tab_A is a template that generate Tab_B.
Tab_A:
ID,
ORDER,
ASSEMBLY,
DESCRIPTION.

Tab_B:
ID,
ORDER,
ASSEMBLY,
DESCRIPTION.

Tab_B in same situation might have less records then his template Tab_A.

How can I create a view to know less records?
Best regards

This should do it if you are using Microsoft SQL Server:

CREATE VIEW SomeView
AS
SELECT ID, ORDER, ASSEMBLY
FROM Tab_A a
WHERE NOT EXISTS (SELECT NULL FROM Tab_B b WHERE a.ID = b.ID);

If you want to know how many are different, then:

SELECT COUNT(*) FROM SomeView;


is vertical scaling good idea for a mysql database server? I operate a mariadb/mysql database server and i do not want to get into the problems with managing a multi-cluster database server
i have access to huge CPU and RAM and STORAGE when needed

so my question to the experienced and expert db admins is that is vertical scaling good enough? yes downtime is ok as long as data is protected
I rather deal with downtime of some system failure or issue that will only happen very rarely...it is better than the headache with managing a multi-cluster database to me

so what are the bottlenecks i will run into running a database up to 512GB memory and like 128vCPU? and like 2TB of SSD storage?

how is this worse than lets say running like a 8 cluster of mariadb/mysql database with each 64GB memory, 16vCPU and 256GB SSD storage?

yes i know about iops but i mean with lots of redis caching i think one can reduce stress on that part as well

pretty much there is more and more powerful CPU and servers that one can pack lots of stuffs in a single machine..not like years ago where that was not possible or at least easy..now one can pack several Terabytes, TBs(not a typo) of memory in a single machine

please send your advice and tips
thanks!!!

"You can't throw hardware at a performance problem."  Or, rather, you can't do that twice.

CPU speed has not changed significantly in 20 years.
The number of cores increases, but InnoDB has always hit a brick wall before using all cores.
SSDs were a quantum improvement over HDDs, but nothing is on the horizon for the next leap.
Bigger RAM (hence buffer_pool) gets into "diminishing returns".
Ethernet makes a 10x "speedup" every so often.
It would take years to fill a Petabyte disk; don't you want to read the data before that?
etc.

One trick is to virtualize a big hunk of iron by having multiple VMs, each running an instance of MySQL.  This does "horizontal" scaling on a single "vertically" scaled server.  This at least helps with the brick wall I mentioned.  But then you have a big, costly, single-point-of-failure.

My usual answer is "let's see if we can program smarter".

A recurring example is Data Warehousing.  DBAs come to this (and other) forums bemoaning that their 'report' (a big SELECT with a big GROUP BY) is taking too long.  I point out the beauty of a Summary table -- 10x speedup, 10x smaller disk footprint.  Poof, problem gone.  No hardware upgrade could have gotten 10x.

Replication provides nearly infinite scaling, nearly linear, of reads.

Sharding (horizontal scaling) provides nearly infinite scaling, nearly linearly, but it does not work for all applications.

The best I see in Vertical is adding enough RAM to turn an I/O-bound query into a CPU-bound query running in the buffer_pool.  I regularly see a 10x improvement for that.  But it is a one-time improvement.  That is, adding even more RAM won't help any more.

So, to your title question, I say a resounding No.
OK, Vertical may help, but be aware of its limits.


Error 1064 - You have an error in your SQL syntax; I am new to developing field, and I have encountered an error while creating a trigger.Could anyone please figure out what is the error?

DELIMITER $$

CREATE TRIGGER update_feedback_dashboard
AFTER INSERT
ON tblcustomfeedback FOR EACH ROW

BEGIN

DECLARE averageScore float default 0;
DECLARE avgscore float default 0;
DECLARE IdCount float default 0;

SELECT SUM(avg_score) into avgscore, COUNT(Id) into count FROM tblcustomfeedback  WHERE feedbackDate = NOW() ORDER BY Id DESC LIMIT 1;

SET averageScore  =avgscore;
SET IdCount  =  count;

IF (SELECT count(*) FROM tblfeedbackdashbd WHERE Date=CURDATE()) = 0
THEN
 INSERT INTO tblfeedbackdashbd(AverageScoresAday ,Date)     VALUES(averageScore/IdCount,CURDATE());
ELSE
 UPDATE tblfeedbackdashbd SET AverageScoresAday=averageScore/IdCount  WHERE   Date=CURDATE();
END IF;
END $$

The error encountered is,

  [Err] 1064 - You have an error in your SQL syntax; check the manual
  that corresponds to your MySQL server version for the right syntax to
  use near 'COUNT(Id) into count FROM tblcustomfeedback  WHERE
  feedbackDate = NOW() ORDER BY' at line 11

The syntax is not:

SELECT id INTO @x, data INTO @y ...

but it is:

SELECT id, data INTO @x, @y ...

Use:

SELECT SUM(avg_score), COUNT(Id)
  INTO averageScore, IdCount
FROM ... ;


Postgres full text search with better match I have this scripts for full text searching in my postgres database:

function for unaccent concat:

CREATE OR REPLACE FUNCTION f_concat_ws_unaccent(text, VARIADIC text[])
RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT unaccent(array_to_string($2, $1)

and this script is for full text search:

SELECT * FROM geocode.addresses
WHERE  to_tsvector('simple', f_concat_ws_unaccent(' ', country, city,street, housenumber, postcode))
    @@ plainto_tsquery('simple', 'some address') limit 1;

Can you help mi edit this select or create some function, which return result with the best match. For example in my database exist 2 cities. First city called  CityA and second city is composite from 2 words, where one word is same as name first city .. for example Upper CityA. When I execute my script like this:

SELECT * FROM geocode.addresses
WHERE  to_tsvector('simple', f_concat_ws_unaccent(' ', country, city,street, housenumber, postcode))
    @@ plainto_tsquery('simple', 'CityA') limit 1;

I need result with the best match row, but scipt return my Upper CityA row instead CityA.
Any ideas how can I edit this for required result?

You want to sort by ts_rank() or ts_rank_cd()

By default, those terms would give the same ranks, but you can normalize by length

Try:

select
  *
from geocode.addresses
where
  ...
order by
  ts_rank( to_tsvector(...), plainto_tsquery(...), 1) desc
limit 1

Where the ts_rank(..., ..., 1) divides the rank by 1 + the logarithm of the document length

There are other ts_rank normalizations in the link


How to optimize a SQL Server query to fetch the results faster Currently I am having a situation where I have to import around 0.5 Million rows into a SQL Server table named BARCODE_MANAGEMENT, which has the following columns:

ID int (identity column),
SETUP_ID int
CUSTOMER_ID int,
BARCODE_LABEL nvarchar(20),
REGULATORY_AGENCY_ID int,
PACKAGE_LEVEL_ID int,
BATCH_NUMBER nvarchar(20),
PRODUCT_CODE nvarchar(20) ,
SERIAL_LIFECYCLE_ID int,
PACK_LIFECYCLE_ID int,

There is no problem in importing and processing the data using ASP.NET program.

Now when I query the database view (BARCODE_MANAGEMENT_VIEW), using the following query , I get a time out on my ASP.NET program as the query execution takes more than 1 min and 40 sec.

SELECT * FROM dbo.BARCODE_MANAGEMENT_VIEW
        WHERE
             (CUSTOMER_ID=@CUSTOMER_ID OR @CUSTOMER_ID=-1) AND
             (REGULATORY_AGENCY_ID=@REGULATORY_AGENCY_ID OR @REGULATORY_AGENCY_ID=-1) AND
             (BATCH_NUMBER= @BATCH_NUMBER OR @BATCH_NUMBER='-1') AND
            (PACKAGE_LEVEL_ID=@PACKAGE_LEVEL_ID OR @PACKAGE_LEVEL_ID=-1) AND
             (PRODUCT_CODE=@PRODUCT_CODE OR @PRODUCT_CODE=-1) AND
            (SERIAL_LIFECYCLE_ID=@SERIAL_LIFECYCLE_ID OR @SERIAL_LIFECYCLE_ID=-1)  AND
            (PACK_LIFECYCLE_ID=@PACK_LIFECYCLE_ID OR @PACK_LIFECYCLE_ID=-1)
        ORDER BY ID ASC

I have created the following indexes as well on the table and not sure how I can do it on a SQL VIEW:

CREATE NONCLUSTERED INDEX [IX_RetrieveList] ON [dbo].[BARCODE_MANAGEMENT]
(
    [SETUP_ID] ASC,
    [CUSTOMER_ID] ASC,
    [REGULATORY_AGENCY_ID] ASC,
    [BATCH_NUMBER] ASC,
    [PACKAGE_LEVEL_ID] ASC,
    [PRODUCT_CODE] ASC,
    [SERIAL_LIFECYCLE_ID] ASC,
    [PACK_LIFECYCLE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

What am I doing wrong. I am a newbie to SQL Server database coding and I would appreciate any suggestions on how I could add new indexes to query for faster retrieval.

The first field in your index is SETUPID but this does not appear in the query, rendering the index unable to support this query.

Which of these fields is the best "discriminator" - the one that filters the rows down the quickest?  That should appear first in your index.

I would suggest that an Index on just CUSTOMER_ID might be a better place to start.


LOAD DATA stuck at Null State MySQL I'm stuck trying to move one table to another, due to columns now no longer having proper references/meaning to their column titles. In doing this, I've created a new table with the same number of columns as the original, simply with the column names changed to better reflect the data coming in.

My problem is, the data is over 50 million rows, so I'm unable to simply alter the columns in place, so I've made a new table that I wish to use moving forward anyway. I've made the necessary column changes, but now when I try to load the data in, it looks like the process is simply stick.

+-----+------+-----------+------+---------+------+-------+----------------------------------

--------------------------------------------------------------------+
| Id  | User | Host      | db       | Command | Time | State | Info                                                                                |
+-----+------+-----------+------+---------+------+-------+------------------------------------------------------------------------------------------------------+
| 481 | root | localhost | database | Query   | 8617 | NULL  | LOAD DATA  LOCAL INFILE '/home/SQLBackUps/file.csv' INTO TABLE `tablename` FIELDS   |
| 483 | root | localhost | database | Sleep   | 7174 |       | NULL                                                                                |
| 485 | root | localhost | database | Query   |    0 | NULL  | show processlist                                                                                |
+-----+------+-----------+------+---------+------+-------+------------------------------------------------------------------------------------------------------+

I ran the talk using mysqlimport with the necessary flags, so it should in theory at least be running, but as the state says null, I'm not sure what that really means as NULL, according to the documentation, only appears for show processlist. The file's permissions are as follows:

-rw-rw-rw-  1 mysql mysql 14141022004 May 15 16:42 file.csv

The file was created using SELECT INTO OUTFILE ...

Is there anything I can do to speed up this process? Right now it's on a very limited Dev machine with 2GB of RAM and an okay processor, so I understand it will take a bit of time, but given it's been over 2 hours and it's still in a null state, I'm stuck wondering if something is wrong, or if there is anything I can do to speed this process up.

Are there other ways to import the data quickly? Are there any flags I can use to speed this process up? Any, and all help is appreciated.

Update:

I started the process again in the very early morning, and have let it run for about 7 and a half hours now, and the process list looks as follows:

mysql> show processlist;
+----+------+-----------+------+---------+-------+-------+------------------------------------------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time  | State | Info                                                                                |
+----+------+-----------+------+---------+-------+-------+------------------------------------------------------------------------------------------------------+
|  9 | root | localhost | db   | Query   | 22978 | NULL  | LOAD DATA INFILE '/home/SQLBackUps/file.csv' INTO TABLE table FIELDS TERMINATED |
| 15 | root | localhost | db   | Query   |     0 | NULL  | show processlist                                                                                |
+----+------+-----------+------+---------+-------+-------+------------------------------------------------------------------------------------------------------+

Here is one thing that caught my eye when you replied to my comment: The target table is InnoDB and you are using LOAD DATA INFILE. I see two issues
ISSUE #1 : LOAD DATA INFILE
While LOAD DATA INFILE can load InnoDB tables, that command can be tuned for loading MyYSAM tables. There is only one option to do this: bulk_insert_buffer_size. either setting to very large or setting it to zero to disable it.
There is no synonymous provision for InnoDB.
ISSUE #2 : InnoDB Storage Engine
Let's take a look at the InnoDB Architecture

Now, picture yourself pushing 50 millions rows into one InnoDB table as a single transaction giving all the plumbing depicted in this elaborate illustration.
To ensure data consistency in the event of a crash, your data has to be written in three places:

There are 128 rollback segments in the System Tablespace (Physical File ibdata1). Your incoming table data must pile up on one Rollback Segment like defensive tackles on a quarterback.
You have an active Double Write Buffer in the System Tablespace. As the name implies, data is being written twice. InnoDB will write to the Double Write Buffer first before writing back to the .ibd files. Those data are used as source data for crash recovery.
The data are also being written in the Transaction Logs (Redo Logs in the Bottom Right Corner) via the Log Buffer. The physical files are ib_logfile0 and ib_logfile1.

My Perspective
InnoDB can handle 1024 current transactions but there are only 128 rollback segments. If there are other transactions going on, you got a New York City traffic jam on your hands. With all the InnoDB Internals to manage through your bulk insert, seeing NULL in the processlist should not be a surprise. You should look at four(4) things to make sure they are up-to-date:

filesize of ibdata1
timestamp on ibdata1
timestamp on ib_logfile0
timestamp on ib_logfile1

SUGGESTIONS
You could set one or more of the following

Disable the Double Write Buffer (set innodb_doublewrite to 0). please set it back to 1 afterwards.
Increase the InnoDB Buffer Pool (increase innodb_buffer_pool_size)
Increase the InnoDB Log File Size (set inndo_log_file_size to 2G)
Increase the InnoDB Log Buffer Size (set inndo_log_buffer_size to 512M)
Change Transaction Flush Behavior (set innodb_flush_log_at_trx_commit to 0). This will disables ACID Compliance (could lost 1 second of transactions upon a crash) but will increase InnoDB Write Performance.
Increase Write IO Thread (set innodb_write_io_threads to 64. You may as well set innodb_write_io_threads to 64)

GIVE IT A TRY !!!


Oracle SQL: Outer Join Not Working As Expected I am joining two tables, T_ADMSTU_PROG_ENROLL (renamed prog_enroll) and T_ADMGRAD_STUDENT (renamed grad). I would expect the result to have the following columns:

TERM_CODE, STU_INST_UID, MAJOR_DESC, MAJOR_CIP, B_GRADUATION_TERM

keyed on prog_enroll.STU_INST_UID = grad.STU_INST_UID and prog.enroll TERM_CODE = B_GRADUATION_TERM and include all rows from both tables. I am, however, missing rows from T_ADMGRAD_STUDENT where the value B_GRADUATION_TERM is not in the TERM_CODE. What am I doing wrong?

SELECT prog_enroll.TERM_CODE, prog_enroll.STU_INST_UID, prog_enroll.MAJOR_DESC,prog_enroll.MAJOR_CIP, grad.B_GRADUATION_TERM FROM
(SELECT DISTINCT TERM_CODE, STU_INST_UID, LISTAGG(CIP_CODE,'+') WITHIN
GROUP(ORDER BY CIP_CODE) as MAJOR_CIP,
LISTAGG(CIP_DESCR,'+') WITHIN GROUP(ORDER BY CIP_CODE) as MAJOR_DESC
FROM
T_ADMSTU_PROG_ENROLL
WHERE
DEGREE_LEVEL_CODE = 'B' AND COLL_TYPE = 'MT'
GROUP BY TERM_CODE, STU_INST_UID) prog_enroll
FULL OUTER JOIN
(SELECT DISTINCT STU_INST_UID, B_GRADUATION_TERM
FROM
T_ADMGRAD_STUDENT
WHERE
STUDENT_ACADEMIC_LEVEL = 'US'
)  grad
ON
grad.STU_INST_UID = prog_enroll.STU_INST_UID
AND
grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE;

Perhaps changing your JOIN condition slightly might help if you want
the value B_GRADUATION_TERM that doesn't match TERM_CODE without creating a Cartesian join :

From:

grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE;

To:

 (grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL);

As shown completely here:

SELECT prog_enroll.TERM_CODE, prog_enroll.STU_INST_UID, prog_enroll.MAJOR_DESC,prog_enroll.MAJOR_CIP, grad.B_GRADUATION_TERM FROM
(SELECT DISTINCT TERM_CODE, STU_INST_UID, LISTAGG(CIP_CODE,'+') WITHIN
GROUP(ORDER BY CIP_CODE) as MAJOR_CIP,
LISTAGG(CIP_DESCR,'+') WITHIN GROUP(ORDER BY CIP_CODE) as MAJOR_DESC
FROM
T_ADMSTU_PROG_ENROLL
WHERE
DEGREE_LEVEL_CODE = 'B' AND COLL_TYPE = 'MT'
GROUP BY TERM_CODE, STU_INST_UID) prog_enroll
FULL OUTER JOIN
(SELECT DISTINCT STU_INST_UID, B_GRADUATION_TERM
FROM
T_ADMGRAD_STUDENT
WHERE
STUDENT_ACADEMIC_LEVEL = 'US'
)  grad
ON
grad.STU_INST_UID = prog_enroll.STU_INST_UID
AND
(grad.B_GRADUATION_TERM = prog_enroll.TERM_CODE or grad.B_GRADUATION_TERM is NULL);


Is replication possible on two different domains? I am implementing a database replication between different geograhical locations where each one is on a different domain. After going through all the process, when I start the agent it runs and stops.

When I look at the details it shows:

Error messages:
The process could not connect to Subscriber 'Mydomain\myusername'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Source: MSSQLServer, Error number: 18452)

Replication across non-trusted domains or workgroups can be done using Windows Authentication by configuring pass-through authentication.

Create a local Windows account on both the Publisher and Subscriber that has the same username and password.  Use this account for the replication agent process account and have the connections to the publisher, distributor, and/or subscriber impersonate this account.  Ensure the account has the permissions required in Replication Agent Security Model.

This approach is covered in the section Use Windows Authentication to Set Up Replication Between Two Computers Running SQL Server in Non-Trusted Domains in HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet.


Clustered Index Selection - PK or FK? I have a SQL Server 2014 table that looks like the following:

OrderId     int           not null IDENTITY --this is the primary key column
OrderDate   datetime2     not null
CustomerId  int           not null
Description nvarchar(255) null

Some folks on my team have suggested that the clustered index should be on OrderId, but I think that the CustomerId + OrderId would be a better choice for the following reasons:

Almost all queries will be looking WHERE CustomerId = @param, not OrderId
CustomerId is a foreign key to the Customer table, so having a
clustered index with CustomerId should speed up joins
While CustomerId isn't unique, having the additional OrderId column
specified in the index will ensure uniqueness (We can use the UNIQUE keyword when creating the clustered index on those 2 columns, to avoid the overhead of not having uniqueness)
Once data is inserted, the CustomerId and OrderId never change, so these rows wouldn't be moving around after initial write.
Data access happens via an ORM that requests all columns by default, so when a query based on CustomerId comes in, the clustered index will be able to provide all columns without any additional work.

Does the CustomerId and OrderId approach sound like the best option given the above? Or, is OrderId on its own better, since it's a single column that's guaranteeing uniqueness by itself?

Currently, the table has a clustered index on OrderId, and a nonclustered index on CustomerId, but it's not covering, so since we're using an ORM and all columns are requested, it's extra work to retrieve them.  So with this post, I'm trying to consider improving performance with a better CI.

The activity on our DB is about 85% reads and 15% writes.

Community wiki answer:

I think a composite clustered index key with CustomerID as the first column will be best since that's in the WHERE clause of nearly all queries.

There may be more splits compared to an incremental key (or more likely suboptimal page density for a time if you manage and maintain fill factor to avoid 'bad' splits). However, the overall performance improvement for customer queries is substantial, because the key lookup is avoided.

OrderID or OrderDate may be best for the second column depending on your most critical queries.

For example, if customers see a chronological list of recent orders after logging in to a web site, OrderDate should be next, to optimize ORDER BY OrderDate DESC.

If you choose OrderID as the clustered index, with a non-clustered index on CustomerID, you'll still get splits and fragmentation, just in the non-clustered index.


Horizontally scaling SQL Server, distributing the database with sharding I wanted to know if there is any way to distribute a SQL Server (I'm using 2012 version) database accross multiple nodes. I'm trying to compare READ queries performance between SQL Server and MongoDB. The distribution is all set with MongoDB with different shards using virtual machines and I wanted to make a similar setting for SQL Server, but I'm facing lots of trouble.

Is there any material on how to do that? Is that as tough process as it seems?

Is there material on how?

Yes. You can read about it at https://msdn.microsoft.com/en-us/library/dn589797.aspx

  Is the process as tough as it seems?

Sort of. It is time consuming in that it requires careful planning in advance on how you want to structure the data, but if you're doing something with a massive database, I've personally been very pleased with the performance enhancement.


Group by transformed column I have a database with creations and orders, and a bunch of tables linking them.

I want to extract, for each month, the number of ordered creations, and the sum of amount for these orders, but for a specific creation name pattern, and a specific type of product references.

I have this, which doesn't work because the inner query does not knows the yearcrea and monthcrea columns:

select
date_part('year', c.created_at) as yearcrea,
date_part('month', c.created_at) as monthcrea,
p.reference,
count (*) as nb,
(
  select sum(o2.amount_ati_cents)
  from creations as c2
  inner join products as p on c.product_id = p.id
  inner join order_items as oi2 on c2.id = oi2.creation_id
  inner join bundles as b2 on b2.id = oi2.bundle_id
  inner join orders as o2 on o2.id = b2.order_id
  where o2.status NOT IN ('canceled', 'new')
  and c2.name like '% - Premium(%'
  and p2.reference like 'FLA_%'
  and date_part('year', c2.created_at) = yearcrea
  and date_part('month', c2.created_at) = monthcrea
) as ca

from creations as c
inner join products as p on c.product_id = p.id

where c.name like '% - Premium(%'
and p.reference like 'FLA_%'

and c.id in (
    select oi.creation_id
    from order_items as oi
    inner join bundles as b on b.id = oi.bundle_id
    inner join orders as o on o.id = b.order_id
    where o.status NOT IN ('canceled', 'new')
)

group by yearcrea, monthcrea, reference
order by yearcrea desc, monthcrea desc

I have also tried

and date_part('year', c2.created_at) = date_part('year', c.created_at)
and date_part('month', c2.created_at) = date_part('month', c.created_at)

but I get an error: subquery uses ungrouped column "c.created_at" from outer query.

How can I filter a select sub-query with "transformed" columns ?

Maybe I'm looking at it the wrong way and there is a simpler way to extract the info I want ?

You can simplify further:

SELECT date_trunc('month', c.created_at) AS year_month
     , p.reference,
     , count (distinct c.id) as nb_crea,
     , (sum(o.amount_ati_cents) / 100) as ca_euros
from   creations   c
join   products    p on p.id = c.product_id
join   order_items i on i.creation_id = c.id
join   bundles     b on b.id = i.bundle_id
join   orders      o on o.id = b.order_id
where  o.status NOT IN ('canceled', 'new')
and    c.name like '% - Premium(%'
and    p.reference like 'FLA_%'
GROUP BY year_month, reference
ORDER BY year_month DESC;

Or even:

SELECT to_char(c.created_at, 'YYYY-MM') AS year_month
...
GROUP  BY 1, reference
ORDER  BY 1 DESC;

date_trunc() and to_char() in the manual.


Track changes in database structure There has been a change in our DB and one of the SPs has been deleted. We have no idea who did it or when.

Is there any way to find it out?
Or enabling an option to be able to track this in future?

There are couple of ways to do such forensic analysis :

Using Default trace (provided that the default trace is running). The default trace create 5 *.TRC file that are recycled when they arrive to 20 MB size. :

-- create a test procedure .. so that we can drop it
use test_kin
go
create procedure usp_ABOUT_TO_DELETE
as
select name from sysobjects
where type = 'U'
order by name

-- now drop the procedure
drop procedure usp_ABOUT_TO_DELETE

Below is the sql to get details out of the default trace :

         /*
          Object Altered
          Object Created
          Object Deleted
          */

SELECT TE.NAME
    ,v.subclass_name
    ,DB_NAME(t.DatabaseId) AS DBName
    ,T.NTDomainName
    ,t.NTUserName
    ,t.HostName
    ,t.ApplicationName
    ,t.LoginName
    ,t.Duration
    ,t.StartTime
    ,t.ObjectName
    ,CASE t.ObjectType
        WHEN 8259
            THEN 'Check Constraint'
        WHEN 8260
            THEN 'Default (constraint or standalone)'
        WHEN 8262
            THEN 'Foreign-key Constraint'
        WHEN 8272
            THEN 'Stored Procedure'
        WHEN 8274
            THEN 'Rule'
        WHEN 8275
            THEN 'System Table'
        WHEN 8276
            THEN 'Trigger on Server'
        WHEN 8277
            THEN '(User-defined) Table'
        WHEN 8278
            THEN 'View'
        WHEN 8280
            THEN 'Extended Stored Procedure'
        WHEN 16724
            THEN 'CLR Trigger'
        WHEN 16964
            THEN 'Database'
        WHEN 16975
            THEN 'Object'
        WHEN 17222
            THEN 'FullText Catalog'
        WHEN 17232
            THEN 'CLR Stored Procedure'
        WHEN 17235
            THEN 'Schema'
        WHEN 17475
            THEN 'Credential'
        WHEN 17491
            THEN 'DDL Event'
        WHEN 17741
            THEN 'Management Event'
        WHEN 17747
            THEN 'Security Event'
        WHEN 17749
            THEN 'User Event'
        WHEN 17985
            THEN 'CLR Aggregate Function'
        WHEN 17993
            THEN 'Inline Table-valued SQL Function'
        WHEN 18000
            THEN 'Partition Function'
        WHEN 18002
            THEN 'Replication Filter Procedure'
        WHEN 18004
            THEN 'Table-valued SQL Function'
        WHEN 18259
            THEN 'Server Role'
        WHEN 18263
            THEN 'Microsoft Windows Group'
        WHEN 19265
            THEN 'Asymmetric Key'
        WHEN 19277
            THEN 'Master Key'
        WHEN 19280
            THEN 'Primary Key'
        WHEN 19283
            THEN 'ObfusKey'
        WHEN 19521
            THEN 'Asymmetric Key Login'
        WHEN 19523
            THEN 'Certificate Login'
        WHEN 19538
            THEN 'Role'
        WHEN 19539
            THEN 'SQL Login'
        WHEN 19543
            THEN 'Windows Login'
        WHEN 20034
            THEN 'Remote Service Binding'
        WHEN 20036
            THEN 'Event Notification on Database'
        WHEN 20037
            THEN 'Event Notification'
        WHEN 20038
            THEN 'Scalar SQL Function'
        WHEN 20047
            THEN 'Event Notification on Object'
        WHEN 20051
            THEN 'Synonym'
        WHEN 20549
            THEN 'End Point'
        WHEN 20801
            THEN 'Adhoc Queries which may be cached'
        WHEN 20816
            THEN 'Prepared Queries which may be cached'
        WHEN 20819
            THEN 'Service Broker Service Queue'
        WHEN 20821
            THEN 'Unique Constraint'
        WHEN 21057
            THEN 'Application Role'
        WHEN 21059
            THEN 'Certificate'
        WHEN 21075
            THEN 'Server'
        WHEN 21076
            THEN 'Transact-SQL Trigger'
        WHEN 21313
            THEN 'Assembly'
        WHEN 21318
            THEN 'CLR Scalar Function'
        WHEN 21321
            THEN 'Inline scalar SQL Function'
        WHEN 21328
            THEN 'Partition Scheme'
        WHEN 21333
            THEN 'User'
        WHEN 21571
            THEN 'Service Broker Service Contract'
        WHEN 21572
            THEN 'Trigger on Database'
        WHEN 21574
            THEN 'CLR Table-valued Function'
        WHEN 21577
            THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
        WHEN 21581
            THEN 'Service Broker Message Type'
        WHEN 21586
            THEN 'Service Broker Route'
        WHEN 21587
            THEN 'Statistics'
        WHEN 21825
            THEN 'User'
        WHEN 21827
            THEN 'User'
        WHEN 21831
            THEN 'User'
        WHEN 21843
            THEN 'User'
        WHEN 21847
            THEN 'User'
        WHEN 22099
            THEN 'Service Broker Service'
        WHEN 22601
            THEN 'Index'
        WHEN 22604
            THEN 'Certificate Login'
        WHEN 22611
            THEN 'XMLSchema'
        WHEN 22868
            THEN 'Type'
        ELSE 'Hmmm???'
        END AS ObjectType
FROM [fn_trace_gettable](CONVERT(VARCHAR(150), (
                SELECT TOP 1 value
                FROM [fn_trace_getinfo](NULL)
                WHERE [property] = 2
                )), DEFAULT) T
INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
INNER JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
    AND v.subclass_value = t.EventSubClass
WHERE TE.NAME IN (
        'Object:Created'
        ,'Object:Deleted'
        ,'Object:Altered'
        )
    -- filter statistics created by SQL server
    AND t.ObjectType NOT IN (21587)
    -- filter tempdb objects
    AND DatabaseID <> 2
    -- get only events in the past 24 hours
    AND StartTime > DATEADD(HH, - 24, GETDATE())
ORDER BY t.StartTime DESC;

Digging into Transaction log -- More advance ! Note the use of undocumented fn_dblog.

Find out the transaction ID

SELECT [Current LSN]
,Operation
,[AllocUnitName]
,[Lock Information]
,[Transaction ID]
,[Description]
,[Begin Time]
,[Transaction Name]
,[Transaction SID]
FROM fn_dblog(NULL, NULL)
WHERE [Operation] = N'LOP_DELETE_ROWS'
AND [AllocUnitName] = N'sys.sysschobjs.clst'
--- change here for the object name you think got dropped/deleted
--- can be table, stored procedure, etc
AND CHARINDEX(cast(N'ABOUT_TO_DELETE' AS VARBINARY(4000)), [Log Record]) > 0

Now find out the transaction based on the above transaction ID:

           SELECT [Current LSN]
           ,Operation
           ,[AllocUnitName]
           ,[Lock Information]
           ,[Transaction ID]
           ,[Description]
           ,[Begin Time]
           ,[Transaction Name]
           ,SUSER_SNAME([Transaction SID]) as CULPRIT_USER -- WE Got the Cluprit!!
         FROM fn_dblog(NULL, NULL)
         WHERE [Transaction ID] = N'0000:0000026c';

Excellent References :

The default trace in SQL Server - the power of performance and security auditing
How to read and interpret the SQL Server log by Remus Rusanu


Postgres select join json query I have a problem to make a postgres query with join from data of json.

I have 2 tables

The first one is media:

id as integer
path as string

and an other table playlist

id as integer
media as string (with my json inside)

For example

Media:

1   /img/a.jpg
2   /img/b.jpg
3   /img/c.jpg
4   /img/d.jpg
5   /img/e.jpg

Playlist:

1   ["1", "2", "5"]
2   ["4", "2", "3", "1"]

I want to make an SELECT on playlist and get back all path ideally as json or if not possible something like (example for playlist 1):

1   /img/a.jpg
2   /img/b.jpg
3   /img/e.jpg
...

You need to "unnest" the elements of the JSON array and then join that to your media table:

The following query assumes that media is a column of type jsonb. If that is not the case (which it should be) you need to cast it media::jsonb.

select m.id, m.path
from media m
  join (
    select jm.id::integer as media_id
    from playlist p
      cross join jsonb_array_elements_text(p.media) as jm(id)
    where p.id = ? --<< your playlist id here
) t on m.id = t.media_id;

jsonb_array_elements_text(p.media) returns all elements of the array as rows and  as jm(id) defines a table alias for that result giving the column that is returned the name id. The expression jm.id::integer as media_id then converts the string value into a proper integer so it can be used in the join condition.

Unrelated, but: this is a case of unnecessary JSON. If you normalize your data model, queries like that will be a lot easier and more efficient. Plus: you can define proper foreign key constraints between playlist and media.


Your entities are based on hours, not days, so you should go with one row per hour, just like you did, not a full day in a row.

I did not understand if when you say data between two timestamps, if timestamp refers to the "inserted" column, or if it refers to the date and times on the data file. If it is the latter one, then having a datetime column for your date and hour, instead of two separate columns, would help you doing your comparisons. You would place an index on this datetime column and when you'd want to get data between two datetimes, or even one specific datetime, it would give you the results ultra-fast.


Best practise for polymorphic associations in MySQL I have a MySQL database with 3 tables holding the main classes of data:

 companies (company_id)
 persons (person_id, company_id)
 loans (loan_id, company_id)

Both a 'loan' and a 'person' belong to a company. A company can have loans and a company can have people (such as directors, employees etc.)

There are several scenarios where other data can belong to either a company, a person or a loan, such as 'notes'. For example a user can add a 'note' specific to either a company, a person or a loan.

There are other examples of this such as 'addresses' and 'phone numbers', which can belong to either a company or a person.

I obviously want to have a single table for data such as 'notes', 'addresses' and 'phone numbers', however I am struggling with the best way to link these to companies, people and loans.

Lets use 'notes' as our example here.

Is it better practice to:

1/ Have 3 extra tables linking 'notes' to companies people and loans:

notes(note_id)
persons_notes(person_id, note_id)
companies_notes(company_id, note_id)
loans_notes (loan_id, note_id)

2/ Have a single table 'entities' which has a primary key entity_id used to link all 3 tables:

entities(entity_id)
persons(person_id, entity_id)
companies(company_id, entity_id)
loans (loan_id, entity_id)
notes (note_id, entity_id)

The latter makes some sense in terms of logic, but does not feel right as persons and companies are a completely different class of data to loans. Somehow it feels dirty.

Notes and addresses will be in a one to many relationship with loans, persons and companies. That is, a company may have many addresses or notes. That is the reason I was thinking of the link tables.

A company can have a registered address, and unlimited trading addresses. A person can have a current address, and previous addresses (if at current address less than 3 years).

In this case, it is the company which owns the loan (business loan). The people belong to the company and are usually the company's directors. So, a company can have many persons and many loans.

A company/person/loan can each have many notes
A company/person can each have many addresses

This question (in various forms) crops up regularly on this forum.

It is called EAV (Entity Attribute Value). For good reason, it is called an antipattern. There are many reasons not to use this form of data model. They are outlined here, here and here (and links within + look at other posters' replies in the same threads).

Not everyone agrees that EAV is always a bad thing, just most of the time. Aaron Bertrand (a big hitter on this group) has written this post on the matter. My advice, however, is to stick with a conventional design and (probably) make life easier for yourself down the line.

Why not just have three main tables as you describe

Person
Company
Loan

And two "subsidiary" tables

Address
Note

Then Person, Company and Loan can have Address_id and Note_id fields. No need for complicated linking tables (at least not yet :-) ). Or to start with, you could even include Note and Address in your main table (if there's ever going to be one Address and one Note (BLOB) field)?

Whatever you do, avoid EAV until you're an experienced data-modeller. p.s. welcome to the forum!

[EDIT]

Could you tell us which RDBMS you are using? It might have some bearing on my replies. The moderators here don't like long ping-pong type chats.

I  "threw together" a diagramme and script which should get you started.

Any further problems/issues, let me know. Much of app design and data modelling is trial and error - you produce a data model, start programming against it and you find all sorts of ways in which your original ideas can't work. That's what prototyping is for. Give it a bash and then work around issues.

And the script from that diagramme.

CREATE TABLE IF NOT EXISTS `mydb`.`Person` (
  `Person_id` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NOT NULL,
  `Employed_by` INT NULL,
  PRIMARY KEY (`Person_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Loan` (
  `Loan_id` INT NOT NULL AUTO_INCREMENT,
  `Amount` DECIMAL NOT NULL,
  `Other_fields` VARCHAR(45) NULL,
  PRIMARY KEY (`Loan_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Person_loan` (
  `Person_id` INT NOT NULL,
  `Loan_id` INT NOT NULL,
  INDEX (`Person_id` ASC),
  INDEX (`Loan_id` ASC),
  INDEX (`Person_id` ASC, `Loan_id` ASC),
  CONSTRAINT `Person_Loan_P_FK`
    FOREIGN KEY (`Person_id`)
    REFERENCES `mydb`.`Person` (`Person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Person_Loan_L_FK`
    FOREIGN KEY (`Loan_id`)
    REFERENCES `mydb`.`Loan` (`Loan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Company` (
  `Company_id` INT NOT NULL AUTO_INCREMENT,
  `Company_name` VARCHAR(45) NULL,
  PRIMARY KEY (`Company_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Works_for` (
  `Person_id` INT NULL,
  `Company_id` INT NULL,
  INDEX `Works_For_P_FK_idx` (`Person_id` ASC),
  INDEX `Works_For_C_FK_idx` (`Company_id` ASC),
  CONSTRAINT `Works_For_P_FK`
    FOREIGN KEY (`Person_id`)
    REFERENCES `mydb`.`Person` (`Person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Works_For_C_FK`
    FOREIGN KEY (`Company_id`)
    REFERENCES `mydb`.`Company` (`Company_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Company_loan` (
  `Company_id` INT NOT NULL,
  `Loan_id` INT NOT NULL,
  INDEX `Company_loan_C_L_PK` (`Company_id` ASC, `Loan_id` ASC),
  INDEX `Company_Loan_L_FK_idx` (`Loan_id` ASC),
  CONSTRAINT `Company_Loan_C_FK`
    FOREIGN KEY (`Company_id`)
    REFERENCES `mydb`.`Company` (`Company_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Company_Loan_L_FK`
    FOREIGN KEY (`Loan_id`)
    REFERENCES `mydb`.`Loan` (`Loan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


Do I need to use NVARCHAR rather than VARCHAR to properly store UTF8 characters in Oracle? I want my Oracle database to properly store ALL possible UTF8 characters. If NLS_CHARSET is set to AL32UTF8 is that all I need to do? Or do I need to also make sure I use NCHAR and NVARCHAR rather than CHAR and VARCHAR to properly store UTf8 characters?

Good question. I'll give a simplified answer.

Oracle supports two character sets simultaneously, by way of different datatypes and parameters. A "normal" database-wide characterset and a "national" characterset.

Now, the "normal" characterset affects the way that VARCHAR2, CHAR and CLOB data is stored. The "national" characterset affects the way that NVARCHAR2, NCHAR and NCLOB data is stored.

These are dictated at database creation time. For example:

CREATE DATABASE ....
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
..... BLAH;

You can see these by checking in your DB. For example:

SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL>

NLS_CHARACTERSET is for CHAR/VARCHAR2, NLS_NCHAR_CHARACTERSET is for NCHAR/NVARCHAR2.

If your normal characterset is UTF%, you're generally safe staying away from the N% datatypes unless you have some strange internationalisation requirements. If so, feel free to edit your question!


Does Azure SQL DW support partitioned views? I've got a really expensive join in data warehouse that isn't distribution aligned. Unfortunately the join key is nullable, and only half the data has a value, meaning that it wouldn't be a good candidate for the distribution key.

Would be be possible to created a partitioned view where the null half of the table is round robin distributed and the other half is distributed on the available key values?

Update Jul 2019 - as at July 2019, Azure SQL Data Warehouse now supports materialized views.

The engine is implemented somewhat differently in SQL Data Warehouse so it does not support the feature Partitioned Views as SQL Server does.  For example CHECK constraints are required to enable these to work correctly, but CHECK constraints are not supported in Azure SQL Data Warehouse.

You could spoof up something like this, ie basically a view over two tables each distributed as you described but they would have to be copies of the original tables in order to change the distribution.  This would add extra complexity to your process.  You could create the copies using CTAS which is powerful in SQL DW, but it's impossible to say if it would improve your processing times.

Make sure you have created all relevant statistics.  From experience I find int\bigint columns work faster with columnstore rather than varchar.

Perhaps you can give an example of your tables, DDL, distributions, query and sample data and I can have a look at it.