Sybase ASE to Microsoft SQL Server Replication

We ended up using the SymmetricDS application and have been moving applications over to MSSQL gradually. So far, so good! Thanks for the help everyone.


PostGIS error building with `make` in ./extensions: nothing to be done for 'comments'

You need xsltproc,

16:02 < robe2> EvanCarroll: Looks like you are probably missing xsltproc

Install xsltproc (instructions in Ubuntu/Debian)

sudo apt-get install xsltproc;

Then,

make distclean;
./autogen.sh;
make;

And try again. It should work.


How to PUSH data between a Local DB Server to Cloud DB Server?

I realize this is an old question but may come in handy for others facing similar dilemma.

Your client is correct. Do no open up ports to the world, as this is will create a security risk. You will want a site-to-site VPN between your back offices and "the cloud". Once this is implemented the "the cloud" SQL will be accessible as if it was in your LAN.


Does implementing gMSA works for 2 node SQL FCI and 3 or 4 Node Always on AG,s?

Yes it does work.

  Since I just want to run SQl server with that gMSA account is there any special permissions needs to be granted at server or database level while running both SQL and agent services?

Nope, the installer will take care of that.

  Please advise any catch of implementing gMSa here.

If certain windows services don't start before SQL Server, then authentication can't take place and SQL Server will fail to start up. This doesn't happen very often and generally when it does there is some other issue with the server that is causing this symptom and behavior.


How to search a MySQL database with encrypted fields

Obviously they are not meant to be viewed, therefore searching on them would be problematic.

One trick I have used in the past is to hash the encrypted data before encrypting it, and storing the hash in an indexed column.  Of course, this only works if you are searching on the whole value; partial values will not have the same hash.

You could probably extend this by making a "full text" index of hashes, if you needed to, but it could get complicated really fast.

ADDENDUM

It's been suggested that I add a footnote to my answer per a fairly lengthy debate in chat about vulnerability to dictionary attacks, so I will discuss this potential security risk to the above approach.

Dictionary Attack:  A dictionary attack is when someone pre-hashes a list of known values, and compares the hashes to your hashed column in the database.  If they can find a match, it's likely that the known value is actually what is being hashed (It's not definite though, because hashes are not guaranteed to be unique).  This is usually mitigated by hashing the value with a random "salt" appended or prepended so the hash will not match the dictionary, but the above answer cannot use a salt because you lose the searchability.

This attack is dangerous when dealing with things like passwords: if you create a dictionary of popular password hashes, you can then quickly search the table for that hash value and identify a user that has such a password and effectively extract credentials to steal that user's identity.

It is less dangerous for items with a high degree of cardinality, like SSN's, credit card numbers, GUIDs, etc. (but there are different risks [read: legal]  associated with storing these, so I am not inclined to advise on storing them).

The reason for this is in order for a dictionary attack to work, you need to have pre-built a dictionary of possible values and their hashes.  You could, in theory, build a dictionary of all possible SSNs (a billion rows, assuming all formatting permutations are removed; multiple dozens of trillions of entries for credit cards)... but that's not usually the point of a dictionary attack, and basically becomes comparable to a brute-force attack where you are systematically investigating every value.

You could also look for a specific SSN or credit card number, if you're trying to match a SSN to a person.  Again, usually not the point of a dictionary attack, but possible to do, so if this is a risk you need to avoid, my answer is not a good solution for you.

So there you have it.  As with all encrypted data, it's usually encrypted for a reason, so be aware of your data and what you are trying to protect it from.


Your proc contains this dynamic code:

set @strSQL='insert into #RecCount(cnt) select isnull(max(a.sr_no),0) from ' + @OrderByColumn + ' a with(nolock)'

When executed, this code throws the error mentioned above because from context it seems that Some Column is passed through @OrderByColumn, but your code tries to do a SELECT from this Some Column.


SQL Server Query performance with window function

You may find that the following index and query rewrite performs better, because it sorts per person rather than once over the whole set, and row estimates are more likely to be accurate:

-- Index
CREATE INDEX IX_Salaries_PersonId_SalaryDate_Inc_ID_Salary1_Salary2_Salary3
ON rdd.Salaries (PersonId, SalaryDate)
INCLUDE (ID, Salary1, Salary2, Salary3);

-- Query
WITH People AS
(
    SELECT DISTINCT
        S.PersonId
    FROM rdd.Salaries AS S
    WHERE
        S.SalaryDate > DATEADD(DAY, -31, GETDATE())
)
SELECT
    P.PersonId,
    CA.SalaryDate,
    CA.ID,
    CA.SalaryValue,
    CA.rn
FROM People AS P
CROSS APPLY
(
    SELECT
        S.SalaryDate,
        S.ID,
        V.SalaryValue,
        rn = ROW_NUMBER() OVER (ORDER BY V.SalaryValue)
    FROM rdd.Salaries AS S
    CROSS APPLY
    (
        SELECT S.Salary1 WHERE S.Salary1 IS NOT NULL
        UNION ALL
        SELECT S.Salary2 WHERE S.Salary2 IS NOT NULL
        UNION ALL
        SELECT S.Salary3 WHERE S.Salary3 IS NOT NULL
    ) AS V (SalaryValue)
    WHERE
        S.PersonId = P.PersonId
        AND S.SalaryDate > DATEADD(DAY, -31, GETDATE())
) AS CA
ORDER BY
    P.PersonId,
    CA.rn
OPTION (QUERYTRACEON 8649);

You can omit the OPTION clause if you find a parallel query is generated naturally, or if you find non-parallel performance is good enough. The desired plan shape is roughly as follows:


The same result als ypercube, but I find a simple max windowing function easier to understand than dense ranks and such.

select mm.id, mm.value, mm.validity from (
   select m.*, max(m.validity) over (partition by m.id) maxval
      from meds m ) mm
   where mm.validity = mm.maxval;

see http://sqlfiddle.com/#!17/e478c/4 for the results.


When possible, are clustered indexes that are declared as unique more performant than clustered indexes that aren't declared as unique?

Adding to Tibor's answer, from the optimizer's perspective, key information can be very useful. This sometimes manifests in obvious ways like avoiding a Stream or Hash Aggregate operator to perform a redundant grouping operation.

Less obviously, the presence of a declared and enforced key can allow the optimizer to explore logical alternatives that are only valid when a key or functional dependency is known to the optimizer. The optimizer does not report when it could not explore a particular option due to the lack of a unique index.

From an execution performance point of view, one example of uniqueness helping performance is when a b-tree seek becomes a singleton lookup rather than a seek plus range scan:

For a unique key or constraint, the execution engine knows it will either find a row at the b-tree seek point, or it will not. When the index is not unique, the engine must scan forward or backward from the seek point to find any further matches. There is an overhead to doing that as I demonstrate in my article Enforcing Uniqueness for Performance.

In general, you are much better off enforcing a key than not. This protects data integrity as well as the other benefits mentioned above.

Related: Should I mark a composite index as unique if it contains the primary key?


Live migrate a database from one instance to another with zero/minimal downtime

This is now possible with tools like Ghostferry: https://github.com/Shopify/ghostferry


Performance about this simplified where-condition query

You dont need subquery, make it one-time I/O, you will get better performance:

select * from jobs
where (
       (status IN (0,1,7) and startDate > getdate())
       or (statu=5 and startDate=getdate())
       )
and type in (15,17,19)


Getting "Cannot open user default database. Login failed." for connections that don't specify a default database

That error either means that the default database does not exist or that it is not accessible to the user. Try to set tempdb as the default and see if that helps.


Actual Execution plan does not have memory grant info

Your query is probably very simple and does not require any memory grant.
It is trivial to reproduce:

See Understanding SQL Server memory grants for more background.


No. As soon as you attach a database to SQL Server, assuming it is compatible, the internal system tables will be upgraded.

While this process is a one-way operation, it takes a few seconds at most.

If you do not wish to upgrade your user databases, don't upgrade to the latest SQL Server.

If you want to keep your databases on a previous compatibility level, that can be done from T-SQL code or inside SQL Server Management Studio, but for all intents and purposes, the internal version of the database will match that of the instance it is installed on.


Getting the value of 'foo' for the closest previous row with higher 'bar'

with data as (
   select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9
)
select
  foo,
  bar,
  prev_foo
from
  data
model
      dimension by (foo, bar)
      measures     (cast (null as number) as prev_foo,
                    cast (null as number) as store_foo
                   )
      rules        (
        store_foo[any, any] = cv(foo),
        prev_foo [any, any] = max(store_foo) [foo < cv(foo), bar > cv(bar)]
      );


How to full text search worked with special character like "of"?

As Martin Smith commented, "It depends."  The factors that control the processing of Full Text indexes depends on the configuration you use in your CREATE FULLTEXT INDEX command.

Language - Which controls the parsing of words and phrases according to that language's rules.
Stop Word List - Words to be left out of the search, though their position in the string is recognized.
Accent Sensitivity - Determines whether accents are processed or ignored.

There is a tool to help you test your queries: https://msdn.microsoft.com/en-us/library/cc280463.aspx

You can use the sys.dm_fts_parser command to test your full text results.  For example, three similar searches using this function.

select * from sys.dm_fts_parser('"of" and "-" and "Department"',
   1033, NULL, 0) --American English, No stop words, Accent Insensitive;

Returns 2 tokens: of, Department

select * from sys.dm_fts_parser('"of""-""Department"',
   1033, NULL, 0) --American English, No stop words, Accent Insensitive;

Returns 2 tokens: of, Department

select * from sys.dm_fts_parser('"of-Department"',
   1033, NULL, 0) --American English, No stop words, Accent Insensitive

Returns 3 tokens: of-Department, of, Department

In general a "-" is ignored when standing alone. However, depending on the Language choice in your Full Text Index, the "-" serves as a hyphen in text and may be reported as shown in the third example which includes "of-Department" as one of the tokens returned.


MySQL SSL error: Unable to get private key

Ok, apparently just changing 'BEGIN PRIVATE KEY' to 'BEGIN RSA PRIVATE KEY' isn't enough.

It must be properly converted from PKCS#8 to PKCS#1 Reference

openssl rsa -in /etc/mysql/ssl/nginx.key -out ~/nginx.key.rsa


Restoring development database from production backup triggered production database to restore as well

Found the answer here https://sqlundercover.com/2017/08/14/ssms-source-database-restoring-after-performing-a-restore-to-a-new-destination/

