Setting isolation level at database level We have a data warehouse that's being dropped and reproduced every night. We have lots of SSRS reports that use the warehouse. Sometimes an SSRS report goes wrong and will never execute, thus preventing our warehouse from being filled up.

I can easily go with dirty reads for that one (data isn't changing anyway) so I figured out we can just go to READ UNCOMMITTED transaction level. And now I've encountered a problem I simply cannot believe:

Is it really not possible to change a database's default isolation level? What would be the reason for that?

It just doesn't make sense. I want my default isolation level to be READ UNCOMMITTED. I really don't want to go to each SSRS report and set it's transaction isolation level separately. We have lots and lots of SSRS reports.

I could enable RCSI but it will only unnecessarily slow the database down. Handling the reports in such a way is pretty much impossible. We have lots of SSRS reports that are written very poorly (in the graphical designer...) and we don't have time to fix them all.

I'm going through the process when in abundance of free time but that doesn't happen often. Having the warehouse not fill up is a very serious problem to us. For now all our problems could be fixed by changing the default isolation level.

The data warehouse is being filled up at night when nobody is using it
The whole day no data gets into the DW and we work with offline data up to -1 day.

So in our model there's really no concurrent writing and reading unless a query gets stuck and somebody forgets about it and leaves it trying to run.

Any solutions?

There are better ways to handle this than dirty reads.  READ UNCOMMITTED queries will still block DDL, like TRUNCATE TABLE, DROP TABLE and ALTER TABLE … SWITCH, so it might not even help.

First, of course, is RCSI which is the basic correct solution for concurrently loading and reading from a database (even a Data Warehouse).  It does not unnecessarily slow down your loads.  It does what is necessary to enable concurrent readers and writers.  And RCSI only creates row versions for UPDATE and DELETE.  INSERT does not generate row versions unless the table has a trigger.

Second, if you want to reload the data warehouse while reports are running READ UNCOMMITTED, they will get wrong results.  So you might as well just KILL the connected sessions, or have your ETL job alter database current set single_user with rollback immediate.

Third, you can use a Database Snapshot to keep the DW available for reads while you are performing ETL.  You can additionally use the snapshot to roll back (restore) a failed ETL process even if it didn't run in a single transaction.


Help with database design I'm quite the beginner when it comes to databases, so please bear that in mind.

I need to store user statistics in several categories for an application.

In the near future I will most likely need to add another application, with different categories, but for the same user.

Both applications will likely change which data is saved over time. perhaps via adding or removing individual fields or even entire categories.

Taking into account performance, maintenance and simplicity of future queries, what's the best approach to database design in this case?

Should I have one table for each application?

Or should I divide everything into smaller tables, maybe even stored in a different database?

You could always do something like this:

Create a users table to store all of your user info:

create table users
(
  id int,
  user_name varchar(50)
);

insert into users values
(1, 'John'),
(2, 'Jill'),
(3, 'Bob'),
(4, 'Sam');

You state that you might need to apply this type of tracking for multiple applications. So you can create an application table to store the app name and any details.

create table applications
(
  id int,
  app_name varchar(50)
);

insert into applications values
(1, 'App 1'),
(2, 'App 2'),
(3, 'App 3');

Next create a categories table to store the info on each of the categories that you want to track by the users.  This table will include a column that will contain the application id. This table will contain a foreign key relationship to the applications table. This allows you to have some flexibility in having the same category in multiple apps.

create table categories
(
  id int,
  cat_name varchar(100),
  a_id int
);

insert into categories values
(1, 'Cat 1', 1),
(2, 'Cat 2', 1),
(3, 'Cat 3', 2),
(4, 'Cat 4', 3);

Finally, create the users_categories table which will contain the stats that you want to track, including user, category and then the action_date (the date the event took place).

create table users_categories
(
  u_id int,
  c_id int,
  action_date datetime
);
insert into users_categories values
(1, 1, current_timestamp()),
(2, 3, '2012-01-15 10:15'),
(3, 4, '2012-08-24 12:48'),
(1, 2, '2012-06-08 08:50');

Then to query the data, you would use something similar to this:

select *
from users u
left join users_categories uc
  on u.id = uc.u_id
left join categories c
  on uc.c_id = c.id
left join applications a
  on c.a_id = a.id

And the results would contain (See SQL Fiddle with Demo):

ID | USER_NAME | U_ID   | C_ID   | ACTION_DATE           | CAT_NAME | A_ID    | APP_NAME
------------------------------------------------------------------------------------------
1  | John      | 1      | 1      | '2012-09-28 06:47:53' | Cat 1    | 1       | App 1
1  | John      | 1      | 2      | '2012-06-08 08:50:00' | Cat 2    | 1       | App 1
2  | Jill      | 2      | 3      | '2012-01-15 10:15:00' | Cat 3    | 2       | App 2
3  | Bob       | 3      | 4      | '2012-08-24 12:48:00' | Cat 4    | 3       | App 3
4  | Sam       | (null) | (null) | (null)                | (null)   | (null)  | (null)

A few other suggestions, you could technically remove the a_id from the categories table and create a join table between categories and applications, similar to this:

create table categories_applications
(
  c_id int,
  a_id int
);
insert into categories_applications values
(1, 1),
(2, 3),
(3, 1),
(1, 2);

This would lead to one additional join in your queries, but the results would be the same:

select *
from users u
left join users_categories uc
  on u.id = uc.u_id
left join categories c
  on uc.c_id = c.id
left join categories_applications ca
  on c.id = ca.c_id
left join applications a
  on ca.a_id = a.id


Is this table designed properly I am trying to model the process of where one co-worker enters a statement of their proposed work procedure and another co-worker commenting and approving the statement.
Currently I have one table as such:

METHODOLOGY
  methodology_id
  entered_by
  statement
  entered_date
  status_code
  reviewed_by
  reviewer_comments
  reviewed_date

status codes are as such:

1 = in progress by originator
2 = finished and locked by originator
3 = in progress by checker
4 = finished, locked and rejected by checker
5 = finished, locked and approved by checker
6 = approved method was unlocked due to revision to methodology
7 = the originator was changed with a new originator after method was approved
8 = the checker was changed with a new checker

Should this be broken out as such:

METHODOLOGY_STATEMENT
  methodology_id
  entered_by
  statement
  entered_date
  status_code

METHODOLOGY_REVIEW
  methodology_review_id
  methodology_id
  status_code
  reviewed_by
  reviewer_comments
  reviewed_date

Right now the requirement is only one Review per Method Statement.

Either way could be correct.

Choose 1 table if you want to make it difficult to change the number of reviews possible.  It may be counter-intuitive to want to make something difficult, but if it would actually be a bad idea to make it 1:n, then building in some pain to make the change has some logic.

But, you almost certainly want to go with two tables for long term flexibility.  If it was me, I'd put a trigger on there to enforce the 'one review per methodology statement' rule.  If that changes, just modify or drop the trigger.

One question to keep in mind, though:  if you go to multiple reviews, how would you resolve status codes that conflict?  For instance, what if you had one code 4 and one code 5 for the same methodology statement?

UPDATE:

...or you can do the much better thing and use a unique constraint as @Max Vernon suggests.


What steps do I need to take to move my SQL instance to a different drive on the same server but with the same drive letter? I need to move my SQL instance to a different drive on the same server but plan to rename the drive to the same drive letter as the original drive the instance was on (after renaming the old drive).

I'm guess I need to stop the SQL service before moving the files, and then start it again.

Are there any other steps needed or precautions I should take?

Note: This instance has databases that are part of synchronous AlwaysOn AGs. Some of the databases are the primary replicas while others are secondary replicas. The primary replica databases are also being moved from the same source and to the same destination drive (as the server instance files).

There are the usual precautions such as ensuring up to the minute backups before you start.

Another option you could consider though is to create Mount Points for the new drive so that you don't need to worry about changing drive letters afterwards - the fewer moving parts the better.


When PostgreSQL query <Value returns =Values I'm running a query using PostgreSQL 9.6.6 and when querying:

SELECT *
FROM table
WHERE
      threshold_value < VALUE

returns all values = VALUE and not < VALUE as it should. In the same way if I query

SELECT *
FROM table
WHERE
      threshold_value = VALUE

Shows no values at all when all threshold_value (values) are equal to VALUE (VALUE is 1.4)

My threshold_value column is of type real. Should this be happening, or is it a known bug? Is this related to conversion errors between the python implementation of PGAdmin and the DB, or am I missing something?


Is it possible to get a list of procedures created with WITH EXECUTE? Is it possible to get a list of stored procedures create with WITH EXECUTE?

I tried looking at sys.procedures, but they all have the principal set to null, and I know there are some procedures created this way.

You need sys.sql_modules:

SELECT p.name, m.execute_as_principal_id
  FROM sys.procedures AS p
  INNER JOIN sys.sql_modules AS m
  ON p.[object_id] = m.[object_id]
  WHERE m.execute_as_principal_id IS NOT NULL
  ORDER BY p.name;

If you need to identify explicit EXECUTE AS CALLER vs. no EXECUTE AS (which are functionally the same), you'll need to parse sys.sql_modules.definition, which is far from foolproof (since EXECUTE AS can appear in many different contexts, including inside comments and string literals).


User cannot select from table when having SELECT permissions if CONTROL is denied I'm setting up my database on an Azure SQL Server instance.
(I know the principal names are terrible, it's just a dry run test)
I created a login ("Test"), a user ("Test"), a schema ("Test"), added a table to the schema ("TestTable", created a role ("TestWriter"), set role permissions for the schema and added the user to the role.
I granted the role the following permissions:
GRANT
    INSERT,
    SELECT,
    UPDATE
ON SCHEMA::Test
    TO TestWriter
GO

and denied all other permissions, following the practice of "least permissions" (i.e. giving a role/user only the minimum permissions it needs):
DENY
    DELETE,
    ALTER,
    CONTROL,
    EXECUTE,
    REFERENCES,
    TAKE OWNERSHIP,
    VIEW DEFINITION
ON SCHEMA::Test
    TO TestWriter
GO

however then there was an error selecting and/or inserting into the table in the schema, the error being
The INSERT permission was denied on the object 'TestTable', database 'TestDb', schema 'Test'.
through trial and error I found out the problem is fixed if I GRANT CONTROL permission to the role.
However, from what I read the CONTROL permission isn't something I'd want to grant willy-nilly to anyone who just needs to SELECT/INSERT into a table. Is there something I am missing? Is it possible to SELECT/INSERT/UPDATE a table WITHOUT having CONTROL permission?

You shouldn't have to explicitly DENY any of those permissions since SQL Server is implicitly deny-first, meaning a Login / User / Security Principal (such as a Role) has no access to anything until you've explicitly granted access (either via scripting it with T-SQL using the GRANT keyword or using the UI that SSMS provides). Explicitly denying a permission takes precedent even over an explicit GRANT.
Think of it this way, there are three states a permission can be in and the order of precedence (by highest precedence first) for permissions in SQL Server work like this: Explicit DENY > Explicit GRANT > Implicit deny-first (default)
By explicitly granting CONTROL, you're not solving the problem or understanding the root is, rather you're just sidestepping it with a workaround by granting a very wide permission set (the explicit GRANT here supercedes the implicit deny-first nature).
Outside of that, I'm unsure of what your root issue actually is, but I'd recommend by first revoking (not granting) all of the explicit DENY permissions you've mapped to the TestWriter role, and start testing what your Test user has access to.


Partitioning table with billion row I have a big table referenced by other tables and need to split the table
on several parts.

I create table by CREATE TABLE product_part0 () INHERITS (product);
Move some rows to product_part0 by INSERT INTO product_part0 SELECT *    FROM ONLY product ORDER BY id LIMIT 200;
Now I need to remove duplicates from master-table. Run DELETE FROM ONLY product WHERE id IN (SELECT id FROM product_part0);

With result:

ERROR: update or delete on table "product" violates foreign key constraint
"product_id_refs" on table "searchcache"
DETAIL: Key (id)=(13375) is still referenced from table "searchcache".

So, how to delete duplicate rows?

I made similar question at postgresql maillist.

Unfortunately, you can't define a foreign key against a partitioned table, only the partitions themselves - but, of course, the referencing table can have only on FK on the column in question...

So you have to either partition the referencing table, too, or live together with foreign-keylessness (with help from a trigger or the application code).

Let me note that similarly, you will have to define the eventual (unique) indexes on the child tables one by one.  This also means that unique constraints have to be defined this way, too.


mysql prevent update other salve-master db while any record update in slave db I have master and 3 slave (slave1,salve2,slave3) db in same host. So when I update any record in slave1 db its affected on all other master and salves db.

My Question is: How to prevent update all other db whenever I update any record in salve1 db, and whenever I update any record in master it will be updated in all db?

in short I want to replicate db in one way master to salve not slave to master..

If we assume you have the following databases:

Master  (DB0);
Slave 1 (DB1);
Slave 2 (DB2);
Slave 3 (DB3);

The only way for a 'slave' (e.g DB1) to update the 'master' (DB0) is if the 'slave1' (DB1) is also acting as a master to the 'master' (DB0). (commonly known as Master to Master Replication)

You can check if this is the case by connecting to your 'master' (DB0) database and running SHOW SLAVE STATUS;

The result 'should' be blank, most likely it isn't.

Look for the fields:
Master_Host and Master_Port - this is the database from which it is receiving data. It will probably relate to one of your 'slaves' (DB1).

Slave_IO_running and Slave_sQL_running - if these are both 'Yes' then replication is up and running, and any changes made to the database above will appear on your 'master'

The quickest way to stop it is to then issue STOP SLAVE; on your 'master' (DB0).

Depending on what version of MySQL you are running there are then some things you can do to erase the settings. https://dev.mysql.com/doc/refman/5.6/en/reset-slave.html (I recomend making a note of the settings in SHOW SLAVE STATUS before issuing any kind of RESET, just in case)

Also you may want to add:
skip-slave-start to your my.cnf file on your master, which will prevent the database starting replication again if the server is restarted.

As @ypercube mentions above, you should avoid writing to a 'slave' database, unless you have specifically configured it as part of a master to master setup.


LEFT OUTER JOIN on multiple conditions Is there a way to link two tables in order?

Table A (Actual):

EX  STATUS  DLV     Actual FEE    Expected Fee
N    ACT    MAIL    1.25
N    ACT    EMAIL   1.25
N    ACT    PICKUP  0
N    NON    MAIL    0
N    NON    EMAIL   1.25
N    NON    PICKUP  0
Y                   0

Table B (Expected):

EX  STATUS  DLV     Expected FEE
N    ACT    MAIL    1.25
N    ACT    EMAIL   0
N    ACT    PICKUP  0
N    NON    MAIL    1.25
N    NON    EMAIL   0
N    NON    PICKUP  0
Y                   0

Table A (Expected Result):

EX  STATUS  DLV     Actual FEE    Expected Fee
N    ACT    MAIL    1.25          1.25
N    ACT    EMAIL   1.25          0
N    ACT    PICKUP  0             0
N    NON    MAIL    0             1.25
N    NON    EMAIL   1.25          0
N    NON    PICKUP  0             0
Y                   0             0

I want to incorporate the follow logic within the join:
A.EX = B.EX (join on first)

but then on the combination of the other 3, since they are unique.

I tried this but of course it didn't work:

FROM
dbo.claims a
left outer join dbo.pricing p on a.EX = p.EX
and a.STATUS = p.STATUS
and a.DLV = p.DLV

I was hoping to link table B to table A to get the expected fee.
If Ex = Y, then it's 0 regardless of status or DLV.  If Ex=N, then I have to depend on the other two columns.

I'm sorry if this is confusing.

create table tableA (EX char(1), STATUS char(3), DLV varchar(10), Actual_FEE decimal(18,2));
insert into tableA values
('N', 'ACT', 'MAIL',   1.25),
('N', 'ACT', 'EMAIL',  1.25),
('N', 'ACT', 'PICKUP', 0),
('N', 'NON', 'MAIL',   0),
('N', 'NON', 'EMAIL',  1.25),
('N', 'NON', 'PICKUP', 0),
('Y', null,  null,     0);
GO

7 rows affected

create table tableB (EX char(1), STATUS char(3), DLV varchar(10), Expected_FEE decimal(18,2));
insert into tableB values
('N', 'ACT', 'MAIL',   1.25),
('N', 'ACT', 'EMAIL',  0),
('N', 'ACT', 'PICKUP', 0),
('N', 'NON', 'MAIL',   1.25),
('N', 'NON', 'EMAIL',  0),
('N', 'NON', 'PICKUP', 0),
('Y', null,  null,     0);
GO

7 rows affected

According to your expected result it could be:

select    a.EX, a.STATUS, a.DLV, a.Actual_FEE, b.Expected_FEE
from      tableA a
left join (select distinct EX, STATUS, DLV, Expected_Fee
           from   tableB) b
on        a.EX = b.EX
and       (a.STATUS = b.STATUS OR b.STATUS IS NULL)
and       (a.DLV = b.DLV or b.DLV IS NULL)
GO

EX | STATUS | DLV    | Actual_FEE | Expected_FEE
:- | :----- | :----- | :--------- | :-----------
N  | ACT    | MAIL   | 1.25       | 1.25
N  | ACT    | EMAIL  | 1.25       | 0.00
N  | ACT    | PICKUP | 0.00       | 0.00
N  | NON    | MAIL   | 0.00       | 1.25
N  | NON    | EMAIL  | 1.25       | 0.00
N  | NON    | PICKUP | 0.00       | 0.00
Y  | null   | null   | 0.00       | 0.00

dbfiddle here


Oracle temporary table usage with XA I'm confused about the compatibility of Oracle XA with global temporary tables. I've read several places on the internet and in the oracle documentation that say "Distributed transactions are not supported for temporary tables." but it doesn't seem 100% clear whether "not supported" is referring to the creation of a temp table or its usage.

I understand that issuing any DDL will implicitly commit the current transaction with Oracle, and that creating a temporary table is DDL so that can't be done inside an XA transaction. However, does this mean that I can't even insert into an already created temporary table in an XA transaction?

I performed a test inserting and querying a temp table inside an XA (distributed) transaction and it worked fine but I'm afraid to build an application around the results of that one test when the documentation says "not supported"...

For me the wording "Distributed transactions are not supported for temporary tables" is clear: In a distributed transaction you should not use statements that involve temporary tables. This means you should not even insert into an already created temporary table in an XA transaction. Of course it can happen that it works but nevertheless it is not supported so you should not use it.

I found Note 1051543.1 "Known issues with Global Temporary Tables and Distributed or XA transactions"  on Oracle Metalink stating

the usage of temporary tables is not supported in distributed transactions
there are a lot of ORA-00600 errors known when using global temporary tables in XA transactions. Even block corruption ca happen
there are some types of XA transaction where the usage of temporary tables will work. One of such situations is described in the article:

  A global temporary table can be safely used if there is only single branch transaction at the database using it, but if there are loopback database links or XA transactions involving multiple branches, then problems can occur including block corruption (...)
  this restriction on temporary table is documented in the 10g manual . It is not documented in the 11g manual but this is considered as a bug.

  This restriction on temporary table is contained in the 12c manual (you supplied the link).


can I get SQL Server to choose between a table and a view? Our reports need to be live and quick - don't they all?  The overwhelming majority of tables in our application database just have INSERTs - and the tables have incrementing primary keys.  Our tables are quite large but our daily INSERTs are relatively few.  ie a table may have over 35,000 rows but only 400 INSERTs a day.

The data structures off which our reports are written are views into the live application database (technically a replicated copy).

What would be swell is to, effectively, make copies of these views every night and save those copies in the reporting database.  Then have SQL Server magically determine if the table and/or the view is required to handle the reporting query.  So if the query is looking at last year, it uses the table - if it's the last hour, it uses today.

It is fairly easy for me to write a view that unions the table and the "today data" that is not in the table, thanks to the incrementing PKs.  But the query plan shows the view is always run, even when it's not required.

I hope that's enough detail to highlight what I am trying to accomplish.  Is this possible in SQL Server?  Any other creative ideas to handle this?

Thanks!!

Not suggesting a better way to run your queries, but hopefully answering the question. You're asking if SQL SErver can ignore the view when you're running queries, but presumably, you're writing the query against the view. In which case, no, SQL Server can't ignore the view.

However, there is a process within the optimizer called simplification. Given enough time (more on that in a moment), SQL Server can recognize which parts of a view you're using or not using and then eliminate tables from the execution plan that are not needed to satisfy the query. But, it has to have enough time to do that. Since you're working with nested views, a major coding issue, you're not generally going to have enough time for the optimizer to get a good execution plan, let alone perform simplification and eliminate unnecessary queries.

Short answer, no. There's not short cut open to you. You need to rearchitect the queries to eliminate nested views. Any other tuning you do will be, at best, perephiral to the problems you're experiencing.


Using Change Data Capture with a frequently changing schema We are following an agile development process, which in this case means that we frequently make changes to the database schema, e.g. adding new columns, moving data to other columns, etc.

Is there a mechanism to update the CDC table to the new schema, and are there any best practices to how you deal with captured data when migrating the database schema?

The mechanism for schema changes is that you disable CDC for the table, and then re-enable it. This of course means you lose all the history, so you'll also want to build in a mechanism to offload that somewhere if you want history from before the most recent schema change.

Maybe a better answer is, once you've tested CDC initially, keep it disabled until you're comfortable that the schema is more stable.


Does a single replica gives any performance improvement? Consider a case where you have a single PostgreSQL database. Then you add a replica (hot standby) and you send all the reads to that replica. This is what does Rails for example.
Does this gives any performance benefit? For my understanding the answer is no.
Let me explain:

when you have only a single database it gets N writes and M reads
when you add the replica, the replica has to perform N writes (replicate them) and M reads... so there isn't any advantage.

Basically adding replicas does not add any benefit for writes (i.e. 2 x number of machines => 2 x number of writes).
The only advantage would be to split the reads: but in that case you need to split the reads between the master and the replica (or between multiple replicas), otherwise it is useless. So the strategy adopted by Rails (i.e. moving all reads to the single replica) seems not effective to me.
Am I missing something? Is the write work performed by a replica less then the work of the master (considering that each update must be applied to both the machines)?

Am I missing something? Is the write work performed by a replica less then the work of the master (considering that each update must be applied to both the machines)?

Probably yes, you are missing something, and write work done on the replica is less than the (total) work of the master.  It is rare for every write on a database to be of the form update foo set bar=592 where pk=127.  What if it were instead update foo set bar=592 where bar is distinct from 592 and pk in (select foo_id from baz where t ilike '%one fish%' and sin(foo.z)<sqrt(baz.y)/7) and tzr @> :something.  How many reads (and CPU time, and memory) would need to happen to figure out which writes end up happening?  Replication, however, just needs to replay the end result.
But if you were setting up read replication just for performance, it would be pretty weird to set up just one of them.  Unless that one was on your LAN, while the master was someplace with more latency.


Permissions on Trigger Problem I am working with a database in MS-SQL 2008 R2, and I'm running into a problem.

The basic setup is this:  There are two tables of interest: dbo.Assesments and audit.Assesments.  dbo.Assesments is the working data table, and has triggers for data changes (insert, update, delete) to update audit.Assesments with a log of changes.  As far as I can tell, this has worked for years, so far so good.

Our L2 support has a Role called SupportRole that they get into with WinAuth, and that role has some select rights and execute rights for stored procedures.  One of those stored procedures does an update to dbo.Assesments.  This is where the problem comes in:  The update goes in, the stored procedure fires to insert a audit row to audit.Assesments, and then they promptly get a "Insert denied, insufficient rights to Insert", the transaction roles back, etc.

This is mystifying to me: I had thought triggers were more like stored procedures, where if they are made they have the rights to do whatever they are doing, but this has me thinking twice.  I don't want to give insert rights to the support role for the audit table/schema.  What should I do?

Triggers run, by default, under the security context of the principal who caused the trigger to fire.

In order to change this behavior, you'll need to create the trigger using the WITH EXECUTE AS OWNER clause.

Below is an example which shows how that works.  WITH EXECUTE AS OWNER allows the trigger to run in the security context of the database owner, instead of the principal who is updating the table.

First, we create a test table:

USE tempdb;
IF OBJECT_ID('dbo.t') IS NOT NULL
BEGIN
    DROP TRIGGER t_trig;
    DROP TABLE dbo.t;
END
GO
CREATE TABLE dbo.t
(
    ID INT NOT NULL
    , ID2 INT NULL
);
GO

Here's the trigger code, with EXECUTE AS OWNER:

CREATE TRIGGER t_trig ON dbo.t
WITH EXECUTE AS OWNER
AFTER INSERT
AS
BEGIN
    UPDATE dbo.t
    SET ID2 = ID
    WHERE EXISTS (SELECT 1 FROM inserted i WHERE i.ID = dbo.t.ID);
END
GO

Now, we'll create a test login with low privileges that can be used to test the hypothesis that EXECUTE AS OWNER allows an under-privileged principal access to functionality they otherwise would not have:

CREATE LOGIN tLogin WITH PASSWORD = 'QWERFsdf23454%';
CREATE USER tLogin FROM LOGIN tLogin WITH DEFAULT_SCHEMA = dbo;

We'll give them the ability to insert rows into dbo.T, but prevent them from running UPDATE statements:

GRANT INSERT ON dbo.t TO tLogin;
DENY UPDATE ON dbo.t TO tLogin;

Here we do the test:

EXECUTE AS USER = 'tLogin';
/*
    Output here shows we're running under the tLogin
    security context
*/
SELECT SUSER_SNAME();
/*
    This will fail, with insufficient privileges
    since we've DENY'd the UPDATE privilege to tLogin.
*/
UPDATE dbo.t SET ID2 = ID;
/*
    this will run the UPDATE since the trigger
    has EXECUTE AS OWNER
*/
INSERT INTO dbo.t(ID) VALUES (1);
/*
    This takes us out of the tLogin security context
*/
REVERT

Here we can see the row in dbo.T has the changes made by the trigger:

SELECT *
FROM dbo.t;

And here, we cleanup the low-privilege user:

DROP USER tLogin;
DROP LOGIN tLogin;

The output from running the above is:


Refreshing a table using CREATE TABLE, DROP and Rename A read-only database (database1) is updated via log shipping every minute,
then a procedure queries multiple tables in it and refreshes a large table on database2.

For this process I

run an "expensive" query against database1 and generate a new Table in database2 (Table_A_TEMP)

BEGIN TRAN
DROP TABLE Table_A
RENAME Table_A_TEMP to Table_A
COMMIT

Does it look too ugly? Otherwise I would have to create a very complex verifications and updates.

Suggestions to minimize locks?

I have to update every minute. One person suggested me to rename the original table as well, I will try, it will probably be quicker, then I drop it after the commit.


postgresql multi column unique or check constraint First a word of pleading.  To say I am a newbie to postgresql would be an insult to the newbies!  I have been working on this for only a day or so.

I have found several questions on this site, and found references in the postgres 9.1 documentation which allude to this issue, but I am new enough to this that my understanding of the various complex examples is insufficient for me to grasp the concept.  At least I think that is the problem...

I have the following table:

CREATE TABLE cc
(
 idx integer NOT NULL DEFAULT nextval('cc_idex_seq'::regclass),
 card_num character(16), -- credit card number
 sec character(4), -- credit card security number from reverse
 card_holder_fname character(20), -- first name of card holder
 card_holder_lname character(20), -- last name of card holder
 card_holder_mi character(1), -- middle initial (if given)
 card_type character(1), -- Visa, Master Card, or Discover
 CONSTRAINT chk_cc_card_type CHECK
  (card_type = ANY (ARRAY['V'::bpchar,'M'::bpchar, 'D'::bpchar]))
)

In an effort to help us combat fraud, we want to ensure that the combination of data for a given table row is the only way that the card in question is ever entered.  Since a given card can wind up in the table more than once, but should never do so with different card holder data, multiple entries need to be allowed IF THEY ARE EXACTLY THE SAME!  But if a card number appears more than once, with different holder data, it should raise an error.

(Note:  This isn't the full table, but it is enough to demonstrate the issue with, I hope...)

I am as yet unable to find a way to do this with sql, and am new enough to postgresql that the functions and triggers elude me.

Any help would be wonderful.

Edit:  Per ypercube's suggestion I am ading what I should have said in the beginning.

1) The information is being entered from an employee operated form (web based script of some kind, I will look into this further)

2) The table / constraint should refuse to allow the insert if the conditions are detected.  Since a new entry would be the entire transaction involving suspect information the entire transaction should be refused.  Obviously the script engine adding the data will have to be modified as well, but I should be able to handle that part.  Writing the data to another table as suspect would be allowable since no actual financial transaction would have been allowed.

3) This data is stored in no other table (this was my initial idea to place it in another table and use a foreign key lookup on the card number + data and reject it if not the same...I am pushing the boss to do this but he has to confer with some one about legality...apparently there is an issue of having it in more than one location)

4) And just for the record, the boss knows he needs a pro to do this, I am simply a stop gap!  (Hopefully a short stop for a small gap!)

