Compare Validate TSQL Data Warehouse to SSAS How do I compare Data in T-SQL DataWarehouse to SSAS Cube?

I would like to conduct this from SSMS (SQL Server Studio Management Studio).

Example

Select CustomerName from dbo.DimCustomer where CustomerId = 5

SHOULD EQUAL

SELECT [Customer].[CustomerName].Members ON ROWS FROM [DimCustomer] where [Customer].[CustomerId].[5]

https://xzwang.wordpress.com/2013/06/17/validatingcubesvsrelational/

Following blog recommends OPENROWSET. Is there any other method to validate data in same environment rather than OPENROWSET in SSMS? Otherwise, team will utilize this or may research Powershell or C#.

select cast("[Customer].[Customer].[Customer].[Email Address]" as nvarchar(50)) EmailAddress,
"[Measures].[Internet Sales Amount]" SalesAmount
from openrowset('MSOLAP', 'datasource=localhost;Initial Catalog=AdventureWorksDW2012Multidimensional-EE;',
' SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS,
NON EMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS ) }
DIMENSION PROPERTIES [Customer].[Customer].[Email Address] ON ROWS FROM [Adventure Works] ')

If you wish to run MDX within SSMS then I believe OPENROWSET is your only option. Read about it carefully as there are some permissions/configurations that you must change to enable it, and they may affect your security.

If you do not have to use SSMS then you could use any programming language that can call the database drivers which communicate with SQL Server and OLAP, such as ADOMD and ADODB. You mention C# which would be perfect - use Microsoft.AnalysisServices.AdomdClient namespace. Or even a simple VBScript file could work if you want a quick and simple solution on Windows, just for short-term testing.


Scalability limitations of PostgreSQL and MySQL I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB.

I suspect that limits as such do exist as one would not come up with Netezza, Greenplum, or Vertica, etc, however I would like to ask if anyone here has a reference to any research paper or formal case studies where these limits are quantified.

There's no simple answer to your question, but here are a few things to think about.

First, scale isn't the only thing to worry about.  What you do with your data is.  If you have 500 tables 30 TB of data, and you are doing simple OLTP with very little reporting, I don't think you will have too many problems.  There are 32TB databases on PostgreSQL out there.  However, at the same time performance will degrade somewhat because it is having to hit disk on everything.  Similarly if you have 50TB if data but have a commonly hit set of about 100GB, then you can build a server with enough RAM to keep that part of the db in memory and you are golden.

On the other hand if you are trying to take the mode (most common value) out of 1TB of data, it doesn't matter what system you are using, this is going to be painful with or without sharding. (Edit:  Sharding may, in fact, make this problem worse.)

The major problems you will run into with huge db's on MySQL and PostgreSQL involve the fact that neither supports intraquery parallelism.  In other words a query is run as a single block by a single thread, and it can't be broken down into pieces and run separately.  This is most often an issue when running large analytical queries over large amounts of data.  This is where Postgres-XC and Green Plum come to the rescue since they separate storage from execution, and can do this at the coordinator level.  Note that Postgres-XC and Green Plum essentially use sharding internally but the coordinators enforce all consistency globally.

With intraquery parallelism you can break up the query, have different processors/disk I/O channels run parts of it, and report back pieces of the result set to be assembled and passed back to the application.  Again, this is usually most helpful in analytic rather than transaction processing loads.

The second thing is that some systems, like Vertica or Greenplum store columns of information together.  This makes the system harder to use from an OLTP perspective and decreases performance there, but it drastically increases performance for large analytic workloads.  So this is a workload-specific tradeoff.

So the answer is that once you get above 1-2 TB in size you may find yourself faced with a number of tradeoffs between systems and workloads.  Again this is specific to databases, size of working sets, etc.  However at this point you really have to go with snowflake systems, i.e. ones unique and tailored to your workload.

This of course means that the limits are not generally quantifiable.

Edit:  I have now worked with a 9TB database which handles a mixture of decision support and transactional processing workloads in PostgreSQL.  The single largest challenge is that if you have questions that hit large portions of the data set, you will have to wait a while for the answer.

However with careful attention to the fundamentals (including indexes, autovacuum, how these work on the low level, etc) and sufficient computing resources, these are entirely manageable (and I estimate would be manageable well into the 30TB range in Pg).

Edit2:  Once you head to 100TB though what works will depend on your data set.  I am working on one right now that will not scale into this range because it will hit the 32TB per table limit in PostgreSQL first.


Difference between Physical Reads and Read-Ahead Reads I am trying to understand read-ahead reads, but it seems a bit complicated to me. I searched on the web and got the following:
From Reading Pages (Microsoft documentation):

Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query.

From an answer to Why is 'physical reads' less than 'read-ahead reads' & 'logical reads' in SQL Server for first time execution of query? by huntharo on Stack Overflow:

Physical Read - The query is blocked waiting for the page to be read from disk into the cache for immediate use.
Read-Ahead Read - The page is being read before it blocks the query and is read into the cache as are all reads. Read-Aheads are possible when you are scanning an index, in which case the next leaf pages in the index can assume to be needed and the read can be initiated for them before the query actually says it needs them. This allows the disk to be busy while the db engine is examining the contents of previously fetched pages.

Maybe someone could clarify the above using their own explanation because I can't find a detailed explanation for read-ahead reads.
To set an example, look at statistics io info:
Table 'TestLarge'. Scan count 1, logical reads 159185, physical reads 348, read-ahead reads 159209

A query always reads data from memory (a logical read). Your example query scanning the TestLarge table touched 159,185 8KB memory pages during its execution.
During execution, SQL Server does two things.
1. It reads data from the pages that belong to the table.
If the required page is already in memory, a logical read is recorded.
If the required page is not in memory, a physical read is recorded.

The page is brought into memory from persistent storage.
The query is blocked until this read completes.
This happened 348 times to your test query.
A logical read is also counted when SQL Server processes the page (that is now in memory) to satisfy your query.

2. It issues read-ahead reads.
Every so often during the scanning operation, SQL Server spends a moment managing read-ahead:

SQL Server gathers a list of 8KB pages that may well be encountered by the current operation in the near future. You can think of this as the engine "looking ahead" of the current scan position for the pages that come next. It achieves using IAM (allocation map) pages or b-tree levels above the leaf, depending on the type of scan.
Any pages on this "look-ahead" list that are not already in memory are passed to the operating system in one or more asynchronous read requests. These are counted as read-ahead reads.
The operating system handles reading the pages into SQL Server memory, and notifies SQL Server when the reads are complete.
The SQL Server thread that issued the asynchronous reads is not blocked. It can continue scanning pages that are in memory while the operating system fetches read-ahead pages in the background, on a separate thread.
Your test query read 159,209 pages into memory via the read-ahead mechanism.

An analogy
Imagine there is a book. You are given the index only. The rest of the book is in the local library. The library has a rule that the whole book cannot be checked out, and only 50 pages at most can be taken from the library at each visit.
Your task is to assemble the book at home in the order pages are referenced in the index (a to z). You are not allowed to leave home, but you have a friend that can go to the library on your behalf.
The first entry in the index is for "aardvark", which appears on page 392 of the book.
You realize it will be very inefficient to do this task a page at a time, so instead of sending your friend to the library for page 392, you read 50 entries in index order, and give your friend that list of pages to take to the library. You count 50 read-ahead reads at this point.
Now you turn back to processing "aardvark". You don't have page 392 in front of you, so you have to wait, doing nothing, until your friend gets back. This is a physical read.
When your friend arrives you count a logical read when you process page 392.
You could start on the other 49 pages your friend brought back (counting a logical read for each one), but you realize it will be more efficient if you give your friend another list of pages to fetch from the library while you are busy with the work in front of you.
Each time you send your friend to the library with a list of pages to fetch, you count read-ahead reads. Each time you process a page in front of you, you count a logical read. If you find yourself without the next page you need (because your friend is too slow), you count a physical read.
The overall task completes quicker when you and your friend can overlap your activities effectively. They can be busy fetching pages you will need soon, while you are busy processing the pages you have in front of you. When this works well, you never have to wait for the next page you need, though you do spend a little time telling your friend what to do.


Duplicate data or Relationships? Given two tables:

Product

int    id
int    operator_id
string name
string description
int    price

Operator

int    id
string logo_path
string tos_path
int    tax_number

I need to call multiple Product a few times. Most of the times I call a Product, I need to get the Operator's name and logo.

I know that we should avoid duplicating data when possible, and be using more elegant ways to bind data, but in this case, isn't it more efficient to put int operator_name and int operator_logo into Product table instead of calling at each request, one more request to find the Operator?

Or is it the really point of relationships, DBs being OK with dealing with one more request instead of duplicating the data?

A bit beginner into optimization and not really knowing what and where to search into to find answers, thanks for yours.

Let's assume your tables are defined like this (by converting your descriptions to SQL Data Definition Language - DDL):

CREATE TABLE operator
(
    id integer NOT NULL PRIMARY KEY,
    logo_path varchar(255),
    tos_path varchar(255),
    tax_number integer
) ;

CREATE TABLE product
(
    id integer NOT NULL PRIMARY KEY,
    operator_id integer NOT NULL REFERENCES operator(id),  /* This is actually ignored by MySQL, but not by "well-behaved" databases */
    name varchar(100),
    description varchar(255),
    price decimal(12,2)
) ;

Let's put some sample data:

INSERT INTO
    operator
    (id, logo_path, tos_path, tax_number)
VALUES
    (1000, '/path/to/logo/1000', '/path/to/tos/1000', 1234),
    (1001, '/path/to/logo/1001', '/path/to/tos/1001', 5678)
;

INSERT INTO
    product
    (id, operator_id, name, description, price)
VALUES
    (1, 1000, 'Product Name', 'Product Description', 1234.56),
    (2, 1001, 'Product 2', 'Description 2', 2345.67)
;

... and now we can perform a SELECT with a JOIN

SELECT
    product.id, product.name, product.price, operator.logo_path,
    operator.tos_path, operator.tax_number
FROM
    product
    JOIN operator ON operator.id = product.operator_id ;

This is the result you would get, you retrieve the data from both product and operator with just one query. The database will handle how to fetch it from the tables. One of the objectives of relational databases is to just let them do this kind of things

id | name         |   price | logo_path          | tos_path          | tax_number
-: | :----------- | ------: | :----------------- | :---------------- | ---------:
 1 | Product Name | 1234.56 | /path/to/logo/1000 | /path/to/tos/1000 |       1234
 2 | Product 2    | 2345.67 | /path/to/logo/1001 | /path/to/tos/1001 |       5678

You can see the full example to play with at dbfiddle here

You always want to use relationships, and JOIN, and not repeat. This is formally called Normalizing your database

