Merge faster than Insert Are there any advantages/disadvantages of making use of a merge over a insert function? Can an insert statement make use of the `OUTPUT'

Note: the ON clause for the merge is set to 1=0 (always insert)

Merge DeviceReading
    Using (
        --table here
    ) As [Source]
    On (1 = 0) -- Effectively Makes this an Insert as 1 Never Matches 0
    When Not Matched Then
        INSERT (
            [value1],
            [value2]
        ) VALUES (
            @value1,
            @value2
        ) OUTPUT Inserted.[value1], Inserted.[value2] Into @InsertIDTable;

MERGE has more flexible OUTPUT. OUTPUT can refer to the merge source which is handy if you want the client to be able to match what it sent to what was actually inserted (e.g. IDENTITY values). INSERT can't do that (for no fundamental reasons; seems to be not implemented).

I can't think of any performance difference. The plans certainly look so similar that I can't make out a difference right now. If there was a major difference SQL Server could optimize one form to the other.


Recreating a Postgres table with all cascading relations transferred I'm trying to optimize the structure of a table. To do so, I've put the columns in the most optimal order possible and then ran this query (simplified):

INSERT INTO table_new ("id", "some_field", "some_other_field") SELECT "id", "some_field", "some_other_field" FROM table_current;

Then I simply rename the table and add all the same indexes and foreign keys.

ALTER TABLE "table_current" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table_current";
ALTER TABLE "table_current" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;

This has worked great in the past, but in this particular case, there are a LOT of other tables with cascading foreign keys on this table.

So if I recreate and rename, then all the other tables still reference table_old in their foreign keys instead of the new table_current.

How do I do such a table migration like this without having to recreate the foreign keys on every other table as well?

There is no easy way to work around the problem. When you drop columns that other things are referencing, like views or foreign keys, you must manually inform all areas of the schema that have dependencies on that column.

There is no shortcut that I know of around this. No one really makes a habit of reordering tables. Most of the requests are for cosmetic reasons, however if you're really doing it for a valid reason (like table packing) then I wish you the best of luck.

It's been a feature request for a while, and it comes up frequently.


Deny INSERT/UPDATE/DELETE to a particular schema Is always hard to me to manage roles and permissions with users connecting to SQL server with Windows authentication.

There are some users (not actually SQL users on SQL, are coming from active directory) connecting to a server with SQL 2014, they are all using windows authentication, I want them to avoid doing INSERT/UPDATE/DELETE in all tables with a particular schema but they can run stored procedures that can affect the tables on that schema.

I was thinking to create a server role and then apply that server role to each database and also for new databases created.

My idea is to create a server role, assign those users to that role and then assign the role to the different databases on the server.

Is that the right way to do it?

Usually you just put the users in an AD group and add the group to SQL as a named user.

Then grant the named user select/execute privileges by schema.

This is how you grant/revoke privileges.

Let's say we have a user abc and he uses schema xyz. Here is the syntax.

GRANT Insert ON xyz TO abc;

Replace 'GRANT' with 'Revoke' and 'Insert' with ('Update' or 'Delete') according to user requirements.

You can revoke all of three at once together too.

Revoke INSERT, UPDATE, DELETE ON xyz TO abc;


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' I have an Operations server running Windows Server 2012R2 and SQL Server 2014 Enterprise back end.  This server is used to deploy new code to other production servers via cmd file called by a SQL Agent Job.  All of the servers are on the same domain.

Server A runs Windows Server 2008R2 and SQL Server 2008R2 back end.  This server has a linked server connecting to a database on Server B, running the same OS and SQL Server version as A.  The linked server is configured with option @useself=TRUE.

All three servers use a service account enabled for delegation.
This service account has sa priveleges on all three SQL Server instances.
All three servers have SPNs configured with that account and are delegated to use Kerberos for the associated MSSQLSVC services.

I can run the following on each and "Kerberos" is returned

    SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

In addition I can telnet, ping, etc. from any of those servers to any other server without issue - everything is connected.  Code deployments have never had a problem and the linked server is referenced often and without issue...except for one scenario and I don't understand why.

Double Hop Scenario

A SQL Agent job on the Operations server is run adhoc by any sysadmin other than the SQL Agent service account and executes a cmd file, also on the Operations server.
The SQL Agent job is configured to "Run As" a SQL Agent service account, having sa priveleges.
The code that is deployed comes from a .sql file on the Operations server.
The cmd file calls SQLCMD to execute the code in the .sql file against Server A.
The code in the .sql file references the linked server and fails with error

*Msg 18456, Level 14, State 1, Server ServerB, Line 1  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. *.

The error is directed at the linked server - Server B.  If I run this code manually, using SQLCMD, from the Operations server against Server A, it runs fine.  If I logon to a box as the SQL Agent service account and run the SQL Agent job, it runs fine.  It's only when the SQL Agent job is executed by someone other than the SQL Agent service account that I get the error.

I've read through so many posts and blogs and MSDN articles regarding Kerberos, double hop, etc telling me to do what has already been done.  What am I missing?

Additional Info
I've finally been able to come back to this and located some additional info.  Using Bogdan's advice, I ran Process Explorer and verified the credentials for the first hop are from the SQL Agent Service account as expected and that TCP is being used.  Alas, that is all the useful info I was able to get out of the tool.

I dug into the Window's application logs and dug around for login information for the different instances on the machines and noticed that Kerberos isn't even being used!!!  Instead NTLM is being used.

So that's the new path I'm heading down - why is NTLM authentication being used, when Kerberos is set up and proper FQDN SDNs exist by port and instance?  Do I need to somehow specify authentication type in the cmd file or SQLCMD call?  Or do I have something misconfigured that I'm not thinking about?

The Mystery Deepens
The SQLCMD call referenced the "first hop" server via Alias.  I modified the SQLCMD server reference to the actual named instance and reran the job.  It worked!  We also use cNames for our machines and SQL instances and so I tried using that.  It also worked!  For grins and giggles I re-tried the alias...it worked???  I go check the Windows applications logs for each of these and it is still reporting authentication as NTLM!

I am completely baffled at this point and at a loss as to how to explain this behavior to fix the rest of our environment.

Finally figured this out!

We reference many of our very active SQL Servers and Instances with SQL aliases and canonical names.  This allows us to perform parallel upgrades and switch machines in and out without code interruption.  The SQL aliases are all configured to reference the canonical names for the same reason.  The standard SPNs we had registered are for the machine names.

EXAMPLE
SQL Server SSxyz\xyzInstance listening on port 12345 has registered SPNs:

MSSQLSVC/SSxyz.mydomain.com:12345
MSSQLSVC/SSxyz:12345
MSSQLSVC/SSxyz:xyzInstance

These are all registered to the service account that owns MSSQLSVC on that machine and delegation is set for each to pass credentials.

The server and instance also have canonical name cxyz\xyzInstance.  Finally, the SQL Server has a 32bit and 64bit alias of xyzAlias, pointing to canonical name cxyz\xyzInstance.

During a double hop scenario from my local pc to xyzAlias to abcAlias, Kerberos authentication fails and falls back to NTLM because the alias and SPN cannot be resolved.  This is why the logs were showing NTLM.

The fix was to add a few more SPNs to register the canonical name of the server to the actual name.  Then the SQL service is registered with another SPN. like this:

setspn -s host/cxyz SSxyz
setspn -s host/cxyz.mydomain.com SSxyz
setspn -s MSSQLSVC/cxyz:12345 sqlserviceaccountname
setspn -s MSSQLSVC/cxyz:xyzInstance sqlserviceaccountname

Once registered, each SPN is configured for delegation.
These appear as unique SPNs and allow the SQL alias to resolve to the actual machine name. The double hop authentication logged is kerberos and everyone is happy!  Except the AD guys...they're going to have a lot of SPNs to register in a couple of hours.


Best way to work around NOT IN NULL issue? There are plenty of explanations of why NOT IN NULL doesn't work, but I didn't see any solutions. (recent question here: Why does NOT IN with a set containing NULL always return FALSE/NULL?)

I have 6 optional arguments to a stored procedure that are used to filter a certain value of a query. The previous developer did something like this:

IF @var1 IS NULL AND .....

    select ...

else

    select ...
    where value in (@var1, ...)

I didn't particularly like this. (The query is quite massive), so I decided to opt for something like this: (We had the results in a temp table anyway)

IF @var IS NOT NULL OR ...

    delete from #temp where value not in (@var1,...)

But I realized that this won't work.

The only thing I can think to do is to create another temp table that will hold only the not-null values in @var1, etc. (Either using if statements or deleting where is NULL) and then doing a join delete on this table.

Is there a better way?

Don't know about Sybase but in SQL Server you can do like this.

select *
from yourtable
where value not in (select N
                    from (values (@var1),
                                 (@var2),
                                 (@var3),
                                 (@var4),
                                 (@var5)) T(N)
                    where N is not null)

If you can't use values to create a derived table you can use union all instead.

where value not in (select N
                    from (select @var1 union all
                          select @var2 union all
                          select @var3 union all
                          select @var4 union all
                          select @var5) T(N)
                    where N is not null)


Unexpected result from query based on old column value I need to update a column and based on the old value, update a second column.

After googling I came up with THIS (*nr 4) solution: copy the table to have the old value at hand.

The table looks like

   id | upd | a1 | a2 .. | b1 | b2 | b3 |
   1  |     | 1  | 1     | 1  | 1  | 1  |

My query looks like:

UPDATE table t1, table t2 SET
    t1.b1  = GREATEST( 5, t1.a1),
    t1.upd = IF(t1.b1 <> t2.b1 ,1 , t1.upd)
WHERE t1.id = t2.id

Based on this query I would expect the table to look like this:

   id | upd | a1 | a2 .. | b1 | b2 | b3 |
   1  | 1*  | 1  | 1     | 5* | 1  | 1  |
*= updated

Only, it isn't. It executes the first line correctly (updating b1 to 5), but not the second one, setting the upd field.

The mysql manual states:

The second assignment in the following statement sets col2 to the current (updated) col1 value,
not the original col1 value. The result is that col1 and col2 have the same value.
 This behavior differs from standard SQL.
UPDATE t1 SET col1 = col1 + 1, col2 = col1;

So I would have expected the second line (update upd) to work as well.

If I add (as a test) these two lines

t1.b2=t1.b1,
t1.b3=t2.b1,

before the t1.upd = line, b2 and b3 still show 1, where I expected b2 to be 5.

Where am I going wrong in my logic (and understanding of MySQL :-)?

NOTE Sorry, I forget to mention: I connot use Triggers. Otherwis it would have been less difficult

I would try this one:

UPDATE
    table t1
SET
    upd = 1,
    b1 = GREATEST(5, a1)
WHERE
    b1 <> GREATEST(5, a1) ;

No need for "copying" the table or using the (not 100% sure) assumption that MySQL updates the columns in a specific order.

The answers in your link are about a much more difficult to do (in MySQL) operation, i.e. swapping the values of 2 columns.

If you want to update multiple columns and set the upd=1 column if at least one of the other columns is updated, you can use a simple OR in the WHERE clause:

UPDATE
    table t1
SET
    upd = 1,
    b1 = GREATEST(5, a1),
    b2 = GREATEST(7, a2),
    b3 = <whatever>
    ---
WHERE
   ( b1 <> GREATEST(5, a1)
  OR b2 <> GREATEST(7, a2)
  OR b3 <> <whatever>
  ---
   ) ;


json functions: double quotes added when going through a variable Im stuck with what it seems simple question, when I create a JSON using the MariaDB/MySQL json functions going through variables, it adds double quotes which is annoying. However, if I don't go through variables, it works fine. Unfortunately I have to go through variables as I'm building my JSON using a recursive function:

without variables:

declare temp_json JSON;
set temp_json = json_object('data',json_object("jsonapi",json_object("version", "1.0")));
select temp_json

OUTPUT

{"data": {"jsonapi": {"version": "1.0"}}}

with variables

declare temp_json JSON;
declare jsonapiheader JSON;
set jsonapiheader = json_object("jsonapi",json_object("version", "1.0"));
set temp_json = json_object('data',jsonapiheader);
select temp_json

OUTPUT

{"data": "{\"jsonapi\": {\"version\": \"1.0\"}}"}`