ALTER TABLE  table_name ADD CONSTRAINT  constraint_name CHECK (
   column_price > 0
   AND column_discount >= 0
   AND column_price > discount
);


Doing a left join and having every match include an extra null row So I want to do a left join and have every row on the left table include a row of nulls on the right, even when there is a match.

Is there a name for this type of join?

And is there a better way to do it than how I do it below (namely something avoiding a union)?

SELECT
    T1.C1,
    ...
    T1.CN
    T2.C1,
    ...
    T2.CM
FROM
    Table1 T1
    INNER JOIN Table2 T2
        ON T1.Key1 = T2.Key1

UNION

SELECT
    T1.C1,
    ...
    T1.CN,
    NULL, --NULL 1
    ...
    NULL, --NULL M
FROM
    Table1 T1

(I could also start with a left join and then union a select like the second select above filtered to only the tables that have a matching ID, but that is still complex way to do it).

I was wondering if there is something like

SELECT
    T1.C1,
    ...
    T1.CN
    T2.C1,
    ...
    T2.CM
FROM
    Table1 T1
    INNER JOIN Table2 T2 INCLUDE_NULL_ROW_WITH_MATCH
        ON T1.Key1 = T2.Key1

This is specifically for SQL Server 2008 if that is relevant.

I see 3 ways to do this but all involve a UNION ALL:

your version`:

SELECT
    T1.C1, ....., T1.CN,
    T2.C1, ..., T2.CM
FROM
    Table1 T1 JOIN Table2 T2
        ON T1.Key1 = T2.Key1

UNION ALL

SELECT
    T1.C1, ..., T1.CN,
    NULL, ... NULL
FROM
    Table1 T1 ;

slightly changing the second part:

SELECT
    T1.C1, ....., T1.CN,
    T2.C1, ..., T2.CM
FROM
    Table1 T1 JOIN Table2 T2
        ON T1.Key1 = T2.Key1

UNION ALL

SELECT
    T1.C1, ....., T1.CN,
    T2.C1, ..., T2.CM
FROM
    Table1 T1 LEFT JOIN Table2 T2
        ON 0 = 1 ;                   -- FALSE

first a UNION, then join:

SELECT
    T1.C1, ....., T1.CN,
    T2.C1, ..., T2.CM
FROM
    Table1 T1 JOIN
      ( SELECT * FROM T2
        UNION ALL
        SELECT NULL, ..., NULL
       ) AS T2
        ON T1.Key1 = T2.Key1
        OR T2.Key1 IS NULL ;

I don't think there will be much difference in execution plans and efficiency but the first one seems more simple.


Transaction log is full changing column type in a large table I've got a table of more than 400 million rows and want to convert the datatype of one of its columns, specifically datetime to datetime2(2).

If I execute my statement, I get the following error:

  The transaction log for database 'xxxx' is full due to 'ACTIVE_TRANSACTION'

So is here any possibility to update this table?

(My log file can have a maximum size of 150GB, I don't have any more free space available.)

Scott's answer made me realise you might have enough space for this:
Create a datetime2 column with a temporary name, and transfer the original column contents to it in batches (to prevent your log running out of space -- and I'm assuming your database is in Simple recovery model).
Then drop the original column, and rename the new column to the old column name.


Suppress bogus warning for query using triggers As far as I understand based on my research, a very simple update query like:

UPDATE someTable SET status = 10 WHERE id=123;

will fill the error log with bogus warnings if the query invokes triggers, and statement based binary logging is turned on.

At the end of this bug report https://bugs.mysql.com/bug.php?id=45677 it says:

  For 5.1, We mark all the statements that invoke a trigger or call a
  function that updated autoinc fields as unsafe and will switch to
  row-format in mixed mode. But in statement mode, we didn't emitted the
  warning. (sic)

  For 5.5, we emitted the warning in statement mode, because  a suppress
  warning feature is introduced.

In my understanding this means that whenever I update a row with an auto-increment column, and triggers are invoked, the queries the trigger runs will be marked as unsafe for replication in statement mode without further consideration. Is this correct? Why is it like that?

If the above is correct, how can I turn the warnings off for these specific queries only?

Please note, that switching to row based replication is not an option for me, because in my three tier replication model I have a blackhole engine in the middle. This kind of setup, I believe, cannot function with row based replication, and is a matter of another question of mine here: Row-row based logging in a blackhole replication filter setup?

I'm using Mysql 5.7

Just switch binary logging from statement based to mixed.

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

Source:
What is the safest way to switch the binlog format at runtime?


Сardinality estimation of partially covering range predicates At the moment I'm trying to figure out how SQL Server evaluates the cardinality of range predicates that partially cover the histogram step.

On the Internet, at cardinality-estimation-for-and-for-intra-step-statistics-value I came across a similar question and Paul White gave a rather interesting answer to it.

According to Paul's answer, the formulas for estimating cardinality for the predicates >= and > (in this case, I am only interested in the Cardinality estimator model of at least 120) are as follows:

For >:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * (F * (DISTINCT_RANGE_ROWS - 1)))

For >=:

Cardinality = EQ_ROWS + (AVG_RANGE_ROWS * ((F * (DISTINCT_RANGE_ROWS - 1)) + 1))

I tested the application of these formulas on the [Production].[TransactionHistory] table of the AdventureWorks2014 database based on the range predicate using the TransactionDate column and datetime range between '20140614' and '20140618'.

The statistics for the histogram step of this range are as follows:

According to the formula, I calculated the cardinality for the following query:

SELECT COUNT(1)
FROM [AdventureWorks2014].[Production].[TransactionHistory]
WHERE [TransactionDate] BETWEEN '20140615 00:00:00.000' AND '20140616 00:00:00.000'

The calculation was performed using the following code:

  DECLARE @predStart DATETIME =  '20140615 00:00:00.000'
  DECLARE @predEnd DATETIME = '20140616 00:00:00.000'

  DECLARE @stepStart DATETIME = '20140614 00:00:00.000'
  DECLARE @stepEnd DATETIME = '20140618 00:00:00.000'

  DECLARE @predRange FLOAT = DATEDIFF(ms, @predStart, @predEnd)
  DECLARE @stepRange FLOAT = DATEDIFF(ms, @stepStart, @stepEnd)

  DECLARE @F FLOAT = @predRange / @stepRange;

  DECLARE @avg_range_rows FLOAT = 100.3333
  DECLARE @distinct_range_rows INT = 3
  DECLARE @EQ_ROWS INT = 0

  SELECT @F AS 'F'

  --for new cardinality estimator

  SELECT @EQ_ROWS + @avg_range_rows * (@F * (@distinct_range_rows - 1) + 1) AS [new_card]

After calculating, I got the following results:

According to the formula, it turned out 150.5, but the optimizer estimates the predicate at 225.75 rows, and if you change the upper border of the predicate to ‘20140617’, the optimizer will already evaluate 250.833 rows, while using the formula we get only 200.6666 rows.

Please tell me, how does Cardinality Estimator evaluate in this case, maybe I made a mistake somewhere in my understanding of the quoted formulas?

SQL Server uses different calculations in different situations. Your example is different from the linked Q & A because your range is entirely contained within a step; it does not cross a step boundary. It is also an interval with two ends rather than one. Writing BETWEEN is the same as writing two separate predicates with >= and <=.

Interval with two boundaries, within a single step

The formula is modified to perform linear interpolation within the step for the number of distinct values expected, and reflect that two range endpoints are now specified (and assumed to exist within the histogram step) rather than one.

Using the histogram steps given in the question:

For the query with BETWEEN '20140615' AND '20140616', the calculation is:

DECLARE
    @Q1 float = CONVERT(float, CONVERT(datetime, '2014-06-15')),
    @Q2 float = CONVERT(float, CONVERT(datetime, '2014-06-16')),
    @K1 float = CONVERT(float, CONVERT(datetime, '2014-06-14')),
    @K2 float = CONVERT(float, CONVERT(datetime, '2014-06-18')),
    @RANGE_ROWS float = 301,
    @DISTINCT_RANGE_ROWS float = 3;

DECLARE
    @S1 float = (@Q1 - @K1) / (@K2 - @K1),
    @S2 float = (@Q2 - @K1) / (@K2 - @K1);

DECLARE
    @F float = @S2 - @S1;

DECLARE
    @AVG_RANGE_ROWS float = @RANGE_ROWS / @DISTINCT_RANGE_ROWS;

SELECT
    @AVG_RANGE_ROWS * ((@F * (@DISTINCT_RANGE_ROWS - 2)) + 2);

...giving 225.75. Changing @Q2 from '20140616' to '20140617' gives a result of 250.833.

Both results match those given in the question.


Informix: is it necessary to update statistics on views? In Informix I have a partitioned table for performance reasons.
One contains the latest data and another the historical data. Also, there is a view which contains data from both.

When I update statistics on the partitioned tables, do I also have to update statistics on the view?

There's no need to update statistics on a view in an Informix database.  Indeed, AFAIK, there's no way to do so; statistics are not stored for views.


Indexing of Data Warehouse tables? Maybe I have misunderstood something but I can recall reading an article which said that there is no reason to create non-clustered indexes on very large data warehousing tables? That is because indexes itself will get so large. One should only consider partitioning these tables. I this right? I don't have real life experience about these cases.

As long as the indices are smaller than the original table, or sorted differently, they can offer a performance boost, though in practice if you have low cardinality they may not.

The main cost of indices is increased time required for inserts and updates.  If your database is updated daily but read from frequently, even large indices may be worthwhile.  It depends on your data.  Remember: all generalizations are false.

Can you give a more specific example of where you're considering applying an index?  If you've already created one, does it show up in execution plans?


GROUP BY rewrite to subqueries with easily interchangeable AND OR conditions Tables:

content        tag_map       extrainfo
|id|title|    |id|tagid|    |id|lat|lng|
|--|-----|    |--|-----|    |--|---|---|
|1 |Lorem|    |1 |8    |    |3 |0,4|0,1|
|2 |Ipsum|    |2 |9    |    |2 |1,1|1,2|
|3 |Dolor|    |1 |9    |
              |1 |7    |

id is unique index

SELECT * FROM content, tag_map, extrainfo
WHERE
tag_map.id = content.id
AND
extrainfo.id = content.id

#AND OR conditions here
#conditions can be very different but I would like
#to use the same query structure
#just change conditions here as necessary

GROUP BY content.id

this query works - but slow.
I found the most alternatives involve subqueries, but I can`t figure out a way to use subqueries and still be able to freely change AND OR conditions that involve columns from ALL tables.