Of course, all rules tend to have exceptions. Having denormalized data is left to a few specific cases (normally, data which is read only, and where a need for speed is of the utmost concern; this is typical of data warehouses and for analytics (OLAP)).

If in doubt: normalize. Always.


ALTER DEFAULT PRIVILEGES - permission denied I have a problem with ALTER DEFAULT PRIVILEGES. I grant the permission:

ALTER DEFAULT PRIVILEGES
    FOR USER user_name
    IN SCHEMA schema_name
    GRANT SELECT ON TABLES TO user_name;

in next step I create a table

create table schema_name.a (q int);

Now, I connect to dabasabe using conn_user and try to read the data in the new table.

select * from schema_name.a

and I receive the error:

SQL Error [42501]: ERROR: permission denied for relation a
  org.postgresql.util.PSQLException: ERROR: permission denied for relation a

Do you have any ideas why the error appears?

The problem here is the ALTER DEFAULT PRIVILEGES statement you issued will apply only to tables that are created by user_name:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

and

target_role
The name of an existing role of which the current role is a member. If FOR ROLE is omitted, the current role is assumed.

So in your case you either use
ALTER DEFAULT PRIVILEGES
    IN SCHEMA schema_name
    GRANT SELECT ON TABLES TO user_name;

(without the FOR USER clause), or create the table as user_name (in which case they'll be the owner, therefore no need to grant anything).
In any case, it's a good practice to always create DB objects as a given user, so that the access privileges are predictable.


Query to flatten schema I have the following tables and columns:

Users

id | other-non-important-columns

Containers

id | Users_id  | other-non-important-columns

Content

id | Container_id | other-non-important-columns

This is a 3 level hierarchy, however I would like to get rid of the second tier (Containers), and associate Content directly with Users i.e.

Content

id | Users_id

What query can I construct to populate the Content table with Users_id?

I am planning to keep Containers. Right now, content must belong to one container. The ultimate solution moving forward will be:

Content belongs to 1 user.
Content can belong to 0 or more containers

...but I'm trying to think through this one step at a time.

The reason I have the columns named as generic 'Id', is because this seems to be what a lot of ORM tools seem to want. I have tried a few ORMs. There are ways of course that you can override that behavior and specify your own primary key, but it seems like it was common/standard practice to just create every table with an auto-incrementing primary key named id.

If you are simply interested in adding a column Users_ID to the Content table, then the following will likely do the trick.  (Assuming this is indeed SQL Server - see the 2nd code block for the MySQL version).

/* add the Users_ID column to the table */
ALTER TABLE dbo.Content ADD Users_ID INT NULL
    CONSTRAINT FK_Content_Users_ID
    FOREIGN KEY (Users_ID) REFERENCES dbo.Users(ID);
GO

/* Update the Users_ID column */
UPDATE dbo.Content
SET Users_ID = Containers.Users_ID
FROM dbo.Content
    INNER JOIN dbo.Containers ON Content.Container_ID = Containers.ID
WHERE Content.Users_ID IS NULL;
GO

/* Make the column not nullable to prevent rows being inserted without
   a Users_ID value present
*/
ALTER TABLE dbo.Content ALTER COLUMN Users_ID INT NOT NULL;

As an aside from the actual question at hand, I would recommend against naming three separate columns ID, since down the road it becomes a nightmare to quickly identify what the ID actually means.  If it is called UserID in the Users table, then call it UserID in the Content table as well.

The code - with minor changes - to work in MySQL:

/* add the Users_ID column to the table */
ALTER TABLE Content ADD Users_ID INT NULL,
    ADD CONSTRAINT FK_Content_Users_ID
    FOREIGN KEY (Users_ID) REFERENCES Users(ID);

/* Update the Users_ID column */
UPDATE Content
    INNER JOIN Containers ON Content.Container_ID = Containers.ID
SET Content.Users_ID = Containers.Users_ID
WHERE Content.Users_ID IS NULL;

/* Make the column not nullable to prevent rows being inserted without
   a Users_ID value present
*/
ALTER TABLE Content MODIFY COLUMN Users_ID INT NOT NULL;


Report subscription deletion failed due to FK constraint We have many SSRS report timed subscriptions (SQL Server 2008R2 SP4).
One of our report developers tried to delete a report subscription, and got the following error:

  An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help The DELETE statement conflicted with the REFERENCE constraint "FK_ReportSchedule_Subscriptions". The conflict occurred in database "ReportServer", table "dbo.ReportSchedule", column 'SubscriptionID'. The statement has been terminated.

I solved the issue by manually deleting the schedule record from the ReportServer.dbo.ReportSchedule table.

The steps were the following:

1. Find the relevant subscription that needs to be deleted.

SELECT sub.SubscriptionID,
       rs.ScheduleID,
       SUB.[Description]
       ,SUB.EventType
       ,SUB.DeliveryExtension
       ,SUB.LastRunTime
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID
                            AND SUB.SubscriptionID = RS.SubscriptionID
WHERE SUB.Description like '%**Mail reciepient here**%'
ORDER BY sub.Description

2. Find the relevant schedule that prevents the record from being deleted.

SELECT [ScheduleID]
      ,[ReportID]
      ,[SubscriptionID]
      ,[ReportAction]
FROM [ReportServer].[dbo].[ReportSchedule]
WHERE SubscriptionID = '**GUID HERE**'

3. Backup the table

SELECT *
  INTO [ReportServer].[dbo].[ReportScheduleBackup]
  FROM [ReportServer].[dbo].[ReportSchedule]

4. Delete the schedule

DELETE FROM [ReportServer].[dbo].[ReportSchedule]
WHERE  ScheduleID ='**GUID HERE**'

The delete command deleted the schedule AND deleted the job from SQL instance that holds the RS databases. That job triggered the timed subscription.

After that, I could delete the report timed subscription from the reports web GUI.

My questions:

Why a manual deletion was needed in the first place ?
Why couldn't I delete the report subscription form the RS GUI? (http://REPORTSERVER_NAME/Reports/Pages/Subscriptions.aspx)

I've been having the same problem for the last few weeks and had settled on manual deletes. I think I've cracked it today. I've compared the foreign key constraint [FK_ReportSchedule_Subscriptions] on the table Subscriptions to the same on a clean (working) install of ssrs. On my clean install the constraint is set with nocheck


Custom unique constraint, only enforced if one column has a specific value Is it possible to have a custom unique constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).
If type is 'true', then I want the combination of type and subset to be unique. Otherwise, there is no constraint. I'm using PostgreSQL 8.4 on Debian.

In other words, you want values in the column subset to be unique among rows where the column type is 'true'.
A partial unique index will do that:
CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true';

Data type does matter. If the column type is boolean (likely should be), you can simplify:
CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type;

This way you can even make combinations with NULL unique, which is not possible otherwise. See:

PostgreSQL multi-column unique constraint and NULL values


Replicating on-premise SQL Server instance with hundreds of databases to an Azure SQL Managed Instance I am wanting to replicate an entire SQL Server 2016 on-premise instance of databases (300+) to an Azure SQL Managed Instance for redundancy of a read-replica in case of on-premise downtime. It appears as though I can use Azure Database Migration Service in order to deploy multiple databases, but this seems to imply that this is a one-time migration.

This will not be a one-time migration, and I am wanting to perform transaction replication with a Publish (on-premise SQL) and Subscriber (Azure SQL). Microsoft has outlined this method, but Publications only allow for a single database to be selected. I would like to explore publishing the full instance of databases, and shipping the logs at a particular interval.

Each of these databases share the same schema, so merging the data into a single database poses problems surrounding application configurations in the event we need to fail-over for read purposes. This is also a temporary solution until we are able to replicate our VMware cluster for Disaster Recovery, and our transactional throughput is on the lower side and is constrained to set working hours.

Is my only option to create publications for each and every database, or can the Azure Data Migration Service be extended for replicating at regular intervals?

Edit: Another option I've been toying with is creating a single database with each individual tenant as a security schema and file group, and developing an ETL pipeline to pass the tenants to these matching security schemas. In essence I would mimic the database-per-tenant through tenant-per-schema, allowing me to only need to push a single database to the Azure SQL. This might cause more headaches than it would solve, though. Does anyone have experience with this at scale?

I shared the case scenario with the Azure SQL Database team at Microsoft via an Exchange Mailing list for Azure SQL Databases contributors/MVPs. The answer provided by one of the managers of the team is the following:  "Yes, transactional replication will be the best way at this point if they want to keep the replication running after the migration to Managed Instances."

Hope this helps.


Mysql scaling up vs scaling out I have an application where each client gets their own database. This is for security, backup ease, and portability. It provides great flexibility and performance has been fine. I am up to 300+ databases and almost 20k tables. I use a 4gb ram server with this configuration. I use a database as a service so I don't actually have physical access to server; but can make changes and have root mysql access.

I am looking to move up the 8gb of memory as my innodb buffer pool is smaller than how much data I have in innodb; and I only have about 600-700mb free memory.

My host allows me to provision up to 32 GB of ram. At some point I will start adding 1 or more new database instances and change my application logic to have a lookup table to find which database to go to.

Right now I am trying to keep costs down and keep it simple so I don't have to re-enginer a bunch of stuff. (Not hard; would take about 2-3 days).

When is the right time to start scaling out instead of up? What issues might I run into if I keep scaling up?

As long as the bottleneck is the amount of RAM you can keep scaling up. MyISAM has some bug related to large key buffer size but InnoDB handles massive buffer pool just fine (even in the TB range). I see you already have 4 buffer pool instances. By increasing the buffer pool it's good to increase this too. The only "issue" you may experience is if you also have larger innodb log files and more dirty pages in the buffer pool then stopping the database may take longer and in case of a crash innodb recovery will take longer.