When configuring the restore there is an option to keep the source database in a restore mode while copying the tail log. This is selected by default.


How can I ensure that in a insert trigger a column gets filled before other column?

If I understood correctly, this query will return the streets and counts you are looking for (for all pipelines and streets):

SELECT x.id, y.name, count(x.id) AS cnt
FROM
    (
        SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
        FROM
            tableX x
            INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
    ) t
GROUP BY x.id, y.name
ORDER BY cnt DESC
FETCH FIRST ROW ONLY
;

Now let's pack it in a trigger procedure:

CREATE or REPLACE function T_function RETURNS trigger AS
$T_function$
BEGIN
    SELECT name
    INTO NEW.name
    FROM
        (
            SELECT x.id, y.name, count(x.id) AS cnt
            FROM
                (
                    SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
                    FROM
                        tableX x
                        INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
                    WHERE x.id = NEW.id -- we count the relevant pipeline only
                ) t
            GROUP BY x.id, y.name
            ORDER BY cnt DESC     -- we don't care the other
            FETCH FIRST ROW ONLY  -- streets in the list
        )
    ;

    RETURN NEW;
END;
$T_function$
LANGUAGE plpgsql
;

the line NEW.id := NEW.id did nothing useful
I flattened the innermost subquery into a simple JOIN (no need for the subquery)
to decrease the level of nested parentheses, I used SELECT name INTO NEW.name instead of NEW.id := (...)


Inserting into table depending on conditions

You should use IF...ELSE (Transact-SQL), ther is the complete example below:

declare @conditional_table table(col int);
insert @conditional_table values(1), (3), (4);

declare @data1 table(col varchar(100));
declare @data2 table(col varchar(100));

declare @table1 table(col varchar(100));
insert @table1 values('table1');
declare @table2 table(col varchar(100));
insert @table2 values('table2');

declare @cnt int = 0;
--WHEN Condition1 THEN INSERT INTO data1 SELECT * FROM table1
-- let's say Condition1 is "there is 1 in @conditional_table"
if exists(select * from @conditional_table where col = 1)
begin
    insert into @data1 select * from @table1;
    set @cnt = @cnt + 1;
end;

--WHEN Condition2 THEN INSERT INTO data2 SELECT * FROM table2
-- let's say Condition1 is "there is 2 in @conditional_table"
if exists(select * from @conditional_table where col = 2)
begin
    insert into @data2 select * from @table2;
    set @cnt = @cnt + 1;
end;

--ELSE SELECT 'done'
if @cnt = 0
select 'done';

-------------------------
-- there was no 'done' selected because @count > 1, some conditions were true
select * from @data1; -- condition1 was true, 'table1' will be returned
select * from @data2; -- condition2 was false, no data is returned


One thing you could do is just set the ServerConnection portion of the Server object to an explicit connection string, specifying the port name.

You could either do this by accessing the Server.ConnectionContext property:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server

$SqlServer.ConnectionContext.ConnectionString = $ConnectionString

$SqlServer.Databases |
    Select-Object Name

Or by passing the ServerConnection object in when you instantiate the Server object:

$ConnectionString = "data source = yourinstancename,10055; initial catalog = master; trusted_connection = true;"

$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$ServerConnection.ConnectionString = $ConnectionString

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerConnection)

$SqlServer.Databases |
    Select-Object Name

EDIT: I just re-read your question, and it seems like one of your attempts is basically identical to my second one up above.  I took your code and all I changed was the data source portion of your connection string, and it worked just fine for me:

$cnString = "Data Source=Server\instance,port;Initial Catalog=master;Trusted_Connection=true;"
$cn = new-object system.data.sqlclient.sqlconnection($cnstring)
$cnSql = New-Object Microsoft.sqlserver.management.common.serverconnection($cn)
$s = New-Object Microsoft.sqlserver.management.smo.server($cnSql)
$s | Select Name, Version


What is the difference between these two create table statements?

The second statement is creating an object table.

It is almost never the case that you really want to use an object table.  That was something that was introduced in the 8i time frame when Oracle was making the database object oriented.  While a lot of the object oriented PL/SQL enhancements have been useful, using object types in SQL is not something that I'd recommend.


Launch a stored procedure when a datetime is expired

You should create a SQL Agent job. This can poll your column and fire your stored procedure when the datetime exceeds a specified value.

Alternatively, if your stored procedure needs to run at exactly the specified time, schedule a SQL Agent job to fire at that exact time and don't poll. You can do this programmatically via the sp_add_jobschedule system stored procedure, or manually depending on your requirements.

Using the latter method you could remove the logic that writes the desired time to a table. If that table value is still required however, you could create a trigger on that table that invokes sp_add_jobschedule to create a job schedule.


Efficiently implementing an orders table?

Multiple tables and Partitioning would have similar performance -- DELETE from one place (table or partition) and INSERT into another.

The syntax for partitioning -- if you PARTITION BY LIST(status) -- is a simple UPDATE ... SET status = '...' WHERE ...;.  But the cost is effectively delete+insert.

But... What is the benefit?  Do you usually do SELECTs that say WHERE status = 'active'?  (or FROM ActiveOrders if using multi-table)?  If so, the performance is probably the same.

But, but... With a single table, INDEX(status, etc) works even slightly better for finding SELECT ... WHERE status = 'active' and etc;

Bottom line:  There are pros and cons of each approach.  Some of the pros and cons show up in the schema, some in the SQL you need to write.

Recommendation:

Start with a single table.
Six months from now, re-open the question.  By then, you will have several schema changes to make and you will be itching to rewrite the SQL.  At that time, do some experimenting with the 3 options (keep single table, use multiple tables, and use partitioning).

It should be possible to write a small number of SQL statements to mass-change from the old schema to the new.


Upgraded from SQL Server 2008 R2 Express to SQL Server 2014

Now i'm running them both side by side

So, to clarify, you did a side-by-side upgrade.  Meaning that you installed a new instance of SQL Server 2014?

In that case, if you are ready to rid yourself of the SQL Server 2008 R2 instance then you would need to transfer all of your necessary entities from the 2008 R2 instance to the 2014 instance.  That'll include everything it would take to make your clients hit the new 2014 instance.  Most notably, you'll be backing up and restoring your databases from the source (SQL Server 2008 R2 instance) to the destination (SQL Server 2014 instance).  But you will most likely need to transfer other server objects as well.

Note: Max brought up a good point in his comment below, ensure that you have a valid database backup/restore on your destination instance before removing anything.

  how can I abandon the 2008 version and only run on the 2014 version

Point your applications/clients to the new SQL Server 2014 instance after the migration.  And then you can rid yourself of the SQL Server 2008 R2 instance if you no longer want those bits to be living on that machine.  Warning, you should verify with the business and end users that functionality is now back to the same state as it was prior to the upgrade.

  I've check the properties on the SQL 2014, and it shows that I'm still with 2008 R2 Express edition

I'm not sure what you mean here.  How did you check the "properties" and what exactly are you referring to?  You're either connecting to your old SQL Server 2008 R2 instance, or you new SQL Server 2014 instance.  If the former, you'd be in the context of the old one.  If the latter, you should be seeing the new 2014 instance.  If that's not what you mean, then please clarify.


Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones

Having the date and time separate will allow you to do aggregates by time much easily. for eg: if you want to run a query to find what time period of the day is most busy. This is much easily performed using a separate time dimension.

Also, you should just have one timekey. Decide on either GMT/ EST time - then use this in the fact table. If you need to run reports based off the other timezone, just convert it in your application or query.


Copy Permission From Windows Based user To Local user

Yes, it is. I guess you mean "Windows Login" and "SQL Server Login".
You can script permissions using system objects such as sys.database_permissions or using a tool such as Idera SQL permissions extractor.


You need to learn the difference between repetition and redundancy.  Sometimes a field value is repeated coincidentally.  This kind of repetition cannot be normalized out.

Normalization is about studying the functional dependencies between key and non-key elements.  It is not about putting everything that might occur twice into a table with a surrogate key.

For example, you say that a phone number may be used by multiple companies and have modeled phone number as an independent table.  This would prevent update anomalies if when one company changed its number all of the other companies using that number changed theirs at the same time and in the same way.  Does any of that actually make business sense?  It doesn't to me.

Also, normalizing geography into a hierarchy is a questionable proposition.  However, you haven't even normalized into a hierarchy.  Is region ID not determined by country ID?  If so, your LOCATION table is not 3NF.  Similarly, are there postcodes that bridge states?  I don't know the Australian system well enough, but I know that in Canada, the US and the UK this doesn't happen.

You've designed everything like its a star schema and company is the fact.  Facts in star schemas are usually transactions, not static entities.  Transactions don't tend to change their properties, because they usually represent something that actually happened and one doesn't generally go back in time to change history.  Static entities live for a long time in your data (maybe forever) and change their properties fairly often.  Star schema is not a good, efficient model for this type of data.

What you should do is run queries on the frequency distributions of each column and on combinations of columns that you think may be keys combined with columns that you know aren't keys.  This will help you test your assumptions about which columns are truly able to act as keys.  Then you should temper those findings with some common sense around what could potentially happen to your data in terms of changes to column values.  Once you know your actual functional dependencies found in your data, then follow the relatively mechanical process of moving your relation to 3NF.


This might also happen if you disable the cookies in your browser.
If this is the case, you may see this message:"Cookies must be enabled past this point" on the top of Phpmyadmin page.
All you need to do is to enable cookies on your browser, or use different browser to log to phpmyadmin.


Suppressing the removal of leading zeros in numeric data

Retaining leading zeros for numeric data types is not possible.  Since you appear to be using this column as a non-numeric identifier, you should consider using a varchar column of appropriate length.

If the maximum length you're willing to accept in the table is 11 characters, and the value is always required, I would define the column like this:

someCol varchar(11) NOT NULL

The column will then differentiate between 08 and 000008, and the leading zeros will be retained.


How to generate unique and sequential Id values

A simple rearrangement:

INSERT INTO `Task`
       (`id`, `vendor_Id`, `name`)
    SELECT MAX(id) + 1, 123, 'Test'
        FROM Task
        WHERE vendor_Id = 123;

(I changed the vendor_id to make it clearer.)


Modeling staff dimension in data warehouse

Typically dimensional modelling starts by identifying facts and the dimensions about those facts.  Most of those things you mention are dimension attributes.