So how do I do like 2) but with the output of 1) ?

my system:
MariaDB 10.2.9

@Evan Carroll is certainly right about the general implementation of JSON in MariaDB.
But if you are still going with MariaDB this might help you:

The behavior you describe is a serious bug which is tracked here:
https://jira.mariadb.org/browse/MDEV-13701
As far as I understood its expected to be fixed with MariaDB-10.2.12.

For the time being I use the following hack:

set @jsonapiheader = json_object("jsonapi",json_object("version", CAST(1.0 AS DECIMAL(2,1))));
set @temp_json = concat('{"data":',@jsonapiheader,'}');
select @temp_json, json_valid(@temp_json);

Output:

{"data":{"jsonapi": {"version": 1.0}}}        |       1

Also look at the CAST within the first JSON_OBJECT. It also works with variables and functions and results in a more accurate JSON output.


db2 connecting to a db via alias and storing user name and password I created a TCP node and I catalogued a db into it. The catalogued db needs user name and password to be accessed. Is there anyway I can store username and password in the cataologue (or other way)? I use the catalogue in an Excel spreadsheet (OLEDB connection) and every time I need to insert username and password.

DB2 won't store your username and password - neither the server, nor the client. What you could do is set up an ODBC setting in Windows. (I am assuming you are using Windows given you mention Excel, though I could be incorrect in that). The ODBC setting allows you to store the username and password on that Windows system. Do be careful with that though as Windows will warn you that the password isn't stored the most securely.


Transferring Data from OLTP to OLAP databases Systems that have both OLTP and OLAP databases periodically move data from the OLTP database to the OLAP database, so that reports can be generated based on the OLAP database.

What's the official term for this process and is there an overview of this process online?

It is mostly referred as ETL process (extraction, transformation, and load).

Here are a link of MSDN article on Transforming OLTP Data to OLAP Data Warehouses. It is an old article but the same concept applied.


Many-to-many tables tracking participation at multiple events I am working on a side project to track observations of different birds, and the time spent during each observation. Right now I have a table called Observations:

 obs_id   |   start_time  | end_time  |   date      |  location
--------------------------------------------------------------------
    1            3            36         27/09/2016    acadia park
    2            4            00         27/09/2016    zion park
    3            12           50         27/09/2016    acadia park
    4            16           32         27/09/2016    yellowstone
    5            9            20         28/09/2016    zion park
    6            21           50         28/09/2016    yellowstone

However, multiple people are observing these birds, and I would like to be able to query the people associated with these birds. I would also like to keep a running total of each person's observation time.

So I have a table of Observers, with a column for their total time spent observing (in hours, calculated from the start_time and end_time columns in the Observations table), but I'm not sure of the best way to relate these two tables. I want to relate these two tables so that I can see the observations that each person was involved with.

 person_id   |   total_time  | name
-----------------------------------------
    1            1.5          Jane Doe
    2            4            John Doe
    3            5            James Doe
    4            6            Jenny Doe
    5            3            Jeremy Doe

Unfortunately, there can be multiple people at an observation, and the same person can have multiple observations, too. That makes this (if I understand correctly) a many-to-many relationship. I'm wondering how to go about fixing that. Should I make a separate associative entity? Or should I just add a column into Observations that contains a foreign key for Observers, and create new rows for each observer – something like this?

 obs_id   |   person_id  | start_time  |  end_time |   date      |  location
--------------------------------------------------------------------
    1           1_1            3          36          27/09/2016    acadia park
    1           1_2            3          36          27/09/2016    acadia park
    2           2_1            4          00          27/09/2016    zion park
    2           2_2            4          00          27/09/2016    zion park
    2           2_3            4          00          27/09/2016    zion park
    2           2_4            4          00          27/09/2016    zion park
    3           3_1            12         50          28/09/2016    yellowstone

This will be implemented using PHP, allowing users to submit their observations on a website and updating a SQL database (MySQL). I've looked at some other problems, like tracking events associated with an item, or tracking student attendance, but none of them have quite the same problem that I do.

Thanks in advance for any help.

Ideally, your system could comprise:

a Locations table with location information and a location_id
a People or Observers table with personal details and a person_id
an Observations table just the way you have it
and an Observer_Observations table with person_id and obs_id (i.e. your many-to-many table)

The total_time column in your Observers table should be calculated from Observer_Observations rather than stored.


High Disk I/O after batch deleting from innodb table I needed to delete many records from a 500GB innodb mysql table. I thought I made the right choice by running many delete statements to batch delete the data.

I deleted 10,000 rows at a time and it took around 28,000 iterations (280 million rows deleted) to complete. I had breaks between each delete to allow the read slave to not get too far behind.

Now that the deletions are complete, there are two mysql threads that are hammering the disks. One of them is reading, the other is writing. 85% of the I/O are writes.

I've read that there is a reordering operation that happens after a delete but I'm not sure if that's it. The processlist doesn't show anything out of the ordinary. Monitoring tools that have been watching the DB for some time don't show much of anything different either, with the exception that queries are taking longer, disk I/O is maxed out, and there is an increase in dirty buffer pool pages.

Is there anyway to find out what is going on and causing the high disk I/O? I'm also looking for a way to make it stop or decrease it's priority since the database is working fine otherwise.

MySql info: mysql  Ver 14.14 Distrib 5.6.30-76.3, for Linux (x86_64) using  6.2

The table in question does not have a primary key or any unique identifier. It has ~925 million rows in it after the delete.

EDIT 1:

Server has 32GB of ram.

The rest of the mysql data on this server is 150gb.

SHOW CREATE TABLE tablename
CREATE TABLE tablename (
  column1 bigint(11) NOT NULL DEFAULT '0',
  column2 bigint(11) NOT NULL DEFAULT '0',
  column3 bigint(11) NOT NULL DEFAULT '0',
  column4 datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  column5 varchar(64) DEFAULT NULL,
  column5 int(11) NOT NULL DEFAULT '32',
  column7 varchar(64) DEFAULT NULL,
  column8 bigint(20) DEFAULT '0',
  column9 bigint(20) DEFAULT '0',
  column10 bigint(20) DEFAULT '5',
  column11 date DEFAULT NULL,
  column12 enum('src','dst','unknown') NOT NULL DEFAULT 'unknown',
  UNIQUE KEY uniquename (column1,column2,column5,column7,column4,column12),
  KEY column1 (column1,column2,column5,column7,column4),
  KEY column11_index (column11)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Column names changed to generic names.

The goal of doing small batch deletes was to try and not interfere with normal daily operations on the database.

There is a read slave, it performed all the delete operations and it is not suffering from the same high disk IO that the master is experiencing.

EDIT2/Update:

The server has been rebooted. Once the mysql service started (even with no clients connected), it continued to use all available disk resources to write to disk.

Is there anyway to tell why it's writing to disk constantly?

First and foremost if your data had some sort of identification (ie PRIMARY KEY, or TIMESTAMP) then you could have done:

DELETE FROM table_name WHERE PRIMARY_KEY_column < PRIMARY_KEY_value;
-- or
DELETE FROM table_name WHERE TIMESTAMP_column < TIMESTAMP_value;

As for what to do now. I would run the following processes:

In a WHILE TRUE make sure that the mysql isn't hanging

mysql -h $HOST -P $port -uroot -e "show processlist;"

Check the row count to see if you are increasing or decreasing

SELECT COUNT(*) FROM table_name;

If the row count is increasing then you should check what other processes are being utilized.
ps fux | grep mysql

However, if the row count is decreasing or consistent then it may mean that your deletes are not fully finished.

Wish I can be more help, but your question felt incomplete and thus not very clear.


Arabic language search in codeigniter error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) Arabic language search in codeigniter error :   Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