Other non-subquery methods are welcome too.

First clean the join syntax:

SELECT * FROM content as c
inner join tag_map as t on c.id=t.id
inner join extrainfo as e on c.id=e.id
WHERE
#AND OR conditions here
#conditions can be very different but I would like
#to use the same query structure
#just change conditions here as necessary
GROUP BY c.id

Now substitute your and/or clauses in place of the rem'd out lines above. Then look into indexing those columns (indexes should cover all the fields in the conditions).

These simple steps will speed up significantly. If you need further speed ups, please post before and after speedup results along with complete details of existing indexes.


Can I time out my scripted "FLUSH TABLES WITH READ LOCK;"? I have a single Enterprise Linux 6 system running MySQL 5.5. I am attempting to use LVM to capture a weekly filesystem snapshot.

However, as many of us have experienced, "FLUSH TABLES WITH READ LOCK;" can lead to a deadlock situation, create a backlog of queries, fill up the connection pool and effectively knock the system offline. In fact last night my "FLUSH TABLES WITH READ LOCK;" didn't complete until 8 hours after execution due to a long running query.

If I perform the MySQL snapshot using a script like this, can I somehow time out the "FLUSH TABLES WITH READ LOCK;". If it doesn't execute after a period of time I would like to kill it and avoid the inevitable deadlock.

mysql -u root <<-MYSQL_INPUT
FLUSH TABLES WITH READ LOCK;
SYSTEM /usr/local/bin/mysql-create-lvm-snapshot;
UNLOCK TABLES;
\q
MYSQL_INPUT

There is no reliable way of interrupting FLUSH TABLES WITH READ LOCK. I disagree with the previous answer. You can try KILLing the FLUSH query all you want. Typically this will just hang till the original command completes.

However, some good news for you. You should be aware that FLUSH TABLES WITH READ LOCK is not strictly necessary in order to take LVM snapshot.

It is required if:

You are interested in point in time recovery (applying incremental restore via binlogs)
You have lots of non-transactional (ie MyISAM) tables being written to
You are running the snapshot against the master and want to create a slave based on the snapshot.

If your database is InnoDB oriented, and nothing interesting is hapenning with your MyISAM system tables (ie no one creating a procedure, GRANTing privileges etc.) then it's enough that you FLUSH only those tables.

