How do I shrink the innodb file ibdata1 without dumping all databases?

This is one of the most controversial topics I have ever dealt with over the years as a MySQL DBA and in the DBA StackExchange.
To put it mildly, there is simply no other way to shrink ibdata1. With innodb_file_per_table disabled, every time you run OPTIMIZE TABLE on an InnoDB table, ibdata1 grows rapidly. Data that are dropped using DROP TABLE and DROP DATABASE cannot be rolled back because they are DDL, not DML. I believe Oracle and MSSQL can rollback DDL. MySQL cannot do that.
There are several classes of information that reside in ibdata1

Table Data
Table Indexes
Table MetaData
MVCC Control Data
Double Write Buffer (Background write to prevent reliance on OS caching)
Insert Buffer (Managing changes to non-unique secondary indexes)

Using innodb_file_per_table=1 will allow you to create new tables with table data and table indexes being created outside ibdata1. You could extract any tables still inside ibdata1 using ALTER TABLE ... ENGINE=InnoDB; or OPTIMIZE TABLE but that will leave that big gaping unused space in ibdata1.
Notwithstanding, you must clean up the InnoDB infrastructure. I already wrote StackExchange posts on how and why to do this:

May 21, 2012 : How large will a MySQL database be relative to the dump file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?

Good News
You only have to dump the data, reload one more time and never revisit this issue again. Running OPTIMIZE TABLE afterwards will indeed shrink the .ibd tablespace file for any InnoDB table.


Critique of indexes:

go:

  KEY `idx_visible` (`go_visible`),  -- DROP; indexing flags is rarely useful
  KEY `idx_maingal` (`go_visible`, go_added),   -- for WHERE

gl:

INDEX(visible, added)  -- in this order

meta:

  PRIMARY KEY (`meta_id`),  -- see below
  KEY `idx_model` (`model_id`,`model`) USING BTREE,
  KEY `idx_model_id` (`model_id`), -- DROP since the above handles it
  KEY `idx_model_key` (`model`,`key`) USING BTREE  -- Prefix indexing useless here

For key-value, it is rarely necessary to have an AUTO_INCREMENT id (meta_id) since there is probably a combo of other columns that is unique.  See my tips here.


Getting error when trying to use OPENQUERY Msg 7321, Level 16, State 2, Line 1

I would suggest you change your original SQL Statement as follows:

SELECT *
FROM OPENQUERY([XXX], 'SELECT * FROM database.table where left(MBCUNO, 1) = ''a'' and (MBCONO=650) ' )

This has the two variable swopped around, and the like % exchanged for a left(mbcuno,1).

Ensure that all your table & column references are correct


Often the simplest way to express the query will perform the best.

The following query should be close to optimal:

select e.id
from entry e
join class c on c.id = e.cid
join principal p on p.id = e.pid
where e.object_id = 45
and c.name = 'Class#3'
and (p.id = 2 and p.role or p.id = 3 and !p.role);

But a lot depends on your data and your indexing. Compare execution plans to be sure.


How do you do two date calculations in one SQL Statement?

Given a date:

DECLARE @date datetime = CONVERT(date, GETDATE());

--The beginning of this month:

SELECT DATEADD(DAY, 1-DAY(GETDATE()), @date);

-- The beginning of last month - just subtract a month:

SELECT DATEADD(MONTH, -1,
  DATEADD(DAY, 1-DAY(GETDATE()), @date));

-- Then to get the 17th of last month, just add 16 days:

SELECT DATEADD(DAY, 16, DATEADD(MONTH, -1,
  DATEADD(DAY, 1-DAY(GETDATE()), @date)));

That will work on 2008 and above. On 2012 you can write it a little shorter:

DECLARE @date datetime = DATEADD(MONTH, -1, GETDATE());

SELECT DATEFROMPARTS(YEAR(@date), MONTH(@date), 17);

If you are doing this for searching, though, and not output, I highly recommend adjusting the date range parameter values, not applying DATEADD() operations data in the loanFirstPmtDate column.


MS SQL Server 2008: How to improve performance after deleting transaction log file

The problem probably occurred because in order to delete the transaction log you would have had to stop the SQL Server. This causes the buffer pool (cached data and execution plans) to be erased - nothing can be in memory for a process that isn't running anymore. Now that you've restarted your server, all of your execution plans need to be recompiled.

I would also guess that you're running the server with the default transaction log settings wich specify a default size of 1MB for the transaction log and an automatic growth of 10%. These are very bad settings for performance. The transaction log has to 0 out the file when it grows. You can read more about transaction log management in 8 Steps to Better Transaction Log Management and Database Maintenance Best Practices Part III - Transaction Log Maintenance.

The first question is what did you do to the transaction log? You should never delete a transaction log. This is how your database is able to recover from a crash.

The second question is do you have a log backup solution in place? That is the only reason your transaction logs are growing so large. I suggest looking at Ola Hallengren's excellent backup scripts.

Typically, you want to grow your transaction log to a fixed, sane, size to start with and leave it there. This will depend on your database, but in the past I've monitored log file growth over the course of the week with full database backups happening every morning and log file backups running every 15 minutes. Once I've figured out how large the transaction log can get, I size the transaction log to about 15% larger than that (but as a multiple of 8GB).


SQL Server's BACKUP DATABASE - how to access the original exception?

You can use extended events to capture all errors in your session (this will include some internal errors in your case that the user doesn't get to see normally).

Use the following code to do this (SQL Server 2008 minimum)

CREATE EVENT SESSION ErrorCatcher ON SERVER
ADD EVENT sqlserver.error_reported (WHERE severity >= 16)
ADD TARGET package0.ring_buffer
WITH (
     EVENT_RETENTION_MODE = NO_EVENT_LOSS,
     MAX_DISPATCH_LATENCY = 1 SECONDS
     )

ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = START

BEGIN TRY
    BACKUP DATABASE MyDatabase
        TO DISK = 'C:\MyDatabase.bak'
        WITH INIT,
        NAME = 'MyDatabase-FullBackup',
        STATS = 5;
END TRY
BEGIN CATCH
    DECLARE @target_data XML

    WAITFOR DELAY '00:00:01' -- Wait for the MAX_DISPATCH_LATENCY time

    SELECT @target_data = CAST(xet.target_data AS xml)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
       ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'ErrorCatcher'

    SELECT
        n.value('(@name)[1]', 'varchar(50)') AS event_name,
        n.value('(@package)[1]', 'varchar(50)') AS package_name,
        n.value('(@id)[1]', 'int') AS id,
        n.value('(@version)[1]', 'int') AS version,
        DATEADD(hh,
                DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
                n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
        n.value('(data[@name="error"]/value)[1]', 'int') as error,
        n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
        n.value('(data[@name="duration"]/value)[1]', 'int') as state,
        n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
        n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message
    FROM @target_data.nodes('RingBufferTarget/event') AS q(n);
END CATCH

ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = STOP

DROP EVENT SESSION ErrorCatcher ON SERVER

Of course you can create the event session only once and just start/stop it on demand. The session is persistent and remains on the server until you drop it.

The ring buffer target is resident in memory and with default settings uses up to 4 MB to capture events before it starts deleting old events (on a FIFO basis). The buffer is also released when you stop the session.


Is filtering on int better than filtering on date

I would answer with: Keep it as it is with datetime!

One of the reasons is simple really, data type precedence. So, if you were ever to compare a datetime to an integer, the engine will decide that rather than converting date->int then compare int = int, it will convert int->datetime and compare datetime = datetime. Which stands the same for every single data type (like varchar)  except for:

user-defined data types
sql_variant
xml

in that order of precedence.

Date comparison functions are also optimized to a crazy degree and they run blitzing fast, so you won't be gaining much in terms of performance, in fact you might end up losing performance since you have to do a join operation and then (again) do a where(how else would you filter by dates after all, generated ID != date you're comparing with).

So you need to ask yourself, is it really necessary to move date into a separate table? If you're planning on having a Data Warehouse, it might help with making the TimeDim table but other than that I see no reason to do so.

If you are interested in data precedence, you can see the list here (highest to lowest, top to bottom): https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017


If SUM() OVER() is not supported, you may try with the following approach. Rows are ordered by INV number as text (INV100, INV105, ...), not as number (100, 105, ...).

Input:

CREATE TABLE #PO (
    PO varchar(10),
    DocEntry int,
    Qty int
)
CREATE TABLE #INV (
    INV varchar(10),
    DocEntry int,
    Qty int
)
INSERT INTO #PO (PO, DocEntry, Qty) VALUES ('A123', 1, 500)
INSERT INTO #PO (PO, DocEntry, Qty) VALUES ('B123', 2, 200)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV100', 1, 100)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV102', 2, 10)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV105', 1, 200)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV106', 2, 190)
INSERT INTO #INV (INV, DocEntry, Qty) VALUES ('IV112', 1, 200)

Statement:

SELECT
    p.PO,
    p.Qty,
    i.INV,
    i.Qty AS Quantity,
    Total = p.Qty -
            (
             SELECT SUM(rt.Qty)
             FROM #INV rt
             WHERE (rt.DocEntry = i.DocEntry) AND (rt.INV <= i.INV)
            )
FROM #PO p
LEFT JOIN #INV i ON (p.DocEntry = i.DocEntry)
ORDER BY p.DocEntry, i.INV

Output:

PO      Qty INV     Quantity    Total
A123    500 IV100   100         400
A123    500 IV105   200         200
A123    500 IV112   200         0
B123    200 IV102   10          190
B123    200 IV106   190         0


One can say that a proper entity-relationship diagram is that which was created using the constructs introduced by Dr. P. P. Chen (e.g., rectangular and diamond-shaped boxes) in his famous 1976 paper entitled The Entity-Relationship Model—Toward a Unified View of Data.

Regarding your specific comparison, Figure 1 displays symbols (perhaps shapes) that look much closer to the Chen’s originals than the ones shown in Figure 2.

Pertinent considerations

But, if a certain drawing portrays (a) entity types, and (b) the corresponding entity types interrelationships, then it can be considered some kind of entity-relationship representation (and multiple methods are associated in a certain way with the work of Dr. Chen).

In this respect, IDEF1X is a data modeling technique —established as a standard in 1993 by the U.S. National Institute of Standards and Technology (NIST)— that takes Dr. E. F. Codd’s relational concepts (e.g., foreign keys and alternate keys) and combines them with entity-relationship ideas and first-order logic elements in order to assist in the composition of more rich and expressive diagrams (which can, e.g., depict supertype-subtype associations, entity type properties or attributes, etc.).