In English language search is working

JOIN Query to use to search

SELECT *, `guest`.*, `guest`.`id` as `guestid`, `assistant`.`guest_id` as `assistantid` FROM `guest`
LEFT JOIN `assistant` ON `guest`.`id` = `assistant`.`guest_id`
WHERE   (
`guest`.`id` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `company_name` LIKE '%محلل مالي%' ESCAPE '!'
OR  `position` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`country_code` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`mobile_no` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`email` LIKE '%محلل مالي%' ESCAPE '!'
OR  `nationality` LIKE '%محلل مالي%' ESCAPE '!'
OR  `category` LIKE '%محلل مالي%' ESCAPE '!'
OR  `gender` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest_type` LIKE '%محلل مالي%' ESCAPE '!'
OR  `location` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`remarks` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`id` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`id_user_master` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`guest_id` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`country_code` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`mobile_no` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`email` LIKE '%محلل مالي%' ESCAPE '!'
 )
ORDER BY `guest`.`id` DESC
 LIMIT 10

in database "utf8_general_ci"

Check the connection for being character set utf8
Check the application for whether it is using UTF-8

Meanwhile, that looks like an awfully slow query -- lots of ORs and leading wildcard on LIKEs.


Why MongoDB not running SSL? I need to run MongoDB with SSL, the version that is currently installed is 3.4.16 without SSL.

Can I download 3.4.16 with SSL and just stop the service copy the Bin files across and then start up the service again, in it's simplistic form?

The answer is simple and short. Yes, that's the right way to do it.


SQL Server 2008 R2 - how to repair System Views I have SQL Server 2008 R2 database, which is corrupted. Dbcc checkdb command gives result:

  Msg 8921, Level 16, State 1, Line 1
  Check terminated. A failure was detected while collecting facts.
  Possibly tempdb out of space or a
  system table is inconsistent. Check
  previous errors.

Then I tried run dbcc checktable, with this command:

sp_MSforeachtable @command1="print '?' dbcc checktable ('?')"

Result of this command is:

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the current command.  The results, if any,
  should be discarded.

Then I reviewed System Views and found, that some System Views are corrupted, in example when I do

select * from [sys].[all_parameters]

or from [sys].[all_columns] I got some result rows and then I got this error:

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the current command.  The results, if any,
  should be discarded.

Is there any ways to repair that System Views?

P.S. I already tried to do repair with data loss but it was unsuccessful...

AFAIK, your best bet is to copy all the tables to another database.  If you're using SQL Server express, this is something you have to do by hand, using SQL Queries or scripts.

It's a good idea to check for possible causes too: run a memory diagnostic, and a disk check.


Upgrade MongoDB meta-data after binary upgrade I upgraded our MongoDB binaries from 2.2.5 to 2.4.9. I did not follow one of the considerations prior to upgrading:

To upgrade to 2.4, sharded clusters must upgrade following the meta-data upgrade procedure.

The reason, not that it matters, is because I didn't think it was needed since we do not have any sharded collections in our environment. So, instead of following the sharded cluster upgrade, I followed the replica set upgrade procedure.

My question is, can I successfully run the meta-data upgrade without having to rollback to 2.2.5?

If you are not using a sharded cluster the meta-data upgrade procedure from 2.2 to 2.4 is not required. It involves the config database which does not exist in a non-sharded environment.


ilike search really uses its index? I have a column called nickname and have created index for it.

 create index test_idx on test_table using btree (nickname);

My query is,

 select * from test_table where nickname ilike '%pga%';

test_table is having around 100k records. when I'm doing explain, i am getting some cost which is same as before creating the index. Why is that.?

(using Postgresql 9.2)

Edit 1:

nickname column is character varying(100)

LIKE with a wildcard at the start of the expression can't use a btree index. In order to make that query use an index, you need to create a trigram index for which you need to install the pg_trgm extension first.

create index on test_table using gin (nickname gin_trgm_ops);


Parent Child Relation in same Table I have a table storing parent/child records as such:

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6953  | Ajit       |   AB7   | comp2   |  7060      |     1     |
| 6957  | Rahul      |   DE1   | comp3   |  7060      |     1     |
| 6958  | uday       |   TG6   | comp4   |  7060      |     1     |
| 6959  | john       |   HY7   | comp5   |  7060      |     1     |
| 6960  | netaji     |   HY5   | comp6   |  7060      |     1     |
| 6961  | prakriti   |   GT6   | comp7   |  7060      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
| 6963  | santosh    |   KK5   | comp9   |  7060      |     1     |
| 6964  | Ravi       |   PP0   | comp10  |  7060      |     1     |
+-------+------------+---------+---------+------------+-----------+

Is it possible to return records where parent record is Enrolled and related Child record is Not Enrolled?

This returns what I need for 1 specific customer:

select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.company = 'comp1' and a.Enrolled = 1
union all
select a.custid, a.custname, a.deptid, a.company, a.parentcustid, a.enrolled
from customer a
where a.parentcustid= 7060 and b.Enrolled = 0

+-------+------------+---------+---------+------------+-----------+
|custid | custname   | deptid  | company |parentcustid| enrolled  |
+=======+============+=========+=========+============+===========+
| 7060  | Sally      |   AB1   | comp1   |  null      |     1     |
| 6962  | sachin     |   KL7   | comp8   |  7060      |     0     |
+-------+------------+---------+---------+------------+-----------+

How can I structure the query to return that type of result set for all parent child records in the table?

A common way to do this in SQL Server is to use a Recursive CTE:

Here's your test data as an insert:

CREATE TABLE #yourmom
(
    custid INT,
    custname VARCHAR(10),
    deptid VARCHAR(3),
    company VARCHAR(10),
    parentcustid INT,
    enrolled BIT
)

INSERT #yourmom ( custid, custname, deptid, company, parentcustid, enrolled )
SELECT x.custid, x.custname, x.deptid, x.company, x.parentcustid, x.enrolled
FROM   ( VALUES ( 7060, 'Sally   ', 'AB1', 'comp1 ', NULL, 1 ),
                ( 6953, 'Ajit    ', 'AB7', 'comp2 ', 7060, 1 ),
                ( 6957, 'Rahul   ', 'DE1', 'comp3 ', 7060, 1 ),
                ( 6958, 'uday    ', 'TG6', 'comp4 ', 7060, 1 ),
                ( 6959, 'john    ', 'HY7', 'comp5 ', 7060, 1 ),
                ( 6960, 'netaji  ', 'HY5', 'comp6 ', 7060, 1 ),
                ( 6961, 'prakriti', 'GT6', 'comp7 ', 7060, 1 ),
                ( 6962, 'sachin  ', 'KL7', 'comp8 ', 7060, 0 ),
                ( 6963, 'santosh ', 'KK5', 'comp9 ', 7060, 1 ),
                ( 6964, 'Ravi    ', 'PP0', 'comp10', 7060, 1 )
        ) AS x ( custid, custname, deptid, company, parentcustid, enrolled );

And here's how a Recursive CTE would look for it:

WITH yourdad
AS ( SELECT y.custid, y.custname, y.deptid, y.company, y.parentcustid, y.enrolled
     FROM   #yourmom AS y
     WHERE  y.parentcustid IS NULL
            AND y.enrolled = 1
     UNION ALL
     SELECT ym2.custid, ym2.custname, ym2.deptid, ym2.company, ym2.parentcustid, ym2.enrolled
     FROM   yourdad AS yd
     JOIN   #yourmom AS ym2
     ON ym2.parentcustid = yd.custid
        AND ym2.enrolled = 0 )
SELECT *
FROM   yourdad;

There are some Great Posts® written about them:

Adam Machanic
Jeff Moden #1 and #2
The late great Dwain Camps

Hope this helps!


Do empty columns take up space in a table? I have table that holds from very basic info. Just a title and a few date fields.
There's one field called comments which is varchar(4000)
Most of the time we leave it blank, but some times will enter a large amount of data here.
Is this a really bad design? Or is this just slightly inefficient?

I would assume the creating a separate table for this column would be better.

note: this is sql server 2008

It takes minimal space when not used

one bit in the NULL bitmap
two bytes for length (which will be zero when NULL)

The overhead is minimal and optimisation will be premature.

Until you know you have an issue, just keep it in one table. You break KISS by introducing outer joins and add an overhead in querying the data.

See https://stackoverflow.com/questions/3793022/how-to-come-to-limits-of-8060-bytes-per-row-and-8000-per-varchar-nvarchar-valu/3793265#3793265 for more


Updlock hint in stored procedure If someone runs stored procedure below very fast (few thousand execution a second), can variable @amount change while executing and execute update more than one time?

CREATE PROCEDURE [dbo].[PayoutInvestment]
    @userid AS VARCHAR(50)
    ,@idi AS INT
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @amount AS DECIMAL(18, 2) = (
            SELECT amount
            FROM investments
            WHERE userid = @userid
                AND idi = @idi
                AND date_stop IS NULL
            );

    IF (@amount IS NOT NULL)
    BEGIN
        UPDATE investments
        SET date_stop = GETUTCDATE()
        WHERE idi = @idi;

        UPDATE u
        SET balance = u.balance + @amount
        FROM users u
        INNER JOIN investments i ON u.userid = i.userid
        WHERE i.idi = @idi;
    END;

    COMMIT TRANSACTION;
END

I know it works 100% if I do it with updlock hint. But would like to know if it is necessary to do it that way.

CREATE PROCEDURE [dbo].[PayoutInvestment]
    @userid AS VARCHAR(50)
    ,@idi AS INT
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @amount AS DECIMAL(18, 2) = (
            SELECT amount
            FROM investments WITH (
                    ROWLOCK
                    ,UPDLOCK
                    )
            WHERE userid = @userid
                AND idi = @idi
                AND date_stop IS NULL
            );

    IF (@amount IS NOT NULL)
    BEGIN
        UPDATE investments
        SET date_stop = GETUTCDATE()
        WHERE idi = @idi;

        UPDATE u
        SET balance = u.balance + @amount
        FROM users u
        INNER JOIN investments i ON u.userid = i.userid
        WHERE i.idi = @idi;
    END

    COMMIT TRANSACTION;
END

I am using Read Committed isolation level.

Each execution of the stored procedure gets a separate copy of the variable, so that is not the risk you need to worry about. The value of the variable is 'private' to your transaction in the sense that no other concurrent transaction can see it or change its value (they have their own variable).

The concurrency problem you need to worry about is that the data in the database may change between statements, or even while a statement is executing.

Using an update locking hint on the investments table only prevents the touched row from being changed by a concurrent session before your transaction completes. It does not necessarily prevent other changes in the database (such as the user that owns that investment changing or the user's id being changed), nor does it prevent other transactions reading that value.

Rather than adding random locking hints that require an expert detailed analysis to assess (and are still easy to get wrong), it is usually simpler, and more sensible, to think about the level of isolation your transaction requires.

If you need the transaction to have persistent effects as if it were the only transaction running on the system (so eliminating worries about concurrent changes), you could run it at the serializable isolation level. If snapshot isolation (not RCSI!) is available to you, and write conflicts are expected to be rare, that could be a suitable isolation level to use as well. Another serialization-type solution would be to use application locks.

For example, using serializable isolation:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

DECLARE @amount decimal(18,2);

UPDATE dbo.investments
SET date_stop = GETUTCDATE(),
    @amount = amount
FROM dbo.investments AS I
JOIN dbo.users AS U
    ON U.userid = I.userid
WHERE
    I.idi = @idi;

IF @amount IS NOT NULL
BEGIN
    UPDATE dbo.users
    SET balance += @amount
    WHERE userid = @userid;
END;

COMMIT TRANSACTION;

You will need to add error handling to detect and resolve any deadlocks or write conflicts that may occur.

For more information, see my series of articles on SQL Server isolation levels:

SQL Server Isolation Levels : A Series


Getting the error "Can't rename a table because it is published for replication" while it's not I'm trying to rename a table, but I'm getting the error :
"cannot rename the table because it is published for replication".
But this table is not among the published articles.
I'm suspecting it's flagged somewhere as published, because it's possible that someone added it before to publication, and dropped it but it was not unflagged in SQL Server. But I don't know how to confirm / disconfirm this.

How can I solve this problem ?

sp_droparticle will probably do the trick. Details: https://msdn.microsoft.com/en-us/library/ms173832(v=sql.105).aspx


How to audit users connections via ODBC to all dbs in SQL Server 2008 R2 I am not a trained DBA, but I manage a software application that uses SQL Server for the back end. The applications manages "projects" and each project has its own database.

Because of the limitation in reporting through the application, we set up a generic SQL Server user account with read-only permissions to all the project databases. Users utilize that account to connect through ODBC and execute queries.

Now I would like to audit those connections - when they are happening and to which database.

Can anybody assist me with this?

I have administrative access to SQL Server itself. I have a very basic knowledge of SQL syntax. I am completely ignorant on the auditing capabilities of SQL Server, though.

Thanks in advance!

Now I would like to audit those connections - when they are happening and to which database.

If you want to audit on an ongoing basis then a server side trace will do the job.

Below is how you will script out from Profiler

Entire SCRIPT :

/****************************************************/
/* Created by: SQL Server 2014 Profiler          */
/* Date: 06/18/2014  04:52:30 PM         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 64, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 7, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 15, 64, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 7, @on
exec sp_trace_setevent @TraceID, 17, 8, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 26, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 17, 64, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 7, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 64, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 7, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 26, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 64, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 7, @on
exec sp_trace_setevent @TraceID, 13, 8, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 26, @on
exec sp_trace_setevent @TraceID, 13, 35, @on
exec sp_trace_setevent @TraceID, 13, 64, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 7fd54d82-f62b-4c99-8e02-ff0aa8d1d418'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

If you want to just see who is connected now then you can use DMVs to look into

The NULL in dbid (below query) indicates that those queries are Ad Hoc queries.

SELECT cr.DatabaseName
    ,s.session_id
    ,s.host_name
    ,s.program_name
    ,s.client_interface_name
    ,s.login_name
    ,s.login_time
    ,s.nt_domain
    ,s.nt_user_name
    ,c.client_net_address
    ,c.local_net_address
    ,cr.ObjName
    ,cr.Query
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
    ) cr
--where s.nt_user_name = '' -- filter here your user name
where s.session_id <> @@SPID
ORDER BY c.session_id


SSIS Suddenly Requires DCOM Permissions I have recently deployed some SSIS packages. I made a job to run them one after another. However, I am now allowed to use MSDTC. So I chained the jobs together like I have done before, but now I am faced with an error message about DCOM. All Internet research points to insufficient permissions on the MSDTC component, but I am worried this is going the wrong way.

Any idea how I can "reset" the need for MSDTC for this package?

The job has several steps. In the step that ran the package, I had to select "SQL Server" as the package source instead of "SQL Server Package Store". This stopped the use of MSDTC.


How to change job output log location, when using Ola Hallengren's scripts When using Ola Hallengren's SQL Server Maintenance Solution, the job output log is going to a local directory on the SQL server. I was thinking of placing these logs on a fileserver to allow access to more people so they can review the job output.

My questions are:

How can I change this location?
Is there any downside to changing the location?

Thanks!

I’m going to reword your questions a little:

Q: Should I log Agent jobs directly to a remote file server? No, because if you lose network connectivity or the file share goes down, your Agent job could fail. Your Agent job might not require network connectivity, like an update stats job, so that failure would suck.

Q: If I want to centralize Agent job history, how should I do it? Consider logging to a table (and Ola’s scripts support this too.) That way you can centralize the data in whatever method you like, like replication or log shipping. I like logging to a DBA utility database, and then restoring that from all my servers to one central server daily. Then I use union all views to combine the data from all my servers. It’s not up to the second, though.


check listener status other than 1521 I have two listners:

one is running on port 1521 another on 1531.

lsnrctl stat shows status of services running on port 1521 only.

How should I check whether the services running on port 1531 is proper or not.

Listner Entry:

    # listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    ADMIN_RESTRICTIONS_LISTENER = ON

    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        )
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
          (PROTOCOL_STACK =
            (PRESENTATION = GIOP)
            (SESSION = RAW)
          )
        )
      )

    ADR_BASE_LISTENER = C:\app\Administrator

    SID_LIST_PARIVARTAN =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = PARIVARTAN)
          (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
          (PROGRAM = extproc)
          (SID_NAME = PARIVARTAN)
        )
      )

    PARIVARTAN =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1531))
        )
      )

    ADR_BASE_PARIVARTAN = C:\app\Administrator

TNSNAMES Entry

    # tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    ORCL =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = orcl)
        )
      )

    PARIVARTAN =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1531))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = parivartan)
        )
      )

tns ping result:

    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
    (HOST = 127.0.0.1)(PORT = 1531))) (CONNECT_DATA = (SERVICE_NAME = parivartan)))
    OK (10 msec)

Run:

lsnrctl status LISTENERNAME

... where LISTENERNAME is the name of the listener that is listening on port 1531. You can get this name from your $ORACLE_HOME/network/admin/listener.ora file.

For example:

LISTENER1531 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1531))
      )
    )
  )

LISTENER1531 would be the listener name for the above listener.ora entry.

If you need to add databases to the listener, add an entry to the listener.ora, as follows:

SID_LIST_LISTENER1531 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = YOURDB)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
      (SID_NAME = YOURDB)
    )
  )

Obviously you'll need to add your own database and listener names & change the path to your $ORACLE_HOME.

You can reload the listener config with:

lsnrctl reload LISTENER1531

The above works fine for me with a `tnsnames.ora as follows:

YOURDB =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = node1)(Port = 1531))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = YOURDB)
 )
)