Staff/employees alone and their place in an organization is probably going to be modeled with a factless fact table.

You may well have a supervisor/employee relationship, and this would be an instance of a factless fact table where the facts in that table are likely to be dates of supervision (these are really a lot like degenerate dimensions).

However, I expect you will have fact tables like hours worked etc which will use these dimensions as foreign keys.

It is very unusual to be modeling dimensions in isolation, as dimensional modeling is a very pragmatic type of approach which is very dependent upon the type of data you have as well as the types of analysis.  The choice of how many dimension tables and which attributes go in which dimensions is very dependent upon data behavior and can sometimes be quite arbitrary.


(Speaking from a MySQL perspective...)

10M rows is not that big -- perhaps only 1GB?  It is hardly a candidate for sharding unless the system activity is really high.

2M rows/year = 25/second.  100/second is where I perk up an eyebrow and suggest looking more closely.  1K/sec is likely to need some special treatment.

ALTER TABLE .. ADD COLUMN .. -- There are techinques that make that not too bad.  And with MySQL 8.0, there is a truely painless ADD COLUMN.

Consider having JSON column for "miscellany" that gets added later; do not add more columns unless you need the database engine to filter on them or sort on them.

For analytics, build and maintain Summary Tables.  These make "report writing" 10x faster than scanning through 10M rows.

CTE and Windowing functions exist in MySQL 8.0 and MariaDB 10.2.  But you may not need such.

5K Writes/day -- In addition to the 2M inserts/year?  Not a big deal.


Backup SQL Server 2017 to remote directory

Can you turn the network path into a drive letter using windows explorer or workplace

This allows to "use different user credentials" - and "remember the settings" + "automatically connect upon startup"(or similarly named) - so it should automatically connect to the network path using the username+password combo you got...
Then use your database backup with the created drive letter from the above step


Find the median of a calculated field in SSRS 2012

I found code with the answer:

'MEDIAN code
'code from http://stackoverflow.com/questions/1943437/mean-median-mode-in-sql-server-reporting-services

Dim values As New System.Collections.Generic.List(Of Integer)
Dim valueCounts As New System.Collections.Generic.Dictionary(Of Integer, Integer)

Function AddValue(newValue As Integer) As Integer
values.Add(newValue)
AddValue = newValue
If Not valueCounts.ContainsKey(newValue) Then
    valueCounts.item(newValue) = 1
Else
    valueCounts.item(newValue) += 1
End If
End Function

Function GetMedian() As Double
Dim count As Integer = values.Count
If count = 0 Then
    Return 0
Else
    values.Sort()
    If count Mod 2 = 1 Then
        Return values(CInt((count / 2) - 0.5))
    Else
        Dim index1 As Integer = count \ 2
        Dim index2 As Integer = index1 - 1

        Dim value1, value2 As Integer
        value1 = values(index1)
        value2 = values(index2)

        Return (value1 + value2) / 2
    End If
End If
End Function


Is it possible to have indexes for like '%*%' type queries

For LIKE queries this is not available.

But for making such queries SQL Server provides functionality called Full-text search. More info in MSDN.


Total_worker_time over SQL 2008 linked server

When a query takes place over a linked server, does the local server
  record Elapsed time in place of CPU time, or does the remote server
  return the actual CPU time to the local server?

When executing a query over Linked Server the SQL Server session will enter an OLEDB wait while waiting for results, and that time will not be accounted for as CPU_time.   The linked server does not return wait stats.