There are other efforts that produced, e.g., the enhanced entity-relationship approach, which also added new notions to the original view.

These paradigms (some more complete than the others) are platforms that were developed to capture business-domain-specific data meaning (i.e., data semantics). With their help, a practitioner can supply communication instruments that facilitate managing the data as it actually is, i.e., an important and independent asset, presenting the scenario of interest from a conceptual perspective (sometimes incorporating logical level aspects) to all the involved parties.

Asides

Concerning some aside comments, it must be said that (i) application program components are different from (ii) database structures, therefore they definitely hold different implications. In this manner, you can use “general” implements like UML sketches to shape object-oriented application program items, e.g., classes (basically, object templates), methods (i.e., object processes, behaviour), etc. In addition, application program components are part of the external level of a computerized information system, so any kind of sketch depicting them should not necessarily have a one-to-one correspondence with a diagram illustrating structures of the conceptual level.

That being so, and since software development is a profession that entails accuracy, identifying and employing the right tool for each clearly delimited task is paramount.


BACKUP WITH CHECKSUM works but DBCC CHECKDB detects problems

sp_BlitzErik wrote in a comment (since deleted):

  ...is asking [about the PAGE_VERIFY_OPTION] because of this: Blitz Results: Page Verification Not Optimal. The CHECKSUM option for backups will only throw errors if you're using the correct page verification option. It can sometimes be suboptimal if your database came from an older version of SQL Server.

And it will only throw errors, even with the CHECKSUM option in use for the page verify option, if the page became corrupted after writing to disk. And I don't suppose they're necessarily asking the question just because of that post - my guess is they have the option set to CHECKSUM already.

The scenario described in the question can and does happen when a page corruption happens in memory (either because of bad memory, a memory scribbler, or a SQL Server corruption bug), and then the corrupt page is written to disk with a valid checksum.

Subsequent reads of the page, including testing of the page checksum, will succeed. However, when DBCC CHECKDB (or a hapless query) runs, the corruption will become evident. In this case, it doesn't matter whether the BACKUP and RESTORE statements use CHECKSUM; they won't detect that corruption.

This is why you cannot just rely on using CHECKSUM everywhere and not run consistency checks.

Hope this helps explain what you saw.

From the errors you posted, it looks like the root page of that clustered index got whacked somehow.


MySQL Query - How to get most recent demographics?

Assuming you have a PRIMARY KEY or otherwise UNIQUE KEY called id (replace with whatever it's called in your table), consider the following query:

SELECT
  cardnbr,
  fname,
  lname,
  address,
  city,
  state,
  postal
FROM
  cards,
  (
    SELECT
      GROUP_CONCAT(top_id_per_group) AS top_ids
    FROM
      (
        SELECT
          SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY lastvisit DESC), ',', 1) AS top_id_per_group
        FROM
          cards
        GROUP BY
          cardnbr
      ) s_top_ids_per_group
  ) s_top_codes
WHERE
  FIND_IN_SET(id, top_ids)
ORDER BY
  cardnbr
;

The idea is to get, per cardnbr, the id where the lastvisit is most recent. Then, get all row data for those ids.

The above is a simplification of Selecting Top N Records Per Group, where N = 1.

Another way to solve it is described in Selecting Non Aggregated Column Data in GROUP BY. The query presented in this post actually provides better performance than the above, but uses some parsing and casting to get the final results (all columns parsed as strings, then cast back to their original types). Also see this post. Apologies for the many links, but each post presents with a different solution to the problem.


Duplicate result when I select with distinct values from foreign key constarint

Here is an example of how that could be achieved

--demo setup
Declare @Table1 table (id int, name varchar(20), lastname varchar(20), imageshortpath varchar(100), imagefullpath varchar(100))
insert into @Table1(id, name, lastname, imageshortpath, imagefullpath) values
(2108,'mudasir', 'khan','imageshortpath','imagefullpath')

Declare @Table2 table (id int, peopleid int, address varchar(20))
insert into @Table2(id, peopleid,address) values
(1,2108,'hyderabad'),(2,2108,'larkana')

--solution
select t1.*,
STUFF((
    SELECT ',' + address
    FROM @Table2 t2
    WHERE t2.peopleid = t1.id
    FOR XML PATH('')
    ), 1, 1, '') AS Address
from
@Table1 t1

| id   | name    | lastname | imageshortpath | imagefullpath | Address           |
|------|---------|----------|----------------|---------------|-------------------|
| 2108 | mudasir | khan     | imageshortpath | imagefullpath | hyderabad,larkana |

Check out this excellent answer about How Stuff and 'For Xml Path' work in Sql Server.


POSTGRESQL: Refresh the materialized view which effect when there are change based on columns

The most efficient solution is to not fire the trigger in that case:

CREATE TRIGGER trigger_tabel
AFTER INSERT
   OR UPDATE OF (column_a) --<< only fire the trigger if column_a is updated
   OR DELETE
ON name_table
FOR EACH ROW EXECUTE PROCEDURE sp_refresh_matview();

If you have multiple columns that should trigger the refresh, you can separate them using commas: OR UPDATE OF (column_a, column_c, column_d)

A less efficient solution is to check if the column changed inside the trigger function:

CREATE OR REPLACE FUNCTION sp_refresh_matview() RETURNS trigger A
$$
DECLARE
rec RECORD;
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'DELETE' OR
       -- add all columns that need to be checked for changes
       (old.column_b IS DISTINCT FROM new.column_b)
    THEN
      FOR rec IN
        SELECT *
        FROM pg_matviews WHERE (
            definition LIKE '%'||TG_TABLE_NAME||'%')
      LOOP
        EXECUTE 'REFRESH MATERIALIZED VIEW '|| rec.matviewname;
      END LOOP;
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql ;

If you are using the same trigger function for all tables, then obviously this only works if all tables have the same columns that you want to check. But not firing the trigger to begin with will be much more efficient and should be your preferred solution.


Cannot Delete FileStream Group from Sql Server 2008

I had the same problem.  Eventually I worked out that I needed to change the db recovery model from "Full" to "Simple", and then I could delete my filestream files.


Can I tell SQL Server to "do something" when a connection is closed?

SQL Server provides a built-in mechanism to support application locking. The locks supported are Shared, Update, IntentShared, IntentExclusive, and Exclusive. Deadlock detection is also built-in, though application lock deadlocks do not terminate the active transaction - you need to check the result code returned from the system procedure call. Books Online documentation:

APPLOCK_MODE
APPLOCK_TEST
sp_getapplock
sp_releaseapplock

Locks associated with the session are automatically released when the session is logged out.

Community Wiki answer generated from a question comment by @JonSeigel


Disable Saving/Exporting Results of TSQL Query in SSMS?

No, there is no way to handle this from the database/instance, unless you just deny them access to the database at all.

SSMS is a client-side tool, and the things that it can do with the data you've already let it consume are outside of server-side controls. Even if you could take those menu items away, there is always the PrintScreen button.