Creating a partition on my soft-delete indicator column In SQL Server 2008 R2 I am exploring the use of partitioning tables.

We have a soft-delete scheme which is sometimes an IsDeleted bit NOT NULL and sometimes a DateDeleted date NULL

I want to focus on the first case, the bit.

I am considering partitioning this table on the bit because the table is almost always queried and joined including this column.

How would I go about this process? Is it a bad idea?

I first tried to step through the wizard but the script it generated for me seemed awkward (multiple drops of constraints, creation of a new CI)

The following is a sample script I just created that shows how to create a table that contains an IsDeleted Field.

After the table is created and filled with 10,000 rows, I add a partitioning function and scheme, and some filegroups and files to contain the partitions.  In real life, you would want to create these filegroups and files on dedicated LUNs to take advantage of extra I/Os and piecemeal restore.

I then alter the table so it makes use of the partitioning function, thereby moving the existing rows into the appropriate filegroup.  Since you cannot perform an 'ALTER TABLE...ALTER CONSTRAINT` to alter the primary key to use the partitioning function, you are forced to drop the primary key and recreate it with the new partitioning function.  This does mean the clustered index will be turned into a heap until you can create the new clustered index with the partitioning function.

USE Test;
GO
CREATE TABLE IsDeletedTest
(
    IsDeletedTestID INT NOT NULL CONSTRAINT PK_IsDeletedTest PRIMARY KEY
        CLUSTERED IDENTITY(1,1)
    , IsDeleted BIT NOT NULL CONSTRAINT DF_IsDeletedTest_IsDeleted
                DEFAULT ((0))
);
GO
/* INSERT 10,000 rows into IsDeletedTest */
INSERT INTO IsDeletedTest (IsDeleted) VALUES (CAST(RAND() * 2 AS INT));
GO 10000
ALTER DATABASE Test ADD FILEGROUP IsDeleted0FG;
ALTER DATABASE Test ADD FILE
    (NAME='IsDeletedFile0', FILENAME='C:\SQLServer\Data\IsDeletedFile0.mdf')
    TO FILEGROUP IsDeleted0FG;