For scaling out the most efficient simple measurement is the concurrently running threads: show global status like 'Threads_running'; You can take a benchmark and see where is the tipping point for TPS on your system. (Usually with increasing concurrency you will see transactions / second increase for a while until it hits different contention issues and then drop usually quite quickly, you can see a sample benchmark and what the results look like here: https://www.percona.com/blog/2014/01/23/percona-server-improve-scalability-percona-thread-pool/) You want to keep it always under that level. Measuring this will give you a good indication of when will you need new server and how many.


Multiple aggregates with cross (lateral) join Following on from Combining array_agg and unnest, given this data:

key |  a | b | c
------------------
 1  |  0 | 1 | {1,2}
 1  |  1 | 2 | {3}
 1  | -1 | 3 | {2}
 1  |  2 | 4 | {}

Executing this query:

SELECT
  d.key,
  min(d.a) AS a,
  sum(d.b) AS b,
  array_agg(DISTINCT x.c) AS c
FROM data AS d
  CROSS JOIN LATERAL unnest(d.c) AS x(c)
GROUP BY d.key

Gives the unexpected result:

key |  a | b | c
------------------
 1  | -1 | 7 | {1,2,3}

What's going on here, and how to get the correct sum?

Answer performance

On my actual data (16642 rows, 1942 keys, 6 aggregates), I get these cost estimates for each suggested solution.

a_horse_with_no_name option 1: 1761.12..49370.52
a_horse_with_no_name option 2: 0.57..89214.72
Erwin Brandstetter: 1761.12..49370.61

The unnesting generates 2 rows for (key = 1, a = 0, b = 1), and the cross join removes the row with the empty array.

So your group by operates on the following set:

key | a  | b | c
----+----+---+--
  1 |  0 | 1 | 1
  1 |  0 | 1 | 2
  1 |  1 | 2 | 3
  1 | -1 | 3 | 2

One solution is to combine two group by queries, each one grouping n a different level:

select *
from (
  select d1."key", min(d1.a), sum(d1.b)
  from data d1
  group by d1."key"
) m
  join (
    select "key", array_agg(DISTINCT x.c) AS c
    from data d2
      left join lateral unnest(d2.c) as x(c) on true
    where x.c is not null
    group by "key"
  ) a on a."key" = m."key";

Another approach is to only include the "first row" for each "unnested" group in the aggregates:

select d."key",
       min(d.a) filter (where idx = 1 or idx is null),
       sum(d.b) filter (where idx = 1 or idx is null),
       array_agg(distinct x.c)
from data AS d
  left join lateral unnest(d.c) with ordinality  AS x(c,idx) on true
group by d."key";

with ordinality returns the position of the unnested element in the original array. For the row with the empty array, that will be null.


Spliting name with delimiter Hi can you help me to split the name into firstname, middle name, lastname using a pl/sql procedure.

This my procedure:

create or replace procedure proc_name(name in varchar2, a out varchar2, b out varchar2,c out varchar2) is
begin
    if regexp_count(name, '/') = 2 then
      a := regexp_substr(name, '[^/]+', 1, 1);
      b := regexp_substr(name, '[^/]+', 1, 2);
      c := regexp_substr(name, '[^/]+', 1, 3);
    end if;
end proc_name;

Here is what it should do. Input: sharmi/devi/elango

The output is:

 firstname:sharmi
 middle name: devi
 lastname : elango

I got output for above test cases.

Suppose my input is: /devi/

Then my output should be:

 firstname: null
 middle name: devi
 lastname: null

How could I make it work?

One of the problems with the regexp_substr() calls in your procedure is: sometimes, they return incorrect values - which will be assigned to the 3 variables (a,b,c).  Suppose that there always are 3 "tokens" (first/middle/last name), delimited by '/'.  Each token can either be a "name" or null.  Let's generate some test data, and apply the regexes that you have used in your procedure (Oracle 12c):

create or replace view test_data
as
select
  rownum as id
, dt.combinations
, regexp_substr( dt.combinations, '[^/]+', 1, 1) var_a
, regexp_substr( dt.combinations, '[^/]+', 1, 2) var_b
, regexp_substr( dt.combinations, '[^/]+', 1, 3) var_c
from
(
  select
    decode ( T1.token, null, '/', T1.token || '/' )  ||
    decode ( T2.token, null, '/', T2.token || '/' )  ||
    decode ( T3.token, null, '' , T3.token )  as combinations
  from
    ( select null as token from dual union select 'sharmi' from dual ) T1
  , ( select null as token from dual union select 'devi' from dual )   T2
  , ( select null as token from dual union select 'elango' from dual ) T3
) dt ;

Selecting from the view gives us:

select * from test_data;

ID  COMBINATIONS        VAR_A   VAR_B   VAR_C
1   sharmi/devi/elango  sharmi  devi    elango
2   sharmi/devi/        sharmi  devi
3   sharmi//elango      sharmi  elango        -- incorrect (should be: var_a and var_b)
4   sharmi//            sharmi
5   /devi/elango        devi    elango        -- incorrect (should be: var_b and var_c)
6   /devi/              devi                  -- incorrect (should be: var_b)
7   //elango            elango                -- incorrect (should be: var_c)
8   //

Instead of using a procedure, you could use a function (original code: see https://community.oracle.com/thread/925201?start=0&tstart=0 , accessed on 2018-01-06) that returns a table of varchar2, like so:

create or replace type split_tbl as table of varchar2(32767);

create or replace function split3
(
    p_list varchar2,
    p_del varchar2 := '/'
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row( substr( l_list,1,l_idx-1 ) );
            l_list := substr(l_list,l_idx+length(p_del));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split3;
/

For testing this function, you could use something like (explanation below):

declare
  combination_ varchar2(128) := '' ;
  result_ split_tbl ;
  type labels is varray(3) of varchar2(16);
  n_label labels := labels(' first name: ',' middle name: ',' last name: ');
begin
  for i in 1 .. 8
  loop
    select
      ' [test case ' || to_char(id) || ']  string -> ' || combinations
    into combination_ from test_data where id = i;

    select * bulk collect into result_
    from table ( split3( ( select combinations from test_data where id = i ) ) ) ;

    dbms_output.put_line( combination_ ) ;

    for token in 1 .. 3
    loop
      dbms_output.put( n_label(token) ) ;
      if result_(token) is null then  dbms_output.put_line( 'null' ) ;
      else dbms_output.put_line( result_(token) ) ;
      end if;
    end loop;

    dbms_output.put_line( '-------------------' ) ;
  end loop;
end;
/

The anonymous block uses an array of varchar2 values containing labels.  It picks up each of our test cases (from the test_data VIEW), calls the "split" function, and labels (and outputs) its return values.

-- output (anonymous block):
[test case 1]  string -> sharmi/devi/elango
 first name: sharmi
 middle name: devi
 last name: elango
-------------------
 [test case 2]  string -> sharmi/devi/
 first name: sharmi
 middle name: devi
 last name: null
-------------------
 [test case 3]  string -> sharmi//elango
 first name: sharmi
 middle name: null
 last name: elango
-------------------
 [test case 4]  string -> sharmi//
 first name: sharmi
 middle name: null
 last name: null
-------------------
 [test case 5]  string -> /devi/elango
 first name: null
 middle name: devi
 last name: elango
-------------------
 [test case 6]  string -> /devi/
 first name: null
 middle name: devi
 last name: null
-------------------
 [test case 7]  string -> //elango
 first name: null
 middle name: null
 last name: elango
-------------------
 [test case 8]  string -> //
 first name: null
 middle name: null
 last name: null
-------------------

One of the advantages of the "split" function is: it can also handle more than 3 tokens.  Eg

SQL> select * from table(split3(
  2  'João/Alexandre/Duarte/Ferreira/Fernandes'
  3  ))
  4  ;
COLUMN_VALUE
João
Alexandre
Duarte
Ferreira
Fernandes


Switch VARCHAR to DATE in MySQL I inherited a database that has a VARCHAR column that really should be a DATE. There's no checking on the form (I need to fix that) so dates constantly get entered as 2016-12-4 instead of 2016-12-04 and so forth.

I'm pretty sure there are historically a lot of bad dates, so I'm a little skeevy about just changing the data type to DATE, but I'm curious about what would be a good strategy for changing that?

Background: (I'm a front-end developer, not a DBA, so while I can get around in SQL I don't know what I don't know and I'm always afraid I'll make a mistake that corrupts the database).

Use str_to_date() to handle the conversion.

str_to_date('2016-12-4', '%Y-%m-%d');


Rebalancing fast with mongodb I am implementing a sharding with MongoDB and have recently added another node, but the rebalancing of data is very slow. Servers have 64 Mac OS with RAM.

I need help on how I can run the mongod process faster.

Yes, mongod <3.4 have slow balancing speed because of "internal throttling", but if you do your balancing work with script (using MoveChunk-command) it's much faster.

However, if you upgrade your system to 3.4 series, balancing will be about 10x faster and even faster if you have multiple shards (>=4)


Consequences of cascade drop on sequences/functions/views I am migrating some data to another server while dropping all sequences/functions/views. When I try to do simply

DROP VIEW | SEQUENCE | FUNCTION | AGGREGATE without CASCADE, errors are thrown to indicate dependencies. The goal is that if no data (=tables?) will be affected by using CASCADE, then I can use it.

Assumption: if no table is dependent on views/functions/sequences, then no table will be affected by using CASCADE.

Then I guess using CASCADE for views will be fine since they always depend on some base tables.

For functions and sequences I am not very sure. I haven't written that many of sql functions myself and I don't know if the data in a table are generated by some function, whether it means the table depends on the function.

For sequences, it seems that a sequence will be generated automatically by postgres if I use serial as column type. My first guess would be that in this case, the table that is using serial depends on the sequence. However, by testing on a dummy database on my laptop, I found that the data seems to be unaffected. (I just use SELECT COUNT(*) FROM TABLE_NAME after dropping the sequence)

I am quite certain that no table would be dropped if you remove views, sequences and functions with the CASCADE option.

But parts of a table may vanish:

Indexes, triggers, rules, column defaults, check constraints and in PostgreSQL v12 generated columns may vanish if you drop the functions and operators used in them.
Column defaults will vanish with the sequences used in them.

Dropping views should not affect tables at all.

Disclaimer: I have probably forgotten one or two cases.

Why don't you just selectively dump the parts of the database that you need with the -l and -L options of pg_restore?


Changing the "default database" opton in login I am looking for a way to change the option of "default database" to master for all the logins on my instance from current user database in order to maintain the consistency.

Application code logins are all set to use " USE [DBNAME]" so changing all logins to master should be good.

Is there a way to have the script check first or list all logins with default databases and then listing/printing the changes to change the above?

Thanks

You can use sys.server_principals to find default database and alter login to change it.

SELECT name,
       default_database_name,
       'alter login ['+name+'] with default_database=master;'
FROM sys.server_principals
WHERE default_database_name <> 'master';


Are there any inherently non-partitionable datasets? Are there any practical datasets that, by their nature, cannot be partitioned in any practical way?

I am wondering about this because such datasets would be ill-suited to scaling out on commodity hardware and databases containing such data could be forced to scale up on expensive servers, at great cost.

Edit: by nature, I mean that the lack of partitionability is a consequence of the data and of the queries that must be made against it, rather than poor design choices.

Interesting question about the nature of data itself - we could almost stray into philosophy here!. As Phil Sumner points out, it would be a bad idea to try and do this using fields which always have a similar value - i.e. if one had a table ("Open_Source_Programmers", or even "Programmers" in general) that was partitioned on gender, the partitions would be very skewed.

See here for example - lots of articles about this (I Googled "how many open source programmers are female"), but as far as I have seen, it's certainly not above 5% and is probably closer to the 1.5% mentioned in the article I've quoted.

Similarly, I imagine that partitioning people who self-identified as a "programmer" would be very skewed on an age criterion. IMHO, few would fall outside 15-30.

From Codd's laws (The guaranteed access rule - sometimes called law 1 or 2 depending on whose definition you're looking at), every datum should be accessible "by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row", every table should have a primary key.

By definition, if you, as a database practitioner are looking at a given dataset, it must be interesting to you (or, maybe more often, the business you're working for) for a reason. These business reasons will dictate your partitioning logic. Holding truly random data in a database makes no sense. If it were truly random, what criteria would you use to query it? How would you make sense of any results? A given datum (or data point) will (or should) have some sort of independent variable associated with it (time would probably be the main one).

But, to go back to my Open Source contributors example, any logical decision to partition the data would not include gender - maybe "Continent"? As far as I can see, a good first pass would be to go with "North America", "Europe" and "ROW" (rest of world).

Your question begs another, perhaps more fundamental, question. If data is not partitionable, is it data at all? Furthermore, when you query data, you are, ipso facto, partitioning it in some shape or form (SELECT * FROM.. with no WHERE clause excluded).

Information theory, philosophy are all going to come into play here (I've never formally studied either), but I'm very interested in this on an intellectual level and would be delighted for anybody to critique what I have written and point out any (hopefully not obvious) flaws.


How can I migrate a WordPress site from MySQL to SQL Server Express? I have developed a web portal in WordPress using xampp server. I have my database in MySQL.

Now I need to deploy this WordPress portal on other system with SQL Server Express Edition.

So how can I deploy it successfully with SQL Server?

What software or driver or service will be needed for this deployment on the system?

You will need SQL Server Migration Assistant to migrate the data. For wordpress to work with MSSQL you also need the "wp-db-abstraction" plugin.
I'm still working on that one...


On triggers and replication in MySQL I have a classical statement-based replication between the master and the slave. Triggers were created on the master and replicated on the slave as well.

Assuming that I have, say an ON UPDATE trigger, is the following correct?

  MySQL will only write to the binary log statements that were not the
  result of the trigger. Data consistency between the master and the
  slave is therefore ensured by the fact that the same triggers are
  present on both.

The relevance of this pertains to incremental updates (or accumulutors), eg.

CREATE TRIGGER vesion_increment AFTER UPDATE ON table
FOR EACH ROW
BEGIN
  UPDATE table SET version=version+1;
END

In this case, the UPDATE table SET version=version+1 will not be in the binary log and hence not replicated but taken care of by the same trigger on the slave. Is that correct?

Yes, you are correct. You can often get bitten from the fact that the triggers are missing on the slave and thus the trigger effects are not there.

Also if the triggers use any routines (PROCEDUREs and FUNCTIONs) you have to make sure they are present on the slave too. The latter might bite you if you provision your slaves from a mysqldump, since by default mysqldump does not dump routines.


Log bad call to stored procedure I have an external application making a stored procedure call into a SQL Server 2017 database, via a normal database connection (PHP library (PDO?) of some form using the server, database, user, password) where it sends the stored procedure call as a block of SQL in a text string:

"DECLARE @Ret INT ;
 EXEC @Ret = spStoredProc :TheDate ;
"

where :TheDate is a DATETIME parameter (set to a value via PHP magic), and should be either NULL or a string containing a valid date.

Unfortunately, the caller is passing the NULL as 'NULL' (quoted string), which is leading to a 'Cannot convert varchar to datetime' error during SQL Server's set up to run the stored proc, and the stored procedure never runs.

Expecting : EXEC @Ret = spStoredProc NULL

Getting : EXEC @Ret = spStoredProc 'NULL'

Is there a way to log this situation? I have no control over the external application doing the call, so I want to log this situation on the SQL Server side.

SQLServer does not support function overloading - so you can't write two definitions for this procedure using different argument lists.

However, you could re-write the stored procedure to accept a string rather than a datetime as its first parameter.

I suspect the error occurs because of an implicit type casting when the string 'NULL' is received. You did, however, say that the parameter may be passed as "a string containing a valid date". The fact you get the error when the contents of the string is not a valid date implies to me the function is expecting a datetime (and not expecting a string) and performing an implicit type conversion (because in both those cases, it is receiving a string - in one case where the value represents a valid date, and in the other case where the value is 'NULL' - the first converts the type successfully to datetime and the second fails).

If you explicitly change your procedure to receive a string argument, then perform the conversion within the procedure itself, you could first check if the string value is 'NULL' - then set a local variable as null to be used within the procedure. If the string value is not 'NULL' then use a try catch block to cast(@input as datetime) - if it succeeds, then great, continue with your procedure. If not, throw an error.


Script/tool to use to check when the server is not being heavily used and configuring backup on Availbility Groups If I were to backup 50 databases in SQL server, I want to make sure that backup is taking place when the server is not that busy. Is there any way or script/tool to use to check when the server is not being heavily used?
With respect to backing up in always on availability groups, If I do my backups in my secondary replica, the log files in my primary replica will continue to grow big since it doesn't get truncated coz the log is not backed up in the primary replica. How can we avoid that issue with the log backup on the primary replica? Is it possible to do the full backups on the secondary replica and log backups on the primary replica to avoid the log in the primary replica growing too big? Will it work if we restore the database?

I will answer your second question.

  If I do my backups in my secondary replica, the log files in my primary replica will continue to grow big since it doesn't get truncated coz the log is not backed up in the primary replica

NO, this is where Availability Groups are so nice. No matter on which secondary replica you take transaction log backup it would work. What happens is when you take backup on secondary replica and after it finishes the secondary replica will give all information related to log backup to primary replica like LSN and VLF's that can be marked as truncated and accordingly primary replica does the changes. Bottom line is you can take log backup on any replica and the changes would be reflected on primary.

I would suggest you read Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups). From the BOL I quote

  A consistent log chain is ensured across log backups taken on any of the replicas (primary or secondary), irrespective of their availability mode (synchronous-commit or asynchronous-commit).

So you can see you can take transaction log backup on any replica no matter whether it is synchronous or asynchronous replica.

  Is it possible to do the full backups on the secondary replica and log backups on the primary replica to avoid the log in the primary replica growing too big? Will it work if we restore the database?

Yes you can take full backup on secondary replica but you have to make sure that they are COPY_ONLY full backups. As already pointed in BOL article shared above.

  Backup Types Supported on Secondary Replicas

  •BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it is executed on secondary replicas. Note that copy-only backups do not impact the log chain or clear the differential bitmap.

  •Differential backups are not supported on secondary replicas.

Please note that the backup history is kept in the msdb database of the replica the backup got executed on. This implies that backup history and chain cannot be retrieved out of one instance only.

Differential backup can only be done on instance which is primary node.

Restore will work as normal but I must tell you cannot just restore database which is part of availability group you need to first evict it out of AG and then perform the restore.

At last I suggest you to read MSDN blog series on Availability Groups backup and restore by SQLGardner for more details

If you are backing up lot of big databases it does create lot of I/O so your idea to configure backup when load is relatively very less is correct. Normally during midnight or during maintenance windows load is relatively very less you can physically look at server to see the load and configure backup at that time.


Why am I getting error 1064 when creating a table? Here is my DDL statement:

create table if not exists 'fmrecruit'.'resource_type'(
  'ID' bigint not null auto_increment,
  'type' bigint null,
  'first_name' varchar(30) not null,
  'middle_name' varchar(30) null,
  'last_name' varchar(30) null,
  'org_name' varchar(45) null,
  'notes' varchar(256) null,
  'primary_skills' varchar(256) not null,
  'seconadary_skills' varchar(256) null,
  'created_by' varchar(25) not null,
  'created_date' timestamp not null,
  'updated_by' varchar(25) null,
  'updated_date' timestamp null,
  primary key('ID'),
  index resource_type_fk_idx('type' asc),
  constraint resource_type_fk foreign key('type') refrences fmrecruit.resource_type('ID')
  on delete no action
  on update no action)
  engine=innodb;

When creating a table, you should not use single quotes (') to enclose table or column names, but instead use backticks (`) to enclose table and column names, when required.

See Schema Object Names in the MySQL documentation.

If ANSI_QUOTES mode is enabled, it is also permissible to quote identifiers within double quotation marks (").


Slow performance matching columns to lists of values I've made a stored procedure to get all results which match specific students and objectives.

CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY
AS
SELECT Id, UserId, ObjectiveId, DateUploaded, TrafficLight
FROM [Results]
WHERE
  [UserId] IN (SELECT [Id] FROM @students)
  AND [ObjectiveId] IN (SELECT [Id] FROM @objectives)
  AND [TrafficLight] IS NOT NULL
ORDER BY [UserId] ASC, [ObjectiveId] ASC, [DateUploaded] DESC

It uses a user-defined table type for passing in arrays of students and objectives:

CREATE TYPE [dbo].[IdList] AS TABLE(
    [Id] [int] NULL
)

Typically @students contains ~30 IDs and @objectives contains ~100 IDs. The Results table has about 500,000 rows and a nonclustered index on UserId, ObjectiveId, TrafficLight.

At the moment it's taking a long time (up to 5 seconds) to search 500,000 rows. I'm new to stored procedures and think I must be doing something wrong. How can the performance be improved?

Based on the ORDER BY and the fact that you only care about non-NULL trafficlights, I would consider either:

...ON dbo.Results(UserId, ObjectiveId, DateUploaded DESC, TrafficLight)

Or even adding a filter on that index:

...WHERE TrafficLight IS NOT NULL

Whether the filter makes sense or not depends on what percentage of the table is NULL vs. NOT NULL and how often you run this exact query compared to one that doesn't filter. It may not be intuitive to have TrafficLight in the index, but this is due to some optimizer blindness.


Using Mirroring for incremental backups? We have a machine tool application that runs on a SQL server backend.  These tools run all day every day and I am looking for a way to incrementally backup the database without any machine downtime.

I've seen mirroring solutions with two servers, but these solutions would not work for us do to cost and size constraints.  Is it possible to programmatically or through some service to mirror this database to a backup hard drive on the main PC?

Some data loss is acceptable.

None of the SQL Server backups require downtime. Full, differential, log backup are all online.

So just do a normal run-of-the-mill backup scheme (eg. full weekly, diferential daily, log hourly etc) and go with it. Is incremental and is online.


NESTING_TRANSACTION_FULL latch Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which resource is the bottleneck when load increases.

Usual advices about performance tuning don't help. CPU is not overloaded (about 30%), page life expectancy is about 1.5 hours; IO queues are almost always empty. There is a suspicious record leading sys.dm_os_wait_stats view by wait_time_ms: LATCH_EX. And the largest latch (4 times larger than second largest) is NESTING_TRANSACTION_FULL. Which is described as "internal use only".

Does NESTING_TRANSACTION_FULL statistic mean anything? And could it give us a lead to diagnose problems with its configuration, hardware, application etc?

TRANSACTION_*, MSQL_TRANSACTION_MANAGER, NESTING_TRANSACTION_FULL, NESTING_TRANSACTION_READONLY

Within this group of latch classes that are used during various transaction-related operations, the TRANSACTION_DISTRIBUTED_MARK latch is unique. It is used when placing markers in the transaction logs to allow for recovery to a named point. There is only one transaction mark latch in any instance of SQL Server 2005. This latch rarely, if ever, encounters contention, and thus there is no need for an extensive description. The source of any contention is also clear, because this latch is used by only a single operation. The other latch classes in this group are used in various transaction contents.

this post is original from:

http://www.informit.com/articles/article.aspx?p=686168&seqNum=5


Strange Issue with Linked server and using IF exists We are trying to insert data from remote server using below query

if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name =MonthlyReportData')
begin
Insert into [ServerB].[DB_B].dbo.Monthlydata
select  *,getdate() from
[ServerA].[DB_A].dbo.monthlydata
end

Above query fails with below error

  The OLE DB provider "SQLNCLI11" for linked server "ServerA" does not contain the table ""DB_A"."dbo"."MonthlyReportData"". The table either does not exist or the current user does not have permissions on that table.

Table Doesn't exist,but whats strange is  query checking   `begin' block ,even after if exists fails

If we modify the query to below,it works with out any error and print 1 doesn't gets printed

  if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name =MonthlyReportData')
    begin
    print 1
    end

if we modify further the query to, query a table which exists it work,i mean it doesn't go further after begin block

 if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name =MonthlyReportData')
    begin
    Insert into [ServerB].[DB_B].dbo.Monthlydata
    select  *,getdate() from
    [ServerA].[DB_A].dbo.validtable
    end

In the above example,in begin block,observe exists works correctly and doesn't throw any error only when you have a valid table in begin and end block''

Hope i explained this clearly.

Below are few things checked

1.Issue Exists even with information_schema.tables
2.Tried this as well,but no help  :https://stackoverflow.com/questions/22182437/check-if-table-exists-in-external-linked-database
3.Account used to connect is Sysadmin

Below answer works when i use a single statement and use exec.I should have made myself clear,above query is generated dynamically and passed to exec..see below example

set @Sql='

Insert into [server_A].[db_A].dbo.Monthlydata
select  *,getdate() from ' +quotename(@dblocation)+'.'+quotename(cast(@dbname as varchar(20)))+'.dbo.MonthlyReportData
end'

exec(@sql)

Please let me know if i am unclear or you need any further info

Below is my complete code

DEclare @sql varchar(4000)
Declare @dblocation varchar(200)
declare @dbname varchar(200)

Declare Getdata cursor for
select  dblocation,id
from
temp

open GetBMSdata
fetch next from Getdata into @dblocation,@dbname

while @@FETCH_STATUS=0
begin

set @Sql='
if exists (select 1 from '+quotename(@dblocation)+'.'+quotename(@dbname)+'dbo.MonthlyReportData)
begin
Insert into [servera].[dba].dbo.Monthlydata
select  * from ' +quotename(@dblocation)+'.'+quotename(@dbname)+'dbo.MonthlyReportData
end'

exec(@sql)

fetch next from Getdata into @dblocation,@dbname
end

close Getdata
deallocate Getdata

I ended up modifying my code like below

set @Sql='
if exists (select 1 from '+quotename(@dblocation)+'.'+quotename(+Cast(@dbname as varchar(200)))+'.sys.tables where name='MonthlyReportData'')
begin
exec(
''
Insert into [serverb].dba_b.dbo.MonthlyReportData
select  *,getdate() from ' +quotename(@dblocation)+'.'+quotename(+Cast(@dbname as varchar(200)))+'.dbo.MonthlyReportData
''
)
end
'
print @sql
exec(@sql)

The whole query is validated by the engine before it runs, so the IF statement cannot prevent that message for a table that does not exist.

You could use dynamic sql inside the IF.

if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name = 'MonthlyReportData')
begin
  exec('Insert into [ServerB].[DB_B].dbo.Monthlydata
    select  *,getdate() from
    [ServerA].[DB_A].dbo.monthlydata')
end

After clarification and full sample

Declare @sql varchar(4000)
Declare @dblocation varchar(200)
declare @dbname varchar(200)

Declare Getdata cursor for
select  dblocation,id
from
temp

open Getdata
fetch next from Getdata into @dblocation,@dbname

while @@FETCH_STATUS=0
begin
if exists (select 1 from [ServerA].[DB_A].information_Schema.tables where table_name = 'MonthlyReportData')
begin
 set @Sql='

 Insert into [serverb].[db_a].dbo.data

 select  *,getdate() from '
 +quotename(@dblocation)+'.'+quotename(cast(@dbname as
 varchar(20)))+'.dbo.MonthlyReportData
 end'

 exec(@sql)
end

fetch next from Getdata into @dblocation,@dbname
end

close Getdata
deallocate Getdata


Use T-SQL to return free space on a network share I'd like to be able to calculate the amount of free space I have for offsite backups so I can prevent any jobs from running should there not be the relevant space and also be able to send alerts when required. I'm aware of using cmd command FSUTIL (as below) for local drives however this doesn't seem to work for network drives.  Please can an alternative be suggested?
Thanks in advance.

exec xp_cmdshell 'FSUTIL VOLUME DISKFREE c:\'

I'm not sure if this will give you exactly what you want, but you could try this:

DECLARE @cmd VARCHAR(2000)
SET @cmd = 'DIR "\\SHARENAME"'

EXEC xp_cmdshell @cmd

The last line would give you the freespace

output
04/04/2016  05:39 AM            15,872 TrackSqlLogins_0_131042360126710000.xel
08/15/2014  07:01 AM               449 TransparentDataEncryptionCertificate
08/15/2014  07:01 AM               636 TransparentDataEncryptionCertificatePrivateKey
              13 File(s)  1,208,231,169 bytes
               8 Dir(s)  217,800,073,216 bytes free


Galera cluster replication stalls when using wsrep_provider_options repl.commit_order=2 we are currently testing if moving from mariaDB master/slave replication to a 3-node-galera-cluster is an option for us.

General replication in the cluster works fine like expected. Load test shows that write-performance does not meet our requirements though.
To migrate data from the master/slave-system to the cluster one cluster node is set up as slave to that m/s-system (backup from master is imported, slave replication on the cluster node starts from the point where the backup is taken). The cluster node never catches up to the master (seconds-behind-master is increasing).
So we looked for tuning-options for our cluster. Setting repl.commit_order=2 improved performance, replication from the master to the cluster node went fine. But we then experienced that the other cluster nodes weren't replicating any more (though their state was synced and the wsrep%-variables did not show anything unusual, no throttling), replication within the cluster seemed to have stalled entirely.
When resetting repl.commit_order replication worked again.
Our cluster was not replicating neither when we had just the cluster operational (without replicating from the master node) with repl.commit_order=2.

Here excerpts from the server.cnf:

[galera]
# Mandatory settings
wsrep_provider         = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address  = "gcomm://10.0.0.10,10.0.0.8,10.0.0.11"
wsrep_cluster_name       = foobar
wsrep_node_name          = foobar-db1
wsrep_node_address       = 10.0.0.10
binlog_format            = row
default_storage_engine   = InnoDB
innodb_autoinc_lock_mode = 2

wsrep_slave_threads      = 16
wsrep_provider_options   = 'gcs.fc_limit=80;gcs.fc_factor=0.8;repl.commit_order=2'

# SST settings
wsrep_sst_method         = xtrabackup-v2
wsrep_sst_receive_address= 10.0.0.10
wsrep_sst_auth           = galera:somepw

wsrep_retry_autocommit   = 10

# required since 10.1
wsrep_on=ON

Any ideas?

(Is your question mostly about the "migration"?  Or about write performance on the resulting Cluster?)

Look at your transactions.  Optimal is some sort of "medium" size.  Too short leads to long latency.  Too long leads to extra risk of deadlocks/failure at COMMIT time.

Look at the network.  You are dependent on how far apart the nodes are.  If there are next to each other, you do not get the HA benefits.  If they are too far apart, you have latency issues.

Look at the write queries.  If they are likely to conflict, but you are sending them to different nodes, then you may be adding to the problem, relative to somehow focusing some sets of queries onto a single node.

Do you have "critical reads"?  How are you handling them?

More Galera tips .


The value could not be converted because of a potential loss of data Let me start out by clarifying that this is not a duplicate question, nor a potential duplicate for that matter. I have tried implementing every answer to every single variant that already exists of this problem on StackOverflow and DBA Stack Exchange, without any luck.

I've been struggling with this problem for the past two days (working on it for approximately 7 hours a day), and even after Googling the issue, it appears that no one else are having the same exact variant of my problem.

What am I trying to do?

In SSIS, I am trying to read from a CSV file, and insert the rows from it into an OLE DB database. For that, I have made the simplest setup ever, seen below.

Flat File Source - reads the CSV rows.
Derived Column - currently does nothing (it's just there for experimenting).
Data Conversion - currently does nothing (it's just there for experimenting).
OLE DB Destination - stores the rows in the database.

When I try to run it, it stops executing at my OLE DB Destination with the following error-message.

  There was an error with input column "Amount" (187) on input "OLE DB Destination Input" (51). The column status returned was: "The value could not be converted because of a potential loss of data.".

The column that fails (Amount) is currently of type DT_STR. It appears to be the type that I have the most faith in right now.

What have I tried?

I've tried using the Flat File Connection's "Suggest Types" function on the column that is failing. This made it recommend the Single byte signed int datatype.

Stops at my Flat File Source.
The error is Data conversion failed. The data conversion for column "Amount" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I've tried using the Derived Column to cast the column into a DT_I4.

Stops at my Derived Column.
The error is Data conversion failed. The data conversion for column "Amount" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I've tried using the Data Conversion to cast my column's value to a DT_I4.

Stops at my Data Conversion.
The error is Data conversion failed. The data conversion for column "Amount" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I've tried changing the length of my DT_STR value in the source and the destination.

Stops at the source or the destination depending on the settings.

I've tried connecting using an Excel Source connector instead with and without the IMEX=1 added to the connection string. No luck.

My programming sense tells me that I am screwed. I have never ever encountered such a big problem for such a simple thing.

And why is it that I can't just ignore the "potential" loss of data? It's really frustrating to say the least.

My system

It's a Windows Server 2008 R2 machine with SQL Server 2008 installed. The machine itself is fully updated through Windows Update.

Ignoring the error

First of all, you should be able to ignore string truncation by going to your Flat File Source, Error Output and then changing "Fail Component" under "Truncation" to "Ignore Error".

Better solution

The real issue could be that the string length inside the SSIS pipeline is still wrong because it got initialized at some earlier point.

You can determine if that is the case by double-clicking the green arrow from your Flat File Source (or after Derived Column/Data Conversion) and choosing "Meta Data". There you can see the length of the field inside the pipeline.

If have often seen that meta data doesn't match the source component anymore, mostly if creating the Flat File Source AND its corrsponding Connection via the Wizard.

My suggestion would be to delete the connection and the Flat File Source and recreate them as I haven't found a way to re-sychronize pipeline meta data with source components.


Inserting into three different tables, using foreign keys -- 500000x each We're working on refactoring many of our offline (python) scripts.  I'm very new to any kind of SQL, but have been trying to learn as much as I can, especially in the performance field.

We have a script that loads a CSV from a client, that has user-defined fields in it, that must be matched to unique names (and eventually keys) in a separate table.

We write each row of the CSV to our DB in 4 different queries, to 4 different tables.  All four queries relate to the same row of data, and use the first's primary key in each.

The latter 3 use the primary key from the first insert, so I assume that's a constraint.

We're getting around 100-150 IOPs on our Amazon RDS instance, so it's painfully slow trying to do something like this.  We proved (well, somewhat), that the script is not the bottleneck.  I created another script that just ran a loop to 'insert' into a dummy table with no indexes, and it ran about as fast.

Is there anything we can do to optimize our current method?  I think there has to be, I couldn't imagine we really have to perform 2 million queries for our theoretical max-size CSV file of 500,000 rows.

I've ruled out (or think I have, I could be very, very wrong) a few options:

batch-inserts: Since each insert goes to a separate table, can we really batch insert?
stored procedure/functions: The headers are dynamic, so I don't think there's anything we can really do in that department.
Building a query through string concatenation: Mostly because I don't know even where to start with this, but I also think it would be impossible since we need the keys from the first insert.

Read the entire CSV into a new MyISAM table with no indexes.
Write SQL statements to migrate that data en-masse to the other 4 tables.
Drop that table.

100-150 IOPs -- not bad.  That's about all you can get with a consumer-quality disk.

So, the trick is to minimize the IOPs.

What engine are you using?  (InnoDB has some tunables that can help/hurt.)
What indexes?  (The more indexes, the more I/O.  And the cache may not be set optimally.)


Read committed snapshot:Finding lock on table IF update command is run on a table and commit is pending then another user cannot be able to update the table.

Is there any query to get the user or PC which caused lock on the table.

It is possible to list the locks using the DMV sys.dm_tran_locks (sp_lock is deprecated).

The DMV query can be hard to code against and the results can be difficult to interpret. Consider using the free sp_WhoIsActive tool from Adam Machanic instead. He explains how to use it to analyse chains of blocking locks in this blog post (which also contains a download link).


Cannot install SQL Server on a local disk I'm installing a new instance of SQL Server, and I got this error:

instance directory (or command line option /instancedir) value for a cluster installation cannot be a directory on shared drive

It is pretty clear, and this is my situation:

I have a microsoft cluster with 3 servers. All with Windows 2012R2 installed. SQL Server is version2016 SP1.

On the first 2 servers I have some SQL Server instance in failover with dedicated storage. One of the drive is Y:

This istance (IS1) cannot move on server03. Also for the disc, in failover cluster manager, under Advanced Policies I deleted server03 from possible owners.

Now I have another disk, in server03, with letter Y. This is a local disk.

To configure AlwaysON I need to have some local instances on Server03, and I'd like to use this disk to have an automatic seeding.

How can I convince sql server installation Y is a local disk?

If I understand this correctly, you have 2 servers configured for in a FCI installation. They use shared disk which is the Y drive. You have a 3rd server that has local disk which is the Y drive.

Node 1 and 2 should be installed first as a FCI instance.
As long as the FCI is working as expected and fail overs are happening as expected between Node 1 and Node 2, Node 3 should be added to the Windows Cluster as an extra node.
Node 3 should have SQL server installed on it as a standalone node.
Enable Always On feature on Node 3 and the FCI instance
Node 3 should not be listed as a possible owner of FCI instance.

You should then be able to have an FCI install and a standalone install on the same windows cluster. You will be able to automatic failover between Node 1 and Node 2, with manual failover to Node 3.

I have this set up in my lab now.


Speed up query that have joined tables and function calls I have a Bootstrap Table that use server side pagination to display results from a query like this:

SELECT
    Item.a,
    Item.b,
    Item.c,
    Info1.Name,
    Info1.Name,
    Info2.Name,
    Info3.Name,
    Info4.Name,
    Info5.Name,
    Info6.Name,
    Info7.Name,
    Info8.Name,
    Info9.Name,
    dbo.CalculateSomeText1(Item.Id) AS Text1
    dbo.CalculateSomeText2(Item.Id) AS Text2
    dbo.CalculateSomeText3(Item.Id) AS Text3
    dbo.CalculateSomeText4(Item.Id) AS Text4
    dbo.CalculateSomeText5(Item.Id) AS Text5
    dbo.CalculateSomeText6(Item.Id) AS Text6
    dbo.CalculateSomeText7(Item.Id) AS Text7
    dbo.CalculateSomeText8(Item.Id) AS Text8
    dbo.CalculateSomeText9(Item.Id) AS Text9
FROM
    Item
    LEFT JOIN Info1 ON Item.Id1 = Info1.Id
    LEFT JOIN Info2 ON Item.Id2 = Info2.Id
    LEFT JOIN Info3 ON Item.Id3 = Info3.Id
    LEFT JOIN Info4 ON Item.Id4 = Info4.Id
    LEFT JOIN Info5 ON Item.Id5 = Info5.Id
    LEFT JOIN Info6 ON Item.Id6 = Info6.Id
    LEFT JOIN Info7 ON Item.Id7 = Info7.Id
    LEFT JOIN Info8 ON Item.Id8 = Info8.Id
    LEFT JOIN Info9 ON Item.Id9 = Info9.Id

The table offers a 'master search' that allow user to enter some text and then retrieve records that have that text in any column. (And then there's a filter for each column).

Anyway, the table was fairly large (800K rows) and the raw query above took about 4 minutes.

Is there any trick I could put in here to speed up the thing? If not, do I have to do the search again everytimes user click [next page]? (I'm afraid caching the whole resultset may kill my application)

I figured that searching on function returns requires the function to be executed for each rows which make it worse. But I guess it could not be helped, the function may not be deterministic.

Info1,.. Info9 were just examples. The point is the table Item has a lot of 1-Many references.

The main table is Item, the query above is the raw that retrieve the data, I planned on doing the pagination in application code rather than in SQL (it doesn't support SKIP anyway). If you have any suggestion on paging with SQL, please, I'm all ears.

The main table definition is

Item(
    Id int PK,
    i1 int,
    i2 int,
    ..
    i9 int,
    s1 nvarchar(50),
    s2 nvarchar(50),
    s3 nvarchar(50),
    ...
    s20 nvarchar(50),
    Id1 int FK -> Info1,
    Id2 int FK -> Info2,
    ...
    Id9 int FK -> Info9,
)

The other Info table is fairly simple. They're just for reference. The point is, the main table is large with many text fields, none of them has index (except the keys). And the query is supposed to get all of the field, I was trying to simplify the query for easier understanding.
Most of the function calls involve SELECT on other tables. These functions belong to the legacy system that I have no power over. Too long, too complicated to touch anyway.

What I try to achieve is a way to speed up this query. A general idea is fine. I hope it's clear enough.

I'm trying out an indexed view for this, once I get it going I'll show you guys the result if it actually helps at all.

If you mark the functions as WITH SCHEMABINDING, then this may help - particularly if the function doesn't access any tables.


How can a database be normalized when an optional field causes denormalization? Suppose you have a warehouse full of widgets.  Each widget in the warehouse is stored in a specific identifiable location within the warehouse.  You might have a schema which looks like this:

A widget may also be (optionally) located on a cart in the warehouse, which could lead to a schema like this:

However, a problem arises because a cart can also only be located in one location within the warehouse, which turns the prior schema into something like this:

Howver, this schema is denormalized and can result in anomalies, such as if the data was:

widgetID  storageLocationID  cartID
========  =================  ======
1         foo                A
2         bar                A

Although both widgets are located on the same cart, the database shows them in different locations which is not possible.

If a cart was required, it would be relatively simple to address this normalization issue by simply removing the storageLocationID from the Widgets table.  However, because a cart is not required, that solution will not work.

How can this data be structured to eliminate the anomalies?

You could consider a cart to be a storage location.

Simply add attributes to the table so that a location can be identified as type "cart" or "shelf", like so:

If there's a requirement for a hierarchy - for example, if a cart might be stored in a storage location itself - you could define the hierarchy within your storagelocation table.

This is not a perfect solution - it may make it difficult to enforce restrictions that a cart must exist in a storage location (like a bay or aisle).

However, in most database engines it's straightforward to enforce some business logic here using CHECK constraints.


Impact of miss-ordered fields in a composite, clustered primary key? I have tables where the primary key is something like this:

-StoreID
-BusinessDate

Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a daily basis and is never updated and almost never deleted.

I think is would be better if the field order would be:

-BusinessDate
-StoreID

My reasoning is that the sort order of this key would more naturally follow the typical insert order. I'm wondering if I should change the order of the fields in the key.

So far performance has been acceptable. Will the current field ordering cause serious performance problems in the future if I don't change anything?

The order of columns in a primary key does not affect the insert performance, since the combination of the values in the two columns of the primary key are meant to be unique.

However the order of index impacts the performance of SELECT queries. In the new order you will have a greater and increasing range of values (dates) across which the index is clustered (dates) as compared to clustering around 100 stores.

If most of your queries are focused on a single store with the new ordering your queries will be slower.


Merge Two Group By Rows SQL How do I merge two rows returned by a Group By statement:

    Select  SiteId,
            SiteName,
            EventCount = Count(*)
        From #MyLogs

        GROUP BY EventCode, SiteId, SiteName

returns this:

SiteId   SiteName       EventCount
 1      Abbottabad         53
 1      Abbottabad         52

How do I merge these results into one Row

The result I need is:

    SiteId   SiteName  |  OpenEvent            |        CloseEvent
                       | Count(Event = 43)     |     Count(Event = 44)
---------------------------------------------------------------------
      1     Abbottabad |  53                   |          52

I have posted the sample data and query here: Link

You have two possibilities: PIVOT and a crosstab:

DECLARE @sampleData AS TABLE (
    SiteId int,
    SiteName varchar(50),
    EventCode int,
    EventCount int
);

INSERT INTO @sampleData VALUES
(1,'Abbottabad',43,53),
(1,'Abbottabad',44,52);

SELECT *
FROM @sampleData
PIVOT (SUM(EventCount) FOR EventCode IN ([43],[44])) AS p;

SELECT
    SiteId,
    SiteName,
    SUM(CASE EventCode WHEN 43 THEN EventCount END) AS [OpenEvent Count(Event = 43)],
    SUM(CASE EventCode WHEN 44 THEN EventCount END) AS [CloseEvent Count(Event = 44)]
FROM @sampleData
GROUP BY SiteId,
    SiteName;

Personally I prefer a crosstab, as it is more flexible.

Edit: changed to original table to clarify the technique.

IF OBJECT_ID('tempdb..#MyLogs') IS NOT NULL
    DROP TABLE #myLogs

CREATE TABLE #MyLogs(
    SiteId int,
    SiteName varchar(50),
    EventCode int
);

-- Insert some test data
INSERT INTO #MyLogs
SELECT 1 AS SiteId, 'Abbottabad' AS SiteName, 43 AS EventCode
FROM master.dbo.spt_values
WHERE type = 'P'
    AND number BETWEEN 1 AND 53;

INSERT INTO #MyLogs
SELECT 1 AS SiteId, 'Abbottabad' AS SiteName, 44 AS EventCode
FROM master.dbo.spt_values
WHERE type = 'P'
    AND number BETWEEN 1 AND 52

SELECT
    SiteId,
    SiteName,
    SUM(CASE EventCode WHEN 43 THEN 1 END) AS [OpenEvent Count(Event = 43)],
    SUM(CASE EventCode WHEN 44 THEN 1 END) AS [CloseEvent Count(Event = 44)]
FROM #MyLogs
GROUP BY SiteId,
    SiteName;


Return values at more/less specific time points In PostgreSQL 10.0, I have a big table with two columns:

CREATE TABLE xs (
  ts timestamp with time zone NOT NULL,
  x integer NOT NULL
)

Now, if I have 100 given time points, how do I return exactly 100 x-es that are right before (<=) these time points (for each of those points)?

For just a single one this would be very easy:

SELECT x FROM xs WHERE ts <= '2018-03-13 11:41:47.167973+00' LIMIT 1

But how to do this efficiently for a 100? Something like:

SELECT x FROM xs
  WHERE ts FIRST_LESS_THAN_EQUAL_IN
    ('2018-03-13 11:41:47.167973+00',
     '2018-03-13 11:41:47.198564+00',
     '2018-03-13 11:41:47.555668+00',
      ...
     '2018-03-13 11:41:57.766888+00')

Obviously, there is an index on ts.

I suggest a LATERAL subquery, best with a LEFT [OUTER] JOIN to preserve all input rows (displaying a NULL value in case there should be no match at all):

SELECT v.reference_ts, t.most_recent_x
FROM  (
   VALUES
      (timestamptz '2018-03-13 11:41:47.167973+00') -- type cast in first row
    , ('2018-03-13 11:41:47.198564+00')
    , ('2018-03-13 11:41:47.555668+00')
    , ('2018-03-13 11:41:57.766888+00')
   ) AS v(reference_ts)
LEFT JOIN LATERAL (
   SELECT x AS most_recent_x
   FROM   xs
   WHERE  ts <= v.reference_ts  -- ts is unique (?!); see below
   ORDER  BY ts DESC
   LIMIT  1
   ) AS t ON true;

A VALUES expression is one way to put your 100 timestamps into a derived table we can work with. The free-standing VALUES expression may require an explicit type cast in the first row. See:

Casting NULL type when updating multiple rows

There are others ways to pass your values, like an array (or an array literal cast to timestamptz[]) and unnest() in the query:

Optimizing a Postgres query with a large IN

About the LATERAL subquery:

What is the difference between LATERAL and a subquery in PostgreSQL?

Don't forget the ORDER BY or you get arbitrary results - like @a1ex07 commented.

If ts is not unique, it's undefined which of the duplicates is returned, unless you add more ORDER BY items to make it deterministic. Like: ORDER BY ts DESC, x DESC to get the row with the biggest x from any set of peers. And make the index match.

An index on (ts) (like you mentioned) works wonders on performance.
Or, ideally, on (ts DESC, x) if preconditions for index-only scans are met.

Does PostgreSQL use an index-only scan in this JOIN?

dbfiddle here


How using many tables and JOINS affect performance vs. using limited number of tables and lesser JOINS? I want to design a db schema concerning a schedule.

I. I can use all fields in the same table, for example:

id | shop_id | mo (varchar) | tu (varchar) | we | th | fr | sa | su
 1 | 1       | 08:00-12:00  | ...

II. or I can use more tables one for schedule with structure:

id | shop_id | mo_id | tu_id | we_id | th_id | fr_id | sa_id | su_id

and then one table for each day... for ex: monday with structure:

id | form_time | to_time

III. or I can use one table for schedule with structure:

id | shop_id | weekday_id

and then only one table for weekdays with structure:

id | day | form_time | to_time

Which structure of these 3 structures you think is better in performance?

You are obviously familiar with data normalization and joining tables.  Not to dismiss your question, but I would focus more on a solid design that captures the information in the simplest way and without putting undo constraints on your application.  I think you'll find then the performance works itself out.  I'm wondering what's wrong with this:

TABLE events
  id | shop_id | title | desc | from (DATETIME) | to (DATETIME)

To figure out a day's schedule, you then have to do some work on those DATETIME fields to figure out whether they were on Mon, Tue, etc.  But that's pretty easy,  and now there's all sorts of other ways you could slice this data.  You could even create 7 VIEWS (S,M,T,W,T,F,S) that return only events on each day of the week, if that's so important.


Are these database indexes correct for my search I am new to Database Administration. I am following information provided by: http://www.brentozar.com/blitzindex/

I have a table VehicleMonitoringLog. When I execute the sp_BlitzIndex script it shows that this is a Heap, because it doesn't have a clustered index.

Now, all of our searches on this table are done based on iAssetId and dtUTCDateTime. This is the Query:

Select top (1) *
from VehicleMonitoringLog
where iAssetId = 1
Order By dtUtcDateTime DESC

We don't search based on the PK, BUT we are inserting data into this table every few seconds.

My questions are:

Because we are inserting data every few seconds do I need to create a clustered index on the PK?

OR, should I create a clustered index on iAssetId and dtUtcDateTime like this?

CREATE CLUSTERED INDEX
[VehicleMonitoringLog_Asset_dtmUTCDateTime_ClusteredIndex]

ON [dbo].[VehicleMonitoringLog] ( [iAssetId] ASC, [dtUTCDateTime] 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]

EDIT: These are the 3 columns in Question:

I have added an image showing the PK and FK of the Table. Another search we do is something like this:

Select * from VehicleMonitoringLogs
where iAssetId = 1
and dtUTCDateTime > GetDate() - 1
and dtUTCDateTime  <  GetDate()
Order by dtUTCDateTime ASC

The clustered index in your question will optimize the select query because:

1) you are requesting all columns

2) the first column satisfies the equality predicate on iAssetID

3) the second column is useful for the dtUTCDateTime inequality predicate

Since you also have a surrogate iVehicleMonitoringId key that serves no purpose other than to provide a unique value, consider changing your primary key to a clustered composite one on iAssetId, dtUTCDateTime, and iVehicleMonitoringId.  That would avoid the need for a separate index.  My assumption is you have no tables that reference this log table so the wider key shouldn't be an issue.


Should I put the error handler inside the loop, or the loop inside the error handler? When a procedure is looping over a statement that could raise an error condition, should I put the loop inside the error handler, or put the error handler inside the loop? I want the procedure to run as quickly as possible.

I'm developing a maintenance job that deletes expired data from a table in chunks. It's designed to run continuously. It's implemented by two stored procedures: the first deletes from the table, and the second calls the other in a loop until either there is no expired data left to delete, or an error occurs.

The delete procedure looks like this:

CREATE PROCEDURE DeleteSomeExpiredData (
  @ExpiryAgeInDays TINYINT,
  @MaxDeleteCount TINYINT,
  @ActualDeleteCount INT OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON;

  DELETE TOP (@MaxDeleteCount)
  FROM BigTable
  WHERE DataCollectionDate < DATEADD(DAY, -@ExpiryAgeInDays, GETDATE());

  SET @ActualDeleteCount = @@ROWCOUNT;
END;

I want the looping procedure to reraise errors from the delete procedure, so I'm combining a TRY...CATCH statement and a WHILE BEGIN...END statement.

I can either put the WHILE BEGIN...END loop inside the TRY...CATCH statement:

CREATE PROCEDURE CycleDeleteExpiredData (
  @ExpiryAgeInDays TINYINT,
  @MaxDeleteCount TINYINT
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ActualDeleteCount INT = 1;

  BEGIN TRY
    WHILE @ActualDeleteCount > 0
    BEGIN
      EXEC DeleteSomeExpiredData
        @ExpiryAgeInDays = @ExpiryAgeInDays,
        @MaxDeleteCount = @MaxDeleteCount,
        @ActualDeleteCount = @ActualDeleteCount OUTPUT;
    END;
  END TRY
  BEGIN CATCH
    EXEC RethrowError;
  END CATCH;
END;

Or I can put the TRY...CATCH statement inside the WHILE BEGIN...END statement:

CREATE PROCEDURE CycleDeleteExpiredData (
  @ExpiryAgeInDays TINYINT,
  @MaxDeleteCount TINYINT
)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @ActualDeleteCount INT = 1;

  WHILE @ActualDeleteCount > 0
  BEGIN
    BEGIN TRY
      EXEC DeleteSomeExpiredData
        @ExpiryAgeInDays = @ExpiryAgeInDays,
        @MaxDeleteCount = @MaxDeleteCount,
        @ActualDeleteCount = @ActualDeleteCount OUTPUT;
    END TRY
    BEGIN CATCH
      EXEC RethrowError;
    END CATCH;
  END;
END;

These look functionally equivalent, and I find both easy to read. But there may be a semantic difference I'm not aware of. Can you tell me any?

Error handling should be (IMO) for the whole stored procedure: so loop inside the TRY/CATCH

Not least, what happens when more code is added pre- or post-loop?
Then you need the TRY/CATCH to cover both loop and new code.

Poor maintainability and extra work required.

You can have an inner TRY/CATCH if you want to handle certain inner errors: this is acceptable for, say, "UPSERT" calls (before SQL Server 2008 MERGE of course). See Pros and Cons of Checking if value exist for unique column or let db raise unique error on inserting for more


How to get average of views before clicking on advertisement? I've got two tables containing information about viewing and clicking on advertisement by each user:

Clicks:

userID   Action   Time
123      c        2016-01-08 01:57:00
123      c        2016-01-11 03:17:12
200      c        2016-01-09 02:20:10
332      c        2016-01-12 07:07:07

Views:

userID   Action   Time
123      0        2016-01-07 01:33:00
123      0        2016-01-10 04:12:13
200      w        2016-01-08 02:20:10
332      0        2016-01-09 07:07:07
332      0        2016-01-10 07:07:07
332      0        2016-01-11 07:07:07

I want to know what was the average of views before clicking on advertisement first time, second time etc. From the example above: User 123 clicked first time having only one view before, user 200 also, and user 332 had three views before clicking first time. So the average for the first click is (1+1+3)/3=1.67. Only user 123 clicked twice, after two views (in total).

The result I want looks like this:

Nr_clicks  Avg_views
1          1.67
2          2
...

The Action column in Views table can take different values (but not c), in Clicks table is always c. I was trying to use JOIN with subquery and then average results but I get only some errors. I'm working in Hive.

Ok, it take me some time but I think it works well:

select clickcume nr_clicks, avg(view_b) avg_view_before from(
select userid, clickcume, max(viewcume) view_b from(
select a.*, b.time_v, b.viewcume  from
(
  select userid, time as time_c,
         count(*) over (partition by userid order by time) as clickcume
  from views) a join
(
  select userid, time as time_v,
         count(*) over (partition by userid order by time) as viewcume
  from clicks) b
  on (a.userid=b.userid)
where time_c>=time_v ) c
group by userid, clickcume ) d
group by clickcume


Set up common database server for vb.net application I have developed a school management software in vb.net. I used in-built database management program of Visual Studio. At first my client requirement was single handed use (i.e. the SW works on only one computer), now suddenly when the software if complete and submitted to client they said to me that work load is very much and they want that multiple user should use the software.

Now I have problem that how do I use my database file (i.e. .mdf) in such a way that multiple users can connect to it with different computer "LOCALLY VIA LAN".

Actually I have done some research and I understood very little which is that I can use SQL Server Express as a server on one machine, import my existing DB file into SQL Server Express, and make some changes in my connection string in VB.net so that it connect to the specified DB.

Can someone please help me with my problem or give me some link to which I can refer.

Thanks.

First you'll need to nominate a computer that's going to act as the database server (obviously this will need to be one that all machines running the application can connect to over the LAN and that is always going to be running when the application is in use). We'll call this machine "server"

On "server" download and install SQL Server Express from here: https://www.microsoft.com/en-us/download/details.aspx?id=42299 - you'll want the version that is called "ExpressAndTools" and make sure you get the appropriate 32/64bit version depending on your hardware and OS (installing with default settings should work well enough for your purposes).

Next you'll also need to download and install SQL Server Management Studio on the computer that currently has the application/database on (it's from the same link as above and is just called "MgmtStudio"). Once that's installed open it up and should pop a dialog asking for details of where to conenct. Enter "(localDB)\V11.0" as the Server name, select "Windows Authentication" and click on "Connect". You should then see an entry in the left hand column called something like "(localDB)\V11.0 (SQL Server V11.0 - domain\username)", expand that entry out and you should see a folder called "Databases", expand that and you should see an entry matching the name of your application's database. Right-click on that Select Tasks -> Backup. In the backup dialog make sure it's set to Backup type: FULL with the destination being "Disk" and click on "Add" to chose a folder/filename for the back up and then click Ok to start the backup.

Once that's completed you'll need to copy the back up file to the "Server" computer, then open Management Studio on there and when it asks you for connection details use "localhost\sqlexpress" with Windows Authentication. Once your connected expand the node labelled "localhost\sqlexpress" in the left hand column, right-click on "Databases" and select "Restore Database", in the resulting dialog change the "source" radio button to "Device", click on "..." and then click on "Add" and browse to the backup file from earlier. Once that's selected it should populate the rest of the restore fields for you and you can just click "Ok".

Once that's finished you'll need to update the "App.config" (or "Web.config" if it's an ASP.NET application) to reflect the new database connection string. You should be able to find a line similar to thi:

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-WebApplication3-20161207104129.mdf;Initial Catalog=aspnet-WebApplication3-20161207104129;Integrated Security=True" providerName="System.Data.SqlClient" />

change that to point to the SQL Express server so it's something like this (obviously fill out your actual details!):

<add name="DefaultConnection" connectionString="Data Source=nameofserverhere\sqlexpress;Initial Catalog=nameofdatabasehere;Integrated Security=True" providerName="System.Data.SqlClient" />

This should then mean that when the application looks for it's database it will look to the one on the "server" not try and attach the mdf file.

It's been a while since I did this to be honest so hopefully I haven't missed any steps!


Why does sp_rename not affect the output of sp_helptext? create procedure Eg_Proce
as
begin
select * from po;
end

go

exec sp_rename 'Eg_Proce','Example_Procedure'

After renaming procedure

sp_helptext 'Example_Procedure'

The definition of the procedure is not updated

create procedure Eg_Proce
as
begin
select * from po;
end

Don't know why but it is kind of documented.

sp_rename (Transact-SQL)

  Renaming a stored procedure, function, view, or trigger will not
  change the name of the corresponding object name in the definition
  column of the sys.sql_modules catalog view.

sys.sql_modules is a view that uses the base table sys.sysschobjs. sp_helptext uses the view syscomments that in turn uses the base table sys.sysschobjs.


SQL Server benchmarking HammerDb TPM = 0 I'm trying to use HammerDb 3.0 to get a baseline to compare my servers.

I can create the Db, schema, data and items required no problem. I can execute the test and see that it's running.

When the script completes it give me a NOPM number but says TPM is zero. When it's running the Transaction counter doesn't graph anything either.

Is this a known or common problem?

The error is in the output as "SQL Server returned non-numeric transaction count data".  TPM is the same as the transaction count in the SSMS activity monitor ie "Batches/sec". NOPM is queried directly from the schema under test. (TPM is a database specific metric NOPM is database independent). It appears that workload is running OK but SQL Server is not returning transaction counter data. The first thing to check is whether SQL Server has been started with the "-X" option. This disables all performance statistics and would result in this effect. Secondly run the following SQL:

select cntr_value from sys.dm_os_performance_counters where counter_name = 'Batch Requests/sec'

This is what HammerDB runs (you can see it i nthe source code) - when this query works HammerDB should be able to monitor and report transactions.

For saving details more information is required.


How to remove known elements from a JSON[] array in PostgreSQL? I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to model those as JSON in native PostgreSQL arrays.

This is a stripped down snippet of my table creation statement:

CREATE TABLE test.persons
(
  id UUID,
  firstName TEXT,
  lastName TEXT,
  communicationData JSON[],
  CONSTRAINT pk_person PRIMARY KEY (id)
);

As you can see it is a person featuring a list of communication data objects in JSON. One of such objects might look like this:

{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6"}

I can easily append such a JSON object to an array using PostgreSQL's array_append. However, I fail at removing a known value from the array. Consider f.e. this SQL statement:

UPDATE test.persons
SET communicationData = array_remove(
      communicationData,
      '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf6"}'::JSON
    )
WHERE id = 'f671eb6a-d603-11e3-bf6f-07ba007d953d';

This fails with ERROR:  could not identify an equality operator for type json. Do you have a hint how I could remove a known value from the JSON array? It would also be possible to remove by position in the array, as I know that one also...

PostgreSQL version is 9.3.4.

jsonb in Postgres 9.4 or later
Consider the jsonb data type in Postgres 9.4 or later. The 'b' at the end stands for 'binary'. Among other things, there is an equality operator (=) for jsonb. Most people will want to switch.
Depesz blog about jsonb.
json
There is no = operator defined for the data type json, because there is no well defined method to establish equality for whole json values. But see below.
You could cast to text and then use the = operator. This is short, but only works if your text representation happens to match. Inherently unreliable, except for corner cases. See:

How to query a json column for empty objects?

Or you can unnest the array and use the ->> operator to .. get JSON object field as text and compare individual fields.
Test table
2 rows: first one like in the question, second one with simple values.
CREATE TABLE tbl (
   tbl_id int PRIMARY KEY
 , jar    json[]
);

INSERT INTO t VALUES
   (1, '{"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}"
        ,"{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}"
        ,"{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}')

 , (2, '{"{\"value\" : \"a\", \"typeId\" : \"x\"}"
        ,"{\"value\" : \"b\", \"typeId\" : \"y\"}"
        ,"{\"value\" : \"c\", \"typeId\" : \"z\"}"}');

Demos
Demo 1
You could use array_remove() with text representations (unreliable).
SELECT tbl_id
     , jar, array_length(jar, 1) AS jar_len
     , jar::text[] AS t, array_length(jar::text[], 1) AS t_len
     , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text) AS t_result
     , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text)::json[] AS j_result
FROM   tbl;

Demo 2
Unnest the array and test fields of individual elements.
SELECT tbl_id, array_agg(j) AS j_new
FROM   tbl, unnest(jar) AS j   -- LATERAL JOIN
WHERE  j->>'value' <> '03334/254146'
AND    j->>'typeId' <> 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5'
GROUP  BY 1;

Demo 3
Alternative test with row type.
SELECT tbl_id, array_agg(j) AS j_new
FROM   tbl, unnest(jar) AS j   -- LATERAL JOIN
WHERE  (j->>'value', j->>'typeId') NOT IN (
         ('03334/254146', 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5')
        ,('a', 'x')
       )
GROUP  BY 1;

UPDATE as requested
Finally, this is how you could implement your UPDATE:
UPDATE tbl t
SET    jar = j.jar
FROM   tbl t1
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT j
      FROM   unnest(t1.jar) AS j  -- LATERAL JOIN
      WHERE  j->>'value'  <> 'a'
      AND    j->>'typeId' <> 'x'
      ) AS jar
   ) j
WHERE  t1.tbl_id = 2              -- only relevant rows
AND    t1.tbl_id = t.tbl_id;

db<>fiddle here
About the implicit LATERAL JOIN:

How to turn JSON array into Postgres array?
Query for element of array in JSON column

About unnesting arrays:

How to preserve the original order of elements in an unnested array?

DB design
To simplify your situation consider an normalized schema: a separate table for the json values (instead of the array column), joined in a n:1 relationship to the main table.