You can send commands via ssh (that is you don't have to actually log in and run the command):

ssh user@host 'mysqladmin shutdown'


In Oracle how do I put certain tables or partitions in a different group so the rest of the db can be brought up with OR without them?

Put the tables in a different tablespace. Then you can put the datafiles under a different location/group/naming scheme to the rest of the data, and configure your backups to ignore these files.

If you need to recover the database without this data, you should drop the tablespace with the database in mounted mode:

startup mount;
drop tablespace your_unneeded_data including contents;
alter database open;


Yes and No. With improper configuration, too much RAM can cause performance degradation.

For example on Linux platform, if HugePages is not configured, with large SGA and many database sessions, the pagetable will hold a significant amount of memory. Once at one our clients, the database server had 512 GB memory installed. They had about 4000-5000 database sessions with a 150 GB SGA, and this resulted in a 360GB pagetable size - so the server ran out of memory, and this caused hangs and reboots. After enabling HugePages, the size of the pagetable decreased to 1,5 GB (from 360 GB!!), saving and allowing to use more than 350 GB extra memory.

Another case, where the customer used Solaris x86-64 11.1, is the reverse of the above case. The database used 1 GB pages with 300 GB SGA, but because of a Solaris bug, 99.8% of the time spent handling large pages at OS level was overhead, caused the CPU to run in kernel mode, causing huge load on the server (300+ with 32 cores). The database was running fine with 24 GB SGA, but could not handle the load with 300 GB SGA. Disabling large pages in Solaris resolved this issue (there is a Solaris patch for it, but the local admins did not install it...).

These are however not Oracle limitations, but configuration issues. Oracle can handle large amount of memory. One of our customers has been using 500 GB+ SGA for a long time without any problems, and they are planning to increase it further.


Downgrading from AWS Aurora MySQL 5.7 to AWS Aurora MySQL 5.6

I think exporting to a SQL file first would work:

export to a SQL file (in S3 or elsewhere)
import to a 5.6 instance

See this: Amazon Aurora Can Export Data into Amazon S3, where it says:

  You can now use the SELECT INTO OUTFILE S3 SQL statement to query data from an Amazon Aurora database cluster and save it directly into text files in an Amazon S3 bucket. This means you no longer need the two-step process of bringing the data to the SQL client and then copying it from the client to Amazon S3. It’s an easy way to export data selectively to Amazon Redshift or any other application.
  ...


I need to know if a JOIN query is possible

I used Magier's example to come up with this:

SELECT oc_url_alias.keyword, oc_product.status, oc_product.date_modified,
REPLACE(query, 'product_id=', '') AS keymatch
FROM oc_url_alias
JOIN oc_product on REPLACE(query, 'product_id=', '') = oc_product.product_id
WHERE oc_product.status = 1;

which outputs exactly what I need.

Thanks for all the help everyone posted.


How to create a train schedule in SQLite3?

I think you need a track_segment table to track the segments between stations. The problem with your Station table is that you include distance there, but distance to/from what?

So let's try this:

Train
-----
  ID
  Train_number
  Days_of_operation
  other_details

Station
-------
  ID
  Name
  other_data

Track_Segment
-------------
  From_Station_ID
  To_Station_ID
  Length
  Line_ID

Line
----
  ID
  Name
  other_data

Train_Run
---------
  ID
  Train_ID
  from_Station_ID
  to_station_ID
  Depart_datetime
  Arrive_datetime
  other_details

This allows a Train_run to reference a specific train, as well as the two endpoints of the journey. Of course, there might be multiple segments of track between these two stations, so the total length of the journey could be had by looking at all the segments between from_station_id and to_station_id in track_segment. I also included the line table, since you had a refernce to it in your tables. I assume that a "Line" is just a descriptive term for a specific train journey that might have multiple stops along the way, like "Orient Express" or "Rocky Mountain Route".


How to better use CLR Function from performance point of view (repeat inside each DB or have general function)?

This appears to be a duplicate of this question:

Setting up a central CLR stored procedure / function respository library for internal stored procs in other databases to use?

However, I do not feel that either of the two answers there are adequate since they do not mention some of the more important aspects of this question.

There is no obvious choice here as to which location is better for SQLCLR objects in general since there can be constraints imposed by what is being done with the SQLCLR code. There are some uses that will require the Assembly to be in each individual database, and one use that would require the Assembly to be in a centralized database. It all depends on a few different aspects of what the code is doing. Hence we need to look at what those aspects are in order to determine if there is even a choice to begin with, and if so, what the pros and cons would be.

SQLCLR-specific functional aspects

User-Defined Types (UDTs): UDTs cannot be referenced across databases; they cannot be declared with 3-part names (i.e. DatabaseName.SchemaName.UserDefinedTypeName). If any UDTs are being used then the Assembly will need to be added to each database in which the UDT will be used. However, if other SQLCLR objects are being used, then assuming that there is the choice to place those objects in either a centralized DB or in each customer/application DB, then you could always place the UDTs in an Assembly that gets placed in each customer/application DB and another Assembly containing Functions / Stored Procedures / User-Defined Aggregates / Triggers.
Security:

How many Databases are affected: Is the CLR code doing anything that requires the Assembly to be marked with a PERMISSION_SET of either EXTERNAL_ACCESS or UNSAFE? If so, are you importing any DLLs that were signed outside of your control and cannot be resigned? Typically these would be unsupported .NET Framework libraries or 3rd party DLLs. When you have no control over the signing of Assemblies that need to be marked as either EXTERNAL_ACCESS or UNSAFE, then you might be forced to set the database containing the Assembly(ies) to TRUSTWORTHY ON. Since setting a database to TRUSTWORTHY ON is a security risk, it is preferable to minimize the number of databases you would need to do this to, in which case putting the code into a centralized database seems like a better approach. And if you already have a central DB for other code and want to truly minimize this type of security risk, you could have a second centralized DB for just this code.

If you do have control over the signing of the DLL(s), then you should definitely be creating a Certificate or Asymmetric Key in the master database based on the DLL, and then creating a Login based on that Certificate or Asymmetric Key, and then assigning either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission to that Login. Those few steps right there (and the only things being created are the Certificate or Key and the Login) will allow any Assembly signed with the same private key to be set to either EXTERNAL_ACCESS or UNSAFE (depending on which permission was granted to the Login), no matter what database(s) it is loaded into. And if you are able to do this, then you can place Assemblies set to either EXTERNAL_ACCESS or UNSAFE in all customer/application databases without any more security risk than you would have placing that same code into a centralized database **.
Different permissions for different clients/apps need: If, for any reason, some clients/apps might need to have a different PERMISSION_SET than others, then that would requiring loading the Assembly(ies) into each client/app database. This would allow you to have some databases using SAFE while others were using EXTERNAL_ACCESS. This goes beyond what can be done with object-level permissions. By setting an Assembly that has code to do file system functions to SAFE, you guarantee that the code can't work, even if someone does find a way to bypass your regular security and can still EXECUTE the SQLCLR Stored Procedure.

AppDomains: This aspect concerns memory/resource utilization and separation. This is probably the area that is the most impacting in terms of considerations, yet it is also probably the least understood. So, let's start by looking at how T-SQL objects would handle the same central DB vs each client/app DB question.

T-SQL Functions and Stored Procedures, upon being executed, store their execution plans in the plan cache (well, not Inline TVFs), which is in memory. Thinking in terms of just memory utilization, using a centralized DB has the advantage of storing a single plan rather than one plan per each client/app DB, especially if there are 100 or more DBs. However, having a cached plan invites the question of whether or not it is an optimal plan for subsequent executions. It is possible, with a potentially wide range of variation in how it is being executed across so many client/app DBs, that a single plan is great for some but also quite horrible for others. If you don't want the performance hit of specifying WITH RECOMPILE, then deploying that to each client/app DB would allow for more individualized optimization. In short: central DB is less memory used for plan cache, but potentially worse performance; separate DBs is more memory for plan cache, but fewer potential performance issues.

When it comes to SQLCLR objects, the same plan-caching pros and cons exist for each approach. But now that we are dealing with App Domains there are additional ramifications to consider. App Domains are memory spaces / sandboxes that .NET uses to run code in. Each App Domain is its own separate sandbox. In SQL Server, App Domains are created per each Database and Assembly Owner combination. So multiple Assemblies in the same DB owned by the same User will share an App Domain, but Assemblies in that same DB owned by another User will have a different App Domain, and Assemblies in other DBs will be in their own App Domains. With that in mind:

Memory consumption increases at a faster rate when deploying to individual client/app DBs since the Assemblies being used are loaded into the App Domains (but they are not loaded until they are first used). The App Domain also holds all of the variables, resource handles, etc (until those things are marked for Garbage Collection and GC decides that the moon and stars are aligned perfectly and takes that as a sign to run). So a 2 MB Assembly in one database using one AppDomain that has a certain amount of memory reserved for variables, etc can be quite different than loading that same Assembly into 100 DBs where it is now 200 MB (technically there is some portion of the DLL that is shared in memory across multiple instances of it, but I'm not sure how to measure that) plus 100 times the amount of space reserved for variables etc.

A related issue is if you are using Regular Expressions and making use of the RegEx option for Compiled which compiles the expression down to Intermediate Language (MSIL). This does speed things up for repeatedly used expressions, but once an expression is compiled, it cannot be garbage collected and will stay in the AppDomain until it is restarted. If there is a commonly used RegEx function that is using the Compiled option, the memory used to store it will be repeated per each DB if the Assembly is loaded into each DB. In this case it might make sense to put this code in a centralized DB.
Resource limitations can be an issue when using a centralized database. Depending on what classes you are using, you might unknowingly create a resource bottleneck. For example:

When using the static RegEx methods instead of the instance methods, the Regular Expressions that you use are cached. But the default cache size is only 15 expressions. If a great variety of expressions is being sent in from a large number of clients or apps, then expressions won't stay in the cache for very long. So if this is the only reason to consider loading the Assembly into each Database, then you could just increase the cache size. Please see the MSDN page for RegEx.CacheSize for details.
Similarly, if making WebRequests then there is a default maximum number of active connections that can be made to a particular URI. And that default is just 2. If you make more requests to that same URI (very easy to do if it is a static location and you are using a centralized DB for this code) then any requests above that maximum will simply wait in line for a current connection to close (i.e. blocking). So you would have to either load the Assembly into each client/app DB or increase the connections-per-URI limit. You can set the default maximum for all URIs in the current App Domain by setting ServicePointManager.DefaultConnectionLimit (this can be set once per start up of the App Domain, such as in a static class constructor), or can be set on a per-URI basis by creating a HttpWebRequest and then setting its .ServicePoint.ConnectionLimit property (this needs to be done each time the WebRequest is instantiated since the object has a maximum time to live and once garbage collected, the ConnectionLimit will revert to the ServicePointManager.DefaultConnectionLimit value, as noted above, when a new instance is created).

If you are using a static variable to cache certain values (shared memory -- rare but still a possibility), then you need to decide what the scope of sharing those values should be. If you want the sharing to be contained within each client/app DB, then load the Assembly into each client/app DB. But if you want to share those values across all DBs, then put the Assembly into a shared, centralized DB.

General functional aspects

Database access: Is the code referencing any database-specific objects? Keep in mind that executing SQL using the in-process / Context Connection = true; connection will execute initially with the "current" database being set to the database where the object exists, not necessarily where the object is being called from. Hence code running in a customer/application database and calling an object in a centralized database won't be able to reference objects by using only 2-part names. However, you can still use a centralized database for such code as long as you have an input parameter for @DatabaseName (use: [SqlFacet(MaxSize = 128)] SqlString DatabaseName) and then pass DB_NAME() into it. Then you can use DatabaseName.Value in the SQLCLR code for either a USE statement or to concatenate into the Dynamic SQL to create the appropriate fully-qualified object names (i.e. 3-part names).

This is probably not a determining factor if you are only referencing system-based objects (i.e. sys.databases) that return the same rows no matter what database you are in. Nor should it be an issue if you are making an external connection since you would already be passing in the database name for the connection string, or you will just be logging into the default database for the login making the connection.
Collation differences: If the Collations between the centralized DB and the client / app DBs are the same, then this is not a determining factor when deciding between these two models. But, if your system is going to support different collations, then you need to be aware of what you code is doing as it might be impacted by Collation Precedence. If you are sending in strings that will be compared to other strings, then the behavior might not be what is expected, even if there is no error produced. The collation used to compare local variables and string literals will be the default Collation where the object (i.e. Stored Procedure or Function) exists. If this Collation is different than the collation of the "current" database when that object is called (if passing in a literal or variable), or different than the field being passed in, then there can be a variety of differences in how that comparison is done. Hence, if supporting a variety of Collations, then string-based operations might be more stable/consistent when the code is deployed to each client/app DB.

Distractions

The following are reasons given in this Question, and in the duplicate question linked at the top of this answer, for preferring one method or the other, but that I feel are not truly relevant in deciding which method is a better fit:

Centralized DB is easier to support: How so? The code should only exist once in source control. And assuming that the code loaded into each client/app database is the same, then troubleshooting should be roughly the same in either case. If anything, placing the SQLCLR code in a common database, separate from the client/app DBs adds a layer of complication with cross-database calls that can technically be seen as a reason to not centralize this (or other) code.
Centralized DB is easier on deployments: If this is the case, then I would say that one needs to fix their deployment process. If you are working with a model in which you duplicate the client / app databases to have identical schema and just different data, then you already face this problem when deploying schema changes. Deploying SQLCLR is also just DDL statements. It might be more difficult to deploy SQLCLR code if attempting to load the Assembly from the DLL, but there is no reason to do that. Just make sure to have a self-contained SQL script that does a CREATE ASSEMBLY or ALTER ASSEMBLY using the hex bytes (i.e. FROM 0x4D5F000002C...).
Individual Client / Application DBs are better for backup/restore: The argument here is that if there is a problem and you need to restore, then having everything contained in the Client / App DB being restored is easiest. I would say that if you need to do a restore, then restoring 2 DBs (Client / App DB and common DB) is pretty much the same work, and restoring 101 DBs (100 Client / App DBs and 1 common DB) is still pretty much the same. And from a reliability stand-point, either your backup process is reliable and can be trusted to properly back up both Client / App DBs and common DB, or you need to fix your backup process ;-).
Individual Client / Application DBs are easier for testing variations: To a degree this is true if you need to test one version of the code without changing all references. Though usually this should be done by testing in a completely different environment to begin with. But if there is a customer who is going to beta-test some code change, for example, then this situation can be mitigated easily enough by using the model suggested by @AaronBertrand in his answer to this question which is very similar (it concerns mainly T-SQL objects and not specifically SQLCLR code): Create function in central database or repeat in each database? . The model discussed in that answer is to use a centralized DB for most/all cases, and to create Synonyms in each Client / App DB so that the local code can reference local names. Then, if a variation is needed, code can be placed in a particular Client / App DB and the Synonyms would then be removed. In that case, the local T-SQL code would still reference the same local names and not know the difference. Personally, I really like this approach and cannot think of any particular downside to it outside of needing to create / remove the Synonyms, which doesn't seem like a high price to pay for the flexibility gained.

Ergo: for the particular situation described in this Question (i.e. scalar function, no external resources, no database-object access, no resource limitations), it seems like using your single configuration database would be fine.

** If you find yourself thinking "but assemblies set to EXTERNAL_ACCESS or UNSAFE are security risks because of what they allow you to do": I was not saying that there is no risk at all with assemblies set to EXTERNAL_ACCESS or UNSAFE if you use the Certificate / Asymmetric Key -based method. What I am saying is, in that configuration, whatever risk exists is no different between placing the Assembly in a centralized database versus in each client/application database. This is because any potential security problems that could result from assemblies that are set to EXTERNAL_ACCESS or UNSAFE are not localized to the database in which those assemblies exist (unlike setting TRUSTWORTHY to ON). Any security issues are system wide. But, when setting databases to TRUSTWORTHY ON, then you have additional, per-database security issues.


How to compute a membership expiration date on SQL

All that you have to do is to add the ExpirationPeriod to the Year of payment and add 12.31 as the date of expiration.

If someone pay 2014.05.07 for 3 years then his membership expired at 2014+3.12.31


OK, replacing the previous answer with this one.  USE OF ms_foreach stored procedures is not recommended.  This is a little more code but it's more reliable.  You can limit the databases you search through, it checks to make sure the transaction table exists, and still provides the database name in the results.  Note - this will provide a separate result set for each DB. If this is not desired, create a temp table and dump the results into the temp table by uncommenting the table creation block for #Results, the INSERT just before "EXEC sp_executesql @runMe" and the final SELECT.

    DECLARE
          @DataSetID INT
        , @Linkdate VARCHAR(19)
        , @runMe NVARCHAR(4000)
        , @dbCnt INT
        , @dbName VARCHAR(500)
        , @tableName VARCHAR(500)
        , @objName VARCHAR(500)

    --initialize variables
    SET @DataSetID = 1;
    SET @Linkdate = '8/31/2015';
    SET @tableName = 'transactions'; --assuming if this is present the other are as well
    SET @dbName='';

    /*
    --CREATE A TABLE TO CAPTURE ALL RESULT
    IF OBJECT_ID('tempdb..#Results','U') IS NOT NULL
        DROP TABLE #Results;

    CREATE TABLE #Results
    (
          DBName            VARCHAR(500)
        , InterfaceName     VARCHAR(255)
        , BrokerCode        VARCHAR(255)
        , BrokerDescription VARCHAR(1000)
    )
    */

    IF OBJECT_ID('tempdb..#DBList','U') IS NOT NULL
        DROP TABLE #DBList;

    SELECT name
    INTO #DBList
    FROM sys.databases;

    SELECT @dbCnt=COUNT(1)
    FROM #DBList;

    WHILE @dbCnt > 0
    BEGIN
        SELECT @dbName=MIN(name)
        FROM #DBList
        WHERE name > @dbName

        SET @objName = @dbName+'..'+@tableName

        IF OBJECT_ID(@objName) IS NOT NULL
        BEGIN
            SET @runMe = N'
            select distinct '''+@dbName+''' AS DBName, I.InterfaceName
                ,case
                    when B.brokerCode is null
                        then ''Not Populated''
                    else B.BrokerCode
                    end
                ,case
                    when B.brokerDescription is null
                        then ''Not Populated''
                    else B.BrokerCode
                    end
            from ['+@dbname+']..transactions T
            inner join ['+@dbname+']..interfaceoriginations I on I.OriginationID = t.OriginationID
            left join ['+@dbname+']..brokers B on B.BrokerID = t.BrokerID
            where linkdate = '''+@linkdate+'''
            order by 1
                ,2;'

            --INSERT #Results
            EXEC sp_executesql @runMe
        END

        SET @dbCnt = @dbCnt-1
    END

    --SELECT * FROM #Results


Database Design - One table from many source(One to One)

I do not like both of your solutions.

Solution 1: Storing different things at the same place is a bad idea. Storing  foreign key to different tables in the same field is worse.
Solution 2: this is also no a good idea. You get a lot of NULL values.
The best thing is to draw a picture.

My prefered solution is that of @Joel Brown (with some minor changes) Instead of 'text' i woul call the supertype 'message' and it should not only hold the content but the all fields (and references) common to all message types.

message(
  message_id PK
  content
  common attributes ...
)

Tweet(
  message_id PK, FK
  tweet_username
  tweet attributes
)

Sms(
  message_id PK, FK
  sender_number
  sms attributes ...
)

Email(
  message_id PK, FK
  email_sender
  email attributes ...
)

The implementation is straight forward: each entity is a table. the superentity has a primary key. this primary key is used for the subentities too.
If you want model your system with an entity text that has relationships to entities sms, email and tweet as described in your post, you get the following EER diagram

I don't like this model in your special case but there are other situation where such a model is appropriate.

There is a standard way to transform such 1:1-entities  to tables. we have three relations sends, mails, tweets which have an optional entity (SMS,Email,Tweet) and a mandatory Entity (Text). In this case the foreign key is stored in the table of the optional entity. (You did it the wrong way) So you get the following tables

text(
  text_id PK
  content
  other attributes ...
)

Tweet(
  tweet_id PK
  text_id FK
  tweet_username
  tweet attributes
)

Sms(
  sms_id PK
  text_id FK
  sender_number
  sms attributes ...
)

Email(
  email_id PK
  text_id FK
  email_sender
  email attributes ...
)

Using Extended Entity Relationship Diagrams (and maybe any other modelling method) makes it almost
 impossible to create without cheating such solutions that you have proposed. Diagrams can be drawn with pencil and paper. The diagrams
in this post  are created with DIA. The Extended Entity Relationship Model and how to transfer it into tables can be found in  T.J.Teorey, D.Yang,J.P.Fry:
A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model


How to get one complete column in mysql?

If you remove all newlines and whitespace from the output before grepping, it should work for you.

Use

mysql -u root -pxxxxxxxxx -e "show slave status\G;" 2>&1 | tr -d '\n\r \t' | grep -v "Warning: Using a password" | grep "Last_Error"

where the tr command removes the newline, carriage return, space, and tab characters from the input.


What is the best way to get a random ordering?

ORDER BY NEWID() will sort the records randomly. An example here

SELECT *
FROM Northwind..Orders
ORDER BY NEWID()


Out of memory issues on SQL Server 2012 SP3

Since the SQL Server is running in a VM, as indicated by the (hypervisor) indicator in the version statement, you should ensure it has a memory reservation in VMWare (or Hyper-V, etc) on the host server.

The memory reservation should typically be 100% of the memory allocated to the VM for SQL Server virtual machines that are used in a production environment.  Without a memory reservation, the host server may "steal" memory from the virtual machine via use of a "balloon driver" for use by some other VM, which is likely the cause of your out-of-memory condition.

In VMWare vCenter, to set the Memory Reservation on a Virtual Machine:

Power off the virtual machine before configuring the memory settings.
In the vSphere Client, right-click a virtual machine from the inventory and select Edit Settings.
In the Virtual Machine Properties window, select the Resources tab and select Memory.
In the Resource Allocation panel, select the Reserve all guest memory (All locked) check box.
Click OK.

If you're using Microsoft Hyper-V, disable dynamic memory for the VM, using this process in Hyper-V Manager:

Ensure the VM is turned off.
Right-Click the VM, choose "Settings"
Select "Memory" from the left-hand pane.
Ensure "Enable Dynamic Memory" is not checked.
Start the VM.


WAIT STATS - high Wait Time on ASYNC_IO_COMPLETION

As mentioned

  The value for wait count is very low compared to all the other wait
  types (the lowest)

the count being only 9, i.e. the wait has only showed up for 9 times, during the run of gathering wait stats for a complete day, wont be a concern here for me.

However, its worth checking the other wait types BACKUPIO and BACKUPBUFFER .

This wait type generally shows up when you are taking the backup over the network with not a good NW connectivity or on the tape etc (i.e. any other extremely slow backup system)

We had this similar wait on one of our SQL server 2005 for backing up the database approx 200 GB in size. As the size being large and we did not had compression feature available at that time, witness lots of waits. we had to go with third party compression tool and saw those wait types count going down.So i believe it could be a good start if you start checking on the backups. Make sure you use the SQL server compression feature for backing up the databases.

However just to check if this wait type is causing any problems, you need to check for any issues in backup throughput.

May be you can go around and test the backups by doing native backups on same network as compared to other network and check for other counter values.

Also, you can refer A cause of high-duration ASYNC_IO_COMPLETION waits for more explanation by Paul on this wait.


Duplicate index - Same key, one with INCLUDE columns, which one to drop?

I believe the IndexInclude will satisfy anything that would've used IndexNoInclude right? since the Keys are exactly the same

Are they the same? Maybe I am misreading the output, but it certainly looks like IndexNoInclude has an extra key column: [ID] NUMERIC.

You should review your queries to see which of the 6 INCLUDE columns are actually being referenced / used. You could likely get away with "merging" the two indexes such that the remaining index has both key columns and between 1 and 6 of the INCLUDE columns.


SQL Server 2005 get execution plan of a overnight stored procedure

So is there a way I can see the execution plan of the proc or the statement in particular?

You have to refer to DMV's for extracting such information. sys.dm_exec_cached_plans, sys.dm_exec_sql_text and sys.dm_exec_query_plan are the ones to look into.

Especially sys.dm_exec_query_plan DMF will return the plan for a given batch or procedure (along with the "subplans" for each query comprising the batch).

Note: Restarting sql server will flush out all dmv data.

Example :

-- Do not run it in PROD !!!!
-- free up the procedure cache for TESTING ONLY !!
dbcc freeproccache

-- create an SP with different queries
create procedure usp_Kin_Test
as
select name, create_date from sys.objects
where type = 'P'
order by create_date desc

select name, OBJECT_ID from sys.procedures
order by name

-- execute the query
exec usp_Kin_Test

--- Check the DMV's for cached query plan

SELECT deqp.dbid
    ,deqp.objectid
    ,CAST(detqp.query_plan AS XML) AS singleStatementPlan
    ,deqp.query_plan AS batch_query_plan
    ,ROW_NUMBER() OVER (
        ORDER BY Statement_Start_offset
        ) AS query_position
    ,CASE
        WHEN deqs.statement_start_offset = 0
            AND deqs.statement_end_offset = - 1
            THEN '-- see objectText column--'
        ELSE '-- query --' + CHAR(13) + CHAR(10) + SUBSTRING(execText.TEXT, deqs.statement_start_offset / 2, (
                    (
                        CASE
                            WHEN deqs.statement_end_offset = - 1
                                THEN DATALENGTH(execText.TEXT)
                            ELSE deqs.statement_end_offset
                            END
                        ) - deqs.statement_start_offset
                    ) / 2)
        END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset) AS detqp
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
WHERE execText.TEXT LIKE '%usp_Kin_Test%' -- CHANGE here for your stored procedure !!