ALTER DATABASE Test ADD FILEGROUP IsDeleted1FG;
ALTER DATABASE Test ADD FILE
    (NAME='IsDeletedFile1', FILENAME='C:\SQLServer\Data\IsDeletedFile1.mdf')
    TO FILEGROUP IsDeleted1FG;
ALTER DATABASE Test ADD FILEGROUP IsDeleted2FG;
ALTER DATABASE Test ADD FILE
    (NAME='IsDeletedFile2', FILENAME='C:\SQLServer\Data\IsDeletedFile2.mdf')
    TO FILEGROUP IsDeleted1FG;
GO
/* Create a partition function using a BIT datatype for the valid values
     of 1 and 0 */
CREATE PARTITION FUNCTION IsDeletedPF (BIT)
AS RANGE LEFT FOR VALUES (0,1);

/* Create a partition scheme that implements the partition function */
CREATE PARTITION SCHEME IsDeletedPS AS PARTITION IsDeletedPF
    TO (IsDeleted0FG, IsDeleted1FG, IsDeleted2FG);

/* MOVE the clustered index into the various filegroups
    according to partition scheme */
ALTER TABLE IsDeletedTest DROP CONSTRAINT PK_IsDeletedTest
    WITH (MOVE TO IsDeletedPS(IsDeleted));
GO
ALTER TABLE IsDeletedTest ADD CONSTRAINT PK_IsDeletedTest
    PRIMARY KEY CLUSTERED (IsDeleted, IsDeletedTestID)
    WITH (
                     ONLINE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON,
             FILLFACTOR=100 /* adjust this as necessary */
                 )
    ON IsDeletedPS(IsDeleted);

/* Show the movement of a single row from partition 1 to partition 2 */
SELECT IsDeleted, COUNT(1) FROM dbo.IsDeletedTest GROUP BY IsDeleted;
SELECT O.Name, PS.partition_number, PS.row_count
FROM sys.dm_db_partition_stats PS
    INNER JOIN sys.objects O ON PS.object_id = O.object_id
WHERE O.Name = 'IsDeletedTest';
UPDATE dbo.IsDeletedTest
    SET IsDeleted = CASE WHEN IsDeleted = 0 THEN 1 ELSE 0 END
    WHERE IsDeletedTestID = 10000;
SELECT IsDeleted, COUNT(1) FROM dbo.IsDeletedTest GROUP BY IsDeleted;
SELECT O.Name, PS.partition_number, PS.row_count
FROM sys.dm_db_partition_stats PS
    INNER JOIN sys.objects O ON PS.object_id = O.object_id
WHERE O.Name = 'IsDeletedTest';
GO

DROP TABLE IsDeletedTest;
DROP PARTITION SCHEME IsDeletedPS;
DROP PARTITION FUNCTION IsDeletedPF;
ALTER DATABASE Test REMOVE FILE IsDeletedFile0;
ALTER DATABASE Test REMOVE FILE IsDeletedFile1;
ALTER DATABASE Test REMOVE FILE IsDeletedFile2;
ALTER DATABASE Test REMOVE FILEGROUP IsDeleted0FG;
ALTER DATABASE Test REMOVE FILEGROUP IsDeleted1FG;
ALTER DATABASE Test REMOVE FILEGROUP IsDeleted2FG;

I just ran this and got the following results:

As you can see, the table had an almost perfect distribution of rows in the IsDeleted = 0 and IsDeleted = 1 state.  Updating a row from 0 to 1 resulted in the row moving to the other partition.

I hope this helps show how to alter a table to take advantage of partitioning on an IsDeleted soft-delete BIT field.

When I ran the wizard to partition this table, with the option to script to a new query window, I got this code:

USE [Test]
GO
BEGIN TRANSACTION
ALTER TABLE [dbo].[IsDeletedTest] DROP CONSTRAINT [PK_IsDeletedTest];

ALTER TABLE [dbo].[IsDeletedTest] ADD  CONSTRAINT [PK_IsDeletedTest] PRIMARY KEY NONCLUSTERED
(
    [IsDeletedTestID] ASC
) WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , IGNORE_DUP_KEY = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    )
ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_IsDeletedPS_635194638000795374] ON [dbo].[IsDeletedTest]
(
    [IsDeleted]
) WITH (
    SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    )
ON [IsDeletedPS]([IsDeleted])

DROP INDEX [ClusteredIndex_on_IsDeletedPS_635194638000795374] ON [dbo].[IsDeletedTest]

COMMIT TRANSACTION

As far as I can tell, this code looks like it doesn't work.  It drops the PK, then recreates it, without using the partitioning function.  It then creates a clustered index, using the partitioning function, but then immediately drops it!


RMAN Control File Information I have daily full backups going on for my Oracle 12c databases on linux. Daily retention on disk is 6 days.

This is what happens:

Full backup happens at location

  /orcl_bkup/backup_full

After each day's full backup gets over, I make sure that the above mentioned path only contains that day's backup pieces in that path and the older backups will be moved to another folder

  /orcl_bkup/old_bkup

The reason for the above mentioned movement of backup pieces is necessary for my OS team for some activity at their end.

My question is that, in this scenario, will my control file lose the information of older backups? Do I need to do catalog start with the /orcl_bkup/old_bkup and catalog the backup location to delete the obsolete backups?

Yes, you need to catalog the backups at their new location.

You should run crosscheck also on the backups, so the entries pointing to the old location become EXPIRED.


Best practices on dealing with devs that want to use XP_CMDSHELL to BCP OUT This is not a technical question. I would like to know the best practice to use  in a place where devs want to have SA for everything.

The most recent event is:

Devs want to use BCP OUT to export data to a txt file really fast.

What should I think about it? Should I say "why you need this to be THIS fast?"

I'm sure there's a lot of alternatives to export data to .TXT file. they can be slower but it does the job.

We don't use Windows login here, only logins, so I can't create a proxy account (I'm reading something about this).

Note: They use Delphi to write application program code.

There are two things at play here

Data security
Server health

For 1, management has to be okay with any developer walking out the door with whatever data they export to a text file. This might be sensitive PII, it might be protected IP, or just customer lists that a competitor would want.

For 2, any one of these developers that has sysadmin can change settings, shut down the server, take backups, run DBCC commands, and a whole bunch of other stuff. If management is going to let them do that, then management and the developers have to be on the on call rotation to fix things when a developer with sysadmin messes something up.

Also, xp_cmdshell doesn't just write files out, it can interact with the file system, executables, and nearly anything else you can dream of. You could use it to map a network drive to a DropBox folder and... Well, you get the idea.

BCP doesn't necessarily need xp_cmdshell to work. Maybe time to talk about the process behind that.

  Permissions

  A bcp out operation requires SELECT permission on the
  source table.

  A bcp in operation minimally requires SELECT/INSERT permissions on the
  target table. In addition, ALTER TABLE permission is required if any
  of the following is true:

Hope this helps!


Restoring Individual MyISAM Tables I'm using Xtrabackup to perform a MySQL DB restore.

I found some problems when I'm trying to restore individual tables: tables with InnoDB engine can be restored without any problem but I can't find a way to backup and restore MyISAM tables.
Is there a way to restore MyISAM individual tables with Xtrabackup or similar products?

To restore individual MyISAM tables from XtraBackup:

Copy table.{frm,MYD,MYI} files to MySQL database directory e.g /var/lib/mysql/sakila
Execute FLUSH TABLES (yes, mysqld may be running)
Check the restored version is available on MySQL instance with SHOW TABLES and selecting rows from them.


Replication with Always On availability groups I have a three server set up:

SQL01 – Primary server
SQL02 – Secondary server
SQL04 – Remote distribution server
SQL04 – Subscribe

SQL01 / SQL02 DB's are part of basic availability groups and i am trying to configure transaction replication of some of these databases to SQL04.

SQL04 is the remote distributor.

I have been following this Microsoft guide.

My issues comes at the validation of the replica hosts as publishers sproc ( the final step!)

USE Distribution;
GO
DECLARE @redirected_publisher sysname;
EXEC sys.sp_validate_replica_hosts_as_publishers
    @original_publisher = 'SQL01',
    @publisher_db = 'DB',
    @redirected_publisher = 'AG-listener';

I receive the error stating read access is not enabled, the same error which is stated in the MS guide, but i cannot work out how to step over it!

error:

  The query at the redirected publisher 'SQL02' to determine whether there were sysserver entries for the subscribers of the original publisher 'SQL01' failed with
          error '976', error message 'Error 976, Level 14, State 1, Message:
  The target database, 'db',
          is participating in an availability group and is currently not accessible for queries.

  Either data movement is suspended or the availability replica is not enabled for read access.

  To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.

  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.'.

The problem is that procedure only works if read access is enabled for the secondaries and, as per the documentation, Basic Availability Groups do not allow readable secondaries. You need to manually perform the checks that this procedure normally handles as per the doco:

  This is expected behavior. You must verify the presence of the subscriber server entries at these secondary replica hosts by querying for the sysserver entries directly at the host.

Basically, connect to each of your replicas and run this query:

SELECT * FROM sysservers

Make sure that your replication subscribers (SQL04) are listed in sysservers on each AG replica. If they are then you're fine.


STATISTICS IO difference for heap and clustered index Assume we have two tables with data:

create table heap (value int);
create table clust (value int primary key);
insert into heap values (1);
insert into clust values (1);

By inspecting their storage statistics

select obj.name, st.alloc_unit_type_desc, st.index_level, st.page_count
from (values ('heap'), ('clust')) obj(name)
    join sys.indexes ix on ix.object_id = object_id(obj.name)
    cross apply sys.dm_db_index_physical_stats(
        db_id(), ix.object_id, ix.index_id, NULL, 'DETAILED') st;

select obj.name, au.total_pages, au.used_pages, au.data_pages
from (values ('heap'), ('clust')) obj(name)
    join sys.indexes ix on ix.object_id = object_id(obj.name)
    join sys.partitions p on p.object_id = ix.object_id and p.index_id = ix.index_id
    join sys.allocation_units au on au.container_id = p.partition_id;

one can see that both occupy the same number of pages:

name  alloc_unit_type_desc index_level page_count
----- -------------------- ----------- ----------
heap  IN_ROW_DATA          0           1
clust IN_ROW_DATA          0           1

name  total_pages  used_pages  data_pages
----- ------------ ----------- -----------
heap  2            2           1
clust 2            2           1

In this case why statistics io

set nocount on;
set statistics io on;
declare @cnt int;
select @cnt = count(1) from heap;
select @cnt = count(1) from clust;
set statistics io off;

show that scanning the clustered index compared to scanning the heap takes one extra logical read?

Table 'heap'. Scan count 1, logical reads 1...
Table 'clust'. Scan count 1, logical reads 2...

What this extra logical read is?

P.S.
My question is not about "clustered index vs heap performance" or query tuning. I'm trying to better understand things involved into STATISTICS IO reported for clustered index scan.

The example is for SqlServer 2014

  Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

I also tried it on SqlServer 2008 R2

  Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)

