The dump database on a SSD disk is slowly restored in SQL Server I prepared new server for my DB on Windows 2008R2 + SQL Server 2005 SP4.

I always estimated time of restore database of a backup as time of copying of the file of a backup at that place where the base will be located.

But this time this indicator strongly differs.

Copying takes 30 minutes.

Restore database takes 300 minutes.

After restore database all operations work so quickly as expected.

I can't understand in what the reason of such slow restore database.

Basic reason for restore operation to be slow can be

Hardisk where you are restoring is not able to cope up with I/O request which is required to meet all requests. May be its a busy disk whoes I/O is getting utilized by other process and you started a restore creating extra load and more candidated for I/O. May be disk is slow, requiring some firmware upgrade
Database which you are restoring had to many Virtual log files or when backup was taken some process was rolling back. So now when database will recovery it would make sure that process rolls back completely and then only it can make database online. Backup includes both data and some amount of transaction log to bring database in transactionally consistent state. The amount of transaction log included depends on transaction that were running when backup was  happening. To understand backup please read this article

You must read Optimizing backup and restore in SQL Server

Why copy operation took 30 mins totally depends on load on network and size of backup file being transferred


Will a non-clustered index on a column with covering index on all the columns of table affect the Delete statement Records)? I am trying to delete thousands of records say 35K from a table which consists like 10 million records.

Delete * from Table where ScenarioID=859

I have also tried bulk deletion

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM tableWHERE ScenarioID= @ID
    SET @RowsDeleted = @@ROWCOUNT
END

I would like to ask

Generally How much time it would take to delete such no of records?
If there is any Index on that table then is it gonna affect the delete statement?
Note: I have a non-clustered index on a column ID. Does this have any affect on performance of if i delete records based on that ID
are there any common bottle-necks that may cause delete to take long time than it should take ?

Is there any thing going wrong in this plan ?

PS: My database is hosted in Azure and it takes just below 10 seconds to execute the same command in Azure portal

Generally How much time it would take to delete such no of records ?

Impossible for us to guess - way too many factors involved here, including performance of your data and log drives (a factor of which level of service you chose), your log growth rate and number of auto-growth events that occur, concurrent activity, number of indexes, existence of foreign keys, indexed views, etc.

  If there is any Index on that table then is it gonna affect the delete statement?

Yes. With the exception of filtered indexes, a delete from a table will also delete from each index. This is not free. If you generate an actual plan using the free SentryOne Plan Explorer, it tries to make it very obvious that the cost involves other indexes too:

In Management Studio, there is no visual cue that you're not just deleting from a single (clustered) index, but the information is in the plan XML - so you can also dig for it in the properties:

  are there any common bottle-necks that may cause delete to take long time than it should take?

Yes, mentioned above and in the other answer. You need to find the right balance of number of rows to delete, and we can't guess that for you without a lot more information. Please read this post:

Break large delete operations into chunks

One of the key things you'll see there - and I mention it in an answer to another question of yours - is to use transactions to break the looped deletes up into separate atomic operations.

DECLARE @RowsDeleted INT = 1, @counter INT = 1;

BEGIN TRANSACTION;

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM dbo.table WHERE ScenarioID = @ID;
    SELECT @RowsDeleted = @@ROWCOUNT, @counter += 1;
    IF @counter % 10 = 0
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
END

COMMIT TRANSACTION;

Again, you'll need to find the right balance here. Maybe it's committing 500 rows at a time, or 1,000, or 5,000. Impossible for us to know.

Another common bottleneck is logging activity. This may be resolved by using smaller transactions but could still be an issue. Unfortunately, in SQL Azure I'm not sure how exactly you would find out if that is the case (don't believe the default trace is an option for you).

If you are deleting the majority of the table, another option is to move the rows you want to keep into a new table, then drop the old table, and rename the new one.


trigram matching on a jsonb array Hello I'm trying to implement some sort of fuzzy string matching using pg_trgm on my table which contains a jsonb array,
here is a sample data:

      name
------------------
[{"firstname": "jhon", "lastname": "doe"},{"firstname": "jhonny", "lastname": "doe"}]
------------------
[{"firstname": "jane", "lastname": "doe"},{"firstname": "janne", "lastname": "doe"}]

and this is a sample of my current query:

SELECT * FROM persons WHERE exists(
    SELECT * FROM json_array_elements(persons.name::json)
        WHERE word_similarity('jhon', (COALESCE(value->>'firstname', '') || ' ' || (COALESCE(value->>'lastname', ''))) ) >= 0.7);

It does the job but it's slow(no index).
My question is what can I do to speed up the searching (index, alternative query) ?
TIA

You can't index a rows-returning expression, so it would probably be best to normalize your data with one nickname per row, rather all stuffed into an array.  Then you could index the "nickname" table and join it to the "persons" table.

If you don't want to do that, you could transform the name field into a string, and index that.  This could lead to unwanted results if the query could span over the delimiter and still meet the cutoff (not very likely, unless the query also contains punctuation), so the queries are not identical, but might be good enough.

create or replace function arr_val_agg(jsonb) returns text as $$
  select string_agg(bar.value,', ') from jsonb_array_elements($1)
    join lateral jsonb_each_text(value) bar on true;
$$ language SQL immutable;

create index on persons using gin (arr_val_agg(name) gin_trgm_ops );

select * from persons where arr_val_agg(name) %> 'jhon';

As the lengths of arr_val_agg get longer, the more false positives you are likely to get from the trigram index which need to be filtered out by a recheck, so performance might suffer.


Get total amount of time between records when changed state I have a table, showing the status (healthy, broken) for each device at a given timestamp. I need to get the total amount of time of a whole breakdown.

So the data is stored like this:

device_owner   device_id    timestamp          status
owner1         device_1     2001-01-01 09:00   0
owner1         device_2     2001-01-01 09:15   0
owner1         device_1     2001-01-01 09:30   1
owner1         device_2     2001-01-01 09:45   1

and so on.

The example above shows

device_1 was down from 09:00 to 09:30 (total 30 mins),
device_2 was down from 09:15 to 09:45 (total 30 mins)

What I need

Need to calculate the total amount of time for the whole breakdown for owner_1 will be 45 mins (from 09:00 to 09:45) instead of 60 mins (30 + 30).

device_owner   total_breakdown_min
owner_1        45

What I've done

Currently I can define a breakdown time for each device separately (device_1 = 30mins, device_2 = 30mins). For this I transformed the table using LEAD over partition by device_owner, device_id and date part from timestamp:

device_owner  device_id     timestamp        status lead_timestamp   lead_status
owner1        device_1     2001-01-01 09:00   0     2001-01-01 09:30     1
owner1        device_1     2001-01-01 09:30   1     NULL                 NULL
owner1        device_2     2001-01-01 09:15   0     2001-01-01 09:45     1
owner1        device_2     2001-01-01 09:45   1     NULL                 NULL

So the datetime difference between timestamp and lead_timestamp in sum gives me the total breakdown time for a device.

;WITH LeadStatus AS
(
    SELECT
        D.*,
        lead_status = LEAD(D.status) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC),
        lead_timestamp = LEAD(D.datetime) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC)
    FROM
        #DeviceStatus AS D
)
SELECT * FROM LeadStatus

It is the same device list for any owner. The data snapshot is to be taken between 2 dates (StartDate, EndDate).
The records are written to a DB each n seconds and also when the device status changes.

We can use conditional aggregation and an OVER clause to get the number of broken devices for each row in devicestatus.

SELECT device_owner,
       timestamp,
       sum(CASE status
             WHEN 0
               THEN 1
             WHEN 1
               THEN -1
           END) OVER (PARTITION BY device_owner
                      ORDER BY timestamp) broken#
       FROM devicestatus;

We now use lag() to get the previous number of broken devices for each row.

SELECT device_owner,
       timestamp,
       broken#,
       lag(broken#,
           1,
           0) OVER (PARTITION BY device_owner
                    ORDER BY timestamp) previous_broken#
       FROM (SELECT device_owner,
                    timestamp,
                    sum(CASE status
                          WHEN 0
                            THEN 1
                          WHEN 1
                            THEN -1
                        END) OVER (PARTITION BY device_owner
                                   ORDER BY timestamp) broken#
                    FROM devicestatus) x1;

Now we use conditional aggregation and the OVER clause once again to get an identifier for each period where at least one device of a user was down.

SELECT device_owner,
       timestamp,
       sum(CASE
             WHEN previous_broken# = 0
               THEN 1
             ELSE
               0
           END) OVER (PARTITION BY device_owner
                      ORDER BY timestamp) broken_period#
       FROM (SELECT device_owner,
                    timestamp,
                    broken#,
                    lag(broken#,
                        1,
                        0) OVER (PARTITION BY device_owner
                                 ORDER BY timestamp) previous_broken#
                    FROM (SELECT device_owner,
                                 timestamp,
                                 sum(CASE status
                                       WHEN 0
                                         THEN 1
                                       WHEN 1
                                         THEN -1
                                     END) OVER (PARTITION BY device_owner
                                                ORDER BY timestamp) broken#
                                 FROM devicestatus) x1) x2;

From here on we can now use GROUP BY to get the minimum and maximum timestamp of the periods where at least one device of a user was down.

SELECT device_owner,
       max(timestamp) mints,
       min(timestamp) maxts
       FROM (SELECT device_owner,
                    timestamp,
                    sum(CASE
                          WHEN previous_broken# = 0
                            THEN 1
                          ELSE
                            0
                        END) OVER (PARTITION BY device_owner
                                   ORDER BY timestamp) broken_period#
                    FROM (SELECT device_owner,
                                 timestamp,
                                 broken#,
                                 lag(broken#,
                                     1,
                                     0) OVER (PARTITION BY device_owner
                                              ORDER BY timestamp) previous_broken#
                                 FROM (SELECT device_owner,
                                              timestamp,
                                              sum(CASE status
                                                    WHEN 0
                                                      THEN 1
                                                    WHEN 1
                                                      THEN -1
                                                  END) OVER (PARTITION BY device_owner
                                                             ORDER BY timestamp) broken#
                                              FROM devicestatus) x1) x2) x3
       GROUP BY device_owner,
                broken_period#;

We can now simply use datediff(), sum() and GROUP BY to calculate the total downtime for each device owner.