All things InnoDB, you can just take the snapshot. There is no benefit in FLUSHing. InnoDB will make proper recovery anyhow.

The need for FLUSH TABLE comes from the requirement of getting the binary log file and position when you want to be able to rebuild a slave or otherwise use binary logs.


How can I import multiple .xlsx and .xls files into multiple tables in Access? I have 100 .xslx and .xls files with different formats and I need to import them all separately into Access keeping their original file names intact.

Folder example:
C:\Users\username\Desktop\2019_data\foldername\

sample names  of the many different sheets in the folder=

LGPO221154-1-ANDERSON-CELL 18-49.xlsx
LGPO221154-1-ANDERSON-CELL 50_UP.xlsx
LGPO222975_1_CELL_49_UNDER_Bandera.XLS
LGPO222975_1_LL_49_UNDER_Comal.XLS

I haven't done MSSQL in a while but if I remember you can do a ssis package.  I think its part of sql management studio. Where you can read in files and do inserts.


Get table sizes without SELECT permission I want a scheduler to run a simple SQL script to collect the sizes of tables in our MySQL database:

SELECT table_name, data_length, index_length
FROM information_schema.TABLES

The user the scheduler logs in as has very limited privileges:

GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'our-user'@'%'
IDENTIFIED BY PASSWORD '...'

This is intentional - we don't want the user to access any data; only metadata such as table sizes.

From the MySQL manual I thought those permissions should suffice. In particular, that's what it has to say about information_schema.* tables:

  Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges.

but then adds

  These restrictions do not apply for InnoDB tables; you can see them with only the PROCESS privilege.

Our tables are InnoDB tables.

Still, when running the script under our-user, I only get the system tables (TABLES, VIEWS, INNODB_*) listed. The same script does show our user tables when run by a different user with SELECT permission for our tables.

From the manual it also follows that VIEWS can be run with the privileges of creator, so I tried wrapping my query in a view:

CREATE DEFINER = CURRENT_USER SQL SECURITY DEFINER
VIEW view_table_sizes AS <the query above>

However, when this view is SELECTed from by our-user, only the system tables are returned. Needless to say, the view was created by a privileged user.

Create an EVENT that fires every 15 minutes. Here is a tutorial.

The EVENT could be used to populate a table with the necessary information.

Then GRANT SELECT on that table and only that table to your user.


When to open your MySQL cursor A relatively simple question but from all my research I have been unable to find a decent answer.

Which is technically more correct, open your cursor after your handlers have been defined or before you actually use your cursor?

For example,

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

The above statements seem to be standard in most examples however would it be possible to do

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SELECT x,y,z INTO @X, @Y, @Z FROM TableA WHERE X=1;

  IF (@X >= @Y) THEN

      OPEN cur1;
      OPEN cur2;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        FETCH cur2 INTO c;
        IF done THEN
          LEAVE read_loop;
        END IF;
        IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
        ELSE
          INSERT INTO test.t3 VALUES (a,c);
        END IF;
      END LOOP;

      CLOSE cur1;
      CLOSE cur2;
  ELSE
      OPEN cur1;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        IF done THEN
          LEAVE read_loop;
        END IF;
          INSERT INTO test.t3 VALUES (a,b);
      END LOOP;

      CLOSE cur1;
  END IF;
END;

I can see what your second code is doing. You want to open 2 cursors only if certain conditions are met, but only 1 cursor if not met. IMHO that should be fine.

In fact, the book MySQL Stored Procedure Programming has this sample code on pages 110-111 under Example 5-17

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_customer=1;
SET l_last_customer=0;
OPEN customer_csr;
cust_loop:LOOP
    FETCH customer_crs INTO l_customer_id;
    IF l_last_customer=1 THEN LEAVE cust_loop; END IF;
    SET l_customer_count=l_customer_count+1;
    sales_block: BEGIN
        DECLARE l_last_sale INT DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_sale=1;
        OPEN sales_csr;
        sales_loop:LOOP
            FETCH sales_csr;
            IF l_last_sale=1 THEN LEAVE sales_look; END IF;
            CALL check_sale(l_sales_id);
            SET l_sales_count=l_sales_count+1
        END LOOP sales_loop;
    END sales_block;
END LOOP cust_loop;
SET l_last_customer=0;
CLOSE customer_csr;

Notice in the code the opening and closing of a cursor inside a BEGIN...END block. You should be OK with your code. If mysql complains, place your THEN code and ELSE code within BEGIN...END blocks.


Database with multiple .mdf files I have searched online but not found what I am looking for.

The problem/query is to my knowledge a database in SQL Server should have One .mdf file and maybe some .ndf files and One .log file.

I have seen many database with one mdf and multiple ndf files. But recently I came across some database on a server where every database had multiple mdf files.

I took a backup of some database moved them to another server and restored the Primary file as .mdf and all the other files as .ndf obviously except the log file it was restored as .log.

Now my questions are:

Is there any wisdom in having multiple .mdf files for a database?
Is there any drawbacks of having multiple .mdf files?

It was SQL Server 2008 R2 64bit Enterprise Edition

Any advice or pointer in the right direction is much appreciated. Thank you.

But recently I came across some database on a server where every database had multiple mdf files.

That is because of incorrect naming convention. Microsoft says that every database has one primary data file but it does not means it can have only one "mdf data file", database can have many  data file with .mdf extension but only one will be primary data file. It is better to give mdf extension to primary data file and ndf to secondary data file to have proper demarcation but this is no hard and fast rule you can also give .abc extension to primary data file so what you witnessed is normal. As a fact you can give any extension you like.

  Is there any wisdom in having multiple .mdf files for a database?

If you mean:

  Is there any wisdom in having multiple primary files for a database?

The answer is no, a database can have only one primary data file.

But if you mean:

  Is there any wisdom in having multiple data files (.mdf, .ndf, or differently named) for a database?

It depends, you can and cannot have advantage with multiple data files. If they are spread across different physical drives (I am talking about spindles) you would see some advantage with write intensive application. If they are all on same logical partitions it won't have any advantage because underlying they would be using common resource. Using files and filegroups improves database performance, because it lets a database be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that is made up of three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can access the data in parallel at the same time.

As per this MSDN BOL article

Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.

Other advantage which I know is consider a 1 TB database if you would have single data file for it and you want to restore this database on some other server its highly unlikely you would have 1 TB of free space. Now if the same database is spread on different files each having size 250 G it becomes easier in restoration. This might not be actually your scenario but it does helps a lot its little easy to find a server with four 250 G drives than one 1 TB drive

I would say instead of many data files its better to have different file groups but again there are not many environments. Databases made up of multiple filegroups can be restored in stages by a process known as piecemeal restore. Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple mode. Files or filegroups in a database can be backed up and restored individually. This allows you to restore only the damaged files without having to restore the rest of the database. Files in a filegroup backup can be restored individually or as a group


Problem with pdb Oracle 12c I use pre-built Oracle 12c r2

when I tried to sign up as sqlplus / as sysdba

I got a ORA-01017 which I solved with unset TWO_TASK

Since then, I can not sign in to the user I created
Because I'm in the root container of the CDB

How to connect on PDB?

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
NAME
--------------------------------------------------------------------------------
    CON_ID   DBID    CON_UID GUID
---------- ---------- ---------- --------------------------------
CDB$ROOT
     1  776972821          1 4700A987085A3DFAE05387E5E50A8C7B

PDB$SEED
     2 1737080764 1737080764 51C985B6B6D35149E0530100007FC08B

ORCL
     3 4079644691 4079644691 51C99766D7E2568DE0530100007F4FAE

In the root container you can switch the current session to a specific PDB with

ALTER SESSION SET container=orcl;

If you want to actually connect to a specific container (because the user only exists in that container or you have an application which should not know about containers) then you have to connect to the actual net service for the specific PDB. You can use lsnrctl service or check the default tnsnames.ora. There is a good article about this.

This requires some preparation, so check out the Oracle documentation for the concepts as well, but in essence you would have to use a TNS alias or connection description specifying SERVICE_NAME=PDB1 (in your case probably orcl, although that’s a uncommon name for a PDB) instead of a SID or the CDB net service name.


MySQL 5.7: REGEXP version of PostgreSQL REGEXP_REPLACE I have a CASE STATEMENT in PostgreSQL which I need to convert into a MySQL statement.

       CASE
        WHEN mobilenumber ~'^09[0-9]'
        THEN regexp_replace(mobilenumber, '0', '+63')
        WHEN mobilenumber ~'^9[0-9]' AND LENGTH(mobilenumber) = 10
        THEN '+63' || mobilenumber
        ELSE mobilenumber
      END

I am using MySQL 5.7. When I run the statement below, I am getting syntax error.

CASE
   WHEN mobilenumber regexp ~'^09[0-9]'
   THEN REGEXP(mobilenumber, '0', '+63')
END

Any advice please?

-------Update---------

CASE
   WHEN mobilenumber regexp ~'^09[0-9]'
   THEN REPLACE(mobilenumber, '0', '+63')
END

The problem is I am REGEXP in my THEN instead of REPLACE. I also need to remove the ~ in the WHEN statement. When I do that, all 0s are replaced by +63. Is there a way to control this?

What about

...
WHEN mobilenumber REGEXP '^09[0-9]'
THEN concat('+63', substring(mobilenumber, 2))
...

? As far as I understand it, you only want the first zero to be replaced. If the pattern matches, you know that the first digit is a zero. So just simply take the substring after the first character and contact the '+63' in the beginning.


Oracle Tracing Activity of Triggers Our legacy application has over 4000+ triggers.

In order to diagnose a bug, we have to trace which trigger(s) inserted records into a particular table.

Had the legacy triggers logged their activity into a common table, we could have easily traced the activity on a particular table.

But this is not how the triggers were coded.

Is there a way to setup some kind of auditing process to trace following activity on the table :

 a. which trigger inserted record into the table
 b. what was the statement
 c. when the activity took place.

Since this is a development environment - we are not too concerned about performance implications.

thanks all.

Sure.

Enable auditing as:

alter system set audit_trail=db,extended scope=spfile;

Restart the database for the above to take effect.

Then enable auditing on the table:

audit insert on schema.table by access;

Then view the audit records in DBA_AUDIT_TRAIL. This gives you b. and c.. Using SQL_TEXT, you can find the SQL in V$SQL, and in V$SQL, the PROGRAM_ID (=OBJECT_ID in DBA_OBJECTS) and PROGRAM_LINE# columns tell you where that statement came from.


Working Around Filtering Problems in Merge Replication I am in the process of defining filters on a merge replication publication for our database.

The problem I am coming up against is that merge replication has these rules, I don't like to call them limitations because I can understand the purpose.