and result is the same (though one can not use sys.dm_db_index_physical_stats on 2008 R2 this way).

Accessing either a Clustered Index or Non-Clustered Index requires traversing that b-tree structure. For some reason, on scan operations there seems to be one extra logical read. In your case you have a single page, which is the 2 logical reads. If you had enough rows to fill up enough data pages that would in turn require another level within the b-tree index structure, then you would see an additional logical read.

Heaps, by definition, have no index (b-tree) structure. Since you have one data page, the operations only need that one logical read. In such a simplistic example it would appear to be less work than the Clustered Index approach, but as soon as you get a few more data pages then you will start to see a difference since the b-tree structure will allow for going directly to appropriate data pages while the Heap still has to check all of the pages.

For example, I have a test table with a structure of:

CREATE TABLE [dbo].[GuidPkAsUI](
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [PK_GuidPkAsUI] PRIMARY KEY CLUSTERED,
    [InsertTime] [datetime] NOT NULL
                            CONSTRAINT [DF_GuidPkAsUI_InsertTime]  DEFAULT (getdate()),
);

It has 767,968 rows in it via:

INSERT INTO [dbo].[GuidPkAsUI] ([ID])
  SELECT TOP (767968) NEWID()
  FROM   master.sys.all_columns ac1
  CROSS JOIN master.sys.objects so1;

I copied it to a new table that is the same structure and data, but missing the two constraints (i.e. no Clustered Index) using the following:

SELECT *
INTO dbo.GuidPkAsUIheap
FROM dbo.GuidPkAsUI;

The following queries:

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.GuidPkAsUIheap'),
                                             0, NULL, 'DETAILED');

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.GuidPkAsUI'),
                                             1, NULL, 'DETAILED');

show that GuidPkAsUIheap has 1 level (with 3135 data pages) and GuidPkAsUI has 3 levels (with 3135 data pages, 20 index pages on one level -- intermediate, and 1 index page on another level -- root, totaling 3156 pages).

The following queries:

SET STATISTICS IO ON;
SELECT COUNT(1) FROM dbo.GuidPkAsUIheap;
SET STATISTICS IO OFF;
-- 3135

SET STATISTICS IO ON;
SELECT COUNT(1) FROM dbo.GuidPkAsUI;
SET STATISTICS IO OFF;
-- 3157

shows that GuidPkAsUIheap requires 3135 logical reads (the number of data pages) while GuidPkAsUI requires 3157 logical reads (the number of data and index pages plus one). So here the logical reads for the Clustered Index are still higher than for the Heap.

I then rebuilt the tables via:

ALTER TABLE [dbo].[GuidPkAsUIheap] REBUILD;
ALTER TABLE [dbo].[GuidPkAsUI] REBUILD WITH (FILLFACTOR = 100);

Running the SELECT * FROM sys.dm_db_index_physical_stats queries above again shows the Heap to be the same but the Clustered Index now has only 10 intermediate index pages instead of 20.

Running the SELECT COUNT(1) queries above again shows the same 3135 logical reads for the Heap and 3147 logical reads for the Clustered Index (all data and index pages plus one).

Now, let's find one specific row:

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUIheap WHERE [ID] = '93359759-193F-4CBF-B9F6-738475F8488E';
SET STATISTICS IO OFF;
-- 3135

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUI WHERE [ID] = '93359759-193F-4CBF-B9F6-738475F8488E';
SET STATISTICS IO OFF;
-- 3

The Heap still takes 3135 logical reads. But the Clustered Index takes a mere 3 logical reads: 1 for the root index page, 1 for the next level index page, and 1 for the leaf level / data page.

Now let's force a scan as we look for a single row:

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUIheap WHERE CONVERT(CHAR(36), [ID]) = '98331062-8BF3-4FAE-98B4-204D0DE06FE1';
SET STATISTICS IO OFF;
-- 3135

SET STATISTICS IO ON;
SELECT * FROM dbo.GuidPkAsUI WHERE CONVERT(CHAR(36), [ID]) = '98331062-8BF3-4FAE-98B4-204D0DE06FE1';
SET STATISTICS IO OFF;
-- 3147

Here we get the same logical reads that the COUNT(1) queries get.


SQL Server collation mismatch reducing the amount of data visible to an application? One of our servers died recently, it was rebuilt with a wrong collation and so it looks as follows:

SQL Server - SQL_Latin1_General_CP1_CI_AS
master db -  SQL_Latin1_General_CP1_CI_AS
model db -   SQL_Latin1_General_CP1_CI_AI
msdb db - SQL_Latin1_General_CP1_CI_AI
temp db - SQL_Latin1_General_CP1_CI_AS
every user db - SQL_Latin1_General_CP1_CI_AI

The databases are accessible, my question is: will the data visible to users via an app be limited because of the mismatching collation? Some users have said that they think they have lost a lot of data even though the database was restored from a very recent backup.

It is SQL Server 2008 R2 with SP3 on Windows Server 2012 R2 (compatible only due to service packs)

Thank you

will the data visible to users via an app be limited because of the mismatching collation?

Assuming you are speaking of scenarios that do not error (because an error does not cause people to wonder if something is missing), but instead just return data that might not be ALL of the expected data, then it depends on how the queries are being done. If ALL of the following conditions are true, then yes, some data can be filtered out now when it wasn't before:

a query uses a temporary table (local or global)
the Collation for string columns in the CREATE TABLE #TempTable statement did not explicitly specify the Collation via the COLLATE keyword
the query is filtering at least one such string column in the temporary table on a string literal or local variable
there is an accent mismatch between the data in that column and the string used for filtering (in the literal or variable)

Meaning, previously someone could have executed:

SELECT * FROM #TempTable WHERE Name = 'a';

and gotten back rows containing the following: A a À Á Â Ã Ä Å à á â ã ä å. That same query, with the new tempdb Collation, will now return only rows containing: A a.

Also, outside of the unexpected filtering that is happening in your user-facing code, there are other potential problems waiting for you. If master is a different Collation, then names of system-level entities (e.g. Databases, Logins, etc) will behave outside of expectations and differently than the rest of the system and could cause odd behavior similar to what your users are reporting. Meaning, if you have accents in Database and/or Login and/or variable/parameter/cursor names and have code that works because it is expecting accent-insensitive comparisons, then you / your application might experience what your users are experiencing (though it could take a while to notice, and even longer to debug). AND, it is assumed that all four system DBs are the same Collation, and some stored procedures and views in msdb JOIN to master on string columns and do break in this scenario. Hence this does really need to be fixed.