Note : You can do many cool things by digging into Plan cache as described by Jonathan here. Also refer to DMVs for Query Plan Metadata


You can look into tailing the oplogs of the shards yourself, thereby sending the data to a cluster in a remote data center incrementally.  Similarly you can look into tools like mongoriver from Stripe or mongo-connector from MongoDB labs if you don't want to do all the work yourself from scratch (but then are relying on a third party keeping that tool up to date or an unsupported labs project).  You will still need to wrap these utilities in some logic based on your needs and do some manual data verification to validate date between the two sites, and getting point-in-time snapshots of a cluster is non-trivial.

The only "out of the box", supported, way to do this (that I am aware of) is to use the on-premise backup capabilities of Ops Manager which is a paid offering from MongoDB themselves (full disclosure: I used to work for MongoDB).  It's a complicated process to keep a full copy of a sharded cluster in a separate location, so really your choices are to invest your own time and resources, or pay for a product to take care of it for you.


Resource Waits: Mathematical link between Wait Time and Average Waiter Count

I think a task is considered waiting if it is waiting for a whole second -- 1000ms.
So if wait time for 1 second is 2000ms that means that 2 tasks where waiting in that second.


Cassandra goes down after some some days

This seems to be an issue with the values for column_index_cache_size_in_kb and key_cache_size_in_mb in your cassandra.yaml file. As explained here, the database will stop when the total size of all the index data for a partition is greater than the value defined in column_index_cache_size_in_kb.

If the tuning of these values don't fix the issue, you may consider using larger instances for the nodes


MySql LOAD DATA with subquery performance issue