SELECT device_owner,
       sum(datediff(minute,
                    maxts,
                    mints)) total_breakdown_min
       FROM (SELECT device_owner,
                    max(timestamp) mints,
                    min(timestamp) maxts
                    FROM (SELECT device_owner,
                                 timestamp,
                                 sum(CASE
                                       WHEN previous_broken# = 0
                                         THEN 1
                                       ELSE
                                         0
                                     END) OVER (PARTITION BY device_owner
                                                ORDER BY timestamp) broken_period#
                                 FROM (SELECT device_owner,
                                              timestamp,
                                              broken#,
                                              lag(broken#,
                                                  1,
                                                  0) OVER (PARTITION BY device_owner
                                                           ORDER BY timestamp) previous_broken#
                                              FROM (SELECT device_owner,
                                                           timestamp,
                                                           sum(CASE status
                                                                 WHEN 0
                                                                   THEN 1
                                                                 WHEN 1
                                                                   THEN -1
                                                               END) OVER (PARTITION BY device_owner
                                                                          ORDER BY timestamp) broken#
                                                           FROM devicestatus) x1) x2) x3
                    GROUP BY device_owner,
                             broken_period#) x4
       GROUP BY device_owner;

SQL Fiddle


How can I dynamically create views in Postgres 10 using variables? I know how to do this in MySQL and MSSQL but I'm struggling to do the same in Postgres 10.12.  I have about 200 views to create and the only difference in each one of them is the schema location and the where clause.
For example:
create or replace view SCHEMA1.myview as
    select * from SHAREDSCHEMA.sometable where MYCOL = 'ABC';

So what I need to do is create that view around 200 times with each iteration being:
SCHEMA1.myview
SCHEMA2.myview
SCHEMA3.myview
etc...

MYCOL = 'ABC'
MYCOL = 'EFG'
MYCOL = 'HIJ'
etc...

Hope that makes sense.  Again, the only difference between each iteration is the schema name changes and the where clause changes.
I could probably write some Python script or something to generate the code but I'd rather do it in Postgres if possible.
I know this isn't valid code, but essentially this is what I am trying to do:
do $$
declare
    myschema varchar(10) := 'SCHEMA1';
    mywhere varchar(3) := 'ABC';
begin
    create or replace view @myschema@.myview as
        select * from SHAREDSCHEMA.sometable where MYCOL = @mywhere@;
end $$;

Then each time I run that, I simply change @myschema and @mywhere.
Any help would be appreciated.
Thanks!

You need dynamic SQL for this:
do $$
declare
    myschema varchar(10) := 'SCHEMA1';
    mywhere varchar(3) := 'ABC';
begin
    execute format('
        create or replace view %I.myview as
        select * from SHAREDSCHEMA.sometable where MYCOL = %L', myschema, mywhere);
end $$;

The format() function is highly recommended when creating dynamic SQL to properly deal with identifiers and constant values.
You can also put this into a function (or procedure in newer versions):
create function create_view(p_schema text, p_value text)
   returns void
as
$$
begin
  execute format('
      create or replace view %I.myview as
      select * from SHAREDSCHEMA.sometable where MYCOL = %L', p_schema, p_value);
end
$$
language plpgsql;

Then you can run:
select create_view('myschema', 'xyz');

If you want, you can use that to create all views in one go:
with data (schema_name, value) as
  values
     ('schema1', 'abc'),
     ('schema2', 'def'),
     ('schema3', 'ghi')
)
select create_view(schema_name, value)
from data;


Inserts via Entity Framework on server take about double the time than locally I'm inserting lots of data into SQL Server with many of them having foreign key dependencies. Meaning if I insert 1 row in my Person table, I'm also inserting 1 row in 8 other related tables.

This import takes a lot of time already on my local machine (about 4h30min for 300k (x9) rows), but it takes almost exactly double the time to insert this data on various servers. All of the servers are more powerful than my local machine, so it most likely is not a hardware problem.

What could be the bottleneck here? How do I best go about finding out the cause?
I'm using SQL Server 2017, but we've had this issue since SQL Server 2015 (the one we started the project)

All of the servers are more powerful than my local machine, so it most
  likely is not a hardware problem.

This is a common misconception.

Single-row inserts, like EF generates, are not expensive in CPU cycles.  Typically bottleneck is writing to the log file.  Your local machine may well give you better log write performance than a powerful server.  Your local machine may have a fast, local SSD.  Or you may have write caching enabled on your local disk.

  What could be the bottleneck here?

My guess is log writes.  If you commit after each row, then this will be much worse.  In EF each call to SaveChanges() uses a separate transaction, so you want to batch 10s to 100s of rows in each call.  You also don't want to have too many rows added to a DbContext instance, as they are all tracked for the lifetime of the DbContext.

But more generally, SQL Server will tell you where the bottleneck is.  You can look at the session wait stats for the session performing the insert in SQL 2016+:

select *
from sys.dm_exec_session_wait_stats
where session_id = 51
order by wait_time_ms desc

Or you can look at the server-wide wait stats while the import is running:

https://blogs.msdn.microsoft.com/dbrowne/2013/01/18/my-favorite-query-for-investigating-sql-server-performance/


Which database is more suitable for executing user submitted queries? I am in charge of creating a custom reporting tool for the company's clients. The goal is to let the client's in-house/contractor developers to query our database via a web-based query tool.

There are two main concerns here: Having users only read-only access to the database -- with no ability to run admin functions via queries, and having every query being async and have a timeout to prevent un/intentional DDoS attacks.

I can choose from PostgreSQL, MongoDB and MySQL, but I'm not expert in any of these. Therefore my question is which of these databases offers:

a) more control over each individual query

b) more sophisticated user-based permissions and access control system

c) and a better built-in performance overload handling in case of many small queries or few huge queries that can potentially crash the database

I understand that I'm mixing NoSQL and RDBMS here, but I'm happy to go for either of these if I can build a more reliable solution in the end.

Also scaling and performance come as lower priorities and can be totally excluded from the comparison.

I can only comment on the (never ending) MySQL vs. Postgres question. I have never used MongoDB.

  more control over each individual query

Not sure what you mean with that. If that refers to limiting resource consumption then I don't think any of those three products will help you with that.

If that is about the possibilities you have when writing queries, then Postgres is the clear choice for that. It has a lot more modern SQL features then MySQL does. My understanding is that MongoDB's aggregation capabilities are still not on the same level as those available in modern relational databases - but I have never used MongoDB so I can't tell.

  more sophisticated user-based permissions and access control system

Postgres has definitely the more sophisticated permission systems with user groups and row level security.

  and a better built-in performance overload handling in case of many small queries or few huge queries that can potentially crash the database

To my knowledge, neither Postgres nor MySQL have any built-in features to limit resource consumption of individual queries like e.g. Oracle or SQL Server offer (apart from things like global statement timeouts). But as I don't really use MySQL any more there might be something I don't know about.


Mysqldump, exclude data from tables by query It's possible to ignore tables in mysqldump using:

mysqldump -u username -p database --ignore-table=database.table1 > database.sql

Is it possible to ignore certain records in tables while dumping?

From a single table, it is easy to add

--where='...'


Alternatives to EAV model I am looking for a design pattern that would allow me to capture metadata relating to columns on a table. More specifically, here is my situation.

I have a database with fields in a field table, crops in a crop table and prediction models in a model table. Each field can grow one crop and subscribe to multiple models. Each model offers different kind of predictions for a specific crop.

Now, each crop allows a particular set of inputs from the farmer. For instance, all crops will expect a planting date while only some of them will expect a flowering date. Therefore, I create a generic_input table and crop-specific tables (potato_input, soy_input, etc.) to capture that data. Each column of those table is an input that needs to be captured.

Here comes the interesting part. Each model also specifies a  set of required crop inputs. That is, inputs that need to be set in order for that model to be able to compute its predictions. That means I would essentially need to reference particular columns of my input tables which is not really possible.

So far, the only real solution I found would be the Entity-Attribute-Value pattern. However, I've read so many posts warning against its use (example) that I am quite reluctant to go down that road. Another option would be to create an enum or a table listing all my inputs but then if new inputs need to be added or if we add a new crop to the system, we need to update that list as well: that results in some sort of data duplication or poor relational consistency.

Are there any other pattern that could support my use case? What would be an appropriate solution? Is this a case where EVA would actually be a good solution?

EAV is a solution when the values to be stored cannot be identified during design-time but are captured at run-time. It sounds like your situation is slightly different - you know all the values to be captured but not how to store them.

One relational approach is to use entity inheritance. This is what you describe with generic_input and potato_input etc. Since each crop will have exactly one row in the generic table and exactly one row in a single specific table this inheritance can be flattened into a view for consumption by the models.

If you don't know until run-time which values each model will consume you can introduce a model_attribute table to list each model's requirements. Use dynamic SQL to construct the SELECT from the above flattened view.

The data could be stored in general-purpose columns. The input table then becomes create table input(crop, value_1, value_2, value_3, .. value_n). Further meta-data maps crops to columns' meanings e.g. "for wheat value_1 is planting date; for poppies value_1 is flowering date". This design is good if you really like logic puzzles and debugging.

It is only a small step to unpivot that table to get input(crop, value_number, value). Then substitue the column meanings for value_number to give input(crop, attribute, value) and you have EAV.

My experience with EAV is that it is really rubbish for queries with predicates ("select planting_date where flowering_date = 1st August") but are fine for retrieving all values for a thing ("select * from EAV where entity_id = xyz"). It's left to the application to make sense of the bucket of values that are returned, but it will do a better job than SQL.

EAV is a dynamic schema solution. Others that are better supported by DBMS are XML and JSON. These allow each row of a table to hold values that other rows don't. Depending on the DB software the JSON/XML can be parsed as part of the SQL query. You don't say, but maybe your models accept inputs in one of these formats, too? Of the two JSON is more compact and XML has a richer parsing & query ecosystem.


Missing Target Local Server in Job SQL Server 2016 Enterprise, CU15 + GDR.
Client recently started using OPCON in production and I noticed that when OPCON creates jobs, it doesn't add the Local Server as the target. This prevents the job from executing.

Is there anyway to determine if a specific job doesn't have any target server defined as is the case above?
Thanks,
Craig

Selecting all jobs that have an enabled schedule but no next run date.
SELECT
    sj.name AS job_name,
    sjs.next_run_date,
    syss.enabled AS schedule_enabled
  FROM msdb.dbo.sysjobs sj
  INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id
  INNER JOIN msdb.dbo.sysschedules syss ON sjs.schedule_id = syss.schedule_id
  WHERE
  1=1
 AND  sjs.next_run_date = 0
 AND syss.enabled = 1


Find parent rows that have identical sets of child rows Suppose I have a structure like this:
Recipes table
RecipeID
Name
Description

RecipeIngredients table
RecipeID
IngredientID
Quantity
UOM