1) When creating an article filter you cannot include a subquery, or at least you shouldn't. If you do then it will appear to work the first time you sync, but if something changes in the subquery table the filter will not be re-evaluated.

2) When using a join filter you can only join two tables together.

The problem I am coming across is that the relationships in our database are more complicated than that. For instance here is one of our relationships,

user <- regions selected <- STREET LIGHTS -> settings (are we syncing street lights) -> user

Here is an example table structure to explain the above in more detail,

User Table,

Id    Username
1     petermc

UserRegion Table, (think of this as a security table, which region is a particular user allowed to see)

Id    UserId    RegionId
1     1         2
1     2         4

SyncSetting Table,

Id    UserId    Table
1     1         StreetLight

Region Table (this is just a look up table to show some example regions)

Id    Region Name
1     North Auckland
2     South Auckland
3     Central Auckland
4     Great Barrier Island

Road Table,

Id    RegionId    Name
1     1           Rosedale Rd
2     1           North Shore Rd

StreetLight Table

Id    RoadId    Last Replaced
1     1         2012-05-01
2     1         2009-06-03
3     2         2001-06-08

So in this case if I wrote out a select statement to apply my filtering for petermc it would look like this,

select * from StreetLight where
roadId in (select Id from Road where RegionId in (select regionId from UserRegion where userid = 1))
and exists (select 1 from syncsetting where userid = 1 and [table] = 'StreetLight')

So I am doing two things there. First filtering based on region to reduce a very large table into a smaller more manageable subset.

The second is specifying whether the subscriber is interested in the StreetLight table at all. If they are not then the subscriber should have an empty StreetLight table. This part is important because the publication will have a large number of tables in it, so it doesn't make sense to include stuff that the subscriber is not going to use.

Our biggest database has millions of records in some tables, and there will be a moderate amount of updates of these records too. We must get this filtering correct. The option to not filter those tables is not feasible.