SO, since it is just the system DBs that are "incorrect" (it is best to have master and msdb match as there are some stored procedures that JOIN between them and error when their Collations don't match), you should change only those back to the expected Collation of SQL_Latin1_General_CP1_CI_AI. You can do this easily enough by using SETUP.EXE /ACTION=Rebuilddatabase. It allows you to change just the system DBs and does not touch user Databases.

Also see the MSDN documentation for: Rebuild System Databases and Set or Change the Server Collation.

At the very least the command-line should look like (but all as one line, or you need to use ^ at the end of each line in order to continue the command on the following line):

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME={InstanceName}
      /SQLSYSADMINACCOUNTS={accountsToAddToSysadminRole}
      /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI [/otherOptions]

For this situation you do not need to use the other recommendation of the undocumented -q switch for sqlservr.exe. That option rebuilds ALL databases, and there is no need to make modifications to databases that are already correct. The less you touch (especially with an undocumented feature) the better off you are.


How do I get the corresponding row for a RowsetID? We're faced with the following error when a query runs against a partitioned table with Read-Only file groups:

  The index "ix_my_index" for table "dbo.my_table" (RowsetId 72057600379912192) resides on a read-only filegroup ("MyFileGroup"), which cannot be modified

How would I identify which row the query is trying to modify from the RowsetID?

I know sysrowsets used to exist, but doesn't seem to in SQL Server 2012.

That looks like a partition_id to me, not "a row." You can start to get an idea of what values might be stored in that partition through the following:

SELECT partition_number
  FROM sys.partitions
  WHERE partition_id = 72057600379912192;

Then you'll have to look at the partition function / scheme to tell where the boundaries are for that specific partition.

I don't think sys.sysrowsets is going to tell you anything that sys.partitions won't. It's unfortunate that the product interchanges words like partition_id, hobt_id, and RowsetId so carelessly.


SQL Server 2008 R2 service packs Are the SQL Server service packs 'cumulative' or 'incremental'?

The scenario is that we have a couple of instances running on SQL Server 2008 R2 RTM and we have been asked to patch it up to SP3 level. I'm not sure if I just apply SP3 or do I need to do SP1 - SP2 - SP3

SQL Server 2008 R2 service packs are cumulative and can be used to
  upgrade all releases of SQL Server 2008 R2 to Service Pack 3.

From http://www.microsoft.com/en-US/download/details.aspx?id=44271

Given the documentation, you should be able to go directly from SP0 to SP3.


Can I use Azure Data Lake in place of Data Warehouse to separate Compute and Storage of RDBMS analytics I am from a telemetry data engineering background and am looking for some help on a use-case I am not familiar with as to whether my proposed architecture is practically feasible in Azure. The use-case is that I am trying to build some intelligence from a relational database that is updated regularly.
Currently, a data warehouse is in place which uses ADF to copy database tables in 3NF to some staging tables. The staging tables are then used to upsert to their respective tables in the 'Data Warehouse'. No denormalisation has been done. This has been pushed to the end-user to do in PowerBI. I inherited this.
The solution is expensive and places high demand on the end-user analysts. I wanted to use a Kimball style approach and have a fact table with some dimension tables. In order to reduce costs, I want to do this in Azure Data Lake and then use DataBricks to carry out the denormalisation and store the resulting outputs in a separate zone in Data Lake. I would then take the daily snapshots of the database tables and update the fact and dimension tables where data has changed/been added. (I still need to determine how to determine the daily changes without a watermark in the source tables).
I do not see any reference architectures where this is done. It seems the data is always put into Synapse or SQLDB, even if it does make a stop in Data Lake. In order to reduce the costs would my approach be ok where I use Data Lake and DataBricks for everything (Staging, denormalisation, and serving the fact/dim tables)? The analysts would then have a much simpler model to connect up to PowerBI and use Dataflows to carry out their aggregations.
Any advice would be greatly appreciated.

Your approach sounds reasonable. Many of the analytics-oriented DBMS allow access to data lake files in situ. You're just reproducing a similar feature in user-space.
IIRC ADF's data flow uses a DataBricks cluster under the covers. This may be a simpler implementation than writing dataframes in a notebook and getting it to run in 'Bricks.
I think most opt for a load then process approach because SQL makes such manipulation easy and Synapse is optimised to perform it.
If your source database is SQL Server you may want to consider Change Data Capture for watermarks / delta tracking.


Find Events between Dates I have table with following data

id   start_date end_date
1    2018-01-01 2018-01-15
2    2018-01-11 2018-01-16
3    2018-01-03 2018-02-10
4    2018-01-01 2018-03-15

I want to list events based on months. If I am on 2018-01 it gives results proper but when I am in 2018-02 it does not give me entry 4 which spread between 2018-01-01 to 2018-03-15

SELECT * FROM `table` `t`
WHERE  (start_date >='2018-01-01' AND start_date <='2018-01-31')
OR     (end_date >= '2018-01-01'  AND   end_date <='2018-01-31')

Any help

What about using BETWEEN by taking the first and last month of the ranges:

SELECT *
FROM `table` `t`
WHERE
  '2018-01-01' BETWEEN
     DATE_FORMAT(start_date ,'%Y-%m-01') AND
     LAST_DAY(end_date)

As this won't use indexes an alternate is to use generated columns:

ALTER TABLE `table`
ADD start_month DATE GENERATED ALWAYS AS (DATE_FORMAT(start_date ,'%Y-%m-01')),
ADD end_month DATE GENERATED ALWAYS AS (LAST_DATE(end_date)),
ADD INDEX start_end_month(start_month,end_month)

Then the query will become the following and use the start_end_month index:

SELECT *
FROM `table` `t`
WHERE
  '2018-01-01' BETWEEN
     start_month AND end_month


SQL Server 2005 - Best way to constraint a Datetime field as Date Whereas:

  date field type starting with SQL Server 2008

Given a table:

CREATE TABLE dbo.MyTable
(
    Code int NOT NULL,
    DateCode datetime NOT NULL
)
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Code] ASC, [DateCode] DESC
);

Which is the best way to CONSTRAINT that DateCode is only a Date?

It must allow dates without time part.

2017-01-02
2017-01-02 00:00:00.000
20170102

By now I'm using:

ALTER TABLE dbo.MyTable ADD CONSTRAINT [CHK_MyTable_DateCode]
    CHECK ([DateCode] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateCode])));

But, I wonder if there is a best/short way to accomplish it.

You can simplify it a little bit, since SQL Server knows with datetime that it can treat the value as the number of days since 0. I prefer to use '19000101' over zero, since it's slightly less cryptic what's going on:

ALTER TABLE dbo.MyTable
ADD CONSTRAINT CHK_MyTable_DateCode
CHECK (DateCode = DATEDIFF(DAY, '19000101', DateCode));

However, I find it an odd choice to scold users who might use built-ins like GETDATE() or parameters like @OrderDate which they don't realize contains a time component. So a slightly cleaner way to solve this would be with a trigger:

CREATE TRIGGER dbo.MyTable_RoundCodeDate
ON dbo.MyTable
INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.MyTable(Code, DateCode)
    SELECT Code, DATEDIFF(DAY, '19000101', DateCode)
    FROM inserted;
END
GO