This is probably due to the fact that memory tables by default use a hash index instead of a btree index. Read more about it here.
Or maybe you don't use an index at all in your memory table?
A pretty similar performance problem is described in this blog post: Performance gotcha of MySQL memory tables
If you don't have an index on the id column, add one (btree!)

The MEMORY storage engine supports both HASH and BTREE indexes. You can specify one or the other for a given index by adding a USING clause as shown here:
   CREATE TABLE lookup
       (id INT, INDEX USING HASH (id))
       ENGINE = MEMORY;
   CREATE TABLE lookup
       (id INT, INDEX USING BTREE (id))
       ENGINE = MEMORY;

If you already have an index on that column, check with
show indexes from table_2;

of what index_type your index is. Convert to btree if necessary.


Select with limit, get total count, optimize query

MyISAM or InnoDB?

Your link does not say anything about SQL_CALC_FOUND_ROWS being slower.  And I would expect it to be faster if for no other reason than having one fewer round trip to the server.

The risk comes when you have thousands of rows.  Then either method may be spending more time than you can afford.  Come back when you encounter that; we can discuss workarounds.

Hmmm... I wonder if the LEFT makes the CALC run faster, since it should not have to reach into users after 12 rows.  But I fear that the optimizer is not that smart.  I'll guess that the following subquery will avoid that issue:

SELECT  SQL_CALC_FOUND_ROWS mr.*,
      ( SELECT  name
            FROM  users
            WHERE  id = mr.user_id
      ) AS name
    FROM  medias_ratings mr
    WHERE  mr.id_media = XXX  -- changed by EDIT
      AND  mr.id_client = YYY
    ORDER BY  mr.`date` DESC
    LIMIT  12;

Now, for indexes.

mr:  INDEX(id_media, id_client, date)  (Put the '=' columns first, then the ORDER BY.) -- changed by EDIT

users: Add a 'covering' index: INDEX(id, name) specifically to make this query faster -- it can run "Using index", as seen in the EXPLAIN.


Lookup data while in mysql cli edit mode (external editor) (\e)

Well, sort of.

In vim, save your work to a different file...

:w myfile.sql [enter]

Then exit vim with an artificial error so the mysql client won't bother to re-read the temporary file.

:cq [enter]

Then clear the input buffer in mysql.

\c [enter]

Then do what you've gotta do.

Then re-launch vim

\e [enter]

The editor will have your last command in it.  Clear that out and then paste the temporary file back into the buffer.

:r myfile.sql [enter]

Keep editing, then save and exit and your new content will be returned to mysql for execution.


Select rows from table 1 only if all corresponding rows have certain status in the join table

There are a couple issues I'll have to gloss over at this point due to lack of info:

no DDL; no sample data; no list of valid invoices.status values
"invoices are only in approved and closed status" implies an invoice needs at least 2 records ... one with status='approved' and one with status='closed'

Instead of trying to find all orders with invoices that are a) 'approved' and 'closed', or b) 'approved' or 'closed' ... just look for orders that have no invoices with a status other than 'approved' or 'closed':

select distinct
       o.id
from   orders o
where not exists(select 1
                 from   order_lines ol
                 join   invoice_lines il
                 on     il.order_line_id = ol.id

                 join   invoices i
                 on     i.id = il.invoice_id
                 and    ifnull(i.status,'UNDEFINED') not in ('approved','closed')

                 where  ol.order_id = o.id)


Read GPS Coordinates into LineString from Table

You can use dynamic SQL to build the string to feed into STGeomFromText(). I don't think you'll be able to do it relationally (though, to be fair, this is not my expertise). This will get you going until (if) something better comes along.

declare @Geo table(long float, lat float)
insert into @Geo(  long, lat ) values
               ( -121.527200, 45.712113 ),
               ( -121.517265, 45.714240 ),
               ( -121.511536, 45.714825)

DECLARE
  @cols NVARCHAR(MAX) = N'',
  @sql NVARCHAR(MAX) = N'DECLARE @g GEOGRAPHY;
     SET @g = GEOGRAPHY::STGeomFromText(''LINESTRING (';

SELECT @cols += N',' + RTRIM(long) + N' ' + RTRIM(lat) FROM @Geo;

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql += @cols + N')'', 4326);'

SELECT @sql += N'SELECT @g.STPointN(' + RTRIM(ROW_NUMBER() OVER (ORDER BY long))
  + N');' FROM @Geo;

PRINT @sql;
EXEC sys.sp_executesql @sql;


There are 3 choices.  VARCHAR is one of them; probably the 'worst'.

You suggested a second one -- an extra table.

The third is:  ENUM('one_option', 'another', ...).  It will take only 1 byte (like TINYINT).  You can directly use the strings that you are currently using with VARCHAR.  If you ever need to add another option, ALTER TABLE will be 'instant' if you add the new option on the end of the list.

It is useless to index low-cardinality columns; the optimizer will decide that a table scan is faster and ignore the index.  However, a 'composite' index that includes such a column is often useful.


This may be helpful: http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

Sorry, I know this belongs in the comments section, but SO won't let me comment until my rating is above 50 :(.


There is no other way than shrinking a db to claim back unused space.
No matter if you truncate or delete data or update with smallers length of data, the .mdf file will stay the same size. Which is good, because shrinking has negative side effects such as fragmentation of files and indexes.

If that table you are archiving later on grows again to the bigger size shrinking will not be a good option here. It's better to have that space already allocated rather than do heavy IO operations on the sub system in some days again. If you know that this table will not grow that big, because you put some maintenance in place, shrink that db once and be prepared to rebuild your indexes.


Fast way to drop schema with large amount of objects

Several years ago I did put together a package of db2utils which included a procedure called DROP_SCHEMA. This was written before ADMIN_DROP_SCHEMA became available in DB2, and uses a fairly crude mechanism for determining what to drop, and in what order. To whit: it uses a large UNION query to get all objects belonging to the schema from the various SYSCAT views:

WITH DROP_LIST (CREATE_TIME, SCHEMA_NAME, DDL) AS (
    SELECT
        CREATE_TIME,
        TABSCHEMA AS SCHEMA_NAME,
        'DROP ' || CASE TYPE
            WHEN 'A' THEN 'ALIAS'
            WHEN 'H' THEN 'TABLE'
            WHEN 'N' THEN 'NICKNAME'
            WHEN 'S' THEN 'TABLE'
            WHEN 'T' THEN 'TABLE'
            WHEN 'U' THEN 'TABLE'
            WHEN 'V' THEN 'VIEW'
            WHEN 'W' THEN 'VIEW'
        END || ' ' || QUOTE_IDENTIFIER(TABSCHEMA) || '.' || QUOTE_IDENTIFIER(TABNAME) AS DDL
    FROM SYSCAT.TABLES
    UNION
    SELECT
        CREATE_TIME,
        TRIGSCHEMA AS SCHEMA_NAME,
        'DROP TRIGGER ' || QUOTE_IDENTIFIER(TRIGSCHEMA) || '.' || QUOTE_IDENTIFIER(TRIGNAME) AS DDL
    FROM SYSCAT.TRIGGERS
    UNION
    SELECT
        CREATE_TIME,
        ROUTINESCHEMA AS SCHEMA_NAME,
        'DROP ' || CASE ROUTINETYPE
            WHEN 'F' THEN 'SPECIFIC FUNCTION'
            WHEN 'M' THEN 'SPECIFIC METHOD'
            WHEN 'P' THEN 'SPECIFIC PROCEDURE'
        END || ' ' || QUOTE_IDENTIFIER(ROUTINESCHEMA) || '.' || QUOTE_IDENTIFIER(SPECIFICNAME) AS DDL
    FROM SYSCAT.ROUTINES
    UNION
    SELECT
        CREATE_TIME,
        TYPESCHEMA AS SCHEMA_NAME,
        'DROP TYPE ' || QUOTE_IDENTIFIER(TYPESCHEMA) || '.' || QUOTE_IDENTIFIER(TYPENAME) AS DDL
    FROM SYSCAT.DATATYPES
    UNION
    SELECT
        CREATE_TIME,
        SEQSCHEMA AS SCHEMA_NAME,
        'DROP SEQUENCE ' || QUOTE_IDENTIFIER(SEQSCHEMA) || '.' || QUOTE_IDENTIFIER(SEQNAME) AS DDL
    FROM SYSCAT.SEQUENCES
    WHERE SEQTYPE <> 'I'
    UNION
    SELECT
        CREATE_TIME,
        SCHEMANAME AS SCHEMA_NAME,
        'DROP SCHEMA ' || QUOTE_IDENTIFIER(SCHEMANAME) || ' RESTRICT' AS DDL
    FROM SYSCAT.SCHEMATA
)
SELECT CREATE_TIME, DDL
FROM DROP_LIST
WHERE SCHEMA_NAME = ASCHEMA!

(Full source; the QUOTE_IDENTIFIER function is another utility in the package)

It then orders the result in descending order by creation time, running the SQL DROP statements with EXECUTE IMMEDIATE:

BEGIN ATOMIC
    FOR D AS
        SELECT DDL
        FROM TABLE(X_DROP_LIST(ASCHEMA))
        ORDER BY CREATE_TIME DESC
    DO
        EXECUTE IMMEDIATE D.DDL;
    END FOR;
END

This is simple, but crude especially in light of DB2's later introduction of deferred revalidation of objects. This can result in creation timestamp orderings for which the drop sequence is no longer correct. In practice, it mostly worked for me and on the rare occasions it didn't I could fall back to running the query manually to figure out what needed dealing with.

This brings me to the other major difference to ADMIN_DROP_SCHEMA: error handling. ADMIN_DROP_SCHEMA takes several parameters which are used to construct and fill a table with error codes and messages for post-analysis.

While comprehensive, I found this didn't work well with most of my use-cases. Most of the time I was using these tools to tear down development / testing schemas, then tweaking a creation script and re-running it, or using it to destroy relatively simple personal schemas when users were deleted. Hence I designed DROP_SCHEMA deliberately to be atomic: it either works and the schema disappears in its entirety, or it fails with an error and the schema remains in its entirety (assuming you rollback the containing transaction).

I did generally find it faster than ADMIN_DROP_SCHEMA but that wasn't an intention of the design, and given I haven't maintained it for several years there may well be new schema-based objects in DB2 which it doesn't correctly handle. Still, it may serve as a useful starting point for others' efforts.


Recover the disk space after deleting rows from table

You will have to take the table offline to prevent any reads or writes that would get locked  and probably have to roll back. If the table is called mydb.mytable, here is what you must do:
USE mydb
DROP TABLE IF EXISTS mytablenew;
CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable RENAME mytableold;
INSERT INTO mytablenew SELECT * FROM mytableold;
ANALYZE TABLE mytablenew;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

Any queries attempting to read from or write to mytable will fail immediately rather than hang on a lock from OPTIMIZE TABLE.
Give it a Try !!!
UPDATE 2013-08-26 15:24 EDT
Look back at the code I suggested. Please note the line
ALTER TABLE mytable RENAME mytableold;

That line takes the table offline with respect to the application.
To get a good idea how long it should take to optimize run the following:
USE mydb
CREATE TABLE mytablenew LIKE mytable;
INSERT INTO mytablenew SELECT * FROM mytable;

However long this takes, is how long it will actually take to run.


How to enforce strict handling of GROUP BY with non-aggregate / bare columns or UPDATE with multiple-row SELECT in SQLite

SQLite implicitly adds a "LIMIT 1" to all scalar subqueries. There is no built-in mechanism to change this; you would have to modify SQLite's source code.


The ALTER TABLE statement conflicted with the CHECK constraint

The error message is self-explanatory: Your check constraint is trying to enforce that all values in Datecom are at least one day earlier than Datelivr, but you must have at least one row where this is not true - either Datecom is the same day, later, or one of the values is NULL. Find those rows using:

SELECT Numcom, Datecom, Datelivr
FROM dbo.Commade
WHERE datediff(day,Datecom,Datelivr) <= 0
  OR Datecom IS NULL
  OR Datelivr IS NULL;


Sql*Plus ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

When you use sqlplus userName/myPassword@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=asdasdasd.com)(PORT=1524))(CONNECT_DATA=(SID=AARCER1)))" then you don't need any tnsnames.ora file.