This is a complicated situation, but I think that the best solution would be to make an additional table (which I'll call UserFilters), just to hold the consolidated data from the Road, UserRegion and SyncSetting tables in a single combined row format and then to Join your Replication Filter to that table instead.  This UserFilters table would need to be maintained by triggers on the constituent tables so that when rows in Road, UserRegion or SyncSetting change, the rows in UserFilters would be automatically changed to match.  I will demonstrate how to do all of this below.

First a note; This table (UserFilters) is a de-Normalized table that is adjunct to your normal application data schema.  That is, it is an operational artifact that is only used to manipulate the Replication technology into doing what you want, and is not part of your application's data design/schema.  This use of denormalization is considered acceptable as it is solely to address technology limitations and is not directly used by your applications.

OK, here's how to do it:

1. Write Your Desired Filter Query In JOIN Form:

Here's your original filter query (unrolled, to make it easier to decompose):

select  *
from    StreetLight
where   roadId in
    (
    select  Id
    from    Road
    where   RegionId in
        (
        select  regionId
        from    UserRegion
        where   userid = 1
        )
    )
  and   exists
    (
    select  1
    from    syncsetting
    where   userid  = 1
      and   [table] = 'StreetLight'
    )

We want to re-write this into a format with only JOINs and WHEREs, but no subQueries.  (This can always be done but I won't go into how in this article.)  Like so:

SELECT  StreetLight.*
FROM    StreetLight
JOIN    Road        ON  Road.Id             = Streetlight.roadId
JOIN    UserRegion  ON  UserRegion.regionid = Road.RegionId
JOIN    syncsetting ON  syncsetting.userid  = UserRegion.userid
                    And syncsetting.[table] = 'StreetLight'
Where   UserRegion.userid = 1

2. Create A View  Consolidating All of the Filter Conditions

We use the JOIN clauses above to make a view that contains only the columns relevant to the Replication Filter, and that projects all of the rows necessary to completely describe all of the filter conditions.  This is easier than it sounds, it should look like this:

CREATE VIEW vwUserFilters As
SELECT
        Road.Id             As RoadId,
        Road.RegionId       As RegionId,
        UserRegion.userid   As UserId,
        syncsetting.[table] As syncTable
FROM    Road
JOIN    UserRegion  ON  UserRegion.regionid = Road.RegionId
JOIN    syncsetting ON  syncsetting.userid  = UserRegion.userid
--  Comment the next line to generalize for all tables
--                  And syncsetting.[table] = 'StreetLight'

Note that we have also generalized the View's expression to cover all users.  Also, we have commented out the SyncSetting "[table] = 'StreetLight'", in order to generalize this for all tables covered by SyncSetting and Road (which may or may not be valid, you'll have to decide that).

3. Create The UserFilters Table

Create the UserFilters table using the view as a guide for the columns.  However, we'll want to add an IDENTITY column for key/index performance reasons:

CREATE TABLE adjUserFilters
(
    filterId    INT Identity(1,1) PRIMARY KEY,
    RoadId      INT         NOT NULL,
    RegionID    INT         NOT NULL,
    userid      INT         NOT NULL,
    syncTable   VARCHAR(32) NOT NULL
)

Then populate it using the View:

INSERT INTO adjUserFilters
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters

For performance and locking reasons you'll want a lot of index paths.  I cannot say for sure what they should be, but here's what I would start with:

CREATE UNIQUE INDEX IX_adjUserFilters
    ON dbo.adjUserFilters(userid, syncTable, RoadId)
CREATE INDEX IX_adjUserFilters_1
    ON dbo.adjUserFilters(RegionID, userid)
CREATE NONCLUSTERED INDEX IX_adjUserFilters_2
    ON dbo.adjUserFilters(RoadId, userid)

4. Add Maintenance Triggers to the Constituent Tables

You will need to add triggers to the Road, UserRegion and SyncSetting tables to keep the contents of [adjUserFilters] in synch with these tables when they are modified.  They should look like this:  First the Road table:

CREATE TRIGGER dbo.trRoad_MaintainUserFilters_IUD
   ON  dbo.Road
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   RoadId IN(Select d.Id From deleted As d)

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable
    FROM    vwUserFilters As v
    JOIN    inserted      As i  ON i.Id = v.RoadId
END

The UserRegion table:

CREATE TRIGGER dbo.trUserRegion_MaintainUserFilters_IUD
   ON  dbo.UserRegion
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   EXISTS(
        Select * From deleted As d
        Where   d.UserId    = adjUserFilters.userid
          And   d.RegionId  = adjUserFilters.RegionId
        )

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable
    FROM    vwUserFilters As v
    JOIN    inserted      As i
        ON  i.UserId    = v.userid
        And i.RegionId  = v.RegionId
END

And finally, the SyncSetting table:

CREATE TRIGGER dbo.trSyncsetting_MaintainUserFilters_IUD
   ON  dbo.syncsetting
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- First, Remove any filter rows corresponding to any DELETE or UPDATE rows
    DELETE FROM adjUserFilters
    WHERE   EXISTS(
        Select * From deleted As d
        Where   d.UserId    = adjUserFilters.userid
          And   d.[table]   = adjUserFilters.syncTable
        )

    -- Now, Add in any rows implied by any INSERT or UPDATE rows
    INSERT INTO adjUserFilters
    SELECT  v.RoadId, v.RegionID, v.UserId, v.syncTable
    FROM    vwUserFilters As v
    JOIN    inserted      As i
        ON  i.UserId    = v.userid
        And i.[table]   = v.syncTable
END

5. Create Your Replication Filter Query

Now you can create your Replication Query Filter for "user1" like so:

select  StreetLight.*
from    StreetLight     As t
JOIN    adjUserFilters  As f
    ON  f.syncTable = 'StreetLight'
    And f.RoadId    = t.RoadId
Where   f.userid    = 1
-- (corrected)

As requested, this is the script you would use to synchronize the adjUserFilters on a periodic basis, instead of from Triggers:

DELETE FROM adjUserFilters;

INSERT INTO adjUserFilters
SELECT RoadId, RegionID, UserId, syncTable FROM vwUserFilters

Off hand, I am not sure how you would insure that this runs right before or as part of the Merge Synchronization Job, but it probably can be done.


postgres: archive_command can't write to an nfs share I've enabled the archive_mode with wal_level = hot_standby on a master server. I intended to send the wal logs to a nfs share with:

archive_command = 'test ! -f /mnt/psql_wal/%f && cp -i %p /mnt/psql_wal/%f'

But it keeps telling me this:

sh: /var/lib/pgsql/archive_wal.sh: Permission denied
LOG:  archive command failed with exit code 126
DETAIL:  The failed archive command was: test ! -f /mnt/psql_wal/000000010000000000000001 && cp -i pg_xlog/000000010000000000000001 /mnt/psql_wal/000000010000000000000001
cp: failed to access ‘/mnt/psql_wal/000000010000000000000001’: Permission denied

Basic nfs permissions issue, you may think, but no. The following command works:

sudo -H -u postgres cp -i /var/lib/pgsql/data/pg_xlog/000000010000000000000001 /mnt/psql_wal/000000010000000000000001

Is the archive command running under a different user?

You are probably a victim of selinux or something like it, in which the same system OS account can have different permissions depending whether it is running as a deamon or from a command line (and, it often seems, depending on the barometric pressure, the phase of the moon, and what you had for breakfast).

You can turn it off, or put it into permissive mode where it allows the access and just logs a gripe.


Check If Record Was Inserted Or Updated In Table I have created a trigger that if the length of [name] <= 2 run an update statement.  This trigger fires 98% of the time, which leads me to believe the 2% of the time it does not fire means that the record was Updated() not Inserted()

This is sample DDL and my trigger, is there an issue with my trigger syntax?  Or should I be looking at a way to determine if the record was updated?

Create Table TwoNumericNames
(
  name varchar(500)
)

Insert Into TwoNumericNames (name) Values
('AA'), ('RR'), ('CC'), ('MM'), ('EE')

CREATE TRIGGER trgUpdateName
ON [TwoNumericNames]
FOR INSERT
AS
Begin

UPDATE [TwoNumericNames]
SET [name] = CASE
                WHEN [name] LIKE '%AA%' THEN 'Alpha Awe'
                WHEN [name] LIKE '%RR%' THEN 'Raw Red'
                WHEN [name] LIKE '%CC%' THEN 'Capt Cree'
                WHEN [name] LIKE '%MM%' THEN 'My More'
                WHEN [name] LIKE '%EE%' THEN 'Ed Ewe'
            END
WHERE LEN([name]) <= 2

END

EDIT
Perhaps an AfterInsert() trigger would work better.  Is this the way it should be set-up?

Create Table TwoNumericNames
(
  id int,
  name varchar(500)
)

Insert Into TwoNumericNames (name) Values
(1,'AA'), (2,'RR'), (3,'CC'), (4,'MM'), (5,'EE')

CREATE TRIGGER [dbo].[trgUpdateName] ON [dbo].[TwoNumericNames]
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;

  -- get the last id value of the record inserted or updated
  DECLARE @id INT
  SELECT @id = [Name]
  FROM INSERTED

  -- Insert statements for trigger here
  UPDATE [dbo].[TwoNumericNames]
  SET [name] = CASE
                WHEN [name] LIKE '%AA%' THEN 'Alpha Awe'
                WHEN [name] LIKE '%RR%' THEN 'Raw Red'
                WHEN [name] LIKE '%CC%' THEN 'Capt Cree'
                WHEN [name] LIKE '%MM%' THEN 'My More'
                WHEN [name] LIKE '%EE%' THEN 'Ed Ewe'
            END
  WHERE [id] = @id

END

As many have commented, you want to isolate your trigger to fire and modify only when necessary.  This means your going to have to filter on just the record that was inserted.  You can do this with the inserted view.  As you will see from the sample code below, I've slightly modified your code to more easily see what's going on and isolate the update statement to only affect the inserted row.

--create the table
create table TwoNumericNames (rowID int IDENTITY, name nvarchar(500))

--insert a row
INSERT TwoNumericNames
VALUES (N'AA')

--note the row
select * from TwoNumericNames;
go

--create trigger
CREATE TRIGGER trgUpdateName
ON TwoNumericNames
FOR INSERT
AS
Begin

    UPDATE T
    SET T.[name] = CASE
                    WHEN I.[name] LIKE '%AA%' THEN 'Alpha Awe'
                    WHEN I.[name] LIKE '%RR%' THEN 'Raw Red'
                    WHEN I.[name] LIKE '%CC%' THEN 'Capt Cree'
                    WHEN I.[name] LIKE '%MM%' THEN 'My More'
                    WHEN I.[name] LIKE '%EE%' THEN 'Ed Ewe'
                    ELSE I.[name]
                END
    FROM TwoNumericNames T
    JOIN inserted I ON I.rowID = T.rowID

END
go

--insert another row
INSERT TwoNumericNames
VALUES (N'RR')

--note only the one record is updated
select * from TwoNumericNames


Use NOLOCK hint when calling table valued function Suppose the following function:

CREATE FUNCTION [dbo].[ufnTest]()
RETURNS TABLE
AS RETURN SELECT 1 AS Nr

My actual function will select actual data from many different tables. I (mostly) understand the risks of using nolock hints, and decided that in this case I actually want them. So I try to call above function like this:

SELECT * FROM [dbo].[ufnTest]() WITH(NOLOCK)

However, this fails with the following message:

  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The tips for fixing this error are not relevant. Perhaps this table hint isn't available for table valued functions then?

There are several alternatives I'd consider. One is to use the hint on all tables in my select query, but I'd prefer not to as (a) I'll have to remember this whenever I change the function around and (b) it's repetitive. Another alternative would be to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, but a downside is that I don't quite know how to set it back to wat it was before (and statements after the select from my TVF should have the original isolation level).

So, I'd prefer to make WITH (NOLOCK) for a select from a table valued function work. Is this possible?

You cannot use a WITH (NOLOCK) on a Table-Valued Function, unless you put it on every single table in the code inside the function.

Your best bet would be, like you said, to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

To change this back to the default, you need to find out what isolation level is currently set (before changing it above).

This can be done by running DBCC USEROPTIONS and checking the value for isolation level.

The default tends to be READ COMMITTED, so to change it to that, you'll want to write a statement like the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED


How to succesfully set up sp_AllNightLog to create a Reporting Server? I would like to set up a secondary, mirrored SQL Server for reporting purposes.

The reporting database doesn't have to be up to date with the primary and can generally lag behind the production database by as much as one day, maybe more.

I've been trying to achieve something like this for a few days now with little to no success, using Brent Ozar's SQL-Server-First-Responder-Kit and sp_AllNightLog.

Generally, the first FULL backup gets restored, and sometimes even a few LOG backups, but then it gets stuck, it stops seeing newer backups and just goes in a loop, doing nothing.

Troubleshooting seems to indicate to me that sp_AllNightLog @Restore = 1 runs into an error when restoring a database, or maybe a LOG file, records that in the restore_worker table, which stops all further restores from being issued. If I clear the error_number (-1) and last_error_date from the table, it starts restoring again but then hits an error again because it starts restoring the FULL backup instead of continuing with LOG restores. Then it logs that error date and error number and stops again.

Another issue is that sp_DatabaseRestore (from the Kit) leaves the restored databases, either from a FULL restore or LOG restore, in an unrecovered state (always uses NORECOVERY and never runs RESTORE WITH RECOVERY). That makes the databases unreadable on the secondary unless I change sp_AllNightLog from using @RunRecovery = 0 to @RunRecovery = 1:

-- Line 1302
IF @restore_full = 0
    BEGIN

        IF @Debug = 1 RAISERROR ('Starting Log only restores', 0, 1) WITH NOWAIT;

        EXEC master.dbo.sp_DatabaseRestore @Database = @database,
             @BackupPathFull = @restore_path_full,
             @BackupPathLog = @restore_path_log,
             @ContinueLogs = 1,
             @RunRecovery = 1,
             @OnlyLogsAfter = @only_logs_after,
             @Debug = @Debug

    END

IF @restore_full = 1
    BEGIN

        IF @Debug = 1 RAISERROR ('Starting first Full restore from: ', 0, 1) WITH NOWAIT;
        IF @Debug = 1 RAISERROR (@restore_path_full, 0, 1) WITH NOWAIT;

        EXEC master.dbo.sp_DatabaseRestore @Database = @database,
             @BackupPathFull = @restore_path_full,
             @BackupPathLog = @restore_path_log,
             @ContinueLogs = 0,
             @RunRecovery = 1,
             @Debug = @Debug

    END

Do I have to delete backup history before I set this up?
Has anyone written a complete guide to setting something like this up?
I genuinely can't seem to find one even while googling.

Any help is greatly appreciated!

Like any transaction log restore, sp_AllNightLog needs exclusive access to a database in order to do the restore.

This means you'll run into trouble when:

A user starts a very long-running query
sp_AllNightLog tries to start a log restore, and is blocked
Other users try to start queries, but are blocked by sp_AllNightLog, leading to timeouts and angry users

I wouldn't use sp_AllNightLog for this purpose (and that's not what it's designed for, so that's why you're not finding tutorials on how to do it.) If you DID insist on doing it, you would need to write code to:

Check to see if there are any user queries running
If yes, then bail out gracefully without trying to start a restore (but probably check to see if it's been a long time since a restore has been done - because you might be in a position where you need to kill the reporting query to get the data caught up)
If no, then start the restore process

That is left as an exercise for the reader.


Task scheduler for SQL Server Express I have an ASP.NET MVC app which works with database under SQL Server 2008 R2 Express edition. There is a need to perform a regular task on updating some records in the database.

Unfortunately the Express Edition lacks SQL Agent.
What approach would you recommend?

You can check out the Standalone SQL Agent open source project.  It uses the native SQL Agent tables and procedures (which still exist in SQL Express) to store the jobs replacing the SQL Agent itself.  The engine is pretty well done for T/SQL and Dos commands.  Things like SSIS, etc aren't done yet.  Also the editor is only about 1/2 done, but the engine to run the jobs is the important part.


SqlPS and PowerShell with Import-Module SQLPS different behaviors I'm trying to write an automated installation script in powershell for SQL Server Express 2014. I'm stuck with a stupid issue when trying to enable the TCP protocol in the SQL instance.

What I'm doing is the following:

Import-Module "sqlps" -DisableNamechecking

$smo = 'Microsoft.SqlServer.Management.Smo.'
$wmi = new-object ($smo + 'Wmi.ManagedComputer').

# List the object properties, including the instance names.
$Wmi

# Enable the TCP protocol on the default instance.
$uri = "ManagedComputer[@Name='JOHN-PC']/ ServerInstance[@Name='DEMO']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $false
$Tcp.Alter()
$Tcp

The issue I have is when I run the script in a standard powershell (run as administrator) the list of instance of my $wmi object is empty.

If I run the same commands (except the first one to load the module) in SqlPS, it works as expected, with my instances being populated correctly.

Unfortunately, I cannot run the whole script in SqlPs, because the script itself installs SQL Server.

This is how I alter TCP to use a specific port including enabling TCP at the end -- You have $Tcp.IsEnabled = $false is that correct?

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer .
$Instance = $mc.ServerInstances[$InstanceName]
#Change TCP Port
$TCP = $Instance.ServerProtocols['Tcp']
$AllIps = $TCP.IPAddresses['IPAll']
$AllIps.IPAddressProperties['TcpDynamicPorts'].Value = ''
$AllIps.IPAddressProperties['TcpPort'].Value = $Port
$TCP.IsEnabled = $true
$TCP.Alter()

so I would do the following

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null
$mc = new-object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer .
$Instance = $mc.ServerInstances[$InstanceName]
#Enable TCP
$TCP = $Instance.ServerProtocols['Tcp']
$TCP.IsEnabled = $true  ## or false to disable
$TCP.Alter()


Find rows with similar string values I have a Microsoft SQL Server 2012 database table with around 7 million crowd-sourced records, primarily containing a string name value with some related details. For nearly every record it seems there are a dozen similar typo records and I am trying to do some fuzzy matching to identify record groups such as "Apple", "Aple", "Apples", "Spple", etc. These names can also contain multiple words with spaces between them.

I've come up with a solution of using an edit-distance scalar function that returns number of keystrokes required for transformation from string1 to string2 and using that function to join the table to itself. As you can imagine, this doesn't perform that well since its having to execute the function millions of times to evaluate a join.

So I put that in a cursor so at least only one string1 is being evaluated at a time, this at least gets results coming out but after letting it run for weeks it has only made it through evaluating 150,000 records. With 7 million to evaluate, I don't think I have the kind of time my method is going to take.

I put full text indexes on the string names, but couldn't really find a way to use the full text predicates when I didn't have a static value I was searching.

Any ideas how I could do something like the following in a way that wouldn't take months to run?

  SELECT t1.name, t2.name
  FROM names AS t1
  INNER JOIN names AS t2
       ON EditDistance(t1.name,t2.name) = 1
       AND t1.id != t2.id

I've tried soundex, but since the names can contain spaces and multiple words per value I get too many false positives to use it reliably.

Having dealt this issue, the most effective and performant way is to create a CLR function that calculates the Levenshtein Distance. You will be able to mark the assembly as SAFE (if you're at all concerned about security), and it runs much quicker than SOUNDEX() or any in-built SQL Server functions.

Here's the code to set up the assembly and function in the database, as well as a basic version of the Levenshtein Distance algorithm implemented in C# from https://en.wikibooks.org/wiki/Algorithm_Implementation/Strings/Levenshtein_distance#C#

C#:

using System;
using System.Security.Cryptography;

namespace LevenshteinDistance
{
    public class LevenshteinDistance
    {
        private int LevenshteinDistance(string a, string b)
        {
            if (string.IsNullOrEmpty(a))
            {
                if (!string.IsNullOrEmpty(b))
                {
                    return b.Length;
                }
                return 0;
            }

            if (string.IsNullOrEmpty(b))
            {
                if (!string.IsNullOrEmpty(a))
                {
                    return a.Length;
                }
                return 0;
            }

            int cost;
            int[,] d = new int[a.Length + 1, b.Length + 1];
            int min1;
            int min2;
            int min3;

            for (int i = 0; i <= d.GetUpperBound(0); i += 1)
            {
                d[i, 0] = i;
            }

            for (int i = 0; i <= d.GetUpperBound(1); i += 1)
            {
                d[0, i] = i;
            }

            for (int i = 1; i <= d.GetUpperBound(0); i += 1)
            {
                for (int j = 1; j <= d.GetUpperBound(1); j += 1)
                {
                    cost = (a[i-1] != b[j-1])? 1 : 0;

                    min1 = d[i - 1, j] + 1;
                    min2 = d[i, j - 1] + 1;
                    min3 = d[i - 1, j - 1] + cost;
                    d[i, j] = Math.Min(Math.Min(min1, min2), min3);
                }
            }
            return d[d.GetUpperBound(0), d.GetUpperBound(1)];
        }
    }
}

T-SQL:

use [master];
go

exec sp_configure 'clr enabled', 1;
go
reconfigure with override;
go

use [database_name];
go

-- Drop the function...
if exists (select 1 from sys.objects so where so.[name] = 'LevenshteinDistance')
    drop function dbo.LevenshteinDistance;
go

-- ...then the assembly
if exists (select 1 from sys.assemblies sa where sa.[name] = 'LevenshteinDistance')
    drop assembly [LevenshteinDistance];
go

-- Now load the assembly from an appropriately accessible location
create assembly [LevenshteinDistance]
from
    'd:\LevenshteinDistance.dll'
with
    permission_set = safe;
go

-- Create an asymmetric key from the assembly file
use [master];
go

if not exists (select 1 from sys.asymmetric_keys ak where ak.[name] = 'LevenshteinDistanceKey')
begin
    create asymmetric key LevenshteinDistanceKey
    from executable file = 'd:\LevenshteinDistance.dll';
end
go

-- Create a user to associate with the assembly from the asymmetric key, and then
-- revoke connect access. The login is used to execute the assembly.
use [master];
go

if not exists (select 1 from sys.server_principals sp where sp.[name] = 'LevenshteinDistanceKeyUser')
begin
    create login LevenshteinDistanceKeyUser from asymmetric key LevenshteinDistanceKey;
    revoke connect sql from LevenshteinDistanceKeyUser;
end
go

grant external access assembly to LevenshteinDistanceKeyUser;
go

use [database_name];
go
alter assembly [LevenshteinDistance] with permission_set = safe;
go

-- Create the SQL function which will be called
create function [dbo].LevenshteinDistance
(
    @string1 nvarchar(2048)
    ,@string2 nvarchar(2048)
)
returns nvarchar(max)
as
    external name LevenshteinDistance.[LevenshteinDistance.LevenshteinDistance].LevenshteinDistance;
go


Query to select specific columns based on current date I have a table named 'mrtg' that has four columns:

mysql> select * from mrtg;
+----------+------+------+------+
| city     | day1 | day2 | day3 |
+----------+------+------+------+
| Delhi    |    2 |    6 |    9 |
| Mumbai   |    1 |    3 |    8 |
| Banglore |    4 |    1 |    6 |
+----------+------+------+------+
3 rows in set (0.00 sec)

If current date = '2019-09-01' then I want to select as

SELECT CITY, DAY1 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2 FROM MRTG;

If current date = '2019-09-02' then I want to select as

SELECT CITY, DAY1, DAY2, DAY3 FROM MRTG;

and so on.

How can I write a query for this?

The values represent day-wise data for a whole month. I do wonder about this type of table structure, but it's the client requirement. I need to find a solution to fetch only the columns from day 1 to the current date.

If you were able to start with a new table I would use this structure to store the data:

CREATE TABLE mrtg (
    city varchar(30)
    , logdate date
    , value int
)

This way you can store data for any time period and work out the days needed from the date.

Since you can't create that we'll have to see if we can modify what you've got in a query.
I've had a play and this will get the data into a table format that I believe would be easier to work with:

select data.*
  from mrtg
  left join (
    select city, 1 as day, day1 as datavalues from mrtg
    union all
    select city, 2 as day, day2 from mrtg
    union all
    select city, 3 as day, day3 from mrtg
    #....
    #union all
    #select city, 4 as day, day4 from mrtg
  ) data
  on mrtg.city = data.city
  where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))

You'll have to add as many union all elements as you have columns but then you can filter on the number of days you want. You will now be able to GROUP BY and perform any calculations you need to.

select data.city, sum(data.datavalues) as TotalToDate
  from mrtg
  left join (
    select city, 1 as day, day1 as datavalues from mrtg
    union all
    select city, 2 as day, day2 from mrtg
    union all
    select city, 3 as day, day3 from mrtg
    #....
    #union all
    #select city, 4 as day, day4 from mrtg
  ) data
  on mrtg.city = data.city
  where data.day <= DAYOFMONTH(STR_TO_DATE('2019-08-02','%Y-%m-%d'))
  GROUP BY city

For keeping your main queries cleaner and smaller I'd recommend creating a VIEW for the subquery with all of the UNION ALL statements in.


Does it make sense to order condition in where query? We have a clustered index in table Tbl with order of A,B,C.
Does it make senses to write a query to have

WHERE A = @a, B = @b, C = @c

or it'll be as same as

WHERE C = @c, B = @b, A = @a

Does the order of query make sense for clustered index?

It makes no difference: the optimiser evaluates the query and will work out predicate order for itself, matching a suitable index and all the other good stuff it does.

This is because SQL is "declarative" not "procedural": you say what you want, not how to do it.

It's nicer to read though...


ASMCA(ASM Configuration Assistant) GUI is not rendering properly Hi Gurus,

While running ASMCA utility on OEL 7.5, Oracle is not able to render the GUI of the tool. I clicked on exit button and next screen comes as a vertical line (Screen attached above).No option there to expand the window.

Have ever anyone come across this type of issue? Please let me know if you have solution for this.

Any help is appreciated.

Thanks
Prabhakar

This happens because of color display setting of Linux GNOME desktop. I used Xming Software on windows for remote installation.It worked.


Need a SQL Query for finding OS Version, RAM, etc DBAs, I need some help. Is it possible to find below mentioned parameters through SQL query or else GUI (cluadmin, msinfo32, systeminfo, etc.) is the only option. I'm aware of finding below mentioned parameters through GUI, but based on the fact that working with query will save lot of time, I'm looking for a SQL query to get below details in SSMS.

OS Version - (eg., Windows 2012 Server Standard SP1)

RAM (GB) - (eg., 128GB)

Machine Type - (eg., Physical or Virtual)

Installed On - (When SQL instance is installed or built eg., 08 August, 2016)

If SQL instance is in cluster, I'm looking to get below details as well

SQL Virtual Name -

SQL Virtual IP -

Cluster Name -

Cluster IP -

Node1 -

Node1 IP -

Node2 -

Node2 IP -

Check out Glenn Berry's Diagnostic Scripts. He includes a whole range of queries in there for accessing server information, including a basic health check.

You will need to download the set of scripts appropriate for the version of SQL Server you're diagnosing.


Does a NULL DATE use as much storage as a value? I need to store a DATETIME value that may or may not have a date.
My options are:

| starts DATETIME     |
|---------------------|
| 0000-00-00 12:38:06 |

| starts_date DATE | starts_time TIME |
|------------------|------------------|
| NULL             | 12:38:06         |

If a NULL DATE value takes 0 storage, I may save a few bytes on most records (8 bytes on full DATETIME vs 3 bytes on just TIME). However, the following pages say that NULLs still take as much storage as the actual values on the fixed length columns, such as CHAR vs. VARCHAR. Is the DATE considered fixed-length too?

The byte size of NULL Decimal(19,4) or DATETIME (this is for SQL Server though)
Does an empty column value occupy same storage space as a filled column value

If you are using MyISAM, don't.
If you found that DATETIME is 8 bytes, you are using an old version of MySQL; it takes only 5 bytes now.  Upgrade.
"Fixed" is slightly important for MyISAM; rarely important for InnoDB.  So don't worry.  In particular, don't worry unless you have a billion rows.


Ideal MAXDOP & CPUs - how to trace for parallel queries? There is a discussion going on in our company on what the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server.

The server has 12 CPUs, 32GB RAM and all database sizes add up to < 30GB so they can all fit in memory (I tried to force this by doing a select * from every table). On certain payroll days, the CPU gets maxed out to 100% for a few seconds.

MAXDOP was originally set to the default 0. We later changed it to 8 based on several 'best-practices' articles. However the vendor suggests to change it to 1 (no parallelism), while others suggest changing it to 4, so that one run-away query doesn't hog most of the CPUs.

I'd like to find out how many CPUs are actually being used by queries. There is a Degree of Parallelism event in Profiler. The BinaryData column says :

0x00000000, indicates a serial plan running in serial.
0x01000000, indicates a parallel plan running in serial.
>= 0x02000000 indicates a parallel plan running in parallel.

What does "parallel plan running in serial" mean ?
I see a lot of 0x01000000, and a few 0x08000000's in my trace. How can i determine whether one query is hogging CPUs and if reducing it to 4 will help ?

The post on Five SQL Server Settings to Change:

http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

Suggests the following:

Maximum Degree of Parallelism: Limit parallelism to the number of physical cores in a single CPU socket,
Cost Threshold for Parallelism: Start with 50 then tune up or down depending on your need.

Also: A parallel plan running in serial means that although a parallel plan was created, the needed resources for running parallel are not available.  So the plan will serially runs sections of the parallel plan until finished.