The key on RecipeIngredients is (RecipeID, IngredientID).
What are some good ways for finding duplicate recipes? A duplicate recipe is defined as having the exact same set of ingredients and quantities for each ingredient.
I've thought of using FOR XML PATH to combine the ingredients into a single column. I haven't fully explored this but it should work if I make sure the ingredients/UOMs/quantities are sorted in the same sequence and have a proper separator. Are there better approaches?
There are 48K recipes and 200K ingredient rows.

This is a generalization of the relational division problem. No idea how efficient this will be:

; WITH cte AS
( SELECT RecipeID_1 = r1.RecipeID, Name_1 = r1.Name,
         RecipeID_2 = r2.RecipeID, Name_2 = r2.Name
  FROM Recipes AS r1
    JOIN Recipes AS r2
      ON r1.RecipeID <> r2.RecipeID
  WHERE NOT EXISTS
        ( SELECT 1
          FROM RecipeIngredients AS ri1
          WHERE ri1.RecipeID = r1.RecipeID
            AND NOT EXISTS
                ( SELECT 1
                  FROM RecipeIngredients AS ri2
                  WHERE ri2.RecipeID = r2.RecipeID
                    AND ri1.IngredientID = ri2.IngredientID
                    AND ri1.Quantity = ri2.Quantity
                    AND ri1.UOM = ri2.UOM
                )
         )
)
SELECT c1.*
FROM cte AS c1
  JOIN cte AS c2
    ON  c1.RecipeID_1 = c2.RecipeID_2
    AND c1.RecipeID_2 = c2.RecipeID_1
    AND c1.RecipeID_1 < c1.RecipeID_2;

Another (similar) approach:

SELECT RecipeID_1 = r1.RecipeID, Name_1 = r1.Name,
       RecipeID_2 = r2.RecipeID, Name_2 = r2.Name
FROM Recipes AS r1
  JOIN Recipes AS r2
    ON  r1.RecipeID < r2.RecipeID
    AND NOT EXISTS
        ( SELECT IngredientID, Quantity, UOM
          FROM RecipeIngredients AS ri1
          WHERE ri1.RecipeID = r1.RecipeID
        EXCEPT
          SELECT IngredientID, Quantity, UOM
          FROM RecipeIngredients AS ri2
          WHERE ri2.RecipeID = r2.RecipeID
        )
    AND NOT EXISTS
        ( SELECT IngredientID, Quantity, UOM
          FROM RecipeIngredients AS ri2
          WHERE ri2.RecipeID = r2.RecipeID
        EXCEPT
          SELECT IngredientID, Quantity, UOM
          FROM RecipeIngredients AS ri1
          WHERE ri1.RecipeID = r1.RecipeID
        ) ;

And another, different one:

; WITH cte AS
( SELECT RecipeID_1 = r.RecipeID, RecipeID_2 = ri.RecipeID,
          ri.IngredientID, ri.Quantity, ri.UOM
  FROM Recipes AS r
    CROSS JOIN RecipeIngredients AS ri
)
, cte2 AS
( SELECT RecipeID_1, RecipeID_2,
         IngredientID, Quantity, UOM
  FROM cte
EXCEPT
  SELECT RecipeID_2, RecipeID_1,
         IngredientID, Quantity, UOM
  FROM cte
)

  SELECT RecipeID_1 = r1.RecipeID, RecipeID_2 = r2.RecipeID
  FROM Recipes AS r1
    JOIN Recipes AS r2
      ON r1.RecipeID < r2.RecipeID
EXCEPT
  SELECT RecipeID_1, RecipeID_2
  FROM cte2
EXCEPT
  SELECT RecipeID_2, RecipeID_1
  FROM cte2 ;

Tested at SQL-Fiddle

Using the CHECKSUM() and CHECKSUM_AGG() functions, test at SQL-Fiddle-2:
(ignore this as it may give false positives)

ALTER TABLE RecipeIngredients
  ADD ck AS CHECKSUM( IngredientID, Quantity, UOM )
    PERSISTED ;

CREATE INDEX ckecksum_IX
  ON RecipeIngredients
    ( RecipeID, ck ) ;

; WITH cte AS
( SELECT RecipeID,
         cka = CHECKSUM_AGG(ck)
  FROM RecipeIngredients AS ri
  GROUP BY RecipeID
)
SELECT RecipeID_1 = c1.RecipeID, RecipeID_2 = c2.RecipeID
FROM cte AS c1
  JOIN cte AS c2
    ON  c1.cka = c2.cka
    AND c1.RecipeID < c2.RecipeID  ;


pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib
9.1.11 having initially database clusture in directory /var/lib/postgresql/9.1/main.

By using initdb command i have created two other database clusters under directories /usr/local/pgsql/data and /usr/local/pgsql/standby under port 5433 and 5434 respectively. Changes in postgresql.conf and pg_hba.conf files of both clusters as follows:

In postgresql.conf:

wal_level = hot_standby
hot_standby = on
log_destination = 'stderr'
logging_collector = on
log_statement = 'all'

In pg_hba.conf:

local   all             all                                    trust
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5
local   replication     postgres                                trust
host    replication     postgres        127.0.0.1/32            trust
host    replication     postgres        ::1/128                 trust

After that I have installed pgpool to by command sudo apt-get install pgpool2.
This installation create  directory in /etc/pgpool containing files as follows

pgpool.conf
pool_hba.conf
pcp.conf

Changes pgpool.conf as follows:

listen_addresses = '*'
port = 9999
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/9.1/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'localhost'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/var/local/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'localhost'
backend_port0 = 5434
backend_weight0 = 1
backend_data_directory0 = '/var/local/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

pool_hba.conf file as follows:

local   all         all                               trust
host    all         all         127.0.0.1/32          trust

Make entry in pcp.conf of postgresql user with md5 password.
restart pgpool  /etc/init.d/pgpool2 start command and it started.

But when i am trying to use command psql -p 9999 then it throws error as follows:

  bpsql: ERROR:  MD5 authentication is unsupported in replication, master-slave
  and parallel modes.
  HINT:  check pg_hba.conf**.

I have lot of googlee but I am not getting any solution.
Please help me to find solution and suggest me if I am doing anything wrong.

This exact error message is in the FAQ.

You have md5 authentication configured in pg_hba.conf and trust authentication (meaning no authentication whatsoever) in pool_hba.conf.

You need to configure either trust authentication or md5 authentication in both files.


How calculate per tenant disk usage in a multitenant database with tenant_id columns in postgresql I am using Django and postgres for a multi-tenant webapp. We have around 100 tables and each table has a tenant_id column, identifying the tenant of that particular row. I need a solution that would calculate the total space per tenant. Is there a possibility of doing that? And can it be done dynamically?

For the background: we need that so as to limit per user disc space upto a limit of X GB.

Thank you.

Assuming that the average size of a single row is somewhat comparable between tenants, something like the following:

select tenant_id,
       pg_total_relation_size('the_table')::decimal * (count(*)::decimal / (select count(*) from the_table)) as approx_tenant_size
from the_table
group by tenant_id;

That will calculate size per tenant based on the percentage of rows for that tenant.

This will however return completely wrong estimates if you have large text or varchar columns and some tenants only store short values in there and some really large values.


In READ COMMITTED isolation, is it possible to read the old value after the update? I'm trying to solve an issue with SQL Server 2008 R2 and am grasping at straws.

Given that data is housed in leaf-nodes of the B-tree, and is also exists in indexes, that someone could read multiple values during a single UPDATE statement?

Imagine one process updates a row:

--Change status from Pending -> Waiting
BEGIN TRANSACTION
   UPDATE Transactions SET Status = 'Waiting'
   WHERE TransactionID = 12345
COMMIT TRANSACTION

Is it possible that during this BEGIN TRANS; UPDATE; COMMIT another process could read both new and old values?

I wonder this because the update does not alter a value in only one place. That value will exist in multiple places:

leaf nodes of clustered index
IX_Tranasctions_1
IX_Tranasctions_2
IX_Tranasctions_3
IX_Tranasctions_4
IX_Tranasctions_5
...
IX_Tranasctions_n

If the update begins, it has to update values in all these locations. What happens if another process uses an index execution plan to find the value:

IX_Clustered: Pending Waiting
IX_Transactions_1: Pending Waiting
IX_Transactions_2: Pending Waiting
IX_Transactions_3: Pending
IX_Transactions_4: Pending
IX_Transactions_5: Pending
...
IX_Transactions_n: Pending

At that instant, if a query is issued that looks at the value in the clustered index it will find Waiting.

But if a query uses IX_Transactions_4 it will find a value of Pending.

Undocumenting the locking mechanism

The lock order internally is undocumented, but i assume SQL Server takes a shared lock on the clustered and non-clustered indexes:

IX_Clustered: Shared
IX_Transactions_1: Shared
IX_Transactions_2: Shared
IX_Transactions_3: Shared
IX_Transactions_4: Shared
IX_Transactions_5: Shared

Then upgrades those locks to Update locks:

IX_Clustered: Shared Update
IX_Transactions_1: Shared Update
IX_Transactions_2: Shared Update
IX_Transactions_3: Shared Update
IX_Transactions_4: Shared Update
IX_Transactions_5: Shared Update

at this point, another process can still read the values in those 5 indexes; because selects are compatible with Update locks.

Then the update proceeds. If we take a picture at an instant in time:

IX_Clustered: Shared Update Exclusive -> value changed
IX_Transactions_1: Shared Update Exclusive -> value changed
IX_Transactions_2: Shared Update Exclusive
IX_Transactions_3: Shared Update
IX_Transactions_4: Shared Update
IX_Transactions_5: Shared Update

Another process can no longer read values from items with an Exclusive lock, but it can still read values from items that still have the Update lock (Shared locks are compatible with Update locks)

Unless of course that's not what happens

That only works if SQL only promotes to exclusive as needed. If, instead it goes:

IX_Clustered: S U IX X -> value changed
IX_Transactions_1: S U IX X -> value changed
IX_Transactions_2: S U IX X
IX_Transactions_3: S U IX
IX_Transactions_4: S U IX
IX_Transactions_5: S U IX

And i can't type anymore; my will to continue is crushed. Like i said, i'm grasping at straws.

You can use trace flag 1200 to print out locking info and 3916 to print logging info. (Both undocumented AFAIK)

For the following test set up

CREATE TABLE T
(
A INT IDENTITY PRIMARY KEY,
B INT
)

CREATE NONCLUSTERED INDEX IX ON T(B)

INSERT INTO T
SELECT number
FROM master..spt_values