Your entry in tnsnames.ora file should be like this:

AARCER1 = (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST= asdasdasd.com)(PORT=1524))
    (CONNECT_DATA =
      (SID = AARCER1)
    )
  )

Then you can shorten your command to sqlplus userName/myPassword@AARCER1 - that is the purpose of tnsnames.ora.

In order to use tnsnames.ora you must also have file sqlnet.ora.
There you need an entry like this:

NAMES.DIRECTORY_PATH = (TNSNAMES)

However, default value is NAMES.DIRECTORY_PATH=(tnsnames, ldap, ezconnect), so actually you can skip it.

Set environment variable TNS_ADMIN to location of these files, i.e.

export TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin


On which machine should TCP Chimney Offload be disabled?

Bit of a carbon copy of a previous answer...

Personally, I disable it at both ends of the pipe but the problem usually manifests at the database end (due to the comparatively high traffic). As described in this MSDN blog post, I would start with disabling in the OS with:

netsh int ip set chimney DISABLED

It may be necessary to disable the features at the card driver level in some cases. It certainly won't hurt to do so by default.


Firstly,

  Change data capture is available only on the Enterprise, Developer,
  and Evaluation editions of SQL Server.

So that may decide for you if any of your customers will not have the enterprise editions, or you don't yet know you will be using the enterprise editions.  (As the spec includes "multiple future applications" this may be an real issue for you)

Unlike triggers it is not real time, this is both an advantage and a disadvantage.    Using triggers always slow down an update.

I worked on one system when we used triggers (generated by CodeSmith), as well as tracking all the changes to the records, we also linked the changes together to a “history” table that included the module of the application that made the change, and the UI item the user used to make the change.

However you may be best solving this at the application level, by say writing all update to a message queue that is then replayed to create a database at any given point of time, see Temporal Patterns on Martin Flowler blog for a good overview of options.


What is the best sql data type for x.x.x

I can't say it would be the best ("best" in what sense?) but one way to do this is to have the 4 numbers in 4 separate TINYINT columns.

You can have the PRIMARY KEY as a composite 4-column one or as a persisted column. Considering from the data provided that the 4th number can be NULL, I'd choose the persisted as primary key and add a unique constraints on the 4 columns:

create table versions
( a tinyint not null,
  b tinyint not null,
  c tinyint not null,
  d tinyint null,
  version as cast( concat( a, '.', b, '.', c,
                           case when d is null then '' else concat('.',  d) end
                   ) as varchar(15))
      persisted,
  constraint version_uq
      unique clustered (a,b,c,d),
  constraint versions_pk
      primary key (version),
) ;

Tested at rextester.com.

Another option would be to use the HierarchyID type.


bind variable and shell scripting with sqlplus

Sure it is possible:

[oracle@o71 ~]$ cat 1.sh
#!/bin/bash

SH_NUM=10

sqlplus -S bp/bp<<EOD
set lines 220 pages 5000
col plan_table_output format a50
var a number;
exec :a :=${SH_NUM};
insert into t1 values(:a);
commit;
select * from table(dbms_xplan.display_cursor(format=>'basic'));
exit
EOD

Execute it (I just put DBMS_XPLAN.DISPLAY_CURSOR in there to see the last statement executed):

[oracle@o71 ~]$ ./1.sh

1 row created.

Commit complete.

PLAN_TABLE_OUTPUT
--------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
insert into t1 values(:a)

-----------------------------------------
| Id  | Operation                | Name |
-----------------------------------------
|   0 | INSERT STATEMENT         |      |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |
-----------------------------------------

12 rows selected.


Why does a query run slower in a Stored Procedure than in the Query window?

As Martin pointed out in the comments, the problem is that the query is using a cached plan which is inappropriate for the parameters given.

The link he provided on Slow in the Application, Fast in SSMS? Understanding Performance Mysteries provided a lot of useful information which lead me to some solutions.

The solution I am currently using is to copy the parameters to local variables in the procedure, which I think makes SQL re-evaluate the execution plan for the query anytime it's run, so it picks the best execution plan for the parameters given instead of using an inappropriate cached plan for the query.

Other solutions which may work are using the OPTIMIZE FOR or RECOMPILE query hints.


Average, between specific date/time, of the difference between current field and previous field

I used your SQL Fiddle and posted a working solution here.

WITH Calc AS
(
    SELECT
        S.Pair,
        S.Date,
        S.Time,
        S.Bid,
        ABS(S.Bid - T.Bid) AS Diff
    FROM dbo.forexData AS S
    CROSS APPLY
    (
        SELECT TOP (1)
            T.Bid
        FROM dbo.forexData AS T
        WHERE
            T.Id > S.Id
            AND t.Pair = s.Pair
        ORDER BY
            T.Bid DESC
    ) AS T
)
SELECT
    Pair,
    AVG(DIFF)
FROM Calc
GROUP BY
    Pair;

To summarize:

I fixed a hole in your existing logic - currently your CROSS APPLY doesn't link to the same pair value so if you have multiple pairs then your DIFF could be incorrect.
I put the fixed logic into a CTE
I queried the CTE with a GROUP BY on pair
I added an ORDER BY specification to determine the TOP ordering

You can add a date filter in a WHERE to the outer SELECT if you desire.


Fixing an error 9004 without taking the database offline

Not knowing much else about your problem, if your database is online and you're not using anything that may be actively using part of the log - such as Mirroring, AGs, Replication, etc, you could attempt removing that part of the log from the sweep by changing to the simple recovery mode (if in bulk or full) and issuing a checkpoint. Then go back to your normal recovery model and either take a full or differential to restart a valid LSN chain.

This may or may not work for you depending on what the actual underlying cause is, but should work for most cases caused by a state of 6.

Please note: I am not advocating that you do anything to compromise your database. If the database is online and functioning, this won't cause any data loss nor would it be destructive other than to your LSN chain which can be bridged.


Yes,

Instant File Initialization works on TempDB Data files.  As with any other database, it doesn't work on the log file.  See Aaron's Blog Post to see where he shows IFI working with TempDB via setup.


Avoid already existing objects when importing into Oracle

Exclude the schemas with the exclude parameter. E.g:

EXCLUDE=SCHEMA:\"IN\(\'SYS\',\'SYSTEM\',\'OUTLN\',\'ORDDATA\',\'OLAPSYS\',\'MDDATA\',\'SPATIAL_WFS_ADMIN_USR\',\'ANONYMOUS\',\'SPATIAL_CSW_ADMIN_USR\',\'ANONYMOUS\',\'FLOWS_FILES\',\'APEX_PUBLIC_USER\',\'APEX_030200\',\'OWBSYS\',\'OWBSYS_AUDIT\',\'SCOTT\',\'SYSMAN\',\'MGMT_VIEW\'\)\"

The list of schemas depends on the database version and options installed.

In a parameterfile, you can use the above without \ (escaping special characters).


Receiving error while creating stored procedure to insert Excel files into a table

Here is your solution:

Create Procedure [dbo].[OpenExcel]

 @FileName nvarchar(2000),

  @TableName varchar(2000)

AS Begin

    declare @Provider nvarchar(2000);

    set @Provider = 'Microsoft.ACE.OLEDB.12.0';

    declare @RetValue nvarchar(4000) ;

    set @RetValue='select * from openrowset("' + @Provider + ''',''Excel 8.0;Database=' + @FileName +';hdr=no'+''', ''select * from [Sheet1$]" )'

    declare @Bufr nvarchar(4000)

    set @Bufr='delete from '+@TableName+';'

    set @Bufr=@Bufr+'insert into '+@TableName  exec(@RetValue)

End


Slow MySQL query with Order and Group by

As @Back in a Flash has suggested, an index on created_at in user_articles will probably help if the optimizer chooses to use it... but the optimizer might not catch on to the viability of that index, because you're doing something else that needs an explanation.

You're using GROUP BY but you're not aggregating anything that I can see.  If you're not looking for the MIN(), MAX(), SUM(), COUNT(), or the result of some similar aggregate function, then your use of GROUP BY here is incorrect.  By default, MySQL does not throw an error when GROUP BY is used like this.

It looks as if multiple rows in user_articles could reference the same row in articles.  Your query, as written, will only return one matching row from user_articles for each referenced row in articles, and the selection of that row will not be deterministic.

That doesn't seem like what you want to be doing.  If it is, you shouldn't be doing it this way. :)

If it isn't... or if each row in user_articles references a unique row in articles, then your query is unnecessarily burdening the optimizer by insisting that this is indeed what it wants the server to try to do.  As the query is written, the GROUP BY is superfluous at best, and possibly contributing to the performance problem, since, even if you had an index on created_at, that conflicts with the fact that the server also needs to GROUP BY (potentially) a different index.