Excluding log shipped DB from current database maintenance plan (Ola's solution)

When you set up Log Shipping, it creates a job for transaction log backup. If you already have an existing transaction log backup job, irrespective of kind (maintenance plan, Ola's solution, SQL Agent job) should be disabled/deleted.
There is a workaround to keep the existing transaction log backup job and set up Log Shipping. You can disable the job that gets created during the Log Shopping setup.
Sample code:
-- Execute the following statements at the Primary to configure Log Shipping
-- for the database [HostName\SQL2019].[DbaDatabase],
-- The script needs to be run at the Primary in the context of the [msdb] database.
-------------------------------------------------------------------------------------
-- Adding the Log Shipping configuration

-- ****** Begin: Script to be run at Primary: [HostName\SQL2019] ******

DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId   AS uniqueidentifier
DECLARE @SP_Add_RetCode As int

EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
        @database = N'DbaDatabase'
        ,@backup_directory = N'\\logshipping\backup'
        ,@backup_share = N'\\logshipping\backup'
        ,@backup_job_name = N'LSBackup_DbaDatabase'
        ,@backup_retention_period = 4320
        ,@backup_compression = 2
        ,@backup_threshold = 60
        ,@threshold_alert_enabled = 1
        ,@history_retention_period = 5760
        ,@backup_job_id = @LS_BackupJobId OUTPUT
        ,@primary_id = @LS_PrimaryId OUTPUT
        ,@overwrite = 1

IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN

DECLARE @LS_BackUpScheduleUID   As uniqueidentifier
DECLARE @LS_BackUpScheduleID    AS int

EXEC msdb.dbo.sp_add_schedule
        @schedule_name =N'LSBackupSchedule_HostName\SQL20191'
        ,@enabled = 1
        ,@freq_type = 4
        ,@freq_interval = 1
        ,@freq_subday_type = 4
        ,@freq_subday_interval = 15
        ,@freq_recurrence_factor = 0
        ,@active_start_date = 20201029
        ,@active_end_date = 99991231
        ,@active_start_time = 0
        ,@active_end_time = 235900
        ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
        ,@schedule_id = @LS_BackUpScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
        @job_id = @LS_BackupJobId
        ,@schedule_id = @LS_BackUpScheduleID

EXEC msdb.dbo.sp_update_job
        @job_id = @LS_BackupJobId
        ,@enabled = 0

END

EXEC master.dbo.sp_add_log_shipping_alert_job

-- ****** End: Script to be run at Primary: [HostName\SQL2019]  ******


ibdata1 corrupted, recover from ib_logfile?

ib_logfile-s store only recent changes, so you won't recover all data from them.

It's possible however to fetch records directly from disk image, you don't need to restore ibdata1. Actually you even don't need a filesystem.

Check out data recovery toolkit from http://www.percona.com/software/mysql-innodb-data-recovery-tools .

You need something like

./page_parser -f disk.img

And then you can fetch records from InnoDB indexes.

UPDATE: Data recovery toolkit moved to GitHub


How to Insert all PDF files from a folder into a varbinary(max) column in SQL using a Query

So it sounds like you do not have to parse the pdf, just import the entire thing into one column. If so, you could try using SSIS. Create a data flow task and use the Import Column transformation, this should allow you to import the pdf as an image; you'd just need to specify the path to your files. As far as processing several files in the folder, create a ForEach Loop for the data flow task. And for the automation part, just schedule the ssis package via sql jobs to run at specific times or on a regular schedule.


Finding count of objects within a specified time window in SQL (Postgresql DB)

Use conditional aggregation:

select count(*) filter (where date_joined > now() - interval'24 hours') as last_24_hours,
       count(*) filter (where date_joined > now() - interval'48 hours') as last_48_hours
from auth_user

The filter clause is available since Postgres 9.4, for older versions you need to use a case statement:

select count(case when date_joined > now() - interval'24 hours' then 1 end) as last_24_hours,
       count(case when date_joined > now() - interval'48 hours' then 1 end) as last_48_hours
from auth_user


SQL Server making arithmetic abort enabled

As well as Marian's answer which is SQL Server Instance level, you can use ALTER DATABASE to set it individually. You can also use it on the model database so that all new databases inherit the setting


Select a row from one table, if it doesn't exist, select from another table

If it is preferable to select the rows from the first table, you should take out the filter that would remove them when the person exists in the other.
There’s also no need to distinct the rows, so use union all instead of union
select
  t1.*
from table_1 t1
union all
select
  t2.*
from table_2 t2
where t2.person not in (select person from table_1)


PAD_INDEX and FILLFACTOR on clustered Identity index

PAD_INDEX only applies to non leave level pages. By default those are filled full.

So specifying PAD_INDEX=ON together with FILLFACTOR is only useful when you specify a fillfactor smaller then 100%.

In your case, specifying PAD_INDEX is not necessary, since by default your leave pages are how you want them. Full.


Where does the stored procedure Searchinall come from?

When you create a new database in SQL Server the Model database is used as a template. Your new database will contain any stored procedurethat is in the model database.
From what you are describing it seems that the stored procedure, Searchinall is a custom procedure added to the model database by someone in your organization and then automatically included in any database that you created.


You can use two steps for large tables
First get all colum nnames from your
set session group_concat_max_len = 1000000;

select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name '
AND TABLE_SCHEMA = 'schema_name'
order BY ORDINAL_POSITION

And use the collected information for the next Query instead of ColumnName1-3
SELECT 'ColumnName1', 'ColumnName2', 'ColumnName3'm, 'ColumnName4'
UNION ALL
SELECT `ColumnName1`, `ColumnName2`, `ColumnName3`, `ColumnName4`
    FROM table_name
    INTO OUTFILE '/path/result.txt'

If the table is not that big, you can combine the both with user defined variables and prepared statements


Why is my PostgreSQL ORDER BY case-insensitive?

So en_US.UTF-8 and en_US.utf8 have different sort orders?

No, these both are the same, just a different naming convention.

  I'm mystified by why the Debian version appears to be case-insensitive and the OS X version is not.

Yes, you are correct. This is the default behavior on Mac. Collations don't work on any BSD-ish OS (incl. OSX) for UTF8 encoding.

Here is a reference to prove that:

Problems with sort order (UTF8 locales don't work

As a_horse_with_no_name said, Postgres uses the collation implementation from the OS. There is no way to get the same result on both operating systems.

In your case you may(I said maybe)do like this: ORDER BY lower(fieldname).


I think this expected behavior for an inactive session that has been killed.

I have a session connected as a user 'JAY' which is in 'INACTIVE' state.

 [oracle@myserver datafile]$ sqlplus jay@hrpdb
 Connected to:
 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Let's check session details using sys user.

SQL> col username format a20

SQL> select sid,serial#,username,status from v$session where username ='JAY';

       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        15      36102 JAY                  INACTIVE

Let's kill it.

SQL> alter system kill session '15,36102';

System altered.

Check session details once again.

SQL> select sid,serial#,username,status from v$session where username ='JAY';

       SID    SERIAL# USERNAME             STATUS
---------- ---------- -------------------- --------
        15      36102 JAY                  KILLED

It is still there in 'KILLED' state.

Now let's try to do some operation in Jay's session which was inactive and killed.

SQL> select * from tabs;
select * from tabs
*
ERROR at line 1:
ORA-00028: your session has been killed

Now let's check session details using SYS user.

SQL> select sid,serial#,username,status from v$session where username ='JAY';

no rows selected

The session is no longer visible in v$session.


SQL query returns different results when index is dropped

When you create/drop the index, you also affect SQL Server Query Optimizer's plan created to service your query.

Have a look at this Connect item.

Basically, SQL Server is free to optimise the expressions in the SELECT clause prior to applying the WHERE conditions.  Not very intuitive, but someone decided it should do so.

Normally, to avoid divide by zero errors, I throw a NULLIF in there which is sufficient to get past the error while not really affecting the result (since the WHERE clause actually prunes them).

e.g.

(A.VAT_TOL_PCT < (((((((A.VAT_ENTRD_AMT - A.VAT_CALC_AMT)) / ( NULLIF(A.VAT_CALC_AMT,0)))) * ( 100))) * ( 1)))


Error reading MySQL 5.5 binary log using mysqlbinlog

Since the binary log on the Master is corrupt, there is nothing else you can do. My condolences. Just skip to the next binlog:

STOP SLAVE;
CHANGE MASTER TO master_log_file='ibm-pr-slglcd-01.000076',master_log_pos=107;
START SLAVE;

Of course, 107 is the start position for MySQL 5.5 binlogs. If the Master is 5.1 (or 5.0.95), use 106. If the Master is prior to 5.1, use 98.

If Master BinLog Corruption is frequent, you may need to consider using a smaller size for the binary logs on the Master (perhaps 128M instead of the default 1G):

[mysqld]
max_binlog_size=128M

This will not stop corruption, but lowering the size it will minimize data loss from 1G to 128M.


Simplify a SQL table result with less rows

This is referred to as an aggregate query.  See the MySQL dev page for details about the various aggregates you can use.

This query will return a sum total for each sport, for each Name.

SELECT Name
    , SUM(Football) AS Football
    , SUM(Darts) AS Darts
    , SUM(Diving) AS Diving
FROM mytable
GROUP BY Name;

See this SQL Fiddle for a working example.


MySQL: Why am I getting a syntax error when using the FIELDS ESCAPED BY clause on a select statement

This seems to be a simple syntax issue. Use the below query instead which basically doesn't use FIELDS before ESCAPED by '\\' since it's already specified before TERMINATED by ','.
 SELECT "ansId","assmt","ques","answerCode","details" FROM ayanswer
 UNION
 SELECT ansId,assmt,ques,answerCode,details FROM ayanswer
 INTO OUTFILE 'c:/data/test1.csv'
 FIELDS TERMINATED by ','
 OPTIONALLY ENCLOSED BY '"'
 ESCAPED by '\\';

Supporting Resources

13.2.10.1 SELECT ... INTO Statement

TABLE employees ORDER BY lname LIMIT 1000
    INTO OUTFILE '/tmp/employee_data_1.txt'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY '\'
    LINES TERMINATED BY '\n';

13.2.7 LOAD DATA Statement


Adding a clustered index to a heap table

Would you agree this is the right thing to do?

Yes, especially if (1) the table is looked up using the Primary Key, and (2) you are noticing performance issues.

Note that if the table is primarily looked up using a set of data other than the PK (Example: Order Lines may be looked up by the OrderID and sorted by the OrderLineId), then you may consider a clustered index based on the most common lookup(s), especially if they select sequences of rows. For example, on the Order Lines example above, having a Clustered index like:

CREATE CLUSTERED INDEX Idx_OrderID_OrderLineId
ON OrderLines (OrderId, OrderLineId)

would be optimal for retrieving all of the order lines for a given order.

Also, there are cases where having a non-clustered covering index may be optimal. You will need to keep an eye on performance whatever you do with these tables.

  If so what are the best practices for adding a clustered index to this
  table, i assume i will have to remove the existing non clustered index
  on the primary key (and any foreign keys referencing it) and then add
  the clustered index and recreate the foreign keys?  Are there any scripts to assist with dropping and recreating the foreign keys as there are quite a few?

Best practices depends on the answers to the above questions. If you actually want a clustered PK (because that is how the table is commonly referenced, or because there is no clear clusterable index that could be created), then you will need to perform all of the steps you specify above. If you decide to create a clustered index that is identical to your PK (and yes, people do this... ) you won't need to do all these steps. And if you decide your optimum clustered index is some combination of fields not identical to the PK, then you will not need to do all of these steps either.

If you do replace the PK with a clustered PK, then there are scripts that can help with identifying the foreign keys. I tend to use the View Dependencies functionality inside to SSMS to identify possible locations for FKs to my table, then manually script them out and drop them myself (because I had some bad experiences with scripts that should handle FKs), so I can't recommend any specific choices right now.

  Would there be any benefit to rebuilding the heap as a short term fix while waiting for a chance to sort out the indexes?

Not really, no.


Can Cassandra be used to increase database write rate?

...be expected to decrease the write time by increasing the number of nodes to which I can write.

Yes.  While the article is dated, Netflix performed a scalability experiment back in 2011, as detailed here:

Benchmarking Cassandra Scalability on AWS - Over a million writes per second

Essentially, they found that Cassandra was indeed linearly scalable.  That is, the amount of ops/second that your cluster can sustain is directly proportionate to the number of nodes in your cluster.

  the database supports strategic analysis and report generation, and not real time information

This is the part that makes me pause.  Cassandra is typically not a good fit as an analytics back-end.  The reason for this, is that (due to its distributed nature) your table structures must be modeled according to your read queries.

Essentially, that means one table serves one query.  Additional queries requiring different columns in the WHERE clause, typically have to be served by different tables hosting duplicate data.  This can quickly become cumbersome in an OLAP environment.

So while Cassandra's masterless, distributed architecture can help scale your writes, your reads quickly lose flexibility.


Unable to create read only user-- postgresql 9.4

You're misunderstanding how REVOKE works.

You can't REVOKE a right that was never GRANTed. It doesn't create a deny ACL (a negative right), it removes a granted right if it exist. You didn't GRANT rights to user1 on the schema; instead that user inherited them from the public role everyone's a member of. So you can't revoke it.

You want to revoke the right given to public, then grant it to roles who should have read/write. quick and dirty example:

CREATE ROLE read_write_user;
GRANT read_write_user TO user2;
GRANT read_write_user TO user3;
GRANT ALL ON SCHEMA public TO read_write_user;
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO user1;


Search entire database(210 tables) for a unique Id (PK) and get the table name

In your query:

SELECT table_name
FROM information_schema.columns
WHERE (:uniqueid ) IN (SELECT 'Id'
                       FROM information_schema.columns
                       WHERE 'Id' = (:uniqueid ))

you are trying to mix data and meta-data. information_schema contains only meta-data so you won't find :uniqueid in there. You will have to extract meta-data first, and then iterate over that some how. Assuming all columns are named id (which is a bad choice, but nothing you can avoid if I get it right), something like the following pseudo-code:

c1.execute("SELECT table_name FROM information_schema.columns WHERE column_name = id")
for row in c1.fetchall():
    t = row[0]
    c2.execute("SELECT 1 FROM %s WHERE id = :uniqueid" % (t) )
    if c2.fetchone():
        print "Found %s in %s" %  (:uniqueid, t)
        sys.exit(0)

will print the first table where there is an id = :uniqueid

Another option is to generate a union all for all tables:

SELECT 't1' from t1 where id = :uniqueid
UNION ALL
SELECT 't2' from t2 where id = :uniqueid
UNION ALL
...
UNION ALL
SELECT 't210' from t210 where id = :uniqueid


How to merge column in mysql depending on match

SELECT column1,column2,column3
MAX(CASE WHEN name='foo' THEN data ELSE 0 END)as foo,
MAX(CASE WHEN name='foo2' THEN data ELSE 0 END)as foo2,
MAX(CASE WHEN name='foo3' THEN data ELSE 0 END)as foo3
FROM tableName
GROUP BY column3


We've got a ton of learning resources on that Aggressive Indexes page (I just updated it with more, coincidentally) and I can't even begin to do justice to it overall. Having said that, here goes.

Imagine that you've only got a clustered index on a table. Inserts will be pretty doggone fast. However, when you try to update a row in that table, and you don't specify the clustering key in your where clause, then you'll end up with a lot of table locks (aggression). In that case, the fix is to add indexes for the fields you frequently query on.

On the other hand, imagine that you've got dozens of indexes. Whenever you want to insert or delete rows, you're going to need locks all over the place to get your work done. The high number of indexes will slow you down because nobody can get their insert or delete done quickly - and again, you'll see aggressive index warnings. In that case, the fix is to prune down the number of indexes to a more managable number.

Generally, for transactional tables (as opposed to overnight data warehouse loads), I tell people to aim for:

5 or less nonclustered indexes per table,
5 or less fields per index

That rule of 5 and 5 can be violated - the less write activity you have, and the faster your hardware is, and the better you tune your queries, the more you may be able to get away with more indexes. On the flip side, if you have crappy hardware and crappy queries, you might need to drop those numbers lower.

The 5 and 5 rule stems from the fact that I've gotta start people somewhere, and I have 5 fingers on one hand, and 5 fingers on the other - so the rule is easy to communicate.


Which database is best for storing mouse movement data for analytic
  stuff?

As per MongoDB BOL Here

MongoDB Compass is the GUI for MongoDB. Visually explore your data. Run ad hoc queries in seconds. Interact with your data with full CRUD(Create,Read,Update,Delete) functionality. View and optimize your query performance. Available on Linux, Mac, or Windows. Compass empowers you to make smarter decisions about indexing, document validation, and more.

As with the help of MongoDB Compass you can

Visualize and explore
Insert, modify, and delete
Debug and optimize

Know your data with built-in schema visualization

MongoDB Compass analyzes your documents and displays rich structures within your collections through an intuitive GUI. It allows you to quickly visualize and explore your schema to understand the frequency, types and ranges of fields in your data set.

Get immediate insight into server status and query performance

Real-time server statistics let you view key server metrics and database operations. Drill down into database operations easily and understand your most active collections.

A better approach to CRUD makes it easier to interact with your data

Modify existing documents with greater confidence using the intuitive visual editor, or insert new documents and clone or delete existing ones in just a few clicks.

Understand performance issues with visual explain plans

Know how queries are running through an easy-to-understand GUI that helps you identify and resolve performance issues.

View utilization and manage your indexes

Understand the type and size of your indexes, their utilization and special properties. Add and remove indexes at the click of a button.

A simpler way to validate your data

Create and modify rules that validate your data using a simple point and click interface. CRUD support lets you fix data quality issues easily in individual documents.

Visualize, understand, and work with your geospatial data

Point and click to construct sophisticated queries, execute them with the push of a button and Compass will display your results both graphically and as sets of JSON documents.

As per your said database store structure , it's a format of {key : Value} pair. which is JSON format is fully supported

  {x: some-value, y: some-value, some-other-properties:{}}

For Your Further ref For Understanding the JSON Format


No. Each CU contains all the fixes in the CUs prior to it.

For the second part of the question, the only RELIABLE way supported across most versions (2005+) is to get a 4 part number and compare it to any of the comprehensive build lists available on the internet:

Select Serverproperty('ProductVersion')

You can also check @@Version but it's not reliable, as some older CUs don't list their CU number as they were meant to. You can also try Serverproperty('ProductUpdateLevel') but it's only supported on a few newer versions and service packs of SQL Server.

So I stick with build numbers. Most DBAs have one script or another to pull data from web sources into a table to do translations.

If in doubt start the CU installer and it will check whether it's needed or not. It won't let you continue upgrading if you've already upgraded everything on the server.


using $Partition function to improve query performance

Why execution plans are different

First query

select sum(PrinBal)
from fpc
    inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703

The optimizer knows:

gl.SnapshotKey = fpc.SnapshotKey; and
fpc.SnapshotKey = 201703

so it can infer:

gl.SnapshotKey = 201703

Just as if you had written:

select sum(PrinBal)
from fpc
    inner join gl on gl.SnapshotKey = fpc.SnapshotKey
where fpc.SnapshotKey = 201703
and gl.SnapshotKey = 201703

The literal value 201703 can also be used by the optimizer to determine the partition id. With both SnapshotKey predicates (one given, one inferred) this means the optimizer knows the partition id for both tables.

Going further, with a literal value (201703) for SnapshotKey now available on both tables, the join predicate:

gl.SnapshotKey = fpc.SnapshotKey

simplifies to:

201703 = 201703; or simply
true

Meaning there is no join predicate at all. The result is a logical cross join. Expressing the final execution plan using the closest available T-SQL syntax, it is as if you wrote:

SELECT
    CASE
        WHEN SUM(Q1.c) = 0 THEN NULL
        ELSE SUM(Q1.s)
    END
FROM
(
    SELECT c = COUNT_BIG(*), s = SUM(GL.PrinBal)
    FROM dbo.gl AS GL
    WHERE GL.SnapshotKey = 201703
    AND $PARTITION.PF(GL.SnapshotKey) = $PARTITION.PF(201703)
) AS Q1
CROSS JOIN
(
    SELECT Dummy = 1
    FROM dbo.fpc AS FPC
    WHERE FPC.SnapshotKey = 201703
    AND $PARTITION.PF(FPC.SnapshotKey) = $PARTITION.PF(201703)
) AS Q2;

Second query

select sum(PrinBal)
from fpc
    inner join gl on $Partition.PF(gl.SnapshotKey) = $Partition.PF(fpc.SnapshotKey)
where fpc.SnapshotKey = 201703

The optimizer can no longer infer anything about gl.SnapshotKey, so the simplifications and transformations made for the first query are no longer possible.

Indeed, unless it is true that each partition holds only a single SnapshotKey, the rewrite is not guaranteed to produce the same results.

Again, expressing the execution plan produced using the closest available T-SQL syntax:

SELECT
    CASE
        WHEN SUM(Q2.c) = 0 THEN NULL
        ELSE SUM(Q2.s)
    END
FROM
(
    SELECT
        Q1.PtnID,
        c = COUNT_BIG(*),
        s = SUM(Q1.PrinBal)
    FROM
    (
        SELECT GL.PrinBal, PtnID = $PARTITION.PF(GL.SnapshotKey)
        FROM dbo.gl AS GL
    ) AS Q1
    GROUP BY
        Q1.PtnID
) AS Q2
CROSS APPLY
(
    SELECT Dummy = 1
    FROM dbo.fpc AS FPC
    WHERE
        $PARTITION.PF(FPC.SnapshotKey) = Q2.PtnID
        AND FPC.SnapshotKey = 201703
) AS Q3;

This time there is no logical cross join. Instead, there is a correlated join (an apply) on the partition id.

  why the second query is faster.

This is hard to assess from the information given. Using mock data and tables based on the queries and plan image provided, I found the first query outperformed the second in every case.

The same query expressed using different syntax can often produce a different execution plan, simply because the optimizer started from a different point, and explored options in a different order before it found a suitable execution plan. Plan search is not exhaustive, and not every possible logical transformation is available, so the end result is likely to be different. As noted above, the two queries do not necessarily express the same requirement anyway (at least given the information available to the optimizer).

On a separate note, be aware that the initial columnstore implementation in SQL Server 2012 (and to a somewhat lesser extent, 2014) has many limitations, not least on the optimization side of things. You will likely get better, and more consistent, results by upgrading to a more recent release (ideally the very latest). This is particularly true if you're going to be using partitioning.

I certainly would not recommend you get into the habit of rewriting joins using $PARTITION, except as a very last resort, and with a very deep understanding of what you are doing.

That's about all I can say without being able to see the schema or plan detail.


Hands-off Scripted Install of MySQL 5.7 on Linux

This isn't much of a solution, but it's possible to put the data files in place on the system before installing the RPM packages.

That is, on a dev system, install MySQL and set the root password as you want it. Shutdown Mysql and make a copy of the files/directories in /var/lib/mysql (maybe put them in a tar file or something).

On a new system, before installing the MySQL RPM packages, take a look and see if /var/lib/mysql exists - if not, create it and untar your files into it. When the RPM installs it won't overwrite those files, and so will start up with your previously set root password.

This seems like quite a cheesy option, and probably won't work when versions of the RPMs change (even dot release changes might break because they'll need an upgrade process on the archived data files). However, it's a possible way forward.

Another (less than ideal) way forward is something like this:

Check to see if /var/lib/mysqld exists. Remember this.
Install Mysql RPMs (the post-installs automatically create /var/lib/mysql if it doesn't exist - you can't really stop this from happening)
If (1) was "doesn't exist":

Ensure Mysql is stopped (the RPMs don't start it, but just be sure)
Delete /var/lib/mysql/*
run /usr/sbin/mysqld --initialize-insecure --datadir="/var/lib/mysql" --user=mysql (which is what the init.d script does, if /var/lib/mysql is empty, except we're using 'insecure' which means no root password is generated)
Start mysql
Use mysql client to log on with empty password and set the root password to whatever you want.
I'm really not a fan of using Ansible or whatever to delete database data files. Even though this method has suitable checks to make sure it doesn't delete a live database, it still seems like you're creating a dangerous situation here. However, it's another possible approach.

I guess the last approach is to petition Oracle to allow setting a root password during installation (maybe via an environment variable that the RPM post-install picks up, or a magic file somewhere that contains the password that the post-install looks for. That would make life so much easier ;-)


Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.

--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY

--Restore the most recent diff backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to diff backup'
WITH RECOVERY, STATS=10

More info on RESTORE: http://msdn.microsoft.com/en-us/library/ms186858.aspx


How can I resolve this ORA-01652 error when I already added a new file to the TEMP tablespace?

Each tablespace has one or more datafiles that it uses to store data.

The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max gb per datafile.

run the following for actual allowed size:

select value from v$parameter where name = 'db_block_size';

Compare the result you get with the first column below, and that will indicate what your max datafile size is.

I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

--------   --------------------   --------------

   2,048                  8,192          524,264

   4,096                 16,384        1,048,528

   8,192                 32,768        2,097,056

  16,384                 65,536        4,194,112

  32,768                131,072        8,388,224

You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you've currrently set the max file size to (which cannot exceed the aforementioned 32gb):

select bytes/1024/1024 as mb_size,
       maxbytes/1024/1024 as maxsize_set,
       x.*
from   dba_data_files x

MAXSIZE_SET is the maximum size you've set the datafile to. Also relevant is whether you've set the AUTOEXTEND option to ON (its name does what it implies).

If your datafile has a low max size or autoextend is not on you could simply run:

alter database datafile 'path_to_your_file\that_file.DBF' autoextend on maxsize unlimited;

However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:

alter tablespace system add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;

Also it is generally a good/common practice for you to have a tablespace that is dedicated to indexes (it doesn't seem that you do, because you didn't specify the tablespace in your create index statement, and it is using the default system tablespace), so after creating such a tablespace, you would run something like this (in your case):

CREATE INDEX FTS_INDEX ON FILTERED_TEKLI_IIS_TABLOSU (ilAdi,ilceAdi,caddeAdi)
tablespace name_of_ts_for_indexes

(After creating that tablespace and a datafile for it), via:

create tablespace name_of_ts_for_indexes datafile
'c:\app\xyz\oradata\orcl\name_of_ts_for_indexes01.dbf' autoextend on maxsize unlimited nologging;

That way your tables would be on one table space and the indexes on another.

Thanks to Brian D. for additional Information.


How to set Auto_Increment value based on a variable?

You can do it with a prepared statement.

set @sql = concat('alter table ai auto_increment = ', @newai);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;


innodb_force_recovery when InnoDB corruption

According to MySQL's documentation, if you use innodb_force_recovery=1, the server will start even if it detects a corrupt page.

http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

Still, there are a couple of things you may want to check.

Are there any relevant errors in mysql's error log?  cat /var/log/mysqld.log

Is the file system OK, can you create files with touch in the same partition ?  ex. touch /var/somefile.txt

If you have a full backup of your databases, as you mention in your question, you can delete the ibdata and ib_logfile(s) and recreate them, then restore your databases.  This will resolve any corruption issues you may have in the innodb files.

The steps are:

Stop mysql
delete the ibdata and ib_logfile files.

ex : rm -f /var/lib/mysql/ibdata1

ex : rm -f /var/lib/mysql/ib_logfile*
Start MySQL (which recreates the innodb files)
Restore the dump  mysql -u root -p < mydump.sql

As long as you only delete the innodb files(ibdata and ib_logfiles), your users and their access rights to the databases will still be in tact.


How to create an Event Notification that runs a Job/procedure when mirroring state changes

Step 1: Create a service to receive the notifications and a queue for it:
use msdb;
go

create queue dbm_notifications_queue;
create service dbm_notification_service
    on queue dbm_notifications_queue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
go

create event notification dbm_notifications
    on server
    for database_mirroring_state_change
    to service N'dbm_notification_service', N'current database';
go

Note that I'm using msdb, this is not an accident. Because the server level event notifications are sent from msdb it is much better if you create the opposite conversation endpoint (the target) also in msdb, which implies that the destination service and the queue must also be deployed in msdb.
Step 2: create the event notification processing procedure:
use msdb;
go

create table dbm_notifications_errors (
    incident_time datetime not null,
    session_id int not null,
    has_rolled_back bit not null,
    [error_number] int not null,
    [error_message] nvarchar(4000) not null,
    [message_body] varbinary(max));
create clustered index cdx_dbm_notifications_errors
    on dbm_notifications_errors  (incident_time);
go

create table mirroring_alerts (
    alert_time datetime not null,
    start_time datetime not null,
    processing_time datetime not null,
    database_id smallint not null,
    database_name sysname not null,
    [state] tinyint not null,
    [text_data] nvarchar(max),
    event_data xml not null);
create clustered index cdx_mirroring_alerts
    on mirroring_alerts (alert_time);
go

create procedure dbm_notifications_procedure
as
begin
    declare @dh uniqueidentifier, @mt sysname, @raw_body varbinary(max), @xml_body xml;

    begin transaction;
    begin try;
        receive top(1)
            @dh = conversation_handle,
            @mt = message_type_name,
            @raw_body = message_body
        from dbm_notifications_queue;
        if N'http://schemas.microsoft.com/SQL/Notifications/EventNotification' = @mt
        begin
            set @xml_body = cast(@raw_body as xml);
             -- shred the XML and process it accordingly
             -- IMPORTANT! IMPORTANT!
             -- DO NOT LOOK AT sys.database_mirroring
             -- The view represents the **CURRENT** state
             -- This message reffers to an **EVENT** that had occured
             -- the current state may or may no be relevant for this **PAST** event
            declare @alert_time datetime
                , @start_time datetime
                , @processing_time datetime = getutcdate()
                , @database_id smallint
                , @database_name sysname
                , @state tinyint
                , @text_data nvarchar(max);

            set @alert_time = @xml_body.value (N'(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME');
            set @start_time = @xml_body.value (N'(//EVENT_INSTANCE/StartTime)[1]', 'DATETIME');
            set @database_id = @xml_body.value (N'(//EVENT_INSTANCE/DatabaseID)[1]', 'SMALLINT');
            set @database_name = @xml_body.value (N'(//EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME');
            set @state = @xml_body.value (N'(//EVENT_INSTANCE/State)[1]', 'TINYINT');
            set @text_data = @xml_body.value (N'(//EVENT_INSTANCE/TextData)[1]', 'NVARCHAR(MAX)');

            insert into mirroring_alerts (
                alert_time,
                start_time,
                processing_time,
                database_id,
                database_name,
                [state],
                text_data,
                event_data)
            values (
                @alert_time,
                @start_time,
                @processing_time,
                @database_id,
                @database_name,
                @state,
                @text_data,
                @xml_body);
        end
        else if N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' = @mt
        begin
        set @xml_body = cast(@raw_body as xml);
        DECLARE @error INT
                , @description NVARCHAR(4000);
        WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)
        SELECT @error = CAST(@xml_body AS XML).value('(//ssb:Error/ssb:Code)[1]', 'INT'),
            @description = CAST(@xml_body AS XML).value('(//ssb:Error/ssb:Description)[1]', 'NVARCHAR(4000)');

        insert into dbm_notifications_errors(
            incident_time,
            session_id,
            has_rolled_back,
            [error_number],
            [error_message],
            [message_body])
        values (
            getutcdate(),
            @@spid,
            0,
            @error,
            @description,
            @raw_body);
            end conversation @dh;
        end
        else if N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' = @mt
        begin
            end conversation @dh;
        end
        commit;
    end try
    begin catch
        declare @xact_state int = xact_state(),
            @error_number int = error_number(),
            @error_message nvarchar(4000) = error_message(),
            @has_rolled_back bit = 0;
        if @xact_state = -1
        begin
            -- Doomed transaction, it must rollback
            rollback;
            set @has_rolled_back = 1;
        end
        else if @xact_state = 0
        begin
            -- transaction was already rolled back (deadlock?)
            set @has_rolled_back = 1;
        end
        insert into dbm_notifications_errors(
            incident_time,
            session_id,
            has_rolled_back,
            [error_number],
            [error_message],
            [message_body])
        values (
            getutcdate(),
            @@spid,
            @has_rolled_back,
            @error_number,
            @error_message,
            @raw_body);
        if (@has_rolled_back = 0)
        begin
            commit;
        end
    end catch
end
go

Writing service broker procedure is not your run-of-the-mill code. One must follow certain standards and is very easy to stray off into quicksand territory. This code shows some good practices:

wrap the message dequeue and the processing in a transaction. No brainer, obvious.
always check the message type received. A good service broker procedure must handle Error and EndDialog messages appropriately by ending the dialog from it's side. Not doing so results in handle leaks (sys.conversation_endpoints grows)
always check if a message was dequeued by RECEIVE. Some samples check@@rowcount after RECEIVE, which is perfectly OK. This sample code relies on the message name check (no message implies NULL message type name) and handles that case implicitly.
create a processing errors table. the background nature of SSB activated procedures makes it really difficult to troubleshoot errors if the messages simply vanish w/o a trace.

Besides, this code also does some good-practice code with regard to the task at hand (monitoring DBM):

differentiate between post_time (when was the notification sent?), start_time (when did the action that triggered the notificaton start?) and processing_time (when was the notification processed?). post_time and start_time will likely be identical or very close, but processing_time can be seconds, hours, days apart from post_time. the interesting one for audit is usually post_time.
since the post_time and the processing_time are different, it should be obvious that a DBM monitoring task in an even notification activated procedure has no business looking at sys.database_mirroring view.  That view will show the current state at the moment of processing, which may or may not be related to the event. If the processing occurs a long time after the event is posted (think maintenance downtime) than the issue is obvious, but it can handle also in 'healthy' processing if the DBM changes state very fast and post two (or more) events in a row (which happens frequently): in this situation the processing, as in the code you posted, audit the event as they occur, but will record the current, final, state. Reading such an audit could be very confusing later.
always audit the original XML event. This way you can later query this XML for any information that was not 'shredded' into columns in the audit table.

Step 3: attach the procedure to the queue:
alter queue dbm_notifications_queue
with activation (
    status=on,
    procedure_name = [dbm_notifications_procedure],
    max_queue_readers = 1,
    execute as  owner);

Repeating the above steps on all instances involved in mirroring ensures you get notified no matter which instance is the principal.


Something like this?  This will help you extract the LONG into text without a procedure, but I'm not sure about what SYSDATE logic you want to limit your result set to.  You would have to play with that final WHERE clause to get the right values.

SELECT * from (
  SELECT PARTITION_NAME,
    extractvalue
      ( dbms_xmlgen.getxmltype
        ( 'select high_value
           from DBA_TAB_PARTITIONS where table_name = ''' || t.table_name || ''' and PARTITION_NAME = ''' || t.PARTITION_NAME || ''''),
        '//text()' ) as high_value
  FROM DBA_TAB_PARTITIONS t
  WHERE TABLE_NAME = 'MY_TABLE')
WHERE to_char(add_months(sysdate,-1),'SYYYYMMDD') < high_value;


For every inserted row, generate series of rows in another table with AFTER INSERT trigger in PostgreSQL

This is likely best handled in application code since it's a procedural goal you're trying to accomplish, not really a relational one, since the approach likely requires iteration.
That being said, you possibly can accomplish it in a relational way via Dynamic SQL if you had a numbers table you could select the TOP N number of rows from prepended with your id. The Dynamic SQL comes into play as a way to build the query with N = your qty field.
E.g. the query that your Dynamic SQL could generate would look like this (with id and qty filled in):
SELECT CONCAT('A1', '_', Number) AS sub_id
FROM NumbersTable
ORDER BY Number
LIMIT 25


Creating logins mapping for a linked server without creating logins for AD users

Is there any way to avoid creating logins but get the mapping for AD
  users?

If you run this code:

sp_helptext 'sp_addlinkedsrvlogin'

You'll be able to find a piece of code responsible for win login creation:

-- IF SPECIFIED CHECK LOCAL USER NAME (NO NT GROUP!)
select @localid = 0
if (@locallogin IS NOT NULL)
begin
    -- share-lock the local login
    EXEC %%LocalLogin ( Name = @locallogin ) . Lock ( Exclusive = 0 )
    IF @@ERROR = 0
        select @localid = principal_id from sys.server_principals
            where name = @locallogin and type in ('S', 'U')
    else
    begin
        -- ADD ROW FOR NT USER LOGIN IF NEEDED --
        if (get_sid('\U'+@locallogin) IS NOT NULL)
        begin
            EXEC @ret = sys.sp_MSaddlogin_implicit_ntlogin @locallogin
            if (@ret = 0)
                select @localid = principal_id from sys.server_principals
                    where name = @locallogin and type = 'U'
        end
    end
    if (@localid = 0)
    begin
        ROLLBACK TRAN
        raiserror(15007,-1,-1,@locallogin)
        return (1)
    end
end

Here sys.sp_MSaddlogin_implicit_ntlogin @locallogin creates the corresponding login and its principal_id will next be retrieved by this code:

select @localid = principal_id from sys.server_principals
                        where name = @locallogin and type = 'U'

(here U indicates WINDOWS_LOGIN)

And then it will be passed to create a mapping:

EXEC %%LinkedServer(Name=@rmtsrvname).NewLinkedLogin(
            LocalID=@localid, UseSelf=@useselfbit, RemoteName=@rmtuser, Password=@pwd)

So the answer to your question is NO unless you map all your logins to the same remote_user by passing NULL as @locallogin.


Best practice design when expecting large data in a table

Yes, the same concept could be used.  What you have done is re-implement table partition, but in user space.  Most industrial-strength RDBMSs will have this built in.  The provided functionality often includes additional abilities, such as efficiently adding and removing partitions at run time without applicaiton changes.  By choosing to roll your own you miss out on these additional features.  Additionally you complicate some things, such as surrogate ID uniqueness checking, aggregate queries across your whole user community and DRI referencing the "user" table.

Be aware that your sub-tables are very unlikely to be well balanced.  There aren't many Mr. Aardvark or Ms. Zymology in the world but a lot of Smiths and Jones.

The reason key lookups are faster on smaller tables is because the indexes have fewer levels, assuming you have B-Tree indexes.  Therefore the DBMS has to read fewer pages to get from the index's root node to its leaf & data pages.  The index on your 676 sub-tables is likely to be only one or two levels deep and so incur only one or two page reads to read a key's row.  In contrast a full B-Tree built on 1M rows may be three or four levels deep and require that many page reads per lookup.  Built-in partitioning can give you similar benefits if you define your index as partitioned, too.

This is a good reason to keep your index keys compact, if you have a choice.  For example, if your user name is 30 characters long this will take 30 bytes to store and you will get a certain number on a page.  If instead you calculate an integer hash (4 bytes) of the user name and index that, there will be 30/4 = 7-ish times more rows per index page and the index will likely have fewer levels. (Of course you will have to account for potential hash collisions.)  Similarly limiting the amount of free space you have in the index to allow for inserts will help increase density.


PostGIS "could not open extension control file" when creating extension

Looks like you have more than one PostgreSQL install. You're connected to a PostgreSQL install from EnterpriseDB, in /opt. This install cannot see, or use, extensions installed into the Ubuntu-packaged PostgreSQL install.

The instructions you followed will have installed PostgreSQL 9.3 from http://apt.postgresql.org/, i.e. PGDG. You should connect to that PostgreSQL install, not the EnterpriseDB one, if you want to use the PostGIS you installed.

You need to either use the system-packaged (PGDG) PostgreSQL install on your system, or install PostGIS separately into the EnterpriseDB-packaged PostgreSQL install using their StackBuilder product.


How to write marker messages into the MySQL general_log?

Since you have mysql.general_log already converted to MyISAM and indexed, I would recommend making snapshots of that table based on a timestamp range. What I mean by snapshot is a temp table that contains just the events you wish to mark.

Suppose you want to log entries from the last 10 minutes. This Dynamic SQL should do it for you :

SET sql_log_bin = 0; ## Do this so it does not replicate to slaves
CREATE DATABASE IF NOT EXISTS myloggingdatabase;
USE myloggingdatabase
SET @now = NOW();
SET @ten_minutes_ago = @now - INTERVAL 10 MINUTE;
SET @snapshot = CONCAT('general_log_',DATE_FORMAT(@now(),'%Y%m%d_%H%M%00'));
SET @sql = CONCAT('DROP TABLE IF EXISTS ',@snapshot);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('CREATE TABLE ',@snapshot,' LIKE mysql.general_log');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('INSERT INTO ',@snapshot,' SELECT * FROM mysql.general_log');
SET @sql = CONCAT(@sql,' WHERE event_time>=',QUOTE(@ten_minutes_ago));
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

If you want all the events from yesterday, do this

SET sql_log_bin = 0; ## Do this so it does not replicate to slaves
CREATE DATABASE IF NOT EXISTS myloggingdatabase;
USE myloggingdatabase
SET @now = NOW();
SET @midnight_today = DATE(@now) + INTERVAL 0 SECOND;
SET @midnight_yesterday = @midnight_today - INTERVAL 1 DAY;
SET @snapshot = CONCAT('general_log_',DATE_FORMAT(@midnight_today(),'%Y%m%d_%H%M%00'));
SET @sql = CONCAT('DROP TABLE IF EXISTS ',@snapshot);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('CREATE TABLE ',@snapshot,' LIKE mysql.general_log');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql = CONCAT('INSERT INTO ',@snapshot,' SELECT * FROM mysql.general_log');
SET @sql = CONCAT(@sql,' WHERE event_time>=',QUOTE(@midnight_yesterday));
SET @sql = CONCAT(@sql,' AND event_time<',QUOTE(@midnight_today));
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

You will to have discard those temp tables now and then by simply running

DROP DATABASE IF EXISTS myloggingdatabase;

GIVE IT A TRY !!!


Do long-running and locking queries on subscriber databases cause performance issues on the publisher dbs?

Long running and locking queries on subscriber databases wouldn't typically cause performance issues on the publisher. The distributer can be run separately if you are worried about them affecting the performance of your SQL server.

The Log Reader Agent will take the changes from the Publisher and store them in the distribution database. The Distribution Agent will attempt to apply these to the Subscriber. If the subscriber is busy or tables are locked it will wait for them to be available or timeout and keep retrying on the schedule defined during setup.

You can view how many unreplicated commands exist and how far behind your subscriber is from the Publisher by using the Replication Monitor or querying the sp_browsereplcmds procedure.

For more info on the agents, and the settings you can change, take a look at this Microsoft docs page.


What are the circumstances where autovacuum can be disabled?

(to prevent wraparound)

is the key bit. PostgreSQL must do freeze vacuums where transaction IDs are set to XID_FROZEN to prevent transaction ID wrap-around, and as a result massive data corruption.

If you disable these or cancel them all the time your database will eventually go into emergency shutdown.

See the user manual on VACUUM.

You can do a manual VACUUM FREEZE during a quiet period to get this all over and done with in one go if you want.


Occasional error: Could not obtain information about Windows NT group/user 'domain\user', error code 0x54b

I would strongly advise against using sysadmin privileges to simply send an email... have you ever hear of the principle of least privilege!? This wouldn't solve your problem, just provide a work around! Similarly, you would be enabling the login to drop databases, change passwords, and create all sorts of chaos!

Have you checked the SQL error logs, or the event logs. It may also be a good idea to check the logs on your DC, to see if there is any information there.

Are you having any problems with the account that the agent job is being execute as? Presumably this has the appropriate permissions for what you are asking? when you are executing the script you are executing it under your context, not the context of the agent. This is likely why it works for you all the time.

Without any additional information, preferably from your logs, it's difficult to help any better than this


Streaming replication between postgresql 9.2 and 9.3

In general, log shipping between servers running different major
  PostgreSQL release levels is not possible. It is the policy of the
  PostgreSQL Global Development Group not to make changes to disk
  formats during minor release upgrades, so it is likely that running
  different minor release levels on primary and standby servers will
  work successfully. However, no formal support for that is offered and
  you are advised to keep primary and standby servers at the same
  release level as much as possible.

It is quite plain in the documentation - you may try but don't be surprised if it does not work.


Replicaset with MongoDB: Two datacenters, one primary and the other secondary

If I have six nodes (3 node in each DC), would I need an arbiter?

Yes - as Mongo docs mentioned an odd number is ideal.

If you have 6 Nodes and 1 goes down, the remaining 5 will elect a new primary. When 2 go down, 4 will elect a primary. When 3 do go down, you only have 3 left which are no longer a majority and all 3 will become read-only until a 4th node comes back online.

If you have 6 + an arbiter, then the scenario plays out slightly more beneficially. 1 node goes down, 5+1 elect a new primary. 2 go down, 4+1 elect a new primary. 3 go down, 3+1 still has a majority (3+1 / 7) so they can elect a new primary. Finally, when 4 nodes are down you are left with 2 nodes up and 1 arbiter, which is less than the majority (2+1 / 7) so you'll enter into read-only until a new node comes back online.

An odd number gives you one more level of outage before you lose the majority of your voting nodes and an arbiter is a cheap way to gain that benefit.

  If I have five nodes (3 in DC1 and 2 in DC2), if DC1 fails, wouldn't I
  need an arbiter in DC2?

In this config, adding an arbiter would give you an even number, so that's not ideal (see above). However, if DC1 goes down you're also losing your majority automatically and thus down to read-only by default. Although it might seem strange, this is the recommended setup from Mongo.

But why?

You could have 3 in DC1 and 3 in DC2 with an arbiter in DC2, which would allow you to maintain a voting majority in DC2 if DC1 were to completely fail. This works if you want automatic failover into DC2. But if DC2 goes down and a node in DC1 goes down, you also lose DC1 majority and end up with read-only nodes - probably not ideal.

That's why Mongo recommends that you set the priority for nodes in DC2 to 0. In this case you should have 3 nodes + 1 arbiter in DC1 and 3 nodes in DC2. You will need to manually fail over to DC2 if DC1 completely fails, but it gives you the best case scenario as far as keeping DC1 active and primary.


File database vs application server database for concurrent performance & bulk data

First, is it true that the ideal implementation for bulk storage is to use a flat file database?

If you're talking about SQLite, it isn't a "flat file database". Sure, it's a single file database, but it's highly structured.

  Second, is it true that for concurrent reads & writes, PostgreSQL vastly outperforms a flat file database?

PostgreSQL will typically vastly outperform SQLite for workloads that have simultaneous reads and writes because readers can proceed while the table is being written to. For multiple concurrent readers and writers with non-trivial transactions there's no contest. To make this possible PostgreSQL must write each change at least twice and then do cleanup "vacuum" work later.

Often you can serialize many short read and write transactions, so they appear to execute very quickly even though they each wait a while on a lock. So for trivial reads and writes SQLite may still do very well even with several concurrent clients, so long as the lock durations are short. For long transactions it's totally hopeless.

  In other words, why not use a database server, specifically PostgreSQL?

Probably because it's trivial to set up and run SQLite, so it's way easier to embed in an application.

PostgreSQL doesn't support in-process embedding. While it's easy enough to bundle the database server it's still hassle many devs don't want. Upgrading PostgreSQL is also a pain, as the file format changes with every major version and requires an upgrade step - wheras SQLite's file format is incredibly upward- and downward-compatible.


The provided statistics stream is corrupt, when running UPDATE Statistics

It's a bug.  You need CU11 for Server 2008 R2 SP2
http://support.microsoft.com/kb/2829845

VSTS bug #: 1790001
FIX: "The provided statistics stream is corrupt" when you run the
"UPDATE STATISTICS" statement against a table in SQL Server 2008 R2 or
SQL Server 2012


How to port LTRIM from Oracle to SQL Server?

Yeehaw.

DECLARE @MyTable TABLE ( MyColumn VARCHAR(100) );

INSERT @MyTable ( MyColumn )
    VALUES ( '-----, ,,, This is ,- a test,---,' );

SELECT *,
        SUBSTRING(mt.MyColumn, ca.p, LEN(mt.MyColumn))
FROM @MyTable AS mt
CROSS APPLY ( SELECT *
              FROM (VALUES (PATINDEX ('%[^ ,-]%', MyColumn))
            ) AS x (p) ) AS ca;


How to insert nested JSON arrays into a table?

Use the function json_array_elements() instead, available in Postgres 9.5.
Quoting the manual, it ...

  Expands a JSON array to a set of JSON values.

And values can in turn be JSON arrays (or any other JSON values).

INSERT INTO testj (j)
SELECT * FROM json_array_elements(j);


In Postgres 9.5, the most elegant, simple, safe and fast solution is the new UPSERT implementation:

INSERT INTO device (device_id) VALUES (1234567)
ON CONFLICT (device_id) DO NOTHING;

-- now we have either inserted the device or it was there already
INSERT INTO data (device_id, data)
VALUES (1234567, 'some data);

Obviously, there must be a some kind of unique index or exclusion constraint on (device_id).
Details in the manual.

For older versions or for auto-generated serial IDs:

Is SELECT or INSERT in a function prone to race conditions?


How to create a view with added rows for missing dates?

I wouldn't organize your "helper table" that way.  I would keep only {Region, Salesman, StartDate}.  To create a view of intervals, join that table to itself to create data ranges where a.StartDate < b.StartDate.  Use an outer join, so that NULL represents an EndDate of "now", which you can coalesce with the current date or other, as appropriate.  To represent departures (not reassignments) use an artificial region named CiaoBaby or some such.

Now your maintenance chore is much easier; you need only capture staffing changes.  The table is oodles smaller (over time, 100 or 1000 oodles).  And you automatically have the built-in assumption that, absent contrary information, a salesman remains attached today to the same one as last month.


How do I make a citation table where work can't refer to itself?

you can use check constraint, add this to table definition:

CONSTRAINT check_work_ids_are_not_equal CHECK work_1 <> work_2

yes, you can create PK on (work_1, work_2)
it has a sense to add index on Work_citation(work_2). This way you will not need to scan the whole Work_Citation table when delete rows from Work table to check that the are no referencing rows


What are the alternatives for prefixed indexes in Oracle?

You can create an index on an expression (which you cannot do in MySQL):

CREATE INDEX table_idx ON emp(substr(last_name,1,8));

But that won't help anything unless you also use that expression in the where clause, e.g.

where substr(last_name,1,8) = 'foobar'

What exactly is the usage of an index in MySQL that doesn't index the whole value?


MySQL boolean field search vs indexed varchar performance

Short answer for bool vs varchar:  Probably not much diff, but it may depend.
Short answer for bool that is true 90% of the time:  The index is useless.
Long answer...
What percentage of the rows are "is_active"?  If it is more than about 20%, the index won't be used!.
Use reasonable limits on VARCHAR.  Even without that, 'inactive' is only 8 characters, not 255.  So, WHERE status = 'inactive' is not much slower than WHERE is_active.
Is that the entire WHERE clause?  I ask because Optimization requires looking at the entire WHERE clause.  For that matter, then entire SELECT can be important.
Often the query involves
WHERE is_active
  AND other-stuff

In which case, this may be very beneficial, even with 90% being active:
INDEX(is_active, other-column)

But, again, I need to see the details before giving a definitive answer.
And, no, the poor cardinality of is_active is not relevant in a composite index.


Installation SQL Server 2012 - “Ready to Install” page missing

Ok, I found the solution...

When you have SQL Server 2012 EXPRESS edition, and you want to get ConfigurationFile.ini from your installation process, you have to:

1) Run downloaded exe, which will unpack your setup.exe to C:/231231231231...sth

2) Cancel installation. Go to that dir via cmd.

3) In cmd run:
setup.exe /ACTION=INSTALL /UIMODE=Normal

Express edition is run in AutoAdvance which skips "Ready to install" and ConfigurationFile in general.. not sure why.


You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply`
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$


First questions -

Yes they are stored on page normally (for non-(MAX) values)
They are not padded, but there is a 2 byte per row overhead to store the length of the field

1 - IT DEPENDS on if the page is full or not.  Very likely it will fit on the current page.  The header record for the row needs to updated as well to update the length of the field.

2 - Data will only be rearranged at the page level for an insert or update, so nothing else gets shifted.  If it fits on your current page it will, if not there will be a page split.

3 - Never as far as I know for varchar - if you want this behavior you can make it a CHAR instead.

4 - Only if the value exceeds the space on the page.

As to the design itself...

You will definitely get better performance by using the id instead of a varchar.

An int or smallint is orders of magnitude smaller than your strings.
Index lookups are a lot more efficient on integers than on strings
Data stored on the page will be consistent and much smaller
SQL likes keys!  A lot of optimizations can  take place when you use PK/FK relationships because the engine can infer some metadata about duplicates/distinct values, etc.


Are there limitations on size or record count in MySQL?

Here are a couple resources from the docs on MySQL limitations.

  Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables. [src]

  There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table.  [src]

  Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. [src]

Bottom line is, MySQL can easily handle your scenario.


How do I troubleshoot loss of connection with SQL server

Definitely have a look at the NT and SQL logs... and if you were running on SQL Server 2008 or later, check the ring buffers DMV for a few extra clues. Do you have any monitoring tools that you can increase the sample rate with, to add evidence the problem is maybe with the Citrix Xenapp delivery?

Ring Buffers:
http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

Kendra Little did a good introduction video on troubleshooting query timeouts, not quite the same as connection timeouts.
http://www.brentozar.com/archive/2011/07/mystery-of-query-timeouts-video/


SQL Server 2012 + SQL Server 2008R2 on the same VM

We have mixed instances of SQL 2008R2 and 2012 on the same server running either windows server 2008R2 or 2012.

there are no problems at all and it is supported by Microsoft.

note that we are a company that make software for investment banks and our software is used in biggest and critical deals like Facebook, twitter, etc globally and we do hosting for different investment banks that use our software.


PHPMyAdmin for multiple servers w/ single pma database

I have a little surprise you : Did you know that if you have the SUPER privilege, you can write to a read_only database ?
According to the MySQL Documentation on read_only:

This variable is off by default. When it is enabled, the server
permits no updates except from users that have the SUPER privilege or
(on a slave server) from updates performed by slave threads. In
replication setups, it can be useful to enable read_only on slave
servers to ensure that slaves accept updates only from the master
server and not from clients

All you need to do is connect to the slave as a DB user with the SUPER privilege and write to the pma database to your heart's content.
UPDATE 2011-10-24 23:33 EDT
On the slave servers, you must add this /etc/my.cnf and restart mysql
[mysqld]
replicate-ignore-db=pma

That way, each pma database can be specific to the local DB instance. As for writing, you must use SUPER privilege to bypass the read_only restriction. Only other alternative is to drop the read_only option from /etc/my.cnf and restat mysql.


Differentiate SQL Server Express from SQL Server installed in a server

The MSSQLSERVER name in the services does not mean anything other than SQL Server (any edition) was installed as the default (unnamed) instance. It does NOT give you any information as to what version or edition it is! This can be SQL Server Express!

You need to connect to that database instance and run

SELECT @@VERSION

to find out what exact version/edition it is.

Having the two SQL Server service entries just means you have two instances - which edition each instance is cannot be determined from their names alone. OK - SQLEXPRESS is the default name used for SQL Server Express - but you could install a Standard or Enterprise edition with that instance name - just for fun.


AlwaysOn High Availability Take database offline from primary

Now I have requirement to make one database offline from the primary replica and make it online after some time.

This is not possible since database is added to AOAG. You cannot take the db offline unless you remove it from AG.

What you can do is ...

Disable the login on primary.
Enable read_only secondary.
Have your application configure to use read intent and read from secondary
After your testing is done, enable the login on primary.


What is FT BATCH CMPLETE in the cmd column for sysprocesses?

When I queried sysprocesses to see what the spid was doing, it had FT BATCH CMPLETE in the cmd column. Does anyone know what this command is?

It is related to Full text catalog.

You can find out more info using sys.dm_fts_outstanding_batches

e.g. from Pro Full-Text Search 2008 book, Below will give you the Number of Full-Text Index Population Batches in progress

SELECT
 OBJECT_NAME(ob.table_id) AS table_name,
 fc.name AS catalog_name,
 COUNT(*) AS outstanding_batches
FROM sys.dm_fts_outstanding_batches ob
INNER JOIN sys.fulltext_catalogs fc
 ON ob.catalog_id = fc.fulltext_catalog_id
INNER JOIN sys.databases d
 ON ob.database_id = d.database_id
GROUP BY
 ob.table_id,
 fc.name;

Also, you can use sys.dm_fts_index_population to get details about full-text index populations currently in progress.

  I can't figure out what the process is doing, even tracing it comes up with nothing.

You can use Adam Machanic's - sp_WhoIsActive to get a more detailed view.

As a side note, sysprocesses is deprecated and is not accurate as per KB - Information on CPU and Physical_io Columns of Sysprocesses

  sysprocesses.cpu is accurate but only updated at certain points. For lengthy operations that do not return any data to the client, it may not be updated for long intervals of time. Some of these operations may include DBCC CHECKDB, CREATE INDEX, database recovery, long-running joins, and so on. From a practical standpoint, it may often appear to be updated only at the end of each command.

If high CPU utilization is a frequent symptom on your server due to FullText, then highly suggest you to look into Improve the Performance of Full-Text Indexes

A good practice would be to baseline your server instance.


Getting the first 3 children(items) for each owner in a list of primary keys

You can use window functions to solve this kind of issues, something like:

select item_name
from (
    select name as item_name
         , row_number() over (partition by owner_id
                              order by id) as rn
    from items_item
    where owner_id in (64, 65, 130, 99, 43, 140, 108, 143, 115, 154)
) as t
where rn <= 3

I updated to order by id in window clause, according to comment


This login is used for Extended Events. More information here: Extended Events logins

I disabled it once, and I was no longer able to collect the data I needed. If I were you I'd keep the login as it is, unless of course you already know that you wont need tracing/Extended Events.

When it comes to telemetry you may like to read the instructions how it can be disabled: Disable telemetry Apparently many users are not happy about telemetry. More information here


Try using heterogenous Connectivity to link the Oracle database to the mySQL database.
It would be easier to directly query for what you want from Oracle, rather than trying to replicate the data to Oracle. I tried doing this on Redhat 5.6, but I had issues with the ODBC driver that was written for Redhat 5.6. I later tries it with Redhat 6.3 and it worked much better. The issue dealt with how the ODBC driver read unicode characters. It is pretty easy to setup, you can install HAS as a different Oracle Home, or use the same Oracle home. Linux has a built in ODBC test program that will help you to get it working.

http://docs.oracle.com/cd/B28359_01/server.111/b28277/toc.htm


Get multiple columns from a function depending on outer group by

You want to do a LATERAL join, by putting the the function in the FROM list.  If you just put the function in the from list after table "a", you don't need the LATERAL keyword, as joins to functions are automatically lateral.  If you wrap it in a dummy "select * from", then you do need the keyword:

WITH A AS (SELECT DISTINCT trip_id FROM stop_times)
SELECT route.*
FROM A CROSS JOIN LATERAL (SELECT * FROM getroutepertrip(trip_id)) as route
GROUP BY trip_id
ORDER by trip_id

or

WITH A AS (SELECT DISTINCT trip_id FROM stop_times)
SELECT route.*
FROM A, getroutepertrip(trip_id) as route
GROUP BY trip_id
ORDER by trip_id


Minimize impacts of a known Denial-of-Service in SQL Server

If you have SQL Enterprise edition, you could use Resource governor to limit resources consumed by the problemmatic application. You will need to create a classifier function that will "detect" the application e.g. based on user login or application name and assign application sessions to separate Workload group, while keeping all other users and apps in Default group. Then define resource pool for the application and allocate resources limits for CPU, Memory and/or IOPS. Map the Workload group to the resource pool. If done correctly, the application will be restricted to the defined CPU, memory and IO limits.

Suggested reading: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor

Edit: Based Diego's question I came up with possible workarounds without SQL enterprise edition. It is slightly rough and involves migration of the troublesome databases, but it's the only way I can think of:

Option 1) Install a new SQL server instance on the current server, migrate the database(s) there, edit connection string in the application etc. Limit assigned CPU cores and RAM on instance level (Processors affinity and Max Server Memory in server options). Secondary SQL instances on already licensed server do not require more licenses.

Option 2) In a virtualized environment you might create a whole new virtual machine, limit its resources on hypervisor level and then install a new SQL instance into that VM. Note that depending on your current licensing setup you might need additional Windows and/or SQL server licenses for the new VM.

Personally I'd go with 2) because it is easier to clean up when no longer needed. (And in my case I know that I have licensed entire virtualization hosts so I can create new VMs with SQL freely.)


Linked Server Configuration using remote user logins

I think you need to check the Impersonate option or not use sa for testing—use another SQL Login instead for testing this functionality. I'm not 100% certain it's required but you may also need to consider ensuring the remote logins have a matching sid on both SQL Server instances as I wrote about in the User mapping to a standby database post if you do not use impersonation so something easy enough to test using sp_helplogins and CREATE Login if needed.

Why

I don't recall where I heard or read about it, or if it's even applicable in this case, but I thought the impersonate rights were inherited implicitly for logins with sysadmin and local db_owner rights.

  Impersonate

  Pass the username and password from the local login to the linked
  server. For SQL Server Authentication, a login with the exact same
  name and password must exist on the remote server. For Windows logins,
  the login must be a valid login on the linked server.

  To use impersonation, the configuration must meet the requirement for
  delegation.

  Not be made

  Specify that a connection will not be made for logins not defined in
  the list.

  Source

Impersonation

If you go with using impersonation, below are a couple syntax examples to use for domain user account logins and local SQL logins which connected to the master DB.

GRANT IMPERSONATE ON LOGIN::[domain\username1_to_be_impersonated] TO [domain\username2]
GRANT IMPERSONATE ON USER::[user1_to_be_impersonated] TO [user2]


REBUILD - Clustered Index, TABLE, or both?

If you rebuild the clustered index there shouldn't be any need to rebuild the table.  If the table was a heap (didn't have a clustered index) then you might want to rebuild it.


Semantic of SQL Query for summing up values

No column is added to the table during the select. Adding columns to a table is usually restricted to a lot fewer people, than those that are allowed to select from a table. You can do this as:

ALTER TABLE tablenames
    ADD AvValue float;

MERGE INTO tablenames x
USING (
    select t.*
         , sum(value) over (partition by prename order by NumberOfDays) SumValue
    from tablenames t
) y
    ON x.<key> = y.<key>
WHEN MATCHED THEN
    UPDATE SET x.AvValue = y.SumValue/y.NumberOfDays

All untested since I don't know what the key for tablenames is.

You can probably calculate AvValue directly in the USING clause, but I'll leave that as an exercise.

EDIT: The implementation of MERGE in SQL-server seems to have several problems, see use-caution-with-sql-servers-merge-statement/ (link provided by Aaron Bertrand in comment. Using an update instead would be something like (still untested):

WITH y AS (
    SELECT t.*
     , SUM(value) OVER (PARTITION BY prename
                        ORDER BY NumberOfDays) SumValue
    FROM tablenames t
)
UPDATE y
SET y.AvValue = y.SumValue/y.NumberOfDays;


Ignore login mappings during Dacpac compare

I found using SqlPackage.exe will not drop security objects.

I was having the same issue as well. Going forward, we are going to deploy database changes using DACPACs. We need a way to exclude users, roles and logins from the update process so they are not dropped from the target SQL Server.

Our database is installed at hundreds of clients sites and many SQL Server installations have had their security setup by the DBAs at that company. They will often add their own domain logins and users and in some cases tighten down roles more than our defaults.

I noticed that when choosing the option to "Upgrade Data-Tier Application" within SQL Server Management Studio 2012 I did not have the option to ignore security objects. The review actually stated it was going to drop users and roles.

But when I used SqlPackage.exe it did not drop the security objects. This is because the parameter "DropObjectsNotInSource" is set to false by default.

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:.\bin\debug\SandBox.dacpac /TargetServerName:sandboxserver /TargetDatabaseName:SandBox

The only downside is that if you want to drop deprecated objects from the database, you will need to add the drops to the post deployment script.

I hope Microsoft will add this feature in the future. These objects are often managed by DBAs and not developers.

Vote here: https://connect.microsoft.com/SQLServer/feedback/details/775839/ssdt-add-publication-setting-to-ignore-database-users


Return inserted entity in mysql stored procedure

That's not using "prepare" to do the escaping.  Put the IN parameter in the EXECUTE.

Please don't name parameters with @ -- it is too confusing with @variables.  I like to prefix in args with _.

A UUID is 36 characters; The binary equivalent is BINARY(16).  Your VARCHAR(32) does not agree with "I use Binary uuid for having more security".


SQL Server link to MySQL reports "using password: no"

I just ran into this installing a linked server to MySQL with the "MySQL 8.0" connector. The same error message in the OP occurred when trying the 64-bit versions of both the ANSI and the Unicode connectors on 64-bit Windows Server 2016 host/SQL Server 2019 Standard.
In my case, the issue had to do with the connection string, specifically the PWD=MyPa$$word portion. Replacing it with the equivalent password=MyPa$$word fixed the issue.
Credit to this MSSQL Tips article, which has a chart of the possible connection string parameters.