Using an INSTEAD OF trigger is not popular with a few people here, but I think it is better to massage those values before inserting than after. PK will be handled the same as without a trigger - violations will still be caught at insert time (with an after trigger, though, you wouldn't hit the violation until after the trigger code runs, which theoretically at least means more logging). In fact you could add a GROUP BY to the trigger to silently ignore duplicates from the source statement, e.g. INSERT ... VALUES(1,GETDATE()), (1,CURRENT_TIMESTAMP);.


SQL Identify Missing Data This is getting the best of me!!

I created calendar table (table1) loaded with all days for a period of 5 years.  Among other things the table includes the date, the day type (weekend, holiday etc.)  Important fields here are table1.date and table1.type

I have a second table (table2) that records days worked.  Important fields here are table2.date and table2.employee

I want to identify which dates were not worked by an employee excluding any days that might be a weekend or holiday since those are not required work days.

table1
=======
2/10/20 Weekday
2/11/20 Weekday
2/12/20 Weekday
2/13/20 Weekday
2/14/20 HOLIDAY
2/15/20 Weekend
2/16/20 Weekend

table2
=======
2/10/20   john
2/10/20   jimmy
2/10/20   mary
2/11/20   john
2/11/20   jimmy
2/11/20   mary
2/12/20   john
2/12/20   mary
2/13/20   jimmy
2/13/20   mary
2/14/20   jimmy

Query result should show days that do not exist in table2.

Jimmy 2/12/20
John 2/13/20

You can do this using the EXCEPT operator to remove the weekdays worked from a CROSS JOIN query that returns all possible weekdays worked by each employee.

SELECT DISTINCT wr.employee, c.work_date
FROM cal c
CROSS JOIN work_record wr
WHERE c.day_type = 'Weekday'
EXCEPT
SELECT employee, date_worked
FROM work_record wr

DDL for creating the tables and data:

CREATE TABLE [cal](
    [work_date] [date] NULL,
    [day_type] [varchar](20) NULL
) ON [PRIMARY]
GO

CREATE TABLE [work_record](
    [date_worked] [date] NULL,
    [employee] [varchar](20) NULL
) ON [PRIMARY]
GO

INSERT [cal] ([work_date], [day_type])
VALUES (CAST(N'2020-02-10' AS Date), N'Weekday'),
(CAST(N'2020-02-11' AS Date), N'Weekday'),
(CAST(N'2020-02-12' AS Date), N'Weekday'),
(CAST(N'2020-02-13' AS Date), N'Weekday'),
(CAST(N'2020-02-14' AS Date), N'HOLIDAY'),
(CAST(N'2020-02-14' AS Date), N'Weekend'),
(CAST(N'2020-02-14' AS Date), N'Weekend')

INSERT [work_record] ([date_worked], [employee])
VALUES (CAST(N'2020-02-10' AS Date), N'john'),
(CAST(N'2020-02-10' AS Date), N'jimmy'),
(CAST(N'2020-02-10' AS Date), N'mary '),
(CAST(N'2020-02-11' AS Date), N'john '),
(CAST(N'2020-02-11' AS Date), N'jimmy'),
(CAST(N'2020-02-11' AS Date), N'mary '),
(CAST(N'2020-02-12' AS Date), N'john '),
(CAST(N'2020-02-12' AS Date), N'mary '),
(CAST(N'2020-02-13' AS Date), N'jimmy'),
(CAST(N'2020-02-13' AS Date), N'mary '),
(CAST(N'2020-02-14' AS Date), N'jimmy')


SQL server restore readonly file group without backup from .ndf file We have SQl Server database with ReadOnly file group. Common file groups except readonly are backuped regulary.

We restore the database from backup but readonly file group has not restored, its *.ndf file is not corrupted. Is it posiible to attach this file and reset file status RECOVERY_PENDING?

The example script:

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')
    DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1', FILENAME='D:\data\TestDB_Part1.mdf',
SIZE=3, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2', FILENAME ='D:\data\TestDB_Part2.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part3
(NAME = 'TestDB_Part3', FILENAME ='D:\data\TestDB_Part3.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part4
(NAME = 'TestDB_Part4', FILENAME ='D:\data\TestDB_Part4.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 ),
FILEGROUP TestDB_Part5
(NAME = 'TestDB_Part5', FILENAME ='D:\data\TestDB_Part5.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1 );
GO
USE TestDB;
GO

CREATE PARTITION FUNCTION TestDB_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (10,20,30,40);
GO

USE TestDB;
GO
--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2, TestDB_Part3, TestDB_Part4, TestDB_Part5);
GO

--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (ID);
GO

--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable
ON TestTable(ID)
ON TestDB_PartitionScheme (ID);
GO

--- Step 6 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 4
VALUES (32,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (42,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 5
VALUES (52,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 3
VALUES (22,GETDATE());
GO

--- Step 7 : Test Data from TestTable
SELECT * FROM TestTable;
GO

--- Step 8 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO

ALTER DATABASE [TestDB] MODIFY FILEGROUP [TestDB_Part2] READONLY
GO

-- Backup rw and ro separately
BACKUP DATABASE [TestDB]
READ_WRITE_FILEGROUPS
TO  DISK = N'D:\data\TestDB_RW.bak'
WITH NOFORMAT, INIT,
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- list backup files
RESTORE FILELISTONLY from disk =  N'D:\data\TestDB_RW.bak'

RESTORE DATABASE [TestDB2]
read_write_filegroups
  FROM  DISK = N'D:\data\TestDB_RW.bak'  WITH  FILE = 1,
      MOVE N'TestDB_Part1' TO N'D:\data\TestDB2_Part1.ndf',
      MOVE N'TestDB_Part2' TO N'D:\data\TestDB2_Part2.ndf',
      MOVE N'TestDB_Part3' TO N'D:\data\TestDB2_Part3.ndf',
      MOVE N'TestDB_Part4' TO N'D:\data\TestDB2_Part4.ndf',
      MOVE N'TestDB_Part5' TO N'D:\data\TestDB2_Part5.ndf',
      MOVE N'TestDB_log' TO N'D:\data\TestDB2_log.ldf',
  REPLACE, NOUNLOAD,  STATS = 10
GO

note that when you ran the RESTORE FILELISTONLY look at the column IsPresent and for the readonly file it shows 0
this mean that this file does not exists in the backupset.

anyway this file cannot be restored from that backup set because it does not exists there, so you must take it from a full backup (or just a file backup)

the only option to reset the "Archive_Pending" is by restore that element (either file group, file or page) and make it consistent with the database LSN (log sequence number)

the term attache is only for new attached databases and cannot be used with files.

so you can take it from full backup or from file backup (any backup that this file is "IsPresent" = 1 )

to continue your DEMO SCRIPT

-- take backup of that RO file group
BACKUP DATABASE [TestDB]
FILEGROUP = N'TestDB_Part2'
TO  DISK = N'D:\data\File2Backup.bak' ;
GO

-- restore the RO file / FG
RESTORE DATABASE [TestDB2] FILE = N'TestDB_Part2' FROM  DISK =     N'D:\Data\File2Backup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

and thats it.... your DB is up and running

I hope this has been informative for you, please let me know if you have any more questions.


Looking for an efficient way to clone tens of millions of database records A laboratory I work in is attempting to clone the open, biochemical database managed by PubChem found here.  (Please don't ask why, I am not in charge of this project, I am simply tasked with the cloning of the database) But their database contains tens of millions of records and the script that I currently have running is using the "READ DATA INFILE" instruction of MySQL to write the 720 thousand csv files we have of their database into our MySQL database... one by one.  At the current rate, the sheer amount of data being saved is going to make this script take roughly 240 days to finish, and we don't have that much time. The questions I have are

Is there a generally accepted way to quickly enter vast amounts of csv files into
mysql (over 650 GB of data)
If not, is there a database system better suited for this sort of task, perhaps one designed specifically for big data? (I'm no database expert, so forgive me if this question makes no sense)

Simple approaches:

If your tables are indexed, drop the indexes and put them back after you load the data.  It's generally going to be faster for MySQL to build the index on a table in one pass than to continually index the incoming data as you add it.
Run more than one copy of your script, each with a subset of the data source files, doing inserts in parallel
Enable compression if you're using InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-compression.html  Typically we think of compression as making things smaller with a trade-off in speed... but my experience with InnoDB compression is that it can actually perform faster when you're dealing with a server that is blocking on I/O but has spare CPU capacity, because you can trade some of one for some of the other -- the time saved by not having to do disk read/write improves performance more than the extra CPU cycles for compress/decompress harms performance.

At some point, of course, you're always going to max out either CPU or I/O on your server, but running more than one process in parallel will help to some extent, especially if the rows aren't all going into just one table.  If they are (I'm not familiar with the data set in question) then you do have a potential problem there, since some of these approaches won't help, but if not, then once you max your machine, you still have the option of marshaling multiple servers to do the work of initially creating the tables, and then copy them over to the final destination when you're done.

With MyISAM tables, the table spaces are intrinsically transportable from server to server.  You can create the tables on one or more entirely different servers; run FLUSH TABLES WITH READ LOCK; and then (while keeping the tables locked by not closing your connection) you can actually copy the .frm and .MY* files from one server to the other.  When you run SHOW TABLES after copying the files into the directory for the appropriate database, and setting the permissions, they just magically appear and are accessible, because MyISAM does not have an internal data dictionary.  Everything is in those files.

In MySQL 5.6, InnoDB tablespaces became transportable also; it's not quite as simple as MyISAM, because the files have to be prepared first: http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html ... but still potentially a viable way to get more work done in parallel.

You could also also leverage cloud servers to create and index these tables for you and then copy the tablespace files back to your production system, assuming you have sufficient bandwidth available to actually transfer the files... and you're willing to gamble just a little bit... the spot market price (for idle cloud capacity) for some very serious hardware (multi-core, with high-capacity SSDs, etc.) can be less than 1/10th of the on-demand (normal) price... if you go that route, you have to automate the process to move the completed files off of the cloud server as expeditiously as possible, since the machine can be interrupted at any time if the spot price goes above the amount you're willing to pay.


PostgreSQL How do I convert struct text to plain *char in C functions? I'm using my internal C function which doesn't know about postgresql's text struct, how do I pass text argument when char * expected?

#include <stdio.h>
#include <string.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(fun);

Datum fun(PG_FUNCTION_ARGS)
{
    text    *str1, *str2;

    str1 = PG_GETARG_TEXT_P(0);
    str2 = PG_GETARG_TEXT_P(1);

    FILE *fp = fopen("/tmp/fun.log", "w");
    fprintf(fp, "%s\n", VARDATA_ANY(str1));
    fprintf(fp, "%s\n", VARDATA_ANY(str2));
    fclose(fp);

    PG_RETURN_INT32(0);
}

Here's what xxd sees in log file:

0000000: 666f 6f74 6261 6c6c c88d 7409 70d3 6b09  football..t.p.k.
0000010: 080a 7a6f 6f6d 0a                        ..zoom.

After executing:

SELECT * FROM fun('football','zoom');

Why junk after football?

The data pointed to by the text structure isn't null-terminated, so you can't use it with C "string" functions directly.

To determine the length of the text, you can use VARSIZE(your_text)-VARHDRSZ.

In your example, you could use fwrite instead of printf to output the string (passing in the length), or copy the text to a char* and terminate it manually if you intend to use C string functions.

Here's a sample of how either could be done:

#include <stdio.h>
#include <string.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(fun);

size_t log_pgtext(FILE *to, text *what)
{
    return fwrite(VARDATA(what), 1, VARSIZE(what)-VARHDRSZ, to);
}

char *dup_pgtext(text *what)
{
    size_t len = VARSIZE(what)-VARHDRSZ;
    char *dup = palloc(len+1);
    memcpy(dup, VARDATA(what), len);
    dup[len] = 0;
    return dup;
}

Datum fun(PG_FUNCTION_ARGS)
{
    text    *str1, *str2;

    str1 = PG_GETARG_TEXT_P(0);
    str2 = PG_GETARG_TEXT_P(1);

    FILE *fp = fopen("/tmp/fun.log", "w");

    log_pgtext(fp, str1);
    fprintf(fp, "\n");

    char *str = dup_pgtext(str2);
    fprintf(fp, "%s\n", str);
    pfree(str);

    fclose(fp);

    PG_RETURN_INT32(0);
}

Reference documentation for PostgreSQL 9.2.


Efficiciency of multiple small triggers versus one large trigger on SQL Server I have been given the honourable task of adding - very carefully! - a few triggers to our ERP system's database, to get data - "in real time" - from the ERP system to other pieces of software running at our company. The triggers will not make any changes to the data, just pick a few fields from the inserted/updates rows, roll them up in a XML message and send them to a message queue. There will be no JOINs, either.
(Whether or not this is a good idea in to begin with is a question for another time.)

I can imagine writing several triggers, one for each scenario. Or I could could write two large triggers, one for INSERT, and for UPDATE. With enough force, I could probably even boil that down to just one huge trigger that runs both on INSERT and on UPDATE.

From a testing/debugging/maintenance point of view, several self-contained triggers have obvious advantages: Each one can be written, tested and enabled/disabled individually. Each trigger would check if whatever just happened to the respective table is relevant to that trigger, if so, it would do its thing, and if not, it would just quit.

However, I am worried that multiple triggers might cause performance issues. The tables I am supposed to attach these trigger(s) see frequent INSERT and UPDATE queries as part of normal operation[1]. (OTOH, the load on the database server is relatively mild so far.)

Are there any general guidelines on which approach is better?

Thank you very much for any information you might be able to share,
Benjamin

[1] Frequent in this case means "a couple of dozen transactions per minute on a busy day".

Community wiki answer:

You already sound like you know the right answer. The best approach is the one that has the trigger code run the fastest with the least amount of code. If it were me, I'd have one trigger per action. Will there be duplicate code - absolutely - but it saves you for when anything needs to be changed and can be customized per situation.

You could try both and then simulate load to see which you like/performs the best. Aaron Bertrand found there was observable additional overhead with multiple triggers performing the same operations, but YMMV.

However, a couple dozen transactions per minute doesn't sound like a tremendous amount of activity. Maintainability 'might' be able to justify a small amount of overhead. There are numerous advantages to using separate 'single-function' triggers. As you indicated, they have a single purpose and can be individually tested (by different people), enabled, disabled. Isolating the processing into separate 'single-function' triggers 'might' reduce the risk involved in future maintenance.

Of course, final testing of the individual triggers cannot be by different people, as you need to ensure the triggers work properly in unison. And, while future maintenance of smaller chunks of code is logically easier, you cannot ignore the other triggers when making future changes, as something that causes no issues in trigger A could completely mess up what trigger C is doing.