SQL Server : Execute as multiple users simultaneously?

No, you can't.

You could create user C that has permissions of A and B...


There are many factors that could be causing this slow down of inserts as you have described.

Something that I do not see in your information is the Database / Table size that the inserts are going into.  As the table grows, the longer your inserts will take due to table size, fragmentation and writing the same data to your indexes.

The DM queries you are running have given you some good information.  I would also recommend using Brent Ozar's toolkit. Link

I would suggest you use SP_BlitzIndex and SP_BlitzCache.  These will give you another view of what is actually happening in the database and what the insert queries you are running are doing at that time.

I would also check when the last time a DBCC CHECKDB was run and also when you last rebuilt your indexes and statistics.

Thanks for the updates, see below:

Some more info:

Not a huge amount of read/writes so I'm not too concerned.

Do you know when the last time the trans_all index was rebuilt?  If you only have one covering index and many inserts, it will take longer each time when inserting data to it if it is out of date.  Below is a query to check fragmentation on your database tables:

Use <Database>
GO

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

UPDATE 1:

MAX DOP settings can affect your query run time, but I can't be sure it is doing so without seeing the query you are using and then looking at the estimated plan results to see what SQL server is using.  There are two statistics to use to find out how the query is affected when adjusting the MAX DOP.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

Here is some information by Kendra Little over at Brent Ozar on using these to give you guidelines.

tsql-measure-performance-improvements

and

q-can-high-maxdop-make-a-query-slower

UPDATE 2:

I would recommend increasing your Log size if it is 98% full.  Either add another trans log or increase it.  You might also look at TEMP_DB and see what it is doing because SQL using that as a dumping ground for many things.

Empty space isn't really an issue unless you are out of room to grow.  It is just telling you how much room is left until the next auto grow.


Regex replace with empty string results in NULL characters in between


The $indexstats command was added for the MongoDB 3.2 server release in Dec 2015. Without server support for measuring or logging index use, I'm not aware of any obvious workaround short of modifying the 2.0.x server code and building a custom server.

MongoDB 2.0 is a relatively ancient server version: it was first released in Sept 2011 and reached End-of-Life in March 2013. I'd strongly recommend planning to upgrade to a modern & supported version of MongoDB (currently 3.4+, although 3.4 will reach EOL in Jan 2020). There are numerous essential bug fixes, stability, and security improvements since 2.0.


What problems are solved by splitting street addresses into individual columns?

I spent 7 years developing software for a publishing company and one of the hardest problems we ever tackled was parsing street addresses in subscription lists.  It is useful to split up addresses into distinct fields, but you can never, EVER design for every possible pathological aberration of address formats and components the human brain can devise.

Every locality can have its quirks, and that's just in the US. Throw in other countries and things get unmanageable very quickly for any approach that wants to parse every address.  Just two examples:

In Spain, the street number always comes after the street name and a comma, and many addresses contain a floor number ordinal, such as 1° or 3ª, along with abbreviations for "left" ("Izda" meaning left-hand-door after you get up the stairs), "right" ("Dcha") or other possibilities.  Now multiply that quirkiness by the number of different countries and areas with different historical customs for addresses... (Japan? Rural England? Korea? China?)

In Portland, OR, there are N-S and E-W axes that divide the city into NW, NE, SW and SE quadrants (as well as a N "quadrant", but I digress).  N-S streets are numbered incrementally East and West from this axis, and addresses on E-W streets are dictated by the N-S street number being the "hundred block" of the number (i.e. a house on an E-W street between 11th and 12th avenues would have a number like 1123).  Pretty standard stuff for US addresses.

Every so often you run into a Portland address like 0205 SW Nebraska St.  A leading zero? WTF? There goes my integer column for house "number".

When the grid was set up, the N-S axis was defined by the Willamette river.  Everything to the East of the river was NE or SE, and West of the river NW or SW.  As the city grew south they ran into the inconvenient fact that the river meanders to the East, so projecting the axis South you have this problematic area that's on the "West" side of the river but East of the axis.  The solution was to add a leading zero, in effect a minus sign, with the numbers incrementing towards the East from the axis line.

If I were you I'd give up hope of designing the ultimate system.  You cannot cover all possibilities, and new ones will be created as humanity pushes into previously undeveloped land.

For US addresses, take a look at what the USPS has already done in address standardization, and remember to make the house_number column a varchar. While you're at it figure out how you're going to parse 1634 E N Fort Lane Ave.

For the rest of the world, I'd probably try to abstract additional fields to cover 80-90% of what is likely to come up, and provide a set of uninterpreted fields that can handle everything else when necessary. I.e. if your parser fails to handle an address, save it unparsed and flagged as such.  If you do manage to parse an address, make sure you remember the order in which you found the various fields so you can reassemble it into something deliverable.

I was going to say that the most important field is going to be post code, but even that is not a given in many places.

Good luck.  This can be a fun and extremely frustrating endeavor but the key to sanity is to know when to quit trying and just store the input unparsed, or partially parsed with the original input as backup.


Why does my procedure run slowly until I update statistics?

SQL Server uses a cost based formula for determining how to satisfy queries.

"It'll cost 10 random reads in a billion row table vs 1000 sequential reads. I think I can satisfy the needs faster by doing the 1000 reads since they'll all in order" and decision made off it goes. Unfortunately, when the engine opens the data file to find those 1000 sequential rows, the data has been updated and instead of living in there, there's simply a pointer saying "sorry we had to move, try this address in the next state" And you now have 1000 forwarding rows to contend with. The database engine can't go back and say let's try re-evaluating this costing metric because those 10 reads sound a lot better.

That's probably what is happening (simplified) with your data. The statistics on your data distribution are all out of whack after your ELT processing which is typical-you've just moved a pantsload of data so what was true before may not be as true now. Depending on usage, I will have an explicit call to a reindexing job (thank you Ola) after ETL is complete so that now that all my data is up to date, I need to get all the metadata the server uses up to date as well.


Triggers only work on the rows which are affected by the statement causing the trigger to execute.

When you want to update a whole table, use a normal statement, not triggers.

When you have data, that can be easily calculated, don't store this in a table. Either you calculate it in your SELECT statement or create a view.

Example:

CREATE VIEW view_with_day_difference_calculated AS
SELECT start_date, TIMESTAMPDIFF(day, start_date, NOW()) AS days
FROM your_table;

Then all you have to do is this in the future.

SELECT * FROM view_with_day_difference_calculated;


How to check if a trigger fires on INSERT, UPDATE or DELETE statements?

The sys.triggers table does not expose this data, instead we have two options:

OBJECTPROPERTY

Using the OBJECTPROPERTY metadata function we can return the action as a property as below:

OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger')
OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger')
OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger')

This will return a simple bit value indicating whether the trigger fires on that particular action.

sys.trigger_events

As per the docs this contains a row per event for which a trigger fires. Specifically the columns we're interested in are type and type_desc.

type is a foreign key to the sys.trigger_event_types table - type_desc seems to contain the type_name column from the sys.trigger_event_types table.

This data can be returned using the following script:

SELECT
    [o].[name]
   ,[t].[type_desc]
FROM
    [sys].[trigger_events] AS [t]
    INNER JOIN [sys].[objects] AS [o] ON
        [o].[object_id] = [t].[object_id]
WHERE
    [o].[name] = 'TriggerNameHere';


Require to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2

When calling sp_send_dbmail, you can set @body_format='HTML' and place the HTML source code in the @body parameter.

Important: I'm not sure if there will be a plain-text version of your message for recipients that can't or won't read HTML formatted e-mail.

Like Mark says, you should check the documentation for sp_send_dbmail for more information. Also, if you want the challenge, you could use the FOR XML construct in a SELECT statement to generate an XML document where the elements are named and arranged like HTML tags.


Reformatting master MySQL server and temporary solution

Globally speaking, I would essentially be performing the following steps in the same situation.

Before starting, backup everything on both machines.

Put the website or application into maintenance, preventing users from accessing the database.  Also make sure there are no imports/updates/activity coming in from cron jobs.  You can verify this by running SHOW FULL PROCESSLIST; in MySQL.
Make sure the slave server is fully synced with the master. Read notes below.
Stop replication on the slave server STOP SLAVE; and turn off the master server.
Exchange IP addresses between the old and new master server.  Or point the application's database connection to the slave server.
Bring the application back online.
Perform your maintenance on the old master server.
Once your old master server is back up and running
Put the website or application back into maintenance.
Backup your new master server's database and restore it to the original and now functional master server.
Exchange IP addresses once again between the two machines. Or point the application's database connection to the master server again.
Reconfigure replication between the newly restored master server and the original slave server.  Read notes below.
Bring the application back online.

For step 2, when you are making sure that the master and slave are synced, you would do this. Comparing the current log file and file position for both servers.

On the master server

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         |                  |
+---------------+----------+--------------+------------------+

On the slave server

SHOW SLAVE STATUS;

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master.server.ip.address
                Master_User: root
                Master_Port: 3306
              Connect_Retry: 3
            Master_Log_File: mysql-bin.003
        Read_Master_Log_Pos: 73

Since you are planning on reverting back to the original infrastructure, there is no need to setup the slave server as a new master.  You can leave the slave server's configuration in place, so that once the master server is back online, you can restart replication more quickly.

Configuring replication

Rolando answered a similar question with an easy to read how to on setting up replication.

Clarification about master slave configuration in mysql


Any reason not to add 'rowversion' column?

Other than the minor things you've already picked up on (additional storage requirements, and potential performance differences as a result of requiring more I/O for the same number of rows), no, I can't think of any real gotchas with adding a ROWVERSION column to these tables.

One potential issue, though, with merge replication specifically, is if Access grabs a row from one of the servers, then tries to grab a copy of the same row from a different subscriber (not sure if that is possible in your architecture). The reason is that the ROWVERSION values are re-generated at each subscriber, so they won't match the source - this may make it look like the row has changed, when it hasn't. Maybe not possible, or maybe it's ok that such a row always be considered "dirty," but something to keep in mind (and probably test thoroughly).

You might also want to review the merge replication section in this document about schema changes in general.


Create a table in your test database with the users that should have access, create a restore script that copies that table into a temp table, do your restore, recreate the table in your test database, use that table to create the appropriate access permissions.