Archive for the ‘sql’ Category
T-SQL выборка контента XML nodes в виде таблицы из XML переменной
Я не очень силён в SQL, да и нужен он в последнее время редко, но второй раз искать решение однажды решённой проблемы — это совсем не круто, так что пусть полежит тут.
select T.x.value('.','nvarchar(100)') as id from @xml.nodes('//Value') T(x)
Вообще есть идея собрать католог снипетов и впихнуть поиск по ним как плагин в студию. Как сделал бинг, только с преферансом и куртизанками.
UPD: пример
declare @xml XML = '<?xml version="1.0" encoding="utf-8"?><ArrayOfInt xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><id>1</id><id>2</id><id>3</id><id>4</id><id>5</id><id>6</id><id>7</id><id>8</id><id>9</id><id>10</id><id>11</id><id>12</id><id>13</id><id>14</id><id>15</id><id>16</id><id>17</id><id>18</id><id>19</id><id>20</id><id>21</id><id>22</id><id>23</id><id>24</id><id>25</id><id>26</id><id>27</id><id>28</id><id>29</id><id>30</id><id>31</id><id>32</id><id>33</id><id>34</id><id>35</id><id>36</id><id>37</id><id>38</id><id>39</id><id>40</id><id>41</id><id>42</id><id>43</id><id>44</id><id>45</id><id>46</id><id>47</id><id>48</id><id>49</id><id>50</id><id>51</id><id>52</id><id>53</id><id>54</id><id>55</id><id>56</id><id>57</id><id>58</id><id>59</id><id>60</id><id>61</id><id>62</id><id>63</id><id>64</id><id>65</id><id>66</id><id>67</id><id>68</id><id>69</id><id>70</id><id>71</id><id>72</id><id>73</id><id>74</id><id>75</id><id>76</id><id>77</id><id>78</id><id>79</id><id>80</id><id>81</id><id>82</id><id>83</id><id>84</id><id>85</id><id>86</id><id>87</id><id>88</id><id>89</id><id>90</id><id>91</id><id>92</id><id>93</id><id>94</id><id>95</id><id>96</id><id>97</id><id>98</id><id>99</id><id>100</id></ArrayOfInt>'; select T.c.value('.','int') as id from @xml.nodes('/ArrayOfInt/id') T(c);
Разделение диапазона дат на фреймы
Для отчётов в разрезе времени бывает необходимо разделить диапазон дат на более мелкие промежутки времени по часу или несколько минут. Для этого удобно использовать таблицу в каждой строке которой было бы начало и конец диапазона.
DECLARE @step int --= 15; DECLARE @start datetime -- = '2014-01-01 00:00'; DECLARE @end datetime -- = '2014-01-01 10:00'; ;WITH N(n) AS ( SELECT 0 UNION ALL SELECT n+1 FROM N WHERE N < (DateDiff (mi, @start, @end) / @step) ) SELECT DateAdd(mi, n * @step, @start) AS [Start], DateAdd(mi, (n + 1) * @step, @start) AS [End] FROM N OPTION (MaxRecursion 32000)
Я предпочитаю оборачивать этот запрос в udf. У такого подхода есть досадное ограничение — udf не может содержать OPTION (MAXRECURSION N). Эта опция обязательно понадобится если диапазон дат большой или фрейм очень маленький. Но можно выставить эту опцию в конечном запросе.
Количество строк во всех таблицах (T-SQL)
Иногда полезно для реверс инижинеринга
declare @tmp table (name varchar(50), rcount int) declare @sTblName varchar(50) declare @nRowCount int -- the rows declare @nObjectID int -- Object ID declare iter cursor for select TABLE_NAME from information_schema.tables open iter fetch next from iter into @sTblName while (@@FETCH_STATUS = 0) begin set @nObjectID = OBJECT_ID(@sTblName) if @nObjectID is not null begin select TOP 1 @nRowCount = rows from sysindexes where id = @nObjectID AND indid < 2 insert into @tmp values (@sTblName, @nRowCount) end fetch next from iter into @sTblName end close iter deallocate iter select * from @tmp order by rcount desc
UPD:
Если есть таблицы не в dbo схеме
declare @tmp table (name varchar(60), rcount int) declare @tblName varchar(50) declare @schema varchar(10) declare @nRowCount int -- the rows declare @nObjectID int -- Object ID declare iter cursor for select TABLE_NAME, TABLE_SCHEMA from information_schema.tables open iter fetch next from iter into @tblName, @schema while (@@FETCH_STATUS = 0) begin set @nObjectID = OBJECT_ID(@schema+'.'+@tblName) if @nObjectID is not null begin select TOP 1 @nRowCount = rows from sysindexes where id = @nObjectID AND indid < 2 insert into @tmp values (@tblName, @nRowCount) end fetch next from iter into @tblName, @schema end close iter deallocate iter
UPD2:
И индексы перебилдить все скопом (в тырнете нашёл)
DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@sql) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor deallocate TableCursor