Then running the following twice (as the second run doesn't contain the irrelevant locks acquired during compilation)

DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

SET NOCOUNT ON;

UPDATE T
SET B += 1
WHERE A = 1000

/*If you habitually have 3604 on then change the below*/
DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS

This returns the following output

  Process 54 acquiring IX lock on OBJECT: 11:245575913:0  (class
  bit2000000 ref1) result: OK

  Process 54 acquiring IU lock on PAGE: 11:1:127  (class bit0 ref1)
  result: OK

  Process 54 acquiring U lock on KEY: 11:72057594039042048
  (1f00de11a529) (class bit0 ref1) result: OK

  Process 54 acquiring IX lock on PAGE: 11:1:127  (class bit2000000
  ref0) result: OK

  Process 54 acquiring X lock on KEY: 11:72057594039042048
  (1f00de11a529) (class bit2000000 ref0) result: OK

  XdesId 0000:00000423: m_logReserved 9162, delta 9162, op
  LOP_BEGIN_XACT, caller GenerateLogRec, LSN 00000027:00000159:0001.

  XdesId 0000:00000423: m_logReserved 9379, delta 217, op
  LOP_MODIFY_ROW, caller GenerateLogRec, LSN 00000027:00000159:0002.

  Process 54 acquiring IX lock on PAGE: 11:1:166  (class bit2000000
  ref1) result: OK

  Process 54 acquiring X lock on KEY: 11:72057594039107584
  (dbf572a551f4) (class bit2000000 ref1) result: OK

  XdesId 0000:00000423: m_logReserved 9597, delta 218, op
  LOP_DELETE_ROWS, caller GenerateLogRec, LSN 00000027:00000159:0003.

  Process 54 acquiring IX lock on PAGE: 11:1:166  (class bit2000000
  ref0) result: OK

  Process 54 acquiring RangeI-N lock on KEY: 11:72057594039107584
  (a39688da7d04) (class bit1000000 ref1) result: OK

  Process 54 acquiring X lock on KEY: 11:72057594039107584
  (82cbfa4b30c1) (class bit2000000 ref0) result: OK

  XdesId 0000:00000423: m_logReserved 9671, delta 74, op
  LOP_INSERT_ROWS, caller GenerateLogRec, LSN 00000027:00000159:0004.

  Process 54 releasing lock reference on KEY: 11:72057594039107584
  (dbf572a551f4)

  Process 54 releasing lock reference on PAGE: 11:1:166

  Process 54 releasing lock reference on KEY: 11:72057594039042048
  (1f00de11a529)

  Process 54 releasing lock reference on PAGE: 11:1:127

  XdesId 0000:00000423: m_logReserved 0, delta -9671, op
  LOP_COMMIT_XACT, caller GenerateLogRec, LSN 00000027:00000159:0005.

No lock is taken on the non clustered index (1:166) until after the LOP_MODIFY_ROW update on the clustered index key (on page 1:127) so yes this would be possible.

And the above is with a narrow (per row) update plan.

With a wide (per index) plan each index is updated in turn. Possibly with intervening sort operations as in the plan below.

This would make the window of opportunity wider but, whilst a transaction could potentially read the "old" value from an index yet to be updated, it would not be possible for a read committed transaction to read the "new" version of the value until the transaction was committed.

It is certainly possible for a read committed statement to read two different committed versions of the same value though.

In one connection run

WHILE ( 1 = 1 )
  BEGIN
      UPDATE T
      SET    B += 1
      WHERE  A = 1000;
  END

And then in another run

SELECT *
FROM   T T1 WITH(READCOMMITTEDLOCK )
       INNER HASH JOIN T T2  WITH(READCOMMITTEDLOCK )
         ON T1.A + 0 = T2.A + 0
WHERE  T1.A = 1000
       AND T2.A = 1000

For me about 50% of the time this second query returns a row with different B values as the value changes between the two seeks on T.


I want to select an increment counter group by medec field please how to do this and i would like the output will be as below I want to select an increment counter group by medec field please how to do this and i would like the output will be as below

SELECT @n := @n + 1 as n, Nb, medec, prest, Nompatient, ftt, Categorie FROM (SELECT @n := 0, m.nom as medec, s.id as Nb, ft.id as ftt, sv.libelle , m.nom , pr.libelle as prest, p.nom as Nompatient, ct.libelle as Categorie from services sv, medecins m, sejours s, sejourprestations sjp, prestations pr, patients p, categoriepatients ct, factures ft WHERE sv.id = s.service_id
AND s.medecin_id = m.id
AND s.patient_id = p.id
AND s.categorie_patient_id = ct.id
AND sjp.sejour_id = s.id
AND sjp.prestation_id = pr.id
AND s.facture_id=ft.id
AND day(s.created_at) = day(CURRENT_DATE)) AS ishmael GROUP BY n ORDER BY medec

You can emulate ROW_NUMBER() function in this way:

select
    rn, medec
from
    (select
       medec,
       @rn := case when @last_medec = medec then @rn +  1 else 1 end rn,
       @last_medec := medec
     from
       tbl, (select @rn := 1, @last_medec := '') x
     order by
       medec) y

rn | medec
-: | :-----
 1 | medec1
 2 | medec1
 1 | medec2
 2 | medec2

db<>fiddle here

Have a look at this MySql tutorial:


Is it fine to run a public website on the sql express db? Is it fine to run a public high traffic website on a sql server 2008 express db?

Here is a partial list of restrictions of this version:

Number of CPUs:  1
Maximum memory utilized:  1 GB
Maximum database size:  10 GB
Replication: Subscriber only
Backup compression: N/A
Most of performance features is not available.
...

See comparison list for more details


No email alert on Procedure fail I have configured Database mail and alert, test email is being sent successfully to designated recipient who were part of OPERATORS but I have created an alert on EXECUTE PROCEDURE (exec sp_insert_shift_A_data; which is not sending email on Procedure fail error, I have set SEVERITY to "016 Miscellaneous user error" and CHECK on raise alert when message contains and MESSAGE TEXT given "Violation of UNIQUE KEY constraint".

Kindly guide me if there is any mistake in configuration or How it is configured? Why it is not sending email on Procedure fail error while procedure failed to execute?

The severity value for a unique key violation error is 14, not 16. You need to set your alert to fire on severity 14 instead. You can see the severity returned using the below code:

CREATE TABLE Test123 (Num INT)
GO

INSERT INTO Test123 (Num)
VALUES (1)
GO

CREATE UNIQUE INDEX UQ123 ON Test123(Num)
GO

BEGIN TRY
    INSERT INTO Test123 (Num)
    VALUES (1)
    DROP TABLE Test123
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity, ERROR_MESSAGE() AS Message
    DROP TABLE Test123
END CATCH
GO

Alternatively, change your alert to fire on error number 2601 (the error number for duplicate key value errors).

EDIT: The above is valid for a unique index. For a unique constraint, the severity is still 14, but the error number is 2627.


How to extract the last inserted row in SQL Server? When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?

By definition, a table is an unordered bag of rows (see #3 here). There is no way to ask SQL Server which row was inserted last unless you are doing so in the same batch as the insert. For example, if your table has an IDENTITY column, you can use SCOPE_IDENTITY() (never use @@IDENTITY, since that can be unreliable if you have or will ever add triggers to the source table):

INSERT dbo.table(column) SELECT 1;
SELECT SCOPE_IDENTITY();

More generally, you can use the OUTPUT clause, which doesn't rely on an IDENTITY column (but will still make it difficult to identify which row(s) the clause identifies if there is no PK):

INSERT dbo.table(column) OUTPUT inserted.* SELECT 1;

If you're not talking about the same batch, then the only real way to identify the last row inserted is to use a date/time column where the timestamp of insertion is recorded. Otherwise it is like you emptied a bag of marbles on the floor, then asked someone to enter the room and identify which one hit the floor last.

You may be tempted or even advised to use the IDENT_CURRENT() function, but I explain here why this is unreliable too.

You could add a column to track this going forward:

ALTER TABLE dbo.table ADD DateInserted DEFAULT CURRENT_TIMESTAMP;

Now you can find the last row(s) inserted by simply:

;WITH x AS (SELECT *, r = RANK() OVER (ORDER BY DateInserted DESC)
   FROM dbo.table)
SELECT * FROM x WHERE r = 1;

(If you don't want ties, you can add a tie-breaking column to the ORDER BY, or you can simply change RANK() to ROW_NUMBER() if you don't care which of the tied rows you get.)

You might make the assumption that the last row inserted is the highest identity value, but this isn't necessarily the case. The identity can be reseeded and it can also be overridden using SET IDENTITY_INSERT ON;.


MySQL equivalent of a spatial ST_Union aggregate function? I'm working on a DB-spacial project in Mysql and I was trying to perform a union operation for a group of shapes. Im looking for something similar to the  SQL Server function UnionAggregate, or the PostGIS Spatial Aggregate function ST_Union

for example (In SQL SERVER):

SELECT
        geometry::STGeomFromWKB(Shape,27582),
        area_code
FROM area_table
WHERE area_code='xxxxxxx';

ST_GeomFromWKB do the same job in Mysql.

in the second part and with the UnionAggregate function + group by :

select
        dbo.UnionAggregate((geometry::STGeomFromWKB(Shape,27582)).MakeValid()),
        area_code
FROM area_table
WHERE area_code='xxxxxxx'
GROUP BY area_code;

Any Idea how to perform a similar operation (aggregate+group by) in Mysql.

Thanks.

I found another alternative to perform the union aggregation by using ST_Union. I had some issues cause this Mysql function get only 2 parameters as inputs.

As a solution, I create my own function as follow :

CREATE FUNCTION `fct_create_geom_collection`(ac CHAR(8))
 RETURNS GEOMETRY
BEGIN
                    DECLARE position INT;
                    DECLARE sortie BOOLEAN DEFAULT FALSE;
                    -- get all area_IDs
                    DECLARE curgeom CURSOR FOR
                    SELECT DISTINCT area_ID
                    FROM area_table
                    WHERE area_code= ac ORDER BY area_ID;
                    DECLARE CONTINUE HANDLER FOR NOT FOUND SET sortie = TRUE;

    OPEN curgeom;
                  -- get the first area_id
                   FETCH curgeom INTO position ;
                  -- put the shape into @var_g_0 for this area_id
                   SELECT ST_GeomFromWKB(Shape,27582) INTO @var_g_0
                   FROM area_table
                   WHERE area_ID = position  ;
    LLD: LOOP
    IF sortie THEN
    LEAVE LLD;
    END IF;
                    -- get the second area_id
                    FETCH curgeom INTO position  ;
                    -- put the second  shape into @var_g for this area_id
                    SELECT ST_GeomFromWKB(Shape,27582) INTO @var_g
                    FROM area_table
                    WHERE area_ID    = position   ;

                    -- performing a union operation between @var_g_0 and @var_g
                    SET @geom = ST_UNION(@var_g_0,@var_g); -- enclosed st_union
                    -- put the result into @var_g_0
                    SET @var_g_0 = @geom;

                   END LOOP;
                   CLOSE curgeom;
                   RETURN ST_GeomFromText(ST_ASTEXT(@geom));
                 -- RETURN ST_GeomFromWKB(@geom);

END$$

This function enable an aggregate union over shapes for each "area code" which is what UnionAggregate do and what I need.


wait_timeout kills session in SLEEP MODE or all connection ?` Do wait_timeout session clear all running session or only sessions which are in SLEEP mode for specified seconds ?

Wait Timeout is a variable which represent the time in seconds that MySQL will wait and kill the session if it is in idle/sleep state.

Default value is 28000 secs. If you want to change the wait time value, you also need to change the interactive_timeout variable.

There will be no active sessions with this time interval. If your session is showing as active and some activity is happening so long, then there is issue with the session.


Does SQL Server route connections to a secondary replica which has commit mode as synchronous in case of an AG with multiple secondary nodes? If I have an AG with 4 different nodes as below:

Primary Node
Secondary Node 1 - Asynchronous Commit - Read Only Yes
Secondary Node 2 - Synchronous Commit - Read Only No
Secondary Node 3 - Synchronous Commit  - Read Only Yes

In this case, if a connection to secondary node is required from an application, will the SQL Server route it to Node 1 or Node 3 ? Node 2 is out of question because it's Read Only is set to NO, isn't it ?

Ideally it should be routed to Node 3 because it has synchronous commit and data will be more consistent with the primary. Is that's what will happen here?

It will depend on your routing list.

SQL Server 2104 and SQL 2012 read-only routing directed traffic to the first available replica in the routing list, unless it was not accessible, and then it would direct the connection to the next replica in the routing list. When you have multiple secondary replicas available for read, it is not possible to spread the read load across those replicas.

In SQL Server 2016 you can configure load-balancing across a set of read-only replicas.

Details Here: https://blogs.msdn.microsoft.com/alwaysonpro/2016/05/02/sql-server-2016-alwayson-availability-group-enhancements-load-balance-read-only-routing/


Records in a table unique period I have the following table:

+--+---------+-------+--------------+--------------+
|ID|HEADER_ID|VERSION|FROM_DATE_TIME|  TO_DATE_TIME|
+--+---------+-------+--------------+--------------+
| 1|      100|      1|03-06-14 12:56|17-12-14 09:08|
| 2|      100|      2|17-04-14 09:09|14-04-15 14:55|
| 3|      100|      3|14-04-15 14:56|          NULL|
| 4|      101|      1|02-09-13 11:33|02-09-14 11:32|
| 5|      101|      2|02-09-14 11:33|30-09-14 17:08|
| 6|      101|      2|15-12-14 07:51|15-01-14 07:51|
| 7|      102|      1|17-03-11 09:44|17-03-12 09:43|
| 8|      102|      2|17-03-12 09:44|17-03-13 09:43|
+--+---------+-------+--------------+--------------+

This table contains the versions in time for a certain header.

The id column which is the primary key.
The header_id column, which is a foreign key to a header table.
The version column, which defines the version (this does not have to be unique).
The from_date_time column, which defines the start date and time that this version was active.
The to_date_time column, which defines the end date and time that this version was active.

What I would like to achieve is the following:

The combination header_id and the period between the from_date_time and the to_date_time should be unique. So periods should not overlap.

Is it possible? Is it possible with MySQL? How to implement in MySQL? How to implement in other DBMS if MySQL is not possible.

There is no "constraint" in MySQL to achieve your complex constraint.  However, you could probably write a TRIGGER(s) that would check each INSERT and UPDATE to see if your requirements are violated.  The TRIGGER could act like a constraint -- barf on your INSERT/UPDATE.

I suspect (but do not know for a fact) that other DBMS also have no such constraint, yet do have a TRIGGER-like capability.


Cant start mysql and cant find mysql.sock I am on Cento 6.9 and trying to solve a problem with Mysql.

When I try to start mysql I get:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket
  '/var/lib/mysql/mysql.sock' (2)

As per recommendation of gurus here I tried to find mysql.sock file. I have tried:

find . -name mysql.sock

But could not find anything. Any idea where my mysql.sock is?

I have also tried:

lsof -U -c mysqld -a

which returns nothing.

Checks:

Is the server actually running locally?
Grep for mysql.sock in my.cnf and provide results
Run: sudo /usr/sbin/lsof  | grep mysql.sock
Check (i.e. grep) mysqld err logs to see if its remarking about mysql.sock or any other startup issues.
Check permissions on the MySQL directories, i.e. /var/lib/mysql/ should be read/writable by the mysql group and sufficient permissions for users to reach the socket file.
How are you starting the mysqld process? The error looks more like a client connection issue?

While you can re-install solutions I have used in the past include:

Touch the socket file and make sure it is owned by the mysql group and user.

ls -l /tmp/mysql.sock
srwxrwxrwx 1 mysql mysql 0 Jun 15 15:08 /tmp/mysql.sock

Check the my.cnf for the setting for where the socket is.

You can use the following steps to re-create the socket. Just change the name to reflect the expected name of the socket as given by my.cnf under the [mysqld] and [client] sections.

sudo touch /tmp/mysql.sock
sudo chown mysql:mysql /tmp/mysql.sock
sudo chmod 0777 /tmp/mysql.sock

Starting mysqld as a service via systemctl mysql start etc.
Make sure you are using sudo etc. as most command require root level privileges to work correctly.


Deleting a Large Crashed MyISAM Table Our server has a large MyISAM MySQL table that is no longer needed and is currently crashed. If we repair it then it greatly slows down our server consuming sources and it's almost 50GB so the process will take a very long time.

Can we safely delete the .myd and .myi files for the table manually? What would be the proper procedure for this to not affect the other tables. Would MySQL simply need to be restarted after we delete the files or are there more steps to this?

Thank you!

From my experience with MYISAM (I am still trying to find documentation that supports it), it is safe to manually delete all three table files (.MYD, .MYI, .frm) and that's it - the table is dropped.

After doing that on a test table, MySQL could not find it anymore (SELECT * FROM test returned an error saying the table does not exist), and I was able to CREATE TABLE test again, so it seems that nothing remains from the original table.

I would recommend the following:

Delete the three files.
Try dropping table table again (this will
probably return an error, hopefully 'table does not exist'.

Good luck.


Best way to check if record exists or not in MySQL I have a procedure that should check if a record exists or not for particular date range, if exists then fetch the record else fetch last 20 record.

For this i have to write a query multiple times, one for checking the existance , then fetch the same record or fetch record without where clause but with limit .

Query goes something like this inside procedure

set @cnt = (select count(*) from table where date_field between date1 and date2) ;
if @cnt > 0 then
    select * from table where date_field between date1 and date2 ;
else
    select * from table order by date_field desc limit 0,20 ;
end if ;

Is there any way i can do it in a single query because my query has too many joins and too complicated, it may take some amount of time, so if i call it two times the fetching time will increase.

You could probably reduce this to 2 queries with something like:

select * from table where date_field between date1 and date2 ;
set @count = found_rows()
if @count = 0 then
    select * from table order by date_field desc limit 0,20 ;
end if ;


Query is slow with JDBC parameters, fast with concatenated SQL I have an application that can be run with either mysql or SQL Server. There is a query that runs in a reasonable time on mysql, but is very slow on SQL Server. My application uses hibernate, but this particular query is coded in raw SQL so I know exactly what SQL is being executed. If I run the same query on SQL Server directly in a database client, it runs quickly. It's only slow when run in my application.

I discovered that if I take out the use of JDBC parameters and just concatenate the values into the SQL string, it runs much much faster. However this isn't a good solution; it doesn't guard against SQL injection or handle characters that might need escaping.

I have found recommendations to use sendStringParametersAsUnicode=false on the connection, but this isn't appropriate for my case because the columns are all nvarchar. In any case, I did try this and it didn't make anything faster and made some things slower. I also found info that suggested the jtds driver is faster, but it's not compatible with hibernate so I have to use the Microsoft driver (mssql-jdbc-7.0.0.jre8.jar).

My slow-running code looks like this:

    String sql = " SELECT DISTINCT FOO.BAR_ID_ "
            [big join here across multiple tables]
            "WHERE FOO.ID_= :fooId " +
            " AND BAZ.NAME_ LIKE :likeParameter ";
    Query query = getSession().createSQLQuery(sql)
            .setParameter("fooId", fooId)
            .setParameter("likeParameter", likeParameter);
    return query.list();

It runs quickly if I change it to this:

String sql = " SELECT DISTINCT FOO.BAR_ID_ "
            [big join here across multiple tables]
            " WHERE FOO.ID_= " + fooId  +
            " AND BAZ.NAME_ LIKE '" + likeParameter + "'";
    Query query = getSession().createSQLQuery(sql);
    return query.list();

The LIKE parameter has wildcards on both ends, which I would expect to prevent the use of an index on that column, but that would be true for the concatenation case as well, and it runs quickly. Clearly there is some other issue with SQL Server and JDBC parameters besides the Unicode issue. What can I do to get around this?

UPDATE: here are the query plans:

Fast query with in-line data: https://www.brentozar.com/pastetheplan/?id=SkkH0NiU4

Slow query with parameters: https://www.brentozar.com/pastetheplan/?id=SkBxhQoLE

Not a complete answer, but here's some diagnostics and suggestions from looking at the plans.

You're running SQL 2016, so there's a ton of useful info in the plan.

First is that you're running a GDR patch for SQL 2016 SP1 (13.0.4224.16).  SQL 2016 SP2 was released on April 24, 2108, almost a year ago, and after 1 year applying the service pack is required for support.  So you can test this on SQL 2016 SP2, or SQL 2017 to see if it behaves differently.

When optimizing a parameterized query, SQL Server has to pick a plan that will be used for all future values of fooId and likeParameter.  Apparently it's guessing you will often have a selective LIKE predicate, and so using the Name index will, on average, be cheaper.  With the hard-coded values, SQL Server doesn't need to build a plan that works for multiple different LIKE predicates, and so picks a plan that doesn't use a non-clustered index.

To evaluate the predicate NAME_ LIKE @p SQL Server is range scanning a non-clustustered index on NAME_.  This works great if you happen to pass a LIKE predicate that starts with a few literal characters, but when it starts with a wildcard, will require a complete scan of the index.  Here scanning, and evaluating the LIKE predicate for each of 14M rows.

In the fast plan SQL seeks 21,518 separate components using the clustered index and checks each one's name against the LIKE predicate.

From the plans:

The difference between the plans is a single operator, which accounts for all of the increased cost: a scan of 14,675,300 rows from the index [PDL_COMPONENT].[IDX_COMPONENT_NAME], taking 37sec of CPU time:

  <RunTimeInformation>
    <RunTimeCountersPerThread
      Thread="0"
      ActualRows="741"
      Batches="0"
      ActualEndOfScans="1"
      ActualExecutions="1"
      ActualExecutionMode="Row"
      ActualElapsedms="37038"
      ActualCPUms="37028" />
  </RunTimeInformation>

Not parameter sniffing, as the ParameterCompiledValue is the same as ParemeterRuntimeValue:

    <ParameterList>
      <ColumnReference Column="@P1" ParameterDataType="nvarchar(127)" ParameterCompiledValue="N'%geronimo%'" ParameterRuntimeValue="N'%geronimo%'" />
      <ColumnReference Column="@P0" ParameterDataType="int" ParameterCompiledValue="(9)" ParameterRuntimeValue="(9)" />
    </ParameterList>

Using the query with the hard-coded values isn't the worst idea here.  Or you could try a RECOMPILE and turn off plan caching without giving up parameterizing your queries.

And I don't know what the indexes and foreign keys in the database are, but using DISTINCT has really bad code smell, as it often indicates that one or more JOIN is not correct, and it affects the cardinality estimation of the query.


DBCC FREESYSTEMCACHE(‘SQL Plans’) deletes all ad-hoc and prepared plans, not just single-used ones Kimberly Tripp's blog post here made me think that running DBCC FREESYSTEMCACHE('SQL Plans') clears just single-used ad-hoc and prepared query plans. But that seems incorrect. It clears all. Which is probaly not a good option to go if you have high average use count of prepared plans.

Is there any option to clear JUST ad-hoc plans? Not the prepared ones?

Try this:

DECLARE @plan_handle varbinary(64)

DECLARE db_cursor CURSOR FOR
SELECT plan_handle
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @plan_handle

WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC FREEPROCCACHE (@plan_handle);
    FETCH NEXT FROM db_cursor INTO @plan_handle
END

CLOSE db_cursor
DEALLOCATE db_cursor


Is there a way to retrieve the logical filename from a backup file? I would like to write an automation script that restores a SQL Server DB from its backup file. However, doing this is not a straightforward procedure in SQL because the main query needs additional inputs which can actually be obtained using another query. Can I do this in one query?

There is a question already on SO; but the solution is not very flexible. RESTORE FILELISTONLY's definition changes very frequently. Even otherwise, the solution seems very verbose.

Isn't there a simpler way to store the results of a query into a variable and use them? This is a cakewalk in any programming language.

Get the Logical Name:

RESTORE FILELISTONLY
FROM DISK = 'D:SourceBackUpFile.bak'
GO

Restore the DB:

RESTORE DATABASE DBName
FROM DISK = 'D:SourceBackUpFile.bak'
WITH RECOVERY
MOVE 'SourceMDFLogicalName' TO 'D:TargetMDFFile.mdf',
MOVE 'SourceLDFLogicalName' TO 'D:TargetLDFFile.ldf'

Taken from this SO question. This seems to work for all versions up to and including 2019.

Just to note, the definition has changed twice since SQL 2000 - I don't judge 2 changes in 19 years as a frequent change personally. In that sense I wouldn't worry about using RESTORE FILELISTONLY as long as you use a generic script like the one below.

CREATE TABLE #FileListHeaders (
     LogicalName    nvarchar(128)
    ,PhysicalName   nvarchar(260)
    ,[Type] char(1)
    ,FileGroupName  nvarchar(128) NULL
    ,Size   numeric(20,0)
    ,MaxSize    numeric(20,0)
    ,FileID bigint
    ,CreateLSN  numeric(25,0)
    ,DropLSN    numeric(25,0) NULL
    ,UniqueID   uniqueidentifier
    ,ReadOnlyLSN    numeric(25,0) NULL
    ,ReadWriteLSN   numeric(25,0) NULL
    ,BackupSizeInBytes  bigint
    ,SourceBlockSize    int
    ,FileGroupID    int
    ,LogGroupGUID   uniqueidentifier NULL
    ,DifferentialBaseLSN    numeric(25,0) NULL
    ,DifferentialBaseGUID   uniqueidentifier NULL
    ,IsReadOnly bit
    ,IsPresent  bit
)
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(4)) as float) > 9 -- Greater than SQL 2005
BEGIN
    ALTER TABLE #FileListHeaders ADD TDEThumbprint  varbinary(32) NULL
END
IF cast(cast(SERVERPROPERTY('ProductVersion') as char(2)) as float) > 12 -- Greater than 2014
BEGIN
    ALTER TABLE #FileListHeaders ADD SnapshotURL    nvarchar(360) NULL
END
INSERT INTO #FileListHeaders
EXEC ('RESTORE FILELISTONLY FROM DISK = N''BackupFileName.bak''')

SELECT * FROM #FileListHeaders

DROP TABLE #FileListHeaders


Prevent SQL Server Management Studio changing destination DB on restore Is there a way to prevent SQL Server Management Studio 2014 from automatically setting the destination database when you try to restore a backup to another database?

I just had a nasty surprise when I wanted to restore a "somewhat fresh" backup of the production DB on the acceptance, and noticed after the restore (+Overwrite database) started not on acceptance but production. I ruined a whole 6 hours of business work (thanks IDE to know better than me how to get me fired...)

No and as you're aware this is a pretty serious accident to make.

I'd suggest learning a little PowerShell. Why? Because that kind of restore is going to happen often, and you could script it into an agent job or one liner - and you won't make this mistake again. It's one of the great parts of scripting; reducing human error and improving the script each time rather than having to remember everything yourself.

As for other suggestions

You can backup from an account on PROD that can only backup and doesn't have restore privileges.
Never do just a restore. Always do a backup (with copy only) of what you're overwriting before the restore. Every time. (If you're extremely confident of your existing backups and can run a quick verify of them... eg that you've taken a log backup in the last minute or so and the log chains aren't broken and that the files are there... you may get away with that too).

I can tell you there's been a number of times I've been given specific server and database names, had them double checked, then had someone realise while its in progress that THEY were wrong and had mixed something up. Having a quick rollback on hand is a godsend.


How to Use SELECT DISTINCT I have a question about using the DISTINCT keyword in SQL.
I have a table named Customers.  Each record contains
3 fields: CustID, CustName, CustCity.  5 records show
a CustCity of "Chicago".

I want the SQL results to show just one record for each unique city name.
Also, I just want to see the CustID and the CustName from each record.

I tried this but got a syntax error:

SELECT CustID, CustName DISTINCT CustCity
FROM Customers

MS-Access lacks window functions, so you'll need a subquery solution. I think this would work:

SELECT c.*
FROM Customers AS c
  INNER JOIN
    ( SELECT CustCity, MIN(CustID) AS CustID
      FROM Customers
      GROUP BY CustCity
    ) AS m
  ON (  m.CustCity = c.CustCity
    AND m.CustID = c.CustID
     ) ;


Large query with subquery runs indefinitely after mysql update We recently did a big server update, in the process migrating from mysql-5.0 to 5.5.40 Several of the queries, which had been running well on the old server, now grind everything to a screeching halt. After some investigation, I figured out the following minimal example of a query that triggers the problem:

SELECT
    (SELECT date FROM results
        WHERE person_id = people.id
        ORDER BY results.date DESC limit 1
        ) AS date
FROM people
WHERE people.boss_id = 123;

Some base info:

Both people and results are MyISAM tables
There are primary key ids on both tables and no other indexes
results is one of our larger tables, currently clocking in at about 4 million rows
people.boss_id = 123 filters down to ~3000 rows
The above the query runs in about 4 seconds on the older mysql-5.0 server
On the newer, mysql-5.5 server the same type of query on the same dataset has run for up to three hours before I manually killed the process, during which it reported as "sending data" My research suggests that that means that the database is filtering the data, but I'm not 100% sure on that.
Running the above query on the newer server produces the same results: stuck query "sending data" until I manually kill the process.
Both tables work fine on their own
I can actually run a single isolated version of the subquery (ie where person_id = 123 or whatever). It takes a few microseconds on the newer server.

Here's what an EXPLAIN EXTENDED looks like:

select_type         table    type   possible_keys   key         key_len     ref     rows    filtered    Extra
PRIMARY             people   ref    boss_id         boss_id     4           const   4217    100.00      ----
DEPENDENT SUBQUERY  results  index  boss_id         date        32          NULL    1       1700.00     Using where

So, any ideas on what's causing this? I'm more than a little stuck. Have I run into some sort of bug? Could upgrading to mysql-5.6 possibly fix this (it's got a new query planner, after all)? Might changing one or both of the tables to InnoDB help? Would repairing one table or the other help?

Incidentally, one of the annoying side effects of this problem is that it locks up phpmyadmin. It's a little unnerving, actually. The commandline works fine and well, but phpmyadmin will just sit there "[loading]" until I kill the rogue process via command line. Any ideas on why a stuck query might do that?

This does not work in MYSQL?
Give it a try - what do you have to lose?

SELECT people.id, max(results.date)
FROM people
JOIN results
      on people.id = results.person_id
     and people.boss_id = 123
GROUP BY people.id;

Would benefit from indexes on:
1) results.person_id
2) people.boss_id

Index on date may help but start without


mysql client auto reconnect in case of ERROR 2013 I'm searching for a way how to instruct mysql client to reconnect in case of ERROR 2013. I'm testing PXC with ProxySQL and want to keep sql statements flowing from the client in case when the writer node get killed and new one is promoted. Is it possible for mysql client to reconnect when server goes down during the query? Can mysql client rerun the sql query (insert, update, ...)? With Sysbench it is possible if setting the --mysql-ignore-errors=all parameter.
(from Comment)  I'll be calling that SP from a custom lua script, where I'll test the 'error 2013' condition and in that case I'll rerun that query. Does this make sense, or the value of @err set by the error handler can't be passed to the script, because the session will just die, when the mysqld will get killed?
DELIMITER //
CREATE  PROCEDURE sbtest.InsertIntoTable (
                IN trnid INT, IN unixtime INT,
                OUT err INT)
BEGIN DECLARE CONTINUE HANDLER FOR 2013 SET @err = 1;
INSERT
     INTO  sbtest.failover_test ( node, trn_id, unix_time )
         VALUES  (@@hostname, trnid, unixtime);
END//

my table:
failover_test | CREATE TABLE `failover_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `node` varchar(255) DEFAULT NULL,
  `trn_id` int DEFAULT NULL,
  `unix_time` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=116837 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

--
CALL sbtest.InsertIntoTable(1, 1599207191, @err);SELECT @err

Encapsulate the query in a stored procedure.  In the SP, test for errors.  When one happens, reconnect and retry the query.
In general, autoreconnect is not wise.  This is because you might crash in the middle of a multi-statement transaction.  If you don't notice the crash, it could start a new transaction in the middle.
So, depending on how critical the data is, be careful.


Efficient table structure for application logging Am using SQL Server as my RDBMS and i want to maintain some transaction of an auto run tool which will log some info on a daily basis. I need some suggestions how an efficient structure to be.

I mean single table will be an efficient one or two or more tables based on the transaction type (say for example success,failure).

If possible, explain with a simple scenario

1) The first thing you would need to figure out, what recovery model you should go with , i.e. decide accordingly as per the need between , FULL , BULK and SIMPLE recovery model.

Read here for configuring the same: Choosing the Recovery Model for a Database

2) When you create a new database in SQL Server 2008 and SQL Server 2005, the data and log files have the following characteristics by default:

  Data: initial size 2MB; Autogrowth: by 1MB, unrestricted growth

  Log:  initial size 1MB; Autogrowth: by 10 percent, unrestricted
  growth

Therefore the first thing you will need to look upon what is the default value you will like to give while setting up for first time.

Its better to test the activity first on test env, come up with a minimal amount of capacity planning to help define your initial size and a maximum size.

3) Once you have come up with a default values for log file, its recommended to monitor the usage of log file or track its growth:

There are various ways of doing that.

You can use below query and collect the results storing in a table over a period of time via SQL agent job:

SELECT name,
  size, -- in 8-KB pages
  max_size, -- in 8-KB pages
  growth,
  is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG'

or using DBCC SQLPERF(LOGSPACE)

4) If a database is configured with the Full or Bulk Logged recovery model, you should back up the transaction log regularly so it can be truncated to free up inactive log space. The backup can also be used (along with the database backups) to restore the database in the event of failure.

Therefore its better to choose you're defaults correctly in order to avoid lot of event changes in log that may hamper the performance.


What would cause extra records to be inserted during a INSERT trigger The following trigger that joins Inserted table on itself creates multiple unwanted records:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
    IF EXISTS(SELECT 1 FROM Inserted a
                LEFT JOIN Inserted b ON b.Flag = 1 AND a.ID = b.ID
                WHERE b.Flag IS NULL AND a.Flag = 0
                ) -- exists
    BEGIN
        INSERT INTO Foo(ID, Etc, Flag)
        SELECT a.ID, a.Etc, 1 FROM Inserted a
            LEFT JOIN Inserted b ON b.Flag = 1 AND a.ID = b.ID
            WHERE b.Flag IS NULL AND a.Flag = 0
    END -- insert
END -- trigger

Where as this trigger that joins Inserted on Foo (the table that the trigger is linked to) creates one record.

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
    IF EXISTS(SELECT 1 FROM Inserted a
                LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
                WHERE b.Flag IS NULL AND a.Flag = 0
                ) -- exists
    BEGIN
        INSERT INTO Foo(ID, Etc, Flag)
        SELECT a.ID, a.Etc, 1 FROM Inserted a
            LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
            WHERE b.Flag IS NULL AND a.Flag = 0
    END -- insert
END -- trigger

The second trigger does exactly what I want, but I'm trying why the first trigger creates so many records. I would've expected Inserted and Foo to be identical when the trigger fires. Is there any kind of special property/behaviour that the Inserted table exhibits when joining on itself.

Apologies if there's not enough info, I've tried to reduce to the basic differences between the two triggers.

Update #1

I'm trying to backfill the table with records where the user account has transactions, but doesn't have the flag set.

Update #2

The inserts are carried out by a C# app. The data source is flat file where each line represents a single row in the table. I don't know if the app uses connection pooling or if this matters. The bulk inserts are done once a month. The C# app usually imports 8373 rows. The 1st trigger inserts 50 additional row, there's only one row that I want.

First, you don't need the IF EXISTS part in your trigger code. You have the exact same code in the second part so if there are 0 rows produced, you'll be inserting 0 rows anyway:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)
    SELECT a.ID, a.Etc, 1 FROM Inserted a
        LEFT JOIN Foo b ON b.Flag = 1 AND a.ID = b.ID
        WHERE b.Flag IS NULL AND a.Flag = 0
END ; -- trigger

which can also be written as:

CREATE TRIGGER [dbo].[trFooInsert] on [dbo].[Foo]
AFTER INSERT
AS
BEGIN
    INSERT INTO Foo(ID, Etc, Flag)
    SELECT a.ID, a.Etc, 1
        FROM Inserted a
        WHERE a.Flag = 0
          AND NOT EXISTS
              ( SELECT *
                FROM Foo b
                WHERE b.Flag = 1 AND a.ID = b.ID
              )
END ; -- trigger

So, you want any insert on the table with Flag=0 to also trigger another insert, with same data but Flag=1 (unless there is already a row in Foo with that ID and Flag=1.

Second, and what the actual problem is about, Inserted is a special table with all the (just) "inserted" rows, not the full table you inserted into. It obviously has less (than or equal) rows as Foo after the insert. So a join to it will not give same results as a join to Foo.

The trigger that produces the multiple unwanted rows had Inserted b instead of Foo b so it does this:

For any insert on the table with Flag=0 it also triggered another insert, with same data but Flag=1 (unless this same insert had such a row inserted).

You can read more about the special Inserted and Deleted tables at the MSDN:
How to use the inserted and deleted Tables


What are the minimum matching criteria recommended for reliable demographic based patient matching? When matching patients based on demographic data are there any recommendations on what fields should match for the patient to be the "Same Patient"?

I know the algorithms will be different for different implementations, I'm just curious if there are any best practices or recommendations around this process.

First Name
Last Name
Date of Birth
SSN
Address
City
State
Zip

etc?

There's this great essay (in spanish, sorry) written by Pablo Pazos, a CS Engineer from Uruguay who has been working on Healthcare IT since 2006 and has made some great contributions to the field, in which he describes an algorithm for doing this.

You can run the article through a translator, but the gist of it is that the basic info to determine a person's identity are their given and family names (both from father and mother), sex, and date of birth. Interestingly enough, he specifically excludes id numbers like SSN from his identity matching algorithms, since "any kind of identifier is NOT part of his identity" (I guess this point could be debatable, though). Also, he excludes attributes like street address, phone numbers, etc. since they aren't really related with the identity of someone, they aren't associated with "who someone actually is".

Also, he assigns different "weights" to each of the former attributes, like this:

First name: 17.5%
Middle name: 17.5%
Family name (father): 17.5%
Family name (mother): 17.5%
Sex: 10%
DOB: 20%

With the matches found on every one of these attributes, he describes a methodology to obtain a composite "concordancy match index" with which comparisons between records can be possible. Also, "partial" matches on the name attributes are possible by using algorithms like Levenshtein's distance.

Good read, IMO. Sorry it's in spanish, but I hope I was able to convey its main ideas.


MySql version and MySql Library version Does the version of MySql Library have the same impact as the version of MySql server when it comes to other software requirements?

Example: I run Magento 1.9.3, which specifies MySQL 5.6 as a minimum requirement.
If my web server installation is running MySql Library of an older version (5.1.73), what problems will this cause, if any?

I can't seem to find any definitive answer out there regarding this detail?

(A pseudo-answer, since it has been several weeks.)

Libraries tend to work across a wide range of server versions.  I cannot address the specifics here, but suspect there would not be an issue.


Uexpected values inserted in table I have a table called FunctionWeights which contains data as follows:

My query is as follows:

select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchCity'
Insert into #Temp2 select RowNumber,PercentMatch,@constVal, PercentMatch * @constVal from dbo.MatchCity(@City)
select * from #Temp2

select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchSSN'
Insert into #Temp2 select RowNumber,PercentMatch, @constVal, PercentMatch * @constVal from dbo.MatchSSN(@SSN)
select * from #Temp2

and so on................ for all function names.

Data-types for columns of #Temp2 are as follows:

Rownumber int not null,
ValFromFunc float(3)null,
FuncWeight float(3) null,
Percentage float(3) not null

However, The #Temp2 table after every insert has only 1 in FunctionWeight column for all the functionNames matched as shown above in queries.

The snapshot of #Temp2 is as follows after insert for any function Name i.e FunctionWeight does not change as it should.

If the FuncWeight field in the #Temp2 table is truly defined as float(3), then the most likely problem is that the datatype of @constVal is DECIMAL(x, 0) or just DECIMAL. Either of those would round up the 0.7 values to be just 1, though the 0.3 and 0.35 values would round down to 0.

Nothing in that posted code or data indicates what could force all values of FunctionWeight to come out as 1. Since some of the FunctionWeight values are 1, I have to assume that the "snapshot of #Temp2" image was taken after testing FunctionName values that have non-1 values for FunctionWeight.

Another possibility is that we are not being shown all rows in the FunctionWeights table and that there is duplication of the FunctionName values where the duplicated rows have a value of 1 for FuncWeight. In that case the last record read by the select @constVal = FunctionWeight from dbo.FunctionWeights query would be the value stored in @constVal, though that would not always be the "duplicated" row since there is no ORDER BY on the query.


galera cluster in simple failover mode Can mariadb-galera cluster be configured without data replication but simply in failover mode? I envision such configuration as one active master server and one backup server, both using the same mounted ScaleIO volume ( already with built-in data redundancy)...Is such scenario possible?
Thank you,

No. Galera doesn't work with shared storage, as far as I know.

The way initial cluster members join is by basically blowing away the MySQL datadir, and rsyncing it over from another peer. That would not work out well with shared storage.

Plus, I don't think MySQL itself is really built for shared storage...


Creating a global temp table in a SQL Agent job I want to create a global temp table in a SQL Server Agent Job.

Here is the code:

CREATE TABLE ##TestEmployee(Id int, EmployeeName nvarchar(50))

When I execute in SSMS, all is well.

I then put the above code on a SQL Agent Job. The job will complete successfully, but when I try doing a select statement in SSMS, I get an Invalid Object error.

SELECT * FROM ##TestEmployee

Are there any gotchas I am missing by creating a global temp table in SQL Server Agent?

The difference between global and local temp tables isn't how long they exist, it's who can see them.

Local temp tables can only be seen by the session (spid) that created them.  Global temp tables can be seen by everyone, but only while the session that created them is active.

If you are running it in a SQL Agent job, as soon as it completes it disappears. That's why you can't access it, because it is no longer there.


Why are views not copied over when using SQL Server Log Shipping? I have 2 Sql Servers (2008) with Log Shipping setup between them. The receiving server is configured to keep the database in Read-Only mode. When creating a view on the primary server, this view is not copied over to the secondary. It was my believe that any DDL statements are also shipped. Can anyone clarify?

All modifications to the source database will be copied over to the target database, assuming that your log shipping process is not broken or blocked.  The target database must be in read-only made (which in your case it is) but users accessing the database will prevent SQL from applying the transaction log.

Log shipping needs to be able to put the target database into single-user mode before applying the logfile.  This is the one serious drawback of log-shipping.  Try disconnecting all users, then wait for log shipping to get caught up.  Your view will appear.


How to get SQL insert and/or update to not lock entire table on MS SQL Server Very much a newbie on DB work, so appreciate your patience with a basic question.  I'm running SQL Server 2014 on my local machine, and I have a small table and a basic client application to test different approaches with.  I'm getting what appears to be a table lock during both INSERT INTO and UPDATE statements.  The client is an ASP.NET application with the following code:

OleDbConnection cn = new OleDbConnection("Provider=SQLNCLI11; server=localhost\\SQLEXPRESS; Database=<my db>; user id=<my uid>; password=<my pwd>");
cn.Open();
OleDbTransaction tn = cn.BeginTransaction();
OleDbCommand cmd = new OleDbCommand("INSERT INTO LAYOUTSv2 (LAYOUTS_name_t, LAYOUTS_enabled_b, LAYOUTS_data_m) VALUES ('name', '-1', 'data')", cn, tn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
int newkey = Decimal.ToInt32((decimal)cmd.ExecuteScalar());
Console.WriteLine("Created index " + newkey);
Thread.Sleep(15000);
tn.Commit();
tn = cn.BeginTransaction();
cmd.CommandText = "UDPATE LAYOUTSv2 SET LAYOUTS_enabled_b='-3' WHERE LAYOUTS_key='" + newkey + "'";
cmd.Transaction = tn;
cmd.ExecuteNonQuery();
Console.WriteLine("updated row");
Thread.Sleep(15000);
tn.Rollback();
cn.Close();

I run this code, then from the management studio I run SELECT * FROM LAYOUTSv2.  During both cases when the client thread is paused (i.e. prior to commit/rollback) the SELECT query hangs until the commit/rollback occurs.

The table has field LAYOUTS_key assigned as the primary key.  In the properties window it shows that it is unique and clustered, with page locks and row locks both allowed.  The lock escalation setting for the table is Disable...I've tried both the other available settings of Table and AUTO with no changes.  I've tried SELECT ... WITH (NOLOCK) and that returns a result immediately, but as is well-cautioned here and other places it's not what I should be doing.  I've tried putting the ROWLOCK hint on both the INSERT and UPDATE statements, but nothing has changed.

The behavior I'm looking for is this:  prior to commit of an INSERT, queries from other threads read all rows except the one that's being INSERTed.  Prior to commit of an UPDATE queries from other threads read the starting version of the row being UPDATEed.  Is there any way I can do this?  If I need to provide other information to clarify my use case please let me know.  Thanks.

Chances are it isn't locking the "whole table".

It is locking a row in the table but your SELECT * FROM LAYOUTSv2 tries to read the whole table so necessarily is blocked by that lock.

For the insert case you can merely specify the READPAST hint to skip past the locked row- however that will not give your desired result for the UPDATE case (it will skip the row again not read the starting version of the row).

If you configure the database for read committed snapshot isolation this will give your desired effect for both cases (at the expense of greater use of tempdb)


Is it possible to run a stored procedure automatically on a new database when it is attached? Replication is causing issues on our daily snapshots.
What we would like to do is remove replication automatically on a database when it is attached to the SQL server.
Is this possible?

This Link may answer your question. An hourly job as pointed out by David Browne would be my choice.


Oracle 12 Grid installaton falsly detecting pre-existing ASM installation I seem to have run into a rather strange problem during my attempt to install Oracle 12 Grid Infrastructure:

System details: Windows2012
Oracle 12c
Grid infrastructure for ASM (stand-alone)

I installed the grid infrastructure, but due to a network problem, it didn't finish successfully. So, I used the deinstall tool to delete it. So far so good. So, now I'm trying to rerun the install.  During the first step, if I select 'Install and configure Oracle Grid Infrastructure for a Standalone Server', I get the following error:

[INS-40404] The installer has detected a configured instance of Oracle Grid infrastructure on the server

My assumption (which may or may not be correct) is that the previous deinstall didn't clean up everything properly.

However, if I select Upgrade Oracle Grid Infrastructure or Oracle Automatic Storage Management' I get the following:

[INS-40406] The Installer has not detected an existing Oracle Grid Infrastructure software on the system.

(Which sort of makes sense... the software was deleted.)

Anyone know how to clean this up? I've done some searching, and did find a reference to a problem under Linux where they recommended removing a file called 'ocr.loc' from /etc; however, since I'm under Windows, I'm not sure if there is anything equivalent. I've also found plenty of references to using the setup program with a "deinstall" parameter but that doesn't work either.

I think I managed to figure out the problem.

There was a registry entry under hkey_local_machine/software/oracle which needed to be removed manually. Once that was done, the install was able to proceed.


How to determine the cost of index creation? I have a table that I want to create an index on to improve the performance of a couple of SELECT queries that use the table.

How can I test whether the index has had any significant detrimental impact on DUI operations on the table?

The table is a log table, so it is written to frequently. Is it a case of running a particular INSERT or UPDATE query against that table before and after creating the desired index?

If you have a non-production environment, you could simulate your insert / update workload by running SQL Query Stress.  Do that before adding the index to get a baseline, and then after to see how much slower it is, and whether that change is acceptable.

In addition to additional overhead for your DUI operations, another consideration is blocking.  You might have different blocking patterns than you're used to, since some SELECT queries will be using the new index, and inserts / updates will have to take locks on that index and the clustered index.

I only point this out to make the point that DUI "overhead" is not the only consideration.  Adding indexes can often help with blocking, since SELECT queries on the narrower NC index won't be blocked by updates to other columns in the clustered index.

Without knowing anything about your table, here's a quick walkthrough of what the SQL Query Stress approach might look like.  I'll set up a "LogTable" in a new database:

USE [master];
GO

CREATE DATABASE [232113];
GO

ALTER DATABASE [232113] SET RECOVERY SIMPLE WITH NO_WAIT;
GO

USE [232113];
GO

CREATE TABLE dbo.LogTable
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Col1 DATETIME NOT NULL,
    Col2 CHAR(4) NOT NULL
);

Then I use these two queries to simulate the INSERT / UPDATE workload:

-- insert a row
INSERT INTO dbo.LogTable
    (Col1, Col2)
VALUES
    (GETDATE(), 'val2');

-- update a random row
UPDATE dbo.LogTable
SET Col2 = 'val3'
WHERE Id = (SELECT TOP (1) Id
            FROM dbo.LogTable
            ORDER BY NEWID());

Then I run SQL Query Stress over 6 threads, 250 iterations per thread, with a 100 ms delay between each execution.  This completes in about 25 seconds with just the clustered index in place.

Now I'll create a nonclustered index on Col2, which is affected by both the inserts and the updates:

CREATE NONCLUSTERED INDEX IX_Col2 ON dbo.LogTable (Col2);

Running the test again, it took basically the same amount of time (25 seconds).  Notice that the logical reads went up, so there is some cost here (it just didn't affect the overall duration).

Of course, this is a very simple case, with no other activity on the "server" (my laptop), so the difference is minimal.  But hopefully it demonstrates the concept.


Schemas permission in SQL DB I am a fresh admin in SQL DB and I need to learn how to establish the security settings in SQL DB. I have encountered an issue that is somehow unclear or even weird for me.
I know that if one user has Fixed Database-Level Roles db_datareader, he or she can perform "SELECT" on all user tables in DB.
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
But in my SQL DB, even the user which has already been added to that role can not perform "SELECT" on any table/view unless the "SELECT" permission is explicitly granted in "Schemas settings" which implies db_datareader role does not work at all.
Could anyone please give me advice on this? Many thanks

these are the standard permission:

for this mapped user settings:

you have in place some kind of deny for your users.


Modelling route data in MySQL I need to create a data model for routing data between points. For example, path between two cities which will include the two start and destination cities, way points (like smaller towns along the way), total distance between the two cities (distances between routing points do not need to be modelled though it would be good if that can be done too).

My current idea is like below.

Create one table containing the cities: city. Columns: city_id (primary key), city_name, etc.
Create a many-to-many table path with 4 columns: from_city (references city.city_id, to_city (references city.city_id), calculated column path = MD5(CONCAT(from_city, to_city)), distance to store the total distance for the route.
Create a table to store route points called town similar to the city table.
Create a man-to-many table route between path and town which will have three columns: town_id (references town.town_id), path_id (references path.path) and route_position which will be a value 1-n which will indicate the position of the route point in the actual path. For example, first route point will have number 1, second 2 and so on.

Questions:

Do you guys think this is a feasible model? Is there a better approach to this?
In the path table, is it better to use a MD5 hash or create a multiple-column index on from_city and to_city?

EDIT:

To give some context, I'm currently using a graph database to store this data and have a user-defined function that uses A* search to find the shortest path between points. Problem is as the graph gets denser, this computation becomes too slow. So, I think a lookup-based approach would be faster.

Do you guys think this is a feasible model?

No.

  Is there a better approach to this?

Yes. Use PostgreSQL, and PostGIS. Instead of storing towns, store your points as WGS84 coordinates. You can use PostGIS to resolve the towns or addresses to WGS84 coordinates. You can connect them self and find the shortest path, or... you can use load the OSM data and have a Google Maps-like product.

See also,

MySQL spatial comparison with PostGIS

MariaDB has a graph backend OQGRAPH that may also assist you, but I wouldn't touch it with a 10 foot pole. Database Administrators has one question on it. Outside I've jokes, I've never seen it mentioned.